<?php



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


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


$HeaderTitle = 'Checks To Be Collected List';
$Title = 'Checks To Be Collected List';


include("config.inc.php");
include("function.inc.php");

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

//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2  from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}



	


$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);
$printeddate = $printdate[2].'/'.$printdate[1].'/'.$printdate[0];

$workbook = new Spreadsheet_Excel_Writer();

$workbook->send('ChecksCollection.xls');
$columnWidth = 20;
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'Center',
  'bold'=>1
  ));
  
$LabelFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'Center',
  'bold'=>1
  ));
  
  $DetailFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'Left',
  'bold'=>0
  ));  
  
  $DetailLabel =& $workbook->addFormat(array('size'=>10,
  'align'=>'Left',
  'bold'=>1
  ));

$x=1;
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('ChecksCollection');







$worksheet->setMerge($x, 0, $x, 1);
$worksheet->setMerge(0, 1, 0, 2);
$worksheet->setMerge($x, 2, $x, 3);
$worksheet->setMerge($x, 6, $x, 7);
$worksheet->setMerge($x, 8, $x, 9);
$worksheet->setMerge($x, 10, $x, 11);
$worksheet->setMerge($x, 12, $x, 13);
$worksheet->setMerge($x, 14,$x, 15);
$worksheet->setColumn(2,16,13);
$worksheet->setColumn(8,9,15);
$worksheet->write(0,7,'Checks Collection', $TitleFormat);
$worksheet->write(0,11,'Printed Date :', $TitleFormat);
$worksheet->write(0,12,$printeddate, $TitleFormat);


//$rownum=$rownum+1;
$rownum=2;
													 	 	

$worksheet->write($rownum,0,'Due Date',$LabelFormat);
$worksheet->write($rownum,1,'Ledger number',$LabelFormat);
$worksheet->write($rownum,2,'Ledger name',$LabelFormat);
$worksheet->write($rownum,3,'Manula no',$LabelFormat);
$worksheet->write($rownum,4,'Jv description',$LabelFormat);
$worksheet->write($rownum,5,'Bank',$LabelFormat);
$worksheet->write($rownum,6,'Amount',$LabelFormat);
$worksheet->write($rownum,7,'Cur.',$LabelFormat);
$worksheet->write($rownum,8,'Chk no.',$LabelFormat);
$worksheet->write($rownum,9,'Bank deposit',$LabelFormat);
$worksheet->write($rownum,10,'Bank deposit name',$LabelFormat);
$worksheet->write($rownum,11,'Status',$LabelFormat);
$worksheet->write($rownum,12,'Deposit due date',$LabelFormat);
$worksheet->write($rownum,13,'Comm. cur.',$LabelFormat);
$worksheet->write($rownum,14,'Comm. amt',$LabelFormat);
$worksheet->write($rownum,15,'Comm. Account',$LabelFormat);

	
	
	    if ($duedate=='depositduedate') {
        $query = "select 
		date_format(chk_valuedate,'%d/%m/%Y') as chk_valuedate, checkscollected.ledger_number, chk_bank, chk_amount, checkscollected.currency_code, chk_number, journaldt_id2, chk_bankdeposit, chk_status, date_format(chk_depositvaluedate,'%d/%m/%Y') as chk_depositvaluedate , journaldt_id1, chk_commission, chk_commissioncur, chk_commissionacc, checkcollected_posted, check_visible,  manual_no, journaldt_desc, trans_id, chk_date
		,ledger.ledger_name as chk_bankdepositname,l2.ledger_name client_name  from checkscollected  
         inner join ledger  on ledger.ledger_number=Chk_BankDeposit 
         inner join ledger l2 on l2.ledger_number= checkscollected.ledger_number 
         where   Chk_DepositValueDate >= '".displayParadoxDate($fromdate)."' and Chk_DepositValueDate <= '".displayParadoxDate($todate)."'
		 and ifnull(Chk_BankDeposit,-1) = if('$bank'='',ifnull(Chk_BankDeposit,-1),'$bank')
         order by Chk_BankDeposit,currency_code,chk_valueDate ";        
    } else {
        $query = "select 
		date_format(chk_valuedate,'%d/%m/%Y') as chk_valuedate, checkscollected.ledger_number, chk_bank, chk_amount, checkscollected.currency_code, chk_number, journaldt_id2, chk_bankdeposit, chk_status, date_format(chk_depositvaluedate,'%d/%m/%Y') as chk_depositvaluedate, journaldt_id1, chk_commission, chk_commissioncur, chk_commissionacc, checkcollected_posted, check_visible,  manual_no, journaldt_desc, trans_id, chk_date
		,l2.ledger_name as chk_bankdepositname,ledger.ledger_name as client_name from checkscollected 
         inner join ledger on ledger.ledger_number= checkscollected.ledger_number 
         left join ledger l2 on l2.ledger_number=Chk_BankDeposit 
         where ledger.ledger_number=checkscollected.ledger_number  
		 and Chk_ValueDate >= '".displayParadoxDate($fromdate)."' and Chk_ValueDate <= '".displayParadoxDate($todate)."'
		 and ifnull(Chk_bank,-1) = if('$bank'='',ifnull(Chk_bank,-1),'$bank')
         order by Chk_bank,checkscollected.currency_code,Chk_ValueDate ";
        
    }
	
	//echo $query;
	
	$result = mysql_query($query,$connection) or die(mysql_error());		

$vbank = '';
while($row=mysql_fetch_array($result)){
extract($row);

	if (($duedate=='duedate') and ($vbank!=$chk_bank) and ($vbank != '') ){
		$rownum=$rownum+1;
		$worksheet->write($rownum,2,'Total Group - '.$vbank,$DetailLabel);
		$worksheet->write($rownum,6,number_format($chkamt,$DecimalBase),$DetailLabel);
		$worksheet->write($rownum,7,$currency_code,$DetailLabel);
		$chkamt=0;
		}
	
	if (($duedate=='depositduedate') and ($vbank!=$chk_bankdeposit) and ($vbank != '') ){
		$rownum=$rownum+1;
		$worksheet->write($rownum,2,'Total Group - '.$chk_bankdepositname,$DetailLabel);
		$worksheet->write($rownum,3,number_format($chkamt,$DecimalBase),$DetailLabel);
		$worksheet->write($rownum,4,$currency_code,$DetailLabel);
		$chkamt=0;
		}
		
	if (($duedate=='depositduedate') and ($vbank!=$chk_bankdeposit)){
			
		$rownum=$rownum+1;
		$rownum=$rownum+1;
		$worksheet->write($rownum,0,'Bank',$DetailLabel);
		$worksheet->write($rownum,1,$chk_bankdeposit,$DetailLabel);
		$worksheet->write($rownum,2,$chk_bankdepositname,$DetailLabel);
		$worksheet->write($rownum,3,$currency_code,$DetailLabel);
	
	}
	else if (($duedate=='duedate') and ($vbank!=$chk_bank)){
		$rownum=$rownum+1;
		$rownum=$rownum+1;
		$worksheet->write($rownum,0,'Bank',$DetailLabel);
		$worksheet->write($rownum,1,$chk_bank,$DetailLabel);
	}

	
		$rownum=$rownum+1;
		$worksheet->write($rownum,0,$chk_valuedate,$DetailFormat);
		$worksheet->write($rownum,1,$ledger_number,$DetailFormat);
		$worksheet->write($rownum,2,$client_name,$DetailFormat);
		$worksheet->write($rownum,3,$manual_no,$DetailFormat);
		$worksheet->write($rownum,4,$journaldt_desc,$DetailFormat);
		$worksheet->write($rownum,5,$chk_bank,$DetailFormat);
		$worksheet->write($rownum,6,$chk_amount,$DetailFormat);
		$worksheet->write($rownum,7,$currency_code,$DetailFormat);
		$worksheet->write($rownum,8,$chk_number,$DetailFormat);
		$worksheet->write($rownum,9,$chk_bankdeposit,$DetailFormat);
		$worksheet->write($rownum,10,$chk_bankdepositname,$DetailFormat);
		$worksheet->write($rownum,11,$chk_status,$DetailFormat);
		$worksheet->write($rownum,12,$chk_depositvaluedate,$DetailFormat);
		$worksheet->write($rownum,13,$chk_commissioncur,$DetailFormat);
		$worksheet->write($rownum,14,$chk_commission,$DetailFormat);
		$worksheet->write($rownum,15,$chk_commissionacc,$DetailFormat);
		
		

		/*
if (($duedate=='depositduedate') and ($vbank!=$chk_bankdeposit) and ($vbank != '') ){
		$rownum=$rownum+1;
		$worksheet->write($rownum,2,'Total Group '.$chk_bankdeposit,$DetailLabel);
		}
*/


		
if ($duedate=='depositduedate') 
	$vbank=$chk_bankdeposit;
	
	if ($duedate=='duedate') 
	$vbank=$chk_bank;

	
	$chkamt = $chkamt + $chk_amount;

}
	
  $sql = " select sum(convertcurfunc(currency_code,'$base2',now(),0,0,null,null,chk_amount)) as totchkbase2, 
           sum(convertcurfunc(chk_commissioncur,'$base2',now(),0,0,null,null,chk_commission)) as totcombase2
           from checkscollected ";
		   
		if ($duedate=='depositduedate')   
			$sql = $sql ." where   ifnull(Chk_BankDeposit,-1) = if('$bank'='',ifnull(Chk_BankDeposit,-1),'$bank') and Chk_DepositValueDate >= '".displayParadoxDate($fromdate)."' and Chk_DepositValueDate <= '".displayParadoxDate($todate)."' ";
		else
			$sql = $sql ." where ifnull(Chk_bank,-1) = if('$bank'='',ifnull(Chk_bank,-1),'$bank') and Chk_ValueDate >= '".displayParadoxDate($fromdate)."' and Chk_ValueDate <= '".displayParadoxDate($todate)."' ";
		
	$result = mysql_query($sql,$connection) or die(mysql_error());		
		$row=mysql_fetch_array($result);
		$Totalchkamt = $row[0];
		$TotalComm = $row[1];
	
	
	if (($duedate=='duedate')  ){
		$rownum=$rownum+1;
		$worksheet->write($rownum,2,'Total Group - '.$vbank,$DetailLabel);
		$worksheet->write($rownum,6,number_format($chkamt,$DecimalBase),$DetailLabel);
		$worksheet->write($rownum,7,$currency_code,$DetailLabel);
		
		$rownum=$rownum+1;
		$worksheet->write($rownum,2,'Total Checks '.$vbank,$DetailLabel);
		$worksheet->write($rownum,6,number_format($Totalchkamt,$DecimalBase),$DetailLabel);
		$worksheet->write($rownum,7,$currency_code,$DetailLabel);		
		}
		
		
		if (($duedate=='depositduedate')  ){
		$rownum=$rownum+1;
		$worksheet->write($rownum,2,'Total Group - '.$chk_bankdepositname,$DetailLabel);
		$worksheet->write($rownum,3,number_format($chkamt,$DecimalBase),$DetailLabel);
		$worksheet->write($rownum,4,$currency_code,$DetailLabel);		
		
		$rownum=$rownum+1;
		$worksheet->write($rownum,3,'Total Checks ',$DetailLabel);
		$worksheet->write($rownum,6,number_format($Totalchkamt,$DecimalBase),$DetailLabel);
		$worksheet->write($rownum,7,$currency_code,$DetailLabel);		
		$worksheet->write($rownum,13,'Total Commission',$DetailLabel);		
		$worksheet->write($rownum,14,number_format($TotalComm,$DecimalBase),$DetailLabel);		
		$worksheet->write($rownum,15,$currency_code,$DetailLabel);		
	
		}

	
	
	
	
$workbook->close();	
	
	
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 format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 
 function insertDate($data){
	return substr($data, 6, 4) . substr($data, 3, 2) . substr($data, 0, 2);
}
function displayParadoxDate($data){
	
	$data = insertDate($data);
	return substr($data, 0, 4) ."-". substr($data, 4, 2) ."-".substr($data, 6, 2);
}




?>