<?php  
	session_start();
    header('Content-Type: text/html; charset=utf-8');
	
	set_time_limit(600);
	
	$client_id = $_SESSION['client_id'];
	$GMT = $_SESSION['gmt'];
	$locale = $_SESSION['locale'];
	$user_id = $_SESSION['id'];
	$dimension = $_SESSION['dimension'];
	$role = $_SESSION['role_id'];
	
        //require "log.php";
        //include "../settings.php";
		include "writedblog.php";
		include "logger.php";
		include "../scripts/ClassLibs/milesconvert.php";
		include "LogistMobile_function.php";
		
//require_once("../scripts/ClassLibs/ClassArchive.php");
//require_once("../scripts/ClassLibs/ClassTimeDate.php");
//$ObjArchive = new Archive();

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";
}		
		
$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='root';
$DB_PASS='12345678';
$DB_NAME='tmslogistic';
$db = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$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;
        }

if ($_REQUEST['func']=='start_archive')
{
	$CarImei = $_REQUEST['imei'];
	$TimeFrom = $_REQUEST['time'].' 00:00:00';
    $TimeTo = $_REQUEST['time'].' 23:59:59';
	$StartTime = $_REQUEST['StartTime'];
	$EndTime = $_REQUEST['EndTime'];
	$data = array();
	
	/*$query = "SELECT g.latitude,g.longitude,g.azimuth,ADDTIME(g.time,'0 ".$GMT.":0:0')AS time,g.speed,g.altitude,g.Distance FROM tms_gpsdata_fixed g
WHERE g.time>=SUBTIME('".$TimeFrom."','0 ".$GMT.":0:0') AND g.time<=SUBTIME('".$TimeTo."','0 ".$GMT.":0:0') AND g.DeviceLoginName='".$CarImei."';";*/
	$query = "SELECT g.latitude,g.longitude,g.azimuth,ADDTIME(g.time,'0 ".$GMT.":0:0')AS time,g.speed,g.altitude,g.Distance FROM tms_gpsdata_fixed g
WHERE g.time>=SUBTIME('".$StartTime."','0 ".$GMT.":0:0') AND g.time<=SUBTIME('".$EndTime."','0 ".$GMT.":0:0') AND g.DeviceLoginName='".$CarImei."';";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' QUERY = '.$query );
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($latitude, $longitude, $azimuth, $time, $speed, $altitude, $distance);	
	$i=0;
	while($stmt->fetch()) {
		$data['track'][$i] = array(
			'latitude' => $latitude,
			'longitude' => $longitude,
			/*'azimuth' => $azimuth,
			'time' => $time,
			'speed' => $speed,
			'altitude' => $altitude,
			'distance' => $distance*/
		);
		$i++;
	}
	$stmt->close();
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' execute date NULL ' ); }
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' prepare date NULL ' ); }
	
	
	/*
	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;
	*/
	
	$query = "SELECT ADDTIME(e.date,'0 ".$GMT.":0:0'), e.EventType,e.GeoX,e.GeoY FROM tms_events e WHERE e.DeviceLoginName='".$CarImei."' AND (e.EventType='6' OR e.EventType='12') AND e.date>=SUBTIME('".$StartTime."','0 ".$GMT.":0:0') AND e.date<=SUBTIME('".$EndTime."','0 ".$GMT.":0:0')";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' QUERY = '.$query );
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($date, $EventType, $GeoX, $GeoY);	
	$i=0;
	while($stmt->fetch()) {
		$data['stop'][$i] = array(
			'date' => $date,
			'EventType' => $EventType,
			'GeoX' => $GeoX,
			'GeoY' => $GeoY
		);
		$i++;
	}
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' execute date NULL ' ); }
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' prepare date NULL ' ); }
	$stmt->close();
	
	$query = "SELECT ADDTIME(e.date,'0 ".$GMT.":0:0'),e.EventType,e.GeoX,e.GeoY FROM tms_events e WHERE e.DeviceLoginName='".$CarImei."' AND (e.EventType='7' OR e.EventType='12') AND e.date>=SUBTIME('".$StartTime."','0 ".$GMT.":0:0') AND e.date<=SUBTIME('".$EndTime."','0 ".$GMT.":0:0')";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' QUERY = '.$query );
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($date, $EventType, $GeoX, $GeoY);	
	$i=0;
	while($stmt->fetch()) {
		$data['park'][$i] = array(
			'date' => $date,
			'EventType' => $EventType,
			'GeoX' => $GeoX,
			'GeoY' => $GeoY
		);
		$i++;
	}
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' execute date NULL ' ); }
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' start_archive INSTANCEID = '.$client_id.' prepare date NULL ' ); }
	
	echo json_encode($data);
	$stmt->close();
	$db->close();
	exit;
	
	
	
	//$ObjArchive->GetCountStops($CarImei,$TimeFrom,$TimeTo);     
 
//    $PrevTime = $ObjArchive->ObjTimeDate->ConvertTimeToGMT($TimeFrom,$GMT);
//    $CurrTime = $ObjArchive->ObjTimeDate->ConvertTimeToGMT($TimeTo,$GMT);
//    $table = "tms_gpsdata_fixed";
    //$ObjArchive->GetMovementInfo($CarImei,$PrevTime,$CurrTime,$GMT,$table);
    
//	$ObjArchive->GetMovementInfo2($CarImei,$PrevTime,$CurrTime,$GMT,$table);
	
	//$ObjArchive->GetImageByAzimuth('images/green/0_green.png',$ObjArchive->AzimythMovement);  
    
    //$ObjArchive->GetStops($CarImei,$PrevTime,$CurrTime,'images/Events/StopEventMiddle.png',$GMT);
    //$ObjArchive->GetParkin($CarImei,$PrevTime,$CurrTime,'images/Events/ParkingEventMiddle.png',$GMT);
	
	//$ObjArchive->GetStops2($CarImei,$PrevTime,$CurrTime,'images/Events/StopEventMiddle.png',$GMT);
    //$ObjArchive->GetParkin2($CarImei,$PrevTime,$CurrTime,'images/Events/ParkingEventMiddle.png',$GMT);
	
	//$ObjArchive->GetStops2($CarImei,$PrevTime,$CurrTime,'images/Events/StopEventMiddle.png',$GMT);
    //$ObjArchive->GetParkin2($CarImei,$PrevTime,$CurrTime,'images/Events/ParkingEventMiddle.png',$GMT);
      
//    echo json_encode($ObjArchive);

}

        
/*if ($_REQUEST['func'] == 'show') {
	$userID = $_REQUEST['userID'];
	$time = $_REQUEST['time'];
	$query = "SELECT r.`id_request`,  c.`client`, o.`name`, r.`region`,  r.`city`, r.`address`, 
		r.`status`, r.`time_execut`, t.`name_job`, o.`id_outlet`, o.geoX, o.geoY, r.time_beginning_work FROM `toir_request` r
		INNER JOIN  `type_job` t   ON t.id_type_job= r.`id_type_job`
		INNER JOIN `client` c ON c.id_client = r.`client_id`
		INNER JOIN `toir_users` u ON u.id=r.`name_master`
		INNER JOIN `equipment` e ON e.`id_equipment`=r.`id_equipment`
		INNER JOIN `toir_outlets` o ON o.`id_outlet`=r.`outlet_id`
		WHERE u.id = $userID AND r.`time_execut`>='".$time.' 00:00:00'."' AND r.`time_execut`<='".$time.' 23:59:59'."' 
		 		ORDER BY r.place;";
	//echo $query; exit;
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id, $client, $outlet,    $region, $city, $address,   $status,  $time_execut, $job_type, $id_outlet,
						$geoX, $geoY, $time_beginning_work);	
	while($stmt->fetch()) {
		$address = $region.' '.$city.' '.$address;
		if ($status == 1) {
			$status = '<img src="images/cmd_ok24.png" />';
		} else {
			$status = '<img src="images/Point_Red_old.png" />';
		}
		$data[] = array(
			'id' => $id,
			'client' => $client,
			'outlet' => $outlet,
			'address' => $address,
			'status' => $status,
			'time_execute'	=> $time_execut,
			'job_type' => $job_type,
			'id_outlet' => $id_outlet,
			'geoX' => $geoX,
			'geoY' => $geoY,
			'time_beginning_work' => $time_beginning_work								
		);
	}	
	echo'{rows:'.json_encode($data).'}';
}*/



if ($_REQUEST['func'] == 'show') {
	$userID = $_REQUEST['userID'];
	$time = $_REQUEST['time'];
	$FactTripID = $_REQUEST['FactTripID'];
	$i=0;
	$query = "SELECT fc.`Id`,fc.`StartCheckTime`,fc.`EndCheckTime`,fc.`Description`,fc.`Necessary`,fc.`Name`,fc.`GeoX`,fc.`GeoY`,fc.`PointState`,fc.`Date`,fc.`OrderInTrip`,fc.`Distance`,fc.`Time`,fc.`WaitTime`,fc.`LateTime`,fc.`EstimateTime`,fc.`Address`,fc.`DurationInPoint`,fc.Type,fc.`IsOrderPoint`,fc.`StartWorkTime`,fc.`EndWorkTime`,fc.`Code`,fc.`ClientName`
FROM `tms_facttrip` ft 
INNER JOIN `tms_checkpoint` fc ON ft.`ID`=fc.`FactTripID`
LEFT JOIN `tms_car` tc ON ft.`Car_ID`=tc.`Car_ID`
LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
WHERE fc.`FactTripID`=".$FactTripID." ORDER BY fc.`OrderInTrip` ASC;";
	//echo $query;
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($Id, $StartCheckTime, $EndCheckTime, $Description, $Necessary, $Name, $GeoX, $GeoY, $PointState, $Date, $OrderInTrip, $Distance, $Time, $WaitTime, $LateTime, $EstimateTime, $Address, $DurationInPoint, $Type, $IsOrderPoint, $StartWorkTime, $EndWorkTime, $Code, $ClientName);	
	while($stmt->fetch()) {
	
	/*
		/// <summary>
        ///  Не посещенная
        /// </summary>
        NotVisited = 0 ,
        
		/// <summary>
        ///  Посещенная и выполненная
        /// </summary>
        Completed = 1,
        
		/// <summary>
        ///  Посещенная, но не выполненная
        /// </summary>
        NotCompleted = 2,
        
		/// <summary>
        ///  Посещенная, частично выполненная
        /// </summary>
        PartialyCompleted = 3,
        
		/// <summary>
        /// Посещена: на месте
        /// </summary>
        InPlace = 4,
        
		//2013-12-26 новые состояния для точек
        /// <summary>
        /// Не доставлена из-за опоздания
        /// </summary>
        NotDelivered_lateness = 5,
        
		/// <summary>
        /// Не доставлена-клиент отказался
        /// </summary>
        NotDelivered_client_refused = 6
	*/
	
	
	
	
	if($PointState==1 || $PointState==4){
		$calculate = '<img src="app/images/Circle_Green.png" width="16" height="16" />';
	}elseif($PointState==2 || $PointState==5 || $PointState==6){
		$calculate = '<img src="app/images/Circle_Red.png" width="16" height="16" />';
	}elseif($PointState==3){
		$calculate = '<img src="app/images/Circle_Yellow.png" width="16" height="16" />';
	}else{
		$calculate = '<img src="app/images/Circle_Blue.png" width="16" height="16" />';
	}
	
	//if($Type==8){$Type = 'Склад';}else if($Type==1){$Type = 'Магазин';}
	if($Type==8){$Type = $warehouse ;}else if($Type==1){$Type = $store;}
	
	
	$EstimateTimeFact = getEstimateTimeFact($Id);
	
	if($dimension==1){ $Distance = mtomiles($Distance); }
	else{ $Distance = Round($Distance/1000,2); }
	
	$EstimateTimeOut = (int)($DurationInPoint*60) + strtotime($EstimateTime);
	$EstimateTimeOut = date('Y-m-d H:i:s', $EstimateTimeOut);
	
	$arrayTimeFact = getEstimateTimeFact2($Id);
	//$EstimateTimeFact = $arrayTimeFact[0]['INDATE_FACT'];
	
	$EstimateTimeFactOut = "";
	$EstimateTimeFactIn = "";
	if(count($arrayTimeFact)>0){
		$EstimateTimeFactOut = $arrayTimeFact[0]['OUTDATE_FACT'];
		$EstimateTimeFactIn = $arrayTimeFact[0]['INDATE_FACT'];
	}
	//$EstimateTimeFactOut = $arrayTimeFact[0]['OUTDATE_FACT'];
	
		$data[$i] = array(
			'Id' => $Id,
			'StartCheckTime' => $StartCheckTime,
			'EndCheckTime' => $EndCheckTime,
			'Description' => $Description,
			'Necessary' => $Necessary,
			'Name'	=> $Name,
			'GeoX' => $GeoX,
			'GeoY' => $GeoY,
			'PointState' => $PointState,
			'Date' => $Date,
			'OrderInTrip' => $OrderInTrip,
			'Distance' => $Distance,
			'Time' => $Time,
			'WaitTime'	=> $WaitTime,
			'LateTime' => $LateTime,
			'EstimateTime' => $EstimateTime,
			//'EstimateTimeFact' => $EstimateTimeFact,
			'EstimateTimeFact' => $EstimateTimeFactIn,
			'Address' => $Address,
			'DurationInPoint' => $DurationInPoint,
			'Type' => $Type,
			'IsOrderPoint' => $IsOrderPoint,
			'StartWorkTime' => $StartWorkTime,
			'EndWorkTime' => $EndWorkTime,
			'Code' => $Code,
			'ClientName' => $ClientName,
			'calculate' => $calculate,
			'EstimateTimeOut' => $EstimateTimeOut,
			'EstimateTimeFactOut' => $EstimateTimeFactOut
		);
		$i++;
	}	
	echo'{rows:'.json_encode($data).'}';
	$stmt->close();
	$db->close();
	$db2->close();
	exit;
}



if ($_REQUEST['func'] == 'showTrip') {
	
	//$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);
	}
	$where = "";
	if( $_REQUEST['warehouse'] || $_REQUEST['warehouse'] != "" ){
		$where .= " AND ch.`Name` = '".$_REQUEST['warehouse']."'";
	}
	
	if( $_REQUEST['device'] || $_REQUEST['device'] != "" ){
		$where .= " AND (wc.`imei` LIKE '%".$_REQUEST['device']."%' OR wc.`car_name` LIKE '%".$_REQUEST['device']."%')";
	}
	
	if($role == 1){
		$whereon = "";
	}else{
		$project_in = project_to_users($db);
		if(count($project_in)>0){
			$whereon = " AND p.`Project_ID` IN (".implode(",", $project_in).")";
		}else{
			$whereon = " AND p.`Project_ID` IN ('')";
		}
	}
	
$query = "SELECT t.`ID`,t.`TripId`,t.`Closed`,t.`Car_ID`, td.`Login`, CONCAT('',wc.`car_name`,' (',td.`Login`,')') AS Login2, t.`Name`,t.`WalkerNum`,t.`TripPrice`,t.`Distance`,t.`Time`,t.`Comments`,t.`StartTime`,t.`EndTime`,t.`Dispatcher_ID`,t.`Driver_ID`,t.`Forwarder_ID`,t.`Start_speedometer`,t.`End_speedometer`,p.`Name`,TIME(lt.`dateRelease`)
FROM `tms_facttrip` t
INNER JOIN `tms_car` tc ON t.`Car_ID`=tc.`Car_ID`
LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
INNER JOIN `tms_project` p ON p.`Project_ID` = t.`Project_Id` ".$whereon."
INNER JOIN `wm_car_info` wc ON wc.`imei` = td.`Login`
INNER JOIN `tms_checkpoint` ch ON ch.`FactTripID` = t.`ID`
LEFT JOIN `tms_web_lock_trip` lt ON lt.`trip_id` = t.`TripId`
WHERE td.`ID` IN (SELECT `ID` FROM `tms_device` WHERE `Client_id`=".$client_id.")
AND t.`StartTime`>='$time".' 00:00:00'."' AND t.`StartTime`<='$time".' 23:59:59'."' AND p.`Client_ID`=".$client_id." ".$where." GROUP BY t.`ID`";
/*
$query = "SELECT t.`ID`,t.`TripId`,t.`Closed`,t.`Car_ID`,td.`Login`,t.`Name`,t.`WalkerNum`,t.`TripPrice`,t.`Distance`,t.`Time`,t.`Comments`,t.`StartTime`,t.`EndTime`,t.`Dispatcher_ID`,t.`Driver_ID`,t.`Forwarder_ID`,t.`Start_speedometer`,t.`End_speedometer`
FROM `tms_facttrip` t
INNER JOIN `tms_car` tc ON t.`Car_ID`=tc.`Car_ID`
LEFT JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
INNER JOIN `tms_project` p ON p.`Project_ID` = t.`Project_Id`
WHERE td.`ID` IN (SELECT `ID` FROM `tms_device` WHERE `Client_id`=".$client_id.")
AND t.`StartTime`>='$time".' 00:00:00'."' AND t.`StartTime`<='$time".' 23:59:59'."'";
*/


	//echo $query; exit;
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($ID,$TripId,$Closed,$Car_ID,$Login,$Login2,$Name,$WalkerNum,$TripPrice,$Distance,$Time,$Comments,$StartTime,$EndTime,$Dispatcher_ID,$Driver_ID,$Forwarder_ID,$Start_speedometer,$End_speedometer,$Project_ID,$dateRelease);
	while ($stmt->fetch()) {

	/*if($Closed==1){
		$calculate = '<img src="app/images/Circle_Green.png" width="16" height="16" />';
	}elseif($Closed==2){
		$calculate = '<img src="app/images/Circle_Red.png" width="16" height="16" />';
	}elseif($Closed==3){
		$calculate = '<img src="app/images/Circle_Yellow.png" width="16" height="16" />';
	}elseif($Closed==4){
		$calculate = '<img src="app/images/Circle_Green.png" width="16" height="16" />';
	}else{
		$calculate = '<img src="app/images/Circle_Blue.png" width="16" height="16" />';
	}*/
		/*
		/// <summary>
        ///  Выполненный
        /// </summary>
        Complited = 1,
        /// <summary>
        ///  Открытй рейс (Не выполненный)
        /// </summary>
        Open = 0,
        /// <summary>
        ///  Находящийся в процессе выполнения
        /// </summary>
        InPerform =2
		*/
		
		if($Closed==1){
			$calculate = '<img src="app/images/Circle_Green.png" width="16" height="16" />';
		}elseif($Closed==2){
			$calculate = '<img src="app/images/Circle_Red.png" width="16" height="16" />';
		}else{
			$calculate = '<img src="app/images/Circle_Blue.png" width="16" height="16" />';
		}
		
		if($dimension==1){ $Distance = mtomiles($Distance); }
		else{ $Distance = Round($Distance/1000,2); }
		
		$data[] = array(
			'ID' => $ID,
			'TripId' => $TripId,
			'Closed' => $Closed,
			'Car_ID' => $Car_ID,
			'Login' => $Login,
			'Login2' => $Login2,
			'Name' => $Name,
			'WalkerNum' => $WalkerNum,
			'TripPrice' => $TripPrice,
			'Distance' => $Distance,
			'Time' => $Time,
			'Comments' => $Comments,
			'StartTime' => $StartTime,
			'EndTime' => $EndTime,
			'Dispatcher_ID' => $Dispatcher_ID,
			'Driver_ID' => $Driver_ID,
			'Forwarder_ID' => $Forwarder_ID,
			'Start_speedometer' => $Start_speedometer,
			'End_speedometer' => $End_speedometer,
			'calculate' => $calculate,
			'Signal' => '<img src="images/off.png" />',
			'project' => $Project_ID,
			'dateRelease' => $dateRelease
		);
	}
	$stmt->close();
	}
	}
	
	echo '{rows:'.json_encode($data).'}';
	$db->close();
	exit;	
}



if ($_REQUEST['func'] == 'showUsers') {	
	$query = "SELECT id, `name` FROM `toir_users` WHERE `instance_id`=$instance_id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id, $name);
	while ($stmt->fetch()) {
		$data[] = array(
				'id' => $id,
				'name' => $name
		);
	}
	$stmt->close();
	echo'{rows:'.json_encode($data).'}';
	$db->close();
	exit;
}

if ($_REQUEST['func'] == 'getRoutePath') {
	$trip_id = $_REQUEST['trip_id'];
	$trip_route = "";
	$query = "SELECT ASTEXT(`Route`) FROM `tms_checkpoint` WHERE `FactTripID` = ".$trip_id.";";
	//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;
		//$data[] = array($str);
		//echo $str.'</br>';
		//$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;
	
	
	//echo json_encode($data);
	
	//echo 'LINESTRING(36.20819 50.06773,36.20962 50.06709,36.20831 50.06592,36.20508 50.06293,36.20145 50.05961,36.19873 50.05691,36.19716 50.05536,36.19586 50.05418,36.19489 50.05329,36.19445 50.05293,36.19439 50.05285,36.19371 50.05226,36.19288 50.05153,36.19209 50.05084,36.19128 50.05019,36.1903 50.0495,36.18896 50.04866,36.18896 50.04866,36.18879 50.04855,36.18904 50.04828,36.18939 50.04802,36.19049 50.04723,36.19187 50.04582,36.19526 50.04294,36.19614 50.04104,36.19696 50.0391,36.19735 50.03819,36.19741 50.03809,36.19759 50.03787,36.19803 50.03727,36.19875 50.0361,36.19967 50.0345,36.20025 50.03342,36.2006 50.03289,36.20107 50.03229,36.20152 50.03162,36.20176 50.03126,36.20189 50.03119,36.20219 50.03101,36.20378 50.03006,36.20424 50.02978,36.2046 50.02959,36.20506 50.02954,36.20592 50.02895,36.20635 50.02872,36.20856 50.0308,36.20937 50.03161,36.20959 50.03185,36.20847 50.03236,36.20895 50.03215,36.20856 50.0318)';
	
	$db->close();
	exit;
}

if ($_REQUEST['func'] == 'calculateTrip') {	
	$trip_id = $_REQUEST['trip_id'];
	$arrOfCoordinates = array();
$query = "SELECT c.`Id`,c.`GeoX`,c.`GeoY`,c.`Id`,c.`Name`,c.`Address`,c.`EstimateTime`,c.Description,c.`IsOrderPoint`,c.PointState,c.ClientName
FROM `tms_checkpoint` c
WHERE c.`FactTripID` = ".$trip_id."
ORDER BY c.`OrderInTrip` ASC;";
	if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->bind_result($Id,$GeoX,$GeoY,$Id,$Name,$Address,$EstimateTime,$Phone,$Type,$PointState,$ClientName);
		while ($stmt->fetch()) {
			if($PointState==1 || $PointState==4){
		$calculate = 'Circle_Green';
	}elseif($PointState==2 || $PointState==5 || $PointState==6){
		$calculate = 'Circle_Red';
	}elseif($PointState==3){
		$calculate = 'Circle_Yellow';
	}else{
		$calculate = 'Circle_Blue';
	}
		
		$EstimateTimeFact = getEstimateTimeFact($Id);
		
		$arrOfCoordinates[] = array(
				'GeoX' => $GeoX,
				'GeoY' => $GeoY,
				'Id' => $Id,
				'Name' => $Name,
				'Address' => $Address,
				'EstimateTime' => $EstimateTime,
				'Phone' => $Phone,
				'EventType' => $EventType,
				'GeoXIn' => $GeoXIn,
				'GeoYIn' => $GeoYIn,
				'Type' => $Type,
				'PointState' => $PointState,
				'calculate' => $calculate,
				'ClientName' => $ClientName,
				'EstimateTimeFact' => $EstimateTimeFact,
			);
		}
		$stmt->close();
		echo json_encode($arrOfCoordinates);
	}
	
	$db->close();
	exit;
}	

if ($_REQUEST['func'] == 'calculateTrip2') {	
	$trip_id = $_REQUEST['trip_id'];
	$arrOfCoordinates = array();
	
	//$query = "SELECT `GeoX`,`GeoY`,`Id`,`Name`,`Address`,`EstimateTime` FROM `tms_checkpoint` WHERE `FactTripID` = ".$trip_id.";";
	//$query = "SELECT c.`GeoX`,c.`GeoY`,c.`Id`,c.`Name`,c.`Address`,c.`EstimateTime`,c.Type,c.PointState,e.EventType,e.SensorType,e.`GeoX` AS GeoXIn,e.`GeoY` AS GeoYIn, c.ClientName FROM `tms_checkpoint` c LEFT JOIN tms_events e ON c.`Id` = e.ParamInt WHERE c.`FactTripID` = ".$trip_id." GROUP BY Id;";
	
	//if($client_id == 74){ $EventType = ""; }else{ $EventType = "OR e.EventType=19"; }
	$EventType = "OR e.EventType=19";
	
	$query = "SELECT * FROM (SELECT c.`GeoX`,c.`GeoY`,c.`Id`,c.`Name`,c.`Address`,c.`EstimateTime`,c.`IsOrderPoint`,c.PointState,e.EventType,e.SensorType,e.`GeoX` AS GeoXIn,e.`GeoY` AS GeoYIn, c.ClientName, ADDTIME(e.`date`,'0 ".$GMT.":0:0')
FROM `tms_checkpoint` c
LEFT JOIN tms_events e ON c.`Id` = e.ParamInt
WHERE c.`FactTripID` = ".$trip_id." AND (e.EventType=28 OR e.EventType=13 OR e.EventType=14 OR e.EventType=15 OR e.EventType=4 ".$EventType.")
ORDER BY e.`date` DESC) t
GROUP BY t.Id;";

/*$query = "SELECT * FROM (SELECT c.`GeoX`,c.`GeoY`,c.`Id`,c.`Name`,c.`Address`,c.`EstimateTime`,c.`IsOrderPoint`,c.PointState,e.EventType,e.SensorType,e.`GeoX` AS GeoXIn,e.`GeoY` AS GeoYIn, c.ClientName, ADDTIME(e.`date`,'0 ".$GMT.":0:0')
FROM `tms_checkpoint` c
LEFT JOIN tms_events e ON c.`Id` = e.ParamInt
WHERE c.`FactTripID` = ".$trip_id." AND (e.EventType=13 OR e.EventType=14 OR e.EventType=15 OR e.EventType=4)
ORDER BY e.`date` DESC) t
GROUP BY t.Id;";*/
	
	/*$query = "SELECT * FROM (
SELECT c.`GeoX`,c.`GeoY`,c.`Id` AS tId,c.`Name`,c.`Address`,c.`EstimateTime`,c.`IsOrderPoint`,c.PointState,e.EventType,e.SensorType,e.`GeoX` AS GeoXIn,e.`GeoY` AS GeoYIn, c.ClientName, ADDTIME(e.`date`,'0 ".$GMT.":0:0'),
(SELECT e.EventType FROM tms_events e WHERE e.EventType=28 AND e.ParamInt = tId ORDER BY e.`date` DESC LIMIT 1) AS EventType2
FROM `tms_checkpoint` c 
LEFT JOIN tms_events e ON c.`Id` = e.ParamInt
WHERE c.`FactTripID` = ".$trip_id." AND (e.EventType=13 OR e.EventType=14 OR e.EventType=15 OR e.EventType=4)
ORDER BY e.`date` DESC
) t
GROUP BY t.tId;";*/
	
	if($stmt = $db->prepare($query)){
		$stmt->execute();
		$stmt->bind_result($GeoX,$GeoY,$Id,$Name,$Address,$EstimateTime,$Type,$PointState,$EventType,$SensorType,$GeoXIn,$GeoYIn, $ClientName, $EstimateTimeFact);
		while ($stmt->fetch()) {
			
	if($PointState==1 || $PointState==4){
		$calculate = 'Circle_Green';
	}elseif($PointState==2 || $PointState==5 || $PointState==6){
		$calculate = 'Circle_Red';
	}elseif($PointState==3){
		$calculate = 'Circle_Yellow';
	}else{
		$calculate = 'Circle_Blue';
	}
	
	//$EventType2 = getEventType($Id);
	//if($EventType2 == 28){
	if($EventType == 28){
		if ($SensorType > 0){
			$EventType = 14;
		}elseif($SensorType < 0){
			$EventType = 16;
		}
	}		
			
			$arrOfCoordinates[] = array(
				'GeoX' => $GeoX,
				'GeoY' => $GeoY,
				'Id' => $Id,
				'Name' => $Name,
				'Address' => $Address,
				'EstimateTime' => $EstimateTime,
				'Phone' => $Phone,
				'EventType' => $EventType,
				'GeoXIn' => $GeoXIn,
				'GeoYIn' => $GeoYIn,
				'Type' => $Type,
				'PointState' => $PointState,
				'calculate' => $calculate,
				'ClientName' => $ClientName,
				'EstimateTimeFact' => $EstimateTimeFact
			);
		}
		$stmt->close();
		echo json_encode($arrOfCoordinates);
	}
	
	$db->close();
	exit;
}



if (isset($_REQUEST['CloseFacttrip'])) {
	
	$Facttrip_ID = $_REQUEST['Facttrip_ID'];	
	
	$xml = '<Data InstanceID="'.$client_id.'" >
	<p type="CloseFacttrip">
	<p type="CloseFacttrip" Facttrip_ID="'.$Facttrip_ID.'"/>
	</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();
	  
	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)); }
}

