<?php
//include("../init.php");


class GridDataManipulator{
	/*
	 * Load tree node.
	 */	
	
	protected $db;
	protected $table;
	protected $where;
	protected $addOnTable;
	protected $addOnTable_where;
	
		
	function __construct($db,$table) 
	{
		$this->db = $db;
		$this->table = $table;
	}
	function addWhere($condition)
	{
		$this->where[] = $condition;
	}
	/**
	 * Creates the select statement from all the parameters without limit AND order!!!
	 *
	 */
	function createSelectSQL()
	{
		$this->db->LogWrite("kliče selectsql",false,"kliče ");
		if(isset($_POST['SQL']))
		{
			
			$sql = stripslashes( $_POST['SQL']);	
		}
		else if($_POST['autoFill']) //in case a combobox want's to automaticly get some shit...yeah...
		{
			$af = $_POST['autoFill'];
			$q = $_POST['query'];
			$sql = "SELECT `$af` FROM `$this->table` GROUP BY `$af` HAVING $af LIKE '$q%' ORDER BY `$af`";
		}
		else
		{
			$sql = "SELECT * FROM $this->table";
		}
		//custom where statement send by parameters
		$postWhere = $_POST['SQLwhere'];
		if($postWhere)
		{
			$this->where[] = $postWhere;
		}
		
		//filtering
		if(isset($_POST['filter']) || isset($_POST['srcfields']))
		{
			$this->where[] = $this->filtering($_POST['filter']);
			//$sql .= $this->filtering($_POST['filter']);
		}

		//remote sorting
		if(isset($_POST['sort']) || isset($_POST['groupBy']) )
		{
		//	$sql .= ' ORDER BY ';
		//	$this->sort = array();

			if( isset($_POST['groupBy']) && $_POST['groupBy']!=$_POST['sort'] )
			{
				$this->sort[] = $_POST['groupBy'].' '.$_POST['groupDir'];
			}
			if(isset($_POST['sort']) )
			{
				$this->sort[] = $_POST['sort'].' '.$_POST['dir'];
			}
				
		//	$sql .= implode(',',$sort);
			
		}
		if (is_array($this->where))
		{
			if(!$this->whereKeyword) $this->whereKeyword = "WHERE";
			$sql .= " $this->whereKeyword ".implode(' AND ',$this->where);		
		}
		return $sql;
	}
	/**
	 * Fetches all the data and stores it in an array
	 *Its now seperated from selectAction so that you may call it independetly
	 * 
	 * @return Array with data
	 */
	function fetchSelect()
	{
		$this->db->LogWrite("kliče fetch select",false,"kliče fetch select");
		//made a seperate createSelect function, to use in cases like export, copy all filtered records etc!
		$this->sql = $this->createSelectSQL();
		$this->db->LogWrite($this->sql,false,"Sledim grajenju SQLa (after createSelectSQL()):");
		
		
		//if paging is activated
		if (isset($_POST['start']) )
		{
			$countSql = $this->sql;
		
			if($_POST['limit'] < 0 ) $_POST['limit'] = 20;
			
				$limit .= ' LIMIT '.$_POST['start'].", ".$_POST['limit'];
			
			
			if(!$this->rsCount = $this->db->SQLexecute($countSql))
			{
				return false;
			}
			$this->results = $this->db->NumRows($this->rsCount);
		}
		
		
		if (is_array($this->sort) )
		{
			$this->sql .= ' ORDER BY '.implode(',',$this->sort);
		}
		$this->sql .= $limit;
		
		$this->db->LogWrite($this->sql,false,"Sledim grajenju SQLa (pred SQLexecute()):");
		$this->arr = array();
		If (!$this->rs = $this->db->SQLexecute($this->sql)) {	return false;}
				
				while($obj = $this->db->fetchAssoc($this->rs)){
					$this->arr[] = $obj;
				}
				return $this->arr;
	}
 	function  selectAction()
 	{
 		$this->db->LogWrite(json_encode($this->arr),true,"polje izpisano:");
 		$echo = '{success:true';
 		if(!isset($this->arr) ) 
 		{
 			if($this->fetchSelect() === false) return $this->db->JsonError();
 		}
 		
 		if($_POST['addOn'])
 		{
 			if($_POST['addOn'] == 'setSelection')
 			{
 			
	 			if (is_array($this->where))
				{
					if(!$this->whereKeyword) $this->whereKeyword = "WHERE";
					$updateWhere .= " $this->whereKeyword ".implode(' AND ',$this->where);		
				}
				$val = $_POST['setValue'];
				if(!is_numeric($val) )
					$val = "'$val'";
				
				$setUpdateSql = "UPDATE $this->table $this->addOnTable SET ".$_POST['setField']." = $val ".$updateWhere." ".$this->addOnTable_where;
				
				unset($this->addOnTable);
				unset($this->addOnTable_where);
				
				//$this->db->LogWrite($setUpdateSql,false,"class.gridData -> selectAction");
				try{
					echo "/*poskusam najprej tale update: $setUpdateSql*/";
					$this->db->SQLexecute($setUpdateSql);
				}
				catch (Exception $e)
				{//ce ne uspe gremo oni stari nacin!
					foreach ($this->arr as $bla) {
 						$selIds[] = $bla['id'];
	 				}
	 				$_POST['ids'] = implode(',',$selIds);
	 				$this->setSelectionAction();
				}
				$this->fetchSelect();
 			}
 		}
 		$this->db->LogWrite($this->results,false,"pride sem");
		if (isset($_POST['start']) )
		{
			$echo.= ', results:'.$this->results;
		}
		
			$echo.= ', STATEMENT: "'.$this->sql.'",rows: '.json_encode($this->arr);
			$echo.='}';
			$this->db->LogWrite($echo,true,"izpis podatkov");
			return $echo;	
 	}
	function deleteAction()
	{
		if(!$idField = $_POST['idField'])
			$idField = 'id';
		
		
		if($_POST['id'])
		{
		
		$sql = 'DELETE FROM '.$_POST['RMtable'].' WHERE '.$idField.' = '.$_POST['id'];
		}
		else if($_POST['ids'])
		{
			$sql = 'DELETE FROM '.$_POST['RMtable'].' WHERE '.$idField.' IN  ('.$_POST['ids'].')';
		}
		else 
		{
			return '{success:false,error:"No ID found!"}';
		}
		If (!$this->db->SQLexecute($sql)) {
			
				return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
			
			}else{
				
			
			
			return '{success:true }';
		}
	}
	function updateAction()
	{
		$_POST['value'] = addslashes($_POST['value']);
		$sql = 'UPDATE '.$_POST['RMtable']." SET ".$_POST['field']." = '".$_POST['value']."' WHERE id = ".$_POST['id'];
			If (!$rs = $this->db->SQLexecute($sql)) {
			
				Echo '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
			
			}else{
				
			
			
			$r = '{success:true';
			if($_POST['newValue'] == true)
				$r .= ',newValue:"'.$_POST['value'].'"';
			return $r. ',error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
		}
		
	}
	function setSelectionAction()
	{
		$val = $_POST['setValue'];
		if(!is_numeric($val) )
			$val = "'$val'";
		if($ids = $_POST['ids'])
		{
			$sql = "UPDATE $this->table SET ".$_POST['setField']." = $val WHERE id IN ($ids)";
			$this->db->SQLexecute($sql);
		}
		echo "/*updating all selected rows: $sql*/";
		return "{success:true}";
		
	}
	function insertAction()
	{
		$sql = 'INSERT INTO '.$_POST['RMtable'];
			$val = '';
			if(isset($_POST['defaultFields']))
			{
				$sql .= '('.$_POST['defaultFields'].')';
				$val = str_replace( '\\','', $_POST['defaultValues']) ;
				
			}
			$sql .= " VALUES($val)";
			
		If (!$rs = $this->db->SQLexecute($sql)) {
				
					return '{"success":false,"error":"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
				
				}else{
					
				
				
				return  '{"success":true,"insert_id":'.$this->db->GetLastInsertId().' }';
			}
	}
	function exportAction()
	{
		$expCol = json_decode(stripslashes($_POST['cols']));
		$expHead = json_decode(stripslashes($_POST['heads']));
		$this->db->SQLexecute('DROP TEMPORARY TABLE IF EXISTS exportTMP');
		//let's add id
		/*if(!in_array('id',$expCol) )
		{
			array_unshift($expCol,'id');
			array_unshift($expHead,'id');
		}*/
		/*if(isset($_POST['SQL']))
		{
			$sql = $_POST['SQL'];	
		}
		else
		{
			$sql = "SELECT * FROM $this->table";
		}
		//filtering
		if(isset($_POST['filter']) || isset($_POST['srcfields']))
		{
			$sql .= $this->filtering($_POST['filter']);
		}*/
		if($_POST['arrayExport'])
		{
			$arr = json_decode($_POST['data']);
		}
		else
		{
			
			$this->db->SQLexecute('SET NAMES UTF8');
			
		/*	$sql = $this->createSelectSQL();
			
			//remote sorting
			if(isset($_POST['sort']) || isset($_POST['groupBy']) )
			{
				$sql .= ' ORDER BY ';
				$this->sort = array();
				if( isset($_POST['groupBy']) && $_POST['groupBy']!=$_POST['sort'] )
					$this->sort[] = $_POST['groupBy'].' '.$_POST['groupDir'];
				if(isset($_POST['sort']) )
					$this->sort[] = $_POST['sort'].' '.$_POST['dir'];
					
				$sql .= implode(',',$this->sort);
				
			}
			
			$sql = 'CREATE  TEMPORARY TABLE exportTMP CHARACTER SET UTF8 COLLATE utf8_general_ci '.$sql;
		
			$this->db->SQLexecute($sql);
			if($this->db->IsError())
			{
				return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
				break;
			}
			$arr = array();
			If (!$rs = $this->db->SQLexecute('select '.implode(',',$expCol). ' from exportTMP')) {
				
					return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
				
			}else{
				
				while($obj = $this->db->fetchAssoc($rs)){
					$arr[] = $obj;
				}
			}
		
				 
	*/
					//Echo $echo;
		/*	$fh = fopen('/exportTest.txt','W');
				fwrite($fh,var_export($arr) );
				fclose($fh);*/
		}
			 unset($_POST['start']);
			 unset($_POST['limit']);
			$this->selectAction();
		
			$arr = $this->arr;
			
			if($_POST['RMexport']=='excel')
			{
				require_once "Spreadsheet/Excel/Writer.php";
				
				//echo mb_detect_encoding($arr[0][0]);
				$props = json_decode($_POST['props'],true);
				
				$xls =new Spreadsheet_Excel_Writer();
				$xls->setVersion(8);
				// Create worksheet
				$cart = $xls->addWorksheet('izvoz');
				$cart->setInputEncoding('UTF-8');
				$titleFormat = $xls->addFormat();
				$titleFormat->setFontFamily('Helvetica');
				$titleFormat->setSize('13');
				$titleFormat->setColor('navy');
				$titleFormat->setBottom(2);
				$titleFormat->setBottomColor('navy');
				$cart->write(0,0,$props['title'],$titleFormat);
				$cart->write(0,1,'',$titleFormat);
				$cart->write(0,2,'',$titleFormat);
				$cart->write(0,3,'',$titleFormat);
				$cart->mergeCells(0,0,0,3);
				$cart->setRow(0,30);
				$cart->setColumn(0,count($expCol)-1,15);
				$hiddenFormat = $xls->addFormat();
				$hiddenFormat->setSize('0');
				$hiddenFormat->setLocked();
				/*
				 * Explanation of this code!
				 * first 9 characters are specific code that identifies this cell!
				 * Next characters are information about the import
				 * 	10-11 - the line where the data starts
				 * 	12-13 - line where header information is stored (if 0 means there is no header information and you should get data from grid)
				 * 	14-15 - column where id is stored - if 00 then there is no id
				 */
				$cart->write(0,4,'00RMIjC00040001',$hiddenFormat);
				$colHeadingFormat = $xls->addFormat();
				$colHeadingFormat->setBold();
				$colHeadingFormat->setFontFamily('Helvetica');
				$colHeadingFormat->setSize('18');
				$colHeadingFormat->setAlign('center');
				$colHeadingFormat->setTextRotation('90');
				$colHeadingFormat->setFgColor('red');
				// An array with the data for the column headings
				for ($i=0;$i<count($expHead);$i++ ) {
					$cart->write(1,$i,$expHead[$i]);
				}
	
				$vrstica = 3;
				for ($i=0;$i<count($arr);$i++ ) {
					$j=0;
					foreach ($arr[$i] as $data)
					{
						//$cart->setInputEncoding( mb_detect_encoding($data) );
						
						$cart->write($i+3,$j,  trim($data) );
						$j++;
					}
				}
				
				$xls->send("export.xls");
				$xls->close();
			}
			elseif ($_POST['RMexport']=='csv')
			{
				header('Content-Type: text/csv');
				header('Expires: 0');
				header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
				header('Pragma: public');
   				header('Content-Disposition: attachment; filename="export.csv";');
   				echo implode(';',$expHead)."\n";
   				foreach ($arr as $row) {
   					$rowDisplay = array();
   					foreach ($expCol as $colName)
   						$rowDisplay[] = $row[$colName];
   					
   					echo implode(';',$rowDisplay)."\n";
   				}
   				
   				
			}
			elseif($_POST['RMexport']=='xml')
			{
				header('Content-Type: text/csv');
				header('Expires: 0');
				header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
				header('Pragma: public');
				header('Content-Disposition: attachment; filename=\"export.xml";');
   				$XMLwr = new XMLWriter();
   				$XMLwr->openMemory();
   				$XMLwr->startDocument('1.0');
   				$XMLwr->startElement('data');
				foreach ($arr as $row) 
				{
					$XMLwr->startElement('row');
   					foreach ($row as $di => $value) {
   						$XMLwr->writeElement($di,$value);
   					}
   					$XMLwr->endElement();
   					
   				}
   				$XMLwr->endElement();
   				$XMLwr->endDocument();
   				echo $XMLwr->flush();
			}
					
		
		
	}
	
	public function pasteImportAction()
	{
		
		$data = explode("\n",$_POST['pasteData']);
		$cols = $_POST['cols'];
		$l = count($data);
		$firstRow = explode("\t",$data[0]);
		//let's check for special RM code ;)
		$code = $firstRow[4];
		if(substr($code,0,9) == '00RMIjC00')
		{
			$rowData = (int)substr($code,9,2) - 1;
			$rowHeaders = (int)substr($code,11,2);
			$idCol = (int)substr($code,13,2);
			//echo "code found!!! :)";
			if($rowHeaders)
				$cols = $data[$rowHeaders - 1];
			else  //we add the id that we added in exprot (maybe some code will be requered for this ? )
				$cols = 'id,'.$cols;
			
		}
		else //there is no code 
		{
			//we asume that only data is pastet!
			$rowData = 0;
		}
		$numColsData = count( $firstRow );
		for($i=0;$i<$rowData;$i++)
		{
			unset($data[$i]);
		}
		for($i=$rowData;$i<$l;$i++)
		{
			if($data[$i])
				$data[$i] = '("'.str_replace("\t",'","', addslashes($data[$i]) ).'")';
			else unset($data[$i]);
		}
		$cols = explode(',',$cols);
		$numCols = count($cols);
		//if there is less data then sent columns, we fill only the first n columns
		for ($i=$numColsData;$i<$numCols;$i++ )
		{
			unset($cols[$i]);
		}
		$cols = implode(',',$cols);
		
		try {
			if(!$idCol)
			{	//if there is no ID row...we delete all data and insert this as new!
				$this->db->SQLexecute("TRUNCATE TABLE $this->table");
				$sql = "INSERT INTO $this->table ( $cols ) VALUES ".implode(',',$data);
					If (!$this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'"}'; exit;}
			}
			else 
			{ //else we update fields that have id and insert fields that don't...this will be fun with 200k records :D
				foreach (explode(',',$cols) as $col) {
					if($col != 'id')
						$dupId[] = $col. " = VALUES($col)";
				}
				
				$sql = "INSERT INTO $this->table ( $cols ) VALUES ".implode(',',$data).
				" ON DUPLICATE KEY UPDATE ".implode(',',$dupId);
					If (!$this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'"}'; exit;}
			}
		}
		
		catch (Exception $e)
		{
			
			
			return "{success:false, error: 'Import error! Please check that number of columns match!',msg:".json_encode($e->getMessage())."}";
		}
		return "{success: true}";
	}
	public function importFileAction()
	{
		$fh = fopen($_FILES['csvfilepath']['tmp_name'], 'r');
		
		$struktura = trim(fgets($fh));
		$struktura = str_replace(array("'",'"','.',' '),'',$struktura);
		
		
		fclose($fh);
		//we read the line and column separator
		$lineSep = $_POST['lineSep'];
		$columnSep = stripslashes($_POST['columnSep']);
		
		//we detect the encoding of the file
		$fileEncoding = strtolower( mb_detect_encoding($struktura) );
		$sql = "SHOW CHARACTER SET WHERE Charset LIKE '$fileEncoding'";
		if(!$res = $this->db->SQLexecute($sql) ){echo '{"success":false,"error":"Error with charsets!"}'; exit; }
		if($row = $this->db->fetchAssoc($res))
		{//if the encoding matches one of the databases legal encoding we use it in the load querry
			$mysqlEncoding = $row['Charset'];
		}
		else
		{//otherwise we try to convert it
		
		//array to convert mb_detect_encoding values to mysql legal charset
			$encodTable['utf-8'] = 'UTF8';
			$encodTable['ascii'] = 'ascii';
			
			$mysqlEncoding = $encodTable[ $fileEncoding  ];
		}
		
		if(!$mysqlEncoding)
		{
			return '{"success":false,"error":"Unknown character set: '.$encLine.'!"}';
		}
		try {
		$this->db->SQLexecute("TRUNCATE TABLE $this->table");
		$sql = "LOAD DATA LOCAL INFILE '".$_FILES['csvfilepath']['tmp_name']."' 
					INTO TABLE  ".DB_WARPIT_USERSPOOL.".".$this->table." CHARACTER SET '$mysqlEncoding' FIELDS TERMINATED BY '$columnSep' ENCLOSED BY '\"' LINES TERMINATED BY '$lineSep' IGNORE 1 LINES ($struktura) ";
		If (!$this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'"}'; exit;}
		}
		catch (Exception $e)
		{
			return "{success:false, error: 'Import error! Please check that number of columns match!'}";
		}
		return "{success: true}";
	}
	
	
	private function filtering($filter)
{
	 $srcfields = json_decode( stripslashes($_POST['srcfields'])  );
	$where = " 0=0 "; 
	if (is_array($filter)) 
	{ 
		for ($i=0;$i<count($filter);$i++){
			$originalFilter = $filter[$i]['field'];
			if($filter[$i]['data']['RMtable']) $filter[$i]['field'] =  $filter[$i]['data']['RMtable'].'.'. $filter[$i]['field'];
			if($filter[$i]['data']['convertNull']) $filter[$i]['field'] = "IF(".$filter[$i]['field']." IS NULL,0,".$filter[$i]['field'].")";
			
			switch($filter[$i]['data']['type']){ 
				case 'string' : $qs .= " AND ".$filter[$i]['field']." LIKE '%".$filter[$i]['data']['value']."%'"; Break; 
				case 'list' : 
					if (strstr($filter[$i]['data']['value'],',')){ 
						$fi = explode(',',$filter[$i]['data']['value']); 
						for ($q=0;$q<count($fi);$q++){ 
							$fi[$q] = "'".$fi[$q]."'"; 
						} 
						$filter[$i]['data']['value'] = implode(',',$fi); 
						$qs .= " AND ".$filter[$i]['field']." IN (".$filter[$i]['data']['value'].")"; 
					}else{ 
						$qs .= " AND ".$filter[$i]['field']." = '".$filter[$i]['data']['value']."'"; 
					} 
				Break; 
				case 'boolean' : $qs .= " AND ".$filter[$i]['field']." = ".($filter[$i]['data']['value']); Break; 
				case 'numeric' : 
					//IF (user_id IS NULL, 0, id_user) = 0
					switch ($filter[$i]['data']['comparison']) { 
						case 'ne' : $qs .= " AND ".$filter[$i]['field']." != ".$filter[$i]['data']['value']; Break; 
						case 'eq' : $qs .= " AND ".$filter[$i]['field']." = ".$filter[$i]['data']['value']; Break; 
						case 'lt' : $qs .= " AND ".$filter[$i]['field']." < ".$filter[$i]['data']['value']; Break; 
						case 'gt' : $qs .= " AND ".$filter[$i]['field']." > ".$filter[$i]['data']['value']; Break; 
					} 
				Break; 
				case 'date' : 
					switch ($filter[$i]['data']['comparison']) { 
						case 'ne' : $qs .= " AND ".$filter[$i]['field']." != '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break; 
						case 'eq' : $qs .= " AND ".$filter[$i]['field']." LIKE '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."%'"; Break; 
						case 'lt' : $qs .= " AND ".$filter[$i]['field']." < '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break; 
						case 'gt' : $qs .= " AND ".$filter[$i]['field']." > '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; Break; 
					} 
				Break; 
			} 
			
			$filter[$i]['field'] = $originalFilter;
			
		} 
		$where .= $qs; 
	}
	if(is_array($srcfields))
	{
		$qr = $_POST['srcquery'];
		if ($qr) {
			foreach ($srcfields as $fld) {
				
				$srWh[] = $fld." LIKE '%$qr%'";
			}
			$where .= ' AND ('.implode(' OR ',$srWh).') ';
		}	 
	}
	return $where;
	
}
 
}


?>