<?php
/**********************************************************************
    Copyright (C) FrontAccounting, LLC.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program 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 License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_REP_AGING_BILL_WISE_SUPP_REPORT';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Sales Summary Report
// ----------------------------------------------------------------
$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_Aging_Bill_wise_report();

function getAgingBillWise($from, $to, $supplier_id, $invoice_no, $show_balance=0)
{
$fromdate = date2sql($from);
$todate = date2sql($to);

$allocated_from = "(SELECT trans_type_from as trans_type, trans_no_from as trans_no, date_alloc, person_id, SUM(amt) amount
                    FROM ".TB_PREF."supp_allocations alloc
                    WHERE date_alloc <= '$todate' AND alloc.person_id='$supplier_id'
                    ) alloc_from";

$allocated_to = "(SELECT trans_type_to as trans_type, trans_no_to as trans_no, date_alloc, person_id , SUM(amt) amount
                  FROM ".TB_PREF."supp_allocations alloc
                  WHERE date_alloc <= '$todate' AND alloc.person_id='$supplier_id'
                  ) alloc_to";

$sql = "SELECT st.* ,(st.ov_amount + st.ov_gst + st.ov_discount) AS TotalAmount,
               IFNULL(IFNULL(alloc_from.amount, alloc_to.amount),0) AS alloc_amt
        FROM ".TB_PREF."supp_trans as st
        LEFT JOIN $allocated_from ON alloc_from.trans_type = st.type AND alloc_from.trans_no = st.trans_no AND alloc_from.person_id=st.supplier_id
        LEFT JOIN $allocated_to ON alloc_to.trans_type = st.type AND alloc_to.trans_no = st.trans_no AND alloc_to.person_id=st.supplier_id
        WHERE ";

$sql .= "st.ov_amount != 0 AND st.tran_date >= ".db_escape($fromdate)." AND st.tran_date <= ".db_escape($todate);

if (!$show_balance)
    $sql .= " AND round((ABS(st.ov_amount) + st.ov_gst + st.ov_discount) - IFNULL(IFNULL(alloc_from.amount, alloc_to.amount),0), 6) > 0.005 ";

if ($supplier_id)
    $sql .= " AND st.supplier_id = ".db_escape($supplier_id);
if ($invoice_no)
    $sql .= " AND st.trans_no = ".db_escape($invoice_no);

$sql .= " GROUP BY st.type,st.trans_no ORDER BY st.tran_date ";


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


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

    $sql = "SELECT SUM(IF(t.type = ".ST_SUPPINVOICE." OR (t.type IN (".ST_JOURNAL." , ".ST_BANKDEPOSIT.") AND t.ov_amount>0),
        -abs(t.ov_amount + t.ov_gst + t.ov_discount), 0)) AS credits,";

    $sql .= "SUM(IF(t.type != ".ST_SUPPINVOICE." AND NOT(t.type IN (".ST_JOURNAL." , ".ST_BANKDEPOSIT.") AND t.ov_amount>0),
        abs(t.ov_amount + t.ov_gst + t.ov_discount) * -1, 0)) AS charges,";

    $sql .= "SUM(IF(t.type != ".ST_SUPPINVOICE." AND NOT(t.type IN (".ST_JOURNAL." , ".ST_BANKDEPOSIT.")), t.alloc * -1, t.alloc)) 
        AS Allocated,";

    $sql .= "SUM(IF(t.type = ".ST_SUPPINVOICE.", 1, -1) *
        (abs(t.ov_amount + t.ov_gst + t.ov_discount) - abs(t.alloc))) AS OutStanding
        FROM ".TB_PREF."supp_trans t
        WHERE t.supplier_id = ".db_escape($supplier_id);
    if ($to)
        $sql .= " AND t.tran_date < '$to'";
    $sql .= " GROUP BY supplier_id";

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



    return db_fetch($result);
}

function get_po_grn_number($type, $trans_no)
{
	$sql = " SELECT * FROM ".TB_PREF."grn_items as gi, ".TB_PREF."supp_invoice_items as sit, ".TB_PREF."grn_batch as gb WHERE gi.id=sit.grn_item_id AND 
		gi.grn_batch_id=gb.id AND sit.supp_trans_type=".db_escape($type) ."  AND sit.supp_trans_no=".db_escape($trans_no);
		// return db_query($sql,"No transactions were returned");
		$result = db_query($sql);
		return db_fetch($result);
}

  	

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

function print_Aging_Bill_wise_report()
{
	global $path_to_root;
	$from = $_POST['PARAM_0'];
	$to = $_POST['PARAM_1'];
	$supplier_id = $_POST['PARAM_2'];
	$show_balance = $_POST['PARAM_3'];
	$invoice_no = $_POST['PARAM_4'];
	$aging_id = $_POST['PARAM_5'];
	$account_group = $_POST['PARAM_6'];
	$orientation = $_POST['PARAM_7'];
	$destination = $_POST['PARAM_8'];

	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");
	$orientation = ($orientation ? 'L' : 'P');

	if ($supplier_id == ALL_TEXT)
		$supp = _('All');
	else
		$supp = get_supplier_name($supplier_id);

	if ($invoice_no == '')
		$Invoice = _('All');
	else
		$Invoice = $invoice_no;

	
	if ($account_group == ALL_TEXT)
		$acc_name = _('All');
	else
		$acc_name = get_account_type_name($account_group);

	$dec = user_price_dec();

	$rep = new FrontReport(_('Aging Bill Wise Report'), "AgingBillWiseReport", user_pagesize(), 9, $orientation);

	$params =   array( 	0 => $comments,
						1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
						2 => array( 'text' => _('Supplier Id'),'from' => $supp,'to' => ''),
						3 => array( 'text' => _('Invoice #'),'from' => $Invoice,'to' => ''),
						4 => array( 'text' => _('Group Name'),'from' => $supp,'to' => ''),
						// 4 => array( 'text' => _('Sales Person'),'from' => $sale_Prsn,'to' => ''),
						// 5 => array( 'text' => _('Item Stock'),'from' => $stock_id,'to' => ''),
						// 6 => array( 'text' => _('Items Group'),'from' => $item_group,'to' => ''),
						// 7 => array( 'text' => _('Sales Type'),'from' => $type,'to' => '')
					);

	$cols = array(0, 100 ,170 , 220, 250, 300, 350, 430, 490, 550, 575);

$headers = array(_('Account'), _('Pay Term'), _('PO NO'), _('GRN'), _('Invoice NO'), _('Inv Date'), _('Total Amt'), _('Pending Amt'), _('Balance'), _('Days'));
	$aligns = array('left', 'left', 'left', 'left', 'left', 'center', 'center', 'left', 'left', 'left', 'left', 'left');
    if ($orientation == 'L')
    	recalculate_cols($cols);

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);
	$rep->NewPage();
	
	// $totalnet = 0.0;
    
    $sql = "SELECT sp.supplier_id, sp.supp_name AS name, sp.curr_code, sp.supp_ref ,cm.account_type FROM ".TB_PREF."suppliers as sp 
           LEFT JOIN ".TB_PREF."chart_master  as cm ON  sp.payable_account=cm.account_code WHERE 1=1 ";
	if ($supplier_id != ALL_TEXT)
		$sql .= " AND sp.supplier_id=".db_escape($supplier_id);

   	if ($account_group != ALL_TEXT)
		$sql .= " AND cm.account_type=".db_escape($account_group);

	$sql .= " ORDER BY  cm.account_type";
	// display_error($sql);
	$result = db_query($sql, "The suppliers could not be retrieved");

	$amt_total = 0;
	$qty_total = 0;
	$acc_id=0;


	while ($myrow=db_fetch($result))
	{
	$bal_amt   = 0;

			$bal = get_open_balance($myrow['supplier_id'], $from);
        $rate = $convert ? get_exchange_rate_from_home_currency($myrow['curr_code'], Today()) : 1;
	$init = array();
	$bal['charges'] = isset($bal['charges']) ? $bal['charges'] : 0;
	$bal['credits'] = isset($bal['credits']) ? $bal['credits'] : 0;
	$bal['Allocated'] = isset($bal['Allocated']) ? $bal['Allocated'] : 0;
	$bal['OutStanding'] = isset($bal['OutStanding']) ? $bal['OutStanding'] : 0;
	$init[0] = round2(abs($bal['charges']*$rate), $dec);
	$init[1] = round2(Abs($bal['credits']*$rate), $dec);
	$init[2] = round2($bal['Allocated']*$rate, $dec);
		$bal_amt += Abs($init[0] - $init[1]);

		if(	$acc_id!=$myrow['account_type'])
		{
			    $rep->Font('BU');
			    $rep->fontSize += 2;
		        	$rep->TextCol(0, 2, strtoupper(get_account_type_name($myrow['account_type'])) ) ;
				$rep->NewLine();
				$rep->fontSize -= 2;
				    $rep->Font();
		}
		$acc_id=$myrow['account_type'];
			$rep->Font('bold');
				$rep->SetTextColor(96, 96, 96);
		$rep->TextCol(0, 5, "OPENING BALANCE ".strtoupper(get_supplier_name($myrow['supplier_id'])) );
			$rep->SetTextColor(0, 0, 0);
			$rep->AmountCol(7, 8,$bal_amt,2,0,0,0,0,null,1,true);
				$rep->Font();

			$rep->NewLine();

	$transactions = getAgingBillWise($from, $to, $myrow['supplier_id'], $invoice_no, $show_balance);

	// $rep->NewLine(2);

	
	while ($trans=db_fetch($transactions))
	{
		// global $systypes_array;
						$bal_amt +=$trans['TotalAmount'];
						if($trans['type']!=ST_SUPPINVOICE)
							continue;
		if(abs($trans['TotalAmount'])==$trans['alloc'])
			continue;
		
				$rep->TextCol(0, 1, get_supplier_name($trans['supplier_id']));
				    $pay_row=get_payment_terms($trans['payment_terms']);


				$rep->TextCol(1, 2,($trans['payment_terms']) ? $pay_row['terms'] : '-');

				$po_grn = get_po_grn_number($trans['type'], $trans['trans_no']);
				$rep->TextCol(2, 3,	($po_grn['purch_order_no']) ? $po_grn['purch_order_no'] : '-');
				$rep->TextCol(3, 4,	($po_grn['grn_batch_id']) ? $po_grn['grn_batch_id'] : '-');
				$rep->TextCol(4, 5,	$trans['reference']);

				// $rep->TextCol(4, 5,	$systypes_array[$trans['type']]);
				// $data = get_sales_parent_numbers(ST_SALESINVOICE, $trans['trans_no']);
				$rep->TextCol(5, 6, $trans['tran_date']);
				if ($trans['TotalAmount']<0)
				$rep->SetTextColor(255, 0, 0);
				else
				$rep->SetTextColor(0, 0, 0);
			     $total = abs($trans['TotalAmount']);
				$rep->AmountCol(6, 7,$total,2,0,0,0,0,null,1,true);
					$rep->SetTextColor(0, 0, 0);
				// ($c, $n, $txt, $dec=0, $corr=0, $r=0, $border=0, $fill=0, $link=NULL, $stretch=1, $color_red=false)

        // display_error($total." ".$trans['alloc']);                 
				$pending=$total-$trans['alloc'];
	$amt_total += $total;
	// $qty_total += $trans['alloc'];
		$pending_amt += $pending;
				$rep->AmountCol(7, 8,	$pending,2,0,0,0,0,null,1,true);
					
				$rep->AmountCol(8, 9,	$pending_amt,2,0,0,0,0,null,1,true);
				// $rep->TextCol(8, 9,	$trans['due_date']);
				$data = Today();

				// $bal_amt=$bal_amt+$trans['TotalAmount'];
				// display_error($data);
				if(!$aging_id)
				$numberDays = date_diff2(sql2date($trans['tran_date']), $data, 'd');
				else
					$numberDays = date_diff2(sql2date($trans['due_date']), $data, 'd');
				// display_error(Today($trans['tran_date']));
					// $rep->AmountCol(7, 8, $numberDays); 
				$rep->TextCol(9, 10, abs($numberDays));	
				// $total = abs($trans['TotalAmount']) ; //($trans['quantity']*$trans['unit_price']) * $trans['rate'] ;
				// $rep->AmountCol(10, 11, $total, $dec);

				// if ($trans['type'] == ST_SUPPCREDIT || $trans['type'] == ST_SUPPAYMENT || $trans['type'] == ST_BANKPAYMENT)
				// $bal_amt = $bal_amt-$trans['TotalAmount'];
			    // else


				
				$rep->NewLine();

				if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
				{
					$rep->Line($rep->row - 2);
					$rep->NewPage();
				}
		
				// $bal_amt = $bal_amt+$trans['TotalAmount'];
	// display_error($amt_total);
	}


	$rep->Font('bold');
	$rep->NewLine();
	$rep->Line($rep->row + $rep->lineHeight);
	$rep->TextCol(0, 2,	_("Total"));
	$rep->AmountCol(6, 7, $amt_total, $dec);
	$rep->AmountCol(7, 8, $pending_amt, $dec);
	$rep->NewLine();
    $rep->TextCol(0, 2,	_("Actual Credit Debit Balance"));
	$rep->AmountCol(8, 9, $bal_amt, $dec);
	$rep->Line($rep->row - 5,2);
	$rep->Font();
		$rep->NewLine(1.5);

 }	

	$rep->End();
}

