<?php  
  	session_start();
  	//error_reporting(E_ALL);
    header('Content-Type: text/html; charset=utf-8');
    require_once("../settings.php");
	//require_once("settings.php");
	include_once 'TOiR_translate.php';
    require_once("ClassLibs/ClassMessageAndLogs.php");

    require_once 'htmltodocx/PHPWord.php';
    require("htmltodocx/phpDocx.php");




    $ObjLogsTOiR = new MessageAndLogs();
	$instance_id = $_SESSION['client_id'];
        //require "log.php";
        //include "../settings.php";
		include "writedblog.php";
		//require_once 'settings.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']);
        //$db = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
        $db->query("SET CHARSET SET utf8");
        $db->query("SET NAMES 'utf8'");
        
        $db1 = new mysqli ($dbset['DB']['DB_HOST'],$dbset['DB']['DB_USER'],$dbset['DB']['DB_PASS'],$dbset['DB']['DB_NAME']);
        //$db = new mysqli ($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME);
        $db1->query("SET CHARSET SET utf8");
        $db1->query("SET NAMES 'utf8'");
        
        if (mysqli_connect_errno()) {
            echo "Error connecting to DB";
            exit;
        }

$start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
$limit = isset($_REQUEST['limit']) ? $_REQUEST['limit'] : 50;

if ($_REQUEST['func'] == 'show') {
	$filter = isset($_REQUEST['filter']) ? $_REQUEST['filter'] : '';
	/* if ($filter) {
		$query = "SELECT id FROM  `toir_users` WHERE `name` LIKE '%$filter%'";
		$stmt = $db->prepare($query);
		 
	}  */
	$data = array();
	if ($filter) {
		$filterParam = "AND u.name LIKE '%$filter%'"; 
	} else {
		$filterParam = '';
	}
	/* $query = "SELECT COUNT(r.id_request) FROM toir_request r
	LEFT JOIN `toir_users` u ON u.id=r.`name_master` 
	WHERE r.instance_id=$instance_id $filterParam"; */
	$query = "SELECT COUNT(*) 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`
	LEFT 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 r.`instance_id`=$instance_id  $filterParam";
	
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($total);
	$stmt->fetch();
	$stmt->close();
	
	$query = "SELECT r.`id_request`,  t.name_job, r.`workshop`,  c.`client`, o.`name`, r.`region`,  r.`city`, r.`address`, r.`contact_number`, r.`contact_name`, r.`id_code_PSD`, 
		r.`contract_number`, r.`note`, r.`type_TT`, r.`sales_contact_number`,  r.`status`,  u.name, e.model, r.`time_change`, r.`time_execut`, r.work_duration, r.`external_id` 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`
		LEFT 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 r.`instance_id`=$instance_id  $filterParam ORDER BY r.`id_request` DESC LIMIT $start, $limit";
		//echo $query; exit;
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($id, $job_type, $workshop, $client, $outlet, $region, $sity, $address, $contact_phone, $contact_man, $ID_PSD,
		$client_number, $description, $outlet_type, $sales_man, $status, $worker, $equipment, $date_create, $date_execut, $work_duration, $external_id);
		while ($stmt->fetch()) {
			if ($workshop) {
				$workshop = $inWorkshop;
			} else {
				$workshop = $onPlace;
			}
			
			$status2 = $status;
			
			if ($status) {
				$status = $achieved;
			} else {
				$status = $inProcess;
			}
			if (!$worker) {
				$worker = $notAssigned;
			}
			$data[] = array(
				'id' => $id,
				'job_type' => $job_type,
				'workshop' => $workshop,
				'client' => $client,
				'outlet' => $outlet,
				'region' => $region,
				'sity' => $sity,
				'address' => $address,
				'contact_phone' => $contact_phone,
				'contact_man' => $contact_man,
				'ID_PSD' => $ID_PSD,
				'client_number' => $client_number,
				'description' => $description,
				'outlet_type' => $outlet_type,
				'sales_man' => $sales_man,
				'status' => $status,
				'status2' => $status2,
				'worker' => $worker,
				'equipment' => $equipment,
				'date_create' => $date_create,
				'date_execut' => $date_execut,
				'work_duration' => 	$work_duration,
				'external_id' => $external_id	
			);
		}
	$stmt->close();	
	echo'{"total":"'.$total.'",rows:'.json_encode($data).'}'; 
}

if ($_REQUEST['func'] == 'showJobType') {
	$query = "SELECT `id_type_job`,`name_job` FROM `type_job` 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).'}'; 
}

if ($_REQUEST['func'] == 'showClients') {
	$query = "SELECT `id_client`, `client` FROM `client` 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).'}';
}

if ($_REQUEST['func'] == 'showOutlets') {
	$clientID = $_REQUEST['clientID'];
	$query = "SELECT `id_outlet`, `name` FROM `toir_outlets` WHERE client_id = $clientID";
	$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).'}';
}

if ($_REQUEST['func'] == 'showEquipment') {
	$outletID = $_REQUEST['outletID'];
	/* $query = "SELECT ce.`id_equipment`, e.`model` FROM `toir_client_equipment` ce
	INNER JOIN `equipment` e ON e.`id_equipment`=ce.`id_equipment`
	WHERE client_id = $clientID"; */
	$query = "SELECT e.`id_equipment`, e.`model` FROM `toir_outlet_equipment` oe
		INNER JOIN `equipment` e ON e.`id_equipment`=oe.`equipment_id`
		WHERE oe.`outlet_id`=$outletID";
	//echo $query;
	$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).'}';
}

if ($_REQUEST['func'] == 'showUsersRE') {
	$data[0] = array(
			'id' => 0,
			'name' => $notAssigned
	);
	$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).'}';
}

if ($_REQUEST['func'] == 'getOutletsData') {
	$id = $_REQUEST['id'];
	$query = "SELECT      
		  `region`,
		  `city`,
		  `address`,
		  `contact_number`,
		  `contact_name`,
		  `id_code_PSD`,
		  `contract_number`,
		  `note`,
		  `type_TT`,
		  `sales_contact_number`
	FROM `toir_outlets` WHERE `id_outlet`= $id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($region, $sity, $address, $contact_phone, $contact_man, $ID_PSD, $client_number, $description, 
			$outlet_type, $sales_man);
	while ($stmt->fetch()) {
		$data = array(
				'region' => $region,
				'sity' => $sity,
				'address' => $address,
				'contact_phone' => $contact_phone,
				'contact_man' => $contact_man,
				'ID_PSD' => $ID_PSD,
				'client_number' => $client_number,
				'description' => $description,
				'outlet_type' => $outlet_type,
				'sales_man' => $sales_man			
		);
	}
	$stmt->close();
	echo json_encode($data);	
}

if ($_REQUEST['func'] == 'addedit') {
	$data[workshop] = isset($_REQUEST['workshop']) ? $_REQUEST['workshop'] : 0;
	$data[status] = isset($_REQUEST['status']) ? $_REQUEST['status'] : 0;
	$data[requestcount] = isset($_REQUEST['requestcount']) ? $_REQUEST['requestcount'] : 0;
	
	//$data[name_master] = isset($_REQUEST['status']) ? $_REQUEST['status'] : 0;
	foreach ($_POST as $key => $value)
	{			
		$data[$key] = $value;		
	}
	
	if($_POST['id'] || $_POST['id']!=""){
		
		$query = "UPDATE toir_request SET 
				     `id_type_job`=$data[job_type],
		             `workshop`=$data[workshop],
		             `client_id`=$data[client],
		             `outlet_id`=$data[outlet],
		             `region`='$data[region]',
		             `city`='$data[sity]',
		             `address`='$data[address]',
		             `contact_number`='$data[contact_phone]',
		             `contact_name`='$data[contact_man]',
		             `id_code_PSD`='$data[ID_PSD]',
		             `contract_number`='$data[client_number]',
		             `note`='$data[description]',
		             `type_TT`='$data[outlet_type]',
		             `sales_contact_number`='$data[sales_man]',
		             `time_change`='$data[date_create]',
		             `time_execut`='$data[date_execut]',
		             `status`=$data[status],
		             `name_master`=$data[worker],
		             `id_equipment`=$data[equipment],
		             `work_duration` = $data[work_duration],
		             `requestcount` = $data[requestcount],
					 `external_id` = '$data[external_id]'
				WHERE id_request = $_POST[id]";
	}else{
		$query = "INSERT INTO  toir_request (
					 `instance_id`,
		             `id_type_job`,
		             `workshop`,
		             `client_id`,
		             `outlet_id`,
		             `region`,
		             `city`,
		             `address`,
		             `contact_number`,
		             `contact_name`,
		             `id_code_PSD`,
		             `contract_number`,
		             `note`,
		             `type_TT`,
		             `sales_contact_number`,
		             `time_change`,
		             `time_execut`,
		             `status`,
		             `name_master`,
		             `id_equipment`,
		             `work_duration`,
					 `requestcount`,
		             `external_id`
		     ) 
				VALUES (
					$instance_id,
					$data[job_type],
					$data[workshop],
					$data[client],
					$data[outlet],
				   '$data[region]',
				   '$data[sity]',
				   '$data[address]',
				   '$data[contact_phone]',
				   '$data[contact_man]',
				   '$data[ID_PSD]',
				   '$data[client_number]',
				   '$data[description]',
				   '$data[outlet_type]',
				   '$data[sales_man]',
				   '$data[date_create]',
				   '$data[date_execut]',
				    $data[status],
				    $data[worker],
				    $data[equipment],
				    $data[work_duration],
					$data[requestcount],
				    '$data[external_id]'
				    
			)";
	}
	//echo $query; exit;
	if ($stmt = $db->prepare($query)) {
		$stmt->execute();
		if (!$_POST['id'] || $_POST['id'] =="") {
			$lastInsertID = $stmt->insert_id;			
		} 
		$stmt->close();		
		//Bind request into trip if it exist or if request has worker
		$request_id = $_POST['id'] ? $_POST['id'] : $lastInsertID;		
		$date = date('Y-m-d', strtotime($data['date_execut']));
		$query = "SELECT id FROM `toir_trip` WHERE  `StartTime`>='$date 00:00:00' AND `StartTime`<='$date 23:59:59' 
				AND `name_master`=$data[worker]";
		$ObjLogsTOiR->WriteLogAttendance("Check trip ".$query,'Toir_planning_trip','logs');
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($trip_id);
		$stmt->fetch();
		$stmt->close();
		//$ObjLogsTOiR->WriteLogAttendance("Check trip ".$query,'Toir_planning_trip','logs');
		if ($trip_id) {
			$ObjLogsTOiR->WriteLogAttendance("Bind request request_id=".$request_id.' trip_id='.$trip_id,'Toir_planning_trip','logs');
			$query = "SELECT MAX(r.place) FROM `toir_trip_request` tr
			INNER JOIN `toir_request` r ON r.`id_request`=tr.`request_id`
			WHERE tr.`trip_id`=$trip_id ";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->bind_result($max);
			$stmt->fetch();
			$stmt->close();
			$max++;
			$query = "UPDATE `toir_request` SET `place` = $max WHERE `id_request`=$request_id";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
			
			$query = "INSERT INTO `toir_trip_request` (`trip_id`, `request_id` ) VALUES ($trip_id, $request_id)";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
			$ObjLogsTOiR->WriteLogAttendance("Bind request INSERT ".$query,'Toir_planning_trip','logs');
			
			
			$arrOfRequestData = array();
			$query = "SELECT r.`id_request` FROM toir_request r
			INNER JOIN `toir_trip_request` tr ON tr.`request_id`=r.id_request AND tr.`trip_id`=$trip_id";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->bind_result($id_request);
			while ($stmt->fetch()) {
				$arrOfRequestData [] = $id_request;
			}
			$stmt->close();
			
			foreach ($arrOfRequestData as $id) {
				$query = "UPDATE toir_request SET time_beginning_work=NULL WHERE id_request=$id";
				$stmt = $db->prepare($query);
				$stmt->execute();
				$stmt->close();
			}
			
			$query = "UPDATE `toir_trip` SET `Distance` = 0, `Time`= 0, `trip_route`='' WHERE id=$trip_id";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
			
			/* $_REQUEST['func'] = 'showTrip';
			$_REQUEST['time'] = $date; 
			include 'TOiR_planning.php'; */
		}
		
		echo json_encode(array('success'=>true,'message'=>''));
	} else {
		echo json_encode(array('success'=>false, 'message'=>$db->errno . ' ' . $db->error));
	}
	$db->close();	
}

if ($_REQUEST['func'] == 'delete') {
	$id = $_REQUEST['id'];	
	$query = "DELETE FROM `toir_request` WHERE `id_request`=$id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->close();
}

if ($_REQUEST['func'] == 'editshow') {
	$id = $_REQUEST['id'];
	$data = array();
	$query = "SELECT
		  `id_type_job`,		  
		  `client_id`,
		  `outlet_id`,
		  `region`,
		  `city`,
		  `address`,
		  `contact_number`,
		  `contact_name`,
		  `id_code_PSD`,
		  `contract_number`,
		  `note`,
		  `type_TT`,
		  `sales_contact_number`,		  
		  `name_master`,
		  `workshop`,
		  `status`,
		  `id_equipment`,
		  `time_change`,
		  `time_execut`,
		  `work_duration`,
		  `requestcount`,
		  `external_id`
		   
		FROM `toir_request`
		WHERE `id_request` = $id";
	
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($job_type, $client, $outlet, $region, $sity, $address, $contact_phone, $contact_man, $ID_PSD,
	$client_number, $description, $outlet_type, $sales_man, $worker, $workshop,  $status,  $equipment,  $date_create,
			$date_execut, $work_duration, $requestcount, $external_id);
	while ($stmt->fetch()) {
	$data = array(
	//'id' => $id,
	'job_type' => $job_type,	
	'client' => $client,
	'outlet' => $outlet,
	'region' => $region,
	'sity' => $sity,
	'address' => $address,
	'contact_phone' => $contact_phone,
	'contact_man' => $contact_man,
	'ID_PSD' => $ID_PSD,
	'client_number' => $client_number,
	'description' => $description,
	'outlet_type' => $outlet_type,
	'sales_man' => $sales_man,
	'worker' => $worker,
	'workshop' => $workshop,			
	'status' => $status,	
	'equipment' => $equipment,
	'date_create' => $date_create,
	'plTO' => 0,
	'date_execut' => $date_execut,
	'work_duration' => $work_duration,
	'requestcount' => $requestcount,
	'external_id' => $external_id				
			);
		}
	$stmt->close();
	echo json_encode($data);
	
}

/* SELECT  t.`Distance` FROM `toir_trip_request` tr
INNER JOIN `toir_trip` t ON tr.`trip_id`=t.`id`
WHERE tr.`request_id`=81 */

if ($_REQUEST['func'] == 'isLocked') {
	//$numRows = 0;
	$id_request = $_REQUEST['id_request'];
	$query = "SELECT t.`Closed` FROM `toir_trip_request`  tr
			INNER JOIN `toir_trip` t ON t.id=tr.`trip_id`
			WHERE tr.`request_id`=$id_request";
	//echo $query; exit;
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($Closed);
	$stmt->store_result();
	$stmt->fetch();
	$numRows = $stmt->num_rows;	
	if ($numRows) {
		if ($Closed) {
			echo 0;
		} else {
			echo 1;
		}
	}  else {		 
		echo 0;
	}	
}

if ($_REQUEST['func'] == 'closeRoute') {
	$arrOfTripId = array();
	$query = "SELECT DISTINCT t.id  FROM `toir_trip` t
			INNER JOIN `toir_trip_request` tr ON tr.`trip_id`=t.id
			INNER JOIN `toir_request` r ON tr.request_id=r.`id_request`
			WHERE t.`Closed` = 0";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($trip_id);
	while($stmt->fetch()) {
		$arrOfTripId[] = $trip_id;
	}
	$stmt->close();	
	foreach ($arrOfTripId as $trip_id) {
		$query = "SELECT COUNT(r.`status`) FROM `toir_trip_request` tr
				INNER JOIN `toir_request` r ON r.`id_request`=tr.`request_id` AND r.status =1
				WHERE tr.`trip_id`=$trip_id";	
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($countClosedRequest);
		$stmt->fetch();
		$stmt->close();
		
		$query = "SELECT COUNT(id) FROM `toir_trip_request` WHERE `trip_id`=$trip_id";	
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($allRequestInTrip);
		$stmt->fetch();
		$stmt->close();
		
		if (($countClosedRequest == $allRequestInTrip) && ($allRequestInTrip !=0 && $countClosedRequest != 0)) {
			$query = "UPDATE `toir_trip` SET `Closed`=1 WHERE id=$trip_id";
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();
		}
	}
}

if ($_REQUEST['func'] == 'showJobInfo') {
	$request_id = $_REQUEST['request_id'];
	$query = "SELECT 
		  ji.`id_request`, 
		  rj.`name_result_job`, 
		  ji.`comment`, 
		  ji.`possible_fault`, 
		  ji.`conclution_master`, 
		  ji.`id_preventive` 
		FROM `toir_job_information` ji 
		INNER JOIN `result_job` rj ON rj.`id_result_job`=ji.`id_result_job` 
		WHERE ji.`id_request` = $request_id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id_request, $id_result_job, $comment, $possible_fault, $conclution_master, $id_preventive);
	$stmt->fetch();
	$stmt->close();
	if (!$id_request) {
		$id_request = null;
	}
	$data = array(
		'id_request' => $id_request,
		'id_result_job' => $id_result_job,
		'comment' => $comment,
		'possible_fault' => $possible_fault,
		'conclution_master' => $conclution_master,
		'id_preventive' => $id_preventive			
	);
	echo'{rows:'.json_encode($data).'}';
}

if ($_REQUEST['func'] == 'showQualityChecking') {
	$request_id = $_REQUEST['request_id'];

	$query = "SELECT 
					  COUNT(`answer1`)
					FROM `toir_questionary_answers`
					WHERE `id_request` = ".$request_id.";";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($cnt);			
	$stmt->fetch();
	$stmt->close();
	if($cnt > 0)
	{
			$query = "SELECT 
			  qa.`id_request`, 
			  qs.`title`,
			  qs.`question`, 
			  qa.`answer1`, 
			  qa.`answer2`,
			  qs.`type1`,
			  qs.`type2`
			FROM `toir_questionary` qs
			LEFT JOIN `toir_questionary_answers` qa ON qa.`id_questionary`=qs.`id`  AND qa.`id_request`=".$request_id."
			ORDER BY  qs.`id` ASC;";
			//echo $query; exit();
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($id_request, $title, $question, $answer1, $answer2, $type1, $type2);
		while($stmt->fetch())
		{
			if($type1 == 'foto')
			{
				if($type2 == 'foto')
				{
					if($answer1 != '')
					{
						if($answer2 != '')
						{
							$data[] = array(
								'id_request' => $id_request,
								'title' => $title,
								'question' => $question,
								'answer1' => '<a class = "center-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer1.'</a>',
								'answer2' => '<a class = "center-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer2.'">'.$answer2.'</a>'
							);
						}else{
							$data[] = array(
								'id_request' => $id_request,
								'title' => $title,
								'question' => $question,
								'answer1' => '<a class = "center-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer1.'</a>',
								'answer2' => '<a class = "hide-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer2.'</a>'
							);
						}
					}else
					{
						if($answer2 != '')
						{
							$data[] = array(
								'id_request' => $id_request,
								'title' => $title,
								'question' => $question,
								'answer1' => '<a class = "hide-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer1.'</a>',
								'answer2' => '<a class = "center-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer2.'</a>'
							);
						}else{
							$data[] = array(
								'id_request' => $id_request,
								'title' => $title,
								'question' => $question,
								'answer1' => '<a class = "hide-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer1.'</a>',
								'answer2' => '<a class = "hide-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer2.'</a>'
							);
						}
					}
					
				}else
				{
					if($answer1 != '')
					{
						$data[] = array(
							'id_request' => $id_request,
							'title' => $title,
							'question' => $question,
							'answer1' => '<a class = "center-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer1.'</a>',
							'answer2' => $answer2
						);
					}else{
						$data[] = array(
							'id_request' => $id_request,
							'title' => $title,
							'question' => $question,
							'answer1' => '<a class = "hide-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer1.'">'.$answer1.'</a>',
							'answer2' => $answer2
						);
					}
					
				}
				
			}else{
				
				if($type1 == 'checkbox' && $answer1 != NULL){$answer1 = '+';}
				if($answer1 === NULL){$answer1 = '-';}
				if($type2 == 'foto')
				{
					if($answer2 != '')
					{
						$data[] = array(
							'id_request' => $id_request,
							'title' => $title,
							'question' => $question,
							'answer1' => $answer1,
							'answer2' => '<a class = "center-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer2.'">'.$answer2.'</a>'
						);
					}else{
						$data[] = array(
							'id_request' => $id_request,
							'title' => $title,
							'question' => $question,
							'answer1' => $answer1,
							'answer2' => '<a class = "hide-icon" target = "_blank" href="'.$IMAGE_PATH2.$answer2.'">'.$answer2.'</a>'
						);
					}
					
				}else
				{
					$data[] = array(
						'id_request' => $id_request,
						'title' => $title,
						'question' => $question,
						'answer1' => $answer1,
						'answer2' => $answer2
					);
				}
			}
		}
		$stmt->close();
		if (!$id_request) {
			$id_request = null;
		}
		
		echo'{rows:'.json_encode($data).'}';
	}else{
		echo'{rows:[]}';
	}
	
	
}

if ($_REQUEST['func'] == 'showConfirmation') {
	//IMAGE_PATH
	$request_id = $_REQUEST['request_id'];
	$query = "SELECT 
			  `id_request`, 
			  `name_client`, 
			  `post_client`, 
			  `signature_client`, 
			  `signature_master`
			FROM `confirmation`  
			WHERE `id_request`=$request_id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id_request, $name_client, $post_client, $signature_client, $signature_master);
	$stmt->fetch();
	$stmt->close();
	if (!$id_request) {
		$id_request = null;
	}
	if ($signature_client) {
		$signature_client = $IMAGE_PATH . $signature_client;
	} 
	if ($signature_master) {
		$signature_master = $IMAGE_PATH . $signature_master;
	}
	
	$data = array(
		'id_request' => $id_request,
		'name_client' => $name_client,
		'post_client' => $post_client,
		'signature_client' => $signature_client,
		'signature_master' => $signature_master,
		'foto' => $IMAGE_PATH . $foto,
		'signature_client_img' => "<img src='$signature_client' width='100' height='70'>",
		'signature_master_img' => "<img src='$signature_master' width='100' height='70'>",
		'foto_img' => ''	
							
	);
	echo'{rows:'.json_encode($data).'}';
}


if ($_REQUEST['func'] == 'showInfoEquipment') {
	$request_id = $_REQUEST['request_id'];
	$query = "SELECT e.`id_equipment`, e.`count`, e.`actual_number`, e.`actual_model`, e.`changeTime`, e.`model` FROM `equipment` e
		INNER JOIN `toir_request` r ON r.`id_request`=$request_id AND r.`id_equipment`=e.`id_equipment`";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id_equipment, $count, $actual_number, $actual_model, $changeTime, $model);
	$stmt->fetch();
	$stmt->close();
	$data = array(
		'id_equipment'	=> $id_equipment,
		'count' => $count,
		'actual_number' => $actual_number,
		'actual_model' => $actual_model,
		'changeTime' => $changeTime,
		'model' => $model		
	);
	echo'{rows:'.json_encode($data).'}';
}

if ($_REQUEST['func'] == 'showInfoUsedSpares') {
	$request_id = $_REQUEST['request_id'];
	$query = "SELECT
			  `id`,
			  `request_id`,
			  `spares_group`,
			  `name`,
			  `article`,
			  `count`,
			  `insert_time`,
			  `mobile_time_change`
			FROM `toir_used_spares` 
			WHERE `request_id`=$request_id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id, $request_id, $spares_group, $name, $article, $count, $insert_time,  $mobile_time_change);
	while ($stmt->fetch()) {
		$data[] = array(
			'id' => $id,
			'request_id' => $request_id,
			'spares_group' => $spares_group,
			'name' => $name,
			'article' => $article,
			'count' => $count,
			'insert_time' => $insert_time,
			'mobile_time_change' => $mobile_time_change
				
		);
	}
	$stmt->close();
	echo'{rows:'.json_encode($data).'}';
}

if ($_REQUEST['func'] == 'showFoto') {
	//$IMAGE_PATH
	$arrOfImagePath = array();
	$request_id = $_REQUEST['request_id'];
	$query = "SELECT `foto` FROM `confirmation` WHERE `id_request` = $request_id";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($foto);
	$stmt->fetch();
	$stmt->close();
	if ($foto) {
		$arrOfImagePath = explode(',', $foto);
		$image_files_string = '';
		$src = '"src":';
		foreach ($arrOfImagePath as $value) {
			$image_files_string .= "{".$src."'".$IMAGE_PATH.$value."'},";
		}
		echo  '{rows:['.$image_files_string.']}';
	} 
	
}

if (isset($_REQUEST['removeRequest'])) {
	$query = "SELECT `id_request`, `name_master` FROM `toir_request`  WHERE `status`=0 
			AND DATE(`time_execut`)<DATE(NOW()) AND DATE(`time_execut`)>DATE(SUBDATE(NOW(),7))";
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id_request, $name_master);
	while($stmt->fetch()) {
		if ($name_master == 0) {
			//no trip
			$queryUpdate = "UPDATE `toir_request` SET `time_execut`=CONCAT(DATE(NOW()),' 00:00:00') WHERE `id_request`=$id_request";
			if (!$db1->query($queryUpdate)) {
				echo 'UPDATE Error '.$queryUpdate;
			}			
		} else {
			//we have trip
			$queryDelete = "DELETE FROM `toir_trip_request` WHERE request_id=$id_request";
			if (!$db1->query($queryDelete)) {
				echo 'DELETE Error '.$queryDelete;
			}			
			
			$queryUpdate = "UPDATE `toir_request` SET `time_execut`=CONCAT(DATE(NOW()),' 00:00:00'),
			`place`=0, `name_master`=0, time_beginning_work=null WHERE `id_request`=$id_request";
			if (!$db1->query($queryUpdate)) {
				echo 'UPDATE Error '.$queryUpdate;
			}			
		}
	}
}


if(isset($_REQUEST['show_typeeq'])){
	
	$query = "SELECT et.`service` FROM `equipment` e
INNER JOIN `equipment_type` et ON et.`id` = e.`typeTO`
INNER JOIN `equipment_type_name` en ON e.`maker` = en.`id`
WHERE e.`id_equipment` = ".$_REQUEST['show_typeeq']."";
	if($stmt = $db->prepare($query)){
	$stmt->execute();
	$stmt->bind_result($service);
	$stmt->fetch();
	$stmt->close();
	echo '{"success":true,"message":"'.$service.'"}';
	}
	$db->close();
	exit;
}


if(isset($_REQUEST['showNextTypeTO'])){
	
	$type=$_REQUEST['type'];
	//$query = "SELECT `id`,`service` FROM `equipment_type` WHERE `type_id` = ".$_REQUEST['showNextTypeTO']";";
	$query = "SELECT `id`,`service` FROM `equipment_type` WHERE `type_id` = (SELECT `type_id` FROM `equipment_type` WHERE `id` = ".$type.");";
	//echo $query; exit;
	if($stmt = $db->prepare($query)){
	$stmt->execute();
	$stmt->bind_result($id,$service);
	while ($stmt->fetch()) {
		$data[] = array(
			'id' => $id,
			'name' => $service
		);
	}
	$stmt->close();
	}
	$db->close();
	echo '{rows:'.json_encode($data).'}';
	exit;
	
}
	
if(isset($_REQUEST['print'])){
	
	$id=$_REQUEST['id'];
	
	$query = "SELECT 
r.`region`,r.`city`,r.`address`,r.`contact_name`,r.`contact_number`,r.`mobile_time_change`,r.`fact_work_duration`,r.`work_duration`,r.note, r.time_beginning_work,
t.`Distance`,t.`StartTime`,t.`Comments`,
tj.`name_job`,
u.name,
c.`client`,c.`city`,c.`region`,c.`address`,c.`contact_number`,c.`contact_name`,
o.`name`,o.`city`,o.`region`,o.`address`,o.`contact_number`,o.`contact_name`,
e.`inventory_number`,e.`serial_number`,e.model,e.count, e.type,
cf.`name_client`,cf.`post_client`,cf.`signature_client`,cf.`signature_master`,
rj.`name_result_job`,ji.`comment`,ji.`possible_fault`,ji.`conclution_master`
FROM `toir_request` r
LEFT JOIN `toir_trip_request` tr ON tr.`request_id`=r.`id_request`
LEFT JOIN `toir_trip` t ON t.`id` = tr.`trip_id`
LEFT JOIN `type_job` tj ON tj.id_type_job= r.`id_type_job`
LEFT JOIN `toir_users` u ON u.`id` = r.`name_master`
LEFT JOIN `client` c ON c.id_client = r.`client_id`
LEFT JOIN `toir_outlets` o ON o.`id_outlet`=r.`outlet_id`
LEFT JOIN `equipment` e ON e.`id_equipment`=r.`id_equipment`
LEFT JOIN `confirmation` cf ON cf.`id_request` = r.`id_request`
LEFT JOIN `toir_job_information` ji ON ji.`id_request` = r.`id_request`
LEFT JOIN `result_job` rj ON rj.`id_result_job` = ji.`id_result_job`
WHERE r.`id_request` = ".$id.";";
	if($stmt = $db->prepare($query)){
	$stmt->execute();
	$stmt->bind_result(
		$r_region, $r_city, $r_address, $r_contact_name,$r_contact_number,$r_mobile_time_change,$r_fact_work_duration,$r_work_duration,$r_note, $time_beginning_work,
		$t_Distance,$t_StartTime,$t_Comments,
		$tj_name_job,
		$u_name,
		$c_client,$c_city,$c_region,$c_address,$c_contact_number,$c_contact_name,
		$o_name,$o_city,$o_region,$o_address,$o_contact_number,$o_contact_name,
		$e_inventory_number,$e_serial_number,$e_model,$e_count, $e_type,
		$cf_name_client,$cf_post_client,$cf_signature_client,$cf_signature_master,
		$rj_name_result_job,$ji_comment,$ji_possible_fault,$ji_conclution_master
	);
	while ($stmt->fetch()) {
		//$r_end_work_UNIX = (int)($r_work_duration*60) + strtotime($r_mobile_time_change);
		//$r_end_work = date('Y-m-d h:i:s', $r_end_work_UNIX);
		//$r_end_work = date('d-m-Y h:i:s', $r_mobile_time_change);
		
		$t_start_time = date('h:i:s', strtotime($t_StartTime));
		$t_start_day = date('m-d-Y',  strtotime($t_StartTime));
		
		$r_end_work = date('d-m-Y h:i:s', strtotime($r_mobile_time_change));
		$r_start_work = strtotime($r_mobile_time_change) - (int)($r_fact_work_duration*60);
		$r_start_work = date('d-m-Y h:i:s', $r_start_work);
		
$path1 = $IMAGE_PATH2.$cf_signature_client;
$type1 = pathinfo($path1, PATHINFO_EXTENSION);
$data1 = file_get_contents($path1);
if($data1){
$base641 = 'data:image/' . $type1 . ';base64,' . base64_encode($data1);
}
$path2 = $IMAGE_PATH2.$cf_signature_master;
$type2 = pathinfo($path2, PATHINFO_EXTENSION);
$data1 = file_get_contents($path2);
if($data1){
$base642 = 'data:image/' . $type2 . ';base64,' . base64_encode($data2);
}	
		$data = array(
			'r_id'=>$id,
			'r_region'=>$r_region,'r_city'=>$r_city,'r_address'=>$r_address,'r_contact_name'=>$r_contact_name,'r_contact_number'=>$r_contact_number,'r_mobile_time_change'=>$r_mobile_time_change,'r_work_duration'=>$r_work_duration,'r_note'=>$r_note, 'time_beginning_work' => $time_beginning_work,
			't_Distance'=>$t_Distance,'t_StartTime'=>$t_StartTime,'t_Comments'=>$t_Comments,
			'tj_name_job'=>$tj_name_job,
			'u_name'=>$u_name,
			'c_client'=>$c_client,'c_city'=>$c_city,'c_region'=>$c_region,'c_address'=>$c_address,'c_contact_number'=>$c_contact_number,'c_contact_name'=>$c_contact_name,
			'o_name'=>$o_name,'o_city'=>$o_city,'o_region'=>$o_region,'o_address'=>$o_address,'o_contact_number'=>$o_contact_number,'o_contact_name'=>$o_contact_name,
			'e_inventory_number'=>$e_inventory_number,'e_serial_number' => $e_serial_number,'e_model'=> $e_model, 'e_count' => $e_count, 'e_type' => $e_type,
			'cf_name_client'=>$cf_name_client,'cf_post_client'=> $cf_post_client,'cf_signature_client'=>$cf_signature_client,'cf_signature_master'=> $cf_signature_master,
			'rj_name_result_job'=>$rj_name_result_job,'ji_comment'=> $ji_comment,'ji_possible_fault' => $ji_possible_fault,'ji_conclution_master'=> $ji_conclution_master,
			'r_start_work' => $r_start_work, 'r_end_work' => $r_end_work, 't_start_time' => $t_start_time, 't_start_day' => $t_start_day,
			'IMAGE_PATH' => $IMAGE_PATH2,
			'IMAGE641' => $base641,
			'IMAGE642' => $base641
		);
	}
	$stmt->close();
	}
	
	$query = "SELECT
	 `spares_group`, `name`, `article`, `count`, `insert_time`, `mobile_time_change`, `spareRef`
	 FROM `toir_used_spares`
	 WHERE `request_id` =".$id.";";
    if($stmt = $db->prepare($query)){
        $stmt->execute();
        $stmt->bind_result($spares_group, $name, $article, $count, $insert_time, $mobile_time_change, $spareRef);
         while ($stmt->fetch()) {
            $data['toir_used_spares'][] = array(
                'spares_group' => $spares_group,
                'name' => $name,
                'article' => $article,
                'count' => $count,
                'insert_time' => $insert_time,
                'mobile_time_change' => $mobile_time_change,
                'spareRef' => $spareRef
             );
        }
        $stmt->close();
    }
    foreach($data as $value){
        if (is_null($value)) {
            $value = '';
        }
    }
	$db->close();
	echo json_encode($data);
	exit;
	
}

if(isset($_REQUEST['docx']))
{
	$request_report = "../files/request_report.docx";
	$request_report_foto = "../files/request_report_foto.docx";
	$request_report_zip = "../files/request_report.zip";

	$id=$_REQUEST['id'];
	
	$query = "SELECT 
					r.`region`,
					r.`city`,
					r.`address`,
					r.`contact_name`,
					r.`contact_number`,
					r.`mobile_time_change`,
					r.`fact_work_duration`,
					r.`work_duration`,
					r.`note`, 
					r.`time_beginning_work`,
					t.`Distance`,
					t.`StartTime`,
					t.`Comments`,
					tj.`name_job`,
					u.`name`,
					c.`client`,
					c.`city`,
					c.`region`,
					c.`address`,
					c.`contact_number`,
					c.`contact_name`,
					o.`name`,
					o.`city`,
					o.`region`,
					o.`address`,
					o.`contact_number`,
					o.`contact_name`,
					e.`inventory_number`,
					e.`serial_number`,
					e.`model`,
					e.`count`, 
					e.`type`,
					cf.`name_client`,
					cf.`post_client`,
					cf.`signature_client`,
					cf.`signature_master`,
					cf.`foto`,
					rj.`name_result_job`,
					ji.`comment`,
					ji.`possible_fault`,
					ji.`conclution_master`,
					e.`typeTO`
				FROM `toir_request` r
				LEFT JOIN `toir_trip_request` tr ON tr.`request_id`=r.`id_request`
				LEFT JOIN `toir_trip` t ON t.`id` = tr.`trip_id`
				LEFT JOIN `type_job` tj ON tj.id_type_job= r.`id_type_job`
				LEFT JOIN `toir_users` u ON u.`id` = r.`name_master`
				LEFT JOIN `client` c ON c.id_client = r.`client_id`
				LEFT JOIN `toir_outlets` o ON o.`id_outlet`=r.`outlet_id`
				LEFT JOIN `equipment` e ON e.`id_equipment`=r.`id_equipment`
				LEFT JOIN `confirmation` cf ON cf.`id_request` = r.`id_request`
				LEFT JOIN `toir_job_information` ji ON ji.`id_request` = r.`id_request`
				LEFT JOIN `result_job` rj ON rj.`id_result_job` = ji.`id_result_job`
				WHERE r.`id_request` = ".$id.";";
				//echo $query; exit;
	if($stmt = $db->prepare($query))
	{
		$stmt->execute();
		$stmt->bind_result(
			$r_region, $r_city, $r_address, $r_contact_name,$r_contact_number,$r_mobile_time_change,$r_fact_work_duration,$r_work_duration,$r_note, $time_beginning_work,
			$t_Distance,$t_StartTime,$t_Comments,
			$tj_name_job,
			$u_name,
			$c_client,$c_city,$c_region,$c_address,$c_contact_number,$c_contact_name,
			$o_name,$o_city,$o_region,$o_address,$o_contact_number,$o_contact_name,
			$e_inventory_number,$e_serial_number,$e_model,$e_count, $e_type,
			$cf_name_client,$cf_post_client,$cf_signature_client,$cf_signature_master,$cf_foto,
			$rj_name_result_job,$ji_comment,$ji_possible_fault,$ji_conclution_master, $typeTO
		);
	while ($stmt->fetch()) 
	{
		
		$t_start_time = date('h:i:s', strtotime($t_StartTime));
		$t_start_day = date('m-d-Y',  strtotime($t_StartTime));
		
		$r_end_work = date('d-m-Y h:i:s', strtotime($r_mobile_time_change));
		$r_start_work = strtotime($r_mobile_time_change) - (int)($r_fact_work_duration*60);
		$r_start_work = date('d-m-Y h:i:s', $r_start_work);
		
		$path1 = $IMAGE_PATH2.$cf_signature_client;
		$type1 = pathinfo($path1, PATHINFO_EXTENSION);
		$data1 = file_get_contents($path1);
		if($data1)
		{
			$base641 = 'data:image/' . $type1 . ';base64,' . base64_encode($data1);
		}
		$path2 = $IMAGE_PATH2.$cf_signature_master;
		$type2 = pathinfo($path2, PATHINFO_EXTENSION);
		$data1 = file_get_contents($path2);
		if($data1)
		{
			$base642 = 'data:image/' . $type2 . ';base64,' . base64_encode($data2);
		}

	}
	$stmt->close();
	}
	$toir_used_spares = '';
	$value25 = array();
	$value26 = array();
	$value27 = array();
	$value28 = array();
	$value29 = array();

	$query = "SELECT
	 `spares_group`, `name`, `article`, `count`, `insert_time`, `mobile_time_change`, `spareRef`
	 FROM `toir_used_spares`
	 WHERE `request_id` =".$id.";";
    if($stmt = $db->prepare($query))
    {
        $stmt->execute();
        $stmt->bind_result($spares_group, $name, $article, $count, $insert_time, $mobile_time_change, $spareRef);
        while ($stmt->fetch()) 
        {
            //$toir_used_spares .= '<tr><td colspan="6">'.$name.'</td><td colspan="2">'.$article.'</td><td>'.$count.'</td></tr>';
            //цикл
            $value25[] = array('#Value25#'=>$name);
            $value26[] = array('#Value26#'=>$article);
            $value27[] = array('#Value27#'=>$count);
            //echo $name;
        }
        $stmt->close();
    }

    if(empty($value25))
    {
    	$value25[] = array('#Value25#'=>'-');
    	$value26[] = array('#Value26#'=>'-');
    	$value27[] = array('#Value27#'=>'-');
    }

    $query = "SELECT igru.`count`,igr.`name`
				 FROM `toir_used_ingredients` igru
				 LEFT JOIN `toir_ingredients` igr
				 ON igru.`id_ingredient` = igr.`id`
				 WHERE igru.`request_id` =".$id.";";
	//echo $query;exit();
	$total_ingredients = 0;
	
    if($stmt = $db->prepare($query))
    {
        $stmt->execute();
        $stmt->bind_result($count_ingr, $name_ingr);
        while ($stmt->fetch()) 
        {
            //$toir_used_spares .= '<tr><td colspan="6">'.$name.'</td><td colspan="2">'.$article.'</td><td>'.$count.'</td></tr>';
            //цикл
            $value28[] = array('#Value28#'=>$name_ingr);
            $value29[] = array('#Value29#'=>$count_ingr);
            $total_ingredients += $count_ingr;
        }
        $stmt->close();
    }
    if(empty($value28))
    {
    	$value28[] = array('#Value28#'=>'-');
    	$value29[] = array('#Value29#'=>'-');
    }
    //var_dump($value25);
    foreach($data as $value){
        if (is_null($value)) {
            $value = '';
        }
    }
	$db->close();
	/*
	if($toir_used_spares != '')
	{
		
	}else{
		
	}*/
//if($e_type == ''){$e_type = '1';}
$address_new = "$o_name $o_region, $o_city, $o_address,  $r_contact_number $r_contact_name";
//echo $address_new; exit();
//echo $c_client; exit();
//$PHPWord = new PHPWord();
$phpdocx = new phpdocx("../files/Template.docx");

//$document = $PHPWord->loadTemplate('../files/Template.docx');
$phpdocx->assign("#Value1#",$c_client);
$phpdocx->assign("#Value2#",$address_new);
$phpdocx->assign("#Value3#",$e_type);
$phpdocx->assign("#Value4#",$e_model);
$phpdocx->assign("#Value5#",$e_inventory_number);
$phpdocx->assign("#Value6#",$e_serial_number);
$phpdocx->assign("#Value7#",$e_count);
$phpdocx->assign("#Value8#",$t_start_day);
$phpdocx->assign("#Value9#",$t_start_time);
$phpdocx->assign("#Value10#",$cf_name_client);
$phpdocx->assign("#Value11#",$cf_post_client);
$phpdocx->assign("#Value12#",$tj_name_job);
$phpdocx->assign("#Value13#",$u_name);
$phpdocx->assign("#Value14#",$r_start_work);
$phpdocx->assign("#Value15#",$r_end_work);
$phpdocx->assign("#Value16#",$t_Distance);
$phpdocx->assign("#Value17#",$ji_comment);
$phpdocx->assign("#Value18#",$ji_possible_fault);
$phpdocx->assign("#Value19#",$ji_conclution_master);
$phpdocx->assign("#Value20#",$ji_comment);
$phpdocx->assign("#Value21#",21);
$phpdocx->assign("#Value22#",$u_name);
$phpdocx->assign("#Value23#",$cf_post_client);
$phpdocx->assign("#Value24#",$cf_name_client);
$phpdocx->assign("#Value30#",$total_ingredients);
$phpdocx->assign("#Value31#",$typeTO);
$phpdocx->assign("#Value32#",$id);

$phpdocx->assignBlock("spares",array(array("#NAME#"=>"Найменування","#ARTICLE#"=>"Артикул","#COUNT#"=>"Кількість")));
$phpdocx->assignBlock("ingredients",array(array("#INGREDIENTS#"=>"Витрачено порцій/інгредієнтів напоїв при проведенні ремонтних робіт:")));
$phpdocx->assignNestedBlock("value25",$value25,array("spares"=>1));
$phpdocx->assignNestedBlock("value26",$value26,array("spares"=>1));
$phpdocx->assignNestedBlock("value27",$value27,array("spares"=>1));

$phpdocx->assignNestedBlock("value28",$value28,array("ingredients"=>1));
$phpdocx->assignNestedBlock("value29",$value29,array("ingredients"=>1));

if(!is_dir($IMAGE_PATH2.'tmp'))
{
	mkdir($IMAGE_PATH2.'tmp');
}
copy($IMAGE_PATH2.$cf_signature_master, $IMAGE_PATH2.'tmp/'.$cf_signature_master);
copy($IMAGE_PATH2.$cf_signature_client, $IMAGE_PATH2.'tmp/'.$cf_signature_client);
rename($IMAGE_PATH2.'tmp/'.$cf_signature_master, $IMAGE_PATH2.'tmp/image2.jpeg');
rename($IMAGE_PATH2.'tmp/'.$cf_signature_client, $IMAGE_PATH2.'tmp/image3.jpeg');
$phpdocx->addImage("image3.jpeg",$IMAGE_PATH2.'tmp/image3.jpeg');
$phpdocx->addImage("image2.jpeg",$IMAGE_PATH2.'tmp/image2.jpeg');

if(file_exists($request_report))
{
	unlink($request_report);
}
$phpdocx->save($request_report);

unlink($IMAGE_PATH2.'tmp/image2.jpeg');
unlink($IMAGE_PATH2.'tmp/image3.jpeg');
//$document->save('../files/Solarsystem.docx');

$fotos = explode(',', $cf_foto);
//echo $cf_foto;exit();

$PHPWord2 = new PHPWord();
// New portrait section
$section = $PHPWord2->createSection();
//var_dump($fotos);exit();
for ($i=0, $k = count($fotos); $i < $k; $i++) 
{ 
	//echo $IMAGE_PATH2.$fotos[$i];
	$section->addImage($IMAGE_PATH2.$fotos[$i], array('width'=>550, 'height'=>413, 'align'=>'center'));
	$section->addTextBreak(2);
}

$objWriter = PHPWord_IOFactory::createWriter($PHPWord2, 'Word2007');

if(file_exists($request_report_foto))
{
	unlink($request_report_foto);
}
$objWriter->save($request_report_foto);
if(file_exists($request_report_zip))
{
	unlink($request_report_zip);
}
	$zip = new ZipArchive();
	$zip -> open($request_report_zip, ZIPARCHIVE::CREATE);
	$zip -> addFile($request_report_foto, "request_report_foto.docx");
	$zip -> addFile($request_report, "request_report.docx");
	$zip -> close();

	echo '{"success":true"}';
}

if ($_GET['func']=='SaveReportZip')
{

    $File = "../files/request_report.zip";

    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 ($_POST['func'] == 'export_xls')
{
        
        require_once '../classes/PHPExcel.php';
        include_once '../classes/PHPExcel/IOFactory.php';
		//echo $month_1;exit;
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
		$objPHPExcel = $objReader->load("../files/templates/template.xls");
		$objDrawing = new PHPExcel_Worksheet_Drawing();
		$objDrawing2 = new PHPExcel_Worksheet_Drawing();
		
			$id=$_REQUEST['id'];
	
			$query = "SELECT 
							r.`contact_name`,
							r.`contact_number`,
							r.`mobile_time_change`,
							r.`fact_work_duration`,
							t.`Distance`,
							u.`name`,
							c.`client`,
							o.`name`,
							o.`city`,
							o.`region`,
							o.`address`,
							o.`contact_number`,
							o.`contact_name`,
							e.`inventory_number`,
							e.`serial_number`,
							e.`model`,
							e.`count`, 
							cf.`name_client`,
							cf.`post_client`,
							cf.`signature_client`,
							cf.`signature_master`,
							ji.`comment`
						FROM `toir_request` r
						LEFT JOIN `toir_trip_request` tr ON tr.`request_id`=r.`id_request`
						LEFT JOIN `toir_trip` t ON t.`id` = tr.`trip_id`
						LEFT JOIN `toir_users` u ON u.`id` = r.`name_master`
						LEFT JOIN `client` c ON c.id_client = r.`client_id`
						LEFT JOIN `toir_outlets` o ON o.`id_outlet`=r.`outlet_id`
						LEFT JOIN `equipment` e ON e.`id_equipment`=r.`id_equipment`
						LEFT JOIN `confirmation` cf ON cf.`id_request` = r.`id_request`
						LEFT JOIN `toir_job_information` ji ON ji.`id_request` = r.`id_request`
						WHERE r.`id_request` = ".$id.";";
						//echo $query; exit;
			if($stmt = $db->prepare($query))
			{
				$stmt->execute();
				$stmt->bind_result(
									$r_contact_name,
									$r_contact_number,
									$r_mobile_time_change,
									$r_fact_work_duration,
									$t_Distance,
									$u_name,
									$c_client,
									$o_name,
									$o_city,
									$o_region,
									$o_address,
									$o_contact_number,
									$o_contact_name,
									$e_inventory_number,
									$e_serial_number,
									$e_model,
									$e_count,
									$cf_name_client,
									$cf_post_client,
									$cf_signature_client,
									$cf_signature_master,
									$ji_comment
								);
				while ($stmt->fetch()) 
				{

					$r_end_work = date('d-m-Y h:i:s', strtotime($r_mobile_time_change));
					$r_start_work = strtotime($r_mobile_time_change) - (int)($r_fact_work_duration*60);
					$r_start_work = date('d-m-Y h:i:s', $r_start_work);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,2,'Замовник: '.$c_client);

			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,3,'Адреса Замовника: '.$o_name.' '.$o_region.', '.$o_city.', '.$o_address.', '.$r_contact_number.' '.$r_contact_name);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,4,'Модель кавомашини: '.$e_model.' '.$e_inventory_number);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,5,'Серійний номер: '.$e_serial_number);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,6,'Поточні показники лічильника кавових напоїв: '.$e_count);

			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,2,'ПІБ сервісного інженера: '.$u_name);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,3,'Дата та час початку робіт: '.$r_start_work);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,4,'Дата та час завершення робіт: '.$r_end_work);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,5,'Транспорт: '.$t_Distance.' км.');

			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,36,'Посада: ');
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,37,'ПІБ: '.$u_name);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,36,'Посада: '.$cf_post_client);
			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,37,'ПІБ: '.$cf_name_client);

			        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,29,'ПІБ: '.$ji_comment);


					$objDrawing->setName('Signature client');
					$objDrawing->setDescription('Signature client');
					$objDrawing->setPath("ExtJsGallery\images\\".$cf_signature_client);
					$objDrawing->setHeight(150);
					$objDrawing->setWidth(150);
					$objDrawing->setCoordinates('A40');
					$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

					$objDrawing2->setName('Signature master');
					$objDrawing2->setDescription('Signature master');
					$objDrawing2->setPath($_SERVER['DOCUMENT_ROOT']."/monit2_task_ne_test/app/ExtJsGallery/images/".$cf_signature_master);
					$objDrawing2->setHeight(150);
					$objDrawing2->setWidth(150);
					$objDrawing2->setCoordinates('E40');
					$objDrawing2->setWorksheet($objPHPExcel->getActiveSheet());
				}
				$stmt->close();
				//echo $c_client;exit();
				
		    }
		    $i = 1; $j = 12; $k = 12; $n = 12; $m = 12; $l = 12; $p = 12; $r = 12; $s = 12; $g = 2;
		    $coord_array = array('B' => 1,'C' => 2,'D' => 3,'E' => 4,'F' => 5,'G' => 6,'H' => 7,'I' => 8);
		    $query = "SELECT
							 qs.`category`, 
							 qs.`title`, 
							 qs.`question`, 
							 qs.`type1`, 
							 qs.`type2`, 
							 qsa.`answer1`, 
							 qsa.`answer2`
							 FROM `toir_questionary` qs
							 LEFT JOIN `toir_questionary_answers` qsa ON qsa.`id_questionary` = qs.`id` AND qsa.`id_request`=".$id."
							 ORDER BY  qs.`id` ASC;";
							 //echo $query;
		    if($stmt = $db->prepare($query))
		    {
		        $stmt->execute();
		        $stmt->bind_result($category, $title, $question, $type1, $type2, $answer1, $answer2);
		        while ($stmt->fetch()) 
		        {
		        	if($type1 == 'checkbox'){if($answer1 == 1){$answer1 = '+';}else{$answer1 = '-';}}
			        if($type1 == 'text'){if($answer1 === NULL){$answer1 = '-';}}
		            switch ($category) {
		            	case 1:
				            	if($type1 != 'foto')
				            	{
				            		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, 8, $answer1);
				            	}
				            	if($type2 == 'foto')
				            	{
				            		$coord = array_search($i, $coord_array);
				            		if($answer2 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer2);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates($coord.'9');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            	}
				            	//echo $i;
				            	++$i;

		            		break;
		            	case 2:
		            			if($type1 != 'foto')
				            	{	
			            			if($j == 15)
			            			{
			            				$j++;
			            				$objPHPExcel->getActiveSheet()->setCellValue("B".$j, $answer1);
			            			}elseif($j == 20)
			            			{
			            				//$j++;
			            				//$j++;
			            				$j += 2;
			            				$objPHPExcel->getActiveSheet()->setCellValue("B".$j, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("B".$j, $answer1);
			            			}
			            			$j++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('B17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("B17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("B17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$j++;
				            	}
		            		break;
		            	case 3:
				            	if($type1 != 'foto')
				            	{	
			            			if($k == 15)
			            			{
			            				$k++;
			            				$objPHPExcel->getActiveSheet()->setCellValue("C".$k, $answer1);
			            			}elseif($k == 20)
			            			{
			            				$k += 2;
			            				$objPHPExcel->getActiveSheet()->setCellValue("C".$k, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("C".$k, $answer1);
			            			}
			            			$k++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('C17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("C17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("C17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$k++;
				            	}
		            		break;
		            	case 4:
				            	if($type1 != 'foto')
				            	{	
			            			if($n == 15)
			            			{
			            				$n++;
			            				$objPHPExcel->getActiveSheet()->setCellValue("D".$n, $answer1);
			            			}elseif($n == 20)
			            			{
			            				$n +=2; 
			            				$objPHPExcel->getActiveSheet()->setCellValue("D".$n, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("D".$n, $answer1);
			            			}
			            			$n++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('D17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("D17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("D17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$n++;
				            	}
		            		break;
		            	case 5:
				            	if($type1 != 'foto')
				            	{	
			            			if($m == 14)
			            			{
			            				$m += 2; 
			            				$objPHPExcel->getActiveSheet()->setCellValue("E".$m, $answer1);
			            			}elseif($m == 18)
			            			{
			            				$m += 4;
			            				$objPHPExcel->getActiveSheet()->setCellValue("E".$m, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("E".$m, $answer1);
			            			}
			            			$m++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('E17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("E17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("E17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$m++;
				            	}
		            		break;
		            	case 6:
				            	if($type1 != 'foto')
				            	{	
			            			if($l == 14)
			            			{
			            				$l++;
			            				$objPHPExcel->getActiveSheet()->setCellValue("F".$l, $answer1);
			            			}elseif($l == 18)
			            			{
			            				$l += 2;
			            				$objPHPExcel->getActiveSheet()->setCellValue("F".$l, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("F".$l, $answer1);
			            			}
			            			$l++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('F17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("F17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("F17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$l++;
				            	}
		            		break;
		            	case 7:
				            	if($type1 != 'foto')
				            	{	
			            			if($p == 14)
			            			{
			            				$p++;
			            				$objPHPExcel->getActiveSheet()->setCellValue("G".$p, $answer1);
			            			}elseif($p == 18)
			            			{
			            				$p += 2;  
			            				$objPHPExcel->getActiveSheet()->setCellValue("G".$p, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("G".$p, $answer1);
			            			}
			            			$p++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('G17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("G17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("G17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$p++;
				            	}
		            		break;
		            	case 8:
				            	if($type1 != 'foto')
				            	{	
			            			if($r == 14)
			            			{
			            				$r++;
			            				$objPHPExcel->getActiveSheet()->setCellValue("H".$r, $answer1);
			            			}elseif($r == 18)
			            			{
			            				$r += 2; 
			            				$objPHPExcel->getActiveSheet()->setCellValue("H".$r, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("H".$r, $answer1);
			            			}
			            			$r++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('H17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("H17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("H17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$r++;
				            	}
		            		break;
		            	case 9:
				            	if($type1 != 'foto')
				            	{	
			            			if($s == 14)
			            			{
			            				$s++;
			            				$objPHPExcel->getActiveSheet()->setCellValue("I".$s, $answer1);
			            			}elseif($s == 18)
			            			{
			            				$s += 2; 
			            				$objPHPExcel->getActiveSheet()->setCellValue("I".$s, $answer1);
			            			}else
			            			{
			            				$objPHPExcel->getActiveSheet()->setCellValue("I".$s, $answer1);
			            			}
			            			$s++;
				            		
				            	}else{
				            		if($answer1 != NULL)
				            		{
				            			$objDrawing = new PHPExcel_Worksheet_Drawing();
										$objDrawing->setPath("ExtJsGallery\images\\".$answer1);
										$objDrawing->setHeight(150);
										$objDrawing->setWidth(150);
										$objDrawing->setCoordinates('I17');
										$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
				            		}
				            		//$objPHPExcel->getActiveSheet()->setCellValue("I17", $answer1);
				            		//$objPHPExcel->getActiveSheet()->getCell("I17")->getHyperlink()->setUrl(strip_tags($IMAGE_PATH2.$answer1));
				            		$s++;
				            	}
		            		break;
		            	case 10:
				            	if($type1 == 'text')
				            	{	
			            			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($g, 32, $answer1);
				            	}
				            	$g++;
		            		break;
		            }
		        }
		        $stmt->close();
		    }

			$ObjWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            header('Content-Type: application/vnd.ms-excel Content-Type: text/html; charset=utf-8');
            if(file_exists("../files/Report.xls"))
            {
                unlink("../files/Report.xls");
            }
            $ObjWriter->save('../files/Report.xls');
}


if ($_GET['func']=='SaveXls')
{
   
    $File = ('../files/Report.xls');
    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;
    }
}