<?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_template.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"];

			$customer_info = get_customer($invoice_info["customer_id"]);

			// Add some data
			$sheet
				->setCellValue('A3', "按金發票")
				->setCellValue('H4', $customer_info["code"])
				->setCellValue('H5', $customer_info["customer_name"])
				->setCellValue('H6', rsa_crypt($customer_info["address"], 2))
				->setCellValue('AS4', date("Y-m-d", strtotime($invoice_info["docdate"])))
				->setCellValue('AS5', $code)
				->setCellValue('AU6', $invoice_info["duedate"]);



			//$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('AV10')->getNumberFormat()->setFormatCode('0.00');

			$sheet->mergeCells('AV10:BA10');

			$sheet
				->setCellValue('A10', "按金")
				->setCellValue('AV10', numberformat($invoice_info["deposit_amount"]));

			$sheet
				->getStyle("A10:AV10")->getFont()->setBold(false);

			$sheet
				->setCellValue('AV'.(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 = $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('A'.(10+$k), "合約編號: ".$order_info["code"].", 租用單位: ".$order_room_list);
				}else{
					$sheet
						->setCellValue('U'.(10+$k), $order_room_list);
				}

				$sheet
					->getStyle("A".(10+$k).":AV".(10+$k))->getFont()->setBold(false);

				$k++;

			}



		}else{
			$invoice_info = get_invoice($invoice_id);
			$balance = $invoice_info["invoice_balance"];
			$code = $invoice_info["invoice_code"];

			$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('H5', $customer_info["customer_name"])
				->setCellValue('H6', rsa_crypt($customer_info["address"], 2))
				->setCellValue('AS4', date("Y-m-d", strtotime($invoice_info["docdate"])))
				->setCellValue('AS5', $code)
				->setCellValue('AS6', $invoice_info["duedate"])
				->setCellValue('AV11', $invoice_info["amount"])
				->setCellValue('A15', $invoice_info["remark"]);
			

			$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','ORDER_PRODUCT','PENALTY','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');
						}


						$order_room_info = get_order_room($order_info["id"]);

						$sheet->insertNewRowBefore(10, (2+count($order_room_info)));
						$sheet->getStyle('AV10')->getNumberFormat()->setFormatCode('0.00');
						$sheet->mergeCells('AV10:BA10');
						$sheet
							->getStyle("A10:AV10")->getFont()->setBold(false);

						$sheet
							->setCellValue('A10', "租金")
							->setCellValue('AV10', numberformat($dtl["invoice_dtl_amount"]));

						$sheet
							->setCellValue('A11', "日期: ".$start_date->format('Y-m-d')." 至 ".$end_date->format('Y-m-d')."");
						$sheet
							->getStyle("A11:AV11")->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 = $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('A'.(11+$k), "合約編號: ".$order_info["code"].", 租用單位: ".$order_room_list);
							}else{
								$sheet
									->setCellValue('U'.(11+$k), $order_room_list);
							}

							$sheet
								->getStyle("A".(11+$k).":AV".(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, 1);
						$sheet->getStyle('AV10')->getNumberFormat()->setFormatCode('0.00');
						$sheet->mergeCells('AV10:BA10');
						$sheet
							->getStyle("A10:AV10")->getFont()->setBold(false);

						$sheet
							->setCellValue('A10', $product_info["name_tc"])
							->setCellValue('AV10', numberformat($dtl["invoice_dtl_amount"]));


					}

					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('AV10')->getNumberFormat()->setFormatCode('0.00');
						$sheet->mergeCells('AV10:BA10');
						$sheet
							->getStyle("A10:AV10")->getFont()->setBold(false);

						$sheet
							->setCellValue('A10', "罰款".$remark)
							->setCellValue('AV10', numberformat($dtl["invoice_dtl_amount"]));

					}

				}


			}

		}





// Redirect output to a client’s web browser (Excel5)
		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->save('php://output');
		exit;
	}else{
		echo "<script>alert('找不到相關帳單。');history.back();</script>";
		exit;
	}