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

set_time_limit(120);

//ini_set('error_reporting', E_ALL);
//ini_set('error_reporting', E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);

$client_id = $_SESSION['client_id'];
$locale = $_SESSION['locale'];
$user_id = $_SESSION['id'];
$role = $_SESSION['role_id'];



$dimension = $_SESSION['dimension'];
$gmt = $_SESSION['gmt'];

if($locale == 'en'){
	include "lang/LogistMobile/lang.en.php";
}elseif($locale == 'ru'){
	include "lang/LogistMobile/lang.ru.php";
}elseif($locale == 'ua'){
	include "lang/LogistMobile/lang.ua.php";
}
//require "log.php";
//include "../settings.php";
include "writedblog.php";
include "logger.php";
include "../scripts/ClassLibs/milesconvert.php";
require "../scripts/ClassLibs/ClassGeo.php";

include "LogistMobile_function.php";


// -- old monit task function include --->
//include "LogistMobile_checkwebtask.php";
// <-- old monit task function include ---

$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_HOST='194.0.150.90';
$DB_USER='ilog';
$DB_PASS='1111';
$DB_NAME='tms37';
$db = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
$db2 = 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()) {
	echo "Error connecting to DB";
	exit;
}




//var_dump($dbset);exit();
/*writeToLogFile( ' ->->->->->->->->->->->->->->->->->->->->->->->-> ');
foreach($_REQUEST as $key => $value) {
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' REQUEST parameter: '.$key.' has '.$value );	
}*/





//$arrCarSettings = getMaxCarSettings($db,'444');
//print_r($arrCarSettings);
//$arrCarPercent = getMaxCarPercent($db,$arrCarSettings,8285);
//print_r($arrCarPercent);


if (isset($_REQUEST['tms_order'])) {
	$data = array();
	$time = $_REQUEST['time'];
	$project = $_REQUEST['project'];

	//$query = "SELECT `Order_ID`,`CanUseLeaseCar`,`OriginalAddress`,`Warehouse_ID`,`StartTime`,`EndTime`,`LoadUnloadTime`,`ClientCar`,`Zone_ID`,`IsInTrip`,`GeoX`,`GeoY`,`Project_ID`,`Dispatcher_ID`,`Category_ID`,`IsPassive`,`IsGrouped`,`Date`,`PictureIDInTrip`,`SelectedPictureIDInTrip`,`PictureIDNotInTrip`,`SelectedPictureIDNotInTrip`,`LinkAddress`,`Priority`,`Name`,`Code` FROM `tms_order` WHERE `IsInTrip` = 0 AND DATE(`Date`)='".$time."';";
/*$query = "SELECT o.`Order_ID` AS ID,c.Name AS `Client`,IF(`WarehouseType`=1,'Склад','Магазин') AS WarehouseType,o.Code,o.OriginalAddress,o.`StartTime`,o.`EndTime`,o.`LoadUnloadTime`,o.`GeoX`,o.`GeoY`,o.`Name`
FROM `tms_order` o
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
WHERE `IsInTrip` = 0 AND DATE(`Date`) = '".$time."';";*/
//+++	

/*if($CustomInt == 'Да'){
			$CustomInt = '<img src="app/images/Circle_Green.png" width="16" height="16" />';
}elseif($CustomInt == 'Нет'){
			$CustomInt = '<img src="app/images/Circle_Red.png" width="16" height="16" />';
}*/

/*$query = "SELECT o.`Order_ID` AS ID,c.Name AS `Client`,IF(`WarehouseType`=1,'Склад','Магазин') AS WarehouseType,o.Code,o.OriginalAddress,o.`StartTime`,o.`EndTime`,o.`LoadUnloadTime`,o.`GeoX`,o.`GeoY`,o.`Name`, IF(o.CustomInt=1, '<img src=\"app/images/Circle_Green.png\" width=\"16\" height=\"16\" />', '<img src=\"app/images/Circle_Red.png\" width=\"16\" height=\"16\" />') as CustomInt
FROM `tms_order` o
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
INNER JOIN `tms_project` p ON p.`Project_ID`=o.`Project_ID`
WHERE o.`IsInTrip` = 0 AND DATE(p.`Date`) = '".$time."' AND p.`Client_ID` = ".$client_id.";";*/
/*
$query = "SELECT o.`Order_ID` AS ID,c.Name AS `Client`,IF(`WarehouseType`=1,'Склад','Магазин') AS WarehouseType,o.Code,o.OriginalAddress,TIME(o.`StartTime`) AS StartTime,TIME(o.`EndTime`) AS EndTime,o.`LoadUnloadTime`,o.`GeoX`,o.`GeoY`,o.`Name`, IF(o.CustomInt=1, '<img src=\"app/images/Circle_Green.png\" width=\"16\" height=\"16\" />', '<img src=\"app/images/Circle_Red.png\" width=\"16\" height=\"16\" />') AS CustomInt, o.`Priority`
FROM `tms_order` o
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
INNER JOIN `tms_project` p ON p.`Project_ID`= o.`Project_ID`
WHERE o.`IsInTrip` = 0 AND p.`Client_ID` = ".$client_id." AND p.`Project_ID` = (SELECT Project_ID FROM tms_project WHERE DATE(tms_project.`Date`) = '".$time."' AND tms_project.`Client_ID` = ".$client_id." ORDER BY tms_project.`Project_ID` DESC LIMIT 1);";
*/


	$warehouse_in = warehouse_to_users($db,$user_id,$client_id);
	if(count($warehouse_in)>0){
		$FromWarehouse = " AND o.`FromWarehouse_ID` IN (".implode(",", $warehouse_in).")";
	}else{
		$FromWarehouse = "";
	}

$query = "SELECT o.`Order_ID` AS ID,c.Name AS `Client`,IF(`WarehouseType`=1,'".$warehouse."','".$store."') AS WarehouseType,o.Code,o.OriginalAddress,TIME(o.`StartTime`) AS StartTime,TIME(o.`EndTime`) AS EndTime,o.`LoadUnloadTime`,o.`GeoX`,o.`GeoY`,o.`Name`, IF(o.CustomInt=1, '<img src=\"app/images/Circle_Green.png\" width=\"16\" height=\"16\" />', '<img src=\"app/images/Circle_Red.png\" width=\"16\" height=\"16\" />') AS CustomInt, o.`Priority`, o.`CustomString1`, o.`CustomString2`, o.`CustomString3`, o.`CustomString4`, o.`CustomString5`, o.`CustomString6`, o.`CustomString7`, o.`CustomString8`, z.`Name` AS ZoneName
FROM `tms_order` o
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
INNER JOIN `tms_project` p ON p.`Project_ID`= o.`Project_ID`
LEFT JOIN `tms_zone` z ON z.`Zone_ID` = o.`Zone_ID`
WHERE o.`IsInTrip` = 0 AND p.`Client_ID` = ".$client_id." AND p.`Project_ID` = ".$project." ".$FromWarehouse.";";


	if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			echo'{rows:'.json_encode($result).'}';
	}
	$db->close();
	exit;
}
if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'tms_trip') {
	
	$time = isset($_REQUEST['time']) ? $_REQUEST['time'] : date('Y-m-d', time());
	
	$project = $_REQUEST['project'];
	
	//$query = "SELECT `Trip_ID`,`Name`,`Car_ID`,`TripPrice`,`Distance`,`Time`,`Comments`,`StartTime`,`EndTime` FROM tms_trip WHERE DATE(`StartTime`) = '".$time."';";
	/*$query = "SELECT t.`Trip_ID`,t.`Name`,t.`Car_ID`,d.`Login`,t.`TripPrice`,t.`Distance`,t.`Time`,t.`Comments`,t.`StartTime`,t.`EndTime`,IF(ft.`Closed` IS NULL,'не выпущен','выпущен') AS Closed, t.`Project_ID` FROM `tms_trip` t
LEFT JOIN `tms_facttrip` ft ON ft.`TripId` = t.`Trip_ID`
INNER JOIN `tms_car` c ON c.`Car_ID`=t.`Car_ID`
INNER JOIN `tms_device` d ON c.`Device_id`=d.`ID`
WHERE DATE(t.`StartTime`) = '".$time."';";*/

//+++	
/*$query = "SELECT  t.`Trip_ID` AS TID,t.`Name`,t.`Car_ID`,  CONCAT('',wc.`car_name`,' (',d.`Login`,')') AS Login , d.`Login` AS Login2, t.`TripPrice`,t.`Distance`,t.`Time`,
		t.`Comments`,t.`StartTime`,t.`EndTime`,IF(ft.`Closed` IS NULL,'не выпущен','выпущен') AS Closed, IF(ft.`Closed` IS NULL,0,1) AS Closed2, 
		t.`Project_ID`, IF(t.`Distance` IS NULL,'не просчитан','просчитан') AS Calculated, 
		(SELECT COUNT(`TripPoint_ID`) FROM `tms_trippoint` WHERE `Type` = 1 AND `Trip_ID` = TID) AS ColChek, lt.`is_lock` AS is_lock
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`
 INNER JOIN `tms_device` d ON c.`Device_id`=d.`ID`
 INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
 LEFT JOIN  `tms_web_lock_trip` lt ON lt.`trip_id`=t.`Trip_ID`
INNER JOIN `wm_car_info` wc ON wc.`imei` = d.`Login`  
WHERE DATE(p.`Date`) = '".$time."' AND p.`Client_ID` = ".$client_id." GROUP BY t.`Trip_ID`;";
*/

/*$query = "SELECT  p.`Project_ID`,t.`Trip_ID` AS TID,t.`Name`,t.`Car_ID`,  CONCAT('',wc.`car_name`,' (',d.`Login`,')') AS Login , d.`Login` AS Login2, t.`TripPrice`,t.`Distance`,t.`Time`,
		t.`Comments`,t.`StartTime`,t.`EndTime`,IF(ft.`Closed` IS NULL,'не выпущен','выпущен') AS Closed, IF(ft.`Closed` IS NULL,0,1) AS Closed2, 
		t.`Project_ID`, IF(t.`Distance` IS NULL,'не просчитан','просчитан') AS Calculated, 
		(SELECT COUNT(`TripPoint_ID`) FROM `tms_trippoint` WHERE `Type` = 1 AND `Trip_ID` = TID) AS ColChek, lt.`is_lock` AS is_lock
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`
 INNER JOIN `tms_device` d ON c.`Device_id`=d.`ID`
  LEFT JOIN  `tms_web_lock_trip` lt ON lt.`trip_id`=t.`Trip_ID`
INNER JOIN `wm_car_info` wc ON wc.`imei` = d.`Login`
INNER JOIN `tms_project` p ON p.`Project_ID`= t.`Project_ID`
WHERE p.`Client_ID` = ".$client_id."
AND p.`Project_ID` = (SELECT Project_ID FROM tms_project WHERE DATE(tms_project.`Date`) = '".$time."' AND tms_project.`Client_ID` = ".$client_id." ORDER BY tms_project.`Project_ID` DESC LIMIT 1)
GROUP BY t.`Trip_ID`;";
*/
$query = "SELECT  t.`Trip_ID` AS TID,t.`Name`,t.`Car_ID`,  CONCAT('',wc.`car_name`,' (',d.`Login`,')') AS Login , d.`Login` AS Login2, t.`TripPrice`,t.`Distance`,t.`Time`,
		t.`Comments`,t.`StartTime`,t.`EndTime`,IF(ft.`Closed` IS NULL,'".$not_published."','".$published."') AS Closed, IF(ft.`Closed` IS NULL,0,1) AS Closed2, ft.`Closed` AS Closed3, 
		t.`Project_ID` AS Project_ID2, IF(t.`Distance` IS NULL,'".$not_calculated."','".$calculated."') AS Calculated, IF(t.`Distance` IS NULL,0,1) AS Calculated2, 
		(SELECT COUNT(`TripPoint_ID`) FROM `tms_trippoint` WHERE `Type` = 1 AND `Trip_ID` = TID) AS ColChek, lt.`is_lock` AS is_lock, lt.`is_lock` AS is_lock2, lt.nav, t.`WalkerNum`, TIME(lt.dateRelease)
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`
 INNER JOIN `tms_device` d ON c.`Device_id`=d.`ID`
  LEFT JOIN  `tms_web_lock_trip` lt ON lt.`trip_id`=t.`Trip_ID`
INNER JOIN `wm_car_info` wc ON wc.`imei` = d.`Login`
INNER JOIN `tms_project` p ON p.`Project_ID`= t.`Project_ID`
WHERE p.`Client_ID` = ".$client_id."
AND p.`Project_ID` = ".$project."
GROUP BY t.`Trip_ID` ORDER BY t.`Trip_ID`;";

//echo $query;
	$result=array();
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($TID,$Name,$Car_ID,$Login,$Login2,$TripPrice,$Distance,$Time,$Comments,$StartTime,$EndTime,$Closed,$Closed2,$Closed3,$Project_ID,$Calculated,$Calculated2,$ColChek,$is_lock,$is_lock2,$lock_nav,$WalkerNum,$dateRelease);
	while($stmt->fetch()){
	
	if (is_null($is_lock)) {
		$is_lock = 0;
		$is_lock2 = $no;
	} elseif ((int) $is_lock == 0){
		$is_lock2 = $no;
	} elseif ((int) $is_lock == 1){
		$is_lock2 = $yes;
	}
	
	$StartTime2 = $StartTime;
	if (!is_null($StartTime)) {
		$StartTime = substr($StartTime, 11);
		$EndTime = substr($EndTime, 11);
	}
	
	if( $dimension==1 ){ $Distance = mtomiles($Distance); }
	else{ $Distance = round($Distance/1000, 2); }
					
	$ColChek = $ColChek - 2;
	
	$arrCarSettings = getMaxCarSettings($db2,$Login2,$client_id);
	$arrCarPercent = getMaxCarPercent($db2,$arrCarSettings,$TID,$client_id);
	//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' tms_trip getMaxCarPercent = '.$arrCarSettings[0][0].' '.$arrCarSettings[1][0] );
	
	//print_r($arrCarPercent);
	
		if($Closed3==1){
			$Closed3 = '<img src="app/images/Circle_Green.png" width="16" height="16" />';
		}elseif($Closed3==2){
			$Closed3 = '<img src="app/images/Circle_Red.png" width="16" height="16" />';
		}elseif($Closed3===0){
			$Closed3 = '<img src="app/images/Circle_Blue.png" width="16" height="16" />';
		}else{
			$Closed3 = '';
		}
	
	$result[]=array(
		'TID'=>$TID,
		'Name'=>$Name,
		'Car_ID'=>$Car_ID,
		'Login'=>$Login,
		'Login2'=>$Login2,
		'TripPrice'=>$TripPrice,
		'Distance'=>$Distance,
		'Time'=>$Time,
		'Comments'=>$Comments,
		'StartTime'=>$StartTime,
		'StartTime2'=>$StartTime2,
		'EndTime'=>$EndTime,
		'Closed'=>$Closed,
		'Closed2'=>$Closed2,
		'Closed3'=>$Closed3,
		'Project_ID'=>$Project_ID,
		'Calculated'=>$Calculated,
		'Calculated2'=>$Calculated2,
		'ColChek'=>$ColChek,
		'is_lock'=>$is_lock,
		'is_lock2'=>$is_lock2,
		'lock_nav'=>$lock_nav, // блокировка для выпуска в навижн
		'WalkerNum'=>$WalkerNum,
		'Volume'=>$arrCarPercent[0],
		'Weight'=>$arrCarPercent[1],
		'dateRelease'=>$dateRelease
	);
	}
	}
	$stmt->close();
	}
	
	
	/*if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			
			if (count($result) > 0) {
				foreach ($result as $key => $value) {
					if (is_null($value['is_lock'])) {
						$result[$key]['is_lock']  = 0;
						$result[$key]['is_lock2']  = $no;
					} elseif ((int) $value['is_lock'] == 0){
						$result[$key]['is_lock2']  = $no;
					} elseif ((int) $value['is_lock'] == 1) {
						$result[$key]['is_lock2']  = $yes;
					}
										
					if (!is_null($value['StartTime'])) {
						//print_r($value); exit();
						$result[$key]['StartTime'] = substr($value['StartTime'], 11);
						$result[$key]['EndTime'] = substr($value['EndTime'], 11);
					}
					
					if( $dimension==1 ){ $result[$key]['Distance'] = mtomiles($result[$key]['Distance']); }
					else{ $result[$key]['Distance'] = round($value['Distance']/1000, 2); }
					
					$result[$key]['ColChek'] = $result[$key]['ColChek'] - 2;
					
					//$result[$key]['Volume'] = 1;
					//$result[$key]['Weight'] = 1;
					//$arrCarSettings = getMaxCarSettings($db,'444');
					//$arrCarPercent = getMaxCarPercent($db,$arrCarSettings,8285);
					
				}				
			}			
			
	}*/
	
	
	echo'{rows:'.json_encode($result).'}';
	
	$db->close();
	$db2->close();
	exit;
}
}

if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'tms_device') {
	
	$time = $_REQUEST['time'];
	$project = $_REQUEST['project'];
	
	$imei_in = imei_to_users($db);
	
	
		
	
	//+++	
		/*$query = "
		SELECT d.`Login` AS ID,CONCAT('',c.`car_name`,' (',d.`Login`,')') AS Login FROM `tms_device` d INNER JOIN `wm_car_info` c ON c.`imei` = d.`Login` WHERE d.`Client_id` = ".$client_id."
		AND d.ID NOT IN (
		SELECT Device_id FROM tms_car_snapshot s
		INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID`
		INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
		WHERE DATE(p.`Date`) = DATE('".$time."') AND p.`Client_ID` = ".$client_id." )";*/
	
	// -- с настройкой блокировки
	/*$query = "SELECT d.`Login` AS ID,CONCAT('',c.`car_name`,' (',d.`Login`,')') AS Login FROM `tms_device` d
INNER JOIN `wm_car_info` c ON c.`imei` = d.`Login`
INNER JOIN `tms_car` cr ON cr.`Device_id` = d.`ID`
WHERE d.`Client_id` = ".$client_id." AND cr.Active=1 AND d.ID NOT IN (
		SELECT Device_id FROM tms_car_snapshot s
		INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID`
		INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
		WHERE DATE(p.`Date`) = DATE('".$time."') AND p.`Client_ID` = ".$client_id." );";
*/	
	// -- с учетем нескольких ходок
/*	$query = "SELECT d.`Login` AS ID,CONCAT('',c.`car_name`,' (',d.`Login`,')') AS Login FROM `tms_device` d
INNER JOIN `wm_car_info` c ON c.`imei` = d.`Login`
INNER JOIN `tms_car` cr ON cr.`Device_id` = d.`ID`
WHERE d.`Client_id` = ".$client_id." AND cr.Active=1;";
*/	
	if($role == 1){
		$where = "";
	}else{
		if(count($imei_in)>0){
			$where = " AND d.`Login` IN (".implode(",", $imei_in).")";
		}else{ echo '{rows:[]}'; exit; }
	}
	
	$query = "SELECT d.`Login` AS ID, c.`car_name` AS Login FROM `tms_device` d
INNER JOIN `wm_car_info` c ON c.`imei` = d.`Login`
INNER JOIN tms_car_snapshot cs ON cs.`Device_id` = d.`ID` 
WHERE d.`Client_id` = ".$client_id." AND cs.Active=1 AND cs.`Project_ID` = ".$project." ".$where.";";
	
	//CONCAT('',c.`car_name`,' (',d.`Login`,')') AS Login
	
	/*$query = "SELECT `ID`,`Login` FROM `tms_device` WHERE `Client_id` =$client_id AND ID NOT IN (
			SELECT Device_id FROM tms_car_snapshot s
		INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID`
		INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
		WHERE DATE(p.`Date`) = DATE(NOW()) AND p.LockedDisp_ID=4)";*/
	
	
	/* "SELECT Device_id FROM tms_car_snapshot s
		INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID`
		INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
		WHERE DATE(p.`Date`) = DATE(NOW()) AND p.LockedDisp_ID=4";
 */	
	/* $query = "SELECT d.`ID`,d.`Login` FROM `tms_device` d
		INNER JOIN tms_car_snapshot c ON c.`Device_id`=d.`ID`
		INNER JOIN tms_trip t ON c.`ID`=t.`Car_ID`
		INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
		WHERE d.`Client_id`=$client_id  AND DATE(p.`Date`) = DATE(NOW()) 
		AND d.ID NOT IN (SELECT `ID` FROM `tms_device` WHERE `Client_id` =$client_id)";
	 */
	
	/* $query = "SELECT d.`ID`,d.`Login` FROM `tms_car_snapshot` s
INNER JOIN `tms_project` p ON p.`Project_ID`=s.`Project_ID`
LEFT JOIN `tms_trip` t ON t.`Car_ID`=s.ID
INNER JOIN tms_device d ON s.`Device_id`=d.`ID`
WHERE d.`Client_id`=$client_id AND DATE(p.`Date`) = DATE(NOW())
	AND NOT IN ()"; */
	
	
	
	
	
	
	if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			
			echo'{rows:'.json_encode($result).'}';
	}
	$db->close();
	exit;
}
}


// ++++++++ change car ++++++++++++++++
if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'tms_device_change') {
	
	$time = $_REQUEST['time'];
	$imei_in = imei_to_users($db);
	
	if($role == 1){
		$where = "";
	}else{
		if(count($imei_in)>0){
			$where = " AND d.`Login` IN (".implode(",", $imei_in).")";
		}else{ echo '{rows:[]}'; exit; }
	}
	
	//$query = "SELECT d.`Login` AS ID,CONCAT('',c.`car_name`,' (',d.`Login`,')') AS Login, d.ID AS CarId FROM `tms_device` d INNER JOIN `wm_car_info` c ON c.`imei` = d.`Login` WHERE d.`Client_id` = ".$client_id."";
	$query = "SELECT d.`Login` AS ID,c.`car_name` AS Login, cr.`Car_ID` AS CarId FROM `tms_device` d INNER JOIN `tms_car` cr ON cr.`Device_id` = d.`ID` INNER JOIN `wm_car_info` c ON c.`imei` = d.`Login` WHERE d.`Client_id` = ".$client_id." ".$where."";
	//CONCAT('',c.`car_name`,' (',d.`Login`,')')
	if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			
			echo'{rows:'.json_encode($result).'}';
	}
	$db->close();
	exit;
}
}

if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'checkCarIn') {
	
	$ID = $_REQUEST['ID'];
	$CarId = $_REQUEST['CarId'];
	$time = $_REQUEST['time'];
	$project = $_REQUEST['project'];
	
	/*$query = "SELECT s.`Car_ID` FROM tms_car_snapshot s
		INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID`
		INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
		WHERE DATE(p.`Date`) = DATE('".$time."') AND p.`Client_ID` = ".$client_id." AND s.`Car_ID` = ".$CarId."";
	*/
	$query = "SELECT s.`Car_ID` FROM tms_car_snapshot s
		INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID`
		INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID`
		WHERE p.`Project_ID` =".$project." AND p.`Client_ID` = ".$client_id." AND s.`Car_ID` = ".$CarId."";
	
	
	if($stmt = $db->prepare($query)){
	$stmt->execute();
	$stmt->bind_result($Device_id);
	while($stmt->fetch()){}
	$stmt->close();
		echo '{"success":true,"message":"'.$Device_id.'"}';
	}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; }
	
	$db->close();
	exit;
}
}

if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'changeCarIn') {
		$carid = $_REQUEST['carid'];
		$login = $_REQUEST['login'];
		$loginold = $_REQUEST['loginold'];
		$trid = $_REQUEST['trid'];
		$between = $_REQUEST['between'];
		$time = $_REQUEST['time'];
		$project = $_REQUEST['project'];
		
		//$query = "SELECT t.`Trip_ID`,s.ID,s.`Car_ID` FROM tms_car_snapshot s INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID` INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID` WHERE DATE(p.`Date`) = DATE('".$time."') AND p.`Client_ID` = ".$client_id." AND s.Car_ID = ".$carid.";";
		//$query = "SELECT tms_car_snapshot.ID, tms_car_snapshot.`Car_ID` FROM tms_car_snapshot LEFT JOIN tms_car ON tms_car_snapshot.Car_ID = tms_car.Car_ID WHERE tms_car.Car_ID = ".$carid." AND tms_car_snapshot.Project_ID = (SELECT Project_ID FROM tms_trip WHERE Trip_ID = ".$trid." LIMIT 1)";
		$query = "SELECT tms_car_snapshot.ID, tms_car_snapshot.`Car_ID` FROM tms_car_snapshot LEFT JOIN tms_car ON tms_car_snapshot.Car_ID = tms_car.Car_ID WHERE tms_car.Car_ID = ".$carid." AND tms_car_snapshot.Project_ID = ".$project.";";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		//$stmt->bind_result($Trip_ID,$Snap_ID_new,$Car_ID_new);
		$stmt->bind_result($Snap_ID_new,$Car_ID_new);
		while($stmt->fetch()){}
		$stmt->close();
		
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' Snap_ID_new = '.$Snap_ID_new );
		
		if($Snap_ID_new || $Snap_ID_new!="" || $Snap_ID_new!=0){
		
		//$query = "SELECT t.`Car_ID`,f.`Car_ID` FROM `tms_trip` t INNER JOIN  `tms_facttrip` f ON t.`Trip_ID` = f.`TripId` WHERE t.`Trip_ID` = ".$trid.";";
		//$query = "SELECT s.ID,s.`Car_ID` FROM tms_car_snapshot s INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID` INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID` WHERE DATE(p.`Date`) = DATE('".$time."') AND p.`Client_ID` = ".$client_id." AND s.Car_ID = (SELECT c.Car_ID FROM tms_device d INNER JOIN tms_car c ON d.ID = c.`Device_id` WHERE d.login = ".$loginold.")";
		$query = "SELECT s.ID,s.`Car_ID` FROM tms_car_snapshot s INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID` INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID` WHERE p.`Project_ID` = ".$project." AND p.`Client_ID` = ".$client_id." AND s.Car_ID = (SELECT c.Car_ID FROM tms_device d INNER JOIN tms_car c ON d.ID = c.`Device_id` WHERE d.login = '".$loginold."')";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->bind_result($Snap_ID_old,$Car_ID_old);
		while($stmt->fetch()){}
		$stmt->close();
		}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
		}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
		
		if(!isset($between) || $between==""){
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' !between' );
			$query = "UPDATE `tms_trip` SET `Car_ID` = ".$Snap_ID_new." WHERE `Trip_ID` = ".$trid.";";
			//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' '.$query );
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$stmt->close();
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			
			$query = "SELECT ID FROM `tms_facttrip` WHERE `TripId` = ".$trid.";";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
			$result = $db->query($query);
			if ($result->num_rows) {
				$query = "UPDATE `tms_facttrip` f INNER JOIN `tms_checkpoint` c ON c.`FactTripID` = f.`ID` SET f.Car_ID = ".$Car_ID_new.", c.CarID = ".$Car_ID_new." WHERE f.`TripId` = ".$trid.";";
				writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
				if($stmt = $db->prepare($query)){
				if($stmt->execute()){
				$stmt->close();
				}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
				}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' num_rows empty '); }
			$result->close();
		}else{
			//echo 'between';
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' between' );
			$query = "UPDATE `tms_trip` SET `Car_ID` = ".$Snap_ID_new." WHERE `Trip_ID` = ".$trid.";";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$stmt->close();
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			
			$query = "SELECT ID FROM `tms_facttrip` WHERE `TripId` = ".$trid.";";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
			$result = $db->query($query);
			if ($result->num_rows) {
				$query = "UPDATE `tms_facttrip` f INNER JOIN `tms_checkpoint` c ON c.`FactTripID` = f.`ID` SET f.Car_ID = ".$Car_ID_new.", c.CarID = ".$Car_ID_new." WHERE f.`TripId` = ".$trid.";";
				writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
				if($stmt = $db->prepare($query)){
				if($stmt->execute()){
				$stmt->close();
				}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
				}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' num_rows empty '); }
			$result->close();
			
			
			//$query = "SELECT t.`Trip_ID` FROM tms_car_snapshot s INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID` INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID` WHERE DATE(p.`Date`) = DATE('".$time."') AND p.`Client_ID` = ".$client_id." AND s.Car_ID = ".$carid.";";
			$query = "SELECT t.`Trip_ID` FROM tms_car_snapshot s INNER JOIN tms_trip t ON s.`ID`=t.`Car_ID` INNER JOIN `tms_project` p ON p.`Project_ID`=t.`Project_ID` WHERE p.`Project_ID` = ".$project." AND p.`Client_ID` = ".$client_id." AND s.Car_ID = ".$carid." AND t.`Trip_ID` <> ".$trid.";";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$stmt->bind_result($Trip_IDs);
			while($stmt->fetch()){
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' Trip_IDs = '.$Trip_IDs );
			}
			$stmt->close();
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			
			$query = "UPDATE `tms_trip` SET `Car_ID` = ".$Snap_ID_old." WHERE `Trip_ID` = ".$Trip_IDs.";";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$stmt->close();
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			
			$query = "SELECT ID FROM `tms_facttrip` WHERE `TripId` = ".$Trip_IDs.";";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
			$result = $db->query($query);
			if ($result->num_rows) {
				$query = "UPDATE `tms_facttrip` f INNER JOIN `tms_checkpoint` c ON c.`FactTripID` = f.`ID` SET f.Car_ID = ".$Car_ID_old.", c.CarID = ".$Car_ID_old." WHERE f.`TripId` = ".$Trip_IDs.";";
				writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
				if($stmt = $db->prepare($query)){
				if($stmt->execute()){
				$stmt->close();
				}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
				}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; $db->close(); exit; }
			}
			$result->close();
		}
		
		echo '{"success":true,"message":""}';
		
		}else{ echo '{"success":false,"message":"Снимок для данного устройства не создан"}'; }
		
		}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; }
		}else{ echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}'; }
		$db->close();
		exit;	
}
}
// ++++++++ change car ++++++++++++++++




if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'tms_project') {
	
	//$time = $_REQUEST['time'];
	//$time = isset($_REQUEST['time']) ? $_REQUEST['time'] : date('Y-m-d', time());
	if(isset($_REQUEST['time'])){
		$time = $_REQUEST['time'];
	}
	else{
		date_default_timezone_set("UTC");
		$date = time() + $gmt*60*60;
		
		$time =  date('Y-m-d', $date);
	}
	
	//$query = "SELECT `Project_ID` AS ID,`Name` FROM `tms_project`;";
	//+++	
	
	$project_in = project_to_users($db);
	
	if($role == 1){
		$query = "SELECT `Project_ID` AS ID,`Name` FROM `tms_project` WHERE DATE(`Date`)='".$time."' AND `Executing`=1 AND `Client_ID` = ".$client_id." ORDER BY `Project_ID` DESC;";
	}else{
		if(count($project_in)>0){
		$query = "SELECT `Project_ID` AS ID,`Name` FROM `tms_project` WHERE `Project_ID` IN (".implode(",", $project_in).") AND DATE(`Date`)='".$time."' AND `Executing`=1 AND `Client_ID` = ".$client_id." ORDER BY `Project_ID` DESC;";
		}else{ echo '{rows:[]}'; exit; }
	}
	if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			echo'{rows:'.json_encode($result).'}';
	}
	$db->close();
	exit;
}
}

if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'tms_warehouse') {
	
	$warehouse_in = warehouse_to_users($db,$user_id,$client_id);
	
	if(count($warehouse_in)>0){
		$query = "SELECT w.`Warehouse_ID` AS ID, w.`Name`, w.Code FROM `tms_warehouse` w INNER JOIN `wm_warehouse_to_users` wu ON wu.`warehouse_id` = w.`Warehouse_ID` WHERE w.`WarehouseType` = 1 AND w.`instanceID` = ".$client_id." AND `user_id` = ".$user_id." ORDER BY wu.`default` DESC;";
	}else{
		$query = "SELECT `Warehouse_ID` AS ID, w.`Name`, w.Code FROM `tms_warehouse` w WHERE w.`WarehouseType` = 1 AND `instanceID` = ".$client_id.";";
	}
	
	//+++
	$i=0;
	$data = array();
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($ID,$Name,$Code);
	while($stmt->fetch()){
		//$Name = str_replace(array("\n","\r\n","\r"), '', $Name);
		$data[$i] = array(
		'ID' => $ID,
		'Name' => trim($Name),
		'Code' => trim($Code)
		);
		$i++;
	}
	$stmt->close();
	}
	}
	/*
	if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			echo'{rows:'.json_encode($result).'}';
	}
	*/
	
	
	echo'{rows:'.json_encode($data).'}';
	$db->close();
	exit;
}
}

if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'tms_trippoint') {
	
	//$userID = $_REQUEST['userID'];
	//$time = $_REQUEST['time'];
	$FactTripID = $_REQUEST['FactTripID'];
	
	/*function updateonerrortime(){
			global $db;
			global $FactTripID;
			$query = "UPDATE tms_trip SET `Distance` = NULL, `Time` = NULL, `StartTime` = NULL, `EndTime` = NULL WHERE `Trip_ID` = ".$FactTripID.";";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
	}*/
	
	//$query = "SELECT `TripPoint_ID`,`Type`,`Point_ID`,`Address`,`EstimateTime`,`DurationInPoint`,`OrderInTrip`,`Trip_ID`,`Route`,`Distance`,`Time`,`GeoX`,`GeoY`,`Name`,`WaitTime`,`LateTime` FROM `tms_trippoint` WHERE `Trip_ID` = ".$FactTripID.";";
	
// --- work query without facttrip point state --->	
	$query = "SELECT * FROM ((SELECT t.`TripPoint_ID` AS ID, c.Name AS `Client`,IF(w.`WarehouseType`=1,'".$warehouse."','".$store."') AS WarehouseType,o.Code,t.`Address`,TIME(o.`StartTime`) AS StartTime,TIME(o.`EndTime`) AS EndTime,o.`LoadUnloadTime`,o.`GeoX`,o.`GeoY`,o.Name, t.OrderInTrip, t.`EstimateTime`, t.`DurationInPoint`, t.Distance , IF(o.CustomInt=1, 'Да', 'Нет') as CustomInt, o.`Priority`, o.`CustomString1`, o.`CustomString2`, o.`CustomString3`, o.`CustomString4`, o.`CustomString5`, o.`CustomString6`, o.`CustomString7`, o.`CustomString8`, t.`Trip_ID`, w.`WarehouseType` AS WarehouseType2
	FROM `tms_trippoint` t
INNER JOIN `tms_loadunloadpoint` lp ON
t.`Point_ID` = lp.`LoadUnloadPoint_ID`
LEFT JOIN `tms_order` o ON lp.`OrderWarehouse_ID`=o.`Order_ID`
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
WHERE t.`Trip_ID` = ".$FactTripID." AND lp.`IsOrderPoint` = 1)
UNION ALL
(SELECT t.`TripPoint_ID` AS ID, c.Name AS `Client`, IF(w.`WarehouseType`=1,'".$warehouse."','".$store."') AS WarehouseType,w.Code,w.Address,TIME(w.`StartServeTime`) AS StartTime,TIME(w.`EndServeTime`) AS EndTime,w.`StaticUnloadTime`,w.`GeoX`,w.`GeoY`,w.`Name`, t.OrderInTrip, t.`EstimateTime`, t.`DurationInPoint`, t.Distance, 'Нет', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, t.`Trip_ID`,w.`WarehouseType` AS WarehouseType2 FROM `tms_trippoint` t
INNER JOIN `tms_loadunloadpoint` lp ON t.`Point_ID` = lp.`LoadUnloadPoint_ID`
INNER JOIN `tms_warehouse` w ON lp.`OrderWarehouse_ID` = w.`Warehouse_ID`
LEFT JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
WHERE t.`Trip_ID` = ".$FactTripID.")) t ORDER BY  t.`OrderInTrip`;";
// <--- work query without facttrip point state ---	


// --- work query with facttrip point state --->	
/*	$query = "SELECT * FROM ((SELECT t.`TripPoint_ID` AS ID, c.Name AS `Client`,IF(w.`WarehouseType`=1,'".$warehouse."','".$store."') AS WarehouseType,o.Code,t.`Address`,TIME(o.`StartTime`) AS StartTime,TIME(o.`EndTime`) AS EndTime,o.`LoadUnloadTime`,o.`GeoX`,o.`GeoY`,o.Name, t.OrderInTrip, t.`EstimateTime`, t.`DurationInPoint`, t.Distance , IF(o.CustomInt=1, 'Да', 'Нет') AS CustomInt, o.`Priority`, o.`CustomString1`, o.`CustomString2`, o.`CustomString3`, o.`CustomString4`, o.`CustomString5`, o.`CustomString6`, o.`CustomString7`, o.`CustomString8`
,ch.`PointState`
FROM `tms_trippoint` t
INNER JOIN `tms_loadunloadpoint` lp ON
t.`Point_ID` = lp.`LoadUnloadPoint_ID`
LEFT JOIN `tms_order` o ON lp.`OrderWarehouse_ID`=o.`Order_ID`
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`


LEFT JOIN `tms_trip` tr ON tr.`Trip_ID` = t.`Trip_ID`
LEFT JOIN `tms_facttrip` ft ON ft.`TripId` = t.`Trip_ID`
LEFT JOIN `tms_checkpoint` ch ON ch.`FactTripID` = ft.`ID`

WHERE t.`Trip_ID` = ".$FactTripID." AND lp.`IsOrderPoint` = 1 GROUP BY t.`TripPoint_ID`)

UNION ALL

(SELECT t.`TripPoint_ID` AS ID, c.Name AS `Client`, IF(w.`WarehouseType`=1,'".$warehouse."','".$store."') AS WarehouseType,w.Code,w.Address,TIME(w.`StartServeTime`) AS StartTime,TIME(w.`EndServeTime`) AS EndTime,w.`StaticUnloadTime`,w.`GeoX`,w.`GeoY`,w.`Name`, t.OrderInTrip, t.`EstimateTime`, t.`DurationInPoint`, t.Distance, 'Нет', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
,ch.`PointState`
FROM `tms_trippoint` t
INNER JOIN `tms_loadunloadpoint` lp ON t.`Point_ID` = lp.`LoadUnloadPoint_ID`
INNER JOIN `tms_warehouse` w ON lp.`OrderWarehouse_ID` = w.`Warehouse_ID`
LEFT JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`

LEFT JOIN `tms_trip` tr ON tr.`Trip_ID` = t.`Trip_ID`
LEFT JOIN `tms_facttrip` ft ON ft.`TripId` = t.`Trip_ID`
LEFT JOIN `tms_checkpoint` ch ON ch.`FactTripID` = ft.`ID`

WHERE t.`Trip_ID` = ".$FactTripID." GROUP BY t.`TripPoint_ID`)) t ORDER BY  t.`OrderInTrip`;";
*/
// <--- work query with facttrip point state ---

	$PointStateArray = get_point_state($db,$FactTripID);
	
	
	//echo $query;
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($ID, $Client,$WarehouseType,$Code,$OriginalAddress,$StartTime,$EndTime,$LoadUnloadTime,$GeoX,$GeoY,$Name, $OrderInTrip, $EstimateTime, $DurationInPoint, $Distance, $CustomInt, $Priority, $CustomString1, $CustomString2, $CustomString3, $CustomString4, $CustomString5, $CustomString6, $CustomString7, $CustomString8, $Trip_ID, $WarehouseType2);
	
		

	$i=1;
	$k = 0;
	while ($stmt->fetch()) {
//add		
		if( strtotime($EstimateTime) ){
			$DurationUNIX = (int)($DurationInPoint*60) + strtotime($EstimateTime);
			$Duration = date('H:i:s', $DurationUNIX);
			
			$EstimateTime = date('H:i:s',strtotime($EstimateTime));
			
			
		}else{
			//$EstimateTime = substr($EstimateTime, 11 ,-6);
			$Duration = '';
			//updateonerrortime();
		}
//endadd	

		if($CustomInt == 'Да'){
			$CustomInt = '<img src="app/images/Circle_Green.png" width="16" height="16" />';
		}elseif($CustomInt == 'Нет'){
			$CustomInt = '<img src="app/images/Circle_Red.png" width="16" height="16" />';
		}
		
		 
		if($dimension==1){ $Distance = mtomiles($Distance); }
		else{ $Distance = Round($Distance/1000); }
		
	
	$data[$k] = array(
			'ID' => $ID,
			'Client' => $Client,
			'WarehouseType' => $WarehouseType,
			'Code' => $Code,
			'OriginalAddress' => $OriginalAddress,
			'StartTime' => $StartTime,
			'EndTime' => $EndTime,
			'LoadUnloadTime' => $LoadUnloadTime,
			'GeoX' => $GeoX,
			'GeoY' => $GeoY,
			'Name' => $Name,
			'OrderInTrip' => $OrderInTrip,
			'OrderInTrip2' => $i,
			'EstimateTime' => $EstimateTime,
			'Duration' => $Duration,
			'Distance' => $Distance,
			'CustomInt' => $CustomInt,
			'Priority' => $Priority,
			'CustomString1' => $CustomString1,
			'CustomString2' => $CustomString2,
			'CustomString3' => $CustomString3,
			'CustomString4' => $CustomString4,
			'CustomString5' => $CustomString5,
			'CustomString6' => $CustomString6,
			'CustomString7' => $CustomString7,
			'CustomString8'=> $CustomString8,
			'PointState'=> $PointStateArray[$k],
			'Trip_ID'=> $Trip_ID,
			'DurationInPoint'=> $DurationInPoint,
			'WarehouseType2' => $WarehouseType2
		);
	$i++;
	$k++;
	}
	$stmt->close();
	echo'{rows:'.json_encode($data).'}';
	/*if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			echo'{rows:'.json_encode($result).'}';
	}*/
	$db->close();
	exit;
}
}

if (isset($_REQUEST['addTrip'])) {

$date = $_REQUEST['date'];
$flag = $_REQUEST['flag'];
$trip_id = $_REQUEST['trip_id'];
$trip_name = $_REQUEST['trip_name'];
$trip_master = $_REQUEST['trip_master'];
$trip_project = $_REQUEST['trip_project'];

$trip_warehouse1 = $_REQUEST['trip_warehouse1'];
$trip_warehouse2 = $_REQUEST['trip_warehouse2'];

$trip_houre = $_REQUEST['trip_houre'];
$trip_min = $_REQUEST['trip_min'];
$trip_comments = $_REQUEST['trip_comments'];

$dates = date('Y-m-d').' '.$trip_houre.':'.$trip_min.':00';
$datee = date('Y-m-d').' 23:59:59';

		//+++ not use, project create server by xml
		/*if( is_numeric($trip_project) ){
			
		}else{
			$query = "INSERT INTO `tms_project` (`Name`) VALUES ('".$trip_project."')";
            $stmt = $db->prepare($query);
            $stmt->execute();
			$trip_project = $db->insert_id;
			$stmt->close();
		}*/
		
		$WalkerNum = 0;
		$query = "SELECT `WalkerNum` FROM tms_trip t
INNER JOIN tms_car_snapshot cs ON cs.ID = t.`Car_ID`
INNER JOIN tms_car c ON cs.`Car_ID` = c.`Car_ID`
INNER JOIN tms_device d ON d.`ID` = c.`Device_id`
WHERE d.`Login` = '".$trip_master."' AND t.`Project_ID` = ".$trip_project." ORDER BY `Trip_ID` DESC LIMIT 1;";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->bind_result($WalkerNum);
		$stmt->fetch();
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		$WalkerNum = $WalkerNum + 1;
		
		if($flag='add'){
			
			//$query = "INSERT INTO `tms_trip` (`Name`,`Car_ID`,`Comments`,`StartTime`,`EndTime`,`Project_ID`,`Dispatcher_ID`) VALUES  ('".$trip_name."',(SELECT c.`Car_ID` FROM tms_car c INNER JOIN tms_device d ON d.`ID` = c.`Device_id` WHERE d.`Login`=".$trip_master."),'".$trip_comments."','".$dates."','".$datee."',".$trip_project.",4);";
			$query = "INSERT INTO `tms_trip` (`Name`,`Car_ID`,`Comments`,`StartTime`,`EndTime`,`Project_ID`,`Dispatcher_ID`,`WalkerNum`) VALUES  ('".$trip_name."',(SELECT ID FROM `tms_car_snapshot` WHERE `Car_ID`=(SELECT c.`Car_ID` FROM tms_car c INNER JOIN tms_device d ON d.`ID` = c.`Device_id` WHERE d.`Login`='".$trip_master."') AND `Project_ID`=$trip_project),'".$trip_comments."','".$dates."','".$datee."',".$trip_project.",4,".$WalkerNum.");";
			//echo $query;
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
            if($stmt->execute()){
			$trip_id = $db->insert_id;
			$stmt->close();
				echo '{"success":true,"message":""}';
			}else{
				echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
				writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error );
				exit;
			}
			}else{
				echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
				writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error );
				exit;
			}
			
			
			
			
			
			
			//$queryW1 = "SELECT lp.`LoadUnloadPoint_ID` FROM `tms_warehouse2` w INNER JOIN `tms_loadunloadpoint` lp ON lp.`OrderWarehouse_ID` = w.`Warehouse_ID` WHERE w.`WarehouseType` = ".$trip_warehouse1.";";
			
			$query = "INSERT INTO `tms_loadunloadpoint_index` VALUES (NULL)";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$LoadUnloadPoint_ID0 = $db->insert_id;
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			
			$query = "INSERT INTO `tms_loadunloadpoint` (`LoadUnloadPoint_ID`,`IsOrderPoint`, `OrderWarehouse_ID`, `IsStartWarehouse`) VALUES (".$LoadUnloadPoint_ID0.", 0, ".$trip_warehouse1.", 1)";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt ->execute()){
			$loadPoint_Id = $db->insert_id;
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			
			/* -- `IsOrderPoint`=0
			-- `OrderWarehouse_ID` from form
			-- `IsStartWarehouse` 0 and 1 */
			
			/*$query = "INSERT INTO `tms_trippoint`
(`Type`,`Point_ID`,`Address`,`Trip_ID`,`GeoX`,`GeoY`,`Name`, `OrderInTrip`)
SELECT 8,$loadPoint_Id,`Address`,$trip_id,`GeoX`,`GeoY`,`Name`,1
FROM `tms_warehouse` WHERE `Warehouse_ID`=$trip_warehouse1";*/
			
			$query = "INSERT INTO `tms_trippoint_index` VALUES (NULL)";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$trippoint_index_ID0 = $db->insert_id;
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			
			$query = "INSERT INTO `tms_trippoint` (`TripPoint_ID`,`Type`,`Point_ID`,`Address`,`Trip_ID`,`GeoX`,`GeoY`,`Name`, `OrderInTrip`) SELECT $trippoint_index_ID0,1,$loadPoint_Id,`Address`,$trip_id,`GeoX`,`GeoY`,`Name`,1 FROM `tms_warehouse` WHERE `Warehouse_ID`=$trip_warehouse1";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
            if($stmt->execute()){
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			
			
			
			
			
			
			$query = "INSERT INTO `tms_loadunloadpoint_index` VALUES (NULL)";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$LoadUnloadPoint_ID = $db->insert_id;
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			
			//$queryW2 = "SELECT lp.`LoadUnloadPoint_ID` FROM `tms_warehouse2` w INNER JOIN `tms_loadunloadpoint` lp ON lp.`OrderWarehouse_ID` = w.`Warehouse_ID` WHERE w.`WarehouseType` = ".$trip_warehouse2.";";
			$query = "INSERT INTO `tms_loadunloadpoint` (`LoadUnloadPoint_ID`, `IsOrderPoint`, `OrderWarehouse_ID`, `IsStartWarehouse`) VALUES ($LoadUnloadPoint_ID, 0, $trip_warehouse2, 0)";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt ->execute()){
			$unLoadPoint_Id = $db->insert_id;
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			
			/*$query = "INSERT INTO `tms_trippoint`
(`Type`,`Point_ID`,`Address`,`Trip_ID`,`GeoX`,`GeoY`,`Name`, `OrderInTrip`)
SELECT 8,$unLoadPoint_Id,`Address`,$trip_id,`GeoX`,`GeoY`,`Name`,2
FROM `tms_warehouse` WHERE `Warehouse_ID`=$trip_warehouse2";*/
			
			$query = "INSERT INTO `tms_trippoint_index` VALUES (NULL)";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$trippoint_index_ID1 = $db->insert_id;
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			
			$query = "INSERT INTO `tms_trippoint` (`TripPoint_ID`,`Type`,`Point_ID`,`Address`,`Trip_ID`,`GeoX`,`GeoY`,`Name`, `OrderInTrip`) SELECT $trippoint_index_ID1,1,$unLoadPoint_Id,`Address`,$trip_id,`GeoX`,`GeoY`,`Name`,2 FROM `tms_warehouse` WHERE `Warehouse_ID`=$trip_warehouse2";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
            if($stmt->execute()){
			$stmt->close();
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' addTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}
		
		$db->close();
		exit;
}

if (isset($_REQUEST['bindPointToTrip'])) {
	
	//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ' );
	
	$dropPosition = $_REQUEST['dropPosition'];
	$pointsArr = $_REQUEST['pointsArr'];
	$fromPointId = $_REQUEST['fromPointId'];
	
	$trip_id = $_REQUEST['trip_id'];
	$tripPointNewId = array();
	#$order_id = $_REQUEST['order_id'];
	
	
		$query = "SELECT MAX(ID) FROM `tms_lupointcargo_index`";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($ID);
		$stmt->fetch();
		$stmt->close();
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip MAXID = '.$ID );
		
		$query = "SELECT COUNT(`OrderCargo_ID`) FROM `tms_ordercargo` WHERE `Order_ID` IN (".implode(',',$pointsArr).")";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($pointsArr2);
		$stmt->fetch();
		$stmt->close();
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip pointsArr2 = '.$pointsArr2 );
		
		if($pointsArr2!==0){ $nextID = $ID + $pointsArr2; }else{ $nextID = $ID + 1; }
		
		$pointsArr3 = count($pointsArr);
		
		$query = "INSERT INTO `tms_lupointcargo_index` (`ID`) VALUES ($nextID)";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
	
	foreach ($pointsArr as $order_id) {
		//$ID++;
		
		$query = "SELECT `OriginalAddress`, `GeoX`, `GeoY`, `Name`, Comments FROM `tms_order` WHERE `Order_ID`=$order_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($OriginalAddress, $GeoX, $GeoY, $Name, $Comments);
		while ($stmt->fetch()) {
			$orderDataArr = array(
					'address' => $OriginalAddress,
					'GeoX' => $GeoX,
					'GeoY' => $GeoY,
					'Name' => $Name,
					'Comments' => $Comments
			);
		}
		
		$query = "SELECT lp.`OrderWarehouse_ID` FROM tms_trippoint  tp INNER JOIN `tms_loadunloadpoint` lp ON lp.`LoadUnloadPoint_ID`=tp.`Point_ID` WHERE tp.`Trip_ID`=$trip_id AND lp.`IsStartWarehouse`=0";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($fromWarehouse_ID);
		$stmt->fetch();
		$stmt->close();
		
		
		$query = "SELECT MAX(ID) FROM `tms_loadunloadpoint_index`";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($loadunloadpoint_index);
		$stmt->fetch();
		$stmt->close();
		$loadunloadpoint_index = $loadunloadpoint_index + $pointsArr3;
		
		//$query = "INSERT INTO `tms_loadunloadpoint_index` VALUES (NULL)";
		$query = "INSERT INTO `tms_loadunloadpoint_index` VALUES (".$loadunloadpoint_index.");";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$LoadUnloadPoint_ID = $db->insert_id;
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		
		
		
		
		$query = "SELECT MAX(ID) FROM `tms_trippoint_index`";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($trippoint_index);
		$stmt->fetch();
		$stmt->close();
		$trippoint_index = $trippoint_index + $pointsArr3;
		
		$query = "INSERT INTO `tms_trippoint_index` VALUES (".$trippoint_index.");";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$trippoint_ID = $db->insert_id;
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		
		
		
		
		$query = "SELECT `StartTime`, `EndTime`, `Date` FROM `tms_order` WHERE `Order_ID` = $order_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($StartTime, $EndTime, $Date);
		$stmt->fetch();
		$stmt->close();
		
		if($StartTime == ''){ $StartTime = 'NULL'; }else{ $StartTime = "'".$StartTime."'"; }
		if($EndTime == ''){ $EndTime = 'NULL'; }else{ $EndTime = "'".$EndTime."'"; }
		
		// SELECT StartTime, EndTime, Date FROM tms_order WHERE Order_ID = $order_id 
		$query = "INSERT INTO `tms_loadunloadpoint` (`LoadUnloadPoint_ID`, `OrderWarehouse_ID`, `FromWarehouse_ID`, `IsOrderPoint`, `StartTime`, `EndTime`, `FixedDate`) VALUES ($LoadUnloadPoint_ID, $order_id, $fromWarehouse_ID, 1, $StartTime, $EndTime, '$Date')";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$point_id = $db->insert_id;
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		$query = "SELECT MIN(`OrderInTrip`) FROM `tms_trippoint` WHERE `Trip_ID` = $trip_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($OrderInTrip2);
		$stmt->fetch();
		$stmt->close();
		writeToLogFile( ' -> OrderInTrip Selected '.date('Y-m-d H:i:s').' '.$OrderInTrip2 );
		
		
		$query = "SELECT `OrderInTrip` FROM `tms_trippoint` WHERE `Trip_ID`=$trip_id  ORDER BY `OrderInTrip` DESC LIMIT 1";		
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($maxOrderInTrip);
		$stmt->fetch();
		$stmt->close();
		
		//$OrderInTrip = $maxOrderInTrip;
		$maxOrderInTrip = $maxOrderInTrip--;
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip maxOrderInTrip = '.$maxOrderInTrip );
		
		//$query = "INSERT INTO `tms_trippoint` (`Type`, `Point_ID`, `OrderInTrip`, `Address`, `GeoX`, `GeoY`, `Name`, `Trip_ID`)
		//VALUES (1, $point_id, 0, '$orderDataArr[address]', $orderDataArr[GeoX], $orderDataArr[GeoY], '$orderDataArr[Name]', $trip_id) ";
		
		$query = "INSERT INTO `tms_trippoint` (`TripPoint_ID`,`Type`, `Point_ID`, `OrderInTrip`, `Address`, `GeoX`, `GeoY`, `Name`, `Trip_ID`, `Comments`) VALUES (".$trippoint_ID.",1, $point_id, $maxOrderInTrip, '".addslashes($orderDataArr[address])."', $orderDataArr[GeoX], $orderDataArr[GeoY], '".addslashes($orderDataArr[Name])."', $trip_id, '".addslashes($orderDataArr[Comments])."')";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$tripPointNewId[] = $db->insert_id;
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		/*$secondStorageOrderInTrip = ++$OrderInTrip;
		$query = "UPDATE `tms_trippoint` SET OrderInTrip=$secondStorageOrderInTrip
		WHERE `Trip_ID`=$trip_id AND OrderInTrip=$maxOrderInTrip AND Type=8";
		
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close(); */
		
		$query = "UPDATE tms_trip SET `Distance`= NULL, `Time`=NULL, `StartTime`=NULL, `EndTime`=NULL WHERE Trip_ID=$trip_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		$query = "UPDATE `tms_order` SET IsInTrip=1 WHERE `Order_ID`=$order_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		//+++
		/*$query = "INSERT INTO `tms_lupointcargo_index` VALUES (NULL)";
		$stmt = $db->prepare($query);
		$stmt->execute();
		$Lupointcargo_ID = $db->insert_id;
		$stmt->close();
		*/
		
		/*
		$query = "INSERT INTO `tms_lupointcargo`
		(Lupointcargo_ID,`DimensionUnitVolume_ID`,`CargoValueVolume`,`LUPoint_ID`,`DimensionUnitWeight_ID`,`CargoValueWeight`,`Specialization_ID`,`Comments`,`UnloadType_ID`,`Name`,`CargoValueCount`,`DimensionUnitCount_ID`,`Cost`)
		SELECT ".$Lupointcargo_ID.",`DimensionUnitVolume_ID`,`CargoValueVolume`,".$point_id.",`DimensionUnitWeight_ID`,`CargoValueWeight`,`Specialization_ID`,`Comments`,`UnloadType_ID`,`Name`,`CargoValueCount`,`DimensionUnitCount_ID`,`Cost`
		FROM `tms_ordercargo` WHERE `Order_ID` = ".$order_id.";";
		*/
		
		
		
		
		/*
		$query = "INSERT INTO `tms_lupointcargo`
		(`LUPointCargo_ID`,`DimensionUnitVolume_ID`,`CargoValueVolume`,`LUPoint_ID`,`DimensionUnitWeight_ID`,`CargoValueWeight`,`Specialization_ID`,`Comments`,`UnloadType_ID`,`Name`,`CargoValueCount`,`DimensionUnitCount_ID`,`Cost`)
		SELECT ".$ID.", `DimensionUnitVolume_ID`,`CargoValueVolume`,".$point_id.",`DimensionUnitWeight_ID`,`CargoValueWeight`,`Specialization_ID`,`Comments`,`UnloadType_ID`,`Name`,`CargoValueCount`,`DimensionUnitCount_ID`,`Cost`
		FROM `tms_ordercargo` WHERE `Order_ID` = ".$order_id.";";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();
		*/
		
		$ordercargoArr = array();
		$query = "SELECT `DimensionUnitVolume_ID`,`CargoValueVolume`,`DimensionUnitWeight_ID`,`CargoValueWeight`,`Specialization_ID`,`Comments`,`UnloadType_ID`,`Name`,`CargoValueCount`,`DimensionUnitCount_ID`,`Cost` FROM `tms_ordercargo` WHERE `Order_ID` = ".$order_id.";";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->bind_result($DimensionUnitVolume_ID,$CargoValueVolume,$DimensionUnitWeight_ID,$CargoValueWeight,$Specialization_ID,$Comments,$UnloadType_ID,$Name,$CargoValueCount,$DimensionUnitCount_ID,$Cost);
		while( $stmt->fetch() ) {
			if(!$UnloadType_ID || $UnloadType_ID==""){ $UnloadType_ID = 'NULL'; }
			$ordercargoArr[] = array(
					'DimensionUnitVolume_ID' => $DimensionUnitVolume_ID,
					'CargoValueVolume' => $CargoValueVolume,
					'DimensionUnitWeight_ID' => $DimensionUnitWeight_ID,
					'CargoValueWeight' => $CargoValueWeight,
					'Specialization_ID' => $Specialization_ID,
					'Comments' => $Comments,
					'UnloadType_ID' => $UnloadType_ID,
					'Name' => $Name,
					'CargoValueCount' => $CargoValueCount,
					'DimensionUnitCount_ID' => $DimensionUnitCount_ID,
					'Cost' => $Cost
			);
		}
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		foreach ($ordercargoArr as $value){
			if(!$value['Specialization_ID'] || $value['Specialization_ID'] == "" ){ $value['Specialization_ID'] = 'NULL'; } 
			$query = "INSERT INTO `tms_lupointcargo` (`LUPointCargo_ID`,`DimensionUnitVolume_ID`,`CargoValueVolume`,`LUPoint_ID`,`DimensionUnitWeight_ID`,`CargoValueWeight`,`Specialization_ID`,`Comments`,`UnloadType_ID`,`Name`,`CargoValueCount`,`DimensionUnitCount_ID`,`Cost`) VALUES (".$ID.",".$value['DimensionUnitVolume_ID'].",".$value['CargoValueVolume'].",".$point_id.",".$value['DimensionUnitWeight_ID'].",".$value['CargoValueWeight'].",".$value['Specialization_ID'].",'".$value['Comments']."',".$value['UnloadType_ID'].",'".$value['Name']."',".$value['CargoValueCount'].",".$value['DimensionUnitCount_ID'].",".$value['Cost'].");";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
			$stmt->close();
			$ID++;
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
			}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}
		
		//+++
	}
	
		$strPoint = implode(',', $tripPointNewId);
		if ($dropPosition == 'after') {
			$afterDropPoint = '>';
			$beforeDropPoint = '<=';
		} elseif ($dropPosition == 'before') {
			$afterDropPoint = '>=';
			$beforeDropPoint = '<';
		}
		
		
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip strPoint = '.$strPoint.' dropPosition = '.$dropPosition.' afterDropPoint = '.$afterDropPoint.' beforeDropPoint = '.$beforeDropPoint );
		
		//after drop point
		$pointAfterArr = array();
		/*$query = "SELECT `TripPoint_ID` FROM `tms_trippoint` WHERE OrderInTrip $afterDropPoint (
		SELECT OrderInTrip FROM tms_trippoint WHERE `TripPoint_ID`=$fromPointId AND OrderInTrip<>0
		) AND `TripPoint_ID` NOT IN ($strPoint) AND `Type`<>8 AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";*/
		
		$query = "SELECT `TripPoint_ID` FROM `tms_trippoint`
		LEFT JOIN `tms_loadunloadpoint` ON tms_trippoint.`Point_ID` = tms_loadunloadpoint.`LoadUnloadPoint_ID`
		WHERE OrderInTrip $afterDropPoint (
		SELECT OrderInTrip FROM tms_trippoint 
		WHERE `TripPoint_ID`=$fromPointId AND OrderInTrip<>0
		) AND `TripPoint_ID` NOT IN ($strPoint) AND tms_loadunloadpoint.`IsOrderPoint` = 1 AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";// 
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->bind_result($TripPoint_ID);
		while ($stmt->fetch()) {
			$pointAfterArr[] =  $TripPoint_ID;
		}
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		$sd = implode(',', $pointAfterArr);
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip sd = '.$sd.' count pointAfterArr = '.count($pointAfterArr) );
		
		$pointBeforeArr = array();
		/*$query = "SELECT `TripPoint_ID` FROM `tms_trippoint` WHERE OrderInTrip $beforeDropPoint (
		SELECT OrderInTrip FROM tms_trippoint WHERE `TripPoint_ID`=$fromPointId AND OrderInTrip<>0
		) AND `TripPoint_ID` NOT IN ($strPoint) AND `Type`<>8 AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";*/
		$query = "SELECT `TripPoint_ID` FROM `tms_trippoint` 
		LEFT JOIN `tms_loadunloadpoint` ON tms_trippoint.`Point_ID` = tms_loadunloadpoint.`LoadUnloadPoint_ID`
		WHERE OrderInTrip $beforeDropPoint (
		SELECT OrderInTrip FROM tms_trippoint WHERE `TripPoint_ID`=$fromPointId AND OrderInTrip<>0
		) AND `TripPoint_ID` NOT IN ($strPoint) AND tms_loadunloadpoint.`IsOrderPoint` = 1
		AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($TripPoint_ID);
		while ($stmt->fetch()) {
			$pointBeforeArr[] =  $TripPoint_ID;
		}
		$stmt->close();
		
		
		$query = "SELECT MIN(`OrderInTrip`) FROM `tms_trippoint` WHERE `Trip_ID` = $trip_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($bind_result);
		while($stmt->fetch()){
			$OrderInTrip2 = $bind_result;
		}
		$stmt->close();
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip OrderInTrip2 = '.$OrderInTrip2 );
		
		$orderInTrip = 2;
		if($OrderInTrip2 > 1){ $orderInTrip++; }
		
		

		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip orderInTrip = '.$orderInTrip );
		
		foreach ($pointBeforeArr as $idPoint) {
			$query = "UPDATE `tms_trippoint` SET OrderInTrip =$orderInTrip WHERE `TripPoint_ID`=$idPoint";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
			//echo $query."\n";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
			$orderInTrip++;
		}
		foreach ($tripPointNewId as $idPoint) {
			$query = "UPDATE `tms_trippoint` SET OrderInTrip =$orderInTrip WHERE `TripPoint_ID`=$idPoint";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
			//echo $query."\n";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
			$orderInTrip++;
		}
		foreach ($pointAfterArr as $idPoint) {
			$query = "UPDATE `tms_trippoint` SET OrderInTrip =$orderInTrip WHERE `TripPoint_ID`=$idPoint";
			writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
			//echo $query."\n";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
			$orderInTrip++;
		}
		
		//$query = "SELECT TripPoint_ID FROM tms_trippoint WHERE Trip_ID=$trip_id AND `Type`=8 ORDER BY `OrderInTrip` DESC LIMIT 1";
		$query = "SELECT TripPoint_ID FROM tms_trippoint 
		LEFT JOIN `tms_loadunloadpoint` ON tms_trippoint.`Point_ID` = tms_loadunloadpoint.`LoadUnloadPoint_ID`
		WHERE Trip_ID=$trip_id AND `tms_loadunloadpoint`.`IsOrderPoint` = 0 ORDER BY `OrderInTrip` DESC LIMIT 1";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($TripPoint_ID);
		$stmt->fetch();
		$stmt->close();
		
		//if(count($pointAfterArr) == 0){ $orderInTrip++; }
		
		
		$query = "UPDATE `tms_trippoint` SET OrderInTrip =$orderInTrip WHERE `TripPoint_ID`=$TripPoint_ID";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' bindPointToTrip QUERY = '.$query );
		//echo $query."\n";
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();
		
		$db->close();
		exit;
}

function unBindPointFromTrip($trip_id,$pointsArr){
//if (isset($_REQUEST['unBindPointFromTrip'])) {
	
	//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ' );
	#$tripPoint_id = $_REQUEST['tripPoint_id'];
	
	//$trip_id = $_REQUEST['trip_id'];
	//$pointsArr = $_REQUEST['pointsArr'];	
	
	global $db;
	
	$strPoint = implode(',', $pointsArr);
	#check  is sklad
	$type = false;
	//$query = "SELECT `Type` FROM `tms_trippoint` WHERE `TripPoint_ID` IN ($strPoint) AND `Type`=8 LIMIT 1";
	$query = "SELECT `Type` FROM `tms_trippoint` LEFT JOIN `tms_loadunloadpoint` ON tms_trippoint.`Point_ID` = tms_loadunloadpoint.`LoadUnloadPoint_ID` WHERE `TripPoint_ID` IN ($strPoint) AND tms_loadunloadpoint.`IsOrderPoint` = 0 LIMIT 1;";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($type);
	$stmt->fetch();
	$stmt->close();
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip type = '.$type );
	if ($type) {
		echo 0;
		exit;
	}
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
	
	$arrOfBindPoints = array();
	$query = "SELECT `TripPoint_ID` FROM `tms_trippoint` WHERE Trip_ID=$trip_id AND TripPoint_ID NOT IN ($strPoint) ORDER BY `OrderInTrip`";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($TripPoint_ID);
	while ($stmt->fetch()) {
		$arrOfBindPoints[] = $TripPoint_ID;
	}
	$stmt->close();
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
	
	
	$orderInTrip = 1;
	foreach ($arrOfBindPoints as $TripPoint_ID) {
		$query = "UPDATE `tms_trippoint` SET `OrderInTrip`=$orderInTrip WHERE `Trip_ID`=$trip_id AND TripPoint_ID=$TripPoint_ID";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		$orderInTrip++;
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
	
	}
	
	foreach ($pointsArr as $tripPoint_id) {
		$query = "SELECT l.`OrderWarehouse_ID`, l.`LoadUnloadPoint_ID`, tp.`OrderInTrip`  FROM `tms_trippoint` tp INNER JOIN `tms_loadunloadpoint` l ON l.`LoadUnloadPoint_ID`=tp.`Point_ID` WHERE tp.`TripPoint_ID`=$tripPoint_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->bind_result($order_id, $LoadUnloadPoint_ID, $OrderInTrip);
		$stmt->fetch();
		$stmt->close();
		
	
		
		$query = "DELETE FROM tms_trippoint WHERE TripPoint_ID=$tripPoint_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		
		$query = "DELETE FROM tms_loadunloadpoint WHERE `LoadUnloadPoint_ID`=$LoadUnloadPoint_ID";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		
		$query = "UPDATE `tms_order` SET IsInTrip=0 WHERE `Order_ID`=$order_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		
		$query = "UPDATE tms_trip SET `Distance` = NULL, `Time`=NULL, `StartTime`=NULL, `EndTime`=NULL WHERE Trip_ID=$trip_id";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		
		/*$tripPointDataArr = array();
		 $query = "SELECT `TripPoint_ID`, `OrderInTrip` FROM `tms_trippoint`
		WHERE `Trip_ID`=$trip_id AND `OrderInTrip`>$OrderInTrip ";
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($TripPoint_ID, $OrderInTrip);
		while ($stmt->fetch()) {
		$tripPointDataArr[] = array(
				'TripPoint_ID' => $TripPoint_ID,
				'OrderInTrip' => $OrderInTrip
		);
		}
		
		if (count($tripPointDataArr)>0) {
		foreach ($tripPointDataArr as $valueArr) {
		$newOrderInTrip = $valueArr['OrderInTrip']--;
		$query = "UPDATE `tms_trippoint` SET OrderInTrip=$newOrderInTrip,  `Distance` = NULL, `Time`= NULL
		WHERE TripPoint_ID=$valueArr[TripPoint_ID]";
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();
		//_________________
			
		}
		} else {
		$query = "UPDATE `tms_trippoint` SET `Distance` = NULL, `Time`= NULL WHERE `Trip_ID`=$trip_id";
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();
		} */
		
		
		//+++
		$query = "DELETE FROM `tms_lupointcargo` WHERE `LUPoint_ID` = ".$tripPoint_id.";";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip QUERY = '.$query );
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
		
		/*$query = "DELETE FROM `tms_lupointcargo_index` WHERE `ID` IN (SELECT `LUPointCargo_ID` FROM `tms_lupointcargo` WHERE `LUPoint_ID` = ".$tripPoint_id.");";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();*/
		//+++
		
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' unBindPointFromTrip ERRORQUERY = '.$db->errno.' '.$db->error ); }
		
	}
	
	//$db->close();
	//exit;
}
if (isset($_REQUEST['unBindPointFromTrip'])) {
	
	$trip_id = $_REQUEST['trip_id'];
	$pointsArr = $_REQUEST['pointsArr'];
	
	unBindPointFromTrip($trip_id,$pointsArr);
	
	$db->close();
	exit;
}





if (isset($_REQUEST['CalculateTrip'])) {
	
	$Login = $_REQUEST['Login'];
	$TripID = $_REQUEST['Trip_ID'];
	$WalkerNum = $_REQUEST['WalkerNum'];
	$WithChange = $_REQUEST['Withchange'];
	
	//if($_REQUEST['Withchange'] == true){$WithChange = 1;}else{$WithChange = 0;}
	
	if( count($Login) > 1){
	
	$i=0;
	$xml = '<Data InstanceID="'.$client_id.'" ><p type="CalculateTripNotOne">';
	while($i<count($Login)){
		$countid=0;
		$query = "SELECT COUNT(t.`Trip_ID`) FROM tms_trip t INNER JOIN tms_car_snapshot cs ON cs.ID = t.`Car_ID` INNER JOIN tms_car c ON cs.`Car_ID` = c.`Car_ID` INNER JOIN tms_device d ON d.`ID` = c.`Device_id` WHERE d.`Login` = '".$Login[$i]."' AND t.`Project_ID` = (SELECT `Project_ID` FROM `tms_trip` WHERE `Trip_ID`=".$TripID[$i].") AND t.`Distance` IS NULL AND t.`Trip_ID` NOT IN(".$TripID[$i].") AND t.WalkerNum < ".$WalkerNum[$i].";";
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').'CalculateTrip  while INSTANCEID = '.$client_id.' QUERY = '.$query );
		if ($stmt = $db->prepare($query)){
			$stmt->execute();
			$stmt->bind_result($countid);
			$stmt->fetch();
			$stmt->close();
		}
	
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').'CalculateTrip while INSTANCEID = '.$client_id.' countid = '.$countid );
		if((int)$countid > 0){

		}else{
			$xml .= '<p type="CalculateTripNotOne"  TripID="'.$TripID[$i].'" ChangeOrder="'.$WithChange.'" />';
		}
		$i++;
	}
	$xml .= '</p></Data>';
	
	}else{
	
	$countid=0;
	$query = "SELECT COUNT(t.`Trip_ID`) FROM tms_trip t INNER JOIN tms_car_snapshot cs ON cs.ID = t.`Car_ID` INNER JOIN tms_car c ON cs.`Car_ID` = c.`Car_ID` INNER JOIN tms_device d ON d.`ID` = c.`Device_id` WHERE d.`Login` = '".$Login[0]."' AND t.`Project_ID` = (SELECT `Project_ID` FROM `tms_trip` WHERE `Trip_ID`=".$TripID[0].") AND t.`Distance` IS NULL AND t.`Trip_ID` NOT IN(".$TripID[0].") AND t.WalkerNum < ".$WalkerNum[0].";";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').'CalculateTrip  INSTANCEID = '.$client_id.' QUERY = '.$query );
	if ($stmt = $db->prepare($query)){
      $stmt->execute();
	  $stmt->bind_result($countid);
      $stmt->fetch();
	  $stmt->close();
    }
	
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').'CalculateTrip INSTANCEID = '.$client_id.' countid = '.$countid );
	if((int)$countid > 0){
		echo false;
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').'CalculateTrip INSTANCEID = '.$client_id.' FALSE' );
		$db->close();
		exit;
	}
	
	/*$xml = '<Data Login="'.$Login.'" Password="'.$password.'">
	<p type="CalculateTrip">
	<p type="CalculateTrip"  TripID="'.$TripID.'" />
	</p>
	</Data>';*/
	
	$xml = '<Data InstanceID="'.$client_id.'" >
	<p type="CalculateTrip">
	<p type="CalculateTrip"  TripID="'.$TripID[0].'" ChangeOrder="'.$WithChange.'" />
	</p>
	</Data>';
	
	
	
	}
	
	
	
	
	//$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`) VALUES ('".$xml."',".$Login.")";
	
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`) VALUES ('".$xml."',".$client_id.")";
	
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').'CalculateTrip  INSTANCEID = '.$client_id.' QUERY = '.$query );
	if ($stmt = $db->prepare($query)) {
    if ($stmt->execute()) {
	  $last_id = $db->insert_id;
      $stmt->close();
	  //echo true;
	  
		//$res = check($last_id);
		//echo $res;
		echo json_encode(array('success'=>true, 'message'=>$last_id));
	  
    }else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	
	$db->close();
	exit;
}

if ($_REQUEST['func'] == 'ReleaseTrip') {
	
	
	$Project_ID = $_REQUEST['Project_ID'];
	//$Login = $_REQUEST['Login'];
	$arrayLogin = $_REQUEST['arrayLogin'];
	$arrayTripId = $_REQUEST['arrayTripId'];
	$arraySt = $_REQUEST['arraySt'];
	
	try {
	
	$res_checkcar = '';
	$strTripId = '';
	foreach ($arrayTripId as $key => $value){
		$rescheckcar = checkCarBeforeTripRelease($db,$arrayTripId[$key],$arrayLogin[$key],$Project_ID,$arraySt[$key]);
		if(count($rescheckcar) > 0){
			$strTripId .= $arrayTripId[$key].' ';
			$res_checkcar .= implode(' ',$rescheckcar);
			$arrayTripId = array_diff($arrayTripId, array($arrayTripId[$key]));
			
		}
	}
	$arrayTripId = array_values($arrayTripId);
	//print_r($arrayTripId);
	//exit;
	
	
	if(count($arrayTripId) > 0){
	$i=0;
	$xml = '<Data InstanceID="'.$client_id.'" >
			<p type="ReleaseTripNotAll">';
	while ($i < count($arrayTripId)){
		$xml .= '<p type="ReleaseTripNotAll"  Trip_ID="'.$arrayTripId[$i].'" ProjectID="'.$Project_ID.'"  />';
		
		
		$i++;
	}
	$xml .= '</p>
			</Data>';
	
	/*
	$xml = '<Data InstanceID="'.$client_id.'" >
			<p type="ReleaseTrip">
			<p type="ReleaseTrip"  ProjectID="'.$Project_ID.'" />
			</p>
			</Data>';
    */
	
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`) VALUES ('".$xml."',".$client_id.")";
	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
		$last_id = $db->insert_id;
		$stmt->close();
		//$res = check($last_id);
		//echo $res;
		
		/*$query = "UPDATE tms_web_lock_trip SET dateRelease = '".date('Y-m-d H:i:s')."' WHERE `trip_id` IN (".implode(',',$arrayTripId).");";
		if ($stmt = $db->prepare($query)){
		if ($stmt->execute()){
		$stmt->close();
		}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
		}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
		*/
		foreach ($arrayTripId as $trip_id) {
		$id = false;
		$query = "SELECT id FROM `tms_web_lock_trip` WHERE `trip_id`=$trip_id";
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->bind_result($id);
		$stmt->fetch();
		$stmt->close();
		if ($id) {
			$query = "UPDATE `tms_web_lock_trip` SET dateRelease = '".date('Y-m-d H:i:s')."' WHERE id=$id";
		} else {
			$query = "INSERT INTO `tms_web_lock_trip` (`trip_id`, dateRelease) VALUES ($trip_id, '".date('Y-m-d H:i:s')."')";
		}
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();
		unset($id);
		}}
		}
		//echo $query; exit;
		
		$result_message = array('success'=>true, 'message'=>$last_id);
		if($res_checkcar && $res_checkcar!=""){
			$result_message['exception'] = 'Рейс(ы) '.$strTripId.' не были выпущены, данное авто используется в рейсе(ах) '.$res_checkcar.' дождитесь выполнения, либо закройте их вручную';
		}
		echo json_encode($result_message);
	
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	
	}else{ echo json_encode(array('success'=>false, 'message'=>'Рейс(ы) '.$strTripId.' не были выпущены, данное авто используется в рейсе(ах) '.$res_checkcar.' дождитесь выполнения, либо закройте их вручную' )); }
	
	} catch (Exception $e) {
		echo json_encode(array('success'=>false, 'message'=>$e->getMessage()));
	}
	
	$db->close();
	exit;
	
	
}


if ($_REQUEST['func'] == 'ReleaseTripNav') {
	
	$Project_ID = $_REQUEST['Project_ID'];
	//$Login = $_REQUEST['Login'];
	$arrayTripId = $_REQUEST['arrayTripId'];
	
	
	$i=0;
	$xml = '<Data InstanceID="'.$client_id.'" >
			<p type="SendToNavisionReleaseTripNotAll">';
	while ($i < count($arrayTripId)){
		$xml .= '<p type="SendToNavisionReleaseTripNotAll"  Trip_ID="'.$arrayTripId[$i].'" ProjectID="'.$Project_ID.'"  />';
		$i++;
	}
	$xml .= '</p>
			</Data>';
	
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`) VALUES ('".$xml."',".$client_id.")";
	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
		$last_id = $db->insert_id;
		$stmt->close();
		
		/*$query = "UPDATE tms_web_lock_trip SET nav = 1 WHERE `trip_id` IN (".implode(',',$arrayTripId).");";
		if ($stmt = $db->prepare($query)){
		if ($stmt->execute()){
		$stmt->close();
		}
		}*/
		foreach ($arrayTripId as $trip_id) {
		$id = false;
		$query = "SELECT id FROM `tms_web_lock_trip` WHERE `trip_id`=$trip_id";
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->bind_result($id);
		$stmt->fetch();
		$stmt->close();
		if ($id) {
			$query = "UPDATE `tms_web_lock_trip` SET nav = 1 WHERE id=$id";
		} else {
			$query = "INSERT INTO `tms_web_lock_trip` (`trip_id`, nav) VALUES ($trip_id, 1)";
		}
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();
		unset($id);
		}}
		}
		
		echo json_encode(array('success'=>true, 'message'=>$last_id));
	
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	
	$db->close();
	exit;
}



if (isset($_REQUEST['RecalculateTrip'])) {
	
	$Login = $_REQUEST['Login'];
	$TripID = $_REQUEST['Trip_ID'];
	

	/*$xml = '<Data Login="'.$Login.'" Password="'.$password.'">
	<p type="RecalculateTrip">
	<p type="RecalculateTrip"  TripID="'.$TripID.'" />
	</p>
	</Data>';*/
    
	$xml = '<Data InstanceID="'.$client_id.'" >
	<p type="RecalculateTrip">
	<p type="RecalculateTrip"  TripID="'.$TripID.'" />
	</p>
	</Data>';
	
	//$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`) VALUES ('".$xml."',".$Login.")";
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`) VALUES ('".$xml."',".$client_id.")";

	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
	  $last_id = $db->insert_id;
      $stmt->close();
	  //echo $last_id;
	  
		//$res = check($last_id);
		//echo $res;
		echo json_encode(array('success'=>true, 'message'=>$last_id));
	  
    }else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	
	
	
	$db->close();
	exit;
}



if (isset($_REQUEST['AutoplanTrip'])) {

	$time = $_REQUEST['time'];
	$Project_ID = $_REQUEST['project'];
	
	$type = $_REQUEST['type'];
	if(!isset($_REQUEST['type']) || $_REQUEST['type']==""){ $type = 0;  }
	
	//$query = "SELECT `Project_ID` FROM `tms_project` WHERE DATE(`Date`)='".$time."' AND `Executing`=1 AND `Client_ID` = ".$client_id." ORDER BY `Date` DESC;";
	/*$query = "SELECT `Project_ID` FROM `tms_project` WHERE DATE(`Date`)='".$time."' AND `Executing`=1 AND `Client_ID` = ".$client_id." ORDER BY `Project_ID` DESC LIMIT 1;";
	if($stmt = $db->prepare($query)){
	$stmt->execute();
	$stmt->bind_result($Project_ID);
	$stmt->fetch();
	$stmt->close();
	}*/
	
	$xml = '<Data InstanceID="'.$client_id.'" >
	<p type="AutoplanTrip">
	<p type="AutoplanTrip" ProjectID="'.$Project_ID.'" AutoplanType = "'.$type.'" />
	</p>
	</Data>';
	
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`,`in_progress`) VALUES ('".$xml."',".$client_id.",0)";
	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
		$last_id = $db->insert_id;
		$stmt->close();
	  
		//$res = check($last_id);
		//echo $res;
		
		echo json_encode(array('success'=>true, 'message'=>$last_id));
		
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }

	$db->close();
	exit;
	
	
}



if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'getRoutePath') {
	$TripID = $_REQUEST['TripID'];
	$trip_route = "";
	$query = "SELECT ASTEXT(`Route`) FROM `tms_trippoint` WHERE `Trip_ID` = ".$TripID." ORDER BY `OrderInTrip` ASC;";
	//echo $query;
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($str);
	while ($stmt->fetch()) {
		if($str || $str!=""){
		$str = substr($str,11,strlen($str)-1);
		$str = substr($str,0,strlen($str)-1);
		$trip_route .= $str;
		}
	}
	$stmt->close();
	//if($trip_route!=""){ $trip_route = substr($str,0,strlen($str)-1); }
	echo $trip_route;
	$db->close();
	exit;
}
}

if(isset($_REQUEST['func'])){
if ($_REQUEST['func'] == 'getRoutePoint') {
	$TripID = $_REQUEST['TripID'];
	$arrOfCoordinates = array();
	
	//$query = "SELECT `GeoX`,`GeoY`,`TripPoint_ID`,`Name`,`Address`,`EstimateTime`,`Type` FROM `tms_trippoint` WHERE `Trip_ID` = ".$TripID.";";	 
	
	$query = "SELECT t.`GeoX`, t.`GeoY`, t.`TripPoint_ID`, t.`Name`, t.`Address`, t.`EstimateTime`, lp.IsOrderPoint AS `Type`, IF((lp.`IsOrderPoint` = 0), '', c.Name) AS Client, t.`OrderInTrip` FROM
	`tms_trippoint` t
	INNER JOIN `tms_loadunloadpoint` lp ON
	t.`Point_ID` = lp.`LoadUnloadPoint_ID`
	LEFT JOIN `tms_order` o ON lp.`OrderWarehouse_ID`=o.`Order_ID`
	LEFT JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
	LEFT JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`	
	WHERE `Trip_ID` = ".$TripID." ORDER BY t.`OrderInTrip` ASC;";
	
	$i = 1;$j=0;
	if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->bind_result($GeoX,$GeoY,$Id,$Name,$Address,$EstimateTime,$Type,$Client,$OrderInTrip);
		while ($stmt->fetch()) {
			$arrOfCoordinates[$j] = array(
				'GeoX' => $GeoX,
				'GeoY' => $GeoY,
				'Id' => $Id,
				'Name' => $Name,
				'Address' => $Address,
				'EstimateTime' => $EstimateTime,
				'Phone' => '',
				'Type' => $Type,
				'Client' => $Client,
				'OrderInTrip' => $OrderInTrip,
				'OrderInTrip2' => $i
			);
			$i++;$j++;
		}
		$stmt->close();
		echo json_encode($arrOfCoordinates);
	}
	$db->close();
	exit;
}
}

if (isset($_REQUEST['resetCalculate'])) {
	sleep(1);
}


if (isset($_REQUEST['removePointPositionInTrip'])) {
	$fromPointId = $_REQUEST['fromPointId'];
	$pointsArr = $_REQUEST['pointsArr'];
	$dropPosition = $_REQUEST['dropPosition'];
	$trip_id = $_REQUEST['trip_id'];
	$strPoint = implode(',', $pointsArr);
	$Type = false;
	//$query = "SELECT `Type` FROM `tms_trippoint` WHERE `TripPoint_ID` IN ($strPoint) AND Trip_ID=$trip_id AND `Type`=8 LIMIT 1";
	$query = "SELECT `Type` FROM `tms_trippoint` 
LEFT JOIN `tms_loadunloadpoint` ON tms_trippoint.`Point_ID` = tms_loadunloadpoint.`LoadUnloadPoint_ID`
WHERE `TripPoint_ID` IN ($strPoint) AND tms_loadunloadpoint.`IsOrderPoint` = 0 LIMIT 1";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($Type);
	$stmt->fetch();
	$stmt->close();
	if ($Type) {
		echo 0;
		exit;
	}
	
	if ($dropPosition == 'after') {
		$afterDropPoint = '>';
		$beforeDropPoint = '<=';
	} elseif ($dropPosition == 'before') {
		$afterDropPoint = '>=';
		$beforeDropPoint = '<';
	}
	
	
	//after drop point
	$pointAfterArr = array();
	/*$query = "SELECT `TripPoint_ID` FROM `tms_trippoint` WHERE OrderInTrip $afterDropPoint (
	SELECT OrderInTrip FROM tms_trippoint WHERE `TripPoint_ID`=$fromPointId
	) AND `TripPoint_ID` NOT IN ($strPoint) AND `Type`<>8 AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";*/
	
	$query = "SELECT `TripPoint_ID` FROM `tms_trippoint` 
LEFT JOIN `tms_loadunloadpoint` ON tms_trippoint.`Point_ID` = tms_loadunloadpoint.`LoadUnloadPoint_ID`
WHERE OrderInTrip $afterDropPoint (
	SELECT OrderInTrip FROM tms_trippoint WHERE `TripPoint_ID`=$fromPointId
	) AND `TripPoint_ID` NOT IN ($strPoint) AND tms_loadunloadpoint.`IsOrderPoint` = 1 AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";
	
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($TripPoint_ID);
	while ($stmt->fetch()) {
	$pointAfterArr[] =  $TripPoint_ID;
	}
	$stmt->close();
	
	$pointBeforeArr = array();
	/*$query = "SELECT `TripPoint_ID` FROM `tms_trippoint` WHERE OrderInTrip $beforeDropPoint (
	SELECT OrderInTrip FROM tms_trippoint WHERE `TripPoint_ID`=$fromPointId
	) AND `TripPoint_ID` NOT IN ($strPoint) AND `Type`<>8 AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";*/
	$query = "SELECT `TripPoint_ID` FROM `tms_trippoint`
	LEFT JOIN `tms_loadunloadpoint` ON tms_trippoint.`Point_ID` = tms_loadunloadpoint.`LoadUnloadPoint_ID`
	WHERE OrderInTrip $beforeDropPoint (
	SELECT OrderInTrip FROM tms_trippoint WHERE `TripPoint_ID`=$fromPointId
	) AND `TripPoint_ID` NOT IN ($strPoint) AND tms_loadunloadpoint.`IsOrderPoint` = 1 AND Trip_ID=$trip_id ORDER BY `OrderInTrip`";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($TripPoint_ID);
	while ($stmt->fetch()) {
	$pointBeforeArr[] =  $TripPoint_ID;
	}
	$stmt->close();
	
	
		$query = "SELECT MIN(`OrderInTrip`) FROM `tms_trippoint` WHERE `Trip_ID` = $trip_id";
		writeToLogFile( ' -> orderInTrip '.date('Y-m-d H:i:s').' '.$query );
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($OrderInTrip2);
		$stmt->fetch();
		$stmt->close();
		writeToLogFile( ' -> OrderInTrip Selected '.date('Y-m-d H:i:s').' '.$OrderInTrip2 );
		
		$orderInTrip = 2;
		if($OrderInTrip2 > 1){ $orderInTrip++; }

	
	
	foreach ($pointBeforeArr as $idPoint) {
	$query = "UPDATE `tms_trippoint` SET OrderInTrip =$orderInTrip WHERE `TripPoint_ID`=$idPoint";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->close();
	$orderInTrip++;
	}
	foreach ($pointsArr as $idPoint) {
	$query = "UPDATE `tms_trippoint` SET OrderInTrip =$orderInTrip WHERE `TripPoint_ID`=$idPoint";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->close();
	$orderInTrip++;
	}
	foreach ($pointAfterArr as $idPoint) {
	$query = "UPDATE `tms_trippoint` SET OrderInTrip =$orderInTrip WHERE `TripPoint_ID`=$idPoint";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->close();
	$orderInTrip++;
	}
	
	$query = "UPDATE `tms_trippoint` SET `Distance` = NULL, `Time`= NULL WHERE `Trip_ID`=$trip_id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->close();
	
	$db->close();
	exit;
}



if (isset($_REQUEST['checkProjectExist'])) {
	
	$time = $_REQUEST['time'];
	
	//$query = "SELECT COUNT(Project_ID) AS RESULT FROM `tms_project` WHERE DATE(`Date`)=DATE(NOW()) AND `Executing`=1;";
	//$query = "SELECT COUNT(Project_ID) AS RESULT FROM `tms_project` WHERE DATE(`Date`)=DATE('".$time."') AND `Executing`=1;";
	$query = "SELECT COUNT(Project_ID) AS RESULT FROM `tms_project` WHERE DATE(`Date`)=DATE('".$time."') AND `Executing`=1 AND `Client_ID`=".$client_id.";";
	if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			echo json_encode($result);
	}
	$db->close();
	exit;
}

if (isset($_REQUEST['addProject'])) {
	$name_project = $_REQUEST['name_project'];
	$date_project = $_REQUEST['date_project'];
	
	$xml = '<Data InstanceID="'.$client_id.'" >
	<p type="CreateProject">
	<p type="CreateProject"  ProjectName="'.$name_project.'" ProjectDate="'.$date_project.'" />
	</p>
	</Data>';
    
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`) VALUES ('".$xml."',".$client_id.")";
	  //echo $query;
	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
	  $last_id = $db->insert_id;
      $stmt->close();
	  //echo $last_id;
	  
		//$res = check($last_id);
		//echo $res;
		
		echo json_encode(array('success'=>true, 'message'=>$last_id));
		
		$_SESSION['addProjectName'] = $name_project;
		$_SESSION['addProjectDate'] = $date_project;
		
    }else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	
	
	
	$db->close();
	exit;
}

if (isset($_REQUEST['unLockTrip'])) {
	$trip_id = $_REQUEST['trip_id'];
	$query = "UPDATE `tms_web_lock_trip` SET is_lock=0, nav=0 WHERE  `trip_id`=$trip_id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->close();
	$db->close();
	exit;
}

if (isset($_REQUEST['lockTrips'])) {
	$projectId = $_REQUEST['projectId'];
	$tripIdArr = array();
	$query = "SELECT `Trip_ID` FROM `tms_trip` WHERE  `Project_ID`=$projectId AND `Distance` IS NOT NULL";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($Trip_ID);
	while ($stmt->fetch()) {
		$tripIdArr[] = $Trip_ID;
	}
	$stmt->close();
	foreach ($tripIdArr as $trip_id) {
		$id = false;
		$query = "SELECT id FROM `tms_web_lock_trip` WHERE `trip_id`=$trip_id";
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($id);
		$stmt->fetch();
		$stmt->close();
		if ($id) {
			$query = "UPDATE `tms_web_lock_trip` SET is_lock=1 WHERE id=$id";
		} else {
			$query = "INSERT INTO `tms_web_lock_trip` (`trip_id`, `is_lock`) VALUES ($trip_id, 1)";
		}
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->close();
		unset($id);
	}
	
	$db->close();
	exit;
}




if (isset($_REQUEST['DeleteTrip'])) {
	$arrayOrdersId = $_REQUEST['arrayOrdersId'];	
	$i=0;
	
	$xml = '<Data InstanceID="'.$client_id.'" >
			<p type="DeleteTrip">';
	
	while ($i < count($arrayOrdersId)){
		$xml .= '<p type="Trip"  Trip_ID="'.$arrayOrdersId[$i].'"  />';
		$i++;
	}
	
	$xml .= '</p>
			</Data>';
	
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`,`in_progress`) VALUES ('".$xml."',".$client_id.",0)";
	//echo $query;
	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
	$last_id = $db->insert_id;
	$stmt->close();
	  
	//$res = check($last_id);
	//echo $res;
	echo json_encode(array('success'=>true, 'message'=>$last_id));
	
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
}







if (isset($_REQUEST['tms_order_onmap'])) {
	$data = array();
	$time = $_REQUEST['time'];
	$project = $_REQUEST['project'];

/*
$query = "SELECT o.`Order_ID`, c.Name AS `Client`,o.Code,o.OriginalAddress,o.`GeoX`,o.`GeoY`,o.`Name`
FROM `tms_order` o
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
INNER JOIN `tms_project` p ON p.`Project_ID`= o.`Project_ID`
WHERE o.`IsInTrip` = 0 AND p.`Client_ID` = ".$client_id." AND p.`Project_ID` = (SELECT Project_ID FROM tms_project WHERE DATE(tms_project.`Date`) = '".$time."' AND tms_project.`Client_ID` = ".$client_id." ORDER BY tms_project.`Project_ID` DESC LIMIT 1);";
*/
$query = "SELECT o.`Order_ID`, c.Name AS `Client`,o.Code,o.OriginalAddress,o.`GeoX`,o.`GeoY`,o.`Name`, o.`Priority`,TIME(o.`StartTime`) AS StartTime,TIME(o.`EndTime`) AS EndTime
FROM `tms_order` o
INNER JOIN `tms_warehouse` w ON w.`Warehouse_ID` = o.`Warehouse_ID`
INNER JOIN `tms_client` c ON c.`Client_ID` = w.`Client_ID`
INNER JOIN `tms_project` p ON p.`Project_ID`= o.`Project_ID`
WHERE o.`IsInTrip` = 0 AND p.`Client_ID` = ".$client_id." AND p.`Project_ID` = '".$project."';";

	if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			echo json_encode($result);
	}
	$db->close();
	exit;
}

if(isset($_REQUEST['func'])){
if ($_POST['func'] == 'showCenterMap')
{
	
	$query = "SELECT `MapCenter_longitude`, `MapCenter_latitude` FROM `wm_settings` WHERE `client_id` = ".$client_id.";";
	if ($stmt = $db->prepare($query))
	{
		if(!$stmt = $db->query($query)){
			echo '{"success":false,"message":"'.$db->errno.' '.$db->error.'"}';
	}else{
			//$result = $stmt->fetch_all(MYSQLI_ASSOC);
			for ($result = array(); $tmp = $stmt->fetch_array(MYSQLI_ASSOC);) $result[] = $tmp;
			$stmt->close();
			echo json_encode($result);
	}
	$db->close();
	exit;
	}
}
}

// -------- печать маршрутного листа ----------
if (isset($_REQUEST['print']))
{
	$time = $_REQUEST['time'];
	$project = $_REQUEST['project'];
	$i=0;
	$query = "SELECT ft.ID AS ftID,c.`car_name`,d.`name`,d.`surname`,ft.`StartTime`,ft.`EndTime`,ft.`Distance`,ft.`Time`,(SELECT COUNT(Id) FROM `tms_checkpoint`  WHERE `FactTripID` = ftID) FROM `tms_facttrip` ft
LEFT JOIN `tms_car` tc ON ft.`Car_ID`=tc.`Car_ID`
LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
LEFT JOIN `wm_car_info` c ON c.`imei` = td.`Login`
LEFT JOIN `wm_drivers` d ON d.`id` = c.`driver_id`
WHERE ft.`Project_Id` = ".$project.";";
	if($stmt = $db->prepare($query)){
	$stmt->execute();
	$stmt->bind_result($ftID,$car_name,$name,$surname,$StartTime,$EndTime,$Distance,$Time,$count);
	while ($stmt->fetch()){
	
	if($dimension==1){ $Distance = mtomiles($Distance); }
	if($dimension!=1){ $Distance = Round( ($Distance/1000), 2); }
	
	$data['facttrip'][$i] = array(
			'ID'=>$ftID,
			'car_name'=>$car_name,
			'driver'=>$name.' '.$surname,
			'StartTime'=>$StartTime,
			'EndTime'=>$EndTime,
			'Distance'=>$Distance,
			'Time'=> Round($Time, 2),
			'count'=>$count
	);
	$i++;
	}
	$stmt->close();
	}
	
	foreach($data['facttrip'] as $key => $value){
		
		/*$query = "SELECT c.Id AS chID, c.`Name`, c.`ClientName`, c.`Address`, c.`EstimateTime`, c.`DurationInPoint`,  (SELECT SUM(`CargoValueVolume`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`CargoValueWeight`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`Cost`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID) FROM `tms_facttrip` f
INNER JOIN `tms_checkpoint` c ON f.`ID` = c.`FactTripID`
WHERE f.`ID` = ".$value['ID'].";";*/

$query = "SELECT c.Id AS chID, c.`Name`, c.`ClientName`, c.`Address`, c.`EstimateTime`, c.`DurationInPoint`,  (SELECT SUM(`CargoValueVolume`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`CargoValueWeight`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`Cost`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), c.`OrderInTrip`,
o.`CustomString1`, o.`CustomString2`, o.`CustomString3`, o.`CustomString4`, o.`CustomString5`, o.`CustomString6`, o.`CustomString7`, o.`CustomString8`
FROM `tms_facttrip` ft
INNER JOIN `tms_checkpoint` c 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 ft.`ID` = ".$value['ID'].";";

		if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->bind_result($chID,$Name,$ClientName,$Address,$EstimateTime,$DurationInPoint,$CargoValueVolume,$CargoValueWeight,$Cost,$OrderInTrip,$CustomString1,$CustomString2,$CustomString3,$CustomString4,$CustomString5,$CustomString6,$CustomString7,$CustomString8);
		$sumVolume = 0.0;
		$sumWeight = 0.0;
		$sumCost = 0.0;
		while ($stmt->fetch()){
			$data['checkpoint'][$value['ID']][] = array(
				'chID' => $chID,
				'Name' => $Name,
				'ClientName' => $ClientName,
				'Address' => $Address,
				'EstimateTime' => $EstimateTime,
				'DurationInPoint' => $DurationInPoint,
				'CargoValueVolume' => Round(abs($CargoValueVolume), 2),
				'CargoValueWeight' => Round(abs($CargoValueWeight), 2),
				'Cost' => Round(abs($Cost), 2),
				'OrderInTrip' => $OrderInTrip,
				'CustomString1' => $CustomString1,
				'CustomString2' => $CustomString2,
				'CustomString3' => $CustomString3,
				'CustomString4' => $CustomString4,
				'CustomString5' => $CustomString5,
				'CustomString6' => $CustomString6,
				'CustomString7' => $CustomString7,
				'CustomString8' => $CustomString8
			);
			$sumVolume = $sumVolume + abs($CargoValueVolume);
			$sumWeight = $sumWeight + abs($CargoValueWeight);
			$sumCost = $sumCost + abs($Cost);
		}
		
		$data['facttrip'][$key]['sumVolume'] = Round($sumVolume, 2);
		$data['facttrip'][$key]['sumWeight'] = Round($sumWeight, 2);
		$data['facttrip'][$key]['sumCost'] =  Round($sumCost, 2);
		
		$stmt->close();
		}
    }
	
	$db->close();
	
	echo json_encode($data);
	exit;
	
}

// -------- xls маршрутного листа ----------
if (isset($_REQUEST['export_xls']))
{
	$time = $_REQUEST['time'];
	$project = $_REQUEST['project'];
	$globalClientID = $_REQUEST['globalClientID'];
	$i=0;

    require_once '../classes/PHPExcel.php';
    include_once '../classes/PHPExcel/IOFactory.php';

    $ObjPHPExcel = new PHPExcel();
    //$ObjPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $Column='A';
	$ObjPHPExcel->setActiveSheetIndex(0);
	$Sheet = $ObjPHPExcel->getActiveSheet();



	switch ($globalClientID) 
	{
		case 120:
				$Sheet->setTitle('Маршрутный лист');
                $Sheet->getStyle('A1:M1')->getFill()->applyFromArray(
                    array(
                        'type'       => PHPExcel_Style_Fill::FILL_SOLID,
                        'startcolor' => array('rgb' => '67C380'),
                        'endcolor' => array('rgb' => '67C380')
                    )
                );
                $Sheet->getColumnDimension('A')->setWidth(20);
                $Sheet->getColumnDimension('B')->setWidth(5);
                $Sheet->getColumnDimension('C')->setWidth(35);
                $Sheet->getColumnDimension('D')->setWidth(35);
                $Sheet->getColumnDimension('E')->setWidth(60);
                $Sheet->getColumnDimension('F')->setWidth(35);
                $Sheet->getColumnDimension('G')->setWidth(30);
                $Sheet->getColumnDimension('H')->setWidth(12);
                $Sheet->getColumnDimension('I')->setWidth(7);
                $Sheet->getColumnDimension('J')->setWidth(15);
                $Sheet->getColumnDimension('K')->setWidth(30);
                $Sheet->getColumnDimension('L')->setWidth(17);
                $Sheet->getColumnDimension('M')->setWidth(25);

                $Sheet->setCellValueByColumnAndRow(0, 1, 'Автомобиль');
                $Sheet->setCellValueByColumnAndRow(1, 1, ' ');
                $Sheet->setCellValueByColumnAndRow(2, 1, 'Покупатель'); 
                $Sheet->setCellValueByColumnAndRow(3, 1, 'Адрес доставки');
                $Sheet->setCellValueByColumnAndRow(4, 1, 'Доп. информация');
                $Sheet->setCellValueByColumnAndRow(5, 1, 'Контакты');
                $Sheet->setCellValueByColumnAndRow(6, 1, 'Бумага');         
                $Sheet->setCellValueByColumnAndRow(7, 1, 'Пач.');            
                $Sheet->setCellValueByColumnAndRow(8, 1, 'Мест');                      
                $Sheet->setCellValueByColumnAndRow(9, 1, 'Документ счет');            
                $Sheet->setCellValueByColumnAndRow(10, 1, 'Примечание');            
                $Sheet->setCellValueByColumnAndRow(11, 1, 'Место на складе');            
                $Sheet->setCellValueByColumnAndRow(12, 1, 'Менеджер');
                

	            $RowNumber=2;

			break;
		default:
				$Sheet->setTitle('Маршрутный лист');

                $Sheet->getStyle('A1:A7')->getFill()->applyFromArray(
                    array(
                        'type'       => PHPExcel_Style_Fill::FILL_SOLID,
                        'startcolor' => array('rgb' => '67C380'),
                        'endcolor' => array('rgb' => '67C380')
                    )
                );

                $Sheet->getStyle('A8:G8')->getFill()->applyFromArray(
                    array(
                        'type'       => PHPExcel_Style_Fill::FILL_SOLID,
                        'startcolor' => array('rgb' => '67C380'),
                        'endcolor' => array('rgb' => '67C380')
                    )
                );

                $Sheet->getColumnDimension('A')->setWidth(15);
                $Sheet->getColumnDimension('B')->setWidth(30);
                $Sheet->getColumnDimension('C')->setWidth(30);
                $Sheet->getColumnDimension('D')->setWidth(30);
                $Sheet->getColumnDimension('E')->setWidth(10);
                $Sheet->getColumnDimension('F')->setWidth(15);
                $Sheet->getColumnDimension('G')->setWidth(10);

                $RowNumber=1;

			break;
	}


	$query = "SELECT ft.ID AS ftID,c.`car_name`,d.`name`,d.`surname`,ft.`StartTime`,ft.`EndTime`,ft.`Distance`,ft.`Time`,(SELECT COUNT(Id) FROM `tms_checkpoint`  WHERE `FactTripID` = ftID) FROM `tms_facttrip` ft
				LEFT JOIN `tms_car` tc ON ft.`Car_ID`=tc.`Car_ID`
				LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
				LEFT JOIN `wm_car_info` c ON c.`imei` = td.`Login`
				LEFT JOIN `wm_drivers` d ON d.`id` = c.`driver_id`
				WHERE ft.`Project_Id` = ".$project.";";
	if($stmt = $db->prepare($query)){
	$stmt->execute();
	$stmt->bind_result($ftID,$car_name,$name,$surname,$StartTime,$EndTime,$Distance,$Time,$count);
	while ($stmt->fetch())
	{
	
		if($dimension==1){ $Distance = mtomiles($Distance); }
		if($dimension!=1){ $Distance = Round( ($Distance/1000), 2); }
		
		$data['facttrip'][$i] = array(
				'ID'=>$ftID,
				'car_name'=>$car_name,
				'driver'=>$name.' '.$surname,
				'StartTime'=>$StartTime,
				'EndTime'=>$EndTime,
				'Distance'=>$Distance,
				'Time'=> Round($Time, 2),
				'count'=>$count
		);
		$i++;
	}
	$stmt->close();
	}

	$chkRowNumber = 0;

	foreach($data['facttrip'] as $key => $value)
	{
		switch ($globalClientID) 
		{
			case 120:
					$Sheet->setCellValueByColumnAndRow(0,$RowNumber, $value['car_name']);
				break;
			default:

					$Sheet->setCellValueByColumnAndRow(0, $RowNumber, 'Рейс №:');
					//$Sheet->getStyle('B'.$RowNumber)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$Sheet->setCellValueByColumnAndRow(1, $RowNumber, $value['ID']);
			        $Sheet->mergeCells('B'.$RowNumber.':G'.$RowNumber);
			        $firstCell = $RowNumber;

			        $Sheet->setCellValueByColumnAndRow(0, ++$RowNumber, 'Машина:');
			        $Sheet->setCellValueByColumnAndRow(1, $RowNumber, $value['car_name']);
			        $Sheet->mergeCells('B'.$RowNumber.':G'.$RowNumber);

			        $Sheet->setCellValueByColumnAndRow(0, ++$RowNumber, 'Водитель:');
			        $Sheet->setCellValueByColumnAndRow(1, $RowNumber, $value['driver']);
			        $Sheet->mergeCells('B'.$RowNumber.':G'.$RowNumber);

			        $Sheet->setCellValueByColumnAndRow(0, ++$RowNumber, 'Время старта:');
			        $Sheet->setCellValueByColumnAndRow(1, $RowNumber, $value['StartTime']);

			        $Sheet->setCellValueByColumnAndRow(3, $RowNumber, 'Время финиша:');
			        //
			        $Sheet->setCellValueByColumnAndRow(4, $RowNumber, $value['EndTime']);
			        $Sheet->mergeCells('B'.$RowNumber.':C'.$RowNumber);
			        $Sheet->mergeCells('E'.$RowNumber.':G'.$RowNumber);

			        $Sheet->setCellValueByColumnAndRow(0, ++$RowNumber, 'Макс. вес:');
			        $Sheet->mergeCells('B'.$RowNumber.':C'.$RowNumber);

			        $Sheet->setCellValueByColumnAndRow(3, $RowNumber, 'Макс. объем:');
			        $Sheet->mergeCells('E'.$RowNumber.':G'.$RowNumber);

			        $Sheet->setCellValueByColumnAndRow(0, ++$RowNumber, 'Пробег, км:');
			        $Sheet->setCellValueByColumnAndRow(1, $RowNumber, $value['Distance']);
			        $Sheet->mergeCells('B'.$RowNumber.':C'.$RowNumber);

			        $Sheet->setCellValueByColumnAndRow(3, $RowNumber, 'Продолжительность:');
			        $Sheet->setCellValueByColumnAndRow(4, $RowNumber, $value['Time']);
			        $Sheet->mergeCells('E'.$RowNumber.':G'.$RowNumber);

			        $Sheet->setCellValueByColumnAndRow(0, ++$RowNumber, 'Стоимость, грн:');
			        $Sheet->mergeCells('B'.$RowNumber.':C'.$RowNumber);
			        $chkRowNumber = $RowNumber;

			        $Sheet->setCellValueByColumnAndRow(3, $RowNumber, 'Кол‐во точек:');
			        $Sheet->setCellValueByColumnAndRow(4, $RowNumber, $value['count']);
			        $Sheet->mergeCells('E'.$RowNumber.':G'.$RowNumber);
			        $lastCell = $RowNumber;

			        $Sheet->getStyle('A'.$firstCell.':A'.$lastCell)->getFill()->applyFromArray(
	                    array(
	                        'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	                        'startcolor' => array('rgb' => '67C380'),
	                        'endcolor' => array('rgb' => '67C380')
	                    )
	                );

			        $Sheet->setCellValueByColumnAndRow(0, ++$RowNumber, '№');
			        $Sheet->setCellValueByColumnAndRow(1, $RowNumber, 'Клиент'); 
			        $Sheet->setCellValueByColumnAndRow(2, $RowNumber, 'Адрес');
			        $Sheet->setCellValueByColumnAndRow(3, $RowNumber, 'Вес');         
			        $Sheet->setCellValueByColumnAndRow(4, $RowNumber, 'Объем');            
			        $Sheet->setCellValueByColumnAndRow(5, $RowNumber, 'Прибытие');                      
			        $Sheet->setCellValueByColumnAndRow(6, $RowNumber, 'Разгрузка');

			        $Sheet->getStyle('A'.$RowNumber.':G'.$RowNumber)->getFill()->applyFromArray(
	                    array(
	                        'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	                        'startcolor' => array('rgb' => '67C380'),
	                        'endcolor' => array('rgb' => '67C380')
	                    )
	                );      

			        ++$RowNumber;

				break;
		}
		

		switch ($globalClientID) 
		{
			case 120:
					$query = "SELECT c.Id AS chID, c.`Name`, c.`ClientName`, c.`Address`, c.`EstimateTime`, c.`DurationInPoint`,  (SELECT SUM(`CargoValueVolume`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`CargoValueWeight`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`Cost`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), c.`OrderInTrip`,
							o.`CustomString1`, o.`CustomString2`, o.`CustomString3`, o.`CustomString4`, o.`CustomString5`, o.`CustomString6`, o.`CustomString7`, o.`CustomString8`, o.`Comments`, o.`Code`, c.IsOrderPoint
							FROM `tms_facttrip` ft
							INNER JOIN `tms_checkpoint` c 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 ft.`ID` = ".$value['ID']."
							-- GROUP BY c.`Address`
							ORDER BY c.`OrderInTrip`;";
				break;
			default:
					$query = "SELECT c.Id AS chID, c.`Name`, c.`ClientName`, c.`Address`, c.`EstimateTime`, c.`DurationInPoint`,  (SELECT SUM(`CargoValueVolume`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`CargoValueWeight`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), (SELECT SUM(`Cost`) FROM `tms_checkpointcargo` WHERE `CheckPoint_ID` = chID), c.`OrderInTrip`,
							o.`CustomString1`, o.`CustomString2`, o.`CustomString3`, o.`CustomString4`, o.`CustomString5`, o.`CustomString6`, o.`CustomString7`, o.`CustomString8`, o.`Comments`, o.`Code`, c.IsOrderPoint
							FROM `tms_facttrip` ft
							INNER JOIN `tms_checkpoint` c 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 ft.`ID` = ".$value['ID'].";";
				break;
		}

		
		//echo $query;
		if($stmt = $db->prepare($query))
		{
			$stmt->execute();
			$stmt->bind_result($chID,$Name,$ClientName,$Address,$EstimateTime,$DurationInPoint,$CargoValueVolume,$CargoValueWeight,$Cost,$OrderInTrip,$CustomString1,$CustomString2,$CustomString3,$CustomString4,$CustomString5,$CustomString6,$CustomString7,$CustomString8, $Comments, $Code, $IsOrderPoint);
			$sumVolume = 0.0;
			$sumWeight = 0.0;
			$sumCost = 0.0;
			while ($stmt->fetch())
			{
				switch ($globalClientID) 
				{
					case 120:
					
							if($CustomString1 == NULL){$CustomString1 = '-';}
							if($CustomString2 == NULL){$CustomString2 = '-';}
							if($CustomString3 == NULL){$CustomString3 = '-';}
							if($CustomString4 == NULL){$CustomString4 = '-';}
							if($CustomString5 == NULL){$CustomString5 = '-';}
							if($CustomString6 == NULL){$CustomString6 = '-';}
							if($CustomString7 == NULL){$CustomString7 = '-';}

	                        $Sheet->setCellValueByColumnAndRow(1,$RowNumber, $OrderInTrip);
	                        $Sheet->setCellValueByColumnAndRow(2,$RowNumber, $ClientName);
	                        $Sheet->setCellValueByColumnAndRow(3,$RowNumber, $Address);
	                        $Sheet->setCellValueByColumnAndRow(4,$RowNumber, $Comments);
	                        $Sheet->setCellValueByColumnAndRow(5,$RowNumber, $CustomString4);
	                        $Sheet->setCellValueByColumnAndRow(6,$RowNumber, $CustomString1);
	                        $Sheet->setCellValueByColumnAndRow(7,$RowNumber, $CustomString2);
	                        $Sheet->setCellValueByColumnAndRow(8,$RowNumber, $CustomString3);
	                        $Sheet->setCellValueByColumnAndRow(9,$RowNumber, $Code);
	                        $Sheet->setCellValueByColumnAndRow(10,$RowNumber, $CustomString5);
	                        $Sheet->setCellValueByColumnAndRow(11,$RowNumber, $CustomString6);
	                        $Sheet->setCellValueByColumnAndRow(12,$RowNumber, $CustomString7);
	                        
	                        $RowNumber++;

						break;
					default:

							if($IsOrderPoint == 1){
							$sumVolume = $sumVolume + abs($CargoValueVolume);
							$sumWeight = $sumWeight + abs($CargoValueWeight);
							$sumCost = $sumCost + abs($Cost);
							}
							
				            $Sheet->setCellValueByColumnAndRow(0,$RowNumber, $chID);
                            $Sheet->setCellValueByColumnAndRow(1,$RowNumber, $ClientName);
                            $Sheet->setCellValueByColumnAndRow(2,$RowNumber, $Address);
                            $Sheet->setCellValueByColumnAndRow(3,$RowNumber, Round(abs($CargoValueWeight),2));
                            $Sheet->setCellValueByColumnAndRow(4,$RowNumber, Round(abs($CargoValueVolume),2));
                            $Sheet->setCellValueByColumnAndRow(5,$RowNumber, $EstimateTime);
                            $Sheet->setCellValueByColumnAndRow(6,$RowNumber, $DurationInPoint);

                            ++$RowNumber;

						break;
				}
			}
		
			$stmt->close();

			switch ($globalClientID) 
			{
				case 120:
						$Sheet->getStyle('A'.$RowNumber.':L'.$RowNumber)->getFill()->applyFromArray(
			                array(
			            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
			                    'startcolor' => array('rgb' => 'D86B6B'),
			                    'endcolor' => array('rgb' => 'D86B6B')
			                )
			            );
					break;
				default:

						$Sheet->setCellValueByColumnAndRow(1, $chkRowNumber, Round($sumCost,2));
						$Sheet->setCellValueByColumnAndRow(4, $chkRowNumber - 2, Round($sumVolume,2));
						$Sheet->setCellValueByColumnAndRow(1, $chkRowNumber - 2, Round($sumWeight,2));

						$Sheet->getStyle('A'.$RowNumber.':G'.$RowNumber)->getFill()->applyFromArray(
			                array(
			            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
			                    'startcolor' => array('rgb' => 'D86B6B'),
			                    'endcolor' => array('rgb' => 'D86B6B')
			                )
			            );
			        break;
			}
			
            ++$RowNumber;
		}
    }
	
	$ObjWriter = PHPExcel_IOFactory::createWriter($ObjPHPExcel, 'Excel2007');
    header('Content-Type: application/vnd.ms-excel Content-Type: text/html; charset=utf-8');
    if(file_exists("../files/RoadMap.xlsx"))
    {
        unlink("../files/RoadMap.xlsx");
    }
    $ObjWriter->save('../files/RoadMap.xlsx');

	$db->close();
	
	//echo json_encode($data);
	exit;
	
}

if (isset($_REQUEST['save_export_xls']))
{
    $File = ('../files/RoadMap.xlsx');
    if (file_exists($File)) 
    {
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename='.basename($File));
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($File));
        ob_clean();
        flush();
        readfile($File);
        exit;
    }
}

// -------- список авто со снепшота для блокировки/разблокировки ----------
if (isset($_REQUEST['showcarsnapshot']))
{
	$project = $_REQUEST['project'];
	$i=0;
	$data = array();
	
	$imei_in = imei_to_users($db);
	
	if($role == 1){
		$where = "";
	}else{
		if(count($imei_in)>0){
			$where = " AND td.`Login` IN (".implode(",", $imei_in).")";
		}else{ echo '{rows:[]}'; exit; }
	}
	
	$query = "SELECT wc.`car_name`,td.`Login`,IF(cs.`Active`=2,'true','false') FROM `tms_car_snapshot` cs
LEFT JOIN `tms_car` tc ON cs.`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 cs.`Project_ID` = ".$project." ".$where.";";
	
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($Login,$Login2,$Active);
	while( $stmt->fetch() ){
	$data[$i] = array(
			'Login' => $Login,
			'Login2' => $Login2,
			'indoor' => $Active
		);
	$i++;
	}
	}
	$stmt->close();
	}
	
	echo'{rows:'.json_encode($data).'}';

	$db->close();
	exit;
}
// -------- функционал блокировки/разблокировки ----------
if (isset($_REQUEST['blockcarsnapshot']))
{
	$login = $_REQUEST['login'];
	$checked = $_REQUEST['checked'];
	$project = $_REQUEST['project'];
	
	if($checked=='true'){ $checked = 2; }else{ $checked = 1; }
	$login = "'".implode("','", $login)."'";
	$query = "UPDATE tms_car_snapshot SET `Active`=".$checked." WHERE `Project_ID` = ".$project." AND `Device_id` IN (SELECT ID FROM tms_device WHERE Login IN (".$login."));";
	echo $query;
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->close();
	echo '{"success":true,"message":""}';
	}else{ echo '{"success":false,"message":""}'; }
	}else{ echo '{"success":false,"message":""}'; }
	
	$db->close();
	exit;
}

// -------- функционал блокировки/разблокировки всех авто----------
if (isset($_REQUEST['blockcarsnapshotall']))
{
	$checked = $_REQUEST['pressed'];
	$project = $_REQUEST['project'];
	
	if($checked=='true'){
		$query = "UPDATE tms_car_snapshot SET `Active`=2 WHERE `Project_ID` = ".$project.";";
	}else{
		$query = "UPDATE tms_car_snapshot SET `Active`=1 WHERE `Project_ID` = ".$project.";";
	}
	
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
		$stmt->close();
	echo '{"success":true,"message":""}';
	}else{ echo '{"success":false,"message":""}'; }
	}else{ echo '{"success":false,"message":""}'; }
	
	$db->close();
	exit;
}


// -------- функционал переменние данние 19.01.2016 ----------
if ($_REQUEST['func'] == 'VariableDataGet')
{
	$type = $_REQUEST['type'];
	
	$xml = '<Data InstanceID="'.$client_id.'" >
	<p type="GetSetting">
	<p type="GetSetting" settingType="'.$type.'"/>
	</p>
	</Data>';
	
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`,`in_progress`) VALUES ('".$xml."',".$client_id.",0)";
	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
	$last_id = $db->insert_id;
	$stmt->close();
	  
	//$res = check($last_id);
	//echo $res;
	echo json_encode(array('success'=>true, 'message'=>$last_id));
	
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	
}

if ($_REQUEST['func']=='VariableDataSet')
{
	$type = $_REQUEST['type'];
	$value = $_REQUEST['value'];
	
	$xml = '<Data InstanceID="'.$client_id.'" >
	<p type="UpdateSetting">
	<p type="UpdateSetting" settingType="'.$type.'" settingValue="'.$value.'"/>
	</p>
	</Data>';
	
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`,`in_progress`) VALUES ('".$xml."',".$client_id.",0)";
	if ($stmt = $db->prepare($query)){
	if ($stmt->execute()){
	$last_id = $db->insert_id;
	$stmt->close();
	  
	//$res = check($last_id);
	//echo $res;
	echo json_encode(array('success'=>true, 'message'=>$last_id));
	
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	}else{ echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error)); }
	
}

if($_REQUEST['func']=='update_status_route'){
	$project = $_REQUEST['project'];
	$result = get_route_state($db,$project);

	echo json_encode($result);
}

if($_REQUEST['func']=='update_status_point'){
	
}

if(isset($_REQUEST['create_project_to_users'])){
	echo create_project_to_users($db);
}

if(isset($_REQUEST['checkImeiBeforeAutoplan'])){
	$project = $_REQUEST['project'];
	
	if($role == 1){
		$result = array();
	}else{
		$result = checkImeiBeforeAutoplan($db,$project);
	}
	
	echo json_encode($result);
}



if(isset($_REQUEST['manualPlanning'])){
	$pointID = $_REQUEST['pointID'];
	$pointIn = $_REQUEST['pointIn'];
	$pointOut = $_REQUEST['pointOut'];
	
	$tripID = $_REQUEST['tripID'];
	
	unBindPointFromTrip($tripID,$pointOut);
	
	$i = 0;
	$j = 2;
	while($i < count($pointID)){
		//$query = "UPDATE `tms_trippoint` SET `OrderInTrip` = ".$j." WHERE `TripPoint_ID` = ".$point[$i]."";
		$query = "UPDATE `tms_trippoint` SET `OrderInTrip` = ".$pointIn[$i]." WHERE `TripPoint_ID` = ".$pointID[$i].";";
		if ($stmt = $db->prepare($query)){
		if ($stmt->execute()){
			$stmt->close();
		}}
		$j++;
		$i++;
	}
	
	echo json_encode(array('success'=>true, 'message'=>''));
}

if(isset($_REQUEST['classGeoRoute'])){
	
	$points = $_REQUEST['points'];
	$array_poly = array();
	
	$i = 0;
	$str = '';
	while($i < count($points)-1){
		//echo $points[$i].' = '.$points[$i+1].'<br/>';
		
		
		$geoxy1 = explode(",", $points[$i]);
		$geoxy2 = explode(",", $points[$i+1]);
		//echo $geoxy1[0].','.$geoxy1[1].','.$geoxy2[0].','.$geoxy2[1].'<br/>';
		//print_r( GetGeoRouteInnotech($geoxy1[1],$geoxy1[0],$geoxy2[1],$geoxy2[0]) );
		
		//$geoxy = GetGeoRouteInnotech($geoxy2[0],$geoxy2[1],$geoxy1[0],$geoxy1[1]);
		$geoxy = GetGeoRouteInnotech($geoxy1[1],$geoxy1[0],$geoxy2[1],$geoxy2[0]);
		if(!$geoxy){
			sleep(1);
			$geoxy = GetGeoRouteInnotech($geoxy1[1],$geoxy1[0],$geoxy2[1],$geoxy2[0]);
		}
		$str .= '"'.$i.'":{"Dist":"'.$geoxy['Dist'].'","Dur":"'.$geoxy['Dur'].'",'.$geoxy['R'].'},';
		//array_push($array_poly,$geoxy);
		//print_r($geoxy);
		//$array_poly[] = array(
		//	'Dist'=>$geoxy['Dist'],
		//	'Dur'=>$geoxy['Dur'],
		//	'XY'=>$geoxy['R']
		//);
		
		
		$i++;
	};
	
	$str = substr($str, 0, strlen($str)-1);
	echo '{"success":true,"geoXgeoY":{'.$str.'}}';
	//echo json_encode(array('success'=>false, 'geoXgeoY'=>$array_poly));
	//echo GetGeoRouteInnotech(50.440344,30.495735,50.47994,30.63202);
	
}


