<?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"] == "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..');
        } 

        $sql = "delete from a_lcodes where c_code = '" . $_GET['txt_entno'] . "'";
        $conn->exec($sql);
        $ctype = "";


        if ($_GET['bank'] == "on") {
            $ctype = "B";
        }

        // Sanitize Monthly Data
        $jan = (isset($_GET['jan']) && $_GET['jan'] !== '') ? $_GET['jan'] : 0;
        $feb = (isset($_GET['feb']) && $_GET['feb'] !== '') ? $_GET['feb'] : 0;
        $mar = (isset($_GET['mar']) && $_GET['mar'] !== '') ? $_GET['mar'] : 0;
        $apr = (isset($_GET['apr']) && $_GET['apr'] !== '') ? $_GET['apr'] : 0;
        $may = (isset($_GET['may']) && $_GET['may'] !== '') ? $_GET['may'] : 0;
        $jun = (isset($_GET['jun']) && $_GET['jun'] !== '') ? $_GET['jun'] : 0;
        $jul = (isset($_GET['jul']) && $_GET['jul'] !== '') ? $_GET['jul'] : 0;
        $aug = (isset($_GET['aug']) && $_GET['aug'] !== '') ? $_GET['aug'] : 0;
        $sep = (isset($_GET['sep']) && $_GET['sep'] !== '') ? $_GET['sep'] : 0;
        $oct = (isset($_GET['oct']) && $_GET['oct'] !== '') ? $_GET['oct'] : 0;
        $nov = (isset($_GET['nov']) && $_GET['nov'] !== '') ? $_GET['nov'] : 0;
        $dec = (isset($_GET['dec']) && $_GET['dec'] !== '') ? $_GET['dec'] : 0;

        // Insert new record with month values
        // Note: Used 'COT_B' for October based on your DB schema
        $sql_lcode = "Insert Into a_lcodes(c_code, c_name, c_type, paccno, c_opbal, C_DATE ,c_remark,cur,rate,C_SUBGRO1,cat,C_SUBGRO2,COMPANY,P_SUBGRO1,P_SUBGRO2, JAN_B, FEB_B, MAR_B, APR_B, MAY_B, JUN_B, JUL_B, AUG_B, SEP_B, COT_B, NOV_B, DEC_B) Values "
        . "('" . trim($_GET["txt_entno"]) . "', '" . trim($_GET["txt_gl_name"]) . "', '" . $_GET['acctype'] . "','" . $_GET['paccno'] . "','" . $_GET['txt_Opening'] . "','" . $_GET["dtpOpenDate"] . "','" . $_GET["txt_remarks"] . "','" . $_GET['currency'] . "','" . $_GET['rate'] . "','" . $_GET['acType'] . "','" . $ctype . "','" . $_GET['acType1'] . "','" . $_GET['company'] . "','" . $_GET['ptype1'] . "','" . $_GET['ptype2'] . "', '" . $jan . "', '" . $feb . "', '" . $mar . "', '" . $apr . "', '" . $may . "', '" . $jun . "', '" . $jul . "', '" . $aug . "', '" . $sep . "', '" . $oct . "', '" . $nov . "', '" . $dec . "')";
 
        $conn->exec($sql_lcode);

        $mrefno = "BF/" . trim($_GET["txt_entno"]);
        
        $sql = "delete from a_ledger where l_refno = '" . $mrefno . "' and l_flag ='OPB' and COMPANY = '" .$_SESSION['company']. "'";
        $conn->exec($sql);

        $amo = $_GET['txt_Opening'] * $_GET["rate"];
        $mflag = ($_GET['txt_Opening'] < 0) ? "DEB" : "CRE";
        
        if (file_exists('./gl_posting.php')) {
            require_once './gl_posting.php';
            $ayear = ac_year($_GET["dtpOpenDate"]);
        } else {
            $ayear = date("Y", strtotime($_GET["dtpOpenDate"]));
        }
    
        $sql = "insert into a_ledger (l_refno, l_date, l_code, l_amount, l_flag, l_flag1,L_LMEM,Currency,rate,curamo,COMPANY,acyear) value
                  ('" . $mrefno . "','" . $_GET["dtpOpenDate"] . "','" . trim($_GET["txt_entno"]) . "','" . $amo . "','OPB','" . $mflag . "','Opening Balance','" . $_GET["currency"] . "','" . $_GET["rate"] . "','" . $_GET['txt_Opening'] . "','" . $_SESSION['company'] . "','" . $ayear . "')";
        $conn->exec($sql);
        
        $sqllog = "insert into a_entry_log(refno, username, docname, trnType, stime, sdate,company) values 
        ('" . $mrefno . "', '" . $_SESSION["CURRENT_USER"] . "', 'Account Master', 'Save', '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d") . "','".$_SESSION['company']."')";
        $conn->query($sqllog);

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


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

    $ResponseXML = "";

    $ResponseXML .= "<table   class=\"table table-bordered\">
                            <tr>
                              <th width=\"121\">Account Code</th>
                              <th width=\"424\">Account Name</th>
                            </tr>";

    if ($_GET["mstatus"] == "cusno") {
        $letters = $_GET['cusno'];
        $sql = "select c_code,c_name from a_lcodes where  c_code like '%$letters%' ORDER BY c_code";
    } else if ($_GET["mstatus"] == "customername") {
        $letters = $_GET['customername'];
        $sql = "select c_code,c_name from a_lcodes where  c_name like '%$letters%' ORDER BY c_code";
    } else {
        $letters = $_GET['customername'];
        $sql = "select c_code,c_name from a_lcodes where  c_name like '%$letters%' ORDER BY c_code";
    }

    foreach ($conn->query($sql) as $row) {
        $cuscode = $row["c_code"];
        $stname = $_GET["stname"];

        $ResponseXML .= "<tr>
                              <td onclick=\"ledgno('$cuscode', '$stname');\">" . $row['c_code'] . "</a></td>
                              <td onclick=\"ledgno('$cuscode', '$stname');\">" . $row['c_name'] . "</a></td>
                            </tr>";
    }

    $ResponseXML .= "   </table>";

    echo $ResponseXML;
}



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

    header('Content-Type: text/xml');
    echo "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";

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

    if ($_GET['stname'] == "mas") {
        $sql = "select * from a_lcodes where c_code='" . $_GET["ledgno"] . "'";
    } else {
        $sql = "select C_CODE, C_NAME from a_lcodes where c_code='" . $_GET["ledgno"] . "'";
    }

    $result = $conn->query($sql);
    if ($row = $result->fetch()) {
        $ResponseXML .= "<c_code><![CDATA[" . $row['C_CODE'] . "]]></c_code>";
        $ResponseXML .= "<c_name><![CDATA[" . $row['C_NAME'] . "]]></c_name>";
        $ResponseXML .= "<stname><![CDATA[" . $_GET['stname'] . "]]></stname>";
        $ResponseXML .= "<company><![CDATA[" . $row['COMPANY'] . "]]></company>";
        $ResponseXML .= "<ptype1><![CDATA[" . $row['P_SUBGRO1'] . "]]></ptype1>";
        $ResponseXML .= "<ptype2><![CDATA[" . $row['P_SUBGRO2'] . "]]></ptype2>";

        // Retrieve Monthly Data
        $ResponseXML .= "<jan><![CDATA[" . ($row['JAN_B'] ? $row['JAN_B'] : 0) . "]]></jan>";
        $ResponseXML .= "<feb><![CDATA[" . ($row['FEB_B'] ? $row['FEB_B'] : 0) . "]]></feb>";
        $ResponseXML .= "<mar><![CDATA[" . ($row['MAR_B'] ? $row['MAR_B'] : 0) . "]]></mar>";
        $ResponseXML .= "<apr><![CDATA[" . ($row['APR_B'] ? $row['APR_B'] : 0) . "]]></apr>";
        $ResponseXML .= "<may><![CDATA[" . ($row['MAY_B'] ? $row['MAY_B'] : 0) . "]]></may>";
        $ResponseXML .= "<jun><![CDATA[" . ($row['JUN_B'] ? $row['JUN_B'] : 0) . "]]></jun>";
        $ResponseXML .= "<jul><![CDATA[" . ($row['JUL_B'] ? $row['JUL_B'] : 0) . "]]></jul>";
        $ResponseXML .= "<aug><![CDATA[" . ($row['AUG_B'] ? $row['AUG_B'] : 0) . "]]></aug>";
        $ResponseXML .= "<sep><![CDATA[" . ($row['SEP_B'] ? $row['SEP_B'] : 0) . "]]></sep>";
        $ResponseXML .= "<oct><![CDATA[" . ($row['COT_B'] ? $row['COT_B'] : 0) . "]]></oct>"; // COT_B used
        $ResponseXML .= "<nov><![CDATA[" . ($row['NOV_B'] ? $row['NOV_B'] : 0) . "]]></nov>";
        $ResponseXML .= "<dec><![CDATA[" . ($row['DEC_B'] ? $row['DEC_B'] : 0) . "]]></dec>";

        if ($_GET['stname'] == "mas") {
            $prev = "";
            $sql = "select * from a_docs where refno = '" . $row["C_CODE"] . "'";
            foreach ($conn->query($sql) as $row2) {
                $filetype = pathinfo($row2['loc'], PATHINFO_EXTENSION);
                $filetype = "application/" . $filetype;
                $prev .= "<div data-fileindex='3' width='160px' height='160px' class='col-sm-2'>
                            <div width='160px' class='file-thumbnail-footer'>
                                <div class='file-footer-caption'>" . $row2['file_name'] . "</div>
                            </div>
                        </div> ";
            }
            $ResponseXML .= "<filebox><![CDATA[" . $prev . "]]></filebox>";
            $ResponseXML .= "<c_type><![CDATA[" . trim($row['C_TYPE']) . "]]></c_type>";
            
            $sql = "select * from a_ledger where l_code = '" . $row['C_CODE']  . "' and l_flag = 'OPB' and COMPANY= '" . $_SESSION['company'] . "'";
            $result1 = $conn->query($sql);
            $lamo=0;
            $ldate = "";
            if ($rowOp = $result1->fetch()){
                $lamo = $rowOp['L_AMOUNT'];
                $ldate = $rowOp['L_DATE'];
            }
            
            $ResponseXML .= "<txt_Opening><![CDATA[" . $lamo . "]]></txt_Opening>";
            $ResponseXML .= "<dtpOpenDate><![CDATA[" . $ldate . "]]></dtpOpenDate>";
            $ResponseXML .= "<cur><![CDATA[" . $row['cur'] . "]]></cur>";
            $ResponseXML .= "<rate><![CDATA[" . $row['rate'] . "]]></rate>";
            $ResponseXML .= "<txt_gl_code><![CDATA[" . $row['PAccNo'] . "]]></txt_gl_code>";
            $ResponseXML .= "<c_remark><![CDATA[" . $row['C_REMARK'] . "]]></c_remark>";
            $ResponseXML .= "<acctype><![CDATA[" . trim($row['CAT']) . "]]></acctype>";
            $ResponseXML .= "<actype><![CDATA[" . $row['C_SUBGRO1'] . "]]></actype>";
            $ResponseXML .= "<actype1><![CDATA[" . $row['C_SUBGRO2'] . "]]></actype1>";

            $sql = "select C_NAME from a_lcodes where c_code='" . $row['PAccNo'] . "'";
            $result1 = $conn->query($sql);
            if ($row1 = $result1->fetch()) {
                $ResponseXML .= "<txt_gl_name><![CDATA[" . $row1['C_NAME'] . "]]></txt_gl_name>";
            } else {
                $ResponseXML .= "<txt_gl_name><![CDATA[]]></txt_gl_name>";
            }
        }
    }
    $ResponseXML .= "</salesdetails>";
    echo $ResponseXML;
}

if ($_GET["Command"] == "get_list") {
    $result = array();
    $sql = "select c_code,c_name from a_lcodes where  c_name like '%". $_GET['term'] . "%' ORDER BY c_code limit 10";
    foreach ($conn->query($sql) as $items) {
        array_push($result, array("id" => $items['c_code'], "label" => $items['c_code'] . '-' . $items['c_name'], "name" => $items['c_name']));
    }
    echo json_encode($result);
}
?>