<?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', true);
	ini_set('display_startup_errors', true);*/
	date_default_timezone_set('Asia/Hong_Kong');

	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/Deposit_Report.xls"); // 檔案名稱
	$sheet    = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)

	//get deposit data
	$sql          = "select *,
									deposit.id as deposit_id,
									deposit.status as deposit_status,
									deposit.code as deposit_code,
									ord.code as order_code,
									deposit.duedate as deposit_duedate,
									deposit.docdate as deposit_docdate,
									deposit.amount as deposit_amount,
									deposit.balance as deposit_balance,
									deposit.lastupdate as deposit_lastupdate,
									deposit.lastupby as deposit_lastupby
								from `deposit` deposit
								INNER JOIN `order` ord ON deposit.order_id = ord.id
								where deposit.deleted = ? and deposit.docdate between ? and ? order by deposit.createdate DESC";
	$parameters   = array(0, $_GET["startdate"], $_GET["enddate"]);
	$deposit_info = bind_pdo($sql, $parameters, "selectall");

	$row_num = 2;
	foreach ($deposit_info as $deposit) {
		$order_info = get_order($deposit["order_id"]);

		$order_room_info = get_order_room($deposit["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);

		$customer_info = get_customer($deposit["customer_id"]);
		if ($customer_info["typeid"] == "PERSONAL") {
			$name = $customer_info["customer_name"];
		} else {
			$name = $customer_info["companyname"];
		}

		if ($deposit["actual_returnamount"] != NULL && $deposit["actual_returnamount"] != $deposit["deposit_amount"]) {
			$remark = "使用按金支付部份費用";
		} else {
			$remark = "";
		}

		$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["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"]);

			$payment_info_code = $payment_info["code"];
		}else{
			$payment_info_code = "";
		}

		if(!empty($order_info["withdraw_date"])){
			$withdraw_date = $order_info["withdraw_date"];
		}else{
			if($deposit["deposit_status"] == "RETURNED" && $deposit["actual_returndate"]){
				$withdraw_date = $deposit["actual_returndate"];
			}else{
				$withdraw_date = "";
			}
		}
		$sheet
			->setCellValue('A' . $row_num, $order_info["code"])
			->setCellValue('B' . $row_num, $order_room_list)
			->setCellValue('C' . $row_num, $payment_info_code)
			->setCellValue('D' . $row_num, $deposit["deposit_code"])
			->setCellValue('E' . $row_num, $name)
			->setCellValue('F' . $row_num, $deposit["deposit_duedate"])
			->setCellValue('G' . $row_num, $deposit["deposit_amount"])
			->setCellValue('H' . $row_num, ($deposit["deposit_amount"] - $deposit["deposit_balance"]))
			->setCellValue('K' . $row_num, $deposit["actual_returnamount"])
			->setCellValue('L' . $row_num, $withdraw_date)
			->setCellValue('M' . $row_num, $remark);
		$row_num++;

		//if any deposit penalty
		$sql = "select * from invoice where deduct_by_deposit_id > ? and deduct_by_deposit_amount > ? ";
		$parameters = array(0, 0);
		$deposit_penalty_info = bind_pdo($sql, $parameters, "selectall");
		if(!empty($deposit_penalty_info)){
			foreach($deposit_penalty_info as $penalty){
				$this_deposit_info = get_deposit($penalty["deduct_by_deposit_id"]);
				$sheet
					->setCellValue('I' . $row_num, $this_deposit_info["deposit_code"])
					->setCellValue('J' . $row_num, $penalty["deduct_by_deposit_amount"]);
				$row_num++;
			}
		}
	}

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