<?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> Daily Sales Per Warehouse </TITLE>

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

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

				 
table.group td { font-size: 14px; 
                 font-weight: bold;
				 font-style: italic;
                 font-family:Times-New-Roman;
               }	

table.detail th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                 }				 			   
table.detail td { font-size: 12px; 
				  font-weight: normal;
                  font-family:Times-New-Roman;
                }		
table.detail td.sr  { font-size: 11px; 
					  font-style: italic; 
                      font-family:Times-New-Roman;
                    }				
				 
.totals { font-size: 12px; 
          font-weight: bold;
          font-family:Times-New-Roman;
        }					  
				 
h4 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 2px;
   }
h5 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 1px;
   }   
   
</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);
}



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

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


//Read From CashRegister Table
/*$queryCashRegister=" Select ifNull(CashRegisterTTC,0) As CashRegisterTTC ".
				   " From CashRegister ".
				   " Where CashRegister_Name = '".$_SESSION['cashregister']."' ";*/

$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>';

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

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

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

  

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($curr))  $curr = '';
//else  $curr = "'".$curr."'";   

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($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."'";  
   

print <<<HERE
</font></td>
</tr>
<tr height='5'><td colspan=4><h5></td></tr>
</table>
</td></tr>

<tr><td>
<table class='params' width='800' border='0' align='left' style='table-layout:fixed' >
<tr>
  <td width="12%"><b>Date From/To:</b></td>
  <td width="18%">$dispfromdate - $disptodate </td>
  <td width="8%" valign='top'><b>Currency:</b></td>
  <td width="12%">$dispcurr</td>
  <td width="6%" valign='top'><b>User:</b></td>
  <td width="44%">$dispfromuser</td>
</tr>
<tr height='5'><td colspan=6><h4></td></tr>
</table>
</td></tr>

<tr><td>
HERE;

$masterclass = 'master';



	

$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 Q1.Item_Code, Q1.Item_Description, Q1.Warehouse_Code, SaleDtQuantity, ifNull(QQtyExists.QuantityExists,0) As QuantityExists, ".
    	 " QQtyExists.Warehouse_Code, SaleDtTotal, SaleDtTotalTTC, SaleDtTotalBase2, SaleDtTotalTTCBase2 ".
		 " From ( ";
		 
$query = $query." Select RetailDt.Item_Code, Items.Item_Description, RetailDt.Warehouse_Code, 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 ".
				" From RetailDt  ".
				" Inner Join Retail on RetailDt.Sale_Id = Retail.Sale_Id ".
				" Inner Join Items on RetailDt.Item_Code = Items.Item_Code ";
		 
		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 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') between ifNull(". $fromdate.",date_format(Retail.Sale_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Retail.Sale_Date,'%Y-%m-%d'))  ".
				" 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')) ";
		 
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 RetailDt.Item_code, RetailDt.Warehouse_Code ".
			   " Order By RetailDt.Item_code, RetailDt.Warehouse_Code ".
			   " ) As Q1 ";
		 
$query = $query." Left Join (  ".
				"               Select Stk.Item_Code, Stk.Warehouse_Code, Sum((ifNull(quantityproducein,0) + ifNull(quantityentered,0) - ifNull(quantityproduceout,0) - ifNull(quantitydelivered,0))) As QuantityExists  ".
				"               From StkTransaction Stk ".
				"               Where Date_Format(Stk.Transaction_Date,'%Y-%m-%d') <= ifNull(". $todate.",date_format(Stk.Transaction_Date,'%Y-%m-%d')) ".
				//"               And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
				"               And ifNull(Stk.project_code,'-1')   Like Concat( ifNull(".$fromproject.",ifNull(Stk.project_code,'-1')),'%')  ".
				"               And ifNull(Stk.Costcent_Code,'-1')  Like Concat(ifNull(".$fromcostcent.",ifNull(Stk.Costcent_Code,'-1')),'%')  ".
				"               And ifNull(Stk.warehouse_code,'-1') Like Concat( ifNull(". $fromwarehouse .",ifNull(Stk.warehouse_code,'-1')),'%')  ".
				"               And (($Module_Grn=1 and Stk.Transaction_Type <> 'PI') or ($Module_Grn=0 and 1=1)) ".
				"               Group By Stk.Item_Code, Stk.Warehouse_code ".
				"             ) As QQtyExists on Q1.Item_Code = QQtyExists.Item_Code ";

//and Q1.Warehouse_Code = QQtyExists.Warehouse_Code  		 


//echo $query; exit;



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

$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){

	//Check If Item Exists In Array
	$ItemFound = 0;
	$count = count ($ArrayItems); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayItems[$j] == $rows[0])
			$ItemFound = 1;
	}
				
	if ($ItemFound == 0) 
		$ArrayItems[$count] = $rows[0];
		
    //Check If Warehouse Exists In Array
	$WarehouseFound = 0;
	$count = count ($ArrayWarehouse); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayWarehouse[$j] == $rows[5])
			$WarehouseFound = 1;
	}
				
	if ($WarehouseFound == 0) 
		$ArrayWarehouse[$count] = $rows[5];
		
	$ArrayRecordData[$rows[0]]['ItemCode']      = $rows[0];
	$ArrayRecordData[$rows[0]]['ItemDesc']      = $rows[1];
	$ArrayRecordData[$rows[0]]['SoldQty']       = $rows[3];
	$ArrayRecordData[$rows[0]]['Warehouse']     = $rows[2];
	
	
	$ArrayRecordData[$rows[0]][$rows[5]]['ExistsQty']   = $rows[4];
	
	$ArrayRecordData[$rows[6]]['TotalQty']    = $ArrayRecordData[$rows[6]]['TotalQty'] + $rows[10];
	$ArrayRecordData[$rows[6]]['TotalAmount'] = $ArrayRecordData[$rows[6]]['TotalAmount'] + $rows[12];


}


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

echo "<table width='100%' border='0' align='left' class='master' style='table-layout:fixed'>
	  <col width=120>
	  <col width=280>
	  <col width=70>
	  <col width=70>";
	  
$CountWarehouse = count($ArrayWarehouse); 
for ($j=0; $j < $CountWarehouse; $j++){	
	echo"<col width=80>";
}

echo" <tr>
		<th >Item Code</th>
		<th >Description</th>
		<th >Qty Sold</th>
		<th >Warehouse</th>";
		
$CountWarehouse = count($ArrayWarehouse); 
for ($j=0; $j < $CountWarehouse; $j++){	
	echo"<th >Qty Exists (".$ArrayWarehouse[$j].") </th>";
}		
	
$ColsNum = $CountWarehouse + 4;	
echo" </tr>
	  <tr height='5'><td colspan='$ColsNum'><h4></td></tr>";


		
// Displaying Data
$CountItems = count($ArrayItems); 
for ($i=0; $i<$CountItems; $i++){ 

	echo "<tr>"; 
	echo "	<td align='left' valign='top' >". $ArrayRecordData[$ArrayItems[$i]]['ItemCode'] ."</td>";
	echo "	<td align='left' valign='top' >". $ArrayRecordData[$ArrayItems[$i]]['ItemDesc'] ."</td>";
	echo "	<td align='right' valign='top'>".number_format($ArrayRecordData[$ArrayItems[$i]]['SoldQty'], $ClientSalesRMaskQty)."</td>";
	echo "	<td align='center' valign='top' >". $ArrayRecordData[$ArrayItems[$i]]['Warehouse'] ."</td>";
	
	
	$CountWarehouse = count($ArrayWarehouse); 
	for ($j=0; $j < $CountWarehouse; $j++){
		$ArrayTotalByReport[$ArrayWarehouse[$j]]['ExistsQty']    = $ArrayTotalByReport[$ArrayWarehouse[$j]]['ExistsQty'] + $ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['ExistsQty'];
		
		echo "	<td align='right' valign='top'>".number_format($ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['ExistsQty'], $ClientSalesRMaskQty)."</td>";
	}
	
	echo "</tr>";
	
	$ArrayTotalByReport['SoldQty']  = $ArrayTotalByReport['SoldQty'] + $ArrayRecordData[$ArrayItems[$i]]['SoldQty'];
	
}
		
	


// Report Totals
$CountWarehouse = count($ArrayWarehouse); 
$ColsNum = $CountWarehouse + 4;	

echo "<tr height='5'><td colspan='$ColsNum'><h4></td></tr>"; 
//echo "<tr height='10'><td colspan='$ColsNum'>&nbsp;</td></tr>"; 
echo "<tr class='totals'>"; 
echo "  <td align='right' colspan='2'>Total </td>";
echo "	<td align='right' valign='top' >". number_format($ArrayTotalByReport['SoldQty'], $ClientSalesRMaskQty) ."</td>";
echo "  <td align='right' >&nbsp;</td>";

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

echo "</tr>";
	

echo "</table></td></tr>";
echo "</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;
}


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

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

?>