<?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 /////////////////////////////////////////////////////////////////////////

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

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

    $sql = "SELECT trans_no FROM invpara";
    $result = $conn->query($sql);
    $row = $result->fetch();
    $no = $row['trans_no'];
    $ResponseXML .= "<id><![CDATA[$invno]]></id>";
    $ResponseXML .= "</new>";

    echo $ResponseXML;
}

function getno($sdate) {
header('Content-Type: text/xml');
    include './connection_sql.php';
    $sql = "SELECT trans_no FROM invpara";
    $result = $conn->query($sql);
    $row = $result->fetch();
    $tmpinvno = "00000" . $row["trans_no"];
    $lenth = strlen($tmpinvno);
    $invno = trim("TRN/") . substr($tmpinvno, $lenth - 8);

    return $invno;
    
}

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

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


    $sql = "delete from tmp_po_data where stk_no='" . $_GET['itemCode'] . "' and tmp_no='" . $_GET['tmpno'] . "' ";
    $result = $conn->query($sql);
    if ($_GET["Command1"] == "add_tmp") {

        $sql = "Insert into tmp_po_data(stk_no, descript,rate, tmp_no,serial_no,brand)values 
			('" . $_GET['itemCode'] . "', '" . $_GET['itemDesc'] . "','" . $_GET['itemPrice'] . "','" . $_GET['tmpno'] . "','" . $_GET['serial_no'] . "','" . $_GET['brand'] . "')";
        $result = $conn->query($sql);
    }

    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
					<tr>
						<td style=\"width: 20%;\">Item</td>
						<td style=\"width: 20%;\">Description</td>
						<td style=\"width: 20%;\">Brand</td>
						<td style=\"width: 20%;\">Serial No</td>
						<td style=\"width: 20%;\">Rate</td>
						<td style=\"width: 20%;\"></td>
					</tr>";

    $i = 1;
    $mtot = 0;
    $sql = "Select * from tmp_po_data where tmp_no='" . $_GET['tmpno'] . "'";
    foreach ($conn->query($sql) as $row) {

        $ResponseXML .= "<tr>                              
                             <td ondblclick=\"setitem('" . $row['stk_no'] . "','" . $row['descript'] . "','" . $row['rate'] . "');\">" . $row['stk_no'] . "</td>
                             <td ondblclick=\"setitem('" . $row['stk_no'] . "','" . $row['descript'] . "','" . $row['rate'] . "');\">" . $row['descript'] . "</td>
                             <td ondblclick=\"setitem('" . $row['stk_no'] . "','" . $row['descript'] . "','" . $row['rate'] . "');\">" . $row['brand'] . "</td>
                             <td ondblclick=\"setitem('" . $row['stk_no'] . "','" . $row['descript'] . "','" . $row['rate'] . "');\">" . $row['serial_no'] . "</td>
                             <td ondblclick=\"setitem('" . $row['stk_no'] . "','" . $row['descript'] . "','" . $row['rate'] . "');\">" . number_format($row['rate'], 2, ".", ",") . "</td>
                             <td><a class=\"btn btn-danger btn-xs\" onClick=\"del_item('" . $row['stk_no'] . "')\"> <span class='fa fa-remove'></span></a></td>
                             </tr>";

        $mtot = $mtot + $row['rate'];
        $i = $i + 1;
    }

    $ResponseXML .= "   </table>]]></sales_table>";
    $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();

        $sql = "select * from tmp_transfer where tmp_no='" . $_GET["Transfer_No"] . "'";
        foreach ($conn->query($sql) as $row) {

            $sql1 = "Insert into dag_transfer_item(REFNO, STK_NO, DESCRIPT, RATE ,serial_no,brand,LVL) values 
		('" . $_GET["Transfer_No"] . "','" . $row["stk_no"] . "','" . $row["descript"] . "', '" . $row["rate"] . "', '" . $row["serial_no"] . "', '" . $row["brand"] . "','3')";
            $result = $conn->query($sql1);

            $sql = "update s_ordtrn_dag set lvl='3' where serial_no = '" . $row["serial_no"] . "'";
            $result = $conn->query($sql);
        }

        $sql = "insert into dag_transfer(REFNO, SDATE, SUP_CODE, SUP_NAME,COUNTRY, REMARK, AMOUNT) values 
                                        ('" . $_GET["Transfer_No"] . "', '" . $_GET['invdate'] . "', '" . $_GET["Suppler"] . "', '" . $_GET["s_name"] . "' ,'" . $_GET['con'] . "','" . $_GET['Remark'] . "','" . $_GET['subtot'] . "')";

        $result = $conn->query($sql);


        $sql = "delete from tmp_transfer where tmp_no='" . $_GET['Transfer_No'] . "' ";
        $result = $conn->query($sql);

        $sql = "SELECT trans_no FROM invpara";
        $resul = $conn->query($sql);
        $row = $resul->fetch();
        $no = $row['trans_no'];
        $no2 = $no + 1;
        $sql = "update invpara set trans_no = $no2 where trans_no = $no";
        $result = $conn->query($sql);
        

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

if ($_GET["Command"] == "checked1") {
    try {
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->beginTransaction();

        $ResponseXML = "";

        if ($_GET['stat'] == "1") {

            $sql = "select * from view_dag_invoice where serial_no = '" . $_GET["serial"] . "'";
            foreach ($conn->query($sql) as $row) {

                $sql = "Insert into tmp_transfer(stk_no, descript, rate, tmp_no, serial_no, brand, po_number)values
('" . $row['STK_NO'] . "', '" . $row['DESCRIPT'] . "', '" . $row['RATE'] . "', '" . $_GET['transfer'] . "', '" . $_GET["serial"] . "', '" . $row['brand'] . "', '" . $row['REFNO'] . "')";
                $result = $conn->query($sql);

            }
            
        } else {

            $sql = "delete from tmp_transfer where serial_no = '" . $_GET['serial'] . "'";
            $result = $conn->query($sql);
          
            
        }
         $sql1 ="select sum(rate) as tot from tmp_transfer  where  tmp_no='" . $_GET['transfer'] . "'";
         $result1 = $conn->query($sql1);
         $row1 = $result1->fetch();

        $ResponseXML .= "<subtot><![CDATA[" . number_format($row1["tot"], 2, ".", ", ") . "]]></subtot>";

        echo $ResponseXML;
        $conn->commit();
    } 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=\"100\">Code</th> 
                        <th width=\"200\">Name</th> 
                        <th width=\"121\">Amount</th>  
                    </tr>";


    $sql = "select REFNO, SDATE,SUP_CODE,SUP_NAME,AMOUNT from s_ordmas where refno <> ''";

    if ($_GET['refno'] != "") {
        $sql .= " and REFNO like '%" . $_GET['refno'] . "%'";
    }
    if ($_GET['cusname'] != "") {
        $sql .= " and SUP_NAME like '%" . $_GET['cusname'] . "%'";
    }
    $stname = $_GET['stname'];

    $sql .= " ORDER BY REFNO desc limit 50";

    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['SDATE'] . "</a></td>
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['SUP_CODE'] . "</a></td>
                                  <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['SUP_NAME'] . "</a></td>
                                      <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['AMOUNT'] . "</a></td>
                            </tr>";
    }
    $ResponseXML .= "</table>";
    echo $ResponseXML;
}



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

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



    $ResponseXML .= "<sales_table><![CDATA[<table style=\"width:100%;\" class=\"table\">
					<tr>
					
					</tr>";


    $sql = "Select * from view_dag_invoice where REFNO <> ''  and lvl = '0'";

    if ($_GET['Item'] != "") {
        $sql .= " and STK_NO like '%" . $_GET['Item'] . "%'";
    }
    if ($_GET['Description'] != "") {
        $sql .= " and Descript like '" . $_GET['Description'] . "%'";
    }
    if ($_GET['Brand'] != "") {
        $sql .= " and brand like '" . $_GET['Brand'] . "%'";
    }
    if ($_GET['SerialN'] != "") {
        $sql .= " and serial_no like '" . $_GET['SerialN'] . "%'";
    }
    if ($_GET['Rate'] != "") {
        $sql .= " and AMOUNT like '" . $_GET['Rate'] . "%'";
    }

    $sql .= " order by REFNO desc limit " . $_GET['limit'];



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

        $ResponseXML .= "<tr>
                             <td style=\"width: 10%;\">" . $row['STK_NO'] . "</td>
                             <td style=\"width: 30%;\">" . $row['DESCRIPT'] . "</td>
                             <td style=\"width: 20%;\">" . $row['brand'] . "</td>
                             <td style=\"width: 20%;\">" . $row['serial_no'] . "</td>
                             <td style=\"width: 15%;\">" . $row['RATE'] . "</td>
                             <td style=\"width: 5%;text-align: center;\" ><input name='checkfield' id=" . $row['serial_no'] . " onClick ='checked_item1(this.id)' type='checkbox'></td>
                        </tr>";
    }

    $ResponseXML .= "   </table>]]></sales_table>";
    $ResponseXML .= "</salesdetails>";

    echo $ResponseXML;
}
?>
