webadmin.php

/var/www/hkosl.com/innoutstorage/webadmin/output_order2b.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"];
        //kelvin, 20200803
        $total_remain = $order_info["total_discounted_price"]*$order_info["rent_month"]+$order_info["deposit"]+$order_info["total_order_product"]-$order_info["last_paid"]-$total_prepaid-$order_info["free_first_month_deduct"]-$order_info["free_2_month_deduct"]-$order_info["discount_amount"];
    }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"]-$order_info["discount_amount"];
        }
    }

    //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["discount_amount"])))
        ->setCellValue('AO33', "港幣 : 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"]-$order_info["discount_amount"]))
        //->setCellValue('AO31', "港幣 : -HKD$".numberformat($order_info["last_paid"]+$order_info["prepaid_amount"]+$order_info["total_order_product"]))
        ->setCellValue('AO34', "港幣 : -HKD$".numberformat($total_paid))
        //->setCellValue('I31', "(預繳: HKD$".numberformat($order_info["prepaid_amount"]).")")
        ->setCellValue('I34', "(預繳: 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('AO35', "港幣 : HKD$".numberformat($total_remain))
        ->setCellValue('A40', $order_info["remark"])
        ->setCellValue('R46', date("Y-m-d"))
        ->setCellValue('AT46', date("Y-m-d"));

    // Add a drawing to the 許可人簽署
    $objDrawing = new PHPExcel_Worksheet_Drawing();
    $objDrawing->setName('chop');
    $objDrawing->setDescription('chop');
    $objDrawing->setPath('./pdf_template/chop.jpg');
    $objDrawing->setOffsetX(0);    // setOffsetX works properly
    $objDrawing->setOffsetY(-20);  //setOffsetY has no effect
    $objDrawing->setCoordinates('AU43');
    $objDrawing->setHeight(60); // logo height
    $objDrawing->setWorksheet($sheet);

    if($order_info['renew'] == 1){
        $sheet->setCellValue('A39', "租金以".$paymethod2["name_tc"]."支付");
    }else{
        $sheet->setCellValue('A38', "按金以".$paymethod["name_tc"]."支付".($order_info["paymethod"] == "CREDIT_CARD"?" (並會扣除3%手續費)":""))
            ->setCellValue('A39', "租金以".$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);
    
    // Add a drawing to the header
    $objDrawing = new PHPExcel_Worksheet_Drawing();
    $objDrawing->setName('Logo');
    $objDrawing->setDescription('Logo');
    $objDrawing->setPath('./pdf_template/header.png');
    $objDrawing->setOffsetX(8);    // setOffsetX works properly
    $objDrawing->setOffsetY(500);  //setOffsetY has no effect
    $objDrawing->setCoordinates('A1');
    $objDrawing->setHeight(120); // logo height
    $objDrawing->setWorksheet($sheet);



    $sheet->getRowDimension('1')->setRowHeight(80);

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