<?php
header("Content-type: text/html; charset=windows-1256");

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


print <<< HERE
<html">
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<TITLE> List of Retail Report </TITLE>
<style type="text/css">

table.master h1 { font-size: 16px; 
                  font-weight: bold;
				  font-style: italic;
                  font-family:Times-New-Roman;
                }				 
				
table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                }				 
table.master td { font-size: 14px; 
                  font-family:Times-New-Roman;
                 }
table.detail th { font-size: 13px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                }				 
				 
table.detail td { font-size: 11px; 
                  font-family:Times-New-Roman;
                 }				 
				 
				 
table.totals td.labels { font-size: 13px; 
                         font-weight: bold;
                         font-family:Times-New-Roman;
					  }					  
table.totals td.amounts { font-size: 13px; 
                          font-family:Times-New-Roman;
					    }
table.totals p { border-width: 1px;
 				 border-style: none none dotted none;
				 border-color: #666666;
				}	
h4 { border-top-style: double; 
	 border-top-color: black; 
	 border-top-width: 1px;
   }				
				 
table.notes th { font-size: 12px; 
                 font-weight: bold;
                 font-family:Times-New-Roman;
                }				 
table.notes td { font-size: 12px; 
                 font-family:Times-New-Roman;
                }
</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($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");

$querylog="SELECT print as printaccess 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);
}




//Read From Setup Table							
$querysetup= " Select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, grid_color1, grid_color2, ".
             " Company_Name As CompanyName, Company_Memo As CompanyMemo, Company_City As CompanyCity, ".
			 " Company_Telephone As CompanyTelephone, Company_Fax As CompanyFax, ".
			 " Setup_VatNo, Setup_VatPercentage from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}
//echo $_SESSION['cashregister'];
//Read From CashRegister Table
/*$queryCashRegister=" Select ifNull(CashRegisterTTC,0) As CashRegisterTTC ".
				   " From CashRegister ".
				   " Where CashRegister_Name = '".$_SESSION['cashregister']."' ";*/

$sub_query = " Select Date_Format(min(Retail.Sale_Date),'%d/%m/%Y %h:%i %p') As StartDate, Date_Format(max(Retail.Sale_Date),'%d/%m/%Y %h:%i %p') As EndDate From Retail ".
			 " Where Upper(Retail.CashRegister_Name) = Upper('".$_SESSION['cashregister']."') ".
		     " And Date_Format(Retail.Sale_Date,'%Y%m%d') = Date_Format(now(),'%Y%m%d') ";
			 
$sub_result=mysql_query($sub_query,$connection) or die(mysql_error());
while($sub_row=mysql_fetch_array($sub_result)){
	extract($sub_row);
}
$queryCashRegister = " Select ifNull(CashRegisterTTC,0) As CashRegisterTTC, ifNull(retailserialno,0) As RetailSerialNo  ".
					 " From CashRegister Inner Join ".$HTTP_SESSION_VARS['dbLabel'].".logindt on logindt.Cash_Register = CashRegister.CashRegister_Name ".
					 " Where logindt.User_Account = '".$HTTP_SESSION_VARS['useraccount']."' ";
$resultCashRegister=mysql_query($queryCashRegister,$connection) or die(mysql_error());
while($rowCashRegister=mysql_fetch_array($resultCashRegister)){
	extract($rowCashRegister);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	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);		

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
		 "                                          where user_account='".$HTTP_SESSION_VARS['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);





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

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

if($notfromreport == 1) $fromuser = $_SESSION['useraccount'];

$dispfromdate = $fromdate;
$disptodate   = $todate;
$dispfromuser = $fromuser;
$dispcurr     = $curr;

  
$dispfromgroup = $fromgroup;
$disptogroup = $togroup;
$dispfromsubgroup = $fromsubgroup;
$disptosubgroup = $tosubgroup;

$dispfromcashreg = $fromcashreg; 

if (empty($fromuser))  $fromuser = 'null';
else  $fromuser = "'".$fromuser."'";   
if (empty($fromcashreg))  $fromcashreg = 'null';
else  $fromcashreg = "'".$fromcashreg."'";   
if (empty($fromsalesman))  $fromsalesman = 'null';
else  $fromsalesman = "'".$fromsalesman."'";   

if (empty($fromgroup))  $fromgroup = 'null';
else   $fromgroup = "'".$fromgroup."'";    
if (empty($togroup))  $togroup = 'null';
else  $togroup = "'".$togroup."'";   

if (empty($fromsubgroup))  $fromsubgroup = 'null';
else  $fromsubgroup = "'".$fromsubgroup."'";    
if (empty($tosubgroup))  $tosubgroup = 'null';
else  $tosubgroup = "'".$tosubgroup."'";      


if (empty($curr))  $curr = '';
//else  $curr = "'".$curr."'";   
$StartDate = $fromdate;
$EndDate = $todate;





if(strlen($fromdate)==10 && $fromtime == '')
	$fromdate = $fromdate." 00:00:00";

if(strlen($todate)==10 && $totime == '')
	$todate = $todate." 23:59:59";

if (strlen($fromdate)==10 && $fromtime != '')   
   $fromdate = $fromdate." ".$fromtime.":00";
if (strlen($todate)==10 && $totime != '')   
   $todate = $todate." ".$totime.":59";

if (!empty($fromdate))
   $fromdate = explodewordtime($fromdate);
if (!empty($todate))
   $todate = explodewordtime($todate);  
   
   
if (empty($fromdate))  $fromdate = 'null';
else  $fromdate = "'".$fromdate."'"; 
if (empty($todate))  $todate = 'null';
else  $todate = "'".$todate."'";

if (empty($fromcustomer))  $fromcustomer = 'null';
else  $fromcustomer = "'".$fromcustomer."'";    
if (empty($tocustomer))  $tocustomer = 'null';
else  $tocustomer = "'".$tocustomer."'";       
   
if (empty($fromwarehouse))  $fromwarehouse = 'null';
else  $fromwarehouse = "'".$fromwarehouse."'";    

if (empty($fromproject))  $fromproject = 'null';
else  $fromproject = "'".$fromproject."'";    
if (empty($toproject))  $toproject = 'null';
else  $toproject = "'".$toproject."'";       

if (empty($fromcostcent))  $fromcostcent = 'null';
else  $fromcostcent = "'".$fromcostcent."'";    
if (empty($tocostcent))  $tocostcent = 'null';
else  $tocostcent = "'".$tocostcent."'";     

if (empty($fromvendor))  $fromvendor = 'null';
else  $fromvendor = "'".$fromvendor."'";    
if (empty($tovendor))  $tovendor = 'null';
else  $tovendor = "'".$tovendor."'";     

if (empty($fromcredit))  $fromcredit = 'null';
else  $fromcredit = "'".$fromcredit."'";    
if (empty($tocredit))  $tocredit = 'null';
else  $tocredit = "'".$tocredit."'";     
   
if(empty($showgroups)) $showgroups = 0;
if(empty($showcost)) $showcost = 0;
if(empty($costoption)) $costoption = 1;


		
echo "<table border=0 width='250' cellspacing=0 cellpadding=0><tr><td align='left'>";
echo "<table border=0 width='240' cellspacing=0 cellpadding=0 class='master' style='table-layout:fixed;' >
		<col width=240>";
		
echo "	<tr>";
echo " 		<td align='center' valign='top'><h1>$CompanyName</h1></td>";
echo "	</tr>";
		
echo "	<tr>";
echo "  	<td align='center' valign='top' style='font-size:11px; padding-bottom:5px; border-bottom:1px dashed black;'>".nl2br($CompanyMemo)."</td>";
echo "	</tr>";
echo "</table></td></tr>";
		
echo"<tr><td align='left'>";
echo "<table border=0 width='240' cellspacing=0 cellpadding=0 class='master' style='table-layout:fixed;' >
	  <col width=40>
	  <col width=200>";
echo"	<tr><th align='left' valign='top'>Date:</th><td align='left'>". $StartDate ." - ". $EndDate ."</td></tr>";

if($dispfromcashreg != '')
echo"	<tr><th align='left'  style='padding-bottom:0px;' valign='top'>CashRegister:</th><td style='padding-right:80px;' align='center' >".$dispfromcashreg."</td></tr>";	  
if($dispfromuser != '')
	echo"	<tr><th align='left'  style='padding-bottom:0px;' valign='top'>User:</th><td style='padding-right:80px;' align='center' >".$dispfromuser."</td></tr>";	  


echo"	<tr><th align='left' style='padding-bottom:5px; border-bottom:1px dashed black;'>Pos:</th><td align='left' style='padding-bottom:5px; border-bottom:1px dashed black;'>". $_SESSION['cashregister'] ."</td></tr>";	  


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

if ($showdetail == 1) $masterclass = 'master1';
else $masterclass = 'master';


if($groupby=='')
	$groupby = 1;

$query = '';

$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) Q5  ";
					
					
$query = " Select ItemCode, ItemDescription, SaleDtQuantity, SaleDtTotal, SaleDtTotalTTC, SaleDtTotalBase2, SaleDtTotalTTCBase2, SaleDtDiscAmt, SaleDtDiscAmtTTC,  ".
		 " SaleDtDiscAmtBase2, SaleDtDiscAmtTTCBase2, SaleDtVatAmt, SaleDtVatAmtBase2, SaleDtDiscPer, MasterCode, MasterDescription, VendorCommission, ".
		 " ItemCost, (SaleDtQuantity*ItemCost) As TotalCost,  (SaleDtTotal - (SaleDtQuantity*ItemCost)) As ProfitAmount, ".
		 " if(SaleDtTotal=0, 0, (((SaleDtTotal - (SaleDtQuantity*ItemCost)) / (SaleDtTotal)) * 100)) As ProfitMargin, LastCostPriceB1, LastCostPriceB2 ".
		 " From ( ".
		 " Select if($showgroups!=1, RetailDt.Item_code, Null) As ItemCode, if($showgroups!=1, Items.Item_Description, Null) As ItemDescription, Sum(ifNull(SaleDt_Quantity,0)) As SaleDtQuantity, ".
	     " Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_Price,0))-((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_Price,0)*ifNull(SaleDt_DiscountPer,0))/100) - ifNull(SaleDt_DiscountAmount,0))), ".$DecimalBase.") As SaleDtTotal, ".
		 " Round(Sum((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceTTC,0))-((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceTTC,0)*ifNull(SaleDt_DiscountPer,0))/100) - (ifNull(SaleDt_DiscountAmount,0)*(1 + RetailDt.Item_Vat/100))), ".$DecimalBase.") As SaleDtTotalTTC, ".
		 " Round(Sum((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceBase2,0))-((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceBase2,0)*ifNull(SaleDt_DiscountPer,0))/100) - ifNull(SaleDt_DiscountBase2,0)), ".$DecimalBase.") As SaleDtTotalBase2, ".
		 " Round(Sum((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceTTCBase2,0))-((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceTTCBase2,0)*ifNull(SaleDt_DiscountPer,0))/100) - (ifNull(SaleDt_DiscountBase2,0)*(1 + RetailDt.Item_Vat/100))), ".$DecimalBase.") As SaleDtTotalTTCBase2, ".
		 " Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_Price,0)*ifNull(SaleDt_DiscountPer,0))/100) + ifNull(SaleDt_DiscountAmount,0)), ".$DecimalBase.") As SaleDtDiscAmt, ".
		 " Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceTTC,0)*ifNull(SaleDt_DiscountPer,0))/100) + (ifNull(SaleDt_DiscountAmount,0)*(1 + RetailDt.Item_Vat/100))), ".$DecimalBase.") As SaleDtDiscAmtTTC, ".
		 " Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceBase2,0)*ifNull(SaleDt_DiscountPer,0))/100) + ifNull(SaleDt_DiscountBase2,0)), ".$DecimalBase.") As SaleDtDiscAmtBase2, ".
		 " Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceTTCBase2,0)*ifNull(SaleDt_DiscountPer,0))/100) + (ifNull(SaleDt_DiscountBase2,0)*(1 + RetailDt.Item_Vat/100))), ".$DecimalBase.") As SaleDtDiscAmtTTCBase2, ".
		 " Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_Price,0))-((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_Price,0)*ifNull(SaleDt_DiscountPer,0))/100) - ifNull(SaleDt_DiscountAmount,0))*ifNull(RetailDt.Item_Vat,0)/100), ".$DecimalBase.") As SaleDtVatAmt, ".
		 " Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceBase2,0))-((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_PriceBase2,0)*ifNull(SaleDt_DiscountPer,0))/100) - ifNull(SaleDt_DiscountBase2,0))*ifNull(RetailDt.Item_Vat,0)/100), ".$DecimalBase.") As SaleDtVatAmtBase2, ".
		 " ifNull(convertcurfunc(Items.Currency_Code, '".$Base1."', Retail.Sale_Date, 0, 0, '', '', ifNull(Items.Item_LastCostPrice,0)),0) as  LastCostPriceB1, ".
		 " ifNull(convertcurfunc(Items.Currency_Code, '".$Base2."', Retail.Sale_Date, 0, 0, '', '', ifNull(Items.Item_LastCostPrice,0)),0) as  LastCostPriceB2, ".
		 " if($showgroups!=1, Round(Sum(((ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_Price,0)*ifNull(SaleDt_DiscountPer,0))/100) + ifNull(SaleDt_DiscountAmount,0))*100/Sum(ifNull(SaleDt_Quantity,0)*ifNull(SaleDt_Price,0)), ".$DecimalBase."), Null) As SaleDtDiscPer, ".
		 " if($groupby=6, ifNull(RetailDt.Vendor_Code,''), '') As MasterCode, if($groupby=6, ifNull(Vendor.Ledger_Name,''), '') As MasterDescription, ifNull(Vendor.Vendor_Commission, 0) As VendorCommission,  ";

if ($showcost == 1 && $groupby == 2) {		 
	if ($costoption == 1)
		$query = $query." ifNull(MeanCostCur(RetailDt.Item_code, concat(".$todate.", ' 23:59:59'), if(upper('".$curr."')='".$Base1."', 1,if(upper('".$curr."')='".$Base2."',2, 1)), Retail.currency_code, if(upper('".$curr."')='".$Base1."', '',if(upper('".$curr."')='".$Base2."','',items.currency_code ))),0) as ItemCost ";
	else
		$query = $query." ifNull(ItemLifoCost(RetailDt.Item_code, concat(".$todate.", ' 23:59:59'), if(upper('".$curr."')='".$Base1."', 1,if(upper('".$curr."')='".$Base2."',2, 1)), Retail.currency_code, if(upper('".$curr."')='".$Base1."', '',if(upper('".$curr."')='".$Base2."','',items.currency_code ))),0) as ItemCost ";
} else {
	$query = $query." 0 as ItemCost ";				
}		


$query = $query.	
	     " From RetailDt  ".
		 " Inner Join Retail on RetailDt.Sale_Id = Retail.Sale_Id ".
		 " Inner Join Items on RetailDt.Item_code = Items.Item_code ".
		 " Left Join Vendor on RetailDt.Vendor_Code = Vendor.Ledger_Number ";
		 
		if ($projectexist > 0 && $FI_RepRestrictProject == 1)
			$query= $query.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
							 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query= $query.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
					

$query= $query.					
	     " Where Retail.User_Account = ifNull(".$fromuser.", Retail.User_Account) ".
		 " And IfNull(SaleDt_ItemStatus,0) Not In (2,3) ".
		 " And Retail.CashRegister_Name = ifNull(".$fromcashreg.", Retail.CashRegister_Name) ".
		 " And ifNull(Retail.Customer_Code,'-1') between ifNull(".$fromcustomer.",ifNull(Retail.Customer_Code,'-1')) and ifNull(". $tocustomer.",ifNull(Retail.Customer_Code,'-1')) ".
		 " And Date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s') between ifNull(". $fromdate.",date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s')) and ifNull(". $todate.",date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s'))  ".
		 " And ifNull(Retail.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Retail.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Retail.Project_Code,'-1')) ".
		 " And ifNull(Retail.CostCenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Retail.CostCenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Retail.CostCenter_Code,'-1')) ".
		 " And ifNull(RetailDt.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(RetailDt.Warehouse_Code,'-1')) ".
		 " And ifNull(Retail.Salesman_Code,'-1') = ifNull(".$fromsalesman.", ifNull(Retail.Salesman_Code,'-1')) ".
		 " And ifNull(Retail.Sales_creditacc,'-1') between ifNull(".$fromcredit.",ifNull(Retail.Sales_creditacc,'-1')) and ifNull(". $tocredit.",ifNull(Retail.Sales_creditacc,'-1')) ".
		 " And ifNull(RetailDt.Vendor_Code,'-1') between ifNull(".$fromvendor.",ifNull(RetailDt.Vendor_Code,'-1')) and ifNull(". $tovendor.",ifNull(RetailDt.Vendor_Code,'-1')) ".
	   	 " 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)) ";
		 
if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
	$query = $query." And RetailDt.Warehouse_Code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";
	
$query = $query." Group By MasterCode, ItemCode ".
				" Order By MasterCode, ItemCode ";
				
$query = $query." ) As Q1 Order By MasterCode, ItemCode ";
		 

$query1 = " Select if($showgroups!=1, Sale_ID, -1) As SaleID, if($showgroups!=1, Sale_Reference, Null) As SaleReference, if($showgroups!=1, Sale_Date, Null) As SaleDate, ".
		  " if($showgroups!=1, Sale_Remark, Null) As Sale_Remark, Retail.Currency_code As CurrCode, Round(Sum(ifNull(Sale_NetTotal,0)), ".$DecimalBase.") As  SaleNetTotal, ".
		  " Round(Sum(ifNull(Sale_NetTotalBase2,0)), ".$DecimalBase.") As SaleNetTotalBase2, ".
		  " if($groupby=3, ifNull(Retail.User_Account,''), if($groupby=4, ifNull(Retail.CashRegister_Name,''), if($groupby=5, ifNull(Retail.Customer_Code,''), ''))) As MasterCode, ".
		  " if($groupby=3, ifNull(Users.User_Name,''), if($groupby=4, ifNull(Retail.CashRegister_Name,''), if($groupby=5, ifNull(Customer.Customer_Name,''), ''))) As MasterDescription, ".
		  " Retail.User_Account, Retail.CashRegister_Name, ".
		  " Round(Sum(ifNull(Sale_Taxable,0)), ".$DecimalBase.") As  SaleTaxable, Round(Sum(ifNull(Sale_TaxableBase2,0)), ".$DecimalBase.") As  SaleTaxableBase2, ".
		  " Round(Sum(ifNull(Sale_NoneTaxable,0)), ".$DecimalBase.") As  SaleNoneTaxable, Round(Sum(ifNull(Sale_NoneTaxableBase2,0)), ".$DecimalBase.") As  SaleNoneTaxableBase2, ".
		  " Round(Sum(ifNull(Sale_DiscountAmount,0)), ".$DecimalBase.") As  SaleDiscountAmount, Round(Sum(ifNull(Sale_DiscountBase2,0)), ".$DecimalBase.") As  SaleDiscountBase2, ".
		  " Round(Sum(ifNull(Sale_VAT,0)), ".$DecimalBase.") As  SaleVAT, Round(Sum(ifNull(Sale_VATBase2,0)), ".$DecimalBase.") As  SaleVATBase2, ".
		  " if($showgroups!=1, ifNull(Sale_DiscountPer,0), Null) As SaleDiscountPer, Sum(ifNull(Sales_CashInBase1,0)) As SalesCashInBase1, Sum(ifNull(Sales_CashOutBase1,0)) As SalesCashOutBase1,  ".
		  " Sum(ifNull(Sales_CashInBase2,0)) As SalesCashInBase2, Sum(ifNull(Sales_CashOutBase2,0)) As SalesCashOutBase2, Sum(ifNull(Sale_CashBase1,0)) As SaleCheckBase1,  ".
		  " Sum(ifNull(Sale_CashBase2,0)) As SaleCheckBase2, Sum(ifNull(Sales_CreditAmountBase1,0)) As SalesCreditAmountBase1, Sum(ifNull(Sales_CreditAmountBase2,0)) As SalesCreditAmountBase2, ".
		  " Sum(ifNull(Sales_TipsBase1,0)) As SalesTipsBase1, if(ifNull(Sales_CreditAmountBase1,0) > 0, 'CR', '') As IsCredit, Sum(if(Sales_TipsBase1>0,1,0)) As SumofTips ".
  		  " From Retail ".
		  " Left Join Customer on Retail.Customer_Code = Customer.Customer_Code ".
		  " Left Join ".$HTTP_SESSION_VARS['dbLabel'].".Users on Retail.User_Account = Users.User_Account ";
		  
		if ($projectexist > 0 && $FI_RepRestrictProject == 1)
			$query1= $query1.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
							 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query1= $query1.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";

$query1= $query1.			
          " Where Retail.User_Account = ifNull(".$fromuser.", Retail.User_Account) ".
		  " And Retail.CashRegister_Name = ifNull(".$fromcashreg.", Retail.CashRegister_Name) ".
		  " And ifNull(Retail.Customer_Code,'-1') between ifNull(".$fromcustomer.",ifNull(Retail.Customer_Code,'-1')) and ifNull(". $tocustomer.",ifNull(Retail.Customer_Code,'-1')) ".
		  " And Date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s') between ifNull(". $fromdate.",date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s')) and ifNull(". $todate.",date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s'))  ".
		  " And ifNull(Retail.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Retail.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Retail.Project_Code,'-1')) ".
		  " And ifNull(Retail.CostCenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Retail.CostCenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Retail.CostCenter_Code,'-1')) ".
		  " And ifNull(Retail.Salesman_Code,'-1') = ifNull(".$fromsalesman.", ifNull(Retail.Salesman_Code,'-1')) ".
		  " And ifNull(Retail.Sales_creditacc,'-1') between ifNull(".$fromcredit.",ifNull(Retail.Sales_creditacc,'-1')) and ifNull(". $tocredit.",ifNull(Retail.Sales_creditacc,'-1')) ".
	   	  " Group By MasterCode, SaleID, SaleReference   ".
		  " Order By MasterCode, Sale_Date, SaleReference  ";

//echo $query1; exit;

$SaleDtCost    = 0;
$SaleDtAmount  = 0;
$SaleDtDiscAmt = 0;
$SaleDtVatAmt  = 0;

$TotalTaxable	  = 0;
$TotalNoneTaxable = 0;
$TotalDiscAmt	  = 0;
$TotalVatAmt	  = 0;
$TotalAmount 	  = 0;
$TotalQty	  	  = 0;
		
$SumTaxable	    = 0;
$SumNoneTaxable = 0;
$SumDiscAmt	    = 0;
$SumVatAmt	    = 0;
$SumAmount      = 0;
$SumQty	   		= 0;

$TotalCashInB1  = 0;
$TotalCashInB2  = 0;
$TotalCashOutB1 = 0;
$TotalCashOutB2 = 0;
$SubTotalCashB1 = 0;
$SubTotalCashB2 = 0;
$TotalCashB1    = 0;
$TotalCashB2    = 0;
$TotalChecksB1  = 0;
$TotalChecksB2  = 0;
$TotalCreditsB1 = 0;
$TotalCreditsB2 = 0;
$TotalCouponB1  = 0;
$TotalCouponB2  = 0;
$NetTotalB1     = 0;
$NetTotalB2	    = 0;
$NumofCash		= 0;
$NumofChecks		= 0;
$SumofTips		= 0;
$NumofCoupon	= 0;
$GroupByCode  = '';
$GroupByLabel = '';

$MasterCode  = '';
$InvCounter  = 0;
$TotalInvoices  = 0;


if ($groupby == 2 || $groupby == 6)
	$result=mysql_query($query,$connection) or die(mysql_error());
else 	
	$result=mysql_query($query1,$connection) or die(mysql_error());
	
$TotalInvoices = mysql_num_rows($result);
while($rows=mysql_fetch_row($result)){

		
	if ($groupby == 2 || $groupby == 6) {
		if ($CashRegisterTTC == 1 && $showcost == 0){
			if($curr == '' or $curr == $Base1) {
				$SaleDtAmount  = $rows[4];
				$SaleDtDiscAmt = $rows[8];
				$SaleDtVatAmt  = $rows[11];
				$SaleDtCost    = $rows[21];
			}	
			else {
				$SaleDtAmount  = $rows[6];
				$SaleDtDiscAmt = $rows[10];
				$SaleDtVatAmt  = $rows[12];
				$SaleDtCost    = $rows[22];
			}	
		} else {
			if($curr == '' or $curr == $Base1) {
				$SaleDtAmount  = $rows[3];
				$SaleDtDiscAmt = $rows[7];
				$SaleDtVatAmt  = $rows[11];
				$SaleDtCost    = $rows[21];
			}	
			else {
				$SaleDtAmount  = $rows[5];
				$SaleDtDiscAmt = $rows[9];
				$SaleDtVatAmt  = $rows[12];
				$SaleDtCost    = $rows[22];
			}	
		}
			
		$SaleDtUnitCost  = $rows[17]; 
		$SaleDtTotalCost = $rows[18];
		$SaleDtProfitAmt = ($SaleDtAmount - $SaleDtTotalCost);
		$SaleDtProfitMar = 0;
		
		if($SaleDtAmount != 0) $SaleDtProfitMar = (($SaleDtProfitAmt / $SaleDtAmount) * 100);
		
			
		$MasterCode  = $rows[14];
		if ($MasterCode == '') $MasterCode = 'None';
			
		if ($groupby == 6 && ($GroupByCode != ''  && $GroupByCode != $MasterCode) && $showgroups != 1) 
		{
			$GroupByLabel = '';
			$GroupByLabel = 'Total by Vendor';
				
	 
	 
			
		 
				
			$SumQty     = 0;
			$SumAmount  = 0;
			$SumDiscAmt = 0;
			$SumVatAmt  = 0;
			
			$VendotComission = 0;
			
		}
			
		if ($groupby == 6 && ($GroupByCode == '' || $GroupByCode != $MasterCode) && $showgroups != 1) {
		  
			$GroupByLabel = '';
			$GroupByLabel = 'Group by Vendor';
				
				
		 

			$GroupByCode = $MasterCode;
			$VendotComission = $rows[16];
		}
			
		if ($rows[2] != 0)
			$SaleDtUnitPrice = $SaleDtAmount / $rows[2];
		
		
		
			
			
		$TotalQty     = $TotalQty     +  $rows[2];
		$TotalAmount  = $TotalAmount  +  $SaleDtAmount;
		$TotalDiscAmt = $TotalDiscAmt +  $SaleDtDiscAmt;
		$TotalVatAmt  = $TotalVatAmt  +  $SaleDtVatAmt;
		$TotalCost    = $TotalCost    +  $SaleDtTotalCost;
		$TotalProfit  = $TotalProfit  +  $SaleDtProfitAmt;
		if($TotalAmount != 0) $TotalProfitMar = (($TotalProfit / $TotalAmount) * 100);
			
		$SumQty     = $SumQty     +  $rows[2];
		$SumAmount  = $SumAmount  +  $SaleDtAmount;
		$SumDiscAmt = $SumDiscAmt +  $SaleDtDiscAmt;
		$SumVatAmt  = $SumVatAmt  +  $SaleDtVatAmt;
			
	} else if (($groupby == 1 && $showgroups != 1) || ($groupby != 1 && 1 == 1)) {
		
		$MasterCode  = $rows[7];
		if ($MasterCode == '') $MasterCode = 'None';
			
		if (($groupby == 5 || $groupby == 3 || $groupby == 4) && ($GroupByCode != ''  && $GroupByCode != $MasterCode) && $showgroups != 1) 
		{
			$GroupByLabel = '';
			if ($groupby == 5)		$GroupByLabel = 'Total by Customer';
			else if ($groupby == 3)	$GroupByLabel = 'Total by User';
			else if ($groupby == 4)	$GroupByLabel = 'Total by CashRegister';
				
			
	 
				
			$SumTaxable 	= 0;
			$SumNoneTaxable = 0;
			$SumDiscAmt	    = 0;
			$SumVatAmt 	    = 0;
			$SumAmount 	    = 0;
			
			$InvCounter     = 0;
				
		}
			
		if (($groupby == 5 || $groupby == 3 || $groupby == 4) && ($GroupByCode == '' || $GroupByCode != $MasterCode)  && $showgroups != 1) {
			  
			$GroupByLabel = '';
			if ($groupby == 5)		$GroupByLabel = 'Group by Customer';
			else if ($groupby == 3)	$GroupByLabel = 'Group by User';
			else if ($groupby == 4)	$GroupByLabel = 'Group by CashRegister';
				
				
		 

			$GroupByCode = $MasterCode;
			
		}
			
		if($curr == '' or $curr == $Base1){
			$SaleNetAmount   = $rows[5];
			$SaleTaxable     = $rows[11];
			$SaleNoneTaxable = $rows[13];
			$SaleDiscAmt 	 = $rows[15];
			$SaleVatAmt		 = $rows[17];
		}	
		else {
			$SaleNetAmount 	 = $rows[6];
			$SaleTaxable     = $rows[12];
			$SaleNoneTaxable = $rows[14];
			$SaleDiscAmt 	 = $rows[16];
			$SaleVatAmt		 = $rows[18];
		}	
				
	 
		
		if ($showgroups == 1) {
			 
		} else {
		 
			
			if ($showdetail == 1) {
				$querydt = " Select RetailDt.Item_code, Items.Item_Description, RetailDt.Warehouse_Code, ".
						   " ifNull(SaleDt_Quantity,0) As SaleDtQuantity, ".
						   " Round(ifNull(SaleDt_Price,0), ".$DecimalBase.") As SaleDtPrice, ".
						   " Round(ifNull(SaleDt_PriceTTC,0), ".$DecimalBase.") As SaleDtPriceTTC, ".
						   " Round(ifNull(SaleDt_PriceBase2,0), ".$DecimalBase.") As SaleDtPriceBase2, ".
						   " Round(ifNull(SaleDt_PriceTTCBase2,0), ".$DecimalBase.") As SaleDtPriceTTCBase2, ".
						   " RetailDt.SaleDt_ID ".
						   " From RetailDt  ".
						   " Inner Join Items on RetailDt.Item_code = Items.Item_code ".
						   " Where RetailDt.Sale_Id = '".$rows[0]."' ".
						   " And IfNull(SaleDt_ItemStatus,0) Not In (2,3) ".
						   " And ifNull(RetailDt.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(RetailDt.Warehouse_Code,'-1')) ".
						   " And ifNull(RetailDt.Vendor_Code,'-1') between ifNull(".$fromvendor.",ifNull(RetailDt.Vendor_Code,'-1')) and ifNull(". $tovendor.",ifNull(RetailDt.Vendor_Code,'-1')) ";
							   
				if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
					$querydt = $querydt." And RetailDt.Warehouse_Code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";
			
				$querydt = $querydt." Order By RetailDt.Item_code ";
					
					
			 
					
				$resultdt=mysql_query($querydt,$connection) or die(mysql_error());
				while($rowsdt=mysql_fetch_row($resultdt)){
						
					if ($CashRegisterTTC == 1){
						if($curr == '' or $curr == $Base1) $SaleDtAmount = $rowsdt[5];
						else $SaleDtAmount = $rowsdt[7];
					} else {
						if($curr == '' or $curr == $Base1) $SaleDtAmount = $rowsdt[4];
						else $SaleDtAmount = $rowsdt[6];
					}
						
						
			 
						
					$TotalQty    = $TotalQty    +  $rowsdt[3];
						
						
					if ($RetailSerialNo == 1) {
						$querysr = " SELECT SerialNo_Code ".
								   " FROM Purchasesserialno ".
								   " WHERE Purchasesserialno.RetailDt_Id = '".$rowsdt[8]."' ".
								   " AND Purchasesserialno.Journal_Type = 'DI' ";
							
						$resultsr=mysql_query($querysr,$connection) or die(mysql_error());
						$numsr=mysql_num_rows($resultsr);
							
						if ($numsr != 0)
						{
							$SerialCode='';
								
							while($rowsr=mysql_fetch_array($resultsr)){
								$SerialCode = $SerialCode." , ".$rowsr[0];
							}
								
							$SerialCode = substr($SerialCode,2);
						 
						}
					}
						
				}
					
					

			 
			}
		}
		
		$TotalTaxable	  = $TotalTaxable     +  $SaleTaxable;
		$TotalNoneTaxable = $TotalNoneTaxable +  $SaleNoneTaxable;
		$TotalDiscAmt	  = $TotalDiscAmt	  +  $SaleDiscAmt;
		$TotalVatAmt	  = $TotalVatAmt 	  +  $SaleVatAmt;
		$TotalAmount 	  = $TotalAmount	  +  $SaleNetAmount;
		
		$SumTaxable	    = $SumTaxable     +  $SaleTaxable;
		$SumNoneTaxable = $SumNoneTaxable +  $SaleNoneTaxable;
		$SumDiscAmt	    = $SumDiscAmt	  +  $SaleDiscAmt;
		$SumVatAmt	    = $SumVatAmt 	  +  $SaleVatAmt;
		$SumAmount      = $SumAmount 	  +  $SaleNetAmount;
			
		$InvCounter  += 1;
	}

}



// Report Totals
$groupby=4;
 {
 


	
	$showsummary=1;
	if ($showsummary == 1) {
		$query2 = " Select Sale_Bank As SaleBank, Sale_BankBranch As SaleBankBranch, Sale_CheckNo As SaleCheckNo, ".
				  " Date_Format(Sale_CheckDate,'%d/%m/%Y') As SaleCheckDate, Sale_CashCur As SaleCashCur, ".
				  " Round(ifNull(Sale_Cash,0), ".$DecimalBase.") As  SaleCash, Sale_Information As SaleInformation ".
				  " From Retail ";
		
		$query3 = " Select Sale_CashCur As CheckCurr, Round(Sum(ifNull(Sale_Cash,0)), ".$DecimalBase.") As CheckAmount ".
				  " From Retail ";

		$query4 = " Select Sales_creditacc As CreditCard, Clients.Ledger_Name As CreditDesc, ".
				  " Sales_creditacccur As CreditCur, Round(Sum(ifNull(Sales_CreditAmount,0)), ".$DecimalBase.") As  CreditAmount ,Count(*) As SumofRetails".
				  " From Retail ".
				  " Left Join Clients on Clients.Ledger_Number = Retail.Sales_creditacc ";
				  
		$query5 = " Select Sales_creditacccur As CreditCur, Round(Sum(ifNull(Sales_CreditAmount,0)), ".$DecimalBase.") As  CreditAmount ".
				  " From Retail ";
				  
		$query6 = " Select Round(Sum(ifNull(Sales_CashInBase1,0)),5) As SalesCashInBase1, Round(Sum(ifNull(Sales_CashOutBase1,0)),5) As SalesCashOutBase1,  ".
				  " Round(Sum(ifNull(Sales_CashInBase2,0)),5) As SalesCashInBase2, Round(Sum(ifNull(Sales_CashOutBase2,0)),2) As SalesCashOutBase2, ".
				  " Sum(ifNull(Sale_CashBase1,0)) As SaleCheckBase1, Sum(ifNull(Sale_CashBase2,0)) As SaleCheckBase2, ".
				  " Sum(ifNull(Sales_CreditAmountBase1,0)) As SalesCreditAmountBase1, Sum(ifNull(Sales_CreditAmountBase2,0)) As SalesCreditAmountBase2, ".
				  " Sum(ifNull(Sales_TipsBase1,0)) As SalesTipsBase1, ".
				  " Sum(ifNull(Coupon_AmountBase1,0)) As CouponAmountBase1, Sum(ifNull(Coupon_AmountBase2,0)) As CouponAmountBase2 ".
				  " ,Sum(if(Sales_CashInBase1>0,1,0)) as NumofCash, Sum(if(Sale_CashBase1>0,1,0)) as NumofChecks, Sum(if(Sales_CreditAmountBase1>0,1,0)) as NumofCredits, Sum(if(Coupon_AmountBase1>0,1,0)) as NumofCoupon".
				  " From Retail ";

	   
				  
		$query7 = " Select Retail.Coupon_Id, Coupon.Coupon_Reference As CouponReference, Coupon.Coupon_Name As CouponName, Round(Sum(ifNull(Retail.Coupon_Amount,0)), ".$DecimalBase.") As CouponAmount, ".
				  " Round(Sum(abs(ifNull(Retail.Coupon_Amount,0)) - ifNull(convertcurfunc(Coupon.Currency_Code, Retail.Currency_code, Coupon_Date, 0, 0, '', '', ifNull(Coupon.Coupon_AmountConsum,0)),0)), ".$DecimalBase.") As CouponAmountRem ".
				  " From Retail ".
				  " Left Join Coupon on Retail.Coupon_Id =  Coupon.Coupon_Id ";
		
		$query8 = " Select Round(Sum(ifNull(Sales_CashInBase1,0)),5) As SalesCashInBase1, Round(Sum(ifNull(Sales_CashOutBase1,0)),5) As SalesCashOutBase1,  ".
				  " Round(Sum(ifNull(Sales_CashInBase2,0)),5) As SalesCashInBase2, Round(Sum(ifNull(Sales_CashOutBase2,0)),2) As SalesCashOutBase2, ".
				  " Sum(ifNull(Sale_CashBase1,0)) As SaleCheckBase1, Sum(ifNull(Sale_CashBase2,0)) As SaleCheckBase2, ".
				  " Sum(ifNull(Sales_CreditAmountBase1,0)) As SalesCreditAmountBase1, Sum(ifNull(Sales_CreditAmountBase2,0)) As SalesCreditAmountBase2, ".
				  " Sum(ifNull(Sales_TipsBase1,0)) As SalesTipsBase1, ".
				  " Sum(ifNull(Coupon_AmountBase1,0)) As CouponAmountBase1, Sum(ifNull(Coupon_AmountBase2,0)) As CouponAmountBase2 ".
				  " ,Sum(if(Sales_CashInBase1>0,1,0)) as NumofCash, Sum(if(Sale_CashBase1>0,1,0)) as NumofChecks, Sum(if(Sales_CreditAmountBase1>0,1,0)) as NumofCredits, Sum(if(Coupon_AmountBase1>0,1,0)) as NumofCoupon".
				  " , IfNull(PaymentMethods.payment_description,'Customers') as payment_description ".
				  " From Retail ";
				  
		if ($projectexist > 0 && $FI_RepRestrictProject == 1) {
			$query2= $query2.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
			$query3= $query3.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
			$query4= $query4.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
			$query5= $query5.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
			$query6= $query6.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
			$query7= $query7.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
			$query8= $query8.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
		}	
									 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) {
			$query2= $query2.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
			$query3= $query3.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
			$query4= $query4.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
			$query5= $query5.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
			$query6= $query6.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
			$query7= $query7.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
			$query8= $query8.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";
		}	
		
				  
		$querycond= " Where Retail.User_Account = ifNull(".$fromuser.", Retail.User_Account) ".
				    " And Retail.CashRegister_Name = ifNull(".$fromcashreg.", Retail.CashRegister_Name) ".
				    " And ifNull(Retail.Customer_Code,'-1') between ifNull(".$fromcustomer.",ifNull(Retail.Customer_Code,'-1')) and ifNull(". $tocustomer.",ifNull(Retail.Customer_Code,'-1')) ".
				    " And Date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s') between ifNull(". $fromdate.",date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s')) and ifNull(". $todate.",date_format(Retail.Sale_Date,'%Y-%m-%d %H:%i:%s'))  ".
				    " And ifNull(Retail.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Retail.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Retail.Project_Code,'-1')) ".
				    " And ifNull(Retail.CostCenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Retail.CostCenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Retail.CostCenter_Code,'-1')) ".
				    " And ifNull(Retail.Salesman_Code,'-1') = ifNull(".$fromsalesman.", ifNull(Retail.Salesman_Code,'-1')) ".
					" And ifNull(Retail.Sales_creditacc,'-1') between ifNull(".$fromcredit.",ifNull(Retail.Sales_creditacc,'-1')) and ifNull(". $tocredit.",ifNull(Retail.Sales_creditacc,'-1')) ";
					
		$query2= $query2.$querycond." And Sale_CheckNo != '' And ifNull(Sale_Cash,0) != 0 Order By Sale_CheckNo  ";
		$query3= $query3.$querycond." And Sale_CheckNo != '' And ifNull(Sale_Cash,0) != 0 Group By CheckCurr ";
		$query4= $query4.$querycond." And Sales_creditacc != '' And ifNull(Sales_CreditAmount,0) != 0 Group By CreditCard, CreditCur Order By CreditCard ";
		$query5= $query5.$querycond." And Sales_creditacc != '' And ifNull(Sales_CreditAmount,0) != 0 Group By CreditCur ";
		$query7= $query7.$querycond." And Retail.Coupon_Id != '' And Retail.Coupon_Id Is Not Null And ifNull(Retail.Coupon_Amount,0) != 0 Group By CouponReference Having CouponAmount != 0 ";
		
		$query8=$query8;
		$query8= $query8." Left Join PaymentMethods on Retail.Payment_Description = PaymentMethods.Payment_Description ";
		$query6= $query6.$querycond;
		$query8= $query8.$querycond;
		$query8= $query8."group by ifNull(PaymentMethods.Payment_Description,'-1') ";
		
				
		//echo $query6; exit;
		$result6=mysql_query($query6,$connection) or die(mysql_error());
		while($rows6=mysql_fetch_array($result6)){
				extract($rows6);
		}		
		
		$TotalCashInB1  = $SalesCashInBase1;
		$TotalCashInB2  = $SalesCashInBase2;
		$TotalCashOutB1 = $SalesCashOutBase1;
		$TotalCashOutB2 = $SalesCashOutBase2;
		$TotalChecksB1  = $SaleCheckBase1;
		$TotalChecksB2  = $SaleCheckBase2;
		$TotalCreditsB1 = $SalesCreditAmountBase1;
		$TotalCreditsB2 = $SalesCreditAmountBase2;
		$TotalCouponB1  = $CouponAmountBase1;
		$TotalCouponB2  = $CouponAmountBase2;
		$TotalTipsB1 	= $SalesTipsBase1;
		
		$SubTotalCashB1 = $TotalCashInB1 - $TotalCashOutB1;
		$SubTotalCashB2 = $TotalCashInB2 - $TotalCashOutB2;
		
		$CurrDate = Date('Y-m-d h:i:s');
		$SubTotalCashB11 = ConvertCurrency($Base2, $Base1, $CurrDate, $SubTotalCashB2, $connection);
		$SubTotalCashB22 = ConvertCurrency($Base1, $Base2, $CurrDate, $SubTotalCashB1, $connection);
		
		$TotalCashB1 = $SubTotalCashB1 + $SubTotalCashB11;
		$TotalCashB2 = $SubTotalCashB2 + $SubTotalCashB22;
		
		 
		
		
		$TotalTipsB2 = ConvertCurrency($Base1, $Base2, $CurrDate, $TotalTipsB1, $connection);
		
		$NetTotalB1	 = $TotalCashB1 + $TotalChecksB1 + $TotalCreditsB1 + $TotalCouponB1 + $TotalTipsB1;
		$NetTotalB2	 = $TotalCashB2 + $TotalChecksB2 + $TotalCreditsB2 + $TotalCouponB2 + $TotalTipsB2;
		
		$query11 = "select sum(ifnull(ctransdt_amountb1,0)) as cashoutb1, sum(ifnull(ctransdt_amountb2,0)) as cashoutb2
				from cashregistertransdt
				left join cashregistertrans on cashregistertrans.ctrans_id = cashregistertransdt.ctrans_id
				where cashregister_name = ifNull(".$fromcashreg.", cashregister_name)    
				And Date_format(ctransdt_date,'%Y-%m-%d %H:%i:%s') between ifNull(". $fromdate.",date_format(ctransdt_date,'%Y-%m-%d %H:%i:%s')) 
				and ifNull(". $todate.",date_format(ctransdt_date,'%Y-%m-%d %H:%i:%s')) and ctransdt_type = 'OUT';";
		$result11=mysql_query($query11,$connection) or die(mysql_error());
		while($rows11=mysql_fetch_array($result11)){
				extract($rows11);
		}
		if($cashoutb1 != '') $totcashoutb1 = $cashoutb1;
		else $totcashoutb1 = 0;
		if($cashoutb2 != '') $totcashoutb2 = $cashoutb2;
		else $totcashoutb2 = 0;
		
		echo "<tr><td colspan='11' style='padding-top:20px;'>"; 
		echo "<table width='250' border='0' align='left' class='summary' style='table-layout:fixed;font-size:10px'>
			  <col width=60>
			  <col width=65>
			  <col width=60>
			  <col width=55>
			  ";
	
	 
	
		$TotalQty = $NumofCash + $NumofChecks + $NumofCredits + $NumofCoupon + $SumofTips;
		
		echo"	<tr><th align='left' style='border-bottom: 1px solid black;'>Summ.C/V</th><th align='center'>$Base1</th><th align='center'>$Base2</th><th align='left' style='border-bottom: 1px solid black;'>Quantity</th><th></th></tr>";
		echo"	<tr><th align='left'>Cash</th><td align='right'>". number_format($TotalCashB1, $DecimalBase) ."</td><td align='right'>". number_format($TotalCashB2, $DecimalBase) ."</td><td align='center'>$NumofCash</td><td align='center'>&nbsp;</td></tr>";
		echo"	<tr><th align='left'>Checks</th><td align='right'>". number_format($TotalChecksB1, $DecimalBase) ."</td><td align='right'>". number_format($TotalChecksB2, $DecimalBase) ."</td><td align='center'>$NumofChecks</td><td align='center'>&nbsp;</td></tr>";
		
		$result8=mysql_query($query8,$connection) or die(mysql_error());
		while($rows8=mysql_fetch_array($result8)){
				extract($rows8);
				$TotalCreditsB1 = $SalesCreditAmountBase1;
				$TotalCreditsB2 = $SalesCreditAmountBase2;
				
				echo"	<tr><th align='left'>$payment_description</th><td align='right'>". number_format($TotalCreditsB1, $DecimalBase) ."</td><td align='right'>". number_format($TotalCreditsB2, $DecimalBase) ."</td><td align='center'>$NumofCredits</td><td align='center'>&nbsp;</td></tr>";
		}	
		//echo"	<tr><th align='left'>Credits</th><td align='right'>". number_format($TotalCreditsB1, $DecimalBase) ."</td><td align='right'>". number_format($TotalCreditsB2, $DecimalBase) ."</td><td align='center'>$NumofCredits</td><td align='center'>&nbsp;</td></tr>";		
		echo"	<tr><th align='left'>Coupon</th><td align='right'>". number_format($TotalCouponB1, $DecimalBase) ."</td><td align='right'>". number_format($TotalCouponB2, $DecimalBase) ."</td><td align='center'>$NumofCoupon</td><td align='center'>&nbsp;</td></tr>";
		echo"	<tr><th align='left'>Tips</th><td align='right'>". number_format($TotalTipsB1, $DecimalBase) ."</td><td align='right'>". number_format($TotalTipsB2, $DecimalBase) ."</td><td align='center'>$SumofTips</td><td align='center'>&nbsp;</td></tr>";
		echo "	<tr height='5'><td colspan='4'><h4></td><td align='center'>&nbsp;</td></tr>";
		echo"	<tr><th align='left'>Total</th><td align='right'>". number_format($NetTotalB1, $DecimalBase) ."</td><td align='right'>". number_format($NetTotalB2, $DecimalBase) ."</td><td align='center'>$TotalQty</td><td align='center'>&nbsp;</td></tr>";
		echo"	<tr><th align='left'>Cash out</th><td align='right'>". number_format($totcashoutb1, $DecimalBase) ."</td><td align='right'>". number_format($totcashoutb2, $DecimalBase) ."</td></tr>";
		echo "	<tr height='5'><td colspan='4'><h4></td><td align='center'>&nbsp;</td></tr>";
		echo"	<tr><th align='left'>Net Total</th><td align='right'>". number_format($NetTotalB1-$totcashoutb1, $DecimalBase) ."</td><td align='right'>". number_format($NetTotalB2-$totcashoutb2, $DecimalBase) ."</td><td align='center'>$TotalQty</td><td align='center'>&nbsp;</td></tr>";
		echo"</table>";
		echo"</td></tr>";
		
	}
}

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



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 ConvertCurrency($FromCurr, $ToCurr, $FromDate, $Amount, $connection)
{
	$query = "SELECT ifNull(convertcurfunc('".$FromCurr."', '".$ToCurr."', '".$FromDate."', 0, 0, '', '', ifNull($Amount,0)),0) From Dual ";
	$result=mysql_query($query,$connection);
	$row=mysql_fetch_row($result);
	//echo $query;
	Return	$row[0];

}

function explodewordtime($date)
{

$trimtime = explode(' ',$date);
$date = $trimtime[0];
$time = $trimtime[1];

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

if ($printaccess==1 && $_GET['print'] == 1)
print <<< HERE

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

?>