<?php
include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once (PATH_TO_ROOT."CRM/_CRM_config.php");
include_once(PATH_TO_ROOT."CRM/class/class.checkString.php");
include_once (PATH_TO_ROOT.'CRM/class/class.logger.php');
$Logger = new LoggerX($db);
ini_set("display_errors", "0");

setlocale(LC_ALL, 'sl_SI.UTF-8');


$defaulTable = '_visitStatistic';
function getPersentation($db, $projectId, $dan, $ura, $cati_location){
	$sql = "SELECT proj_name FROM ".DB_WARPIT_WEBCATI_BASE.".`_Projects` WHERE id = ".$projectId;
	$res = $db->SQLexecute($sql);
	$dat = $db->fetchAssoc($res);
	$ProjectName = $dat['proj_name'];
	
	$sql = "SELECT * FROM ".DB_WARPIT_WEBCATI.".`".$ProjectName."_language` WHERE `id_question` IN ( 9, 10 ) AND `id_text_type` = 3";
	$res = $db->SQLexecute($sql);
	while($row = $db->fetchAssoc($res)){
		$lang[] = explode("/", $row['lngText']);
	}
	foreach ($lang[0] as $day) {
		$tmp = explode(" ", $day);
		$days[$tmp[0]]['label'] = str_replace($tmp[0]." ", "", $day);	
	}
	foreach ($lang[1] as $t) {
		$tmp = explode(" ", $t);
		$time[$tmp[0]]['label'] = str_replace($tmp[0]." ", "", $t);
	}
	$sql = "SELECT presentation_location FROM ".WARPIT_CRM."._PresentatorPermissions WHERE cati_location = ".$cati_location." LIMIT 1";
	$res = $db->SQLexecute($sql);
	$dat = $db->fetchAssoc($res);
	$cati_location = $dat['presentation_location'];
	return $days[$dan]['label'].", ".$time[$ura]['label']. ", ".$cati_location;
}
function getDateFromString($date, $configMonths){
    $t = explode(" ", $date);
    $newDate = array();
    foreach ($t as $key => $value) {        
        if(!is_numeric($value)){
            $newDate[] = $configMonths[$value];
            //$newDate[] = $value;
        }else{
            $newDate[] = str_pad(trim($value,"."), 2, "0", STR_PAD_LEFT);
        }
    }
    //print_r($newDate);
    return implode(".",$newDate);
    
}
if($_POST['action'] == 'getPerson'){
        
    $sql = "SELECT * FROM ".WARPIT_CRM.".". $defaulTable . " WHERE id=" . $_POST['personId'];
    $res = $db->SQLexecute($sql);
    $result['success'] = TRUE;
    $result['results'] = $db->NumRows($res);
    $result['data'] = $db->fetchAssoc($res);
    print json_encode($result);
    exit;
}
function array_column($array, $column, $id, $id2)
{
    $ret = array();
    foreach ($array as $row) $ret[$row[$id]."|".$row[$id2]] = $row[$column];
    return $ret;
}
if($_POST['action'] == 'searchPerson')
{
    $searchString = StringChecker::Check($_POST['query']);
    $date = StringChecker::Check($_POST['date']);
    
    //$sql = "SELECT id, CONCAT_WS(' ', firstname, surname) as value FROM " . WARPIT_CRM . "." .$defaulTable . " WHERE firstname != '' AND surname != '' AND CONCAT(firstname, ' ', surname) LIKE '%".StringChecker::Check($searchString)."%' OR CONCAT (surname, ' ', firstname) LIKE '%".StringChecker::Check($searchString)."%' ORDER BY firstname ASC";
    
    $sql = "SELECT DISTINCT(id_project) FROM " . WARPIT_CRM . "." .$defaulTable;
//    $sql .= " WHERE id_project = 25";
    $res = $db->SQLexecute($sql);
    $days = array();
    while($row = $db->fetchAssoc($res)){
        $sql = "SELECT proj_name FROM ".DB_WARPIT_WEBCATI_BASE.".`_Projects` WHERE id = ".$row['id_project'];
        $rez = $db->SQLexecute($sql);
        $dat = $db->fetchAssoc($rez);
        $ProjectName = $dat['proj_name'];
        
        $sql = "SELECT * FROM ".DB_WARPIT_WEBCATI.".`".$ProjectName."_language` WHERE `id_question` IN ( 9 ) AND `id_text_type` = 3";
        $rez = $db->SQLexecute($sql);
        $lang = array();
        while($zad = $db->fetchAssoc($rez)){
            $lang[] = explode("/", $zad['lngText']);
        }
        foreach ($lang[0] as $day) {
            $tmp = explode(" ", $day);
            $l = $tmp[2]." ".$tmp[3]." ".$tmp[4];
            $days[] = array("date" => getDateFromString($l, $configMonths), 'dayId' => $tmp[0], 'projectId' => $row['id_project']);   
        }
    }
    $dateArray = array_column($days, "date", "projectId", "dayId");
    
    $filter[] = "firstname != ''";
    $filter[] = "AND surname != ''";
    $sql = "SELECT 	id, 
                    id_project,
                    dan,
    				firstname AS ime, 
    				surname AS priimek, 
    				address AS naslov, 
    				city AS mesto, 
    				post_code AS posta, 
    				nacin_pristopa,
    				order_status,
    				order_reason
    			FROM " . WARPIT_CRM . "." .$defaulTable. " 
			WHERE ";
	if(!empty($searchString)){
	    $filter[] = "AND (CONCAT(firstname, ' ', surname) LIKE '%".$searchString."%'";
        $filter[] = "OR CONCAT(surname, ' ', firstname) LIKE '%".$searchString."%'";
        $filter[] = "OR address LIKE '%".$searchString."%'";
        $filter[] = "OR phone1 LIKE '%".$searchString."%')";
        
	} if(!empty($date)){
            $found = array_search($date, $dateArray);
            if($found !== FALSE){
                $keys = explode("|", $found);
                $filter[] = "AND id_project = ".$keys[0]." AND dan = ".$keys[1];
            }
        }
    $sql .= implode(" ", $filter);
    
	$sql .= " ORDER BY firstname ASC";
    //echo "/*$sql*/";

	$rows = $sql;
	$sql.= " LIMIT ".$_POST['start'].", ".$_POST['limit'];
    $res = $db->SQLexecute($sql);
    while($row = $db->fetchAssoc($res)){    

    	if($row['nacin_pristopa'] > 0 && $row['order_status'] != 2){
    		$row['color'] = "red";
    	} elseif($row['order_reason'] == 5){
    		$row['color'] = "gray";
    	}
		
    	$sql = "SELECT id FROM ".WARPIT_CRM."._customerActivities WHERE customerId = ".$row['id']." AND activityId = 5 AND deleted = 0";
    	if($db->NumRows($db->SQLexecute($sql)) > 0){
    		$row['color'] = "green";
    	}
        $row['datum_pristopa'] = $dateArray[$row['id_project']."|".$row['dan']];
        $data[] = $row;
    }
    $result['success'] = TRUE;
    $result['results'] = $db->NumRows($db->SQLexecute($rows));
    $result['rows'] = $data != null ? $data : "";
    print json_encode($result);
    exit;
}else if($_POST['action'] == "getOrders"){
	$id = StringChecker::Check($_POST['userId']);
	$sql = "SELECT p.product_name as order_item, v.product_amount as order_amount, v.datum_dostave as order_date
			FROM ".WARPIT_CRM.".".$defaulTable." as v JOIN ".WARPIT_CRM."._Products as p ON p.id = v.article_id WHERE v.id = ".$id;
	$res = $db->SQLexecute($sql);
	while($row = $db->fetchAssoc($res)){
		$row['order_date'] = date_format(new DateTime($zad['order_date']),"d.m.Y");
		$data[] = $row;
	}
	$result['success'] = TRUE;
    $result['results'] = sizeof($data);
    $result['rows'] = $data != null ? $data : "";
    echo json_encode($result);
    exit;
}else if($_POST['action'] == "getCompanions"){
	//ini_set("display_errors",1);
	$id = StringChecker::Check($_POST['userId']);
	$sql = "SELECT id_tel, id_sub_tel FROM ".WARPIT_CRM.".". $defaulTable . " WHERE id=" . $id;
	$res = $db->SQLexecute($sql);
	while($row = $db->fetchAssoc($res)){
		$row['name'] = "Alone";
		if($row['id_sub_tel'] != 0){
			$sql = "SELECT id, CONCAT_WS(' ', firstname, surname) as name, id_project, cati_location, dan, ura FROM ".WARPIT_CRM.".". $defaulTable . " WHERE id_tel=" . $row['id_sub_tel'];
			$raz = $db->SQLexecute($sql);
			$dat = $db->fetchAssoc($raz);
			$pres = getPersentation($db, $dat['id_project'], $dat['dan'], $dat['ura'], $dat['cati_location']);
			$data[] = array("id" => $dat['id'], "name" => $dat['name'], "presentation" => $pres);
		}else{
			$sql = "SELECT id, CONCAT_WS(' ', firstname, surname) as name, id_project, cati_location, dan, ura FROM ".WARPIT_CRM.".". $defaulTable . " WHERE id_sub_tel=" . $row['id_tel'];
			$raz = $db->SQLexecute($sql);
			if($db->NumRows($raz) > 0){
				while($dat = $db->fetchAssoc($raz)){
					$pres = getPersentation($db, $dat['id_project'], $dat['dan'], $dat['ura'], $dat['cati_location']);
					$data[] = array("id" => $dat['id'], "name" => $dat['name'], "presentation" => $pres);	
				}
			}
		}
	}
	$result['success'] = TRUE;
    $result['results'] = sizeof($data);
    $result['rows'] = $data != null ? $data : "";
    print json_encode($result);
    exit;
}elseif($_POST['action'] == "deleteActivity"){
	$id = StringChecker::Check($_POST['id']);
	
	$sql = "SELECT * FROM ".WARPIT_CRM."._customerActivities WHERE id = ".$id;
	
	$res = $db->SQLexecute($sql);
	$row = $db->fetchAssoc($res);
	$fileLocation = str_replace("../", "",$row['fileLocation']);
	$temp = explode("/",strrev($fileLocation),2);
	$dirLocation = strrev($temp[1]);
	if(file_exists(PATH_TO_ROOT.$fileLocation)){
		rename(PATH_TO_ROOT.$fileLocation, PATH_TO_ROOT.$dirLocation."/".$id."_deleted_by_".$_SESSION['UserInfo']['id']."_".strrev($temp[0]));
		
	}
	$sql = "UPDATE ".WARPIT_CRM."._customerActivities SET deleted = 1, fileLocation = '".$dirLocation."/".$id."_deleted_by_".$_SESSION['UserInfo']['id']."_".strrev($temp[0])."' WHERE id=".$id;

	$db->SQLexecute($sql);
	$Logger->Log('activities', 'delete-activity', 'all', 
				  ".WARPIT_CRM.".".$table.", '', StringChecker::Check($sql));
	
	/*
	$files = @scandir(PATH_TO_ROOT.$dirLocation);
	if(count($files) <= 2){
		rmdir(PATH_TO_ROOT.$dirLocation);
	}*/
	
	
	echo "{success: true}";
}
?>