<?php
include ("_dirinfo.php");
include (PATH_TO_ROOT."_config/dbConfig.php");

$verzija = $db->get('_WarpitConfig','cName = "version"','cValue',DB_WARPIT_MAIN);
$verzija = $verzija[0];
$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
$sql = "SELECT proj_name,id FROM _Projects";
$res = $db->SQLexecute($sql);
//$db->ChangeDatabase(DB_WARPIT_WEBCATI);
while ($row = $db->fetchRow($res))
{
	$tbl = $row[0];
	$projId = $row[1];
	try {
		if( 1  > $verzija)
		{
		/*$sql = "ALTER TABLE  `$tbl"."_structure` ADD  d_stop DATETIME AFTER t_stop,ADD d_start DATETIME AFTER t_stop,ADD d_status DATETIME AFTER t_stop";
		//$db->SQLexecute($sql);

		$sql = "UPDATE $tbl"."_structure SET d_stop = FROM_UNIXTIME(t_stop),d_start = FROM_UNIXTIME(t_start)";
		$db->SQLexecute($sql);

		$sql = "ALTER TABLE  `$tbl"."___telIndex` ADD  ank_user_type TINYINT AFTER id_ank";
		$db->SQLexecute($sql);

		$sql = "ALTER TABLE  `$tbl"."_structure` ADD  user_type TINYINT AFTER user_id";
		$db->SQLexecute($sql);
		$sql = "ALTER TABLE  `".$tbl."___telLog` ADD  user_type TINYINT AFTER id_user";;
		$db->SQLexecute($sql);*/
		$db->ChangeDatabase(DB_WARPIT_WEBCATI);

	/*	$sql = "ALTER TABLE  `".$tbl."` DROP  attachAnswers";
		$db->SQLexecute($sql);

		$sql = "ALTER TABLE  `".$tbl."` ADD  attachAnswers int(11) AFTER anno_sql";;
		$db->SQLexecute($sql);

		$sql = "ALTER TABLE  `".$tbl."` ADD  attachFalse BOOL AFTER attachAnswers";;
		$db->SQLexecute($sql);
		*/
		$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);

		/*$sql = "ALTER TABLE  `".$tbl."___EngineLog` ADD  eventLogger text";;
		$db->SQLexecute($sql);*/

		$db->ChangeDatabase(DB_WARPIT_WEBCATI);
		/*$sql = "ALTER TABLE  `".$tbl."` ADD  srotAttributes int(1), ADD erotAttributes int(1)";
		$db->SQLexecute($sql);
		*/
		$sql = "ALTER TABLE  `".$tbl."` ADD  open_attributes char(250) NULL, ADD filter_attributes text NULL";
		$db->SQLexecute($sql);

		}
		if(4 > $verzija)
		{
			$db->ChangeDatabase(DB_WARPIT_WEBCATI);
			$sql = "ALTER TABLE `".$tbl."` ADD answerGroups text NULL,ADD legendGroups text NULL";
			$db->SQLexecute($sql);

			$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
			$sql = "ALTER TABLE `".$tbl."_structure` ADD id_device tinyint AFTER user_cp";
			$db->SQLexecute($sql);
		}
		if(5 > $verzija)
		{
			$db->ChangeDatabase(DB_WARPIT_WEBCATI);
			$sql = "UPDATE $tbl SET goto_sql='\$status==1#-6,\$status==90#-8,true#-10' WHERE q_pos = -9";
			$db->SQLexecute($sql);
		}
		if(7 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."_structure ADD id_telname INT(8) AFTER id_tel";
				$db->SQLexecute($sql);
				$sql = "ALTER TABLE ".$tbl."___telLog ADD id_telname INT(8) AFTER id_tel";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{

			}
			$sql = "ALTER TABLE ".$tbl."___EngineLog ADD id_user int(11) NOT NULL, ADD user_type tinyint(4) NOT NULL";
			$db->SQLexecute($sql);
		}
		if(9 > $verzija)
		{
			$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
			$length = 15;
		    $characters = "0123456789abcdefghijklmnopqrstuvwxyz";
		    $charCnt = strlen($characters)-1;
				 $string ='';
			    for ($p = 0; $p < $length; $p++) {
			        $string .= $characters[mt_rand(0, $charCnt)];
			    }
				$sqlTmp = "INSERT INTO _www_externalLinks (`id_project`, `linkType`, `loginSID`) VALUES ($projId,1,'".$string."-')";
				$db->SQLexecute($sqlTmp);
				$wwwId = $db->GetLastInsertId();
				$sqlTmp = "UPDATE _www_externalLinks SET loginSID = CONCAT(loginSID,id) WHERE id = $wwwId";
				$db->SQLexecute($sqlTmp);

				 $string ='';
			    for ($p = 0; $p < $length; $p++) {
			        $string .= $characters[mt_rand(0, $charCnt)];
			    }
				$sqlTmp = "INSERT INTO _www_externalLinks (`id_project`, `linkType`, `loginSID`) VALUES ($projId,2,'".$string."-')";
				$db->SQLexecute($sqlTmp);
				$wwwId = $db->GetLastInsertId();
				$sqlTmp = "UPDATE _www_externalLinks SET loginSID = CONCAT(loginSID,id) WHERE id = $wwwId";
				$db->SQLexecute($sqlTmp);

				 $string ='';
			    for ($p = 0; $p < $length; $p++) {
			        $string .= $characters[mt_rand(0, $charCnt)];
			    }
				$sqlTmp = "INSERT INTO _www_externalLinks (`id_project`, `linkType`, `loginSID`) VALUES ($projId,3,'".$string."-')";
				$db->SQLexecute($sqlTmp);
				$wwwId = $db->GetLastInsertId();
				$sqlTmp = "UPDATE _www_externalLinks SET loginSID = CONCAT(loginSID,id) WHERE id = $wwwId";
				$db->SQLexecute($sqlTmp);

		}
		if(13 > $verzija)
		{

			$lng = $db->get('_Projects',$projId,'language',DB_WARPIT_WEBCATI_BASE);
			$db->ChangeDatabase(DB_WARPIT_WEBCATI);


			$sql = "INSERT INTO $tbl (q_pos,q_name,tip_sql) VALUES (-16,'_notActive',5)";
			$db->SQLexecute($sql);

			$lastId = $db->GetLastInsertId();
			$sql = "INSERT INTO $tbl"."_language (id_language,id_question,id_text_type,lngText) VALUES ($lng, $lastId,2,'This survey is not active!')";
			$db->SQLexecute($sql);

			//povecat ID od dreve za jedno!
			$treeAI = $db->getAutoIncVal($tbl.'_tree');
			$treeAI++;
			$db->SQLexecute("ALTER TABLE $tbl"."_tree AUTO_INCREMENT = $treeAI");

		}
		if(19 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."___telIndex ADD `status_comment` varchar(250) COLLATE utf8_unicode_ci NOT NULL AFTER app_koment";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."___telIndex can not be modified!</font><br>";
			}
		}
		if(30 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI);
				$sql = "ALTER TABLE ".$tbl." CHANGE `answers_filter` `answers_filter` TINYINT( 1 ) NOT NULL DEFAULT '0'";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				echo "Field answers_filter does not exist...!";
			}
		}
		if(34 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI);
				$sql = "ALTER TABLE ".$tbl." ADD `repeat_legend` INT NOT NULL";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl." can not be modified!</font><br>";
			}
		}
		if(35 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."___quotaCalc ADD `QT_user_type` INT NOT NULL AFTER `QT_user_id`";
				$db->SQLexecute($sql);


			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."___quotaCalc can not be modified!</font><br>";
			}
		}
		if(43 > $verzija)
		{
			// add id_mask...
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI);
				$sql = "ALTER TABLE ".$tbl." ADD `id_mask` INT(8) NULL AFTER `id_style`";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl." can not be modified with id_mask!</font><br>";
			}

			// structure add index on id_tel
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."_structure ADD KEY `id_tel` (`id_tel`)";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."_structure can not be modified with INDEX on id_tel!</font><br>";
			}

			// structure add index on id_telname
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."_structure ADD KEY `id_telname` (`id_telname`)";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."_structure can not be modified with INDEX on id_telname!</font><br>";
			}

		}
		if(53 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."_structure CHANGE `user_cp` `id_loginSID` INT NULL DEFAULT NULL";
				$db->SQLexecute($sql);


			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."_structure can not be modified!</font><br>";
			}
		}
		if(55 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE `".$tbl."___telLog`
						ADD `int_channel_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
						ADD `int_channel` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
						ADD `ext_channel_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
						ADD `ext_channel` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
						ADD `sip` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
						ADD KEY `int_channel_id` (`int_channel_id`),
						ADD KEY `ext_channel_id` (`ext_channel_id`)";
				$db->SQLexecute($sql);


			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."___telLog can not be modified with Asterisk components!</font><br>";
			}
		}
		if(61 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."___telIndex ADD `callback` tinyint NOT NULL DEFAULT 0 AFTER call_num";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."___telIndex can not be modified!</font><br>";
			}
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$sql = "ALTER TABLE ".$tbl."___telIndex ADD INDEX `callback` (`callback`)";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl."___telIndex can not be modified with INDEX on callback!</font><br>";
			}
		}
		if(63 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI);
				$sql = "ALTER TABLE ".$tbl." ADD `autoSubmit` tinyint(1) NOT NULL default 0";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl." can not be modified with autoSubmit feature!</font><br>";
			}
		}
		if(77 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI);
				$sql = "ALTER TABLE ".$tbl." ADD `multi_nogo_legends` VARCHAR( 100 ) NOT NULL AFTER `multi_nogo_answers`";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl." can not be modified with no-go for legend feature!</font><br>";
			}
		}
		if(78 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI);
				$sql = "ALTER TABLE ".$tbl." ADD `id_plugin` int(5) NULL";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl." can not be modified with plugin feature!</font><br>";
			}
		}
		if(80 > $verzija)
		{
			try
			{
				$db->ChangeDatabase(DB_WARPIT_WEBCATI);
				$sql = "ALTER TABLE ".$tbl." ADD `missing_values` VARCHAR( 100 ) NULL";
				$db->SQLexecute($sql);
			}
			catch (Exception $e)
			{
				var_dump($e->getMessage());
				echo "<br><font color=red>Table ".$tbl." can not be modified with missing values feature!</font><br>";
			}
		}
		if(82 > $verzija)
        {
            try
            {
                $db->ChangeDatabase(DB_WARPIT_WEBCATI);
                $sql = "ALTER TABLE ".$tbl." ADD `id_incentive` INT( 11 ) NULL";
                $db->SQLexecute($sql);
            }
            catch (Exception $e)
            {
                var_dump($e->getMessage());
                echo "<br><font color=red>Table ".$tbl." can not be modified with id_incentive!</font><br>";
            }
        }

        if(83 > $verzija)
        {
            try
            {
                $db->ChangeDatabase(DB_WARPIT_WEBCATI);
                $sql = "ALTER TABLE ".$tbl." ADD `incentive_possibilites` TINYINT( 1 ) NOT NULL DEFAULT  '0'";
                $db->SQLexecute($sql);
            }
            catch (Exception $e)
            {
                var_dump($e->getMessage());
                echo "<br><font color=red>Table ".$tbl." can not be modified with id_incentive!</font><br>";
            }
        }
        if(83 > $verzija)
        {
            try
            {
                $db->ChangeDatabase(DB_WARPIT_WEBCATI);
                $sql = "ALTER TABLE ".$tbl." ADD `allow_listening` TINYINT( 1 ) NOT NULL DEFAULT  '0' AFTER  `incentive_possibilites`";
                $db->SQLexecute($sql);
            }
            catch (Exception $e)
            {
                var_dump($e->getMessage());
                echo "<br><font color=red>Table ".$tbl." can not be modified with allow_listening!</font><br>";
            }
        }
        if(106 > $verzija)
        {
            try
            {
                $db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
                $sql = "ALTER TABLE `".$tbl."___EngineLog` ADD `id_language` SMALLINT NULL AFTER `user_type`";
                $db->SQLexecute($sql);
            }
            catch (Exception $e)
            {
                var_dump($e->getMessage());
                echo "<br><font color=red>Table ".$tbl."___EngineLog can not be modified with id_language!</font><br>";
            }
        }
        if(112 > $verzija){
        	try{

        		$sql = "SELECT proj_name FROM ".DB_WARPIT_WEBCATI_BASE."._Projects";
        		if(!$res = $db->SQLexecute($sql)) die($db->JsonError());
        		while($row = $db->fetchRow($res)){

        			if($db->TableExists($row[0], DB_WARPIT_WEBCATI)){
        				$sql = "SHOW COLUMNS FROM ".DB_WARPIT_WEBCATI.".".$row[0]." LIKE 'open_end_type'";
        				if(!$temp = $db->SQLexecute($sql)) die($db->JsonError());
        				if($db->NumRows($temp) == 0){
        					$sql = "ALTER TABLE `".DB_WARPIT_WEBCATI."`.`".$row[0]."` ADD `open_end_type` TINYINT ( 4) NOT NULL DEFAULT '1' COMMENT '1 text box 2 text area'";
        					if(!$db->SQLexecute($sql)) die($db->JsonError());
        				}
        			}
        		}

        	}catch (Exception $e)
            {
                var_dump($e->getMessage());
                echo "<br><font color=red>Table ".$tbl."___EngineLog can not be modified with id_language!</font><br>";
            }
        }

		echo "$tbl SUCCESSFUL!<br>";
	}
	catch (Exception $e)
	{
		print_r($e->getMessage());
		echo "<br><br><br>";
	}

}

?>
