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


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


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

$RepTitle = 'Sales Viewer';




$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($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(strlen($fromdate)==10)
	$fromdate = $fromdate." 00:00:00";

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

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

 if($groupby==1)
	$type=' Insert ';
	
if($groupby==2)
	$type=' Discount ';
	
if($groupby==3)
	$type=' Refund ';
	
if($groupby==4)
	$type=' Delete ';
 if($groupby==5)
	$type=' Edit ';


 
 if($groupby==4){
 print <<<HERE
</font></td>
</tr>
<tr height='5'><td colspan=4><h5></td></tr>
</table>
</td></tr>

<tr><td colspan=2>
<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>User:</b></td>
  <td width="12%">$dispfromuser</td>
  <td width="9%" valign='top'><b>CashRegister:</b></td>
  <td width="18%">$dispcashregister</td>
  <td width="6%" valign='top'><b>Type:</b></td>
  <td width="18%">$type</td>
  
</tr>
</table></td></tr>
<tr><td colspan=10><table class='params' width='800' border='0' align='left' style='table-layout:fixed' >
HERE;


 echo "	<tr height='1'><td colspan=6><h5></td></tr>";
 echo "<tr><td></td>
						<td align=center><b>CashRegister</td>
						<td align=center><b>Invoice No</td>
						<td align=center><b>Invoice Date</td>
						<td align=center><b>Date Deleted</td>
						<td align=center><b>Net Total </td>
			</tr>";
 echo "	<tr height='5'><td colspan=6><h5></td></tr>";
	
 $query = " Select date_format(viewer_date,'%d/%m/%Y %H:%i:%s'), user_name, cashregister_name, trans_reference, date_format(trans_date,'%d/%m/%Y %H:%i:%s'), trans_netamount1
			From viewerretail
			Left Join ".$HTTP_SESSION_VARS['dbLabel'].".Users on viewerretail.user_account = Users.User_Account
			where Trans_Type = 'D' And FormName = 'Retail'
			And Date_Format(viewer_date,'%Y-%m-%d') between Date_Format('".$fromdate."','%Y-%m-%d') and Date_Format('".$todate."','%Y-%m-%d')
			and viewerretail.user_account = if('$fromuser'='',viewerretail.user_account,'$fromuser')
			and cashregister_name = if('$fromcashreg'='',cashregister_name,'$fromcashreg')
			Order by user_name,viewer_date,trans_date";
 
 
  $result=mysql_query($query,$connection) or die(mysql_error());
  $username='';
  while($row=mysql_fetch_row($result)){
 
	if($username!=$row[1])
		echo "<tr><td><u><b>User: $row[1]</b><u></td></tr>";
	
	echo "<tr><td></td>
						<td>$row[2]</td>
						<td>$row[3]</td>
						<td>$row[4]</td>
						<td>$row[0]</td>
						<td align=right>". number_format($row[5],$DecimalBase)."</td>
			</tr>";
	
	
	
   $username = $row[1];
   $viewerdate = $row[0];
  }
 
} 
else if($groupby==5){
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>User:</b></td>
  <td width="12%">$dispfromuser</td>
  <td width="6%" valign='top'><b>CashRegister:</b></td>
  <td width="18%">$dispcashregister</td>
  <td width="6%" valign='top'><b>Type:</b></td>
  <td width="18%">$type</td>
  
</tr>
</table></td></tr>
<tr><td colspan=10><table class='params' width='800' border='0' align='left' style='table-layout:fixed' >
HERE;


 echo "	<tr height='1'><td colspan=12><h5></td></tr>";
 echo "<tr><td></td>
						<td align=center><b>CashRegister</td>
						<td align=center><b>Invoice No</td>
						<td align=center><b>Invoice Date</td>
						<td align=center><b>Date Deleted</td>
						<td align=center><b>O.Total</td>
						<td align=center><b>N.Total</td>
						<td align=center><b>O.Discount</td>
						<td align=center><b>N.Discount</td>
						<td align=center><b>O.Account </td>
						<td align=center><b>N.Account </td>
						<td align=center><b>User Account</td>
			</tr>";
 echo "	<tr height='5'><td colspan=12><h5></td></tr>";
	
 $query = " Select	date_format(viewer_date,'%d/%m/%Y %H:%i:%s'), user_name, cashregister_name, trans_reference
			, date_format(trans_date,'%d/%m/%Y %H:%i:%s'), trans_netamount1, trans_netamount2, trans_discountamount1
			, trans_discountamount2, trans_account1, trans_account2,user_discount, date_format(viewer_date,'%d/%m/%Y')
			From viewerretail
			Left Join ".$HTTP_SESSION_VARS['dbLabel'].".Users on viewerretail.user_account = Users.User_Account
			where (Trans_Type = 'U' or Trans_Type = 'I' ) And FormName = 'Retail'
			And Date_Format(viewer_date,'%Y-%m-%d') between Date_Format('".$fromdate."','%Y-%m-%d') and Date_Format('".$todate."','%Y-%m-%d')
			and viewerretail.user_account = if('$fromuser'='',viewerretail.user_account,'$fromuser')
			and cashregister_name = if('$fromcashreg'='',cashregister_name,'$fromcashreg')
			Order by viewerretail.user_account,viewer_date,trans_date";
 
 
  $result=mysql_query($query,$connection) or die(mysql_error());
  $username='';
  while($row=mysql_fetch_row($result)){
 
	if($username!=$row[1]){
		echo "<tr><td><u><b>User: $row[1]</b><u></td></tr>";
		$viewerdate='';
	}
	/*if($viewerdate!=$row[12]){
			echo "<tr><td></tD><td colspan=3><u><b><font size=2>$row[12]</b></u></font></td></tr>";
		}*/
	
	echo "<tr><td></td>
						<td>$row[2]</td>
						<td>$row[3]</td>
						<td>$row[4]</td>
						<td>$row[0]</td>
						<td align=right>". number_format($row[5],$DecimalBase)."</td>
						<td align=right>". number_format($row[6],$DecimalBase)."</td>
						<td align=right>". number_format($row[7],$DecimalBase)."</td>
						<td align=right>". number_format($row[8],$DecimalBase)."</td>
						<td align=right>". $row[9]."</td>
						<td align=right>". $row[10]."</td>
						<td align=right>". $row[11]."</td>
			</tr>";
	
	
	
   $username = $row[1];
   $viewerdate = $row[12];
  }
 
}
else if($groupby==2){
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>User:</b></td>
  <td width="12%">$dispfromuser</td>
  <td width="6%" valign='top'><b>CashRegister:</b></td>
  <td width="18%">$dispcashregister</td>
  <td width="6%" valign='top'><b>Type:</b></td>
  <td width="18%">$type</td>
  
</tr>
</table></td></tr>
<tr><td colspan=10><table class='params' width='800' border='0' align='left' style='table-layout:fixed' >
<col width=100>
<col width=100>
<col width=100>
<col width=150>
<col width=100>
<col width=100>
<col width=100>
<col width=100>

HERE;

 echo "	<tr height='1'><td colspan=12><h5></td></tr>";
 echo "<tr><td></td>
						<td align=center><b>CashRegister</td>
						<td align=center><b>Invoice No</td>
						<td align=center><b>Invoice Date</td>
						<td align=center><b>Gross Total</td>
						<td align=center><b>Discount</td>
						<td align=center><b>Net Total</td>
						<td align=center><b>Payment Type</td>
			</tr>";
 echo "	<tr height='5'><td colspan=12><h5></td></tr>";
	
 $query = " Select	date_format(viewer_date,'%d/%m/%Y %H:%i:%s'), user_name, cashregister_name, trans_reference
			, date_format(trans_date,'%d/%m/%Y %H:%i:%s'), trans_netamount1, trans_netamount2, trans_discountamount1
			, trans_discountamount2, trans_account1, trans_account2,user_discount, date_format(viewer_date,'%d/%m/%Y')
			From viewerretail
			Left Join ".$HTTP_SESSION_VARS['dbLabel'].".Users on viewerretail.user_account = Users.User_Account
			where Trans_Type = 'Ds' And FormName = 'Retail'
			And Date_Format(viewer_date,'%Y-%m-%d') between Date_Format('".$fromdate."','%Y-%m-%d') and Date_Format('".$todate."','%Y-%m-%d')
			and viewerretail.user_account = if('$fromuser'='',viewerretail.user_account,'$fromuser')
			and cashregister_name = if('$fromcashreg'='',cashregister_name,'$fromcashreg')
			Order by viewerretail.user_account,viewer_date,trans_date";
 
 
  $result=mysql_query($query,$connection) or die(mysql_error());
  $username='';
  while($row=mysql_fetch_row($result)){
 
	if($username!=$row[1]){
		echo "<tr><td><u><b>User: $row[1]</b><u></td></tr>";
		$viewerdate='';
	}
/*	if($viewerdate!=$row[12]){
			echo "<tr><td></tD><td colspan=3><u><b><font size=2>$row[12]</b></u></font></td></tr>";
		}*/
	
	echo "<tr><td></td>
						<td>$row[2]</td>
						<td>$row[3]</td>
						<td>$row[4]</td>
						<td align=right>". number_format($row[5],$DecimalBase)."</td>
						<td align=right>". number_format($row[8],$DecimalBase)."</td>
						<td align=right>". number_format($row[6],$DecimalBase)."</td>
						
					 
			</tr>";
	
	
	
   $username = $row[1];
   $viewerdate = $row[12];
  }
}
else if($groupby==1 or $groupby==3){
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>User:</b></td>
  <td width="12%">$dispfromuser</td>
  <td width="10%" valign='top'><b>CashRegister:</b></td>
  <td width="18%">$dispcashregister</td>
  <td width="6%" valign='top'><b>Type:</b></td>
  <td width="18%">$type</td>
  
</tr>
</table></td></tr>
<tr><td colspan=10><table class='params' width='800' border='0' align='left' style='table-layout:fixed' >
<col width=100>
<col width=100>
<col width=100>
<col width=150>
<col width=100>
<col width=100>
<col width=100>
<col width=100>

HERE;

 echo "	<tr height='1'><td colspan=12><h5></td></tr>";
 echo "<tr><td></td>
						<td align=center><b>CashRegister</td>
						<td align=center><b>Invoice No</td>
						<td align=center><b>Invoice Date</td>
						<td align=center><b>Gross Total</td>
						<td align=center><b>Discount</td>
						<td align=center><b>Net Total</td>
			</tr>";
 echo "	<tr height='5'><td colspan=12><h5></td></tr>";
	
 $query = " Select	date_format(sale_date,'%d/%m/%Y %H:%i:%s'), user_name, Null, sale_reference
			, date_format(sale_date,'%d/%m/%Y %H:%i:%s'), SALE_NETTOTAL, SALE_NETTOTAL, Sale_DiscountAmount
			, Sale_DiscountAmount, null, null,discount_user, date_format(sale_date,'%d/%m/%Y'),CashRegister_Name
			From retail
			Left Join ".$HTTP_SESSION_VARS['dbLabel'].".Users on retail.user_account = Users.User_Account
			where Date_Format(Retail.Sale_Date,'%Y-%m-%d') between Date_Format('".$fromdate."','%Y-%m-%d') and Date_Format('".$todate."','%Y-%m-%d') 
			and retail.user_account = if('$fromuser'='',retail.user_account,'$fromuser')
			";
	if($groupby==3) 		
		$query = $query." and exists(select 1 from retaildt where retaildt.sale_id=retail.sale_id and retaildt.saledt_quantity < 0)";
		
		
		
	$query = $query."	Order by retail.user_account,sale_date,sale_date";
 
  $result=mysql_query($query,$connection) or die(mysql_error());
  $username='';
  while($row=mysql_fetch_row($result)){
 
	if($username!=$row[1]){
		echo "<tr><td><u><b>User: $row[1]</b><u></td></tr>";
		$viewerdate='';
	}
/*	if($viewerdate!=$row[12]){
			echo "<tr><td></tD><td colspan=3><u><b><font size=2>$row[12]</b></u></font></td></tr>";
		}*/
	
	echo "<tr><td></td>
						<td>$row[13]</td>
						<td>$row[3]</td>
						<td>$row[4]</td>
						<td align=right>". number_format($row[5],$DecimalBase)."</td>
						<td align=right>". number_format($row[8],$DecimalBase)."</td>
						<td align=right>". number_format($row[6],$DecimalBase)."</td>
						
					 
			</tr>";
	
	
	
   $username = $row[1];
   $viewerdate = $row[12];
  }
 
}
 
 
 
 
 
 
 
 
 

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

?>