<?php

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

include("config.inc.php");

require_once('Spreadsheet/Excel/Writer.php');

set_time_limit(0);

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


$query_login="select login_belongstogrp from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlogin=mysql_query($query_login,$connection) or die(mysql_error());

$row_login=mysql_fetch_row($resultlogin);
$belongstogroup=$row_login[0];
if (empty($belongstogroup)) $belongstogroup = 0;

$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('Form_ClientExcel.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of Clients');
 $worksheet->setInputEncoding('utf-8');
$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'  ));  
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  '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
  ));    
     	

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

  
$RowNum    = 4;
$ColumnNum = 0;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Client Code", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Name", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Financial #", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Salesman Name", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Area", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"State", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Country", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Address 1", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Address 2", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Address 3", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Telephone 1", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Telephone 2", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Telephone 3", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Fax", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Selling Price", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Vatable", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Currency", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Credit Limit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Payment Terms", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Project", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"CostCenter", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"City", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Email", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Home Page", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Profit Center", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Creation Date", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Balance ".$Base2, $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Last Trans. Date", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 50);
$worksheet->write($RowNum,$ColumnNum,"Last Trans. Desc", $boldcenterFormat);

	

	
$query_clients=" Select Clients.Ledger_number as ClientCode, Clients.ledger_name as ClientName, clients.client_financialno as financialno, salesmen.ledger_name as SalesmanName, 
				 Clients.Area_Code, Client_City, Client_State, Client_Country, Client_Address, Client_Address2, Client_Address3, 
				 Client_Telephone1, Client_Telephone2, Client_DlvFax as Client_Telephone3, Client_Fax, Client_Email, Client_homepage, 
				 Clients.Currency_Code as CurrencyCode, Client_PaymentTerms, Client_CreditLimit, ProfitCenter_Code,
				 Clients.Project_Code as ProjectCode, Clients.Costcenter_code as CostcentCode, Client_SellingPrice, 
				 if(ifnull(Client_VAT,0)=1, 'YES', 'NO') as ClientVatable, date_format(Client_CreationDate, '%d/%m/%Y') as Client_CreationDate
				 From Clients 
				 Left Join Salesmen on Salesmen.Ledger_Number = Clients.Salesmen_Code
				 Order By Clients.ledger_number";
	
//echo$query_clients;
	
$result_clients=mysql_query($query_clients,$connection) or die(mysql_error());
$totalrecords=mysql_num_rows($result_clients);

$RowNum    = 5;
$ColumnNum = 0;

while($row=mysql_fetch_array($result_clients)){
	extract($row);
	
	//Get Client Balance
	$queryBalance = "SELECT IFNULL(GetClientBalance('".$ClientCode."',now(),'".$CostcentCode."','".$Base2."','".$Base1."','".$Base2."',1) ,0) ";
	$resultBalance=mysql_query($queryBalance,$connection) or die(mysql_error());
	$rowBalance=mysql_fetch_array($resultBalance);
	$ClientCurrBalance = $rowBalance[0];
	//Get Client Last Transaction
	$queryTrans = "SELECT date_format(Journal_date,'%d/%m/%Y'), Journaldt_desc From JournalDt Where Ledger_Number = '".$ClientCode."' Order By Journal_date desc Limit 1";
	$resultTrans=mysql_query($queryTrans,$connection) or die(mysql_error());
	$rowTrans=mysql_fetch_array($resultTrans);
	$LastTransDate = $rowTrans[0];
	$LastTransDesc = $rowTrans[1];
	
	$worksheet->write($RowNum, $ColumnNum, $ClientCode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ClientName, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $financialno, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $SalesmanName, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Area_Code, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_State, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Country, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Address, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Address2, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Address3, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Telephone1, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Telephone2, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Telephone3, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Fax, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_SellingPrice, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ClientVatable, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $CurrencyCode, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($Client_CreditLimit, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_PaymentTerms, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ProjectCode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $CostcentCode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_City, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_Email, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_homepage, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ProfitCenter_Code, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Client_CreationDate, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($ClientCurrBalance, $DecimalBase2), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LastTransDate, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LastTransDesc, $regularleftFormat);
	$ColumnNum += 1;
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;	
}

$RowNum += 1;
$ColumnNum = 0;	
	
$worksheet->write($RowNum, $ColumnNum, "Total Records: ".$totalrecords, $regularleftFormat);
$ColumnNum += 1;





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

$workbook->close();

?>