webadmin.php

/var/www/hkosl.com/innoutstorage2019/webadmin/output_order2.php

<?php
    require_once('check_login.php');

    if(empty($_GET["order_id"]) || (int)$_GET["order_id"] <= 0){
        echo "<script>alert('請選擇正確的合約。'); history.back();</script>";
        exit;
    }

    /**
     * PHPExcel
     *
     * Copyright (C) 2006 - 2014 PHPExcel
     *
     * This library is free software; you can redistribute it and/or
     * modify it under the terms of the GNU Lesser General Public
     * License as published by the Free Software Foundation; either
     * version 2.1 of the License, or (at your option) any later version.
     *
     * This library is distributed in the hope that it will be useful,
     * but WITHOUT ANY WARRANTY; without even the implied warranty of
     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
     * Lesser General Public License for more details.
     *
     * You should have received a copy of the GNU Lesser General Public
     * License along with this library; if not, write to the Free Software
     * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
     *
     * @category   PHPExcel
     * @package    PHPExcel
     * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
     * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
     * @version    1.8.0, 2014-03-02
     */

    /** Error reporting */
    /*error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);*/
    date_default_timezone_set('Asia/Hong_Kong');

    ini_set('apc.cache_by_default', 0);

    if (PHP_SAPI == 'cli')
        die('This example should only be run from a Web Browser');

    /** Include PHPExcel */
    require_once dirname(__FILE__) . '/PHPExcel_1.8.0/Classes/PHPExcel.php';

    $reader = PHPExcel_IOFactory::createReader('Excel5'); // 讀取舊版 excel 檔案
    $PHPExcel = $reader->load("../webadmin/PHPExcel_1.8.0/contract_template2.xls"); // 檔案名稱
    $sheet = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)

    // Set Orientation, size and scaling
    $PHPExcel->setActiveSheetIndex(0);
    $PHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
    $PHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
    $PHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
    $PHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
    $PHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

    //get order data
    $order_id = (int)$_GET["order_id"];
    if(!empty($order_id)){
        $order_info = get_order($order_id);
        $order_room_info = get_order_room($order_id);
        $order_room_list = "";
        foreach($order_room_info as $order_room){
            $room_info = get_room($order_room["room_id"]);
            $order_room_list .= check_combine_room($room_info["code"]).", ";
        }
        $order_room_list = substr_replace($order_room_list, "", -2);

        //$customer_info = get_customer($order_info["customer_id"]);

        //->setCellValue('W13', $customer_info["customer_name"])

        $location_info = get_location($order_info["location_id"]);
        $Licensor_en = $location_info['contract_en'];
        $Licensor_tc = $location_info['contract_tc'];

        //紅酒倉
        if($order_info["location_id"] == 8){
            $sheet
                ->setCellValue('A5', "Wine Cellar License Agreement\n紅酒倉租用許可協議書");
        }

// Add some data
        $sheet
            ->setCellValue('V7', $order_info["code"])
            ->setCellValue('AS7', date("Y-m-d"))
            ->setCellValue('G9', $Licensor_en)
            ->setCellValue('G10', $Licensor_tc);

        if($order_info["customer_typeid"] == "PERSONAL"){
            $sheet
                ->setCellValue('A14', "Identification Number 身份證明號碼 :")
                ->setCellValue('S14', rsa_crypt($order_info["customer_identity_id"], 2))
                ->setCellValue('A15', "Name 姓名 :")
                ->setCellValue('H15', $order_info["customer_name"]);
        }else{
            $sheet
                ->setCellValue('A14', "Business Registration No 商業登記證號碼 :")
                ->setCellValue('U14', rsa_crypt($order_info["customer_identity_id"], 2))
                ->setCellValue('A15', "Company Name 公司名稱 :")
                ->setCellValue('N15', $order_info["customer_companyname"]);

            $sheet ->mergeCells('U14:BB14');
            $sheet->getStyle('U14')
                ->getAlignment()
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }

        /*$sheet ->mergeCells('N15:Z15');
        $sheet->getStyle('N15')
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);*/


        $paymethod = get_master_type_code("PAYMENT_METHOD", $order_info["paymethod"]);
        if(empty($order_info["paymethod2"]))
            $order_info["paymethod2"] = $order_info["paymethod"];

        $paymethod2 = get_master_type_code("PAYMENT_METHOD", $order_info["paymethod2"]);

        if($order_info["prepaid_month"] > 0){
            $total_prepaid = $order_info["prepaid_amount"]+$order_info["total_order_product"];

            $total_paid = $order_info["last_paid"]+$order_info["prepaid_amount"]+$order_info["total_order_product"];

            $total_remain = $order_info["total_discounted_price"]*$order_info["rent_month"]+$order_info["deposit"]+$order_info["total_order_product"]-$order_info["last_paid"]-$order_info["prepaid_amount"]-$order_info["free_first_month_deduct"]-$order_info["free_2_month_deduct"];
        }else{
            //prevent error
            if($order_info["prepaid_month"] == 0){
                $total_prepaid = 0;

                $total_paid = $order_info["last_paid"];
                $total_remain = $order_info["total_discounted_price"]*$order_info["rent_month"]+$order_info["deposit"]+$order_info["total_order_product"]-$order_info["last_paid"]-$order_info["prepaid_amount"]-$order_info["free_first_month_deduct"]-$order_info["free_2_month_deduct"];
            }
        }

        //unit change
        if($order_info["createdate"] > "2017-04-27 18:00:00"){
            $unit = "平方呎";
        }else{
            $unit = "立方呎";
        }

        $sheet
            ->setCellValue('I16', rsa_crypt($order_info["customer_address"], 2))
            //->setCellValue('N15', "=TEXT(".rsa_crypt($order_info["tel"], 2).",'0') ")
            ->setCellValue('N17', (string)rsa_crypt($order_info["customer_tel"], 2))
            //->setCellValueExplicit('N15', rsa_crypt($order_info["tel"], 2), PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValue('N18', rsa_crypt($order_info["customer_email"], 2))
            ->setCellValue('M21', $order_room_list)
            ->setCellValue('O22', $order_info["roughly_size"].$unit)
            ->setCellValue('N23', $order_info["startdate"]." 至 ".$order_info["enddate"])
            ->setCellValue('O24', $order_info["room_key"])
            ->setCellValue('Q27', "(原價 : HKD$".numberformat($order_info["total_retail_price"]).")")
            //->setCellValue('AD25', "租金以".$paymethod["name_tc"]."支付")
            ->setCellValue('Q26', "港幣 : HKD$".numberformat($order_info["total_discounted_price"])." x ".$order_info["rent_month"]."月")
            ->setCellValue('AO28', "港幣 : HKD$".numberformat($order_info["total_discounted_price"]*$order_info["rent_month"]))
            ->setCellValue('AO29', "港幣 : HKD$".numberformat($order_info["deposit"]))
            //->setCellValue('AD29', "按金以".$paymethod2["name_tc"]."支付")
            ->setCellValue('AO30', "港幣 : HKD$".numberformat($order_info["total_order_product"]))
            ->setCellValue('AO31', "港幣 : -HKD$".numberformat($order_info["free_first_month_deduct"]+$order_info["free_2_month_deduct"]))
            ->setCellValue('AO32', "港幣 : HKD$".numberformat(($order_info["total_discounted_price"]*$order_info["rent_month"])+$order_info["deposit"]+$order_info["total_order_product"]-$order_info["free_first_month_deduct"]-$order_info["free_2_month_deduct"]))
            //->setCellValue('AO31', "港幣 : -HKD$".numberformat($order_info["last_paid"]+$order_info["prepaid_amount"]+$order_info["total_order_product"]))
            ->setCellValue('AO33', "港幣 : -HKD$".numberformat($total_paid))
            //->setCellValue('I31', "(預繳: HKD$".numberformat($order_info["prepaid_amount"]).")")
            ->setCellValue('I33', "(預繳: HKD$".numberformat($total_prepaid).")")
            //->setCellValue('AO32', "港幣 : HKD$".numberformat(($order_info["total_discounted_price"]*$order_info["rent_month"])+$order_info["deposit"]+$order_info["total_order_product"]-$order_info["last_paid"]-$order_info["free_first_month_deduct"]-$order_info["prepaid_amount"]))
            ->setCellValue('AO34', "港幣 : HKD$".numberformat($total_remain))
            ->setCellValue('A39', $order_info["remark"])
            ->setCellValue('R45', date("Y-m-d"))
            ->setCellValue('AT45', date("Y-m-d"));

        if($order_info['renew'] == 1){
            $sheet->setCellValue('A38', "租金以".$paymethod2["name_tc"]."支付");
        }else{
            $sheet->setCellValue('A37', "按金以".$paymethod["name_tc"]."支付".($order_info["paymethod"] == "CREDIT_CARD"?" (並會扣除3%手續費)":""))
                ->setCellValue('A38', "租金以".$paymethod2["name_tc"]."支付");
        }

        if($order_info["customer_typeid"] == "BUSINESS"){
            //insert row for business user contact person name
            $sheet->insertNewRowBefore(16, 1);
            $sheet
                ->setCellValue('A16', "Contact Person 聯絡人 :")
                ->setCellValue('N16', $order_info["customer_name"]);
        }

        //add 2 row in head
        //$sheet->insertNewRowBefore(1, 2);
        $sheet->removeRow(1);

        //add column on left
        $sheet->insertNewColumnBefore('A', 1);


        $sheet->insertNewRowBefore(21,1);
        $sheet->setCellValue('B21', 'Customer I.D. 顧客編號:'. $order_info['customer_code']);
        $sheet->getStyle('B21')->getFont()->setSize(11);

// Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Contract_'.$order_info["code"].'.xls"');
        header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $objWriter->save('php://output');

        exit;
    }else{
        echo "<script>alert('找不到相關合約。');history.back();</script>";
        exit;
    }