<?php

session_start();

////////////////////////////////////////////// Database Connector /////////////////////////////////////////////////////////////
require_once ("connection_sql.php");

////////////////////////////////////////////// Write XML ////////////////////////////////////////////////////////////////////
header('Content-Type: text/xml');

date_default_timezone_set('Asia/Colombo');

if (isset($_GET["Command"])) {
    if ($_GET["Command"] == "getrecdt") {


        $dbgrid = "<table class=\"table\"><tr>
                        <th style=\"width: 120px;\">Ref No</th>
                        <th style=\"width: 150px;\">Date</th>
                        <th style=\"width: 150px;\">Cheque No</th>
                        <th style=\"width: 120px;\">Amount</th>
                        <th style=\"width: 80px;\">Rec.</th>
                        <th style=\"width: 80px;\">Date</th>
                        <th>Detail</th>
                        <th style=\"width: 40px;\">ID</th>
                    </tr>";
        $crgrid = "<table class=\"table\"><tr>
                        <th style=\"width: 120px;\">Ref No</th>
                        <th style=\"width: 150px;\">Date</th>
                        <th style=\"width: 150px;\">Cheque No</th>
                        <th style=\"width: 120px;\">Amount</th>
                        <th style=\"width: 80px;\">Rec.</th>
                        <th style=\"width: 80px;\">Date</th>
                        <th>Detail</th>
                        <th style=\"width: 40px;\">ID</th>
                    </tr>";

        $dtpDate = $_GET['dtto'];
        $month1 = date("m", strtotime($_GET["dtto"]));
        $month1_y = date("Y", strtotime($_GET["dtto"]));


        $sql = "Select * from a_ledger where ((month(l_date)<'" . $month1 . "' and l_FLAG2='1' and year(l_date)<='" . $month1_y . "' and ( (l_month> " . $month1 . " and l_year= " . $month1_y . ")) or l_year> " . $month1_y . "  ) OR (month(l_date)<'" . $month1 . "' and l_FLAG2='0' and year(l_date)<='" . $month1_y . "' )OR (YEAR(l_date)<'" . $month1_y . "' and l_FLAG2='0' ) OR month(l_date)='" . $month1 . "' OR (l_year='" . $month1_y . "' and l_month='" . $month1 . "')) and l_flag<>'OPB' and L_FLAG1='DEB' and l_code='" . Trim($_GET['bank']) . "'  order by l_date";
        $i = 1;
        foreach ($conn->query($sql) as $row) {

            $id_deb = "id_deb" . $i;
            $dt_deb = "dt_deb" . $i;
            // if (($row['L_FLAG2'] == 0 or $row['tmpRecDate'] >= $dtpDate) and ( $row['L_DATE'] <= $dtpDate)) {
                $dbgrid .= "<tr>
                        <td>" . $row['L_REFNO'] . "</td>
                        <td>" . $row['L_DATE'] . "</td>
                        <td>" . $row['chno'] . "</td>
                        <td align='right'>" . number_format($row['L_AMOUNT'], 2, ".", ",") . "</td>";


                if (!is_null($row['tmpRecDate'])) {
                    if ($row['tmpRecDate'] > $dtpDate) {
                        $dbgrid .= "<td><input type='checkbox' id = '" . $dt_deb . "'></td>";
                    } else {
                        $dbgrid .= "<td><input type='checkbox' checked id = '" . $dt_deb . "'></td>";
                    }
                } else {
                    $dbgrid .= "<td><input type='checkbox'  id = '" . $dt_deb . "'></td>";
                }


                $dbgrid .= "<td>" . $row['tmpRecDate'] . "</td><td>" . $row['L_LMEM'] . "</td>
                        <td><input disabled id ='" . $id_deb . "' value='" . $row['ID'] . "'></td>
                    </tr>";
                $i = $i + 1;
            // }
        }

        $sql = "Select * from a_ledger where ((month(l_date)<'" . $month1 . "' and l_FLAG2='1' and year(l_date)<='" . $month1_y . "' and ( (l_month> " . $month1 . " and l_year= " . $month1_y . ")) or l_year> " . $month1_y . "  ) OR (month(l_date)<'" . $month1 . "' and l_FLAG2='0' and year(l_date)<='" . $month1_y . "' )OR (YEAR(l_date)<'" . $month1_y . "' and l_FLAG2='0' ) OR month(l_date)='" . $month1 . "' OR (l_year='" . $month1_y . "' and l_month='" . $month1 . "')) and l_flag<>'OPB' and L_FLAG1='cre' and l_code='" . Trim($_GET['bank']) . "'   order by l_date";
        $t = 1;
        foreach ($conn->query($sql) as $row) {
            $id_cre = "id_cre" . $t;
            $dt_cre = "dt_cre" . $t;

            if (($row['L_FLAG2'] == 0 or $row['tmpRecDate'] >= $dtpDate) and ( $row['L_DATE'] <= $dtpDate)) {
                $crgrid .= "<tr>
                        <td>" . $row['L_REFNO'] . "</td>
                        <td>" . $row['L_DATE'] . "</td>
                        <td>" . $row['chno'] . "</td>
                         <td align='right'>" . number_format($row['L_AMOUNT'], 2, ".", ",") . "</td>";

                if (!is_null($row['tmpRecDate'])) {

                    if ($row['tmpRecDate'] > $dtpDate) {
                        $crgrid .= "<td><input type='checkbox'  id = '" . $dt_cre . "'></td>";
                    } else {
                        $crgrid .= "<td><input type='checkbox' checked id = '" . $dt_cre . "'></td>";
                    }
                } else {
                    $crgrid .= "<td><input type='checkbox'  id = '" . $dt_cre . "'></td>";
                }

                $crgrid .= "<td>" . $row['tmpRecDate'] . "</td><td>" . $row['L_LMEM'] . "</td>
                        <td><input disabled id ='" . $id_cre . "' value='" . $row['ID'] . "'></td>
                    </tr>";
                $t = $t + 1;
            }
        }


        $ResponseXML = "";
        $ResponseXML .= "<salesdetails>";
        $ResponseXML .= "<dbgrid><![CDATA[" . $dbgrid . "]]></dbgrid>";
        $ResponseXML .= "<count><![CDATA[" . $i . "]]></count>";

        $sql = "select * from a_bank_bal  where   bdate='" . $dtpDate . "'  and code='" . Trim($_GET['bank']) . "'   "; 
        $sql1 = $conn->query($sql);
        if ($row1 = $sql1->fetch()) {
            $ResponseXML .= "<bank_bal><![CDATA[" . $row1['CLOSE_BALANCE'] . "]]></bank_bal>";
        } else {
            $ResponseXML .= "<bank_bal><![CDATA[0]]></bank_bal>";
        }

        $sql = "select * from a_bank_bal  where   bdate<'" . $dtpDate . "'  and code='" . Trim($_GET['bank']) . "'    ";
        $sql1 = $conn->query($sql);
        if ($row1 = $sql1->fetch()) {
            $ResponseXML .= "<last_dt><![CDATA[" . $row1['BDATE'] . "]]></last_dt>";
        } else {
            $ResponseXML .= "<last_dt><![CDATA[.]]></last_dt>";
        }


;



        $ResponseXML .= "<crgrid><![CDATA[" . $crgrid . "]]></crgrid>";
        $ResponseXML .= "<count1><![CDATA[" . $t . "]]></count1>";
        $ResponseXML .= "</salesdetails>";
        echo $ResponseXML;
    }
} else {

    if ($_POST["Command"] == "save_item") {


        $txtBankCode = $_POST['bank'];
        $dtpDate = $_POST['dtto'];
        $txtBankClosBal = $_POST['bank_bal'];

        $month1 = date("m", strtotime($_POST["dtto"]));
        $month1_y = date("Y", strtotime($_POST["dtto"]));

        
        try {
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $conn->beginTransaction();
            
            if ($_SESSION["CURRENT_USER"] == "") { 
                exit('Please Logging Again..');
            } 
            if ($_SESSION['company'] == "") { 
                exit('Please Logging Again..');
            } 
            
            $i = 1;
            $count = $_POST['count'];
            while ($_POST["count"] > $i) {
                $id_deb = "id_deb" . $i;
                $dt_deb = "dt_deb" . $i;

                if ($_POST[$dt_deb] == '1') {
                    $sql = "update a_ledger set l_year ='" . $month1_y . "',l_month='" . $month1 . "', l_flag2 = '1',recdate='" . $dtpDate . "',tmprecdate='" . $dtpDate . "' where id ='" . $_POST[$id_deb] . "'";
                } else {
                    $sql = "update a_ledger set l_year ='',l_month='',l_flag2 = '0',recdate='',tmprecdate = null where id ='" . $_POST[$id_deb] . "'";
                }
                $result = $conn->query($sql);
                $i = $i + 1;
            }

            $i = 1;
            $count = $_POST['count1'];
            while ($_POST["count1"] > $i) {
                $id_cre = "id_cre" . $i;
                $dt_cre = "dt_cre" . $i;

                if ($_POST[$dt_cre] == '1') {
                    $sql = "update a_ledger set l_year ='" . $month1_y . "',l_month='" . $month1 . "', l_flag2 = '1',recdate='" . $dtpDate . "',tmprecdate='" . $dtpDate . "' where id ='" . $_POST[$id_cre] . "'";
                } else {
                    $sql = "update a_ledger set l_year ='',l_month='',l_flag2 = '0',recdate='',tmprecdate = null where id ='" . $_POST[$id_cre] . "'";
                }
                $result = $conn->query($sql);

                $i = $i + 1;
            }


            $sql = "delete from a_bank_bal  where   bdate='" . $dtpDate . "'  and code='" . Trim($_POST['bank']) . "'    ";
            $result = $conn->query($sql);

            $sql = "Insert into a_bank_bal(CODE,  BDATE,  CLOSE_BALANCE, BMonth, BYear,COMPANY) Values
            ('" . Trim($_POST['bank']) . "', '" . $dtpDate . "','" . $_POST['bank_bal'] . "', '" . $month1 . "', '" . $month1_y . "', '" . $_SESSION['company'] . "')";
            $result = $conn->query($sql);
            
            
            $sqllog = "insert into a_entry_log(refno, username, docname, trnType, stime, sdate,company) values 
            ('" . $_POST["bank"] . "', '" . $_SESSION["CURRENT_USER"] . "', 'Bank Rec', 'Update', '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d") . "','".$_SESSION['company']."')";
            $resultlog = $conn->query($sqllog);
            
            
            echo "Saved";

            $conn->commit();
        } catch (Exception $e) {
            $conn->rollBack();
            echo $e;
        }
    }
}

 if($_GET["Command"]=="update_customer")
{
	
	$sql="select * from  dep_mas";
	$result=mysqli_query($GLOBALS['dbacc'],$sql);
	$row = mysqli_fetch_array($result);
	if (($row["datefrom"]<=$_GET["dtpDate"]) and ($_GET["dtpDate"]<=$row["dateto"])){
	} else {
		exit("Out of Current Accounting Year");
	}
	
	$sql="delete from  bank_bal where code = '" . trim($_GET["txtBankCode"]) . "' and bdate = '" . $_GET["dtpDate"] . "'";
	$result=mysqli_query($GLOBALS['dbacc'],$sql);
	
	$sql="select * from  bankmaster where bm_code = '" . trim($_GET["txtBankCode"]) . "'";
	$result=mysqli_query($GLOBALS['dbacc'],$sql);
	$row_rst = mysqli_fetch_array($result);
	
	$txt_bankbal=str_replace(",", "", $_GET["txt_bankbal"]);
	$txtclosebal=str_replace(",", "", $_GET["txtclosebal"]);
	
	$sql="Insert into bank_bal(code, bank, bdate, open_balance, close_balance, bmonth, byear,state_balance) Values ('" . trim($_GET["txtBankCode"]) . "', '" . trim($row_rst["bm_bank"]) . "', '" . $_GET["dtpDate"] . "', " . $txt_bankbal . ", " . $txtclosebal . ", '" . date("m", strtotime($_GET["dtpDate"])) . "', '" . date("Y", strtotime($_GET["dtpDate"])) . "','".$_GET["txtBankClosBal"]."')";
	$result=mysqli_query($GLOBALS['dbacc'],$sql);
      // echo $sql;  
	  
	$sql="select * from  ledger where l_code = '".$_GET["txtBankCode"]."' and l_date>='".$_GET["dtfrom"]."' and l_date<='".$_GET["dtpDate"]."' and recdate='1'";
	$result=mysqli_query($GLOBALS['dbacc'],$sql);
	while($row_rst = mysqli_fetch_array($result)){
		$sql1="update ledger set lock1='1' where l_refno='".$row_rst["l_refno"]."'";
		$result1=mysqli_query($GLOBALS['dbacc'],$sql1);
	}
	
	$sql_rec="select * from lock_bank_rec where bank_code = '".$_GET["txtBankCode"]."'";
	$result_rec=mysqli_query($GLOBALS['dbacc'],$sql_rec);
	if($row_rec = mysqli_fetch_array($result_rec)){
		$sql="update lock_bank_rec set lock_date_from='".$_GET["dtfrom"]."', lock_date_to='".$_GET["dtpDate"]."' where bank_code= '".$_GET["txtBankCode"]."'";
		$result=mysqli_query($GLOBALS['dbacc'],$sql);
	} else {
	 	$sql="insert into lock_bank_rec (bank_code, lock_date_from, lock_date_to) values ('".$_GET["txtBankCode"]."', '".$_GET["dtfrom"]."', '".$_GET["dtpDate"]."')";
		$result=mysqli_query($GLOBALS['dbacc'],$sql);
	}        
      echo "Updated";          

}


if($_GET["Command"]=="display")
{
    if(isset($_SESSION["CURRENT_USER"])){
        
        require_once './gl_posting.php';
        $ayear = ac_year($_GET["dtpDate"]);
        
	$txtFinalBal = 0;
	$txtCre = 0;
	$txtDeb = 0;

	$txtBankClosBal = 0;
	$txt_bankbal=0;
	$txtclosebal = 0;
	$b_pay = 0;
	$b_depo = 0;
	$txt_bankbal2=0;
	$txt_bankbal2 = 0; 
	$txtBankClosBal=0;
	$txttot = 0;

	
    $sql="delete from a_tmpbankrecon_deb where user='".$_SESSION['CURRENT_USER']."'";
    $result = $conn->query($sql);
    
    $sql="delete from a_tmpbankrecon_cre where user='".$_SESSION['CURRENT_USER']."'";
    $result = $conn->query($sql);
	
	
	header('Content-Type: text/xml'); 
	echo "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";

	$txtBankCode = $_GET["txtBankCode"];
	
    $ResponseXML .= "";
	$ResponseXML .= "<salesdetails>";
			
    ////////// DEBIT /////////////////////////////
	$mRow = 1;
	$txtDeb = 0;
	$ResponseXML .= "<debtable><![CDATA[<table width=\"625\" border=\"1\" cellspacing=\"0\">
         <tr>
          <td background=\"images/headingbg.gif\" width=\"100\" height=\"25\">Ref No</td>
          <td background=\"images/headingbg.gif\" width=\"75\">Date</td>
          <td background=\"images/headingbg.gif\" width=\"75\">Cheque No</td>
          <td background=\"images/headingbg.gif\" width=\"300\">Name</td>
          <td background=\"images/headingbg.gif\" width=\"50\">Amount</td>
          <td background=\"images/headingbg.gif\" width=\"5\">F</td>
		  <td background=\"images/headingbg.gif\" width=\"10\"></td>
		  <td background=\"images/headingbg.gif\" width=\"10\"></td>
         
        </tr>";
 
	$sql_strsql="Select * from ledger where l_date <= '" . $_GET["dtpDate"] . "' and l_flag!='OPB' and l_flag1 = 'DEB' and l_flag3='R' and l_code='" . trim($_GET["txtBankCode"]) . "' and acyear < '$ayear' order by l_date";
	foreach ($conn->query($sql_strsql) as $row_strsql) {
	     
     $tmprecdate = ac_year($row_strsql["tmprecdate"]);   
	 if (($row_strsql["l_flag2"] == "0") or (($row_strsql["l_month"] == date("m", strtotime($_GET["dtpDate"])))&&($tmprecdate == $ayear)) ) {
   		
		$ResponseXML .=  "<tr>
          <td>".$row_strsql["l_refno"]."</td>
          <td>".$row_strsql["l_date"]."</td>
          <td>".$row_strsql["chno"]."</td>";
		  
		  
		  $F="";
		  
		  if (($row_strsql["l_flag2"] == "1") or ($row_strsql["l_flag4"] == "CAS")) {
		  	$F="1";
      
      		$txtDeb = $txtDeb + $row_strsql["l_amount"];
      		$txtFinalBal =$txtDeb + $txtCre;
   		   } else {
			  $F="0";
   		   }
   			
		   if (is_null($row_strsql["recdate"]) == false) {
      		$F = $row_strsql["recdate"];
   		   }
		   
   		   if ($row_strsql["l_flag4"] == "CAS") {
			  $F  = $row_strsql["l_date"];
   		   }
   			
			
			
			$descrip="";
			
            $sql_rspaymas="select barer, heading from paymas where refno='" . $row_strsql["l_refno"] . "'"; 
            $result_rspaymas = $conn->query($sql_rspaymas);
            if ($row_rspaymas = $result_rspaymas->fetch()) { 
    			  $descrip=str_replace("~", "&", $row_rspaymas["barer"]);
    	 		  $descrip=str_replace("&nbsp;", " ", $descrip);
    			  $ResponseXML .=  " <td>".$descrip."</td>";
    		   } else {
		     
    			 $descrip=str_replace("~", "&", $row_strsql["l_lmem"]);
    	 		 $descrip=str_replace("&nbsp;", " ", $descrip);
    			  
    		   	 $ResponseXML .=  " <td>descrip ".$descrip."</td>";
			
		   }
		
			$ResponseXML .= "<td align=right>".number_format($row_strsql["l_amount"], 2, ".", ",")."</td>";
		  	
			$ResponseXML .= "<td>".$F ."</td>";
			
			$refno=$row_strsql["l_refno"].$row_strsql["id"];
			
			if ($row_strsql["l_flag2"] != "0") { 
				$ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'DEB');\" checked=\"checked\" /></td>";
				$l_flag2="1";
			} else {
				$ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'DEB');\" /></td>";
				$l_flag2="0";
			}	

			$ResponseXML .=  " <td>".$row_strsql["id"]."</td></tr>";
			
			$sql="insert into tmpbankrecon_deb (ref_no, trn_date, chq_no, name, heading, amt, f, descrip, chk, id, user) values
			('".$row_strsql["l_refno"]."', '".$row_strsql["l_date"]."', '".$row_strsql["chno"]."', '', '', ".$row_strsql["l_amount"].", '".$F."', '".$descrip."', '".$l_flag2."', ".$row_strsql["id"].", '".$_SESSION['CURRENT_USER']."')";
			$result = $conn->query($sql);
		
  	  }
	}
    
   		 
	$sql_strsql="Select * from ledger where l_date <= '" . $_GET["dtpDate"] . "' and l_flag!='OPB' and l_flag1 = 'DEB' and l_flag3='R' and l_code='" . trim($_GET["txtBankCode"]) . "' and acyear = '$ayear' order by l_date";
	foreach ($conn->query($sql_strsql) as $row_strsql) { 
 
	 if (($row_strsql["l_flag2"] == "0") or ($row_strsql["l_month"] == date("m", strtotime($_GET["dtpDate"]))) ) {
   		
		$ResponseXML .=  "<tr>
          <td>".$row_strsql["l_refno"]."</td>
          <td>".$row_strsql["l_date"]."</td>
          <td>".$row_strsql["chno"]."</td>";
		  
		  
		  $F="";
		  
		  if (($row_strsql["l_flag2"] == "1") or ($row_strsql["l_flag4"] == "CAS")) {
		  	$F="1";
      
      		$txtDeb = $txtDeb + $row_strsql["l_amount"];
      		$txtFinalBal =$txtDeb + $txtCre;
   		   } else {
			  $F="0";
   		   }
   			
		   if (is_null($row_strsql["recdate"]) == false) {
      		$F = $row_strsql["recdate"];
   		   }
		   
   		   if ($row_strsql["l_flag4"] == "CAS") {
			  $F  = $row_strsql["l_date"];
   		   }
   			
			
			
			$descrip="";
			
            $sql_rspaymas="select barer, heading from paymas where refno='" . $row_strsql["l_refno"] . "'";
            $result_rspaymas = $conn->query($sql_rspaymas);
            if ($row_rspaymas = $result_rspaymas->fetch()) {  
			  $descrip=str_replace("~", "&", $row_rspaymas["barer"]);
	 		  $descrip=str_replace("&nbsp;", " ", $descrip);
			  
			  $ResponseXML .=  " <td>".$descrip."</td>";
			 			 
		   } else {
		     
			 $descrip=str_replace("~", "&", $row_strsql["l_lmem"]);
	 		 $descrip=str_replace("&nbsp;", " ", $descrip);
			  
		   	 $ResponseXML .=  " <td>descrip ".$descrip."</td>";
			
		   }
		
			$ResponseXML .= "<td align=right>".number_format($row_strsql["l_amount"], 2, ".", ",")."</td>";
		  	
			$ResponseXML .= "<td>".$F ."</td>";
			
			$refno=$row_strsql["l_refno"].$row_strsql["id"];
			
			if ($row_strsql["l_flag2"] != "0") { 
				$ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'DEB');\" checked=\"checked\" /></td>";
				$l_flag2="1";
			} else {
				$ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'DEB');\" /></td>";
				$l_flag2="0";
			}	

			$ResponseXML .=  " <td>".$row_strsql["id"]."</td></tr>";
			
			$sql="insert into tmpbankrecon_deb (ref_no, trn_date, chq_no, name, heading, amt, f, descrip, chk, id, user) values
			('".$row_strsql["l_refno"]."', '".$row_strsql["l_date"]."', '".$row_strsql["chno"]."', '', '', ".$row_strsql["l_amount"].", '".$F."', '".$descrip."', '".$l_flag2."', ".$row_strsql["id"].", '".$_SESSION['CURRENT_USER']."')";
		    $result = $conn->query($sql);
		
  	  }
	} 
   
   $ResponseXML .= "</table>]]></debtable>";
   
   
   $ResponseXML .= "<cretable><![CDATA[<table width=\"625\" border=\"1\" cellspacing=\"0\">
         <tr>
          <td background=\"images/headingbg.gif\" width=\"100\" height=\"25\">Ref No</td>
          <td background=\"images/headingbg.gif\" width=\"75\">Date</td>
          <td background=\"images/headingbg.gif\" width=\"75\">Cheque No</td>
          <td background=\"images/headingbg.gif\" width=\"300\">Name</td>
          <td background=\"images/headingbg.gif\" width=\"50\">Amount</td>
          <td background=\"images/headingbg.gif\" width=\"5\">F</td>
		  <td background=\"images/headingbg.gif\" width=\"10\"></td>
		  <td background=\"images/headingbg.gif\" width=\"10\"></td>
          
        </tr>";
   
  
   //////// CREDIT /////////////////
 
            $sql_strsql="Select * from ledger where   l_date<='" . $_GET["dtpDate"] . "' and l_flag!='OPB' and  l_flag1='CRE' and l_flag3='R' and l_code='" . trim($_GET["txtBankCode"]) . "' and acyear < '$ayear' order by l_date";
            foreach ($conn->query($sql_strsql) as $row_strsql) {  

             $F="";
             $tmprecdate = ac_year($row_strsql["tmprecdate"]);   
             if (($row_strsql["l_flag2"] == "0") or (($row_strsql["l_month"] == date("m", strtotime($_GET["dtpDate"])))&&($tmprecdate == $ayear)) ) {

                    $ResponseXML .=  "<tr>
                          <td>".$row_strsql["l_refno"]."</td>
                          <td>".$row_strsql["l_date"]."</td>
                          <td>".$row_strsql["chno"]."</td>";


                      $descrip="";
                        $sql_rspaymas="select barer, heading from paymas where refno='" . $row_strsql["l_refno"] . "'";
                        $result_rspaymas = $conn->query($sql_rspaymas);
                        if ($row_rspaymas = $result_rspaymas->fetch()) {   
                            

                              $descrip=str_replace("~", "&", $row_rspaymas["barer"]);
                              $descrip=str_replace("&nbsp;", " ", $descrip);

                              $ResponseXML .=  " <td>".$descrip."</td>";
                              $descrip=$row_rspaymas["barer"];
                       } else {

                             $descrip=str_replace("~", "&", $row_strsql["l_lmem"]);
                             $descrip=str_replace("&nbsp;", " ", $descrip);

                             $ResponseXML .=  " <td>".$descrip."</td>";
                             $descrip=$row_strsql["l_lmem"];
                       }

                      
              $ResponseXML .=  "<td align=right>".number_format($row_strsql["l_amount"], 2, ".", ",")."</td>";

                      if (($row_strsql["l_flag2"] == "1") or ($row_strsql["l_flag4"] == "CAS")) {
                    $F = "1";
                    $txtCre = $txtCre + $row_strsql["l_amount"];
                    $txtFinalBal = $txtDeb + $txtCre;
              } else {
                    $F = 0;
              }

              if (is_null($row_strsql["recDate"]) == false) {
                    $F = $row_strsql["recdate"];
              }

                      if ($row_strsql["l_flag4"] == "CAS") {
                    $F = $row_strsql["l_date"];
              }

                      $ResponseXML .= "<td>".$F ."</td>";

                  

                      $refno=$row_strsql["l_refno"].$row_strsql["id"];
                      if ($row_strsql["l_flag2"] != "0") { 
                                    $ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'CRE');\" checked=\"checked\" /></td>";
                                    $l_flag2="1";
                            } else {
                                    $ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'CRE');\" /></td>";
                                    $l_flag2="0";
                            }	

                            $ResponseXML .=  " <td>".$row_strsql["id"]."</td></tr>";

                            $sql="insert into tmpbankrecon_cre (ref_no, trn_date, chq_no, name, heading, amt, f, descrip, chk, id, user) values 
                            ('".$row_strsql["l_refno"]."', '".$row_strsql["l_date"]."', '".$row_strsql["chno"]."', '', '', ".$row_strsql["l_amount"].", '".$F."', '".$descrip."', '".$l_flag2."', ".$row_strsql["id"].", '".$_SESSION['CURRENT_USER']."')";
                            $result = $conn->query($sql);
                    }

            }
       

 
	$sql_strsql="Select * from ledger where   l_date<='" . $_GET["dtpDate"] . "' and l_flag!='OPB' and  l_flag1='CRE' and l_flag3='R' and l_code='" . trim($_GET["txtBankCode"]) . "' and acyear = '$ayear' order by l_date";
	  foreach ($conn->query($sql_strsql) as $row_strsql) {  
	       
	
		$F="";
		 
	 if (($row_strsql["l_flag2"] == "0") or ($row_strsql["l_month"] == date("m", strtotime($_GET["dtpDate"]))) ) {

                $ResponseXML .=  "<tr>
                  <td>".$row_strsql["l_refno"]."</td>
                  <td>".$row_strsql["l_date"]."</td>
                  <td>".$row_strsql["chno"]."</td>";
		 
		  
		  $descrip="";
		   $sql_rspaymas="select barer, heading from paymas where refno='" . $row_strsql["l_refno"] . "'"; 
	       $result_rspaymas = $conn->query($sql_rspaymas);
            if ($row_rspaymas = $result_rspaymas->fetch()) {   
			  
			  $descrip=str_replace("~", "&", $row_rspaymas["barer"]);
	 		  $descrip=str_replace("&nbsp;", " ", $descrip);
			 
			  $ResponseXML .=  " <td>".$descrip."</td>";
			  $descrip=$row_rspaymas["barer"];
		   } else {
		   	 
			 $descrip=str_replace("~", "&", $row_strsql["l_lmem"]);
	 		 $descrip=str_replace("&nbsp;", " ", $descrip);
			  
		   	 $ResponseXML .=  " <td>".$descrip."</td>";
			 $descrip=$row_strsql["l_lmem"];
		   }
		   
		   
          $ResponseXML .=  "<td align=right>".number_format($row_strsql["l_amount"], 2, ".", ",")."</td>";
		  
		  if (($row_strsql["l_flag2"] == "1") or ($row_strsql["l_flag4"] == "CAS")) {
        	$F = "1";
        	$txtCre = $txtCre + $row_strsql["l_amount"];
        	$txtFinalBal = $txtDeb + $txtCre;
    	  } else {
        	$F = 0;
    	  }
    
    	  if (is_null($row_strsql["recDate"]) == false) {
        	$F = $row_strsql["recdate"];
    	  }
    	  
		  if ($row_strsql["l_flag4"] == "CAS") {
        	$F = $row_strsql["l_date"];
    	  }
		 
		  $ResponseXML .= "<td>".$F ."</td>";
		  
		 
		  
		  $refno=$row_strsql["l_refno"].$row_strsql["id"];
		  if ($row_strsql["l_flag2"] != "0") { 
				$ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'CRE');\" checked=\"checked\" /></td>";
				$l_flag2="1";
			} else {
				$ResponseXML .=  "<td><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["id"]."', 'CRE');\" /></td>";
				$l_flag2="0";
			}	

			$ResponseXML .=  " <td>".$row_strsql["id"]."</td></tr>";
			
            $sql="insert into tmpbankrecon_cre (ref_no, trn_date, chq_no, name, heading, amt, f, descrip, chk, id, user) values
            ('".$row_strsql["l_refno"]."', '".$row_strsql["l_date"]."', '".$row_strsql["chno"]."', '', '', ".$row_strsql["l_amount"].", '".$F."', '".$descrip."', '".$l_flag2."', ".$row_strsql["id"].", '".$_SESSION['CURRENT_USER']."')";
            $result = $conn->query($sql);
   		}
		
	}
	$ResponseXML .= "</table>]]></cretable>";		
	
	
	$b_pay = 0;
	$b_depo = 0;
	
    $sql_rst1="select sum(l_amount) from ledger where  (l_flag2 !='1' or tmprecdate > '".$_GET["dtpDate"]."') and l_yearfl='0' and (l_flag != 'OPB') AND (l_code = '" . trim($_GET["txtBankCode"]) . "') AND (l_flag1 = 'CRE') AND year(l_date)='" . date("Y", strtotime($_GET["dtpDate"])) . "' and month(l_date) ='" . date("m", strtotime($_GET["dtpDate"])) . "' and acyear = '$ayear'";
    $result_rst = $conn->query($sql_rst1);
    $row_rst = $result_rst->fetch();
	 
    $b_pay += $row_rst[0];
    
    $sql_rst2="select sum(l_amount) from ledger where  l_flag2 ='1' and l_yearfl='0' and (l_flag != 'OPB') AND (l_code = '" . trim($_GET["txtBankCode"]) . "') AND (l_flag1 = 'CRE') and month(l_date) ='" . date("m", strtotime($_GET["dtpDate"])) . "' and l_month = '".date("m", strtotime($_GET["dtpDate"]))."' and acyear = '$ayear'";
    $result_rst = $conn->query($sql_rst2);
    $row_rst = $result_rst->fetch();
    
     
    $b_pay += $row_rst[0];


    $ResponseXML .= "<txtbankpay><![CDATA[".$b_pay."]]></txtbankpay>";
	

        
    $sql_rst1="select sum(l_amount) from ledger where  (l_flag2 !='1' or tmprecdate > '".$_GET["dtpDate"]."') and l_yearfl='0' and (l_flag != 'OPB') AND (l_code = '" . trim($_GET["txtBankCode"]) . "') AND (l_flag1 = 'DEB') AND year(l_date)='" . date("Y", strtotime($_GET["dtpDate"])) . "' and month(l_date) ='" . date("m", strtotime($_GET["dtpDate"])) . "' and acyear = '$ayear'";
	$result_rst = $conn->query($sql_rst1);
    $row_rst = $result_rst->fetch();
    
    $b_depo += $row_rst[0];
        
    $sql_rst2="select sum(l_amount) from ledger where  l_flag2 ='1' and l_yearfl='0' and (l_flag != 'OPB') AND (l_code = '" . trim($_GET["txtBankCode"]) . "') AND (l_flag1 = 'DEB') and month(l_date) ='" . date("m", strtotime($_GET["dtpDate"])) . "' and l_month = '".date("m", strtotime($_GET["dtpDate"]))."' and acyear = '$ayear'";
    $result_rst = $conn->query($sql_rst2);
    $row_rst = $result_rst->fetch();
    $b_depo += $row_rst[0];

        
	$ResponseXML .= "<txtbankdepo><![CDATA[".number_format($b_depo, 2, ".", ",")."]]></txtbankdepo>";
	
	$ResponseXML .= "<txtFinalBal><![CDATA[".number_format($txtFinalBal, 2, ".", ",")."]]></txtFinalBal>";
	
	
	$sql="select sum(amt) as tot_deb from tmpbankrecon_deb where chk='1' and user='".$_SESSION['CURRENT_USER']."'";
	$result = $conn->query($sql);
    $row = $result->fetch();
	$txtDeb=$row["tot_deb"];
	
	
	$sql="select sum(amt) as tot_cre from tmpbankrecon_cre where chk='1' and user='".$_SESSION['CURRENT_USER']."'";
	$result = $conn->query($sql);
    $row = $result->fetch();
	$txtCre=$row["tot_cre"];
	
	$ResponseXML .= "<txtCre><![CDATA[".$txtCre."]]></txtCre>";
	$ResponseXML .= "<txtDeb><![CDATA[".$txtDeb."]]></txtDeb>";
	
	$txt_bankbal=str_replace(",", "", $_GET["txt_bankbal"]);
 
	$txtclosebal = $txt_bankbal - $b_pay + $b_depo;
	 
	
	$txt_bankbal2=str_replace(",", "", $_GET["txt_bankbal2"]);
	$txtclosebal2 = $txt_bankbal2 - $b_pay + $b_depo;
	
	$txtBankClosBal=str_replace(",", "", $_GET["txtBankClosBal"]);
	$txttot = $txtBankClosBal + $txtDeb - $txtCre;
	

	$ResponseXML .= "<txtclosebal><![CDATA[".number_format($txtclosebal, 2, ".", ",")."]]></txtclosebal>";
	$ResponseXML .= "<txttot><![CDATA[".number_format($txttot, 2, ".", ",")."]]></txttot>";
	
	$ResponseXML .= "<txtclosebal2><![CDATA[".number_format($txtclosebal2, 2, ".", ",")."]]></txtclosebal2>";
		
	$ResponseXML .= " </salesdetails>";
	echo $ResponseXML;			
	
        }else{
            
        }
}
	
