<?php

session_start();
if($HTTP_SESSION_VARS['dbName']==""){
header("location:../index.php");
}


print <<< HERE
<HTML>
<HEAD>
<TITLE> Purchase Report </TITLE>

<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: 11px; 
                  font-family:Times-New-Roman;
                 }
.td.master1  {
					border-top:1px #000000 solid;
				 }
			   
tr.totals  { font-size: 12px; 
             font-weight: bold;
             font-family:Times-New-Roman;
           }					   
				
table.detail th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }				 
				
table.detail td { font-size: 11px; 
                  font-family:Times-New-Roman;
                 }	
				 
table.group td { font-size: 12px; 
                 font-weight: bold;
                 font-family:Times-New-Roman;
               }				 				 


</style>




</HEAD>
HERE;
include("config.inc.php");
include 'function.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($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");

//maroun 30.03.2011
$querylog="SELECT print as printaccess, ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['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,ItemTargetDate from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}

$querysetup="select Module_AdditionalTaxes as ModuleAdditionalTax, ifNull(Module_Heatno,0) As Module_Heatno from module";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}



$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$HTTP_SESSION_VARS['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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$HTTP_SESSION_VARS['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);		


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




$dispfromproject  = $fromproject;
$dispfromdate = $fromdate;
$disptodate = $todate;


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

$dispfromitem = $fromitem;
$disptoitem = $toitem;

$dispfromheatno = $fromheatno;
$disptoheatno = $toheatno;

if (empty($showweight)) $showweight = 0;

   
if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);  

if (empty($fromdate))   
   $fromdate = 'null';
else
   $fromdate = "'".$fromdate."'"; 
   
if (empty($todate))   
   $todate = 'null';
else
   $todate = "'".$todate."'";
   
if (empty($fromitem)) $fromitem = 'null';
else   $fromitem = "'".$fromitem."'";    
if (empty($toitem)) $toitem = 'null';
else   $toitem = "'".$toitem."'";    
   
if (empty($fromheatno)) $fromheatno = 'null';
else   $fromheatno = "'".$fromheatno."'";    
if (empty($toheatno)) $toheatno = 'null';
else   $toheatno = "'".$toheatno."'";    
   
if ($options==1)
$dispoptions = "Cumulative";
else
$dispoptions = "Detail";

   
if ($groupby==1)
	$dispgroupby='None';
else if ($groupby==2)
	$dispgroupby='Supplier';
else if ($groupby==3)
	$dispgroupby='Group';
else if ($groupby==4)
	$dispgroupby='SubGroup';
else if ($groupby==5)
	$dispgroupby='Category';
else if ($groupby==5)
	$dispgroupby='Area';
	
  

echo "<table width='100%' border='0' align='left' >";
if ($prtheader == 1){
	ShowReportHeader('Purchases Report',$PageLayout, $connection);
} else {
	ShowReportTitle('Purchases Report',$PageLayout, $connection);
}
print <<< HERE
<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="7%" valign='top'><b>Date</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="15%">$dispfromdate <br> $disptodate</td>
  <td width="7%" valign='top'><b>Item</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="25%" >$dispfromitem <br> $disptoitem </td>
HERE;
if($Module_Heatno==1 and $vShowHeatno==1)
  echo"<td width='25%' >$dispfromheatno <br> $disptoheatno </td>";
 
print <<<HERE
 
  <td width="7%" valign='top'><b>Supplier / (1)</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="25%" >$fromsupplier <br> $tosupplier </td>
  <td width="25%" >$fromsupplier1 <br> $tosupplier1 </td>
  
</tr>

<tr>
  <td width="7%"><b>Group</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="20%" >$fromgroup <br> $togroup </td>
  <td width="12%"><b>SubGroup</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="20%" >$fromsubgroup <br> $tosubgroup</td>
  <td width="10%" ><b>Category</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="31%" colspan=5>$fromcategory <br> $tocategory  </td>
</tr>
<tr>
  <td width="7%"><b>Warehouse</b></td>
  <td width="20%" colspan='2'>$fromwarehouse </td>
  <td width="12%" colspan='2'><b>Currency</b></td>
  <td width="20%" >$displaycurr</td>
  <td width="10%" ><b>Area</b></td>
  <td width="31%" colspan=5>$fromarea </td>
</tr>
<tr>
  <td width="7%"><b>Group by</b></td>
  <td width="20%" colspan='2'>$dispgroupby</td>
  <td width="12%" colspan='2'><b>Options</b></td>
  <td width="20%" colspan=6>$dispoptions</td>
  
</tr>

</table>
HERE;
 //if ($purchaseoption==1)
     //Send table names in this order: //Items //Supplier //Group //SubGroup //Category //Warehouse //TransferWh //Area //Client
   //  REPORTSOPTION.Fabicate('Stk','PurchasesDt','Items','Items','Items','Stk','','Clients','');


  if ($displaycurr==$Base2) 
     $curvar='2';
  else if ($displaycurr==$Base1) 
     $curvar='1';
  else $curvar='0';



  if (($fromcategory!='') or ($tocategory!='') )
	if ($documentfilter != '') 
		$documentfilter ="items.Category_code >='" .$fromcategory."'"." and items.Category_code <='".$tocategory."'";
     else 
		$documentfilter="Items.Category_code >='" .$fromcategory."'"." and Items.Category_code <='" .$tocategory."'";
  
  if (($fromgroup!='') or ($togroup!='')) {
      if ($documentfilter !='') 
		$documentfilter = $documentfilter ." and items.Group_Code >='" .$fromgroup."'"." and items.Group_code <='" .$togroup."'";
      else 
	    $documentfilter =" items.Group_Code >='" .$fromgroup."'"." and items.Group_code <='" .$togroup."'";
  }
   
  
  
  if (($fromsubgroup!='') or ($tosubgroup!=''))  {
  if ($documentfilter !='') 
	$documentfilter = $documentfilter . " and items.SubGroup_Code >='" .$fromsubgroup ."'"." and items.subGroup_code <='" .$tosubgroup ."'";
  else 
	$documentfilter =" items.SubGroup_Code >='" . $fromsubgroup ."'" ." and items.subGroup_code <='" . $tosubgroup."'";
  }
  
  if ($documentfilter != '') 
     $documentfilter = $documentfilter . " and ";
  $documentfilter=$documentfilter . " date_format(transaction_date,'%Y-%m-%d')  <= ".$todate;
  $documentfilter=$documentfilter . " And date_format(transaction_date,'%Y-%m-%d') >= ".$fromdate;

  if($showwdiscount==1)
	$documentfilter=$documentfilter . " And purchasesdt.PurchaseDt_DiscountAmount <> 0 ";
	
if($showzero==1)	
	$documentfilter=$documentfilter . " And purchasesdt.PurchaseDt_Price = 0 ";
  
  if (($fromsupplier!='') or ($tosupplier!='')) {
      if ($documentfilter!='')  
		$documentfilter=$documentfilter . " and purchasesdt.supplier_code >='" . $fromsupplier."'"." and purchasesdt.supplier_code <='" . $tosupplier."'";
      else  $documentfilter=$documentfilter . " purchasesdt.supplier_code >='" .$fromsupplier."'"." and purchasesdt.supplier_code <='" . $tosupplier ."'";
  }
  
  if (($fromsupplier1!='') or ($tosupplier1!='')) {
      if ($documentfilter!='')  
      $documentfilter=$documentfilter . " and purchases.supplier_code1 >='" . $fromsupplier1."'"." and purchases.supplier_code1 <='" . $tosupplier1."'";
      else  $documentfilter=$documentfilter . " purchases.supplier_code1 >='" .$fromsupplier1."'"." and purchases.supplier_code1 <='" . $tosupplier1 ."'";
   }
   if($UserAcc !=''){
	   $documentfilter=$documentfilter . "and purchases.user_Account = '$UserAcc'";
   }
  if ($fromwarehouse!='')
     $documentfilter= $documentfilter . " and stk.Warehouse_code  = '" . $fromwarehouse . "'";
  //else if (reportsoption.purchaseoption.checked)   {
   //       if (reportsoption.warehousefilter!='') 
    //         $documentfilter= $documentfilter .' and '. reportsoption.warehousefilter;
     //     if (reportsoption.supplierfilter<>'') 
      //       $documentfilter= $documentfilter .' and '. reportsoption.supplierfilter;
  //}
  //if ($purchaseoption==1)  and (reportsoption.itemsfilter<>'') then
   //  if documentfilter<>'' then
    //    documentfilter:= documentfilter +' and '+ reportsoption.itemsfilter
    // else  documentfilter:= documentfilter + reportsoption.itemsfilter;

   /*  if (REPORTSOPTION.ProjectTrial.Text <> '') or ((not Datamodule1.Projects.IsEmpty) and ReportsOption.ProjectChilds.Checked) then begin
        if documentfilter <> '' then
           Filterstr:=' and '
        else Filterstr:='';
        if reportsoption.ProjectChilds.checked then
           if not Datamodule1.Projects.IsEmpty then
              DocumentFilter := DocumentFilter + Filterstr+ Datamodule1.GetFilter('Stk.Project_Code','Project')
           else documentfilter:=documentfilter+Filterstr+'stk.Project_Code like '''+reportsoption.Projecttrial.Text+ '%' + ''''
        else documentfilter:=documentfilter+Filterstr+'stk.Project_Code like '''+reportsoption.Projecttrial.Text+ '''';//>>
     end;

     if (REPORTSOPTION.CostcenterTrial.Text <> '') or ((not Datamodule1.Costcenters.IsEmpty) and ReportsOption.CostcenterChilds.Checked) then begin
        if documentfilter <> '' then
           Filterstr:=' and '
        else Filterstr:='';
        if reportsoption.CostcenterChilds.checked then
           if not Datamodule1.Costcenters.IsEmpty then
              DocumentFilter := DocumentFilter + Filterstr+ Datamodule1.GetFilter('Stk.Costcent_Code','Costcenter')
           else documentfilter:=documentfilter+Filterstr+'stk.Costcent_Code like '''+reportsoption.Costcentertrial.Text+ '%' + ''''
        else documentfilter:=documentfilter+Filterstr+'stk.Costcent_Code like '''+reportsoption.Costcentertrial.Text+ '''';
     end;
*/
   
  if ($documentfilter=='') 
     $documentfilter = " And Stk.Item_code between ifNull(".$fromitem.",Stk.Item_code) and ifNull(". $toitem.",Stk.Item_code) ";
  else 
	$documentfilter = $documentfilter ." And Stk.Item_code between ifNull(".$fromitem.",Stk.Item_code) and ifNull(". $toitem.",Stk.Item_code) ";

	
	$documentfilter = $documentfilter ."  And IfNull(Stk.Item_Heatno,-1) between ifNull(".$fromheatno.",IfNull(Stk.Item_Heatno,-1)) and ifNull(". $toheatno.",IfNull(Stk.Item_Heatno,-1)) ";
	
	
  if($Module_Heatno==1 and $vShowHeatno==1)
	$groupByHeatNo= ", PurchaseDt_HeatNo";
  else 
	$groupByHeatNo = "";
	
  $OrderField = '';
  if ($groupby==1)  {
     $OrderField = 'order By Rcode,ICode,DueDate'.$groupByHeatNo;
     $GroupCap = '';
     $GroupField = '  A.RCode ';
     $GroupFieldName = 'A.RDescription';
  } else if ($groupby==2)  {
     $OrderField = 'order By supplier,Rcode,ICode,DueDate'.$groupByHeatNo;
     $GroupCap = 'Supplier';
     $GroupField = 'ifnull(A.Supplier,"***")';
     $GroupFieldName = 'A.SupplierName';
     //$ReportQueryGroupField.DisplayLabel:=Label_capSupp.asstring;
  } else if ($groupby==6) {
     $OrderField =  'order By Area,Rcode,ICode,DueDate'.$groupByHeatNo;
     $GroupCap = 'Area';
     $GroupField = 'ifnull(A.Area,"***")';
     $GroupFieldName = 'A.AreaName';
     //ReportQueryGroupField.DisplayLabel:='Area';
  } else if ($groupby==3) {
     $OrderField = 'order By f_Group,Rcode,ICode,DueDate'.$groupByHeatNo;
     $GroupCap = 'Group';
     $GroupField = 'ifnull(A.F_Group,"***")';
     $GroupFieldName = 'A.GroupName';
     //$ReportQueryGroupField.DisplayLabel:=Label_capGroup.asstring;
  } else if ($groupby==4){
     $OrderField = 'order By subgroup,Rcode,ICode,DueDate'.$groupByHeatNo;
     $GroupCap = 'SubGroup';
     $GroupField = 'ifnull(A.Subgroup,"***")';
     $GroupFieldName = 'A.SubgroupName';
     //$ReportQueryGroupField.DisplayLabel:=Label_capSubGroup.asstring;
  } else if ($groupby==5){
     $OrderField = 'order By category,Rcode,ICode,DueDate'.$groupByHeatNo;
     $GroupCap = 'Category';
     $GroupField = 'ifnull(A.Category,"***")';
     $GroupFieldName = 'A.CategoryName';
     //ReportQueryGroupField.DisplayLabel:=Label_capCategory.asstring;
  }


//Cumulative
  if ($options==1) {
     $query = 'Select A.Reference, A.RCode As RelationKey, A.ICode As ICode, A.RDescription, A.IDescription, A.Supplier, A.SupplierName, A.f_Group, A.GroupName,';
     $query = $query . 'A.SubGroup, A.SubGroupName, A.Category, A.CategoryName, A.Area, A.AreaName, A.Warehouse, A.ExistingQty, A.InvNumb, A.DueDate, A.Currency, A.Purchasedt_discountper,';
     $query = $query . 'A.Item_Vat, A.Item_Tax1, A.Item_Tax2, A.Item_Tax3, A.Item_Tax4, A.Item_Tax5, SUM(if(A.Transaction_Type="PI",Charges,(-1)*Charges)) As Charges, A.DiscountAmount,';
     $query = $query . "Sum(A.QtyReturn) As QtyReturn, Sum(A.QtyPurchase) As QtyPurchase, Sum(A.QtyNetPurchase) As QtyNetPurchase, '".$GroupCap."' As GroupCap, ".$GroupField." As GroupField, ".$GroupFieldName." As GroupFieldName,";
     $query = $query . 'A.TargetDate, A.costcenter, A.costcenterName, A.project, A.projectName,';
     $query = $query . 'Sum(((A.Charges - (A.Charges*A.Purchasedt_discountper)/100)*A.QtyNetPurchase)-A.DiscountAmount)/Sum(A.QtyNetPurchase) As UnitPrice,';
     $query = $query . 'SUM(LandFobVar*A.QtyNetPurchase) As PurchaseAmount,';
     $query = $query . 'SUM(((landfobvar-A.charges/A.QtyNetPurchase)*A.QtyNetPurchase)* if(ifnull(A.Purchase_VatChkbx,0)=1,Item_vat,0)/100) As PurchaseVat,';
     $query = $query . 'if(SUM(A.QtyNetPurchase) = 0,0,SUM(LandFobVar*A.QtyNetPurchase)/SUM(A.QtyNetPurchase)) As NetCost,';
     $query = $query . 'if(SUM(A.QtyNetPurchase) = 0,0,SUM(LandFobVar*A.QtyNetPurchase)/SUM(A.QtyNetPurchase)) As CostPrice,';
     $query = $query . 'if(A.QtynetPurchase<>0,SUM(A.Charges)/Sum(A.QtyNetPurchase),0) As ChargesU,';

     $query = $query . 'A.Transaction_Type As TType,';
     if ($ModuleAdditionalTax ==1) {
        $query = $query . 'Sum(if((Select Setup_Tax1Active From Setup)=1 And A.Item_Tax1 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax1/100,0)) As PurchaseTax1,';
        $query = $query . 'Sum(if((Select Setup_Tax2Active From Setup)=1 And A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax2/100,0)) As PurchaseTax2,';
        $query = $query . 'Sum(if((Select Setup_Tax3Active From Setup)=1 And A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax3/100,0)) As PurchaseTax3,';
        $query = $query . 'Sum(if((Select Setup_Tax4Active From Setup)=1 And A.Item_Tax4 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax4/100,0)) As PurchaseTax4,';
        $query = $query . 'Sum(if((Select Setup_Tax5Active From Setup)=1 And A.Item_Tax5 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax5/100,0)) As PurchaseTax5,';
        $query = $query . 'Sum(if((Select Setup_Tax1Active From Setup)=1 And A.Item_Tax1 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax1/100,0)) + ';
        $query = $query . ' Sum(if((Select Setup_Tax2Active From Setup)=1 and A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax2/100,0)) + ';
        $query = $query . ' Sum(if((Select Setup_Tax3Active From Setup)=1 and A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax3/100,0)) + ';
        $query = $query . ' Sum(if((Select Setup_Tax4Active From Setup)=1 and A.Item_Tax4 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax4/100,0)) + ';
        $query = $query . ' Sum(if((Select Setup_Tax5Active From Setup)=1 and A.Item_Tax5 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax5/100,0)) As Purchase_TotalTaxes,';
     } else {
        $query = $query . '0.0 As PurchaseTax1, 0.0 As PurchaseTax2, 0.0 As PurchaseTax3, 0.0 As PurchaseTax4, 0.0 As PurchaseTax5, 0.0 As Purchase_TotalTaxes,';
     }
     $query = $query . 'A.Item_Weight As ItemWeight, Round(Sum(A.Item_Weight*A.QtyNetPurchase)) as TotalWeight, A.PurchaseDt_HeatNo  ';
     $query = $query . 'From (';
     $query = $query . '  Select Purchases.Purchase_Reference As Reference,Purchases.Purchase_VatChkbx,';
     $query = $query . '  if('.$masterby.'=1,Purchases.Supplier_Code,Purchasesdt.Item_Code) As RCode,';
     $query = $query . '  if('.$masterby.'=1,PurchasesDt.Item_Code,PurchasesDt.Supplier_Code) As ICode,';
     $query = $query . '  if('.$masterby.'=1,Suppliers.Ledger_Name,Items.Item_Description) As RDescription,';
     $query = $query . '  if('.$masterby.'=1,Items.Item_Description,Suppliers.Ledger_Name) As IDescription,';
     $query = $query . '  Stk.Transaction_Type, PurchasesDt.Supplier_Code As Supplier, Suppliers.Ledger_Name As SupplierName, Purchasesdt.Costcent_code As costcenter, Purchasesdt.Project_code As Project,';
     $query = $query . '  Costcent.Costcent_name As CostcenterName, Project.Project_name As ProjectName, Items.Group_Code As f_Group, Grouping.Group_Name As GroupName, Items.SubGroup_Code As SubGroup,';
     $query = $query . '  Subgroup.Subgroup_Name As SubGroupName, Items.Category_Code As Category, Category.Category_Name As CategoryName, Suppliers.Area_Code As Area, Area.Area_Name As AreaName,';
     $query = $query . '  Stk.Item_Code, Stk.PriceBase1, Stk.PriceBase2, Stk.Warehouse_Code As Warehouse, Q.QuantityExists As ExistingQty, Purchases.Purchase_Reference As InvNumb,';
     $query = $query . '  Stk.Transaction_Date As DueDate, Stk.Currency_Code As Currency, PurchasesDt.Purchasedt_discountper, PurchasesDt.Item_Vat, PurchasesDt.Item_Tax1, PurchasesDt.Item_Tax2,';
     $query = $query . '  PurchasesDt.Item_Tax3, PurchasesDt.Item_Tax4, PurchasesDt.Item_Tax5, PurchasesDT.PurchaseDt_TargerDate As TargetDate,';
     $query = $query . '  if('.$curvar.'=2,PurchaseDt_ChargesBase2,if('.$curvar.'=1,PurchaseDt_ChargesBase1,PurchaseDt_Charges)) As Charges,';
     $query = $query . '  if('.$curvar.'=2,PurchasesDt.PurchaseDt_DiscountBase2,if('.$curvar.'=1,PurchasesDt.PurchaseDt_DiscountBase1,PurchasesDt.PurchaseDt_DiscountAmount)) As DiscountAmount,';


     $query = $query . '  if(Stk.Transaction_Type = "PR",abs(QuantityEntered),0) As QtyReturn,if(Stk.Transaction_Type = "PI",QuantityEntered,0) As QtyPurchase,';
     $query = $query . '  (if('.$curvar.'=2,PurchaseDt_PriceBase2,if('.$curvar.'=1,PurchaseDt_PriceBase1,PurchaseDt_Price))*QuantityEntered -';
     $query = $query . '   if('.$curvar.'=2,PurchaseDt_PriceBase2,if('.$curvar.'=1,PurchaseDt_PriceBase1,PurchaseDt_Price))*QuantityEntered*Purchasedt_Discountper/100 -';
     $query = $query . '   if('.$curvar.'=2,PurchasesDt.PurchaseDt_DiscountBase2,if('.$curvar.'=1,PurchasesDt.PurchaseDt_DiscountBase1,PurchasesDt.PurchaseDt_DiscountAmount)) +';
    
     $query = $query . '   if('.$curvar.'=2,PurchaseDt_ChargesBase2,if('.$curvar.'=1,PurchaseDt_ChargesBase1,PurchaseDt_Charges))';
     
     $query = $query . '  )/QuantityEntered As LandFobVar,';
     $query = $query . '  QuantityEntered As QtyNetPurchase, Items.Item_Weight, PurchasesDt.PurchaseDt_HeatNo';

     $query = $query . '  From StkTransaction Stk';
     $query = $query . '  Inner Join PurchasesDt on Stk.Transaction_ID = PurchasesDt.PurchaseDt_ID';
     $query = $query . '  Inner Join Purchases on PurchasesDt.Purchase_ID = Purchases.Purchase_ID';
     $query = $query . '  Inner Join Items on Stk.Item_Code = Items.Item_Code';
     $query = $query . '  Inner Join Suppliers on Suppliers.Ledger_Number = PurchasesDt.Supplier_Code';
     $query = $query . '  Left Join Grouping on Items.Group_Code = Grouping.Group_Code';
     $query = $query . '  Left Join SubGroup on Items.SubGroup_Code = SubGroup.SubGroup_Code';
     $query = $query . '  Left Join Category on Items.Category_Code = Category.Category_Code';
     $query = $query . '  Left Join Area on Area.Area_Code = Suppliers.Area_Code';
     $query = $query . '  Left Join Costcent on Costcent.costcent_code = Purchasesdt.costcent_code';
     $query = $query . '  Left Join project on project.project_code = purchasesdt.project_code';
	 
	if ($projectexist > 0 && $FI_RepRestrictProject == 1) {
		$query = $query." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
						"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
						"              and (loginstk.project_code is not null and loginstk.project_code != '') ". 
						"		   ) Q1 on PurchasesDt.project_code like concat(Q1.project_code,'%') ";
	}
				
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) {
		$query = $query." 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 != '') ".
						"			 ) Q2 on PurchasesDt.costcent_code like concat(Q2.Costcent_Code,'%')";
	}
			
     $query = $query . '  Left Join (';
     $query = $query . '   Select stk.item_Code,Sum(QuantityProduceIn-QuantityProduceOut+QuantityEntered-QuantityDelivered) As QuantityExists';
     $query = $query . '   From stkTransaction Stk';
     
     $query = $query . '   Where True';
    
     if ($fromwarehouse != '') 
        $query = $query . "   And Warehouse_Code = '".$fromwarehouse. "'";
    // else if ($purchaseoption) then
	//	 if (reportsoption.warehousefilter<>'') then
    //    $query = $query . '   And '+REPORTSOPTION.warehousefilter);

    
     $query = $query . " And Stk.Item_code between ifNull(".$fromitem.",Stk.Item_code) and ifNull(". $toitem.",Stk.Item_code) ";
     $query = $query . " And IfNull(Stk.Item_Heatno,-1) between ifNull(".$fromheatno.",IfNull(Stk.Item_Heatno,-1)) and ifNull(". $toheatno.",IfNull(Stk.Item_Heatno,-1)) ";

     $query = $query . '   Group By stk.Item_Code';
     $query = $query . '  ) As Q on Stk.Item_Code = Q.Item_Code';
     $query = $query . "  Where Transaction_Type in ('PI','PR')";
     if ($documentfilter !='') 
        $query = $query . '  And ' . $documentfilter;
     $query = $query . ') As A ';
	 //$query = $query . ' Group By A.ICode, A.RCode';
	 if ($groupby==1)
		{
			if ($PurchasePrintDetail==0)
			$query = $query . ' Group By '.$GroupField;
			else
			$query = $query . ' Group By A.ICode,'.$GroupField;
		}	
	 else
		{
			if ($PurchasePrintDetail==0)
				$query = $query . ' Group By '.$GroupField ;
			else	
			if ($PurchasePrintSubDetail==1)
				$query = $query . ' Group By '.$GroupField.' ,A.Rcode,A.ICode ' ;
			else		
				$query = $query . ' Group By '.$GroupField.' ,A.RCode' ;
		}
  //Detail
  } else {
     $query = $query . 'Select A.Reference, A.RCode As RelationKey, A.ICode As ICode, A.RDescription, A.IDescription, A.Supplier, A.SupplierName, A.f_Group, A.GroupName,';
     $query = $query . 'A.SubGroup, A.SubGroupName, A.Category, A.CategoryName, A.Area, A.AreaName, A.Warehouse, A.ExistingQty, A.InvNumb, date_format(A.DueDate,"%d/%m/%Y") as DueDate, A.Currency, A.Purchasedt_discountper,';
     $query = $query . 'A.Item_Vat, A.Item_Tax1, A.Item_Tax2, A.Item_Tax3, A.Item_Tax4, A.Item_Tax5, A.Charges, A.DiscountAmount, A.QtyReturn, A.QtyPurchase, A.QtyNetPurchase,';
     $query = $query . " '".$GroupCap."' As GroupCap, ".$GroupField." As GroupField, ".$GroupFieldName." As GroupFieldName, A.TargetDate, A.costcenter, A.costcenterName, A.Project, A.ProjectName,";
     $query = $query . '((((A.Charges - (A.Charges*A.Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)/A.QtyNetPurchase) As UnitPrice,';
     $query = $query . 'LandFobVar*A.QtyNetPurchase As PurchaseAmount, ((landfobvar-A.charges/A.QtyNetPurchase)*A.QtyNetPurchase)* if(ifnull(Purchase_VatChkbx,0)=1,Item_vat,0)/100 As PurchaseVat,';
     $query = $query . 'if(A.QtyNetPurchase = 0,0,(LandFobVar*A.QtyNetPurchase)/A.QtyNetPurchase) As NetCost,';
     $query = $query . 'if(A.QtyNetPurchase = 0,0,(LandFobVar*A.QtyNetPurchase)/A.QtyNetPurchase) As CostPrice,';
     $query = $query . 'if(A.QtynetPurchase<>0,A.Charges/A.QtyNetPurchase,0) As ChargesU,';
     $query = $query . 'A.Transaction_Type As TType,';//Ayman 13.07.2009
     if ($ModuleAdditionalTax==1) {
        $query = $query . 'if((Select Setup_Tax1Active from Setup)=1 and A.Item_Tax1 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax1/100,0) As PurchaseTax1,';
        $query = $query . 'if((Select Setup_Tax2Active from Setup) = 1 and A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax2/100,0) As PurchaseTax2,';
        $query = $query . 'if((Select Setup_Tax3Active from Setup) = 1 and A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax3/100,0) As PurchaseTax3,';
        $query = $query . 'if((Select Setup_Tax4Active from Setup) = 1 and A.Item_Tax4 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax4/100,0) As PurchaseTax4,';
        $query = $query . 'if((Select Setup_Tax5Active from Setup) = 1 and A.Item_Tax5 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax5/100,0) As PurchaseTax5,';
        $query = $query . '(if((Select Setup_Tax1Active from Setup) = 1 and A.Item_Tax1 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax1/100,0) +';
        $query = $query . ' if((Select Setup_Tax2Active from Setup) = 1 and A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax2/100,0) +';
        $query = $query . ' if((Select Setup_Tax3Active from Setup) = 1 and A.Item_Tax2 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax3/100,0) +';
        $query = $query . ' if((Select Setup_Tax4Active from Setup) = 1 and A.Item_Tax4 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax4/100,0) +';
        $query = $query . ' if((Select Setup_Tax5Active from Setup) = 1 and A.Item_Tax5 > 0,(((A.Charges - (A.Charges*Purchasedt_discountper)/100)* A.QtyNetPurchase)- A.DiscountAmount)*Item_tax5/100,0)) As Purchase_TotalTaxes,';
     } else {
        $query = $query . '0.0 As PurchaseTax1, 0.0 As PurchaseTax2, 0.0 As PurchaseTax3, 0.0 As PurchaseTax4, 0.0 As PurchaseTax5, 0.0 As Purchase_TotalTaxes,';
     }
     $query = $query . 'A.Item_Weight As ItemWeight, Round((A.Item_Weight*A.QtyNetPurchase)) as TotalWeight, A.PurchaseDt_HeatNo  ';
     $query = $query . 'From (';
     $query = $query . '  Select Purchases.Purchase_Reference As Reference,Purchases.Purchase_VatChkbx,';
     $query = $query . '  if('.$masterby.'=1,Purchases.Supplier_Code,Purchasesdt.Item_Code) As RCode,';
     $query = $query . '  if('.$masterby.'=1,PurchasesDt.Item_Code,PurchasesDt.Supplier_Code) As ICode,';
     $query = $query . '  if('.$masterby.'=1,Suppliers.Ledger_Name,Items.Item_Description) As RDescription,';
     $query = $query . '  if('.$masterby.'=1,Items.Item_Description,Suppliers.Ledger_Name) As IDescription,';
     $query = $query . '  Stk.Transaction_Type, PurchasesDt.Supplier_Code  As Supplier, Suppliers.Ledger_Name As SupplierName, Purchasesdt.Costcent_code As Costcenter, Purchasesdt.Project_code As Project, Costcent.costcent_name As CostcenterName,';
     $query = $query . '  Project.Project_name As ProjectName, Items.Group_Code As f_Group, Grouping.Group_Name As GroupName, Items.SubGroup_Code As SubGroup, Subgroup.Subgroup_Name As SubGroupName, Items.Category_Code As Category,';
     $query = $query . '  Category.Category_Name As CategoryName, Suppliers.Area_Code As Area, Area.Area_Name As AreaName, Stk.Item_Code, Stk.PriceBase1, Stk.PriceBase2, Stk.Warehouse_Code As Warehouse, Q.QuantityExists As ExistingQty,';
     $query = $query . '  Purchases.Purchase_Reference As InvNumb, Stk.Transaction_Date As DueDate, Stk.Currency_Code As Currency, PurchasesDt.Purchasedt_discountper, PurchasesDt.Item_Vat, PurchasesDt.Item_Tax1, PurchasesDt.Item_Tax2,';
     $query = $query . '  PurchasesDt.Item_Tax3, PurchasesDt.Item_Tax4, PurchasesDt.Item_Tax5, PurchasesDT.PurchaseDt_TargerDate As TargetDate,';
  
     $query = $query . '  if('.$curvar.'=2,PurchaseDt_ChargesBase2,if('.$curvar.'=1,PurchaseDt_ChargesBase1,PurchaseDt_Charges)) As Charges,';
     $query = $query . '  if('.$curvar.'=2,PurchasesDt.PurchaseDt_DiscountBase2,if('.$curvar.'=1,PurchasesDt.PurchaseDt_DiscountBase1,PurchasesDt.PurchaseDt_DiscountAmount)) As DiscountAmount,';
     $query = $query . '  if(Stk.Transaction_Type = "PR",abs(QuantityEntered),0) As QtyReturn,if(Stk.Transaction_Type = "PI",QuantityEntered,0) As QtyPurchase,';
     $query = $query . '  (if('.$curvar.'=2,PurchaseDt_PriceBase2,if('.$curvar.'=1,PurchaseDt_PriceBase1,PurchaseDt_Price))*QuantityEntered -';
     $query = $query . '   if('.$curvar.'=2,PurchaseDt_PriceBase2,if('.$curvar.'=1,PurchaseDt_PriceBase1,PurchaseDt_Price))*QuantityEntered*Purchasedt_Discountper/100 -';
     $query = $query . '   if('.$curvar.'=2,PurchasesDt.PurchaseDt_DiscountBase2,if('.$curvar.'=1,PurchasesDt.PurchaseDt_DiscountBase1,PurchasesDt.PurchaseDt_DiscountAmount)) +';
     $query = $query . '   if('.$curvar.'=2,PurchaseDt_ChargesBase2,if('.$curvar.'=1,PurchaseDt_ChargesBase1,PurchaseDt_Charges))';
     $query = $query . '  )/QuantityEntered As LandFobVar,';
     $query = $query . '  QuantityEntered As QtyNetPurchase, Items.Item_Weight, PurchasesDt.PurchaseDt_HeatNo';

     $query = $query . '  From StkTransaction Stk';
     $query = $query . '  Inner Join PurchasesDt on Stk.Transaction_ID = PurchasesDt.PurchaseDt_ID';
     $query = $query . '  Inner Join Purchases on PurchasesDt.Purchase_ID = Purchases.Purchase_ID';
     $query = $query . '  Inner Join Items on Stk.Item_Code = Items.Item_Code';
     $query = $query . '  Inner Join Suppliers on Suppliers.Ledger_Number = PurchasesDt.Supplier_Code';
     $query = $query . '  Left Join Grouping on Items.Group_Code = Grouping.Group_Code';
     $query = $query . '  Left Join SubGroup on Items.SubGroup_Code = SubGroup.SubGroup_Code';
     $query = $query . '  Left Join Category on Items.Category_Code = Category.Category_Code';
     $query = $query . '  Left Join Area on Area.Area_Code = Suppliers.Area_Code';
     $query = $query . '  Left Join Costcent on Costcent.costcent_code = Purchasesdt.costcent_code';
     $query = $query . '  Left Join Project on Project.Project_code = Purchasesdt.Project_code';
	 
	if ($projectexist > 0 && $FI_RepRestrictProject == 1) {
		$query = $query." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
						"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
						"              and (loginstk.project_code is not null and loginstk.project_code != '') ". 
						"		   ) Q1 on PurchasesDt.project_code like concat(Q1.project_code,'%') ";
	}
				
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) {
		$query = $query." 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 != '') ".
						"			 ) Q2 on PurchasesDt.costcent_code like concat(Q2.Costcent_Code,'%')";
	}
	
     $query = $query . '  Left Join (';
     $query = $query . '   Select stk.item_Code,Warehouse_code,Sum(QuantityProduceIn-QuantityProduceOut+QuantityEntered-QuantityDelivered) As QuantityExists';
     $query = $query . '   From stkTransaction Stk';
     $query = $query . '   Where True';
 
     if ($fromwarehouse != '') 
        $query = $query . "   And Warehouse_Code ='".$fromwarehouse."'";
     //else if (reportsoption.purchaseoption.checked) then
      //  if (REPORTSOPTION.warehousefilter<>'') then
       //    $query = $query . '   And '+REPORTSOPTION.warehousefilter);
	 $query = $query .  " And Stk.Item_code between ifNull(".$fromitem.",Stk.Item_code) and ifNull(". $toitem.",Stk.Item_code) ";
	 $query = $query .  " And IfNull(Stk.Item_Heatno,-1) between ifNull(".$fromheatno.",IfNull(Stk.Item_Heatno,-1)) and ifNull(". $toheatno.",IfNull(Stk.Item_Heatno,-1)) ";
     

     $query = $query . '   Group By stk.Item_Code,Warehouse_code';
	 
		
     $query = $query . '  ) As Q on Stk.Item_Code = Q.Item_Code and Stk.Warehouse_Code = Q.Warehouse_Code';
     $query = $query . '  Where Transaction_Type in ("PI","PR")';
	 
	 
     if ($documentfilter !='') 
        $query = $query . '  And '. $documentfilter;
		
     $query = $query . ') As A'; 
  }
  if ($OrderField != '') 
	$query = $query . " " .$OrderField;
	 // echo $query;
	 // exit;

	echo "<tr height='8' >";
	echo "  <td colspan='13'><hr width='100%' color='black' size='3'></td>";
	echo "</tr>";
echo"<tr><td><table border=0 class='master'  width='100%'>";

echo" <thead><tr><th  colspan=2>Item Code / Description</th>";

if ($options!=1) // not Cumulative
echo"<th>W.H.</th>";
else
echo"<th>&nbsp;</th>"; //Cumulative

echo"<th>Cost.C</th>";
echo"<th>Project</th>";

if($Module_Heatno == 1 && $groupby == 1  and $vShowHeatno==1)
	echo"<th>HeatNo</th>";
	
echo"<th>Type</th>";
echo"<th>Cur.</th>";

if (($options!=1)and($ItemTargetDate==1)and($masterby!=1))
echo"<th>Target <br> Date</th>";
else
echo"<th>&nbsp;</th>";

if ($showweight == 1) {
	echo"<th colspan=6 align=center>Quantity</th>";
} else {
	echo"<th colspan=4 align=center>Quantity</th>";
}
echo"<th colspan=3 align=center>Per Unit</th>";
echo"<th colspan=4 align=center>Total</th>";
echo"</tr>";
 

echo" <tr><td></td>";
echo"<td></td>";
echo"<td></td>";
echo"<td></td>";
echo"<td></td>";
echo"<td></td>";
echo"<td></td>";
echo"<td></td>";

if ($options==1)
{
echo"<th with='5%'>Purchase</th>";
echo"<th with='5%'>Return</th>";
}
else
{
echo"<th with='5%'>Reference</th>";
echo"<th with='5%'>Date</th>";
}

echo"<th with='5%'>Net</th>";
if ($showweight == 1) {
	echo" <th with='5%'>Weight</th>
		  <th with='5%'>Total Weight</th>";
}

echo"<th with='5%'>Exist</th>";

echo"<th with='5%'>FOB</th>";
echo"<th with='5%'>Charges</th>";
echo"<th with='5%'>Landed</th>";

echo"<th with='5%'>FOB</th>";
echo"<th with='5%'>Charges</th>";
echo"<th with='5%'>Landed</th>";
echo"<th with='5%'>VAT</th>";
echo"</tr>";
echo"<tr><td></td></tr></thead>";

//echo $query;
//exit;


// total sum qty 
$TotSumQtyPur = 0;$TotSumQtyRet = 0;$TotSumQtyNet = 0;$TotSumQtyExt = 0; $TotSumQtyWeight = 0;
//total sum per unit 
$TotSumPerFob = 0;$TotSumPerChr = 0;$TotSumPerLan = 0;
//total sum per unit 
$TotSumTotFob = 0;$TotSumTotChr = 0;$TotSumTotLan = 0;$TotSumTotVat = 0;


// sum qty 
$SumQtyPur = 0;
$SumQtyRet = 0;
$SumQtyNet = 0;
$SumQtyWeight = 0;
$SumQtyExt = 0;
 

//sum per unit 
$SumPerFob = 0;
$SumPerChr = 0;
$SumPerLan = 0;

//sum per unit 
$SumTotFob = 0;
$SumTotChr = 0;
$SumTotLan = 0;
$SumTotVat = 0;

// sum qty per subdetail
$SubSumQtyPur = 0;
$SubSumQtyRet = 0;
$SubSumQtyNet = 0;
$SubSumQtyExt = 0;
$SubSumQtyWeight = 0;
 

//sum per unit  per subdetail
$SubSumPerFob = 0;
$SubSumPerChr = 0;
$SubSumPerLan = 0;

//sum per unit  per subdetail
$SubSumTotFob = 0;
$SubSumTotChr = 0;
$SubSumTotLan = 0;
$SubSumTotVat = 0;


$grouped='';
$groupedfield='';
$result=mysql_query($query,$connection) or die(mysql_error());

$col=0;
while($row=mysql_fetch_row($result)){
	
		if($col%2==0){
			$color = "style='background-color:".$report_color1."'";
		}else{
			$color = "style='background-color:".$report_color2."'";
		}

		$Costpricecap=$row[41]/$row[31]-$row[45];
		$CostAmountCap=($row[41]/$row[31]-$row[45])*$row[31];


	if (($grouped!=$row[1] or $groupedfield!=$row[33])  and $PurchasePrintDetail==1)
		{
			if ($groupedfield!='' and $groupedfield!=$row[33]) // to remove summary from first record
			{
			
				// print subdetail before
				if ($PurchasePrintSubDetail==1)
				{
				echo"<tr><td  colspan=8 align='right'>&nbsp;</td><td class='master1'><b>".$SubSumQtyPur."</td><td class='master1'><b>".$SubSumQtyRet."</td><td class='master1'><b>".$SubSumQtyNet."</b></td>";
				
				if ($showweight == 1) {
					echo"<td class='master1'>&nbsp;</td>";
					echo"<td class='master1'><b>".$SubSumQtyWeight."</b></td>";
				}
				echo"	<td class='master1'><b>".$SubSumQtyExt."</b></td>";
				if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
					echo"<td class='master1'><b>".number_format($SubSumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerLan, $DecimalBase)."</b></td>";
				else
					echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
				
				echo"<td class='master1'><b>".number_format($SubSumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotVat, $DecimalBase)."</b></td></tr>";
			}
				
			echo"<tr><td colspan=8 align='right'><b>Total Group&nbsp;$groupedfield</b>&nbsp;</td>
					<td class='master1'><b>".$SumQtyPur."</b></td><td class='master1'><b>".$SumQtyRet."</b></td><td class='master1'><b>".$SumQtyNet."</b></td>";
					
			if ($showweight == 1) {
				echo"<td class='master1'>&nbsp;</td>";
				echo"<td class='master1'><b>".$SumQtyWeight."</b></td>";
			}		
			echo"<td class='master1'><b>".$SumQtyExt."</b></td>";
		
			if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
				echo"<td><b>".number_format($SumPerFob, $DecimalBase)."</b></td><td><b>".number_format($SumPerChr, $DecimalBase)."</b></td><td><b>".number_format($SumPerLan, $DecimalBase)."</b></td>";
				else
				echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
				
				echo"<td class='master1'><b>".number_format($SumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".$SumTotLan."</b></td><td class='master1'><b>".number_format($SumTotVat, $DecimalBase)."</b></td></tr>";
				$SumQtyPur = 0;
				$SumQtyRet = 0;
				$SumQtyNet = 0;
				$SumQtyWeight = 0;
				$SumQtyExt = 0;
				$SumPerFob = 0;
				$SumPerChr = 0;
				$SumPerLan = 0;
				$SumTotFob = 0;
				$SumTotChr = 0;
				$SumTotLan = 0;
				$SumTotVat = 0;
			}
	}		
	else
	if ( $groupby==1 and $PurchasePrintDetail==0)
		{	
		
					// print subdetail before
				if ($PurchasePrintSubDetail==1)
				{
				echo"<tr><td  colspan=8>&nbsp;</td><td class='master1'><b>".$SubSumQtyPur."</td><td class='master1'><b>".$SubSumQtyRet."</td><td class='master1'><b>".$SubSumQtyNet."</b></td>";
				
				if ($showweight == 1) {
					echo"<td class='master1'>&nbsp;</td>";
					echo"<td class='master1'><b>".$SubSumQtyWeight."</b></td>";
				}
				
				echo"<td class='master1'><b>".$SubSumQtyExt."</b></td>";
				if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
					echo"<td class='master1'><b>".number_format($SubSumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerLan, $DecimalBase)."</b></td>";
				else
					echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
				
				echo"<td class='master1'><b>".number_format($SubSumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotVat, $DecimalBase)."</b></td></tr>";
				}
				
					// print detail total
				if ($groupedfield!=''  and $groupedfield!=$row[33] and $PurchasePrintDetail==1) // to remove summary from first record
				{
			echo"<tr><td  colspan=8 align='right'><b>Total Group&nbsp;$groupedfield</b>&nbsp;</td><td class='master1'><b>".$SumQtyPur."</td><td class='master1'><b>".number_format($SumQtyRet, $DecimalBase)."</td><td class='master1'><b>".$SumQtyNet."</b></td>";
			if ($showweight == 1) {
				echo"<td class='master1'>&nbsp;</td>";
				echo"<td class='master1'><b>".$SumQtyWeight."</b></td>";
			}
			echo"<td class='master1'><b>".$SumQtyExt."</b></td>";
			
		
				if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
					echo"<td class='master1'><b>".number_format($SumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumPerLan, $DecimalBase)."</b></td>";
				else
					echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
					
				echo"<td class='master1'><b>".number_format($SumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumTotVat, $DecimalBase)."</b></td></tr>";
				$SumQtyPur = 0;
				$SumQtyRet = 0;
				$SumQtyNet = 0;
				$SumQtyWeight = 0;
				$SumQtyExt = 0;
				$SumPerFob = 0;
				$SumPerChr = 0;
				$SumPerLan = 0;
				$SumTotFob = 0;
				$SumTotChr = 0;
				$SumTotLan = 0;	
				}
	}			
		
	if ((($groupby<>1) and ($groupedfield!=$row[33])) and $PurchasePrintDetail==1)
		{
			echo"<tr><td colspan=3>&nbsp;</td></tr>";
			echo"<tr><td colspan=3><B>Group by ".$dispgroupby."<b></td><td><B>".$row[33]."</td><td colspan=8><B>".$row[34]."</td></tr>";
			echo"<tr><td colspan=3>&nbsp;</td></tr>";
			$SubSumQtyPur = 0;		$SubSumQtyRet = 0;		$SubSumQtyNet = 0;		$SubSumQtyExt = 0;		$SubSumQtyWeight = 0;		$SubSumPerFob = 0;		$SubSumPerChr = 0;		$SubSumPerLan = 0;		$SubSumTotFob = 0;		$SubSumTotChr = 0;		$SubSumTotLan = 0;		$SubSumTotVat = 0;	
			$aftergroupby='yes';
		}	
	else
		echo"<tr><td colspan=3>&nbsp;</td></tr>";




	if (($grouped!=$row[1] or $groupedfield!=$row[33])  and $PurchasePrintDetail==1)
		{
				if ($aftergroupby!='yes' and $PurchasePrintSubDetail==1)
				{
				echo"<tr><td  colspan=8>&nbsp;</td><td class='master1'><b>".$SubSumQtyPur."</td><td class='master1'><b>".$SubSumQtyRet."</td><td class='master1'><b>".$SubSumQtyNet."</b></td>";
				
				if ($showweight == 1) {
					echo"<td class='master1'>&nbsp;</td>";
					echo"<td class='master1'><b>".$SubSumQtyWeight."</b></td>";
				}
				
				echo"<td class='master1'><b>".$SubSumQtyExt."</b></td>";
				
				if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
					echo"<td class='master1'><b>".number_format($SubSumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerLan, $DecimalBase)."</b></td>";
				else
					echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
				
				echo"<td class='master1'><b>".number_format($SubSumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotVat, $DecimalBase)."</b></td></tr>";
				}
		
				echo"<tr><td colspan=2><b><font size=2>".$row[1]."</b></font></td><td colspan=6><font size=2><b>".$row[3]."</b></font></td>	";
				$SubSumQtyPur = 0;		$SubSumQtyRet = 0;		$SubSumQtyNet = 0;		$SubSumQtyExt = 0;		$SubSumQtyWeight = 0;		$SubSumPerFob = 0;		$SubSumPerChr = 0;		$SubSumPerLan = 0;		$SubSumTotFob = 0;		$SubSumTotChr = 0;		$SubSumTotLan = 0;		$SubSumTotVat = 0;
				$aftergroupby='no';
		}
	else
	if ( $groupby==1 and $PurchasePrintDetail==0)
		{	
			if ($aftergroupby!='yes' and $PurchasePrintSubDetail==1)
				{
				echo"<tr><td  colspan=8>&nbsp;</td><td class='master1'><b>".$SubSumQtyPur."</td><td class='master1'><b>".number_format($SubSumQtyRet, $DecimalBase)."</td><td class='master1'><b>".number_format($SubSumQtyNet, $DecimalBase)."</b></td>";
				if ($showweight == 1) {
					echo"<td class='master1'>&nbsp;</td>";
					echo"<td class='master1'><b>".$SubSumQtyWeight."</b></td>";
				}
				
				echo"<td class='master1'><b>".number_format($SubSumQtyExt, $DecimalBase)."</b></td>";
				
				if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
					echo"<td class='master1'><b>".number_format($SubSumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerLan, $DecimalBase)."</b></td>";
				else
					echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
				
				echo"<td class='master1'><b>".number_format($SubSumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotVat, $DecimalBase)."</b></td></tr>";
				}
				echo"<tr><td colspan=2><b><font size=2>".$row[1]."</b></font></td><td colspan=6><font size=2><b>".$row[3]."</b></font></td>";
				
				$SubSumQtyPur = 0;		$SubSumQtyRet = 0;		$SubSumQtyNet = 0;		$SubSumQtyExt = 0;		$SubSumQtyWeight = 0;		$SubSumPerFob = 0;		$SubSumPerChr = 0;		$SubSumPerLan = 0;		$SubSumTotFob = 0;		$SubSumTotChr = 0;		$SubSumTotLan = 0;		$SubSumTotVat = 0;	
				$SumTotVat = 0;
				$aftergroupby='no';
			}
	if (($groupby==1 and $PurchasePrintDetail==1) or $PurchasePrintSubDetail==1) 
		{
		echo"</tr>";
		echo"<tr $color><td>".$row[2]."</td><td>".substr($row[4],0,15)."</td>";
		if ($options!=1) // not Cumulative
		echo"<td>".$row[15]."</td>";
		else
		echo"<td>&nbsp;</td>";
		echo"<td>".$row[36]."&nbsp;</td><td>".$row[38]."</td>";
		if($Module_Heatno==1  && $groupby == 1 and $vShowHeatno==1)
			echo"<td>".$row[55]."</td>";
		echo"<td>".$row[46]."</td><td>".$row[19]."</td><td>";
		if (($options!=1)and($ItemTargetDate==1)and($masterby!=1))
			echo $row[35]; else echo "&nbsp;";
			echo"</td>";
		
			$SubSumQtyPur = $SubSumQtyPur + $row[30];		$SubSumQtyRet = $SubSumQtyRet + $row[29];		$SubSumQtyNet = $SubSumQtyNet + $row[31];		$SubSumQtyExt = $SubSumQtyExt + $row[26];		$SubSumPerFob = $SubSumPerFob + $Costpricecap;		$SubSumPerChr = $SubSumPerChr + $row[45];		$SubSumPerLan = $SubSumPerLan + $row[43];
			$SubSumTotFob = $SubSumTotFob + $CostAmountCap;		$SubSumTotChr = $SubSumTotChr + $row[27];		$SubSumTotLan = $SubSumTotLan + $row[41];		$SubSumTotVat = $SubSumTotVat + $row[42];
		}
		$col+=1;

	if (($groupby!=1 and $PurchasePrintDetail==0))
	echo"<tr><td colspan=2><b><font size=2>".$row[33]."</b></font></td><td colspan=6><font size=2><b>".$row[34]."</b></font></td>";

	$SumQtyPur = $SumQtyPur + $row[30];$SumQtyRet = $SumQtyRet + $row[29];$SumQtyNet = $SumQtyNet + $row[31]; $SumQtyWeight = $SumQtyWeight + $row[54];
	$SumQtyExt = $SumQtyExt + $row[26];$SumPerFob = $SumPerFob + $Costpricecap;$SumPerChr = $SumPerChr + $row[45];
	$SumPerLan = $SumPerLan + $row[43];$SumTotFob = $SumTotFob + $CostAmountCap;$SumTotChr = $SumTotChr + $row[27];$SumTotLan = $SumTotLan + $row[41];$SumTotVat = $SumTotVat + $row[42];



	$TotSumQtyPur = $TotSumQtyPur + $row[30];$TotSumQtyRet = $TotSumQtyRet + $row[29];$TotSumQtyNet = $TotSumQtyNet + $row[31]; $TotSumQtyWeight = $TotSumQtyWeight + $row[54];
	$TotSumQtyExt = $TotSumQtyExt + $row[26];$TotSumPerFob = $TotSumPerFob + $Costpricecap;$TotSumPerChr = $TotSumPerChr + $row[45];
	$TotSumPerLan = $TotSumPerLan + $row[43];$TotSumTotFob = $TotSumTotFob + $CostAmountCap;$TotSumTotChr = $TotSumTotChr + $row[27];$TotSumTotLan = $TotSumTotLan + $row[41];$TotSumTotVat = $TotSumTotVat + $row[42];


	if ($options==1)
	echo"<td>".$row[30]."</td><td>".$row[29]."</td>";
	else
	echo"<td>".$row[0]."</td><td>".$row[18]."</td>";


	echo"<td>".$row[31]."</td>";

	if ($showweight == 1) {
		echo"<td>".$row[53]."</td>";
		echo"<td>".$row[54]."</td>";
	}

	if ($groupby==1 and $options==1) 
		echo"<td>".$row[16]."</td>";
	else
		echo"<td>&nbsp;</td>";

		
		
	if (($groupby==1 and $PurchasePrintDetail==1) or ($groupby!=1 and $PurchasePrintSubDetail==1) ) //$PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))
		echo"<td>".number_format($Costpricecap, $DecimalBase)."</td><td>".number_format($row[45], $DecimalBase)."</td><td>".number_format($row[43], $DecimalBase)."</td>";
	else
		echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";

		
		
	echo"<td>".number_format($CostAmountCap, $DecimalBase)."</td><td>".number_format($row[27], $DecimalBase)."</td><td>".number_format($row[41], $DecimalBase)."</td><td>".number_format($row[42], $DecimalBase)."</td>";

	$grouped=$row[1];  
	$groupedfield=$row[33];  

	$lastrec=$row[33];
}


	// print subdetail before
			if ($PurchasePrintSubDetail==1)
			{
			echo"<tr><td  colspan=8>&nbsp;</td><td class='master1'><b>".$SubSumQtyPur."</td><td class='master1'><b>".$SubSumQtyRet."</td><td class='master1'><b>".$SubSumQtyNet."</b></td>";
			if ($showweight == 1) {
				echo"<td class='master1'>&nbsp;</td>";
				echo"<td class='master1'><b>".$SubSumQtyWeight."</b></td>";
			}
			echo"<td class='master1'><b>".$SubSumQtyExt."</b></td>";
			if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
				echo"<td class='master1'><b>".number_format($SubSumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumPerLan, $DecimalBase)."</b></td>";
			else
				echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
			
			echo"<td class='master1'><b>".number_format($SubSumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SubSumTotVat, $DecimalBase)."</b></td></tr>";
		}
		
		
// last summary
 if ((($grouped!=$row[1] or $groupedfield!=$row[33])  and $PurchasePrintDetail==1) )
 {
 echo"<tr><td colspan=8 align='right'><b>Total Group &nbsp;$lastrec</b>&nbsp;</td><td class='master1'><b>".$SumQtyPur."</td><td class='master1'><b>".number_format($SumQtyRet, $DecimalBase)."</td><td class='master1'><b>".$SumQtyNet."</td>";
 if ($showweight == 1) {
	echo"<td class='master1'>&nbsp;</td>";
	echo"<td class='master1'><b>".$SumQtyWeight."</b></td>";
 }
 echo"<td class='master1'><b>".$SumQtyExt."</td>";
		
		if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
			echo"<td class='master1'><b>".number_format($SumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumPerLan, $DecimalBase)."</b></td>";
		else
			echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
			
		echo"<td class='master1'><b>".number_format($SumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($SumTotVat, $DecimalBase)."</b></td>";
}		
 
 // last total summary
 //if ((($grouped!=$row[1] or $groupedfield!=$row[33])  and $PurchasePrintDetail==1) )
 {
 
 echo"<tr><td colspan=8 align='right'><b>Summary &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</b>&nbsp;</td>
 <td class='master1'><b>".$TotSumQtyPur."</td><td class='master1'><b>".$TotSumQtyRet."</td><td class='master1'><b>".$TotSumQtyNet."</td>";
 if ($showweight == 1) {
	echo"<td class='master1'>&nbsp;</td>";
	echo"<td class='master1'><b>".$TotSumQtyWeight."</b></td>";
 }
 echo"<td class='master1'><b>".$TotSumQtyExt."</td>";
		
		if ($PurchasePrintDetail!=1 or ($PurchasePrintDetail!=1 and $groupby!=1))			
			echo"<td class='master1'><b>".number_format($TotSumPerFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($TotSumPerChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($TotSumPerLan, $DecimalBase)."</b></td>";
		else
			echo"<td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
			
		echo"<td class='master1'><b>".number_format($TotSumTotFob, $DecimalBase)."</b></td><td class='master1'><b>".number_format($TotSumTotChr, $DecimalBase)."</b></td><td class='master1'><b>".number_format($TotSumTotLan, $DecimalBase)."</b></td><td class='master1'><b>".number_format($TotSumTotVat, $DecimalBase)."</b></td>";
}
 
 
echo"</table></td></tr>"; 
  
  
  //echo $query;
 // ReportQuery.ParamByName('MasterBySupplier').AsBoolean := reportsoption.masterbySupplier.checked;
 // ReportQuery.ParamByName('Cur').AsString := CurVar;

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 format_currency($amount, $DecimalBase) { 

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

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

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


?>
