<?php

include "settings.php";
include "logger.php";
include "GetDistance.php";

$db = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
$db->query("SET CHARSET SET utf8");
$db->query("SET NAMES 'utf8'");
if (mysqli_connect_errno()) {	
	exit('Error connecting to DB');
}

$db = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
$db->query("SET CHARSET SET utf8");
$db->query("SET NAMES 'utf8'");
if (mysqli_connect_errno()) {	
	exit('Error connecting to DB');
}

$db2 = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
$db2->query("SET CHARSET SET utf8");
$db2->query("SET NAMES 'utf8'");
if (mysqli_connect_errno()) {	
	exit('Error connecting to DB');
}

$db3 = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
$db3->query("SET CHARSET SET utf8");
$db3->query("SET NAMES 'utf8'");
if (mysqli_connect_errno()) {	
	exit('Error connecting to DB');
}

function export($dateFrom, $dateTo, $client_id) {
	
	global $db;
	global $db2;
	global $db3;
	
	$query = "SELECT f.ID,f.Closed,wc.`car_name`,f.`Name`,f.`Distance`,f.`Time`,f.`StartTime`,f.`EndTime`,p.`Name` 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`  
WHERE p.`Client_ID` = ".$client_id." AND (p.`Date` >= '".$dateFrom."' AND p.`Date` <= '".$dateTo."') ORDER BY f.ID ASC;";
	writeToLogFile($query);
	if ($stmt = $db->prepare($query)) {
		$stmt->execute();
		$stmt->bind_result($ID,$Closed,$car_name,$Name,$Distance,$Time,$StartTime,$EndTime,$project);
		$stmt->store_result();
		if($stmt->num_rows()>0){
			
		$dom = new domDocument("1.0", "UTF-8");
		$dom->preserveWhiteSpace = false;
		$dom->formatOutput = true;

		$root = $dom->createElement("TRIPS");
		$dom->appendChild($root);
			
		while ($stmt->fetch()) {
					
		$TRIP = $dom->createElement("TRIP");
		$root->appendChild($TRIP);
		
		$TRIP_CODE = $dom->createElement("TRIP_CODE", $ID);
		$TRIP->appendChild($TRIP_CODE);
		$PROJECT_CODE = $dom->createElement("PROJECT_CODE", $project);
		$TRIP->appendChild($PROJECT_CODE);
		$CAR = $dom->createElement("CAR", $car_name);
		$TRIP->appendChild($CAR);
		$TRIP_NAME = $dom->createElement("TRIP_NAME", $Name);
		$TRIP->appendChild($TRIP_NAME);
		$TRIP_DIST_PLAN = $dom->createElement("TRIP_DIST_PLAN", round(($Distance/1000),2));
		$TRIP->appendChild($TRIP_DIST_PLAN);
		$TRIP_DURATION_PLAN = $dom->createElement("TRIP_DURATION_PLAN", round($Time,2));
		$TRIP->appendChild($TRIP_DURATION_PLAN);
		$START_TIME_PLAN = $dom->createElement("START_TIME_PLAN", $StartTime);
		$TRIP->appendChild($START_TIME_PLAN);
		$FINISH_TIME_PLAN = $dom->createElement("FINISH_TIME_PLAN", $EndTime);
		$TRIP->appendChild($FINISH_TIME_PLAN);
		
		
		
				$query2 = "SELECT c.`Id`,c.`Name`,c.`GeoX`,c.`GeoY`,c.`PointState`,c.`OrderInTrip`,c.`Distance`,c.`Time`,c.`EstimateTime`,c.`DurationInPoint`,c.`Code`,c.`ClientName`,c.`PointState`,`IsOrderPoint`,`OrderInTrip`,(SELECT MAX(`OrderInTrip`) FROM `tms_checkpoint` WHERE `FactTripID` = ".$ID."),(SELECT MIN(`OrderInTrip`) FROM `tms_checkpoint` WHERE `FactTripID` = ".$ID.") FROM `tms_checkpoint` c WHERE c.`FactTripID` = ".$ID." ORDER BY `OrderInTrip` ASC;";
				writeToLogFile($query2);
				if ($stmt2 = $db2->prepare($query2)) {
				if ($stmt2->execute()) {	
				$stmt2->bind_result($Id,$cName,$cGeoX,$cGeoY,$cPointState,$cOrderInTrip,$cDistance,$cTime,$cEstimateTime,$cDurationInPoint,$cCode,$cClientName,$cPointState,$IsOrderPoint,$OrderInTrip,$maxOrderInTrip,$minOrderInTrip);
				$stmt2->store_result();
				if($stmt2->num_rows()>0){
					
					$ORDERS = $dom->createElement("ORDERS");
					$TRIP->appendChild($ORDERS);
					
					while($stmt2->fetch()){
						
					$ORDER = $dom->createElement("ORDER");
					$ORDERS->appendChild($ORDER);
					
					$ID_POINT = $dom->createElement("ID_POINT",$Id);
					$ORDER->appendChild($ID_POINT);
					
					if(!$cCode || $cCode==""){ $cCode = "-"; }
					$CODE = $dom->createElement("CODE",$cCode);
					$ORDER->appendChild($CODE);
					
					$IN_TRIP_NUMBER = $dom->createElement("IN_TRIP_NUMBER",$cOrderInTrip);
					$ORDER->appendChild($IN_TRIP_NUMBER);
					
					$PLAN_DIST = $dom->createElement("PLAN_DIST",round(($cDistance/1000),2));
					$ORDER->appendChild($PLAN_DIST);
					
					$DELIVERY_DATE_PLAN = $dom->createElement("DELIVERY_DATE_PLAN",$cEstimateTime);
					$ORDER->appendChild($DELIVERY_DATE_PLAN);
					
					$cEstimateTime2 = strtotime($cEstimateTime) + ($cDurationInPoint * 60);
					$DELIVERY_OUTDATE_PLAN = $dom->createElement("DELIVERY_OUTDATE_PLAN",date('Y-m-d H:i:s',$cEstimateTime2));
					$ORDER->appendChild($DELIVERY_OUTDATE_PLAN);
					
					$STATUS_VISIT = $dom->createElement("STATUS_VISIT",$cPointState);
					$ORDER->appendChild($STATUS_VISIT);
					
					//if($IsOrderPoint == 0){ $IsOrderPoint = 'Склад'; }else{ $IsOrderPoint = 'Магазин'; }
					$TYPE_POINT = $dom->createElement("TYPE_POINT",$IsOrderPoint);
					$ORDER->appendChild($TYPE_POINT);
					
					
					
					// ++++++++++ 
					$query3 = "SELECT c.`OrderInTrip`,c.`DurationInPoint`,c.`GeoX`,c.`GeoY`,e.`date`,e.`GeoX`,e.`GeoY` FROM tms_events e INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt WHERE e.ParamInt = ".$Id." AND (e.EventType=13 OR e.EventType=14 OR e.EventType=15)";
					writeToLogFile($query3);
					if ($stmt3 = $db3->prepare($query3)) {
					if ($stmt3->execute()) {	
					$stmt3->bind_result($ccOrderInTrip,$ccDurationInPoint,$ccGeoX,$ccGeoY,$edate,$eGeoX,$eGeoY);
					$stmt3->store_result();
					if($stmt3->num_rows()>0){
					$stmt3->fetch();
					
					if($ccOrderInTrip == $minOrderInTrip){ 
						$startedate = $edate;
						$START_TIME_FACT = $dom->createElement("START_TIME_FACT", $edate);
						$TRIP->appendChild($START_TIME_FACT);
					}
					elseif($ccOrderInTrip == $maxOrderInTrip){
						$FINISH_TIME_FACT = $dom->createElement("FINISH_TIME_FACT", $edate);
						$TRIP->appendChild($FINISH_TIME_FACT);
						
						$TRIP_DURATION_FACT = $dom->createElement("TRIP_DURATION_FACT", (strtotime($edate)-strtotime($startedate))/60 );
						$TRIP->appendChild($TRIP_DURATION_FACT);
						
						$TRIP_DIST_FACT = $dom->createElement("TRIP_DIST_FACT", 0);
						$TRIP->appendChild($TRIP_DIST_FACT);
					}
						
						$DELIVERY_DATE_FACT = $dom->createElement("DELIVERY_DATE_FACT",$edate);
						$ORDER->appendChild($DELIVERY_DATE_FACT);
						
						$edate2 = strtotime($edate) + ($ccDurationInPoint * 60);
						$DELIVERY_OUTDATE_FACT = $dom->createElement("DELIVERY_OUTDATE_FACT",date('Y-m-d H:i:s',$edate2));
						$ORDER->appendChild($DELIVERY_OUTDATE_FACT);
						
						$FACT_DIST = $dom->createElement("FACT_DIST",0);
						$ORDER->appendChild($FACT_DIST);
						
					}else{
						
						if($OrderInTrip == $minOrderInTrip){
						$startedate = $cEstimateTime;
						$START_TIME_FACT = $dom->createElement("START_TIME_FACT", $cEstimateTime);
						$TRIP->appendChild($START_TIME_FACT);
						}
						elseif($OrderInTrip == $maxOrderInTrip){
						$FINISH_TIME_FACT = $dom->createElement("FINISH_TIME_FACT", $cEstimateTime);
						$TRIP->appendChild($FINISH_TIME_FACT);
						
						$TRIP_DURATION_FACT = $dom->createElement("TRIP_DURATION_FACT", (strtotime($cEstimateTime)-strtotime($startedate))/60 );
						$TRIP->appendChild($TRIP_DURATION_FACT);
						
						$TRIP_DIST_FACT = $dom->createElement("TRIP_DIST_FACT", 0);
						$TRIP->appendChild($TRIP_DIST_FACT);
						
						}
					}
					
					
					}
					$stmt3->close();
					}
					
					
					
					}
				}	
				}
				$stmt2->close();
				}
	
		
		}
		}else{
			//echo 'Рейсов не найдено'; exit;
			echo '<?xml version="1.0" encoding="utf-8"?><RESULT>Рейсов не найдено</RESULT>'; exit;
			}
		$stmt->close();
		return  $dom->saveXML();
	}else{
		//echo 'Ошибка выполнения';
		echo '<?xml version="1.0" encoding="utf-8"?><RESULT>Ошибка выполнения</RESULT>'; exit;
	}
	
	$db->close();
	return false;
}


echo export('2015-07-13', '2015-07-13', 92);