<?php

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



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

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


print<<<HERE
<script>

setInterval(function(){
	 window.scrollTo(0,document.body.scrollHeight);
	 }, 500);

</script>
HERE;
//Read From Setup Table							
$querysetup= " select BASE1,BASE2 from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}

$comp = explode('_' , $_SESSION['dbName']);
$AllocComp = $comp[0];
preg_match_all('!\d+!', $comp[1], $matches);
$AllocYear = $matches[0][0];

 
 
if (!empty($vfromdate))  $vfromdate = explodeword($vfromdate);
if (!empty($vtodate))    $vtodate = explodeword($vtodate); 

if($_POST['ledgertype'][0] == 1){
	$querySups= " select distinct journaldt.ledger_number as SendLedger_number from journaldt INNER JOIN suppliers on journaldt.Ledger_number = suppliers.Ledger_number ";
}else if($_POST['ledgertype'][0] == 2){
	$querySups= " select distinct journaldt.ledger_number as SendLedger_number from journaldt INNER JOIN clients on journaldt.Ledger_number = clients.Ledger_number ";
}
$resultSups=mysql_query($querySups,$connection) or die(mysql_error());
while($rowSups=mysql_fetch_array($resultSups)){
extract($rowSups);
			 // echo"<BR>".
			$queryDb="SELECT
							JDT.journal_id JD_journal_id,
							JDT.Journal_Date JD_Journal_Date,
							JDT.journaldt_Desc as AE_flag,
							J.JOURNAL_TYPE JD_JOURNAL_TYPE,
							L.Currency_code JD_LCURR,
							round(IF
							( L.Currency_code = '$BASE1',
								ifnull(Journaldt_amount1,0), 
								if(L.currency_code = '$BASE2', ifnull(Journaldt_amount2 ,0),
								ifnull(convertcurFunc(JDT.Currency_code , L.currency_code,  now(), 0, 0, '$BASE1', '$BASE2', JDT.journaldt_Amount ) ,0)
								) 
							),3) JD_Amount,

							round(IF
								( L.Currency_code = 'GHC',
									ifnull(ALLCOnD.AllocatedAmount1,0), 
									if(L.currency_code = 'USD', ifnull(ALLCOnD.AllocatedAmount2 ,0),
									ifnull(convertcurFunc(JDT.Currency_code , L.currency_code,  now(), 0, 0, 'GHC', 'USD', ALLCOnD.AllocatedAmount ) ,0)
									) 
								),3) JD_AllocatedAmounts
							FROM
							journaldt JDT
							INNER JOIN journal J on JDT.journal_id = J.journal_id
							INNER JOIN Ledger L on JDT.Ledger_number = L.Ledger_number";
										
			if($_POST['ledgertype'][0] == 1){
						$queryDb .= " left join (		SELECT
							journal_idD,
							ledger_number,
							currency_code,
							ifnull( sum( amount ), 0 ) AllocatedAmount,
							ifnull( sum( amountbase1 ), 0 ) AllocatedAmount1,
							ifnull( sum( amountbase2 ), 0 ) AllocatedAmount2 
						FROM
							accesslabelsazar.alloclink 
						WHERE
							journal_idD IS NOT NULL 
							AND upper( AllocComp ) = upper( '$AllocComp' ) 
						GROUP BY
							journal_idD,
							ledger_number 
						) ALLCOnD ON JDT.journal_id = ALLCOnD.journal_idD 	AND JDT.ledger_number = ALLCOnD.ledger_number ";
			}else if($_POST['ledgertype'][0] == 2){
					$queryDb .= " 	left join (	SELECT
						journal_idC,
						ledger_number,
						currency_code,
						ifnull( sum( amount ), 0 ) AllocatedAmount,
						ifnull( sum( amountbase1 ), 0 ) AllocatedAmount1,
						ifnull( sum( amountbase2 ), 0 ) AllocatedAmount2 
					FROM
						accesslabelsazar.alloclink 
					WHERE
						journal_idC IS NOT NULL 
						AND upper( AllocComp ) = upper( '$AllocComp' ) 
					GROUP BY
						journal_idC,
						ledger_number 
					) ALLCOnD ON JDT.journal_id = ALLCOnD.journal_idC 	AND JDT.ledger_number = ALLCOnD.ledger_number";
			}
										
			$queryDb .= "	 
							WHERE
							JDT.ledger_number = '$SendLedger_number ' ";
							
							if($_POST['ledgertype'][0] == 1){
								// Supp
								$queryDb.= " AND JDT.journaldt_dbcd = 'D' " ;
							}else if($_POST['ledgertype'][0] == 2){
								// Clt
								$queryDb.= " AND JDT.journaldt_dbcd = 'C' " ;
							}
			 
			$queryDb.= " And J.Journal_BelongsToGroup >= 5 
							And ifnull(JDT.Journal_BelongsToGroup,0) >= 5
							And if('$vfromdate' <> '' ,  JDT.journal_date between '$vfromdate 00:00:00' and '$vtodate 23:59:59' , 1=1)
							having round(JD_Amount,2) > 0 and  (JD_Amount - JD_AllocatedAmounts) > 0.01
							order by JDT.Journal_Date asc";
			// echo "<br>".$queryDb; 
			$resultDb=mysql_query($queryDb,$connection) or die(mysql_error());
			while($rows=mysql_fetch_array($resultDb)){

				extract($rows);
				$AE_flag = addslashes($AE_flag);
				
				$querySupName="SELECT ledger_name from ledger where ledger_number = $SendLedger_number";
				$resultSupName=mysql_query($querySupName,$connection) or die(mysql_error());
				$rowSupName =mysql_fetch_array($resultSupName);
								
				if($_POST['ledgertype'][0] == 1){
					echo "<br>Allocating Supplier ($SendLedger_number) : ".$rowSupName[0]."...";
				}else if($_POST['ledgertype'][0] == 2){
					echo "<br>Allocating Client ($SendLedger_number) : ".$rowSupName[0]."...";				}
				
				
				
				// echo"<BR> Querying C(s) : ".
				$queryCD="SELECT
							JDT.journal_id JC_journal_id,
							JDT.Journal_Date JC_Journal_Date,
							JDT.Currency_code JC_Currency_code,
							J.JOURNAL_TYPE JC_JOURNAL_TYPE,
							round(IF
							( L.Currency_code = '$BASE1',
									ifnull(Journaldt_amount1,0), 
									if(L.currency_code = '$BASE2', ifnull(Journaldt_amount2 ,0),
									ifnull(convertcurFunc(JDT.Currency_code , '$JD_LCURR',  now(), 0, 0, '$BASE1', '$BASE2', JDT.journaldt_Amount ) ,0)
									) 
							),3) JC_Amount,
							Q.AllocatedC,
							round(IF
							( L.Currency_code = '$BASE1',
									ifnull(Journaldt_amount1,0), 
									if(L.currency_code = '$BASE2', ifnull(Journaldt_amount2 ,0),
									ifnull(convertcurFunc(JDT.Currency_code , '$JD_LCURR',  now(), 0, 0, '$BASE1', '$BASE2', JDT.journaldt_Amount ) ,0)
									) 
							),3) - round(ifnull(Q.AllocatedC,0),3) as RemainingAmountC
							FROM
							journaldt JDT
							INNER JOIN journal J on JDT.journal_id = J.journal_id
							INNER JOIN Ledger L on JDT.Ledger_number = L.Ledger_number
							left join (
								SELECT
									journal_idc,
									ledger_number,
									sum( amount )  as AllocatedC
								FROM
									accesslabelsazar.alloclink 
								WHERE
									journal_idD IS NOT NULL
									and ledger_number = '$SendLedger_number'
									and upper(AllocComp) = upper('$AllocComp')
								GROUP BY
									journal_idC,ledger_number
							)Q on Q.journal_idC = jdt.journal_id and Q.ledger_number = jdt.ledger_number
							WHERE
							JDT.ledger_number = '$SendLedger_number'  ";
							
							if($_POST['ledgertype'][0] == 1){
								// Supp
								$queryCD.= " AND JDT.journaldt_dbcd = 'C' " ;
							}else if($_POST['ledgertype'][0] == 2){
								// Clt
								$queryCD.= " AND JDT.journaldt_dbcd = 'D' " ;
							}
			 
			$queryCD.= " And J.Journal_BelongsToGroup >= 5
							And ifnull(JDT.Journal_BelongsToGroup,0) >= 5
							And if('$vfromdate' <> '' ,  JDT.journal_date between '$vfromdate 00:00:00' and '$vtodate 23:59:59' , 1=1)
							having RemainingAmountC > 0
							order by JDT.Journal_Date asc";

// echo "<br>".$queryCD;exit;
				$resultCD=mysql_query($queryCD,$connection) or die(mysql_error());
				$originalAmount = $JD_Amount;
				$AllocatedAmount = 0;
				while($rowsC=mysql_fetch_array($resultCD)){
					extract($rowsC); 
 					
					if(round($originalAmount,3)>0.005){  
						if($originalAmount <= $RemainingAmountC){
							$queryB1 = "select convertcurFunc('$JD_LCURR', '$BASE1', now(), 0,0, '$Base1','$Base2',$originalAmount)";
							$resB1 = mysql_query($queryB1,$connection) or die(mysql_error());
							$rowsB1 =mysql_fetch_array($resB1);

							$queryB2 = "select convertcurFunc('$JD_LCURR', '$BASE2', now(), 0,0, '$Base1','$Base2',$originalAmount)";
							$resB2 = mysql_query($queryB2,$connection) or die(mysql_error());
							$rowsB2 =mysql_fetch_array($resB2);
							
							
							if($_POST['ledgertype'][0] == 1){
								$queryInsAlloc = "INSERT INTO `".$_SESSION['dbLabel']."`.`alloclink`(`AllocLink_id`, `AllocLink_date`, `journal_idD`, `journal_idC`, `currency_code`, `amount`, `amountbase1`, `amountbase2`, `AllocComp`, `AllocYear`, `allocRemark`, `trans_id`, `transdt_id`, `transtype`,`ledger_number`,`AE_flag`) VALUES (null, now() , $JD_journal_id, $JC_journal_id , '$JD_LCURR' , $originalAmount, ".$rowsB1[0]." , ".$rowsB2[0].", '$AllocComp', $AllocYear, 'Auto Alloc of old Invoice FIFO($SendLedger_number)', '' , '', '$JC_JOURNAL_TYPE',$SendLedger_number,'$AE_flag')";
							}else if($_POST['ledgertype'][0] == 2){
								$queryInsAlloc = "INSERT INTO `".$_SESSION['dbLabel']."`.`alloclink`(`AllocLink_id`, `AllocLink_date`, `journal_idD`, `journal_idC`, `currency_code`, `amount`, `amountbase1`, `amountbase2`, `AllocComp`, `AllocYear`, `allocRemark`, `trans_id`, `transdt_id`, `transtype`,`ledger_number`,`AE_flag`) VALUES (null, now() , $JC_journal_id , $JD_journal_id, '$JD_LCURR' , $originalAmount, ".$rowsB1[0]." , ".$rowsB2[0].", '$AllocComp', $AllocYear, 'Auto Alloc of old Invoice FIFO($SendLedger_number)', '' , '', '$JC_JOURNAL_TYPE',$SendLedger_number,'$AE_flag')";
							}
							  // echo"<BR>1-".
							mysql_query($queryInsAlloc,$connection) or die(mysql_error());
							$AllocatedAmount = $originalAmount; 
						}else{
							$queryB1 = "select convertcurFunc('$JD_LCURR', '$BASE1', now(), 0,0, '$Base1','$Base2',$RemainingAmountC)";
							$resB1 = mysql_query($queryB1,$connection) or die(mysql_error());
							$rowsB1 =mysql_fetch_array($resB1);

							$queryB2 = "select convertcurFunc('$JD_LCURR', '$BASE2', now(), 0,0, '$Base1','$Base2',$RemainingAmountC)";
							$resB2 = mysql_query($queryB2,$connection) or die(mysql_error());
							$rowsB2 =mysql_fetch_array($resB2);


							
							if($_POST['ledgertype'][0] == 1){
								$queryInsAlloc = "INSERT INTO `".$_SESSION['dbLabel']."`.`alloclink`(`AllocLink_id`, `AllocLink_date`, `journal_idD`, `journal_idC`, `currency_code`, `amount`, `amountbase1`, `amountbase2`, `AllocComp`, `AllocYear`, `allocRemark`, `trans_id`, `transdt_id`, `transtype`,`ledger_number`,`AE_flag`) VALUES (null, now() , $JD_journal_id, $JC_journal_id , '$JD_LCURR' , $RemainingAmountC, ".$rowsB1[0]." , ".$rowsB2[0].", '$AllocComp', $AllocYear, 'Auto Alloc of old Invoice FIFO($SendLedger_number)', '' , '', '$JC_JOURNAL_TYPE',$SendLedger_number,'$AE_flag')";
							}else if($_POST['ledgertype'][0] == 2){
								$queryInsAlloc = "INSERT INTO `".$_SESSION['dbLabel']."`.`alloclink`(`AllocLink_id`, `AllocLink_date`, `journal_idD`, `journal_idC`, `currency_code`, `amount`, `amountbase1`, `amountbase2`, `AllocComp`, `AllocYear`, `allocRemark`, `trans_id`, `transdt_id`, `transtype`,`ledger_number`,`AE_flag`) VALUES (null, now() , $JC_journal_id, $JD_journal_id , '$JD_LCURR' , $RemainingAmountC, ".$rowsB1[0]." , ".$rowsB2[0].", '$AllocComp', $AllocYear, 'Auto Alloc of old Invoice FIFO($SendLedger_number)', '' , '', '$JC_JOURNAL_TYPE',$SendLedger_number,'$AE_flag')";
							}
							mysql_query($queryInsAlloc,$connection) or die(mysql_error());

							$AllocatedAmount = ($RemainingAmountC);
						}
						$originalAmount = $originalAmount - $AllocatedAmount ;
					} 
				}
			}
// echo "<hr>";
}
echo "<br><br> <h1>Done!</h1> <br> please use this link to go back : <a href='Form_AdvancedPaymentAllocation1.php' alt ='go Back to Allocation' >Go Back to Alloctation</a>";
// Header("Location: Form_ADvancedPaymentAllocation1.php?fromledger=$SendLedger_number");




function explodeword($date)
{

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


?>