<?php
//error_reporting(E_ALL);
ob_start();
//ini_set('display_errors', "On");

require_once "../database.php";

$customerid = "";
$inv_customer_id = "";
$inv_customer_name = "";
$customer_name = "";
$item_no = "";
$item_name = "";
$num = "";
$unit_price = "";
$pre_tax_price = "";
$business_tax = "";
$summary_price = "";
#$delivery_date = "";
$summary = "";
$contract_begin_date = "";
$contract_end_date = "";
$salesman = "";
$operator_name = "";
$enter_date = "";
$creater = "";
$create_at = "";
#excel字段與mysql表字段映射
$excel_field_mapping = [
	0 => "date_seq",
	1 => "customerid",
	2 => "inv_customer_id",
	3 => "inv_customer_name",
	4 => "customer_name",
	5 => "item_no",
	6 => "item_name",
	7 => "num",
	8 => "unit_price",
	9 => "pre_tax_price",
	10 => "business_tax",
	11 => "summary_price",
	12 => "delivery_date",
	13 => "summary",
	14 => "contract_begin_date",
	15 => "contract_end_date",
	16 => "salesman",
	17 => "operator_name",
	18 => "enter_date",
];

if ($_SERVER["REQUEST_METHOD"] == "POST") {
	include "header.php";
	// 载入db.php来连结资料库
	require_once "../database.php";
	require_once '../PHPExcel/PHPExcel.php';
	require_once '../PHPExcel/IOFactory.php';

	$file_name = $_FILES["file"]["name"];
	$temp_file_name = $_FILES["file"]["tmp_name"];
	//$target_dir = "../inv-uploads/ec/" . $_POST['yyyymm'] . "/";

	//if (is_dir($target_dir) || mkdir($target_dir, '0777'));
	$target_dir = "../inv-uploads/";

	$target_file = strtolower($target_dir . strtotime("now") . "." . pathinfo($file_name, PATHINFO_EXTENSION));
	$EXTENSION = pathinfo($target_file, PATHINFO_EXTENSION);
	if (move_uploaded_file($temp_file_name, $target_file)) {
		if ($EXTENSION == 'xls') {
			$objReader = PHPExcel_IOFactory::createReader('Excel5'); //支持xls
		} else {
			$objReader = PHPExcel_IOFactory::createReader('Excel2007'); //支持xlsx是
		}
		$objPHPExcel = $objReader->load($target_file); //读取上传到服务器的文件
		$sheet = $objPHPExcel->getSheet(0); //拿到第一个sheet数据
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumn = $sheet->getHighestColumn(); // 取得总列数
		$highestColumn = PHPExcel_Cell::columnIndexFromString($highestColumn); // 转换字母为对应的统计数字；
		$excelArray = array();
		$success_in = 0;
		$fail_in = 0;
		#循環讀取excel ec_purchase_order
		/**
		 * 1.将表格存放在 ec_purchase_order_tmp表,group by 是否有重复记录,有则定向到ec_purchase_order_import显示
		 * 2.ec_purchase_order_tmp表group by 后无重复, 则union  ec_purchase_order 是否有重复记录,有则定向到ec_purchase_order_import显示
		 * 3.如果没有重复资料,则重定向到ec_purchase_order页面
		 */
		# 1.将表格存放在 ec_purchase_order_tmp表,group by 是否有重复记录,有则定向到ec_purchase_order_import显示
		mysqli_query($link, "delete from  ec_purchase_order_tmp where form_key='" . $_POST['form_key'] . "'");

		for ($row = 3; $row <= $highestRow - 3; $row++) {
			$val = $sheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
			if ($val == "") return;
			$data = [];
			for ($column = 0; $column < $highestColumn; $column++) {
				$val = $sheet->getCellByColumnAndRow($column, $row)->getCalculatedValue();
				//上午 오전 am  下午 오후 pm  2023-03-06 오후 12:47:28
				#包含上午 오전
				if ($column == 18 &&    mb_strpos($val, '오전')) {
					$_am = str_replace('오전', "",		$val);
					//echo "_A:" . $_am . 'AM';
					$val = date("Y-m-d  G:i:s",  strtotime($_am . 'AM'));
				} elseif ($column == 18 &&    mb_strpos($val, '오후')) {
					$_pm = str_replace('오후', "",	$val);
					$val = date("Y-m-d  G:i:s",  strtotime($_pm . 'PM'));
				}
				$data[$excel_field_mapping[$column]] = (empty($val) || $val == '') ? 'null' : "'" . $val . "'"; //读取excel数据到数组
			}
			$data['yyyymm'] =	$_POST['yyyymm'];
			$data['form_key'] =	$_POST['form_key'];
			$db_query1 = 'insert into  ec_purchase_order_tmp(' . implode(array_keys($data), ',') . ') values (' . implode(array_values($data), ',') . ')';
			mysqli_query($link, $db_query1);
		}

		#ec_purchase_order_tmp表group by 是否有重复记录,有则定向到ec_purchase_order_import显示
		#gorup by 

		$sql_group_epot = "
		SELECT count(*)  cnt,
					date_seq,
					customerid,
					item_no,
					num,
					unit_price,
					pre_tax_price,
					business_tax,
					summary_price,
					enter_date From ec_purchase_order_tmp
					WHERE form_key = '" . $_POST['form_key'] . "'
					GROUP BY  date_Seq, customerid, item_no, num, unit_price, pre_tax_price, business_tax, summary_price, enter_date
					HAVING COUNT(*) > 1";

		$res_group_epot = mysqli_fetch_row(mysqli_query($link, $sql_group_epot));
		#存在重复的记录ec_purchase_order_import
		if (!empty($res_group_epot)) {
			header("location: ec_purchase_order_import.php?error_form_key=" . $_POST['form_key']);
			exit();
		}
		$sql_group_epo = "
				SELECT
				count(*) cnt,
				a.date_seq,
				a.customerid,
				a.item_no,
				a.num,
				a.unit_price,
				a.pre_tax_price,
				a.business_tax,
				a.summary_price,
				a.enter_date
			FROM
				ec_purchase_order a,
				ec_purchase_order_tmp b
			WHERE
				a.date_seq = b.date_seq
			AND a.customerid = b.customerid
			AND a.item_no = b.item_no
			AND a.num = b.num
			AND a.unit_price = b.unit_price
			AND a.pre_tax_price = b.pre_tax_price
			AND a.business_tax = b.business_tax
			AND a.summary_price = b.summary_price
			AND a.enter_date = b.enter_date
			GROUP BY a.date_seq, a.customerid, a.item_no, a.num, a.unit_price,
				a.pre_tax_price, a.business_tax, a.summary_price, 	a.enter_date";

		$res_group_epo = mysqli_fetch_row(mysqli_query($link, $sql_group_epo));
		//	echo 	$sql_group_epo;
		//	exit();
		#存在重复的记录ec_purchase_order_import
		if (!empty($res_group_epo)) {
			header("location: ec_purchase_order_import.php?error_form_key=" . $_POST['form_key']);
			exit();
		}
		#不存在重复记录则插入到ec_purchase_order
		$sql_ins_epo = "insert into ec_purchase_order(id,date_seq,customerid,inv_customer_id,inv_customer_name,customer_name,item_no,item_name,num,unit_price,pre_tax_price,business_tax,summary_price,delivery_date,summary,contract_begin_date,contract_end_date,salesman,operator_name,enter_date,yyyymm,form_key,creater,create_at )
		select   id,date_seq,customerid,inv_customer_id,inv_customer_name,customer_name,item_no,item_name,num,unit_price,pre_tax_price,business_tax,summary_price,delivery_date,summary,contract_begin_date,contract_end_date,salesman,operator_name,enter_date,yyyymm,form_key,creater,create_at From 
		ec_purchase_order_tmp
		where form_key='" . $_POST['form_key'] . "'";
		mysqli_query($link,	$sql_ins_epo);
		mysqli_query($link, "delete from ec_purchase_order_tmp where form_key='" . $_POST['form_key'] . "' ");

		mysqli_close($link);
	}
}
#echo  $fail_in;
header("location: ec_purchase_order.php");

die();