| 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');
$_GET["payment_id"]=513;
$_GET["type"]="INVOICE";
if (empty($_GET["payment_id"]) || (int)$_GET["payment_id"] <= 0 || empty($_GET["type"])) {
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 檔案
//require_once dirname(__FILE__) . '/PHPExcel_1.8.0/Classes/PHPExcel/Reader/Excel2007.php';
//$reader = new PHPExcel_Reader_Excel2007;
$PHPExcel = $reader->load("../webadmin/PHPExcel_1.8.0/payment_template3.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);
$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;
foreach ($payment_dtl_info as $key => $payment_dtl) {
if ($payment_dtl["payable_type"] == $_GET["type"]) {
$thistype_total_amt += $payment_dtl["amount"];
}
}
$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('AZ12', numberformat($thistype_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"]);
}
}
/*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"]);
}
}*/
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($dtl2["invoice_dtl_amount"]));
$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);
}
}
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Payment_' . $payment_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;