<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}


print <<< HERE
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<TITLE> Trial Balance Report </TITLE>
<link rel="stylesheet" type="text/css" media="all" href="css-v2/style.css">
<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

			 
table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }
				 
table.master td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }
table.master td.detail { font-size: 12px; 
                         font-weight: bold;
						 font-style: italic;
                         font-family:Times-New-Roman;
						}
						
table.master td.detail1 { font-size: 12px; 
                          font-style: italic;
                          font-family:Times-New-Roman;
						}						
				 
			   
td.totalborder {
                font-weight: bold;
                border-width: 1px;
				padding: 1px;
				border-style: solid;
				border-color: #666666;
               }			   
				
table.detail th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }				 
				
table.detail td { font-size: 11px; 
                  font-family:Times-New-Roman;
                 }	
				 
table.group td { font-size: 12px; 
                 font-weight: bold;
                 font-family:Times-New-Roman;
               }				 				 

			   
table.totals td { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                 }					  
				 

</style>




</HEAD>


HERE;
include("config.inc.php");
include("function.inc.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");
if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}
//maroun 30.03.2011
$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';

$cum1 = $cum;
if($cum1 == '') $cum1 = 0;

$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, Company_Name As CompanyName, report_color1,report_color2 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 ".$_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_ConfirmJV,0) as Module_ConfirmJV From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

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

$querycurrency="select Currency_code from currency order by currency_code";

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);



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


$curarray = Array();
$i=0;
$resultcurrency=mysql_query($querycurrency,$connection) or die(mysql_error());
while($curf=mysql_fetch_row($resultcurrency)){
	$curarray[$i] = $curf[0];
	$i++;
}

if (empty($fromledger))   
   $fromledger = 'null';
else
   $fromledger = "'".$fromledger."'";   
   
if (empty($toledger))   
   $toledger = 'null';
else
   $toledger = "'".$toledger."'";      

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

$excludecst = "".$excludecst;    
if ($excludecst == '')   
   $excludecst = 'null';
else
   $excludecst = "'".$excludecst."'";   
     

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($fromacctype))   
   $fromacctype = 'null';
else
   $fromacctype = "'".$fromacctype."'"; 
   
if (empty($frombustype))   
   $frombustype = 'null';
else
   $frombustype = "'".$frombustype."'";    

$cltcst = "".$cltcst; 
if ($cltcst == '')   
   $cltcst = 'null';
else
   $cltcst = "'".$cltcst."'";       

$cltprj = "".$cltprj;      
if ($cltprj == '')   
   $cltprj = 'null';
else
   $cltprj = "'".$cltprj."'";  

if ($fromsalesman=='') $fromsalesman = 'null';
else $fromsalesman = "'".$fromsalesman."'"; 
if ($tosalesman=='') $tosalesman = 'null';
else $tosalesman = "'".$tosalesman."'";    

print <<< HERE

HERE;
echo "<div id=''>";
	


	ShowReportTitle('Trial Balance','landscape', $connection);

echo"</div>";

print <<< HERE
<tr><td align='left'><table class="report-master" width="100%" border="0" align="center" >
<tr>
  <td width="7%" valign='top'><b>Date</b></td>
  <td width="5%"><b>From</b></td>
  <td width="10%">$dispfromdate </td>
  <td width="5%"><b>To </b></td>
  <td width="10%">$disptodate</td>
  <td width="7%" valign='top'><b>Ledger</b></td>
  <td width="5%"><b>From </b></td>
  <td width="15%">$dispfromledger </td>
  <td width="5%"><b>To </b></td>
  <td width="31%">$disptoledger </td>
</tr>

</table></td></tr>
HERE;

echo "<tr><td colspan=10><table  border='0' width='100%' align='center' class='report-master' style='table-layout:fixed; border-collapse:collapse;' cellpadding='0' cellspacing='0'>
	  <col width=80>
	  <col width=250>
	  <col width=70>";
$resultcurrency=mysql_query($querycurrency,$connection) or die(mysql_error());
while($curf=mysql_fetch_row($resultcurrency)){
echo" <col width=120>";
}
echo"<thead>
<tr>
    <th align='left'>Ledger Desc.</th>
	<th align='left'>&nbsp;</th>
    <th>C/V</th>";

$resultcurrency=mysql_query($querycurrency,$connection) or die(mysql_error());
while($curf=mysql_fetch_row($resultcurrency)){
	echo "<th>$curf[0]</th>";
}
	echo"<th colspan='10'>&nbsp;</th>";
	
echo"</tr></thead>";

if ($prtprjcst == 1) {
	echo"<tr>";
	echo"<th> * Project </th>";
	echo"</tr>";
}
if ($prtprjcst2 == 1) {
	echo"<tr>";
	echo"<th> ** Cost center </th>";
	echo"</tr>";
}

$currbase1 = 0;
$currbase2 = 0;
print <<<HERE

 
HERE;
if ($fromproject != 'null' || $fromcst != 'null' || $excludecst != 'null' || $prtprjcst == 1  || $prtprjcst2 == 1) {
  
    $querydetail= $querydetail." select Ledger.Ledger_number, Ledger.Ledger_name, Ledger.Ledger_grpdet, Ledger.Ledger_RequiredDetail,  ".
				   " if('$currbase1' = '1', '".$Base1."', Null) CvCurrBase1, if('$currbase2' = '1', '".$Base2."', Null) CvCurrBase2, ".
				   " JournalDt.Document,ifNull(CstJour.Project_code, '{None}') Project_code, ifNull(project.project_name, '{None}') project_name,ifNull(CstJour.Costcent_Code, '{None}') Costcent_Code, ifNull(costcent.costcent_name, '{None}') costcent_name,if($curr!=0, JournalDt.Currency_code, null) Currency_code,  ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount, Journaldt_amount) , 0),0)) PrevDebitAmount, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), 0),0)) PrevDebitAmountBase1, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), 0),0)) PrevDebitAmountBase2, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is not null, Cstjour_amount, Journaldt_amount) , 0),0)) PrevCreditAmount, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is not null, Cstjour_amount1, Journaldt_amount1), 0),0)) PrevCreditAmountBase1, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'C', if(CstJour.Cst_increment is not null, Cstjour_amount2, Journaldt_amount2), 0),0)) PrevCreditAmountBase2, ".
				   " Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",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) ) PrevBalance, ".
				   " Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",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) ) PrevBalanceBase1, ".
				   " Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",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) ) PrevBalanceBase2, ".
				   " 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) , 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', if(CstJour.Cst_increment is not null, Cstjour_amount1, 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', if(CstJour.Cst_increment is not null, Cstjour_amount2, 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', if(CstJour.Cst_increment is not null, Cstjour_amount, 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', if(CstJour.Cst_increment is not null, Cstjour_amount1, 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', if(CstJour.Cst_increment is not null, Cstjour_amount2, 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', 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, ".
				   " Sum(if(CstJour.Project_code is null,0,1)) countprj, Sum(if(CstJour.Costcent_Code is null,0,1)) countcst ";


} else {
    $querydetail= $querydetail." select Ledger.Ledger_number, Ledger.Ledger_name, Ledger.Ledger_grpdet, Ledger.Ledger_RequiredDetail,  ".
				   " if('$currbase1' = '1', '".$Base1."', Null) CvCurrBase1, if('$currbase2' = '1', '".$Base2."', Null) CvCurrBase2, ".
				   " JournalDt.Document,Null Project_code, Null project_name,Null Costcent_Code, Null costcent_name, if($curr!=0, JournalDt.Currency_code, null) Currency_code,  ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', Journaldt_amount , 0),0)) PrevDebitAmount, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', Journaldt_amount1, 0),0)) PrevDebitAmountBase1, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', Journaldt_amount2, 0),0)) PrevDebitAmountBase2, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'C', Journaldt_amount , 0),0)) PrevCreditAmount, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'C', Journaldt_amount1, 0),0)) PrevCreditAmountBase1, ".
				   " sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'C', Journaldt_amount2, 0),0)) PrevCreditAmountBase2, ".
				   " Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', Journaldt_amount, (-1)*Journaldt_amount),0) ) PrevBalance, ".
				   " Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', Journaldt_amount1, (-1)*Journaldt_amount1),0) ) PrevBalanceBase1, ".
				   " Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(Journaldt_dbcd = 'D', Journaldt_amount2, (-1)*Journaldt_amount2),0) ) PrevBalanceBase2, ".
				   " 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, ".
				   " Sum(0) countprj, Sum(0) countcst ";
				   
}			   
 

$querydetail= $querydetail.
			   " from JournalDt ".
			   " Left Outer join Ledger on JournalDt.Ledger_number = Ledger.Ledger_number ".
			   " Inner join Journal on JournalDt.Journal_id = Journal.Journal_id ";

				//maroun
				if ($options==4)
				$querydetail= $querydetail." inner join Clients on Clients.Ledger_number = JournalDt.Ledger_Number ";				
				if ($options==4)
				{
				$querydetail= $querydetail."	And upper(ifNull(Clients.CostCenter_Code,'-1')) like ifNull(upper(".$cltcst."), upper(ifNull(Clients.CostCenter_Code,'-1')))   ".
				"   And upper(ifNull(Clients.Project_Code,'-1')) like ifNull(upper(".$cltprj."), upper(ifNull(Clients.Project_Code,'-1')))   ";
				}
				
						//maroun
						if (($options == 4 ) && $projectmasterexist > 0) {	
						  $querydetail= $querydetail." inner join ( select distinct Masterproject from ".$_SESSION['dbLabel'].".loginstk ".
														 "			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
														 "             and (Masterproject is not null and Masterproject != '') ".
														 "			 ) PrjMasterQry on Clients.project_code like concat(PrjMasterQry.Masterproject,'%') ";
						
						}
			
						//maroun
						if (($options == 4 ) && $costcentermasterexist > 0) {	
						  $querydetail= $querydetail." inner join ( select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
														 "			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
														 "             and (Mastercostcenter is not null and Mastercostcenter != '') ".
														 "			 ) CstMasterQry on Clients.CostCenter_Code like concat(CstMasterQry.Mastercostcenter,'%') ";
						
						}
						//maroun
					if (($options == 4) && $salesmenexist > 0) {
						$querydetail = $querydetail . " 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 ($fromproject != 'null' || $fromcst != 'null' || $excludecst != 'null' || $prtprjcst == 1  || $prtprjcst2 == 1) {
	$querydetail= $querydetail." 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 ".$_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,'%')";
	}

} 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 ($options == 7) 
  $querydetail= $querydetail." Inner Join acctypesetup on JournalDt.Ledger_number like concat(acctypesetup.Ledger_Number,'%') ";
 
if ($options == 8)
	$querydetail= $querydetail." Inner Join Clients on Clients.Ledger_number = JournalDt.Ledger_Number ";				 
	
/*if ($ledgerexist > 0) {
	$querydetail = $querydetail." 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 JournalDt.Ledger_Number like concat(Q3.ledger_number,'%') ) or ($ledgerinclude = 0 and JournalDt.Ledger_Number Not like concat(Q3.ledger_number,'%') ) or JournalDt.Ledger_Number is null) ";	
}*/

$querydetail= $querydetail.  
				" where ( ($prtdoc = 0 and ledger.Ledger_number between ifNull(".$fromledger.",ledger.Ledger_number) and ifNull(". $toledger.",ledger.Ledger_number) ) or ".
				"         ($prtdoc = 1 and ledger.Document between ifNull(".$fromledger.",ledger.Document) and ifNull(". $toledger.",ledger.Document) ) ) ".
				" and ( ($prtselper = 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  ".
				"       ($prtselper = 0 and 1 = 1 ) )  ".
				//" and ((ledger.Ledger_inactive = 0 and 1 = 1) or (ledger.Ledger_inactive = 1 and date_format(ledger.Ledger_inactivedate,'%Y%m%d') >= date_format(". $fromdate.",'%Y%m%d'))) ".
				" and (($alltypes = 1 and 1 = 1) or ($alltypes = 0 and $jvtypelen = 0 and 1 = 1) or ($alltypes = 0 and $exclude = 0 and $jvtypelen > 0 and Journal_type in ($jvtype)) or ($alltypes = 0 and $exclude = 1 and $jvtypelen > 0 and Journal_type not in ($jvtype)) ) ".
				" and (($options In (1,7,8) and 1 = 1) or ($options = 4 and ledger.ledger_type = 1) or ($options = 5 and ledger.ledger_type = 2) or ($options In (2,3,6) and ledger.ledger_type In (1,2)) )  ";

if ($Module_BelongsToGroup == 1)				
	$querydetail= $querydetail." and ifNull(ledger.Ledger_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ".
							   " and ifNull(JournalDt.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0)  ";
		
if($ledgerinclude== 1 && $ledgerexist > 0) {
	$querydetail = $querydetail." And Exists ( select 1 from ".$_SESSION['dbLabel'].".ledgerrepstk ".
								"              where ledgerrepstk.user_account='".$_SESSION['useraccount']."' ".
								"              and (ledger_number is not null and ledger_number != '') ".
								"              and JournalDt.ledger_number like concat(ledgerrepstk.ledger_number,'%') ".
								"            ) ";
}
if($ledgerinclude== 0 && $ledgerexist > 0){
	$querydetail = $querydetail." 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 JournalDt.ledger_number like concat(ledgerrepstk.ledger_number,'%') ".
								"                ) ";	
}
		

if ($options == 4){
	$querydetail = $querydetail." and ifNull(Clients.Salesmen_Code,'-1') between ifNull(".$fromsalesman.",ifNull(Clients.Salesmen_Code,'-1')) and ifNull(". $tosalesman.",ifNull(Clients.Salesmen_Code,'-1')) ";
}
if ($options == 7) 
  $querydetail= $querydetail." and acctypesetup.type_code = ifNull(".$fromacctype.",acctypesetup.type_code) ";				
  
if ($options == 8) 
	$querydetail= $querydetail." and ifNull(Clients.Client_BusinessType, -1) = ifNull(".$frombustype.",ifNull(Clients.Client_BusinessType, -1)) ";

if ($curr == 1 && !(empty($fromcur))  )
  $querydetail= $querydetail." and Upper(JournalDt.Currency_code) = Upper('".$fromcur."') ";
  
if($Module_ConfirmJV == 1) $querydetail= $querydetail." and ifnull(Journal_Confirmed,0) = 1 ";

if ($fromproject != 'null' || $fromcst != 'null' || $excludecst != 'null' || $prtprjcst == 1  || $prtprjcst2 == 1) {  

  $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'))) ".
							 " and ((".$excludecst." is not null and upper(ifNull(CstJour.Costcent_Code,'-1')) Not Like upper(".$excludecst.")) or ($excludecst is null and 1 = 1) ) ";
							 //" and ((CstJour.Cst_increment is not null and (($currbase1 = 1 and ifNull(Cstjour_amount1,0) != 0) or ($currbase2 = 1 and ifNull(Cstjour_amount2,0) != 0) or ($curr = 1 and ifNull(Cstjour_amount,0) != 0))) or ".
							 //"      (CstJour.Cst_increment is null and (($currbase1 = 1 and ifNull(Journaldt_amount1,0) != 0) or ($currbase2 = 1 and ifNull(Journaldt_amount2,0) != 0) or ($curr = 1 and ifNull(Journaldt_amount,0) != 0))) ) ";
} 
/*else {
  $querydetail= $querydetail." and (($currbase1 = 1 and ifNull(Journaldt_amount1,0) != 0) or ($currbase2 = 1 and ifNull(Journaldt_amount2,0) != 0) or ($curr = 1 and ifNull(Journaldt_amount,0) != 0)) ";
}*/
  
/*$querydetail= $querydetail." Group By Ledger.Ledger_number, Ledger.Ledger_name, JournalDt.Currency_code ".
                           " order by  Ledger.Ledger_number ";*/

//echo $querydetail;exit;
$queryjournal=  " select Q3.Ledger_number, Q3.Ledger_name, Q3.Document, Q3.Ledger_grpdet, ".
				" Q3.Ledger_RequiredDetail, Q3.CvCurrBase1,Q3.CvCurrBase2, Q3.Project_code, ".
				" Q3.project_name,Q3.Costcent_Code, Q3.costcent_name,Q3.Currency_code, ".
				" Q3.PrevDebitAmount, Q3.PrevDebitAmountBase1, Q3.PrevDebitAmountBase2, ".
				" Q3.PrevCreditAmount, Q3.PrevCreditAmountBase1, Q3.PrevCreditAmountBase2, ".
				" Q3.PrevBalance, Q3.PrevBalanceBase1, Q3.PrevBalanceBase2, ".
				" Q3.DebitAmount, Q3.DebitAmountBase1, Q3.DebitAmountBase2, ".
				" Q3.CreditAmount, Q3.CreditAmountBase1, Q3.CreditAmountBase2, ".
				" Q3.Balance, Q3.BalanceBase1, Q3.BalanceBase2, ".
				" Q3.ActBalance, Q3.ActBalanceBase1, Q3.ActBalanceBase2, Q3.Ord, Q3.CountPrj, Q3.CountCst ".
				" from ".
				" ( ".
				" select Q1.Ledger_number, Q1.Ledger_name, Q1.Document, Q1.Ledger_grpdet, ".
			    " Q1.Ledger_RequiredDetail, Q2.CvCurrBase1,Q2.CvCurrBase2, ". 
				" Q2.Project_code, Q2.project_name,Q2.Costcent_Code, Q2.costcent_name,Q2.Currency_code, ".
				" Sum(Q2.PrevDebitAmount) PrevDebitAmount ,  Sum(Q2.PrevDebitAmountBase1) PrevDebitAmountBase1,   Sum(Q2.PrevDebitAmountBase2) PrevDebitAmountBase2, ".
				" Sum(Q2.PrevCreditAmount) PrevCreditAmount, Sum(Q2.PrevCreditAmountBase1) PrevCreditAmountBase1, Sum(Q2.PrevCreditAmountBase2) PrevCreditAmountBase2, ".
				" Sum(Q2.PrevBalance) PrevBalance,           Sum(Q2.PrevBalanceBase1) PrevBalanceBase1,           Sum(Q2.PrevBalanceBase2) PrevBalanceBase2, ".
				" Sum(Q2.DebitAmount) DebitAmount,           Sum(Q2.DebitAmountBase1) DebitAmountBase1,           Sum(Q2.DebitAmountBase2) DebitAmountBase2, ".
				" Sum(Q2.CreditAmount) CreditAmount,         Sum(Q2.CreditAmountBase1) CreditAmountBase1,         Sum(Q2.CreditAmountBase2) CreditAmountBase2, ".
				" Sum(Q2.Balance) Balance,                   Sum(Q2.BalanceBase1) BalanceBase1,                   Sum(Q2.BalanceBase2) BalanceBase2, ".
				" Sum(Q2.PrevBalance + Q2.Balance) ActBalance, Sum(Q2.PrevBalanceBase1 + Q2.BalanceBase1) ActBalanceBase1, Sum(Q2.PrevBalanceBase2 + Q2.BalanceBase2) ActBalanceBase2, ".
				" 2 As Ord, Sum(Q2.countprj) CountPrj, Sum(Q2.countcst) CountCst ".
				" from ".
				" ( select Ledger.Ledger_number, Ledger.Ledger_name, Ledger.Document, Ledger.Ledger_grpdet, ".
				"   Ledger.Ledger_RequiredDetail ".
				"   from Ledger ";
							
								

				
				$queryjournal= $queryjournal."   where ( ($prtdoc = 0 and ledger.Ledger_number between ifNull(".$fromledger.",ledger.Ledger_number) and ifNull(". $toledger.",ledger.Ledger_number) ) or ".
											 "           ($prtdoc = 1 and ledger.Document between ifNull(".$fromledger.",ledger.Document) and ifNull(". $toledger.",ledger.Document) ) ) ";
											 //"     and ((ledger.Ledger_inactive = 0 and 1 = 1) or (ledger.Ledger_inactive = 1 and date_format(ledger.Ledger_inactivedate,'%Y%m%d') >= date_format(". $fromdate.",'%Y%m%d'))) ";
				
				$queryjournal= $queryjournal." ) Q1, ".
				" (   ";
				
$queryjournal= $queryjournal.$querydetail;
$queryjournal= $queryjournal." Group By Ledger.Ledger_number, Ledger.Ledger_name, Currency_code ".
                             " order by  Ledger.Ledger_number ";
$queryjournal= $queryjournal." ) Q2 ".
							 //" Where Q2.Ledger_number like concat(Q1.Ledger_number,'%') ".
							 " Where ((Q1.Ledger_grpdet = 1 and Q2.Ledger_number like concat(Q1.Ledger_number,'%')) or (Q1.Ledger_grpdet != 1 and Q2.Ledger_number = Q1.Ledger_number)) ".
							 " Group by Q1.Ledger_number, Q1.Ledger_name, Q1.Document, Q1.Ledger_grpdet, if(Q1.Ledger_grpdet=0, Q2.Currency_code, null) ";
							 
							 
if ($curr == 1) {
	$queryjournal= $queryjournal." union ".
								 " select Q2.Ledger_number, Q2.Ledger_name, Q2.Document, Q2.Ledger_grpdet, ".
								 " Q2.Ledger_RequiredDetail, Q2.CvCurrBase1,Q2.CvCurrBase2, ". 
								 " Q2.Project_code, Q2.project_name,Q2.Costcent_Code, Q2.costcent_name, Null Currency_code, ".
								 " Sum(Q2.PrevDebitAmount) PrevDebitAmount ,  Sum(Q2.PrevDebitAmountBase1) PrevDebitAmountBase1,   Sum(Q2.PrevDebitAmountBase2) PrevDebitAmountBase2, ".
								 " Sum(Q2.PrevCreditAmount) PrevCreditAmount, Sum(Q2.PrevCreditAmountBase1) PrevCreditAmountBase1, Sum(Q2.PrevCreditAmountBase2) PrevCreditAmountBase2, ".
								 " Sum(Q2.PrevBalance) PrevBalance,           Sum(Q2.PrevBalanceBase1) PrevBalanceBase1,           Sum(Q2.PrevBalanceBase2) PrevBalanceBase2, ".
								 " Sum(Q2.DebitAmount) DebitAmount,           Sum(Q2.DebitAmountBase1) DebitAmountBase1,           Sum(Q2.DebitAmountBase2) DebitAmountBase2, ".
								 " Sum(Q2.CreditAmount) CreditAmount,         Sum(Q2.CreditAmountBase1) CreditAmountBase1,         Sum(Q2.CreditAmountBase2) CreditAmountBase2, ".
								 " Sum(Q2.Balance) Balance,                   Sum(Q2.BalanceBase1) BalanceBase1,                   Sum(Q2.BalanceBase2) BalanceBase2, ".
								 " Sum(Q2.PrevBalance + Q2.Balance) ActBalance, Sum(Q2.PrevBalanceBase1 + Q2.BalanceBase1) ActBalanceBase1, Sum(Q2.PrevBalanceBase2 + Q2.BalanceBase2) ActBalanceBase2, ".
								 " 1 As Ord, Sum(Q2.countprj) CountPrj, Sum(Q2.countcst) CountCst ".
								 " from ( ";

	$queryjournal= $queryjournal.$querydetail;							 
	$queryjournal= $queryjournal." Group By Ledger.Ledger_number, Ledger.Ledger_name, Currency_code ".
								 " order by  Ledger.Ledger_number ";
	$queryjournal= $queryjournal." ) Q2 ".
								 " Group by Q2.Ledger_number, Q2.Ledger_name, Q2.Document, Q2.Ledger_grpdet ";
}							 
							 
if($prtprjcst == 1  || $prtprjcst2 == 1) {							 
	$queryjournal= $queryjournal." union ".
								 " select Q2.Ledger_number, Q2.Ledger_name, Q2.Document, Q2.Ledger_grpdet, ".
								 " Q2.Ledger_RequiredDetail, Q2.CvCurrBase1,Q2.CvCurrBase2, ". 
								 " Q2.Project_code, Q2.project_name,Q2.Costcent_Code, Q2.costcent_name, Null Currency_code, ".
								 " Sum(Q2.PrevDebitAmount) PrevDebitAmount ,  Sum(Q2.PrevDebitAmountBase1) PrevDebitAmountBase1,   Sum(Q2.PrevDebitAmountBase2) PrevDebitAmountBase2, ".
								 " Sum(Q2.PrevCreditAmount) PrevCreditAmount, Sum(Q2.PrevCreditAmountBase1) PrevCreditAmountBase1, Sum(Q2.PrevCreditAmountBase2) PrevCreditAmountBase2, ".
								 " Sum(Q2.PrevBalance) PrevBalance,           Sum(Q2.PrevBalanceBase1) PrevBalanceBase1,           Sum(Q2.PrevBalanceBase2) PrevBalanceBase2, ".
								 " Sum(Q2.DebitAmount) DebitAmount,           Sum(Q2.DebitAmountBase1) DebitAmountBase1,           Sum(Q2.DebitAmountBase2) DebitAmountBase2, ".
								 " Sum(Q2.CreditAmount) CreditAmount,         Sum(Q2.CreditAmountBase1) CreditAmountBase1,         Sum(Q2.CreditAmountBase2) CreditAmountBase2, ".
								 " Sum(Q2.Balance) Balance,                   Sum(Q2.BalanceBase1) BalanceBase1,                   Sum(Q2.BalanceBase2) BalanceBase2, ".
								 " Sum(Q2.PrevBalance + Q2.Balance) ActBalance, Sum(Q2.PrevBalanceBase1 + Q2.BalanceBase1) ActBalanceBase1, Sum(Q2.PrevBalanceBase2 + Q2.BalanceBase2) ActBalanceBase2, ".
								 " 3 As Ord, Sum(Q2.countprj) CountPrj, Sum(Q2.countcst) CountCst ".
								 " from ( ";

	$queryjournal= $queryjournal.$querydetail;
	$queryjournal= $queryjournal." Group By Ledger.Ledger_number, Ledger.Ledger_name, Currency_code, CstJour.Project_code, project.project_name ".
								 " order by  Ledger.Ledger_number, project.project_name ";
	$queryjournal= $queryjournal." ) Q2 ".
								 " Group by Q2.Ledger_number, Q2.Ledger_name, Q2.Document, Q2.Ledger_grpdet, Q2.Project_code, Q2.project_name ".
								 " Having Sum(Q2.Balance) != 0 ".
								 " union ".
								 " select Q2.Ledger_number, Q2.Ledger_name, Q2.Document, Q2.Ledger_grpdet, ".
								 " Q2.Ledger_RequiredDetail, Q2.CvCurrBase1,Q2.CvCurrBase2, ". 
								 " Q2.Project_code, Q2.project_name,Q2.Costcent_Code, Q2.costcent_name, Null Currency_code, ".
								 " Sum(Q2.PrevDebitAmount) PrevDebitAmount ,  Sum(Q2.PrevDebitAmountBase1) PrevDebitAmountBase1,   Sum(Q2.PrevDebitAmountBase2) PrevDebitAmountBase2, ".
								 " Sum(Q2.PrevCreditAmount) PrevCreditAmount, Sum(Q2.PrevCreditAmountBase1) PrevCreditAmountBase1, Sum(Q2.PrevCreditAmountBase2) PrevCreditAmountBase2, ".
								 " Sum(Q2.PrevBalance) PrevBalance,           Sum(Q2.PrevBalanceBase1) PrevBalanceBase1,           Sum(Q2.PrevBalanceBase2) PrevBalanceBase2, ".
								 " Sum(Q2.DebitAmount) DebitAmount,           Sum(Q2.DebitAmountBase1) DebitAmountBase1,           Sum(Q2.DebitAmountBase2) DebitAmountBase2, ".
								 " Sum(Q2.CreditAmount) CreditAmount,         Sum(Q2.CreditAmountBase1) CreditAmountBase1,         Sum(Q2.CreditAmountBase2) CreditAmountBase2, ".
								 " Sum(Q2.Balance) Balance,                   Sum(Q2.BalanceBase1) BalanceBase1,                   Sum(Q2.BalanceBase2) BalanceBase2, ".
								 " Sum(Q2.PrevBalance + Q2.Balance) ActBalance, Sum(Q2.PrevBalanceBase1 + Q2.BalanceBase1) ActBalanceBase1, Sum(Q2.PrevBalanceBase2 + Q2.BalanceBase2) ActBalanceBase2, ".
								 " 4 As Ord, Sum(Q2.countprj) CountPrj, Sum(Q2.countcst) CountCst ".
								 " from ( ";

	$queryjournal= $queryjournal.$querydetail;
	$queryjournal= $queryjournal." Group By Ledger.Ledger_number, Ledger.Ledger_name, Currency_code, CstJour.Costcent_Code, costcent.costcent_name ".
								 " order by  Ledger.Ledger_number, costcent.costcent_name ";
	$queryjournal= $queryjournal." ) Q2 ".
								 " Group by Q2.Ledger_number, Q2.Ledger_name, Q2.Document, Q2.Ledger_grpdet, Q2.Costcent_Code, Q2.costcent_name ";
								 //" Having Sum(Q2.Balance) != 0 ";
}							 
					 
							 
$queryjournal= $queryjournal." ) Q3 ".
							 " Where (($prtgrponly = 1 and Q3.Ledger_grpdet = 1 and (($prtparents = 1 and length(Q3.Ledger_number) <= $grplen) or ($prtparents = 0 and length(Q3.Ledger_number) = $grplen))) or ($prtgrponly = 0 and 1 = 1))  ";

//stopped by demand of elie cause base2 is 0.1 and base is zero and client appears in trial balance
							 /*
if ($prtzerobal == 0){
	$queryjournal= $queryjournal." And (Q3.PrevDebitAmountBase1 != 0 or Q3.PrevCreditAmountBase1 != 0 or Q3.PrevDebitAmountBase2 != 0 or Q3.PrevCreditAmountBase2 != 0 or ".
							     " 		Q3.DebitAmountBase1 != 0 or Q3.CreditAmountBase1 != 0 or Q3.DebitAmountBase2 != 0 or Q3.CreditAmountBase2 != 0) ";
}
*/
/*
if ($prtzerobal == 0 && $currbase1 == 1 && $currbase2 == 1){
	$queryjournal= $queryjournal." And ((Q3.DebitAmountBase1 != 0 or Q3.CreditAmountBase1 != 0) and (Q3.DebitAmountBase2 != 0 or Q3.CreditAmountBase2 != 0)) ";
}
else if ($prtzerobal == 0 && $currbase1 == 1){
	$queryjournal= $queryjournal." And (Q3.DebitAmountBase1 != 0 or Q3.CreditAmountBase1 != 0) ";
}
else if ($prtzerobal == 0 && $currbase2 == 1){
	$queryjournal= $queryjournal." And (Q3.DebitAmountBase2 != 0 or Q3.CreditAmountBase2 != 0) ";
}
*/

//$queryjournal= $queryjournal." And (($prtzerobal = 0 and (($prtselper = 1 and round(abs(Q3.Balance),0) != 0) or ($prtselper = 0 and round(abs(Q3.ActBalance),0) != 0))) or ($prtzerobal = 1 and 1 = 1)) ";
$queryjournal= $queryjournal." And (($prtzerobal = 0 and (($prtselper = 1 and round(abs(Q3.Balancebase1+Q3.Balancebase2),4) != 0) or ($prtselper = 0 and round(abs(Q3.ActBalancebase1+Q3.ActBalancebase2),4) != 0))) or ($prtzerobal = 1 and 1 = 1)) ";
if($vrange1=='') $vrange1=0;
if($vrange2=='') $vrange2=0;

//$queryjournal= $queryjournal." And (round(abs(Q3.Balance),0) not between $vrange1 and $vrange2) ";
//$queryjournal= $queryjournal." And ( $prtzerobal = 1 and round(abs(Q3.Balancebase1+Q3.Balancebase2),0) not between $vrange1 and $vrange2) ";
$queryjournal= $queryjournal." And (($prtdtonly = 1 and Q3.Ledger_grpdet = 0) or ($prtdtonly = 0 and 1 = 1)) ".
							 " And (($prtreqdt = 1 and Q3.Ledger_RequiredDetail = 1) or ($prtreqdt = 0 and 1 = 1)) ";
							 
if($prtprjcst == 0 && $prtprjcst2 == 0) 							 
	$queryjournal= $queryjournal." Order by Q3.Ledger_number, Q3.Ledger_name, Q3.Ord, Q3.Currency_code ";
else
    $queryjournal= $queryjournal." Order by Q3.Ledger_number, Q3.Ledger_name, Q3.Ord, Q3.Currency_code, Q3.project_name, Q3.costcent_name ";	

		 // echo $queryjournal."<br>"; exit;

$MasterGroup = '';
$MainRecord = '';

$SumPrevDbBase1  = 0;
$SumPrevCrBase1  = 0;
$SumPrevBalBase1 = 0;
$SumDbBase1  = 0;
$SumCrBase1  = 0;
$SumBalBase1 = 0;
$SumActDbBase1  = 0;
$SumActCrBase1  = 0;
$SumActBalBase1 = 0;

$SumPrevDbBase2  = 0;
$SumPrevCrBase2  = 0;
$SumPrevBalBase2 = 0;
$SumDbBase2  = 0;
$SumCrBase2  = 0;
$SumBalBase2 = 0;
$SumActDbBase2  = 0;
$SumActCrBase2  = 0;
$SumActBalBase2 = 0;

$CountProject = 0;
$CountCostCent = 0;
$col = 0;
//echo $queryjournal; exit;
$prev_group = "0";
 //$printline = 0;
//$queryjournal2 = $queryjournal." limit 0,1";
//echo $queryjournal2; exit;
$resultjournal=mysql_query($queryjournal,$connection) or die(mysql_error());

while($rowsjournal=mysql_fetch_row($resultjournal)){
	if ($rowsjournal[3] == 0 && $rowsjournal[11] != '' && $curr == 1){
	  
		$i=0;
		$resultcurrency=mysql_query($querycurrency,$connection) or die(mysql_error());
		while($curf=mysql_fetch_row($resultcurrency)){
			$subcur = 'subcur'.$rowsjournal[0].$curarray[$i];                           
			if( $rowsjournal[11] == $curarray[$i]){
			if($rowsjournal[30] != '') 
				$$subcur = format_currency($rowsjournal[30], $DecimalBase);
			}
			$i++;
		}
	   
	}
} 
$temp_ledger = '';
$resultjournal=mysql_query($queryjournal,$connection) or die(mysql_error());
while($rowsjournal=mysql_fetch_row($resultjournal)){
	
	  if($prev_group!=substr($rowsjournal[0], 0, 1))
	{
	//echo substr($rowsjournal[0], 0, 1);
	$prev_group = substr($rowsjournal[0], 0, 1);
	    $printline = 1;
		
		
	}
	else
	{
	   $printline = 0;
	   //echo "test";
	}

    $Ledger_grpdet = $rowsjournal[3];
    //$Ledger_grpdet  = 1;
    if($Ledger_grpdet == 1)
	{
	   $font = "font-weight:bold;";
	}
	else
	{
	   $font="";
	}
	 if($printline == 1)
	   {
	     $border = "border-top: solid 2px #000";
	   }
	   else
	   {
	      $border = "";
	   }
	   
    if($col%2==0)
		$Color="style='background-color:".$report_color1.";'";
	else
		$Color="style='background-color:".$report_color2."' ";	
		
    $class='';
	
	if($rowsjournal[33] == 3 && $rowsjournal[34] > 0 && $prtprjcst==1) 
	    $CountProject = $rowsjournal[34];
	if($rowsjournal[33] == 4 && $rowsjournal[35] > 0 && $prtprjcst2==1) 
	    $CountCostCent = $rowsjournal[35];	

	 
	if (($MasterGroup == '' || $MasterGroup!= $rowsjournal[0]) ) {	
	   $CountProject = 0;
	   $CountCostCent = 0;
	  if($rowsjournal[3] == 1){
		echo "<tr $Color>";
		echo "<td align='left' valign='top' style='border-right:1px solid black; ".$font." ".$border."'>";
		echo "<a href=\"rep_statementofaccount.php?fromdate=$dispfromdate&todate=$disptodate&fromledger=".$rowsjournal[0]."&toledger=".$rowsjournal[0]."&fromproject=&fromcst=&fromcur=$fromcur&dateoption=1&transaction=3&ledgeroption=1&curr=$curr&currbase1=$currbase1&currbase2=$currbase2&cum=$cum1&alltypes=0&exclude=0&jvtype=&showunposted=0&prtfolio=1&prtref=1&prtdesc=1&prtvaluedate=1&prtjvtype=0&prtcostcent=0&prtproject=0&prevbal=1&dtjv=0&dtdate=1&checks=1&cltcst=&cltprj=&parentprj=0&prtaging=0&prtheader=1&prtcstjour=0&prtwording=0&salesman=&prtwnac=0&varprtbreak=1\" 
			target='_blank' style='text-decoration:none; color: inherit;'>";
		echo $rowsjournal[0];
		echo"</a></td>";
		 echo "<td align='left' valign='top' style='border-right:1px solid black; ".$font." ".$border."'> ". $rowsjournal[1] ."</td>";
		 $col++;
	  }
	} else if (($MasterGroup != '' && $MasterGroup == $rowsjournal[0] && $rowsjournal[11] == '') && ($rowsjournal[3] == 1)) {	
	   $CountProject = 0;
	   $CountCostCent = 0;
	   
	    echo "<tr $Color>";
		echo "<td colspan='2' align='left' valign='top' style='border-right:1px solid black; ".$font."  ".$border."'>&nbsp;</td>";
	} else if($rowsjournal[33] == 3 && $CountProject > 0 && $prtprjcst==1)  {
		echo "<tr $Color>";
		echo "<td align='left' valign='top' style='border-right:1px solid black'>&nbsp;</td>";
		echo "<td colspan='2' align='left' valign='top' style='border-right:1px solid black; ".$font."  ".$border."'>* ". $rowsjournal[8] ."</td>";
	} else if($rowsjournal[33] == 4 && $CountCostCent > 0 && $prtprjcst2==1) {
		echo "<tr $Color>";
		echo "<td align='left' valign='top' style='border-right:1px solid black'>&nbsp;</td>";
		echo "<td colspan='2' align='left' valign='top' style='border-right:1px solid black; ".$font."  ".$border."'>** ". $rowsjournal[10] ."</td>";
	} 
	
	
	if ($rowsjournal[3] == 0 && $rowsjournal[11] != '' && $curr == 1){
	   $class='detail1';
		$i=0; 
		if($temp_ledger != $rowsjournal[0]){
			echo "<tr $Color>";
			echo "<td align='left' valign='top' style='border-right:1px solid black; ".$font." ".$border."'>";
			echo "<a href=\"rep_statementofaccount.php?fromdate=$dispfromdate&todate=$disptodate&fromledger=".$rowsjournal[0]."&toledger=".$rowsjournal[0]."&fromproject=&fromcst=&fromcur=$fromcur&dateoption=1&transaction=3&ledgeroption=1&curr=$curr&currbase1=$currbase1&currbase2=$currbase2&cum=$cum1&alltypes=0&exclude=0&jvtype=&showunposted=0&prtfolio=1&prtref=1&prtdesc=1&prtvaluedate=1&prtjvtype=0&prtcostcent=0&prtproject=0&prevbal=1&dtjv=0&dtdate=1&checks=1&cltcst=&cltprj=&parentprj=0&prtaging=0&prtheader=1&prtcstjour=0&prtwording=0&salesman=&prtwnac=0&varprtbreak=1\" 
			target='_blank' style='text-decoration:none; color: inherit;'>";
			echo $rowsjournal[0];
			echo"</a></td>";
			echo "<td align='left' valign='top' style='border-right:1px solid black; ".$font." ".$border."'> ". $rowsjournal[1] ."</td>";
			echo "<td align='center' valign='top' style='border-right:1px solid black; ".$font."  ".$border."'>&nbsp;</td>";
		$resultcurrency=mysql_query($querycurrency,$connection) or die(mysql_error());
		while($curf=mysql_fetch_row($resultcurrency)){
			$subcur = 'subcur'.$rowsjournal[0].$curarray[$i]; 
			echo "<td align='right' valign='top' class='detail' style='border-right:1px solid black; padding-right:2px; ".$font."  ".$border."'>".$$subcur."</td>";
			
			$i++;
		} echo "</tr>";
		}
	$temp_ledger = $rowsjournal[0];	
	$col++;
	}
	
	
	
	$MasterGroup = $rowsjournal[0];
	//echo "<div class='print2'></div>";
	//ShowReportTitle('Trial Balance','landscape', $connection);
}

echo "<tr>";
echo "<td colspan='15'><hr width='100%' color='black' size='1'></td>";
echo "</tr>";

   

echo "</table></tr></td></table>";

function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date){

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

function format_currency($amount, $DecimalBase) { 

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

if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;

?>