<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
	header("location:../index.php");
}

print <<< HERE
<HTML>
<HEAD>
<TITLE> Sales By Warehouse Report </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

table.master th { font-size: 13px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                }				 
table.master td { font-size: 12px; 
                  font-family:Times-New-Roman;
                }
table.master tr.group { font-size: 14px; 
                        font-weight: bold;
                        font-family:Times-New-Roman;
                      }				 				

table.master tr.totals { font-size: 14px; 
                         font-weight: bold;
                         font-family:Times-New-Roman;
						 background-color: #F0F0F0;
                       }
				 

</style>
</HEAD>


HERE;


include("config.inc.php");

//Set Time Execution For the Report
ini_set('max_execution_time', $config['ReportTimeExecution']);

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");


if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}
$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';


$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, report_color1, report_color2, Company_Name As CompanyName, ifNull(ClientSalesRMaskQty,0) As ClientSalesRMaskQty from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
} 

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse, ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Grn,0) As Module_Grn, ifNull(Module_Delivery,0) As Module_Delivery, ifnull(module_chargesInTransfer,0) as module_chargesInTransfer From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist  = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);		

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$_SESSION['dbLabel'].".loginstk ".
		 "                                          where user_account='".$_SESSION['useraccount']."' ".
		 "                                          and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '') ) ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist  = mysql_num_rows($resultwh);


$querygroup=" select distinct group_code,group_name ".
			" from grouping where group_code in (select group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."') ".
			" order by group_code";
$resultgroup = mysql_query($querygroup,$connection) or die(mysql_error());
$groupexist  = mysql_num_rows($resultgroup);

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);

print <<< HERE
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>Sales By Warehouse</b></font></td>
  <td width='10%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
//echo DATE("d"."/"."m"."/"."Y");
echo "<br>".$dt[1]." ".$dt[2];


if ($curr == 'currency1'){
   $curr='null';  
   $displaycur = '';
}   
else {
   $displaycur = $curr;
   $curr="'".$curr."'";
}   

$fromwh = $fromwarehouse;

$subproj 	 = ereg_replace('-','%',$subproj);
$subcostcent = ereg_replace('-','%',$subcostcent);

$SelectedOptions = "Filter by: <br>";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."<br>";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."<br>";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."<br>";
if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."<br>";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."<br>";
if ($fromcategory != '')  $SelectedOptions .= "-> Category From: ". $fromcategory . "  To: ". $tocategory ."<br>";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "<br>";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "<br>";
if ($fromclient != '')    $SelectedOptions .= "-> Client From: ". $fromclient . "  To: ". $toclient ."<br>";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier ."<br>";
if ($fromsalesman != '')  $SelectedOptions .= "-> Salesman From: ". $fromsalesman . "  To: ". $tosalesman ."<br>";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "<br>";
if ($fromarea != '') 	  $SelectedOptions .= "-> Area From: ". $fromarea . "<br>";
if ($subproj != '') 	  $SelectedOptions .= "-> Project Include: ". $subproj . "<br>";
if ($subcostcent != '')	  $SelectedOptions .= "-> Costcent Include: ". $subcostcent . "<br>";

  
if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);  
   
$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromproject   = GetSQLValueString($fromproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromcategory  = GetSQLValueString($fromcategory, 'text');
$tocategory    = GetSQLValueString($tocategory, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$fromarea      = GetSQLValueString($fromarea, 'text');
$fromclient    = GetSQLValueString($fromclient, 'text');
$toclient      = GetSQLValueString($toclient, 'text');
$fromsupplier  = GetSQLValueString($fromsupplier, 'text');
$tosupplier    = GetSQLValueString($tosupplier, 'text');
$fromsalesman  = GetSQLValueString($fromsalesman, 'text');
$tosalesman    = GetSQLValueString($tosalesman, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');     
$subproj 	   = GetSQLValueString($subproj, 'text');
$subcostcent   = GetSQLValueString($subcostcent, 'text');        
	
if (empty($prtcost))      $prtcost = 0;
if (empty($showgroup))    $showgroup = 0;
if (empty($showsubgroup)) $showsubgroup = 0;
if (empty($showcategory)) $showcategory = 0;

if(empty($activeclt))  $activeclt = 1;
if(empty($activeitem)) $activeitem = 1;
				
print <<<HERE
</font></td>
</tr>
<tr>
   <td colspan='4' width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>


<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr>
<tr>
   <td width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>
HERE;

$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) Q5  ";


$query = '';

$query = $query." Select CategoryCode, CategoryName, GroupCode, GroupName, SubGroupCode, SubGroupName, ItemCode, ItemDescription, ".
				" WarehouseCode, WarehouseName, Sum(QtyNetSold) As QtyNetSold, CurrencyCode, (Sum(SellAmount) + Sum(VatAmount)) as SellAmountVat, ".
				" Sum(VatAmount) As VatAmount, Sum(DiscountAmount) As DiscountAmount, MeanCost ";


$query = $query." From (   ".
			    "     Select CategoryCode, CategoryName, GroupCode, GroupName, SubGroupCode, SubGroupName, ItemCode, ItemDescription, ".
				" 	  WarehouseCode, WarehouseName, ifNull(QtyNetSold,0) as QtyNetSold, CurrencyCode, (ifNull(UnitPrice,0)*ifNull(QtyNetSold,0)) As SellAmount, ".
				"     ((ifNull(ItemVat,0)*ifNull(UnitPrice,0)*ifNull(QtyNetSold,0))/100) As VatAmount, ifNull(DiscountAmount,0) As DiscountAmount, ";

if ($FI_ShowCostPrice == 1 && $prtcost == 1) {	
	if ($module_chargesInTransfer == 1 && $fromwh != '')
		$query=$query." ifNull(MeanCostCurByWH(ItemCode, concat(".$todate.", ' 23:59:59'), if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), CurrencyCode, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','', ItemCurrCode )),'$fromwh'),0) MeanCost  ";
	else 		
		$query=$query." ifNull(MeanCostCur(ItemCode, concat(".$todate.", ' 23:59:59'), if(upper($curr)='".$Base1."', 1,if(upper($curr)='".$Base2."',2, 1)), CurrencyCode, if(upper($curr)='".$Base1."', '',if(upper($curr)='".$Base2."','', ItemCurrCode ))),0) MeanCost  ";
} else {
	$query=$query." 0 as MeanCost ";
}
	

$query = $query." From ( ";
				
if($Module_Delivery == 1 ) {	
			
	$query = $query." Select SalesDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, Items.Currency_Code As ItemCurrCode, ".
					" Items.Group_Code As GroupCode,Grouping.Group_Name As GroupName, ".
					" Items.SubGroup_Code As SubGroupCode,Subgroup.Subgroup_Name As SubGroupName, ".
					" Items.Category_Code As CategoryCode, Category.Category_Name As CategoryName, ".
					" SalesDt.Warehouse_Code As WarehouseCode, Warehouse.Warehouse_Name As WarehouseName, ".
					" if(SalesDt.Journal_Type = 'SI',SalesDt.SaleDt_Quantity,(-1)*SalesDt.SaleDt_Quantity) As QtyNetSold, ".
					" Sales.Currency_Code As CurrencyCode, ".
					" if($pricettc=1, if(ifNull(SalesDt.SaleDt_PriceTTC,0)=0, 0,  ".
					" ifNull( (((if(upper($curr)='".$Base1."',SalesDt.SaleDt_PriceTTCBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_PriceTTCBase2, SalesDt.SaleDt_PriceTTC)) - ".
					"         (ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_PriceTTCBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_PriceTTCBase2, SalesDt.SaleDt_PriceTTC)),0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"         ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_DiscountBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_DiscountBase2,SalesDt.SaleDt_DiscountAmount)),0))/SalesDt.SaleDt_Quantity) ,0)),  ".
					" if(ifNull(SalesDt.SaleDt_Price,0)=0, 0,  ".
					" ifNull( (((if(upper($curr)='".$Base1."',SalesDt.SaleDt_PriceBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_PriceBase2, SalesDt.SaleDt_Price)) - ".
					"         (ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_PriceBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_PriceBase2, SalesDt.SaleDt_Price)),0) * ifNull(SalesDt.SaleDt_discountper, 0)/100))*SalesDt.SaleDt_Quantity - ".
					"         ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_DiscountBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_DiscountBase2,SalesDt.SaleDt_DiscountAmount)),0))/SalesDt.SaleDt_Quantity) ,0)) ) As UnitPrice,  ".
					" if(ifNull(sale_chkvat,0) = 1, SalesDt.Item_Vat, 0) As ItemVat, ".
					" if($pricettc=1, ((ifNull(if(upper($curr)='".$Base1."',ifNull(SalesDt.SaleDt_PriceTTCBase1,0),if(upper($curr)='".$Base2."',ifNull(SalesDt.SaleDt_PriceTTCBase2,0), ifNull(SalesDt.SaleDt_PriceTTC,0))),0) * ifNull(SalesDt.SaleDt_discountper, 0)/100)*SalesDt.SaleDt_Quantity + ".
					"                   ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_DiscountBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_DiscountBase2,SalesDt.SaleDt_DiscountAmount)),0)),  ".
					"                 ((ifNull(if(upper($curr)='".$Base1."',ifNull(SalesDt.SaleDt_PriceBase1,0),if(upper($curr)='".$Base2."',ifNull(SalesDt.SaleDt_PriceBase2,0), ifNull(SalesDt.SaleDt_Price,0))),0) * ifNull(SalesDt.SaleDt_discountper, 0)/100)*SalesDt.SaleDt_Quantity + ".
					"                   ifNull(if(upper($curr)='".$Base1."',SalesDt.SaleDt_DiscountBase1,if(upper($curr)='".$Base2."',SalesDt.SaleDt_DiscountBase2,SalesDt.SaleDt_DiscountAmount)),0)) ) As DiscountAmount  ";

			   
	$query = $query." From SalesDt  ";
			  
	$query = $query." Inner Join Sales on SalesDt.Sale_ID = Sales.Sale_ID ".
					" Inner Join Items on SalesDt.Item_Code = Items.Item_Code ".
					" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
					" Left Join SubGroup on Items.SubGroup_Code = SubGroup.SubGroup_Code ".
					" Left Join Category on Items.Category_Code = Category.Category_Code ".
					" Left Join Warehouse on Warehouse.Warehouse_Code = SalesDt.Warehouse_Code ";
							  
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query = $query.$QueryRestrictPrj." on Sales.project_code like concat(Q4.project_code,'%') ";
									 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query = $query.$QueryRestrictCst." on Sales.Costcenter_code like concat(Q5.costcent_code,'%') ";
			

	$query= $query. " Where SalesDt.Journal_Type in ('SI','SR') ".
					" And (Sales.Sale_Reference Is Not Null And Sales.Sale_Reference != '')  ".
					" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And SalesDt.Item_Code between ifNull(".$fromitem.",SalesDt.Item_Code) and ifNull(". $toitem.",SalesDt.Item_Code) ".
					" And ifNull(Items.group_code,'-1') between ifNull(". $fromgroup .",ifNull(Items.group_code,'-1')) and ifNull(". $togroup.",ifNull(Items.group_code,'-1'))  ".
					" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
					" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And Sales.Client_Code between ifNull(".$fromclient.",Sales.Client_Code) and ifNull(". $toclient.",Sales.Client_Code) ".
					" And ifNull(Sales.Salesman_Code,'-1') between ifNull(".$fromsalesman.",ifNull(Sales.Salesman_Code,'-1')) and ifNull(". $tosalesman.",ifNull(Sales.Salesman_Code,'-1')) ".
					" And ifNull(Items.Ledger_Number,'-1') between ifNull(".$fromsupplier.", ifNull(Items.Ledger_Number,'-1')) and ifNull(". $tosupplier.", ifNull(Items.Ledger_Number,'-1')) ".
					" And ifNull(Sales.project_code,'-1')     Like Concat( ifNull(".$fromproject.",ifNull(Sales.project_code,'-1')),'%')  ".
					" And ifNull(Sales.Costcenter_code,'-1')  Like Concat(ifNull(".$fromcostcent.",ifNull(Sales.Costcenter_code,'-1')),'%')  ".
					" And ifNull(SalesDt.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(SalesDt.warehouse_code,'-1')),'%')  ".
					" And (($promprint=1 and ifNull(SaleDt_Promotion,0) = 1) Or ($promprint=2 and ifNull(SaleDt_Promotion,0) = 0) Or ($promprint=3 and 1 = 1))  ".
					" And (($zeroprint=1 and ifNull(SalesDt.SaleDt_Price,0) = 0 and ifNull(SaleDt_Promotion,0) = 0) Or ($zeroprint=2 and ifNull(SalesDt.SaleDt_Price,0) != 0) Or ($zeroprint=3 and 1 = 1))  ".
					" And (($activeitem=1 and ifNull(Items.Item_Active,0) = 1) or ($activeitem=2 and ifNull(Items.Item_Active,0) = 0) or ($activeitem=3 and 1 = 1)) 
					  And Items.Item_showInSalesReport = 1 ";
							  
	if ($subproj != 'Null')
		$query= $query." And ifNull(Sales.project_code,'-1')  Like ".$subproj;  
	if ($subcostcent != 'Null')
		$query= $query." And ifNull(Sales.Costcenter_code,'-1')  Like ".$subcostcent;  						  
	  
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
	  $query= $query." And SalesDt.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	if ($groupexist > 0)
	  $query= $query." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

} else {

	$query = $query." Select DeliveryDt.Item_Code As ItemCode, Items.Item_Description As ItemDescription, Items.Currency_Code As ItemCurrCode, ".
					" Items.Group_Code As GroupCode,Grouping.Group_Name As GroupName, ".
					" Items.SubGroup_Code As SubGroupCode,Subgroup.Subgroup_Name As SubGroupName, ".
					" Items.Category_Code As CategoryCode, Category.Category_Name As CategoryName, ".
					" DeliveryDt.Warehouse_Code As WarehouseCode, Warehouse.Warehouse_Name As WarehouseName, ".
					" if(DeliveryDt.Journal_Type = 'DI',DeliveryDt.DeliveryDt_Quantity, (-1)*DeliveryDt.DeliveryDt_Quantity) As QtyNetSold, ".
					" Deliveries.Currency_Code As CurrencyCode, ".
					" if($pricettc=1, if(ifNull(DeliveryDt.DeliveryDt_PriceTTC,0)=0, 0,  ".
					" ifNull( (((if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_PriceTTCBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_PriceTTCBase2, DeliveryDt.DeliveryDt_PriceTTC)) - ".
					"         (ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_PriceTTCBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_PriceTTCBase2, DeliveryDt.DeliveryDt_PriceTTC)),0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"         ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_DiscountBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_DiscountBase2,DeliveryDt.DeliveryDt_DiscountAmount)),0))/DeliveryDt.DeliveryDt_Quantity) ,0)),  ".
					" if(ifNull(DeliveryDt.DeliveryDt_Price,0)=0, 0,  ".
					" ifNull( (((if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_PriceBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_PriceBase2, DeliveryDt.DeliveryDt_Price)) - ".
					"         (ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_PriceBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_PriceBase2, DeliveryDt.DeliveryDt_Price)),0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100))*DeliveryDt.DeliveryDt_Quantity - ".
					"         ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_DiscountBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_DiscountBase2,DeliveryDt.DeliveryDt_DiscountAmount)),0))/DeliveryDt.DeliveryDt_Quantity) ,0)) ) As UnitPrice,  ".
					" if(ifNull(Delivery_VAT,0) > 0, DeliveryDt.Item_Vat, 0) As ItemVat, ".
					" if($pricettc=1, ((ifNull(if(upper($curr)='".$Base1."',ifNull(DeliveryDt.DeliveryDt_PriceTTCBase1,0),if(upper($curr)='".$Base2."',ifNull(DeliveryDt.DeliveryDt_PriceTTCBase2,0), ifNull(DeliveryDt.DeliveryDt_PriceTTC,0))),0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100)*DeliveryDt.DeliveryDt_Quantity + ".
					"                   ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_DiscountBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_DiscountBase2,DeliveryDt.DeliveryDt_DiscountAmount)),0)),  ".
					"                 ((ifNull(if(upper($curr)='".$Base1."',ifNull(DeliveryDt.DeliveryDt_PriceBase1,0),if(upper($curr)='".$Base2."',ifNull(DeliveryDt.DeliveryDt_PriceBase2,0), ifNull(DeliveryDt.DeliveryDt_Price,0))),0) * ifNull(DeliveryDt.DeliveryDt_discountper, 0)/100)*DeliveryDt.DeliveryDt_Quantity + ".
					"                   ifNull(if(upper($curr)='".$Base1."',DeliveryDt.DeliveryDt_DiscountBase1,if(upper($curr)='".$Base2."',DeliveryDt.DeliveryDt_DiscountBase2,DeliveryDt.DeliveryDt_DiscountAmount)),0)) ) As DiscountAmount  ";


	$query = $query." From DeliveryDt  ";
				
	$query = $query." Inner Join Deliveries on DeliveryDt.Delivery_ID = Deliveries.Delivery_ID ".
					" Inner Join Items on DeliveryDt.Item_Code = Items.Item_Code ".
					" Left Join Grouping on Items.Group_Code = Grouping.Group_Code ".
					" Left Join SubGroup on Items.SubGroup_Code = SubGroup.SubGroup_Code ".
					" Left Join Category on Items.Category_Code = Category.Category_Code ".
					" Left Join Warehouse on Warehouse.Warehouse_Code = DeliveryDt.Warehouse_Code ";
				
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query = $query.$QueryRestrictPrj." on Deliveries.project_code like concat(Q4.project_code,'%') ";
									 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query = $query.$QueryRestrictCst." on Deliveries.Costcent_Code like concat(Q5.costcent_code,'%') ";			
						  

	$query = $query." Where DeliveryDt.Journal_Type in ('DI','DR') ".
					" And (Deliveries.Delivery_Reference Is Not Null And Deliveries.Delivery_Reference != '')  ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And DeliveryDt.item_code between ifNull(".$fromitem.",DeliveryDt.item_code) and ifNull(". $toitem.",DeliveryDt.item_code) ".
					" And ifNull(Items.group_code,'-1') between ifNull(". $fromgroup .",ifNull(Items.group_code,'-1')) and ifNull(". $togroup.",ifNull(Items.group_code,'-1'))  ".
					" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
					" And ifNull(Items.Category_Code,'-1') between ifNull(". $fromcategory .",ifNull(Items.Category_Code,'-1')) and ifNull(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
					" And Deliveries.Client_Code between ifNull(".$fromclient.",Deliveries.Client_Code) and ifNull(". $toclient.",Deliveries.Client_Code) ".
					" And ifNull(Deliveries.Salesman_Code,'-1') between ifNull(".$fromsalesman.",ifNull(Deliveries.Salesman_Code,'-1')) and ifNull(". $tosalesman.",ifNull(Deliveries.Salesman_Code,'-1')) ".
					" And ifNull(Items.Ledger_Number,'-1') between ifNull(".$fromsupplier.", ifNull(Items.Ledger_Number,'-1')) and ifNull(". $tosupplier.", ifNull(Items.Ledger_Number,'-1')) ".
					" And ifNull(Deliveries.project_code,'-1')   Like Concat(ifNull(".$fromproject.",ifNull(Deliveries.project_code,'-1')),'%')  ".
					" And ifNull(Deliveries.Costcent_Code,'-1')  Like Concat(ifNull(".$fromcostcent.",ifNull(Deliveries.Costcent_Code,'-1')),'%')  ".
					" And ifNull(DeliveryDt.warehouse_code,'-1') Like Concat(ifNull(". $fromwarehouse .",ifNull(DeliveryDt.warehouse_code,'-1')),'%')  ".
					" And (($promprint=1 and ifNull(DeliveryDt_Promotion,0) = 1) Or ($promprint=2 and ifNull(DeliveryDt_Promotion,0) = 0) Or ($promprint=3 and 1 = 1))  ".
					" And (($zeroprint=1 and ifNull(DeliveryDt.DeliveryDt_Price,0) = 0 and ifNull(DeliveryDt_Promotion,0) = 0) Or ($zeroprint=2 and ifNull(DeliveryDt.DeliveryDt_Price,0) != 0) Or ($zeroprint=3 and 1 = 1))  ".
					" And (($activeitem=1 and ifNull(Items.Item_Active,0) = 1) or ($activeitem=2 and ifNull(Items.Item_Active,0) = 0) or ($activeitem=3 and 1 = 1)) 
					  And Items.Item_showInSalesReport = 1";
						  
	if ($subproj != 'Null')
		$query= $query." And ifNull(Deliveries.project_code,'-1')  Like ".$subproj; 
	if ($subcostcent != 'Null')
		$query= $query." And ifNull(Deliveries.Costcent_Code,'-1')  Like ".$subcostcent;  

	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query= $query." And DeliveryDt.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	if ($groupexist > 0)
		$query= $query." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

}

$query = $query." ) Q1 ";
$query = $query." ) Q2 ";

$query = $query."  Group By CategoryCode, GroupCode, SubGroupCode, ItemCode, WarehouseCode ".
   			    "  Order by if($showcategory=1,CategoryName,1), if($showgroup=1,GroupName,1), if($showsubgroup=1,SubGroupName,1), ItemCode, WarehouseCode  ";
				

//echo $query; exit;


$ArrayTotalByReport   = Array();
$ArrayWarehouse       = Array();
$ArrayItems	          = Array();
$ArrayRecordData      = Array();


				
$result=mysql_query($query, $connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
	
	
    
    
   //Check If Item Exists In Array
	$ItemFound = 0;
	$count = count ($ArrayItems); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayItems[$j] == $rows[6])
			$ItemFound = 1;
	}
				
	if ($ItemFound == 0) 
		$ArrayItems[$count] = $rows[6];
		
    //Check If Warehouse Exists In Array
	$WarehouseFound = 0;
	$count = count ($ArrayWarehouse); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayWarehouse[$j] == $rows[8])
			$WarehouseFound = 1;
	}
				
	if ($WarehouseFound == 0) 
		$ArrayWarehouse[$count] = $rows[8];
		
	$ArrayRecordData[$rows[6]]['CategoryName']  = $rows[1];
	$ArrayRecordData[$rows[6]]['GroupName']     = $rows[3];
	$ArrayRecordData[$rows[6]]['SubGroupName']  = $rows[5];
	$ArrayRecordData[$rows[6]]['ItemCode']      = $rows[6];
	$ArrayRecordData[$rows[6]]['ItemDesc']      = $rows[7];
	$ArrayRecordData[$rows[6]]['Currency']      = $rows[11];
	$ArrayRecordData[$rows[6]]['Cost']          = $rows[15];
	
	$ArrayRecordData[$rows[6]][$rows[8]]['QtySold']    = $rows[10];
	$ArrayRecordData[$rows[6]][$rows[8]]['SellAmount'] = $rows[12];
	
	$ArrayRecordData[$rows[6]]['TotalQty']    = $ArrayRecordData[$rows[6]]['TotalQty'] + $rows[10];
	$ArrayRecordData[$rows[6]]['TotalAmount'] = $ArrayRecordData[$rows[6]]['TotalAmount'] + $rows[12];
	$ArrayRecordData[$rows[6]]['TotalCost']   = $ArrayRecordData[$rows[6]]['Cost'] * $ArrayRecordData[$rows[6]]['TotalQty'];
			
	$ArrayRecordData[$rows[6]]['Margin']  = $ArrayRecordData[$rows[6]]['TotalAmount'] - $ArrayRecordData[$rows[6]]['TotalCost'] ;
	
}

//sorting data in array
//array_multisort($ArrayItems);
array_multisort($ArrayWarehouse);

echo "<table  border='0' width='100%' class='master' align='center' cellpadding='0' cellspacing='0'>";
echo"<tr><td>";	

//Create Table Columns
CreateTableHeader($showgroup, $showsubgroup, $showcategory, $FI_ShowCostPrice, $prtcost, $ArrayWarehouse);

echo"</td></tr>";
echo"<tr><td><hr width='100%' color='black' size='2'></td></tr>";

$col = 0;		
// Displaying Data
$CountItems = count($ArrayItems); 
for ($i=0; $i<$CountItems; $i++){ 
	echo"<tr><td>";	 
	
	if($col%2==0){
		$color = "style='background-color:".$report_color1."'";
	}else{
		$color = "style='background-color:".$report_color2."'";
	}
	CreateTableStructure($showgroup, $showsubgroup, $showcategory, $FI_ShowCostPrice, $prtcost, $ArrayWarehouse);

	echo"<tr $color>";

	if ($showcategory == 1)
		echo"<td align='left' valign='top' >".$ArrayRecordData[$ArrayItems[$i]]['CategoryName']."</td>";
		
	if ($showgroup == 1)
		echo"<td align='left' valign='top' >".$ArrayRecordData[$ArrayItems[$i]]['GroupName']."</td>";

	if ($showsubgroup == 1)
		echo"<td align='left' valign='top' >".$ArrayRecordData[$ArrayItems[$i]]['SubGroupName']."</td>";	
		
	echo"	<td align='left' valign='top' >".$ArrayRecordData[$ArrayItems[$i]]['ItemCode']."</td>";	
	echo"	<td align='left' valign='top' >".$ArrayRecordData[$ArrayItems[$i]]['ItemDesc']."</td>";		

	$CountWarehouse = count($ArrayWarehouse); 
	for ($j=0; $j < $CountWarehouse; $j++){
		$ArrayTotalByReport[$ArrayWarehouse[$j]]['QtySold']    = $ArrayTotalByReport[$ArrayWarehouse[$j]]['QtySold'] + $ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['QtySold'];
		$ArrayTotalByReport[$ArrayWarehouse[$j]]['SellAmount'] = $ArrayTotalByReport[$ArrayWarehouse[$j]]['SellAmount'] + $ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['SellAmount'];
		
		echo "<td align='right'  valign='top' >". number_format($ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['QtySold'], $ClientSalesRMaskQty) ."</td>";
		echo "<td align='right'  valign='top' >". number_format($ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['SellAmount'], $DecimalBase) ."</td>";
	}
	
	$ArrayTotalByReport['TotalQty']    = $ArrayTotalByReport['TotalQty']  + $ArrayRecordData[$ArrayItems[$i]]['TotalQty'];
	$ArrayTotalByReport['TotalAmount'] = $ArrayTotalByReport['TotalAmount']  + $ArrayRecordData[$ArrayItems[$i]]['TotalAmount'];
	$ArrayTotalByReport['TotalCost']   = $ArrayTotalByReport['TotalCost'] + $ArrayRecordData[$ArrayItems[$i]]['TotalCost'];
	$ArrayTotalByReport['Margin']      = $ArrayTotalByReport['TotalAmount'] - $ArrayTotalByReport['TotalCost'];
	
	echo "<td align='right'  valign='top' >". number_format($ArrayRecordData[$ArrayItems[$i]]['TotalQty'], $ClientSalesRMaskQty) ."</td>";
	echo "<td align='right'  valign='top' >". number_format($ArrayRecordData[$ArrayItems[$i]]['TotalAmount'], $DecimalBase) ."</td>";
	
	if ($prtcost == 1 && $FI_ShowCostPrice == 1)	{
		echo "<td align='center' valign='top' >". $ArrayRecordData[$ArrayItems[$i]]['Currency'] ."</td>";
		echo "<td align='right'  valign='top' >". number_format($ArrayRecordData[$ArrayItems[$i]]['Cost'], $DecimalBase) ."</td>";
		echo "<td align='right'  valign='top' >". number_format($ArrayRecordData[$ArrayItems[$i]]['TotalCost'], $DecimalBase) ."</td>";
		echo "<td align='right'  valign='top' >". number_format($ArrayRecordData[$ArrayItems[$i]]['Margin'], $DecimalBase) ."</td>";
	}	
		

	echo "</tr>";
	echo "</table></td></tr>"; 
	$col+=1;
}


// Display Totals By Report	
echo "<tr>";
echo "   <td><hr width='100%' color='black' size='1'></td>";
echo "</tr>";
echo"<tr><td>";	 
 
CreateTableStructure($showgroup, $showsubgroup, $showcategory, $fi_showcostprice, $prtcost, $ArrayWarehouse);

$ColCount = 0;
if ($showcategory == 1)
	$ColCount += 1;
if ($showgroup == 1)
	$ColCount += 1;
if ($showsubgroup == 1)	
	$ColCount += 1;	
	
echo"	<tr class='totals'>";

if ($ColCount > 0)
	echo"	<td align='center' colspan='$ColCount'>&nbsp;</td>";
	
echo"		<td align='center' colspan='2'>Totals</td>";

$CountWarehouse = count($ArrayWarehouse); 
for ($j=0; $j < $CountWarehouse; $j++){
	echo "	<td align='right'  valign='top' >". number_format($ArrayTotalByReport[$ArrayWarehouse[$j]]['QtySold'], $ClientSalesRMaskQty) ."</td>";
	echo "	<td align='right'  valign='top' >". number_format($ArrayTotalByReport[$ArrayWarehouse[$j]]['SellAmount'], $DecimalBase) ."</td>";
}

echo "<td align='right'  valign='top' >". number_format($ArrayTotalByReport['TotalQty'], $ClientSalesRMaskQty) ."</td>";
echo "<td align='right'  valign='top' >". number_format($ArrayTotalByReport['TotalAmount'], $DecimalBase) ."</td>";
	
if ($prtcost == 1 && $FI_ShowCostPrice == 1)	{
	echo "<td align='center' valign='top' colspan='2'>&nbsp;</td>";
	echo "<td align='right'  valign='top' >". number_format($ArrayTotalByReport['TotalCost'], $DecimalBase) ."</td>";
	echo "<td align='right'  valign='top' >". number_format($ArrayTotalByReport['Margin'], $DecimalBase) ."</td>";
}
echo "</tr>";
echo "</table></td></tr>"; 

	
echo "</table>";


function CreateTableHeader($PShowGroup, $PShowSubGroup, $PShowCategory, $FI_ShowCostPrice, $PPrtCost, $PArrayWarehouse) {
	echo "<table  border='0' width='100%' align='center' class='master' style='table-layout:fixed'>";

	if ($PShowCategory == 1)
		echo "<col width=120>";
	if ($PShowGroup == 1)
		echo "<col width=120>";
	if ($PShowSubGroup == 1)
		echo "<col width=120>";	
		
	echo "<col width=120>";	
	echo "<col width=200>";	
	
	$count = count ($PArrayWarehouse); 
	for ($j=0; $j<$count; $j++){ 
	    echo" <col width=90>";
		echo" <col width=100>";
	}	
	
	echo" <col width=100>";
	echo" <col width=100>";
	
	if ($PPrtCost == 1 && $FI_ShowCostPrice == 1){
		echo "<col width=50>";
		echo "<col width=100>";
		echo "<col width=120>";
		echo "<col width=120>";
	}	
	
	$ColCount = 2;
	if ($PShowCategory == 1)
		$ColCount += 1;
	if ($PShowGroup == 1)
		$ColCount += 1;
	if ($PShowSubGroup == 1)	
		$ColCount += 1;

	echo "<tr>";
	echo"<th colspan='$ColCount'>&nbsp;</th>";
	
	$count = count ($PArrayWarehouse); 
	for ($j=0; $j<$count; $j++){ 
	    echo"<th align='center' colspan='2'>". $PArrayWarehouse[$j] ."</th>";
	}
	
	echo"<th colspan='2'>&nbsp;</th>";
	
	if ($PPrtCost == 1 && $FI_ShowCostPrice == 1){
		echo"<th colspan='4'>&nbsp;</th>";
	}
		
	echo "</tr>";
		
	echo "<tr>";

	if ($PShowCategory == 1)
		echo"<th >Category</th>";
	if ($PShowGroup == 1)
		echo"<th >Group</th>";
	if ($PShowSubGroup == 1)
		echo"<th >SubGroup</th>";
		
	echo"<th >Item Code</th>
		 <th >Description</th>";
		 
	$count = count ($PArrayWarehouse); 
	for ($j=0; $j<$count; $j++){ 
	    echo"<th align='center'> Qty </th>";
		echo"<th align='center'> Amount </th>";
	}	

	echo"<th >Total Qty </th>";
	echo"<th >Total Selling </th>";
	
	if ($PPrtCost == 1 && $FI_ShowCostPrice == 1){
		echo "<th >Cur.</th>";
		echo "<th >Mean Cost</th>";
		echo "<th >Total Cost</th>";
		echo "<th >Margin</th>";
	}
		
	echo "</tr>";
	echo "</table>";

}

function CreateTableStructure($PShowGroup, $PShowSubGroup, $PShowCategory, $FI_ShowCostPrice, $PPrtCost, $PArrayWarehouse) {
	echo "<table  border='0' width='100%' align='center' class='master' style='table-layout:fixed'>";

	if ($PShowCategory == 1)
		echo "<col width=120>";
	if ($PShowGroup == 1)
		echo "<col width=120>";
	if ($PShowSubGroup == 1)
		echo "<col width=120>";	
		
	echo "<col width=120>";	
	echo "<col width=200>";	
	
	$count = count ($PArrayWarehouse); 
	for ($j=0; $j<$count; $j++){ 
	    echo" <col width=90>";
		echo" <col width=100>";
	}	
	
	echo" <col width=100>";
	echo" <col width=100>";
	
	if ($PPrtCost == 1 && $FI_ShowCostPrice == 1){
		echo "<col width=50>";
		echo "<col width=100>";
		echo "<col width=120>";
		echo "<col width=120>";
	}	
	

}


function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 

function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}

function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}

if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;

?>