<?php
/*
 * ---- USE OF THE CLASS ----
 * 
 * - Construct the object with the id of the project and an Object for database manipulation
 * 
 * - Call the method prepare_index_table_and_attributes($att), where the variable $att holds the attributes and criteria to search for in the database of tel. numbers
 * 
 * - Call the method get_ids($sampling_type, $att, $num_of_samples), where $sampling_type is the type of sampling(random, get first x samples, get id`s every x step),
 *   $att are attributes and the values to search for and $num_of_samples is the number of how many samples to extract from the database of tel. numbers
 * 
 */
/*
 * ERROR DESCRIPTION
 * 
 * 1 - The attribute was not found in the desired telephone table 
 * 2 - The database does not contain the number of samples that were requested
 * 3 - There was an database error and the temporary table was not constructed
 */

/**
 * The class main function is to extract telephone numbers and insert them into a table called table_name___telIndex
 *
 */
class sampler
{
	public $table_fields;		// INT fields from the telephone table
	public $id_project;
	public $proj_name;
	public $table_name;		// telephone table name
	public $index_table_name;	// index table name
	public $joined_att;		// attributes ready for a database statement
	public $att_to_select;		// attributes that need to be selected and inserted into the index table
	public $errors; // array of errors
	/**
	 * Enter description here...
	 *
	 * @var db
	 */
	public $datab;
	public $temp_table_att="";
	private $select_where = "status NOT IN(4,5,6,7,9)";
	private $tablePrefix;
	private $telTablePrefix;
	private $fieldPrefix;
	private $telIdField;
	
	
	/**
	 * read warpit config for delay time 
	 *
	 * @return array 
	 */
	private function getDelayTime() {
		$this->datab->ChangeDatabase(DB_WARPIT_MAIN);
		$sampleOptions = configLoader::loadConfig('sample_option');
		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);		

		return $sampleOptions; 
	}
	
	
	/**
	 * 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,$capi=false)
	{
		$this->datab=$datab;
		
			
		$this->capi = $capi;
		$this->tablePrefix = $capi ? '_Capi' : '_Telephone';
		$this->telTablePrefix = $capi ? '_capi___' : 'tel___';
		$this->fieldPrefix = $capi ? 'capi_' : 'tel_';
		$this->telIdField = $capi ? 'id_capi' : 'id_tel';
		$this->projTableIdField = $capi ? 'id_capiTable' : 'id_telname';
		
		
		
		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)
	{	
		$this->id_project = $id;
		// Take all fields of type INT and make a list of them
		$sql="SELECT {$this->tablePrefix}TablesAddon.name FROM _Projects, {$this->tablePrefix}TablesAddon WHERE _Projects.id=" . $id;
		$sql.=" AND _Projects.$this->projTableIdField={$this->tablePrefix}TablesAddon.id_telbaza AND {$this->tablePrefix}TablesAddon.type IN (4,5,7)";
		
		//echo "<br/>" . $sql . "<br/>";
		
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_04:" . $this->datab->GetError(); //return false; //echo $datab->get_err_msg();
		
		if($this->capi)
		{
			$arr[] = 'id_obcina';
			
		}
		else
		{
			
			$arr=array();
			$arr[] = 'oms';
			$arr[] = 'region';
			$arr[] = 'zip';
			while($row=$this->datab->fetchAssoc($result))
			{
				$arr[]=$this->fieldPrefix.$row["name"];
			}
			/*
			echo "<br/>TELEPHONE TABLE:<br/>";
			echo "<pre>";
			print_r($this->table_fields);
			echo "</pre>";
			*/
		}	

		$this->table_fields=$arr;
			
			// Get the name of the telephone table
			$sql="SELECT {$this->tablePrefix}Tables.name FROM _Projects, {$this->tablePrefix}Tables WHERE _Projects.id=" . $id;
			$sql.=" AND _Projects.$this->projTableIdField={$this->tablePrefix}Tables.id";
			if(!$result=$this->datab->SQLexecute($sql))
				echo $this->datab->GetError();
			
			if(!$row=$this->datab->fetchAssoc($result))
				echo $this->datab->GetError();
			else 
			{
				$this->table_name=$row["name"];
			}
	
		
		// Get the name of the table_name_telIndex table
		$sql="SELECT _Projects.proj_name FROM _Projects WHERE _Projects.id=" . $id;
		
		if(!$result=$this->datab->SQLexecute($sql))
			echo $this->datab->GetError();
		
		if(!$row=$this->datab->fetchAssoc($result))
			echo $this->datab->GetError();
		else 
		{
			$this->proj_name = $row['proj_name'];
			$this->index_table_name= $this->capi ? '_capiSample___'.$this->table_name   :  $row["proj_name"].'___telIndex';
		}
		
		return true;

	}
	
	/**
	 * This method prepares the index table for the telephone numbers to be selected and also prepares the attributes for a database query
	 *
	 * @param array $att - The array containing names of columns to be constructed in the telephone index table
	 * @return bool
	 */
	public function prepare_index_table_and_attributes($att)
	{
		if($this->capi)
		{
			$this->att_to_select = 'id_project';
			$this->att_to_insert = $this->id_project;
			$this->select_where = "1=1";
			return true;//capi does not need special table
		}
		
		$atrributes_to_select=array();
		$temp_table_att=array();
		$this->specialSelectFilter = " AND proj_reserved = 0";
		
		
		$td = $this->datab->describe($this->telTablePrefix.$this->table_name);
		if(substr($td['telephone']['type'],0,3) == 'int')
			$telType = 'int';
		else 
			$telType = 'varchar(30)';
		// Check if maybe somehow a column was selected that is not in the telephone table
		foreach($att as $key)
		{
			if(in_array($key, $this->table_fields)==false)
			{
				$this->errors[]=1; 		// The attribute was not found in the desired telephone table 
				echo "$key not in table fields!";
				return false;
			}
			
		}
		//constructs table from all fields
		foreach($this->table_fields as $key )
		{
			if($key == 'oms' && $telType != 'int')
			{
				$columns.="`" . $key . "` varchar(7) NOT NULL DEFAULT '0',";
			}
			else
				$columns.="`" . $key . "` int(7) NOT NULL DEFAULT '0',";
			$keys.="KEY `" . $key . "` (`" . $key . "`),";
			$atrributes_to_select[]=$key;
			$temp_table_att[]=$this->telTablePrefix.$this->table_name . "." . $key;
		}
		$atrributes_to_select[] = 'tdisable';
		
		$this->att_to_select=join(", ", $atrributes_to_select);
		$this->att_to_insert = $this->att_to_select;
		$this->temp_table_att=join(", ", $temp_table_att);
		
		if (!$this->createIndexTable($this->proj_name,$columns,$keys)) 
			return false;

		if ($this->createLogTable($this->proj_name,$telType)) 
			return true;
		else 
			return false;
	}

	
	/**
	 * create telIndex table
	 * only if not exists yet
	 *
	 * @return unknown
	 */
	public function createIndexTable($projName,$columns,$keys) { 
		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$sql="CREATE TABLE IF NOT EXISTS `".$projName."___telIndex` (
			  `id` int(9) NOT NULL AUTO_INCREMENT,
			  `id_tel` int(9) DEFAULT NULL,
			  " . $columns . "
			  `status` int(3) NOT NULL DEFAULT '0',
			  `status_rec` int(3) NOT NULL DEFAULT '0',
			  `last_call` int(11) NOT NULL DEFAULT '0',
			  `call_num` tinyint(2) NOT NULL DEFAULT '0',
			  `callback` tinyint(2) NOT NULL DEFAULT '0',
			  `call_time` int(11) NOT NULL DEFAULT '0',
			  `id_ank` int(8) NOT NULL DEFAULT '0',
			  `ank_user_type` TINYINT NOT NULL DEFAULT '0',
			  `app_time` int(11) NOT NULL DEFAULT '0',
			  `app_koment` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `status_comment` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
			  `app_middle` int(8),
			  `app_call_this_num` varchar(50),
			  `tdisable` tinyint(1) NOT NULL DEFAULT '0',	
			  PRIMARY KEY (`id`),
			  UNIQUE (`id_tel`),
			  " . $keys . "
			  INDEX `status` (`status`),
			  INDEX `status_rec` (`status_rec`),
			  INDEX `call_num` (`call_num`),
			  INDEX `callback` (`callback`),
			  INDEX `last_call` (`last_call`),
			  INDEX `call_time` (`call_time`),
			  INDEX `id_ank` (`id_ank`),
			  INDEX `app_time` (`app_time`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
			";
		$this->datab->LogWrite($sql,true,"make index table");
		
		if(!$this->datab->SQLexecute($sql))
			return false; 
		
		return true;			
	}

	
	/**
	 * create telLog table
	 * only if not exists
	 *
	 * @return unknown
	 */
	public function createLogTable($projName,$telType) { 
		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
		$sql="CREATE TABLE IF NOT EXISTS `".$projName."___telLog` (
			  `id` int(9) NOT NULL AUTO_INCREMENT,
			  `id_tel` int(9) DEFAULT NULL,
			  `id_telname` int(9),
			  `id_structure` int(9),
			  `id_user` int(9),
			  `user_type` TINYINT,
			  `oms`  $telType,
			  `telephone` $telType,
			  `status` int(3) NOT NULL DEFAULT '0',
			  `status_rec` int(3) NOT NULL DEFAULT '0',
			  `call_time` int(11) NOT NULL DEFAULT '0',
			  `end_time` int(11) NOT NULL DEFAULT '0',
			  `app_koment` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `int_channel_id` varchar(20) NULL,
			  `int_channel` varchar(100) NULL,
			  `ext_channel_id` varchar(20) NULL,
			  `ext_channel` varchar(100) NULL,
			  `sip` varchar(20) NULL, 
			  PRIMARY KEY (`id`),
			  INDEX `status` (`status`),
			  INDEX `status_rec` (`status_rec`),
			  INDEX `call_time` (`call_time`),
			  INDEX `end_time` (`end_time`),
			  INDEX `id_structure` (`id_structure`),
			  INDEX `id_tel` (`id_tel`,`id_telname`),
			  INDEX `id_user` (`id_user`,`user_type`),
			  INDEX `int_channel_id` (`int_channel_id`),
			  INDEX `ext_channel_id` (`ext_channel_id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
			";
		$this->datab->LogWrite($sql,true,"make log table");
			
		if(!$this->datab->SQLexecute($sql))
			return false;
			
		return true;
	}
	

	/**
	 * generate where for time sample delay
	 *
	 */
	private function getDelayWhere() {
		$nowTime = time();
		$sampleOptions = $this->getDelayTime();
		$delayTime = $sampleOptions['delay_day_time']*86400;
		$goTime = $nowTime-$delayTime;
		
		return $goTime; 
	}
	
	
	/**
	 * use all available numbers 
	 *
	 */
	public function useAll() {
		/**
		 * Borut add 2014.11.20 - 12:00, IGNORE, not sure if this is a good idea, time will tell
		 */
		$sql = "INSERT IGNORE INTO ".$this->index_table_name." ($this->telIdField, ".$this->att_to_select.") 
				SELECT id as $this->telIdField, ".$this->att_to_insert." 
				FROM {$this->telTablePrefix}".$this->table_name." 
				WHERE proj_reserved=0 AND usp_utime<".$this->getDelayWhere()." AND ".$this->select_where;
		//echo "/*$sql*/";
		
		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);		
		if(!$result=$this->datab->SQLexecute($sql)){
			echo "/*ERROR_01:" . $this->datab->GetError()."*/";
			return false;
		}
		
		return $this->datab->affectedRows();
	}
	
	
	/**
	 * Get id`s randomly
	 *
	 * @param INT $num_of_samples - The number of samples we intend to extract from the telephone table and insert it into the index table
	 * @return bool
	 */
	private function get_random_ids($num_of_samples) {	
		/**
		 * Borut add 2014.11.20 - 12:00, IGNORE, not sure if this is a good idea, time will tell
		 */
		$sql = "INSERT IGNORE INTO " . $this->index_table_name . "($this->telIdField, " . $this->att_to_select . ") 
				SELECT id as $this->telIdField, " . $this->att_to_insert . " 
				FROM {$this->telTablePrefix}" . $this->table_name . " 
				WHERE " . $this->joined_att . "$this->specialSelectFilter AND usp_utime<".$this->getDelayWhere()." AND $this->select_where 
				ORDER BY rand() LIMIT " . $num_of_samples;
		echo "/*$sql*/";

		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);		
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_04:" . $this->datab->GetError();
		
		return $this->datab->affectedRows();	
	}
	
	
	/**
	 * Get first x id`s matching the search criteria
	 *
	 * @param INT $num_of_samples - The number of samples we intend to extract from the telephone table and insert it into the index table
	 * @return bool
	 */
	private function first_x_samples($num_of_samples) {
		$sql = "INSERT INTO " . $this->index_table_name . "($this->telIdField, " . $this->att_to_select . ") 
				SELECT id as $this->telIdField, " . $this->att_to_insert . " 
				FROM {$this->telTablePrefix}" . $this->table_name . " 
				WHERE " . $this->joined_att . "$this->specialSelectFilter = 0 AND usp_utime<".$this->getDelayWhere()." AND $this->select_where 
				ORDER BY {$this->telTablePrefix}" . $this->table_name . ".id LIMIT 0," . $num_of_samples;
		
		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);		
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_02:" . $this->datab->GetError();
		
		/*
		echo "----------------------<br/>";
		echo $sql;
		echo "<br/>-----------------------<br/>";
		*/
		
		return $this->datab->affectedRows();
		//echo "<br/>ST: " . $num . "<br/>";
		if($num==$num_of_samples)
		{
			return true;
			$sql="COMMIT;";
			if(!$result=$this->datab->SQLexecute($sql))
				echo "<br/>ERROR_02:" . $this->datab->GetError();
		}
		else
		{
			$sql="ROLLBACK;";
			if(!$result=$this->datab->SQLexecute($sql))
				echo "<br/>ERROR_02:" . $this->datab->GetError();
			$this->errors[]=2; 		// The database does not contain the number of samples that were requested
				return false;
		}
		
		return $this->datab->affectedRows();
		
	}
	
	/**
	 * Get id`s every x step
	 *
	 * @param INT $num_of_samples
	 * @return bool
	 */
	private function get_every_x_step($num_of_samples) {
		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);		

		$sql="DROP TEMPORARY TABLE IF EXISTS rands;";
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_01:" . $this->datab->GetError();
		
		$sql="create temporary table rands (rownum INT, id INT)";
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_02:" . $this->datab->GetError();
		
		$sql="insert into rands (rownum, id) SELECT @row := @row +1 AS rownum, id FROM (SELECT @row :=0)r, {$this->telTablePrefix}" . $this->table_name . " WHERE " . $this->joined_att . "$this->specialSelectFilter = 0 AND usp_utime<".$this->getDelayWhere()." AND $this->select_where";

		$this->datab->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);		
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_03:" . $this->datab->GetError();
				
		$sql="SELECT MAX(rownum) AS max_row FROM rands";
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_04:" . $this->datab->GetError();
				
		$row=$this->datab->fetchAssoc($result);
		//echo "MAX: " . $row["max_row"] . "<br/>";
		$max=floor($row["max_row"]/$num_of_samples);
		
		//echo "EVERY: " . $max . " STEP<br/>";		
		
		/*$sql="SET autocommit=0";
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_02:" . $this->datab->GetError();*/
			
	
		$sql="INSERT INTO " . $this->index_table_name . "($this->telIdField, " . $this->att_to_select . ") SELECT rands.id as $this->telIdField," . $this->temp_table_att . " FROM rands, {$this->telTablePrefix}" . $this->table_name . " WHERE rands.rownum %" . $max . " =0 AND rands.id={$this->telTablePrefix}" . $this->table_name . ".id LIMIT " . $num_of_samples;
		
		/*
		echo "----------------------<br/>";
		echo $sql;
		echo "<br/>-----------------------<br/>";
		*/
		
		if(!$result=$this->datab->SQLexecute($sql))
			echo "<br/>ERROR_05:" . $this->datab->GetError();
		
		return $this->datab->affectedRows();
		//echo "<br/>ST: " . $num . "<br/>";
		if($num==$num_of_samples)
		{
			return true;
			$sql="COMMIT;";
			if(!$result=$this->datab->SQLexecute($sql))
				echo "<br/>ERROR_02:" . $this->datab->GetError();
		}
		else
		{
			$sql="ROLLBACK;";
			if(!$result=$this->datab->SQLexecute($sql))
				echo "<br/>ERROR_02:" . $this->datab->GetError();
			$this->errors[]=2; 		// The database does not contain the number of samples that were requested
				return false;
		}
		
		return $this->datab->affectedRows();
		
		
	}
	
	
	/**
	 * The method takes the input parameter, which is an array, and converts it into a form that can be used to execute a database query
	 *
	 * @param array $att - the array holds the names of the columns that have been previously constructed in the index table with the method create_index_table()
	 * @return bool
	 */
	private function prepare_attributes($att)
	{
		$atrribute_pairs=array();
		foreach($att as $key => $value)
		{
			if(in_array($key, $this->table_fields)==false)
			{
				$this->errors[]=1; 		// The attribute was not found in the desired telephone table 
				return false;
			}
			$atrribute_pairs[]="{$key}='{$value}'";
		}
		
		$this->joined_att=join(" AND ", $atrribute_pairs);
		
		return true;
	}
	
	
	/**
	 * This method is called to get the samples from the telephone table to the table_name_telIndex table
	 *
	 * @param INT $sampling_type - Sampling type can be:
	 * 1 - Get id`s randomly
	 * 2 - Get first x id`s matching the search criteria
	 * 3 -  Get id`s every x step
	 * @param array $att
	 * @param INT $num_of_samples
	 * @return bool
	 */
	public function get_ids($sampling_type, $att, $num_of_samples)
	{
		
		if(!($this->prepare_attributes($att))) return false;
		
		// Get id`s randomly
		if($sampling_type==1) return $this->get_random_ids($num_of_samples);
		
		// Get first x id`s matching the search criteria
		else if($sampling_type==2) return $this->first_x_samples($num_of_samples);
		
		// Get id`s every x step
		else if($sampling_type==3) return $this->get_every_x_step($num_of_samples);
		
		return true;
	}
	
	
 	/**
 	 * The method return true or false, depending on the fact if there were any errors found
 	 *
 	 * @return bool
 	 */
 	public function found_errors()
	{
		if (count($this->errors) > 0)
		{
			return true;
		}
		else
			return false;
	}
	
	
	/**
	 * Outputs on the screen what kind of errors have been found
	 *
	 * @param string $delim - The delimiter for different errors (<br/>, space, semicolon,...)
	 * @return string
	 */
	public function list_errors($delim = ' ')
	{
		$errors=implode($delim,$this->errors);
		$this->errors=array();
		return $errors;
	}
	
	
}
?>