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

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



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


print <<< HERE
<HTML>
<HEAD>

<TITLE> List of Customers Retail</TITLE>

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

table.master th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                      }				 
table.master td { font-size: 11px; 
                  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]);

if ($itemstatus == '1') $RepTitle = "Gift Items List";
else if ($itemstatus == '1') $RepTitle = "Void Items List";
else $RepTitle = "Items Status List";





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




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>$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(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 = '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>User:</b></td>
  <td width="18%">$dispfromuser</td>
  <td width="10%" valign='top'><b>CashRegister:</b></td>
  <td width="13%">$dispcashregister</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;
echo "<table width='780' border='0' align='left' class='master' style='table-layout:fixed'>
			  <col width=60>
			  <col width=90>
			  <col width=180>
			  <col width=200>
			  <col width=70>
			  <col width=180>
			  <tr>
				<th >REF. </th>
				<th >Date</th>
				<th >Item Code</th>
				<th >Description</th>
				<th >Qty.</th>
				<th >Status</th>
			  </tr>";			  
echo "<tr><td colspan='6'><h4></h4></td></tr>";

$query = "select Sale_Reference, date_format(retail.Sale_Date, '%d-%m-%Y %H:%i'), retaildt.item_code, item_description, SaleDt_Quantity, if(saleDt_ItemStatus = 1, 'Gift', SaleDt_ItemStatusRemark) 
		from retaildt
		inner join retail on retail.sale_id = retaildt.sale_id
		inner join items on items.item_code = retaildt.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 .= "	where Retail.User_Account = ifNull(".$fromuser.", Retail.User_Account) and saleDt_ItemStatus <> 0 ".
		  " 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 ($itemstatus == '1')		
	$query .= " and saleDt_ItemStatus = '1' ";
if ($itemstatus == '2')		
	$query .= " and saleDt_ItemStatus = '2' ";	
if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
	$query .= " And RetailDt.Warehouse_Code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";

$query .= " order by Sale_Reference ";
//echo $query;
$result = mysql_query($query, $connection);
while ($rw = mysql_fetch_array($result)){
	
	echo "<tr height='15px'>
			<td align='center'>".$rw[0]."</td>
			<td >".$rw[1]."</td>
			<td >".$rw[2]."</td>
			<td >".$rw[3]."</td>
			<td align='center'>".$rw[4]."</td>
			<td align='center'>".$rw[5]."</td>
		  </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;

?>