<?php
require_once(__DIR__."/../includes/sysinit.php");
require_once(SABLE_CLASS_PATH."/sable_validation_functions.php");
class Report 
{
	// Properties of the class:
	var $edtStartDate,	$edtEndDate, $selClientId, $reportId, $edtReportName, $selPmId, $selTxnTypeId, $edtHardwareModel;
	private $sableDB, $heading, $data, $sql, $idx;
	
	function __construct()
	{
		$this->sableDB = new SableDB;
	}
	
	function __destruct(){}
	
	function runReport()
	{
		switch(intval($this->reportId))
		{
			case 1:  {$this->runClientReport();	break;}
			case 2:  {$this->runAgeingReport();	break;}
			default: {SableSystem::ajaxResponse(false, "", "No report selected.", 5, "edtStartDate", false, "edtStartDate");}
		}
	}
	
	private function sendAjaxResponse($url, $paramArray)
	{
		$hash = SableDBFunctions::storeAjaxResponse($paramArray);
		SableSystem::ajaxPageRedirect($url.'+'.$hash, '');
	}
	
	private function getFormattedSQL($condition)
	{
		$prfx = $this->idx ? " and " : " where ";
		$this->sql .= $prfx.$condition;
		$this->idx++;
	}
	
	private function runClientReport()
	{
		$this->sql = "select finance_id, txn_type.txn_type_name, payment_method.pm_name, txn_date, 
					  txn_amount, concat(client_name, ' ', client_surname) as client_full_name, txn_paid
					  from finance
					  inner join txn_type on txn_type.txn_type_id = finance.txn_type_id
					  inner join payment_method on payment_method.pm_id = finance.pm_id
					  inner join client on client.client_id = finance.client_id ";
		
		$this->idx = 0;
				
		if (!empty($this->edtStartDate))	$this->getFormattedSQL("(DATE(txn_date) >= '".$this->edtStartDate."')");
		if (!empty($this->edtEndDate))		$this->getFormattedSQL("(DATE(txn_date) <= '".$this->edtEndDate."')");
		if ($this->selClientId)				$this->getFormattedSQL("(client.client_id = ".$this->selClientId.")");
		if ($this->selPmId)					$this->getFormattedSQL("(payment_method.pm_id = ".$this->selPmId.")");
		if ($this->selTxnTypeId)			$this->getFormattedSQL("(txn_type.txn_type_id = ".$this->selTxnTypeId.")");

		//SableDebug::debug($sql);
		//$rows = $this->sableDB->getRows($sql);
		$out = ['sql' => $this->sql];
		$this->sendAjaxResponse('/report/finance/', $out);	
	}
	
	private function runAgeingReport()
	{
		$today = new DateTime();
		$year = $today->format('Y');
		$month = $today->format('m');

		$today->modify('-1 month');
		$year30 = $today->format('Y');
		$month30 = $today->format('m');

		$today->modify('-1 month');
		$year60 = $today->format('Y');
		$month60 = $today->format('m');

		$today->modify('-1 month');
		$year90 = $today->format('Y');
		$month90 = $today->format('m');

		$today->modify('-1 month');
		$year120 = $today->format('Y');
		$month120 = $today->format('m');
		
		$this->sql = 'select concat(client_name, " ", client_surname) as client_full_name, 
				sum(txn_amount) as total_balance, 
				SUM(IF((YEAR(txn_date) = "'.$year.'") AND (MONTH(txn_date) = "'.$month.'"), txn_amount, 0)) as client_balance_current,
				SUM(IF((YEAR(txn_date) = "'.$year30.'") AND (MONTH(txn_date) = "'.$month30.'"), txn_amount, 0)) as client_balance_30,
				SUM(IF((YEAR(txn_date) = "'.$year60.'") AND (MONTH(txn_date) = "'.$month60.'"), txn_amount, 0)) as client_balance_60,
				SUM(IF((YEAR(txn_date) = "'.$year90.'") AND (MONTH(txn_date) = "'.$month90.'"), txn_amount, 0)) as client_balance_90,
				SUM(IF((YEAR(txn_date) <= "'.$year120.'") AND (MONTH(txn_date) <= "'.$month120.'"), txn_amount, 0)) as client_balance_120 
				from finance 
				inner join client on client.client_id = finance.client_id 
				group by client.client_id 
				having (total_balance > 0)'; //do not show clients in credit, or with zero balance.
		$this->idx = 0;

		if ($this->selClientId)	
			$this->sql .= " and (client.client_id = ".$this->selClientId.")";

		//SableDebug::debug($this->sql);
		$out = ['sql' => $this->sql];
		$this->sendAjaxResponse('/report/ageing/', $out);	}
}