<?php
###############################################################################
# Useful for jqGrid management
# Requires base.php and pdo/cppdo.php
###############################################################################

class gridPDO extends cpPDO
{
	static function getNumericRequestVariable($name, $default)
	{
		if(isset($_REQUEST[$name]))
			return intval($_REQUEST[$name]);
		return $default;
	}
	
	###############################################################################

	static function getIndexedRequestVariable($name, $default, $indexarray)
	{
		$entry = $default;
		if(isset($_REQUEST[$name]))
			$entry = $_REQUEST[$name];
		if(!in_array(strtolower($entry), array_map('strtolower', $indexarray)))
			$entry = $default;
		return $entry;
	}
	
	###############################################################################

	public function fnGetSQLSearchString($searchOperation, $searchString, $searchfield)
	{
		if (strlen($searchString) == strlen(utf8_decode($searchString)))
			$searchString = strtolower($searchString);
		if(strtolower($searchfield) == "tld" && $searchString != "")
			$searchString = trim($searchString, " \r\n.\t;'\"");

		$searchCondition = "";
		switch($searchOperation)
		{
			case 'eq': $searchCondition = '= '    	. $this->db_connect_handle->quote($searchString); 		break;
			case 'ne': $searchCondition = '!= '   	. $this->db_connect_handle->quote($searchString); 		break;
			case 'bw': $searchCondition = 'LIKE ' 	. $this->db_connect_handle->quote($searchString.'%'); 	break;
			case 'ew': $searchCondition = 'LIKE '	. $this->db_connect_handle->quote('%'.$searchString); 	break;
			case 'cn': $searchCondition = 'LIKE '	. $this->db_connect_handle->quote('%'.$searchString.'%'); break;
			case 'lt': $searchCondition = '< '    	. $this->db_connect_handle->quote($searchString); 		break;
			case 'gt': $searchCondition = '> '    	. $this->db_connect_handle->quote($searchString); 		break;
			case 'le': $searchCondition = '<= '   	. $this->db_connect_handle->quote($searchString); 		break;
			case 'ge': $searchCondition = '>= '   	. $this->db_connect_handle->quote($searchString); 		break;
		}
		return $searchCondition;
	}
	
	###########################################################################
	# set $tablename = a. or b. etc for use when data is from two tables
	# $sql = select .. FROM table1 a, table2 b
	
	public function jqGridSearchData($tablename="")
	{
		$ui_search_status	= "false";
		$ui_search_field  	= "";
		$ui_search_string 	= "";
		$ui_search_operator = "";
		$ui_search_filter	= "";
	
		if(isset($_REQUEST['_search']))
			$ui_search_status = $_REQUEST['_search']; 
		if(isset($_REQUEST['searchField']))
			$ui_search_field = $_REQUEST['searchField']; 
		if(isset($_REQUEST['searchString']))
			$ui_search_string = $_REQUEST['searchString']; 
		if(isset($_REQUEST['searchOper']))
			$ui_search_operator = $_REQUEST['searchOper']; 
	
		if(isset($_REQUEST['filters']))
		{
			$ui_search_filter = $_REQUEST['filters'];
			$ui_search_filter = str_replace("\\", "", $ui_search_filter);
		}

		$qWhere = '';
		if($ui_search_status == 'true')
		{
			$qWhere = 'WHERE';
			if($ui_search_filter != "")
			{
				$firstElem = true;
				$searchData = json_decode($ui_search_filter);
				foreach ($searchData->rules as $rule) 
				{
					if (!$firstElem) 
					{
						if (in_array($searchData->groupOp, array('AND', 'OR'))) 
							$qWhere .= ' '.$searchData->groupOp.' ';
					}
					else 
						$firstElem = false;	 
					$qWhere .= " $tablename" . $rule->field . " " . $this->fnGetSQLSearchString($rule->op, $rule->data, $rule->field);
				}
				if($qWhere == 'WHERE' && $ui_search_string != "")
					$qWhere .= " $tablename" . $ui_search_field . ' ' . $this->fnGetSQLSearchString($ui_search_operator, $ui_search_string, $ui_search_field);
			}
			else			
			{
				$qWhere .= " " . $ui_search_field . ' ' . $this->fnGetSQLSearchString($ui_search_operator, $ui_search_string, $ui_search_field);
			}
		}
		
		if($qWhere == '')
			return '';
		else
			return ' ' . $qWhere;
	}
	
	###########################################################################
	
	public function getGridStats($sql, $responce)
	{
		$page = gridPDO::getNumericRequestVariable('page', 1);   
		$limit = gridPDO::getNumericRequestVariable('rows', 20);
		
		$stmt = $this->db_connect_handle->prepare($sql);
		$stmt->execute();
		$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
		$count = 0;
		foreach($rows as $row)
			if(isset($row['count']))
				$count = $row['count'];
		if($count > 0) 
			$total = ceil($count/$limit);
		else
			$total = 0;
		if ($page > $total) 
			$page = 1;
		$start = $limit*$page - $limit; 
		
		$responce->page = $page;
		$responce->total = $total;
		$responce->records = $count;
		$responce->start = $start;
		$responce->limit = $limit;
	}
	
	###########################################################################

	public function jqGridCount($table, $idcolumn, $responce)
	{
		$dbError = false;
		try
		{
			$sqlCount = "SELECT COUNT(DISTINCT $idcolumn) AS count FROM  " . $table . $this->jqGridSearchData();
			# extra constraint field and value
			if(isset($_REQUEST['ecf']) && isset($_REQUEST['ecv']))
			{
				$fparts = explode(",", $_REQUEST['ecf']);
				$vparts = explode(",", $_REQUEST['ecv']);
				if(count($fparts) == count($vparts))
				{
					$i = 0;
					foreach($fparts as $fp)
					{
						$fv = trim($vparts[$i++]);
						$fp = filter_var(trim($fp), FILTER_SANITIZE_STRING);
						if(stristr($sqlCount, " where "))
							$sqlCount .= " AND " . $fp . " = " . $this->db_connect_handle->quote($fv);
						else
							$sqlCount .= " WHERE " . $fp . " = " . $this->db_connect_handle->quote($fv);
					}
				}
			}
			
			$this->getGridStats($sqlCount, $responce);
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################

	public function jqGridData($table, $idcolumn, $columns, $responce)
	{
		$valid_columns = $this->getAllColumnNames($table);
		$sidx = gridPDO::getIndexedRequestVariable('sidx', $idcolumn, $valid_columns); 
		$sord = gridPDO::getIndexedRequestVariable('sord', 'asc', array('asc', 'desc')); 
		
		$dbError = false;
		try
		{
			$SQL = "SELECT DISTINCT $idcolumn, $columns FROM " . $table . $this->jqGridSearchData();
			# extra constraint field and value
			if(isset($_REQUEST['ecf']) && isset($_REQUEST['ecv']))
			{
				$fparts = explode(",", $_REQUEST['ecf']);
				$vparts = explode(",", $_REQUEST['ecv']);
				if(count($fparts) == count($vparts))
				{
					$i = 0;
					foreach($fparts as $fp)
					{
						$fv = trim($vparts[$i++]);
						$fp = filter_var(trim($fp), FILTER_SANITIZE_STRING);
						if(stristr($SQL, " where "))
						{
							if(strtolower($fv) == "null" || strtolower($fv) == "not null")
								$SQL .= " AND " . $fp . " is " . $fv;
							else
								$SQL .= " AND " . $fp . " = " . $this->db_connect_handle->quote($fv);
						}
						else
						{
							if(strtolower($fv) == "null" || strtolower($fv) == "not null")
								$SQL .= " WHERE " . $fp . " is " . $fv;
							else
								$SQL .= " WHERE " . $fp . " = " . $this->db_connect_handle->quote($fv);
						}
					}
				}
			}
			$SQL .= " ORDER BY $sidx $sord";
			if($responce->records > 0)
				$SQL .= " LIMIT $responce->start , $responce->limit";
			$stmt = $this->db_connect_handle->prepare($SQL);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			$i = 0;
			$colnames = explode(",", $columns);
			foreach($rows as $row)
			{
				$responce->rows[$i]['id']=$row[$idcolumn];
				foreach($colnames as $col)
					$responce->rows[$i][$col] = $row[$col];
				$i++;
			}
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	# Replaces request data for "id" with $idcolumn
	
	static function jqGridIDColumnFix($idcolumn)
	{
		if($idcolumn !== "id")
		{
			foreach($_REQUEST as $key=>$uvalue)
			{
				if(strtolower($key) == "id")
				{
					$_REQUEST[$idcolumn] = $uvalue;
					unset($_REQUEST['id']);
				}
			}
		}
	}
	
	###########################################################################
	# $uniqueColumns contains an array of column names that should have
	# unique values (no two rows with the same entry for the column)
	
	public function jqGridEdit($table, $idcolumn, $uniqueColumns)
	{
		$this->jqGridIDColumnFix($idcolumn);
		$all_columns = $this->getColumns($table);
		$valid_columns = $this->getAllColumnNames($table);
		$keyArray = array();
		$valueArray = array();
		$idValue = null;
		foreach($_REQUEST as $key=>$uvalue) 
		{
			if(!strcasecmp($idcolumn,$key))
			{
				$idValue = $uvalue;
				continue;
			}
			if(in_array(strtolower($key), array_map('strtolower', $uniqueColumns)))
			{
				$duplicate = false;
				$idfound = $this->getSingleEntry($table, $key, $uvalue, $idcolumn);
				if($idfound !== false)
				{
					$duplicate = true;
					if(isset($_REQUEST[$idcolumn]))
					{
						if($idfound == $_REQUEST[$idcolumn])
							$duplicate = false;
					}
				}
				if($duplicate)
				{
					$this->setError("jqGridEdit: $key -> $uvalue exists.");
					return false;
				}
			}
		
			$type = "";
			$ac = count($all_columns)/3;
			for($i = 0; $i < $ac; $i++)
			{
				if(!strcasecmp($all_columns[$i*3],$key))
				{
					$type = $all_columns[$i*3+1];
					break;
				}
			}

			if($type != "" && isset($uvalue) && $uvalue != "")
			{
				$uvalue = trim($uvalue);
				$uvalue = trim($uvalue,chr(0xC2).chr(0xA0)); 
				if(strtolower($key) == "password")
					$valueArray[] = generateHash($uvalue);
				else
					$valueArray[] = $uvalue;
				$keyArray[] = $key;
			}
		}
		
		if($idValue != null)
			$valueArray[] = $idValue;
		else
		{
			$this->setError("jqGridEdit: Missing $idcolumn Value");
			return false;
		}

		$dbError = false;
		try
		{
			$sql = "UPDATE " . $table . " SET ";
			foreach($keyArray as $key)
				$sql .= "$key = ?,";
			$sql = trim($sql, ",");
			$sql .= " WHERE $idcolumn = ?";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute($valueArray);
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function jqGridAdd($table, $idcolumn, $uniqueColumns)
	{
		$all_columns = $this->getColumns($table);
		$keyArray = array();
		$valueArray = array();
		foreach($_REQUEST as $key=>$uvalue) 
		{
			if(!strcasecmp($idcolumn,$key))
				continue;
			if(in_array(strtolower($key), array_map('strtolower', $uniqueColumns)))
			{
				$dbError = false;
				$status = $this->isEntryPresent($table, $key, $uvalue, $dbError);
				if($dbError == false && $status === true)
				{
					$this->setError("jqGridEdit: $key -> $uvalue exists.");
					return false;
				}
			}
			$type = "";
			$ac = count($all_columns)/3;
			for($i = 0; $i < $ac; $i++)
			{
				if(!strcasecmp($all_columns[$i*3],$key))
				{
					$type = $all_columns[$i*3+1];
					break;
				}
			}
			if($type != "" && isset($uvalue))
			{
				$uvalue = trim($uvalue);
				$uvalue = trim($uvalue,chr(0xC2).chr(0xA0)); 
				$valueArray[":$key"] = $uvalue;
				$keyArray[] = $key;
			}
		}

		$dbError = false;
		try
		{
			$sql = "INSERT INTO $table (";
			foreach($keyArray as $key)
				$sql .= "$key,";
			$sql = trim($sql, ",");
			$sql .= ") VALUES(";
			foreach($keyArray as $key)
				$sql .= ":$key,";
			$sql = trim($sql, ",");
			$sql .= ")";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute($valueArray);
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function jqGridDelete($table, $idcolumn="id")
	{
		$this->jqGridIDColumnFix($idcolumn);
		if(isset($_REQUEST[$idcolumn]))
		{
			try
			{
				$ids = explode(",", $_REQUEST[$idcolumn]);
				foreach($ids as &$id)
					$id = $this->db_connect_handle->quote($id);
				$this->db_connect_handle->exec("DELETE FROM $table WHERE $idcolumn IN (" . implode(', ', $ids) . ')');
				return true;
			}
			catch (PDOException $e) 
			{
				$dberror = true;
				$this->setError($e->getMessage());
			}
		}
		return false;
	}
}
	
###############################################################################
?>
