<?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"] == "new_inv") {
    $invno = getno();
    $sql = "Select colno from tmpinvpara";
    $result = $conn->query($sql);
    $row = $result->fetch();
    $tono = $row['colno'];
    $_SESSION["newInvTmpNo"] = $tono;
    $sql = "delete from tmp_po_data_dag where tmp_no='" . $tono . "'";
    $result = $conn->query($sql);

    $sql = "update tmpinvpara set colno=colno+1";
    $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;
}

function getno() {
   include './connection_sql.php';
    $sql = "select dcl from invpara where COMCODE='" . $_SESSION['company'] . "'";
    $result = $conn->query($sql);
    $row = $result->fetch();
    $tmpinvno = "000000" . $row["dcl"];
    $lenth = strlen($tmpinvno);
    return $invno=trim("DCL/").substr($tmpinvno, $lenth-5);
    // return $invno = substr($tmpinvno, $lenth - 6);
}


if ($_GET["Command"] == "setitem") {
    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";

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

    if ($_GET["Command1"] == "add_tmp") {
        $rate = str_replace(",", "", $_GET["itemPrice"]);
        $qty = str_replace(",", "", $_GET["qty"]);

    
     
        $discount = 0;
       $subtotal = $rate * $qty;
        $sql = "Insert into tmp_po_data_dag (stk_no, descript, qty, rate,subtot, tmp_no, srlNo, reqDate, brand)values 

            ('" . $_GET['itemCode'] . "', '" . $_GET['itemDesc'] . "',  " . $_GET['qty'] . "," . $rate . ",'" . $subtotal . "','" . $_GET['tmpno'] . "','" . $_GET['srlNo'] . "','" . $_GET['reqDate'] . "','" . $_GET['brand'] . "' ) ";
 
        $result = $conn->query($sql);
    }



    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
                   <tr>
                        <td style=\"width: 90px;\">Item</td>
                        <td>Size</td>     
                        <td>Brand</td>                          
                        <td>Price</td> 
                         <td>Qty</td> 
                        <td>Serial Number</td>  
                        <td>Required Date</td>
                       
                        <td style=\"width: 10px;\"></td>

                   </tr>";
    $i = 1;
    $mtot = 0;
    $sql = "Select * from tmp_po_data_dag where tmp_no='" . $_GET['tmpno'] . "'";
    foreach ($conn->query($sql) as $row) {
        $ResponseXML .= "<tr>                              
                            <td>" . $row['stk_no'] . "</td>
                            <td>" . $row['descript'] . "</td>
                             <td>" . $row['brand'] . "</td>
                              <td>" . $row['rate'] . "</td>
                              <td>" . $row['qty'] . "</td>
                             <td>" . $row['srlNo'] . "</td>
                             <td>" . $row['reqDate'] . "</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['subtot'];
        $i = $i + 1;
    }
    $mtot1 = 0;

    
     

    $ResponseXML .= "   </table>]]></sales_table>";
    $ResponseXML .= "<item_count><![CDATA[" . $i . "]]></item_count>"; 
    $ResponseXML .= "</salesdetails>";
    echo $ResponseXML;
}



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

    try {

        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $conn->beginTransaction();
 

        $sql = "select REF_NO,CANCELL,totpay,DEPARTMENT from s_salma_dcl where REF_NO ='" . $_GET['txt_entno'] . "'";

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

        if ($row = $result->fetch()) {
            exit("Already Entered");
            if ($row['CANCELL'] != "0") {

                echo "Already Cancelled";

                exit();
            }
  
            if ($row['totpay'] > 0) {
                echo "Already Paid";
                exit();
            } else {
                $invno = $row['REF_NO'];
            }

            $sql = "delete from s_salma_dcl where REF_NO = '" . $invno . "'";
            $conn->exec($sql);

 
            $sql = "delete from s_invo_dcl where ref_no = '" . $invno . "'";
            $conn->exec($sql);
      
           $sql = "delete from s_trn where REFNO = '" . $invno . "'";
            $conn->exec($sql);
        } else {
            $invno = getno();
            $sql = "update invpara set dcl=dcl+1 where COMCODE='" . $_SESSION['company'] . "'";
            $conn->exec($sql);
        }

        $subtot = str_replace(",", "", $_GET["subtot"]);
        $mtot = 0;
        $sql = "select * from tmp_po_data_dag where tmp_no='" . $_GET["tmpno"] . "'";
        foreach ($conn->query($sql) as $row) {
            $sqlinv = "select dagitem from invpara where COMCODE='" . $_SESSION['company'] . "'";
            $resultinv = $conn->query($sqlinv);
            $rowinv= $resultinv->fetch();
            $stkNo = trim($invno)."/".$_GET["customercode"]."/" . $rowinv["dagitem"];

           $sql = "Insert into s_mas (`STK_NO`, `BRAND_NAME`, `DESCRIPT`, `QTYINHAND`, `SELLING`, `COST`,type,SDATE,status,statusc) values 
        ('" . $stkNo . "','" . $row["brand"] . "','" . $row["descript"] . "'," . $row["qty"] . ", '" . $row["rate"] . "','".$row["rate"] ."','DAG', '" . $_GET['invdate'] . "','0','0')";

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

            $sql = "Insert into s_submas (STO_CODE,`STK_NO`, `DESCRIPt`, `QTYINHAND`) values 
             ('01','" . $stkNo . "','" . $row["descript"] . "', '" . $row["qty"] . "')";
 
            $result = $conn->query($sql);


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

            $sql = "Insert into s_invo_dcl (REF_NO, SDATE, STK_NO, DESCRIPT, QTY , PRICE, reqDate, BRAND,srlNo,status,statusc) values 

        ('" . trim($invno) . "', '" . $_GET['invdate'] . "','" . $stkNo . "','" . $row["descript"] . "', " . $row["qty"] . "," . $row["rate"] . ", '" . $row["reqDate"] . "','" . $row["brand"] . "','" . $row["srlNo"] . "','0','0')";
//            echo $sql;
            $result = $conn->query($sql);

            
             $mtot = $mtot + ($row["rate"] * $row["qty"]); 
        }
 
        
        

        $sql = "insert s_salma_dcl (REF_NO,SDATE,trn_type,  C_CODE, CUS_NAME,c_add1, vat,tmp_no,grand_tot,SAL_EX,gst,use_name,DEPARTMENT,company) values 

    ('" . $invno . "', '" . $_GET['invdate'] . "','DGI' ,'" . $_GET["customercode"] . "', '" . $_GET["customername"] . "','" . $_GET["cont_p"] . "','8','" . $_GET["tmpno"] . "','" .$mtot . "','" . $_GET['salesrep'] . "','8','" . $_SESSION['UserName'] . "','01','" . $_SESSION['company'] . "')";
 
        $result = $conn->query($sql);
 
         $sql = "delete from tmp_po_data_dag where stk_no='" . $_GET['itemCode'] . "' and tmp_no='" . $_GET['tmpno'] . "' ";
        $result = $conn->query($sql);

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

        $conn->commit();

        echo "Saved";
    } catch (Exception $e) {

        $conn->rollBack();

        echo $e;
    }
}

 

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

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";
    $sql = "Select * from s_salma_dcl where REF_NO='" . $_GET['refno'] . "'";
    $result = $conn->query($sql);
    if ($row = $result->fetch()) {
        $ResponseXML .= "<C_REFNO><![CDATA[" . $row["REF_NO"] . "]]></C_REFNO>";
        $ResponseXML .= "<C_DATE><![CDATA[" . $row["SDATE"] . "]]></C_DATE>"; 
        $ResponseXML .= "<C_CODE><![CDATA[" . $row["C_CODE"] . "]]></C_CODE>";
        $ResponseXML .= "<name><![CDATA[" . $row["CUS_NAME"] . "]]></name>";
        $ResponseXML .= "<txt_remarks><![CDATA[" . $row["REMARK"] . "]]></txt_remarks>";
        $ResponseXML .= "<Attn><![CDATA[" . $row['C_ADD1'] . "]]></Attn>";
        $ResponseXML .= "<tmp_no><![CDATA[" . $row["tmp_no"] . "]]></tmp_no>";
 
        $msg = "";

        if ($row['CANCELL'] == "1") {

            $msg = "Cancelled";
        }

        $ResponseXML .= "<msg><![CDATA[" . $msg . "]]></msg>";
  
        
       $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
                   <tr>
                        <td style=\"width: 90px;\">Item</td>
                        <td>Size</td>     
                        <td>Brand</td>   
                         <td>Qty</td> 
                        <td>Serial Number</td>  
                        <td>Required Date</td>
                       
                        <td style=\"width: 10px;\"></td>

                   </tr>";
    $i = 1;
    $mtot = 0;
    $sql = "Select * from s_invo_dcl where REF_NO='" . $_GET['refno'] . "'";
    foreach ($conn->query($sql) as $row) {
        $ResponseXML .= "<tr>                              
                            <td>" . $row['STK_NO'] . "</td>
                            <td>" . $row['DESCRIPT'] . "</td>
                             <td>" . $row['BRAND'] . "</td>
                              <td>" . $row['QTY'] . "</td> 
                             <td>" . $row['srlNo'] . "</td>
                             <td>" . $row['reqDate'] . "</td>
                              
                              
                             </tr>";
       $mtot = $mtot + $row['subtot'];
        $i = $i + 1;
    }
        $mtot1 = 0;
        $ResponseXML .= "   </table>]]></sales_table>";
        $ResponseXML .= "<item_count><![CDATA[" . $i . "]]></item_count>";
        
    }

 

    $ResponseXML .= "</salesdetails>";

    echo $ResponseXML;
}





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\"></th>   
                    </tr>";

    $sql = "select * from s_salma_dcl where    trn_type = 'DGI'";



    if ($_GET['refno'] != "") {
        $sql .= " and REF_NO like '%" . $_GET['refno'] . "%'";
    }
    
    if ($_GET['refno1'] != "") {
        $sql .= " and CUS_CODE like '%" . $_GET['refno1'] . "%'";
    }

    if ($_GET['cusname'] != "") {

        $sql .= " and CUS_NAME like '%" . $_GET['cusname'] . "%'";
    }

    $stname = $_GET['stname'];
 
    $sql .= "  ORDER BY id desc limit 50";
 
    foreach ($conn->query($sql) as $row) {

        $cuscode = $row["REF_NO"];
 
        $ResponseXML .= "<tr>       
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['REF_NO'] . "</a></td> 
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['SDATE'] . "</a></td>
                              <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['C_CODE'] . "</a></td>
                                  <td onclick=\"crnview('$cuscode', '$stname');\">" . $row['CUS_NAME'] . "</a></td>
                                      <td onclick=\"crnview('$cuscode', '$stname');\"></a></td>

                            </tr>";
    }

    $ResponseXML .= "</table>";

    echo $ResponseXML;
}







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

    $ResponseXML = "";

    $ResponseXML .= "<salesdetails>";

    try {

        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->beginTransaction();

        $sql = "select REF_NO,CANCELL,TOTPAY,DEPARTMENT,dele_no from s_salma_dcl where tmp_no ='" . $_GET['tmpno'] . "'";
        $result = $conn->query($sql);
        if ($row = $result->fetch()) {
            if ($row['CANCELL'] == "1") {
                echo "Already Cancelled";
                exit();
            }
            if ($row['CANCELL'] > 1) {
                echo "Already Processed, Cannot Cancel";
                exit();
            }
            $invno = $row['REF_NO'];
            $sql = "update s_salma_dcl set CANCELL='1' where REF_NO = '" . $row['REF_NO'] . "'";

            $conn->exec($sql);
 


            // $sql = "delete from s_trn where REFNO='" . $row['REF_NO'] . "'";

            // $conn->exec($sql);
 /*           
            //new
            $sqlInv="select * from s_invo_dcl where REF_NO='".$row['REF_NO']."' ";
            foreach ($conn->query($sqlInv) as $rowInv) {
                $sql2="update s_mas set QTYINHAND=QTYINHAND+".$rowInv["QTY"]." where STK_NO='".$rowInv['STK_NO']."'";
                $conn->exec($sql);
                $sql2="update s_submas set QTYINHAND=QTYINHAND+".$rowInv["QTY"]." where STO_CODE='".$row["DEPARTMENT"]."' and STK_NO='".$rowInv['STK_NO']."'";
                $conn->exec($sql);
            }
*/


            $sql = "update s_invo_dcl set CANCELL='1' where ref_no = '" . $row['REF_NO'] . "'";

            $conn->exec($sql);
            $sql = "select * from c_bal where REFNO ='" . $_GET['REF_NO'] . "'";
            $result = $conn->query($sql);
            if ($row = $result->fetch()) {
                exit('Process Complete... Cant Cancell');
            }
            

            echo "ok";

            $conn->commit();
        } else {

            echo "Entry Not Found";
        }
    } catch (Exception $e) {

        $conn->rollBack();

        echo $e;
    }
}
?>