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

//set_time_limit(900);
ini_set('memory_limit', '32M');

include "settings.php";
include "logger.php";

$locale = $_SESSION['locale'];
$client_id = $_SESSION['client_id'];

include_once "../../classes/PHPExcel.php";
include_once "../../classes/PHPExcel/IOFactory.php";

//-- for vvtrack
//$dir = $_SERVER['DOCUMENT_ROOT']."/app/TOiR/files/";

//-- for test
$dir = $_SERVER['DOCUMENT_ROOT']."/monit2_task_ne_test/app/TOiR/files/";

if($locale == 'en'){
	include "../lang/TOiR/lang.en.php";
}elseif($locale == 'ru'){
	include "../lang/TOiR/lang.ru.php";
}elseif($locale == 'ua'){
	include "../lang/TOiR/lang.ua.php";
}

$db = 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(isset($_FILES['file'])){
    $file = $_FILES['file'];
	if ($file['size']!=0){
	
		//$allowedType = array('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel', 'application/excel', 'application/xml');
		//if(in_array($file['type'], $allowedType)){
		//$filename = $dir.$file['name'];
		$filename = $dir.$client_id.".".pathinfo($file['name'], PATHINFO_EXTENSION);
		writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload filename <> '.$filename);
		$namef = $file['name'];
		if( file_exists($filename) ){ unlink($filename); }
		$m = move_uploaded_file($file['tmp_name'], $filename);
        if($m!=false){
			
			
			if(file_exists($filename)){
	
	
// ----- XML equipment ----->
if($_REQUEST['typeequipment']==1){

				//do with file-->
				$xml = simplexml_load_file($filename);
				
				foreach ($xml->Кофемашина as $equipment) {
					$IsFolder = (string) $equipment->{'CatalogObject.Номенклатура'}->IsFolder;
					if ($IsFolder == 'false') {		
						$Description = (string) $equipment->{'CatalogObject.Номенклатура'}->Description;
						$SerialNumber = (string) $equipment->{'CatalogObject.Номенклатура'}->ЗаводськийНомер;
						$Code = (string) $equipment->{'CatalogObject.Номенклатура'}->Code;		
						$equipmentData[] = array(
							'Description' => $Description, //model
							'SerialNumber' => $SerialNumber, //serial_number
							'Code' => $Code //inventory_number	
						);
					}
					/*print_r($equipment);
					echo '<br /><br />';*/
				}
				
$count = 0;
foreach ($equipmentData as $equipment) {
	$query = "SELECT `id_equipment` FROM `equipment` WHERE `inventory_number`='$equipment[Code]'";	
	writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query1 <> '.$query);
	$stmt = $db->prepare($query);
	$stmt->execute();
	$stmt->bind_result($id_equipment);
	$stmt->fetch();
	$stmt->close();	
	if (!$id_equipment) {
		$query = "INSERT INTO `equipment` (`model`, `serial_number`, inventory_number) 
		VALUES ('$equipment[Description]', '$equipment[SerialNumber]', '$equipment[Code]')";
		//echo $query; exit;
		writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query2 <> '.$query);
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$lastInsertId = $stmt->insert_id;
		$stmt->close();
		$query = "INSERT INTO `toir_client_equipment` (`instanse_id`, `id_equipment`) VALUES ($client_id, $lastInsertId)";
		writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query3 <> '.$query);
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		$count++;
		}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query3 error <> '.$db->errno . ' ' . $db->error); }
		}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query3 error <> '.$db->errno . ' ' . $db->error); }
		}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query2 error <> '.$db->errno . ' ' . $db->error); }
		}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query2 error <> '.$db->errno . ' ' . $db->error); }
		
	}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml duplicate id_equipment <> '.$id_equipment); }
	unset($id_equipment);
}

if ($count) {
	//echo (json_encode(array("success" => true, "message" => 'Заимпортировано '.$count.' едениц оборудования')));
	echo (json_encode(array("success" => true, "message" => $messageComfirmImport1.' '.$count.' '.$messageComfirmImport2)));
} else {
	//echo (json_encode(array("success" => true, "message" => 'Нет новых едениц оборудования')));
	echo (json_encode(array("success" => true, "message" => $messageEquipmentNew)));
}
				
				
			
		
// <----- XML equipment -----

// ----- XLSX equipment ----->			
}else if($_REQUEST['typeequipment']==2){
	
	/*$objPHPExcel = PHPExcel_IOFactory::load($filename);
	$objPHPExcel->setActiveSheetIndex(0);
	$worksheet = $objPHPExcel->getActiveSheet();
    $highestRow = $worksheet->getHighestRow();*/
	
	$objReader = PHPExcel_IOFactory::createReader('Excel2007');
	$objReader->setReadDataOnly(true);
	$objPHPExcel = $objReader->load($filename);
	$worksheet = $objPHPExcel->getActiveSheet();
	$highestRow = $worksheet->getHighestRow();
	
	writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx  <> '.$_REQUEST['typeref'].' <> highestRow <> '.$highestRow );
	
	if($_REQUEST['typeref']=='equipment'){	
		
		$values = "";
		$count = 0;
		for ( $row = 2; $row <= $highestRow; ++$row )
		{
       
 	    //$worksheet->getStyleByColumnAndRow(1, $row)->getNumberFormat()->setFormatCode('yyyy/mm/dd');
		//$worksheet->getStyle('B'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
		$Date = $worksheet->getCellByColumnAndRow(1,$row)->getValue();
		$Date = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($Date));
		
		$worksheet->getStyle('C'.$row)->getNumberFormat()->setFormatCode('#,##0.00');
		$Serial = (int)$worksheet->getCellByColumnAndRow(2,$row)->getValue();
		
		$Model = $worksheet->getCellByColumnAndRow(3,$row)->getValue();
		
		if($Serial || $Serial !=""){
		
		$Serial = addslashes($Serial);
		$Model = addslashes($Model);
		
		$query = "SELECT `id_equipment` FROM `equipment` WHERE `inventory_number`='".$Serial."'";	
		writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query1 <> '.$query);
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($id_equipment);
		$stmt->fetch();
		$stmt->close();	
		if (!$id_equipment) {
			$query = "INSERT INTO `equipment` (`year_constr`,`model`,`serial_number`,`inventory_number`) VALUES ('".$Date."','".$Model."','".$Serial."','".$Serial."')";
			writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query2 <> '.$query);
			if($stmt = $db->prepare($query)){
			if($stmt->execute()){
				$lastInsertId = $stmt->insert_id;
				$stmt->close();
				
				$query = "INSERT INTO `toir_client_equipment` (`instanse_id`, `id_equipment`) VALUES (".$client_id.", ".$lastInsertId.")";
				writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query3 <> '.$query);
				if($stmt = $db->prepare($query)){
				if($stmt->execute()){
					$stmt->close();
					$count++;
				}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query3 error <> '.$db->errno . ' ' . $db->error); }
				}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query3 error <> '.$db->errno . ' ' . $db->error); }
			}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query2 error <> '.$db->errno . ' ' . $db->error); }
			}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query2 error <> '.$db->errno . ' ' . $db->error); }
		
		}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx duplicate id_equipment <> '.$id_equipment); }
		unset($id_equipment);
		}
		
		}
		
		if($count!=0){
			echo (json_encode(array("success" => true, "message" => $messageComfirmImport1.' '.$count.' '.$messageComfirmImport2)));
		}else{
			echo (json_encode(array("success" => true, "message" => $messageEquipmentNew)));
		}
		
		/*$values .= "('".$Name."','".$Code."','".$City." ".$Address."','".$GeoX."','".$GeoY."',2,".$client_id."),";
		$r = $row % 1000;
        if( $r==0 ){
			$values = substr($values,0,strlen($values)-1);
			$query="INSERT INTO `tms_warehouse` (`Name`,`Code`,`Address`,`GeoX`,`GeoY`,`WarehouseType`,`instanceID`) VALUES ".$values.";";
			//writeToLogFile( date("Y-m-d H:i:s").' - <> '.$query );
			//echo $query;
			if($stmt = $db->prepare($query)){
			$stmt->execute();
			$stmt->close();
			$values = "";
			}
			
		}
		}
		$values = substr($values,0,strlen($values)-1);
		$query="INSERT INTO `tms_warehouse` (`Name`,`Code`,`Address`,`GeoX`,`GeoY`,`WarehouseType`,`instanceID`) VALUES ".$values.";";
		//writeToLogFile( date("Y-m-d H:i:s").' - <> '.$query );
		//echo $query;
		if($stmt = $db->prepare($query)){
		if($stmt->execute()){
		$stmt->close();
		
		echo (json_encode(array("success" => true, "message" => $messageComfirmImport1.' '.$highestRow.' '.$messageComfirmImport2)));
		
		}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query3 error <> '.$db->errno . ' ' . $db->error); echo (json_encode(array("success" => false, "message" => $db->errno . ' ' . $db->error))); }
		}else{ writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xml query3 error <> '.$db->errno . ' ' . $db->error); echo (json_encode(array("success" => false, "message" => $db->errno . ' ' . $db->error))); }
		*/
		
		
		
	}else if($_REQUEST['typeref']=='outlet'){
		
		$values = "";
		$count = 0;
		for ( $row = 2; $row <= $highestRow; ++$row )
		{
       
		$Code = $worksheet->getCellByColumnAndRow(4,$row)->getValue();
		$Address = $worksheet->getCellByColumnAndRow(5,$row)->getValue();
		$Region = $worksheet->getCellByColumnAndRow(6,$row)->getValue();
		$City = $worksheet->getCellByColumnAndRow(7,$row)->getValue();
		
		if($Code || $Code !=""){
		
		$Address = addslashes($Address);
		$Region = addslashes($Region);
		$City = addslashes($City);
		
		$query = "SELECT `id_outlet` FROM `toir_outlets` WHERE  `import_id`='".$Code."';";
		writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query1 <> '.$query);
		$stmt = $db->prepare($query);
		$stmt->execute();
		$stmt->bind_result($id_outlet);
		$stmt->fetch();
		$stmt->close();
		if (!$id_outlet) {
			$xml = simplexml_load_file('http://maps.google.com/maps/api/geocode/xml?address='.$Address.'&sensor=false');			
			if ($xml->status == 'OK') {			
				$geoY = $xml->result->geometry->location->lat; //больше
				$geoX = $xml->result->geometry->location->lng;
			} else {
				$geoX = '';
				$geoY = '';
			}
						
			$query = "INSERT INTO `toir_outlets` (`address`, `region`, `city`, `geoX`, `geoY`, `import_id`) VALUES ('".$Address."','".$Region."', '".$City."', '".$geoX."', '".$geoY."', '".$Code."');";			
			writeToLogFile(' -> '.date('Y-m-d H:i:s').' <> file-upload xlsx query2 <> '.$query);
			$count++;
			$stmt = $db->prepare($query);
			$stmt->execute();
			$stmt->close();			
		}
		unset($id_outlet);
		
		}
		
		}
		
		if($count!=0){
			echo (json_encode(array("success" => true, "message" => $messageComfirmImport1.' '.$count.' '.$messageComfirmImport3)));
		}else{
			echo (json_encode(array("success" => true, "message" => $messageEquipmentNew3)));
		}
	}
		
}		
// <----- XLSX equipment -----
		
	
	
	
	
	
	
			}else{
				//echo (json_encode(array("success" => true, "message" => 'Ошибка открытия '.$namef)));
				echo (json_encode(array("success" => true, "message" => $messageErrorOpen.' '.$namef)));
			}
			exit;
			
			

        }else{
			//echo(json_encode(array("success" => false, "message" => 'Ошибка загрузки файла '.$namef)));
			echo(json_encode(array("success" => false, "message" => $messageErrorLoad.' '.$namef)));
			exit;
		}
	
	
	
		
		
		
	}else{
		//echo(json_encode(array("success" => false, "message" => 'Неверный формат файла')));
		echo(json_encode(array("success" => false, "message" => $messageErrorFormat)));
		exit;
	}
	}else{
		//echo(json_encode(array("success" => false, "message" => 'Файл не может быть пустым')));
		echo(json_encode(array("success" => false, "message" => $messageErrorLength)));
		exit;
	}
?>