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

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

//Option Balance of Project-Costcent
if ($report == 3) {
	$groupby = 2;
	//$showdetail = 1;
}

//$option 1=Project   2=Costcenter
if ($option == 1) {
	$ReportName  = 'Report of Project';
	$ReportTitle = 'Monthly Balance of Project per Account';
	$OptionLabel = 'Project';
	$SubOptionLabel = 'Costcenter';
} 
else {
	$ReportName  = 'Report of Costcenter';
	$ReportTitle = 'Monthly Balance of Costcenter per Account';
	$OptionLabel = 'Costcenter';
	$SubOptionLabel = 'Project';
}

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($HTTP_SESSION_VARS['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);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter,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) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$querymodule="select ifNull(module_groupbyledger,0) As module_groupbyledger,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($rows=mysql_fetch_array($resultmodule)){
  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_BalanceProjCostMonthly.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet($ReportName);
$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();

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

$SelectedGroupBy = "Group by:  ";
if ($groupby == 1 ) $SelectedGroupBy = $SelectedGroupBy . "Ledger";
if ($groupby == 2 ) $SelectedGroupBy = $SelectedGroupBy . $OptionLabel;


$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 ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";
$SelectedOptions = $SelectedGroupBy . "\n" . $SelectedOptions;

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

$vMonthFrom = intval(substr($fromdate, 5, 2));
$vMonthTo   = intval(substr($todate, 5, 2));
$vYearFrom  = substr($fromdate, 0, 4);
$vYearTo    = substr($todate, 0, 4);    

$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';      


//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,$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 (0, 0, 5);
$worksheet->setColumn (1, 1, 15);
$worksheet->setColumn (2, 2, 30);
$worksheet->setColumn (3, 100, 15);


if ($showdetail == 1) {
	if ($groupby == 1) {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum,$ColumnNum,"Ledger No.", $boldleftFormat);
		$ColumnNum += 2;
		$worksheet->write($RowNum,$ColumnNum,"Ledger Name", $boldleftFormat);
		$ColumnNum += 1;
	} else {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum,$ColumnNum,$OptionLabel, $boldleftFormat);
		$ColumnNum += 2;
		$worksheet->write($RowNum,$ColumnNum,$OptionLabel." Name", $boldleftFormat);
		$ColumnNum += 1;
	}
}


 
if ($groupby == 1) {
	$RowNum   += 1;
	$ColumnNum = 0;

	if ($showdetail == 1) {	
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,$OptionLabel, $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,$OptionLabel." Name", $boldleftFormat);
		$ColumnNum += 1;
	} else {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum,$ColumnNum,"Ledger No.", $boldleftFormat);
		$ColumnNum += 2;
		$worksheet->write($RowNum,$ColumnNum,"Ledger Name", $boldleftFormat);
		$ColumnNum += 1;
	}
} else {
	$RowNum   += 1;
	$ColumnNum = 0;

	if ($showdetail == 1) {	
		if ($report == 3) {
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum,$ColumnNum,$SubOptionLabel, $grouplevel1Format);
			$ColumnNum += 2;
			$worksheet->write($RowNum,$ColumnNum,$SubOptionLabel." Name", $grouplevel1Format);
			$ColumnNum += 1;

			$RowNum   += 1;
			$ColumnNum = 0;
		}
		
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Ledger No.", $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Ledger Name", $boldleftFormat);
		$ColumnNum += 1;
	} else {
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum,$ColumnNum,$OptionLabel, $boldleftFormat);
		$ColumnNum += 2;
		$worksheet->write($RowNum,$ColumnNum,$OptionLabel." Name", $boldleftFormat);
		$ColumnNum += 1;
		
		if ($report == 3) {
			$RowNum   += 1;
			$ColumnNum = 1;
			$worksheet->write($RowNum,$ColumnNum,$SubOptionLabel, $boldleftFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,$SubOptionLabel." Name", $boldleftFormat);
			$ColumnNum += 1;
		}
	}
}	

for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

	if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
	else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
	else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
	else { $vMFrom = 1; $vMTo = 12; }
					
	for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
		$worksheet->write($RowNum,$ColumnNum,getMonth($m)." ".$y, $boldcenterFormat);
		$ColumnNum += 1;
				
	}
}		

$worksheet->write($RowNum,$ColumnNum,"Balance ".$currency, $boldcenterFormat);
$ColumnNum += 1;		



$queryjournal=$queryjournal." select MasterCode, MasterDescription, DetailCode, DetailDescription, Currency_code, ".	
							" Balance, BalanceBase1, BalanceBase2, SubMasterCode, SubMasterDescription ";
							
for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

	if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
	else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
	else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
	else { $vMFrom = 1; $vMTo = 12; }
					
	for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
					
		$queryjournal = $queryjournal.
						", ".getMonth($m).$y."Balance AS ".getMonth($m).$y."Balance ".
						", ".getMonth($m).$y."BalanceBase1 AS ".getMonth($m).$y."BalanceBase1 ".
						", ".getMonth($m).$y."BalanceBase2 AS ".getMonth($m).$y."BalanceBase2 ";
					
	}
}			
				
$queryjournal=$queryjournal." from ( ";
$queryjournal=$queryjournal." select if($groupby=1, Ledger.Ledger_number, 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($groupby=1, Ledger.Ledger_name, if($option=1, ifNull(project.project_name, 'None'), ifNull(costcent.costcent_name, 'None'))) As MasterDescription , ".
							" if($report=3, if($option=2, 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'))), Null) As SubMasterCode, ".
							" if($report=3, if($option=2, ifNull(project.project_name, 'None'), ifNull(costcent.costcent_name, 'None')), Null) As SubMasterDescription , ".
							" if($showdetail=1, if($groupby=1, 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'))), Ledger.Ledger_number), '') As DetailCode, ".
							" if($showdetail=1, if($groupby=1, if($option=1, ifNull(project.project_name, 'None'), ifNull(costcent.costcent_name, 'None')), Ledger.Ledger_name), '') As DetailDescription , ".
							" JournalDt.Currency_code, ";
					
//if ($fromproject != 'Null' || $toproject != 'Null' || $fromcostcent != 'Null' || $tocostcent != 'Null') {
  
    $queryjournal= $queryjournal.
					" Sum(if(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')), if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount, Journaldt_amount), (-1)*if(CstJour.Cst_increment is not null, Cstjour_amount, Journaldt_amount)),0)) Balance, ".
					" Sum(if(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')), 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)),0)) BalanceBase1, ".
					" Sum(if(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')), 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)),0)) BalanceBase2 ";
					
	for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

		if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
		else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
		else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
		else { $vMFrom = 1; $vMTo = 12; }
					
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
					
			$queryjournal = $queryjournal.
							", Sum(if(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 (concat(month(JournalDt.Journal_date), year(JournalDt.Journal_date)) = '".$m.$y."'), if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount, Journaldt_amount), (-1)*if(CstJour.Cst_increment is not null, Cstjour_amount, Journaldt_amount)),0)) AS ".getMonth($m).$y."Balance ".
							", Sum(if(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 (concat(month(JournalDt.Journal_date), year(JournalDt.Journal_date)) = '".$m.$y."'), 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)),0)) AS ".getMonth($m).$y."BalanceBase1 ".
							", Sum(if(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 (concat(month(JournalDt.Journal_date), year(JournalDt.Journal_date)) = '".$m.$y."'), 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)),0)) AS ".getMonth($m).$y."BalanceBase2 ";
									
		}

	}				

/*} else {
    $queryjournal= $queryjournal.
					" Sum(if(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')), if(Journaldt_dbcd = 'D', Journaldt_amount , 0),0)) DebitAmount, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'D', Journaldt_amount1, 0),0)) DebitAmountBase1, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'D', Journaldt_amount2, 0),0)) DebitAmountBase2, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'C', Journaldt_amount , 0),0)) CreditAmount, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'C', Journaldt_amount1, 0),0)) CreditAmountBase1, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'C', Journaldt_amount2, 0),0)) CreditAmountBase2, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'D', Journaldt_amount, (-1)*Journaldt_amount),0)) Balance, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'D', Journaldt_amount1, (-1)*Journaldt_amount1),0)) BalanceBase1, ".
					" Sum(if(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')), if(Journaldt_dbcd = 'D', Journaldt_amount2, (-1)*Journaldt_amount2),0)) BalanceBase2 ";
				   
}	*/		   
 

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

//if ($fromproject != 'Null' || $toproject != 'Null' || $fromcostcent != 'Null' || $tocostcent != 'Null') {
	$queryjournal=$queryjournal." Left outer Join 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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
									"			   where loginstk.user_account='".$HTTP_SESSION_VARS['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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
									"			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
									"              and (costcent_code is not null and costcent_code != '') ".
									"			 ) Q2 on CstJour.Costcent_code like concat(Q2.costcent_code,'%')  ";								
//} else{   
  //$queryjournal= $queryjournal.	" Left outer Join project on project.Project_code = Journal.Project_code ".
	//							" Left outer Join costcent on costcent.Costcent_Code = Journal.Costcent_Code ";
							   
//}							   

 

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


//if ($fromproject != 'Null' || $toproject != 'Null' || $fromcostcent != 'Null' || $tocostcent != 'Null') { 
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 ($Module_BelongsToGroup == 1)				
	$queryjournal= $queryjournal." and ifNull(ledger.Ledger_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ".
							   " 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, SubMasterCode, DetailCode ".
							 " Order By MasterCode, SubMasterCode, DetailCode ";
							 
$queryjournal= $queryjournal." ) Q3  ".
							 " where (($balance = 1 and 1 = 1) or ($balance = 2 and Balance > 0) or ($balance = 3 and Balance < 0) or ($balance = 4 and Balance = 0)) ".
							 " Order By MasterCode, SubMasterCode, DetailCode ";

//echo $queryjournal;exit;

$MasterByCode = '';
$MasterByName = '';
$SubMasterByCode = '';
$SubMasterByName = '';

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

$RowNum    = 13;
$ColumnNum = 0;

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

    
    // Group By Options Names and Totals
	if (($report == 3) && ($SubMasterByCode != $rowsjournal[8] && $SubMasterByCode != '')) {
			
		$StyleClass  = 'subtotals';
		$TotalsLabel = 'Total by '. $SubOptionLabel .'('. $currency .')';
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
		$worksheet->write($RowNum, $ColumnNum, $TotalsLabel, $grouplevel1totalFormat);
		$ColumnNum += 3;
		
		for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

			if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
			else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
			else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
			else { $vMFrom = 1; $vMTo = 12; }
												
			for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalBySubMaster['Balance'][$m.$y], $grouplevel1totalFormat);
				$ColumnNum += 1;
			}
		}
		
		$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalBySubMaster['Balance']['Total'], $grouplevel1totalFormat);
		$ColumnNum += 1;
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
				
	}
	
	if ((($showdetail == 1) || ($report == 3 && $showdetail == 0)) && ($MasterByCode != $rowsjournal[0] && $MasterByCode != '')) {
			
		$StyleClass  = 'totals';
		if ($groupby == 1) $TotalsLabel = 'Total by Ledger ('. $currency .')';
		else $TotalsLabel = 'Total by '. $OptionLabel .'('. $currency .')';
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
		$worksheet->write($RowNum, $ColumnNum, $TotalsLabel, $boldrightFormat);
		$ColumnNum += 3;
		
		for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

			if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
			else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
			else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
			else { $vMFrom = 1; $vMTo = 12; }
												
			for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalByMaster['Balance'][$m.$y], $boldrightFormat);
				$ColumnNum += 1;
			}
		}
		
		$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalByMaster['Balance']['Total'], $boldrightFormat);
		$ColumnNum += 1;
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
				
	}

	if ((($showdetail == 1) || ($report == 3 && $showdetail == 0)) && ($MasterByCode == '' || $MasterByCode != $rowsjournal[0])) { 	
	
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
		$worksheet->write($RowNum,$ColumnNum,$rowsjournal[0]." - ".$rowsjournal[1], $boldleftFormat);
		$ColumnNum += 4;
		
		$MasterByCode = $rowsjournal[0];
		$MasterByName = $rowsjournal[1];
		
		unset($ArrayTotalByMaster);
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
	}	
	
	if (($report == 3 && $showdetail == 1) && ($SubMasterByCode == '' || $SubMasterByCode != $rowsjournal[8])) { 	

		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
		$worksheet->write($RowNum,$ColumnNum,$rowsjournal[8]." - ".$rowsjournal[9], $grouplevel1Format);
		$ColumnNum += 4;
		
		$SubMasterByCode = $rowsjournal[8];
		$SubMasterByName = $rowsjournal[9];
		
		unset($ArrayTotalBySubMaster);
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
	}	
	
	if ($showdetail == 1) {
		$ColumnNum += 1;
		$worksheet->writestring($RowNum, $ColumnNum, $rowsjournal[2], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[3], $regularleftFormat);
		$ColumnNum += 1;
	} 
	else if ($showdetail == 0) {
		if ($report != 3) {
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->writestring($RowNum, $ColumnNum, $rowsjournal[0], $regularleftFormat);
			$ColumnNum += 2;
			$worksheet->write($RowNum, $ColumnNum, $rowsjournal[1], $regularleftFormat);
			$ColumnNum += 1;
		} else {
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->writestring($RowNum, $ColumnNum, $rowsjournal[8], $regularleftFormat);
			$ColumnNum += 2;
			$worksheet->write($RowNum, $ColumnNum, $rowsjournal[9], $regularleftFormat);
			$ColumnNum += 1;
		}
	} 
	
	
	$i = 10;
	for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

		if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
		else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
		else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
		else { $vMFrom = 1; $vMTo = 12; }
										
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
			if ($currency == $Base1) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[$i + 1], $regularrightFormat);
				$ColumnNum += 1;
				
				$ArrayTotalByMaster['Balance'][$m.$y]    = $ArrayTotalByMaster['Balance'][$m.$y]    + $rowsjournal[$i + 1];
				$ArrayTotalBySubMaster['Balance'][$m.$y] = $ArrayTotalBySubMaster['Balance'][$m.$y] + $rowsjournal[$i + 1];
			}
			else if ($currency == $Base2) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[$i + 2], $regularrightFormat);
				$ColumnNum += 1;
				
				$ArrayTotalByMaster['Balance'][$m.$y]    = $ArrayTotalByMaster['Balance'][$m.$y]    + $rowsjournal[$i + 2];
				$ArrayTotalBySubMaster['Balance'][$m.$y] = $ArrayTotalBySubMaster['Balance'][$m.$y] + $rowsjournal[$i + 2];
			}
					
			$i = $i + 3;
		}
	}
	
	
	if ($currency == $Base1) {
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[6], $regularrightFormat);
		$ColumnNum += 1;
		
		$ArrayTotalByMaster['Balance']['Total']    = $ArrayTotalByMaster['Balance']['Total']    + $rowsjournal[6];
		$ArrayTotalBySubMaster['Balance']['Total'] = $ArrayTotalBySubMaster['Balance']['Total'] + $rowsjournal[6];
	}	
	else {
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[7], $regularrightFormat);
		$ColumnNum += 1;
		
		$ArrayTotalByMaster['Balance']['Total']    = $ArrayTotalByMaster['Balance']['Total']    + $rowsjournal[7];
		$ArrayTotalBySubMaster['Balance']['Total'] = $ArrayTotalBySubMaster['Balance']['Total'] + $rowsjournal[7];
	}

	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;	
	
	
}

// Group By Options Names and Totals
if (($report == 3) && ($SubMasterByCode != $rowsjournal[8] && $SubMasterByCode != '')) {
		    
	$StyleClass  = 'subtotals';
	$TotalsLabel = 'Total by '. $SubOptionLabel .'('. $currency .')';
		
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
	$worksheet->write($RowNum, $ColumnNum, $TotalsLabel, $boldrightFormat);
	$ColumnNum += 3;
		
	for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

		if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
		else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
		else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
		else { $vMFrom = 1; $vMTo = 12; }
												
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
			$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalBySubMaster['Balance'][$m.$y], $boldrightFormat);
			$ColumnNum += 1;
		}
	}
		
	$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalBySubMaster['Balance']['Total'], $boldrightFormat);
	$ColumnNum += 1;
		
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
		
}

if ((($showdetail == 1) || ($report == 3 && $showdetail == 0)) && ($MasterByCode != $rowsjournal[0] && $MasterByCode != '')) {
		    
	$StyleClass  = 'totals';
	if ($groupby == 1) $TotalsLabel = 'Total by Ledger ('. $currency .')';
	else $TotalsLabel = 'Total by '. $OptionLabel .'('. $currency .')';
		
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
	$worksheet->write($RowNum, $ColumnNum, $TotalsLabel, $boldrightFormat);
	$ColumnNum += 3;
		
	for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

		if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
		else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
		else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
		else { $vMFrom = 1; $vMTo = 12; }
												
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
			$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalByMaster['Balance'][$m.$y], $boldrightFormat);
			$ColumnNum += 1;
		}
	}
		
	$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalByMaster['Balance']['Total'], $boldrightFormat);
	$ColumnNum += 1;
		
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
		
}
	   


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 getMonth($month){
     return date('M', mktime(0,0,0,$month,1));
}

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


?>