<?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_SALES_DETAIL_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_sales_summary_report();

// function getSalesTransactions($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 getSalesTransactions($from, $to,$city_id=0, $dimension_id2=0, $customer_id=0, $branch_id=0, $area=0, $broker=0, $stock_id, $item_group_id=0, $sales_type=0){
	$fromdate = date2sql($from);
	$todate = date2sql($to);

	$sql = " SELECT dtd.description, ((dtd.unit_price * IF(dt.type=".ST_CUSTCREDIT.",-dtd.quantity,dtd.quantity) ) * dt.rate) AS total , IF(dt.type=".ST_CUSTCREDIT.",-dtd.quantity,dtd.quantity) as quantity,dtd.unit_price,dtd.batch_no, dtd.stock_id , sm.units, dm.name, dm.debtor_ref, dt.tran_date, dt.sales_person, dt.order_, dt.reference, dt.trans_no, dt.rate ,dm.sales_type, dm.city_id  FROM ".TB_PREF."debtor_trans as dt,  ".TB_PREF."debtor_trans_details as dtd,
			".TB_PREF."debtors_master as dm,  ".TB_PREF."stock_master as sm,  ".TB_PREF."cust_branch as cb
		WHERE dt.trans_no=dtd.debtor_trans_no AND dtd.debtor_trans_type=dt.type AND  dt.debtor_no=dm.debtor_no AND dtd.stock_id=sm.stock_id AND  dt.branch_code=cb.branch_code AND ( dt.type=10 OR dt.type=11 ) AND  dtd.quantity>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 ($dimension_id2 != 0)
			 $sql .= " AND dm.dimension2_id =".db_escape($dimension_id2);

			 if ($city_id != 0)
			 $sql .= " AND dm.city_id =".db_escape($city_id);

			 $sql .= "ORDER BY dt.tran_date,dt.debtor_no";

              // $sql.=" GROUP BY dm.name";
			     // $sql.=" GROUP BY dtd.id";
              // 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'];
	$city_id = $_POST['PARAM_2'];
	$dimension_id2 = $_POST['PARAM_3'];
	$customer_id = $_POST['PARAM_4'];
	$area = $_POST['PARAM_5'];
	$broker = $_POST['PARAM_6'];
	$stock_id = $_POST['PARAM_7'];
	$item_group_id = $_POST['PARAM_8'];
	$sales_type = $_POST['PARAM_9'];
	$orientation = $_POST['PARAM_10'];
	$destination = $_POST['PARAM_11'];
	// print_r($_POST);exit();
	// display_error(var_dump($_POST));

	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' : 'L');

	if ($dimension_id2 == 0)
		$dim2 = _('All City Wise');
	else
		$dim2 = get_dimension_string($dimension_id2);

	if ($city_id == 0)
		$citty = _('All');
	else
		$citty = get_city_name($city_id);
		
	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 ($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(_('Gross Profit Analysis'), "GrossProfitAnalysis", user_pagesize(), 9, $orientation);
global $SysPrefs;

	$params =   array( 	0 => $comments,
						1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
						2 => array( 'text' => _($SysPrefs->prefs['dim2_name']),'from' => $dim2,'to' => ''),
						3 => array( 'text' => _('City'),'from' => $citty,'to' => ''),
						4 => array( 'text' => _('Customer Id'),'from' => $cust,'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' => ''));
						// 2 => array( 'text' => _('Tax Id Only'),'from' => $tid,'to' => ''),
						// 4 => array( 'text' => _('Branch Id'),'branch_id' => $branch_id,'to' => ''),

	$cols = array(0, 100, 200, 280, 310, 330, 350, 400, 460, 510, 560);
                                                                                // _('SO NO'), _('DC NO'),
	// ,dm.sales_type,dm.city_id 
	$headers = array(_('Customer'), _('Item'), _('Sales Type'), _('Qty'), _('Unit'), _('Price'), _('Sales'), _('Cost per Unit'), _('Cost') ,_('GP'));
	$aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right', 'right');
    if ($orientation == 'L')
    	recalculate_cols($cols);

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);
	$rep->NewPage();
	
	$totalnet = 0.0;
	$transactions = getSalesTransactions($from, $to, $city_id, $dimension_id2, $customer_id, $branch_id, $area, $broker, $stock_id, $item_group_id, $sales_type);

	$rep->NewLine(2);
	
	$amt_total = 0;
	$qty_total = 0;
	$cost_total = 0;
	$gp_total = 0;
	while ($trans=db_fetch($transactions))
	{
		
				// $rep->TextCol(0, 1, $trans['tran_date'],-2);

				
	            if ($destination)
	            {
					$rep->TextCol(0, 1, $trans['debtor_ref'] ." - ".  $trans['name']);
					$rep->TextCol(1, 2, $trans['description']);
	            }	
	            else{

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

					$oldrow = $rep->row;
					$rep->TextColLines(1, 2,	$trans['description'],-2);
					$newrow = $rep->row;
					$rep->row = $oldrow;
	            }
				// $rep->TextCol(2, 3,	get_salesman_name($trans['sales_person']),-2);
				// $rep->TextCol(4, 5,	$trans['order_']);
				// $data = get_sales_parent_numbers(ST_SALESINVOICE, $trans['trans_no']);
				// $rep->TextCol(5, 6, $data[0]);

				$rep->TextCol(2, 3, get_sales_type_name($trans['sales_type']));
				// $rep->TextCol(3, 4, get_city_name($trans['city_id']));
				// display_error($data[0]);
				// exit();
				// display_error($data);
				// $rep->TextCol(5, 6,	$trans['reference']);
				$rep->TextCol(3, 4,	$trans['quantity']);
				$rep->TextCol(4, 5,	$trans['units']);
				$rep->TextCol(5, 6, $trans['unit_price']);	
				$total = $trans['total']; //($trans['quantity']*$trans['unit_price']) * $trans['rate'] ;
				$rep->AmountCol(6, 7, $total, $dec);
				$price=round(get_betch_price($trans['batch_no'],$trans['stock_id'],true,sql2date($trans['tran_date']) ),2) ;
				// $trans['cost']=$price;
				$rep->TextCol(7, 8, $price);
                $cost_amt=$price*$trans['quantity'];
				$rep->TextCol(8, 9, $cost_amt);
                $gp_price =  $total-$cost_amt;
                $rep->TextCol(9, 10, $gp_price);

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


				
				$rep->NewLine();

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

	$rep->Font('bold');
	$rep->NewLine();
	$rep->Line($rep->row + $rep->lineHeight);
	$rep->TextCol(0, 2,	_("Total"));
	$rep->AmountCol(3, 4, $qty_total, $dec);
	$rep->AmountCol(6, 7, $amt_total, $dec);
	$rep->AmountCol(8, 9, $cost_total, $dec);
	$rep->AmountCol(9, 10, $gp_total, $dec);
	
	$rep->Line($rep->row - 5);
	$rep->Font();

	$rep->End();
}

