<?php 

class tgi
{
	protected $db;
	
	public function __construct($db)
	{
		$this->db=$db;
	}

	
	protected function getActiveProject() {
		$row = $this->db->get("TGI_projects","active=1","id");
		$id_project = $row[0];
		
		$this->db->LogWrite($id_project,true,"get active project inside tgi class");
		
		return $id_project;
	}
	

	public function getActiveQuestionnaireLinks($WarpitProjects) {
		if (isset($WarpitProjects['ids'])) {
			$sql = "SELECT id_project,loginSID FROM ".DB_WARPIT_WEBCATI_BASE."._www_ids WHERE id_user=0 AND user_type=3 AND id_project IN (".implode(",",$WarpitProjects['ids']).") AND id_site=0";
			$this->db->LogWrite($sql,true,"SQL get links for all questionnaires");
			$res = $this->db->SQLexecute($sql);
			while ($row = $this->db->fetchArray($res)) {
				$Qlinks[$row['id_project']] = $row['loginSID'];
			
				$this->db->LogWrite($row['id_project']." = ".$row['loginSID'],true,"get links for all questionnaires");
			}
			
			return $Qlinks;
		} else 
			return false;	
	}
	

	public function getWarpitProjects() {
		$sql = "SELECT a.*,b.proj_name FROM `TGI_warpit_projects` a
				LEFT JOIN ".DB_WARPIT_WEBCATI_BASE."._Projects b
				ON a.warpit_project=b.id
				WHERE a.TGI_project='".$this->getActiveProject()."'";
		$res = $this->db->SQLexecute($sql);
		while ($row = $this->db->fetchArray($res)) {
			$this->db->LogWrite($row['proj_name'],true,"get active warpit project names");
			$this->db->LogWrite($row['warpit_project'],true,"get active warpit project id");
			$this->db->LogWrite($row['C_name'],true,"get active warpit project HUMAN names");
			
			$WarpitProjects['name'][$row['warpit_project']] = $row['proj_name'];
			$WarpitProjects['C_name'][$row['warpit_project']] = $row['C_name'];
			$WarpitProjects['ids'][$row['warpit_project']] = $row['warpit_project'];
			$WarpitProjects['pct'][$row['warpit_project']] = $row['done'];
		}
		
		return $WarpitProjects;
	}
	
	/*
	 * Main function for creating warpit tables
	 */
	public function createWarpitTables($arrDDF,$language,$idActiveProject) {
		
		if ($this->buildTables($arrDDF,$language,$idActiveProject)) {
			if ($this->buildQuestions($arrDDF,$language)) {
				return true;
			} 
			else	
				return false;
		} 
		else
			return false;
	}
	
	
	protected function buildQuestions($arrDDF,$language) {
		//echo print_r($arrDDF);
		$this->db->ChangeDatabase(DB_WARPIT_WEBCATI);
		
		foreach ($arrDDF AS $questionnaire_cnt => $questions_array) {
			$questionnaireCounter = $questionnaire_cnt;
			$QuestionnaireName = $questions_array['name'];
			
			if (isset($questions_array['question'])) {
				$positionCounter = 1;
				foreach ($questions_array['question'] AS $questions => $questionProperty) {
					unset($Qlegend_arr);
					unset($TGIlegend_arr);
					unset($Qanswers);
					unset($TGIcode);
					unset($Qlegend);
					unset($TGIlegend);
					unset($ifLegendAnswer);
					unset($Qanswers_arr);
					unset($TGIcode_arr);
						
					$questionName = $questions;
					$type = $questionProperty['type']; 
					$subtype = $questionProperty['subtype'];
					$show = $questionProperty['show'];
					$Qlabel = $questionProperty['label'][0];
					$mustAnswer = $questionProperty['must_answer'];
					 
					if ($type == 1 OR ($type == 3 AND $subtype == 0) OR$type == 5) {
						
						$answerCounter = 1;
						if (isset($questionProperty['answers'])) {
							foreach ($questionProperty['answers'] AS $answerKey => $answerItem) {
								if ($answerKey != "visible") {
									$Qanswers_arr[] = $answerCounter." ".$answerItem[0];
									$TGIcode_arr[] = $answerCounter.":".$answerKey;
									$answerCounter++;
								}
							}
						}
						if (isset($Qanswers_arr)) {
							$Qanswers = implode("/",$Qanswers_arr);
							$TGIcode = implode(",",$TGIcode_arr);
						}	
						
					} elseif (($type == 2 AND $subtype == 1) OR ($type == 3 AND $subtype == 1)) {
						
						$legendCounter = 1;
						if (isset($questionProperty['legend'])) {
							foreach ($questionProperty['legend'] AS $legendKey => $legendItem) {
								$Qlegend_arr[] = $legendCounter." ".$legendItem[0];
								
								$answerCounter = 1;
								if (isset($legendItem['answers'])) {
									foreach ($legendItem['answers'] AS $answerKey => $answerItem) {
										if ($legendCounter == 1) $Qanswers_arr[] = $answerCounter." ".$answerItem[0];
										$TGIcode_arr[] = $answerCounter.":".$answerKey;
										$answerCounter++;
									}
								}
								if (isset($Qanswers_arr)) {
									$Qanswers = implode("/",$Qanswers_arr);
								}	
								if (isset($TGIcode_arr)) {
									$TGIcode = implode(",",$TGIcode_arr);
								}	
									
								$TGIlegend_arr[] = $TGIcode;
								
								$legendCounter++;
							}
						}
						
						if (isset($Qlegend_arr)) {
							$Qlegend = implode("/",$Qlegend_arr);
						}	
						if (isset($TGIlegend_arr)) {
							$TGIlegend = implode(";",$TGIlegend_arr);
						}	
						
					}
					
					$this->db->LogWrite($QuestionnaireName,true,"Questionnaire name");
					$this->db->LogWrite($questionnaireCounter,true,"Questionnaire counter");
					$this->db->LogWrite($questionName,true,"Question name");
					$this->db->LogWrite($type,true,"Question type");
					$this->db->LogWrite($subtype,true,"Question subtype");
					$this->db->LogWrite($show,true,"Question show");
					$this->db->LogWrite($mustAnswer,true,"Must answer");
					$this->db->LogWrite($Qlabel,true,"Question label");
					$this->db->LogWrite($Qanswers,true,"Question answer");
					$this->db->LogWrite($Qlegend,true,"Question legend answer");
					$this->db->LogWrite($TGIcode,true,"TGI -> warpit answer codes");
					$this->db->LogWrite($TGIlegend,true,"TGI -> warpit legend answer codes");
					$this->db->LogWrite("\n\n",true,"");
					
					if ($type == 1 OR $type == 2 OR $type == 5) $les_sql = 1;
					if ($type == 3) $les_sql = 4;  
					
					if ($show == 1) {
						$sql = "INSERT INTO `".$QuestionnaireName."` (`id`,`q_pos`,`q_name`,`tip_sql`,`les_sql`,`open_sql`,`must_answer`) VALUES 
																 (null, $positionCounter, '".$questionName."', ".$type.", '".$les_sql."', 1, ".$mustAnswer.")";
						$this->db->SQLexecute($sql);
						$this->db->LogWrite($sql,true,"fill quest table with new question");		
						$id_question = $this->db->GetLastInsertId();
						
						$sql = "INSERT INTO `".$QuestionnaireName."_tree` (`id`,`id_sup`,`id_question`,`orderBy`,`idLab`,`is_leaf`,`showTogether`) VALUES 
																 (".$id_question.", 0, '".$id_question."', ".($positionCounter-4).", '".$questionName."', 1, 0)";
						$this->db->SQLexecute($sql);
						$this->db->LogWrite($sql,true,"fill tree table with new question");		
						
						if ($Qlegend) $ifLegendAnswer = ",(null, ".$language[0].", '".$id_question."', 4, '".addslashes($Qlegend)."')";
						$sql = "INSERT INTO `".$QuestionnaireName."_language` (`id`,`id_language`,`id_question`,`id_text_type`,`lngText`) VALUES 
																 (null, ".$language[0].", '".$id_question."', 2, '".addslashes($Qlabel)."')
																 ,(null, ".$language[0].", '".$id_question."', 3, '".addslashes($Qanswers)."')
																 ".$ifLegendAnswer;
						$this->db->SQLexecute($sql);
						$this->db->LogWrite($sql,true,"fill language table with new labels");		
						
						
						$positionCounter++;
					}
				}
			}
			
		}
		return true;
	}
	
	
	protected function buildTables($arrDDF,$language,$idActiveProject) {
		$createUser = $_SESSION['UserInfo']['Firstname']." ".$_SESSION['UserInfo']['Surname'];
		$createTime = $_SESSION['UserInfo']['LastLogin'];
		
		
		$this->db->ChangeDatabase(DB_WARPIT_TGI);
		$sql = "DELETE FROM `TGI_warpit_projects` WHERE TGI_project=".$idActiveProject;
		$this->db->LogWrite($sql,true,"clear `TGI_warpit_projects`");		
		$this->db->SQLexecute($sql);

		foreach ($arrDDF AS $questions_array) {
			$name_project = $questions_array['name'];
			$language_project = $language[0];
			$name_project_human = $questions_array['sub_name'][0]; // vzamem privzeto prvi jezik iz DDFja
			
			$this->db->LogWrite($name_project."<->".$language_project."<->".$name_project_human,true,"name and language for project");
			try {
				// create new project and get ID, first remove the old one
				$this->db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
				$id_arr = $this->db->get("`_Projects`","`proj_name`='$name_project'","id");
				$id_project_delete = $id_arr[0];
				
				$sql = "DELETE FROM `_Projects` WHERE id='".$id_project_delete."'";
				$this->db->SQLexecute($sql);
				$sql = "DELETE FROM `_ProjectsTree` WHERE id='".$id_project_delete."'";
				$this->db->SQLexecute($sql);
				$sql = "DELETE FROM `_LanguageProjects` WHERE id_project='".$id_project_delete."'";
				$this->db->SQLexecute($sql);
				$sql = "DELETE FROM `_www_externalLinks` WHERE id_project='".$id_project_delete."'";
				$this->db->SQLexecute($sql);
				$sql = "DELETE FROM `_www_ids` WHERE id_project='".$id_project_delete."'";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($id_project_delete,true,"project ID for delete");
				
				 
				$sql = "INSERT INTO `_Projects` (`id`,`key_proj`,`proj_name`,`language`,`naziv_proj`,`old`,`created`,`creat_name`,`modify`,`modi_name`,`start_date`,`end_date`,`rotation`,`id_capiTable`,`capi_reserve`,`set_use_autodial`,`predictive_work`,`predictive_numbers_tofind`,`predictive_overcall`,`isTemplate`,`id_select_layer`,`redirectActive`,`has_user_quotas`) VALUES
						(null,'-TGIwarpit','".$name_project."',".$language_project.",'','N','".$createTime."','".$createUser."','".$createTime."','".$createUser."','".$createTime."',FROM_UNIXTIME(".(time()+7776000)."),'N',0,0,0,0,0,0,0,0,0,0)";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"projects table");		
				
				$id_project = $this->db->GetLastInsertId();
				$sql = "UPDATE `_Projects` SET `key_proj`='".$id_project."-TGIwarpit' WHERE id=".$id_project;
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"projects table key_proj");		
				
				$sql = "INSERT INTO `_ProjectsTree` (`id`,`id_sup`,`id_project`,`orderBy`,`idLab`,`is_leaf`) VALUES
						(".$id_project.",0,'".$id_project."',1,'".$name_project."',1)";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"projects Tree table");		
								
				$sql = "INSERT INTO `_LanguageProjects` (`id_language`,`id_project`) VALUES
						(".$language_project.",".$id_project.")";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"projects Language table");		
				
				
				for ($n=1;$n<=3;$n++) {
					$sql = "INSERT INTO `_www_externalLinks` (`id`,`id_project`,`linkType`) VALUES
							(null,".$id_project.",$n)";
					$this->db->SQLexecute($sql);
					$this->db->LogWrite($sql,true,"projects externalLinks table");	

					$link_id = $this->db->GetLastInsertId();
					$www_link = $this->randomLinks()."-".$link_id;
					$sql = "UPDATE `_www_externalLinks` SET `loginSID`='".$www_link."' WHERE id=".$link_id;
					$this->db->SQLexecute($sql);
					$this->db->LogWrite($sql,true,"projects www_ids update link");
				}
				
				
				$sql = "INSERT INTO `_www_ids` (`id`,`id_user`,`user_type`,`id_project`,`id_site`) VALUES
						(null,0,3,".$id_project.",0)";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"projects www_ids table");
				
				$link_id = $this->db->GetLastInsertId();
				$www_link = $this->randomLinks()."-".$link_id;
				$sql = "UPDATE `_www_ids` SET `loginSID`='".$www_link."' WHERE id=".$link_id;
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"projects www_ids update link");
				
				
				
				$this->db->ChangeDatabase(DB_WARPIT_TGI);
				// create connection between TGI project and warpit projects
				$sql = "INSERT INTO `TGI_warpit_projects` SET warpit_project=".$id_project.", TGI_project=".$idActiveProject.", C_name='".$name_project_human."'";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"create connection between TGI project and warpit projects");		
				
				
				
				$this->db->ChangeDatabase(DB_WARPIT_WEBCATI);
				
				$sql = "DROP TABLE IF EXISTS `$name_project`";
				$this->db->SQLexecute($sql);
				
				$sql = "CREATE TABLE IF NOT EXISTS `$name_project` (
					  `id` int(5) NOT NULL AUTO_INCREMENT,
					  `q_pos` int(5) NOT NULL,
					  `q_name` char(20) COLLATE utf8_unicode_ci NOT NULL,
					  `tip_sql` int(2) DEFAULT NULL,
					  `drg_sql` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
					  `les_sql` int(2) DEFAULT NULL,
					  `goto_sql` text COLLATE utf8_unicode_ci NOT NULL,
					  `fil_sql` text COLLATE utf8_unicode_ci NOT NULL,
					  `con_sql` text COLLATE utf8_unicode_ci NOT NULL,
					  `open_sql` int(2) DEFAULT NULL,
					  `anno_sql` int(2) DEFAULT NULL,
					  `attachAnswers` int(11) DEFAULT NULL,
					  `attachFalse` tinyint(1) DEFAULT NULL,
					  `rot_sql` text COLLATE utf8_unicode_ci,
					  `srot_sql` int(1) DEFAULT NULL,
					  `erot_sql` int(1) DEFAULT NULL,
					  `srotLegend` int(1) DEFAULT NULL,
					  `erotLegend` int(1) DEFAULT NULL,
					  `srotAttributes` int(1) DEFAULT NULL,
					  `erotAttributes` int(1) DEFAULT NULL,
					  `qrot_sql` char(9) COLLATE utf8_unicode_ci DEFAULT NULL,
					  `id_style` int(8) DEFAULT NULL,
					  `must_answer` tinyint(1) NOT NULL DEFAULT '1',
					  `disable_question` tinyint(1) NOT NULL DEFAULT '0',
					  `answers_filter` tinyint(1) NOT NULL DEFAULT '0',
					  `variableTypeBase` tinyint(1) NOT NULL DEFAULT '4',
					  `variableTypeOpen` tinyint(1) NOT NULL DEFAULT '0',
					  `open_answers` char(250) COLLATE utf8_unicode_ci DEFAULT NULL,
					  `open_legend` char(250) COLLATE utf8_unicode_ci DEFAULT NULL,
					  `open_attributes` char(250) COLLATE utf8_unicode_ci DEFAULT NULL,
					  `multi_nogo_answers` char(250) COLLATE utf8_unicode_ci DEFAULT NULL,
					  `multilanguage_all` text COLLATE utf8_unicode_ci,
					  `qleave_js_alert` tinyint(1) NOT NULL DEFAULT '0',
					  `filter_answer` text COLLATE utf8_unicode_ci,
					  `filter_legend` text COLLATE utf8_unicode_ci,
					  `filter_attributes` text COLLATE utf8_unicode_ci,
					  `hasStructure` tinyint(4) DEFAULT '0',
					  `use_for_quotas` tinyint(1) DEFAULT '0',
					  `isFinal` tinyint(1) DEFAULT '0',
					  `id_showTogether` int(11) NOT NULL DEFAULT '0',
					  `answerGroups` text COLLATE utf8_unicode_ci,
					  `legendGroups` text COLLATE utf8_unicode_ci,
					  `old_id` int(11) NOT NULL,
					  `up_down_rotchk` int(11) NOT NULL,
					  `up_down_rotvar` int(11) NOT NULL,
					  `repeat_legend` int(11) NOT NULL,
					  PRIMARY KEY (`id`),
					  INDEX `tip_sql` (`tip_sql`),
					  INDEX `q_pos` (`q_pos`),
					  UNIQUE KEY `q_name` (`q_name`)
					) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"quest table");		
				
				$sql = "INSERT INTO `$name_project` (`id`,`q_pos`,`q_name`,`tip_sql`,`goto_sql`,`hasStructure`) VALUES
													(1, -6, '_Start', 5, 'true#1', 1),
													(2, -5, '_End', 5, '', 1),
													(3, -15, '_notAllow', 5, '', 1),
													(4, -16, '_notActive', 5, '', 1)";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"fill quest table with defaults");		
				
		
				$sql = "DROP TABLE IF EXISTS `".$name_project."_tree`";
				$this->db->SQLexecute($sql);
				
				$sql = "CREATE TABLE IF NOT EXISTS `".$name_project."_tree` (
					  `id` int(3) NOT NULL AUTO_INCREMENT,
					  `id_sup` int(5) NOT NULL,
					  `id_question` int(5) NOT NULL,
					  `orderBy` tinyint(3) NOT NULL,
					  `idLab` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
					  `node_img` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
					  `is_leaf` tinyint(1) NOT NULL,
					  `showTogether` tinyint(1) NOT NULL,
					  PRIMARY KEY (`id`),
					  INDEX `id_question` (`id_question`),
					  INDEX `orderBy` (`orderBy`),
					  INDEX `is_leaf` (`is_leaf`),
					  INDEX `id_sup` (`id_sup`) 
					) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"quest tree table");		
				
				
				$sql = "DROP TABLE IF EXISTS `".$name_project."_language`";
				$this->db->SQLexecute($sql);
				
				$sql = "CREATE TABLE IF NOT EXISTS `".$name_project."_language` (
					  `id` int(11) NOT NULL AUTO_INCREMENT,
					  `id_language` int(11) NOT NULL,
					  `id_question` int(11) NOT NULL,
					  `id_text_type` int(11) NOT NULL,
					  `lngText` text COLLATE utf8_unicode_ci NOT NULL,
					  PRIMARY KEY (`id`),
					  UNIQUE KEY `qlt_index` (`id_language`,`id_question`,`id_text_type`)
					) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"quest language table");		
				
				
				$sql = "DROP TABLE IF EXISTS `".$name_project."_script`";
				$this->db->SQLexecute($sql);
				
				$sql = "CREATE TABLE IF NOT EXISTS `".$name_project."_script` (
					  `id_question` int(11) NOT NULL,
					  `id_event` int(11) NOT NULL,
					  `script` text COLLATE utf8_unicode_ci NOT NULL,
					  PRIMARY KEY (`id_question`,`id_event`)
					) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
				$this->db->SQLexecute($sql);
				$this->db->LogWrite($sql,true,"quest script table");
					
			} catch(Exception $e) {
				echo 'Message: '.$e->getMessage();
				return false;
			}
		}
		
		return true;
	}
	
	
	protected function randomLinks() {
		$string = "abcdefghijklmnoprstuvzABCDEFGHIJKLMNOPRSTUVZ0123456789";
		for ($n=0;$n<15;$n++) {
			$seed = rand(0,53);
			$randomlink .= $string[$seed];
		}
		return $randomlink;
	}
	
}
	
?>