<?php

session_start();

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

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

date_default_timezone_set('Asia/Colombo');




if($_GET["Command"]=="lastmonthcal") 	{
    require_once './gl_posting.php';
    $ayear = ac_year($_GET["dtpDate"]);
			
	$ResponseXML .= "";
	$ResponseXML .= "<salesdetails>";
	$OpCrAmu=0;
	$OpDbAmu=0;
	$bF=0;
	
	$sql="Select * from a_bank_bal where bdate <'" . $_GET["dtpDate"] . "' and code='" . trim($_GET["txtBankCode"]) . "'  order by bdate desc";
	$result = $conn->query($sql);
    if ($row = $result->fetch()) {    
		$ResponseXML .= "<txt_bankbal><![CDATA[".number_format($row['close_balance'], 2,".", ",")."]]></txt_bankbal>";
		$ResponseXML .= "<dtfrom><![CDATA[".$row['bdate']."]]></dtfrom>";
   		$bdate=$row['bdate'];
	}
    
	$sql = "select max(bdate) from a_bank_bal where code='" . trim($_GET["txtBankCode"]) . "'";
	$result = $conn->query($sql);
    $row = $result->fetch();
         
	
    if($bdate != $row[0]){            
        $sql="Select state_balance from a_bank_bal where bdate <='" . $_GET["dtpDate"] . "' and code='" . trim($_GET["txtBankCode"]) . "'  order by bdate desc";
        $result = $conn->query($sql);
        $row = $result->fetch();

        $ResponseXML .= "<state_bal><![CDATA[".$row[0]."]]></state_bal>";
    }else{
        $ResponseXML .= "<state_bal><![CDATA[]]></state_bal>";	
    }
            
	$sql_rs="sELECT  L_AMOUNT, L_CODE, L_REFNO, l_flag1 from a_ledger where l_flag = 'OPB' and L_CODE='" . trim($_GET["txtBankCode"]) . "' and L_YEARFL = '0' "; 
	$result_rs = $conn->query($sql_rs);
    if ($row_rs = $result_rs->fetch()) {   
	
      if ($row_rs["l_flag1"]== "CRE") {
         $OpCrAmu = $row_rs["L_AMOUNT"];
      } else {
         $OpDbAmu = $row_rs["L_AMOUNT"];
      }
   }

    $sql_opCR="select sum(L_AMOUNT)as ctot from a_ledger where l_flag != 'OPB' and L_YEARFL != '2' and  L_YEARFL != '1'  AND l_flag1='CRE' and L_CODE='" . trim($_GET["txtBankCode"]) . "' and (L_DATE<='" . $bdate.  "'  )"; 
    $result_opCR = $conn->query($sql_opCR);
    if ($row_opCR = $result_opCR->fetch()) {  
    	$OpCrAmu = $OpCrAmu + $row_opCR["ctot"];
    }

    $sql_opDb="select sum(L_AMOUNT)as dtot from a_ledger where  l_flag != 'OPB' and L_YEARFL != '2' and  L_YEARFL != '1' AND l_flag1='DEB' and L_CODE='" . trim($_GET["txtBankCode"]) . "'  and (L_DATE<='" . $bdate.  "' )"; 
    $result_opDb = $conn->query($sql_opDb);
    if ($row_opDb = $result_opDb->fetch()) {   
    	$OpDbAmu = $OpDbAmu + $row_opDb["dtot"];
    }
 

    $bF = $OpDbAmu - $OpCrAmu ;
			
	$ResponseXML .= "<txt_bF><![CDATA[".number_format($bF, 2,".", ",")."]]></txt_bF>";
	
	$ResponseXML .= " </salesdetails>";
	echo $ResponseXML;
}



if($_GET["Command"]=="display") {
   
    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 class='table table-bordered'>
         <tr>
          <td  width=\"100\" height=\"25\">Ref No</td>
          <td  width=\"75\">Date</td>
          <td  width=\"75\">Cheque No</td>
          <td  width=\"300\">Name</td>
          <td  width=\"50\">Amount</td>
          <td  width=\"5\">F</td>
		  <td  width=\"10\"></td>
		  <td  width=\"10\"></td>
         
        </tr>";
 
	$sql_strsql="Select * from a_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 class='linehover' style='line-height:7px;'>
          <td style='line-height:7px;'>".$row_strsql["L_REFNO"]."</td>
          <td style='line-height:7px;'>".$row_strsql["L_DATE"]."</td>
          <td style='line-height:7px;'>".$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 a_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 style='line-height:7px;'>descrip ".$descrip."</td>";
			
		   }
		
			$ResponseXML .= "<td align=right style='line-height:7px;'>".number_format($row_strsql["L_AMOUNT"], 2, ".", ",")."</td>";
		  	
			$ResponseXML .= "<td style='line-height:7px;'>".$F ."</td>";
			
			$refno=$row_strsql["L_REFNO"].$row_strsql["ID"];
			
			if ($row_strsql["L_FLAG2"] != "0") { 
				$ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'DEB');\" checked=\"checked\" /></td>";
				$l_flag2="1";
			} else {
				$ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'DEB');\" /></td>";
				$l_flag2="0";
			}	

			$ResponseXML .=  " <td style='line-height:7px;'>".$row_strsql["ID"]."</td></tr>";
			
			$sql="insert into a_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 a_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 class='linehover' style='line-height:7px;'>
          <td style='line-height:7px;'>".$row_strsql["L_REFNO"]."</td>
          <td style='line-height:7px;'>".$row_strsql["L_DATE"]."</td>
          <td style='line-height:7px;'>".$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 a_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 style='line-height:7px;'>".$descrip."</td>";
			 			 
		   } else {
		     
			 $descrip=str_replace("~", "&", $row_strsql["L_LMEM"]);
	 		 $descrip=str_replace("&nbsp;", " ", $descrip);
			  
		   	 $ResponseXML .=  " <td>descrip ".$descrip."</td>";
			
		   }
		
			$ResponseXML .= "<td align=right style='line-height:7px;'>".number_format($row_strsql["L_AMOUNT"], 2, ".", ",")."</td>";
		  	
			$ResponseXML .= "<td style='line-height:7px;'>".$F ."</td>";
			
			$refno=$row_strsql["L_REFNO"].$row_strsql["ID"];
			
			if ($row_strsql["L_FLAG2"] != "0") { 
				$ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'DEB');\" checked=\"checked\" /></td>";
				$l_flag2="1";
			} else {
				$ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'DEB');\" /></td>";
				$l_flag2="0";
			}	

			$ResponseXML .=  " <td style='line-height:7px;'>".$row_strsql["ID"]."</td></tr>";
			
			$sql="insert into a_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 class='table table-bordered'>
         <tr>
          <td  width=\"100\" height=\"25\">Ref No</td>
          <td  width=\"75\">Date</td>
          <td  width=\"75\">Cheque No</td>
          <td  width=\"300\">Name</td>
          <td  width=\"50\">Amount</td>
          <td  width=\"5\">F</td>
		  <td  width=\"10\"></td>
		  <td  width=\"10\"></td>
          
        </tr>";
   
  
   //////// CREDIT /////////////////
 
        $sql_strsql="Select * from a_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 class='linehover' style='line-height:7px;'>
                  <td style='line-height:7px;'>".$row_strsql["L_REFNO"]."</td>
                  <td style='line-height:7px;'>".$row_strsql["L_DATE"]."</td>
                  <td style='line-height:7px;'>".$row_strsql["chno"]."</td>";


              $descrip="";
                $sql_rspaymas="select barer, heading from a_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 style='line-height:7px;'>".$descrip."</td>";
                      $descrip=$row_rspaymas["barer"];
               } else {

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

                     $ResponseXML .=  " <td style='line-height:7px;'>".$descrip."</td>";
                     $descrip=$row_strsql["L_LMEM"];
               }

                      
              $ResponseXML .=  "<td align=right style='line-height:7px;'>".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 style='line-height:7px;'>".$F ."</td>";

                  

              $refno=$row_strsql["L_REFNO"].$row_strsql["ID"];
              if ($row_strsql["L_FLAG2"] != "0") { 
                            $ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'CRE');\" checked=\"checked\" /></td>";
                            $l_flag2="1";
                    } else {
                            $ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'CRE');\" /></td>";
                            $l_flag2="0";
                    }	

                    $ResponseXML .=  " <td style='line-height:7px;'>".$row_strsql["ID"]."</td></tr>";

                    $sql="insert into a_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 a_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 class='linehover' style='line-height:7px;'>
                  <td style='line-height:7px;'>".$row_strsql["L_REFNO"]."</td>
                  <td style='line-height:7px;'>".$row_strsql["L_DATE"]."</td>
                  <td style='line-height:7px;'>".$row_strsql["chno"]."</td>";
		 
		  
		  $descrip="";
		   $sql_rspaymas="select barer, heading from a_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 style='line-height:7px;'>".$descrip."</td>";
			 $descrip=$row_strsql["L_LMEM"];
		   }
		   
		   
          $ResponseXML .=  "<td align=right style='line-height:7px;'>".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 style='line-height:7px;'>".$F ."</td>";
		  
		 
		  
		  $refno=$row_strsql["L_REFNO"].$row_strsql["ID"];
		  if ($row_strsql["L_FLAG2"] != "0") { 
				$ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'CRE');\" checked=\"checked\" /></td>";
				$l_flag2="1";
			} else {
				$ResponseXML .=  "<td style='line-height:7px;'><input type=\"checkbox\" name=\"".$refno."\" id=\"".$refno."\" onclick=\"checkdb('".$refno."', '".$row_strsql["ID"]."', 'CRE');\" /></td>";
				$l_flag2="0";
			}	

			$ResponseXML .=  " <td style='line-height:7px;'>".$row_strsql["ID"]."</td></tr>";
			
            $sql="insert into a_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 a_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 a_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 a_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 a_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 a_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 a_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;			
	
    
}



if ($_GET["Command"]=="checkdb"){
	header('Content-Type: text/xml'); 
	echo "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";

	$txtBankCode = $_GET["txtBankCode"];
	
    $ResponseXML .= "";
	$ResponseXML .= "<salesdetails>";
	if ($_GET["type"]=="CRE"){
		if ($_GET["chk"]=="true"){
			$sql="update a_tmpbankrecon_cre set chk='1' where id=".$_GET["id"]." and user='".$_SESSION['UserName']."'";
			$result = $conn->query($sql);
		}
	
	
		if ($_GET["chk"]=="false"){
			$sql="update a_tmpbankrecon_cre set chk='0' where id=".$_GET["id"]." and user='".$_SESSION['UserName']."'";
			$result = $conn->query($sql);
		}
		
	} else if ($_GET["type"]=="DEB"){
	
		if ($_GET["chk"]=="true"){
			$sql="update a_tmpbankrecon_deb set chk='1' where id=".$_GET["id"]." and user='".$_SESSION['UserName']."'";
		    $result = $conn->query($sql);
		}
	
	
		if ($_GET["chk"]=="false"){
			$sql="update a_tmpbankrecon_deb set chk='0' where id=".$_GET["id"]." and user='".$_SESSION['UserName']."'";
	    	$result = $conn->query($sql);
		}
	}	
	 
	$ResponseXML .= " </salesdetails>";
	echo $ResponseXML;
 }
 
 
 
 
 
 if ($_GET["Command"]=="save_customer"){
 	
// 	$sql="select * from  a_dep_mas";
// 	$result = $conn->query($sql);
//     $row = $result->fetch();
    
// 	if (($row["datefrom"]<=$_GET["dtpDate"]) and ($_GET["dtpDate"]<=$row["dateto"])){
	
// 	} else {
// 		exit("Out of Current Accounting Year");
// 	}
	
	
 	$sql_rst="select * from a_tmpbankrecon_deb where user='".$_SESSION['UserName']."'";
	foreach ($conn->query($sql_rst) as $row_rst) {    
		
		if ($row_rst["chk"]=="1"){ 
			$sql="Update a_ledger set l_month ='" . date("m", strtotime($_GET["dtpDate"])) . "', recdate='1' where id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "'";
			$result = $conn->query($sql);
			     
			$sql="Update a_ledger set l_flag2 ='1', tmprecdate ='". $_GET["dtpDate"] ."' where id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "'";
			$result = $conn->query($sql);
			
			$sql="update a_op_cheq set recdate='1', recmonth='" . date("m", strtotime($_GET["dtpDate"])) . "'  where id='" . $row_rst["id"] . "' and refno='" . trim($row_rst["ref_no"])  . "'";
			$result = $conn->query($sql);
	
      
		} else {
		
			$sql="Update a_ledger set l_month ='0', recdate='0' where id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "'";
			$result = $conn->query($sql);
			
			$sql="Update a_ledger set l_flag2 ='0', tmprecdate ='0' where id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "'";
			$result = $conn->query($sql);
			
			$sql="update a_op_cheq set recdate='0',recmonth='0' where id='" . $row_rst["id"] . "' and refno='" . trim($row_rst["ref_no"])  . "'";
			$result = $conn->query($sql);
		}
	}
	
	
	$sql_rst="select * from a_tmpbankrecon_cre where user='".$_SESSION['UserName']."'";
	foreach ($conn->query($sql_rst) as $row_rst) {   
		
		if ($row_rst["chk"]=="1"){
			$sql="Update a_ledger set l_month ='" . date("m", strtotime($_GET["dtpDate"])) . "', recdate='1' where id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "' and l_flag1='CRE' and (chno='" . $row_rst["chq_no"] . "' or l_flag4='CAS')";
			$result = $conn->query($sql);
			
			$sql="Update a_ledger set l_flag2 ='1', tmprecdate ='". $_GET["dtpDate"] ."' where  id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "' and (chno='" . $row_rst["chq_no"] . "' or l_flag4='CAS')";
			$result = $conn->query($sql);
			
			$sql="update a_op_cheq set recdate = '1', recmonth = '" . date("m", strtotime($_GET["dtpDate"])) . "'  where  id='" . $row_rst["id"] . "' and  refno = '" . trim($row_rst["ref_no"]) . "' ";
			$result = $conn->query($sql);
	
      
		} else {
			$sql="Update a_ledger set l_month ='0', recdate='0' where id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "' and l_flag1='CRE' and (chno='" . $row_rst["chq_no"] . "' or l_flag4='CAS')";
			$result = $conn->query($sql);
			
			$sql="Update a_ledger set l_flag2 ='0', tmprecdate ='0' where  id='" . $row_rst["id"] . "' and L_REFNO='" . trim($row_rst["ref_no"])  . "' and (chno='" . $row_rst["chq_no"] . "' or l_flag4='CAS')";
			$result = $conn->query($sql);
			
			$sql="update a_op_cheq set recdate='0', recmonth='0'  where   id='" . $row_rst["id"] . "' and refno='" . trim($row_rst["ref_no"]) . "'";
			$result = $conn->query($sql);
		}
	}	

	echo "Saved";
 }
 
 if($_GET["Command"]=="update_customer")
{
	
// 	$sql="select * from  a_dep_mas";
// 	$result = $conn->query($sql);
//     $row = $result->fetch();
    
// 	if (($row["datefrom"]<=$_GET["dtpDate"]) and ($_GET["dtpDate"]<=$row["dateto"])){
	
// 	} else {
// 		exit("Out of Current Accounting Year");
// 	}
	
	$sql="delete from  a_bank_bal where code = '" . trim($_GET["txtBankCode"]) . "' and bdate = '" . $_GET["dtpDate"] . "'";
	$result = $conn->query($sql);
	
	$sql="select * from  a_lcodes where C_CODE = '" . trim($_GET["txtBankCode"]) . "'";
	$result = $conn->query($sql);
    $row = $result->fetch();
	
	$txt_bankbal=str_replace(",", "", $_GET["txt_bankbal"]);
	$txtclosebal=str_replace(",", "", $_GET["txtclosebal"]);
	
	$sql="Insert into a_bank_bal(code, bank, bdate, open_balance, close_balance, bmonth, byear,state_balance) Values 
	('" . trim($_GET["txtBankCode"]) . "', '" . trim($row["C_NAME"]) . "', '" . $_GET["dtpDate"] . "', " . $txt_bankbal . ", " . $txtclosebal . ", '" . date("m", strtotime($_GET["dtpDate"])) . "', '" . date("Y", strtotime($_GET["dtpDate"])) . "','".$_GET["txtBankClosBal"]."')";
	$result = $conn->query($sql);
      
	  
	$sql="select * from  a_ledger where L_CODE = '".$_GET["txtBankCode"]."' and L_DATE>='".$_GET["dtfrom"]."' and L_DATE<='".$_GET["dtpDate"]."' and recdate='1'";
	foreach ($conn->query($sql) as $row_rst) {  
 
		$sql1="update a_ledger set LOCK1='1' where L_REFNO='".$row_rst["L_REFNO"]."'";
		$result = $conn->query($sql1);
	}
	
	$sql_rec="select * from a_lock_bank_rec where bank_code = '".$_GET["txtBankCode"]."'";
	$result_rec = $conn->query($sql_rec);
    if ($row_rec = $result_rec->fetch()) {    
		$sql="update a_lock_bank_rec set lock_date_from='".$_GET["dtfrom"]."', lock_date_to='".$_GET["dtpDate"]."' where bank_code= '".$_GET["txtBankCode"]."'";
		$result = $conn->query($sql);
	} else {
	 	$sql="insert into a_lock_bank_rec (bank_code, lock_date_from, lock_date_to)  values
	 	('".$_GET["txtBankCode"]."', '".$_GET["dtfrom"]."', '".$_GET["dtpDate"]."')";
		$result = $conn->query($sql);
	}        
      echo "Updated";          

}
