<?php
/**
 *	DBContent class used for all database derived content
 */
class DBContent {
	public $auditingOn = true;
	public $table = '';
	public $keyName = '';
	public $keyValue = 0;
	public $fieldArray;
	public $contentTypeName = '';
	public $pageTitle = '';
	public $successMessage = '';
	public $errorMessage = '';
	public $adminID = 0;
	public $newlyAdded = false;

	function __construct($table,$keyName,$keyValue=0,$adminID=0) {
		$this->table = $table;
		$this->keyName = $keyName;
		$this->keyValue = intval($keyValue);
		$this->adminID = intval($adminID);
		$this->fieldArray = array();
	}
	public function addField($fieldID,$fieldValue,$fieldType="text",$insertOnly = false){
		$obj = new DBField($fieldID,$fieldValue,$fieldType,$insertOnly);
		array_push($this->fieldArray,$obj);
	}
	public function addLockedField($fieldID,$fieldType="text"){
		$obj = new DBField($fieldID,"",$fieldType);
		$obj->locked = true;
		array_push($this->fieldArray,$obj);
	}	
	public function unlockField($fieldID,$forceValue=false,$fieldValue="",$forceDB=false){
		foreach($this->fieldArray as $value){
			$obj =& $value;
			if ($obj->fieldID == $fieldID){
				$obj->locked = false;
				if ($forceValue == true){
					$obj->fieldValue = $fieldValue;
				}
				if ($forceDB == true){
					$obj->fieldDBValue = $fieldValue;
				}
			}
		}
	}			
	public function addRequestField($fieldID,$fieldDefaultValue="",$fieldType="text",$insertOnly = false){
		$value = ValidationUtils::getRequestVar("post",$fieldID,$fieldDefaultValue);
		$obj = new DBField($fieldID,$value,$fieldType,$insertOnly);
		array_push($this->fieldArray,$obj);
	}
	public function getFieldValue($fieldID,$fromDB = true){
		foreach($this->fieldArray as $value){
			$obj =& $value;
			if ($obj->fieldID == $fieldID){
				if ($fromDB){
					$returnValue = $obj->fieldDBValue;
				}else{
					$returnValue = $obj->fieldValue;
				}
				//Autoformate date/datetime values
				if ($obj->fieldType == "date"){
					$returnValue = DateUtils::displayDate($returnValue,DATE_FORMAT_SHORT);
				}else if ($obj->fieldType == "datetime"){	
					$returnValue = DateUtils::displayDate($returnValue,DATE_FORMAT_LONG);	
				}
				return $returnValue;
			}
		}
	}
	public function setContentTypeName($name){
		$this->contentTypeName = $name;
		if ($this->keyValue > 0) {
			$this->pageTitle = "Edit " . $this->contentTypeName;
		}else{
			$this->pageTitle = "Add " . $this->contentTypeName;
		}
	}	
	public function save($action="") {
		$newRecord = false;
		if( $this->keyValue > 0 ) {
			$this->updateTableRecord();
			$this->pageTitle = "Edit " . $this->contentTypeName;
		} else {
			$this->insertTableRecord();
			$this->pageTitle = "Add " . $this->contentTypeName;
			$newRecord = true;
		}
		//Audit all that's changed in
		if ($this->auditingOn && $newRecord == false){
			$this->audit();
		}
		//Write out a nice message
		if ($action == "DELETE"){
			$this->successMessage = "Record '".$this->keyValue."' deleted successfully ("  . date(DATE_FORMAT_LONG) . ")";
		}else if ($newRecord){
			$this->successMessage = "Record added successfully ("  . date(DATE_FORMAT_LONG) . ")";
			$this->newlyAdded = true;
		}else{
			$this->successMessage = "Record saved successfully ("  . date(DATE_FORMAT_LONG) . ")";
		}

	}	
	public function audit(){
		//Keep track of every update to each field in history table
		if ($this->keyValue > 0){
			//Cycle through all fields and flag which values have changed
			foreach($this->fieldArray as $value){	
				$obj =& $value;
				$oldValue = addslashes($obj->fieldDBValue);
				if ($obj->locked == false){
					//If there is a value for date_created or date_updated don't store it in audit table
					if ($obj->fieldID == "date_created" || $obj->fieldID == "date_updated" || $obj->fieldID == "created_by"){
						$obj->updated = false;
					}else if ($obj->fieldType == "password"){
						//Is there a value for password (should only come in if user has changed)
						if ($obj->fieldValue != ""){
							$obj->updated = true;
							$oldValue = "N/A";
						}	
					}else if ($oldValue != $obj->fieldValue){
						//For all other non date stamps and password store that it's been changed
						$obj->updated = true;
					}
					if ($obj->updated){
						$sql = "INSERT INTO ". TABLEPRE . "history (table_name,table_id,field_name,old_value,updated_by,date_created) 
								VALUES ('".$this->table."','".$this->keyValue."','".$obj->fieldID."','".$oldValue."','".$this->adminID."','".date("Y-m-d H:i:s")."')";	
						mysqli_query(DBConn::open(),$sql);
					}
				}
			}
		}		
	}
	public function get(){
		if ($this->keyValue > 0){
			$sql = "SELECT * FROM ". TABLEPRE .$this->table." WHERE ". $this->keyName ." = '". $this->keyValue ."' ";
			$result = mysqli_query(DBConn::open(),$sql);
			if ($rs = mysqli_fetch_array($result,MYSQLI_ASSOC)) {	
				foreach($this->fieldArray as $value){	
					$obj =& $value;
					$obj->fieldDBValue = $rs[$obj->fieldID];
				}
			}	
		}
	}
	private function insertTableRecord() {
		$tempFieldIDArray = array();
		$tempValuesArray = array();
		$sqlTemplate = "INSERT INTO ". TABLEPRE .$this->table." ( *fields* ) VALUES ( *values* ) ";
		foreach($this->fieldArray as $value){
			$obj =& $value;
			if ($obj->locked == false){
				array_push($tempFieldIDArray,$obj->fieldID);
				array_push($tempValuesArray,"'".$obj->fieldValue."'");
			}
		}	
		//Format the SQL
		$sql = str_replace("*fields*", implode(",", $tempFieldIDArray), $sqlTemplate);
		$sql = str_replace("*values*", implode(",", $tempValuesArray), $sql);
		//Execute
		mysqli_query(DBConn::open(),$sql);
		$this->keyValue = mysqli_insert_id(DBConn::open());
		return true;
	}
	private function updateTableRecord() {
		$tempFieldArray = array();
		$sqlTemplate = "UPDATE ". TABLEPRE .$this->table." SET *fieldValuePairs* WHERE ". $this->keyName ." = '". $this->keyValue ."' ";
		foreach($this->fieldArray as $value){
			$obj =& $value;
			if ($obj->insertOnly == false && $obj->locked == false){
				if (($obj->fieldType == "datetime" || $obj->fieldType == "date") && $obj->fieldValue == "NULL"){
					array_push($tempFieldArray,$obj->fieldID . " = NULL");
				}else{
					array_push($tempFieldArray,$obj->fieldID . "='" . $obj->fieldValue ."'");
				}
			}
		}
		//Format the SQL
		$sql = str_replace("*fieldValuePairs*", implode(",", $tempFieldArray), $sqlTemplate);
		//Execute
		mysqli_query(DBConn::open(),$sql);
		return true;
	}
}
class DBField{
	public $fieldID;
	public $fieldValue;
	public $fieldDBValue;
	public $fieldType;
	public $insertOnly = false;
	public $locked = false;			//Allow updating?
	public $updated = false;	
	
	public function __construct($fieldID,$fieldValue="",$fieldType="text",$insertOnly=false) {
		$this->fieldID = $fieldID;
		if ($fieldType == "datetime" || $fieldType == "date"){
			$dateTime = false;
			$defaultDate = false;
			if ($fieldType == "datetime"){$dateTime = true;}
			if ($fieldValue == "NOW"){
				$defaultDate = true;
				$fieldValue = "";
			}
			$this->fieldValue = ValidationUtils::mySQLDate($fieldValue,$defaultDate,$dateTime);
		}else if ($fieldType == "int"){
			$this->fieldValue = intval($fieldValue);
		}else if ($fieldType == "url"){
			$this->fieldValue = ValidationUtils::fullURL($fieldValue);
		}else{
			$this->fieldValue = $fieldValue;
		}
		$this->fieldType = $fieldType;
		$this->insertOnly = $insertOnly;
	}
}





/**
 *  Pagination class used for processing 'pages' of results (x results/page)
 *  Dependencies...
 *  	* ValidationUtils for search and page number defaults
 *  	* global var RESULTS_PER_PAGE defined in constants.php
 *		* Primary table must have an 'active' column (or have customOperator override)
 *  
EXAMPLES
 */
class Pagination{
	public $numRows = 0;
	public $lastPage = 0;
	public $perPage = 0;	
	public $curPage = 0;
	public $prevPage = 0;
	public $nextPage = 0;
	
	public $debug = false;
	
	public $sqlLimit = '';
	public $setID = '';				//Keeps pagination results unique
	public $primaryTableAlias = '';	//Allows for automatic 'active' setting
	public $unescapedSearchPhrase = '';
	public $selectedStatuses = '';
	
	public $txtFirst = "FIRST";
	public $txtPrev = "PREV";
	public $txtNext = "NEXT";
	public $txtLast = "LAST";
	
	public $sqlWhereStatus = '';
	public $sqlWhereSearch = '';	
	public $sqlWhereCustom = '';
	private $customOperator = '';	//'AND' 'OR' '' determines how sqlWhereCustom integrates
	
	private $sqlTables = '';
	private $sqlFields = '';
	private $sqlWhere = '';
	private $sqlOrderBy = '';
	
	private $columnsArray;
	private $defaultSort = "";
	
	public function __construct($setID = '',$primaryTableAlias = '',$page = '') {
		if ($page == ''){$page = $_SERVER['PHP_SELF'];}
		$this->page = $page;
		$this->setID = $setID;
		if ($primaryTableAlias != ""){
			$this->primaryTableAlias = $primaryTableAlias . ".";		
		}
		$this->columnsArray = array();
		$this->processRequestVars();
	}
	public function limitResults($perPage = 0){
		if ($perPage == 0){$perPage = RESULTS_PER_PAGE;}
		$this->perPage = intval($perPage);
		//Build 'where' --> custom, status, search (check for override)
		if ($this->customOperator == '' && $this->sqlWhereCustom != ''){
			$this->sqlWhere = $this->sqlWhereCustom;
		}else{
			//Add all where clauses to array and join
			$sqlWhereArray = array();
			if ($this->sqlWhereStatus != ""){array_push($sqlWhereArray,$this->sqlWhereStatus);}
			if ($this->sqlWhereSearch != ""){array_push($sqlWhereArray,$this->sqlWhereSearch);}
			$this->sqlWhere = " (" . implode(" AND ", $sqlWhereArray) . ") ";
			if (($this->customOperator == 'OR' || $this->customOperator == 'AND') && $this->sqlWhereCustom != ''){
				$this->sqlWhere .= $this->customOperator . ' ' . $this->sqlWhereCustom;
			}
		}
		//Determine boundaries
		$sqlCount = "SELECT COUNT(*) FROM " . $this->sqlTables;
		if ($this->sqlWhere != ''){
			$sqlCount .= " WHERE " . $this->sqlWhere;
		}	
		if ($this->debug){echo "limitResults:" . $sqlCount;}
		$result = mysqli_query(DBConn::open(),$sqlCount);
		$query_data = mysqli_fetch_row($result);
		$this->numRows = $query_data[0];
		$this->lastPage = ceil($this->numRows/$this->perPage);
		//Correct page numbers that exceed range
		if ($this->curPage > $this->lastPage) {$this->curPage = $this->lastPage;} 
		if ($this->curPage < 1) {$this->curPage = 1;} 	
		//Build some other nav vars
		$this->prevPage = $this->curPage-1;
		if ($this->prevPage < 1){$this->prevPage = 1;}
		$this->nextPage = $this->curPage+1;
		if ($this->nextPage > $this->lastPage){$this->nextPage = $this->lastPage;}
		//Build limit
		$this->sqlLimit = ' LIMIT ' . ($this->curPage - 1) * $this->perPage .',' .$this->perPage;
	}
	public function getSQLForPage(){
		//Build Order By based on default 
		$sqlOrderBy = "";
		$sqlDefaultSort = "";
		foreach($this->columnsArray as $value){
			$obj =& $value;
			if ($obj->columnID == $this->defaultSort){
				$sqlDefaultSort = $obj->sqlOB;
			}
			//Set sort based on user input
			if ($obj->columnID == $this->sqlOrderBy){
				$sqlOrderBy = $obj->sqlOB;
			}
			unset($obj);
		}
		//If there isn't a column set set it to default
		if ($sqlOrderBy == ""){$sqlOrderBy = $sqlDefaultSort;}
		//Build the full SQL block
		$tempSQL = "SELECT " . $this->sqlFields . " FROM " . $this->sqlTables;
		if ($this->sqlWhere != ""){$tempSQL .= " WHERE " . $this->sqlWhere;}
		if ($sqlOrderBy != ""){$tempSQL .= " ORDER BY " . $sqlOrderBy;}
		$tempSQL .= $this->sqlLimit;
		
		return $tempSQL;
	}
	public function getResults($perPage = 0){
		$this->limitResults($perPage);
		$sql = $this->getSQLForPage();
		return mysqli_query(DBConn::open(),$sql);
	}
	public function getNavigation($showFirstLast = true){
		$temp = "<div class='pagination'>";
		$temp .= "	<div class='pagination-nav-holder'>";
		$temp .= "		<div class='pagination-nav'>";
		$temp .= $this->getNavigationLeft($showFirstLast);
		$temp .= $this->pageDisplay();
		$temp .= $this->getNavigationRight($showFirstLast);
		$temp .= "		</div>";
		$temp .= "	</div>";
		$temp .= $this->getNumRecordsDisplay($this->unescapedSearchPhrase);		
		$temp .= "</div>";		
		return $temp;
	}
	public function pageDisplay(){
		if ($this->lastPage > 1){
			//Show the number of pages (maybe add different formats sometime)
			return "<div class='pagination-page-display'>(Page " . $this->curPage . " of ". $this->lastPage . ")</div>";
		}
	}
	public function getNavigationLeft($showFirstLast = true){
		$temp = "";
		if ($this->lastPage > 1){
			//Show the 'first' nav item
			if ($showFirstLast){
				$temp .= "<div class='pagination-first'>";
				if ($this->curPage == 1){
					$temp .= $this->txtFirst;
				}else{
					$temp .= "<a href='" . $this->page . "?".$this->setID."pnum=1'>". $this->txtFirst ."</a>";
				}
				$temp .= "</div>";
			}
			//Show the 'prev' nav item
			$temp .= "<div class='pagination-prev'>";
			if ($this->curPage == 1){
				$temp .= $this->txtPrev;
			}else{
				$temp .= "<a href='" . $this->page ."?".$this->setID."pnum=" . $this->prevPage. "'>". $this->txtPrev ."</a>";
			}
			$temp .= "</div>";
		}
		return $temp;
	}
	public function getNavigationRight($showFirstLast = true){
		$temp = "";
		if ($this->lastPage > 1){
			//Show the 'next' nav item
			$temp .= "<div class='pagination-next'>";
			if ($this->curPage == $this->lastPage){
				$temp .= $this->txtNext;
			}else{
				$temp .= "<a href='" . $this->page ."?".$this->setID."pnum=" . $this->nextPage. "'>". $this->txtNext ."</a>";
			}
			$temp .= "</div>";
			//Show the 'last' nav item
			if ($showFirstLast){
				$temp .= "<div class='pagination-last'>";
				if ($this->curPage == $this->lastPage){
					$temp .= $this->txtLast;
				}else{
					$temp .= "<a href='" . $this->page ."?".$this->setID."pnum=" . $this->lastPage . "'>". $this->txtLast ."</a>";
				}
				$temp .= "</div>";
			}
		}
		return $temp;
	}
	public function getNumRecordsDisplay($unescapedSearchPhrase = ''){
		$temp = "<div class='pagination-num-records'>" . number_format($this->numRows) . " results found";
		if ($unescapedSearchPhrase != ""){$temp .= " for <span class='pagination-search'>\"" . $unescapedSearchPhrase . "\"</span>";}
		$temp .= "</div>";
		return $temp;
	}
	public function processRequestVars(){		
		//Incoming vars
		$searchAction = ValidationUtils::getRequestVar("both",$this->setID."action","");
		$unescapedSearchPhrase = ValidationUtils::getRequestVar("both",$this->setID."search","",false);
		$curPage = intval(ValidationUtils::getRequestVar("both",$this->setID."pnum",0));
		$sqlOrderBy = ValidationUtils::getRequestVar("both",$this->setID."ob");
		$status = ValidationUtils::getRequestVar("both",$this->setID."status");
		//Can be set as multivar
		if(!empty($_POST['status'])){$status = implode(",", $_POST['status']);}	
		
		//Order by
		if ($sqlOrderBy != ""){
			setcookie($this->setID ."ob", $sqlOrderBy, time()+3600);
		}else{
			$sqlOrderBy = $_COOKIE[$this->setID ."ob"];
		}
		//Cur page
		if ($curPage != 0){
			setcookie($this->setID ."pnum", $curPage, time()+3600);
		}else{
			$curPage = intval($_COOKIE[$this->setID ."pnum"]);
		}
		//Search
		if ($unescapedSearchPhrase != "" && $searchAction == "search"){
			setcookie($this->setID ."search", $unescapedSearchPhrase, time()+3600);
			setcookie($this->setID ."pnum", "", time()+3600);
		}else if ($searchAction == "clear"){
			setcookie($this->setID ."pnum", "", time()+3600);
			setcookie($this->setID ."search", "", time()+3600);
			$unescapedSearchPhrase = "";
		}else{
			$unescapedSearchPhrase = $_COOKIE[$this->setID ."search"];
		}
		//Status
		if ($status != ""){
			setcookie($this->setID . "status", $status, time()+3600);
		}else{
			$status = addslashes($_COOKIE[$this->setID ."status"]);
		}
		if ($status == ""){$status = "1,2";}
		
		//Set values for use
		$this->sqlOrderBy = $sqlOrderBy;
		$this->unescapedSearchPhrase = $unescapedSearchPhrase;
		$this->curPage = $curPage;
		$this->sqlWhereStatus = " " . $this->primaryTableAlias . "active IN (".$status.") ";
		$this->selectedStatuses = $status;	
	}
	public function addColumn($columnID,$columnTitle,$sqlOB="",$align="",$width=""){
		$obj = new PaginationColumn($columnID,$columnTitle,$sqlOB,$align,$width);
		array_push($this->columnsArray, $obj);
	}	
	public function setDefaultSort($columnID){
		//Make sure this column exists in columnsArray (if not we'll default sort to first column)
		$this->defaultSort = $columnID;
	}
	public function setSearchableColumns($fieldList){
		//This will only search columns if there is a search phrase
		if ($this->unescapedSearchPhrase != ""){
			$searchColumns = explode(",", $fieldList);
			$colCount = 0;
			$maxColumns = count($searchColumns);
			$sqlSearch = " (";
			foreach($searchColumns as $value){
				$colCount++;
				$sqlSearch .= $value . " LIKE '%". addslashes($this->unescapedSearchPhrase) ."%'";
				if ($colCount < $maxColumns){
					$sqlSearch .= " OR ";
				}
			}
			$sqlSearch .= ") ";
			$this->sqlWhereSearch = $sqlSearch;
		}
	}	
	public function setCustomWhere($customOperator='',$sqlWhereCustom = ''){	
		$this->sqlWhereCustom = trim($sqlWhereCustom);
		$this->customOperator = trim($customOperator);
	}
	public function setSQLTables($sqlTables){
		$this->sqlTables = trim($sqlTables);
	}
	public function setSQLFields($sqlFields){
		$this->sqlFields = trim($sqlFields);
	}	
	public function getColumns(){
		$temp = "";
		foreach($this->columnsArray as $value){
			$temp .= $this->getColumn($value);
		}
		return $temp;
	}
	public function getColumn($obj){
		$temp .= "<th";
		if ($obj->align != "" || $obj->width != ""){
			$temp .= " style='";
			if ($obj->align != ""){$temp .= "text-align:".$obj->align.";";}
			if ($obj->width != ""){$temp .= "width:".$obj->width."px;";}
			$temp .= "'";
		}
		$temp .= ">";
		if ($obj->sqlOB != ""){
			//Indicated that this column is selected
			if ($this->sqlOrderBy == $obj->columnID){$temp .= "*";}
			$temp .= "<a href='". $this->page . "?".$this->setID."ob=" . $obj->columnID . "'>".$obj->columnTitle."</a>";
		}else{
			$temp .= $obj->columnTitle;
		}
		$temp .= "</th>";	
		return $temp;
	}
}
class PaginationColumn{
	public $columnID;
	public $columnTitle;
	public $sqlOB;
	public $width;
	public $align;
	
	public function __construct($columnID,$columnTitle,$sqlOB="",$align="",$width="") {
		$this->columnID = $columnID;		
		$this->columnTitle = $columnTitle;
		$this->sqlOB = $sqlOB;
		$this->align = $align;
		$this->width = $width;
	}
}
?>