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

	if (empty($_POST["year"]) || empty($_POST["month"]) || (int)$_POST["year"] <= 0 || (int)$_POST["month"] <= 0) {
		echo "<script>alert('請選擇正確的年份和月份。'); history.back();</script>";
		exit;
	}

	$start_date = (int)$_POST["year"] . "-" . (int)$_POST["month"] . "-01";
	$date       = new DateTime($start_date);
	$end_date   = $date->format('Y-m-t');

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

	$row_num = 6;
	$total_receivable_amount = 0;
	$total_get_amount = 0;

	$sql        = "select 'deposit' as _table_name, id, duedate from deposit where duedate between ? and  ? and deleted = ? and status != ? UNION select 'invoice' as _table_name, id, duedate from invoice where duedate between ? and  ? and deleted = ? and status != ? order by duedate ASC";
	$parameters = array($start_date, $end_date, 0, 'VOID', $start_date, $end_date, 0, 'VOID');
	$result = bind_pdo($sql, $parameters, "selectall");

	foreach($result as $detail){

		$order_id = 0;

		if($detail["_table_name"] == "deposit"){
			$deposit_info = get_deposit($detail["id"]);
			$receivable_amount = $deposit_info["amount"];
			$total_receivable_amount += $receivable_amount;
			$type_name = get_master_type_code("PAYMENT_TYPE", "DEPOSIT");
			$code = $deposit_info["deposit_code"];

			/*$sql        = "select sum(amount) as paid_amount from payment_dtl where payable_type = ? and payable_id = ? and deleted = ? and status = ? and docdate between ? and ?";
			$parameters = array("DEPOSIT", $detail["id"], 0, "PAID", $start_date, $end_date);*/
            $sql        = "select sum(amount) as paid_amount from payment_dtl where payable_type = ? and payable_id = ? and deleted = ? and status = ?";
            $parameters = array("DEPOSIT", $detail["id"], 0, "PAID");
			$payment_dtl_info     = bind_pdo($sql, $parameters, "selectone");

			if(empty($payment_dtl_info) || empty($payment_dtl_info["paid_amount"])){
				$get_amount = 0;
			}else{
				$get_amount = $payment_dtl_info["paid_amount"];
			}

			$total_get_amount += $get_amount;

			$order_id = $deposit_info["order_id"];
		}else{
			$invoice_info = get_invoice($detail["id"]);
			$receivable_amount = $invoice_info["amount"];
			$total_receivable_amount += $receivable_amount;
			$type_name = get_master_type_code("PAYMENT_TYPE", "INVOICE");
			$code = $invoice_info["invoice_code"];

			/*$sql        = "select sum(amount) as paid_amount from payment_dtl where payable_type = ? and payable_id = ? and deleted = ? and status = ? and docdate between ? and ?";
			$parameters = array("INVOICE", $detail["id"], 0, "PAID", $start_date, $end_date);*/
			//remove docdate checking, useless
            $sql        = "select sum(amount) as paid_amount from payment_dtl where payable_type = ? and payable_id = ? and deleted = ? and status = ?";
            $parameters = array("INVOICE", $detail["id"], 0, "PAID");
			$payment_dtl_info     = bind_pdo($sql, $parameters, "selectone");

			if(empty($payment_dtl_info) || empty($payment_dtl_info["paid_amount"])){
				$get_amount = 0;
			}else{
				$get_amount = $payment_dtl_info["paid_amount"];
			}

			$total_get_amount += $get_amount;

			$order_id = $invoice_info["order_id"];
		}

		if(!empty($order_id) || $order_id > 0){

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

		}else{
			$order_room_list = "";
		}

		$sheet->getStyle('D'.$row_num.":F".$row_num)->getNumberFormat()->setFormatCode('0.00');

		$sheet
			->setCellValue('A' . $row_num, $type_name["name_tc"])
			->setCellValue('B' . $row_num, $code)
			->setCellValue('C' . $row_num, $order_room_list)
			->setCellValue('D' . $row_num, $receivable_amount)
			->setCellValue('E' . $row_num, $get_amount)
			->setCellValue('F' . $row_num, ($receivable_amount-$get_amount));

		$row_num++;
	}

	$sheet
		->setCellValue('A2', (int)$_POST["year"]."-".(int)$_POST["month"])
		->setCellValue('B2', $total_get_amount)
		->setCellValue('C2', $total_receivable_amount);

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