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

	if (empty($_GET["invoice_id"]) || (int)$_GET["invoice_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/invoice_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);

	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++;
			}
			$sheet->setCellValue('A14', "");
			$sheet->setCellValue('A15', "");
			// $sheet->setCellValue('AN15', "顧客確認簽字:");
		} 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($invoice_info["type"] == "PENALTY"){
				$sheet->setCellValue('AX11', numberformat($invoice_info["amount"]));
			}*/
			
			
			
			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"]));
							
						$sheet->setCellValue('AX12', numberformat($dtl["invoice_dtl_amount"]));
						
					}
				}
			}
		}

// Redirect output to a client’s web browser (Excel5)
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment;filename="Invoice_' . $code . '.xlsx"');

		/*header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Invoice_' . $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 = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007');
		$objWriter->save('php://output');
		exit;
	} else {
		echo "<script>alert('找不到相關帳單。');history.back();</script>";
		exit;
	}