<?php
/**********************************************************************
	Copyright (C) Evolve Techniologies
	Author : Danushka Widanage
	Email : dwidanage@gmail.com
	Company : Evolve Technologies(pvt)Ltd
***********************************************************************/
$page_security = 'SA_CUSTPAYMREP';

// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	evolve
// date_:	2005-05-19
// Title:	Customer Balances
// ----------------------------------------------------------------
$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");
include_once($path_to_root . "/sales/includes/db/customers_db.inc");
include_once($path_to_root . "/sales/includes/db/batch_db.inc");

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

// trial_inquiry_controls();
print_customer_balances();

function get_open_balance($debtorno, $to, $convert){
	if($to)
		$to = date2sql($to);

    $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE.",
    	(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)";
    if ($convert)
    	$sql .= " * rate";
    $sql .= ", 0)) AS charges,
    	SUM(IF(t.type <> ".ST_SALESINVOICE.",
    	(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)";
    if ($convert)
    	$sql .= " * rate";
    $sql .= " * -1, 0)) AS credits,
		SUM(t.alloc";
	if ($convert)
		$sql .= " * rate";
	$sql .= ") AS Allocated,
		SUM(IF(t.type = ".ST_SALESINVOICE.",
			(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount - t.alloc)";
    if ($convert)
    	$sql .= " * rate";
    $sql .= ", 
    	((t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1 + t.alloc)";
    if ($convert)
    	$sql .= " * rate";
    $sql .= ")) AS OutStanding
		FROM ".TB_PREF."debtor_trans t
    	WHERE t.debtor_no = ".db_escape($debtorno)
		." AND t.type <> ".ST_CUSTDELIVERY;
    if ($to)
    	$sql .= " AND t.tran_date < '$to'";
	$sql .= " GROUP BY debtor_no";

    $result = db_query($sql,"No transactions were returned");
    return db_fetch($result);
}

function get_transactions($debtorno, $from, $to){
	$from = date2sql($from);
	$to = date2sql($to);

    $sql = "SELECT ".TB_PREF."debtor_trans.*,
		(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + 
		".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount)
		AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated,
		((".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.")
		AND ".TB_PREF."debtor_trans.due_date < '$to') AS OverDue
    	FROM ".TB_PREF."debtor_trans
    	WHERE ".TB_PREF."debtor_trans.tran_date >= '$from'
		AND ".TB_PREF."debtor_trans.tran_date <= '$to'
		AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)."
		AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY."
		
		AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTCREDIT."
    	AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTPAYMENT."
		AND ".TB_PREF."debtor_trans.type <> ".ST_BANKDEPOSIT."
		
		ORDER BY ".TB_PREF."debtor_trans.tran_date";

    return db_query($sql,"No transactions were returned");
}

function get_transactions1($debtorno, $from, $to, $trans_no){
	$from = date2sql($from);
	$to = date2sql($to);

   /* $sql = "SELECT ".TB_PREF."debtor_trans.*,
		(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + 
		".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount)
		AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated,
		((".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.")
		AND ".TB_PREF."debtor_trans.due_date < '$to') AS OverDue
    	FROM ".TB_PREF."debtor_trans
    	WHERE ".TB_PREF."debtor_trans.tran_date >= '$from'
		AND ".TB_PREF."debtor_trans.tran_date <= '$to'
		AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)."
		AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY."
		
		AND ".TB_PREF."debtor_trans.type <> ".ST_SALESINVOICE."
		AND ".TB_PREF."debtor_trans.trans_no 
		IN( SELECT ".TB_PREF."cust_allocations.trans_no_from FROM `".TB_PREF."cust_allocations` WHERE `".TB_PREF."cust_allocations`.trans_no_to = ".db_escape($trans_no).")
		
		
		ORDER BY ".TB_PREF."debtor_trans.tran_date";*/
		
		
	$sql = "SELECT
			`ca`.id,
			ca.amt AS Allocated,
			ca.date_alloc ,
			ca.trans_no_from,
			ca.trans_type_from,
			ca.trans_no_to,
			ca.trans_type_to,
			dt.*,
			ca.amt AS TotalAmount
			FROM
			0_cust_allocations AS ca
			INNER JOIN 0_debtor_trans AS dt ON ca.trans_no_to = dt.trans_no
			WHERE ca.trans_no_to = ".db_escape($trans_no)." 
			AND dt.tran_date >= '$from' AND dt.tran_date <= '$to'
			AND dt.debtor_no = ".db_escape($debtorno)."
			
			
			GROUP BY ca.id";	

    return db_query($sql,"No transactions were returned");
	//return $sql;
}



//ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT
//----------------------------------------------------------------------------------------------------

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

    	$from 		= $_POST['PARAM_0'];
    	$to 		= $_POST['PARAM_1'];
    	$fromcust 	= $_POST['PARAM_2'];
		$batch_id	= $_POST['PARAM_3'];
    	$currency 	= $_POST['PARAM_4'];
    	$no_zeros 	= $_POST['PARAM_5'];
    	$comments 	= $_POST['PARAM_6'];
	$destination 	= $_POST['PARAM_7'];
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");

	if ($fromcust == ALL_TEXT)
		$cust = _('All');
	else
		$cust = get_customer_name($fromcust);
    	$dec = user_price_dec();

	if ($currency == ALL_TEXT){
		$convert = true;
		$currency = _('Balances in Home Currency');
	}
	else
		$convert = false;
	
	if($batch_id != ''){
		$batch_name = get_batches_name($batch_id);
	}else{
		$batch_name = 'All Batches';
	}
	
	if ($no_zeros) $nozeros = _('Yes');
	else $nozeros = _('No');

	$cols = array(0, 80, 180, 240,	340, 410, 480, 550,	620,800);

	$headers = array(_('Trans Type'), _('#'), _('Date'), _('Due Date'), _('Invoice'), _('Payemnt'),	_('Allocated'),	_('Outstanding'), _('Comment'));

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

    $params =   array( 	0 => $comments,
    				    1 => array('text' => _('Period'), 'from' => $from, 		'to' => $to),
    				    2 => array('text' => _('Customer'), 'from' => $cust,   	'to' => ''),
    				    3 => array('text' => _('Currency'), 'from' => $currency, 'to' => ''),
						4 => array('text' => _('Suppress Zeros'), 'from' => $nozeros, 'to' => ''),
						5 => array('text' => _('Batch'), 'from' => $batch_name, 'to' => ''));

    $rep = new FrontReport(_('Student Balances'), "StudentBalances", user_pagesize(),'9', 'L');
    $rep->Font();
    $rep->Info($params, $cols, $headers, $aligns);
    $rep->NewPage();

	$grandtotal = array(0,0,0,0);

	$sql = "SELECT debtor_no, name, curr_code, debtor_ref FROM ".TB_PREF."debtors_master WHERE inactive = 0 ";
	if ($fromcust != ALL_TEXT)
		$sql .= " AND debtor_no=".db_escape($fromcust);
		
	if($batch_id)
		$sql .= " AND batch_id=".db_escape($batch_id);
		
	$sql .= " ORDER BY debtor_no";
	$result = db_query($sql, "The customers could not be retrieved");
	$num_lines = 0;

	while ($myrow = db_fetch($result))
	{
		if (!$convert && $currency != $myrow['curr_code']) continue;

		$bal = get_open_balance($myrow['debtor_no'], $from, $convert);
		$init[0] = $init[1] = 0.0;
		$init[0] = round2(abs($bal['charges']), $dec);
		$init[1] = round2(Abs($bal['credits']), $dec);
		$init[2] = round2($bal['Allocated'], $dec);
		$init[3] = round2($bal['OutStanding'], $dec);;

		$res = get_transactions($myrow['debtor_no'], $from, $to);
		
		//$rep->TextColLines(1, 5, $res, 10);
		if ($no_zeros && db_num_rows($res) == 0) continue;

 		$num_lines++;
		$rep->fontSize += 2;
		$rep->TextCol(0, 1, $myrow['debtor_ref']);
		$rep->TextCol(1, 3, $myrow['name']);
		//if ($convert)
			//$rep->TextCol(2, 3,	$myrow['curr_code']);
		$rep->fontSize -= 2;
		$rep->TextCol(3, 4,	_("Open Balance"));
		$rep->AmountCol(4, 5, $init[0], $dec);
		$rep->AmountCol(5, 6, $init[1], $dec);
		$rep->AmountCol(6, 7, $init[2], $dec);
		$rep->AmountCol(7, 8, $init[3], $dec);
		$total = array(0,0,0,0);
		for ($i = 0; $i < 4; $i++)
		{
			$total[$i] += $init[$i];
			$grandtotal[$i] += $init[$i];
		}
		$rep->NewLine(1, 2);
		if (db_num_rows($res)==0)
			continue;
		$rep->Line($rep->row + 4);
		while ($trans = db_fetch($res)){
			
			if ($no_zeros && floatcmp($trans['TotalAmount'], $trans['Allocated']) == 0) continue;
			$rep->NewLine(1, 2);
			$rep->TextCol(0, 1, $systypes_array[$trans['type']]);
			//$rep->TextCol(1, 2,	$trans['trans_no']);
			$rep->TextCol(1, 2,	$trans['reference']);
			$rep->DateCol(2, 3,	$trans['tran_date'], true);
			if ($trans['type'] == ST_SALESINVOICE)
				$rep->DateCol(3, 4,	$trans['due_date'], true);
				
			$item[0] = $item[1] = 0.0;
			if ($convert)
				$rate = $trans['rate'];
			else
				$rate = 1.0;
			if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT)
				$trans['TotalAmount'] *= -1;
			if ($trans['TotalAmount'] > 0.0){
				$item[0] = round2(abs($trans['TotalAmount']) * $rate, $dec);//----------charges
				$rep->AmountCol(4, 5, $item[0], $dec);//----------charges
			}/*else{
				$item[1] = round2(Abs($trans['TotalAmount']) * $rate, $dec);
				$rep->AmountCol(5, 6, $item[1], $dec);//----------credits
			}*/
			$item[2] = round2($trans['Allocated'] * $rate, $dec);//----------allocated--------
			$rep->AmountCol(6, 7, $item[2], $dec);//----------allocated--------

				$item[3] = $item[0] - $item[2];
				$rep->AmountCol(7, 8, $item[3], $dec);//--------outstanding

/////////////////////////////			
			$res1 = get_transactions1($myrow['debtor_no'], $from, $to, $trans['trans_no']);
			//$rep->TextColLines(1, 5, $res1, 10);
			$items[1] = $items[2] = 0.00;
			while ($trans1 = db_fetch($res1)){
				$rep->NewLine(1, 1);
				$rep->TextCol(0, 1, $systypes_array[$trans1['trans_type_from']]);
				$rep->TextCol(1, 2, get_reference($trans1['trans_type_from'], $trans1['trans_no_from']));
				//$rep->TextCol(1, 2,	$trans1['trans_no_from']);
				//$rep->TextCol(1, 2,	$trans1['reference']);
				$rep->DateCol(2, 3,	$trans1['date_alloc'], true);
				
				if ($convert)
					$rate1 = $trans1['rate'];
				else
					$rate1 = 1.0;
				
				$items[0] = round2(abs($trans1['TotalAmount']) * $rate, $dec);//----------charges CP
				
				$items[1] = round2(Abs($trans1['TotalAmount']) * $rate, $dec);
				$rep->AmountCol(5, 6, $items[1], $dec);//----------credits-----CP
				
				$item[1] += round2(Abs($trans1['TotalAmount']) * $rate, $dec);
				
				$items[2] = round2($trans1['Allocated'] * $rate, $dec);
				$rep->AmountCol(6, 7, $items[2], $dec);//----------allocated--------CP
				
				$item[2] += round2($trans1['Allocated'] * $rate, $dec);
				
				if ($trans['type'] == ST_SALESINVOICE || $trans['type'] == ST_BANKPAYMENT)
					$items[3] =  $items[1] - $items[2];
				$rep->AmountCol(7, 8, $items[3], $dec);//--------outstanding--CP
//------------dwidanage add new code to get the comment to the report 12/06/2014----------			
				$memo = get_comments_string($trans1['trans_type_from'], $trans1['trans_no_from']);
				if ($memo != ""){
					//$rep->NewLine();
					$rep->TextColLines(8, 9, $memo, -2);
				}
//------------dwidanage add new code to get the comment to the report 12/06/2014----------			
			}
//////////////////////////////
			
			for ($i = 0; $i < 4; $i++)
			{
				$total[$i] += $item[$i];
				$grandtotal[$i] += $item[$i];
			}

		}//--------------details while end
		$rep->Line($rep->row - 8);
		$rep->NewLine(2);
		$rep->TextCol(0, 3, _('Total'));
		for ($i = 0; $i < 4; $i++)
			$rep->AmountCol($i + 4, $i + 5, $total[$i], $dec);
    		$rep->Line($rep->row  - 4);
    		$rep->NewLine(2);
	}//---------customer while end--------
	
	$rep->fontSize += 2;
	$rep->TextCol(0, 3, _('Grand Total'));
	$rep->fontSize -= 2;
	for ($i = 0; $i < 4; $i++)
		$rep->AmountCol($i + 4, $i + 5, $grandtotal[$i], $dec);
	$rep->Line($rep->row  - 4);
	$rep->NewLine();
    	$rep->End();
}

?>
