<?php
require( $_SERVER['DOCUMENT_ROOT'].'/wp-load.php' );

// if ( $_SERVER['REMOTE_ADDR'] == "175.123.107.3" ) {
// 	error_reporting(E_ERROR | E_PARSE);
// 	ini_set("display_errors", 1);
// }

ini_set('memory_limit', -1);

header("Content-Type: application/json; charset=UTF-8");

$result = new StdClass();
$result->result = 0;
$result->error = 1;


if($_FILES["file"] && $_FILES["file"]["name"])
{
	$fileName = 'ustores.xlsx';
	$excelFile = $_SERVER['DOCUMENT_ROOT'].'/wp-content/stores/excel/'.$fileName;

	move_uploaded_file( $_FILES['file']['tmp_name'], $excelFile);

	// require_once $_SERVER['DOCUMENT_ROOT'].'/wp-includes/PHPExcel/Classes/PHPExcel/IOFactory.php';
	// require_once $_SERVER['DOCUMENT_ROOT'].'/wp-includes/PHPExcel/Classes/PHPExcel/IOFactory.php';

  // $objPHPExcel = PHPExcel_IOFactory::load($excelFile);
	// $objWorksheet = $objPHPExcel->getActiveSheet();

	// $highestRow = $objWorksheet->getHighestRow(); // e.g. 10
	// $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
	// $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5


	// if( $highestRow > 1000)
	// 	$highestRow = 1000;

	// global $wpdb;

	// for ($row = 2; $row <= $highestRow; ++$row) {
	 

	// 	$item = array();
	// 	$item['area']  = trim($objWorksheet->getCellByColumnAndRow(0, $row)->getValue());
	// 	$item['brand'] = trim($objWorksheet->getCellByColumnAndRow(1, $row)->getValue());
	// 	$item['name'] = trim($objWorksheet->getCellByColumnAndRow(2, $row)->getValue());
	// 	$item['longitude'] = trim($objWorksheet->getCellByColumnAndRow(3, $row)->getValue());
	// 	$item['latitude'] = trim($objWorksheet->getCellByColumnAndRow(4, $row)->getValue());
	// 	$item['address'] = trim($objWorksheet->getCellByColumnAndRow(5, $row)->getValue());
	// 	$item['op_hour'] = trim($objWorksheet->getCellByColumnAndRow(6, $row)->getValue());
	// 	$item['delivery_yn'] = trim($objWorksheet->getCellByColumnAndRow(7, $row)->getValue());

	// 	if($objWorksheet->getCellByColumnAndRow(8, $row)->getValue())
	// 	{
	// 		$dateTimeObject = PHPExcel_Shared_Date::ExcelToPHPObject($objWorksheet->getCellByColumnAndRow(8, $row)->getValue());

	// 		$item['open_date'] = $dateTimeObject->format('Y-m-d');
	// 	}
	// 	else
	// 		$item['open_date'] = null;

	// 	$item['tel'] = trim($objWorksheet->getCellByColumnAndRow(9, $row)->getValue());
	// 	$item['baemin_link'] = trim($objWorksheet->getCellByColumnAndRow(10, $row)->getValue());

	// 	if(trim($objWorksheet->getCellByColumnAndRow(11, $row)->getValue()) == 'N')
	// 		$item['enabled'] = 0;
	// 	else
	// 		$item['enabled'] = 1;

	// 	$item['reg_date'] = date('Y-m-d H:i:s');

	// 	$rsr = $wpdb->get_results("SELECT * FROM yumsem_stores where name='".$item['name']."' and brand = '".$item['brand']."' ");
	// 	foreach($rsr as $rw) {
	// 		$item['ys_srl'] = $rw->ys_srl  ;
	// 	}

	// 	//var_dump($item);
	// 	if(strlen($item['name']) > 0)
	// 		$wpdb->replace('yumsem_stores', $item);


	// }
	
	require_once $_SERVER['DOCUMENT_ROOT'].'/vendor/autoload.php';
	require_once $_SERVER['DOCUMENT_ROOT'].'/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php';

	


	// Load the spreadsheet file
	$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($excelFile);
	$worksheet = $spreadsheet->getActiveSheet();
	
	$highestRow = $worksheet->getHighestRow(); // e.g. 10
	$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
	$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
	
	if ($highestRow > 1000) {
			$highestRow = 1000;
	}
	
	global $wpdb;
	
	for ($row = 2; $row <= $highestRow; ++$row) {
			$item = array();
	
			$item['area'] = trim($worksheet->getCellByColumnAndRow(1, $row)->getValue());
			$item['brand'] = trim($worksheet->getCellByColumnAndRow(2, $row)->getValue());
			$item['name'] = trim($worksheet->getCellByColumnAndRow(3, $row)->getValue());
			$item['longitude'] = trim($worksheet->getCellByColumnAndRow(4, $row)->getValue());
			$item['latitude'] = trim($worksheet->getCellByColumnAndRow(5, $row)->getValue());
			$item['address'] = trim($worksheet->getCellByColumnAndRow(6, $row)->getValue());
			$item['op_hour'] = trim($worksheet->getCellByColumnAndRow(7, $row)->getValue());
			$item['delivery_yn'] = trim($worksheet->getCellByColumnAndRow(8, $row)->getValue());
	
			//  $openDateValue = $worksheet->getCellByColumnAndRow(9, $row)->getValue();
			// if ($openDateValue) {
			// 		$dateTimeObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($openDateValue);
			// 		$item['open_date'] = $dateTimeObject->format('Y-m-d');
			// } else {
			// 		$item['open_date'] = null;
			// }
			
			$openDateValue = $worksheet->getCellByColumnAndRow(9, $row)->getValue();
				
				if ($openDateValue) {
						$excelTimestamp = (int) $openDateValue; 

						if ($excelTimestamp > 0) {
								
								$unixTimestamp = ($excelTimestamp - 25569) * 86400; 
								
								$dateTime = new DateTime("@$unixTimestamp"); 
								$item['open_date'] = $dateTime->format('Y-m-d');
				
						} else {
								
								$item['open_date'] = null; 
						}
				} else {
						$item['open_date'] = null;
				}

			$item['tel'] = trim($worksheet->getCellByColumnAndRow(10, $row)->getValue());
			$item['baemin_link'] = trim($worksheet->getCellByColumnAndRow(11, $row)->getValue());
	
			$enabledValue = trim($worksheet->getCellByColumnAndRow(12, $row)->getValue());
			$item['enabled'] = ($enabledValue == 'N') ? 0 : 1;
	
			$item['reg_date'] = date('Y-m-d H:i:s');
	
			$rsr = $wpdb->get_results("SELECT * FROM yumsem_stores where name='" . $item['name'] . "' and brand = '" . $item['brand'] . "'");
			foreach ($rsr as $rw) {
					$item['ys_srl'] = $rw->ys_srl;
			}
	
			// var_dump($item);
			if (strlen($item['name']) > 0) {
					$wpdb->replace('yumsem_stores', $item);
			}
	}
	
	$result->result = 1;
	$result->error = 0;
}



 echo json_encode($result);