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

	if(!validateDate($_GET["startdate"], "Y-m-d") || !validateDate($_GET["enddate"], "Y-m-d") || ($_GET["startdate"] > $_GET["enddate"])){
		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', 0);
	ini_set('display_startup_errors', 0);*/
	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/Rental_Report.xls"); // 檔案名稱
	$sheet    = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)

	//deposit and non order invoice in docdate ASC
	$sql        = "select 'deposit' as _table_name, id, docdate from deposit where docdate between ? and  ? and deleted = ? UNION select 'invoice' as _table_name, id, docdate from invoice where docdate between ? and  ? and deleted = ? order by docdate ASC";
	$parameters = array($_GET["startdate"], $_GET["enddate"], 0, $_GET["startdate"], $_GET["enddate"], 0);
	$result = bind_pdo($sql, $parameters, "selectall");

	$row_num = 2;

	foreach($result as $detail){
		if($detail["_table_name"] == "deposit"){
			//deposit and display order info
			$deposit_id = $detail["id"];
			$deposit_info = get_deposit($deposit_id);
			$order_info = get_order($deposit_info["order_id"]);

			$order_room_info = get_order_room($deposit_info["order_id"]);

			$order_room_list = "";
			foreach($order_room_info as $order_room){
				$room_info = get_room($order_room["room_id"]);
				$order_room_list .= $room_info["code"].", ";
			}

			$order_room_list = substr_replace($order_room_list ,"",-2);

			$type_info = get_master_type_code("PAYMENT_TYPE", "DEPOSIT");

			$sql              = "select * from payment_dtl where payable_type = ? and status = ? and deleted = ? and payable_id = ? order by docdate ASC";
			$parameters       = array("DEPOSIT", "PAID", 0, $deposit_id);
			$payment_dtl_info = bind_pdo($sql, $parameters, "selectone");

			if(!empty($payment_dtl_info)){
				$payment_info = get_payment_by_id($payment_dtl_info["payment_id"]);
				$paymenthod_info = get_master_type_code("PAYMENT_METHOD", $payment_info["paymethod"]);

				$payment_info_code = $payment_info["code"];
				$payment_info_docdate = $payment_info["docdate"];
				$payment_info_amount = $payment_dtl_info["amount"];
				$payment_info_paymethod = $paymenthod_info["name_tc"];
			}else{
				$payment_info_code = "";
				$payment_info_docdate = "";
				$payment_info_amount = "";
				$payment_info_paymethod = "";
			}

			$sheet
				->setCellValue('A'.$row_num, $order_info["code"])
				->setCellValue('B'.$row_num, $order_info["docdate"])
				->setCellValue('C'.$row_num, $order_room_list)
				->setCellValue('D'.$row_num, $order_info["total_discounted_price"])
				->setCellValue('E'.$row_num, $order_info["rent_month"])
				->setCellValue('F'.$row_num, $order_info["startdate"])
				->setCellValue('G'.$row_num, $order_info["enddate"])
				->setCellValue('H'.$row_num, $payment_info_code)
				->setCellValue('I'.$row_num, $payment_info_docdate)
				->setCellValue('J'.$row_num, $payment_info_amount)
				->setCellValue('K'.$row_num, $payment_info_paymethod)
				->setCellValue('L'.$row_num, $type_info["name_tc"])
				->setCellValue('M'.$row_num, $deposit_info["deposit_code"])
				->setCellValue('R'.$row_num, ($deposit_info["deposit_amount"]-$deposit_info["deposit_balance"]));

			$row_num++;

		}else{
			//invoice and display order info
			$invoice_id = $detail["id"];
			$invoice_info = get_invoice($invoice_id);

			$type_info = get_master_type_code("PAYMENT_TYPE", "INVOICE");
			$sheet
				->setCellValue('L' . $row_num, $type_info["name_tc"]);

			if(!empty($invoice_info["order_id"])) {
				$order_info = get_order($invoice_info["order_id"]);

				$order_room_info = get_order_room($invoice_info["order_id"]);

				$order_room_list = "";
				foreach ($order_room_info as $order_room) {
					$room_info = get_room($order_room["room_id"]);
					$order_room_list .= $room_info["code"] . ", ";
				}

				$order_room_list = substr_replace($order_room_list, "", -2);

				$sheet
					->setCellValue('A' . $row_num, $order_info["code"])
					->setCellValue('B' . $row_num, $order_info["docdate"])
					->setCellValue('C' . $row_num, $order_room_list)
					->setCellValue('D' . $row_num, $order_info["total_discounted_price"])
					->setCellValue('E' . $row_num, $order_info["rent_month"])
					->setCellValue('F' . $row_num, $order_info["startdate"])
					->setCellValue('G' . $row_num, $order_info["enddate"]);
			}

			//many more than 1 payment for same invoice
			$sql              = "select * from payment_dtl where payable_type = ? and status = ? and deleted = ? and payable_id = ? order by docdate ASC";
			$parameters       = array("INVOICE", "PAID", 0, $invoice_id);
			$payment_dtl_info = bind_pdo($sql, $parameters, "selectall");

			if(!empty($payment_dtl_info)){

				foreach($payment_dtl_info as $dtl){
					$payment_info = get_payment_by_id($dtl["payment_id"]);
					$paymenthod_info = get_master_type_code("PAYMENT_METHOD", $payment_info["paymethod"]);

					$sheet
						->setCellValue('H'.$row_num, $payment_info["code"])
						->setCellValue('I'.$row_num, $payment_info["docdate"])
						->setCellValue('J'.$row_num, $dtl["amount"])
						->setCellValue('K'.$row_num, $paymenthod_info["name_tc"]);

				}

			}else{
				$sheet
					->setCellValue('H'.$row_num, "")
					->setCellValue('I'.$row_num, "")
					->setCellValue('J'.$row_num, "")
					->setCellValue('K'.$row_num, "");
			}

			$sheet
				->setCellValue('M'.$row_num, $invoice_info["invoice_code"]);

			$invoice_dtl_info = get_invoice_detail($invoice_id);
			if(!empty($invoice_dtl_info)){
				foreach($invoice_dtl_info as $dtl){
					if($dtl["type"] == "RENT"){

						$i = $dtl["month"];

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

						$sheet
							->setCellValue('M'.$row_num, $dtl["invoice_code"])
							->setCellValue('N'.$row_num, $start_date->format('Y-m-d')." 至 ".$end_date->format('Y-m-d'))
							->setCellValue('O'.$row_num, $dtl["amount"]);
					}

					if($dtl["type"] == "ORDER_PRODUCT"){

						$sheet
							->setCellValue('P'.$row_num, $dtl["amount"]);

					}

					if($dtl["type"] == "PENALTY"){

						$sheet
							->setCellValue('Q'.$row_num, $dtl["amount"]);

					}

					$row_num++;
				}
			}

		}
	}


	// Redirect output to a client’s web browser (Excel5)
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="Rental_Report_' . $_GET["startdate"] ." to ". $_GET["enddate"]. '.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;