<?php
#error_reporting(E_ALL);
#ini_set('display_errors', "On");
ob_start();
require_once "../database.php";


$yyyymm = '';
$bank_no = '';
$customer_id = '';
$customer_name = '';
$kind = '';
$pay_date = '';
$amount_payable = '';
$start_date_payable = '';
$end_date_payable = '';
$creater = '';
#excel字段與mysql表字段映射
$excel_field_mapping = [
	'E5' => "bank_no",
	'E7' => "start_date_payable",
	'L7' => "end_date_payable",
	#下面为列表
	'A12' => "pay_account",
	'G12' => "customer_id",
	'M12' => "customer_name",
	'P12' => "kind",
	'R12' => "pay_date",
	'T12' => "amount_payable",
	'X12' => "pay_status"
	//18 => "enter_dtae",
];

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/";
	//if (is_dir($target_dir) || @mkdir($target_dir, '0777'));
	$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();
		for ($row = 12; $row < $highestRow - 3; $row += 3) {
			$data = [];
			foreach ($excel_field_mapping  as $key => $val) {
				//if ($_val == "") return;
				$_key = strlen($key) == 3 ? (substr($key, 0, 1) . $row) : $key;
				$value = $sheet->getCell($_key)->getValue();
				$_key2 = strlen($key) == 3 ? (substr($key, 0, 1) . '12') : $key;
				$data[$excel_field_mapping[$_key2]] = (empty($value) || $value == '') ? 'null' : "'" . $value . "'"; //读取excel数据到数组
			}


			$data['yyyymm'] =	$_POST['yyyymm'];
			$data['form_key'] =	$_POST['form_key'];
			$db_query1 = 'insert into  paid_customer_detail(' . implode(array_keys($data), ',') . ') values (' . implode(array_values($data), ',') . ')';
			//		echo 	$db_query1;
			mysqli_query($link, $db_query1);
		}
		#从ec_purchase_order找出客户和合计金额等于销账单中客户和合计金额的记录
		#将ec_purchase_order的id复制给paid_customer_detail 的ec_id栏位
		$sql_update_ec_id = "UPDATE appwms.paid_customer_detail a
		SET ec_id = (
			SELECT
			b.id
			FROM
				ec_purchase_order b
			WHERE
				b.id NOT IN (
					SELECT
						c.ec_id
					FROM
						appwms.paid_customer_detail c
					WHERE
						c.ec_id IS NOT NULL
				)
		
			AND a.customer_id= substring(b.customerid, 2)
			AND a.amount_payable = b.summary_price	
			order by  b.enter_date asc limit 0,1
			 ) where a.form_key ='" . $_POST['form_key'] . "'
		 ";
		mysqli_query($link, $sql_update_ec_id);
		if (mysqli_affected_rows($link) > 0) {
			$success_in++;
		} elseif (mysqli_affected_rows($link) == 0) {
			$fail_in++;
			var_dump($data[$excel_field_mapping[$column]]);
		}
		mysqli_close($link);
	}
}
header("location: paid_customer_detail.php");

die();