<?php
function export($dateFrom, $dateTo, $client_id, $gmt, $imei, $order_code, $key) {
	
	global $db;
	global $db2;
	global $db3;
	global $db4;
	
	
	
	$where1 = "";
	if($imei){
		$where1 = " AND td.Login = '".$imei."' ";
	}
	$where2 = "";
	if($order_code){
		$where2 = " AND c.`Code` = '".$order_code."' ";
	}
	
	$query = "SELECT
	t.`Trip_ID` AS TID,
	p.name,
	wc.car_name,
	CONCAT('',wc.`car_name`,' (',d.`Login`,')') AS Login,
	d.`Login` AS Login2,
	dr.name,
	ep.`Name`,
	t.`Name`,
	t.`Distance`,
	t.`Time`,
	DATE_FORMAT(t.`StartTime`,'%H:%i:%s %d-%m-%Y'),
	DATE_FORMAT(t.`EndTime`,'%H:%i:%s %d-%m-%Y'),
	IF(ft.`Closed` IS NULL, 0, 1) AS Closed,
	
	tp.`Point_ID`,
	o.code,
	w.code,
	cl.`Code`,
	
	tp.`OrderInTrip`,
	tp.`Distance`,
	tp.`EstimateTime`,
	tp.`DurationInPoint`,
	t.`Comments`

/*	
tlc.name,
tp.type,
SEC_TO_TIME(TIME_TO_SEC(TIME(tp.`EstimateTime`))+(tp.`DurationInPoint`*60))

tlc.`CargoValueWeight`,
tlc.`Cost`,
tlc.`CargoValueCount`,
  
  t.`Car_ID`,
  t.`TripPrice`,
  
  t.`Project_ID` AS Project_ID2,
  t.`WalkerNum`
*/

  FROM `tms_trip` t 
  INNER JOIN `tms_car_snapshot` c  ON c.`ID` = t.`Car_ID` 
  LEFT JOIN `tms_facttrip` ft ON ft.`TripId` = t.`Trip_ID` 
  LEFT JOIN `tms_device` d ON c.`Device_id` = d.`ID` 
  LEFT JOIN `tms_web_lock_trip` lt ON lt.`trip_id` = t.`Trip_ID` 
  LEFT JOIN `wm_car_info` wc ON wc.`imei` = d.`Login` 
  INNER JOIN `tms_project` p ON p.`Project_ID` = t.`Project_ID` 
  LEFT JOIN `tms_forwarder` fd ON fd.`Forwarder_ID` = t.`Forwarder_ID` 
  LEFT JOIN `tms_employee` ep ON ep.`Employee_ID` = fd.`Employee_ID` 
  LEFT JOIN `tms_car` car ON car.`Car_ID`=t.`Car_ID`
  LEFT JOIN `wm_drivers` dr ON dr.`id` = wc.`driver_id`
  LEFT JOIN `tms_trippoint` tp  ON t.`Trip_ID` = tp.`Trip_ID`
  LEFT JOIN tms_loadunloadpoint tlp ON tp.`Point_ID` = tlp.`LoadUnloadPoint_ID`
  LEFT JOIN `tms_lupointcargo` tlc ON tlp.LoadUnloadPoint_ID = tlc.`LUPoint_ID`
  LEFT JOIN `tms_order` o ON tlp.`OrderWarehouse_ID` = o.`Order_ID`
  LEFT JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
  LEFT JOIN `tms_client` cl ON cl.`Client_ID` = w.`Client_ID`
	WHERE p.`Client_ID` = ".$client_id." AND (p.`Date` >= '".$dateFrom."' AND p.`Date` <= '".$dateTo."') ".$where1." ".$where2."
	 AND ft.`Closed` IS NULL
	 AND t.Trip_ID NOT IN (SELECT `Trip_ID` FROM `tms_session_ping_export_trip` WHERE `instance_id` = ".$client_id." AND `time` > (`time` - INTERVAL 1 DAY))
	GROUP BY tp.`Point_ID` ORDER BY t.`Trip_ID`, tp.`OrderInTrip` ASC;";
	
	//AND tlp.`IsOrderPoint` = 1

	
	$last_id = null;
	
	writeToLogFile( ' ---> '.date('Y-m-d H:i:s').' <> export plan <> '.$query );
	if ($stmt = $db->prepare($query)) {
		$stmt->execute();
		$stmt->bind_result($ID,$pname,$car_name,$Login,$Login2,$drname,$epname,$tname,$tdistance,$ttime,$StartTime,$EndTime,$Closed,$Point_ID,$ocode,$wcode,$clcode,$OrderInTrip,$tpDistance,$tpEstimateTime,$tpDurationInPoint,$Comments);
		$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()) {
			
			
		if($last_id!=$ID){
		
		
		
		$TRIP = $dom->createElement("TRIP");
		$root->appendChild($TRIP);
		
		$TRIP_CODE = $dom->createElement("TRIP_CODE", $ID);
		$TRIP->appendChild($TRIP_CODE);
		$PROJECT_CODE = $dom->createElement("PROJECT_CODE", $pname);
		$TRIP->appendChild($PROJECT_CODE);
		$CAR = $dom->createElement("CAR", $car_name);
		$TRIP->appendChild($CAR);
		
		$CODE_CAR = $dom->createElement("CODE_CAR", $Login);
		$TRIP->appendChild($CODE_CAR);
		
		$DRIVER = $dom->createElement("DRIVER", $drname);
		$TRIP->appendChild($DRIVER);
		
		//$EXPEDITOR = $dom->createElement("EXPEDITOR", 0);
		//$TRIP->appendChild($EXPEDITOR);
		
		$FORWARDER = $dom->createElement("FORWARDER", $epname);
		$TRIP->appendChild($FORWARDER);
		
		$TRIP_NAME = $dom->createElement("TRIP_NAME", $tname);
		$TRIP->appendChild($TRIP_NAME);
		
		$arrCarTrip = getMaxCarTrip($db2,$ID,$client_id);
		$trip_weight = $arrCarTrip[0]['Weight'];
		$trip_volume = $arrCarTrip[0]['Volume'];;
		
		$TRIP_WEIGHT = $dom->createElement("TRIP_WEIGHT", $trip_weight);
		$TRIP->appendChild($TRIP_WEIGHT);
		
		$TRIP_VOLUME = $dom->createElement("TRIP_VOLUME", $trip_volume);
		$TRIP->appendChild($TRIP_VOLUME);
		
		$TRIP_COMMENTS = $dom->createElement("COMMENTS", $Comments);
		$TRIP->appendChild($TRIP_COMMENTS);
		
		$TRIP_DIST_PLAN = $dom->createElement("TRIP_DIST_PLAN", round(($tdistance/1000),2));
		$TRIP->appendChild($TRIP_DIST_PLAN);
		$TRIP_DURATION_PLAN = $dom->createElement("TRIP_DURATION_PLAN", round($ttime,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);
		
		$ORDERS = $dom->createElement("ORDERS");
		$TRIP->appendChild($ORDERS);
		
		
		//-------------
			$query_ping = "INSERT IGNORE INTO tms_session_ping_export_trip (`Trip_ID`,`session_id`,`instance_id`) VALUES (".$ID.",'".$key."',".$client_id.");";
			writeToLogFile( ' ---> '.date('Y-m-d H:i:s').' <> export plan <> '.$query );
			$db3->query($query_ping);
		//-------------
		
		}
		
		
		
		$ORDER = $dom->createElement("ORDER");
		$ORDERS->appendChild($ORDER);
		
		$ID_POINT = $dom->createElement("ID_POINT",$Point_ID);
		$ORDER->appendChild($ID_POINT);
		
		if(!$ocode || $ocode==""){ $ocode = "-"; }
		$CODE = $dom->createElement("CODE",$ocode);
		$ORDER->appendChild($CODE);
					
		$SHOPCODE = $dom->createElement("SHOPCODE",$wcode);
		$ORDER->appendChild($SHOPCODE);
						
		$CLIENTCODE = $dom->createElement("CLIENTCODE",$clcode);
		$ORDER->appendChild($CLIENTCODE);			
		
		$IN_TRIP_NUMBER = $dom->createElement("IN_TRIP_NUMBER",$OrderInTrip);
		$ORDER->appendChild($IN_TRIP_NUMBER);
						
		$PLAN_DIST = $dom->createElement("PLAN_DIST",round(($tpDistance/1000),2));
		$ORDER->appendChild($PLAN_DIST);
						
		$DELIVERY_DATE_PLAN = $dom->createElement("DELIVERY_DATE_PLAN",$tpEstimateTime);
		$ORDER->appendChild($DELIVERY_DATE_PLAN);
						
		$cEstimateTime2 = strtotime($tpEstimateTime) + ($tpDurationInPoint * 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",0);
		$ORDER->appendChild($STATUS_VISIT);
		
		$STATUS_POINT = $dom->createElement("STATUS_POINT","Курьер в пути");
		$ORDER->appendChild($STATUS_POINT);
        
        $TYPE_POINT = $dom->createElement("TYPE_POINT",$IsOrderPoint);
		$ORDER->appendChild($TYPE_POINT);
		
		
		
		
/*		
		
				//$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`,c.`DriverComments`,(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;";
				$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`,c.`DriverComments`,(SELECT MAX(`OrderInTrip`) FROM `tms_checkpoint` WHERE `FactTripID` = ".$ID."),(SELECT MIN(`OrderInTrip`) FROM `tms_checkpoint` WHERE `FactTripID` = ".$ID."),wh.`Code`
				FROM `tms_checkpoint` c
INNER JOIN `tms_facttrip` ft ON ft.`ID`=c.`FactTripID`
LEFT JOIN tms_order o ON (o.`Code`= c.`Code` AND o.`Project_ID` = ft.`Project_Id`)
LEFT JOIN tms_warehouse wh ON (o.`Warehouse_ID` = wh.`Warehouse_ID` AND wh.DeletedRecord = 0)
WHERE c.`FactTripID` = ".$ID." ".$where2." 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,$DriverComments,$maxOrderInTrip,$minOrderInTrip,$whCode);
				$stmt2->store_result();
				if($stmt2->num_rows()>0){
					
					$ORDERS = $dom->createElement("ORDERS");
					$TRIP->appendChild($ORDERS);
					
					$firstWarehouse = '';
					$firstPointState = '';
					$i=0;
					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);
						
						$SHOPCODE = $dom->createElement("SHOPCODE",$whCode);
						$ORDER->appendChild($SHOPCODE);
						
						$CLIENTCODE = $dom->createElement("CLIENTCODE",0);
						$ORDER->appendChild($CLIENTCODE);
						
						
						$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($i==0){ $firstPointState = $cPointState; }
						//$cPointStateText = "";
						
						writeToLogFile(' --> '.$i.'  firstWarehouse '.$firstWarehouse.' firstPointState '.$firstPointState.'  IsOrderPoint '.$IsOrderPoint.'  cPointState '.$cPointState);
						
						if( $firstPointState == 0 ){
							$cPointStateText = "1";
							//$cPointStateText = "Заказ принят";
						}elseif( ($firstPointState != 0) && $cPointState == 0 ){
							$cPointStateText = "2";
							//$cPointStateText = "Курьер в пути";
						}elseif($firstPointState == 1 && ($cPointState == 1 || $cPointState == 4)){
							$cPointStateText = "4";
							//$cPointStateText = "Заказ получен";
						}elseif($firstPointState == 1 && ($cPointState == 2 || $cPointState == 5 || $cPointState == 6)){
							$cPointStateText = "5";
							//$cPointStateText = "Заказ отменен";
						}elseif($firstPointState == 1 && ($cPointState == 3)){
							$cPointStateText = "6";
							//$cPointStateText = "Заказ принят частично";
							
							$query4 = "SELECT `Name` FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = ".$Id." AND `State`<>2";
							if ($stmt4 = $db4->prepare($query4)) {
							$stmt4->execute();
							$stmt4->bind_result($Name);
							while($stmt4->fetch())
							{
								$PARTIALLY_CARGO = $dom->createElement("PARTIALLY_CARGO",$Name);
								$ORDER->appendChild($PARTIALLY_CARGO);
							}
							$stmt4->close();
							}else{
								echo '<?xml version="1.0" encoding="utf-8"?><RESULT>Ошибка выполнения</RESULT>'; exit;
							}
						}
						
						$STATUS_POINT = $dom->createElement("STATUS_POINT",$cPointStateText);
						$ORDER->appendChild($STATUS_POINT);
						
						//if($IsOrderPoint == 0){ $IsOrderPoint = 'Склад'; }else{ $IsOrderPoint = 'Магазин'; }
						$TYPE_POINT = $dom->createElement("TYPE_POINT",$IsOrderPoint);
						$ORDER->appendChild($TYPE_POINT);
						$query4 = "SELECT SUM(`Cost`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = ".$Id." AND `State` = 2";
						if ($stmt4 = $db4->prepare($query4)) 
						{
							$stmt4->execute();
							$stmt4->bind_result($Cost);
							$stmt4->fetch();

							$CLIENT_PAYMENT = $dom->createElement("CLIENT_PAYMENT", $Cost);
							$ORDER->appendChild($CLIENT_PAYMENT);

							$stmt4->close();
						}

						$DRIVER_COMMENTS = $dom->createElement("COUR_COMMENTS", $DriverComments);
						$ORDER->appendChild($DRIVER_COMMENTS);
						
						
						
						$_PRODUCTS = $dom->createElement("PRODUCTS");
						$ORDER->appendChild($_PRODUCTS);
						
							$query4 = "SELECT `Name`,`CargoValueVolume`,`CargoValueWeight`,`CargoValueCount`,`Cost` FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = ".$Id."";
							if ($stmt4 = $db4->prepare($query4)) {
							$stmt4->execute();
							$stmt4->bind_result($Name,$Volume,$Weight,$Count,$Cost);
							while($stmt4->fetch())
							{
								
		$_PRODUCT = $dom->createElement("PRODUCT");
		$_PRODUCTS->appendChild($_PRODUCT);
		
		$_PROD_NAME = $dom->createAttribute("PROD_NAME");
		$_PROD_NAME->value = $Name;
		$_PRODUCT->appendChild($_PROD_NAME);
		
		$_PROD_CODE = $dom->createAttribute("PROD_CODE");
		$_PROD_CODE->value = 0;
		$_PRODUCT->appendChild($_PROD_CODE);
		
		$_WEIGHT = $dom->createAttribute("WEIGHT");
		$_WEIGHT->value = $Weight;
		$_PRODUCT->appendChild($_WEIGHT);
		
		$_VOLUME = $dom->createAttribute("VOLUME");
		$_VOLUME->value = $Volume;
		$_PRODUCT->appendChild($_VOLUME);
		
		$_COUNT = $dom->createAttribute("COUNT");
		$_COUNT->value = $Count;
		$_PRODUCT->appendChild($_COUNT);
		
		$_COST = $dom->createAttribute("COST");
		$_COST->value = $Cost;
		$_PRODUCT->appendChild($_COST);
								
							}
							$stmt4->close();
							}
						
		
		
						
						$i++;
					}
				}	
				}
				$stmt2->close();
				}else{
					echo '<?xml version="1.0" encoding="utf-8"?><RESULT>Ошибка выполнения</RESULT>'; exit;
				}
*/
		$last_id = $ID;
		
		}
		}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();
	$db2->close();
	$db3->close();
	$db4->close();
	return false;
}

function getMaxCarTrip($db,$tripid,$client_id){
	
	$data=array('Volume'=>0,'Weight'=>0);
	$query = "SELECT SUM(t.1C),SUM(t.2C) FROM (
SELECT SUM(tlc.`CargoValueVolume`) AS 1C ,SUM(tlc.`CargoValueWeight`) AS 2C
FROM `tms_lupointcargo` tlc
INNER JOIN tms_loadunloadpoint tlp ON tlp.LoadUnloadPoint_ID = tlc.`LUPoint_ID`
INNER JOIN `tms_trippoint` tp ON tp.`Point_ID` = tlp.`LoadUnloadPoint_ID`
WHERE tp.`Trip_ID` = ".$tripid." AND 
IsOrderPoint<>0
GROUP BY tlc.`LUPoint_ID`
HAVING 2C < 0
) t;";
writeToLogFile( ' ---> '.date('Y-m-d H:i:s').' <> export plan getMaxCarTrip <> '.$query );
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($Volume,$Weight);
	while($stmt->fetch()){
	 $data = array();
	 $data[]=array(
		'Volume'=>round(abs($Volume),2),
		'Weight'=>round(abs($Weight),2)
	);
	}
	}
	$stmt->close();
	}
	
	return $data;
}