<?php
/**
## Version: 2.1 -- Date: 05-SEP-2019 -- Added version information and revision history.
**/
class SableDB
{
	protected $con, $query, $queryType, $fieldName;
	public $qryObject, $error, $useCallingSite, $recordCount, $fieldCount, $qryRows, $affectedRows;
	private $qryResult;
	// constructor method
	function __construct()
	{
		if ($this->useCallingSite && isset($_SESSION['site']))
		{
			/*
			Let's first get the calling site connection details.
			if $useCallingSite==true then we need to connect to the calling sites database
			and not our default (Spitronics) database:			
			*/
			$qry_result=$con->query('select dbv1, dbv2, dbv3 from sites where site_id = '.$_SESSION['site']['site_id']);				
			$row = $this->qryResult->fetch_assoc();
			$dbu = sableDecode($row['dbv1']);
			$dbp = sableDecode($row['dbv2']);
			$dbn = sableDecode($row['dbv3']);
			//$this->qryResult->close();
		}
		else 
		{
			$dbn = SABLE_DATABASE_NAME;	
			$dbu = SABLE_DATABASE_USER;
			$dbp = SABLE_DATABASE_PASSWORD;				
			$this->con = new mysqli(SABLE_DATABASE_HOST, SABLE_DATABASE_USER, SABLE_DATABASE_PASSWORD, SABLE_DATABASE_NAME);
		}
		$this->con = new mysqli(SABLE_DATABASE_HOST, $dbu, $dbp, $dbn);
        $this->con->set_charset('utf8');
		$this->verifyDBConnect();
	}
	
	// desctructor method
	function __destruct()
	{
		//$this->qryResult->close();
		$this->con->close();
	}
	
	
	private function verifyDBConnect()
	{
		if ($this->con->connect_errno)
		{		
			if (SABLE_DEMO_SYSTEM) $this->error=": ".$this->con->connect_error; else $this->error = ".";
			die('Unable to establish a connection to the database server'.$this->error);
			exit();
		}		
	}
	
	private function runSQL()
	{
		$this->verifyDBConnect($this->con);
		if (!$this->qryResult=$this->con->query($this->query))
		{ 
			$this->error=$this->con->error;
			$errData = "MySQL Error: ".$this->error;
			if (SABLE_DEMO_SYSTEM) $errData.=" [".$this->query."]";
			exit($errData);
		}
		
		$this->qryObject	= $this->qryResult;
		if (isset($this->qryObject->type))
			$this->recordCount 	= mysqli_num_rows($this->qryResult);
		else 
			$this->recordCount = 0;
		$this->fieldCount 	= mysqli_field_count($this->con);
		$this->affectedRows = mysqli_affected_rows($this->con);
	}
	
	private function prepQuery($sql)
	{
		$this->query = $sql;
		$this->runSQL();		
	}
	
	/*
	Function 'executeQuery' has been written to facilitate the execution of SQL queries without
	the need for multiple lines of code. There are 3 query types that are offered by this function:
		1. Query Type 0: Indicates that this is an update, insert or delete
		2. Query Type 1: Returns the count for the query parsed
		3. Query Type 2: Query to return only a single row
		4. Query Type 3: Will return multiple rows and needs to be handled as such by the calling code.
	*/
	public function executeQuery($sql_query, $query_type, $field_name, $useCallingSite=false)
	{
		//here for backwards compatibility
		$this->query 			= $sql_query;
		$this->queryType 		= $query_type;
		$this->fieldName 		= $field_name;
		$this->useCallingSite 	= $useCallingSite;
		return $this->runQuery();
	}
	
	public function runQuery()
	{
		$this->runSQL();
		switch($this->queryType)
		{
			case(0): $result=$this->updateQuery($this->query);	break;
			case(1): $result=$this->qryResult->num_rows;		break;
			case(2): $result=$this->getValue($this->query);		break;
			case(3): $result=$this->getRows($this->query);		break;
		}
		return $result;	
	} /* function runQuery($sql_query, $query_type) */
	
	public function getCount($sql)
	{
		$this->prepQuery($sql);
		$result=$this->qryResult->num_rows;
		//$this->qryResult->close();
		return $result;	
	}
	
	public function getValue($sql) //same as executeQuery type 2; return a single field (single row)
	{
		$this->prepQuery($sql);
		$row=$this->qryResult->fetch_array(MYSQLI_NUM);
		$result = empty($row[0])?0:$row[0];
		//$this->qryResult->close();
		return $result;	
	}
	
	public function getRow($sql) //intended for a single row only
	{
		$this->prepQuery($sql);
		$row=$this->qryResult->fetch_array(MYSQLI_ASSOC); // or $this->qryResult->fetch_assoc();
		//$this->qryResult->close();
		return $row;
	}
	
	public function getRows($sql) //returns array of multiple rows
	{
		$this->prepQuery($sql);
		$data=$this->qryResult->fetch_all(MYSQLI_ASSOC);
		return $data;
	}
	
	public function updateQuery($sql)
	{
		$this->prepQuery($sql);
		$result=$this->affectedRows;
		return $result;		
	}
	
	private function findConstraint($constraintName, $array)
	{
		$found=false;
		foreach($array as $ent)
		{
			$found=($ent['constraint_name']===$constraintName);
			if ($found) break;
		}
		return $found;
	}
	
	public function duplicateRow ($table, $idField, $id) 
	{
		// load the original record into an array
		$row = $this->getRow("SELECT * FROM ".$table." WHERE ".$idField."=".$id);
		
		$uniquesSQL = "select constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC where TC.TABLE_NAME = '".$table."' 
						and TC.CONSTRAINT_SCHEMA = '".SABLE_DATABASE_NAME."' and (TC.CONSTRAINT_TYPE = 'UNIQUE' or TC.CONSTRAINT_TYPE = 'PRIMARY KEY')";
		
		$uniques = $this->getRows($uniquesSQL);
		
		/*
		[0]['constraint_name']='PRIMARY';
		[1]['constraint_name']='user_email';
		*/
		
		$fields = '';
		$values = '';
		foreach ($row as $key => $value) 
		{
			if (empty($fields)) 
			{
				if ($key != $idField) 
				{					
					$fields = $key; 
					if($this->findConstraint($key, $uniques))
						$values = "'".mt_rand(1,999)."'";
					else
						$values = "'".$value."'";
				}
			}
			else 
			{
				if ($key != $idField) 
				{
					$fields .= ','.$key;
					if($this->findConstraint($key, $uniques))
						$values .= ",'".mt_rand(1,999)."'";
					else
						$values .= ",'".$value."'";
				}
			}
		}
		// insert the new record and get the new auto_increment id
		$res = $this->updateQuery("INSERT INTO ".$table." (".$fields.") VALUES (".$values.")");
		$id = $this->getValue('select MAX('.$idField.') from '.$table);
		return $id;//$this->affectedRows;
	}
	
	public function exportSQLReport($fileName, $sql, $fieldId, $delim=",")
	{
		$csv_filename = $fileName;
		if (strpos($csv_filename, '.csv') === false) $csv_filename .= '.csv';			
			
		$report = 	fopen(SABLE_WORKING_PATH."media/downloads/reports/".$csv_filename, "w") or 
					die("Unable to create the file on the server file system.");			
		
		$csv_export = '';
		
		// query to get data from database
		$this->prepQuery($sql);

		// create line with field names
		$c = false;
		while ($fieldinfo=$this->qryResult->fetch_field())
		{
			if ($c) $csv_export .= $delim;
			$csv_export.= '"'.$fieldinfo->name.'"';
			$c = true;
		}
		fwrite($report, $csv_export.PHP_EOL);		
		
		// loop through database query and fill export variable	
		//$this->prepQuery("SELECT * FROM ".$table." ".$where);
		$rows = $this->qryResult->fetch_all();
		$idx  = mysqli_num_fields($this->qryResult);

		foreach($rows as $field=>$row)
		{
			$c = false;	
			$csv_export = '';
			for ($i=0; $i<$idx; $i++)
			{
				$tmp = preg_replace( "/\r|\n/", "", $row[$i]);
				if ($c) $csv_export .= $delim;
				$csv_export .= '"'.$tmp.'"';
				$c=true;
			}
			fwrite($report, $csv_export.PHP_EOL);
		}
		fclose($report);
		
		//header("Content-type: text/x-csv");
		//header("Content-Disposition: attachment; filename='".$csv_filename."'");
		//exit($csv_export);
		//$link = '<a href="'.SABLE_DOWNLOADS_PATH.'reports/'.$csv_filename.'">Download Report Here</a>';
		$path = SABLE_DOWNLOADS_PATH.'reports/'.$csv_filename;
		SableSystem::ajaxFillTag($path, $fieldId);
	}
	
	public function exportData($table, $delim=",", $query="")
	{
		$csv_filename = 'sable_export_'.$table.'_'.date('Y-m-d').'.csv';		
		$csv_export = 'sep='.$delim.PHP_EOL; //defines the separator as a comma
		
		// query to get data from database
		//Replace values as per /list/data_list.php --> getToolBar()
		$query = str_replace('__PRC__', '%', $query);
		$query = str_replace('__AMP__', '&', $query);
		$query = str_replace('__PLS__', '+', $query);
		$query = str_replace('__DBQ__', '"', $query);
		$query = str_replace('__SBQ__', '\'', $query);
		$query = str_replace('__RTB__', '(', $query);
		$query = str_replace('__LTB__', ')', $query);
		$query = str_replace('__SPC__', ' ', $query);		
		
		$this->prepQuery($query);

		// create line with field names
		$c = false;
		while ($fieldinfo=$this->qryResult->fetch_field())
		{
			if($c)$csv_export.= $delim;
			$csv_export.= '"'.$fieldinfo->name.'"';
			$c=true;
		}
		$csv_export.= PHP_EOL;		
		
		// loop through database query and fill export variable	
		//$this->prepQuery("SELECT * FROM ".$table." ".$where);
		$rows=$this->qryResult->fetch_all();
		$idx = mysqli_num_fields($this->qryResult);

		foreach($rows as $field=>$row)
		{
			$c = false;	
			for($i=0;$i<$idx;$i++)
			{
				$tmp = preg_replace( "/\r|\n/", "", $row[$i]);
				if ($c) $csv_export.= $delim;
				$csv_export.= '"'.$tmp.'"';
				$c=true;
			}
			$csv_export.= PHP_EOL;
		}
		
		header("Content-type: text/x-csv");
		header("Content-Disposition: attachment; filename=".$csv_filename);
		exit($csv_export);
	}
}
?>