<?php
session_start();
header('Content-Type: text/html; charset=utf-8');

include_once("../scripts/ClassLibs/ClassReport.php");

	$role = $_SESSION['role'];
	$id_user = $_SESSION['id'];
	$instance_id = $_SESSION['client_id'];
	$GMT = $_SESSION['gmt'];
	
	$dbset = json_decode($_SESSION['settingsApp'],true);
	$db = new mysqli ($dbset['DB']['DB_HOST'],$dbset['DB']['DB_USER'],$dbset['DB']['DB_PASS'],$dbset['DB']['DB_NAME']);
	$db2 = new mysqli ($dbset['DB']['DB_HOST'],$dbset['DB']['DB_USER'],$dbset['DB']['DB_PASS'],$dbset['DB']['DB_NAME']);
		
	$db->query("SET CHARSET SET utf8");
	$db->query("SET NAMES 'utf8'");
	if (mysqli_connect_errno()) {
		echo "Error connecting to DB";
		exit;
	}
	
	$db2->query("SET CHARSET SET utf8");
	$db2->query("SET NAMES 'utf8'");
	if (mysqli_connect_errno()) {
		echo "Error connecting to DB";
		exit;
	}

$table = "tms_gpsdata_fixed";
$ObjReportRun= new Report($table);
	
function getEstimateTimeFact($checkpointid){
	global $db2;
	global $instance_id;
	global $GMT;
	$data = array();
	
	$query = "SELECT
ADDTIME(e.`date`,'0 ".$GMT.":0:0') AS DATE_FACT
,(SELECT ADDTIME(e.`date`,'0 ".$GMT.":0:0') FROM tms_events e INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt WHERE e.ParamInt = ".$checkpointid." AND (e.EventType=4 OR e.EventType=13 OR e.EventType=14 OR e.EventType=15) ORDER BY e.`date` DESC LIMIT 1) AS INDATE_FACT
,(SELECT ADDTIME(e.`date`,'0 ".$GMT.":0:0') FROM tms_events e INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt WHERE e.ParamInt = ".$checkpointid." AND (e.EventType=5 OR e.EventType=18 OR e.EventType=19) ORDER BY e.`date` DESC LIMIT 1) AS OUTDATE_FACT
FROM tms_events e
INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt
WHERE e.ParamInt = ".$checkpointid."
ORDER BY e.`date` DESC LIMIT 1;";
	//echo $query;
	//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' QUERY = '.$query );
	if($stmt = $db2->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($DATE_FACT,$INDATE_FACT,$OUTDATE_FACT);	
	while($stmt->fetch()){
		$data[] = array(
			'DATE_FACT' => $DATE_FACT,
			'INDATE_FACT' => $INDATE_FACT,
			'OUTDATE_FACT' => $OUTDATE_FACT
		);
	}
	$stmt->close();
	//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' date = '.json_encode($data) );
	return $data;
	}else{ /*writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' execute date NULL ' );*/ return array('DATE_FACT' => '','INDATE_FACT' => '','OUTDATE_FACT' => ''); }
	}else{ /*writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' prepare date NULL ' );*/ return array('DATE_FACT' => '','INDATE_FACT' => '','OUTDATE_FACT' => ''); }
}
	
	
if(isset($_REQUEST['load'])){
	
	$total = 0;
	
	$start = isset($_REQUEST['start']) ? abs($_REQUEST['start']) : 0;
	$limit = isset($_REQUEST['limit']) ? abs($_REQUEST['limit']) : 30;
	
	$startdt = isset($_REQUEST['reportQues_startdt']) ? $_REQUEST['reportQues_startdt'] : date('Y-m-d');
	$enddt = isset($_REQUEST['reportQues_enddt']) ? $_REQUEST['reportQues_enddt'] : date('Y-m-d');
	
	$where = " AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59')";
	
	$query = "SELECT COUNT(*)
FROM `tms_checkpoint` c
LEFT JOIN `tms_facttrip` f ON f.`ID` = c.`FactTripID`
LEFT JOIN `tms_project` p ON p.`Project_ID` = f.`Project_Id`
LEFT JOIN `tms_car` tc ON f.`Car_ID`=tc.`Car_ID`
LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
LEFT JOIN `wm_car_info` wc ON wc.`imei` = td.`Login`
LEFT JOIN `wm_drivers` d ON d.`id` = wc.`driver_id`
LEFT JOIN `tms_speedometer_value` sv ON td.`ID` = sv.`DeviceId` AND DATE(sv.`Date`) = DATE(c.`EstimateTime`)
LEFT JOIN `tms_fuel_value` fv ON ( DATE(fv.`Date`) = DATE(c.`EstimateTime`) AND td.`ID` = fv.`DeviceId` )
WHERE p.`Client_ID` = ".$instance_id ." ".$where." AND c.`IsOrderPoint` = 1
ORDER BY c.ID, c.`OrderInTrip` ASC;";
	if ($stmt = $db->prepare($query)) 
	{
        $stmt->execute();
        $stmt->bind_result($total);
        $stmt->fetch();
        $stmt->close();
	}
	
	$query = "SELECT f.`ID` AS FID, c.`Id` AS CID,
wc.`car_name` AS Car, IF( (d.`name` IS NULL OR d.`name`='' ),wc.`car_name`,CONCAT_WS(' ',d.`name`,d.`surname`)) AS `Driver`,
-- d.`id` AS `DriverId`,
IF( (d.`id` IS NULL OR d.`id`=0 ),wc.`imei`,d.`id`) AS `DriverId`,
c.`Name`,c.`Code`,c.`ClientName`,c.`Address`,c.`PointState`,c.`OrderInTrip`,c.`Distance`,c.`Time`,c.`EstimateTime`,c.`DurationInPoint`,c.`DriverComments`,DATE_FORMAT(c.`StartWorkTime`,'%H:%i:%s') AS StartWorkTime,DATE_FORMAT(c.`EndWorkTime`,'%H:%i:%s') AS EndWorkTime
,ROUND((SELECT SUM(Cost) FROM `tms_checkpointcargo` WHERE State > 0 AND `CheckPoint_ID` = CID),2) AS `SumCargo`
,sv.`Start` AS `StartOdom`, sv.`End` AS `EndOdom`
,fv.`value` AS `FuelVal`
FROM `tms_checkpoint` c
LEFT JOIN `tms_facttrip` f ON f.`ID` = c.`FactTripID`
LEFT JOIN `tms_project` p ON p.`Project_ID` = f.`Project_Id`
LEFT JOIN `tms_car` tc ON f.`Car_ID`=tc.`Car_ID`
LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
LEFT JOIN `wm_car_info` wc ON wc.`imei` = td.`Login`
LEFT JOIN `wm_drivers` d ON d.`id` = wc.`driver_id`
LEFT JOIN `tms_speedometer_value` sv ON td.`ID` = sv.`DeviceId` AND DATE(sv.`Date`) = DATE(c.`EstimateTime`)
LEFT JOIN `tms_fuel_value` fv ON ( DATE(fv.`Date`) = DATE(c.`EstimateTime`) AND td.`ID` = fv.`DeviceId` )
WHERE p.`Client_ID` = ".$instance_id ." ".$where." AND c.`IsOrderPoint` = 1
GROUP BY c.`Id` ORDER BY c.ID, c.`OrderInTrip` ASC LIMIT ".$start.",".$limit.";";
	//echo $query;
	//AND f.`Closed` = 1
	if(!$stmt = $db->query($query)){
			//echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
			echo '{"total":"0","rows":[]}';
	}else{
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);)
			{
						if( $tmp['PointState'] == 0 ){
							//$cPointStateText = "2";
							$tmp['PointState'] = "Курьер в пути";
						}elseif($tmp['PointState'] == 1 || $tmp['PointState'] == 4){
							//$cPointStateText = "4";
							$tmp['PointState'] = "Заказ получен";
						}elseif($tmp['PointState'] == 2 || $tmp['PointState'] == 5 || $tmp['PointState'] == 6){
							//$cPointStateText = "5";
							$tmp['PointState'] = "Заказ отменен";
						}elseif($tmp['PointState'] == 3){
							//$cPointStateText = "6";
							$tmp['PointState'] = "Заказ принят частично";
						}
						
						$arrayTimeFact = getEstimateTimeFact( $tmp['CID'] );
						//print_r($arrayTimeFact);
						
						if(!$arrayTimeFact[0]['INDATE_FACT'] || $arrayTimeFact[0]['INDATE_FACT']==""){
							//$tmp['DELIVERY_DATE_FACT'] = $tmp['EstimateTime'];
							$tmp['DELIVERY_DATE_FACT'] = '';
						}else{
							$tmp['DELIVERY_DATE_FACT'] = $arrayTimeFact[0]['INDATE_FACT'];
						}
						
						if(!$arrayTimeFact[0]['OUTDATE_FACT'] || $arrayTimeFact[0]['OUTDATE_FACT']==""){
							//$edate = strtotime($tmp['EstimateTime']) + ($tmp['DurationInPoint'] * 60);
							//$tmp['DELIVERY_OUTDATE_FACT'] = date('Y-m-d H:i:s',$edate);
							$tmp['DELIVERY_OUTDATE_FACT'] = '';
						}else{
							$tmp['DELIVERY_OUTDATE_FACT'] = $arrayTimeFact[0]['OUTDATE_FACT'];
						}
						
						if((int)$tmp['EndOdom'] > (int)$tmp['StartOdom']){
							$tmp['DiffOdom'] = $tmp['EndOdom'] - $tmp['StartOdom'];
						}else{
							$tmp['DiffOdom'] = 0;
						}
						
						if(!$arrayTimeFact[0]['OUTDATE_FACT'] || $arrayTimeFact[0]['OUTDATE_FACT']==""){
							$tmp['InTime'] = 0;
						}else{
						$date_estim = date('Y-m-d',strtotime($tmp['EstimateTime']));
						if( strtotime($tmp['DELIVERY_OUTDATE_FACT']) > (strtotime($date_estim.' '.$tmp['EndWorkTime']) + 900) ){ //strtotime(date('Y-m-d')
							$tmp['InTime'] = 0;
						}else{
							$tmp['InTime'] = 1;
						}
						}
						
						
				
				$result[] = $tmp;
			}
			
			$stmt->close();
			echo '{"total":"'.$total.'","rows":'.json_encode($result).'}';
	}
	$db->close();
	exit;
}


if ($_POST['func']=='export_xls')
{
    $Title = 'Report';
    $Data = $_POST['ColumnsArray'];
	$startdt = $_POST['reportQues_startdt'];
	$enddt = $_POST['reportQues_enddt'];
	
	$Data = array();
	$where = " AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59')";
	$query = "SELECT f.`ID` AS FID, c.`Id` AS CID,
wc.`car_name` AS Car, IF( (d.`name` IS NULL OR d.`name`='' ),wc.`car_name`,CONCAT_WS(' ',d.`name`,d.`surname`)) AS `Driver`,d.`id` AS `DriverId`,
c.`Name`,c.`Code`,c.`ClientName`,c.`Address`,c.`PointState`,c.`OrderInTrip`,c.`Distance`,c.`Time`,c.`EstimateTime`,c.`DurationInPoint`,c.`DriverComments`,DATE_FORMAT(c.`StartWorkTime`,'%H:%i:%s') AS StartWorkTime,DATE_FORMAT(c.`EndWorkTime`,'%H:%i:%s') AS EndWorkTime
,(SELECT SUM(Cost) FROM `tms_checkpointcargo` WHERE State > 0 AND `CheckPoint_ID` = CID) AS `SumCargo`
,sv.`Start` AS `StartOdom`, sv.`End` AS `EndOdom`
,fv.`value` AS `FuelVal`
FROM `tms_checkpoint` c
LEFT JOIN `tms_facttrip` f ON f.`ID` = c.`FactTripID`
LEFT JOIN `tms_project` p ON p.`Project_ID` = f.`Project_Id`
LEFT JOIN `tms_car` tc ON f.`Car_ID`=tc.`Car_ID`
LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
LEFT JOIN `wm_car_info` wc ON wc.`imei` = td.`Login`
LEFT JOIN `wm_drivers` d ON d.`id` = wc.`driver_id`
LEFT JOIN `tms_speedometer_value` sv ON td.`ID` = sv.`DeviceId` AND DATE(sv.`Date`) = DATE(c.`EstimateTime`)
LEFT JOIN `tms_fuel_value` fv ON td.`ID` = fv.`DeviceId` AND DATE(sv.`Date`) = DATE(c.`EstimateTime`)
WHERE p.`Client_ID` = ".$instance_id ." ".$where." AND c.`IsOrderPoint` = 1
GROUP BY c.`Id` ORDER BY c.ID, c.`OrderInTrip` ASC;";
	echo $query;
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($FID, $CID, $Car, $Driver, $DriverId, $Name, $Code, $ClientName, $Address, $PointState, $OrderInTrip, $Distance, $Time, $EstimateTime, $DurationInPoint, $DriverComments, $StartWorkTime, $EndWorkTime, $SumCargo, $StartOdom, $EndOdom, $FuelVal);	
	$arr=array();
	$i=0;
	while($stmt->fetch()){
		$arr['FID'][$i]=$FID;
		$arr['FuelVal'][$i]=$FuelVal;
		$arr['StartOdom'][$i]=$StartOdom;
		$arr['EndOdom'][$i]=$EndOdom;
		if($i>0){
			if($arr['FID'][$i]==$arr['FID'][$i-1]){
				if($arr['FuelVal'][$i]==$arr['FuelVal'][$i-1]){
					$FuelVal=0;
				}
				if($arr['StartOdom'][$i]==$arr['StartOdom'][$i-1]){
					$StartOdom=0;
				}
				if($arr['EndOdom'][$i]==$arr['EndOdom'][$i-1]){
					$EndOdom=0;
				}
			}
			
		}
		$DiffOdom = "";
		$InTime = "";
		
						if( $PointState == 0 ){
							//$cPointStateText = "2";
							$PointState = "Курьер в пути";
						}elseif($PointState == 1 || $PointState == 4){
							//$cPointStateText = "4";
							$PointState = "Заказ получен";
						}elseif($PointState == 2 || $PointState == 5 || $PointState == 6){
							//$cPointStateText = "5";
							$PointState = "Заказ отменен";
						}elseif($PointState == 3){
							//$cPointStateText = "6";
							$PointState = "Заказ принят частично";
						}
						
						$arrayTimeFact = getEstimateTimeFact( $CID );
						
						if(!$arrayTimeFact[0]['INDATE_FACT'] || $arrayTimeFact[0]['INDATE_FACT']==""){
							$DELIVERY_DATE_FACT = '';
						}else{
							$DELIVERY_DATE_FACT = $arrayTimeFact[0]['INDATE_FACT'];
						}
						
						if(!$arrayTimeFact[0]['OUTDATE_FACT'] || $arrayTimeFact[0]['OUTDATE_FACT']==""){
							$DELIVERY_OUTDATE_FACT = '';
						}else{
							$DELIVERY_OUTDATE_FACT = $arrayTimeFact[0]['OUTDATE_FACT'];
						}
						
						if((int)$EndOdom > (int)$StartOdom){
							$DiffOdom = $EndOdom - $StartOdom;
						}else{
							$DiffOdom = 0;
						}
						
						if(!$arrayTimeFact[0]['OUTDATE_FACT'] || $arrayTimeFact[0]['OUTDATE_FACT']==""){
							$InTime = 0;
						}else{
							$date_estim = date('Y-m-d',strtotime($EstimateTime));
						if( strtotime($DELIVERY_OUTDATE_FACT) > (strtotime($date_estim.' '.$EndWorkTime) + 900) ){
							$InTime = 0;
						}else{
							$InTime = 1;
						}
						}
		
		$Data[] = $Name.'/n'.$Address.'/n'.$Driver.'/n'.$StartWorkTime.'/n'.$EndWorkTime.'/n'.$DELIVERY_DATE_FACT.'/n'.$DELIVERY_OUTDATE_FACT.'/n'.$PointState.'/n'.$SumCargo.'/n'.$DriverComments.'/n'.$FuelVal.'/n'.$StartOdom.'/n'.$EndOdom.'/n'.$DiffOdom.'/n'.$InTime.'/n';
		$i++;
	}
	}}
	
	//print_r($Data);
	
    $Fields=array('Название','Адрес доставки','Курьер','Начало работы','Конец работы','Время прибытия','Время отбытия','Состояние резерва','Сумма оплаты','Комментарий курьера','Заправка','Показатели одометра 9:00','Показатели одометра 18:00','Показатели одометра разница','Оценка');
    $ObjReportRun->ExportExcel($Fields,$Title,$Data);
	//$ObjReportRun->ExportExcelColored($Fields,$Title,$Data,'FFFF00','FFFFFF','F0F0F0',$Title,false);
}
if ($_GET['func']=='save_export_xls')
{
    $ObjReportRun->SaveExcel();
} 

?>