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

//error_reporting(E_ALL);

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 ConvertTimeToGMT($Time,$GMT)
{
		 $Time = strtotime($Time);
         $Time=$Time-$GMT*60*60; 
         $Time=date('Y-m-d H:i:s',$Time);
         return $Time;
}

function GetMil($Imei,$TimeFrom,$TimeTo)
{

	global $db2;
	global $instance_id;
	global $GMT;
	
	$TimeFrom = ConvertTimeToGMT($TimeFrom,$GMT);
	$TimeTo = ConvertTimeToGMT($TimeTo,$GMT);
	
			$query = "SELECT SUM(`Distance`) FROM `tms_gpsdata_fixed` WHERE `time`>= '".$TimeFrom."' AND `time`<= '".$TimeTo."' AND `DeviceLoginName`='".$Imei."';";
			if($stmt = $db2->prepare($query)){
			if($stmt->execute()){   
            $stmt->bind_result($Result);
            $stmt->fetch();
			$stmt->close();
			$Result = round($Result/1000, 2);
			
			return $Result;
			
			}else{ return 0; }
			}else{ return 0; }
}

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');
	
	$details = $_REQUEST['details'];
	
	if($details == 6){
		
	$query = "SELECT DATE(f.`StartTime`) AS dt, td.`Login` AS lg, wc.`car_name` FROM tms_facttrip f
INNER JOIN `tms_checkpoint` ch ON ch.`FactTripID` = f.`ID`
INNER 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`
INNER JOIN `wm_car_info` wc ON wc.`imei` = td.`Login`  
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59')
GROUP BY dt,f.`ID`";

	/*$query = "SELECT DATE(f.`StartTime`) AS dt, GROUP_CONCAT(td.`Login`) AS lg, wc.`car_name` FROM tms_facttrip f
INNER JOIN `tms_checkpoint` ch ON ch.`FactTripID` = f.`ID`
INNER 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`
INNER JOIN `wm_car_info` wc ON wc.`imei` = td.`Login`  
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59')
GROUP BY dt,f.`ID`";*/

	$result=array();
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($date,$imei,$car);
	while($stmt->fetch()){
		
		$result_g[]=array(
		'result_1'=>$car,
		'result_2'=>$imei,
		'result_3'=>$date
		);
		
		/*$result[]=array(
		'result_1'=>$car,
		'result_2'=>'',
		'result_3'=>$date,
		'result_4'=>'',
		'result_5'=>'0',
		'result_6'=>'',
		'result_7'=>'',
		'result_8'=>'',
		'result_9'=>'',
		'result_10'=>'',
		'result_11'=>'',
		'result_12'=>'',
		'result_13'=>'',
		'result_14'=>'',
		'result_15'=>'',
		'result_16'=>'',
		'result_17'=>'',
		'result_18'=>'',
		'result_19'=>'',
		'result_20'=>''
		);
		*/
		
	
	}
	$stmt->close();
	}	
	}
	
	$group_data = array();
	foreach ( $result_g as $value ) {
		$group_data[$value['result_3']][] = $value;
	}
	
	foreach ( $group_data as $key=>$value ){

		$imei = array();
		foreach( $group_data[$key] as $key2=>$value2 ){
			//$str .= $group_data[$key][$key2]['result_2'].',';
			array_push($imei,$group_data[$key][$key2]['result_2']);
		}
		//echo $group_data[$key][0]['result_3'].'+'.implode(",", $imei).'<br/>';
		$query = "SELECT `car_name`,imei FROM `wm_car_info` WHERE `client_id` = ".$instance_id." AND `imei` NOT IN (".implode(",", $imei).")";
		if( $stmt = $db->prepare($query) ){
		if( $stmt->execute() ){
		$stmt->bind_result($name,$imeir);
		while($stmt->fetch()){
		
		$dater = $group_data[$key][0]['result_3'];
		$mil = GetMil($imeir,$dater.' 00:00:00',$dater.' 23:59:59');
		
		$result[]=array(
		'result_1'=>$name,
		'result_2'=>'',
		'result_3'=>$dater,
		'result_4'=>'',
		'result_5'=>$mil,
		'result_6'=>'',
		'result_7'=>'',
		'result_8'=>'',
		'result_9'=>'',
		'result_10'=>'',
		'result_11'=>'',
		'result_12'=>'',
		'result_13'=>'',
		'result_14'=>'',
		'result_15'=>'',
		'result_16'=>'',
		'result_17'=>'',
		'result_18'=>'',
		'result_19'=>'',
		'result_20'=>''
		);
		
		}
		}}
		
	}
	
	
	$total = count($result);
	
	
	}elseif($details == 3){
		
$query = "SELECT COUNT(*) FROM `tms_facttrip` f
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 `tms_events` e ON e.`DeviceLoginName` = td.`Login` AND `EventType` = 3 AND e.`date` >= '".$startdt." 00:00:00'
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59')";
	if ($stmt = $db->prepare($query)){
    if ($stmt->execute()){
        $stmt->bind_result($total);
        $stmt->fetch();
        $stmt->close();
	}}
	
$query = "SELECT f.`ID`,f.`Name`,wc.`car_name`,ADDTIME(e.`date`,'0 ".$GMT.":0:0') FROM `tms_facttrip` f
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 `tms_events` e ON e.`DeviceLoginName` = td.`Login` AND `EventType` = 3 AND e.`date` >= '".$startdt." 00:00:00'
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59')";
	$result=array();
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($FID,$Name,$Car,$date);
	while($stmt->fetch()){
	
	$eventMessage = '';
	if($date){
		$eventMessage = 'більше 1';
	}
	
	$result[]=array(
		'result_1'=>$Car,
		'result_2'=>$Name,
		'result_3'=>$date,
		'result_4'=>'',
		'result_5'=>'',
		'result_6'=>'',
		'result_7'=>'',
		'result_8'=>'',
		'result_9'=>'',
		'result_10'=>'',
		'result_11'=>'',
		'result_12'=>'',
		'result_13'=>$eventMessage,
		'result_14'=>'',
		'result_15'=>'',
		'result_16'=>'',
		'result_17'=>'',
		'result_18'=>'',
		'result_19'=>'',
		'result_20'=>$FID
	);
	}
	$stmt->close();
	}	
	}
	
	
	
	
	
	}else{
	
	$where = "";
	
	if($details == 1 ){
		//$where .= " AND f.`Closed` = 1 ";
	}elseif($details == 4){
		//$where .= " AND (f.`Closed` = 0 OR f.`Closed` = 2) ";
	}
	
	if($details == 1 || $details == 3 || $details == 4){
		$where .= " GROUP BY f.`ID` ";
	}elseif($details == 2 || $details == 5){
		$where .= " GROUP BY c.`Id`";
	}
	
	
	
	
	/*$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)){
    if ($stmt->execute()){
        $stmt->bind_result($total);
        $stmt->fetch();
        $stmt->close();
	}}
	
	
	if($details == 3){
		$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`,
c.`Name`,c.`Code`,c.`Address`,c.`PointState`,c.`Distance`,c.`Time`,
c.`EstimateTime`,c.`DurationInPoint`,DATE_FORMAT(c.`StartWorkTime`,'%H:%i:%s') AS StartWorkTime,DATE_FORMAT(c.`EndWorkTime`,'%H:%i:%s') AS EndWorkTime,
f.`Name`, f.`StartTime`,f.`EndTime`,
0 as `date`
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`
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59') AND c.`IsOrderPoint` = 1
AND `Closed` <> 1
GROUP BY f.`ID` ORDER BY c.ID, c.`OrderInTrip` ASC LIMIT ".$start.",".$limit.";";
	}
	elseif($details == 1){
		$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`,
c.`Name`,c.`Code`,c.`Address`,c.`PointState`,c.`Distance`,c.`Time`,
c.`EstimateTime`,c.`DurationInPoint`,DATE_FORMAT(c.`StartWorkTime`,'%H:%i:%s') AS StartWorkTime,DATE_FORMAT(c.`EndWorkTime`,'%H:%i:%s') AS EndWorkTime,
f.`Name`,f.`StartTime`,f.`EndTime`,
ADDTIME(e.date,'0 ".$GMT.":0:0')
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_events e ON td.`Login` = e.`DeviceLoginName` AND (ADDTIME(e.date,'0 ".$GMT.":0:0') >= f.`StartTime` AND ADDTIME(e.date,'0 ".$GMT.":0:0') <= f.`EndTime`)
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59') AND c.`IsOrderPoint` = 1
AND e.EventType = 12
GROUP BY f.`ID` ORDER BY e.date DESC LIMIT ".$start.",".$limit.";";
	}else{
		$query = "";
	}
	
	$result=array();
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($result_1,$result_2,$result_3,$result_4,$result_5,$result_6,$result_7,$result_8,$result_9,$result_10,$result_11,$result_12,$result_13,$result_14,$result_17,$result_18,$result_19,$result_20);
	while($stmt->fetch()){

						if( $result_6 == 0 ){
							//$cPointStateText = "2";
							$result_6 = "Курьер в пути";
						}elseif($result_6 == 1 || $result_6 == 4){
							//$cPointStateText = "4";
							$result_6 = "Заказ получен";
						}elseif($result_6 == 2 || $result_6 == 5 || $result_6 == 6){
							//$cPointStateText = "5";
							$result_6 = "Заказ отменен";
						}elseif($result_6 == 3){
							//$cPointStateText = "6";
							$result_6 = "Заказ принят частично";
						}
						
						$arrayTimeFact = getEstimateTimeFact( $result_2 );
						
						if(!$arrayTimeFact[0]['INDATE_FACT'] || $arrayTimeFact[0]['INDATE_FACT']==""){
							//$tmp['DELIVERY_DATE_FACT'] = $tmp['EstimateTime'];
							$result_15 = '';
						}else{
							$result_15 = $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);
							$result_16 = '';
						}else{
							$result_16 = $arrayTimeFact[0]['OUTDATE_FACT'];
						}
						
	$result[]=array(
		'result_1'=>$result_1,
		'result_2'=>$result_2,
		'result_3'=>$result_3,
		'result_4'=>$result_4,
		'result_5'=>$result_5,
		'result_6'=>$result_6,
		'result_7'=>$result_7,
		'result_8'=>$result_8,
		'result_9'=>Round(($result_9 / 1000),2),
		'result_10'=>$result_10,
		'result_11'=>$result_11,
		'result_12'=>$result_12,
		'result_13'=>$result_13,
		'result_14'=>$result_14,
		'result_15'=>$result_15,
		'result_16'=>$result_16,
		//'result_17'=>'Маршрут: '.$result_17.' / №: '.$result_1,
		'result_17'=>$result_17,
		'result_18'=>$result_18,
		'result_19'=>$result_19,
		'result_20'=>$result_20
	);
	}
	$stmt->close();
	}	
	}
*/



	$query = "SELECT COUNT(*) FROM (SELECT f.`ID`
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`
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59') AND c.`IsOrderPoint` = 1
".$where." ) t";
	if ($stmt = $db->prepare($query)){
    if ($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,f.`Name`,DATE(f.`StartTime`),f.`Distance`,(SELECT COUNT(*) FROM `tms_checkpoint` WHERE `FactTripID` = FID),(SELECT COUNT(*) FROM `tms_checkpoint` WHERE `PointState` > 0 AND `FactTripID` = FID), f.`Closed`,
c.`EstimateTime`,c.`DurationInPoint`,c.`Address`,
wc.imei,f.`StartTime`,f.`EndTime`,
(SELECT COUNT(`PointState`) FROM `tms_checkpoint` WHERE `FactTripID` = FID AND `IsOrderPoint` = 1 AND `PointState` > 0)
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`
WHERE p.`Client_ID` = ".$instance_id ." AND (p.`Date` >= '".$startdt." 00:00:00' AND p.`Date` <= '".$enddt." 23:59:59') AND c.`IsOrderPoint` = 1
".$where." ORDER BY c.ID, c.`OrderInTrip` ASC LIMIT ".$start.",".$limit.";";
	
	//echo $query;
	
	$result=array();
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($FID,$CID,$Car,$Name,$StartTime,$Distance,$CountPoint,$CountFact,$Closed,$EstimateTime,$DurationInPoint,$Address,$imei,$fst,$fet,$countPoint);
	while($stmt->fetch()){
		
		if($Closed == 1){
			$Closed = 'Выполнен';
		}else{
			$Closed = 'Не выполнен';
		}
		
		$EstimateTimeEnd = (int)($DurationInPoint*60) + strtotime($EstimateTime);
		$EstimateTimeEnd = date('Y-m-d H:i:s', $EstimateTimeEnd);
		
		$arrayTimeFact = getEstimateTimeFact( $CID );
						
		if(!$arrayTimeFact[0]['INDATE_FACT'] || $arrayTimeFact[0]['INDATE_FACT']==""){
				$result_11 = '';
		}else{
				$result_11 = $arrayTimeFact[0]['INDATE_FACT'];
		}
						
		if(!$arrayTimeFact[0]['OUTDATE_FACT'] || $arrayTimeFact[0]['OUTDATE_FACT']==""){
				$result_12 = '';
		}else{
				$result_12 = $arrayTimeFact[0]['OUTDATE_FACT'];
		}
		
		$intime = 0;
		if($result_11){
			$diff = strtotime($result_11) - strtotime($EstimateTime);
			//if($diff<=0){
			//	$intime = 0;
			//}else{
				$intime = Round($diff/60);
			//}
		}
	$mil = 0;
	$mil = GetMil($imei,$fst,$fet);
	
	if($details == 1 ){
		if($countPoint>1){
	$result[]=array(
		'result_1'=>$Car,
		'result_2'=>$Name,
		'result_3'=>$StartTime,
		'result_4'=>Round(($Distance/1000),2),
		'result_5'=>$mil,
		'result_6'=>$CountPoint,
		'result_7'=>$CountFact,
		'result_8'=>$Closed,
		'result_9'=>$EstimateTime,
		'result_10'=>$EstimateTimeEnd,
		'result_11'=>$result_11,
		'result_12'=>$result_12,
		'result_13'=>1,
		'result_14'=>$Address,
		'result_15'=>$intime,
		'result_16'=>'',
		'result_17'=>'',
		'result_18'=>'',
		'result_19'=>'',
		'result_20'=>$FID
	);
		}
	}elseif($details == 4 ){
		if($countPoint<1){
	$result[]=array(
		'result_1'=>$Car,
		'result_2'=>$Name,
		'result_3'=>$StartTime,
		'result_4'=>Round(($Distance/1000),2),
		'result_5'=>$mil,
		'result_6'=>$CountPoint,
		'result_7'=>$CountFact,
		'result_8'=>$Closed,
		'result_9'=>$EstimateTime,
		'result_10'=>$EstimateTimeEnd,
		'result_11'=>$result_11,
		'result_12'=>$result_12,
		'result_13'=>1,
		'result_14'=>$Address,
		'result_15'=>$intime,
		'result_16'=>'',
		'result_17'=>'',
		'result_18'=>'',
		'result_19'=>'',
		'result_20'=>$FID
	);	
		}
	}else{
	
	$result[]=array(
		'result_1'=>$Car,
		'result_2'=>$Name,
		'result_3'=>$StartTime,
		'result_4'=>Round(($Distance/1000),2),
		'result_5'=>$mil,
		'result_6'=>$CountPoint,
		'result_7'=>$CountFact,
		'result_8'=>$Closed,
		'result_9'=>$EstimateTime,
		'result_10'=>$EstimateTimeEnd,
		'result_11'=>$result_11,
		'result_12'=>$result_12,
		'result_13'=>1,
		'result_14'=>$Address,
		'result_15'=>$intime,
		'result_16'=>'',
		'result_17'=>'',
		'result_18'=>'',
		'result_19'=>'',
		'result_20'=>$FID
	);
	
	}
	
	}
	$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();
} 

?>