<?php
require_once(__DIR__.'/../../includes/sysinit.php');
//**********************************************************************************
unset($_SESSION['listParams']['searchVisible']);
$_SESSION['listParams']['allowExport']	= 1;
unset($_SESSION['module']['add']);

if (isset($_POST))
{
	$_SESSION['listParams']['searchField'][0]['title']	= 'client_full_name';
	$_SESSION['listParams']['searchField'][0][0]		= 'client_name';
	$_SESSION['listParams']['searchField'][0][1]		= 'client_surname';
	
	$_SESSION['listParams']['searchField'][1]['title']	= 'total_balance';
	$_SESSION['listParams']['searchField'][1][0]		= 'txn_amount';
	
	$_SESSION['listParams']['searchField'][2]['title']	= 'client_balance_current';
	$_SESSION['listParams']['searchField'][2][0]		= 'txn_amount';
	
	$_SESSION['listParams']['searchField'][3]['title']	= 'client_balance_30';
	$_SESSION['listParams']['searchField'][3][0]		= 'txn_amount';
	
	$_SESSION['listParams']['searchField'][4]['title']	= 'client_balance_60';
	$_SESSION['listParams']['searchField'][4][0]		= 'txn_amount';
	
	$_SESSION['listParams']['searchField'][5]['title']	= 'client_balance_90';
	$_SESSION['listParams']['searchField'][5][0]		= 'txn_amount';
	
	$_SESSION['listParams']['searchField'][6]['title']	= 'client_balance_120';
	$_SESSION['listParams']['searchField'][6][0]		= 'txn_amount';
	
}

if (sizeof($_SESSION['caller']['args']) > 2)
{
	$_SESSION['caller']['args'][1] = $_SESSION['caller']['args'][2];
	unset($_SESSION['caller']['args'][2]);
	
	$_SESSION['module']['back'] = 1;
	$_SESSION['module']['backlink'] = SableSystem::getCallPageLink('/report/ageing/');
}

if (isset($data))
{
	$_SESSION['listParams']['listSql'] = $data['sql'];
}
else
{
	$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');
	
	
	$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.
	$_SESSION['listParams']['listSql'] = $sql;
}

$_SESSION['listParams']['module']					= "ageing";
$_SESSION['listParams']['page']						= 0;
$_SESSION['listParams']['offset']					= 50; //the number of records to display per page
$_SESSION['listParams']['parentId']					= ""; //
$_SESSION['listParams']['moduleFolder']				= "report/ageing";
$_SESSION['listParams']['moduleFolderDesc']			= "Age Analysis";
$_SESSION['listParams']['modulePage']				= "ageing_table.php";
$_SESSION['listParams']['moduleIdField']			= "client_id";
$_SESSION['listParams']['moduleDescField']			= "client_full_name";
$_SESSION['listParams']['orderField']				= 0;
$_SESSION['listParams']['orderDirection']			= 0;

$_SESSION['listParams']['listHeadings'][0] = "Client";
$_SESSION['listParams']['listHeadings'][1] = "Balance";
$_SESSION['listParams']['listHeadings'][2] = "Current";
$_SESSION['listParams']['listHeadings'][3] = "30 Days";
$_SESSION['listParams']['listHeadings'][4] = "60 Days";
$_SESSION['listParams']['listHeadings'][5] = "90 Days";
$_SESSION['listParams']['listHeadings'][6] = "120<sup>+</sup> Days";
//if ($_SESSION['user']['access_level'] >= SABLE_ACCESS_USER){$_SESSION['listParams']['listHeadings'][7] = "";} //View

$_SESSION['listParams']['listHeadingWidth'][0] = "10%";
$_SESSION['listParams']['listHeadingWidth'][1] = "10%";
$_SESSION['listParams']['listHeadingWidth'][2] = "10%";
$_SESSION['listParams']['listHeadingWidth'][3] = "10%";
$_SESSION['listParams']['listHeadingWidth'][4] = "10%";
$_SESSION['listParams']['listHeadingWidth'][5] = "10%";
$_SESSION['listParams']['listHeadingWidth'][6] = "10%";
//if ($_SESSION['user']['access_level'] >= SABLE_ACCESS_USER){$_SESSION['listParams']['listHeadingWidth'][7] = "5%";} //View

$_SESSION['listParams']['listFields'][0]['field']	= "client_full_name";
$_SESSION['listParams']['listFields'][1]['field']	= "total_balance";
$_SESSION['listParams']['listFields'][1]['currency']= "1";
$_SESSION['listParams']['listFields'][2]['field']	= "client_balance_current";
$_SESSION['listParams']['listFields'][2]['currency']= "1";
$_SESSION['listParams']['listFields'][3]['field']	= "client_balance_30";
$_SESSION['listParams']['listFields'][3]['currency']= "1";
$_SESSION['listParams']['listFields'][4]['field']	= "client_balance_60";
$_SESSION['listParams']['listFields'][4]['currency']= "1";
$_SESSION['listParams']['listFields'][5]['field']	= "client_balance_90";
$_SESSION['listParams']['listFields'][5]['currency']= "1";
$_SESSION['listParams']['listFields'][6]['field']	= "client_balance_120";
$_SESSION['listParams']['listFields'][6]['currency']= "1";

if ($_SESSION['user']['access_level'] >= SABLE_ACCESS_USER) 
{
	//$_SESSION['listParams']['listFields'][7]['field'] = "<i class='fa fa-2x ".SABLE_DATALIST_VIEW."' title='View --!--'></i>";
	//$_SESSION['listParams']['listFields'][7]['onClick'] = " onClick='viewFinance(\"--#--\")' ";
}

SableGeneralFunctions::callDataList();
