<?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.logger.php');
include_once (PATH_TO_ROOT."CRM/class/class.checkString.php");
include_once (PATH_TO_ROOT."CRM/class/class.Permissions.php");

$permissions = new Permissions($db);

$Logger = new LoggerX($db);

include_once(PATH_TO_ROOT."CRM/class/class.Language-Pack.php");
$LangPack = new LanguagePack($db, $aLanguage);

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

if($_POST['action'] == "update"){
	$sql = "UPDATE ".WARPIT_CRM."._visitStatistic SET ".StringChecker::Check($_POST['field'])." = '".StringChecker::Check($_POST['value'])."' WHERE id = ".StringChecker::Check($_POST['id']);
	$Logger->Log('sprememba_'.StringChecker::Check($_POST['field']), 'single-field-update', StringChecker::Check($_POST['field']), WARPIT_CRM."._visitStatistic", StringChecker::Check($_POST['oldValue']), addslashes($sql));
	$db->SQLexecute($sql);
	
	if($_POST['value'] == 1){
		$sql = "INSERT INTO ".WARPIT_CRM."._statusLog (log_type, log_user, id_order, status_date, status_state, status_comment) 
				VALUES (1, ".intval($_SESSION['UserInfo']['id']).", '".StringChecker::Check($_POST['id'])."', NOW(), '".StringChecker::Check($_POST['value'])."', 'Naročeno')";
		$Logger->Log('dodajanje_v_statusLog', 'multiple-filed-insert', 'log_type, log_user, id_order, status_date, status_state, status_comment', 
		  		  WARPIT_CRM."._statusLog", " ", addslashes($sql));	
		$db->SQLexecute($sql);
		
		$sql = "UPDATE  ".WARPIT_CRM."._visitStatistic SET order_status = ".StringChecker::Check($_POST['value']).", documentModify = NOW() WHERE id = ".StringChecker::Check($_POST['id']);
		$Logger->Log('sprememba_statusa_naročila', 'single-field-update', 'order_status', WARPIT_CRM."._visitStatistic", StringChecker::Check($_POST['oldValue']), addslashes($sql));
		$db->SQLexecute($sql);
	}
	
	echo "{ success : true, sql: '".$sql."'}";

} elseif($_POST['action'] == "get"){
	
	$filter[] = " order_status = 2";
	$filter[] = " AND order_reason = 5";
	($_POST['time_to'] != null ? $filter[] = " AND a.datum_dostave <= STR_TO_DATE('".StringChecker::Check($_POST['time_to'])."', '%d.%m.%Y')" : "")." ";
	($_POST['time_from'] != null ? $filter[] = " AND a.datum_dostave >= STR_TO_DATE('".StringChecker::Check($_POST['time_from'])."', '%d.%m.%Y')" : "")." ";
	$filter[] = " ".($_POST['ime'] != null ? " AND a.firstname LIKE '%".StringChecker::Check($_POST['ime'])."%'" : "")." ";
	$filter[] = " ".($_POST['priimek'] != null ? " AND a.surname LIKE '%".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('".$_POST['dorder_date_from']."', '%d.%m.%Y')" : "")." ";
	$filter[] = " ".($_POST['email'] != null ? " AND a.email LIKE '%".StringChecker::Check($_POST['email'])."%'" : "")." ";
	$filter[] = " ".($_POST['kraj'] != null ? " AND a.city LIKE '%".StringChecker::Check($_POST['kraj'])."%'" : "")." ";
	$filter[] = " ".($_POST['naslov'] != null ? " AND a.address LIKE '%".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 ".StringChecker::Check(WARPIT_CRM)."._Products WHERE product_code LIKE '%".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.order_status = '".StringChecker::Check($_POST['order_status'])."'" : "")." ";
	$filter[] = " ".($_POST['postna_st'] != null ? " AND a.post_code LIKE '%".StringChecker::Check($_POST['postna_st'])."%'" : "")." ";
	$filter[] = " ".($_POST['tel1'] != null ? " AND a.phone1 LIKE '%".StringChecker::Check($_POST['tel1'])."%'" : "")." ";
	$filter[] = " ".($_POST['tel2'] != null ? " AND a.phone2 LIKE '%".StringChecker::Check($_POST['tel2'])."%'" : "")." ";
	$filter[] = " ".($_POST['tp_city'] != null ? " AND b.presentation_location LIKE '%".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 ) {
		if($permissions->hasCRMPermission(31)){
			$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)." LIMIT ". StringChecker::Check($_POST['start']). ", ".StringChecker::Check($_POST['limit']);
			//paging
			$row = "SELECT a.*, b.product_name  FROM ".WARPIT_CRM."._visitStatistic as a 
						LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id ";
			$row .= " 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
						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(" ",$filter)." LIMIT ". StringChecker::Check($_POST['start']). ", ".StringChecker::Check($_POST['limit']);
			//paging
			$row = "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(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']."))";			
			$row .= " AND ".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)." LIMIT ". StringChecker::Check($_POST['start']). ", ".StringChecker::Check($_POST['limit']);	
		//paging
		$row = "SELECT a.*, b.product_name  FROM ".WARPIT_CRM."._visitStatistic as a 
				LEFT JOIN ".WARPIT_CRM."._Products as b ON a.article_id = b.id";
		$row .= " WHERE ".implode(" ",$filter);	
	}
	
	//echo "/*".$sql."*/";
	
	$res = $db->SQLexecute($sql);
	while ($zad = $db->fetchAssoc($res)) {
			
		//pridobitev ekipe
		$sqlTeam = "SELECT a.Firstname, a.Surname, b.idLab
					FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer a, ".DB_WARPIT_WEBCATI_BASE."._groups b
					WHERE b.id = (
						SELECT presentation_team
					        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']."
					)
					AND a.id IN (
						SELECT assistant_id
					        FROM ".WARPIT_CRM."._visitStatistic
					        WHERE id_project = ".$zad['id_project']."
					        AND dan = ".$zad['dan']."
					        AND ura = ".$zad['ura']."
					        AND cati_location = ".$zad['cati_location']."
					)";
		$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['id'] = $zad['id'];
		$tmpData['order_status'] = $zad['order_status'];
		$tmpData['tip_narocila'] = $zad['tip_narocila'];
		$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'] = "	".$LangPack->GetLabel("CRM_delivery_grid", "purchaser_id","ID").": ".$zad['id']."<br />
										".$LangPack->GetLabel("CRM_delivery_grid", "purchaser_name","Ime").": ".$zad['firstname']." ".$zad['surname']."<br />
										".$LangPack->GetLabel("CRM_delivery_grid", "purchaser_post_code","Pošta").": ".$zad['post_code']."<br />
										".$LangPack->GetLabel("CRM_delivery_grid", "purchaser_city","Mesto").": ".$zad['city']."<br />
										".$LangPack->GetLabel("CRM_delivery_grid", "purchaser_naslov","Naslov").": ".$zad['address']."<br />
										".$LangPack->GetLabel("CRM_delivery_grid", "purchaser_phone_1","Tel 1").": ".$zad['phone1']."<br />
										".$LangPack->GetLabel("CRM_delivery_grid", "purchaser_phone_2","Tel 2").": ".$zad['phone2']."<br />
									  ";
		$tmpData['delivery_data_customer'] = "	".$LangPack->GetLabel("CRM_delivery_grid", "customer_name","Ime").": ".$zad['firstname']." ".$zad['surname']."<br />												
												".$LangPack->GetLabel("CRM_delivery_grid", "customer_address","Naslov").": ".$zad['address']."<br />
												".$LangPack->GetLabel("CRM_delivery_grid", "customer_post_code","Pošta").": ".$zad['delivery_post_code']."<br />
												".$LangPack->GetLabel("CRM_delivery_grid", "customer_city","Kraj").": ".$zad['delivery_city']."<br />
												".$LangPack->GetLabel("CRM_delivery_grid", "customer_phone","Tel").": ".$zad['delivery_phone']."
											  ";
		$tmpData['delivery_data_company'] = "	".$LangPack->GetLabel("CRM_delivery_grid", "company_name","Naziv podjetja").": ".$zad['company_delivery_title']."<br />												
												".$LangPack->GetLabel("CRM_delivery_grid", "company_tax_number","Davčna št").": ".$zad['company_delivery_tax_number']."<br />
												".$LangPack->GetLabel("CRM_delivery_grid", "company_address","Naslov").": ".$zad['company_delivery_address']."<br />
												".$LangPack->GetLabel("CRM_delivery_grid", "company_post_code","Pošta").": ".$zad['company_delivery_post_code']."<br />
												".$LangPack->GetLabel("CRM_delivery_grid", "company_city","Kraj").": ".$zad['company_delivery_city']."<br />
												".$LangPack->GetLabel("CRM_delivery_grid", "company_phone","Tel").": ".$zad['company_delivery_phone']."
											  ";
		if ($zad['avans_amount']){
            $tmpData['avans_amount'] = $zad['avans_amount']." EUR "; 
            $tmpData['avans_credit_amount'] = $zad['avans_credit_amount']." EUR ";
        }else {
            $tmpData['avans_amount'] = " / ";
            $tmpData['avans_credit_amount'] = " / ";
        }
		if($zad['tip_narocila'] == 5){
            $tmpData['product_price'] = round($zad['product_amount'] * ($zad['product_price'] - $zad['deposit_amount']),2)." ".MONEY_VALUE;
        }else{
          $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_delivery_grid", "change_history_date_label","datum")."</td>
										  <td style='padding:2px; color:white'>".$LangPack->GetLabel("CRM_delivery_grid", "change_history_status_label","sprememba")."</td>
										  <td style='padding:2px; color:white'>".$LangPack->GetLabel("CRM_delivery_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'] = $db->NumRows($db->SQLexecute($row));
	$results['rows'] = $data != null ? $data : '';
	echo json_encode($results);
}
?>