<?php
header("Content-type: text/html; charset=utf-8");
require_once('Spreadsheet/Excel/Writer.php');

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


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

$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);
}


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


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

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

//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_ListofItemsWithStatus.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet($RepTitle);
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;

//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ));  
$regularcenterFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'center'  ));    
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));  
$regularcenterFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'center'  ));      
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  

$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
  
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));    
  
  
$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();

$dispfromgroup = $fromgroup;
$disptogroup = $togroup;
$dispfromsubgroup = $fromsubgroup;
$disptosubgroup = $tosubgroup;
  
$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($curr != '')   		  $SelectedOptions .= "-> Currency: ". $curr ."\n";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."\n";
if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."\n";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."\n";
if ($fromcategory != '')  $SelectedOptions .= "-> Category From: ". $fromcategory . "  To: ". $tocategory ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "  To: ". $towarehouse . "\n";
//if ($dispfromgroup != '' or $disptogroup != '') $SelectedOptions .= "-> Group From: ". $dispfromgroup . "  To: ". $disptogroup . "\n";


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

//$curr      		 = GetSQLValueString($curr, 'text');
$fromuser     	 = GetSQLValueString($fromuser, 'text');
$fromcashreg     = GetSQLValueString($fromcashreg, 'text');
$fromsalesman  	 = GetSQLValueString($fromsalesman, 'text');
$fromdriver  	 = GetSQLValueString($fromdriver, 'text');
$fromcustomer    = GetSQLValueString($fromcustomer, 'text');
$tocustomer 	 = GetSQLValueString($tocustomer, 'text');
$fromwarehouse   = GetSQLValueString($fromwarehouse, 'text');
$fromproject     = GetSQLValueString($fromproject, 'text');
$toproject       = GetSQLValueString($toproject, 'text');
$fromcostcent    = GetSQLValueString($fromcostcent, 'text');
$tocostcent      = GetSQLValueString($tocostcent, 'text');
$fromvendor      = GetSQLValueString($fromvendor, 'text');
$tovendor    	 = GetSQLValueString($tovendor, 'text');
$fromcredit		 = GetSQLValueString($fromcredit, 'text');
$tocredit  		 = GetSQLValueString($tocredit, 'text');
$fromdate	  	 = GetSQLValueString($fromdate, 'date');
$todate       	 = GetSQLValueString($todate, 'date');   
  
if (empty($showunpost))  $showunpost = 0;
if (empty($showpending)) $showpending = 0;
if (empty($custperitem)) $custperitem = 0;
 
$RepTitle = 'Daily Sales';
if ($showpending == 1) $RepTitle = 'Pending Daily Sales';
   

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,$RepTitle, $TitleFormat);
               
$worksheet->write(1,6,"Printed Date:", $boldleftFormat);
$worksheet->write(1,7,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,6,"Time:", $boldleftFormat);		
$worksheet->write(2,7,$dt[1]." ".$dt[2], $regularleftFormat);

//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 6);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);

$RowNum    = 10;
$ColumnNum = 0;	

$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"REF.", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 25);
$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 35);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 25);
$worksheet->write($RowNum,$ColumnNum,"Status", $boldcenterFormat);
$ColumnNum += 1;
	


	

$query = '';

$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) Q5  ";
					
					
$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;

$RowNum = $RowNum + 1;
$ColumnNum = 0;

$result = mysql_query($query, $connection);
while ($rw = mysql_fetch_array($result)){	
	
	$worksheet->write($RowNum, $ColumnNum, $rw[0], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[1], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[2], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[3], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[4], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[5], $regularleftFormat);
	$ColumnNum += 1;
	$RowNum = $RowNum + 1;
	$ColumnNum = 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;
}


function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}


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


$workbook->close();

?>