<?php
include_once ("_dirinfo.php");

include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."_ajaxManipulators/_class/class.treeData.php");
include_once (PATH_TO_ROOT."_ajaxManipulators/_class/class.gridData.php");
include_once(PATH_TO_ROOT."config.php");


class capiManagmentManipulator extends TreeDataManipulator
{
	public $tableAddon;
	
	
	function selectAction()
	{
			
		$parent = $_POST['node'];
		$isTable = $_POST['isTable'];
		if($isTable)
		{
			$sql = 'SELECT * FROM '.$this->table.'Addon WHERE id_capiTable = '.$parent.' ';
		}
		else 
		{
			$sql = 'SELECT * FROM '.$this->table.' WHERE id_sup = '.$parent.' ';
		
		}
		 
		if(isset($_POST['filter']) && $_POST['filter'] != '')
		{
			$fil = $_POST['filter'];
			$sql.= "AND id_lab LIKE '%$fil% '";	
		}
		
		$sql .= ' ORDER BY orderBy';
		
		echo "/*$sql*/";
		If (!$res = $this->db->SQLexecute($sql)){return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';	}
		else 
		{
		
			// display each child
			while ($row = $this->db->fetchAssoc($res)) {
				// Response parameters.
				$path = array();
				$path['text']		=html_entity_decode( $row['name'] );
				$path['id']			= $row['id'];
				//because we alter the IDs to get unique IDs for the tree we save the original ID
				$path['origId']    = $row['id'];
				if($row['is_leaf'] && !$isTable )
				{
					$path['isTable'] = true;
					$path['leaf'] = false;
				//	$path['allowDrop'] = false;
					$path['iconCls'] = 'icon_database';
					
				}
				else if($isTable)
				{
					$path['leaf']=true;
					$path['isField']=true;
					$path['id'] .= '_field';
				}
				else 
				{
					$path['leaf'] = false;
				}
				$nodes[] = $path;
			}
			
			//return $nodes;
		}
		return json_encode($nodes);
	}
	
	
	function insertTableAction()
		{
		
			
		$parent = $_POST['parId'];
		$newId = $_POST['nodeId'];
		$text = $_POST['nodeText'];
		$type = $_POST['nodeType'];
		$closed = $_POST['closed'];
		//$orderTree = $_POST['tPos'];
		//$orderGrid = $_POST['qPos'];
		$rs = $this->db->SQLexecute("SELECT id FROM $this->table WHERE name = '$text' ");
		if($this->db->fetchAssoc($rs) )
		{
			return "{success:false,error:'Name already exists!'}";
		}
		
		if($newId == 0)
		{
			$orderTree = 1;
		}
		else 
		{
				
				$getNewSql = "SELECT orderBy,id_sup AS par FROM $this->table WHERE id = $newId";
			
				If (!$rs = $this->db->SQLexecute($getNewSql)) {	return '{success:false,error:"'.$this->db->GetError().'" ,STATEMENT: "'.$getNewSql.'"}';}	
				if( !$row = $this->db->fetchAssoc($rs)){return '{success:false,error:"'.$this->db->GetError().'" ,STATEMENT: "'.$getNewSql.'"}';}
				$orderTree = $row['orderBy'];
				//if the target is a leaf we put the new item after it
				if($_REQUEST['isTargetF'])
				{
					$orderTree++;
				}
				//if folder is closed we put the item after the folder
				elseif ($closed == 'true')
				{
					$orderTree++;
				}
				//other wise we put the item on the first place in the folder
				else 
					$orderTree = 1;
		
		}

		$create_telbase = "CREATE TABLE `capi___$text` (
			  `id` int(7) NOT NULL AUTO_INCREMENT,
			  `id_municipality` int(3) NOT NULL DEFAULT '0',
			  `municipality` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `id_settlement` int(4) NOT NULL DEFAULT '0',
			  `settlement` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `id_street` int(4) NOT NULL DEFAULT '0',
			  `street` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `address` int(4) NOT NULL DEFAULT '0',
			  `addon_address` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `zip` int(4) NOT NULL DEFAULT '0',
			  `town` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `x_coordinate` int(9) NOT NULL DEFAULT '0',
			  `y_coordinate` int(9) NOT NULL DEFAULT '0',
			  `id_region` int(3) NOT NULL DEFAULT '0',
			  `population` int(11) NOT NULL DEFAULT '0',
			  `type_settlement` int(1) NOT NULL DEFAULT '0',
			  `id_import` int(11) default '0',  
			  `time_import` int(11) NOT NULL DEFAULT '0',
			  `proj_reserved` int(5) NOT NULL DEFAULT '0',
			  `tdisable` tinyint(1) NOT NULL DEFAULT '0',
			  PRIMARY KEY (`id`),
			  KEY (`id_municipality`),
			  KEY (`id_settlement`),
			  KEY (`id_street`),
			  KEY (`address`),
			  KEY (`zip`),
			  KEY (`x_coordinate`),
			  KEY (`y_coordinate`),
			  KEY (`id_region`),
			  KEY (`population`),
			  KEY (`type_settlement`),
			  KEY (`id_import`),
			  KEY (`proj_reserved`),
			  KEY (`tdisable`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";

		
		$sql[] = $create_telbase;	
		//we increase order in tree (!!!NEW!!!) for this parent only!!!!
		$sql[] = "UPDATE $this->table SET orderBy = orderBy+1 WHERE orderBy >= $orderTree AND id_sup = $parent";

		$sql[] = "INSERT INTO $this->table(id_sup,orderBy,name,is_leaf) VALUES( $parent ,$orderTree,'$text',true ) ";

		if (!$this->db->SQLbegin())	{return '{success:false,error:"Transakcije niso omogocone"}'; }
		foreach ($sql as $sqlStatemens) {
			If (!$this->db->SQLexecute($sqlStatemens)) {
				
					$this->db->SQLrollback();
					return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';
				}	
		}
			$this->db->SQLcommit();
		
			$id = $this->db->GetLastInsertId();
			return '{success:true,insertId:'.$id .'}';	
		}
		
	function insertFieldAction()
		{
				
		$parent = $_POST['parId'];
		$newId = $_POST['nodeId'];
		$text = $_POST['nodeText'];
		$type = $_POST['dataType'];
		$closed = $_POST['closed'];
		
		$tableAddon = $this->table.'Addon';
		$tablePrefix = 'capi___';
		$fieldPrefix = 'capi_';
		
		
		$tableTel = $tablePrefix.$_POST['parText'];
		
		//$orderTree = $_POST['tPos'];
		//$orderGrid = $_POST['qPos'];
		$rs = $this->db->SQLexecute("SELECT id FROM $this->tableAddon WHERE name = '$text' AND id_capiTable = $parent");
		if($this->db->fetchAssoc($rs) )
		{
			return "{success:false,error:'Name already exists!'}";
		}
		
		if($newId == 0)
		{
			$orderTree = 1;
		}
		else 
		{
				if($_REQUEST['isTargetF'])
					{
					$getNewSql = "SELECT orderBy,name FROM $this->tableAddon WHERE id = $newId";
				
					If (!$rs = $this->db->SQLexecute($getNewSql)) {	return '{success:false,error:"'.$this->db->GetError().'" ,STATEMENT: "'.$getNewSql.'"}';}	
					if( !$row = $this->db->fetchAssoc($rs)){return '{success:false,error:"'.$this->db->GetError().'" ,STATEMENT: "'.$getNewSql.'"}';}
					$orderTree = $row['orderBy'];
					$orderTree++;
					$nodeBeforeName = " AFTER CAPI_".$row['name'];
				}
				//other wise we put the item on the first place in the folder
				else 
				{
					$orderTree = 1;
					$nodeBeforeName = " AFTER CAPI_zip";

					
				}
		
		}
		
		$dataType = getDataType($type);
		
		$sql[] = "ALTER TABLE  `$tableTel` ADD  `$fieldPrefix$text` $dataType NOT NULL";
		
		//we update tables for projects that have this tables id_capiname
 		$sqlTmp = "SELECT id,proj_name FROM _Projects WHERE id_capiTable = $parent";
		$res = $this->db->SQLexecute($sqlTmp);
		while($row = $this->db->fetchRow($res))
		{
			$tblStructure = $row[1].'_structure';
			if($this->db->TableExists($tblStructure))
			{
				$sql[] = "ALTER TABLE $tblStructure ADD CAPI_$text $dataType $nodeBeforeName";
			}
		}
		
		
		//we increase order in tree (!!!NEW!!!) for this parent only!!!!
		$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy+1 WHERE orderBy >= $orderTree AND id_capiTable = $parent";

		$sql[] = "INSERT INTO $this->tableAddon(id_capiTable,orderBy,name,type,is_leaf) VALUES( $parent ,$orderTree,'$text',$type,true ) ";

		if (!$this->db->SQLbegin())
		{
			return '{success:false,error:"Transakcije niso omogocone"}';	
		}
		foreach ($sql as $sqlStatemens) {
			If (!$this->db->SQLexecute($sqlStatemens)) {
				
					$this->db->SQLrollback();
					return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';
				}	
		}
		
			$id = $this->db->GetLastInsertId();
			$this->db->SQLcommit();
						
			return '{success:true,insertId:'.$id .'}';	
		}
		
	
	function deleteAction()
	{
			$tablePrefix = 'capi___';
		
			if ($_POST['isTargetF'])
			{
				$sql = "SELECT name FROM $this->table WHERE id = ".$_POST['nodeId'];
				If (!$rs = $this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';	}
				$row = $this->db->fetchArray($rs);
				$tName = $tablePrefix.$row[0];
				If (!$this->db->SQLexecute("DROP TABLE  `$tName`")) {return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';}	
				
				$sqlStatemens = "DELETE FROM $this->tableAddon WHERE id_capiTable = ".$_POST['nodeId'];
				If (!$this->db->SQLexecute($sqlStatemens)) {
					
						return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';
					}
				
			}
			
			return parent::deleteAction();
			
	}
	
	function moveNodeAction()
	{
		if($_POST['isField'])
		{
			//id of the node that was dragged
			$oldId = $_POST['nodeId'];
			//id of the node that we dropped onto
			//there a 2 options: we disabled append (see $point)
			$newId = $_POST['targetId'];
			//where the node was dropped according to the new node (later as the targeted note)
			$point = $_POST['movePoint'];
			
			// we get the orderBy and parent of the dragged node
			$getOldSql = "SELECT orderBy,id_capiTable FROM $this->tableAddon WHERE id = $oldId";
			If (!$rs = $this->db->SQLexecute($getOldSql)) {	return '{success:false,error:"'.$this->db->GetError().'" ,STATEMENT: "'.$getOldSql.'"}';}	
			$row = $this->db->fetchAssoc($rs);
			$oldPos = $row['orderBy'];
			$oldParent = $row['id_capiTable'];
			//if we dropped into the table, the pos is 1
			if($newId == 0)
			{
				$point = 'above'; //so we don't mix up ordering
				$newPos = 1;
				$parent = 0;
			}
			else 
			{	
			//we find the new positions according to the $point 
				if($point=='append')
				{
					//append means that we have moved the question into a folder (the target node is a folder)
					//we must find it's first child and replace it's position, so that the new node will take first place
					$getNewSql = "SELECT orderBy,id AS par FROM $this->tableAddon WHERE id = $newId";	
					
				}
				//we get the position of the target node
				else
				{
					$getNewSql = "SELECT orderBy,id_capiTable AS par FROM $this->tableAddon WHERE id = $newId";
				}
				//NOTE! later we adjust the position acording to the point of drop!!!
					
				If (!$rs = $this->db->SQLexecute($getNewSql)) {	return '{success:false,error:"'.$this->db->GetError().'" ,STATEMENT: "'.$getNewSql.'"}';}	
				if( !$row = $this->db->fetchAssoc($rs))	{return '{success:false,error:"'.$this->db->GetError().'" ,STATEMENT: "'.$getNewSql.'"}';}
				$newPos = $row['orderBy'];
				$parent = $row['par'];
			}
			//if we moved a node down, we must lower ALL indexes
			if($point == 'below')
			{	//we increase the position so that it's after the targeted note
				$newPos++;
			}
			elseif ($point == 'append') {
				//new rule: append is first in folder so position is one :)
				$newPos = 1;
			}
			
			
			$sql[] = "UPDATE $this->tableAddon SET orderBy = $newPos , id_capiTable = $parent WHERE id = $oldId";
				if($parent == $oldParent)	//if parent is the same 
				{
					//if we moved the node down, we decrease the position of all nodes inbetween
					//now we have 2 nodes with this new position and we lower the other one (id <> gridId)
					if ($oldPos < $newPos  )
					{
						$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy -1 WHERE orderBy > $oldPos AND orderBy < $newPos AND id_capiTable = $parent ";
						$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy -1 WHERE orderBy = $newPos AND id = $oldId AND id_capiTable = $parent";
					}
					if ($oldPos > $newPos  )
					{//vice verse if we moved the node up
						$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy +1 WHERE orderBy < $oldPos AND orderBy > $newPos AND id_capiTable = $parent";
						$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy +1 WHERE orderBy = $newPos AND id <> $oldId AND id_capiTable = $parent";
					}
				}
				//otherwise...we lower all indexes from old parent and increase all in new parent (similar to insert and delete!!!)
				else 
				{
					$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy+1 WHERE orderBy > $newPos AND id_capiTable = $parent";
					$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy +1 WHERE orderBy = $newPos AND id <> $oldId AND id_capiTable = $parent";
					
					$sql[] = "UPDATE $this->tableAddon SET orderBy = orderBy-1 WHERE orderBy > $oldPos AND id_capiTable = $oldParent";
				}
			
			$this->db->SQLbegin();
			
			foreach ($sql as $sqlStatemens) {
				If (!$this->db->SQLexecute($sqlStatemens)) {
					
						$this->db->SQLrollback();
						Echo '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';
						return;
					}	
			}
			
			$this->db->SQLcommit();
			return "{success:true}";
		}
		else 
			return parent::moveNodeAction();
		
		
	}
	
		
	
}

class capiFieldEditManipulator extends GridDataManipulator
{
	
	function updateAction()
	{
		$tablePrefix = '_Capi';
		$fieldPrefix = 'capi_';
		$telPrefix = 'capi___';
		
		if( $_POST['field'] == 'name')
		{
			$field = $_POST['value'];
			$type = getDataType( $_POST['dataType']  );
		}
		else 
		{
			$field = $_POST['fieldName'];
			$type = getDataType( $_POST['value']  );
		}
		
		if($_POST['field'] == 'use_as_labels')
        {
            //20150614 Tanis
        }
		
		$oldField = $_POST['oldName'];
		
		If (!$rs = $this->db->SQLexecute("SELECT name FROM  $tablePrefix"."Tables WHERE id = ".$_POST['tableId'] ))	{return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';}	
		$row = $this->db->fetchArray($rs);
		$tableTel = $telPrefix. $row[0];
		
		$dType = $_POST['displayType'];
		if($_POST['field'] == 'name' || $_POST['field'] == 'type')
		If (!$this->db->SQLexecute("ALTER TABLE  `$tableTel` CHANGE `$fieldPrefix$oldField` `$fieldPrefix$field` $type NOT NULL"))
					{
					
						return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';
					}	
		
		$sql = 'UPDATE '.$_POST['RMtable']." SET ".$_REQUEST['field']." = '".$_REQUEST['value']."' WHERE id = ".$_REQUEST['id'];
		If (!$rs = $this->db->SQLexecute($sql)) {return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';	}
		
		return '{success:true,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
		
		
	}
	
	function deleteAction() {
		$tablePrefix = '_Capi';
		$fieldPrefix = 'capi_';
		$telPrefix = 'capi___';
		
		//we get info about the row being deleted! it's position the name of the field and the name of the column
		$sql = "SELECT a.id_capiTable,a.orderBy,a.name,b.name FROM $this->table a INNER JOIN $tablePrefix"."Tables b ON a.id_capiTable = b.id  WHERE a.id IN (".$_POST['ids'].')';
		If (!$rs = $this->db->SQLexecute($sql))	{return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';	}
		$row = $this->db->fetchArray($rs);
		$telId = $row[0];
		$pos = $row[1];
		$fldName = $fieldPrefix.$row[2];
		$tblName = $telPrefix.$row[3];
		//we remove the column from the appropriate table
		If (!$this->db->SQLexecute("ALTER TABLE  `$tblName` DROP  ".$fldName)) {return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sqlStatemens.'"}';	}	
		
		//we update tables for projects that have this tables
		$sqlTmp = "SELECT id,proj_name FROM _Projects WHERE id_capiTable = $telId";
		$res = $this->db->SQLexecute($sqlTmp);
		while($rowStruc = $this->db->fetchRow($res))
		{
			$tblStructure = $rowStruc[1].'_structure';
			if($this->db->TableExists($tblStructure))
			{
				$sql = "ALTER TABLE $tblStructure DROP CAPI_".$row[2];
				$this->db->SQLexecute($sql);
				
			}				 
		}
		
		$sql = "UPDATE $this->table SET orderBy = orderBy -1 WHERE id_capiTAble = $telId  AND orderBy > $pos";
		If (!$this->db->SQLexecute($sql)){return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
		
		return parent::deleteAction();
	}
	
	
	function resetDatabaseAction() {
		return "{success:true}";
		
		// we will use this if we will collect some things inside this table, like status of address or something 
		$flds = "";
		$sql = "UPDATE ".$_POST['telTable']." SET $flds WHERE proj_reserved=''";
		$this->db->SQLexecute($sql);
		return "{success:true}";
	}
	
	
	function exampleAction() {
		header( "Content-Type: text/csv" );
   		header( "Content-Disposition: attachment; filename=\"example.csv\";" );
   		echo "tdisable;id_municipality;municipality;id_settlement;settlement;id_street;street;address;addon_address;zip;town;name;x_coordinate;y_coordinate;id_region;population;type_settlement\n";
	}
	
	
}

class labelDefineManipulator extends GridDataManipulator
{
	public function fetchSelect()
	{
		header('Cache-Control: no-cache, must-revalidate');
		header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
		header('Content-type: application/json');
		
		$this->arr = array();
		
		$telTable = $_POST['telTable'];
		$idTel = $_POST['id_telname'];
		$this->db->LogWrite($telTable." - ".$idTel,false,"variables inside labelDefineManipulator");
		
		$sql = "SELECT * FROM $this->table WHERE id_capiTable = $idTel";
        //print $sql;
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchAssoc($res)){
			$labels[$row['id_var']][$row['is_addon']][$row['reg_code']] = $row['reg_label'];
		}
		
		//fetch region group by!
		$resGroups = $this->db->SQLexecute("SELECT id_region as value FROM $telTable GROUP BY id_region");
		while ($row = $this->db->fetchAssoc($resGroups)){
			$this->arr[] = array('id_capiTable'=>$idTel,'id_var'=>14,'is_addon'=>0,'reg_code'=>$row['value'],'varLabel'=>'id_region','reg_label' => $labels[14][0][$row['value']]  );
		}
		
		//fetch zip group by!
		$resGroups = $this->db->SQLexecute("SELECT zip as value FROM $telTable GROUP BY zip");
		while ($row = $this->db->fetchAssoc($resGroups)){
			$this->arr[] = array('id_capiTable'=>$idTel,'id_var'=>9,'is_addon'=>0,'reg_code'=>$row['value'],'varLabel'=>'zip','reg_label' => $labels[9][0][$row['value']]  );
		}
		
		
		//we need all other fields that are int to show them here too
		$sql = "SELECT id,name,id_capiTable FROM _CapiTablesAddon WHERE id_capiTable=$idTel AND (type=4 OR type=5) AND use_as_labels=1 ORDER BY orderBy";
		$res = $this->db->SQLexecute($sql);
		while ($rowRR = $this->db->fetchAssoc($res) )
		{
			     //20150614 Tanis spremenil iz tel_ v capi_
				$col = 'capi_'.$rowRR['name'];
				
	//			print "SELECT $col as value FROM $telTable GROUP BY $col";

				$resGroups = $this->db->SQLexecute("SELECT $col as value FROM $telTable GROUP BY $col");
				while ($row = $this->db->fetchAssoc($resGroups)){
				    //Tanis spremenil iz 'id_telname'=>$idTel v 'id_capiTable'=>$rowRR['id_capiTable']
					$this->arr[] = array('id_capiTable'=>$rowRR['id_capiTable'],'id_var'=>$rowRR['id'],'is_addon'=>1,'reg_code'=>$row['value'],'varLabel'=>$col,'reg_label' => $labels[$rowRR['id']][1][$row['value']]  );
					}
		}
	   
		
	}
	public function updateAction()
	{
	    //20150614 Tanis spremenil id_telname v id_capiTable
		$idteName = $_POST['id_capiTable'];
		$idVar = $_POST['id_var'];
		$isAddon = $_POST['is_addon'];
		$reg_code = $_POST['reg_code'];
		$reg_label = $_POST['reg_label'];
		
		$sql = "INSERT INTO $this->table VALUES($idteName,$isAddon,$idVar,$reg_code,'$reg_label') ON DUPLICATE KEY UPDATE reg_label = '$reg_label'";
		If (!$this->db->SQLexecute($sql)) {return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';	}
		
		return '{success:true,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
	}
	
}


$db->ChangeDatabase($_REQUEST['database']);

$tableName = $_POST['RMtable'];
$tableAddon = $tableName.'Addon';

if($_POST['RMcomp']=='tree')
{
	$manipulator = new capiManagmentManipulator($db,$tableName);
	$manipulator->tableAddon = $tableAddon;
}
else 
{
	if($_POST['RMcomp']=='labelGrid')
	{
		$manipulator = new labelDefineManipulator($db,$tableName);
	}
	else
	{
		$manipulator = new capiFieldEditManipulator($db,$tableName);
		$manipulator->tableAddon = $tableAddon;
	}
}
$comm = $_REQUEST['action'].'Action';
if(method_exists($manipulator,$comm))
	echo $manipulator->$comm();
else 
{
	
	echo '{success:false,error:"Unknown action!"}';	
	
}


?>
