webadmin.php

/var/www/(Del)eizo.hkosl.com/global/webadmin/import_customer_warranty_preview.php

<?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">&nbsp;</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>
                        &nbsp;Save&nbsp;&nbsp;</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>
                        &nbsp;Back&nbsp;&nbsp;</a></td>
                <td>&nbsp;</td>
            </tr>
        </table>
    </td>
</tr>
<tr>
    <td>
                <span style="float:left;" class="pagetitletxt">&nbsp;&nbsp;<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;">&nbsp;&nbsp;</div>
        : Data has changed
        <div style="width: 20px; height: 20px;background: #F78181;display: inline-block;">&nbsp;&nbsp;</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">&nbsp;</td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>