<?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> Unbalanced Journal 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);
}

$unbrange 	= $_GET['unbrange'];
$fromdate 	= $_GET['fromdate'];
$todate 	= $_GET['todate'];
$curr 		= $_GET['curr'];
$currbase1 	= $_GET['currbase1'];
$currbase2 	= $_GET['currbase2'];

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;

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



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

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

if (empty($cltcst))   
   $cltcst = 'null';
else
   $cltcst = "'".$cltcst."'";       
     
if (empty($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=''>";
	
if ($prtheader == 1){
	ShowReportHeader(' Unbalanced Journal Report','portrait', $connection);
} else {
	ShowReportTitle(' Unbalanced Journal Report','portrait', $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="20%">$dispfromdate </td>
  <td width="5%"><b>To </b></td>
  <td width="20%">$disptodate</td>
</tr>

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

echo "<tr><td colspan=10 align='center'><table  border='0' width='100%' align='center' class='report-master' style='table-layout:fixed; border-collapse:collapse;' cellpadding='0' cellspacing='0'>
	  <col width=100>
	  <col width=120>
	  <col width=100>
	  <col width=50>
	  <col width=150>
	  <thead>
<tr>
    <th align='left'>Reference</th>
	<th align='left'>Date</th>
	<th align='left'>Journal Type</th>
    <th>Currency</th>
	<th>Balance</th>
</tr></thead>";


print <<<HERE

 
HERE;

$col = 0;

$queryjournal = "select Journal_refgovernment,Journal_date, Journal_type, debitamnt1-creditamnt1, debitamnt2- creditamnt2 from (
				select Journal_refgovernment,date_format(journal.Journal_date,'%d-%m-%Y') as Journal_date,Journal_type, sum(if(Journaldt_dbcd = 'D',Journaldt_amount1,0)) as debitamnt1,
				sum(if(Journaldt_dbcd = 'D',Journaldt_amount2,0)) as debitamnt2, sum(if(Journaldt_dbcd = 'C',Journaldt_amount1,0)) as creditamnt1,
				sum(if(Journaldt_dbcd = 'C',Journaldt_amount2,0)) as creditamnt2
				from journaldt
				inner join journal on journal.journal_id = journaldt.journal_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 date_format(journal.Journal_date,'%Y-%m-%d') between ifnull($fromdate,date_format(journal.Journal_date,'%Y-%m-%d')) and ifnull($todate,date_format(journal.Journal_date,'%Y-%m-%d'))
				group by journaldt.journal_id ) as Q where ABS(round(debitamnt1-creditamnt1,$DecimalBase )) > $unbrange
				order by Journal_refgovernment;"; 
				
				//echo $queryjournal;exit;
$resultjournal=mysql_query($queryjournal,$connection) or die(mysql_error());
$tempref = '';
$i=0;
while($rowsjournal=mysql_fetch_row($resultjournal)){
	
    if($col%2==0)
		$Color="style='background-color:".$report_color1.";'";
	else
		$Color="style='background-color:".$report_color2."' ";	
		
    
	if($curr == 1){
		$amount = $rowsjournal[3];
		$currency = $Base1;
	} else if($currbase1 == 1){
		$amount = $rowsjournal[3];
		$currency = $Base1;
	} else if($currbase2 == 1){
		$amount = $rowsjournal[4];
		$currency = $Base2;
	}

	
	echo "<tr $Color height='25px'>";
	echo "<td align='center'>$rowsjournal[0]</td>";
	echo "<td>$rowsjournal[1]</td>";
	echo "<td>$rowsjournal[2]</td>";
	echo "<td>$currency</td>";
	echo "<td>".format_currency($amount,$DecimalBase)."</td>";
	echo "</tr>";
	
	$col ++;
}

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;

?>