<?php
define('CHECK_TIME_OUT', 120);

function getEstimateTimeFact2($checkpointid){
	global $db2;
	global $instance_id;
	global $GMT;
	$data = array();
	
	$query = "SELECT
ADDTIME(e.`date`,'0 ".$GMT.":0:0') AS DATE_FACT
,(SELECT ADDTIME(e.`date`,'0 ".$GMT.":0:0') FROM tms_events e INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt WHERE e.ParamInt = ".$checkpointid." AND (e.EventType=4 OR e.EventType=13 OR e.EventType=14 OR e.EventType=15) ORDER BY e.`date` DESC LIMIT 1) AS INDATE_FACT
,(SELECT ADDTIME(e.`date`,'0 ".$GMT.":0:0') FROM tms_events e INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt WHERE e.ParamInt = ".$checkpointid." AND (e.EventType=5 OR e.EventType=18 OR e.EventType=19) ORDER BY e.`date` DESC LIMIT 1) AS OUTDATE_FACT
FROM tms_events e
INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt
WHERE e.ParamInt = ".$checkpointid."
ORDER BY e.`date` DESC LIMIT 1;";
	//echo $query;
	//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' QUERY = '.$query );
	if($stmt = $db2->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($DATE_FACT,$INDATE_FACT,$OUTDATE_FACT);	
	while($stmt->fetch()){
		$data[] = array(
			'DATE_FACT' => $DATE_FACT,
			'INDATE_FACT' => $INDATE_FACT,
			'OUTDATE_FACT' => $OUTDATE_FACT
		);
	}
	$stmt->close();
	//writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' date = '.json_encode($data) );
	return $data;
	}else{ /*writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' execute date NULL ' );*/ return array('DATE_FACT' => '','INDATE_FACT' => '','OUTDATE_FACT' => ''); }
	}else{ /*writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$instance_id.' prepare date NULL ' );*/ return array('DATE_FACT' => '','INDATE_FACT' => '','OUTDATE_FACT' => ''); }
}

function getEstimateTimeFact($checkpointid){
	global $db2;
	global $client_id;
	global $GMT;
	
	//if($client_id == 74){ $EventType = ""; }else{ $EventType = "OR e.EventType=19"; }
	$EventType = "OR e.EventType=19";
	
	$query = "SELECT ADDTIME(e.`date`,'0 ".$GMT.":0:0') FROM tms_events e INNER JOIN `tms_checkpoint` c ON c.`Id` = e.ParamInt WHERE e.ParamInt = ".$checkpointid." AND (e.EventType=13 OR e.EventType=14 OR e.EventType=15 OR e.EventType=5 ".$EventType.") ORDER BY e.`date` ASC LIMIT 1;";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
	if($stmt = $db2->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($date);	
	$stmt->fetch();
	$stmt->close();
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' date = '.$date );
		return $date;
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' execute date NULL ' ); return ""; }
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' prepare date NULL ' ); return ""; }
	
}

function getEventType($checkpointid){
	global $db2;
	global $client_id;

	$query = "SELECT e.EventType FROM tms_events e WHERE e.ParamInt = ".$checkpointid."  AND (e.EventType=28 OR e.EventType=13 OR e.EventType=14 OR e.EventType=15 OR e.EventType=4) ORDER BY e.`date` DESC LIMIT 1";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' QUERY = '.$query );
	if($stmt = $db2->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($EventType);	
	$stmt->fetch();
	$stmt->close();
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' EventType = '.$EventType );
		return $EventType;
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' execute EventType NULL ' ); return ""; }
	}else{ writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' prepare EventType NULL ' ); return ""; }
}









// -- 09.02.2016 проверка привязаных складов пользователя -->
function warehouse_to_users($db,$user_id,$instance_id){
	$warehouse_in = array();
	$flag = false;
	$query = "SELECT `warehouse_id` FROM `wm_warehouse_to_users` WHERE `user_id` = ".$user_id." AND `instance_id` = ".$instance_id.";";
	//echo $query; exit;
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$stmt->bind_result($warehouse_id);
		$stmt->store_result();
		if($stmt->num_rows()>0){
			$flag = true;
			while($stmt->fetch()){
				array_push($warehouse_in,$warehouse_id);
			}
		}
		$stmt->close();
	}
	}
	return $warehouse_in;
}
// <-- 09.02.2016 проверка привязаных складов пользователя --









// -- 10.02.2016 просчет проценнтного соотношения Веса Объема для окна планирования  -->
function getMaxCarSettings($db,$imei,$client_id){
	$data=array();
	$query = "SELECT csc.`DUCategory`,`CargoValue` FROM tms_car c
INNER JOIN tms_device d ON d.`ID` = c.`Device_id`
INNER JOIN  tms_carsection cs ON cs.`Car_ID` = c.`Car_ID`
INNER JOIN tms_carsectioncargo csc ON csc.Carsection_ID = cs.`CarSection_ID`
WHERE d.`Login` = '".$imei."'
AND (csc.`DUCategory`='Weight' OR csc.`DUCategory`='Volume')
AND d.DeletedRecord = 0 AND csc.`DeletedRecord` = 0
ORDER BY csc.`DUCategory` ASC;";
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($DUCategory,$CargoValue);
	$stmt->store_result();
	if($stmt->num_rows()>0){
	while($stmt->fetch()){
	$data[]=array($CargoValue);
	}
	$stmt->close();
	}else{ $data[0]=array(0); $data[1]=array(0); }
	}
	}
	return $data;
}

function getMaxCarPercent($db,$maxarray,$tripid,$client_id){
	
	$data=array();
	$query = "SELECT ABS(SUM(t.1C)),ABS(SUM(t.2C)) FROM (
SELECT SUM(ABS(tlc.`CargoValueVolume`)) AS 1C ,SUM(ABS(tlc.`CargoValueWeight`)) AS 2C
FROM `tms_lupointcargo` tlc
INNER JOIN tms_loadunloadpoint tlp ON tlp.LoadUnloadPoint_ID = tlc.`LUPoint_ID`
INNER JOIN `tms_trippoint` tp ON tp.`Point_ID` = tlp.`LoadUnloadPoint_ID`
WHERE tp.`Trip_ID` = ".$tripid." AND 
IsOrderPoint<>0
GROUP BY tlc.`LUPoint_ID`
) t;";
//HAVING 2C < 0
writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' tms_trip getMaxCarPercent = '.$query );
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($Volume,$Weight);
	while($stmt->fetch()){
	/*$data[]=array(
		'Volume'=>$Volume,
		'Weight'=>$Weight
	);*/
	}
	}
	$stmt->close();
	}
	
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' tms_trip getMaxCarPercent Volume = '.$Volume.' Weight = '.$Weight );
	
	$VolumeMax = $maxarray[0][0];
	$WeightMax = $maxarray[1][0];
	
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' tms_trip getMaxCarPercent VolumeMax = '.$VolumeMax.' WeightMax = '.$WeightMax );
	
	if($VolumeMax!=0 || $WeightMax!=0){
		$percentVolume = round(((float)(abs($Volume)/$VolumeMax) * 100),2);
		$percentWeight = round(((float)(abs($Weight)/$WeightMax) * 100),2);
		
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' = '. (abs($Volume)/$VolumeMax) * 100 );
		writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' = '. (abs($Weight)/$WeightMax) * 100 );
		
		$data=array($percentVolume,$percentWeight);
	}else{
		$data=array(0,0);
	}
	return $data;
}
// <-- 10.02.2016 просчет проценнтного соотношения Веса Объема для окна планирования  --

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





// -- 23.03.2016 выборка статусов точек и рейсов для окна планирования -->
function get_point_state($db,$TripID){
	$point_state = array();
	$query = "SELECT ch.`PointState` FROM `tms_checkpoint` ch
INNER JOIN `tms_facttrip` ft ON ft.`ID` = ch.`FactTripID`
INNER JOIN `tms_trip` tr ON tr.`Trip_ID` = ft.`TripId`
WHERE tr.`Trip_ID` = ".$TripID." ORDER BY ch.`OrderInTrip` ASC;";
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$stmt->bind_result($PointState);
		$stmt->store_result();
		//if($stmt->num_rows()>0){
			while($stmt->fetch()){
				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" />';
				}elseif($PointState===0){
					$calculate = '<img src="app/images/Circle_Blue.png" width="16" height="16" />';
				}else{
					$calculate = '';
				}
				array_push($point_state,$calculate);
			}
		//}
		$stmt->close();
	}
	}
	return $point_state;
}

function get_route_state($db,$ProjectID){
	$point_state = array();
	$query = "SELECT ft.`Closed` FROM `tms_trip` t
LEFT JOIN `tms_facttrip` ft ON ft.`TripId` = t.`Trip_ID`
INNER JOIN `tms_project` p ON p.`Project_ID`= t.`Project_ID`
WHERE p.`Project_ID` = ".$ProjectID." ORDER BY t.`Trip_ID`;";
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$stmt->bind_result($Closed3);
		$stmt->store_result();
		//if($stmt->num_rows()>0){
			while($stmt->fetch()){
				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 = '';
				}
				array_push($point_state,$Closed3);
			}
		//}
		$stmt->close();
	}
	}
	return $point_state;
}
// <-- 23.03.2016 выборка статусов точек и рейсов для окна планирования --





// -- 26.04.2016 Функции для работы с Проектом/Заявками на стороне сервера -->
function check($lid,$db) {
	while (true) {
		if ($count < CHECK_TIME_OUT) {
			$query = "SELECT `in_progress` FROM `tms_web_tasks` WHERE `id` = $lid";
			if ($stmt = $db->prepare($query)) {
				$stmt->execute();
				$stmt->bind_result($in_progress);
				$stmt->fetch();
				$stmt->close();
				if ($in_progress == 1) {
					return true;
				} elseif ($in_progress == -1) {
					return false;
				}
			} else {
				return false;
			}
				
			$count++;
		} else {
			$query = "UPDATE `tms_web_tasks` SET `in_progress`=-1 WHERE `id` = ".$lid.";";
			if ($stmt = $db->prepare($query)) {
				$stmt->execute();
				$stmt->close();
				return false;				
			} else {
				return false;				
			};
		}
		sleep(1);
	}
}

function getProject($db,$instance_id){
	$Project = null;
	$query = "SELECT `Project_ID` FROM `tms_project` WHERE `Client_ID`=".$instance_id." AND DATE(`Date`)=DATE(NOW());";
	if($stmt = $db->prepare($query)){
	if($stmt->execute()){
	$stmt->bind_result($Project_ID);
	if ($stmt->fetch()) {
		$Project = $Project_ID;
	}
	$stmt->close();
	}
	}
	return $Project;
}

function createProject($db,$instance_id,$start_time = '09:00'){
	$projectId = 'Project_Micron_'.$instance_id.'_'.time();
	$date_project = date('Y-m-d');
	$result = false;
	//$start_time = '09:00';
					
	$xmlTask = '<Data InstanceID="'.$instance_id.'" >
	<p type="CreateProject">
	<p type="CreateProject"  ProjectName="'.$projectId.'" ProjectDate="'.$date_project.'" InstanceProjectID="'.$projectId.'" WarehouseStartTime="0001-01-01 '.$start_time.':00" />
	</p>
	</Data>';
					
	$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`,`in_progress`) VALUES ('".$xmlTask."',".$instance_id.",0)";
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$last_id = $db->insert_id;
		$stmt->close();
		$result = check($last_id,$db);
		if (!$result) {				
			return 'error';
		}
			return 'success';
	}
	}
}

function createOrder($db,$instance_id,$project,$data_array,$data_array2){
	
	$name = $data_array[0]['Name'];
	$NameClient = $data_array[0]['NameClient'];
	$code_client = $data_array[0]['Code'];
	$original_address = $data_array[0]['Address'];
	$startTime = $data_array[0]['StartTime'];
	$endTime = $data_array[0]['EndTime'];
	$note = $data_array[0]['Comments'];
	$ContactPerson = $data_array[0]['ContactPerson'];
	$geoX =  $data_array[0]['GeoX'];
	$geoY = $data_array[0]['GeoY'];
	$phone = $data_array[0]['Phone'];
	
	$code = $data_array[0]['id'];
	
	$load_unload_time = 3;

		//$dir = $_SERVER['DOCUMENT_ROOT']."/monit2_task_ne_test/app/Logist/files/";
		//$dir = $_SERVER['DOCUMENT_ROOT']."/app/Logist/files/";
		$dir = "C:/htdocs/monit2_task_ne/app/Logist/files/";
		
		$filename = $dir.$instance_id."task".rand(1,100).".xml";
		
		if( file_exists($filename) ){ unlink($filename); }
		$dom = new domDocument("1.0", "UTF-8");
		$dom->preserveWhiteSpace = false;
		$dom->formatOutput = true;
		
		$root = $dom->createElement("ORDERS");
		$dom->appendChild($root);
					
		$_ORDER = $dom->createElement("ORDER");
		$root->appendChild($_ORDER);
		
		$_CODE = $dom->createElement("CODE", $code);
		$_ORDER->appendChild($_CODE);
		$_NAME = $dom->createElement("NAME", $name);
		$_ORDER->appendChild($_NAME);
		
		$_ADDRESS = $dom->createElement("ADDRESS", $original_address);
		$_ORDER->appendChild($_ADDRESS);
		
		$_GeoX = $dom->createElement("GeoX", $geoY);
		$_ORDER->appendChild($_GeoX);
		$_GeoY = $dom->createElement("GeoY", $geoX);
		$_ORDER->appendChild($_GeoY);
		$_COMMENTS_ORDERS = $dom->createElement("COMMENTS_ORDERS", $note);
		$_ORDER->appendChild($_COMMENTS_ORDERS);
		$_PHONE = $dom->createElement("PHONE", $phone);
		$_ORDER->appendChild($_PHONE);
		$_COMMENTS = $dom->createElement("COMMENTS", '');
		$_ORDER->appendChild($_COMMENTS);
		$_COMMENTS_SHOP = $dom->createElement("COMMENTS_SHOP", '');
		$_ORDER->appendChild($_COMMENTS_SHOP);
		$_CLIENT_CODE = $dom->createElement("CLIENT_CODE", $code_client);
		$_ORDER->appendChild($_CLIENT_CODE);
		$_CLIENT_NAME = $dom->createElement("CLIENT_NAME", $NameClient);
		$_ORDER->appendChild($_CLIENT_NAME);
		$_SHOP_CODE = $dom->createElement("SHOP_CODE", $code."_".time());
		$_ORDER->appendChild($_SHOP_CODE);
		$_SHOP_NAME = $dom->createElement("SHOP_NAME", '');
		$_ORDER->appendChild($_SHOP_NAME);
		$_DELIVERY_DATE = $dom->createElement("DELIVERY_DATE", date('Y-m-d'));
		$_ORDER->appendChild($_DELIVERY_DATE);
		//$_SHOP_WORK_TIME = $dom->createElement("SHOP_WORK_TIME", '8:00-15:00');
		$_SHOP_WORK_TIME = $dom->createElement("SHOP_WORK_TIME", $startTime.'-'.$endTime);
		$_ORDER->appendChild($_SHOP_WORK_TIME);
		$_SHOP_DINNER_TIME = $dom->createElement("SHOP_DINNER_TIME", '');
		$_ORDER->appendChild($_SHOP_DINNER_TIME);
		$_UNLOAD_TIME = $dom->createElement("UNLOAD_TIME", $load_unload_time);
		$_ORDER->appendChild($_UNLOAD_TIME);
		$_SPECIALIZATION_Name = $dom->createElement("SPECIALIZATION_Name", '');
		$_ORDER->appendChild($_SPECIALIZATION_Name);
		$_SPECIALIZATION_Id = $dom->createElement("SPECIALIZATION_Id", '');
		$_ORDER->appendChild($_SPECIALIZATION_Id);
		$_EXPEDITION = $dom->createElement("EXPEDITION", '');
		$_ORDER->appendChild($_EXPEDITION);
		
		$_WAREHOUSE_CODE = $dom->createElement("WAREHOUSE_CODE", "");
		$_ORDER->appendChild($_WAREHOUSE_CODE);
		
		$_PRODUCTS = $dom->createElement("PRODUCTS");
		$_ORDER->appendChild($_PRODUCTS);
		
		foreach($data_array2 as $key=>$value){
			//echo $value['id'].'<br/>';
			
			$_PRODUCT = $dom->createElement("PRODUCT");
			$_PRODUCTS->appendChild($_PRODUCT);
		
			$_PROD_CODE = $dom->createAttribute("PROD_CODE");
			//$_PROD_CODE->value = $value['id'];
			$_PROD_CODE->value = $value['barcode'];
			$_PRODUCT->appendChild($_PROD_CODE);
		
			$_PROD_NAME = $dom->createAttribute("PROD_NAME");
			$_PROD_NAME->value = $value['NameCargo'];
			$_PRODUCT->appendChild($_PROD_NAME);
		
			$_COUNT = $dom->createAttribute("COUNT");
			//$_COUNT->value = 1;
			$_COUNT->value = $value['count'];
			$_PRODUCT->appendChild($_COUNT);
		
			$_COST = $dom->createAttribute("COST");
			//$_COST->value = $value['Cost1'];
			$_COST->value = $value['Cost'];
			$_PRODUCT->appendChild($_COST);
		
			$_WEIGHT = $dom->createAttribute("WEIGHT");
			$_WEIGHT->value = 0;
			$_PRODUCT->appendChild($_WEIGHT);
		
			$_VOLUME = $dom->createAttribute("VOLUME");
			$_VOLUME->value = 0;
			$_PRODUCT->appendChild($_VOLUME);
		}
		
		$xml = $dom->save($filename);

			$xml = '<Data InstanceID="'.$instance_id.'" >
			<p type="ImportDataXML">
			<p type="ImportDataXML"  ProjectID="'.$project.'" dir="'.$filename.'" />
			</p>
			</Data>';

			$query = "INSERT INTO `tms_web_tasks` (`task`,`user_id`,`in_progress`) VALUES ('".$xml."',".$instance_id.",0)";
			if ($stmt = $db->prepare($query)){
			if ($stmt->execute()){
			$last_id = $db->insert_id;
			$stmt->close();

			$result = check($last_id,$db);
					
			if (!$result) {				
				return 'error';
			}
				return 'success';	
			
			}else{ return 'error'; }
			}else{ return 'error'; }
}
// <-- 26.04.2016 Функции для работы с Проектом/Заявками на стороне сервера --


// -- 06.05.2016 проверка привязаных проектов пользователя -->
function project_to_users($db){
	$user_id = $_SESSION['id'];
	$client_id = $_SESSION['client_id'];
	$project_in = array();
	$flag = false;
	$query = "SELECT `Project_ID` FROM `tms_project_to_logist` WHERE `Logist_ID` = ".$user_id.";";
	//echo $query; exit;
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$stmt->bind_result($project_id);
		$stmt->store_result();
		if($stmt->num_rows()>0){
			$flag = true;
			while($stmt->fetch()){
				array_push($project_in,$project_id);
			}
		}
		$stmt->close();
	}
	}
	return $project_in;
}
// <-- 06.05.2016 проверка привязаных проектов пользователя --

// -- 06.05.2016 создание привязки проекта к пользователю -->
function create_project_to_users($db){
		
		$name_project = $_SESSION['addProjectName'];
		$date_project = $_SESSION['addProjectDate'];
		$user_id = $_SESSION['id'];
		
		$query = "INSERT INTO `tms_project_to_logist` (`Project_ID`,`Logist_ID`) SELECT `Project_ID`,".$user_id." FROM `tms_project` WHERE `Name` = '".$name_project."' AND DATE(`Date`) = '".$date_project."';";
		if ($stmt = $db->prepare($query)){
		if ($stmt->execute()){
			$stmt->close();
			return 'success';
		}else{ return 'error'; }
		}else{ return 'error'; }
		
}
// -- 06.05.2016 создание привязки проекта к пользователю -->


// -- 06.05.2016 проверка привязаных авто пользователя -->
function imei_to_users($db){
		
	$user_id = $_SESSION['id'];
	$client_id = $_SESSION['client_id'];
	$imei_in = array();
	
	$flag = false;
	$query = "SELECT ci.imei FROM wm_car_info ci
INNER JOIN `wm_car_group_ref` gr ON gr.`car_id`=ci.`id`
INNER JOIN `wm_cargroup_user_ref` gu ON gu.`group_id`=gr.`group_id` AND gu.`user_id`= ".$user_id."
WHERE ci.client_id = ".$client_id." GROUP BY ci.id;";
	//echo $query; exit;
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$stmt->bind_result($imei);
		$stmt->store_result();
		if($stmt->num_rows()>0){
			$flag = true;
			while($stmt->fetch()){
				array_push($imei_in,$imei);
			}
		}
		$stmt->close();
	}
	}
	return $imei_in;
}
// -- 06.05.2016 проверка привязаных авто пользователя -->



function checkImeiBeforeAutoplan($db,$project){
	$imei_in = array();
	$client_id = $_SESSION['client_id'];
	
	$imei_to_users_in = imei_to_users($db);
	$imei_to_users_in_str = "'".implode("','", $imei_to_users_in)."'";
	
	$query = "SELECT d.`Login` FROM tms_car_snapshot cs
INNER JOIN `tms_device` d ON cs.`Device_id` = d.`ID` 
WHERE cs.`Project_ID` = ".$project."
AND d.`Login` NOT IN (".$imei_to_users_in_str.");";
	//echo $query; exit;
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$stmt->bind_result($imei);
		$stmt->store_result();
		if($stmt->num_rows()>0){
			while($stmt->fetch()){
				array_push($imei_in,$imei);
			}
		}
		$stmt->close();
	}
	}
	return $imei_in;
}

function checkCarBeforeTripRelease($db,$tripid,$imei,$projectid,$starttime){
	
	$tripid_in = array();
	$query = "SELECT `TripId` FROM `tms_facttrip` ft
INNER JOIN `tms_car` tc ON ft.`Car_ID` = tc.`Car_ID`
INNER JOIN `tms_device` td ON tc.`Device_id`=td.`ID`
WHERE td.`Login` = '".$imei."' AND (ft.`Closed` = 2 OR ft.`Closed` = 0) AND ft.`TripId` <> ".$tripid."
AND ft.`EndTime` > '".$starttime."'";
	//echo $query;
	if ($stmt = $db->prepare($query)) {
	if ($stmt->execute()) {
		$stmt->bind_result($TripId);
		$stmt->store_result();
		if($stmt->num_rows()>0){
			while($stmt->fetch()){
				array_push($tripid_in,$TripId);
			}
		}
		$stmt->close();
	}
	}
	return $tripid_in;
	
}



function getCarCostKm($db,$imei,$client_id){
	$data = array();
	$data[] = array('cost'=>0);
	$query = "SELECT `kmh_kost` FROM `wm_car_info` WHERE `imei` = '".$imei."';";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' tms_trip getCarCostKm = '.$query );
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($cost);
	while($stmt->fetch()){
	 $data = array();
	 $data[]=array(
		'cost'=>$cost,
	);
	}
	}
	$stmt->close();
	}
	return $data;
}


function getCarSettings($db,$imei,$client_id){
	$data = array();
	$query = "SELECT tc.`Rounds` FROM `tms_car` tc INNER JOIN `tms_device` td ON tc.`Device_id` = td.ID WHERE td.`Login` = '".$imei."'";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' tms_trip getCarCostKm = '.$query );
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($Rounds);
	while($stmt->fetch()){
	 $data[]=array(
		'Rounds'=>$Rounds,
	);
	}
	}
	$stmt->close();
	}
	return $data;
}

function getCarRounds($db,$imei,$client_id,$project){
	$data = array();
	$query = "SELECT COUNT(t.`Trip_ID`) FROM `tms_trip` t
LEFT JOIN tms_car_snapshot cs ON cs.ID = t.`Car_ID`
LEFT JOIN tms_car c ON cs.`Car_ID` = c.`Car_ID`
LEFT JOIN tms_device d ON d.`ID` = c.`Device_id`
WHERE t.`Project_ID` = ".$project." AND d.`Login` = '".$imei."'";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' tms_trip getCarCostKm = '.$query );
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($Rounds);
	while($stmt->fetch()){
	 $data[]=array(
		'Rounds'=>$Rounds,
	);
	}
	}
	$stmt->close();
	}
	return $data;
}


function getTripCost($db,$carid,$tripid){
	$data = 0;
	
	$query = "SELECT t.`Distance`, wc.`kmh_kost` FROM `tms_trip` t
LEFT JOIN `tms_car_snapshot` c ON c.`ID` = t.`Car_ID`
LEFT JOIN `tms_device` d ON c.`Device_id` = d.`ID`
LEFT JOIN `wm_car_info` wc ON wc.`imei` = d.`Login`
WHERE t.`Car_ID` = ".$carid." AND t.`Trip_ID` = ".$tripid.";";
	writeToLogFile( ' -> '.date('Y-m-d H:i:s').' INSTANCEID = '.$client_id.' getTripCost = '.$query );
	if( $stmt = $db->prepare($query) ){
	if( $stmt->execute() ){
	$stmt->bind_result($distance,$kmh_kost);
	while($stmt->fetch()){
		$data = Round((($distance/1000) * $kmh_kost),2);
	}
	}
	$stmt->close();
	}
	
	return $data;
}

?>