<?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") {

    // $sql = "select GIN from invpara where flag='".$_SESSION['company']."'"; 
    // $result = $conn->query($sql);
    // $row = $result->fetch();
    // $tmpinvno = "000000" . $row["GIN"];
    // $lenth = strlen($tmpinvno);
    // $invno = trim("".$_SESSION['company']."/ST/") . substr($tmpinvno, $lenth - 12);
    
    $sql = "SELECT GIN FROM invpara where flag='".$_SESSION['company']."'"; 
    $result = $conn->query($sql); 
    $row = $result->fetch();
    $no = $row['GIN']; 
    $tmpinvno = "000000" . $no;
    $lenth = strlen($tmpinvno); 
    $refno =trim("".$_SESSION['company']."/ST/") . substr($tmpinvno, $lenth - 7); 

    // $sql = "Select QTNNO from tmpinvpara where flag='".$_SESSION['company']."'"; 
    // $result = $conn->query($sql);
    // $row = $result->fetch();

    $tono = uniqid();

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

    // $sql = "update tmpinvpara set QTNNO=QTNNO+1 where flag='".$_SESSION['company']."'"; 
    // $result = $conn->query($sql);


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


if($_GET["Command"]=="update")
{
   try {
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->beginTransaction();
    $sql="update s_ginmas  set AR_DATE='" . $_GET["DTARdate"] . "', AR_NO='" . $_GET["txtarno"] . "' where  tmp_no ='" . $_GET['tmpno'] . "'"; 
    $result1 = $conn->query($sql);
    $conn->commit();
    echo "ok";
} catch (Exception $e) {
    $conn->rollBack();
    echo $e;
}
}


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


    if ($_GET["tmpno"] == "") {
        exit("Error");
    }


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

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

    $qty = str_replace(",", "", $_GET["qty"]);



    $sql = "Insert into tmp_gin_data_multi (dep_from,str_invno, str_code, str_description, cur_qty, tmp_no, user_id)values 
    ('" . $_GET['from_dep'] . "','" . $_GET['invno'] . "', '" . $_GET['itemCode'] . "', '" . $_GET['itemDesc'] . "', " . $qty . ", '" . $_GET["tmpno"] . "', '" . $_SESSION["CURRENT_USER"] . "') ";
    $result = $conn->query($sql);
    if (!$result) {
        echo $sql . "<br>";
        echo mysqli_error($GLOBALS['dbinv']);
    }

    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
    <tr>
    <td style=\"width: 150px;\" align='center'><b>From Department</b></td>
    <td style=\"width: 155px;\" align='center'><b>Item Code</b></td>
    <td style=\"width: 380px;\" align='center'><b>Description</b></td>
    <td style=\"width: 80px;\" align='center'><b>Quantity</b></td>
    <td style=\"width: 10px;\"></td>
    </tr>";

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

        $ResponseXML .= "<tr>                              
        <td align='center'>" . $row['DESCRIPTION'] . "</td>
        <td align='center'>" . $row['str_code'] . "</td>
        <td align='left'>" . $row['str_description'] . "</td>
        <td align='center'>" . number_format($row['cur_qty'], 0, ".", ",") . "</td>
        <td><a class=\"btn btn-danger btn-sm\" onClick=\"del_item('" . $row['id'] . "')\"> <span class='fa fa-remove'></span></a></td>
        </tr>";
        $i = $i + 1;
    }

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

    echo $ResponseXML;
}

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

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

    $sql = "delete from tmp_gin_data_multi where id='" . $_GET['code'] . "' and tmp_no='" . $_GET['tmpno'] . "'";
    $result = $conn->query($sql);

    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
    <tr>
    <td style=\"width: 90px;\" align=\"center\">From Dep</td>
    <td style=\"width: 40px;\" align=\"center\">Item Code</td>
    <td style=\"width: 220px;\" align=\"center\">Description</td>
    <td style=\"width: 50px;\" align=\"center\">Qty</td>
    <td style=\"width: 10px;\"></td>
    </tr>";

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

        $ResponseXML .= "<tr>                              
        <td >" . $row['dep_from'] . " " . $row['DESCRIPTION'] . "</td>
        <td>" . $row['str_code'] . "</td>
        <td>" . $row['str_description'] . "</td>
        <td>" . number_format($row['cur_qty'], 0, ".", ",") . "</td>
        <td><a class=\"btn btn-danger btn-sm\" onClick=\"del_item('" . $row['id'] . "')\"> <span class='fa fa-remove'></span></a></td>
        </tr>";
        $i = $i + 1;
    }

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

    $ResponseXML .= "<item_count><![CDATA[" . $i . "]]></item_count>";


    $ResponseXML .= "</salesdetails>";

    echo $ResponseXML;
}

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


    if ($_GET["tmpno"] == "") {
        exit("Error");
    }

    $sql = "select * from s_ginmas where tmp_no = '" . $_GET["tmpno"] . "'";
    $result = $conn->query($sql);
    if ($row = $result->fetch()) {
        exit("Error");
    }

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

        $idate = $_GET["sdate"];

        $sql2 = "select * from s_stomas where CODE='" . $_GET["to_dep"] . "' ";
        $result1 = $conn->query($sql2);
        $row1 = $result1->fetch();
        $DESCRIPTION_to = $row1["DESCRIPTION"];



        $sql = "select dep_from,str_code,cur_qty from tmp_gin_data_multi where tmp_no='" . $_GET["tmpno"] . "' group by dep_from";
        foreach ($conn->query($sql) as $row) {

            // $sqlsub1 = "Select * from s_submas where STK_NO='" . $row["str_code"] . "' and QTYINHAND<'".$row['cur_qty']."' and STO_CODE='" . $_GET["from_dep"] . "'"; 
            // $resultsub1 = $conn->query($sqlsub1);
            // if ($rowsub1 = $resultsub1->fetch()) {
            //     exit('Under Stock');
            // }

            // $sql = "select GIN from invpara where flag='".$_SESSION['company']."'"; 
            // $result1 = $conn->query($sql);
            // $row1 = $result1->fetch();
            // $tmpinvno = "000000" . $row1["GIN"];
            // $lenth = strlen($tmpinvno);
            // $invno = trim("".$_SESSION['company']."/ST/") . substr($tmpinvno, $lenth - 12);
            
            
             $sql = "SELECT GIN FROM invpara where flag='".$_SESSION['company']."'"; 
            $result1 = $conn->query($sql); 
            $row1 = $result1->fetch();
            $no = $row1['GIN']; 
            $tmpinvno = "000000" . $no;
            $lenth = strlen($tmpinvno); 
            $invno =trim("".$_SESSION['company']."/ST/") . substr($tmpinvno, $lenth - 7); 
    
    

            $sql2 = "select * from s_stomas where CODE='" . trim($row['dep_from']) . "' ";
            $result2 = $conn->query($sql2);
            $row2 = $result2->fetch();
            $DESCRIPTION_from = $row2["DESCRIPTION"];


            $sql1 = "insert into s_ginmas(SDATE, REF_NO, DEP_FROM, DEP_F_NAME, DEP_TO, DEP_T_NAME, tmp_no,del_to,company,remark) values
            ('" . $idate . "', '" . $invno . "', '" . $row['dep_from'] . "', '" . $DESCRIPTION_from . "', '" . $_GET["to_dep"] . "', '" . $DESCRIPTION_to . "', '" . $_GET["tmpno"] . "','M','".$_SESSION['company']."','" . $_GET['remark'] . "')";
            $result2 = $conn->query($sql1);

            $sql = "select * from tmp_gin_data_multi where tmp_no='" . $_GET["tmpno"] . "' and dep_from = '" . $row['dep_from'] . "'"; 
            foreach ($conn->query($sql) as $row1) {
                $sqlmas = "select * from s_mas where STK_NO='" . $row1["str_code"] . "'";
            $resultmas = $conn->query($sqlmas);
            $rowmas = $resultmas->fetch();

                $cur_qty = str_replace(",", "", $row1["cur_qty"]);

                $sqlsub = "Select * from s_submas where STK_NO='" . $row1["str_code"] . "' and STO_CODE='" . $_GET["to_dep"] . "'";
                $resultsub = $conn->query($sqlsub);
                if ($rowsub = $resultsub->fetch()) {
                    $sql1 = "update s_submas set QTYINHAND=QTYINHAND+" . $cur_qty . " where STK_NO='" . $row1["str_code"] . "' and STO_CODE='" . $_GET["to_dep"] . "'";
                    $result1 = $conn->query($sql1);
                } else {
                    $sql1 = "insert into s_submas(STO_CODE, STK_NO, DESCRIPt, OPEN_STK, QTYINHAND) values ('" . $_GET["to_dep"] . "',  '" . $row1["str_code"] . "', '" . $row1["str_description"] . "', 0, " . $cur_qty . ")";
                    $result1 = $conn->query($sql1);
                }

                $sql1 = "update s_submas set QTYINHAND=QTYINHAND-" . $cur_qty . " where STK_NO='" . $row1["str_code"] . "' and STO_CODE='" . $row['dep_from'] . "'";
                $result1 = $conn->query($sql1);
                
                 $sql1 = "insert into s_gintrn(STK_NO, SDATE, QTY, TO_DEP, REFNO, FROM_DEP,COST,SUB_TOTAL,UNIQ) values 
                ('" . $row1["str_code"] . "', '" . $idate . "', '" . $cur_qty . "', '".$_GET["to_dep"]."', '" . $invno . "', '" . $row['dep_from'] . "','" . $rowmas["acc_cost"] . "','" . ($rowmas["acc_cost"]*$cur_qty) . "', '" . $_GET["tmpno"] . "')";
               $result1 = $conn->query($sql1);

                $sql1 = "insert into s_trn(STK_NO, SDATE, QTY, LEDINDI, REFNO, DEPARTMENT, seri_no, Dev, SAL_EX, ACTIVE, DONO,COST) values 
                ('" . $row1["str_code"] . "', '" . $idate . "', '" . $cur_qty . "', 'GINI', '" . $invno . "', '" . $row['dep_from'] . "', '', '" . $_SESSION['dev'] . "', '', '1', '','" . $rowmas["acc_cost"] . "')";
               $result1 = $conn->query($sql1);

                $sql1 = "insert into s_trn(STK_NO, SDATE, QTY, LEDINDI, REFNO, DEPARTMENT, seri_no, Dev, SAL_EX, ACTIVE, DONO,COST) values
                ('" . $row1["str_code"] . "', '" . $idate . "', '" . $cur_qty . "', 'GINR', '" . $invno . "', '" . $_GET["to_dep"] . "', '', '" . $_SESSION['dev'] . "', '', '1', '','" . $rowmas["acc_cost"] . "')";
                $result1 = $conn->query($sql1);
            }

            $sql1 = "update invpara set GIN=GIN+1 where flag='".$_SESSION['company']."'"; 
            $result1 = $conn->query($sql1);

            $sql2 = "insert into entry_log(refno, username, docname, trnType, stime, sdate) values ('" . $invno . "', '" . $_SESSION["CURRENT_USER"] . "', 'GIN', 'SAVE', '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d") . "')";
            $result = $conn->query($sql2);
        }

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


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

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";
    try {
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->beginTransaction();

        $sql = "select REF_NO,cancel from s_ginmas where tmp_no ='" . $_GET['tmpno'] . "'";
        $result = $conn->query($sql);
        if ($row = $result->fetch()) {
            if ($row['cancel'] != "0") {
                echo "Not Found";
                exit();
            } else {


                $sql = "select REF_NO , dep_to from s_ginmas where tmp_no ='" . $_GET['tmpno'] . "'";
                foreach ($conn->query($sql) as $row1) {

                    $sql = "select * from s_trn where REFNO='" . $row1["REF_NO"] . "' and LEDINDI='GINR'";


                    foreach ($conn->query($sql) as $row) {
                        $sql1 = "update s_submas set QTYINHAND=QTYINHAND-" . $row["QTY"] . " where STK_NO='" . $row["STK_NO"] . "' and STO_CODE='" . $row["DEPARTMENT"] . "'";
                        $result1 = $conn->query($sql1);
                    }


                    $sql = "select * from s_trn where REFNO='" . $row1["REF_NO"] . "' and LEDINDI='GINI'";

                    foreach ($conn->query($sql) as $row) {
                        $sql1 = "update s_submas set QTYINHAND=QTYINHAND+" . $row["QTY"] . " where STK_NO='" . $row["STK_NO"] . "' and STO_CODE='" . $row["DEPARTMENT"] . "'";
                        $result1 = $conn->query($sql1);
                    }

                    $sql1 = "delete from s_trn where REFNO='" . $row1["REF_NO"] . "'";
                    $result1 = $conn->query($sql1);


                    $sql1 = "update  s_ginmas set cancel='1' where tmp_no ='" . $_GET['tmpno'] . "'";
                    $result1 = $conn->query($sql1);
                    
                    $sql1 = "update  s_gintrn set CANCELL='1' where UNIQ ='" . $_GET['tmpno'] . "'";
                    $result1 = $conn->query($sql1);
                }




                echo "ok";

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






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

    //$department=$_GET["department"];

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


    $sql1 = "delete from tmp_gin_data_multi where tmp_no='" . $_GET["invno"] . "'";
    $result1 = $conn->query($sql1);

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

        $dep_to = $row['DEP_TO'];
        $sdate = $row['SDATE'];
        $remark_val = $row['remark'];

        $sql1 = "select * from s_trn where REFNO='" . $row['REF_NO'] . "' and LEDINDI='GINI'";

        foreach ($conn->query($sql1) as $row1) {

            $sql3 = "select * from s_mas where STK_NO='" . $row1['STK_NO'] . "'";
            $result3 = $conn->query($sql3);
            $row3 = $result3->fetch();

            $sql2 = "Insert into tmp_gin_data_multi (dep_from,str_invno, str_code, str_description, cur_qty, tmp_no, user_id)values 
            ('" . $row1['DEPARTMENT'] . "','" . $row['REF_NO'] . "', '" . $row1['STK_NO'] . "', '" . $row3['DESCRIPT'] . "', " . $row1["QTY"] . ", '" . $_GET["invno"] . "', '" . $_SESSION["CURRENT_USER"] . "') ";
            //echo $sql2;
            $result = $conn->query($sql2);
        }
    }


    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
    <tr>
    <td style=\"width: 90px;\">From Dep</td>
    <td style=\"width: 40px;\">Item</td>
    <td style=\"width: 220px;\">Description</td>
    <td style=\"width: 50px;\">Qty</td>
    <td style=\"width: 10px;\"></td>
    </tr>";

    $i = 1;
    $sql = "Select * from view_tmp_gin where tmp_no='" . $_GET['invno'] . "'";
    $result = $conn->query($sql);
    foreach ($conn->query($sql) as $row) {

        $ResponseXML .= "<tr>                              
        <td>" . $row['dep_from'] . " " . $row['DESCRIPTION'] . "</td>
        <td>" . $row['str_code'] . "</td>
        <td>" . $row['str_description'] . "</td>
        <td>" . number_format($row['cur_qty'], 0, ".", ",") . "</td>
        <td>" . $row['str_invno'] . "</td>
        </tr>";
        $i = $i + 1;
    }


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




    $ResponseXML .= "<tmp_no><![CDATA[" . $_GET['invno'] . "]]></tmp_no>";
    $ResponseXML .= "<to_dep><![CDATA[" . $dep_to . "]]></to_dep>";
    $ResponseXML .= "<sdate><![CDATA[" . $sdate . "]]></sdate>";
    $ResponseXML .= "<remark><![CDATA[" . $remark_val . "]]></remark>";
    $ResponseXML .= " </salesdetails>";
    echo $ResponseXML;
}



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

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

    
    $sql =  "Select * from s_mas where STK_NO='".$_GET['itno']."'";
    $result = $conn->query($sql); 
    if ($row = $result->fetch()) { 

        
        $ResponseXML .= "<str_code><![CDATA[".$row['STK_NO']."]]></str_code>";
        $ResponseXML .= "<str_description><![CDATA[".$row['DESCRIPT']."]]></str_description>";
        $ResponseXML .= "<str_partno><![CDATA[".$row['PART_NO']."]]></str_partno>";
                    //$ResponseXML .= "<str_selpri><![CDATA[".$row['SELLING']."]]></str_selpri>";
        
        

        $dt=date('Y-m-d', strtotime(date('Y-m-d'). ' - 90 days'));
        $sql1 = "Select sum(REC_QTY) as stk from s_purtrn where STK_NO='".$_GET['itno']."' and CANCEL='0' and SDATE>'".$dt."' ";
        $result1 = $conn->query($sql1);
        $row1 = $result1->fetch(); 
        
        $mnewstk=0;
        if (is_null($row1["stk"])==false){$mnewstk=$row1["stk"];}
        
        if ($row["QTYINHAND"]>$row1["stk"]) {
            $unsold=$row["QTYINHAND"]-$row1["stk"];
        }
        
        $department=trim(substr($_GET["department"], 0, 2));
        
        
        $sql = "select QTYINHAND from s_submas where STK_NO='".$_GET['itno']."' AND STO_CODE='".$department."'";
        $result = $conn->query($sql);
        if ($row = $result->fetch()) { 
            
            // if($row = mysqli_fetch_array($sql)){
            if (is_null($row["QTYINHAND"])==false){
                $ResponseXML .= "<qtyinhand><![CDATA[".$row["QTYINHAND"]."]]></qtyinhand>";
            }  else {
                $ResponseXML .= "<qtyinhand><![CDATA[0]]></qtyinhand>";
            }
        }  else {
            $ResponseXML .= "<qtyinhand><![CDATA[0]]></qtyinhand>";
        }


    }               


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

    $ResponseXML .= " <itemlist><![CDATA[ <table class=\"table table-bordered\" border=\"1\">
    <tr>
    <td width=\"122\" align=\"center\"><b>Department</b></td>
    <td width=\"51\" align=\"center\"><b>Quantity</b></td>
    <td width=\"51\" align=\"center\"><b>Pending Order</b></td>

    </tr>";

    $sql2 =  "Select * from s_stomas" ;
    
    foreach ($conn->query($sql2) as $row2) {
                // while ($row2 = mysqli_fetch_array($sql2)) {

        $sql3 = "Select QTYINHAND from s_submas where STO_CODE='" . $row2["CODE"] . "' and STK_NO='" . $_GET["itno"] . "' and qtyinhand >0";
        $result3 = $conn->query($sql3);
        if ($row3 = $result3->fetch()) { 
          
            $mord = 0;
            
            $sql4 =  "Select sum(qty) as qty from view_cusordmas_trn where department='" . $row2["CODE"] . "' and STK_NO='" . $_GET["itno"] . "' and cancell='0' and invno ='0'";
            $result4 = $conn->query($sql4);
            if ($row4 = $result4->fetch()) { 
                $mord = $row4['qty'];
            }
            
            $ResponseXML .= "<tr>
            <td width=\"122\" align=\"center\"><b>" . $row2["DESCRIPTION"] . "</b></font></td>
            <td width=\"51\"  align=center><b>" . number_format($row3["QTYINHAND"], 0, ".", ",") . "</b></font></td> 
            <td width=\"51\"  align=center><b>" . $mord . "</b></font></td>";



            $ResponseXML .= "</tr>";
        } else {

        }
    }

    

    $ResponseXML .= "</table>]]></itemlist>";

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


}   
?>