<?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");

$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_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");

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", " ", addslashes($sql));
		$db->SQLexecute($sql);
	}
	
	echo "{ success : true, sql: '".$sql."'}";

} elseif($_POST['action'] == "get"){
	$filter[] = " 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')" : "")." ";
	($_POST['time_to_finance'] != null ? $filter[] = " AND a.add_payment_date <= STR_TO_DATE('".StringChecker::Check($_POST['time_to_finance'])."','%d.%m.%Y')" : "")." ";
	($_POST['time_from_finance'] != null ? $filter[] = " AND a.add_payment_date >= STR_TO_DATE('".StringChecker::Check($_POST['time_from_finance'])."', '%d.%m.%Y')" : "")." ";
	//izbrane vrste statusov
	$status = array();
	if($_POST['state_ordered'] == 'true' || $_POST['state_ordered'] == 1) { array_push($status, 1); }
	if($_POST['state_delivered'] == 'true' || $_POST['state_delivered'] == 1) { array_push($status, 2); }
	if($_POST['state_date_changed'] == 'true' || $_POST['state_date_changed'] == 1) { array_push($status, 3); }
	if($_POST['state_storno_team'] == 'true' || $_POST['state_storno_team'] == 1){ array_push($status, 4); }
	if($_POST['state_storno_delivery'] == 'true' || $_POST['state_storno_delivery'] == 1) { array_push($status, 5); }
	if($_POST['state_product_return'] == 'true' || $_POST['state_product_return'] == 1) { array_push($status, 6); }
	if($_POST['state_credit_denial'] == 'true' || $_POST['state_credit_denial'] == 1) { array_push($status, 7); }
	if($_POST['state_reclamation'] == 'true' || $_POST['state_reclamation'] == 1) { array_push($status, 99); }
	count($status) != 0 ? $filter[] = "AND a.order_status IN (".implode(",", $status).")" : "";
	//izbrane vrste dostave
	$delivery = array();
	if($_POST['storage_own_delivery'] == 'true' || $_POST['storage_own_delivery'] == 1) { array_push($delivery, 2); }
	if($_POST['storage_central_delivery'] == 'true' || $_POST['storage_central_delivery'] == 1) { array_push($delivery, 1); }
	count($delivery) != 0 ? $filter[] = "AND a.tipDostave IN (".implode(",", $delivery).")" : "";
	//izbrana vrsta dostave(combo)
	($_POST['delivery'] != null ? $filter[] = " AND a.deliverer_id = d.id AND a.deliverer_id = ".StringChecker::Check($_POST['delivery']) : "")." ";
	//filtriranje po ppid
	($_POST['ppid'] != null ? $filter[] = " AND a.id = ".StringChecker::Check($_POST['ppid'])."" : "")." ";
	
	//Window.serach-filter.php
	($_POST['ime'] != null ? $filter[] = " AND a.firstname LIKE '%".StringChecker::Check($_POST['ime'])."%'" : "");
	($_POST['priimek'] != null ? $filter[] = " AND a.surname LIKE '%".StringChecker::Check($_POST['priimek'])."%'" : "");
	($_POST['naslov'] != null ? $filter[] = " AND a.address LIKE '%".StringChecker::Check($_POST['naslov'])."%'" : "");
	($_POST['postna_st'] != null ? $filter[] = " AND a.post_code LIKE '%".StringChecker::Check($_POST['postna_st'])."%'" : "");
	($_POST['kraj'] != null ? $filter[] = " AND a.city LIKE '%".StringChecker::Check($_POST['kraj'])."%'" : "");
	($_POST['email'] != null ? $filter[] = " AND a.email LIKE '%".StringChecker::Check($_POST['email'])."%'" : "");
	($_POST['tel1'] != null ? $filter[] = " AND a.phone1 = '".StringChecker::Check($_POST['tel1'])."'" : "");
	($_POST['tel2'] != null ? $filter[] = " AND a.phone2 = '".StringChecker::Check($_POST['tel2'])."'" : "");
	($_POST['order_article_amount'] != null ? $filter[] = " AND a.product_amount = '".StringChecker::Check($_POST['order_article_amount'])."'" : "");
	($_POST['order_article_code'] != null ? $filter[] = " AND b.product_code = '".StringChecker::Check($_POST['order_article_code'])."'" : "");
	($_POST['order_article_price'] != null ? $filter[] = " AND a.product_price = '".StringChecker::Check($_POST['order_article_price'])."'" : "");
	($_POST['order_status'] != null ? $filter[] = " AND a.order_status = '".StringChecker::Check($_POST['order_status'])."'" : "");
	($_POST['order_type'] != null ? $filter[] = " AND a.tip_narocila = '".StringChecker::Check($_POST['order_type'])."'" : "");
	($_POST['dorder_date_from'] != null ? $filter[] = " AND a.datum_dostave >= STR_TO_DATE('".StringChecker::Check($_POST['dorder_date_from'])."', '%d.%m.%Y')" : "");
	($_POST['dorder_date_to'] != null ? $filter[] = " AND a.datum_dostave <= STR_TO_DATE('".StringChecker::Check($_POST['dorder_date_to'])."', '%d.%m.%Y')" : "");
	($_POST['d_ime'] != null ? $filter[] = " AND a.delivery_name LIKE '%".StringChecker::Check($_POST['d_ime'])."%'" : "");
	($_POST['d_priimek'] != null ? $filter[] = " AND a.delivery_surname LIKE '%".StringChecker::Check($_POST['d_priimek'])."%'" : "");
	($_POST['d_naslov'] != null ? $filter[] = " AND a.delivery_address LIKE '%".StringChecker::Check($_POST['d_naslov'])."%'" : "");
	($_POST['d_postna_st'] != null ? $filter[] = " AND a.delivery_post_code LIKE '%".StringChecker::Check($_POST['d_postna_st'])."%'" : "");
	($_POST['d_kraj'] != null ? $filter[] = " AND a.delivery_city LIKE '%".StringChecker::Check($_POST['d_kraj'])."%'" : "");
	
	$filter = array_unique($filter);
	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 .= " 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(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 ) )	";		
		$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);
	}
	
	$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
		$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['presentation_date'] = $PresentationDate;
		$tmpData['id'] = $zad['id'];
		$tmpData['current_date'] = $currentDate;
		$tmpData['order_status'] = $zad['order_status'];
		$tmpData['dan'] = $zad['dan'];
		$tmpData['add_payment_comment'] = $zad['add_payment_comment'];
		$tmpData['add_payment_date'] = $zad['add_payment_date'];
		$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'] = "	".$LangPack->GetLabel("CRM_order_grid", "purchaser_id","ID").": ".$zad['id']."<br />
										".$LangPack->GetLabel("CRM_order_grid", "purchaser_name","Ime").": ".$zad['firstname']." ".$zad['surname']."<br />
										".$LangPack->GetLabel("CRM_order_grid", "purchaser_post_code","Pošta").": ".$zad['post_code']."<br />
										".$LangPack->GetLabel("CRM_order_grid", "purchaser_city","Mesto").": ".$zad['city']."<br />
										".$LangPack->GetLabel("CRM_order_grid", "purchaser_naslov","Naslov").": ".$zad['address']."<br />
										".$LangPack->GetLabel("CRM_order_grid", "purchaser_phone_1","Tel 1").": ".$zad['phone1']."<br />
										".$LangPack->GetLabel("CRM_order_grid", "purchaser_phone_2","Tel 2").": ".$zad['phone2']."<br />
									  ";
		$tmpData['delivery_data_customer'] = "	".$LangPack->GetLabel("CRM_order_grid", "customer_name","Ime").": ".$zad['firstname']." ".$zad['surname']."<br />												
												".$LangPack->GetLabel("CRM_order_grid", "customer_address","Naslov").": ".$zad['address']."<br />
												".$LangPack->GetLabel("CRM_order_grid", "customer_post_code","Pošta").": ".$zad['delivery_post_code']."<br />
												".$LangPack->GetLabel("CRM_order_grid", "customer_city","Kraj").": ".$zad['delivery_city']."<br />
												".$LangPack->GetLabel("CRM_order_grid", "customer_phone","Tel").": ".$zad['delivery_phone']."
											  ";
		$tmpData['delivery_data_company'] = "	".$LangPack->GetLabel("CRM_order_grid", "company_name","Naziv podjetja").": ".$zad['company_delivery_title']."<br />												
												".$LangPack->GetLabel("CRM_order_grid", "company_tax_number","Davčna št").": ".$zad['company_delivery_tax_number']."<br />
												".$LangPack->GetLabel("CRM_order_grid", "company_address","Naslov").": ".$zad['company_delivery_address']."<br />
												".$LangPack->GetLabel("CRM_order_grid", "company_post_code","Pošta").": ".$zad['company_delivery_post_code']."<br />
												".$LangPack->GetLabel("CRM_order_grid", "company_city","Kraj").": ".$zad['company_delivery_city']."<br />
												".$LangPack->GetLabel("CRM_order_grid", "company_phone","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'] = $db->NumRows($db->SQLexecute($row));
	$results['rows'] = $data != null ? $data : '';
	echo json_encode($results);
}elseif($_POST['action'] == "submit"){
	$date = StringChecker::Check($_POST['date']);
	$comment = StringChecker::Check($_POST['comment']);
	$id = StringChecker::Check($_POST['id']);
	$sql = "UPDATE ".WARPIT_CRM."._visitStatistic SET add_payment_date = STR_TO_DATE ('".$date."','%d.%m.%Y'), add_payment_comment = '".$comment."' WHERE id=".$id;
	$db->SQLexecute($sql);
}
?>