<?php
/**********************************************************************
Copyright (C) Evolve Techniologies
Edited By : Danushka Widanage
Email : dwidanage@gmail.com
Company : Evolve Technologies(pvt)Ltd
***********************************************************************/
$page_security = 'SA_BANKREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Bank Accounts Transactions
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");

//----------------------------------------------------------------------------------------------------

print_bank_transactions();

//----------------------------------------------------------------------------------------------------

function get_bank_balance_to($statement_date, $account)
{
	$sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans_reconcile WHERE bank_act='$account'
	AND trans_date < '$statement_date'";
	$result = db_query($sql, "The starting balance on hand could not be calculated");
	$row = db_fetch_row($result);
	return $row[0];
}


function get_bank_transactions($from, $to, $account)
{
	$from = date2sql($from);
	$to = date2sql($to);
	$sql = "SELECT ".TB_PREF."bank_trans.*, ".TB_PREF."comments.memo_
			FROM ".TB_PREF."bank_trans LEFT JOIN ".TB_PREF."comments ON 
			(".TB_PREF."bank_trans.type = ".TB_PREF."comments.type
			AND ".TB_PREF."bank_trans.trans_no = ".TB_PREF."comments.id)
		WHERE ".TB_PREF."bank_trans.bank_act = '$account'
		AND trans_date >= '$from'
		AND trans_date <= '$to'
		AND reconciled != ''
		ORDER BY trans_date,".TB_PREF."bank_trans.id";
//AND reconciled != ''
//ORDER BY trans_date,".TB_PREF."bank_trans.id
	return db_query($sql,"The transactions for '$account' could not be retrieved");
	//return $sql;
}
//function get_bank_balance
function get_sql_for_bank_account_reconcile_credit($bank_account, $statement_date)
{
	$date = date2sql($statement_date);
	

	/*$sql = "SELECT	".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans 
				WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . "
				AND id IN (SELECT id FROM ".TB_PREF."bank_recon_details WHERE recon_date = '$statement_date')
				AND trans_date <= '$statement_date' 
				AND amount < 0.0
				ORDER BY trans_date";*/
	$sql = "SELECT * FROM ".TB_PREF."bank_trans 
				WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . " 
				AND (reconciled IS NULL 
						OR reconciled!='$statement_date' 
						AND reconciled >= '$statement_date' 
						AND (reconciled >'$statement_date' AND trans_date <= '$statement_date')
					) 
				AND trans_date <= '$statement_date'
				AND amount < 0.0
				ORDER BY trans_date";		

// or	ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
	//return $sql;
	return db_query($sql,"The transactions for '$account' could not be retrieved");
}

function get_sql_for_bank_account_reconcile_debit($bank_account, $statement_date)
{
	/*$sql = "SELECT	".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans 	
				WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . "
				AND id IN (SELECT id FROM ".TB_PREF."bank_recon_details WHERE recon_date = '$statement_date')
				AND trans_date <= '$statement_date' 
				AND amount > 0.0 
				ORDER BY trans_date";*/
	$sql = "SELECT * FROM ".TB_PREF."bank_trans 
				WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . " 
				AND (reconciled IS NULL 
						OR reconciled!='$statement_date' 
						AND reconciled >= '$statement_date' 
						AND (reconciled >'$statement_date' AND trans_date <= '$statement_date')
					) 
				AND trans_date <= '$statement_date'
				AND amount > 0.0
				ORDER BY trans_date";		

	return db_query($sql,"The transactions for '$account' could not be retrieved");
}


function print_bank_transactions()
{
	global $path_to_root, $systypes_array;

	$acc = $_POST['PARAM_0'];
	$statement_date = $_POST['PARAM_1'];
	$comments = $_POST['PARAM_2'];
	$destination = $_POST['PARAM_3'];
	
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");

	$rep = new FrontReport(_('Bank Reconciliation Statement'), "BankStatement", user_pagesize(), 9, "P");
	$dec = user_price_dec();

	$cols = array(0, 60, 120, 300, 400, 480, 560);

	$aligns = array('left',	'left',	'left',	'right', 'right', 'center');

	$headers = array(_('Date'), _('Cheque No'),	 _('Person/Item'), _('Debit'), _('Credit'), _('Balance'));

	$account = get_bank_account($acc);
	$act = $account['bank_account_name']." - ".$account['bank_curr_code']." - ".$account['bank_account_number'];
   	$params =   array( 	0 => $comments,
	    1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
	    2 => array('text' => _('Bank Account'),'from' => $act,'to' => ''),
		3 => array('text' => _('Reconcilation Date'),'from' => $statement_date,'to' => ''));

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);
	$rep->NewPage();

		// Calculate Bank Balance as per reco
		//$date = date2sql($statement_date);
		$sql = "SELECT SUM(IF(reconciled<='$statement_date' AND reconciled !='0000-00-00', amount, 0)) as reconciled,
				 SUM(amount) as books_total
			FROM ".TB_PREF."bank_trans trans
			WHERE bank_act=".db_escape($account['id'])."
			AND trans_date <= '$statement_date'";	
			
		//	." AND trans.reconciled IS NOT NULL";
		//display_notification($sql);
		$t_result = db_query($sql,"Cannot retrieve reconciliation data");
		if ($t_row = db_fetch($t_result)) {
			$books_total = $t_row['books_total'];
			$reconciled = $t_row['reconciled'];
		}			
		$difference = $books_total - $reconciled;		

	$prev_balance = get_bank_balance_to($statement_date, $account["id"]);

	//$trans = get_bank_transactions($from, $to, $account['id']);
	
	$trans_credit = get_sql_for_bank_account_reconcile_credit($account['id'],$statement_date);
	$trans_debit = get_sql_for_bank_account_reconcile_debit($account['id'], $statement_date);
		
	/*$rep->TextCol(0, 13,$prev_balance);
	$rep->NewLine(2);*/
	
	$rows = db_num_rows($trans_debit);
	$rows1 = db_num_rows($trans_credit);
	
	if ($prev_balance != 0.0 || $rows != 0 || $rows1 != 0)
	{
		$rep->Font('bold');
		$rep->TextCol(0, 3,	$act);
		$rep->NewLine(2);
		
		// Bank Balance (by Reco)
		$rep->Font('bold');
		$rep->TextCol(1, 4, _('Balance as per bank statement'));
		if ($reconciled > 0.0)
			$rep->AmountCol(5, 6, $reconciled.'2', $dec);
		else
			$rep->AmountCol(5, 6, $reconciled.'3', $dec);

		$rep->Font();
		$total = $prev_balance;
		$rep->NewLine(2);
		if ($rows > 0 || $rows1 > 0)
		{
			// Keep a running total as we loop through
			// the transactions.
			
			$total_debit = $total_credit = 0;			
			$i =0; $j=0;
			while ($myrow=db_fetch($trans_debit))
			{
				$total += $myrow['amount'];

				if ($myrow['amount'] > 0.0)
				{
					if($i==0){
						$rep->Font('bold');
						$rep->TextCol(0, 4, _("Add :Unrealised Deposits"));
						$rep->Line($rep->row - 1);
						$rep->NewLine(2);
						
					}
					$rep->Font();
					$rep->DateCol(0, 1,	$myrow["trans_date"], true);//4
					$rep->TextCol(1, 2,	$myrow['cheque_no']);//2
					$rep->TextCol(2, 3,	payment_person_name($myrow["person_type_id"],$myrow["person_id"], false));//5
					$rep->AmountCol(3, 4, abs($myrow['amount']), $dec);//6
					$total_debit += abs($myrow['amount']);
					
					$i++;
				}
				$rep->NewLine();
			}
			$rep->AmountCol(5, 6, $total_debit, $dec);//8
			$rep->NewLine(2);
			$rep->AmountCol(5, 6, $reconciled + $total_debit, $dec);//8

			while ($myrow=db_fetch($trans_credit))
			{
				$total += $myrow['amount'];

				if ($myrow['amount'] < 0.0)
				{
					if($j==0){
						$rep->NewLine(2);
						$rep->Font('bold');
						$rep->TextCol(0, 4, _("Less :Unpresented Cheques"));
						$rep->Line($rep->row - 1);
						$rep->NewLine(2);
					}
					$rep->Font();
					$rep->DateCol(0, 1,	$myrow["trans_date"], true);//4
					$rep->TextCol(1, 2,	$myrow['cheque_no']);//2
					$rep->TextCol(2, 3,	payment_person_name($myrow["person_type_id"],$myrow["person_id"], false));//5
					$rep->AmountCol(4, 5, abs($myrow['amount']), $dec);//7
					$total_credit += abs($myrow['amount']);
					
					$j++;
				}
				$rep->NewLine();
				
				if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
				{
					$rep->Line($rep->row - 2);
					$rep->NewPage();
				}
				
			}
			$rep->AmountCol(5, 6, $total_credit, $dec);//8	
			$rep->NewLine(2);

			$rep->Line($rep->row - 2);
			$rep->NewLine();
			//$rep->AmountCol(5, 6, $reconciled + $total_debit + $total_credit, $dec);//8
			$reconciled_amount = $reconciled + $total_debit - $total_credit;

		}
		
		$rep->NewLine(2);	
		$rep->Font('bold');
		$rep->TextCol(1, 4, _('Reconciled Balance'));
		if ($prev_balance > 0.0)
			$rep->AmountCol(5, 6, $reconciled_amount, $dec);
		else
			$rep->AmountCol(5, 6, $reconciled_amount, $dec);
		
		// Print the difference between starting and ending balances.
		$rep->Font();
		$rep->NewLine(2);	
		
		$rep->TextCol(1, 4,	_("Balance per cash book"));
		$rep->AmountCol(5, 6, $books_total, $dec);
		
		$rep->Font();
		$rep->NewLine(2);	

		// Reco Difference
		$rep->Font('bold');
		$rep->TextCol(1, 4,	_("Difference"));
		$difference = $books_total-$reconciled_amount;
		if ($difference > 0.0)
			$rep->AmountCol(5, 6, abs($difference), $dec);
		else
			$rep->AmountCol(5, 6, abs($difference), $dec);
		$rep->Font();
		$rep->NewLine(2);	
			
		$rep->Line($rep->row - $rep->lineHeight + 4);
		$rep->NewLine(2, 1);			
			
	}
	$rep->End();
}

?>