<?php
/**
 * Class that operate on table 'tbl_database_list'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2017-02-25 06:09
 */
class TblDatabaseListMySqlExtDAO extends TblDatabaseListMySqlDAO{

	function getNextForQueue($type){
		
		$txt = '
				SELECT 
					dbl.*
					FROM 
					tbl_database_list dbl
					
					WHERE 
						dbl.ub LIKE "'.$type.'" 
						
					ORDER BY id DESC 
					LIMIT 1
		';
		
		$sqlQuery = new SqlQuery($txt);
		
		$data = self::getTblDatabaseList($sqlQuery);
		
		return $data[0]->databaseName;
	}

	function searchDatabaseListAll($search, $type, $status){
		
		$txt = '
				SELECT 
					dbl.*
					FROM 
					tbl_database_list dbl
					
					WHERE 
						dbl.database_name LIKE "%'.$search.'%" and 
						dbl.ub LIKE "'.$type.'" and 
						dbl.status LIKE "'.$status.'" 
		';
		
		$sqlQuery = new SqlQuery($txt);
		
		return self::getTblDatabaseList($sqlQuery);
	}

	function searchDatabaseList($search, $type, $status, $count, $page){
		$from = ($page - 1) * $count;
		$txt = '
				SELECT 
					dbl.*
					FROM 
					tbl_database_list dbl
					
					WHERE 
						dbl.database_name LIKE "%'.$search.'%" and 
						dbl.ub LIKE "'.$type.'" and 
						dbl.status LIKE "'.$status.'" 
						AND dbl.status != "removed"
					ORDER BY 
						CASE 
							WHEN dbl.status = "queue"
								THEN 1
							WHEN dbl.status = "available"
								THEN 2
							WHEN dbl.status = "used"
								THEN 3
							ELSE 0
						END, dbl.id DESC
					
					LIMIT '.$from.','.$count.'
					
		';
		
		$sqlQuery = new SqlQuery($txt);
		
		return self::getTblDatabaseList($sqlQuery);
	}

	private static function getTblDatabaseList($sqlQuery){
		$queue = QueryExecutor::execute($sqlQuery);
		
		if(empty($queue)) return false;
		
		$return = array();
		
		foreach($queue as $each){
			$data = new TblDatabaseList();
			
			$data->id = $each['id'];
			$data->ub = $each['ub'];
			$data->databaseName = $each['database_name'];
			$data->status = $each['status'];
			$data->dateCreated = $each['date_created'];
			$data->dateUsed = $each['date_used'];
			$data->version = $each['version'];
			
			$return[] = $data;
		}
		
		return $return;
	}
	
	static function getDbUpdates($type)
	{
		$sql = "SELECT * FROM tbl_database_list WHERE (status != 'queue' AND status != 'removed') AND ub = '{$type}'";
		$sqlQuery = new SqlQuery($sql);
		return self::getTblDatabaseList($sqlQuery);
	}
	
	function getVerifiedDatabase($mainDb,$name)
	{
		$sql = "SELECT * FROM {$mainDb} WHERE database_name = '{$name}' GROUP BY database_name";
		$sqlQuery = new SqlQuery($sql);
		return QueryExecutor::execute($sqlQuery);
	}
	
	function getQueuedActivate()
	{
		$sql  = "
					SELECT tdl.* FROM tbl_database_list  tdl
						WHERE
								tdl.status = 'queue' 
							AND
								(SELECT count(id) FROM tbl_database_list WHERE status = 'available' AND ub = tdl.ub) < 51
						ORDER BY tdl.date_created ASC
					LIMIT 1
				";
		$sqlQuery = new SqlQuery($sql);
		return QueryExecutor::execute($sqlQuery);
	}
	
	function getDbListSortByDbName($type)
	{
		$sql = "SELECT * FROM tbl_database_list WHERE ub = '{$type}' AND status = 'queue' ORDER BY date_created DESC LIMIT 1";
		$sqlQuery = new SqlQuery($sql);
		return QueryExecutor::execute($sqlQuery);
	}
}
?>