| 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 = "customer_warranty_" . $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_serial_number_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="1400" 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="1200" align="left" valign="top">
<form action="import_customer_warranty_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_customer_warranty_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 Customer Warranty </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" style="word-break: break-all;">
<tr>
<td colspan="16">
<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'>Series</th>
<th class='content'>Model</th>
<th class='content'>Serial Number</th>
<th class='content'>Purchase Region</th>
<th class='content'>Date of Purchase</th>
<th class='content'>Merchant Name</th>
<th class='content'>Name</th>
<th class='content'>Gender</th>
<th class='content'>Age Group</th>
<th class='content'>Email</th>
<th class='content'>Contact Number</th>
<th class='content'>Address</th>
<th class='content'>Education Level</th>
<th class='content'>Occupation</th>
<th class='content'>Monthly Income</th>
<th class='content'>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;
}
//data decryption
//include_once('../phpseclib0.3.6/Crypt/RSA.php');
set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib0.3.6');
include('Crypt/RSA.php');
$rsa = new Crypt_RSA();
$private_key = "MIICXAIBAAKBgQDH0i//hwmPSCUNAS9UiD3wSH43NxwRKwGtHQKGarANuYNacSXb +rS2N0WKihz+aSkevePj5GZPtu21Fpx0Tt541DBlUrYL0vPK4S6QUX8I5vsOQ/Xu Iyh9NKcD2fbmf/1QrC+q7pv6xa6ctaV8u1RPGI7CAIPtMCrh9Pbg+EFo0wIDAQAB AoGATSi2HRMtV1UR7YggGSG03SR5DGQs49o5/vldQL9QtTQF6zYW2u9fuZ71LO5x DLJZzQOhTJ4k/bIS7yLIjkboM4x75DlRpG14Umi+d2GePB+r9UiqeZ67Zh7OvKFU 1CIkNC2J9G0U2G4RSG2UAZKkimIofmptq7dXa5oYYNL74t0CQQDi4RHoxIhqh9mw rLxvawgcq6gseKuY5zEgJMHGtNQTIGqn1LgxCCwNx5473xc7jW6GQbgCtDVwGqeb VkejSSsFAkEA4XgGfrAFHSQdZ9+GkNsT+eZioX5wyxP78b3csTRPPii4NudJmyeu yHmmhhJV6fknQ9ZsYqLwHH3gJpzXeH/79wJAEL3S+H4oItRLeAIHFaGsQ5FXI5Dz +KB2u4x60BLf41ltBV1pChU0SwAD6R8ZM8fH9Bg9X9W0a8VLwOao+p+XkQJBANG3 MGNaGgeW9fsOAD5wZ85Dqm4IC6X4upmKPeJdg2+PtHjs/Jvid0JlVKzUFTGB3DHP GhVM9QWrV3jS3eBpOtUCQB6Fyf7bkMHxRwa2QLoB3bM/gvk0mJPCBFpILfI5RZZG XezjDt3Xo/MqppBr8nybC42OxjbID9ALPPe/VX9AVmA=";
$rsa->loadKey($private_key); // private key
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 = 14; // set column
//echo "==".$highestRow."<br />";
// 一次讀取一列
for ($excel_row = 2; $excel_row <= $highestRow; $excel_row++) {
echo "<tr>";
$valid_row = 1;
$error_msg = "";
$first_main_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_series_code = $val;
echo "<td class='content'>" . $val . "</td>";
}
if ($excel_column == 1) {
$file_model_code = $val;
echo "<td class='content'>" . $val . "</td>";
}
if ($excel_column == 2) {
$file_serial_number = $val;
echo "<td class='content'>" . $val . "</td>";
}
if ($excel_column == 3) {
$file_region = $val;
if ($file_region == "CN" || $file_region == "HK" || $file_region == "MO") {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_error'>" . $val . "</td>";
$error_msg .= "Region: Only CN, HK and MO allowed, ";
}
}
if ($excel_column > 3) {
//check if this serial number exist
$sql = "select * from serial_number sn, model model, series series where sn.series_code = series.series_code and sn.model_code = model.model_code and sn.series_code = ? and sn.model_code = ? and sn.serial_number = ? and sn.deleted = ? and sn.region = ? and model.region = ? and series.region = ? group by sn.serial_number_id ";
if (!($sth = $dbh->prepare($sql))) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
if ($file_region == "HK" || $file_region == "MO") {
$this_region = "HK";
} else if ($file_region == "CN") {
$this_region = "CN";
} else {
$this_region = "CN";
$valid_row = 0;
}
if (!$sth->execute(array($file_series_code, $file_model_code, $file_serial_number, "0", $this_region, $this_region, $this_region))) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
//$row = $sth->fetch(PDO::FETCH_ASSOC);
if ($sth->rowCount() > 0) { //make sure the series and model exist
$sql = "select * from customer_warranty where series_code=? and model_code=? and serial_number=? and region=? and deleted=? ";
if (!($sth = $dbh->prepare($sql))) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
$parameter = array($file_series_code, $file_model_code, $file_serial_number, $this_region, "0");
if (!$sth->execute($parameter)) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
$row = $sth->fetch(PDO::FETCH_ASSOC);
if ($sth->rowCount() > 0) { //update old data and check the changed part
if ($excel_column == 4) {
//$file_date_of_purchase = $val;
$file_date_of_purchase = PHPExcel_Style_NumberFormat::toFormattedString($val, "YYYY-MM-DD");
if ($row{"date_of_purchase"} == $file_date_of_purchase) {
echo "<td class='content'>" . $file_date_of_purchase . "</td>";
} else {
if (!validateDate($file_date_of_purchase, 'Y-m-d')) {
echo "<td class='content import_product_data_error'>" . $file_date_of_purchase . "</td>";
$valid_row = 0;
$error_msg .= "Date of Purchase: Wrong date format, ";
} else {
echo "<td class='content import_product_data_changed'>" . $file_date_of_purchase . "</td>";
}
}
}
if ($excel_column == 5) {
$file_merchant_name = $val;
if ($row{"merchant_name"} == $file_merchant_name) {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
if ($excel_column == 6) {
$file_customer_name = $val;
if ($rsa->decrypt(base64_decode($row{"customer_name"})) == $file_customer_name) {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
if ($excel_column == 7) {
$file_gender = $val;
if ($row{"gender"} == $file_gender) {
echo "<td class='content'>" . $val . "</td>";
} else {
if ($file_gender == "男" || $file_gender == "女" || $file_gender == "Male" || $file_gender == "Female") {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_error'>" . $val . "</td>";
$valid_row = 0;
$error_msg .= "Gender: 男, 女, Male or Female allowed, ";
}
}
}
if ($excel_column == 8) {
$file_age_group = $val;
if ($row{"age_group"} == $file_age_group) {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
if ($excel_column == 9) {
$file_email = $val;
if ($rsa->decrypt(base64_decode($row{"email"})) == $file_email) {
echo "<td class='content'>" . $val . "</td>";
} else {
if (!filter_var($file_email, FILTER_VALIDATE_EMAIL)) {
echo "<td class='content import_product_data_error'>" . $val . "</td>";
$valid_row = 0;
$error_msg .= "Email: Wrong email format, ";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
}
if ($excel_column == 10) {
$file_contact_number = $val;
if ($rsa->decrypt(base64_decode($row{"contact_number"})) == $file_contact_number) {
echo "<td class='content'>" . $val . "</td>";
} else {
if (!is_numeric($file_contact_number)) {
echo "<td class='content import_product_data_error'>" . $val . "</td>";
$valid_row = 0;
$error_msg .= "Contact Number: Only number allowed, ";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
}
if ($excel_column == 11) {
$file_address = $val;
if ($rsa->decrypt(base64_decode($row{"address"})) == $file_address) {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
if ($excel_column == 12) {
$file_education_level = $val;
if ($row{"education_level"} == $file_education_level) {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
if ($excel_column == 13) {
$file_occupation = $val;
if ($row{"occupation"} == $file_occupation) {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
if ($excel_column == 14) {
$file_monthly_income = $val;
if ($row{"monthly_income"} == $file_monthly_income) {
echo "<td class='content'>" . $val . "</td>";
} else {
echo "<td class='content import_product_data_changed'>" . $val . "</td>";
}
}
$action = "Update";
} else { //insert new data and check the series, model, serial number, region
$action = "Insert";
echo "<td class='content'>" . $val . "</td>";
}
} else { //series, model, serial number, region not match
echo "<td class='content'>" . $val . "</td>";
$first_main_error_msg = "Series, Model, Serial Number and Purchase Region do not match, ";
$valid_row = 0;
}
}
}
if ($valid_row == 0) {
if (!empty($first_main_error_msg)) {
echo "<td class='content import_product_data_error' style='width: 150px;'>Invalid: " . $first_main_error_msg . $error_msg . "</td>";
} else {
echo "<td class='content import_product_data_error' style='width: 150px;'>Invalid: " . $error_msg . "</td>";
}
} else {
echo "<td class='content' style='width: 150px;'>Valid: " . $action . "</td>";
}
echo "</tr>";
}
?>
</table>
<!-- End Content --></td>
</tr>
<tr>
<td align="left" valign="middle"> </td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>