<?php

session_start();

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

include("config.inc.php");
include("function.inc.php");
include("Forms_DataGrid1.php");


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


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



//User Access
$querylog=" select ifNull(login_belongstogrp,0) As FI_BelongsToGrp from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_ReferenceByCostcenter,0) As Module_ReferenceByCostcenter, ifNull(Module_ReferenceByProject,0) As Module_ReferenceByProject, ".
			 " ifNull(Module_PostToJournal,0) As Module_PostToJournal, ifNull(Module_UniqueManualNo,0) As Module_UniqueManualNo ".
			 ",ifnull(Module_BelongsToGroup,0) as Module_BelongsToGroup ".
			 " From Module";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}



$querymodule=" select defaultcur,Chk_CommissionAcc  from receiptsetup";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}


 if ($actionmode=='checkaccountno'){

$vaccountno = $_GET["vaccountno"];

$vaccountname = $_GET["vaccountname"];

if ($codedesc==1)
	$query = "  select ledger_name from ledger where ledger_number = '".$vaccountno ."' and LEDGER_GRPDET=FALSE ";
	else
	$query = "  select ledger_number from ledger where ledger_name = '".$vaccountname ."' and LEDGER_GRPDET=FALSE ";

$result=mysql_query($query,$connection)  or die(mysql_error());
$row=mysql_fetch_row($result);
echo "[BRK]";
if ($row[0]!='')
	echo $row[0];
else	
	echo 0;
echo "[BRK]";
exit;
}	
else  if ($actionmode=='edit'){

$chk_bankdeposit= $_GET["value1"];
$chk_depositvaluedate= $_GET["value2"];
$chk_status= $_GET["value3"];
$journaldt_id1= $_GET["value4"];
$chk_commission= $_GET["value5"];
$chk_commissioncur= $_GET["value6"];
$chk_commissionacc= $_GET["value7"];
$manual_no= $_GET["value8"];
$journaldt_desc= $_GET["value9"];
$autoinc= $_GET["value10"];
$chk_date= $_GET["value11"];
$debit_ledgernumber= $_GET["value12"];


$query = "update ChecksCollected set Chk_BankDeposit = ".GetSQLValueString($chk_bankdeposit,'text')."
									,chk_depositvaluedate = ".GetSQLValueString(displayParadoxDate($chk_depositvaluedate),'date')."
									,chk_status = ".GetSQLValueString($chk_status,'text')."
									,chk_commission = ".GetSQLValueString($chk_commission,'double')."
									,chk_commissioncur = ".GetSQLValueString($chk_commissioncur,'text')."
									,chk_commissionacc = ".GetSQLValueString($chk_commissionacc,'text')."
									,manual_no = ".GetSQLValueString($manual_no,'text')."
									,journaldt_desc = ".GetSQLValueString($journaldt_desc,'text')."	
									,chk_date = ".GetSQLValueString(displayParadoxDate($chk_date),'date')."
									,debit_ledgernumber = ".GetSQLValueString($debit_ledgernumber,'text')."
									where autoinc = ".$autoinc;
		
//echo $query; exit;
			$result=mysql_query($query,$connection) or die(mysql_error());
			$error  = mysql_error(); 		
echo "[BRK]";
echo $error;
echo "[BRK]";
}
else  if ($actionmode=='changestatus'){

$chkstatus = $_GET["chkstatus"];

ShowChecksCollectedGrid($connection,'JS','',$DecimalBase,$chkstatus);



}
else  if ($actionmode=='loadchecks'){


$fromdate = $_GET["fromdate"];
$todate = $_GET["todate"];
$todate = displayParadoxDate($todate);


	$todateplus = date('Y-m-d H:i:s',date(strtotime("+1 day", strtotime($todate))));



	
  $query = "SELECT Checks.Chk_ValueDate, Checks.Ledger_number,Ledger.Ledger_name,
       Checks.Chk_bank, Checks.Chk_amount, Checks.Currency_code, Checks.Chk_number,
       Checks.Journaldt_id2, Checks.Journaldt_id1, journaldt.journal_date as Collect_Date,journaldt.Journaldt_desc,Checks.Chk_ValueDate as Deposit_Date,0 as trans_id, null as manual_no,0 as trans_reference 
       From Checks INNER JOIN Ledger ON (Checks.Ledger_number = Ledger.Ledger_number)
       INNER JOIN Journaldt ON (Checks.Journaldt_id1 = Journaldt.Journaldt_id)
       where  check_visible=True  and Checks.ledger_activity=1
       AND  (Checks.Chk_ValueDate >= '".displayParadoxDate($fromdate)."') 
       AND  (Checks.Chk_ValueDate < '".$todate."') ";
    
     if ($Module_BelongsToGroup==1)
      $query = $query ." and (Ledger.Ledger_BelongsToGroup >= $FI_BelongsToGrp) ";
	  
    
    if ($ApplyLedger_Deactivate==1)
		$qurey = $query ." and (Ledger.Ledger_Deactivate = false or Ledger.Ledger_Deactivate is null) "; 
          
		  
	$query = $query ." union SELECT chkjourdt.Chk_ValueDate, ledger.Ledger_number,Ledger.Ledger_name, chkjourdt.Chk_bank,
        chkjourdt.Chk_amount, chkjourdt.Currency_code, chkjourdt.Chk_number, 
         0 as Journaldt_id2, 0 as Journaldt_id1, transdt.Trans_Date as Collect_Date, 
         transdt.TransDt_Description as Journaldt_desc,checkscollectedtrans.Chk_ValueDate as Deposit_Date,trans.trans_id,manual_no,ifnull(trans.trans_reference,0) as  trans_reference 
         From chkjourdt 
         INNER JOIN transdt ON (chkjourdt.transdt_id = transdt.transdt_id) 
         INNER JOIN trans ON (trans.trans_id = transDT.trans_id) 
         INNER JOIN Ledger ON (transdt.Ledger_number = Ledger.Ledger_number) 
         inner join checkscollectedtrans on (checkscollectedtrans.trans_id=trans.trans_id) AND checkscollectiontrans_posted=0 
         where  1=1 
         AND  (chkjourdt.Chk_ValueDate >= '".displayParadoxDate($fromdate)."')
         AND  (chkjourdt.Chk_ValueDate < '".$todateplus."') 
         ORDER BY Chk_ValueDate, Ledger_number ";

   $result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($result)){

	$isexists  = ifexists($rows[1],$rows[3],$rows[6],$connection);

	if ($rows[14]	== 0)
		$transref = $rows[10];
	else	
		$transref = 'Rec No.' .$rows[14].' '.$rows[10];
		
		
	if ($isexists==0)
		{
		
			$querychecks = "select journaldt_id1 from checks where chk_number = '$rows[6]' ";
			$reschecks=mysql_query($querychecks,$connection) or die(mysql_error());
			$rowchecks=mysql_fetch_row($reschecks);
			$debitledgernumber = $rowchecks[0];
			
			$qdt = "insert into ChecksCollected(CheckCollected_Posted,Chk_ValueDate,Ledger_number,Chk_bank,Currency_code,Chk_amount,Chk_number
					,journaldt_desc,Journaldt_id2,Chk_commissioncur,Chk_commission,Check_visible,trans_id
					,Chk_CommissionAcc,Manual_No,debit_ledgernumber) values(0
														,".GetSQLValueString($rows[0],'date')."
														,".GetSQLValueString($rows[1],'text')."
														,".GetSQLValueString($rows[3],'text')."
														,".GetSQLValueString($rows[5],'text')."
														,".GetSQLValueString($rows[4],'double')."
														,".GetSQLValueString($rows[6],'text')."
														,".GetSQLValueString($transref,'text')."
														,".GetSQLValueString($rows[7],'text')."
														,".GetSQLValueString($rows[5],'text')."
														,0
														,1
														,".GetSQLValueString($rows[12],'double')."
														,".GetSQLValueString($Chk_CommissionAcc,'text')."
														,".GetSQLValueString($rows[13],'text')."
														,".GetSQLValueString($debitledgernumber,'text')."
					)";
					 $resdt=mysql_query($qdt,$connection) or die(mysql_error());
		//echo $qdt."<br>";
		}
	else {

	$qdt = "update ChecksCollected set Journaldt_id1 = ".GetSQLValueString($rows[8],'double')."";
	$resdt=mysql_query($qdt,$connection) or die(mysql_error());
	}



}
 
 ShowChecksCollectedGrid($connection,'JS','',$DecimalBase,$chkstatus);
	
exit;	
}
 else  if ($actionmode=='searchgrid'){


$vcolumnname = $_GET["vcolumnname"];
$searchedvalue = $_GET["searchedvalue"];
$fromdate = $_GET["fromdate"];
$todate = $_GET["todate"];
$todate = displayParadoxDate($todate);


$todateplus = date('Y-m-d H:i:s',date(strtotime("+1 day", strtotime($todate))));


 
 ShowChecksCollectedGrid($connection,'JS','',$DecimalBase,$chkstatus,$vcolumnname,$searchedvalue);
	
exit;	
}   
else if ($actionmode=='checkprojectcode'){

$vwarehousecode = $_GET["vwarehousecode"];

$query = " select project_code from project where project_code = '".$vprojectcode ."' ";
$result=mysql_query($query,$connection)  or die(mysql_error());
$row=mysql_fetch_row($result);

echo "[BRK]";
if ($row[0]!='')
	echo $row[0];
else	
	echo 0;
echo "[BRK]";	
}

else if ($actionmode=='checkcostcentercode'){

$vcostcentercode = $_GET["vcostcentercode"];

$query = " select costcent_code from costcent where costcent_code = '".$vcostcentercode ."' ";
$result=mysql_query($query,$connection)  or die(mysql_error());
$row=mysql_fetch_row($result);

echo "[BRK]";
if ($row[0]!='')
	echo $row[0];
else	
	echo 0;
echo "[BRK]";	
}
else if ($actionmode=='createdepositchecks'){

$filterby = $_GET["filterby"];
$project = $_GET["project"];
$costcenter = $_GET["costcenter"];
$postcomm = $_GET["postcomm"];
$seplines = $_GET["seplines"];

echo"[BRK]";

mysql_query("START TRANSACTION");
mysql_query("set @JvId :=0 ", $connection);
	
$query = " call  Create_ChecksCollectionAutoJv('".$filterby."','".$project."','".$costcenter."',".$postcomm.",".$seplines.",'".$HTTP_SESSION_VARS['dbName']."','".$HTTP_SESSION_VARS['dbLabel']."','".$HTTP_SESSION_VARS['useraccount']."','".$HTTP_SESSION_VARS['username']."','".$base1."','".$base2."',".$FI_BelongsToGrp.",@JvId) ";
$result=mysql_query($query,$connection)  or die(mysql_error());

	
	$query  = " select @JvId from dual ";
	$res=mysql_query($query,$connection) or die(mysql_error());
	$row=mysql_fetch_array($res);
	$JvId = $row[0];
	
	mysql_query("COMMIT");
	
}

function ifexists($ledgernumber,$chkbank,$chknumber,$connection){

$q = "select 1 from ChecksCollected where ledger_number = '$ledgernumber' 
				and chk_bank = '$chkbank' 
				and chk_number = '$chknumber' ";
$res=mysql_query($q,$connection) or die(mysql_error());
$row=mysql_fetch_array($res);

if ($row[0]==1)
	return 1;
else 	
	return 0;				

} 
  
 
 
 
 function insertDate($data){
	return substr($data, 6, 4) . substr($data, 3, 2) . substr($data, 0, 2);
}
function displayParadoxDate($data){
	
	$data = insertDate($data);
	return substr($data, 0, 4) ."-". substr($data, 4, 2) ."-".substr($data, 6, 2);
}
?>