| Server IP : 210.245.233.93 / Your IP : 216.73.216.226 Web Server : Apache/2.2.15 (CentOS) System : Linux webserver2.onesolution.com.hk 2.6.32-754.35.1.el6.x86_64 #1 SMP Sat Nov 7 12:42:14 UTC 2020 x86_64 User : apache ( 48) PHP Version : 5.3.3 Disable Function : exec, shell_exec, system, passthru, popen, proc_open, pcntl_exec MySQL : ON | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/(Del)eizo.hkosl.com/global/webadmin/ |
Upload File : |
<?php
include 'config.php';
// Check if the user is logged in
if ((!isSet($_SESSION['loginname'])) || ($loggin <> '1')) {
header("Location: login.php");
exit;
}
$excel_mimetypes = array(
"application/vnd.ms-excel",
"application/vnd.ms-excel.addin.macroEnabled.12",
"application/vnd.ms-excel.sheet.binary.macroEnabled.12",
"application/vnd.ms-excel.sheet.macroEnabled.12",
"application/vnd.ms-excel.template.macroEnabled.12",
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
);
if (in_array($_FILES['excelfile']['type'], $excel_mimetypes)) {
// possible excel file
// copy the file and save it to service
$uplodetime = date("Y-m-d_H-i-s");
$tmp_file_name = $_FILES["excelfile"]["tmp_name"];
$file_name = $_FILES["excelfile"]["name"];
preg_match("/\.([^\.]+)$/", $file_name, $file_ext);
$file_name = "product_data_".$uplodetime.".".$file_ext[1]; // default length 8
move_uploaded_file($tmp_file_name, "excel_file/$file_name");
}else{
echo "<script>alert('The file type is not a excel file type. Please upload the excel file!'); window.location.href='import_product_data_index.php';</script>";
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>Content Management System (CMS) - Powered by One Solution Limited</title>
<link rel="stylesheet" type="text/css" href="css/style.css"/>
<!-- Main Menu -->
<link rel="stylesheet" type="text/css" href="css/menu.css"/>
<script type="text/javascript" src="js/jquery-1.10.1.min.js"></script>
<script type="text/javascript" src="js/jquery-migrate-1.2.1.min.js"></script>
<script type="text/javascript" src="js/ddaccordion.js"></script>
<script type="text/javascript" src="js/menuddaccordion.js"></script>
<!-- End Main Menu -->
</head>
<body>
<table width="1300" height="600" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="200" align="left" valign="top">
<table width="200" border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="70" align="left" valign="middle"> </td>
</tr>
<tr>
<td width="200" align="left" valign="top"><!-- Main Menu -->
<?php require("menu.php");?><!-- End Main Menu --></td>
</tr>
</table>
</td>
<td width="1100" align="left" valign="top">
<form action="import_product_data_post.php" method="post" name="modifyform" enctype="multipart/form-data">
<input type="hidden" name="excelfile_name" value="<?=$file_name?>"/>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="70" align="right" valign="middle" class="icontxt">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="50" align="center">
<a href="#" onClick="modifyform.submit()"><img src="images/iconSave.png" alt="Save" width="32" height="32" border="0"><br>
Save </a></td>
<td width="50" align="center">
<a href="import_product_data_index.php"><img src="images/iconCancel.png" alt="Cancel" width="32" height="32" border="0"><br>
Back </a></td>
<td> </td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<span style="float:left;" class="pagetitletxt"> <b><img src="images/iconList.jpg" width="48" height="48" align="absmiddle"/>
Preview Import Product Data
</b></span>
</td>
</tr>
<tr>
<td height="25" align="left" valign="middle" class="msg"></td>
</tr>
<tr>
<td align="left" valign="middle"><!-- Content -->
<table width="100%" border="1" cellpadding="5" cellspacing="0">
<tr>
<td colspan="15">
<div style="width: 20px; height: 20px;background: yellow;display: inline-block;"> </div> : Data has changed
<div style="width: 20px; height: 20px;background: #F78181;display: inline-block;"> </div> : Data has error
</td>
</tr>
<tr>
<th class='content'>Region</th>
<th class='content'>Series</th>
<th class='content'>Model</th>
<th class='content'>Free On-site Calibration</th>
<th class='content'>Fixed Warranty Year</th>
<th class='content'>Extend Warranty Year</th>
<th class='content'>Promote Date From</th>
<th class='content'>Promote Date To</th>
<th class='content'>Warranty Detail(SC)</th>
<th class='content'>Warranty Detail(TC)</th>
<th class='content'>Warranty Detail(EN)</th>
<th class='content'>Warranty Detail-Extend(SC)</th>
<th class='content'>Warranty Detail-Extend(TC)</th>
<th class='content'>Warranty Detail-Extend(EN)</th>
<th class='content' style="background-color: #d3d3d3;">Checking</th>
</tr>
<?php
function validateDate($date, $format = 'Y-m-d H:i:s')
{
$d = DateTime::createFromFormat($format, $date);
return $d && $d->format($format) == $date;
}
include 'phpexcel/PHPExcel.php';
include 'phpexcel/PHPExcel/Writer/Excel2007.php';
require_once 'phpexcel/PHPExcel/IOFactory.php';
$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++) {
echo "<tr>";
$error_msg = "";
for ($excel_column = 0; $excel_column <= $highestColumn; $excel_column++) {
$val = $sheet->getCellByColumnAndRow($excel_column, $excel_row)->getValue();
// $val . ',';
if($excel_column == 0){
$file_region = $val;
if($file_region == "CN" || $file_region == "HK"){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_error'>".$val."</td>";
$error_msg .= "Region: Only HK or CN allowed, ";
}
}
if($excel_column == 1){
$file_series_code = $val;
echo "<td class='content'>".$val."</td>";
}
if($excel_column == 2){
$file_model_code = $val;
echo "<td class='content'>".$val."</td>";
}
if($excel_column > 2){
//checking the change and highlight it
//$sql = "select * from series series, model model where model.series_id = series.series_id and series.series_code = ? and model.model_code = ? and series.status =? and series.deleted = ? and model.status = ? and model.deleted=? group by model.model_id ";
$sql = "select * from series series, model model where model.series_id = series.series_id and series.series_code = ? and model.model_code = ? 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, "1", "0", "1", "0" );
$parameter = array($file_series_code,$file_model_code,"0", "0" );
if (!$sth->execute($parameter)) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
$row = $sth->fetch(PDO::FETCH_ASSOC);
if($sth->rowCount() > 0){ //updated record
$action = "Update";
if($excel_column == 3){//free_mix_color
//$file_free_mix_color = $val;
if($val == "Yes" || $val == "N/A"){
if($val == "Yes"){
$file_free_mix_color = 1;
}else if($val == "N/A"){
$file_free_mix_color = 0;
}
if($row{"free_mix_color"} == $file_free_mix_color){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else{
echo "<td class='content import_product_data_error'>".$val."</td>";
$error_msg .= "Free Mix Color: Only N/A or Yes allowed, ";
}
}else if($excel_column == 4){//fixed_warranty_year
if(is_numeric($val) && (int)$val == $val){
if($row{"fixed_warranty_year"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else{
echo "<td class='content import_product_data_error'>".$val."</td>";
$error_msg .= "Fixed Warranty Year: Only Decimal Number allowed, ";
}
}else if($excel_column == 5){//extend_warranty_year
if(is_numeric($val) && (int)$val == $val){
if($row{"extend_warranty_year"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else{
echo "<td class='content import_product_data_error'>".$val."</td>";
$error_msg .= "Extend Warranty Year: Only Decimal Number allowed, ";
}
}else if($excel_column == 6){//promote datefrom
//$file_promote_datefrom = $val;
$file_promote_datefrom = PHPExcel_Style_NumberFormat::toFormattedString($val, "YYYY-MM-DD");
if ($row{"promote_datefrom"} == $file_promote_datefrom || empty($file_promote_datefrom)) {
echo "<td class='content'>" . $file_promote_datefrom . "</td>";
} else {
if (!validateDate($file_promote_datefrom, 'Y-m-d')) {
echo "<td class='content import_product_data_error'>" . $file_promote_datefrom . "</td>";
$valid_row = 0;
$error_msg .= "Promote Date From: Wrong date format, ";
} else {
echo "<td class='content import_product_data_changed'>" . $file_promote_datefrom . "</td>";
}
}
}else if($excel_column == 7){//promote dateto
$file_promote_dateto = PHPExcel_Style_NumberFormat::toFormattedString($val, "YYYY-MM-DD");
if ($row{"promote_dateto"} == $file_promote_dateto || empty($file_promote_dateto)) {
echo "<td class='content'>" . $file_promote_dateto . "</td>";
} else {
if (!validateDate($file_promote_dateto, 'Y-m-d')) {
echo "<td class='content import_product_data_error'>" . $file_promote_dateto . "</td>";
$valid_row = 0;
$error_msg .= "Promote Date From: Wrong date format, ";
} else {
echo "<td class='content import_product_data_changed'>" . $file_promote_dateto . "</td>";
}
}
}else if($excel_column == 8){//warranty_detail_sc
if($row{"warranty_detail_sc"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else if($excel_column == 9){//warranty_detail_tc
if($row{"warranty_detail_tc"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else if($excel_column == 10){//warranty_detail_en
if($row{"warranty_detail_en"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else if($excel_column == 11){//warranty_detail_en
if($row{"warranty_detail_extend_sc"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else if($excel_column == 12){//warranty_detail_en
if($row{"warranty_detail_extend_tc"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else if($excel_column == 13){//warranty_detail_en
if($row{"warranty_detail_extend_en"} == $val){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_changed'>".$val."</td>";
}
}else{}
}else{ //new record
$action = "Insert";
if($excel_column == 3){//free_mix_color
//$file_free_mix_color = $val;
if($val == "Yes" || $val == "N/A"){
if($val == "Yes"){
$file_free_mix_color = 1;
}else if($val == "N/A"){
$file_free_mix_color = 0;
}
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_error'>".$val."</td>";
$error_msg .= "Free On-site Calibration: Only N/A or Yes allowed, ";
}
}else if($excel_column == 4){//fixed_warranty_year
if(is_numeric($val)){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_error'>".$val."</td>";
$error_msg .= "Fixed Warranty Year: Only Number allowed, ";
}
}else if($excel_column == 5){//extend_warranty_year
if(is_numeric($val)){
echo "<td class='content'>".$val."</td>";
}else{
echo "<td class='content import_product_data_error'>".$val."</td>";
$error_msg .= "Extend Warranty Year: Only Number allowed, ";
}
}
if($excel_column > 5){
echo "<td class='content'>".$val."</td>";
}
}
}
}
//show the checking result
if(empty($error_msg)){
$valid = "Valid";
echo "<td class='content' style='width:60px;background-color: #d3d3d3;'>".$valid.": ".$action."</td>";
}else{
$valid = "Invalid";
echo "<td class='content' style='width:100px;background-color: #FA5858;'>".$valid.": ".$error_msg."</td>";
}
echo "</tr>";
}
?>
</table>
<!-- End Content --></td>
</tr>
<tr>
<td align="left" valign="middle"> </td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>