<?php

session_start();

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

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

date_default_timezone_set('Asia/Colombo');

/////////////////////////////////////// GetValue //////////////////////////////////////////////////////////////////////////
///////////////////////////////////// Registration /////////////////////////////////////////////////////////////////////////


function getno($sdate) {

    $invno = "";
    require_once './gl_posting.php';
    $ayear = ac_year($sdate);

    include './connection_sql.php';
    $sql = "select TRANSCTIONS from a_dep_mas  where CURRENT_YEAR = '" . $ayear . "' and COMCODE='" . $_SESSION['company'] . "'";
    $result = $conn->query($sql);
    if ($row = $result->fetch()) {
        $tmpinvno = "000000" . $row["TRANSCTIONS"];
        $lenth = strlen($tmpinvno);
        $invno = $_SESSION['company'] . "/" . trim("" . substr($ayear, 2, 2) . "/P/") . substr($tmpinvno, $lenth - 7);
    }
    return $invno;
}

if ($_GET["Command"] == "getno") {

    require_once './gl_posting.php';
    $ayear = ac_year($_GET['sdate']);

    $invno = getno($_GET['sdate']);
    $sdate = $_GET['sdate'];


    $sql = "select REFNO,bdate from a_bankentmas where tmp_no ='" . $_GET['tmpno'] . "'  and COMPANY = '" . $_SESSION['company'] . "'";
    $result = $conn->query($sql);
    if ($row = $result->fetch()) {
        $invno = $row['REFNO'];

        if ($ayear != ac_year($row['bdate'])) {
            $sdate = $row['bdate'];
        }
    }

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";
    $ResponseXML .= "<invno><![CDATA[" . $invno . "]]></invno>";
    $ResponseXML .= "<tmpno><![CDATA[" . $_GET['tmpno'] . "]]></tmpno>";
    $ResponseXML .= "<dt><![CDATA[" . $sdate . "]]></dt>";
    $ResponseXML .= "</salesdetails>";
    echo $ResponseXML;
}

if ($_GET["Command"] == "new_inv") {
    
    if ($_SESSION["CURRENT_USER"] == "") { 
        exit('Please Logging Again..');
    } 
    if ($_SESSION['company'] == "") { 
        exit('Please Logging Again..');
    }
    
    $invno = getno(date('Y-m-d'));
    $tono = "";

    if ($invno != "") {
        $sql = "Select TRANSCTIONS from a_tmpinvpara_acc where COMCODE = '" . $_SESSION['company'] . "'";
        $result = $conn->query($sql);
        $row = $result->fetch();
        $tono = $row['TRANSCTIONS'];
    }

    $sql = "delete from a_tmp_che_data where tmp_no='" . $tono . "' ";
    $result = $conn->query($sql);

    $sql = "update a_tmpinvpara_acc set TRANSCTIONS=TRANSCTIONS+1 where COMCODE = '" . $_SESSION['company'] . "'";
    $result = $conn->query($sql);

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";
    $ResponseXML .= "<invno><![CDATA[" . $invno . "]]></invno>";
    $ResponseXML .= "<tmpno><![CDATA[" . $tono . "]]></tmpno>";
    $ResponseXML .= "<dt><![CDATA[" . date('Y-m-d') . "]]></dt>";
    $ResponseXML .= "</salesdetails>";
    echo $ResponseXML;
}

if ($_GET["Command"] == "add_tmp") {

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";

    $sql = "Insert into a_tmp_che_data (code, descript, amount, tmp_no,form_no)values 
			('" . $_GET['itemCode'] . "', '" . $_GET['itemDesc'] . "', " . $_GET['itemPrice'] . ",'" . $_GET['tmpno'] . "','BTRN') ";

    $result = $conn->query($sql);
    if (!$result) {
        echo $sql . "<br>";
        echo mysqli_error($GLOBALS['dbinv']);
    }

    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\"><tr>
                        <th style=\"width: 120px;\"></th>
                        <th></th>
                        <th style=\"width: 10px;\"></th>
                        <th style=\"width: 120px;\"></th>
                        <th style=\"width: 10px;\"></th>
                    </tr>";


    $i = 1;
    $mtot = 0;
    $sql = "Select * from a_tmp_che_data where tmp_no='" . $_GET['tmpno'] . "' AND form_no='BTRN'";
    foreach ($conn->query($sql) as $row) {
        $ResponseXML .= "<tr>                              
                         <td>" . $row['code'] . "</td>
                         <td>" . $row['descript'] . "</td>
                         <td></td>
                         <td>" . number_format($row['amount'], 2, ".", ",") . "</td>
                         <td><a class=\"btn btn-danger btn-xs\" onClick=\"del_item('" . $row['id'] . "','BTRN')\"> <span class='fa fa-remove'></span></a></td>
                         </tr>";
        $mtot = $mtot + $row['amount'];
        $i = $i + 1;
    }

    $ResponseXML .= "</table>]]></sales_table>";
    $ResponseXML .= "<form><![CDATA[BTRN]]></form>";
    $ResponseXML .= "<item_count><![CDATA[" . $i . "]]></item_count>";
    $ResponseXML .= "<subtot><![CDATA[" . number_format($mtot, 2, ".", "") . "]]></subtot>";
    $ResponseXML .= "</salesdetails>";
    echo $ResponseXML;
}

if ($_GET["Command"] == "del_item") {

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";


    $sql = "delete from a_tmp_che_data where id='" . $_GET['code'] . "' and tmp_no='" . $_GET['invno'] . "' AND form_no='BTRN'";
    $result = $conn->query($sql);


    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
	            <tr>
                        <th style=\"width: 120px;\"></th>
                        <th></th>
                        <th style=\"width: 10px;\"></th>
                        <th style=\"width: 120px;\"></th>
                        <th style=\"width: 10px;\"></th>
                    </tr>";

    $i = 1;
    $mtot = 0;
    $sql = "Select * from a_tmp_che_data where tmp_no='" . $_GET['invno'] . "' AND form_no='BTRN'";
    foreach ($conn->query($sql) as $row) {
        $ResponseXML .= "<tr>                              
                         <td>" . $row['code'] . "</td>
                         <td>" . $row['descript'] . "</td>
                         <td></td>
                         <td>" . number_format($row['amount'], 2, ".", ",") . "</td>
                         <td><a  class=\"btn btn-danger btn-sm\" onClick=\"del_item('" . $row['id'] . "','BTRN')\"> <span class='fa fa-remove'></span></a></td>
                         </tr>";
        $mtot = $mtot + $row['amount'];
        $i = $i + 1;
    }

    $ResponseXML .= "</table>]]></sales_table>";
    $ResponseXML .= "<item_count><![CDATA[" . $i . "]]></item_count>";
    $ResponseXML .= "<form><![CDATA[BTRN]]></form>";
    $ResponseXML .= "<subtot><![CDATA[" . number_format($mtot, 2, ".", "") . "]]></subtot>";
    $ResponseXML .= " </salesdetails>";
    echo $ResponseXML;
}





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


    
    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..');
        }
        
        require_once './gl_posting.php';
    $ayear = ac_year($_GET["entrydate"]);
    
        $sql = "select refno,COMPANY,Cancel from a_bankentmas where tmp_no ='" . $_GET['tmpno'] . "' and COMPANY = '" . $_SESSION['company'] . "'"; 
        $result = $conn->query($sql);
        if ($row = $result->fetch()) {
            $invno = $row['refno'];

            if ($_SESSION['company'] != $row['COMPANY']) {
                echo "Invalid Login";
                exit();
            }
            if ($row['Cancel'] != "0") {
                echo "Already Cancelled";
                exit();
            }


            $sql = "delete from a_bankentmas where refno = '" . $invno . "'";
            $conn->exec($sql);
            $sql = "delete from a_ledger where l_refno = '" . $invno . "'";
            $conn->exec($sql);
        } else {
            $invno = getno($_GET["entrydate"]);
            $sql = "update a_dep_mas set TRANSCTIONS=TRANSCTIONS+1  where current_year = '" . $ayear . "' and COMCODE='" . $_SESSION['company'] . "'";
            $conn->exec($sql);
        }

       
        $TXT_DETAIL=str_replace("~", "&", $_GET["txt_narration"]);
	    $TXT_DETAIL=str_replace("&nbsp;", " ", $TXT_DETAIL);
	   
	    if ($_GET["payment_type"] == "DEB") {
          $mFlag1 = "DEB";
       } else {
          $mFlag1 = "CRE";
       }
       
        $sql_tmp = "Insert into a_bankentmas(refno, bdate, heading, code, name, amount, type, COMPANY, invno, vatno, inv_date, tmp_no, cancel,chno) Values
        ('" . trim($invno) . "', '" . $_GET["entrydate"] . "', '" . $TXT_DETAIL . "', '" . $_GET['bank'] . "', '', '" . $_GET['txt_payments'] . "', '" . $mFlag1 . "', '" . $_SESSION['company'] . "', '" . $_GET["txtINVNO"] . "', '" . trim($_GET["txtVATNO"]) . "', '" . $_GET["cheq_date"] . "', '".$_GET['tmpno']."', '0','".$_GET["cheq_no"]."')";
	 	$conn->exec($sql_tmp);
		
	 
		if ($_GET["payment_type"] == "DEB") {
	   
	   		// $l_lmem= $TXT_DETAIL . " De. " . $dCode . " - Ch/No. " . $_GET["txtchno"];
			 $l_lmem= $TXT_DETAIL ;
			 
			 
	   		$sql_tmp = "Insert into a_ledger (l_refno, l_date, l_code, l_amount, l_flag, l_flag1, l_head, l_lmem, rights, COMPANY, chno, l_flag3, l_month, recdate,  acyear) Values 
	   		('" . trim($invno) . "', '" . $_GET["entrydate"] . "', '" . $_GET['bank'] . "', " . $_GET['txt_payments'] . ", 'RTN', '" . $mFlag1 . "', '" . $TXT_DETAIL . "', '" .$l_lmem. "', '" . $mUserWrite . "', '" . $_SESSION['company'] . "', '" . trim($_GET["cheq_no"]) . "', 'R', '".$l_month."', '".$recdate."',  '$ayear')";
		 	 $conn->exec($sql_tmp);
           
        } else {
			
			 $l_lmem= $TXT_DETAIL ;
			 
			$sql_tmp = "Insert into a_ledger (l_refno, l_date, l_code, l_amount, l_flag, l_flag1, l_head, l_lmem, rights, COMPANY, chno, l_flag3, l_month, recdate,  acyear) Values  
			('" . trim($invno) . "', '" . $_GET["entrydate"] . "', '" . $_GET['bank']  . "', " . $_GET['txt_payments'] . ", 'BEN', '" . $mFlag1 . "', '" . $TXT_DETAIL . "', '" . $l_lmem . "', '" . $mUserWrite . "', '" . $_SESSION['company'] . "', '" . trim($_GET["cheq_no"]) . "', 'R', '".$l_month."', '".$recdate."',  '$ayear')";
			 	 $conn->exec($sql_tmp);
			
       }
       
       if ($_GET["payment_type"] == "DEB") {
          $mFlag1 = "CRE";
       } else {
          $mFlag1 = "DEB";
       }
        

        $sql = "Select * from a_tmp_che_data where tmp_no='" . $_GET['tmpno'] . "' AND form_no='BTRN'";
        foreach ($conn->query($sql) as $row) {
            
            $amo = $row['amount'] * $_GET["txt_rate"];
            
            $sql_tmp1 = "Insert into a_bankenttrn(refno, bdate, code, amount, nara, COMPANY) Values 
		  	('" . trim($invno) . "', '" . $_GET["entrydate"] . "', '" . $row['code'] . "', " . $amo . ", '" . $row['descript'] . "', '" . $_SESSION['company'] . "')";
	 	    $conn->exec($sql_tmp1);
            
            if ($_GET["payment_type"] == "CRE") {
				
				$sql_tmp1 = "Insert into a_ledger (l_refno, l_date, l_code, l_flag, l_amount, l_flag1, l_flag2, l_flag3, l_lmem,  l_bank, rights, COMPANY, chno, l_month, recdate, acyear) Values
				('" . trim($invno) . "', '" . $_GET["entrydate"] . "', '" . $row['code'] . "', 'RTN', " . $amo . ", '" . $mFlag1 . "', '0', 'R', '" .  $row['descript'] . "', '" .$_GET['bank']  . "', '" . $mUserWrite . "', '" . $_SESSION['company'] . "', '" . trim($_GET["txtchno"]) . "', '".$l_month."', '".$recdate."', '$ayear')";
				$conn->exec($sql_tmp1);
				
              
            } else {
				
				$sql_tmp1 = "Insert into a_ledger (l_refno, l_date, l_code, l_flag, l_amount, l_flag1, l_flag2, l_flag3, l_lmem,  l_bank, rights, COMPANY, chno, l_month, recdate, acyear) Values
				('" . trim($invno) . "', '" . $_GET["entrydate"] . "', '" . $row['code'] . "', 'BEN', " . $amo . ", '" . $mFlag1 . "', '0', 'R', '" .  $row['descript'] . "',  '" . $_GET['bank']  . "', '" . $mUserWrite . "', '" . $_SESSION['company'] . "','" . trim($_GET["txtchno"]) . "', '".$l_month."', '".$recdate."', '$ayear')";
				$conn->exec($sql_tmp1);
              
            }
             
        }
       
                $sqllog = "insert into a_entry_log(refno, username, docname, trnType, stime, sdate,company) values 
        ('" . $invno . "', '" . $_SESSION["CURRENT_USER"] . "', 'Bank Transction', 'Save', '" . 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 "Failed";
    }
}


if ($_GET["Command"] == "pass_rec") {

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";
    $sql = "Select * from a_bankentmas where refno='" . $_GET['refno'] . "'";
    $result = $conn->query($sql);

    if ($row = $result->fetch()) {
       
		
		
        $ResponseXML .= "<refno><![CDATA[" . $row["refno"] . "]]></refno>";
        $ResponseXML .= "<bdate><![CDATA[" . date("Y-m-d", strtotime($row["bdate"])) . "]]></bdate>";
        $ResponseXML .= "<tmp_no><![CDATA[" . $row["tmp_no"] . "]]></tmp_no>";
        // $ResponseXML .= "<C_CODE><![CDATA[" . $row["VEN_CODE"] . "]]></C_CODE>";
        // $ResponseXML .= "<C_NAME><![CDATA[" . $row["BARER"] . "]]></C_NAME>";

        $ResponseXML .= "<code><![CDATA[" . $row["code"] . "]]></code>";

		$ResponseXML .= "<invno><![CDATA[".$row["invno"]."]]></invno>";
		$ResponseXML .= "<vatno><![CDATA[".$row["vatno"]."]]></vatno>";

        $ResponseXML .= "<paytype><![CDATA[" . $row["type"] . "]]></paytype>";
	    if ($row["inv_date"]=="0000-00-00 00:00:00"){
			$ResponseXML .= "<cheq_date><![CDATA[]]></cheq_date>";
		} else {
			$ResponseXML .= "<cheq_date><![CDATA[".$row["inv_date"]."]]></cheq_date>";
		}
		
			$ResponseXML .= "<vatno><![CDATA[".$row["vatno"]."]]></vatno>";

        $ResponseXML .= "<currency><![CDATA[" . $row["Currency"] . "]]></currency>";
        $ResponseXML .= "<txt_rate><![CDATA[" . $row["rate"] . "]]></txt_rate>";
        $ResponseXML .= "<amount><![CDATA[" . $row["amount"] . "]]></amount>";

        $ResponseXML .= "<heading><![CDATA[" . $row["heading"] . "]]></heading>";
        $ResponseXML .= "<naration><![CDATA[" . $row["naration"] . "]]></naration>";
        $ResponseXML .= "<cheq_no><![CDATA[" . $row["chno"] . "]]></cheq_no>"; 


        $msg = "";
        if ($row['CANCEL'] == "1") {
            $msg = "Cancelled";
        }
        $ResponseXML .= "<msg><![CDATA[" . $msg . "]]></msg>";



        









        $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
	            <tr>
                        <th style=\"width: 120px;\"></th>
                        <th></th>
                        <th style=\"width: 10px;\"></th>
                        <th style=\"width: 120px;\"></th>
                        <th style=\"width: 10px;\"></th>
                    </tr>";
        $i = 1;
        $mtot = 0;
        $sql = "delete from a_tmp_che_data where  tmp_no='" . $row["tmp_no"] . "'";
        $result = $conn->query($sql);
        $sql = "Select * from a_bankenttrn where refno='" . $row["refno"] . "' ";
        foreach ($conn->query($sql) as $row1) {
            
            	$sql_lcode="Select * from a_lcodes where c_code = '" . $row1["code"]. "' ";
            	$result_lcode = $conn->query($sql_lcode);
                $row_lcode = $result_lcode->fetch() ;
		
            $sql = "Insert into a_tmp_che_data (code, descript, amount, tmp_no,form_no)values 
			('" . $row1['code'] . "', '" . $row_lcode['C_NAME'] . "', " . $row1['amount'] . ",'" . $row['tmp_no'] . "','BTRN') ";
            $result = $conn->query($sql);
            
             
	 	    
	 	    
        }
        $sql = "Select * from a_tmp_che_data where tmp_no='" . $row["tmp_no"] . "' AND form_no='BTRN'";
        foreach ($conn->query($sql) as $row1) {
            $ResponseXML .= "<tr>                              
                         <td>" . $row1['code'] . "</td>
                         <td>" . $row1['descript'] . "</td>
                         <td></td>
                         <td>" . number_format($row1['amount'], 2, ".", ",") . "</td>
                         <td><a class=\"btn btn-danger btn-xs\" onClick=\"del_item('" . $row1['id'] . "','CN1')\"> <span class='fa fa-remove'></span></a></td>
                         </tr>";
            $mtot = $mtot + $row1['amount'];
            $i = $i + 1;
        }
        $ResponseXML .= "</table>]]></sales_table>";
        $ResponseXML .= "<subtot1><![CDATA[" . number_format($mtot, 2, ".", ",") . "]]></subtot1>";

        $prev = "";

        $sql = "select * from a_docs where refno = '" . $row["REFNO"] . "'";
        foreach ($conn->query($sql) as $row2) {

            $filetype = pathinfo($row2['loc'], PATHINFO_EXTENSION);
            $filetype = "application/" . $filetype;

            $prev .= "<div data-fileindex='3' width='160px' height='160px' id='" . $row2['id'] . "'  class='col-sm-2'>
                    
                
                    <object width='160px' height='160px' type='" . $filetype . "' data='" . $row2['loc'] . "'>
                        <div  class='file-preview-other'>
                            <span  class='file-icon-4x'><i class='glyphicon glyphicon-king'></i></span>
                        </div>
                    </object>                        

                    <div width='160px' class='file-thumbnail-footer'>
                        <div  title='" . $row2['file_name'] . "'  class='file-footer-caption'>" . $row2['file_name'] . "</div>

                        <div  class='file-actions'>
                            <div class='file-footer-buttons'>
                                
                                <a href='" . $row2['loc'] . "' download='" . $row2['file_name'] . "'><i class='glyphicon glyphicon glyphicon-save-file'></i></a>
                                <a onclick='removefile(" . $row2['id'] . ")'><i class='glyphicon glyphicon glyphicon-trash'></i></a>
                                 
                            </div>
                            <div class='clearfix'></div>
                        </div>
                    </div>
                </div> ";
        }

        $ResponseXML .= "<filebox><![CDATA[" . $prev . "]]></filebox>";
    }

    $ResponseXML .= "</salesdetails>";
    echo $ResponseXML;
}




if ($_GET["Command"] == "del_inv") {

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";
    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..');
    }
    
        $sql = "select REFNO,CANCEL,COMPANY from a_bankentmas where tmp_no ='" . $_GET['tmpno'] . "' and COMPANY = '" . $_SESSION['company'] . "'";
        $result = $conn->query($sql);
        if ($row = $result->fetch()) {
            if ($row['CANCEL'] != "0") {
                echo "Already Cancelled";
                exit();
            }

            if ($_SESSION['company'] != $row['COMPANY']) {
                echo "Invalid Login";
                exit();
            }


            $sql = "delete from a_ledger where l_refno = '" . $row['REFNO'] . "'";
            $conn->exec($sql);
            $sql = "update a_bankentmas set CANCEL='1' where refno = '" . $row['REFNO'] . "'";
            $conn->exec($sql);
            
            $sql = "update a_bankenttrn set CANCEL='1' where refno = '" . $row['REFNO'] . "'";
            $conn->exec($sql);
    
    
       $sqllog = "insert into entry_log(refno, username, docname, trnType, stime, sdate,company) values 
('" . $row['REFNO'] . "', '" . $_SESSION["CURRENT_USER"] . "', 'Bank Transction', 'Cancel', '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d") . "','".$_SESSION['company']."')";
$resultlog = $conn->query($sqllog);


            echo "ok";
            $conn->commit();
        } else {
            echo "Entry Not Found";
        }
    } catch (Exception $e) {
        $conn->rollBack();
        echo $e;
    }
}

if ($_GET["Command"] == "update_list") {
    $ResponseXML = "";
    $ResponseXML .= "<table class=\"table\">
	            <tr>
                        <th width=\"121\">Reference No</th>
                        <th width=\"121\">Date</th>
                       
                        <th width=\"200\">Remarks</th> 
                       
                    </tr>";


    $sql = "select REFNO, BDATE,heading,AMOUNT,ID from a_bankentmas where   REFNO <> ''";
    if ($_GET['refno'] != "") {
        $sql .= " and REFNO like '%" . $_GET['refno'] . "%'";
    }
    if ($_GET['cusname'] != "") {
        $sql .= " and heading like '%" . $_GET['cusname'] . "%'";
    }
    // if ($_SESSION['company'] != "C") {
    $sql .= " and COMPANY = '" . $_SESSION['company'] . "'";
    // }
    $stname = $_GET['stname'];

    $sql .= " ORDER BY ID desc limit 50";
    //echo $sql;
    foreach ($conn->query($sql) as $row) {
        $cuscode = $row["REFNO"];


        $ResponseXML .= "<tr>               
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['REFNO'] . "</a></td>
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['BDATE'] . "</a></td>
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['HEADING'] . "</a></td>
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['AMOUNT'] . "</a></td>    
                            </tr>";
    }
    $ResponseXML .= "</table>";
    echo $ResponseXML;
}
?>