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

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


$export = $_GET['export'];

if($export ==1){
	$filename = "Exp_RetailStatistics" . date('Ymd') . ".xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
} 

if($export ==1){
}
print <<< HERE
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<TITLE> List of Retail Report </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: 14px; 
                  font-family:Times-New-Roman;
                 }
				 
table.master1 th { font-size: 14px; 
                   font-weight: bold;
                   font-family:Times-New-Roman;
                 }				 
table.master1 td { font-size: 13px; 
				   font-weight: bold;
                   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($_SESSION['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('".$_SESSION['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 from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}

//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 ".$_SESSION['dbLabel'].".logindt on logindt.Cash_Register = CashRegister.CashRegister_Name ".
					 " Where logindt.User_Account = '".$_SESSION['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 ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


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

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

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

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

$RepTitle = 'Statistical';
 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>$RepTitle</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;
$dispcashregister     = $fromcashreg;

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

if (empty($fromitem))  $fromitem = 'null';
else  $fromitem = "'".$fromitem."'";   
if (empty($toitem))  $toitem = 'null';
else  $toitem = "'".$toitem."'";      


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


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

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."'";  
   
if (empty($showunpost))  $showunpost = 0;
if (empty($showpending)) $showpending = 0;
if (empty($custperitem)) $custperitem = 0;


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></b></td>
  <td width="18%"></td>
  <td width="10%" valign='top'><b></b></td>
  <td width="13%"></td>
</tr>
<tr>
  <td width="15%" valign='top'><b>Group From/To</b></td>
  <td width="35%">$dispfromgroup - $disptogroup </td>
  <td width="15%" colspan=2 valign='top'><b>SubGroup From/To</b></td>
  <td width="35%">$dispfromsubgroup - $disptosubgroup </td>
</tr>  
<tr>
  <td width="10%" valign='top'><b>Item From/To</b></td>
  <td width="60%" colspan=4>$dispfromitem - $disptoitem </td>
</tr>
<tr height='5'><td colspan=8><h4></td></tr>
</table>
</td></tr>

<tr><td>
HERE;
 
 
 
 
$query4 = " Select date_format(Retail.Sale_Date,'%d/%m/%Y'),Projectdt_name, ";

if ($showdetail == 1 ) 
	$query4= $query4." ifnull(Sale_numberofguests,0) As TotalGuests,Customer_name, ";
else 
	$query4= $query4." Sum(ifnull(Sale_numberofguests,0)) As TotalGuests,Customer_name, ";
	
	

		  
$query4= $query4. " Retail.Projectdt_id	";
		  

$query4= $query4. " From Retail  ";
			   

$query4= $query4." Left Join Projectdt on Projectdt.Projectdt_id = Retail.Projectdt_id ";

$query4= $query4." Left Join Customer on Retail.Customer_Code = Customer.Customer_Code ".
				  " Left Join ".$_SESSION['dbLabel'].".Users on Retail.User_Account = Users.User_Account ";
		  
		if ($projectexist > 0 && $FI_RepRestrictProject == 1)
			$query4= $query4.$QueryRestrictPrj." on Retail.Project_Code like concat(Q4.project_code,'%') ";
							 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query4= $query4.$QueryRestrictCst." on Retail.CostCenter_Code like concat(Q5.costcent_code,'%') ";

$query4= $query4.
          " Where Retail.User_Account = ifNull(".$fromuser.", Retail.User_Account) and Sale_Type = 'dinein'".	  
		  " 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'))  ";


if ($showunpost == 1)
	$query4= $query4." And ifNull(Retail.Sale_Posted, 0) = 0 ";
	
		
if ($showdetail == 0 ) 
	$query4= $query4." Group By ifnull(Retail.ProjectDt_id,-1),date_format(Retail.Sale_Date,'%d/%m/%Y')  ";
//else
//	$query4= $query4." Group By ifnull(Retail.ProjectDt_id,-1),date_format(Retail.Sale_Date,'%d/%m/%Y'),Retail.Sale_id  ";

$query4= $query4." Order By  date_format(Retail.Sale_Date,'%d/%m/%Y'), ProjectDt_Name  ";		

//echo $query4;
 
echo "<table width='800' border='0' align='left' class='master' style='table-layout:fixed'>
			  <col width=120>
			  <col width=280>
			  <col width=80>
			  <col width=120>
			  <col width=120>
			  <tr>";
			  

if ($showdetail == 1 ) {
			echo"<th >Date</th>
				<th >Guest</th>
				<th >Table</th>
				 <th  align='right'>No. of Guests</th>
		  	    </tr>
			    <tr height='5'><td colspan=5><h4></td></tr>";
 } else {
 
 echo"<th align='left'>Date</th>
	  <th  align='left'>Table</th>
 	  <th  align='left'>No. of Guests</th>
	  </tr>
	  <tr height='5'><td colspan=5><h4></td></tr>";
 
 }
 $result=mysql_query($query4,$connection) or die(mysql_error());	
 
 if ($showdetail == 1 ) {	 
 
 $Date = '';
 $i=1;
 while($rows=mysql_fetch_row($result)){
 
 
		 if($Date!=$rows[0] and $i>1){
				$GroupByLabel = '';
				$GroupByLabel = 'Total No. of Guests '.$Date;
					
				
				echo "<tr class='totals'>"; 
				echo "  <td align='right' colspan='3'>$GroupByLabel&nbsp;</td>";
				echo "	<td align='right' valign='top' >". $SumQty ."</td>";
				echo "	<td align='right' valign='top' ></td>";
				echo "	<td align='right' valign='top' >&nbsp;</td>";
				echo "</tr>";
				echo "<tr height='5'><td colspan=5><h5></td></tr>"; 
				$SumQty=0;
		 
		 }
	 $SumQty = $SumQty + $rows[2];
	 $TotalSumQty = $TotalSumQty + $rows[2];
				echo "<tr>"; 
				echo "	<td align='left' valign='top' >". $rows[0] ."</td>";
				echo "	<td align='left' valign='top' >". $rows[3] ."</td>";
				echo "	<td align='left' valign='top' >". $rows[1] ."</td>";
				echo "	<td align='right' valign='top' >". $rows[2] ."</td>";
				echo "</tr>";
				
	 
	 
	 
	 $Date=$rows[0];
	 $i+=1;
	 }
 $GroupByLabel = '';
			$GroupByLabel = 'Total No. of Guests '.$Date;
				
			
			echo "<tr class='totals'>"; 
			echo "  <td align='right' colspan='3'>$GroupByLabel&nbsp;</td>";
			echo "	<td align='right' valign='top' >". $SumQty ."</td>";
			echo "	<td align='right' valign='top' ></td>";
			echo "	<td align='right' valign='top' >&nbsp;</td>";
			echo "</tr>";
			echo "<tr height='5'><td colspan=5><h5></td></tr>"; 
			$SumQty=0;	 
 
 }
 else {
			 $Date = '';
			 $i=1;
			 while($rows=mysql_fetch_row($result)){
			 
			 
				 if($Date!=$rows[0] and $i>1){
						$GroupByLabel = '';
						$GroupByLabel = 'Total No. of Guests '.$Date;
							
						
						echo "<tr class='totals'>"; 
						echo "  <td align='right' colspan='2'>$GroupByLabel&nbsp;</td>";
						echo "	<td align='right' valign='top' >". $SumQty ."</td>";
						echo "	<td align='right' valign='top' ></td>";
						echo "	<td align='right' valign='top' >&nbsp;</td>";
						echo "</tr>";
						echo "<tr height='5'><td colspan=5><h5></td></tr>"; 
						$SumQty=0;
				 
				 }
			 $SumQty = $SumQty + $rows[2];
			 $TotalSumQty = $TotalSumQty + $rows[2];
						echo "<tr>"; 
						echo "	<td align='left' valign='top' >". $rows[0] ."</td>";
						echo "	<td align='left' valign='top' >". $rows[1] ."</td>";
						echo "	<td align='right' valign='top' >". $rows[2] ."</td>";
						echo "</tr>";
						
			 
			 
			 
			 $Date=$rows[0];
			 $i+=1;
}
 
 $GroupByLabel = '';
			$GroupByLabel = 'Total No. of Guests '.$Date;
				
			
			echo "<tr class='totals'>"; 
			echo "  <td align='right' colspan='2'>$GroupByLabel&nbsp;</td>";
			echo "	<td align='right' valign='top' >". $SumQty ."</td>";
			echo "	<td align='right' valign='top' ></td>";
			echo "	<td align='right' valign='top' >&nbsp;</td>";
			echo "</tr>";
			echo "<tr height='5'><td colspan=5><h5></td></tr>"; 
			$SumQty=0;
 
 
 }
 
			$GroupByLabel = '';
			$GroupByLabel = 'Total Guests ';
				
			
			echo "<tr class='totals'>"; 
			
			 if ($showdetail == 1 )
				echo "  <td align='right' colspan='3'>$GroupByLabel&nbsp;</td>";
				else
				echo "  <td align='right' colspan='2'>$GroupByLabel&nbsp;</td>";
			
			echo "	<td align='right' valign='top' >". $TotalSumQty ."</td>";
			echo "	<td align='right' valign='top' ></td>";
			echo "	<td align='right' valign='top' >&nbsp;</td>";
			echo "</tr>";
			
			$TotalSumQty=0; 
 
 
 
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;

 
 
 
 
 ?>