<?php
/**********************************************************************
   Copyright (C) Evolve Techniologies
	Author : Danushka Widanage
	Email : dwidanage@gmail.com
	Company : Evolve Technologies(pvt)Ltd
***********************************************************************/
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	evolve
// date_:	2005-05-19
// Title:	Inventory Valuation
// ----------------------------------------------------------------
$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 . "/inventory/includes/db/items_category_db.inc");

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

print_inventory_location_report();

function getSales_Salesman_Total_Tax($from, $to, $sales_type, $main_cat){
	$fromdate = date2sql($from);
	$todate = date2sql($to);
	
	$sql = "SELECT mc.main_cat_name, mc.main_category_id, sc.description, sc.category_id , dtd.debtor_trans_no, dtd.stock_id, 
				dt.tpe,  IF( (dt.tpe = ".db_escape($sales_type)."),sum(dtd.unit_price*dtd.quantity), 0 ) AS total, cb.salesman, s.salesman_name, s.salesman_code, 
				(SELECT sales_type FROM ".TB_PREF."sales_types WHERE id = dt.tpe) AS stype
				
				FROM ".TB_PREF."main_category AS mc , ".TB_PREF."stock_category AS sc, ".TB_PREF."stock_master AS sm, ".TB_PREF."debtor_trans_details AS dtd, 
				".TB_PREF."debtor_trans AS dt,".TB_PREF."cust_branch AS cb, ".TB_PREF."salesman AS s
		
			WHERE mc.main_category_id = sc.main_category_id
				AND sc.category_id = sm.category_id
				AND sm.stock_id = dtd.stock_id
				AND dtd.debtor_trans_no = dt.trans_no
				AND dt.branch_code = cb.branch_code
				AND cb.salesman = s.salesman_code
				AND (dtd.debtor_trans_type = ".ST_SALESINVOICE." OR dtd.debtor_trans_type = ".ST_CUSTCREDIT.")
				AND (dt.type = ".ST_SALESINVOICE." OR dt.type = ".ST_CUSTCREDIT.")
				AND dt.tran_date>=".db_escape($fromdate)." 
				AND dt.tran_date<=".db_escape($todate)."
				AND mc.main_category_id = ".db_escape($main_cat)."
				AND dt.tpe = ".db_escape($sales_type)."
			GROUP BY s.salesman_code
			ORDER BY stype, s.salesman_code";

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

function print_inventory_location_report(){
    global $path_to_root;
	
	$from = $_POST['PARAM_0'];
	$to = $_POST['PARAM_1'];
	$main_category = $_POST['PARAM_2'];
	
	$main_cat = get_main_category_name($main_category);
	//include_once($path_to_root . "/reporting/includes/pdf_report.inc");
	include_once($path_to_root . "/reporting/includes/excel_report.inc");
    $dec = user_price_dec();
	
	$headers = array();
	$headers1[] = '';
	$headers1[] = 'Description';
	$headers1[] = 'Total with Vat';
	$headers1[] = 'Vat';
	$headers1[] = 'Total';

	$cols = array(0,10,  150, 250,  350, 450,  550, 650,   720);
	$headers = $headers1;
	$aligns = array('left',	'left',	'right', 'right',	'right',	'right', 'right', 'right');
    $params =   array( 	0 => $comments,
    					1 => array('text' => _('Main Category'), 'from' => $main_cat, 'to' => ''),
						2 => array('text' => _('Date'), 'from' => $from, 'to' => $to));

    $rep = new FrontReport(_('Sales Report'), "SalesReport", user_pagesize(), 10, 'P');

    $rep->Font();
    $rep->Info($params, $cols, $headers, $aligns);
    $rep->NewPage();
//$rep->TextColLines(1, 5, print_r($items), 10);

	$sql_sales_types = "SELECT * FROM `".TB_PREF."sales_types`";
	
	$result_sales_types = db_query($sql_sales_types);
	$grandtotal = 0;
	 
	while($row_sale_type = db_fetch($result_sales_types)){
		
		$subtotal = $total = $subprov = $provtotal = 0;
		$rep->Font('bold');
		$rep->TextCol(0, 2,	$row_sale_type['sales_type'], -2);
		$rep->Font();
		$rep->NewLine();
		
		$sales_type = $row_sale_type['id'];

		$result_sales = getSales_Salesman_Total_Tax($from, $to, $sales_type, $main_category);
		
		while($row_sales = db_fetch($result_sales)){
			
			$rep->TextCol(1, 2,	$row_sales['salesman_name'], -2);
			$rep->AmountCol(2, 3,	$row_sales['total'], $dec);
			$rep->AmountCol(3, 4,	($row_sales['total']/112)*12, $dec);
			$rep->AmountCol(4, 5,	($row_sales['total']/112)*100, $dec);
			$rep->NewLine();
			
			$subtotal += $row_sales['total'];
			$grandtotal += $row_sales['total'];

		}
		
		$rep->Line($rep->row  + 10);
		$rep->TextCol(0, 2,	'Sub Totals', -2);
		$rep->AmountCol(2, 3,	$subtotal, $dec);
		$rep->AmountCol(3, 4,	($subtotal/112)*12, $dec);
		$rep->AmountCol(4, 5,	($subtotal/112)*100, $dec);
		$rep->Font();
		$rep->NewLine();
		$rep->NewLine();
		
	}
	
	$rep->Line($rep->row  - 4);
	$rep->NewLine();
	$rep->NewLine();
	$rep->NewLine();
	$rep->fontSize += 2;
	$rep->TextCol(0, 2,	'Grand Total', -2);
	$rep->fontSize -= 2;
	$rep->AmountCol(2, 3,	$grandtotal, $dec);
	$rep->AmountCol(3, 4,	($grandtotal/112)*12, $dec);
	$rep->AmountCol(4, 5,	($grandtotal/112)*100, $dec);

	$rep->NewLine();
    $rep->End();
}

?>