<?php

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

include("config.inc.php");
include("Functions_Trans.php");

$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 FA_InvoicesAllocation as loginaccess, ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter ".
		  " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

if ($loginaccess=='' or $loginaccess==0)
	header("location:menu.php");




//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, ifNull(Client_ValidateProject,0) As Client_ValidateProject, ".
             " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Project,0) As Module_Project, ifNull(Module_Costcenter,0) As Module_CostCenter, ".
			 " ifNull(Module_RestrictProjCost,0) As Module_RestrictProjCost, ifNull(Module_RestrictCost,0) As Module_RestrictCost, ".
			 " ifNull(Module_ProjectParent,0) As Module_ProjectParent, ifNull(Module_CostCenterParent,0) As Module_CostCenterParent, ".
			 " ifNull(Module_Delivery,0) As Module_Delivery, ifNull(Module_AcctrxPostRV,0) As Module_AcctrxPostRV, ".
			 " ifNull(Module_AcctrxPostCV,0) As Module_AcctrxPostCV ". 
             " 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 (($Module_ProjectParent=1 and project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%')) or ($Module_ProjectParent=0 and project.project_code = loginstk.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 (($Module_CostCenterParent=1 and costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%')) or ($Module_CostCenterParent=0 and costcent.costcent_code = loginstk.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);	

// Setting Initial Values For Parameters
$querydate=" Select Date_Format(Min(Journal_date),'%d/%m/%Y') As DefaultFromDate, ".
           " Date_Format(Max(Journal_date),'%d/%m/%Y') As DefaultToDate, Date_Format(NOW(),'%d/%m/%Y') As DefaultAgingDate ".
		   " From JournalDt ";
$resultdate=mysql_query($querydate,$connection) or die(mysql_error());
$rowdate=mysql_fetch_row($resultdate);

if (!isset($fromdate))  $fromdate = $rowdate[0];
if (!isset($todate))    $todate   = $rowdate[1];

if (!isset($dispfromdate))  $dispfromdate = $fromdate;
if (!isset($disptodate))    $disptodate   = $todate;

if (!empty($fromdate))  $fromdate = explodeword($fromdate);
if (!empty($todate))    $todate   = explodeword($todate);    
//echo $fromdate;
$sub_query = "Select Ledger_Name From Ledger Where Ledger_Number = $fromledger";
$sub_result = selectData($connection, $sub_query);
$fromledgername = $sub_result[0];

$sub_query = "Select Ledger_Name From Ledger Where Ledger_Number = $toledger";
$sub_result = selectData($connection, $sub_query);
$toledgername = $sub_result[0];

$dispfromledger 	= $fromledger;
$dispfromledgername = $fromledgername;
$disptoledger 		= $toledger;
$disptoledgername 	= $toledgername;

$fromledger    = GetSQLValueString($fromledger, 'text');
$toledger      = GetSQLValueString($toledger, 'text');




print <<< HERE
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> 
<meta name="viewport" content="width=device-width, initial-scale=1.0"> 


<link rel="stylesheet" type="text/css" media="all" href="css-v2/style.css">
<link rel="stylesheet" type="text/css" media="all" href="js/grid/UI_DataGrid_V1.css">
<script type="text/javascript" language="javascript">

function SearchForm() {
	var x=document.invoices;
	
	x.actionmode.value = 'Search';
	x.submit();
}

function SubmitForm() {
	var x=document.invoices;
	
	var vAmountSI = 0;
	var vAmountRV = 0;
	var vTotalPaidAmount = 0;
	var vClosedCounter = 0;
	
	var vSchemaSI = '';
	var vSchemaRV = '';
		
		
	var vInvoicesID    = new Array();
	var vReceiptsID    = new Array();
	
	
	for (var i=0; i< document.getElementsByName('ChkSI[]').length; i++) {
		if (document.getElementsByName('ChkSI[]')[i].checked) {
			var vChkSI = document.getElementsByName('ChkSI[]')[i].value; 
			vNameSI = 'AmountSI'+vChkSI; 
			vAmountSI	= document.getElementById(vNameSI).value;
			vTotalPaidAmount = vTotalPaidAmount + parseFloat(vAmountSI);
			vInvoicesID.push(vChkSI);
		}	
	}
	
	for (var i=0; i<document.getElementsByName('ChkRV[]').length; i++) {
		if (document.getElementsByName('ChkRV[]')[i].checked) {
			var vChkRV = document.getElementsByName('ChkRV[]')[i].value;
			vNameRV = 'AmountRV'+vChkRV;
			vAmountRV	= parseFloat(document.getElementById(vNameRV).value);
			vReceiptsID.push(vChkRV);
		}	
	}
		
	/*if(vAmountRV > vTotalPaidAmount)
		vClosedCounter = 1;
	else vClosedCounter  = 0;
	*/
	
	if (vInvoicesID.length == 0 ) {
		alert("Please select at least one invoice.");
	} 
	else if (vReceiptsID.length == 0 ) {
		alert("Please select a Receipt.");
	}	
	else if (vClosedCounter == 1) {
		alert("Please check for total amount paid.");
	}
	else {
		
		x.action = "Form_AdvPaymentAllocation_Actions.php?mode=AllocateInvoices&PInvoicesID="+vInvoicesID+"&PReceiptsID="+vReceiptsID;
		x.submit();
	}	

}

function SelectInvoice(TagValue) {
	
	for (var i=0; i<document.getElementsByName('ChkSI[]').length; i++) {
		document.getElementsByName('ChkSI[]')[i].checked = false;
		if (document.getElementsByName('ChkSI[]')[i].value == TagValue ) {
			document.getElementsByName('ChkSI[]')[i].checked = true;
		}
	}
	
}


function SelectReceipt(TagValue) {
	
	for (var i=0; i<document.getElementsByName('ChkRV[]').length; i++) {
		document.getElementsByName('ChkRV[]')[i].checked = false;
		if (document.getElementsByName('ChkRV[]')[i].value == TagValue) {
			document.getElementsByName('ChkRV[]')[i].checked = true;
		}
	}
	
}

function showlistledger(str){

	var str1 = 2;
	window.open('select_accountno.php?item='+str+'&accountoption='+str1,'mywindow','width=710,height=550,toolbar=no,location=no,directories=no,status=yes,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes');
}


function roundNumber(num, dec) { // Arguments: number to round, number of decimal places
  var newnumber = Math.round(num*Math.pow(10,dec))/Math.pow(10,dec);
  newnumber = parseFloat(newnumber); 
  
  return newnumber;
}


</script>
<title>Advanced Payments Allocation</title>
<style type="text/css">

body,td,th {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 11px;
}
body {
	margin-top: 2px;
	margin-left: 1px;
}
a:link {
	text-decoration: none;
	color: #0000FF;
}
a:visited {
	text-decoration: none;
	color: #0000FF;
}
a:hover {
	text-decoration: none;
	color: #0000FF;
}
a:active {
	text-decoration: none;
	color: #0000FF;
}

.style12 {
	font-size: 9px;
	color: #999999;
	font-weight: bold;
}
.style15 {color: #5151FF; font-size: 10px;}
.style16 {
	font-size: 16px;
	color: #FF0000;
	font-weight: bold;
	font-family: "Times New Roman", Times, serif;
}
.style17 {font-size: 9px}
.style25 {color: #999999; font-weight: bold; font-size: 10px; }
.style26 {color: #FF0000}
.style27 {color: #999999 ;font-size: 10px;}
.style28 {color: #FFFFFF ;font-size: 10px;}

</style>

<link type="text/css" rel="stylesheet" href="dhtmlgoodies_calendar.css?random=20051112" media="screen"></LINK>
<SCRIPT type="text/javascript" src="dhtmlgoodies_calendar.js?random=20060118"></script>
<script type="text/javascript" src="js/grid/jquery-1.9.1.js"></script>
</head>
<body>
<form name='invoices' method='post' action=''>
<input type='hidden' name='actionmode' value='$actionmode'>
<input type='hidden' name='mainaction' value='$mainaction'>
<input type='hidden' name='message' value='$message' size='200'>
HERE;

//$style= "style=' background-color:#4069A7; color:#FFFFFF;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:12px;'";

$MainCompanyName=explode('_',$_SESSION['dbName']);  
$QueryDir = " Select Concat(CompanyName, '_', DirYear) As DirSchema, Date_Format(StartFinancialDate,'%Y-%m-%d') As StartFinancialDate, Date_Format(EndFinancialDate,'%Y-%m-%d') As EndFinancialDate, CompanyName, DirYear ".
			" 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 ";
			
//echo $QueryDir; exit;
$ResultDir=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir1=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir2=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir3=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir4=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir5=mysql_query($QueryDir,$connection) or die(mysql_error());
$RowsDirCount = mysql_num_rows($ResultDir);

$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) QP  ";
					
$QueryRestrictCst = " 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 != '') ".
					"			 ) QC  ";

					
$j = 0;				
$k = 0;
$QuerySI = ''; 
							
$QuerySI =  " Select headerlink_id, REF, header_date, journal_type, form_id,ledger_number, ledger_name, amountbase1,amountbase2,initialamnt1, initialamnt2, DirSchema ".
			" From ".
			" (  ";
				
// Reading Data For Many Years Or Different Directories					 
while($RowsDir=mysql_fetch_row($ResultDir)){

    $j = $j + 1;    
	//if ($RowsDirCount > 1)
	   $DirSchema   = $RowsDir[0];
	   $DirFromDate = $RowsDir[1];
	   $DirToDate   = $RowsDir[2];
	   $DirCompany  = $RowsDir[3];
	   $DirYear	    = $RowsDir[4];
	   preg_match_all('!\d+!', $DirYear ,$DirYears );
	   $DirYear =  $DirYears[0][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($Module_Delivery == 0 ) {
		$QuerySI = $QuerySI." select headerlink_id,headerlink.reference as REF, date_format(headerlink_date,'%d-%m-%Y') as header_date,headerlink.journal_type as journal_type,".
							" headerlink.form_id as form_id, headerlink.ledger_number as ledger_number, ledger_name,".
							" headerlink.amountbase1-ifnull(Q.amountbase1,0) -ifnull(Q1.amountbase1,0) as amountbase1, headerlink.amountbase2-ifnull(Q.amountbase2,0) -ifnull(Q1.amountbase2,0) as amountbase2, 
							 headerlink.amountbase1 as initialamnt1,  headerlink.amountbase2 as initialamnt2, '$DirSchema' As DirSchema ".
							" from ".$_SESSION['dbLabel'].".headerlink as  headerlink ".
							" left join(  select d1.form_id as form_id,sum(d1.amount) as amount,
															sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
															d1.company as company, d1.related_journalType as journal_type
															from ".$_SESSION['dbLabel'].".temp_detaillink as d1
															left join ".$_SESSION['dbLabel'].".headerlink as h1 on h1.form_id = d1.form_id
															group by d1.form_id
								) as Q on Q.form_id = headerlink.form_id and Q.year=headerlink.year
								and Q.company=headerlink.company and Q.journal_type=headerlink.journal_type".
							" left join( select d1.form_id as form_id,sum(d1.amount) as amount, sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
							d1.company as company, d1.related_journalType as journal_type
							from ".$_SESSION['dbLabel'].".detaillink as d1
							group by d1.form_id )
							as Q1 on Q1.form_id = headerlink.form_id and Q1.year=headerlink.year and Q1.company=headerlink.company and Q1.journal_type=headerlink.journal_type ".
							" Inner Join $DirSchema.Ledger As Ledger on Ledger.ledger_Number = headerlink.ledger_number ";
								
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QuerySI = $QuerySI.$QueryRestrictPrj." on headerlink.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QuerySI = $QuerySI.$QueryRestrictCst." on headerlink.costcent_code like concat(QC.costcent_code,'%') ";
		
		$QuerySI = $QuerySI.
				" Where Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$fromdate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('".$todate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And headerlink.ledger_number between ifNull(".$fromledger.",headerlink.ledger_number) and ifNull(".$toledger.",headerlink.ledger_number) ".
				" And  headerlink.journal_type in ('DI','DR','DV') ".
				" And (headerlink.amountbase1-ifnull(Q.amountbase1,0) -ifnull(Q1.amountbase1,0))>0 ".
				" And upper(headerlink.company) = upper('".$DirCompany."') ".
				" And upper(headerlink.year) = upper('".$DirYear."') ".
				" Group By headerlink_id  ";
							
					
		} else {
		$QuerySI = $QuerySI." select headerlink_id,headerlink.reference as REF, date_format(headerlink_date,'%d-%m-%Y') as header_date,headerlink.journal_type as journal_type,".
					" headerlink.form_id as form_id, headerlink.ledger_number as ledger_number, ledger_name,".
					" headerlink.amountbase1-ifnull(Q.amountbase1,0) -ifnull(Q1.amountbase1,0) as amountbase1, headerlink.amountbase2-ifnull(Q.amountbase2,0)-ifnull(Q1.amountbase2,0) as amountbase2, 
					 headerlink.amountbase1 as initialamnt1,  headerlink.amountbase2 as initialamnt2, '$DirSchema' As DirSchema ".
					" from ".$_SESSION['dbLabel'].".headerlink as  headerlink ".
					" left join(  select d1.form_id as form_id,sum(d1.amount) as amount,
													sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
													d1.company as company, d1.related_journalType as journal_type
													from ".$_SESSION['dbLabel'].".temp_detaillink as d1
													left join ".$_SESSION['dbLabel'].".headerlink as h1 on h1.form_id = d1.form_id
													group by d1.form_id
						) as Q on Q.form_id = headerlink.form_id and Q.year=headerlink.year
						and Q.company=headerlink.company and Q.journal_type=headerlink.journal_type".
					" left join( select d1.form_id as form_id,sum(d1.amount) as amount, sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
							d1.company as company, d1.related_journalType as journal_type
							from ".$_SESSION['dbLabel'].".detaillink as d1
							group by d1.form_id )
							as Q1 on Q1.form_id = headerlink.form_id and Q1.year=headerlink.year and Q1.company=headerlink.company and Q1.journal_type=headerlink.journal_type ".	
					" Inner Join $DirSchema.Ledger As Ledger on Ledger.ledger_Number = headerlink.ledger_number ";			
						
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QuerySI = $QuerySI.$QueryRestrictPrj." on headerlink.project_code like concat(QP.project_code,'%') ";
										 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QuerySI = $QuerySI.$QueryRestrictCst." on headerlink.costcent_code like concat(QC.costcent_code,'%') ";	
	
			$QuerySI = $QuerySI.
				" Where Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$fromdate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('".$todate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And headerlink.ledger_number between ifNull(".$fromledger.",headerlink.ledger_number) and ifNull(".$toledger.",headerlink.ledger_number) ".
				" And  headerlink.journal_type in ('SI','SR','DV') ".
				" And upper(headerlink.company) = upper('".$DirCompany."') ".
				" And upper(headerlink.year) = upper('".$DirYear."') ".
				" And (headerlink.amountbase1-ifnull(Q.amountbase1,0) -ifnull(Q1.amountbase1,0))>0 ".
				" Group By headerlink_id  ";
					
					
		}
		
		$QuerySI = $QuerySI." union
					select headerlink_id,headerlink.reference as REF, date_format(headerlink_date,'%d-%m-%Y') as header_date,headerlink.journal_type as journal_type,".
					" headerlink.form_id as form_id, headerlink.ledger_number as ledger_number, ledger_name,".
					" headerlink.amountbase1-ifnull(Q.amountbase1,0) -ifnull(Q1.amountbase1,0) as amountbase1, headerlink.amountbase2-ifnull(Q.amountbase2,0) -ifnull(Q1.amountbase2,0) as amountbase2, 
					 headerlink.amountbase1 as initialamnt1,  headerlink.amountbase2 as initialamnt2, '$DirSchema' As DirSchema ".
					" from ".$_SESSION['dbLabel'].".headerlink as  headerlink ".
					" left join( select d1.form_id as form_id,sum(d1.amount) as amount,
													sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
													d1.company as company, d1.related_journalType as journal_type
													from ".$_SESSION['dbLabel'].".temp_detaillink as d1
													left join ".$_SESSION['dbLabel'].".headerlink as h1 on h1.form_id = d1.form_id
													group by d1.form_id
						) as Q on Q.form_id = headerlink.form_id and Q.year=headerlink.year
						and Q.company=headerlink.company and Q.journal_type=headerlink.journal_type ".
					" left join( select d1.form_id as form_id,sum(d1.amount) as amount, sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
						d1.company as company, d1.related_journalType as journal_type
						from ".$_SESSION['dbLabel'].".detaillink as d1
						group by d1.form_id )
						as Q1 on Q1.form_id = headerlink.form_id and Q1.year=headerlink.year and Q1.company=headerlink.company and Q1.journal_type=headerlink.journal_type ".		
					" Inner Join $DirSchema.Ledger As Ledger on Ledger.ledger_Number = headerlink.ledger_number ";	
	
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QuerySI = $QuerySI.$QueryRestrictPrj." on headerlink.project_code like concat(QP.project_code,'%') ";
										 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QuerySI = $QuerySI.$QueryRestrictCst." on headerlink.costcent_code like concat(QC.costcent_code,'%') ";
		$QuerySI = $QuerySI.
				" Where Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$fromdate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('".$todate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And headerlink.ledger_number between ifNull(".$fromledger.",headerlink.ledger_number) and ifNull(".$toledger.",headerlink.ledger_number) ".
				" And  headerlink.journal_type = 'JS' ".
				" And upper(headerlink.company) = upper('".$DirCompany."') ".
				" And upper(headerlink.year) = upper('".$DirYear."') ".
				" And (headerlink.amountbase1-ifnull(Q.amountbase1,0) -ifnull(Q1.amountbase1,0))>0 ".
				" Group By headerlink_id  ";
		
		$QuerySI = $QuerySI." union
				select headerlink_id,headerlink.reference as REF, date_format(headerlink_date,'%d-%m-%Y') as header_date,headerlink.journal_type as journal_type,".
				" headerlink.form_id as form_id, headerlink.ledger_number as ledger_number, ledger_name,".
				" headerlink.amountbase1 -ifnull(Q.amountbase1,0)  -ifnull(Q1.amountbase1,0) as amountbase1, headerlink.amountbase2-ifnull(Q.amountbase2,0) -ifnull(Q1.amountbase2,0) as amountbase2, 
				 headerlink.amountbase1 as initialamnt1,  headerlink.amountbase2 as initialamnt2, '$DirSchema' As DirSchema ".
				" from ".$_SESSION['dbLabel'].".headerlink as  headerlink
				left join journaldt on concat(headerlink.journal_type,journaldt.journaldt_id,headerlink.year) = form_id	".
				" left join journal on journal.journal_id = journaldt.journal_id ".
				" left join(  select d1.form_id as form_id,sum(d1.amount) as amount,
												sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
												d1.company as company, d1.related_journalType as journal_type
												from ".$_SESSION['dbLabel'].".temp_detaillink as d1
												left join ".$_SESSION['dbLabel'].".headerlink as h1 on h1.form_id = d1.form_id
												group by d1.form_id
					) as Q on Q.form_id = headerlink.form_id and Q.year=headerlink.year
					and Q.company=headerlink.company and Q.journal_type=headerlink.journal_type ".
				" left join( select d1.form_id as form_id,sum(d1.amount) as amount, sum(d1.amountbase1) as amountbase1, sum(d1.amountbase2) as amountbase2,d1.year as year,
						d1.company as company, d1.related_journalType as journal_type
						from ".$_SESSION['dbLabel'].".detaillink as d1
						group by d1.form_id )
						as Q1 on Q1.form_id = headerlink.form_id and Q1.year=headerlink.year and Q1.company=headerlink.company and Q1.journal_type=headerlink.journal_type ".		
				" Inner Join $DirSchema.Ledger As Ledger on Ledger.ledger_Number = headerlink.ledger_number ";	
	
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QuerySI = $QuerySI.$QueryRestrictPrj." on headerlink.project_code like concat(QP.project_code,'%') ";
										 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QuerySI = $QuerySI.$QueryRestrictCst." on headerlink.costcent_code like concat(QC.costcent_code,'%') ";
		$QuerySI = $QuerySI.
				" Where Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull('".$fromdate."',date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull('".$todate."',date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ".
				" And headerlink.ledger_number between ifNull(".$fromledger.",headerlink.ledger_number) and ifNull(".$toledger.",headerlink.ledger_number) ".
				" And  headerlink.journal_type in ('JV','OP') And upper(Journaldt_dbcd) = 'D' ".
				" And upper(headerlink.company) = upper('".$DirCompany."') ".
				" And upper(headerlink.year) = upper('".$DirYear."') ".
				" And (headerlink.amountbase1-ifnull(Q.amountbase1,0) -ifnull(Q1.amountbase1,0))>0 ".
				" Group By headerlink_id  ";
				
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
            $QuerySI = $QuerySI." Union ";	
			
	}					
}

$QuerySI = $QuerySI.
			" ) QDirDetail ".
			" Order by ledger_number, header_date Asc, REF Asc  ";
			
//echo $QuerySI;
//exit;				

$j = 0;				
$k = 0;
$QueryRV = '';

$QueryRV =  " Select InvoiceId, ClientCode, ClientName, SalesDate, SalesRefer, SalesTotalBase1, SalesTotalBase2, DirSchema, Detaillink_id, journal_type ".
			" From ".
			" (  ";
				
// Reading Data For Many Years Or Different Directories					 
while($RowsDir=mysql_fetch_row($ResultDir2)){

    $j = $j + 1;    
	//if ($RowsDirCount > 1)
	   $DirSchema   = $RowsDir[0];
	   $DirFromDate = $RowsDir[1];
	   $DirToDate   = $RowsDir[2];
	   $DirCompany  = $RowsDir[3];
	   $DirYear	    = $RowsDir[4];
	   preg_match_all('!\d+!', $DirYear ,$DirYears );
	   $DirYear =  $DirYears[0][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; 
				
		$QueryRV = $QueryRV.
				" Select relation_id As InvoiceId, detaillink.Ledger_number As ClientCode, ledger.Ledger_name As ClientName, ".
				" date_format(Detaillink_date,'%d/%m/%Y') As SalesDate, trans_reference As SalesRefer, ifNull(amountbase1,0) As SalesTotalBase1,ifNull(amountbase2,0) As SalesTotalBase2, ".
				" '$DirSchema' As DirSchema, Detaillink_id, detaillink.journal_type as journal_type ".
				" from  ".$_SESSION['dbLabel'].".detaillink as detaillink ".	
				" Inner Join $DirSchema.Ledger As Ledger on detaillink.ledger_Number = Ledger.ledger_Number ";
					
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QueryRV = $QueryRV.$QueryRestrictPrj." on detaillink.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QueryRV = $QueryRV.$QueryRestrictCst." on detaillink.costcent_code like concat(QC.costcent_code,'%') ";
		
		$QueryRV = $QueryRV.
				" Where detaillink.Ledger_number between ifNull(".$fromledger.",detaillink.Ledger_number) and ifNull(".$toledger.",detaillink.Ledger_number) ".
				" And Date_format(Detaillink_date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Detaillink_date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Detaillink_date,'%Y-%m-%d'))  ".
				" And Date_format(Detaillink_date,'%Y-%m-%d') between ifNull('".$fromdate."',date_format(Detaillink_date,'%Y-%m-%d')) and ifNull('".$todate."',date_format(Detaillink_date,'%Y-%m-%d'))  ".
				" And journal_type in ('RV','DR','JV') and form_id is null ".
				" And upper(detaillink.Company) = upper('".$DirCompany."') And upper(detaillink.Year) = upper('".$DirYear."')  ".
				" Group By Detaillink_id ";
				
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
            $QueryRV = $QueryRV." Union ";	
			
	}					
}

$QueryRV = $QueryRV.
			" ) QDirDetail ".
			" Order by ClientCode, SalesDate Asc, SalesRefer Asc  ";
			
//echo $QueryRV;
//exit;	


echo"<table cellpadding='0' cellspacing='0' border='0'  align='center' class='layout-width'>";
include_once("header.php");
echo" <tr><td  valign='top'>";

include_once("sidemenu.php");
echo"  </td></tr>";


echo" <tr style='height:26px;'>
		<td  valign='top' id='content-top'>
			<table border='0' cellpadding='0' cellspacing='0' style='table-layout:fixed; height:26px;' class='layout-width'>
			<tr style='height:26px;'>
				<td style='width:38px; height:26px;'>&nbsp;</td>
				<td valign='middle' style='width:300px; height:26px;'><span class='title'>ADVANCED PAYMENTS ALLOCATION</span></td>
				<td class='message' valign='middle' style='height:26px;'>".htmlspecialchars($message, ENT_QUOTES)."</td>
			</tr></table>
		</td>
	  </tr>";

echo"<tr>
		<td  height='34'  valign='top' id='content-top2'>
			<table border='0' cellpadding='0' cellspacing='0' class='layout-width'>
			<tr>
				<td width='30'  height='34'>&nbsp;</td>
				<td valign='middle' height='34' width='720px' align='left'>";
	
	echo"<button type='button' id='btnSearch' name='btnSearch' onclick='SearchForm(); return false;' title='Search'><span><span>Search</span></span></button>";
	echo"<button type='button' id='btnSubmit' name='btnSubmit' onclick='SubmitForm(); return false;' title='Allocate Invoice'><span><span>Allocate Invoice</span></span></button>";
	
	
echo"	</td></tr></table>";
echo"</td></tr>";
				
echo"<tr><td id='content-body'>
<table border='0'  align='center' valign='top' width=100% class='tablecontent'>
   <tr bgcolor='#F7F7F7'><td valign='top' align='left'>";	 

print <<<HERE
		
		
		<table border="0" style='table-layout:fixed'>
		  <col width=80>
		  <col width=250>
		  <col width=80>
		  <col width=700>

HERE;



echo" <tr>";	  
echo"   <td height='19' colspan='2' align='left' bgcolor='#F5F5F5'><span class='style1'>Date</span></td>";
echo"   <td height='19' colspan='2' align='left' bgcolor='#F5F5F5'><span class='style1'>Ledger No. / Name</span></td>";
echo" </tr>";

echo" <tr height='25'>";	  
echo"   <td valign='middle' bgcolor='#F5F5F5' ><span class='style1'>From</span></td>";
echo"   <td valign='middle' bgcolor='#F5F5F5' ><span class='style1'>";
echo"      <input type='text' name='fromdate' value='$dispfromdate'  onBlur='checkDateFormat(this)'  maxlength='10' size='10'>";
echo"      <A HREF=\"#\" onClick=\"displayCalendar(document.invoices.fromdate,'dd/mm/yyyy',this); return false;\" NAME=\"anchor1\" ID=\"anchor1\"><image src='./img/cal.gif' border=0 align=center></image></A></span></td>";
echo"   <td align='left' bgcolor='#F5F5F5' ><span class='style1'>From</span></td>";
echo"   <td valign='middle' bgcolor='#F5F5F5' ><span class='style1'>";
echo"   <input name='fromledger' type='text' id='fromledger' size='10' value='".$dispfromledger."' readonly />
        <input name='fromledgername' type='text' id='fromledgername' size='35' value='".$dispfromledgername."' readonly />
		</span>
		<label><span class='style1'><a href='javascript:void(0);' NAME='My Window Name' title=' Select Ledger From ' 
             onClick='showlistledger(3); '><img src='img/btnopen.jpg' width='15' height='20' border='0' /></a> </span></label>";
echo"	</td>";
				
echo" </tr>";

echo" <tr height='25'>";
echo"   <td valign='top' bgcolor='#F5F5F5'><span class='style1'>To</span></td>";
echo"   <td valign='top' bgcolor='#F5F5F5'><span class='style1'>";
echo"      <input type='text' name='todate' value='$disptodate'  onBlur='checkDateFormat(this)'   maxlength='10' size='10'>";
echo"      <A HREF=\"#\" onClick=\"displayCalendar(document.invoices.todate,'dd/mm/yyyy',this); return false;\" NAME=\"anchor1\" ID=\"anchor1\"><image src='./img/cal.gif' border=0 align=center></image></A></span></td>";
echo"   <td align='left' bgcolor='#F5F5F5' ><span class='style1'>To</span></td>";
echo"   <td valign='middle' bgcolor='#F5F5F5' ><span class='style1'>";
echo" 	 	<input name='toledger' type='text' id='toledger' size='10' value='".$disptoledger."' readonly />
			<input name='toledgername' type='text' id='toledgername' size='35' value='".$disptoledgername."' readonly />
		</span>";
//echo"	<label><span class='style1'><a href='javascript:void(0);' NAME='My Window Name' title=' Select Ledger To ' 
//             onClick='showlistledger(4); '><img src='img/btnopen.jpg' width='15' height='20' border='0' /></a> </span></label>";
echo"	</td>";
echo" </tr>";




//Display Receipts
echo" <tr>";
echo"   <td colspan='4' valign='top' bgcolor='#F5F5F5'>";

echo"   <div style='position:relative; float:left; overflow:none; border:1px solid #B5B5B5;' >
		<div style='overflow:none; height:20px; width:530px; margin-top:5px; text-align:center; font-weight:bold;' >Credit</div>
		<div style='overflow:auto; height:350px; width:530px; margin-top:5px;' >
		<table border='0' style='table-layout:fixed; width:650px;' id='grid_datatable' class='grid_data'>
		<col width=20><col width=50><col width=90><col width=50><col width=200><col width=120><col width=120>";
		  
echo"	<tr height='25'>";
echo"		<th align='center'>&nbsp;</th>";
echo"		<th align='center'><span class='style1'>REF</span></th>";
echo"		<th align='center'><span class='style1'>Date</span></th>";
echo"		<th align='center'><span class='style1'>Type</span></th>";
echo"		<th align='center'><span class='style1'>Client</span></th>";
echo"		<th align='center'><span class='style1'>Amount $base1</span></th>";
echo"		<th align='center'><span class='style1'>Amount $base2</span></th>";
echo"	</tr>";		

if ($actionmode == 'Search') {

		
	$resultRV=mysql_query($QueryRV,$connection) or die(mysql_error());
	while($rowsRV=mysql_fetch_array($resultRV)){
		extract($rowsRV);
		$ct+=1;
	
		if($ct%2==0)
			$Color="style='background-color:".$grid_color1."'";
		else			
			$Color="style='background-color:".$grid_color2."'";	
		
		$AmountRV	 = 'AmountRV'.$Detaillink_id;							
		$DirSchemaRV = 'DirSchemaRV'.$Detaillink_id;
		
		echo "<tr ".$Color." >";
		echo "	<td align='left'><span class='style15'>
					<input type='hidden' name='$AmountRV' id='$AmountRV' value='".$SalesTotalBase1."' />
					<input type='hidden' name='$DirSchemaRV' id='$DirSchemaRV' value='".$DirSchema."' />
					<label><input type='checkbox' name='ChkRV[]' id='ChkRV[]' value='".$Detaillink_id."' onclick=\"SelectReceipt(this.value);\" /></label></span>
				</td>";
		echo "	<td align='left'><span class='style15'>".$SalesRefer."</span></td>";
		echo "	<td align='left'><span class='style15'>".$SalesDate."</span></td>";
		echo "	<td align='center'><span class='style15'>".$journal_type."</span></td>";
		echo "	<td align='left'><span class='style15'>".$ClientCode."-".$ClientName."</span></td>";
		echo "	<td align='right'><span class='style15'>".number_format($SalesTotalBase1,$DecimalBase1)."</span></td>";
		echo "	<td align='right'><span class='style15'>".number_format($SalesTotalBase2,$DecimalBase2)."</span></td>";
		echo "</tr>";	
		
	}
	
}		
		
echo "	</table></div></div>"; 

//display invoices
echo "	<div style='position:relative; float:left; overflow:none; border:1px solid #B5B5B5;margin-left:15px;' >
		<div style='overflow:none; height:20px; width:530px; margin-top:5px; text-align:center; font-weight:bold;' >Debit</div>
		<div style='overflow:auto; height:350px; width:530px; margin-top:5px;' >
		<table border='0' style='table-layout:fixed; width:600px;' id='grid_datatable' class='grid_data'>
		<col width=20><col width=50><col width=90><col width=200><col width=120><col width=120>";
			  
	echo"	<tr height='25px'>";
	echo"		<th align='center'>&nbsp;</th>";
	echo"		<th align='center'><span class='style1'>Reference</span></th>";
	echo"		<th align='center'><span class='style1'>Date</span></th>";
	echo"		<th align='center'><span class='style1'>Client</span></th>";
	echo"		<th align='center'><span class='style1'>Amount $base1</span></th>";
	echo"		<th align='center'><span class='style1'>Amount $base2</span></th>";
	echo"	</tr>";		

	$ct = 1;
	if ($actionmode == 'Search') {
	
		
	$ct = 1;
	$resultSI=mysql_query($QuerySI,$connection) or die(mysql_error());
	while($rowsSI=mysql_fetch_array($resultSI)){
		$ct+=1;
		extract($rowsSI);
		if($ct%2==0)
			$Color="style='background-color:".$grid_color1."'";
		else			
			$Color="style='background-color:".$grid_color2."'";	
		
		$AmountSI 	 = 'AmountSI'.$headerlink_id;
		$AmountSIb2 	 = 'AmountSIb2'.$headerlink_id;
		echo "<tr ".$Color." >";
		echo "	<td align='left'><span class='style15'><label>
					<input type='hidden' name='$AmountSI' id='$AmountSI' value='".$amountbase1."' />
					<input type='hidden' name='$AmountSIb2' id='$AmountSIb2' value='".$amountbase2."' />
					<label><input type='checkbox' name='ChkSI[]' id='ChkSI[]' value='".$headerlink_id."'  /></label></span>
				</td>";
		echo "	<td align='left'><span class='style15'>".$REF."</span></td>";
		echo "	<td align='center'><span class='style15'>".$header_date."</span></td>";
		echo "	<td align='left'><span class='style15'>".$ledger_number."-".$ledger_name."</span></td>";
		echo "	<td align='right'><span class='style15'>".number_format($amountbase1,$DecimalBase1)."</span></td>";
		echo "	<td align='right'><span class='style15'>".number_format($amountbase2,$DecimalBase1)."</span></td>";
		echo "</tr>";	
		
	}
	}
echo "	</table></div></div>";
echo "	</td>";
echo" </tr>";




	
			


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

echo"   </table></td></tr><tr height='30'><td align='center' id='content-bottom' ></td></tr>";  
echo" </table>  ";//end of last table menu


function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}
echo"  
</form>
</body>
</html>";

?>