| 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/hkosl.com/innoutstorage/webadmin/ |
Upload File : |
<?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');
set_time_limit(0);
ini_set('memory_limit', '512M');
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_template3_full.xls"); // 檔案名稱
/************** contract ***************/
$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'];
// 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"] - $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('Logo');
$objDrawing->setDescription('Logo');
$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);
/************** end contract ***************/
/************** invoice ***************/
//invoice id
$sql = 'select id from invoice where order_id = ? and deleted = ?';
$parameters = array($order_id, 0);
$_invoice = bind_pdo($sql, $parameters, 'selectone');
$_GET['invoice_id'] = $_invoice['id'];
$_GET["type"] = 'INVOICE';
$sheet = $PHPExcel->getSheet(1); // 讀取第一個工作表(編號從 1 開始)
// 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);
if (!empty($_GET["invoice_id"]) && !empty($_GET["type"])) {
$invoice_id = (int)$_GET["invoice_id"];
$invoice_type_info = get_master_type_code("PAYMENT_TYPE", $_GET["type"]);
$remark = "";
if ($_GET["type"] == "DEPOSIT") {
$invoice_info = get_deposit($invoice_id);
$balance = $invoice_info["deposit_balance"];
$code = $invoice_info["deposit_code"];
$order_info = get_order($invoice_info["order_id"]);
$customer_info = get_customer($invoice_info["customer_id"]);
// Add some data
$sheet
->setCellValue('A3', "按金發票")
->setCellValue('H4', $customer_info["code"])
->setCellValue('H6', rsa_crypt($order_info["customer_address"], 2))
->setCellValue('AX4', date("Y-m-d", strtotime($invoice_info["docdate"])))
->setCellValue('AX5', $code);
//->setCellValue('AU6', $invoice_info["duedate"]);
if ($order_info["customer_typeid"] == "PERSONAL") {
$sheet
->setCellValue('H5', $order_info["customer_name"]);
} else {
$sheet
->setCellValue('H5', $order_info["customer_companyname"]);
}
//$sheet->insertNewRowBefore(10, 1);
//$order_info = get_order($invoice_info["order_id"]);
$order_room_info = get_order_room($order_info["id"]);
$sheet->insertNewRowBefore(10, (1 + count($order_room_info)));
$sheet->getStyle('AZ10')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ10:BE10');
$sheet
->setCellValue('A10', "按金")
->setCellValue('AZ10', numberformat($invoice_info["deposit_amount"]));
$sheet->getStyle('AZ10')
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet
->getStyle("A10:BE10")->getFont()->setBold(false);
$sheet
->setCellValue('AZ' . (12 + count($order_room_info)), numberformat($invoice_info["deposit_amount"]));
$order_room_list = "";
$k = 1;
foreach ($order_room_info as $order_room) {
$room_info = get_room($order_room["room_id"]);
$master_room_info = get_master_room($room_info["master_room_id"]);
$order_room_list = check_combine_room($room_info["code"]) . " (" . $master_room_info["length"] . "'x" . $master_room_info["width"] . "'x" . $master_room_info["height"] . "')";
//$order_room_list = substr_replace($order_room_list ,"",-2);
if ($k == 1) {
$sheet
->setCellValue('B' . (10 + $k), "合約編號: " . $order_info["code"] . ", 租用單位: " . $order_room_list);
} else {
$sheet
->setCellValue('V' . (10 + $k), $order_room_list);
}
$sheet
->getStyle("A" . (10 + $k) . ":AZ" . (10 + $k))->getFont()->setBold(false);
$k++;
}
} else {
$invoice_info = get_invoice($invoice_id);
$balance = $invoice_info["invoice_balance"];
$code = $invoice_info["invoice_code"];
$order_info = get_order($invoice_info["order_id"]);
$customer_info = get_customer($invoice_info["customer_id"]);
$customer_paid = $invoice_info["amount"] - $invoice_info["balance"];
$customer_unpaid = $invoice_info["balance"];
// Add some data
$sheet
->setCellValue('A3', "租金及其他發票")
->setCellValue('H4', $customer_info["code"])
->setCellValue('H6', rsa_crypt($order_info["customer_address"], 2))
->setCellValue('AX4', date("Y-m-d", strtotime($invoice_info["docdate"])))
->setCellValue('AX5', $code)
//->setCellValue('AS6', $invoice_info["duedate"])
->setCellValue('AX11', numberformat($order_info["total_discounted_price"] * $order_info["rent_month"] + $order_info["total_order_product"]))
->setCellValue('AX12', "- " . numberformat($order_info["last_paid"]))
->setCellValue('AX13', numberformat($invoice_info["amount"]))
->setCellValue('A17', $invoice_info["remark"]);
if ($order_info["id"] == 1007) {
$sheet->setCellValue('AX11', numberformat($order_info["total_discounted_price"] * $order_info["rent_month"] + $order_info["total_order_product"] - 5418));
}
if ($order_info["customer_typeid"] == "PERSONAL") {
$sheet
->setCellValue('H5', $order_info["customer_name"]);
} else {
$sheet
->setCellValue('H5', $order_info["customer_companyname"]);
}
$sql = "select *,
invoice.id as invoice_id,
invoice.status as invoice_status,
invoice.code as invoice_code,
invoice.docdate as invoice_docdate,
invoice.duedate as invoice_duedate,
invoice.amount as invoice_amount,
invoice_dtl.amount as invoice_dtl_amount
from `invoice` invoice
INNER JOIN `invoice_dtl` invoice_dtl ON invoice_dtl.invoice_id = invoice.id
where invoice.id = ? and invoice.deleted = ? order by FIELD(invoice_dtl.type, 'PENALTY','ORDER_PRODUCT', 'RENT' ), invoice_dtl.month DESC";
$parameters = array($invoice_id, 0);
$invoice_dtl_info = bind_pdo($sql, $parameters, "selectall");
//$invoice_dtl_info = get_invoice_detail($invoice_id);
if (!empty($invoice_dtl_info)) {
//$k = 0;
foreach ($invoice_dtl_info as $dtl) {
if ($dtl["type"] == "RENT") {
$i = $dtl["month"];
$customer_paid -= $dtl["invoice_dtl_amount"];
$order_info = get_order($invoice_info["order_id"]);
/*$start_date = new DateTime($order_info["warehousing_date"]);
$start_date->modify('+' . ($i - 1) . ' month');
$end_date = new DateTime($order_info["warehousing_date"]);
if ($order_info["rent_month"] == $i) {
$end_date->modify('+' . $i . ' month - 1 day');
} else {
$end_date->modify('+' . $i . ' month');
}*/
$result = get_rent_date_from_to($order_info["warehousing_date"], $dtl["month"]);
$start_date = $result["start"];
$end_date = $result["end"];
$order_room_info = get_order_room($order_info["id"]);
$sheet->insertNewRowBefore(10, (2 + count($order_room_info)));
$sheet->getStyle('AZ10')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ10:BE10');
$sheet
->getStyle("A10:BE10")->getFont()->setBold(false);
$sheet->getStyle('AZ10')
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet
->setCellValue('A10', "租金")
//->setCellValue('AZ10', numberformat($dtl["invoice_dtl_amount"]));
->setCellValue('AZ10', numberformat($order_info["total_discounted_price"]));
if ($order_info["id"] == 1007) {
$sheet->setCellValue('AZ10', numberformat($dtl["invoice_dtl_amount"]));
}
$sheet
->setCellValue('B11', "日期: " . $start_date . " 至 " . $end_date . "");
$sheet
->getStyle("A11:AZ11")->getFont()->setBold(false);
$order_room_list = "";
$k = 1;
foreach ($order_room_info as $order_room) {
$room_info = get_room($order_room["room_id"]);
$master_room_info = get_master_room($room_info["master_room_id"]);
$order_room_list = check_combine_room($room_info["code"]) . " (" . $master_room_info["length"] . "'x" . $master_room_info["width"] . "'x" . $master_room_info["height"] . "')";
//$order_room_list = substr_replace($order_room_list ,"",-2);
if ($k == 1) {
$sheet
->setCellValue('B' . (11 + $k), "合約編號: " . $order_info["code"] . ", 租用單位: " . $order_room_list);
} else {
$sheet
->setCellValue('V' . (11 + $k), $order_room_list);
}
$sheet
->getStyle("B" . (11 + $k) . ":AZ" . (11 + $k))->getFont()->setBold(false);
$k++;
}
}
if ($dtl["type"] == "ORDER_PRODUCT") {
$order_product_balance = 0;
if ($customer_paid > 0) {
if ($customer_paid >= $dtl["invoice_dtl_amount"]) {
$customer_paid -= $dtl["invoice_dtl_amount"];
$order_product_balance = 0;
}
} else {
$customer_paid = 0;
$order_product_balance = $dtl["invoice_dtl_amount"] - $customer_paid;
}
$product_info = get_product($dtl["order_dtl_id"]);
$sheet->insertNewRowBefore(10, 2);
$sheet->setCellValue('A10', "其他");
$sheet->getStyle('AZ10')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ10:BE10');
$sheet
->getStyle("A10:BE10")->getFont()->setBold(false);
$sheet
->setCellValue('B11', $product_info["name_tc"])
->setCellValue('AZ10', numberformat($dtl["invoice_dtl_amount"]));
$sheet
->getStyle("A11:BE11")->getFont()->setBold(false);
}
if ($dtl["type"] == "PENALTY") {
//$customer_paid = 0;
$penalty_balance = $dtl["invoice_dtl_amount"] - $customer_paid;
if ($customer_paid > 0) {
if ($customer_paid >= $dtl["invoice_dtl_amount"]) {
$customer_paid -= $dtl["invoice_dtl_amount"];
$penalty_balance = 0;
}
}
if (!empty($dtl["remark"])) {
$remark = " (" . $dtl["remark"] . ")";
} else {
$remark = "";
}
$sheet->insertNewRowBefore(10, 1);
$sheet->getStyle('AZ10')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ10:BE10');
$sheet
->getStyle("A10:BE10")->getFont()->setBold(false);
$sheet
->setCellValue('A10', "罰款" . $remark)
->setCellValue('AZ10', numberformat($dtl["invoice_dtl_amount"]));
}
}
}
}
}
/************** end invoice ***************/
/************** payment ***************/
//payment id
$sql = 'select payment_id from payment_dtl where payable_type = ? and payable_id = ? and status = ? and deleted = ? order by docdate DESC';
$parameters = array('INVOICE', $invoice_id, 'PAID', 0);
$_payment = bind_pdo($sql, $parameters, 'selectone');
$_GET['payment_id'] = $_payment['payment_id'];
$_GET["type"] = 'INVOICE';
$sheet = $PHPExcel->getSheet(2); // 讀取第一個工作表(編號從 2 開始)
// 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);
$payment_info = get_payment_by_id((int)$_GET["payment_id"]);
//$paymethod_info = get_master_type_code("PAYMENT_METHOD", $payment_info["paymethod"]);
$customer_info = get_customer($payment_info["customer_id"]);
$payment_dtl_info = get_payment_dtl((int)$_GET["payment_id"]);
$thistype_total_amt = 0;
$total_amt = 0;
$last_paid = 0;
$total_ori_amt = 0;
foreach ($payment_dtl_info as $key => $payment_dtl) {
$total_amt += $payment_dtl["amount"];
if ($payment_dtl["payable_type"] == $_GET["type"]) {
$thistype_total_amt += $payment_dtl["amount"];
if ($payment_dtl["payable_type"] == "INVOICE") {
$_info = get_invoice($payment_dtl["payable_id"]);
if (!empty($_info)) {
$_order_info = get_order($_info["order_id"]);
$last_paid = $_order_info["last_paid"];
$total_ori_amt = $_order_info["total_discounted_price"] * $_order_info["rent_month"] + $_order_info["total_order_product"];
}
}
}
}
$docdate = new DateTime($payment_info["docdate"]);
// Add some data
if ($_GET["type"] == "DEPOSIT") {
$sheet->setCellValue('A3', "按金付款收據");
} else if ($_GET["type"] == "INVOICE") {
$sheet->setCellValue('A3', "正式收據");
}
$sheet
->setCellValue('H4', $customer_info["code"])
->setCellValue('H5', $customer_info["customer_name"])
->setCellValue('H6', rsa_crypt($customer_info["address"], 2))
->setCellValue('AW4', $payment_info["code"])
->setCellValue('AW5', $docdate->format("Y-m-d"))
->setCellValue('AX12', numberformat($total_amt));
if ($_GET["type"] == "ALL" || $_GET["type"] == "DEPOSIT") {
$this_paymethod_info = get_master_type_code("PAYMENT_METHOD", $payment_info["paymethod"]);
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"]);
if ($payment_info["paymethod"] == "CHEQUE") {
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"] . " - " . $payment_info["bank_name"]);
$sheet->setCellValue('AW7', $payment_info["cheque_num"]);
}
if ($payment_info["paymethod"] == "OTHER" || $payment_info["paymethod"] == "BANKIN") {
$sheet->setCellValue('AW7', $payment_info["paymethod_other"]);
}
} else if ($_GET["type"] == "INVOICE") {
$this_paymethod_info = get_master_type_code("PAYMENT_METHOD", $payment_info["paymethod2"]);
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"]);
if ($payment_info["paymethod2"] == "CHEQUE") {
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"] . " - " . $payment_info["bank_name2"]);
$sheet->setCellValue('AW7', $payment_info["cheque_num2"]);
}
if ($payment_info["paymethod2"] == "OTHER" || $payment_info["paymethod2"] == "BANKIN") {
$sheet->setCellValue('AW7', $payment_info["paymethod_other2"]);
}
$sheet
->setCellValue('A12', '')
->setCellValue('AX12', numberformat($total_ori_amt))
->setCellValue('A13', '已付金額')
->setCellValue('AX13', "- " . numberformat($last_paid))
->setCellValue('A14', '總金額')
->setCellValue('AX14', numberformat($thistype_total_amt));
}
/*if (count($payment_dtl_info) > 1 || $payment_info["payfor"] == "ALL" || $_GET["type"] == "INVOICE") {
$this_paymethod_info = get_master_type_code("PAYMENT_METHOD", $payment_info["paymethod2"]);
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"]);
if ($payment_info["paymethod2"] == "CHEQUE") {
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"] . " - " . $payment_info["bank_name2"]);
$sheet->setCellValue('AW7', $payment_info["cheque_num2"]);
}
if ($payment_info["paymethod2"] == "OTHER" || $payment_info["paymethod2"] == "BANKIN") {
$sheet->setCellValue('AW7', $payment_info["paymethod_other2"]);
}
} else {
$this_paymethod_info = get_master_type_code("PAYMENT_METHOD", $payment_info["paymethod"]);
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"]);
if ($payment_info["paymethod"] == "CHEQUE") {
$sheet->setCellValue('AW6', $this_paymethod_info["name_tc"] . " - " . $payment_info["bank_name"]);
$sheet->setCellValue('AW7', $payment_info["cheque_num"]);
}
if ($payment_info["paymethod"] == "OTHER" || $payment_info["paymethod"] == "BANKIN") {
$sheet->setCellValue('AW7', $payment_info["paymethod_other"]);
}
}*/
$sheet->getStyle('H6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
if (!empty($payment_dtl_info)) {
if ($_GET["type"] == "ALL") {
//override
$sheet->setCellValue('A3', "正式收據");
$sheet->setCellValue('AZ12', numberformat($payment_info["amount"]));
}
foreach ($payment_dtl_info as $dtl) {
/*if($dtl["payable_type"] != $_GET["type"])
continue;*/
$type_info = get_master_type_code("PAYMENT_TYPE", $dtl["payable_type"]);
if ($dtl["payable_type"] == "DEPOSIT") {
$payable_info = get_deposit($dtl["payable_id"]);
$payable_code = $payable_info["deposit_code"];
} else {
$payable_info = get_invoice($dtl["payable_id"]);
$payable_code = $payable_info["invoice_code"];
}
$sheet->setCellValue('H4', $customer_info["code"]);
if ($_GET["type"] == "ALL" && count($payment_dtl_info) > 1) {
$sheet->insertNewRowBefore(11, 2);
$sheet->getStyle('AZ11')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ11:BE11');
$sheet->getStyle("AZ11:BE11")->getFont()->setBold(false);
$sheet->getStyle("AZ11:BE11")->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
if ($dtl["payable_type"] == "DEPOSIT") {
$sheet
->setCellValue('A11', "按金 (發票編號: " . $payable_code . ")")
->setCellValue('AZ11', numberformat($payable_info["amount"]))
->getStyle("A11")->getFont()->setBold(false);;
} else {
$sheet
->setCellValue('A11', "租金 (發票編號: " . $payable_code . ")")
->setCellValue('AZ11', numberformat($payable_info["amount"]))
->getStyle("A11")->getFont()->setBold(false);;
$sql = "select *,
invoice.id as invoice_id,
invoice.status as invoice_status,
invoice.code as invoice_code,
invoice.docdate as invoice_docdate,
invoice.duedate as invoice_duedate,
invoice.amount as invoice_amount,
invoice_dtl.amount as invoice_dtl_amount
from `invoice` invoice
INNER JOIN `invoice_dtl` invoice_dtl ON invoice_dtl.invoice_id = invoice.id
where invoice.id = ? and invoice.deleted = ? order by FIELD(invoice_dtl.type, 'PENALTY','ORDER_PRODUCT', 'RENT' ), invoice_dtl.month DESC";
$parameters = array($dtl["payable_id"], 0);
$invoice_dtl_info = bind_pdo($sql, $parameters, "selectall");
$has_rent = false;
$first_date = "";
$last_date = "";
$first_rent = false;
if (!empty($invoice_dtl_info)) {
foreach ($invoice_dtl_info as $dtl2) {
if ($dtl2["type"] == "RENT") {
$has_rent = true;
$result = get_rent_date_from_to($order_info["warehousing_date"], $dtl2["month"]);
$start_date = $result["start"];
$end_date = $result["end"];
//order by desc
if (!$first_rent) {
//$first_date = $start_date;
$last_date = $end_date;
$first_rent = true;
}
$first_date = $start_date;
//$last_date = $end_date;
}
}
}
}
$order_info = get_order($payable_info["order_id"]);
$order_room_info = get_order_room($order_info["id"]);
//$sheet->setCellValue('B12', "合約編號: " . $order_info["code"]);
$order_room_list = "";
$k = 1;
foreach ($order_room_info as $order_room) {
$room_info = get_room($order_room["room_id"]);
$master_room_info = get_master_room($room_info["master_room_id"]);
$order_room_list = check_combine_room($room_info["code"]) . " (" . $master_room_info["length"] . "'x" . $master_room_info["width"] . "'x" . $master_room_info["height"] . "')";
//$order_room_list = substr_replace($order_room_list ,"",-2);
if ($k == 1) {
$sheet
->setCellValue('B' . (12), "合約編號: " . $order_info["code"] . ", 租用單位: " . $order_room_list)
->getStyle("B12")->getFont()->setBold(false);
} else {
$sheet->insertNewRowBefore(13, 1);
$sheet
->setCellValue("V13", $order_room_list)
->getStyle("V13")->getFont()->setBold(false);
}
$k++;
}
if ($has_rent) {
$sheet->insertNewRowBefore(12, 1);
$sheet
->setCellValue('B12', "日期: " . $first_date . " 至 " . $last_date . "");
$sheet
->getStyle("A12:AZ12")->getFont()->setBold(false);
}
} else {
if ($_GET["type"] == "DEPOSIT" && $dtl["payable_type"] == "DEPOSIT") {
$sheet->insertNewRowBefore(11, 2);
$sheet->getStyle('AZ11')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ11:BE11');
$sheet->getStyle("AZ11:BE11")->getFont()->setBold(false);
$sheet->getStyle("AZ11:BE11")->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet
->setCellValue('A11', "按金 (發票編號: " . $payable_code . ")")
->setCellValue('AZ11', numberformat($payable_info["amount"]))
->getStyle("A11")->getFont()->setBold(false);
$order_info = get_order($payable_info["order_id"]);
$order_room_info = get_order_room($order_info["id"]);
$order_room_list = "";
$k = 1;
foreach ($order_room_info as $order_room) {
$room_info = get_room($order_room["room_id"]);
$master_room_info = get_master_room($room_info["master_room_id"]);
$order_room_list = check_combine_room($room_info["code"]) . " (" . $master_room_info["length"] . "'x" . $master_room_info["width"] . "'x" . $master_room_info["height"] . "')";
//$order_room_list = substr_replace($order_room_list ,"",-2);
if ($k == 1) {
$sheet
->setCellValue('B' . (12), "合約編號: " . $order_info["code"] . ", 租用單位: " . $order_room_list);
} else {
$sheet->insertNewRowBefore(13, 1);
$sheet
->setCellValue("V13", $order_room_list)
->getStyle("V13")->getFont()->setBold(false);
}
$k++;
}
} else if ($_GET["type"] == "INVOICE" && $dtl["payable_type"] == "INVOICE") {
$sql = "select *,
invoice.id as invoice_id,
invoice.status as invoice_status,
invoice.code as invoice_code,
invoice.docdate as invoice_docdate,
invoice.duedate as invoice_duedate,
invoice.amount as invoice_amount,
invoice_dtl.amount as invoice_dtl_amount
from `invoice` invoice
INNER JOIN `invoice_dtl` invoice_dtl ON invoice_dtl.invoice_id = invoice.id
where invoice.id = ? and invoice.deleted = ? order by FIELD(invoice_dtl.type, 'PENALTY','ORDER_PRODUCT', 'RENT' ), invoice_dtl.month DESC";
$parameters = array($dtl["payable_id"], 0);
$invoice_dtl_info = bind_pdo($sql, $parameters, "selectall");
if (!empty($invoice_dtl_info)) {
//$k = 0;
foreach ($invoice_dtl_info as $dtl2) {
if ($dtl2["type"] == "RENT") {
$i = $dtl2["month"];
//$customer_paid -= $dtl2["invoice_dtl_amount"];
$order_info = get_order($payable_info["order_id"]);
/*$start_date = new DateTime($order_info["warehousing_date"]);
$start_date->modify('+' . ($i - 1) . ' month');
$end_date = new DateTime($order_info["warehousing_date"]);
if ($order_info["rent_month"] == $i) {
$end_date->modify('+' . $i . ' month - 1 day');
} else {
$end_date->modify('+' . $i . ' month');
}*/
$result = get_rent_date_from_to($order_info["warehousing_date"], $dtl2["month"]);
$start_date = $result["start"];
$end_date = $result["end"];
$order_room_info = get_order_room($order_info["id"]);
$sheet->insertNewRowBefore(11, (2 + count($order_room_info)));
$sheet->getStyle('AZ11')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ11:BE11');
$sheet
->getStyle("A11:BE11")->getFont()->setBold(false);
$sheet->getStyle('AZ11')
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet
->setCellValue('A11', "租金 (發票編號: " . $payable_code . ")")
->setCellValue('AZ11', numberformat($order_info["total_discounted_price"]));
$sheet
->setCellValue('B12', "日期: " . $start_date . " 至 " . $end_date . "");
$sheet
->getStyle("A12:AZ12")->getFont()->setBold(false);
$order_room_list = "";
$k = 1;
foreach ($order_room_info as $order_room) {
$room_info = get_room($order_room["room_id"]);
$master_room_info = get_master_room($room_info["master_room_id"]);
$order_room_list = check_combine_room($room_info["code"]) . " (" . $master_room_info["length"] . "'x" . $master_room_info["width"] . "'x" . $master_room_info["height"] . "')";
//$order_room_list = substr_replace($order_room_list ,"",-2);
if ($k == 1) {
$sheet
->setCellValue('B' . (12 + $k), "合約編號: " . $order_info["code"] . ", 租用單位: " . $order_room_list);
} else {
$sheet
->setCellValue('V' . (12 + $k), $order_room_list);
}
$sheet
->getStyle("B" . (12 + $k) . ":AZ" . (12 + $k))->getFont()->setBold(false);
$k++;
}
}
if ($dtl["type"] == "ORDER_PRODUCT") {
/*$order_product_balance = 0;
if ($customer_paid > 0) {
if ($customer_paid >= $dtl2["invoice_dtl_amount"]) {
$customer_paid -= $dtl2["invoice_dtl_amount"];
$order_product_balance = 0;
}
} else {
$customer_paid = 0;
$order_product_balance = $dtl2["invoice_dtl_amount"] - $customer_paid;
}*/
$product_info = get_product($dtl2["order_dtl_id"]);
$sheet->insertNewRowBefore(11, 2);
$sheet->setCellValue('A11', "其他 (發票編號: " . $payable_code . ")");
$sheet->getStyle('AZ11')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ11:BE11');
$sheet
->getStyle("A11:BE11")->getFont()->setBold(false);
$sheet
->setCellValue('B11', $product_info["name_tc"])
->setCellValue('AZ11', numberformat($dtl2["invoice_dtl_amount"]));
$sheet
->getStyle("A11:BE11")->getFont()->setBold(false);
}
if ($dtl2["type"] == "PENALTY") {
//$customer_paid = 0;
/*$penalty_balance = $dtl2["invoice_dtl_amount"] - $customer_paid;
if ($customer_paid > 0) {
if ($customer_paid >= $dtl2["invoice_dtl_amount"]) {
$customer_paid -= $dtl2["invoice_dtl_amount"];
$penalty_balance = 0;
}
}*/
if (!empty($dtl2["remark"])) {
$remark = " (" . $dtl2["remark"] . ")";
} else {
$remark = "";
}
$sheet->insertNewRowBefore(11, 1);
$sheet->getStyle('AZ11')->getNumberFormat()->setFormatCode('0.00');
$sheet->mergeCells('AZ11:BE11');
$sheet
->getStyle("A11:BE11")->getFont()->setBold(false);
$sheet
->setCellValue('A11', "罰款 " . $remark)
->setCellValue('AZ11', numberformat($dtl2["invoice_dtl_amount"]));
}
}
}
}
}
//$sheet->setCellValue('B12', "合約編號: " . $order_info["code"] . ", 租用單位: " . $order_room_list);
$sheet->getStyle("B12")->getFont()->setBold(false);
}
}
/************** end payment ***************/
for($i=0; $i<3; $i++) {
if ($i == 0) {
$h = 120;
$rh = 80;
} else {
$h = 110;
$rh = 70;
}
$PHPExcel->setActiveSheetIndex($i);
// 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($h); // logo height
$objDrawing->setWorksheet($PHPExcel->getActiveSheet());
$PHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight($rh);
}
$PHPExcel->setActiveSheetIndex(0);
// 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;
}