<?php

class quotes
{
	private $table_name;		// project name (table name in the future)
	private $joined_att;		// attributes ready for a database statement
	private $att_to_select;		// attributes that need to be selected and inserted into the index table
	private $temp_table_att="";
	private $proj_name;
	
	public $tel_quest_names = array();
	public $quota_layers;
	public $project_id;
	public $element_values_for_recode;
	public $recoded_values="";
	private $vprasanja;
	/**
	 * Database
	 *
	 * @var db
	 */
	private $db;
	private $errors; // array of errors
	
	/**
	 * The constructor creates the index table and extracts all INT fields from the table with telephone numbers
	 *
	 * @param INT $id - Id of the project
	 * @param Object $datab - An instance of an database object
	 * @return bool
	 */
	public function __construct($id, $datab)
	{
		$this->project_id=$id;
		$this->db=$datab;
		if(!$this->construct_db_fields($id))
			return false;
	
	}
	
	/**
	 * The method constructs the columns that are of type INT from the telephone table and gets the name of that table from the table telIndex tables
	 *
	 * @param INT $id - id of the project
	 * @return bool
	 */
	public function construct_db_fields($id)
	{
		// Get the name of the telephone table
		$sql="SELECT _Projects.proj_name, _TelephoneTables.name,id_telname,language FROM _Projects LEFT JOIN _TelephoneTables ON _Projects.id_telname=_TelephoneTables.id WHERE _Projects.id=" . $id;
		
		if(!$result=$this->db->SQLexecute($sql))
			echo "NAPAKA 01:" . $this->db->GetError(); //return false
		
		if(!$row=$this->db->fetchAssoc($result))
			return false;
		else 
		{
			$this->table_name=$row["name"];
			$this->proj_name=$row["proj_name"];
			$this->id_telname=$row["id_telname"];
			$this->id_language = $row['language'];
		}
	
		$arr=array();
		
		if($this->id_telname && $this->db->TableExists($this->proj_name.'___telIndex'))//check if __telIndex table exists
		{//WARNING: if table does not exist it means there were no samples made yet!!! maybe throw an error or warning!
			// Take all fields of type INT and make a list of them
			$sql="SELECT _TelephoneTablesAddon.id, _TelephoneTablesAddon.name FROM _Projects, _TelephoneTablesAddon WHERE _Projects.id=" . $id;
			$sql.=" AND _Projects.id_telname=_TelephoneTablesAddon.id_telbaza AND _TelephoneTablesAddon.type IN (4,5,7)";
			
			//echo "SQL: " . $sql . "<br/><br/>";
			if(!$result=$this->db->SQLexecute($sql))
				echo "NAPAKA 02:" . $this->db->GetError(); //return false
			
		
			// $row=$this->db->fetchAssoc($result)
			
			// ZA JSON OBLIKO
			/*while($row=$this->db->fetchObj($result))
			{
				$row->source=1;
				$result2 = $this->db->SQLexecute("SHOW COLUMNS FROM rm_cati_base." . $this->table_name . "___telIndex LIKE '" . $row->name . "'");
	    		if($this->db->NumRows($result2)>0)
				{
					$arr[]=$row;
					
				}
			}*/
			//we manually add oms, region and zip!
			$arr[] = array('name'=>'oms','id_s'=>'1_0');
			$arr[] = array('name'=>'region','id_s'=>'3_0');
			$arr[] = array('name'=>'zip','id_s'=>'5_0');
			
			while($row=$this->db->fetchAssoc($result))
			{
			/*	$sql="SHOW COLUMNS FROM " . $this->proj_name . "___telIndex LIKE '" . $row["name"] . "'";
				//echo "SQL222: " . $sql . "<br/><br/>";
				$result2 = $this->db->SQLexecute($sql);
	    		if($this->db->NumRows($result2)>0)
				{*/
					$row["id"]=$row["id"] . "_1";
					$row['id_s'] = $row['id'];
					$arr[]=$row;
					
			//	}
			}
		
		}
		/*echo "TELEPHONE TABLE:<br/>";
		echo "<pre>";
		print_r($this->tel_table_fields);
		echo "</pre>";*/
		
		$this->db->ChangeDatabase(DB_WARPIT_WEBCATI);
		// Take all the questions from the project that are ment for the quotas
		$sql="SELECT " . $this->proj_name . ".id, " . $this->proj_name . ".q_name as name,tip_sql FROM " . $this->proj_name . " WHERE use_for_quotas=1 AND (tip_sql = 1 OR tip_sql=4)";
		
		//echo $sql;
		
		if(!$result=$this->db->SQLexecute($sql))
			echo "NAPAKA 032323533:" . $this->db->GetError();
		
		
		/*while($row=$this->db->fetchObj($result))
		{
			$row->source=2;
			$arr[]=$row;
		}*/
		
		while($row=$this->db->fetchAssoc($result))
		{
			$row["id_s"]=$row["id"] . "_2";
			$arr[]=$row;
		}
		
		$this->tel_quest_names=$arr;
		
		$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		/*echo "QUESTIONAIRE TABLE:<br/>";
		echo "<pre>";
		print_r($this->q_table_fields);
		echo "</pre>";*/
		
		return true;

	}
	/**
	 * Get the fields which have all values recoded and can be added to quota layers!
	 *
	 */
	public function get_recoded_q_names()
	{
		$arr = array();
		//we add users
		$arr[]= array('id' => '1_3','id_s' => '1_3','name' => 'Users');
		if(is_array($this->tel_quest_names))
		foreach ($this->tel_quest_names as $telNames) {
			if($telNames['tip_sql'] != 4 )
			{
				$this->get_element_values($telNames['id_s']);
				if(count($this->element_values_for_recode) == 0 )
				{
					$arr[] = $telNames;
				}
			}
			else 
			{
				$this->get_recoded_values($telNames['id_s']);
				if(count($this->recoded_values) > 0 )
				{
					$arr[] = $telNames;
				}
			}
		}
		return $arr;
		
		
	}
	
	public function add_quota_layer($data)
	{
		/*echo "POST:<br/>";
		print_r($data);*/
		$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$def_base=array();
		$tel_base=array();
		$quest_base=array();
		$fieldsForStruct = array();
		foreach($data as $d)
		{
			$e=explode("_", $d);
			if($e[1]==0)
				$def_base[]=$e[0];
			else if($e[1]==1)
				$tel_base[]=$e[0];
			else if($e[1]==2)
				$quest_base[]=$e[0];
			else if($e[1]==3)
				$addUser = true;
		}
		
		//$sql="SELECT max(layer) as max FROM RM_cati_base.quota_layer WHERE id_project=" . $this->project_id;
		$sql="INSERT INTO quota_layer_addon(id,active) VALUES (NULL,1)";
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>234324324 " . $this->db->GetError(); //return false;
		
		$zapSt = 1;
			
		//array that holds the source of the fields at the same index as fields
		$sourceField = array();
		$id = $this->db->GetLastInsertId();
		
		
		// I define last added layer as default for open in statistics
		$sql = " UPDATE _Projects SET id_select_layer = '".$id."' WHERE id = ".$this->project_id;
		$this->db->SQLexecute($sql);
		$fields = array();
		if(sizeof($def_base)>0)
		{
			$sql="SELECT  id,  name FROM  _TelephoneTblDefaultVar WHERE id IN(" . join(", ", $def_base) . ")";
			//echo "SQL: " . $sql . "<br/><br/>";
			if(!$result=$this->db->SQLexecute($sql))
				echo "NAPAKA 02:" . $this->db->GetError(); //return false
			
			while($row=$this->db->fetchAssoc($result))
			{
				$sql = "INSERT INTO quota_layer(id_project, id_question, q_name, layer, source, zap_st) ";
				$sql.=" VALUES('" . $this->project_id . "', '" . $row["id"] . "', '" . $row["name"] . "', '" . $id . "', 0, $zapSt)";
				$zapSt++;
				//echo "<br/>" . $sql . "<br/>";
				$fields[] = 'TP_'.$row["name"];
				$addOnData['source'] = 0;
				$addOnData['id'] = $row['id'];
				$sourceField[] = $addOnData;
				if(!$result2=$this->db->SQLexecute($sql))
					echo "<br/>43123453245 " . $this->db->GetError(); //return false;
			}
			
			
		}
		if(sizeof($tel_base)>0)
		{
			$sql="SELECT _TelephoneTablesAddon.id, _TelephoneTablesAddon.name FROM _TelephoneTablesAddon WHERE id IN(" . join(", ", $tel_base) . ")";
			//echo "SQL: " . $sql . "<br/><br/>";
			if(!$result=$this->db->SQLexecute($sql))
				echo "NAPAKA 02:" . $this->db->GetError(); //return false
			
			while($row=$this->db->fetchAssoc($result))
			{
				$sql="INSERT INTO quota_layer(id_project, id_question, q_name, layer, source, zap_st) ";
				$sql.="VALUES('" . $this->project_id . "', '" . $row["id"] . "', '" . $row["name"] . "', '" . $id . "', 1, $zapSt)";
				$zapSt++;
				//echo "<br/>" . $sql . "<br/>";
				$fields[] = 'TP_'.$row["name"];
				$addOnData['source'] = 1;
				$addOnData['id'] = $row['id'];
				$sourceField[] = $addOnData;
				if(!$result2=$this->db->SQLexecute($sql))
					echo "<br/>43123453245 " . $this->db->GetError(); //return false;
				
			}
			
		}
			
		if(sizeof($quest_base)>0)
		{
			$this->db->ChangeDatabase(DB_WARPIT_WEBCATI);
			
			$sql="SELECT " . $this->proj_name . ".id, " . $this->proj_name . ".q_name as name,tip_sql FROM " . $this->proj_name . " WHERE id IN(" . join(", ", $quest_base) . ")";
			//echo "SQL: " . $sql . "<br/><br/>";
			if(!$result=$this->db->SQLexecute($sql))
				echo "NAPAKA 02:" . $this->db->GetError(); //return false
			$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
			while($row=$this->db->fetchAssoc($result))
			{
				$fields[] = 'QT_'.$row["name"];
				$fieldsForStruct[] = 'QT_'.$row["name"];
				$addOnData['source'] = 2;
				$addOnData['id'] = $row['id'];
				$addOnData['tip_sql'] = $row['tip_sql'];
				$sourceField[] = $addOnData;
				$sql="INSERT INTO quota_layer(id_project, id_question, q_name, layer, source, zap_st) ";
				$sql.="VALUES('" . $this->project_id . "', '" . $row["id"] . "', '" . $row["name"] . "', '" . $id . "', 2, $zapSt)";
				$zapSt++;
				//echo "<br/>" . $sql . "<br/>";
	
				if(!$result2=$this->db->SQLexecute($sql))
					echo "<br/>234324324 " . $this->db->GetError(); //return false;
				
			}
			
			
			
		}
		else if(!$addUser) //if $quest_base == 0, then only telTable variables were selected in this quotalayer!
			//if id_select_layer isn't already set, we set it! 
		{
			$telSelect = true;
				$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
			$sql = "UPDATE quota_layer_addon SET telSelect = 1 WHERE id = $id";
			$this->db->SQLexecute($sql);
			
		}
		
		if($addUser)
		{
		//	$fields[] = 'QT_user_id';
		//	$fieldsForStruct[] = 'QT_user_id';
			$addOnData['source'] = 3;
			$addOnData['id'] = 1;
			$sourceField[] = $addOnData;
			$sql="INSERT INTO quota_layer(id_project, id_question, q_name, layer, source, zap_st) ";
			$sql.="VALUES('" . $this->project_id . "', '', 'user', '" . $id . "', 3, $zapSt)";
			$zapSt++;
			//echo "<br/>" . $sql . "<br/>";

			if(!$result2=$this->db->SQLexecute($sql))
				echo "<br/>234324324 " . $this->db->GetError(); //return false;
				
			
			if(sizeof($tel_base) == 0 && sizeof($def_base) == 0 && sizeof($quest_base) == 0)
			{//only user was set...we put this value into projects table!
				$sql = "UPDATE _Projects SET has_user_quotas = $id WHERE id = $this->project_id";
				if(!$this->db->SQLexecute($sql))
					echo "<br/>234324324 " . $this->db->GetError(); //return false;
			
			}
		}
		
		//we create the question of type 9 (quota info) where you can set the quota text!
		//first we check if it already exists
		$this->db->ChangeDatabase(DB_WARPIT_WEBCATI);
		$sql = "SELECT * FROM ".DB_WARPIT_WEBCATI.".$this->proj_name WHERE q_pos = -2";
		
		if(!$this->db->fetchRow($this->db->SQLexecute($sql)))
		{
			$sql = "INSERT INTO ".DB_WARPIT_WEBCATI.".$this->proj_name (q_pos,q_name,tip_sql,hasStructure,les_sql,isFinal) VALUES(-2,'quotaInfo',5,1,1,1)";
			//'Quotas are full!',  this has to go into language table!!!
			$this->db->SQLexecute($sql); 
			//we increase the autoincrement of the tree to not create duplicates!
			$curAI = $this->db->getAutoIncVal($this->proj_name.'_tree') + 1;
			$this->db->SQLexecute("ALTER TABLE $this->proj_name"."_tree AUTO_INCREMENT = $curAI");
		}
		
		$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		//we create the quota,quotaLimits and quotaCalc  database
		$sql = "CREATE TABLE IF NOT EXISTS {$this->proj_name}___quotaLimits (
			id int(8) NOT NULL auto_increment,
			id_layer int(3) NOT NULL default '0',
			 q_value int(8) NOT NULL default '0',
			  QT_user_id int(8) default NULL,
			  QT_user_type int(8) default NULL,
			  PRIMARY KEY  (`id`),
			  KEY `id_layer` (`id_layer`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
		if(!$this->db->SQLexecute($sql))
					echo "<br/>234324324 " . $this->db->GetError(); //return false;
	 $calcExists = $this->db->TableExists($this->proj_name.'___quotaCalc');
		$sql = "CREATE TABLE IF NOT EXISTS {$this->proj_name}___quotaCalc (
			id int(8) NOT NULL,
			 QT_user_id int(8) default NULL,
			 QT_user_type int(8) default NULL,
			  PRIMARY KEY  (`id`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
		if(!$this->db->SQLexecute($sql))
					echo "<br/>234324324 " . $this->db->GetError(); //return false;

		$sql = "CREATE TABLE IF NOT EXISTS {$this->proj_name}___quotaLock (
			id int(8) AUTO_INCREMENT NOT NULL,
			 isLocked tinyint default 0,
			  PRIMARY KEY  (`id`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
		if(!$this->db->SQLexecute($sql))
					echo "<br/>234324324 " . $this->db->GetError(); //return false;
					
		$limitsFields = $this->db->GetFields($this->proj_name.'___quotaLimits');
 		$missingFields = array_diff($fields,$limitsFields);
		if( count($missingFields)>0)
		{
			foreach ($missingFields as $field) {
				$addFiledSQL[] = $field.' INT(4)';
				$addKeys .= ",ADD KEY $field($field)";
				$updateSQL[] = "a.$field = b.$field";
				
			}
			$sql = "ALTER TABLE " . $this->proj_name . "___quotaLimits ADD(".implode(',',$addFiledSQL).")".$addKeys;
			if(!$this->db->SQLexecute($sql)) echo "ERROR CREATING NEW FIELDS!";
			
			$sql = "ALTER TABLE " . $this->proj_name . "___quotaCalc ADD(".implode(',',$addFiledSQL).")".$addKeys;
			if(!$this->db->SQLexecute($sql)) echo "ERROR CREATING NEW FIELDS!";
	
			
			
			//now we have to update the fields added to 
			
		
		if($calcExists)
		{
			$sql = "UPDATE ".$this->proj_name . "___quotaCalc a INNER JOIN ".$this->proj_name . "_structure b ON a.id=b.id SET ".implode(',',$updateSQL);
			$this->db->SQLexecute($sql);
		}
		
		else //calc did not exists yet...we copy all fields from structure
		{
			$sql = "INSERT INTO $this->proj_name"."___quotaCalc SELECT id,user_id,user_type,".implode(',',$missingFields)." FROM $this->proj_name"."_structure WHERE status=1 AND status_rec=1";
			$this->db->SQLexecute($sql);
		}
		
		}
		if( $telSelect )
		{
			$lockFields = $this->db->GetFields($this->proj_name.'___quotaLock');
			$missingFieldsForLock = array_diff($fields,$lockFields);
			if(count($missingFieldsForLock)>0)
			{
				foreach ($missingFieldsForLock as $field)
				{
					$addFiledLockSQL[] = $field.' INT(4)';
					$addLockKeys .= ",ADD KEY $field($field)";
				}
				$sql = "ALTER TABLE " . $this->proj_name . "___quotaLock ADD(".implode(',',$addFiledLockSQL).")".$addLockKeys;
			if(!$this->db->SQLexecute($sql)) echo "ERROR CREATING NEW FIELDS!";
	
				$this->prepareLockRows();
			}
		}
			
			
	}
	public function delete_layer($idLayer)
	{
		$sql="DELETE FROM quota_layer_addon WHERE id = $idLayer";
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>234324324 " . $this->db->GetError(); //return false;
			
		$sql="DELETE FROM quota_layer WHERE layer = $idLayer";
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>234324324 " . $this->db->GetError(); //return false;
		$sql="DELETE FROM $this->proj_name"."___quotaLimits WHERE id_layer = $idLayer";
		$this->db->SQLexecute($sql);
			
		
		$sql = "UPDATE _Projects SET id_select_layer = 0 WHERE id_select_layer = $idLayer";
		$this->db->SQLexecute($sql);
		$sql = "UPDATE _Projects SET id_select_layer = 0 WHERE has_user_quotas = $idLayer";
		$this->db->SQLexecute($sql);
		
	}
	/**
	 * Reordering layers upon doble click. Moves the clicked name one further
	 *
	 * @param $layer id of the layer
	 * @param $name
	 */
	public function reorder_layer($idLayer,$name)
	{
		$sql = "SELECT zap_st FROM quota_layer WHERE q_name = '$name' AND layer = $idLayer";
		$row = $this->db->fetchArray($this->db->SQLexecute($sql) );
		$orderOld = $row[0];
		
		//we get the next record
		$sql = "SELECT zap_st FROM quota_layer WHERE zap_st = $orderOld+1 AND layer = $idLayer";
		if($row = $this->db->fetchArray($this->db->SQLexecute($sql) ))
		{//we change this row and next row!
			$sql = "UPDATE quota_layer SET zap_st = zap_st-1 WHERE zap_st = $orderOld+1 AND layer = $idLayer";
			$this->db->SQLexecute($sql);
			$sql = "UPDATE quota_layer SET zap_st = zap_st+1 WHERE q_name = '$name' AND layer = $idLayer";
			$this->db->SQLexecute($sql);
			
		}
		else //if clicked name is last we set it on first position!
		{
			$sql = "UPDATE quota_layer SET zap_st = zap_st+1 WHERE zap_st < $orderOld AND layer = $idLayer";
			$this->db->SQLexecute($sql);
			$sql = "UPDATE quota_layer SET zap_st = 1 WHERE q_name = '$name' AND layer = $idLayer";
			$this->db->SQLexecute($sql);
		}
	}
	
	public function insert_layer_define($layer,$fields,&$comboData)
	{
		
		$fields = explode(',',$fields);
		$telSel = true;
		foreach ($fields as $fldName) {
			$qtFields .= ','.$fldName;
			if( substr( $fldName,0,2) == 'QT' )
				$telSel = false;
		}
		
		foreach ($comboData as $data => $value) {
			if(!$value > 0 ) $value = 0;
			$att=array();
			$dataArr = explode('|',$data);
			$insVal = "($layer, $value";
			foreach ($dataArr as $key => $code) {
				//sometimes the label can be after the code
				$code = explode(' ',$code);
				$code = $code[0];
				if($fields[$key] == 'QU_user')//we have to split the qt_user to user and user_type
				{
					$code = str_replace('_',',',$code);
					
				}
				
				$insVal .= ','.$code;
			}
			
			$values[] = $insVal.')';
			
		}
		//replace QU_user if it exists!
		$qtFields = str_replace('QU_user','QT_user_id,QT_user_type',$qtFields);
		//we delete old layer info
		$sql = "DELETE FROM $this->proj_name"."___quotaLimits WHERE id_layer = $layer";
		$this->db->SQLexecute($sql);
		$sql = "INSERT INTO ".$this->proj_name . "___quotaLimits (id_layer,q_value$qtFields) VALUES ".implode(',',$values);
		if(!$result=$this->db->SQLexecute($sql))
			echo $sql."<br/>234324324 " . $this->db->GetError(); //return false;
			
		if($telSel)
		{
			$this->recalculateLock();
		}
		
	}
	
	public function select_quota_layer()
	{
		//$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$sql="SELECT quota_layer.id_project, quota_layer.q_name, quota_layer.layer,a.active,a.interviewer_show,a.allow_other ";
		$sql.="FROM quota_layer INNER JOIN quota_layer_addon a ON a.id = layer WHERE id_project=" . $this->project_id . " ORDER BY layer,zap_st";
		
		//echo "<br/>" . $sql . "<br/>";
		
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>" . $this->db->GetError(); //return false;
		
		$layer=-1;
		$quota_layers=array();
		$l="";
		while($row=$this->db->fetchAssoc($result))
		{
			if($layer==-1)
			{
				$l=array();
				$l["id"]=$row["layer"];
				$names=$row["q_name"];
				$layer=$row["layer"];
				$active = $row['active'];
				$showI = $row['interviewer_show'];
				$allO = $row['allow_other'];
				
			}
			else if($row["layer"]!=$layer)
			{
				$l["names"]=$names;
				$l['active']=$active;
				$l['interviewer_show'] = $showI;
				$l['allow_other'] = $allO; 
				$quota_layers[]=$l;
				$l=array();
				$l["id"]=$row["layer"];
				/*echo "L:<br/>";
				echo "<pre>";
				print_r($l);
				echo "</pre>";*/
				$names=$row["q_name"];
				$layer=$row["layer"];
				$active = $row['active'];
				$showI = $row['interviewer_show'];
				$allO = $row['allow_other'];
			}
			else
			{
				$names.="," . $row["q_name"];
				$layer=$row["layer"];
			}	
		}
		if($names)
		{
			$l["names"]=$names;
			$l['active']=$active;
			$l['interviewer_show'] = $showI;
			$l['allow_other'] = $allO; 
			$quota_layers[]=$l;
			
		}
		for($i=0;$i<count($quota_layers);$i++)
		{
			$sql = "SELECT count(*) FROM $this->proj_name"."___quotaLimits WHERE id_layer = ".$quota_layers[$i]['id'];
			$row = $this->db->fetchRow( $this->db->SQLexecute($sql) );
			$quota_layers[$i]['definedCount'] = $row[0];
		}
		
		$this->quota_layers=$quota_layers;
	}
	
	public function set_active_layer($id,$value)
	{
		$field = $_POST['field'];
		if($field == 'active')
		{
			//lets find out if this layer has any elements from questions
			$sql = "SELECT * FROM quota_layer WHERE layer = $id AND (source = 2 OR source = 3)";
			$res = $this->db->SQLexecute($sql);
			if($this->db->NumRows($res) == 0 )
			{
				
				if($value)
				{
					//this is a telbase only layer! we set this layer in the _Projects and deactivate all others!
					$sql = "UPDATE _Projects SET id_select_layer = $id WHERE id = ".$_POST['id_project'];
					$this->db->SQLexecute($sql);
					
				}
				else 
				{
					//set id_select_layer to 0
					$sql = "UPDATE _Projects SET id_select_layer = 0 WHERE id_select_layer = $id AND id = ".$_POST['id_project'];
					$this->db->SQLexecute($sql);
				}
				$recLock = true;
				
			}
		}
		else if($field == 'allow_other')
		{//this layer has to have at least one question variable!
			$sql = "SELECT * FROM quota_layer WHERE layer = $id AND source = 2";
			$res = $this->db->SQLexecute($sql);
			if($this->db->NumRows($res) == 0 )
				return false;
		}
		$sql = "UPDATE quota_layer_addon SET $field=".$value." WHERE id = ".$id;
		$this->db->SQLexecute($sql);
		if($recLock) $this->recalculateLock();
		return true;
	}
	
	public function get_element_values($element)
	{
		$tmp=explode("_", $element);
		//$name=$tmp[0]; // moren dobiti iz baze
		$id=$tmp[0];
		$source=$tmp[1];
		$values=array();
		unset($tmp);
		
	
		if($source==1 || $source == 0)
		{//an addon field from tables addon OR from basic fileds(oms,region,zip)
			
			if($source == 1)
			{
				$sql="SELECT name FROM _TelephoneTablesAddon ";
				$sql.="WHERE id=" . $id;
				//echo $sql;
				if(!$result=$this->db->SQLexecute($sql))
				{
					echo "<br/>" . $this->db->GetError(); //return false;
					return false;
				}
				
				if(!$row=$this->db->fetchAssoc($result))
				{
					echo "<br/>" . $this->db->GetError();
					return false;
				}
				else
				{
					$name='tel_'.$row["name"];
				}
			}
			else 
			{
				$row = $this->db->fetchAssoc( $this->db->SQLexecute("SELECT name FROM  _TelephoneTblDefaultVar WHERE id = $id") );
				$name = $row['name'];	
			}
			$sql="SELECT DISTINCT(" . $name . ") FROM " . $this->proj_name . "___telIndex ";
			//$sql.="LEFT JOIN _TelephoneLabelsDef b ON b.id_telname = $this->id_telname AND is_addon=$source and b.id_var = $id AND reg_code = $name ";
			$sql.="WHERE " . $name . " NOT IN (SELECT basenum FROM recode WHERE id_project=" . $this->project_id . " AND id_question=" . $id . " AND source=" . $source . " )";
			//$sql="SELECT DISTINCT(a.$name) FROM tel___$this->table_name a WHERE EXISTS(SELECT 1 FROM " . $this->proj_name . "___telIndex b WHERE a.id = b.id_tel AND a." . $name . " NOT IN (SELECT basenum FROM recode WHERE id_project=" . $this->project_id . " AND id_question=" . $id . " AND source=" . $source . ") )";
			
			//echo $sql;
			
			//igor: added getting labels for these values (if set)
			//lets fetch all values...much faster then left join (duuuh)
			$sqlLab = "SELECT * FROM _TelephoneLabelsDef  WHERE id_telname = $this->id_telname AND is_addon=$source and id_var = $id";
			$resLab = $this->db->SQLexecute($sqlLab);
			while ($rowLab = $this->db->fetchAssoc($resLab))
			{
				$labArr[ $rowLab['reg_code']  ] = $rowLab['reg_label'];
			}
			if(!$result=$this->db->SQLexecute($sql))
				echo "<br/>" . $this->db->GetError(); //return false;
				
			while($row=$this->db->fetchAssoc($result))
			{
				// first number is the value of the desired column name, second number is the id of the field in the _TelephoneTablesAddon and the third is the type which identifies that this id has been taken from the _TelephoneTablesAddon
				$tmp=array();
				$tmp["id_s"]=$id . "|" . $source . "|" . $row[$name];
				$tmp["value"]=$row[$name];
				$tmp["label"]= $labArr[ $tmp["value"] ];
				$values[]=$tmp;
				unset($tmp);
			}
			
			$this->element_values_for_recode=$values;
			/*echo "VALUES FOR RECODE:<br/>";
				echo "<pre>";
				print_r($this->element_values_for_recode);
				echo "</pre>";*/
			//$this->get_recoded_values($element);
			
			return true;
		}
		else if($source==2)
		{
			$this->db->ChangeDatabase(DB_WARPIT_WEBCATI);
			
			//$sql="SELECT " . $this->proj_name . ".odg_sql FROM " . $this->proj_name . " WHERE id=" . $id;
			$sql = "SELECT lngText as odg_sql FROM ".$this->proj_name."_language WHERE id_question = $id AND id_language = $this->id_language AND id_text_type = 3";
			if(!$result=$this->db->SQLexecute($sql))
				echo "<br/>" . $this->db->GetError(); //return false;
			
			if($row=$this->db->fetchAssoc($result))
			{
				/*echo "VALUES FOR RECODE:<br/>";
				print_r($row);*/
				
				$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				// first number is the value of the desired column name, second number is the id of the field in the _TelephoneTablesAddon and the third is the type which identifies that this id has been taken from the _TelephoneTablesAddon
				include_once (PATH_TO_ROOT."_class/createQuestion/Question.php");
				$q = new Question();
				
				$pos_answers=$q->answerToArray( $row["odg_sql"]);
				
				//print_r($pos_answers);
				foreach($pos_answers as $answer)
				{
					
					$sql="SELECT id FROM recode WHERE id_project=" . $this->project_id . " AND id_question=" . $id . " AND source=" . $source  . " AND basenum=" . $answer[0];
					
					//echo $sql;
					if(!$result=$this->db->SQLexecute($sql))
						echo "<br/>" . $this->db->GetError(); //return false;
					
					if($this->db->NumRows($result)==0)
					{
						$tmp=array();
						$tmp["id_s"]=$id . "|" . $source . "|" . $answer[0];
						$tmp["value"]=$answer[0];
						$tmp["label"]=$answer[0] . " " . $answer[1];
						$values[]=$tmp;
					}
				}
			}
			
			$this->element_values_for_recode=$values;
			
			//$this->get_recoded_values($element);
			
			return true;
		}
		
		return false;
	}
	
	
	public function recode_values($data, $label, $value)
	{
		$recoded_value=$value;
		$value_to_recode_array=array();
		$is_insert=false;
		foreach($data as $d)
		{
			$tmp=explode("|", $d);
			$id=$tmp[0];
			$source=$tmp[1];
			$value_to_recode=$tmp[2];
			
			//if($this->db->NumRows($result)==0)
			//{
				$value_to_recode_array[]=$value_to_recode;
				$sql="INSERT INTO recode(id_project, id_question, basenum, rangn, reconum, source) VALUES('" . $this->project_id . "', '" . $id . "', '" . $value_to_recode . "', NULL, '" . $recoded_value . "', '" . $source . "')";
				
				if(!$result=$this->db->SQLexecute($sql))
				{
					echo "<br/>" . $this->db->GetError(); //return false;
				}
				else
					$is_insert=true;
			//}	
		}
		
		if($is_insert)
		{
			$sql="INSERT INTO recolabel(id_project, id_question, id_reconum, rec_label, main_values, source) VALUES('" . $this->project_id . "', '" . $id . "', '" . $recoded_value . "', '" . $label . "', '" . join(",", $value_to_recode_array) . "', '" . $source . "')";
				
			if(!$result=$this->db->SQLexecute($sql))
			{
				echo "<br/>" . $this->db->GetError(); //return false;
			}
			if($source == 2)
			{
				$qName = $this->db->get($this->proj_name, $id,'q_name',DB_WARPIT_WEBCATI );
				$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				//recode the entire structure!
				$sql = "UPDATE $this->proj_name"."_structure SET QT_$qName =  $recoded_value ";
				
				$sql .= "WHERE $qName IN (". join(",", $value_to_recode_array).')';

				$this->db->SQLexecute($sql);
			}
		}
		
	}
	//function that automaticly creates a recoded value for each item
	public function auto_recode_values($id_s)
	{
		$this->get_element_values($id_s);
		$tmp=explode("_", $id_s);
		$id=$tmp[0];
		$source=$tmp[1];
		foreach($this->element_values_for_recode as $elVal )
		{
			
			
			$bn = $elVal['value'];
			$lab = $elVal['label'];
			
			$recValues[] = "($this->project_id,$id,$bn,$bn,$source)";
			$labValues[] = "($this->project_id,$id,$bn,'$lab',$bn,$source)";
			$forRecode[] = $bn;
		}
		$sql = "INSERT INTO recode (id_project,id_question,basenum,reconum,source) VALUES ".implode(',',$recValues);
		$this->db->SQLexecute($sql);
		$sql = "INSERT INTO recolabel (id_project,id_question,id_reconum,rec_label,main_values,source) VALUES ".implode(',',$labValues);
		$this->db->SQLexecute($sql);
		
		if($source == 2)
			{
				$qName = $this->db->get($this->proj_name, $id,'q_name',DB_WARPIT_WEBCATI );
				$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				//recode the entire structure!
				$sql = "UPDATE $this->proj_name"."_structure SET QT_$qName =  $qName ";
				
				$sql .= "WHERE $qName IN (". join(",", $forRecode).')';

				$this->db->SQLexecute($sql);
			}
		
	}
	
	public function recode_values2($id_s, $data)
	{
		$recoded_value=$data["value"];
		$recode_label=$data["label"];
		$range_from=$data["range_from"];
		$range_to=$data["range_to"];
		
		$tmp=explode("_", $id_s);
		$id=$tmp[0];
		$source=$tmp[1];
		
		$sql="SELECT id FROM recode WHERE id_project= '" . $this->project_id . "' AND id_question='" . $id . "' AND basenum='" . $range_from . "' AND rangn='" . $range_to . "' AND reconum='" . $recoded_value . "' AND source='" . $source . "'";
		
		//echo $sql;
		
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>" . $this->db->GetError(); //return false;
			
		if($this->db->NumRows($result)==0)
		{
			$value_to_recode_array[]=$value_to_recode;
			$sql="INSERT INTO recode(id_project, id_question, basenum, rangn, reconum, source) VALUES('" . $this->project_id . "', '" . $id . "', '" . $range_from . "', '" . $range_to . "', '" . $recoded_value . "', '" . $source . "')";
			
			if(!$result=$this->db->SQLexecute($sql))
				echo "<br/>" . $this->db->GetError(); //return false;
			
			$main_value=$range_from."-".$range_to;
				
			$sql="INSERT INTO recolabel(id_project, id_question, id_reconum, rec_label, main_values, source) VALUES('" . $this->project_id . "', '" . $id . "', '" . $recoded_value . "', '" . $recode_label . "', '" . $main_value . "', '" . $source . "')";
				
			if(!$result=$this->db->SQLexecute($sql))
			{
				echo "<br/>" . $this->db->GetError(); //return false;
			}
			
		}
		if($source == 2)
			{
				$qName = $this->db->get($this->proj_name, $id,'q_name',DB_WARPIT_WEBCATI );
				$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				//recode the entire structure!
				$sql = "UPDATE $this->proj_name"."_structure SET QT_$qName =  $recoded_value ";
				
				$sql .= "WHERE {$qName}_1 >= $range_from AND {$qName}_1 <= $range_to ";

				$this->db->SQLexecute($sql);
			}
	}
	
	
	
	public function get_recoded_values($element)
	{
		$tmp=explode("_", $element);
		$id=$tmp[0];
		$source=$tmp[1];
		
		$sql="SELECT id, id_reconum, rec_label, main_values FROM recolabel WHERE id_project= '" . $this->project_id . "' AND id_question='" . $id . "'  AND source='" . $source . "' ORDER BY id";
		
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>QQQQQQ: " . $this->db->GetError(); //return false;
		
		$recoded_values=array();
		while($row=$this->db->fetchAssoc($result))
		{
			$tmp=array();
			$tmp["id"]=$row["id"];
			//$tmp["label"]="(" . $row["main_values"] . ") --> " . $row["id_reconum"] . " " . $row["rec_label"];
			$tmp["main_values"]="(" . $row["main_values"] . ") --> ";
			$tmp["reconum"]=$row["id_reconum"];
			$tmp["rec_label"]=$row["rec_label"];
			$recoded_values[]=$tmp;
		}
		
		$this->recoded_values=$recoded_values;
		return true;
		
	}
	
	
	public function delete_quota_layer($data)
	{
		/*echo "POST:<br/>";
		echo "<pre>";
		print_r($data);
		echo "</pre>";*/
		
		$sql="DELETE FROM quota_layer_addon WHERE id=" . $data;
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>" . $this->db->GetError(); //return false;
			
		$sql="DELETE FROM quota_layer WHERE layer=" . $data;
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>" . $this->db->GetError(); //return false;
	//remove extra columns from limit,calc,lock tables...recalculate lock table and all that shajt :)
	
		$sql="DELETE FROM $this->proj_name"."___quotaLimits WHERE id_layer = $data";
		$this->db->SQLexecute($sql);
			
		$sql = "SELECT distinct id_question,source,q_name FROM quota_layer a LEFT JOIN quota_layer_addon b ON a.layer = b.id WHERE id_project = $this->project_id";	
		$res = $this->db->SQLexecute( $sql );
		while ($row = $this->db->fetchRow($res) )
		{
			if($row[1] == 2)
					$pref = 'QT_';
			else 
				$pref = 'TP_';
			$layers[] = $pref.$row[2];
				
		}
		
		$layers[] = "id";
		$layers[] = "id_layer";
		$layers[] = "q_value";
		$layers[] = "QT_user_id";
		$layers[] = "QT_user_type";
		
		
		$limitsFields = $this->db->GetFields($this->proj_name.'___quotaLimits');
		$missingFields = array_diff($limitsFields,$layers);
		
		
		if(count($missingFields)>0)
		{
			$sql = "ALTER TABLE $this->proj_name"."___quotaLimits DROP ".implode(",DROP ",$missingFields);
			$this->db->SQLexecute($sql);
			$sql = "ALTER TABLE $this->proj_name"."___quotaCalc DROP ".implode(",DROP ",$missingFields);
			$this->db->SQLexecute($sql);
		
		}
			$sql = "SELECT distinct id_question,source,q_name FROM quota_layer a LEFT JOIN quota_layer_addon b ON a.layer = b.id WHERE id_project = $this->project_id and telSelect = 1";	
		$res = $this->db->SQLexecute( $sql );
		$layers = array();
		while ($row = $this->db->fetchRow($res) )
		{
			
				$pref = 'TP_';
			$layers[] = $pref.$row[2];
				
		}
		
		$layers[] = "id";
		$layers[] = "isLocked";
		
		$lockFields = $this->db->GetFields($this->proj_name.'___quotaLock');
		$missingLockFields = array_diff($lockFields,$layers);
		if(count($missingLockFields)>0)
		{
			$sql = "ALTER TABLE $this->proj_name"."___quotaLock DROP ".implode(",DROP ",$missingLockFields);
			$this->db->SQLexecute($sql);
			
			$this->prepareLockRows();
		}
		/*$row=$this->db->fetchAssoc($result);
		$max=$row["max"]+1;
		
		for($i=0; $i<count($data["names"]); $i++)
		{
			$d=explode("_", $data["names"][$i]);
			//$zs="zap_st_".$d[1]."_".$d[2];
			$zap_st=$data["zap_st_{$d[1]}_{$d[2]}"];
			
			echo "<br/>---------------------<br/>";
			echo "DATA NAMES: " . $data["names"][$i] . "<br/>";
			echo "DATA NUMBERS: " . $zap_st . "<br/>";
			echo "---------------------<br/>";
			
			$sql="INSERT INTO quota_layer(id_project, id_question, q_name, layer, source, zap_st) ";
			$sql.="VALUES('" . $this->project_id . "', '" . $d[1] . "', '" . $d[0] . "', '" . $max . "', '" . $d[2] . "', '" . $zap_st . "')";
			
			echo "<br/>" . $sql . "<br/>";

			if(!$result=$this->db->SQLexecute($sql))
				echo "<br/>" . $this->db->GetError(); //return false;
		}
		*/
	}
	
	public function delete_recoded_value($id)
	{
		/*echo "POST:<br/>";
		echo "<pre>";
		print_r($data);
		echo "</pre>";*/
		
		
		$sql="SELECT id_project, id_question, id_reconum, source,main_values FROM recolabel WHERE id=" . $id;
		
		if(!$result=$this->db->SQLexecute($sql))
			echo "<br/>" . $this->db->GetError(); //return false;
		else
		{
			if($row=$this->db->fetchAssoc($result))
			{
				//if variable is in a layer, we can't delete!
				$sql = "SELECT * FROM quota_layer WHERE id_project=" . $row["id_project"] . " AND id_question=" . $row["id_question"] . "  AND source=" . $row["source"];
				if($this->db->fetchRow($this->db->SQLexecute($sql)))
				{
					return "{success:false,error:'Variable is used in layer!'}";
				}
				
				$sql="DELETE FROM recode WHERE id_project=" . $row["id_project"] . " AND id_question=" . $row["id_question"] . " AND reconum=" . $row["id_reconum"] . " AND source=" . $row["source"];
				
				if(!$result=$this->db->SQLexecute($sql))
					echo "<br/>" . $this->db->GetError(); //return false;
				else
				{
					$sql="DELETE FROM recolabel WHERE id=" . $id;
		
					if(!$result=$this->db->SQLexecute($sql))
						echo "<br/>" . $this->db->GetError(); //return false;
				}
				if($row['source'] == 2)
				{
					$qName = $this->db->get($this->proj_name, $row['id_question'],'q_name',DB_WARPIT_WEBCATI );
					$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
					//recode the entire structure!
					$tmp = explode('-',$row['main_values']);
					$sql = "UPDATE $this->proj_name"."_structure SET QT_$qName = NULL ";
					if(count($tmp) == 2)
						$sql.= "WHERE {$qName}_1 >= ".$tmp[0]. " AND {$qName}_1 <=".$tmp[1];
					else
						$sql .= "WHERE $qName IN (".$row['main_values'].')';

					$this->db->SQLexecute($sql);
						
				}
				
			}
			
		}
		return "{success:true}";
	}
	private function prepareLockRows()
	{
		//we find all the fields that are used in tel layers! (active or inactive!)
			$sql = "TRUNCATE $this->proj_name"."___quotaLock";
			$this->db->SQLexecute($sql);
			
			$sql = "SELECT distinct id_question,source,q_name FROM quota_layer a left join quota_layer_addon b ON a.layer = b.id WHERE id_project = $this->project_id AND telSelect = 1";	
			$res = $this->db->SQLexecute($sql);
			while ($row = $this->db->fetchAssoc($res))
			{
				$src = $row['source'];
				$fieldMetaData[$row['q_name']] = $row;
				$sql = "SELECT id_reconum,rec_label FROM recolabel WHERE id_project = $this->project_id AND id_question = {$row['id_question']} AND source = {$row['source']} ORDER BY id_reconum";
				$fldRes = $this->db->SQLexecute($sql);
				$fldArr = array();
				while ($fldRow = $this->db->fetchAssoc($fldRes))
				{
					$fldArr[] = $fldRow['id_reconum'];
				}
				
				
				$pre = 'TP_';
				if($src == 1)
				{
					$indexPre = 'tel_';
				}
				else 
					$indexPre = '';
				$fieldGroups[] = $fldArr;
				$fieldCols[] = $pre.$row['q_name'];
				$this->indexCols[] = $indexPre.$row['q_name'];
				
			}
			if(!$fieldGroups) return;
			function createCombinations($curComb,&$arr,$i)
			{
			 	$curArr = $arr[$i];
			 	if(count($arr) == $i ) return array('('.implode(',',$curComb).')');
			 	$string = array();
			 	foreach ($curArr as $recVal)
			 	{
			 		$tmpArr = $curComb;
			 		$tmpArr[] = $recVal;
			 		$string =  array_merge($string, createCombinations($tmpArr,$arr,$i+1));
			 	}
			 	
			 	return $string;
			 	
			}
			$lockArr = createCombinations(array(),$fieldGroups,0);
			
			$sql = "INSERT INTO ".$this->proj_name."___quotaLock (".implode(',',$fieldCols).") VALUES ".implode(',',$lockArr);
			$this->db->SQLexecute($sql);
			
			try {
			$sql = "ALTER TABLE ".$this->proj_name."___telIndex DROP INDEX `quota`";
			$this->db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				
			}
			
			$sql = "ALTER TABLE  ".$this->proj_name."___telIndex ADD INDEX  `quota` (".implode(',',$this->indexCols)." )";
			$this->db->SQLexecute($sql);
			$this->recalculateLock();
			
	}
	
	private function recalculateLock()
	{
		//we unlock all rows
		$sql = "UPDATE $this->proj_name"."___quotaLock SET isLocked = 0";
		$this->db->SQLexecute($sql);
		//we need to get all layers with tel!
			$sql = "SELECT layer,q_name FROM quota_layer a LEFT JOIN quota_layer_addon b ON a.layer=b.id WHERE id_project = $this->project_id AND telSelect = 1 AND active = 1";
			$res = $this->db->SQLexecute($sql);
			while ($row = $this->db->fetchRow($res))
			{
				$telLayers[ $row[0] ][] = 'TP_'.$row[1];
			}
			if($telLayers)
			foreach ($telLayers as $id_layer => &$layerCols) 
			{
				$calcValues = array();
				$limitValues = array();
				$fields = array();
				foreach ($layerCols as $lCol) {
					$fields[] = $lCol;
				}
				
				$select_ank_tpreg = "SELECT ".implode(',',$fields).", count(*) AS number FROM $this->proj_name"."___quotaCalc GROUP BY ".implode(',',$fields);
				$res = $this->db->SQLexecute($select_ank_tpreg );
				while ($qCalcRow = $this->db->fetchAssoc($res) )
				{
					$valsComb = array();
					foreach ($fields as $fld)
					{
						$valsComb[] = $qCalcRow[$fld];	
					}
					$calcValues[ implode('_',$valsComb) ] = $qCalcRow['number'];
				}
				
				$select_quota_tpreg = "SELECT ".implode(',',$fields).",q_value FROM $this->proj_name"."___quotaLimits WHERE id_layer=$id_layer ORDER BY q_value";
				$res = $this->db->SQLexecute($select_quota_tpreg );
				while ($qCalcRow = $this->db->fetchAssoc($res) )
				{
					$valsComb = array();
					foreach ($fields as $fld)
					{
						$valsComb[] = $qCalcRow[$fld];	
					}
					$limitValues[ implode('_',$valsComb) ] = $qCalcRow['q_value'];
				}
				foreach ($limitValues as $combo => $qLimit) {
					if($qLimit <=0 || $qLimit <= $calcValues[ $combo ] )
					{
						$whereCndTmp = array();
						$sepValues = explode('_',$combo);
						foreach ($fields as $ind => $fld)
						{
							$whereCndTmp[] = $fld.' = '.$sepValues[$ind];	
						}
						$whereCnd[] = '( '.implode(' AND ',$whereCndTmp).')';
					}
					
				}
				
				if($whereCnd)
				{
					$sql = "UPDATE $this->proj_name"."___quotaLock SET isLocked = 1 WHERE ".implode(' OR ',$whereCnd);
					//echo $sql;
					$this->db->SQLexecute($sql);
				}
			}
		
	}
	
}
?>