<?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");
require_once (PATH_TO_ROOT."CRM/_lib/excel/PHPExcel.php");
include_once(PATH_TO_ROOT."CRM/class/class.Language-Pack.php");
include_once (PATH_TO_ROOT."CRM/class/class.Permissions.php");

//ini_set("display_errors",1);
$permissions = new Permissions($db);
$LangPack = new LanguagePack($db, $aLanguage);

$stanje[1] = $LangPack->GetLabel("CRM_order_grid", "state_ordered","Naročeno");
$stanje[2] = $LangPack->GetLabel("CRM_order_grid", "state_delivered","Dostava");
$stanje[3] = $LangPack->GetLabel("CRM_order_grid", "state_change_date","Sprememba_datuma");
$stanje[4] = $LangPack->GetLabel("CRM_order_grid", "state_storno_team","Storno ekipa");
$stanje[5] = $LangPack->GetLabel("CRM_order_grid", "state_storno_delivery","Storno dostava");
$stanje[6] = $LangPack->GetLabel("CRM_order_grid", "state_product_return","Vračilo izdelka");
$stanje[7] = $LangPack->GetLabel("CRM_order_grid", "state_credit_denial","Zavrnitev kredita");
$stanje[99] = $LangPack->GetLabel("CRM_order_grid", "state_reclamation","Reklamacija");

$formValues = json_decode($_GET['filterValues']);
$lastParams = json_decode($_GET['lastParams']);

$status = array();
//ini_set("display_errors",1);
foreach($formValues as  $key => $json){
	if($json == "on"){
		if($key != "disablePagging" || $key != "DeliveryOrOrderDate")
			array_push($status, $key);
	}elseif( $key == 'time_from'){
		$date_from = $json;
	}elseif( $key == 'time_to'){
		$date_to = $json;
	}elseif( $key == 'delivery'){
		$delivery_team = $json;
	}elseif( $key == 'ppid'){
		$ppid = $json;
	}elseif( $key == 'teams'){
		$teams_exp = $json;
	}	
}
$filter[] = " a.order_reason > 0";
//window filters
($lastParams->ime != null ? $filter[] = "  a.firstname LIKE '%".StringChecker::Check($lastParams->ime)."%'" : "");
($lastParams->priimek != null ? $filter[] = "  a.surname LIKE '%".StringChecker::Check($lastParam->priimek)."%'" : "");
($lastParams->naslov != null ? $filter[] = "  a.address LIKE '%".StringChecker::Check($lastParams->naslov)."%'" : "");
($lastParams->postna_st != null ? $filter[] = "  a.post_code LIKE '%".StringChecker::Check($lastParams->postna_st)."%'" : "");
($lastParams->kraj != null ? $filter[] = "  a.city LIKE '%".StringChecker::Check($lastParams->kraj)."%'" : "");
($lastParams->email != null ? $filter[] = "  a.email LIKE '%".StringChecker::Check($lastParams->email)."%'" : "");
($lastParams->tel1 != null ? $filter[] = "  a.phone1 = '".StringChecker::Check($lastParams->tel1)."'" : "");
($lastParams->tel2 != null ? $filter[] = "  a.phone2 = '".StringChecker::Check($lastParams->tel2)."'" : "");
($lastParams->order_article_amount != null ? $filter[] = "  a.product_amount = '".StringChecker::Check($lastParams->order_article_amount)."'" : "");
($lastParams->order_article_code != null ? $filter[] = "  b.product_code = '".StringChecker::Check($lastParams->order_article_code)."'" : "");
($lastParams->order_article_price != null ? $filter[] = "  a.product_price = '".StringChecker::Check($lastParams->order_article_price)."'" : "");
($lastParams->order_status != null ? $filter[] = "  a.order_status = '".StringChecker::Check($lastParams->order_status)."'" : "");
($lastParams->order_type != null ? $filter[] = "  a.tip_narocila = '".StringChecker::Check($lastParams->order_type)."'" : "");
($lastParams->dorder_date_from != null ? $filter[] = "  a.datum_dostave >= STR_TO_DATE('".StringChecker::Check($lastParams->dorder_date_from)."', '%d.%m.%Y')" : "");
($lastParams->dorder_date_to != null ? $filter[] = "  a.datum_dostave <= STR_TO_DATE('".StringChecker::Check($lastParams->dorder_date_to)."', '%d.%m.%Y')" : "");
($lastParams->d_ime != null ? $filter[] = "  a.delivery_name LIKE '%".StringChecker::Check($lastParams->d_ime)."%'" : "");
($lastParams->d_priimek != null ? $filter[] = "  a.delivery_surname LIKE '%".StringChecker::Check($lastParams->d_priimek)."%'" : "");
($lastParams->d_naslov != null ? $filter[] = "  a.delivery_address LIKE '%".StringChecker::Check($lastParams->d_naslov)."%'" : "");
($lastParams->d_postna_st != null ? $filter[] = "  a.delivery_post_code LIKE '%".StringChecker::Check($lastParams->d_postna_st)."%'" : "");
($lastParams->d_kraj != null ? $filter[] = "  a.delivery_city LIKE '%".StringChecker::Check($lastParams->d_kraj)."%'" : "");
(isset($teams_exp) ? $team = $teams_exp : $team = null);
//časovna omejitev
($date_to != null ? $filter[] = " a.datum_dostave <= STR_TO_DATE('".$date_to."', '%d.%m.%Y')" : "")." ";
($date_from != null ? $filter[] = " a.datum_dostave >= STR_TO_DATE('".$date_from."', '%d.%m.%Y')" : "")." ";
//izbrane vrste statusov
$state = array();
if(in_array('ordered', $status)) { array_push($state, 1); }
if(in_array('delivered', $status)) { array_push($state, 2); }
if(in_array('date_changed', $status)) { array_push($state, 3); }
if(in_array('storno_team', $status)) { array_push($state, 4); }
if(in_array('storno_delivery', $status)) { array_push($state, 5); }
if(in_array('product_return', $status)) { array_push($state, 6); }
if(in_array('credit_denial', $status)) { array_push($state, 7); }
if(in_array('reclamation', $status)) { array_push($state, 99); }
count($state) != 0 ? $filter[] = " a.order_status IN (".implode(",", $state).")" : "";

//izbrane vrste dostave
$delivery = array();
if(in_array('own_delivery', $status)) { array_push($delivery, 2); }
if(in_array('central_delivery', $status)) { array_push($delivery, 1); }
count($delivery) != 0 ? $filter[] = " a.tipDostave IN (".implode(",", $delivery).")" : "";
//izbrana vrsta dostave(combo)
($delivery_team != null ? $filter[] = " a.deliverer_id = d.id AND a.deliverer_id = ".$delivery_team : "")." ";
//filtriranje po ppid
($ppid != null ? $filter[] = "  a.id = ".$ppid."" : "")." ";
if( $_SESSION['UserInfo']['user_type'] == 0 ) {
	if(isset($team) && $team != null){
		$filter[] = " CONCAT(id_project,'-',ura,'-',dan,'-',cati_location) IN 
					(SELECT CONCAT(id_project,'-',cati_hour,'-',cati_day,'-',cati_location) 
					FROM ".WARPIT_CRM."._PresentatorPermissions WHERE presentation_team = ".$team.")";
	}
	if($permissions->hasCRMPermission(30) && isset($_GET['orders'])){
		$sql = "SELECT DISTINCT(a.id_project), a.*, b.product_name  FROM  ".WARPIT_CRM."._deliverer as d, ".WARPIT_CRM."._visitStatistic as a 
				LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id";
			$sql .= " WHERE ".implode(" AND ", $filter);
	}elseif($permissions->hasCRMPermission(31) && isset($_GET['delivery'])){
		$sql = "SELECT DISTINCT(a.id_project), a.*, b.product_name  FROM  ".WARPIT_CRM."._deliverer as d, ".WARPIT_CRM."._visitStatistic as a 
				LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id";
			$sql .= " WHERE ".implode(" AND ", $filter);
	}else{
		$sql = "SELECT DISTINCT(a.id_project), a.*, b.product_name  FROM  ".WARPIT_CRM."._deliverer as d, ".WARPIT_CRM."._visitStatistic as a
					LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id
					WHERE CONCAT(id_project,'-',ura,'-',dan,'-',cati_location) IN (SELECT CONCAT(id_project,'-',cati_hour,'-',cati_day,'-',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']."))";			
		
			$sql .= " AND ".implode(" AND ", $filter);
		//paging
	}
} else {
	if(isset($team) && $team != null){
		$filter[] = " CONCAT(id_project,'-',ura,'-',dan,'-',cati_location) IN 
					(SELECT CONCAT(id_project,'-',cati_hour,'-',cati_day,'-',cati_location) 
					FROM ".WARPIT_CRM."._PresentatorPermissions WHERE presentation_team = ".$team.")";
	}
	$sql = "SELECT DISTINCT(a.id_project), a.*, b.product_name  FROM  ".WARPIT_CRM."._deliverer as d, ".WARPIT_CRM."._visitStatistic as a 
			LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id";
	$sql .= " WHERE ".implode(" AND ", $filter);
}
$res = $db->SQLexecute($sql);

while ($zad = $db->fetchAssoc($res)){
	//break;
	//filtriranje po skupinah(combo)
	$filter = ($teams_exp != null) ? " presentation_team = ".$teams_exp." AND " : "";
	
	//pridobitev datuma prezentacije in datuma 
	$sql1 = "SELECT * FROM ".WARPIT_CRM."._PresentatorPermissions WHERE $filter 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['delivery_date'] = date_format(new DateTime($zad['datum_dostave']),"d.m.Y");
	$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'];
	$tmpData['deliverer_id'] = $zad['deliverer_id'];		
	
	//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(!empty($zad1['status_comment'])){
					$tmpData['status_history_no_HTML'][] = array("date" => $date, "stanje" => $stanje[$zad1['status_state']], "comment" => $zad1['status_comment']."<br />Spremenjeno na: ".$changedDate);
					$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_no_HTML'][] = array("date" => $date, "stanje" => $stanje[$zad1['status_state']], "comment" => "Spremenjeno na: ".$changedDate);
					$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_no_HTML'][] = array("date" => $date, "stanje" => $stanje[$zad1['status_state']], "comment" => $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']."</td></tr>";	
			}
		}
		$tmpData['status_history'] .= "</table>";
	}
	$data[] = $tmpData;
}

echo "<style>
		#table1 td {
			vertical-align: top;
		}
	  </style>";
echo "<table id='table1' border='1' width='90%' align='center'>";
echo "<tr>
		<th>".$LangPack->GetLabel("CRM_export", "location","Lokacija")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "team","Ekipa")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "buyer","Kupec")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "avans","avans")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "price","Cena")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "purchase_info","Podatki o nakupu")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "state","Status")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "change_history","Zgodovina sprememb")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "presentation_date","Datum prezentacije")."</th>
		<th>".$LangPack->GetLabel("CRM_export", "date","Datum")."</th>
	  </tr>";

foreach($data as $key => $value){
	echo "<tr>";
	echo "<td>".$value['location_data']."</td>";
	echo "<td>".$value['team_data']."</td>";
	echo "<td>".$value['purchaser_data']."</td>";
	echo "<td>".$value['deposit']."</td>";
	echo "<td width='100px'>".$value['product_price']."</td>";
	echo "<td>".$value['purchase_data']."</td>";
	
	switch ($value['order_status']){
		case 1:
			$e_state = "Naročeno";
			break;
		case 2:
			$e_state = "Dostavljeno";
			break;
		case 3:
			$e_state = "Sprememba datuma";
			break;
		case 4:
			$e_state = "Storno ekipa";
			break;
		case 5:
			$e_state = "Storno dostava";
			break;
		case 6:
			$e_state = "Vračilo izdelka";
			break;
		case 7:
			$e_state = "Zavrnitev kredita";
			break;
		case 99:
			$e_state = "Reklamacija";
			break;
	}
	echo "<td>".$e_state."</td>";
	
	$history = "/";
	if(count($value['status_history_no_HTML']) > 0){
		
		foreach ($value['status_history_no_HTML'] as $key1 => $value1) {
			$history = "<table>";
			$history.= "<tr><td>".$LangPack->GetLabel("CRM_order_grid", "change_history_date_label","datum").":</td><td>".$value1['date']."</td></tr>";
			$history.= "<tr><td>".$LangPack->GetLabel("CRM_order_grid", "change_history_status_label","sprememba").":</td><td>".$value1['stanje']."</td></tr>";
			$history.= "<tr><td>".$LangPack->GetLabel("CRM_order_grid", "change_history_note_label","opomba").":</td><td>".$value1['comment']."</td></tr>";
			$history.= "</table>";
		}
		if(count($value['status_history_no_HTML']) > 1){ $history.="<br><br>"; }	
	}
	
	echo "<td width='350'>".$history."</td>";
	echo "<td>".$value['presentation_date']."</td>";
	echo "<td>".$value['delivery_date']."</td>";
	echo "</tr>";
	
	//echo "datum: ".$value['presentation_date']."<br>";	
}

echo "</table>";
?>