<?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_CUST_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");
include_once($path_to_root . "/sales/includes/db/city_db.inc");

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


print_sales_summary_report();

// function getTaxTransactions($from, $to, $tax_id)
// {
// 	$fromdate = date2sql($from);
// 	$todate = date2sql($to);

// 	$sql = "SELECT d.debtor_no, d.name AS cust_name, d.tax_id, dt.type, dt.trans_no,  
// 			CASE WHEN dt.type=".ST_CUSTCREDIT." THEN (ov_amount+ov_freight+ov_discount)*-1 
// 			ELSE (ov_amount+ov_freight+ov_discount) END *dt.rate AS total
// 		FROM ".TB_PREF."debtor_trans dt
// 			LEFT JOIN ".TB_PREF."debtors_master d ON d.debtor_no=dt.debtor_no
// 		WHERE (dt.type=".ST_SALESINVOICE." OR dt.type=".ST_CUSTCREDIT.") ";
// 	if ($tax_id)
// 		$sql .= "AND tax_id<>'' ";
// 	$sql .= "AND dt.tran_date >=".db_escape($fromdate)." AND dt.tran_date<=".db_escape($todate)."
// 		ORDER BY d.debtor_no"; 
//     return db_query($sql,"No transactions were returned");
// }

function getTaxTransactions($from, $to, $customer_id=0, $branch_id=0, $city_id=0, $area=0, $broker=0, $stock_id=0, $item_group_id=0, $sales_type=0, $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."cust_allocations alloc
 				WHERE  date_alloc <= '$todate' AND alloc.person_id='$customer_id'
 				GROUP BY trans_type_from, trans_no_from) alloc_from";
 				//person_id=".db_escape($debtorno)." AND
 	$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."cust_allocations alloc
 				WHERE  date_alloc <= '$todate' AND alloc.person_id='$customer_id'
 				GROUP BY trans_type_to, trans_no_to) alloc_to";
 				//person_id=".db_escape($debtorno)." AND

   //, dt.alloc

 				// (dt.ov_amount + dt.ov_gst + dt.ov_freight + dt.ov_freight_tax + dt.ov_discount) AS TotalAmount,

 				$sql = "SELECT dm.name, dm.debtor_ref, dt.sales_person, dt.reference, dt.tran_date, dt.due_date, (dt.ov_amount + dt.ov_gst + dt.ov_freight + dt.ov_freight_tax + dt.ov_discount) as ov_amount,dt.type, 
        IFNULL(alloc_from.amount, alloc_to.amount) AS alloc, dm.city_id
        FROM " . TB_PREF . "debtor_trans as dt
        INNER JOIN " . TB_PREF . "debtors_master as dm ON dt.debtor_no = dm.debtor_no
        INNER JOIN " . TB_PREF . "cust_branch as cb ON dt.branch_code = cb.branch_code
        LEFT JOIN " . TB_PREF . "voided voided ON dt.type = voided.type AND dt.trans_no = voided.id
        LEFT JOIN $allocated_from ON alloc_from.trans_type = dt.type AND alloc_from.trans_no = dt.trans_no AND alloc_from.person_id=dt.debtor_no
        LEFT JOIN $allocated_to ON alloc_to.trans_type = dt.type AND alloc_to.trans_no = dt.trans_no AND alloc_to.person_id=dt.debtor_no";

	// $sql = " SELECT  dm.name,dm.debtor_ref, dt.sales_person, dt.reference, dt.tran_date, dt.due_date, (dt.ov_amount+dt.ov_gst+dt.ov_freight) as ov_amount , 
	//      		IFNULL(alloc_from.amount, alloc_to.amount) AS alloc ,dm.city_id FROM ".TB_PREF."debtor_trans as dt,  
	// 	".TB_PREF."debtors_master as dm,
	//     ".TB_PREF."cust_branch as cb
    //     LEFT JOIN ".TB_PREF."voided voided ON dt.type=voided.type AND dt.trans_no=voided.id
 	// 	LEFT JOIN $allocated_from ON alloc_from.trans_type = dt.type AND alloc_from.trans_no = dt.trans_no
 	// 	LEFT JOIN $allocated_to ON alloc_to.trans_type = dt.type AND alloc_to.trans_no = dt.trans_no ";

		$sql.=" WHERE dt.debtor_no=dm.debtor_no  AND  dt.branch_code=cb.branch_code  AND dt.ov_amount!=0  ";
		if(!$show_balance)
		$sql .= " AND (dt.type = ".ST_SALESINVOICE." OR dt.type = ".ST_JOURNAL." ) AND round(IF(dt.prep_amount, dt.prep_amount, dt.ov_amount+dt.ov_gst+dt.ov_freight+dt.ov_freight_tax+dt.ov_discount)-alloc,6) > 0 ";
	    else
	      $sql.= "AND dt.type <> ".ST_CUSTDELIVERY.""; 	
		 // AND (dt.ov_amount < IFNULL(alloc_from.amount, alloc_to.amount))

		// OR dt.type=".ST_CUSTCREDIT."
// round(IF(prep_amount, prep_amount, ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount)-alloc,6) > 0

			 $sql .= " AND dt.tran_date >=".db_escape($fromdate)." AND dt.tran_date<=".db_escape($todate);
			 if ($customer_id != 0)
			 $sql .= " AND dm.debtor_no =".db_escape($customer_id);
			 if ($area != 0)
			 $sql .= " AND cb.area =".db_escape($area); 
			if ($broker != -1)
			 $sql .= " AND dt.sales_person =".db_escape($broker);
			 // if ($item_group_id != 0)
			 // $sql .= " AND sm.item_group_id =".db_escape($item_group_id); 
			 if ($sales_type != 0)
			 $sql .= " AND dm.sales_type =".db_escape($sales_type);
			 //  if ($stock_id != '')
			 // $sql .= " AND sm.stock_id =".db_escape($stock_id); 
			if ($city_id != 0)
			 $sql .= " AND dm.city_id =".db_escape($city_id);
			if ($branch_id != 0)
			 $sql .= " AND dm.dimension2_id =".db_escape($branch_id);

             $sql.=" ORDER BY dt.tran_date ";
              // $sql.=" GROUP BY dt.trans_no,dt.type";
			 // display_error($sql);
			 $result = db_query($sql);
			 return $result;
			 // return db_fetch($result);

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

// function getTaxes($type, $trans_no)
// {
// 	$sql = "SELECT included_in_price, SUM(CASE WHEN trans_type=".ST_CUSTCREDIT." THEN -amount ELSE amount END * ex_rate) AS tax
// 		FROM ".TB_PREF."trans_tax_details WHERE trans_type=$type AND trans_no=$trans_no GROUP BY included_in_price";

//     $result = db_query($sql,"No transactions were returned");
//     if ($result !== false)
//     	return db_fetch($result);
//     else
//     	return null;
// }    	

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

function print_sales_summary_report()
{
	global $path_to_root;
	
	$from = $_POST['PARAM_0'];
	$to = $_POST['PARAM_1'];
	$customer_id = $_POST['PARAM_2'];
	$show_balance = $_POST['PARAM_3'];
	$branch_id = $_POST['PARAM_4'];
	$city_id = $_POST['PARAM_5'];
	$area = $_POST['PARAM_6'];
	$broker = $_POST['PARAM_7'];
	$stock_id = $_POST['PARAM_8'];
	$item_group_id = $_POST['PARAM_9'];
	$sales_type = $_POST['PARAM_10'];
	$aging_id = $_POST['PARAM_11'];
	$orientation = $_POST['PARAM_12'];
	$destination = $_POST['PARAM_13'];

	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 ($customer_id == ALL_TEXT)
		$cust = _('All');
	else
		$cust = get_customer_name($customer_id);
	$dec = user_price_dec();

	if ($broker == -1) 
		$sale_Prsn = _('All');
	else
		$sale_Prsn = get_sales_group_name($broker);

	if ($area == 0)
		$sarea = _('All');
	else
		$sarea = get_area_name($area);

	if ($city_id == 0)
		$citty = _('All');
	else
		$citty = get_city_name($city_id);

	if ($branch_id == 0)
		$if_branch = _('All Branch');
	else
		$if_branch = get_dimension_string($branch_id);

	if ($item_group_id == 0) 
	 	$item_group = _('All');
	 else
	 	$item_group = get_item_group_name($item_group_id);
	
	if ($sales_type == 0) 
		$type = _('All');
	else
		$type = get_sales_type_name($sales_type);

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

	$params =   array( 	0 => $comments,
						1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
						2 => array( 'text' => _('Customer Id'),'from' => $cust,'to' => ''),
						3 => array( 'text' => _('Branch'),'from' => $if_branch,'to' => ''),
						4 => array( 'text' => _('City'),'from' => $citty,'to' => ''),
						5 => array( 'text' => _('Area'),'from' => $sarea,'to' => ''),
						6 => array( 'text' => _('Sales Person'),'from' => $sale_Prsn,'to' => ''),
						7 => array( 'text' => _('Item Stock'),'from' => $stock_id,'to' => ''),
						8 => array( 'text' => _('Items Group'),'from' => $item_group,'to' => ''),
						9 => array( 'text' => _('Sales Type'),'from' => $type,'to' => ''));
						// 4 => array( 'text' => _('Branch Id'),'branch_id' => $branch_id,'to' => ''),

	$cols = array(0, 100,170, 220,260, 330, 380, 420, 470, 530);

	$headers = array(_('Customer'),_('Sales Type'), _('Broker'), _('City'), _(' Invoice NO'), _('Inv Date'), _('Due Date'), _('Tot Amount'), _('Outstanding Amt'), _('Days'));
	$aligns = array('left', 'left', 'left','left', 'left', 'left', 'left', 'right', 'right', 'right');
    if ($orientation == 'L')
    	recalculate_cols($cols);

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




	$rep->NewLine(2);

	$amt_total = 0;
	$qty_total = 0;
	$pending_amt = 0;
	$bal_amt=0;

		$sql = "SELECT debtor_no, name,debtor_ref, curr_code, dimension2_id, city_id , sales_type FROM ".TB_PREF."debtors_master WHERE !inactive ";
	if ($customer_id != ALL_TEXT)
		$sql .= "AND debtor_no=".db_escape($customer_id);

	$result = db_query($sql, "The customers could not be retrieved");

	while ($myrow = db_fetch($result))
	{

	$transactions = getTaxTransactions($from, $to, $myrow['debtor_no'], $branch_id, $city_id, $area, $broker, $stock_id, $item_group_id, $sales_type, $show_balance);

	
	while ($trans=db_fetch($transactions))
	{

		        if(!$destination)
		        {

					$oldrow = $rep->row;
					$rep->TextColLines(0, 1, $trans['debtor_ref'] ." - ". $trans['name']);

					$newrow = $rep->row;
					$rep->row = $oldrow;

		        }
		        else
		        {

				  $rep->TextCol(0, 1,	$trans['debtor_ref'] ." - ". $trans['name']);
		          	
		        }

		        $rep->TextCol(1, 2, get_sales_type_name($myrow['sales_type']));
				$rep->TextCol(2, 3, get_salesman_name($trans['sales_person']));
				$rep->TextCol(3, 4, get_city_name($trans['city_id']));
				
				$rep->TextCol(4, 5,	$trans['reference']);
				$rep->TextCol(5, 6,	$trans['tran_date']);
				$rep->TextCol(6, 7,	$trans['due_date']);
				if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT || $trans['ov_amount']<0)
			     $rep->SetTextColor(255, 0, 0);
                else
                 $rep->SetTextColor(0, 0, 0);
                 					
				$total = abs($trans['ov_amount']);//*$trans['unit_price'];
				$rep->AmountCol(7, 8, $total, $dec);
				  $rep->SetTextColor(0, 0, 0);
				// display_error($trans['trans_no']." / ".$trans['trans_type']." / ".$trans['ov_amount']);
				$pending = $total-$trans['alloc'];
				$rep->AmountCol(8, 9, $pending, $dec);


				if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT)
				$bal_amt = $bal_amt-$trans['ov_amount'];
			    else
				$bal_amt = $bal_amt+$trans['ov_amount'];
				// $trans['TotalAmount'] *= -1;
				
				// $rep->TextCol(2, 3,	$trans['quantity']);

//----------------------------------------------------------------------------------------------------------------------------------------------
				$data = Today();
				// 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']));

				if ($trans['type'] == ST_JOURNAL || $trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT)
					$rep->AmountCol(9,10,0);
				else	
					$rep->AmountCol(9, 10, $numberDays);
//----------------------------------------------------------------------------------------------------------------------------------------------

		           if(!$destination)   		
					$rep->row = $newrow;
				 else
				$rep->NewLine();

				if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
				{
					$rep->Line($rep->row - 2);
					$rep->NewPage();
				}
		
	$amt_total += $total;
	// $qty_total += $trans['quantity'];
	// display_error($amt_total);
	$pending_amt += $pending;
	}

 }	

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

	$rep->End();
}

