<?php

header("Content-type: text/html; charset=windows-1256");



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($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 
 

session_start();

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

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


//Set Time Execution For the Report
//ini_set('max_execution_time', $config['ReportTimeExecution']);
set_time_limit(0);

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");

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

//Read From Module Table
$querymodule=" Select ifNull(Module_BelongsToGroup,0) As Module_BelongsToGroup,ifNull(module_delivery,0) As module_delivery, 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);
}

$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2,  Company_Name As CompanyName, COMPANY_CITY,COMPANY_COUNTRY from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  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);	

//maroun
$querymasterproject=" select distinct Masterproject from ".$_SESSION['dbLabel'].".loginstk ".
					" where loginstk.user_account='".$_SESSION['useraccount']."' ".
					" and (Masterproject is not null and Masterproject != '') ";
														 
$projectmasterresult = mysql_query($querymasterproject,$connection) or die(mysql_error());
$projectmasterexist   = mysql_num_rows($projectmasterresult);

//maroun
$querymastercostcenter=" select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
					   " where loginstk.user_account='".$_SESSION['useraccount']."' ".
					   " and (Mastercostcenter is not null and Mastercostcenter != '') ";

$costcentermasterresult = mysql_query($querymastercostcenter,$connection) or die(mysql_error());
$costcentermasterexist   = mysql_num_rows($costcentermasterresult);

//maroun
$querysalesmen=" select distinct clientsalesmen 
			from ".$_SESSION['dbLabel'].".loginstk ".
			  " where user_account='".$_SESSION['useraccount']."' 
			  and clientsalesmen<>'' 
			  or clientsalesmen <> null
			  "  ;
$salesmenresult = mysql_query($querysalesmen,$connection) or die(mysql_error());
$salesmenexist   = mysql_num_rows($salesmenresult);

$queryledger= " select distinct ledger_number, ledger_include ".
              " from ".$_SESSION['dbLabel'].".ledgerrepstk  ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " and (ledger_number is not null and ledger_number != '') ".
			  " order by ledger_number";
$ledgerresult = mysql_query($queryledger,$connection) or die(mysql_error());
$ledgerexist  = mysql_num_rows($ledgerresult);	
$rows=mysql_fetch_array($ledgerresult) ;
$ledgerinclude = $rows[1];
if (empty($ledgerinclude)) $ledgerinclude = 0;

$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_StatementOfAccount.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Export');
$worksheet->setInputEncoding('windows-1256'); 
$worksheet->setLandscape();


// column width from column 1 to 1
$worksheet->setColumn(1,1,12);


//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'=>9,
  'align'=>'left'
  ));
  
$squareborderBolfFormat =& $workbook->addFormat(array('size'=>9,
  'top'=>1,  
  'bottom'=>1,
  'right'=>1,
  'left'=>1,
  'bold'=>1
  ));  
// 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
  ));  
$boldFormatCenter =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'bold'=>1
  )); 
 $boldFormatRight =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  ));
  
$lrBorderFormatNum =& $workbook->addFormat(array('size'=>9,
  'right'=>1,
  'left'=>1,
  'align'=>'right' 
  ));
$boldFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'bold'=>1
  ));   
$lrBorderFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

   

               
				
				
$dispfromproject  = $fromproject;
$dispfromledger = $fromledger;
$disptoledger = $toledger;
$dispfromdate = $fromdate;
$disptodate = $todate;

  
if (empty($fromledger))   
   $fromledger = 'null';
else
   $fromledger = "'".$fromledger."'";   
   
if (empty($toledger))   
   $toledger = 'null';
else{
   //if (strlen($toledger) < 10 )
     // $toledger = str_pad($toledger, 10, '9', STR_PAD_RIGHT);
 
   $toledger = "'".$toledger."'";      
}  

if (empty($fromproject))   
   $fromproject = 'null';
else
   $fromproject = "'".$fromproject."'";    
   
  
if (empty($fromcst))   
   $fromcst = 'null';
else
   $fromcst = "'".$fromcst."'";    
     

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."'";
   

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

if (empty($cltcst))   
   $cltcst = 'null';
else
   $cltcst = "'".$cltcst."'";       
     
if (empty($cltprj))   
   $cltprj = 'null';
else
   $cltprj = "'".$cltprj."'";  
   
if (empty($parentprj))  $parentprj = 0;
if (empty($prtcstjour)) $prtcstjour = 0;
if (empty($prtwording)) $prtwording = 0;
   
$displaycurr = '';   
if ($currbase1 == 1 ) 
   $displaycurr = $Base1;
else if  ($currbase2 == 1 )
   $displaycurr = $Base2;
   
if($currbase1 == 1 && $currbase2 == 1)   
   $displaycurr = ''; 
 

 


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

if ($ledgeroption==1) {
	$queryjournal = " Select Ledger.Ledger_Number,Ledger.Ledger_Name,";
	} else {
	$queryjournal = " Select Ledger.document Ledger_Number, 'Document' as Ledger_Name,";
	}

 $queryjournal =$queryjournal ."  Ledger.Ledger_type, ".
                " if(Ledger_Type=1, Concat(ifNull(Client_Telephone1,''), '  ', ifNull(Client_Telephone2,'')), ".
                " if(Ledger_Type=2, Concat(ifNull(Supplier_Telephone1,''), '  ', ifNull(Supplier_Telephone2,'')), ".
                " if(Ledger_Type=3, Concat(ifNull(Salesman_Telephone1,''), '  ', ifNull(Salesman_Telephone2,'')), Null ))) As TelephoneNo, ".
				" if(Ledger_Type=1, Client_Fax, if(Ledger_Type=2, Supplier_Fax, if(Ledger_Type=3, Salesman_Fax, Null))) As Fax, ".
				" if(Ledger_Type=1, Client_Email, if(Ledger_Type=2, Supplier_Email, if(Ledger_Type=3, Salesman_Email, Null))) As Email, ".
				" if(Ledger_Type=1, Client_Address, if(Ledger_Type=2, Supplier_Address, if(Ledger_Type=3, Salesman_address, Null))) As Address, ".
                " QDirBal.Currency_code, Sum(ifNull(PrevAmount,0)) As PrevAmount, Sum(ifNull(PrevAmount1,0)) As PrevAmount1, Sum(ifNull(PrevAmount2,0)) As PrevAmount2, ".
				" if(Ledger_Type=1, Client_VatNo, if(Ledger_Type=2, Supplier_vatno, Null)) As VatNo ".
				" From Ledger Left Join ".
				" (  ";

// Reading Data For Many Years Or Different Directories
while($RowsDir=mysql_fetch_row($ResultDir)){

	$j = $j + 1;    
	if ($RowsDirCount > 1)
	   $DirSchema = $RowsDir[0];
	else
	   $DirSchema = '';	
			   
	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;    

		if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
		    $queryjournal = $queryjournal.
					        " select JournalDt.Ledger_Number, JournalDt.Currency_code, ".
							" sum(if(( ($dateoption = 1 and date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))  ) or  ".
							"          ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))  ) ), ".
							"        (if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is null,Journaldt_amount,Cstjour_amount), 0) - if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is null,Journaldt_amount,Cstjour_amount), 0)), 0)) PrevAmount, ".
							" sum(if(( ($dateoption = 1 and date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))  ) or  ".
							"          ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))  ) ), ".
							"        (if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is null,Journaldt_amount1,Cstjour_amount1), 0) - if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is null,Journaldt_amount1,Cstjour_amount1), 0)), 0)) PrevAmount1, ".
							" sum(if(( ($dateoption = 1 and date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))  ) or  ".
							"          ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))  ) ), ".
							"        (if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is null,Journaldt_amount2,Cstjour_amount2), 0) - if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is null,Journaldt_amount2,Cstjour_amount2), 0)), 0)) PrevAmount2 ".
							" from $DirSchema.JournalDt As JournalDt ".
							" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
							" Inner Join Ledger ON JournalDt.Ledger_Number = Ledger.Ledger_Number ";
		} else {
			$queryjournal = $queryjournal.
							" select JournalDt.Ledger_Number, JournalDt.Currency_code, ".
							" sum(if(( ($dateoption = 1 and date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))  ) or  ".
							"          ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))  ) ), ".
							"        (if(Journaldt_dbcd = 'D', Journaldt_amount, 0) - if(Journaldt_dbcd = 'C', Journaldt_amount, 0)), 0)) PrevAmount, ".
							" sum(if(( ($dateoption = 1 and date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))  ) or  ".
							"          ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))  ) ), ".
							"        (if(Journaldt_dbcd = 'D', Journaldt_amount1, 0) - if(Journaldt_dbcd = 'C', Journaldt_amount1, 0)), 0)) PrevAmount1, ".
							" sum(if(( ($dateoption = 1 and date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))  ) or  ".
							"          ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))  ) ), ".
							"        (if(Journaldt_dbcd = 'D', Journaldt_amount2, 0) - if(Journaldt_dbcd = 'C', Journaldt_amount2, 0)), 0)) PrevAmount2 ".
							" from $DirSchema.JournalDt As JournalDt ".
							" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
							" Inner Join Ledger ON JournalDt.Ledger_Number = Ledger.Ledger_Number ";
		}	  
				  
				  
		if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
			$queryjournal= $queryjournal." Left outer Join $DirSchema.CstJour As CstJour on Journaldt.JournalDt_ID = Cstjour.JournalDt_ID ";

			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.  
								" where ( ($ledgeroption = 1 and JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number) ) or ".
								"         ($ledgeroption = 2 and ifNull(Ledger.Document,-1) between ifNull(".$fromledger.",ifNull(Ledger.Document,-1)) and ifNull(". $toledger.",ifNull(Ledger.Document,-1)) ) ) ".
								//" and ( ($dateoption = 1 and date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))  ) or  ".
								//"       ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))  ) )  ".
								//" and (($cum = 0 and JournalDt.Currency_code = '".$rowsjournal[13]."') or ($cum = 1 and 1 = 1))  ".
								" 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 (($showunposted = 1 and journaldt_posted = 0 ) or ($showunposted = 0 and 1 = 1 )) ";
								
		if ($Module_BelongsToGroup == 1)				
			$queryjournal= $queryjournal." and ifNull(JournalDt.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ";						
								

		if ($curr == 1 && !(empty($fromcur))  )
			$queryjournal= $queryjournal." and Upper(JournalDt.Currency_code) = Upper('".$fromcur."') ";
										  
		if($Module_ConfirmJV == 1) $queryjournal= $queryjournal." and ifnull(Journal_Confirmed,0) = 1 ";
		
		if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {  
			if ($parentprj == 1) {
				$queryjournal= $queryjournal." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(concat(upper(".$fromproject."),'%'), upper(ifNull(CstJour.project_code,'-1'))) ".
											 " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(concat(upper(".$fromcst."),'%'), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
			} else {
				$queryjournal= $queryjournal." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) ".
											 " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
			}

		}
			
		if ($RowsDirCount > 1 && $k != 1)	
			$queryjournal= $queryjournal." And ifNull(Journal.Journal_Type,'JV') <> 'OP' ";
					
		$queryjournal= $queryjournal." Group By JournalDt.Ledger_Number, JournalDt.Currency_code ";
		  
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
			$queryjournal= $queryjournal." Union ";
								
	}							
}
		
$queryjournal= $queryjournal." ) QDirBal on QDirBal.Ledger_Number = Ledger.Ledger_Number ".
                             " Left Join Clients on Clients.Ledger_Number = Ledger.Ledger_Number ".						
							 " Left Join Suppliers on Suppliers.Ledger_Number = Ledger.Ledger_Number ".
							 " Left Join Salesmen on Salesmen.Ledger_Number = Ledger.Ledger_Number ";
							 
							/*if ($ledgerexist > 0) {
								$queryjournal = $queryjournal." Inner join ( select distinct ledger_number from ".$_SESSION['dbLabel'].".ledgerrepstk ".
															  "			     where user_account='".$_SESSION['useraccount']."' ".
															  "              and (ledger_number is not null and ledger_number != '') ".
															  "			   )  Q3 on (($ledgerinclude = 1 and Ledger.Ledger_Number like concat(Q3.ledger_number,'%') ) or ($ledgerinclude = 0 and Ledger.Ledger_Number Not like concat(Q3.ledger_number,'%') )) ";	
							} */
							
							//maroun
							 if ($projectmasterexist > 0)
								{
							 $queryjournal= $queryjournal." inner join (select distinct Masterproject from ".$_SESSION['dbLabel'].".loginstk
							 			   where loginstk.user_account='".$_SESSION['useraccount']."'
							              and (Masterproject is not null and Masterproject != '')
							 			 ) PrjMasterQry on ifnull(Clients.project_code,concat(PrjMasterQry.Masterproject,'%')) like concat(PrjMasterQry.Masterproject,'%') ";
								}
							
							if ($costcentermasterexist > 0) {	
							$queryjournal= $queryjournal." inner join ( select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
														 "			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
														 "             and (Mastercostcenter is not null and Mastercostcenter != '') ".
														 "			 ) CstMasterQry on ifnull(Clients.CostCenter_Code ,concat(CstMasterQry.Mastercostcenter,'%')) like concat(CstMasterQry.Mastercostcenter,'%') ";
							}
						
					
					
							 $queryjournal= $queryjournal." where ( ($ledgeroption = 1 and ledger.Ledger_number between ifNull(".$fromledger.",ledger.Ledger_number) and ifNull(". $toledger.",ledger.Ledger_number) ) or ".
														  "         ($ledgeroption = 2 and ifNull(ledger.Document,-1) between ifNull(".$fromledger.", ifNull(ledger.Document,-1)) and ifNull(". $toledger.", ifNull(ledger.Document,-1)) ) ) ".
							 //" And PrevAmount != 0 ".
							 //maroun
							 " And upper(ifNull(Clients.Project_Code,'-1')) like if(clients.ledger_number='' or clients.ledger_number is null,upper(ifNull(Clients.Project_Code,'-1')),ifNull(upper(".$cltprj."), upper(ifNull(Clients.Project_Code,'-1')))) ".
							 " And upper(ifNull(Clients.CostCenter_Code,'-1')) like if(clients.ledger_number='' or clients.ledger_number is null,upper(ifNull(Clients.CostCenter_Code,'-1')),ifNull(upper(".$cltcst."), upper(ifNull(Clients.CostCenter_Code,'-1')))) ";
							 		//maroun
					if ($salesmenexist > 0) {
						$queryjournal= $queryjournal. " and clients.Salesmen_Code in 
															(select distinct clientsalesmen from ".$_SESSION['dbLabel'].".loginstk where user_account = '".$_SESSION['useraccount']."'
															 and (clientsalesmen <>'' and clientsalesmen is not null)
															)";	
											}
					
if ($Module_BelongsToGroup == 1)				
	$queryjournal= $queryjournal." and ifNull(ledger.Ledger_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ";
	
if($ledgerinclude== 1 && $ledgerexist > 0) {
	$queryjournal = $queryjournal." And Exists ( select 1 from ".$_SESSION['dbLabel'].".ledgerrepstk ".
								  "              where ledgerrepstk.user_account='".$_SESSION['useraccount']."' ".
								  "              and (ledger_number is not null and ledger_number != '') ".
								  "              and ledger.ledger_number like concat(ledgerrepstk.ledger_number,'%') ".
								  "            ) ";
}
if($ledgerinclude== 0 && $ledgerexist > 0){
	$queryjournal = $queryjournal." And Not Exists ( select 1 from ".$_SESSION['dbLabel'].".ledgerrepstk ".
								  "                  where ledgerrepstk.user_account='".$_SESSION['useraccount']."' ".
								  "          	     and (ledger_number is not null and ledger_number != '') ".
								  "                  and ledger.ledger_number like concat(ledgerrepstk.ledger_number,'%') ".
								  "                ) ";	
}
if ($salesman<>'')
  $queryjournal= $queryjournal." and Salesmen_Code='".$salesman."' ";
$queryjournal= $queryjournal." And Ledger.Ledger_grpdet = 0 ";
if ($ledgeroption==1) {
    $queryjournal= $queryjournal." Group By Ledger.Ledger_Number, Ledger.Ledger_Name, Ledger.Ledger_type, if($cum = 0, QDirBal.Currency_code, Null), TelephoneNo, Fax, Email, Address ";	
} else {
    $queryjournal= $queryjournal."  and QDirBal.currency_code is not null Group By Ledger_Number,if($cum = 0, QDirBal.Currency_code, Null) ";	
}
	
//echo $queryjournal; exit;






$masterledger = '';
$mastercurrency = '';
$SumCreditAmount = 0;
$SumDebitAmount = 0;
$PrevBalance = 0;
$Balance = 0;
$resultjournal=mysql_query($queryjournal,$connection) or die(mysql_error());

$rows=7;
while($rowsjournal=mysql_fetch_row($resultjournal)){
	
	
	
	$j = 0;				
	$k = 0; // Count For First Existsing Db
	$querydetail = '';


	$querydetail = " Select Journal_id, Journal_date, Journaldt_ValueDate, Journal_type, Journal_folio, Journal_refgovernment, ".
				   " Journaldt_desc, Ledger_number, Ledger_name, ledger_type, Document, Project_code, Costcent_Code, ".
				   " Currency_code, Journaldt_dbcd, DebitAmount, CreditAmount, Journaldt_Id, project_name, costcent_name, OrderByDate, SchemaName,  ".
				   " Delivery_Reference,Sorder_Reference,itemdesc,Qty,unit1,unit2,unit2qty ".
				   " from ".
				   " (  ";

	// Reading Data For Many Years Or Different Directories	
	$ResultDir1=mysql_query($QueryDir,$connection) or die(mysql_error());				 
	while($RowsDir=mysql_fetch_row($ResultDir1)){
		
	$masterledger= $rowsjournal[0];
	$mastercurrency = $rowsjournal[7];
	  
		$j = $j + 1;    
		if ($RowsDirCount > 1)
		   $DirSchema = $RowsDir[0];
		else
		   $DirSchema = '';	
		   
		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;    
			if ($dtdate == 1)
			{
			 
			  if ($prtcstjour == 0 && ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" Journal_type, Journal_folio, Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document, cstjour.Project_code, cstjour.Costcent_Code, JournalDt.Currency_code, Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" null as Delivery_Reference,null as Sorder_Reference,'-' as itemdesc,'-' as Qty, ".
								" '-'  as unit1, '-'  as unit2, '-'  as unit2qty ";
			  }
			  else if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" Journal_type, Journal_folio, Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
								
				
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}			
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document, cstjour.Project_code, cstjour.Costcent_Code, JournalDt.Currency_code, Journaldt_dbcd, ".
								" if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0) As DebitAmount, ".
								" if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" null as Delivery_Reference,null as Sorder_Reference,'-' as itemdesc,'-' as Qty, ".
								" '-'  as unit1, '-'  as unit2, '-'  as unit2qty ";
			  } else {
				 $querydetail= $querydetail. 
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" Journal_type, Journal_folio, Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
					
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document,Journal.Project_code,Journal.Costcent_Code,JournalDt.Currency_code, Journaldt_dbcd, ".
								" if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0) As DebitAmount, ".
								" if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" null as Delivery_Reference,null as Sorder_Reference,'-' as itemdesc,'-' as Qty, ".
								" '-'  as unit1, '-'  as unit2, '-'  as unit2qty ";
			  }			   
			} else {
			  
			   if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select null As Journal_id ,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" null As Journal_type, null As Journal_folio, null As Journal_refgovernment, null As Journaldt_desc,";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" null As Document, null As Project_code, null As Costcent_Code, JournalDt.Currency_code, null As Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As CreditAmount, ".
								" null As Journaldt_Id, null As project_name, null As costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName, ".
								" null as Delivery_Reference,null as Sorder_Reference,'-' as itemdesc,'-' as Qty, ".
								" '-'  as unit1, '-'  as unit2, '-'  as unit2qty ";
			   } else {
				 $querydetail= $querydetail. 
								" select null As Journal_id ,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" null As Journal_type, null As Journal_folio, null As Journal_refgovernment, null As Journaldt_desc, ";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail."  Ledger_name, ledger_type, ".
								" null As Document, null As Project_code, null As Costcent_Code, JournalDt.Currency_code, null As Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0)) As CreditAmount, ".
								" null As Journaldt_Id, null As project_name, null As costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName, ".
								" null as Delivery_Reference,null as Sorder_Reference,'-' as itemdesc,'-' as Qty, ".
								" '-'  as unit1, '-'  as unit2, '-'  as unit2qty ";
			   }			   
			}

			$querydetail= $querydetail.
						   " from $DirSchema.JournalDt As JournalDt ".
						   " Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
						   " Inner join ledger on JournalDt.Ledger_number = ledger.Ledger_number ";

			if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
				$querydetail= $querydetail." 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) {
					$querydetail = $querydetail." 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) {
					$querydetail = $querydetail." 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,'%')";
				}					
										   
			} else{   
			  $querydetail= $querydetail." Left outer Join project on project.Project_code = Journal.Project_code ".
										 " Left outer Join costcent on costcent.Costcent_Code = Journal.Costcent_Code ";
									   
			}							   


			if ($ledgeroption==1) {
				$querydetail= $querydetail." where ledger.Ledger_number = '".$rowsjournal[0]."'  ";
			} else {
			    $querydetail= $querydetail." where ledger.document = '".$rowsjournal[0]."'  ";
			}
			$querydetail= $querydetail." and ( ($dateoption = 1 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')) ) or  ".
							"       ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d')) ) )  ".
							" and (($transaction = 1 and Journaldt_dbcd = 'C') or ($transaction = 2 and Journaldt_dbcd = 'D') or ($transaction = 3 and 1 = 1)) ".
							" 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 (($showunposted = 1 and journaldt_posted = 0 ) or ($showunposted = 0 and 1 = 1 )) ".
							" and (($cum = 0 and Upper(JournalDt.Currency_code) = Upper('".$rowsjournal[7]."')) or ($cum = 1 and 1 = 1))  ".
							" And ifNull(Journal.Journal_Type,'JV') <> 'CL' And ifNull(Journal.Journal_Type,'JV') <> 'SI' "; 
							
			if ($Module_BelongsToGroup == 1)				
				$querydetail= $querydetail." and ifNull(JournalDt.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ";
			
			if ($curr == 1 && !(empty($fromcur))  )
			  $querydetail= $querydetail." and Upper(JournalDt.Currency_code) = Upper('".$fromcur."') ";

			if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {  
				if ($parentprj == 1) {
					$querydetail= $querydetail." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(concat(upper(".$fromproject."),'%'), upper(ifNull(CstJour.project_code,'-1'))) ".
											   " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(concat(upper(".$fromcst."),'%'), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
				} else {
					$querydetail= $querydetail." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) ".
											   " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
				}

			}
			
			if ($RowsDirCount > 1 && $k != 1)	
				$querydetail= $querydetail." And ifNull(Journal.Journal_Type,'JV') <> 'OP' ";
			  
			if ($dtdate == 0 || ($prtcstjour == 0 && ($projectexist > 0 || $costcentexist > 0))) {
			  $querydetail= $querydetail." group by date_format(JournalDt.Journal_date,'%d/%m/%Y'),date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y'),";
			  if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document , ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type,JournalDt.Currency_code, Journaldt_Id  ";
			 }

			 //module delivery = 1
			if($module_delivery == 1){
				$querydetail= $querydetail." union ";
				if ($dtdate == 1)
			{
				  if ($prtcstjour == 0 && ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" journal.Journal_type as Journal_type, Journal_folio, Sale_Reference as Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document, cstjour.Project_code, cstjour.Costcent_Code, JournalDt.Currency_code, Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,item_description as itemdesc,if(Journaldt_dbcd = 'D',SaleDt_Quantity,-SaleDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, salesdt.Item_Unit2Qty as unit2qty ";  
			  }
			  else if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" journal.Journal_type as Journal_type, Journal_folio, Sale_Reference as Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
								
				
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}			
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document, cstjour.Project_code, cstjour.Costcent_Code, JournalDt.Currency_code, Journaldt_dbcd, ".
								" if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0) As DebitAmount, ".
								" if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,item_description as itemdesc,if(Journaldt_dbcd = 'D',SaleDt_Quantity,-SaleDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, salesdt.Item_Unit2Qty as unit2qty ";  
			  } else {
				 $querydetail= $querydetail. 
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" journal.Journal_type as Journal_type, Journal_folio, Sale_Reference as Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
					
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document,Journal.Project_code,Journal.Costcent_Code,JournalDt.Currency_code, Journaldt_dbcd, ".
								" if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0) As DebitAmount, ".
								" if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,item_description as itemdesc,if(Journaldt_dbcd = 'D',SaleDt_Quantity,-SaleDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, salesdt.Item_Unit2Qty as unit2qty ";
			  }			   
			} else {
			  
			   if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select null As Journal_id ,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" null As Journal_type, null As Journal_folio, null As Journal_refgovernment, null As Journaldt_desc,";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" null As Document, null As Project_code, null As Costcent_Code, JournalDt.Currency_code, null As Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As CreditAmount, ".
								" null As Journaldt_Id, null As project_name, null As costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName, ".
								" Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,item_description as itemdesc,if(Journaldt_dbcd = 'D',SaleDt_Quantity,-SaleDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, salesdt.Item_Unit2Qty as unit2qty ";
			   } else {
				 $querydetail= $querydetail. 
								" select null As Journal_id ,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" null As Journal_type, null As Journal_folio, null As Journal_refgovernment, null As Journaldt_desc, ";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail."  Ledger_name, ledger_type, ".
								" null As Document, null As Project_code, null As Costcent_Code, JournalDt.Currency_code, null As Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0)) As CreditAmount, ".
								" null As Journaldt_Id, null As project_name, null As costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName, ".
								" Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,item_description as itemdesc,if(Journaldt_dbcd = 'D',SaleDt_Quantity,-SaleDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, salesdt.Item_Unit2Qty as unit2qty ";
			   }			   
			}

			$querydetail= $querydetail.
						   " from $DirSchema.JournalDt As JournalDt ".
						   " Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
						   " Inner join ledger on JournalDt.Ledger_number = ledger.Ledger_number ";

			if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
				$querydetail= $querydetail." 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) {
					$querydetail = $querydetail." 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) {
					$querydetail = $querydetail." 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,'%')";
				}					
										   
			} else{   
			  $querydetail= $querydetail." Left outer Join project on project.Project_code = Journal.Project_code ".
										 " Left outer Join costcent on costcent.Costcent_Code = Journal.Costcent_Code ";
									   
			}							   
			
			$querydetail= $querydetail." left join sales on sales.journal_id  = journal.journal_id ".
									   " left join salesdt on salesdt.sale_id  = sales.sale_id ".
									   " left join deliverydt on deliverydt.deliverydt_id  = salesdt.RelatedDt_Id ".
									   " left join deliveries on deliveries.delivery_id  = deliverydt.delivery_id ".
									   " left join items on items.item_code = salesdt.item_code ";
	
			if ($ledgeroption==1) {
				$querydetail= $querydetail." where ledger.Ledger_number = '".$rowsjournal[0]."'  ";
			} else {
			    $querydetail= $querydetail." where ledger.document = '".$rowsjournal[0]."'  ";
			}
			$querydetail= $querydetail." and ( ($dateoption = 1 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')) ) or  ".
							"       ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d')) ) )  ".
							" and (($transaction = 1 and Journaldt_dbcd = 'C') or ($transaction = 2 and Journaldt_dbcd = 'D') or ($transaction = 3 and 1 = 1)) ".
							" 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.Journal_type in ($jvtype)) or ".
							"      ($alltypes = 0 and $exclude = 1 and $jvtypelen > 0 and journal.Journal_type not in ($jvtype)) ) ".
							" and (($showunposted = 1 and journaldt_posted = 0 ) or ($showunposted = 0 and 1 = 1 )) ".
							" and (($cum = 0 and Upper(JournalDt.Currency_code) = Upper('".$rowsjournal[7]."')) or ($cum = 1 and 1 = 1))  ".
							" And Journal.Journal_Type = 'SI' "; 
							
			if ($Module_BelongsToGroup == 1)				
				$querydetail= $querydetail." and ifNull(JournalDt.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ";
			
			if ($curr == 1 && !(empty($fromcur))  )
			  $querydetail= $querydetail." and Upper(JournalDt.Currency_code) = Upper('".$fromcur."') ";

			if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {  
				if ($parentprj == 1) {
					$querydetail= $querydetail." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(concat(upper(".$fromproject."),'%'), upper(ifNull(CstJour.project_code,'-1'))) ".
											   " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(concat(upper(".$fromcst."),'%'), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
				} else {
					$querydetail= $querydetail." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) ".
											   " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
				}

			}
			
						  
			if ($dtdate == 0 || ($prtcstjour == 0 && ($projectexist > 0 || $costcentexist > 0))) {
			  $querydetail= $querydetail." group by date_format(JournalDt.Journal_date,'%d/%m/%Y'),date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y'),";
			  if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document , ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type,JournalDt.Currency_code, Journaldt_Id  ";
			 }
				



			
				
			} //end module delivery = 1	
			
			  
			
			//module delivery = 0	
			if($module_delivery == 0){
				$querydetail= $querydetail." union ";
				if ($dtdate == 1)
			{
				  if ($prtcstjour == 0 && ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" journal.Journal_type as Journal_type, Journal_folio, Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document, cstjour.Project_code, cstjour.Costcent_Code, JournalDt.Currency_code, Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" deliveries.Delivery_Reference as Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,item_description as itemdesc,if(Journaldt_dbcd = 'D',DeliveryDt_Quantity,-DeliveryDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, deliverydt.Item_Unit2Qty as unit2qty "; 
			  }
			  else if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" journal.Journal_type as Journal_type, Journal_folio, Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
								
				
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}			
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document, cstjour.Project_code, cstjour.Costcent_Code, JournalDt.Currency_code, Journaldt_dbcd, ".
								" if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0) As DebitAmount, ".
								" if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" deliveries.Delivery_Reference as Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,concat(deliverydt.item_code,'-',item_description) as itemdesc,if(Journaldt_dbcd = 'D',DeliveryDt_Quantity,-DeliveryDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, deliverydt.Item_Unit2Qty as unit2qty "; 
			  } else {
				 $querydetail= $querydetail. 
								" select Journal.Journal_id As Journal_id,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" journal.Journal_type as Journal_type, Journal_folio, Journal_refgovernment, concat(Journaldt_desc,ifnull(Journaldt_desc1,'')) as Journaldt_desc, ";
					
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" JournalDt.Document,Journal.Project_code,Journal.Costcent_Code,JournalDt.Currency_code, Journaldt_dbcd, ".
								" if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0) As DebitAmount, ".
								" if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0) As CreditAmount, ".
								" Journaldt.Journaldt_Id, project.project_name, costcent.costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName,  ".
								" deliveries.Delivery_Reference as Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,concat(deliverydt.item_code,'-',item_description) as itemdesc,if(Journaldt_dbcd = 'D',DeliveryDt_Quantity,-DeliveryDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, deliverydt.Item_Unit2Qty as unit2qty ";
			  }			   
			} else {
			  
			   if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
			  
				 $querydetail= $querydetail.
								" select null As Journal_id ,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" null As Journal_type, null As Journal_folio, null As Journal_refgovernment, null As Journaldt_desc,";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type, ".
								" null As Document, null As Project_code, null As Costcent_Code, JournalDt.Currency_code, null As Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Cstjour_amount1, if($currbase2 = 1, Cstjour_amount2, Cstjour_amount)), Cstjour_amount), 0)) As CreditAmount, ".
								" null As Journaldt_Id, null As project_name, null As costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName, ".
								" deliveries.Delivery_Reference as Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,concat(deliverydt.item_code,'-',item_description) as itemdesc,if(Journaldt_dbcd = 'D',DeliveryDt_Quantity,-DeliveryDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, deliverydt.Item_Unit2Qty as unit2qty ";
			   } else {
				 $querydetail= $querydetail. 
								" select null As Journal_id ,date_format(JournalDt.Journal_date,'%d/%m/%Y') As Journal_date,date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As Journaldt_ValueDate, ".
								" null As Journal_type, null As Journal_folio, null As Journal_refgovernment, null As Journaldt_desc, ";
				if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document as Ledger_number, ";
				}
				$querydetail= $querydetail."  Ledger_name, ledger_type, ".
								" null As Document, null As Project_code, null As Costcent_Code, JournalDt.Currency_code, null As Journaldt_dbcd, ".
								" sum(if(Journaldt_dbcd = 'D', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0)) As DebitAmount, ".
								" sum(if(Journaldt_dbcd = 'C', if($curr = 0, if($currbase1 = 1, Journaldt_amount1, if($currbase2 = 1, Journaldt_amount2, Journaldt_amount)), Journaldt_amount), 0)) As CreditAmount, ".
								" null As Journaldt_Id, null As project_name, null As costcent_name, JournalDt.Journal_date As OrderByDate, '$DirSchema' As SchemaName, ".
								" deliveries.Delivery_Reference as Delivery_Reference,deliveries.Sorder_Reference as Sorder_Reference,concat(deliverydt.item_code,'-',item_description) as itemdesc,if(Journaldt_dbcd = 'D',DeliveryDt_Quantity,-DeliveryDt_Quantity) as Qty, ".
								" items.Item_UnitSalesDesc as unit1, items.Item_UnitPurchaseDesc as unit2, deliverydt.Item_Unit2Qty as unit2qty ";
			   }			   
			}

			$querydetail= $querydetail.
						   " from $DirSchema.JournalDt As JournalDt ".
						   " Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
						   " Inner join ledger on JournalDt.Ledger_number = ledger.Ledger_number ";

			if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {
				$querydetail= $querydetail." 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) {
					$querydetail = $querydetail." 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) {
					$querydetail = $querydetail." 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,'%')";
				}					
										   
			} else{   
			  $querydetail= $querydetail." Left outer Join project on project.Project_code = Journal.Project_code ".
										 " Left outer Join costcent on costcent.Costcent_Code = Journal.Costcent_Code ";
									   
			}							   
			
			$querydetail= $querydetail." left join deliveries on deliveries.journal_id  = deliverydt.journal_id ".
									   " left join deliverydt on deliverydt.delivery_id  = deliverydt.delivery_id ".
									   " left join items on items.item_code = deliverydt.item_code ";
	
			if ($ledgeroption==1) {
				$querydetail= $querydetail." where ledger.Ledger_number = '".$rowsjournal[0]."'  ";
			} else {
			    $querydetail= $querydetail." where ledger.document = '".$rowsjournal[0]."'  ";
			}
			$querydetail= $querydetail." and ( ($dateoption = 1 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')) ) or  ".
							"       ($dateoption = 2 and date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d')) ) )  ".
							" and (($transaction = 1 and Journaldt_dbcd = 'C') or ($transaction = 2 and Journaldt_dbcd = 'D') or ($transaction = 3 and 1 = 1)) ".
							" 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.Journal_type in ($jvtype)) or ".
							"      ($alltypes = 0 and $exclude = 1 and $jvtypelen > 0 and journal.Journal_type not in ($jvtype)) ) ".
							" and (($showunposted = 1 and journaldt_posted = 0 ) or ($showunposted = 0 and 1 = 1 )) ".
							" and (($cum = 0 and Upper(JournalDt.Currency_code) = Upper('".$rowsjournal[7]."')) or ($cum = 1 and 1 = 1))  ".
							" And Journal.Journal_Type = 'SI' "; 
							
			if ($Module_BelongsToGroup == 1)				
				$querydetail= $querydetail." and ifNull(JournalDt.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ";
			
			if ($curr == 1 && !(empty($fromcur))  )
			  $querydetail= $querydetail." and Upper(JournalDt.Currency_code) = Upper('".$fromcur."') ";

			if ($fromproject != 'null' || $fromcst != 'null' || $prtcostcent == 1 || $prtproject == 1 || $prtcstjour == 1 || ($projectexist > 0 || $costcentexist > 0)) {  
				if ($parentprj == 1) {
					$querydetail= $querydetail." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(concat(upper(".$fromproject."),'%'), upper(ifNull(CstJour.project_code,'-1'))) ".
											   " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(concat(upper(".$fromcst."),'%'), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
				} else {
					$querydetail= $querydetail." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) ".
											   " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(CstJour.Costcent_Code,'-1'))) ";
				}

			}
			
						  
			if ($dtdate == 0 || ($prtcstjour == 0 && ($projectexist > 0 || $costcentexist > 0))) {
			  $querydetail= $querydetail." group by date_format(JournalDt.Journal_date,'%d/%m/%Y'),date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y'),";
			  if ($ledgeroption==1) {
					$querydetail= $querydetail."	JournalDt.Ledger_number, "; 
				} else {
					$querydetail= $querydetail."	ledger.document , ";
				}
				$querydetail= $querydetail." Ledger_name, ledger_type,JournalDt.Currency_code, Journaldt_Id  ";
			 }
				
		} //end module delivery = 0
		
		
			if ($RowsDirCount > 1 && $j < $RowsDirCount)	
				$querydetail= $querydetail." Union ";
							
		}							
	}

	$querydetail= $querydetail." ) QDirMaster ";
	
							   //" Where DebitAmount !=0 Or CreditAmount !=0  ".
	if ($ledgeroption==1) {
		$querydetail= $querydetail." Order By  Ledger_number, if($cum=0, Currency_code, null), OrderByDate, Journal_refgovernment,journaldt_id  ";
	} else {
	    $querydetail= $querydetail." Order By  Ledger_number, if($cum=0, Currency_code, null), OrderByDate, Journal_refgovernment,journaldt_id  ";
	}
		
	$resultdetail=mysql_query($querydetail,$connection) or die(mysql_error());
	$CountRowsDt = mysql_num_rows($resultdetail);
	
	$break=0;
	
	 $PrevBalance  = 0;
	 $PrevBalance1 = 0;
	 $PrevBalance2 = 0;
	 $PrevBalanceCal = 0;
	 $Balance = 0;
	
	if ($prevbal == 1) {
		  $PrevBalance  = $rowsjournal[8];
		  $PrevBalance1 = $rowsjournal[9];
		  $PrevBalance2 = $rowsjournal[10];
		  $PrevBalanceCal = $rowsjournal[8];
	  }	
	 $VarCountRow=$VarCountRow+1;
	 //echo "xxxx".$prtwnac;
	 if (!isset($prtwnac)) {
		$prtwnac=0;
	}
	


		
	$worksheet->write(1,6,$CompanyName, $TitleFormat);
	if($COMPANY_CITY != '')  $worksheet->write(2,6,$CompanyName, $TitleFormat);
	$worksheet->write(3,6,$COMPANY_COUNTRY, $TitleFormat);
	
	$rows = 4;
	
	$PrevBalance  = 0;
	  $PrevBalance1 = 0;
	  $PrevBalance2 = 0;
	  $PrevBalanceCal = 0;
	  $Balance = 0;
	  
	  if ($prevbal == 1) {
		  $PrevBalance  = $rowsjournal[8];
		  $PrevBalance1 = $rowsjournal[9];
		  $PrevBalance2 = $rowsjournal[10];
		  $PrevBalanceCal = $rowsjournal[8];
	  }	  
	  
	  if ($currbase1 == 1 && $curr == 0 )
	    $PrevBalanceCal = $rowsjournal[9];
	  else if ($currbase2 == 1 && $curr == 0 )
	    $PrevBalanceCal = $rowsjournal[10];	
		
	  if ($prevbal == 1) 	
		$Balance = $PrevBalanceCal;
	
	  $resultdetail=mysql_query($querydetail,$connection) or die(mysql_error());
	  $closingBalance = 0;
	  $totalinvoice = 0;
	  $totaldebit = 0;
	  $totalcredit = 0;
	  $totalpayment = 0;
	  $totalReceipt = 0;
	  $tempref = '';
	while($rowsdetail=mysql_fetch_row($resultdetail)){
		if( ($rowsdetail[22] == '') || ($rowsdetail[3] == 'SI' && $tempref != $rowsdetail[22])){
		$closingBalance = $closingBalance + (round($rowsdetail[15],$DecimalBase) - round($rowsdetail[16],$DecimalBase));
		if($rowsdetail[3] == 'SI') $totalinvoice = $totalinvoice + (round($rowsdetail[15],$DecimalBase)- round($rowsdetail[16],$DecimalBase));
		if($rowsdetail[3] == 'DV') $totaldebit = $totaldebit + (round($rowsdetail[15],$DecimalBase) - round($rowsdetail[16],$DecimalBase));
		if($rowsdetail[3] == 'CV') $totalcredit = $totalcredit +(round($rowsdetail[15],$DecimalBase) - round($rowsdetail[16],$DecimalBase));
		if($rowsdetail[3] == 'PV') $totalpayment = $totalpayment +(round($rowsdetail[15],$DecimalBase) - round($rowsdetail[16],$DecimalBase));
		if($rowsdetail[3] == 'RV') $totalReceipt = $totalpayment +(round($rowsdetail[15],$DecimalBase) - round($rowsdetail[16],$DecimalBase));
		$tempref = $rowsdetail[22];
		}
	}
	$rows=$rows+1;
	$worksheet->setMerge( $rows ,0 , $rows , 1 );
	$worksheet->write($rows,0,"Statement of Account", $boldFormat);
	
	$worksheet->write($rows,10,"Opening Balance", $squareborderBolfFormat);
	 if($cum == 0) $worksheet->write($rows,11,format_currency($PrevBalance, $DecimalBase), $squareborderFormat);
	 else if ($currbase1 == 1 )   $worksheet->write($rows,11,format_currency($PrevBalance1, $DecimalBase), $squareborderFormat);
	 else  if ($currbase2 == 1 )  $worksheet->write($rows,11,format_currency($PrevBalance2, $DecimalBase), $squareborderFormat);
	
	$rows=$rows+1;
	
	$worksheet->setMerge( $rows ,0 , $rows , 1 );
	$worksheet->write($rows,0,"Customer Name:", $boldFormat);
	$worksheet->setMerge( $rows ,2 , $rows , 6 );
	$worksheet->write($rows,2, $rowsjournal[1], $regularFormat);
	
	$worksheet->write($rows,10,"Invoice Total", $squareborderBolfFormat);
	$worksheet->write($rows,11,format_currency($totalinvoice, $DecimalBase), $squareborderFormat);
	
	$rows=$rows+1;
	$worksheet->setMerge( $rows ,0 , $rows , 1 );	
	$worksheet->write($rows,0,"Customer Account Number:", $boldFormat);
	$worksheet->setMerge( $rows ,2 , $rows , 3 );
	$worksheet->write($rows,2, $rowsjournal[0], $regularFormat);
	
	$worksheet->write($rows,10,"Receipt Total", $squareborderBolfFormat);
	$worksheet->write($rows,11,format_currency($totalReceipt, $DecimalBase), $squareborderFormat);
	
	$rows=$rows+1;
	$worksheet->setMerge( $rows ,0 , $rows , 1 );	
	$worksheet->write($rows,0,"Customer Address:", $boldFormat);
	$worksheet->setMerge( $rows ,2 , $rows , 3 );
	$worksheet->write($rows,2, $rowsjournal[6], $regularFormat);
	
	$worksheet->write($rows,10,"Payment Total", $squareborderBolfFormat);
	$worksheet->write($rows,11,format_currency($totalpayment, $DecimalBase), $squareborderFormat);
	
	$rows=$rows+1;
	$worksheet->setMerge( $rows ,0 , $rows , 1 );	
	$worksheet->write($rows,0,"From Date:", $boldFormat);
	$worksheet->write($rows,2, $dispfromdate, $regularFormat);
	
	$worksheet->write($rows,10,"Credit Note Total", $squareborderBolfFormat);
	$worksheet->write($rows,11,format_currency($totalcredit, $DecimalBase), $squareborderFormat);
	
	$rows=$rows+1;
	$worksheet->setMerge( $rows ,0 , $rows , 1 );	
	$worksheet->write($rows,0,"To Date:", $boldFormat);
	$worksheet->write($rows,2, $dispfromdate, $regularFormat);
	
	$worksheet->write($rows,10,"Debit Note Total", $squareborderBolfFormat);
	$worksheet->write($rows,11,format_currency($totaldebit, $DecimalBase), $squareborderFormat);
	
	$rows=$rows+1;
		
	$worksheet->write($rows,10,"Closing balance", $squareborderBolfFormat);
	$worksheet->write($rows,11,format_currency($closingBalance, $DecimalBase), $squareborderFormat);
	


$rows=$rows+1;

					
	//echo $querydetail; exit;

	$resultdetail=mysql_query($querydetail,$connection) or die(mysql_error());
	$CountRowsDt = mysql_num_rows($resultdetail);
	
	
	if ($CountRowsDt > 0) {
		$rows=$rows+1;
		
			
		 $rows=$rows+1;
		 $cols=0;
		
		$worksheet->setColumn ($cols,$cols, 10);
		$worksheet->write($rows,$cols,"Invoice Date", $squareborderBolfFormat);
		
		$cols=$cols+1;
		$worksheet->setColumn ($cols,$cols, 30);
		$worksheet->write($rows,$cols,"Description", $squareborderBolfFormat);  		  
			
		$cols=$cols+1;
		$worksheet->write($rows,$cols,"Invoice/Receipt No", $squareborderBolfFormat);		
		 
		$cols=$cols+1;
		$worksheet->write($rows,$cols,"Sales Order No", $squareborderBolfFormat);
		
		$cols=$cols+1;
		$worksheet->write($rows,$cols,"Delivery Note", $squareborderBolfFormat);	    
		

		$cols=$cols+1;
		$worksheet->setColumn ($cols,$cols, 20);
		$worksheet->write($rows,$cols,"Product", $squareborderBolfFormat);

		$cols=$cols+1;
		$worksheet->write($rows,$cols,"Unit1", $squareborderBolfFormat);
		
		$cols=$cols+1;
		$worksheet->write($rows,$cols,"Quantity", $squareborderBolfFormat);
		
		$cols=$cols+1;
		$worksheet->write($rows,$cols,"Unit2", $squareborderBolfFormat);

		$cols=$cols+1;
		$worksheet->write($rows,$cols,"Unit2 Quantity", $squareborderBolfFormat);

		$cols=$cols+1;
		$worksheet->setColumn ($cols,$cols, 20);
		$worksheet->write($rows,$cols,"Amount Invoice", $squareborderBolfFormat);

		$cols=$cols+1;
		$worksheet->setColumn ($cols,$cols, 20);
		$worksheet->write($rows,$cols,"Amount Received", $squareborderBolfFormat);	
		
		$cols=$cols+1;
		$worksheet->setColumn ($cols,$cols, 20);
		$worksheet->write($rows,$cols,"Balance", $squareborderBolfFormat);
	}
$rows=$rows+1; 
	
	$tempref = '';
	$resultdetail=mysql_query($querydetail,$connection) or die(mysql_error());
	while($rowsdetail=mysql_fetch_row($resultdetail)){
		$cols=0;
		$rows=$rows+1; 
		if( ($rowsdetail[22] == '') || ($rowsdetail[3] == 'SI' && $tempref != $rowsdetail[22])){
		$SumCreditAmount = $SumCreditAmount + round($rowsdetail[16],$DecimalBase);
		$SumDebitAmount  = $SumDebitAmount  + round($rowsdetail[15],$DecimalBase);
		$Balance = $Balance + (round($rowsdetail[15],$DecimalBase) - round($rowsdetail[16],$DecimalBase));
		
		
		$worksheet->write($rows,$cols, $rowsdetail[1], $regularFormat);
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols, $rowsdetail[6], $regularFormat);
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols, $rowsdetail[5], $regularFormat);
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols, $rowsdetail[23], $regularFormat);
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols, $rowsdetail[22], $regularFormat);
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols, '-', $regularFormat);
		$cols=$cols+1;
		$worksheet->write($rows,$cols, '-', $regularFormat);
		$cols=$cols+1;
		$worksheet->write($rows,$cols,'-', $regularFormat);
		$cols=$cols+1;
		$worksheet->write($rows,$cols,'-', $regularFormat);
		$cols=$cols+1;
		$worksheet->write($rows,$cols,'-', $regularFormat);
		$cols=$cols+1;

		
		$worksheet->write($rows,$cols, format_currency($rowsdetail[15], $DecimalBase), $regularFormat);
		
		$cols=$cols+1;
		$worksheet->write($rows,$cols, format_currency($rowsdetail[16], $DecimalBase), $regularFormat);
		
		$cols=$cols+1;
		$worksheet->write($rows,$cols, format_currency($Balance, $DecimalBase), $regularFormat);
		
		} 
	
	if( ($rowsdetail[22] != '') || ($rowsdetail[3] == 'SI' && $tempref == $rowsdetail[22])){
		$cols = 0;
		$rows=$rows+1; 
		
		$cols=$cols+1;
		
		$cols=$cols+1;
		
		$cols=$cols+1;
		
		$cols=$cols+1;
		
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols, $rowsdetail[24], $regularFormat);
		$cols=$cols+1;
		$worksheet->write($rows,$cols, $rowsdetail[26], $regularFormat);
		$cols=$cols+1;
		if($rowsdetail[25] > 0 || $rowsdetail[25] == '-')  $worksheet->write($rows,$cols,$rowsdetail[25], $regularFormat);
		else $worksheet->write($rows,$cols,"(".trim($rowsdetail[25],'-').")", $regularFormat);
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols,$rowsdetail[27], $regularFormat);
		$cols=$cols+1;
		
		$worksheet->write($rows,$cols,$rowsdetail[28], $regularFormat);
		$cols=$cols+1;
		
	
	}
	$tempref = $rowsdetail[22];
	
}
	
 

}



$workbook->close();
	
				
			?>	