<?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") {
    
    if ($_SESSION['company'] == "") { 
        exit("Please Login");
    }
    if ($_SESSION['CURRENT_USER'] == "") { 
        exit("Please Login");
    }


    // $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("GIN/" .$_SESSION["BRANCH_NUM"]."/") . substr($tmpinvno, $lenth - 8); 

    // $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 user_id='" . $_SESSION["CURRENT_USER"] . "'";
    $result = $conn->query($sql);
    
    $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'] . "' and type='GIN TRANSFER' ";
    $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,rate,type)values 
    ('" . $_GET['from_dep'] . "','" . $_GET['invno'] . "', '" . $_GET['itemCode'] . "', '" . $_GET['itemDesc'] . "', " . $qty . ", '" . $_GET["tmpno"] . "', '" . $_SESSION["CURRENT_USER"] . "', '" . $_GET["rate"] . "','GIN TRANSFER') ";
    $result = $conn->query($sql);
    // if (!$result) {
    //     echo $sql . "<br>";
    //     echo mysqli_error($GLOBALS['dbinv']);
    // }

    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table \">
    <tr class=\"info\">
    <td style=\"width: 15%;\" align='center'><b>From Department</b></td>
    <td style=\"width: 15%;\" align='center'><b>Item Code</b></td>
    <td style=\"width: 30%;\" align='center'><b>Description</b></td>
    <td style=\"width: 15%;\" align='right'><b>Rate</b></td>
    <td style=\"width: 15%;\" align='center'><b>Quantity</b></td>
    <td style=\"width: 15%;\" align='center'><b>Unit Total</b></td>
    <td style=\"width: 5%;\"></td>
    </tr>";

    $i = 1;
    $totamo =0;
    $sql = "Select * from view_tmp_gin where tmp_no='" . $_GET['tmpno'] . "' and type='GIN TRANSFER' ";
    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='right'>" . $row['rate'] . "</td>
        <td align='center'>" . number_format($row['cur_qty'], 0, ".", ",") . "</td>
        <td align='center'>" . number_format(($row['rate']*$row['cur_qty']), 2, ".", ",") . "</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;
        $totamo = $totamo + ($row['rate']*$row['cur_qty']);
    }

    $ResponseXML .= "</table>]]></sales_table>";
    $ResponseXML .= "<item_count><![CDATA[" . $i . "]]></item_count>";
    $ResponseXML .= "<totamo><![CDATA[" . $totamo . "]]></totamo>";
    $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'] . "' and type='GIN TRANSFER' ";
    $result = $conn->query($sql);

    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
    <tr class=\"info\">
    <td style=\"width: 15%;\" align='center'><b>From Department</b></td>
    <td style=\"width: 15%;\" align='center'><b>Item Code</b></td>
    <td style=\"width: 30%;\" align='center'><b>Description</b></td>
    <td style=\"width: 15%;\" align='center'><b>Rate</b></td>
    <td style=\"width: 15%;\" align='center'><b>Quantity</b></td>
    <td style=\"width: 5%;\"></td>
    </tr>";

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

        $ResponseXML .= "<tr>                              
        <td align='center'>" . $row['dep_from'] . " " . $row['DESCRIPTION'] . "</td>
        <td align='center'>" . $row['str_code'] . "</td>
        <td align='left'>" . $row['str_description'] . "</td>
        <td align='right'>" . $row['rate'] . "</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"] == "save_item") {
    
    if ($_SESSION['company'] == "") { 
        exit("Please Login");
    }
    if ($_SESSION['CURRENT_USER'] == "") { 
        exit("Please Login");
    }

    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,rate,type from tmp_gin_data_multi where tmp_no='" . $_GET["tmpno"] . "' and type='GIN TRANSFER' 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');
            // }
            
            
                $sql1 = "SELECT GIN FROM invpara where flag='".$_SESSION['company']."'"; 
                $result1 = $conn->query($sql1); 
                $row1 = $result1->fetch();
                $no = $row1['GIN']; 
                $tmpinvno = "000000" . $no;
                $lenth = strlen($tmpinvno); 
                $invno =trim("GIN/" .$_SESSION["BRANCH_NUM"]."/") . substr($tmpinvno, $lenth - 8); 
            
            // $invno=$_GET["invno"];
    
            $sql2 = "select * from s_stomas where CODE='" . $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,TYPE) values
            ('" . $idate . "', '" . $invno . "', '" . $row['dep_from'] . "', '" . $DESCRIPTION_from . "', '" . $_GET["to_dep"] . "', '" . $DESCRIPTION_to . "', '" . $_GET["tmpno"] . "','M','".$_SESSION['company']."','GIN TRANSFER')";
            // echo $sql1;
            $result2 = $conn->query($sql1);
            
            $invtot = 0;
            $sql = "select * from tmp_gin_data_multi where tmp_no='" . $_GET["tmpno"] . "' and dep_from = '" . $row['dep_from'] . "' and type='GIN TRANSFER' "; 
            // echo $sql;
            foreach ($conn->query($sql) as $row1) {

                $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"] . "'";
                    // echo $sql1;
                    $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 . "',  '" . $row1["str_code"] . "')";
                    // echo $sql1;
                    $result1 = $conn->query($sql1);
                }
                
                $sql1="insert into s_gintrn(REFNO, SDATE, STK_NO, DESCRIPT, ORD_QTY, partno, CANCEL, tmp_no, RATE,TYPE,company) values 
                ('".$invno."', '".$idate."', '".$row1["str_code"]."','".$row1["str_description"]."','".$cur_qty."','".$row1["partno"]."','0', '".$row1['tmp_no']."','".$row1["rate"]."','".$row1["type"]."','".$_SESSION['company']."')";
                $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'] . "'";
                // echo $sql1;
                $result1 = $conn->query($sql1);
                
                $sql1 = "update s_mas set QTYINHAND=QTYINHAND-" . $cur_qty . " where STK_NO='" . $row1["str_code"] . "'";
                $result1 = $conn->query($sql1);

                $sql1 = "insert into s_trn(STK_NO, SDATE, QTY, LEDINDI, REFNO, DEPARTMENT, seri_no, Dev, SAL_EX, ACTIVE, DONO,cost,TYPE,company) values 
                ('" . $row1["str_code"] . "', '" . $idate . "', '" . $cur_qty . "', 'GININ', '" . $invno . "', '" . $row['dep_from'] . "', '', '" . $_SESSION['dev'] . "', '', '1', '','" . $row1["rate"] . "','".$row1["type"]."','".$_SESSION['company']."')";
                // echo $sql1;
                $result1 = $conn->query($sql1);

                $sql1 = "insert into s_trn(STK_NO, SDATE, QTY, LEDINDI, REFNO, DEPARTMENT, seri_no, Dev, SAL_EX, ACTIVE, DONO,cost,TYPE,company) values
                ('" . $row1["str_code"] . "', '" . $idate . "', '" . $cur_qty . "', 'GINRN', '" . $invno . "', '" . $_GET["to_dep"] . "', '', '" . $_SESSION['dev'] . "', '', '1', '','" . $row1["rate"] . "','".$row1["type"]."','".$_SESSION['company']."')";
                // echo $sql1;
                $result1 = $conn->query($sql1);
                
                $invtot += $cur_qty*$row1["rate"];
            }
            
            $customercode = $_GET["to_dep"];
            $sql = "select DESCRIPTION from s_stomas where CODE = '". $customercode ."' ";
            $result = $conn->query($sql); 
            $row = $result->fetch();
            $customername = $row["DESCRIPTION"];
            $dep = $_GET["from_dep"];
            
            $sqlisalma="Insert into s_salma_gin(REF_NO, TRN_TYPE, SDATE, C_CODE,CUS_NAME, AMOUNT, GRAND_TOT,  TOTPAY,   DEPARTMENT, CANCELL, DEV, tmp_no, use_name,company) values
            ('".$invno."', 'GINN', '".$idate."', '".trim($customercode)."','".trim($customername)."', '".$invtot."' , '".$invtot."', 0, '".trim($dep)."','0', '".$_SESSION['dev']."', '".$row1['tmp_no']."', '".$_SESSION["CURRENT_USER"]."','".$_SESSION['company']."')"; 
            $resultsalma = $conn->query($sqlisalma);

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

            $sql = "delete from tmp_gin_data_multi where tmp_no='" . $_GET["tmpno"] . "' and type='GIN TRANSFER' ";
            $result = $conn->query($sql);
            
            $sql2 = "insert into entry_log(refno, username, docname, trnType, stime, sdate,company) values 
            ('" . $invno . "', '" . $_SESSION["CURRENT_USER"] . "', 'GIN', 'SAVE', '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d") . "','".$_SESSION['company']."')";
            $result = $conn->query($sql2);
        }
        
        
        echo "Saved";
        
        $conn->commit();
    } 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();
        
        if ($_SESSION['company'] == "") { 
            exit("Please Login");
        }
        if ($_SESSION['CURRENT_USER'] == "") { 
            exit("Please Login");
        }

        $sql = "select REF_NO,cancel from s_ginmas where tmp_no ='" . $_GET['tmpno'] . "' and TYPE='GIN TRANSFER'";
        $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'] . "' and TYPE='GIN TRANSFER' ";
                foreach ($conn->query($sql) as $row1) {

                    $sql = "select * from s_trn where REFNO='" . $row1["REF_NO"] . "' and LEDINDI='GINRN' and TYPE='GIN TRANSFER' ";
                    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='GININ' and TYPE='GIN TRANSFER' ";
                    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 = "update s_mas set QTYINHAND=QTYINHAND+" . $row["QTY"] . " where STK_NO='" . $row["STK_NO"] . "'";
                        $result1 = $conn->query($sql1);
                    }

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

                    $sql1 = "update  s_ginmas set cancel='1' where tmp_no ='" . $_GET['tmpno'] . "' and TYPE='GIN TRANSFER' ";
                    $result1 = $conn->query($sql1);
                    
                    $sql1 = "update  s_gintrn set CANCEL='1' where tmp_no ='" . $_GET['tmpno'] . "' and TYPE='GIN TRANSFER' ";
                    $result1 = $conn->query($sql1);
                    
                    $sql1 = "update  s_salma_gin set CANCELL='1' where REF_NO ='" .$row1["REF_NO"] . "'  ";
                    $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"] . "' and type='GIN TRANSFER' ";
    $result1 = $conn->query($sql1);


    $sql = "select * from s_ginmas where tmp_no='" . $_GET['invno'] . "' and TYPE='GIN TRANSFER'";

    foreach ($conn->query($sql) as $row) {
        
        $REF_NO = $row['REF_NO'];
        $dep_to = $row['DEP_TO'];
        $sdate = $row['SDATE'];

        // $sql1 = "select * from s_trn where REFNO='" . $row['REF_NO'] . "' and TYPE='GIN TRANSFER' and LEDINDI='GININ'";
        // // echo $sql1;
        // 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,type,rate)values 
        //     ('" . $row1['DEPARTMENT'] . "','" . $row['REF_NO'] . "', '" . $row1['STK_NO'] . "', '" . $row3['DESCRIPT'] . "', " . $row1["QTY"] . ", '" . $_GET["invno"] . "', '" . $_SESSION["CURRENT_USER"] . "','GIN TRANSFER','" . $row1["cost"] . "') ";
        //     // echo $sql2;
        //     $result = $conn->query($sql2);
        // }
    }


    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">
    	<tr>
						<th width='70'>Item Code</th>
						<th width='400'>Description</th>
						<th width='60'>Rate</th>
						<th width='50'>Quantity</th>
						<th width='50'>Value</th> 
					</tr>";

    $i = 1;
    // $sql = "Select * from view_tmp_gin where tmp_no='" . $_GET['invno'] . "' and type='GIN TRANSFER' ";
    $sql = "Select * from s_gintrn where REFNO='" . $REF_NO . "'";
    $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>";
         $ResponseXML .= "<tr>                              
                         <td align='center'>" . $row['STK_NO'] . "</td>
                         <td align='left'>" . $row['DESCRIPT'] . "</td>
                         <td align='right'>" . number_format($row['RATE'], 2, ".", ",") . "</td>
                         <td align='right'>" . number_format($row['ORD_QTY'], 0, ".", ",") . "</td> 
                         <td align='right'>" . number_format($row['ORD_QTY']*$row['RATE'], 2, ".", ",") . "</td> 
                         </tr>";
        $i = $i + 1;
    }


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

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



if ($_GET["Command"]=="pass_item_inv"){
    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['COST']."]]></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;
}   

if ($_POST["Command"] == "search_item_search") {

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

     $sql = "select * from s_mas  where   STK_NO <> ''";  
    
     
    if ($_POST['s_refno'] != "") {
        $sql .= " and STK_NO like '%" . $_POST['s_refno'] . "%'";
    }
    if ($_POST['s_desc'] != "") {
        $sql .= " and DESCRIPT like '%" . $_POST['s_desc'] . "%'";
    }
    if ($_POST['s_brand'] != "") {
        $sql .= " and BRAND_NAME like '%" . $_POST['s_brand'] . "%'";
    }
    if ($_POST['s_partno'] != "") {
        $sql .= " and PART_NO like '%" . $_POST['s_partno'] . "%'";
    }
    if ($_POST['s_cat'] != "") {
        $sql .= " and Cat1 like '%" . $_POST['s_cat'] . "%'";
    }
    if ($_POST['s_cost'] != "") {
        $sql .= " and COST like '%" . $_POST['s_cost'] . "%'";
    }
    if ($_POST['s_cash'] != "") {
        $sql .= " and SELLING like '%" . $_POST['s_cash'] . "%'";
    }
    if ($_POST['s_credit'] != "") {
        $sql .= " and CR_SELLING like '%" . $_POST['s_credit'] . "%'";
    }
    if ($_POST['s_qty'] != "") {
        $sql .= " and QTYINHAND like '%" . $_POST['s_qty'] . "%'";
    } 
    
    $sql .=" order by STK_NO desc limit 50";
   
    foreach ($conn->query($sql) as $row) {
       
        $cuscode = $row['STK_NO']; 
               
        $text="";
        $color="";
       
        
        $ResponseXML .= "<tr >               
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 10%; text-align:center;'> " . $row['STK_NO'] . "</a></td>
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 15%; text-align:left;'>" . $row['DESCRIPT'] . "</a></td>
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 10%; text-align:left;'>" . $row['BRAND_NAME'] . "</a></td>
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 15%; text-align:left;'>" . $row['PART_NO'] . "</a></td>
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 10%; text-align:left;'>" . $row['Cat1'] . "</a></td>
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 10%; text-align:right;'>" . $row['COST'] . "</a></td>
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 10%; text-align:right;'>" . $row['SELLING'] . "</a></td> 
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 10%; text-align:right;'>" . $row['CR_SELLING'] . "</a></td> 
        <td onclick=\"pass_item_inv('$cuscode');\" style='width: 10%; text-align:right;'>" . $row['QTYINHAND'] . "</a></td> 
        </tr>";
                 
    }

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

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