1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

<?php
    include 'config.php';

    // Check if the user is logged in

    if ((!isSet($_SESSION['loginname'])) || ($loggin <> '1')) {
        header("Location: login.php");
        exit;
    }

    //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

    date_default_timezone_set('Asia/Hong_Kong');

    error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING);
    ini_set('display_errors', 1);

    require_once "phpexcel/PHPExcel.php";
    //require_once 'func_tcpdf.php';

    $defaultFontface = 'Times New Roman';

    @set_time_limit(0);

    ini_set('post_max_size', '10M');
    ini_set('memory_limit', '1024M');
    $showimg = true;

    $objPHPexcel = PHPExcel_IOFactory::load('phpexcel/export_customer_warranty.xlsx');

    $objWorksheet = $objPHPexcel->getSheetByName('customer_warranty');


    $rownum = 2;

    //$sql = "select * from customer_warranty warranty, customer_title title where warranty.customer_title = title.customer_title_id and warranty.status = ? and warranty.deleted = ? order by warranty.createdate ASC";
    $sql = "select * from customer_warranty warranty where warranty.deleted = ? order by warranty.createdate ASC";
    if (!($sth = $dbh->prepare($sql))) {
        throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
    }

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

    while($row = $sth->fetch(PDO::FETCH_ASSOC)){
        $objWorksheet->getCell('A' . $rownum)->setValue($row{"series_code"});
        $objWorksheet->getCell('B' . $rownum)->setValue($row{"model_code"});
        $objWorksheet->getCell('C' . $rownum)->setValue($row{"serial_number"});
        if($row{"region"} == "HK"){
            $region = "香港";
        }else if($row{"region"} == "MO"){
            $region = "澳門";
        }else if($row{"region"} == "CN"){
            $region = "中國";
        }else{}
        $objWorksheet->getCell('D' . $rownum)->setValue($region);
        $objWorksheet->getCell('E' . $rownum)->setValue($row{"date_of_purchase"});
        $objWorksheet->getCell('F' . $rownum)->setValue($row{"merchant_name"});

        if($row{"free_mix_color"} == "1"){
            $free_mix_color = "有";
        }else{
            $free_mix_color = "無";
        }
        $objWorksheet->getCell('G' . $rownum)->setValue($free_mix_color);
        $objWorksheet->getCell('H' . $rownum)->setValue($row{"fixed_warranty_year"});
        $objWorksheet->getCell('I' . $rownum)->setValue($row{"extend_warranty_year"});

        $objWorksheet->getCell('J' . $rownum)->setValue($rsa->decrypt(base64_decode($row{"customer_name"})));
        $objWorksheet->getCell('K' . $rownum)->setValue($row{"gender"});
        $objWorksheet->getCell('L' . $rownum)->setValue($row{"age_group"});
        $objWorksheet->getCell('M' . $rownum)->setValue($rsa->decrypt(base64_decode($row{"email"})));
        $objWorksheet->getCell('N' . $rownum)->setValue($rsa->decrypt(base64_decode($row{"contact_number"})));
        $objWorksheet->getCell('O' . $rownum)->setValue($rsa->decrypt(base64_decode($row{"address"})));
        $objWorksheet->getCell('P' . $rownum)->setValue($row{"education_level"});
        $objWorksheet->getCell('Q' . $rownum)->setValue($row{"occupation"});
        $objWorksheet->getCell('R' . $rownum)->setValue($row{"monthly_income"});



        foreach (range('A', 'R') as $letter) {
            $objWorksheet->getStyle($letter . $rownum)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objWorksheet->getStyle($letter . $rownum)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objWorksheet->getStyle($letter . $rownum)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objWorksheet->getStyle($letter . $rownum)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        }

        $rownum++;
    }

    header('Content-Type: application/vnd.ms-excel');

    header('Content-Disposition: attachment;filename="export_customer_warranty.xlsx"');

    header('Cache-Control: max-age=0');

    $objPHPexcel->setActiveSheetIndex(0);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel2007');
    $objWriter->save('php://output');

    flush();

?>