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


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


include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.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,report_color1, report_color2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}

$queryModule = " Select ifNull(Module_Delivery,0) As Module_Delivery ".
			   " From module ";
$resultModule=mysql_query($queryModule,$connection) or die(mysql_error());
while($rowModule=mysql_fetch_array($resultModule)){
	extract($rowModule);
}

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

//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);

//Sending HTTP headers
$workbook->send('Exp_NoInvoiceClients.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of Clients With No Invoices');
$worksheet->setInputEncoding('windows-1256'); 
$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'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  )); 
  
$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
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));   

$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();

$regularrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');




$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromclient . "  To: ". $toclient ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project: ". $fromproject . " \n";
	
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($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);  
  
if (empty($fromdate))  $fromdate = "null";
else  $fromdate = "'".$fromdate." 00:00:00'"; 
if (empty($todate))  $todate = "null";
else  $todate = "'".$todate." 23:59:59 '";

if (empty($fromclient))  $fromclient = "null";
else  $fromclient = "'".$fromclient."'";    
if (empty($toclient))  $toclient = "null";
else  $toclient = "'".$toclient."'";       
  
if (empty($fromproject))  $fromproject = "null";
else  $fromproject = "'".$fromproject."'";  

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,"List of Clients With No Invoices", $TitleFormat);
               
$worksheet->write(1,8,"Printed Date:", $boldleftFormat);
$worksheet->write(1,9,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,8,"Time:", $boldleftFormat);		
$worksheet->write(2,9,$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    = 8;
$ColumnNum = 4;
			
$RowNum   += 1;
$ColumnNum = 0;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Client Code", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Client Name.", $boldcenterFormat);
$ColumnNum += 1;

if($fileval == '2')	{
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Net Total ".$Base1, $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Total Quantity ", $boldleftFormat);
}

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

if($Module_Delivery == 1){
	if ($fileval == '1')
		$query = " select ledger_number, ledger_name ".
				" from clients where ledger_number not in (select Client_code from sales where Sale_Date between $fromdate and $todate ) ".
				" and ledger_number Between ifNull(".$fromclient.", ledger_number) and ifNull(". $toclient.", ledger_number) ".
				" order by ledger_number ";
	else if($fileval == '2'){
		$query = "select sales.client_code, ledger_name, sum(Sale_TotalBase1) as totalb1, sum(SaleDt_Quantity) as totalQty
				from salesdt
				inner join sales on sales.sale_id = salesdt.sale_id
				inner join clients on clients.ledger_number = sales.client_code
				where Sale_Date between $fromdate and $todate and sales.client_code Between ifNull(".$fromclient.", sales.client_code) and ifNull(". $toclient.", sales.client_code) 
				and sales.Project_code = ifnull($fromproject,sales.Project_code)	
				group by sales.client_code 
				order by totalb1 desc  ";	
			if ($topnbr != '')
				$query .= "limit $topnbr";	
		}			
}
else if($Module_Delivery == 0){
	if ($fileval == '1')
		$query = " select ledger_number, ledger_name ".
				 " from clients where ledger_number not in (select Client_code from deliveries where Delivery_Date between $fromdate and $todate ) ".
				 " order by ledger_number ";
	else if($fileval == '2'){
		$query = " select deliveries.client_code, ledger_name, sum(Delivery_TotalBase1) as totalb1, sum(DeliveryDt_Quantity) as totalQty
				from deliverydt
				inner join deliveries on deliveries.delivery_id = deliverydt.delivery_id
				inner join clients on clients.ledger_number = deliveries.client_code
				where deliveries.Delivery_Date between $fromdate and $todate and deliveries.client_code Between ifNull(".$fromclient.", deliveries.client_code) and ifNull(". $toclient.", deliveries.client_code) 
				and deliveries.Project_code = ifnull($fromproject,deliveries.Project_code)
				group by deliveries.client_code 
				order by totalb1 desc ";	
			if ($topnbr != '')
				$query .= "limit $topnbr";
		}			 
}

$result=mysql_query($query,$connection) or die(mysql_error());

while($rows=mysql_fetch_row($result)){
	
	$worksheet->write($RowNum,$ColumnNum,$rows[0], $totalclass);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,$rows[1], $totalclass);
	$ColumnNum += 1;	
	if($fileval == '2')	{
		$worksheet->write($RowNum,$ColumnNum,number_format($rows[2],$DecimalBase1), $totalclass);
		$ColumnNum += 1;	
		$worksheet->write($RowNum,$ColumnNum,number_format($rows[3],$DecimalBase2), $totalclass);
		$ColumnNum += 1;	
	}
	$RowNum += 1;
	$ColumnNum=0;
}

			 
function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 

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

//Return Date in YYYY-MM-DD HH:MM:SS format. Supplied Format: DD/MM/YYYY
function insertMySQLDate($data){
	return substr($data, 6, 4) ."-". substr($data, 3, 2) ."-".substr($data, 0, 2)." ".strftime("%H").":".strftime("%M").":".strftime("%S");;
}
//Return Today's Date in DD/MM/YYYY format
function displayToday(){
	return strftime("%d")."/".strftime("%m")."/".strftime("%Y");
}


$workbook->close();

?>