<?php
###############################################################################
# DataGrid.php
#
#
# @author Anil Kumar <akumar@codepunch.com>
# @link   https://codepunch.com
#
############################################################################### 

namespace 	CodePunch\UI;

use 		CodePunch\Base\Util as UTIL;
use			CodePunch\Base\Text as TEXT;
use 		CodePunch\Base\CPLogger;
use 		Exception;

###############################################################################

class DataGrid {
	
	public $database		= null;
	public $authentication	= null;
	
	###########################################################################
	
	public function __construct($auth)
	{ 
		$this->authentication = $auth;
		$this->database = $auth->getDatabase();
	}
	
	###########################################################################
	
	private function getGridStats($count, $page, $limit)
	{
		if($count > 0) 
			$total = ceil($count/$limit);
		else
			$total = 0;
		if ($page > $total) 
			$page = 1;
		$start = $limit*$page - $limit; 
		$responce = new \stdClass();
		$responce->page = $page;
		$responce->total = $total;
		$responce->records = $count;
		$responce->start = $start;
		$responce->limit = $limit;
		return $responce;
	}
	
	###############################################################################

	public function jqGridOps($searchOperation, $ss, $searchfield, &$sv)
	{
		if (strlen($ss) == strlen(mb_convert_encoding($ss, 'ISO-8859-1', 'UTF-8')))
			$ss = strtolower($ss);
		if($ss != "")
			$ss = trim($ss, " \r\n.\t;'\"");

		$sc = "";
		switch($searchOperation)
		{
			case 'eq': 
				$sc = "LOWER($searchfield)" . ' = ? ';
				$sv[] = $ss;
				break;
			case 'ne': 
				$sc = "LOWER($searchfield)" . ' !=? ';
				$sv[] = $ss;
				break;
			case 'bw': 
				$sc = "LOWER($searchfield)" . ' LIKE ? ';
				$sv[] = $ss . "%";
				break;
			case 'ew': 
				$sc = "LOWER($searchfield)" . ' LIKE ? ';
				$sv[] = "%" . $ss;
				break;
			case 'cn': 
				$sc = "LOWER($searchfield)" . ' LIKE ? ';
				$sv[] = "%" . $ss . "%";
				break;
			case 'lt': 
				$sc = "LOWER($searchfield)" . ' < ';
				$sv[] = $ss;
				break;
			case 'gt': 
				$sc = "LOWER($searchfield)" . ' > ';
				$sv[] = $ss;
				break;
			case 'le': 
				$sc = "LOWER($searchfield)" . ' <= ';
				$sv[] = $ss;
				break;
			case 'ge': 
				$sc = "LOWER($searchfield)" . ' >= ';
				$sv[] = $ss;
				break;
		}
		return $sc;
	}
	
	###########################################################################
	# set $tablealias = a. or b. etc for use when data is from two tables
	# $sql = select .. FROM table1 a, table2 b
	
	public function jqGridSearchData($tablealias, &$params)
	{
		$dosearch		= UTIL::get_request_data_boolean("_search");
		$searchfield    = UTIL::get_sanitized_request_string("searchField", "");
		$searchstring 	= UTIL::get_sanitized_request_string("searchString", "");
		$searchop 		= UTIL::get_sanitized_request_string("searchOper", "");
		
		// $searchfilter is not safe!!! Clean at first opportunity
		$searchfilter	= UTIL::get_unsafe_request_data("filters", "");
		$searchfilter 	= str_replace("\\", "", $searchfilter);

		$qWhere = '';
		if($dosearch) {
			if($searchfilter != "") {
				$firstElem = true;
				$searchData = json_decode($searchfilter);
				foreach ($searchData->rules as $rule) {
					
					// Sanitize input
					$rule->op = UTIL::sanitize_string($rule->op);
					$rule->data =  UTIL::sanitize_string($rule->data);
					$rule->field = UTIL::sanitize_string($rule->field);
					
					if (!$firstElem) {
						if (in_array($searchData->groupOp, array('AND', 'OR'))) 
							$qWhere .= ' ' . $searchData->groupOp . ' ';
					}
					else 
						$firstElem = false;	 
					$qWhere .= $this->jqGridOps($rule->op, $rule->data, $tablealias.$rule->field, $params);
				}
				if($qWhere == '' && $searchstring != "")
					$qWhere .= $this->jqGridOps($searchop, $searchstring, $tablealias.$searchfield, $params);
			}
			else {
				$qWhere .= $this->jqGridOps($searchop, $searchstring, $tablealias.$searchfield, $params);
			}
		}
		return $qWhere == "" ? "" : "({$qWhere})";
	}
	
	###########################################################################
	# Get jQGrid Column Model Data
	# $maintable - $the main table to get data from
	# $id - the id column name for the maintable
	# $columns - comma separated list of columns
	# $sidx, $sord (sort column and order)
	# $cid = category id
	# $where, $params - Extra where clause 
	# $validcids - The category ids the user is allowed to access.
	# $responce = null will return just the counts and pagination data.
	# $responce = pagination data, will return the actual cow data
	#
	public function jqGridDomainTableData($maintable, $idcolumn, $columns, $sidx, $sord, $cid, $where, $params, $validcids=array(), $page=1, $limit=20, $responce=null) {
		try {
			$has_subdomain_columns = false;
			$queryBuilder = $this->database->connection->createQueryBuilder();
			$select = explode(",", $columns);

			$alldomaincolumns = null;
			if($sidx != "" && substr($sidx, 1, 1) != ".") 
				$sidx = $this->database->formatSortingColumn($sidx, $alldomaincolumns);
			
			// When sorting on hostname make sure that 'domain and 'subdomain' are in SELECT
			if($sidx == "hostname" || $sidx == "s.hostname") {
				if(!in_array("domain", $select) && !in_array("d.domain", $select))
					$select[] = "domain";
				if(!in_array("subdomain", $select) && !in_array("s.subdomain", $select))
					$select[] = "subdomain";
			}

			$groupby = $select;
			if(!in_array($sidx, $select) && $sidx != "") {
				$select[] = $sidx;
				$groupby[] = $sidx;
			}
			if(!in_array($idcolumn, $select) && $idcolumn != "") {
				$select[] = $idcolumn;
				$groupby[] = $idcolumn;
			}
			foreach($select as &$s) {
				$s = $this->database->formatSelectOrGroupColumn($s, $alldomaincolumns, true);
				if($has_subdomain_columns === false && (strtolower(mb_substr($s, 0, 2)) == "s." || strtolower(mb_substr($s, 0, 6)) == "concat"))
					$has_subdomain_columns = true;
			}
			$select = array_unique($select);
			foreach($groupby as &$s) {
				$s = $this->database->formatSelectOrGroupColumn($s, $alldomaincolumns, false);
			}
			$groupby = array_unique($groupby);
			if($responce == null) {
				unset($groupby);
				if($has_subdomain_columns)
					$select = "count(s.hid) as count";
				else
					$select = "count(DISTINCT d.{$idcolumn}) as count";
			}
			$queryBuilder->select($select);
			$queryBuilder->from($maintable, 'd');
			if($has_subdomain_columns) {
				$subdomaintable = $this->database->getSubdomainTableName();
				$queryBuilder->innerJoin('d', $subdomaintable, 's', "d.sid=s.sid");
			}
			if($where != "") {
				$queryBuilder->where($where);
				$index = 0;
				foreach($params as $p) {
					if(strtolower($p) == "empty")
						$p = "";
					$p = UTIL::expand_date_codes($p);
					$queryBuilder->setParameter($index, $p);
					$index++;
				}
			}
			
			$categoryQuery = $this->database->getCategoryQueryForCurrentUser($cid, $this->authentication, $validcids);
			if($categoryQuery != "")
				$queryBuilder->andWhere($categoryQuery);
			
			if(isset($groupby))
				$queryBuilder->groupBy($groupby);
			if($responce != null) {
				if($sidx != "" && $sidx != "s.hostname") 
					$queryBuilder->orderby($sidx, $sord);
				else if($sidx == "s.hostname") 
					$queryBuilder->orderby("d.domain,s.subdomain", $sord);
				$queryBuilder->setFirstResult($responce->start);
				$queryBuilder->setMaxResults($responce->limit);
			}
			//UTIL::debug_print($queryBuilder->getSQL());
			$rows = $queryBuilder->execute()->fetchAll();
			if($responce != null) {
				$rcolumns = explode(",", $columns);
				$i = 0;
				foreach($rows as $row)
				{
					$row = array_change_key_case($row, CASE_LOWER);
					// Replace the s_ in alias names with s.
					$keys = array_keys($row);
					foreach($keys as $k) {
						if(UTIL::starts_with($k, "s_")) {
							$k2 = "s." . substr($k, 2);
							$row[$k2] = $row[$k];
							unset($row[$k]);
						}
					}
					if($has_subdomain_columns)
						$responce->rows[$i]['id'] = $i+1;
					else
						$responce->rows[$i]['id']=$row[$idcolumn];
					foreach($rcolumns as $col) {
						$rcol = $col;
						$rcol = str_ireplace("d.", "", $rcol);
						if(!isset($row[$rcol])) {
							if(substr($rcol,1,1) != ".") {
								if(!$has_subdomain_columns && isset($row["d.".$rcol]))
									$row[$rcol] = $row["d.".$rcol];
								else if(isset($row["s.".$rcol]))
									$row[$rcol] = $row["s.".$rcol];
							}
						}
						if(!isset($row[$rcol]))
							$row[$rcol] = "";
						if($rcol == "hostname" || $rcol == "s.hostname")
							$row[$rcol] = str_replace("@.", "", $row[$rcol]);
						else if($rcol == "days_to_expiry")
							$row[$rcol] = $this->database->findDaysToExpiry($row);
						$responce->rows[$i][$col] = $row[$rcol];
					}
					$i++;
				}
				return $responce;
			}
			else {
				$rows = UTIL::array_flatten($rows);
				$rows = array_change_key_case($rows, CASE_LOWER);
				$count = 0;
				if(isset($rows['count']))
					$count = $rows['count'];
				$responce = $this->getGridStats($count, $page, $limit);
				// Call self with the responce filled in.
				return $this->jqGridDomainTableData($maintable, $idcolumn, $columns, $sidx, $sord, $cid, $where, $params, $validcids, $page, $limit, $responce);
			}
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
	}
	
	###########################################################################
	# Get jQGrid Column Model Data
	# $maintable - $the main table to get data from
	# $id - the id column name for the maintable
	# $columns - comma separated list of columns
	# $sidx, $sord (sort column and order)
	# $cid = category id
	# $where, $params - Extra where clause 
	# $validcids - The category ids the user is allowed to access.
	# $responce = null will return just the counts and pagination data.
	# $responce = pagination data, will return the actual cow data
	#
	public function jqGridTableData($maintable, $idcolumn, $columns, $sidx, $sord, $where, $params, $page, $limit, $responce=null) {
		try {
			$queryBuilder = $this->database->connection->createQueryBuilder();
			if($responce != null) {
				$select = explode(",", $columns);
				foreach($select as &$s) {
					if(UTIL::starts_with($s, "s."))
						$s = substr($s, 2);
				}
				if(!in_array($idcolumn, $select) && $idcolumn != "") 
					$select[] = $idcolumn;
				
				if(UTIL::starts_with($sidx, "s."))
					$sidx = substr($sidx, 2);
				if(!in_array($sidx, $select) && $sidx != "") 
					$select[] = $sidx;
			}
			else {
				$select = "count({$idcolumn}) as count";
			}
			$queryBuilder->select($select);
			$queryBuilder->from("$maintable s");
			if($where != "") {
				$queryBuilder->where($where);
				$index = 0;
				foreach($params as $p) {
					if(!is_array($p)) 
						$queryBuilder->setParameter($index, $p);
					else if(count($p) == 2) 
						$queryBuilder->setParameter($index, $p[0], $p[1]);
					$index++;
				}
			}
			if(isset($groupby))
				$queryBuilder->groupBy($groupby);
			if($responce != null) {
				if($sidx != "")
					$queryBuilder->orderby($sidx, $sord);
				$queryBuilder->setFirstResult($responce->start);
				$queryBuilder->setMaxResults($responce->limit);
			}
			$rows = $queryBuilder->execute()->fetchAll();
			if($responce != null) {
				$rcolumns = explode(",", $columns);
				$i = 0;
				foreach($rows as $row)
				{
					$row = array_change_key_case($row, CASE_LOWER);
					$responce->rows[$i]['id']=$row[$idcolumn];
					foreach($rcolumns as $col) {
						$rcol = str_ireplace("s.", "", $col);
						//$rcol = $col;
						if($row[$rcol] == null)
							$row[$rcol] = "";
						$responce->rows[$i][$col] = $row[$rcol];
					}
					$i++;
				}
				return $responce;
			}
			else {
				$rows = UTIL::array_flatten($rows);
				$rows = array_change_key_case($rows, CASE_LOWER);
				$count = 0;
				if(isset($rows['count']))
					$count = $rows['count'];
				$responce = $this->getGridStats($count, $page, $limit);
				// Call self with the responce filled in.
				return $this->jqGridTableData($maintable, $idcolumn, $columns, $sidx, $sord, $where, $params, $page, $limit, $responce);
			}
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
	}
}

