<?php

include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.php');


session_start();
if($HTTP_SESSION_VARS['dbName']==""){
header("location:../index.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");

$ShPmPtCl = $_GET['ShowPmPtCl'];
$ShwChkJourDt=$_GET['ShowChkJourDt'];

if($cashtype != '' && $chk == 1){
	$query = "select CashType_Description from cashtype where CashType_Code= '$cashtype' ";
	if ($VoucherType == '1')  $query = $query." and Journal_Type='RV' ";
	else if ($VoucherType == '2')  $query = $query." and Journal_Type='PV' ";
	$res = mysql_query($query,$connection);
	$rw = mysql_fetch_row($res);
	
	
	$Title = "List of ".$rw[0];

} else {
	
if ($VoucherType == '1') 
$Title = 'List of Receipts';

if ($VoucherType == '2') 
$Title = 'List of Payments';

if ($VoucherType == '3') 
$Title = 'List of Debit Notes';

if ($VoucherType == '4') 
$Title = 'List of Credit Notes';
}
	 
	 //echo $Title;
	 



	 //echo $Title;
$fromproject   = GetSQLValueString($fromproject, 'text');
$toproject     = GetSQLValueString($toproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$tocostcent    = GetSQLValueString($tocostcent, 'text');



//maroun 30.03.2011
$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 DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Company_Name As CompanyName, ".
             " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}
	//User Access
$querylog=" select ifNull(login_belongstogrp,0) As FI_BelongsToGrp 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,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter ".
          " 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);



$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_ListOfReceipt.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Export');
$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
$regularFormat =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
// Regular Format
$squareborderFormat =& $workbook->addFormat(array('size'=>9,
  'top'=>1,  
  'bottom'=>1,
  'right'=>1,
  'left'=>1,
  ));  
  
$lrBorderFormat =& $workbook->addFormat(array('size'=>9,
  'right'=>1,
  'left'=>1,
  ));  
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'bold'=>1
  ));  
	
$worksheet->setColumn (4, 4, 11);	
$worksheet->setColumn (5, 5, 32);	
     
                $worksheet->write(1,0,$CompanyName, $TitleFormat);
				$worksheet->write(1,5,"$Title", $TitleFormat);
                $worksheet->write(1,11,"Printed Date:", $boldFormat);
                $worksheet->write(1,13,$printdate[2]."/".$printdate[1]."/".$printdate[0], $boldFormat);
				$worksheet->write(2,11,"Time:", $boldFormat);		
				$worksheet->write(2,13,$dt[1]." ".$dt[2], $boldFormat);

	
	
	if ($fromref=='')
		$fromref = 'null';
	if ($toref=='')
		$toref = 'null';
		
	if ($fromledger=='') $fromledger = 'null';
	else $fromledger = "'".$fromledger."'"; 
	if ($toledger=='')	$toledger = 'null';
	else $toledger = "'".$toledger."'"; 
	
	if ($fromsalesman=='') $fromsalesman = 'null';
	else $fromsalesman = "'".$fromsalesman."'"; 
	if ($tosalesman=='') $tosalesman = 'null';
	else $tosalesman = "'".$tosalesman."'"; 
		
	if (!empty($fromdate))
	   $fromdate = explodeword($fromdate);
	if (!empty($todate))
	   $todate = explodeword($todate);  

	if (empty($fromdate))   
	   $fromdate = 'null';
	else
	   $fromdate = "'".$fromdate."'"; 
	   
	if (empty($todate))   
	   $todate = 'null';
	else
	   $todate = "'".$todate."'";	
	   

	$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
						" 			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
						"              and (project_code is not null and project_code != '') ".
						"			 ) QP  ";
						
	$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
						"			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
						"              and (costcent_code is not null and costcent_code != '') ".
						"			 ) QC  "; 
	   
	 
	$query = " select  Date_Format(Trans.Trans_Date,'%d/%m/%Y'), Date_Format(TransDt_ValueDate,'%d/%m/%Y'), Trans_Reference,  ".
             " Trans_ManualNo, TransDt.Ledger_number, ledger.ledger_name, TransDt_Description, TransDt.Currency_code, Transdt_Amount, ".
			 " journal.Journal_refgovernment, TransDt_dbcd, TransDt.TransDt_id";
			
	if ($varGrpBy == 2) 
		$query = $query." , salesmen.ledger_number as salesmencode, salesmen.ledger_name as salesmenname,  ";
	else
		$query = $query." , null as salesmencode, null as salesmenname,  ";
		
	$query = $query." Trans.Project_Code, Trans.Costcent_Code  ";
	$query = $query." from Trans ".
					" left join journal on journal.journal_id=Trans.journal_id ".
					" inner join TransDt on TransDt.Trans_id=Trans.Trans_id  ".
					" inner join ledger on ledger.ledger_number=TransDt.ledger_number ";
			 
	if ($varGrpBy == 2) {
		if($VoucherType == 1)
			$query .= " inner join clients on clients.ledger_number = ledger.ledger_number ";	 
		$query = $query." left join salesmen on clients.salesmen_code = salesmen.ledger_number ";
	}					
	
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query = $query.$QueryRestrictPrj." on Trans.project_code like concat(QP.project_code,'%') ";
											 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query = $query.$QueryRestrictCst." on Trans.costcent_code like concat(QC.costcent_code,'%') ";
		
	$query = $query." where ( ($option = 1 and date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) ) ".
					" 		or ($option = 2 and date_format(TransDt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) )".
					" 		or ($option = 3 and Trans_Reference between ifnull($fromref,0) and ifnull($toref,9999999999999) )) ".
					" and (($VoucherType = 1 and Trans.Trans_Type = 'RV') or ($VoucherType = 2 and Trans.Trans_Type = 'PV') or ($VoucherType = 3 and Trans.Trans_Type = 'DV') or ($VoucherType = 4 and Trans.Trans_Type = 'CV')) ".
					" and (($dbcdType = 1 and  TransDt_dbcd='C') or ($dbcdType = 2 and  TransDt_dbcd='D') or ($dbcdType = 3 and 1 = 1)) ".
					" and (ledger.ledger_number between ifnull(".$fromledger.",'0') and ifnull(".$toledger.",'9999999999999')) ";
if($projectper == 1)
	$query = $query." and ifNull(Trans.Project_Code,'-1') like ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) ";	
else
	$query = $query." and ifNull(Trans.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Trans.Project_Code,'-1')) ";
if($costcentper == 1)	
	$query = $query." and ifNull(Trans.COSTCENT_CODE,'-1') like ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) ";
else	
	$query = $query." and ifNull(Trans.COSTCENT_CODE,'-1') between ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) and ifNull(". $tocostcent." ,ifNull(Trans.COSTCENT_CODE,'-1'))  ";
	
	$query = $query." And ((($varJournal = 2 and Trans.journal_id <>0) or (Trans.journal_id is not null) )or ($varJournal = 3 and (Trans.journal_id = 0 ) or  (Trans.journal_id is null) )) ";
	
	if ($varGrpBy == 2) {
		$query = $query." and ifNull(salesmen.ledger_number,'-1') between ifNull(".$fromsalesman.",ifNull(salesmen.ledger_number,'-1')) and ifNull(". $tosalesman.",ifNull(salesmen.ledger_number,'-1')) ";
	}
	if($cashtype != '' && $chk == 1)		$query = $query . " and  Trans.CashType_Code='$cashtype' ";
	
	$query = $query." order by if($varGrpBy=3, Transdt.Ledger_number, if($varGrpBy=2, salesmencode, Null)), if($varGrpBy=2, TransDt.Ledger_number, Null ), Trans.Trans_Date, Trans_Reference, if($varGrpBy=1, TransDt.Ledger_number, Null ) ";
	
	
	//echo $query;
	
	$worksheet->write(4,0,"Date", $boldFormat); 		
	$worksheet->write(4,1,"Value Date", $boldFormat);
    $worksheet->write(4,2,"Reference", $boldFormat); 		
	$worksheet->write(4,3,"Manual No.", $boldFormat); 
if ($varGrpBy == 3)
    $worksheet->write(4,4,"", $boldFormat);
else 	
    $worksheet->write(4,4,"Ledger Number", $boldFormat); 		

	$worksheet->write(4,5,"Ledger Name/Description", $boldFormat);
	if($ShPmPtCl == 1 && $VoucherType == 1 || $ShPmPtCl == 1 && $VoucherType == 2){
		$worksheet->write(4,6,"PM.", $boldFormat); 
		$worksheet->write(4,7,"PT.", $boldFormat); 
		$worksheet->write(4,8,"CL.", $boldFormat); 
		$worksheet->write(4,9,"Cur.", $boldFormat); 		
		$worksheet->write(4,10,"Amount", $boldFormat); 	 
		$worksheet->write(4,11,"JV Ref.", $boldFormat); 		
		
		if ($varProjectHeader == 1) {
			$worksheet->write(4,12,"Project", $boldFormat); 
			$worksheet->write(4,13,"Costcenter", $boldFormat); 
		}
	}
	else{
		$worksheet->write(4,6,"Cur.", $boldFormat); 		
		$worksheet->write(4,7,"Amount", $boldFormat); 	 
		$worksheet->write(4,8,"JV Ref.", $boldFormat); 		
		
		if ($varProjectHeader == 1) {
			$worksheet->write(4,9,"Project", $boldFormat); 
			$worksheet->write(4,10,"Costcenter", $boldFormat); 
		}
	}

$t_LedgerNumber='';
$t_SalesmenCode='';
$rownum=5;
	
$result=mysql_query($query,$connection) or die(mysql_error());

while($rows=mysql_fetch_row($result)){

	if ($varGrpBy == 3) {
		if ($t_LedgerNumber != $rows[4] && $t_LedgerNumber != ''){
			$rownum = $rownum + 1;
			$worksheet->write($rownum,0,"Currency",$boldFormat);
			$worksheet->write($rownum,2,"Sum of Amount",$boldFormat);
			
			
			$queryTotal= " select transdt.currency_code, sum(transdt_amount)".
						 " from Trans ".
						 " left join journal on journal.journal_id=Trans.journal_id ".
						 " inner join TransDt on TransDt.Trans_id=Trans.Trans_id  ".
						 " inner join ledger on ledger.ledger_number=TransDt.ledger_number ";
						 
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$queryTotal = $queryTotal.$QueryRestrictPrj." on Trans.project_code like concat(QP.project_code,'%') ";
													 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$queryTotal = $queryTotal.$QueryRestrictCst." on Trans.costcent_code like concat(QC.costcent_code,'%') ";
		
			$queryTotal = $queryTotal.
						 " where ( ($option = 1 and date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) ) ".
						 " 		or ($option = 2 and date_format(TransDt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) )".
						 " 		or ($option = 3 and Trans_Reference between ifnull($fromref,0) and ifnull($toref,9999999999999) )) ".
						 " and (($VoucherType = 1 and Trans.Trans_Type = 'RV') or ($VoucherType = 2 and Trans.Trans_Type = 'PV') or ($VoucherType = 3 and Trans.Trans_Type = 'DV') or ($VoucherType = 4 and Trans.Trans_Type = 'CV')) ".
						 " and (($dbcdType = 1 and  TransDt_dbcd='C') or ($dbcdType = 2 and  TransDt_dbcd='D') or ($dbcdType = 3 and 1 = 1)) ";
						 
				if($projectper == 1)
	$queryTotal = $queryTotal." and ifNull(Trans.Project_Code,'-1') like ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) ";
else	
	$queryTotal = $queryTotal." and ifNull(Trans.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Trans.Project_Code,'-1')) ";
if($costcentper == 1)
	$queryTotal = $queryTotal." and ifNull(Trans.COSTCENT_CODE,'-1') like ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) ";
else 	
	$queryTotal = $queryTotal." and ifNull(Trans.COSTCENT_CODE,'-1') between ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) and ifNull(". $tocostcent.",ifNull(Trans.COSTCENT_CODE,'-1'))  ";
	$queryTotal = $queryTotal." and ledger.ledger_number = '".$t_LedgerNumber."' ".
						 " group by currency_code order by currency_code";


					//echo $queryTotal;exit;
			$queryTotal=mysql_query($queryTotal,$connection) or die(mysql_error());
			while($rowsTotal=mysql_fetch_row($queryTotal)){
				$rownum = $rownum + 1;

				$worksheet->write($rownum,0,"$rowsTotal[0]",$regularFormat);
				$worksheet->write($rownum,2,number_format($rowsTotal[1],2),$regularFormat);
			}
			
			$rownum = $rownum + 2;
		}
				
		if ($t_LedgerNumber != $rows[4] || $t_LedgerNumber == ''){
			$worksheet->write($rownum,0,"Ledger Number", $boldFormat); 
			$worksheet->write($rownum,1,"$rows[4]", $regularFormat); 
			$worksheet->write($rownum,2,"$rows[5]", $regularFormat); 
			
			
			$t_LedgerNumber=$rows[4];
		}
		
		$rownum = $rownum + 1;	
	}

	if ($varGrpBy == 2) {
		if ($t_SalesmenCode != $rows[12] && $t_SalesmenCode != ''){
			$rownum = $rownum + 1;
			$worksheet->write($rownum,0,"Currency",$boldFormat);
			$worksheet->write($rownum,2,"Sum of Amount",$boldFormat);
			
			
			$queryTotal= " select transdt.currency_code, sum(transdt_amount)".
						 " from Trans ".
						 " left join journal on journal.journal_id=Trans.journal_id ".
						 " inner join TransDt on TransDt.Trans_id=Trans.Trans_id  ".
						 " inner join ledger on ledger.ledger_number=TransDt.ledger_number ".
						 " inner join clients on clients.ledger_number = ledger.ledger_number ".
						 " left join salesmen on clients.salesmen_code = salesmen.ledger_number ";
						 
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$queryTotal = $queryTotal.$QueryRestrictPrj." on Trans.project_code like concat(QP.project_code,'%') ";
													 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$queryTotal = $queryTotal.$QueryRestrictCst." on Trans.costcent_code like concat(QC.costcent_code,'%') ";
				
			$queryTotal = $queryTotal.
						 " where ( ($option = 1 and date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) ) ".
						 " 		or ($option = 2 and date_format(TransDt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) )".
						 " 		or ( $option = 3 and Trans_Reference between ifnull($fromref,0) and ifnull($toref,9999999999999) )) ".
						 " and (($VoucherType = 1 and Trans.Trans_Type = 'RV') or ($VoucherType = 2 and Trans.Trans_Type = 'PV') or ($VoucherType = 3 and Trans.Trans_Type = 'DV') or ($VoucherType = 4 and Trans.Trans_Type = 'CV')) ".
						 " and (($dbcdType = 1 and  TransDt_dbcd='C') or ($dbcdType = 2 and  TransDt_dbcd='D') or ($dbcdType = 3 and 1 = 1)) ";
						 
	if($projectper == 1)
	$queryTotal = $queryTotal." and ifNull(Trans.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) ";
else
	$queryTotal = $queryTotal." and ifNull(Trans.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Trans.Project_Code,'-1')) ";
if($costcentper == 1)	
	$queryTotal = $queryTotal." and ifNull(Trans.COSTCENT_CODE,'-1') between ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) ";
else
	$queryTotal = $queryTotal." and ifNull(Trans.COSTCENT_CODE,'-1') between ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) and ifNull(". $tocostcent.",ifNull(Trans.COSTCENT_CODE,'-1'))  ";
	$queryTotal = $queryTotal." and (ledger.ledger_number between ifnull(".$fromledger.",'0') and ifnull(".$toledger.",'9999999999999')) ".
						 " and salesmen.ledger_number = '".$t_SalesmenCode."' ".
						 " group by currency_code order by currency_code";

					//echo $queryTotal;exit;
			$queryTotal=mysql_query($queryTotal,$connection) or die(mysql_error());
			while($rowsTotal=mysql_fetch_row($queryTotal)){
				$rownum = $rownum + 1;

				$worksheet->write($rownum,0,"$rowsTotal[0]",$regularFormat);
				$worksheet->write($rownum,2,number_format($rowsTotal[1],2),$regularFormat);
			}
			
			$rownum = $rownum + 2;
		}
		
		if ($t_SalesmenCode != $rows[12] || $t_SalesmenCode == ''){
			$worksheet->write($rownum,0,"Salesman", $boldFormat); 
			$worksheet->write($rownum,1,"$rows[12]",$regularFormat); 
			$worksheet->write($rownum,2,"$rows[13]",$regularFormat); 

			$t_SalesmenCode=$rows[12];
		}
		
		$rownum = $rownum + 1;
	}

		
		
	$worksheet->write($rownum,0,"$rows[0]",$regularFormat); 
	$worksheet->write($rownum,1,"$rows[1]",$regularFormat); 
	$worksheet->write($rownum,2,"$rows[2]",$regularFormat);
	$worksheet->write($rownum,3,"$rows[3]",$regularFormat);


	if ($varGrpBy == 3) 
		$worksheet->write($rownum,4,"",$regularFormat);
	else {	
		$worksheet->write($rownum,4,"$rows[4]",$regularFormat);
		$worksheet->write($rownum,5,"$rows[5]",$regularFormat);
	}	


	if ($varGrpBy == 3) {
		
		$worksheet->write($rownum,5,"$rows[6]",$regularFormat); 
		$worksheet->write($rownum,6,"$rows[7]",$regularFormat); 
		$worksheet->write($rownum,7,number_format($rows[8],2),$regularFormat);
		$worksheet->write($rownum,8,"$rows[9]",$regularFormat);
		
	} 
	else {
		if($ShPmPtCl == 1){
			if($VoucherType==1){
				if(substr($rows[4],0,4)==4111){
					$query_ShPmPtCl = "select client_CreditLimit, client_PaymentTerms, client_PaymentMode, clients.CURRENCY_CODE from clients inner join ledger on clients.ledger_number = ledger.ledger_number where clients.ledger_number = '$rows[4]'";
					$result_ShPmPtCl = mysql_query($query_ShPmPtCl, $connection) or die(mysql_error());
					$rows_ShPmPtCl = mysql_fetch_row($result_ShPmPtCl);
				}
				else if(substr($rows[4],0,4)==5111){
					$sub_query_ndl = "select ClientFirstDigit, NbrOfDigitInLedger-length(Checkaccount)+1 as ndl1,NbrOfDigitInLedger-length(Checkaccount) as ndl from setup";
					//echo $sub_query_ndl;
					$result_ndl = mysql_query($sub_query_ndl, $connection) or die(mysql_error());
					$rows_ndl = mysql_fetch_row($result_ndl);
					$Checkaccount = $rows_ndl[0].substr($rows[4],4,8);
					$query_ShPmPtCl = "select client_CreditLimit, client_PaymentTerms, Client_PaymentMode, clients.CURRENCY_CODE from clients inner join ledger on clients.ledger_number = ledger.ledger_number where clients.ledger_number = '$Checkaccount'";
					//echo $query_ShPmPtCl;// exit;
					$result_ShPmPtCl = mysql_query($query_ShPmPtCl, $connection) or die(mysql_error());
					$rows_ShPmPtCl = mysql_fetch_row($result_ShPmPtCl);
				}
				
			}
			if(substr($rows[4],0,4)==5300){
				$rows_ShPmPtCl[0] = '';
				$rows_ShPmPtCl[1] = '';
				$rows_ShPmPtCl[2] = '';
				$rows_ShPmPtCl[3] = '';
			}
			$worksheet->write($rownum,6,"$rows_ShPmPtCl[2]",$regularFormat); //HEREEEEEEEEEEEEE
			$worksheet->write($rownum,7,"$rows_ShPmPtCl[1]",$regularFormat);
			$worksheet->write($rownum,8,number_format($rows_ShPmPtCl[0],2)." ".$rows_ShPmPtCl[3],$regularFormat);
			$worksheet->write($rownum,9,"$rows[7]",$regularFormat);
			$worksheet->write($rownum,10,number_format($rows[8],2),$regularFormat);
			$worksheet->write($rownum,11,"$rows[9]",$regularFormat);
		}else{
			$worksheet->write($rownum,6,"$rows[7]",$regularFormat);
			$worksheet->write($rownum,7,number_format($rows[8],2),$regularFormat);
			$worksheet->write($rownum,8,"$rows[9]",$regularFormat);
		}
	}

	if ($varProjectHeader == 1) {
		if($ShPmPtCl == 1 && $VoucherType == 1 || $ShPmPtCl == 1 && $VoucherType == 2){
			$worksheet->write($rownum,12," $rows[22]",$regularFormat);
			$worksheet->write($rownum,13," $rows[23]",$regularFormat);
		}
		else{
			$worksheet->write($rownum,9," $rows[18]",$regularFormat);
			$worksheet->write($rownum,10," $rows[19]",$regularFormat);
		}
	}
	
	//$rownum = $rownum + 1;

	if ($varDesc ==1 & $varGrpBy != 3){
		$rownum=$rownum+1;
		$worksheet->write($rownum ,1,"$rows[6]",$regularFormat);
	}

	   
	if ($varProjectHeader ==1) 
	{
		$sub_query = 	" select cstjourdt.project_code,cstjourdt.costcenter_code,CstjourDt_Per, ".
						" round(CstJourDt_Amount1,".$DecimalBase1.")".
						" from cstjourdt where cstjourdt.TransDt_id = '".$rows[11]."'";
		//echo $sub_query ;exit;				 
		$sub_result=mysql_query($sub_query,$connection) or die(mysql_error());			 
		$sub_RowCount = mysql_num_rows($sub_result);
		if (empty($sub_RowCount)) $sub_RowCount = 0;
			
		if ($sub_RowCount > 0) {
			$rownum = $rownum + 1;
			$worksheet->write($rownum,4,"Project",$boldFormat);
			$worksheet->write($rownum,5,"Costcenter",$boldFormat);
			$worksheet->write($rownum,6,"Percentage",$boldFormat);
			$worksheet->write($rownum,7,"Amount",$boldFormat);
			
				
			while($sub_rows=mysql_fetch_row($sub_result)){
				$rownum = $rownum + 1;
				
				$worksheet->write($rownum,4,"$sub_rows[0]",$regularFormat);
				$worksheet->write($rownum,5,"$sub_rows[1]",$regularFormat);
				$worksheet->write($rownum,6,"$sub_rows[2]%",$regularFormat);
				$worksheet->write($rownum,7,number_format($sub_rows[3], $DecimalBase1),$regularFormat);
			}
		
		}
			
	}  
	if($VoucherType==1){
	  	if ($ShwChkJourDt == 1){
		
			if($dbcdType==1)
				$transdt_id_chk = $rows[11]+1;
			else 
				$transdt_id_chk = $rows[11];
			$query_chkjourdt="select chkjourdt.Chk_number, chkjourdt.Chk_bank, date_format(chkjourdt.Chk_ValueDate, '%d/%m/%Y'), chkjourdt.Currency_Code, 
								chkjourdt.Chk_Amount, chkjourdt.Chk_Amount1, chkjourdt.Chk_Amount2, chkjourdt.Chk_Type 
								from chkjourdt where transdt_id = '$transdt_id_chk'"; //echo $query_chkjourdt;echo "<br>";
			$result_chkjourdt=mysql_query($query_chkjourdt,$connection);
			if(mysql_num_rows($result_chkjourdt)!=0){
				$rownum = $rownum + 1;
				$worksheet->write($rownum,4,"Check No.",$boldFormat);
				$worksheet->write($rownum,5,"Bank",$boldFormat);
				$worksheet->write($rownum,6,"Value Date",$boldFormat);
				$worksheet->write($rownum,8,"Amount",$boldFormat);
			}
			while($row_chkjourdt = mysql_fetch_array($result_chkjourdt)){
				$rownum = $rownum + 1;
				$worksheet->write($rownum,4,"$row_chkjourdt[0]",$regularFormat);
				$worksheet->write($rownum,5,"$row_chkjourdt[1]",$regularFormat);
				$worksheet->write($rownum,6,"$row_chkjourdt[2]",$regularFormat);
				$worksheet->write($rownum,8,number_format($row_chkjourdt[4],2)." ".$row_chkjourdt[3],$regularFormat);
			}
		}
		
	}else{
		if($VoucherType==2){
			if($ShwChkJourDt==1){
				if($dbcdType==2)
					$transdt_id_chk = $rows[11]-1;
				else 
					$transdt_id_chk = $rows[11];
				$query_chkjourdt="select chkjourdt.Chk_number, chkjourdt.Chk_bank, chkjourdt.Chk_ValueDate, chkjourdt.Currency_Code, 
									chkjourdt.Chk_Amount, chkjourdt.Chk_Amount1, chkjourdt.Chk_Amount2, chkjourdt.Chk_Type 
									from chkjourdt where transdt_id = '".$transdt_id_chk."'";
				$result_chkjourdt=mysql_query($query_chkjourdt,$connection);
				if(mysql_num_rows($result_chkjourdt)!=0){
					$rownum = $rownum + 1;
					$worksheet->write($rownum,4,"Check No.",$boldFormat);
					$worksheet->write($rownum,5,"Bank",$boldFormat);
					$worksheet->write($rownum,6,"Value Date",$boldFormat);
					$worksheet->write($rownum,8,"Amount",$boldFormat);
				}
				while($row_chkjourdt = mysql_fetch_array($result_chkjourdt)){
					$rownum = $rownum + 1;
					$worksheet->write($rownum,4,"$row_chkjourdt[0]",$regularFormat);
					$worksheet->write($rownum,5,"$row_chkjourdt[1]",$regularFormat);
					$worksheet->write($rownum,6,"$row_chkjourdt[2]",$regularFormat);
					$worksheet->write($rownum,8,number_format($row_chkjourdt[4],2)." ".$row_chkjourdt[3],$regularFormat);
				}
			}	
		
		} 
	}
	$rownum = $rownum + 1;
}

$rownum = $rownum + 1;
$worksheet->write($rownum,0,"Total of Currencies",$boldFormat);

$rownum = $rownum + 1;
$worksheet->write($rownum,0,"Currency",$boldFormat);
$worksheet->write($rownum,3,"Sum of Amount",$boldFormat);


$queryTotal= " select  transdt.currency_code, sum(transdt_amount)".
			 " from Trans ".
			 " left join journal on journal.journal_id=Trans.journal_id ".
			 " inner join TransDt on TransDt.Trans_id=Trans.Trans_id  ".
			 " inner join ledger on ledger.ledger_number=TransDt.ledger_number ";
			 
if ($varGrpBy == 2) {		 
	$queryTotal = $queryTotal." inner join clients on clients.ledger_number = ledger.ledger_number ".
							  " left join salesmen on clients.salesmen_code = salesmen.ledger_number ";
}				 

if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$queryTotal = $queryTotal.$QueryRestrictPrj." on Trans.project_code like concat(QP.project_code,'%') ";
													 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$queryTotal = $queryTotal.$QueryRestrictCst." on Trans.costcent_code like concat(QC.costcent_code,'%') ";

$queryTotal = $queryTotal." where ( ($option = 1 and date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) ) ".
						  " or ($option = 2 and date_format(TransDt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(TransDt_ValueDate,'%Y-%m-%d')) )".
						  " or ( $option = 3 and Trans_Reference between ifnull($fromref,0) and ifnull($toref,9999999999999) )) ".
						  " and (($VoucherType = 1 and Trans.Trans_Type = 'RV') or ($VoucherType = 2 and Trans.Trans_Type = 'PV') or ($VoucherType = 3 and Trans.Trans_Type = 'DV') or ($VoucherType = 4 and Trans.Trans_Type = 'CV')) ".
						  " and (($dbcdType = 1 and  TransDt_dbcd='C') or ($dbcdType = 2 and  TransDt_dbcd='D') or ($dbcdType = 3 and 1 = 1)) ";
						  
	if($projectper == 1)
	$queryTotal = $queryTotal." and ifNull(Trans.Project_Code,'-1') like ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) ";
else	
	$queryTotal = $queryTotal." and ifNull(Trans.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Trans.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Trans.Project_Code,'-1')) ";
if($costcentper == 1)
	$queryTotal = $queryTotal." and ifNull(Trans.COSTCENT_CODE,'-1') between ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) ";
else
	$queryTotal = $queryTotal." and ifNull(Trans.COSTCENT_CODE,'-1') between ifNull(".$fromcostcent.",ifNull(Trans.COSTCENT_CODE,'-1')) and ifNull( ". $tocostcent.",ifNull(Trans.COSTCENT_CODE,'-1'))  ";
$queryTotal = $queryTotal." and (ledger.ledger_number between ifnull(".$fromledger.",'0') and ifnull(".$toledger.",'9999999999999')) ";
						  						  
if ($varGrpBy == 2) {
	$queryTotal = $queryTotal." and ifNull(salesmen.ledger_number,'-1') between ifNull(".$fromsalesman.",ifNull(salesmen.ledger_number,'-1')) and ifNull(". $tosalesman.",ifNull(salesmen.ledger_number,'-1')) ";
}
if($cashtype != '' && $chk==1)
		$queryTotal = $queryTotal . " and  Trans.CashType_Code='$cashtype' ";
$queryTotal = $queryTotal. " group by currency_code order by currency_code";

//echo $queryTotal;exit;
$queryTotal=mysql_query($queryTotal,$connection) or die(mysql_error());
while($rowsTotal=mysql_fetch_row($queryTotal)){

$rownum = $rownum + 1;

$worksheet->write($rownum,0,"$rowsTotal[0]",$regularFormat);
$worksheet->write($rownum,3,number_format($rowsTotal[1],2),$regularFormat);
	
}
$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 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;
}
?>