<?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']) . "' and COMPANY = '" . $_SESSION['company'] . "' 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']) . "'  and COMPANY = '" . $_SESSION['company'] . "' 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']) . "'  and COMPANY = '" . $_SESSION['company'] . "' "; 
        $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']) . "'  and COMPANY = '" . $_SESSION['company'] . "'  ";
        $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']) . "'  and COMPANY = '" . $_SESSION['company'] . "'  ";
            $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;
        }
    }
}
