<?php
ob_start();
include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
require_once(PATH_TO_ROOT.'CRM/class/FirePHP.class.php');
require_once(PATH_TO_ROOT.'CRM/class/fb.php');
$firephp = FirePHP::getInstance(true);
set_time_limit(0);
ini_set("display_errors",1);
ini_set('error_reporting', E_ALL ^ E_NOTICE);

if($_GET['action'] == "delete"){
	$sql = "DROP TABLE Warpit_CRM.projectsWIthIdTel";
	$db->SQLexecute($sql);
	$sql = "DROP TABLE Warpit_CRM._visitStatistic2";
	$db->SQLexecute($sql);
	$sql = "DROP VIEW Warpit_CRM.id_tel_FIX";
	$db->SQLexecute($sql);
	$sql = "DROP VIEW Warpit_CRM.id_tel_FIX_NO_NAMES";
	$db->SQLexecute($sql);
	exit;
}

$sql = "CREATE  TABLE `Warpit_CRM`.`_visitStatistic2` SELECT * FROM `Warpit_CRM`.`_visitStatistic`";
FB::info($sql, "SQL 1");

$db->SQLexecute($sql);
$sql = "CREATE TABLE IF NOT EXISTS Warpit_CRM.projectsWIthIdTel (
	id INT(11) NOT NULL AUTO_INCREMENT,
	projectId INT(11) NOT NULL,
	id_tel INT (11) NOT NULL,
	name VARCHAR (50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
FB::info($sql, "SQL 3");
$db->SQLexecute($sql);

$sql = "SELECT * FROM ".DB_WARPIT_WEBCATI_BASE."._Projects WHERE proj_name LIKE 'teden%'";
FB::info($sql, "SQL 4");
$rez = $db->SQLexecute($sql);
while($zad = $db->fetchAssoc($rez)){
	$projName = DB_WARPIT_WEBCATI_BASE.".".$zad['proj_name']."_structure";
	$projectId = $zad['id'];
	$sql = "SELECT * FROM ".$projName." WHERE status = 1 AND status_rec = 1";
	$res = $db->SQLexecute($sql);
	while($row = $db->fetchAssoc($res)){
		$sql = "INSERT INTO Warpit_CRM.projectsWIthIdTel VALUES('', '".$projectId."', '".$row['id_tel']."', '".mysql_real_escape_string($row['oseba_1'])."')";
		$db->SQLexecute($sql);
	}
}


$sql = "CREATE VIEW Warpit_CRM.id_tel_FIX AS SELECT v.id AS vId, v.id_tel, v.firstname, v.surname, t.id AS tId, t.telephone, t.name FROM Warpit_CRM._visitStatistic as v JOIN Warpit_Webcati_base.tel___medical as t ON t.telephone = v.id_tel AND CONCAT(v.firstname,' ', v.surname) = t.name";
FB::info($sql, "SQL 5");
$db->SQLexecute($sql);
$sql = "DELETE FROM Warpit_CRM._visitStatistic2 WHERE id_tel < 0";
FB::info($sql, "SQL 6");
$db->SQLexecute($sql);

$sql = "DELETE FROM Warpit_CRM._visitStatistic2 WHERE id_tel IN (SELECT id_tel FROM Warpit_CRM.projectsWIthIdTel as p WHERE p.projectId = id_project AND p.id_tel = id_tel)";
FB::info($sql, "SQL 7");
$db->SQLexecute($sql);

$sql = "DELETE FROM Warpit_CRM._visitStatistic2 WHERE id IN (SELECT vId FROM Warpit_CRM.id_tel_FIX WHERE vId = id)";
FB::info($sql, "SQL 8");
$db->SQLexecute($sql);

$sql = "CREATE VIEW Warpit_CRM.id_tel_FIX_NO_NAMES AS SELECT v.id AS vId, v.id_tel, v.firstname, v.surname, t.id AS tId, t.telephone, t.name FROM Warpit_CRM._visitStatistic2 as v LEFT JOIN Warpit_Webcati_base.tel___medical as t ON t.telephone = v.id_tel";
$db->SQLexecute($sql);

$sql = "UPDATE Warpit_CRM._visitStatistic2 as v SET id_tel = (SELECT tId FROM Warpit_CRM.id_tel_FIX WHERE vId = v.id) WHERE id = (SELECT vId FROM Warpit_CRM.id_tel_FIX WHERE vId = v.id)";
$db->SQLexecute($sql);
$sql = "UPDATE Warpit_CRM._visitStatistic2 as v SET id_sub_tel = (SELECT tId FROM Warpit_CRM.id_tel_FIX WHERE id_tel = v.id_sub_tel) WHERE id_sub_tel = (SELECT id_tel FROM Warpit_CRM.id_tel_FIX WHERE id_tel = v.id_sub_tel)";
$db->SQLexecute($sql);

?>