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

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


//$option 1=Project   2=Costcenter
if ($option == 1) {
	$ReportName  = 'Statement of Project';
	$ReportTitle = 'Statement of Project';
	$OptionLabel = 'Project';
} 
else {
	$ReportName  = 'Statement of Costcenter';
	$ReportTitle = 'Statement of Costcenter';
	$OptionLabel = 'Costcenter';
}


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

$querylog="SELECT print as printaccess,ifNull(login_belongstogrp,0) As FI_BelongsToGrp 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 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);
}

$querymodule=" Select ifNull(Module_BelongsToGroup,0) As Module_BelongsToGroup, ifnull(Module_ConfirmJV,0) as Module_ConfirmJV  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 ".
          " 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);
}

if($fromledger!=''){
$queryledger="select ledger_name as fromledgername from ledger where ledger_number='$fromledger'";
$resultledger=mysql_query($queryledger,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultledger)){
  extract($rows);
  }
}

if($toledger!=''){
$queryledger2="select ledger_name as toledgername from ledger where ledger_number='$toledger'";
$resultledger2=mysql_query($queryledger2,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultledger2)){
  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);



$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_StatementProjCost.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet($ReportName);
$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'  ));  
  
$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();

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

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($currency != '')      $SelectedOptions .= "-> Currency: ". $currency ."\n";	
if ($fromledger != '')    $SelectedOptions .= "-> Ledger From: ". $fromledger . "  To: ". $toledger ."\n";
if ($fromledgername != '')    $SelectedOptions .= "-> Ledger Name From: ". $fromledgername . "  To: ". $toledgername ."<br>";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";

if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);     

$fromledger      = GetSQLValueString($fromledger, 'text');
$toledger        = GetSQLValueString($toledger, 'text');
$fromproject     = GetSQLValueString($fromproject, 'text');
$toproject    	 = GetSQLValueString($toproject, 'text');
$fromcostcent 	 = GetSQLValueString($fromcostcent, 'text');
$tocostcent  	 = GetSQLValueString($tocostcent, 'text');
$fromdate  	   	 = GetSQLValueString($fromdate, 'date');
$todate          = GetSQLValueString($todate, 'date');   

   
$jvtypelen = 0;
if (!empty($jvtype))
   $jvtypelen = strlen($jvtype);
   
if (empty($jvtype))   $jvtype = 'null';      

$showdetail = 1;

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,4,$ReportTitle, $TitleFormat);
               
$worksheet->write(1,7,"Printed Date:", $boldleftFormat);
$worksheet->write(1,8,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,7,"Time:", $boldleftFormat);		
$worksheet->write(2,8,$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,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"JV.Ref", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Folio", $boldcenterFormat);
$ColumnNum += 1;

if ($prtvaluedate == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Value Date", $boldcenterFormat);
	$ColumnNum += 1;
}
if ($prtjvtype == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 7);
	$worksheet->write($RowNum,$ColumnNum,"Type", $boldcenterFormat);
	$ColumnNum += 1;
}

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Ledger No.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Debit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Credit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Balance ".$currency, $boldcenterFormat);
$ColumnNum += 1;



$MainCompanyName=explode('_',$_SESSION['dbName']);  
$QueryDir = " Select Concat(CompanyName, '_', DirYear) As DirSchema ".
			" From ".$_SESSION['dbLabel'].".Directories ".
			" Where Upper(CompanyName) = Upper('".$MainCompanyName[0]."') ".
			" And ( (ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) >= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) <= Date_Format(EndFinancialDate,'%Y%m')) or   ".
			//"  		(ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) <= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) >= Date_Format(EndFinancialDate,'%Y%m')) or ".
			"       (Date_Format(StartFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(StartFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) or ".
            "       (Date_Format(EndFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(EndFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) ".
			"      )  ".
			" Order By DirYear ";
$ResultDir=mysql_query($QueryDir,$connection) or die(mysql_error());
$RowsDirCount = mysql_num_rows($ResultDir);
//echo $QueryDir;

$j = 0;				
$k = 0; // Count For First Existsing Db

$result = mysql_query("start transaction", $connection);
$resultvars = mysql_query("set @AmtBalBase1 :=0.0, @AmtBalBase2:=0.0, @VarMasterCode:='x' ", $connection);
	
$queryjournal=$queryjournal." select MasterCode, MasterDescription, LedgerNumber, LedgerName, Journal_date, Journaldt_ValueDate, ".
                            " OrderByDate, Journal_type, Journal_folio, Journal_refgovernment, Journaldt_desc, Currency_code, Journaldt_dbcd, ".	
							" PrevDbBase1, PrevDbBase2, PrevCrBase1, PrevCrBase2, PrevBalBase1, PrevBalBase2, ".
							" AmtDbBase1, AmtDbBase2, AmtCrBase1, AmtCrBase2, AmtBase1, AmtBase2, ".
							" @AmtBalBase1 := if (MasterCodeOption != MasterCode, if($prtprevbal = 1, PrevBalBase1 + AmtBase1, AmtBase1), @AmtBalBase1 + AmtBase1) As AmtBalBase1, ".
							" @AmtBalBase2 := if (MasterCodeOption != MasterCode, if($prtprevbal = 1, PrevBalBase2 + AmtBase2, AmtBase2), @AmtBalBase2 + AmtBase2) As AmtBalBase2 ".
							" from ( ".		
$queryjournal=$queryjournal." select MasterCode, MasterDescription, LedgerNumber, LedgerName, Journal_date, Journaldt_ValueDate, ".
                            " OrderByDate, Journal_type, Journal_folio, Journal_refgovernment, Journaldt_desc, Currency_code, Journaldt_dbcd, ".	
							" PrevDbBase1, PrevDbBase2, PrevCrBase1, PrevCrBase2, PrevBalBase1, PrevBalBase2, ".
							" AmtDbBase1, AmtDbBase2, AmtCrBase1, AmtCrBase2, AmtBase1, AmtBase2, ".
							" @VarMasterCode As MasterCodeOption, @VarMasterCode := MasterCode ".
							" from ( ".	
$queryjournal=$queryjournal." select MasterCode, MasterDescription, LedgerNumber, LedgerName, Journal_date, Journaldt_ValueDate, ".
                            " OrderByDate, Journal_type, Journal_folio, Journal_refgovernment, Journaldt_desc, Currency_code, Journaldt_dbcd, ".	
							" PrevDbBase1, PrevDbBase2, PrevCrBase1, PrevCrBase2, PrevBalBase1, PrevBalBase2, ".
							" AmtDbBase1, AmtDbBase2, AmtCrBase1, AmtCrBase2, AmtBase1, AmtBase2 ".
							" from ( ";
							
// Reading Data For Many Years Or Different Directories	
$ResultDir=mysql_query($QueryDir,$connection) or die(mysql_error());				 
while($RowsDir=mysql_fetch_row($ResultDir)){

	$j = $j + 1;    
	$DirSchema = $RowsDir[0];
		   
	if ($RowsDirCount >= 1) {
		$QuerySchemaExists = "Show Schemas where `Database` = '".$DirSchema."'  ";   
		$ResultSchemaExists=mysql_query($QuerySchemaExists,$connection) or die(mysql_error());
		$SchemaExists = mysql_num_rows($ResultSchemaExists);
	}	

	if (($RowsDirCount > 1 && $SchemaExists > 0) || ($RowsDirCount == 1) ) {
		
		$k = $k + 1;
	
		$queryjournal=$queryjournal." select if($option=1, if($parentprjcost=1, ifNull(Project.Parent_Code, ifNull(Project.Project_Code, 'None')), ifNull(CstJour.Project_code, 'None')), if($parentprjcost=1, ifNull(Costcent.Parent_Code, ifNull(Costcent.Costcent_Code, 'None')), ifNull(CstJour.Costcent_Code, 'None'))) As MasterCode, ".
									" if($option=1, ifNull(project.project_name, 'None'), ifNull(costcent.costcent_name, 'None')) As MasterDescription , ".
									" Ledger.Ledger_number As LedgerNumber, Ledger.Ledger_name As LedgerName, date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date, JournalDt.Journal_date As OrderByDate, ".
									" date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, Journal_type, Journal_folio, Journal_refgovernment, Journaldt_desc, JournalDt.Currency_code, Journaldt_dbcd, ".
									" ifNull(PrevDbBase1,0) As PrevDbBase1, ifNull(PrevDbBase2,0) As PrevDbBase2, ifNull(PrevCrBase1,0) As PrevCrBase1, ifNull(PrevCrBase2,0) As PrevCrBase2, ifNull(PrevBalBase1,0) As PrevBalBase1, ifNull(PrevBalBase2,0) As PrevBalBase2, ".
									" if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), 0) AmtDbBase1, ".
									" if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), 0) AmtDbBase2, ".
									" if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), 0) AmtCrBase1, ".
									" if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), 0) AmtCrBase2, ".
									" if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), (-1)*if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1)) AmtBase1, ".
									" if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), (-1)*if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2)) AmtBase2 ";

		$queryjournal= $queryjournal.
					   " from $DirSchema.JournalDt As JournalDt ".
					   " Inner join Ledger on JournalDt.Ledger_number = Ledger.Ledger_number ".
					   " Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ";

		$queryjournal=$queryjournal." Left outer Join $DirSchema.CstJour As CstJour on Journaldt.JournalDt_ID = Cstjour.JournalDt_ID ".
									" Left outer Join project on project.Project_code = Cstjour.Project_code ".
									" Left outer Join costcent on costcent.Costcent_Code = Cstjour.Costcent_Code ";

		if ($projectexist > 0 && $FI_RepRestrictProject == 1)
			$queryjournal=$queryjournal." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
										"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
										"              and (loginstk.project_code is not null and loginstk.project_code != '') ".
										"		     ) Q1 on CstJour.project_code like concat(Q1.project_code,'%') ";
										
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$queryjournal=$queryjournal." 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 != '') ".
										"			 ) Q2 on CstJour.Costcent_code like concat(Q2.costcent_code,'%')  ";								
		
		$queryjournal= $queryjournal." Left join ( ".
									" select JournalDt.Ledger_number As LedgerNo, if($option=1, ifNull(CstJour.Project_code, 'None'), ifNull(CstJour.Costcent_Code, 'None')) As PrevMasterCode, ".
									" Sum(if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), 0)) PrevDbBase1, ".
									" Sum(if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), 0)) PrevDbBase2, ".
									" Sum(if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), 0)) PrevCrBase1, ".
									" Sum(if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), 0)) PrevCrBase2, ".
									" Sum(if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), (-1)*if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1))) PrevBalBase1, ".
									" Sum(if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), (-1)*if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2))) PrevBalBase2 ".
									" from $DirSchema.JournalDt As JournalDt ".
									" Left outer Join $DirSchema.CstJour As CstJour on Journaldt.JournalDt_ID = Cstjour.JournalDt_ID ".
									" where date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(".$fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) ".
									" and JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(".$toledger.",JournalDt.Ledger_number)  ".
									" group by LedgerNo, PrevMasterCode ".
									" ) QPrev on QPrev.PrevMasterCode = if($option=1, ifNull(CstJour.Project_code, 'None'), ifNull(CstJour.Costcent_Code, 'None')) and  QPrev.LedgerNo = JournalDt.Ledger_number ";

	$queryjournal= $queryjournal.  
				" where  ledger.Ledger_number between ifNull(".$fromledger.",ledger.Ledger_number) and ifNull(".$toledger.",ledger.Ledger_number)  ".
				" and date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))   ".
				" and (($alltypes = 1 and 1 = 1) or ($alltypes = 0 and $jvtypelen = 0 and 1 = 1) or ($alltypes = 0 and $exclude = 0 and $jvtypelen > 0 and Journal_type in ($jvtype)) or ($alltypes = 0 and $exclude = 1 and $jvtypelen > 0 and Journal_type not in ($jvtype)) ) ";
				//" and Journal.Journal_Type != 'CL' ".
if($projectper == 1)
$queryjournal= $queryjournal." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(upper (".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) ";
else 			
		$queryjournal= $queryjournal.		
				" and upper(ifNull(CstJour.project_code,'-1')) between ifNull(upper(".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) and ifNull(upper(".$toproject."), upper(ifNull(CstJour.project_code,'-1'))) ";
if($costcentper == 1)
$queryjournal= $queryjournal." and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(upper(".$fromcostcent."), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
else			
		$queryjournal= $queryjournal.		
				" and upper(ifNull(CstJour.Costcent_Code,'-1')) between ifNull(upper(".$fromcostcent."), upper(ifNull(CstJour.Costcent_Code,'-1'))) and ifNull(upper(".$tocostcent."), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
 

		if ($RowsDirCount > 1 && $k != 1)	
			$queryjournal= $queryjournal." and Journal.Journal_Type != 'OP' ";
		
		if ($Module_BelongsToGroup == 1)
		$queryjournal= $queryjournal." and ifNull(JournalDt.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ";	
	
	    if($Module_ConfirmJV == 1) 
			 $queryjournal= $queryjournal." and ifnull(Journal_Confirmed,0) = 1 ";
			  
		//$queryjournal= $queryjournal." group by MasterCode, date_format(JournalDt.Journal_date,'%d/%m/%Y'), date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y'), JournalDt.Ledger_number, Journal_refgovernment  ";
			  
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
			$queryjournal= $queryjournal." Union ";
							
	}				
}		
							 
$queryjournal= $queryjournal." ) Q3  ".
							 " Order By MasterCode, date_format(OrderByDate,'%Y-%m-%d'), Journal_refgovernment, LedgerNumber ".
							 " ) Q4  ".
							 " Order By MasterCode, date_format(OrderByDate,'%Y-%m-%d'), Journal_refgovernment, LedgerNumber ".
							 " ) Q5  ".
							 " Order By MasterCode, date_format(OrderByDate,'%Y-%m-%d'), Journal_refgovernment, LedgerNumber ";

//echo $queryjournal;exit;


$MasterByCode = '';
$MasterByName = '';

$ArrayTotalByReport   = Array();
$ArrayTotalByMaster   = Array();
$GeneralTotal   	  = Array();

$RowNum    = 12;
$ColumnNum = 0;

$resultjournal=mysql_query($queryjournal,$connection) or die(mysql_error());
while($rowsjournal=mysql_fetch_row($resultjournal)){

    
    // Group By Options Names and Totals
	if (($showdetail == 1) && ($MasterByCode != $rowsjournal[0] && $MasterByCode != '')) {
		
		$StyleClass   = $grouplevel1totalFormat;
		$TotalsLabel  = 'Total Period by '. $OptionLabel .'('. $currency .')';
		$TotalsLabel1 = 'Total by '. $OptionLabel .'('. $currency .')';
		
		DisplayTotals($worksheet, $prtprevbal, $prtvaluedate, $prtjvtype, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $TotalsLabel1, $StyleClass, $RowNum, $ColumnNum);
		$GeneralTotal['Debit'] 		= $GeneralTotal['Debit'] 	  + $ArrayTotalByMaster['Debit'];
		$GeneralTotal['Credit'] 	= $GeneralTotal['Credit'] 	  + $ArrayTotalByMaster['Credit'];
		$GeneralTotal['Balance'] 	= $GeneralTotal['Balance'] 	  + $ArrayTotalByMaster['Balance'];
		unset($ArrayTotalByMaster);
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 3;
		$ColumnNum = 0;
		
	}

	if (($showdetail == 1) && ($MasterByCode == '' || $MasterByCode != $rowsjournal[0])) { 	

		$StyleClass   = $grouplevel1Format;
		
		if ($currency == $Base1) 
			CreateGroupHeader($worksheet, $prtprevbal, $prtvaluedate, $prtjvtype, $rowsjournal[0], $rowsjournal[1], $OptionLabel, $Base1, $rowsjournal[17], $DecimalBase1, $StyleClass, $RowNum, $ColumnNum);
		else if ($currency == $Base2)	
			CreateGroupHeader($worksheet, $prtprevbal, $prtvaluedate, $prtjvtype, $rowsjournal[0], $rowsjournal[1], $OptionLabel, $Base2, $rowsjournal[18], $DecimalBase2, $StyleClass, $RowNum, $ColumnNum);
		
		$MasterByCode = $rowsjournal[0];
		$MasterByName = $rowsjournal[1];
		
		//Set The Actual Balance Initialy Equal To Previous Balance
		if ($currency == $Base1) {
			$ArrayTotalByMaster['ActDebit']   = $rowsjournal[13];
			$ArrayTotalByMaster['ActCredit']  = $rowsjournal[15];
			$ArrayTotalByMaster['ActBalance'] = $rowsjournal[17];
		}	
		else if ($currency == $Base2){
			$ArrayTotalByMaster['ActDebit']   = $rowsjournal[14];
			$ArrayTotalByMaster['ActCredit']  = $rowsjournal[16];
			$ArrayTotalByMaster['ActBalance'] = $rowsjournal[18];
		}	
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
	}	
						
	
	if ($showdetail == 1) {
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[4], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[9], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[8], $regularleftFormat);
		$ColumnNum += 1;
		
		if ($prtvaluedate == 1) {
			$worksheet->write($RowNum, $ColumnNum, $rowsjournal[5], $regularleftFormat);
			$ColumnNum += 1;
		}	
		if ($prtjvtype == 1) {
			$worksheet->write($RowNum, $ColumnNum, $rowsjournal[7], $regularcenterFormat);
			$ColumnNum += 1;
		}	
			
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[2], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[10], $regularleftFormat);
		$ColumnNum += 1;
		
		if ($currency == $Base1) {
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[19], $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[21], $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[25], $regularrightFormat);
			$ColumnNum += 1;
		
			$ArrayTotalByMaster['Debit']      = $ArrayTotalByMaster['Debit']      + $rowsjournal[19];
			$ArrayTotalByMaster['Credit']     = $ArrayTotalByMaster['Credit']     + $rowsjournal[21];
			$ArrayTotalByMaster['Balance'] 	  = $ArrayTotalByMaster['Balance']    + $rowsjournal[23];
			$ArrayTotalByMaster['ActDebit']   = $ArrayTotalByMaster['ActDebit']   + $rowsjournal[19];
			$ArrayTotalByMaster['ActCredit']  = $ArrayTotalByMaster['ActCredit']  + $rowsjournal[21];
			$ArrayTotalByMaster['ActBalance'] = $ArrayTotalByMaster['ActBalance'] + $rowsjournal[23];
		}
		else if ($currency == $Base2) {
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[20], $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[22], $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[26], $regularrightFormat);
			$ColumnNum += 1;
			
			$ArrayTotalByMaster['Debit']      = $ArrayTotalByMaster['Debit']      + $rowsjournal[20];
			$ArrayTotalByMaster['Credit']     = $ArrayTotalByMaster['Credit']     + $rowsjournal[22];
			$ArrayTotalByMaster['Balance'] 	  = $ArrayTotalByMaster['Balance']    + $rowsjournal[24];
			$ArrayTotalByMaster['ActDebit']   = $ArrayTotalByMaster['ActDebit']   + $rowsjournal[20];
			$ArrayTotalByMaster['ActCredit']  = $ArrayTotalByMaster['ActCredit']  + $rowsjournal[22];
			$ArrayTotalByMaster['ActBalance'] = $ArrayTotalByMaster['ActBalance'] + $rowsjournal[24];
		}
		
	}	
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
	
}

// Group By Options Names and Totals
if (($showdetail == 1) && ($MasterByCode != $rowsjournal[0] && $MasterByCode != '')) {
		
	$StyleClass   = $grouplevel1totalFormat;
	$TotalsLabel  = 'Total Period by '. $OptionLabel .'('. $currency .')';
	$TotalsLabel1 = 'Total by '. $OptionLabel .'('. $currency .')';
		
	DisplayTotals($worksheet, $prtprevbal, $prtvaluedate, $prtjvtype, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $TotalsLabel1, $StyleClass, $RowNum, $ColumnNum);
	$GeneralTotal['Debit'] 		= $GeneralTotal['Debit'] 	  + $ArrayTotalByMaster['Debit'];
	$GeneralTotal['Credit'] 	= $GeneralTotal['Credit'] 	  + $ArrayTotalByMaster['Credit'];
	$GeneralTotal['Balance'] 	= $GeneralTotal['Balance'] 	  + $ArrayTotalByMaster['Balance'];	
	unset($ArrayTotalByMaster);
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 3;
	$ColumnNum = 0;
		
}

$RowNum += 1; 
$ColumnNum += 3;
$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
$worksheet->write($RowNum, $ColumnNum, "General Total by ".$OptionLabel."(". $currency .")", $StyleClass);
$ColumnNum += 2;	
 
$worksheet->writeNumber($RowNum, $ColumnNum, $GeneralTotal['Debit'] , $regularrightFormat);
$ColumnNum += 1;
$worksheet->writeNumber($RowNum, $ColumnNum,$GeneralTotal['Credit'] , $regularrightFormat);
$ColumnNum += 1;
$worksheet->writeNumber($RowNum, $ColumnNum, $GeneralTotal['Balance'], $regularrightFormat);
$ColumnNum += 1;


function CreateGroupHeader($worksheet, $PPrtPrevbal, $PPrtValueDate, $PPrtJvType, $PMasterCode, $PMasterName, $POptionLabel, $PBase, $PPrevBal, $PDecimalBase, $PStyleClass, $PRowNum, $PColumnNum) {
	
	if ($PPrtValueDate == 1 && $PPrtJvType == 1)
		$MergeColumns = 5;
	else if ($PPrtValueDate == 0 && $PPrtJvType == 0)
		$MergeColumns = 3;
	else if ($PPrtValueDate == 1 || $PPrtJvType == 1)
		$MergeColumns = 4;
		
	$worksheet->setMerge($PRowNum, $PColumnNum, $PRowNum, $PColumnNum + 1);
	$worksheet->write($PRowNum, $PColumnNum, "Group by ".$POptionLabel, $PStyleClass);
	$PColumnNum += 2;	
	
	$worksheet->setMerge($PRowNum, $PColumnNum, $PRowNum, $PColumnNum + $MergeColumns - 1);
	$worksheet->write($PRowNum, $PColumnNum, $PMasterCode. " - ".$PMasterName, $PStyleClass);
	$PColumnNum += $MergeColumns;	
	
	if ($PPrtPrevbal == 1) {
		$worksheet->setMerge($PRowNum, $PColumnNum, $PRowNum, $PColumnNum + 1);
		$worksheet->write($PRowNum, $PColumnNum, "Previous Balance ". $PBase , $PStyleClass);
		$PColumnNum += 2;	
		
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PPrevBal, $PStyleClass);
		$PColumnNum += 1;
	}	
			
	
}

function DisplayTotals($worksheet, $PPrtPrevbal, $PPrtValueDate, $PPrtJvType, $PArrayTotal, $PDecimalBase, $PTotalsLabel, $PTotalsLabel1, $PStyleClass, $PRowNum, $PColumnNum) {
	
	if ($PPrtValueDate == 1 && $PPrtJvType == 1)
		$MergeColumns = 7;
	else if ($PPrtValueDate == 0 && $PPrtJvType == 0)
		$MergeColumns = 5;
	else if ($PPrtValueDate == 1 || $PPrtJvType == 1)
		$MergeColumns = 6;
			
		
	$worksheet->setMerge($PRowNum, $PColumnNum, $PRowNum, $PColumnNum + $MergeColumns - 1);
	$worksheet->write($PRowNum, $PColumnNum, $PTotalsLabel, $PStyleClass);
	$PColumnNum += $MergeColumns;
	
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Debit'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Credit'], $PStyleClass);
	$PColumnNum += 1;
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['Balance'], $PStyleClass);
	$PColumnNum += 1;
			
		
	if ($PPrtPrevbal == 1) {
		$PRowNum += 1;
		$PColumnNum = 0;
		
		$worksheet->setMerge($PRowNum, $PColumnNum, $PRowNum, $PColumnNum + $MergeColumns - 1);
		$worksheet->write($PRowNum, $PColumnNum, $PTotalsLabel1, $PStyleClass);
		$PColumnNum += $MergeColumns;
		
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['ActDebit'], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['ActCredit'], $PStyleClass);
		$PColumnNum += 1;
		$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['ActBalance'], $PStyleClass);
		$PColumnNum += 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;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}

function format_currency($amount, $DecimalBase) { 

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

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

$workbook->close();

?>