<?php

include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."CRM/_CRM_config.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once(PATH_TO_ROOT."CRM/class/class.checkString.php");

if($_POST['action'] == "filter"){
	
	$filter[] = " order_status > 0";
	$filter[] = " ".($_POST['ime'] != null ? " AND a.firstname = '".StringChecker::Check($_POST['ime'])."'" : "")." ";
	$filter[] = " ".($_POST['priimek'] != null ? " AND a.surname = '".StringChecker::Check($_POST['priimek'])."'" : "")." ";
	$filter[] = " ".($_POST['dorder_date_to'] != null ? " AND a.datum_dostave <= STR_TO_DATE('".StringChecker::Check($_POST['dorder_date_to'])."', '%d.%m.%Y')" : "")." ";
	$filter[] = " ".($_POST['dorder_date_from'] != null ? " AND a.datum_dostave >= STR_TO_DATE('".StringChecker::Check($_POST['dorder_date_from'])."', '%d.%m.%Y')" : "")." ";
	$filter[] = " ".($_POST['email'] != null ? " AND a.email = '".StringChecker::Check($_POST['email'])."'" : "")." ";
	$filter[] = " ".($_POST['kraj'] != null ? " AND a.city = '".StringChecker::Check($_POST['kraj'])."'" : "")." ";
	$filter[] = " ".($_POST['naslov'] != null ? " AND a.address = '".StringChecker::Check($_POST['naslov'])."'" : "")." ";
	$filter[] = " ".($_POST['order_article_amount'] != null ? " AND a.product_amount = '".StringChecker::Check($_POST['order_article_amount'])."'" : "")." ";
	$filter[] = " ".($_POST['order_article_price'] != null ? " AND a.product_price = '".StringChecker::Check($_POST['order_article_price'])."'" : "")." ";
	$filter[] = " ".($_POST['order_article_code'] != null ? " AND a.article_id = (SELECT id FROM ".WARPIT_CRM."._Products WHERE product_code = '".StringChecker::Check($_POST['order_article_code'])."')" : "")." ";
	$filter[] = " ".($_POST['order_type'] != null ? " AND a.tip_narocila = '".StringChecker::Check($_POST['order_type'])."'" : "")." ";
	$filter[] = " ".($_POST['order_status'] != null ? " AND a.tip_narocila = '".StringChecker::Check($_POST['order_type'])."'" : "")." ";
	$filter[] = " ".($_POST['postna_st'] != null ? " AND a.post_code = '".StringChecker::Check($_POST['postna_st'])."'" : "")." ";
	$filter[] = " ".($_POST['tel1'] != null ? " AND a.phone1 = '".StringChecker::Check($_POST['tel1'])."'" : "")." ";
	$filter[] = " ".($_POST['tel2'] != null ? " AND a.phone2 = '".StringChecker::Check($_POST['tel2'])."'" : "")." ";
	$filter[] = " ".($_POST['tp_city'] != null ? " AND b.presentation_location = '".StringChecker::Check($_POST['tp_city'])."'" : "")." ";
	$filter[] = " ".($_POST['tp_date'] != null ? " AND b.presentation_date = '".StringChecker::Check($_POST['tp_date'])."'" : "")." ";
	//$filter[] = " ".($_POST['tp_name'] != null ? " AND b.presentation_date = '".$_POST['tp_name']."'" : "")." "; ???
	$filter[] = " ".($_POST['tp_time'] != null ? " AND b.presentation_time = '".StringChecker::Check($_POST['tp_time'])."'" : "")." ";
	
	if ( $_SESSION['UserInfo']['user_type'] == 0 ) {
		$sql = "SELECT a.*, b.product_name  FROM ".WARPIT_CRM."._visitStatistic as a 
					LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id
					WHERE CONCAT(dan,'-',ura,'-',cati_location) IN 
						(SELECT CONCAT(cati_day,'-',cati_hour,'-',cati_location) FROM ".WARPIT_CRM."._PresentatorPermissions 
							WHERE presentation_team IN ( SELECT id_group FROM  ".DB_WARPIT_WEBCATI_BASE."._group_link WHERE id = ".$_SESSION['UserInfo']['id']." AND id_type=0 ) )	";
		$sql .= " WHERE ".implode(" ", $filter);		
	} else {
	
		$sql = "SELECT a.*, b.product_name  FROM ".WARPIT_CRM."._visitStatistic as a 
					LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id ";
		$sql .= " WHERE ".implode(" ",$filter);
	}
	
	$res = $db->SQLexecute($sql);
	while ($zad = $db->fetchAssoc($res)) {
		
		//pridobitev datuma prezentacije in datuma
		$sql1 = "SELECT * FROM ".WARPIT_CRM."._PresentatorPermissions WHERE id_project = ".$zad['id_project']."";
		$res1 = $db->SQLexecute($sql1);
		$zad1 = $db->fetchAssoc($res1);
		$tmpDate = new DateTime($zad['documentModify']);
		$currentDate = date_format($tmpDate, 'd.m.Y');
		$tmpPresentationDate = new DateTime($zad1['presentation_date']);
		$PresentationDate = date_format($tmpPresentationDate, 'd.m.Y');
		
		//pridobitev ekipe
		if($zad['order_creator_id']){
			$t = explode("_",$zad['order_creator_id']);
			if($t[0] == "i"){
				$sqlTeam = "SELECT a.Firstname, a.Surname, b.idLab
							FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer a
							LEFT
							JOIN ".DB_WARPIT_WEBCATI_BASE."._group_link gl ON gl.id = a.id
							LEFT JOIN ".DB_WARPIT_WEBCATI_BASE."._groups b ON gl.id_group = b.id
							WHERE a.id = '".$t[1]."'";
			}elseif($t[0] == "u"){
				$sqlTeam = "SELECT Firstname, Surname, 'Administrator' as idLab
							FROM ".DB_WARPIT_MAIN."._user
							WHERE id = '".$t[1]."'";
			}
			
			$resTeam = $db->SQLexecute($sqlTeam);
			$zadTeam = $db->fetchAssoc($resTeam);
		}
		
		//pridobitev lokacije
		$sqlLocation = "SELECT *
				        FROM ".WARPIT_CRM."._PresentatorPermissions
				        WHERE id_project = ".$zad['id_project']."
				        AND cati_day = ".$zad['dan']."
				        AND cati_hour = ".$zad['ura']."
						AND cati_location = ".$zad['cati_location'];
		
		$resLocation = $db->SQLexecute($sqlLocation);
		$zadLocation = $db->fetchAssoc($resLocation);
		$tmpDate = new DateTime($zadLocation['presentation_date']);
		$locationDate = date_format($tmpDate, 'd.m.Y');
		
		$tmpData = array();
		$tmpData['presentation_date'] = $PresentationDate;
		$tmpData['id'] = $zad['id'];
		$tmpData['current_date'] = $currentDate;
		$tmpData['order_status'] = $zad['order_status'];
		$tmpData['dan'] = $zad['dan'];
		$tmpData['ura'] = $zad['ura'];
		$tmpData['projectId'] = $zad['id_project'];
		$tmpData['telId'] = $zad['id_tel'];
		$tmpData['cati_location'] = $zad['cati_location'];
		$tmpData['dogodek'] = $zad['dogodek'];
		$tmpData['location_data'] = "".$zadLocation['presentation_location']."<br />".$zadLocation['presentation_city']."<br />".$locationDate." ".substr($zadLocation['presentation_time'], 0, 5)."";
		$tmpData['team_data'] = "".$zadTeam['idLab']."<br />".$zadTeam['Firstname']." ".$zadTeam['Surname']."";
		$tmpData['purchaser_data'] = "	ID: ".$zad['id']."<br />
										ime: ".$zad['firstname']." ".$zad['surname']."<br />
										posta: ".$zad['post_code']."<br />
										mesto: ".$zad['city']."<br />
										naslov: ".$zad['address']."<br />
										tel 1: ".$zad['phone1']."<br />
										tel 2: ".$zad['phone2']."<br />
									  ";
		$tmpData['delivery_data_customer'] = "	ime: ".$zad['firstname']." ".$zad['surname']."<br />												
												naslov: ".$zad['address']."<br />
												posta: ".$zad['delivery_post_code']."<br />
												kdaj: ".$zad['delivery_city']."<br />
												tel: ".$zad['delivery_phone']."
											  ";
		$tmpData['delivery_data_company'] = "	naziv podjetja: ".$zad['company_delivery_title']."<br />												
												davčna št: ".$zad['company_delivery_tax_number']."<br />
												naslov: ".$zad['company_delivery_address']."<br />
												pošta: ".$zad['company_delivery_post_code']."<br />
												kraj: ".$zad['company_delivery_city']."<br />
												tel: ".$zad['company_delivery_phone']."
											  ";
		if ($zad['avans_amount']) $tmpData['deposit'] = $zad['avans_amount']." EUR "; else $tmpData['deposit'] = " / ";
		$tmpData['product_price'] = round($zad['product_amount'] * $zad['product_price'],2)." ".MONEY_VALUE;
		$tmpData['purchase_data'] =  $zad['product_amount']." x ".$zad['product_name'];
		
		
		//prodobitev podatkov z _statusLog 
		$sql1 = "SELECT * FROM ".WARPIT_CRM."._statusLog WHERE id_order='".$zad['id']."' ORDER BY status_date";
		$res1 = $db->SQLexecute($sql1);
		
		if ($db->NumRows($res1)) {
			
			$tmpData['status_history'] = "<table border=0><tr bgcolor=#757575><td style='padding:2px; color:white '>".$LangPack->GetLabel("CRM_order_grid", "change_history_date_label","datum")."</td>
										  <td style='padding:2px; color:white'>".$LangPack->GetLabel("CRM_order_grid", "change_history_status_label","sprememba")."</td>
										  <td style='padding:2px; color:white'>".$LangPack->GetLabel("CRM_order_grid", "change_history_note_label","opomba")."</td></tr>";
			
			while ($zad1 = $db->fetchAssoc($res1)) {
				$x = explode(" ",$zad1['status_date']);
				$t = explode("-",$x[0]);
				$date = $t[2].".".$t[1].".".$t[0];
				
				if($zad1['status_state'] == 3){
					$tmpChangedDate = new DateTime($zad1['status_changed_date']);
					$changedDate = date_format($tmpChangedDate, 'd.m.Y');
					
					if($zad1['status_comment'] != ""){
						$tmpData['status_history'] .= "<tr><td style='padding:5px; '>".$date."</td><td style='padding:5px; '>".$stanje[$zad1['status_state']]."</td>
													   <td style='padding:5px; '>".$zad1['status_comment']."<br />Spremenjeno na: ".$changedDate."</td></tr>";	
						
					} else {
						$tmpData['status_history'] .= "<tr><td style='padding:5px; '>".$date."</td><td style='padding:5px; '>".$stanje[$zad1['status_state']]."</td>
													   <td style='padding:5px; '>Spremenjeno na: ".$changedDate."</td></tr>";
					}
						
				} else {
					$tmpData['status_history'] .= "<tr><td style='padding:5px; '>".$date."</td><td style='padding:5px; '>".$stanje[$zad1['status_state']]."</td><td style='padding:5px; '>".$zad1['status_comment']."</td></tr>";	
				}
			}
			$tmpData['status_history'] .= "</table>";
		}
		$data[] = $tmpData;
	}

	$results['success'] = true;
	$results['results'] = sizeof($data);
	$results['rows'] = $data != null ? $data : '';
	echo json_encode($results);
}
?>