<?php
		include 'config.php';
		// Check if the user is logged in
		if ((!isSet($_SESSION['loginname'])) || ($loggin <> '1')) {
			header("Location: login.php");
			exit;
		}


		function validateDate($date, $format = 'Y-m-d H:i:s')
		{
			$d = DateTime::createFromFormat($format, $date);
			return $d && $d->format($format) == $date;
		}

		
		$nowdate = date("Y-m-d H:i:s");
		if (!empty($_POST["excelfile_name"])) {
			include 'phpexcel/PHPExcel.php';
			include 'phpexcel/PHPExcel/Writer/Excel2007.php';
			require_once 'phpexcel/PHPExcel/IOFactory.php';

			$file_name = $_POST["excelfile_name"];
			$reader   = PHPExcel_IOFactory::createReader('Excel2007'); // 讀取2007 excel 檔案
			$PHPExcel = $reader->load("excel_file/$file_name"); // 檔案名稱
			$sheet    = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)
			//$highestRow = $sheet->getHighestRow(); // 取得總列數
			$highestRow = $sheet->getHighestDataRow(); // 取得總列數

			$highestColumn = 13; // set column
			//echo "==".$highestRow."<br />";
			// 一次讀取一列
			for ($excel_row = 3; $excel_row <= $highestRow; $excel_row++) {

				$valid_row = 1;
				for ($excel_column = 0; $excel_column <= $highestColumn; $excel_column++) {
					$val = $sheet->getCellByColumnAndRow($excel_column, $excel_row)->getValue();
					// $val . ',';
					//echo "valid_row=".$valid_row.";";
					if ($excel_column == 0) { //region
						$file_region = $val;
						if($val == "CN" || $val == "HK"){

						}else{
							$valid_row = 0;
						}
					} else if ($excel_column == 1) { //series_code
						$file_series_code = $val;
					} else if ($excel_column == 2) { //model_code
						$file_model_code = $val;
					} else if ($excel_column == 3) { //free_mix_color
						if($val == "Yes" || $val == "N/A"){

							if ($val == "Yes") {
								$file_free_mix_color = 1;
							} else if ($val == "N/A") {
								$file_free_mix_color = 0;
							}
						}else{
							$valid_row = 0;
						}

					} else if ($excel_column == 4) { //fixed_warranty_year
						$file_fixed_warranty_year = $val;
						if(is_numeric($val) && (int)$val == $val){

						}else{
							$valid_row = 0;
						}
					} else if ($excel_column == 5) { //extend_warranty_year
						$file_extend_warranty_year = $val;
						if(is_numeric($val) && (int)$val == $val){

						}else{
							$valid_row = 0;
						}
					} else if ($excel_column == 6) { //promote_datefrom
						$file_promote_datefrom = PHPExcel_Style_NumberFormat::toFormattedString($val, "YYYY-MM-DD");
						if (!validateDate($file_promote_datefrom, 'Y-m-d')) {
							$valid_row = 0;
						}

					} else if ($excel_column == 7) { //promote_dateto
						$file_promote_dateto = PHPExcel_Style_NumberFormat::toFormattedString($val, "YYYY-MM-DD");
						if (!validateDate($file_promote_dateto, 'Y-m-d')) {
							$valid_row = 0;
						}
					} else if ($excel_column == 8) { //warranty_detail_sc
						$file_warranty_detail_sc = $val;
					} else if ($excel_column == 9) { //warranty_detail_tc
						$file_warranty_detail_tc = $val;
					} else if ($excel_column == 10) { //warranty_detail_en
						$file_warranty_detail_en = $val;
					} else if ($excel_column == 11) { //warranty_detail_extend_sc
						$file_warranty_detail_extend_sc = $val;
					} else if ($excel_column == 12) { //warranty_detail_extend_tc
						$file_warranty_detail_extend_tc = $val;
					} else if ($excel_column == 13) { //warranty_detail_extend_en
						$file_warranty_detail_extend_en = $val;
					} else {
					}

					//echo $excel_column.":".$valid_row."; ";
				}

				if($valid_row == 1){
					//insert a row to series table
					$sql = "select * from series where series_code = ? and region = ? and status=? and deleted = ? ";

					if (!($sth = $dbh->prepare($sql))) {
						throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
					}

					if (!$sth->execute(array($file_series_code, $file_region,"1", "0"))) {
						throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
					}

					if($sth->rowCount() == 0){//new series
						$sql2 = "insert into series (region, series_code, createby, createdate, lastupby,lastupdate) values (?, ?, ?, ?, ?, ?)";
						if (!($sth2 = $dbh->prepare($sql2))) {
							throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
						}

						if (!$sth2->execute(array($file_region, $file_series_code, $_SESSION['cmsloginid'], $nowdate, $_SESSION['cmsloginid'], $nowdate))) {
							throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
						}

						$file_series_id = $dbh->lastInsertId();
					}else{
						$row = $sth->fetch(PDO::FETCH_ASSOC);
						$file_series_id = $row{"series_id"};
					}


					$sql = "select * from series series, model model where model.series_id = series.series_id and series.series_code = ? and model.model_code = ? and model.region = ? and series.deleted=? and model.deleted=? group by model.model_id ";

					if (!($sth = $dbh->prepare($sql))) {
						throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
					}

					$parameter = array($file_series_code,$file_model_code, $file_region, "0", "0" );
					if (!$sth->execute($parameter)) {
						throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
					}

					if($sth->rowCount() == 0){//new model
						$sql2 = "insert into model (region, series_id, model_code, free_mix_color, fixed_warranty_year, extend_warranty_year, promote_datefrom,promote_dateto, warranty_detail_sc, warranty_detail_tc, warranty_detail_en, warranty_detail_extend_sc, warranty_detail_extend_tc, warranty_detail_extend_en, createby, createdate, lastupby,lastupdate) values (?, ?, ?,?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?,?,?,?,?)";
						if (!($sth2 = $dbh->prepare($sql2))) {
							throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
						}

						$parameter2 = array($file_region, $file_series_id, $file_model_code, $file_free_mix_color, $file_fixed_warranty_year,$file_extend_warranty_year, $file_promote_datefrom, $file_promote_dateto,$file_warranty_detail_sc, $file_warranty_detail_tc, $file_warranty_detail_en,$file_warranty_detail_extend_sc,$file_warranty_detail_extend_tc,$file_warranty_detail_extend_en, $_SESSION['cmsloginid'], $nowdate, $_SESSION['cmsloginid'], $nowdate);
						if (!$sth2->execute($parameter2)) {
							throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
						}
					}else{
						$sql2 = "update model set free_mix_color=?, fixed_warranty_year=?, extend_warranty_year=?,promote_datefrom=?,promote_dateto=?, warranty_detail_sc=?, warranty_detail_tc=?, warranty_detail_en=?,warranty_detail_extend_sc=?, warranty_detail_extend_tc=?, warranty_detail_extend_en=?, lastupby=?,lastupdate=? where series_id=? and model_code=? ";
						if (!($sth2 = $dbh->prepare($sql2))) {
							throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
						}

						$parameter2 = array($file_free_mix_color, $file_fixed_warranty_year,$file_extend_warranty_year, $file_promote_datefrom, $file_promote_dateto,$file_warranty_detail_sc, $file_warranty_detail_tc, $file_warranty_detail_en,$file_warranty_detail_extend_sc,$file_warranty_detail_extend_tc,$file_warranty_detail_extend_en, $_SESSION['cmsloginid'], $nowdate,$file_series_id, $file_model_code);
						if (!$sth2->execute($parameter2)) {
							throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
						}
					}
				}

				//echo $valid_row;

			}

			unlink("excel_file/$file_name");
			header("Location: import_product_data_index.php?msg=Update Product Data Successfully");
		}

	?>