<?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 QTNNO from tmpinvpara_acc";

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

    $row = $result->fetch();



    $tono = $row['QTNNO'];



    $sql = "delete from tmp_po_data where tmp_no='" . $tono . "'";

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



    $sql = "update tmpinvpara_acc set QTNNO=QTNNO+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 SPINV from invpara where COMCODE='" . $_SESSION['company'] . "'";

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

    $row = $result->fetch();

    $tmpinvno = "000000" . $row["SPINV"];

    $lenth = strlen($tmpinvno);

    return $invno = substr($tmpinvno, $lenth - 6);
}

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

        $rate = str_replace(",", "", $_GET["itemPrice"]);

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



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

            $sql = "select vatrate,nbt from invpara";

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

            if ($row_vat = $result_vat->fetch()) {

                $rate = $rate / (1 + $row_vat['vatrate'] / 100);

                $rate = $rate / (1 + $row_vat['nbt'] / 100);
            }
        }





        $discount = 0;

        $subtotal = $rate * $qty;



        $sql = "Insert into tmp_po_data (stk_no, descript, qty, rate,subtot, tmp_no)values 

			('" . $_GET['itemCode'] . "', '" . $_GET['itemDesc'] . "', " . $rate . ", " . $_GET['qty'] . ",'" . $subtotal . "','" . $_GET['tmpno'] . "') ";

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



    $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">

					<tr>

						<td style=\"width: 90px;\">Item</td>

						<td>Description</td>

						<td style=\"width: 60px;\">Qty</td>

						<td style=\"width: 100px;\">Rate</td>

						<td style=\"width: 100px;\">Sub Total</td>

						<td style=\"width: 10px;\"></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>" . $row['stk_no'] . "</td>

							 <td>" . $row['descript'] . "</td>

							 <td>" . number_format($row['rate'], 2, ".", ",") . "</td>

							 <td>" . number_format($row['qty'], 2, ".", ",") . "</td>

							 <td>" . number_format($row['subtot'], 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['subtot'];

        $i = $i + 1;
    }



    $mtot1 = 0;

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

        $sql = "select vatrate from invpara";

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

        if ($row = $result->fetch()) {

            $mtot1 = $mtot * ($row['vatrate'] / 100);
        }
    }

    $sql = "select vatrate,nbt from invpara";

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

    $row = $result->fetch();



    $nbt = ($mtot * ($row['nbt'] / 100));

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

        $mtot1 = ($mtot + $nbt) * ($row['vatrate'] / 100);
    }







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



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

    $ResponseXML .= "<vattot><![CDATA[" . number_format($mtot1, 2, ".", ",") . "]]></vattot>";

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

    $ResponseXML .= "<gtot><![CDATA[" . number_format($mtot1 + $mtot + $nbt, 2, ".", ",") . "]]></gtot>";

    $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 REF_NO,CANCELL,totpay,DEPARTMENT from s_salma where tmp_no ='" . $_GET['tmpno'] . "'";

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

        if ($row = $result->fetch()) {





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

                echo "Already Enterd";

                exit();
            }

            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 where REF_NO = '" . $invno . "'";

            $conn->exec($sql);

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

            $sql = "delete from s_invo 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 SPINV=SPINV+1 where COMCODE='" . $_SESSION['company'] . "'";

            $conn->exec($sql);
        }

                $sql = "select doc_feed from userpermission where username = '".$_SESSION["CURRENT_USER"]."' and docid=27";
        $rowDoc = $conn->query($sql)->fetch();

        if (($rowDoc["doc_feed"] == "0")){
            $cuscode = $_GET["customercode"];

            $sql = "select SDATE from  s_salma where  C_CODE='".$cuscode."' and GRAND_TOT-TOTPAY >1 and CANCELL=0  order by SDATE";
            $sql = $conn->query($sql);

            if ($row1 = $sql->fetch()) {
                    $date1 = $row1["SDATE"];
                    $date2 = date("Y-m-d");

                    $diff = abs(strtotime($date2) - strtotime($date1));

                    $years = floor($diff / (365*60*60*24));

                    $months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));

                    $mday = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));
                    $mdays=$mday+($months*30)+($years*365);

                    $sqlVen = "select crprd, cLIMIT from vendor where CODE = '".$cuscode."'";
                    $result = $conn->query($sqlVen);
                    $rowCus = $result->fetch();

                    $crDays = $rowCus["crprd"];
                    $crLmt = $rowCus["cLIMIT"];
                    if ($mdays > $crDays){
                            exit("Over $crDays Outsnding Avilabale !");
                    }
            }

            $OutInvAmt = 0;
            $OutREtAmt = 0;
            $sql = "select sum(GRAND_TOT-TOTPAY) as totout from view_s_salma where GRAND_TOT>TOTPAY and CANCELL='0' and C_CODE='".$cuscode."' and REF_NO != '" .$row['REF_NO']. "'";
            $row = $conn->query($sql)->fetch();
            $OutInvAmt=$row["totout"];

            $sql = "Select sum(CR_CHEVAL-PAID) as tot from s_cheq where CR_C_CODE='".$cuscode."'  and CR_CHEVAL-PAID>0 and CR_FLAG='0'";
            $row = $conn->query($sql)->fetch();
            $OutREtAmt=$row["tot"];

            $chkOstValue =   $OutInvAmt + $OutREtAmt;
            if($OutREtAmt > 0){
                    exit("Outstanding Return Cheque amount is $OutREtAmt!");
            }else if ($chkOstValue > $crLmt){
                    exit("Credit Limit over $crLmt !");
            }
        }


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





        $mtot = 0;

        $sql = "select * from tmp_po_data where tmp_no='" . $_GET["tmpno"] . "'";

        foreach ($conn->query($sql) as $row) {



            $sql = "Insert into s_invo (REF_NO, SDATE, STK_NO, DESCRIPT, QTY , PRICE) values 

		('" . trim($invno) . "', '" . $_GET['invdate'] . "','" . $row["stk_no"] . "','" . $row["descript"] . "', " . $row["rate"] . "," . $row["qty"] . ")";

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



            $sql = "Insert into s_trn (`SDATE`, `STK_NO`, `REFNO`, `QTY`, `LEDINDI`, `DEPARTMENT`) values 

		('" . $_GET['invdate'] . "','" . $row["stk_no"] . "','" . trim($invno) . "', " . $row["rate"] . ",'INV', '" . $_GET['department'] . "')";

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





            $sql1 = "update s_mas set  QTYINHAND=QTYINHAND-" . $row["rate"] . " where  STK_NO='" . $row["stk_no"] . "'";

            $conn->exec($sql1);



            $sql = "update s_submas set  QTYINHAND=QTYINHAND-" . $row["rate"] . " where  STK_NO='" . $row["stk_no"] . "' and sto_code='" . $_GET['department'] . "'";

            $conn->exec($sql);



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

        $mtot1 = 0;



        $sql = "select vatrate,nbt from invpara";

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

        $row = $result->fetch();



        $nbt = ($mtot * ($row['nbt'] / 100));

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

            $mtot1 = ($mtot + $nbt) * ($row['vatrate'] / 100);
        }



        $mgrand_tot = ($mtot + $mtot1 + $nbt) * $_GET['txt_rate'];



        $sql = "insert s_salma (REF_NO,SDATE,trn_type,  C_CODE, CUS_NAME,c_add1, vat,tmp_no,REMARK,btt,grand_tot,SAL_EX,gst,use_name,DEPARTMENT,dele_no,company, TYPE) values 

	('" . $invno . "', '" . $_GET['invdate'] . "','INV' ,'" . $_GET["customercode"] . "', '" . $_GET["customername"] . "','" . $_GET["cont_p"] . "','" . $mtot1 . "','" . $_GET["tmpno"] . "','" . $_GET['txt_remarks'] . "','" . $nbt . "','" . $mgrand_tot . "','" . $_GET['salesrep'] . "','" . $row['vatrate'] . "','" . $_SESSION['UserName'] . "','" . $_GET['department'] . "','" . $_GET['DANO'] . "','" . $_SESSION['company'] . "', '" . $_GET["paymethod"] . "')";

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



        //==============gl_posting==================================================



        require_once '../gl_posting.php';

        $ayear = ac_year($_GET["invdate"]);

        $vatrate = $row['vatrate'];

        $amount = $mgrand_tot;

        $vatAmount = $amount * $vatrate / (100 + $vatrate);

        $vatAmount = number_format($vatAmount, "2", ".", "");



        $orgAmount = $amount - $vatAmount;



        $sqlGlPost = "select * from gl_posting where docname = 'INVOICE' and action = 'ADD_VAT'";
        $result = $conn->query($sqlGlPost);
        $rowGlPost = $result->fetch();

        $sqlLedger = "Insert into ledger(l_refno, l_date, l_code, l_amount, l_flag, l_flag1, acyear, ComCode, c_remarks) Values ('" . $invno . "', '" . $_GET["invdate"] . "', '" . $rowGlPost["l_code"] . "', " . $vatAmount . ", 'INV', '" . $rowGlPost['entry_flag'] . "', '$ayear', '" . $_SESSION['company'] . "','" . $_GET["customercode"] . "')";
        $conn->query($sqlLedger);







        $sqlGlPost = "select * from gl_posting where docname = 'INVOICE' and action = 'ADD_SALE'";

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

        $rowGlPost = $result->fetch();



        $sqlLedger = "Insert into ledger(l_refno, l_date, l_code, l_amount, l_flag, l_flag1, acyear, ComCode, c_remarks) Values ('" . $invno . "', '" . $_GET["invdate"] . "', '" . $rowGlPost["l_code"] . "', " . $orgAmount . ", 'INV', '" . $rowGlPost['entry_flag'] . "', '$ayear', '" . $_SESSION['company'] . "','" . $_GET["customercode"] . "')";

        $conn->query($sqlLedger);



        $sqlGlPost = "select * from gl_posting where docname = 'INVOICE' and action = 'ADD_DEBTORS'";

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

        $rowGlPost = $result->fetch();



        $sqlLedger = "Insert into ledger(l_refno, l_date, l_code, l_amount, l_flag, l_flag1, acyear, ComCode, c_remarks) Values ('" . $invno . "', '" . $_GET["invdate"] . "', '" . $rowGlPost["l_code"] . "', " . $amount . ", 'INV', '" . $rowGlPost['entry_flag'] . "', '$ayear', '" . $_SESSION['company'] . "','" . $_GET["customercode"] . "')";

//            echo $sqlLedger;

        $conn->query($sqlLedger);





        /////////// if cash invoice ////////////////////////



        if ($_GET["paymethod"] == "CA") {



            $sql = "select INV_RECNO from invpara where COMCODE='" . $_SESSION['company'] . "'";

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

            $row = $result->fetch();



            $tmprecno = "000000" . $row["INV_RECNO"];

            $lenth = strlen($tmprecno);

            $recno = $_SESSION['company'] . trim("RN/CR/") . substr($tmprecno, $lenth - 5);



            $sql = "insert into s_crec(CA_REFNO, CA_DATE, CA_CODE, CA_CASSH, CA_AMOUNT, overpay, FLAG, pay_type, CA_SALESEX, CANCELL, tmp_no, DEPARTMENT, cus_ref, AC_REFNO, TTDATE, DEV, company) values

      ('" . $recno . "', '" . $_GET["invdate"] . "', '" . trim($_GET["customercode"]) . "', " . $mgrand_tot . ", " . $mgrand_tot . ", 0, 'REC', 'Cash', '" . trim($_GET["salesrep"]) . "', '0', '" . $_GET["tmpno"] . "', 'O', '0', '', '',  '" . $_SESSION['dev'] . "' ,  '" . $_SESSION['company'] . "' )";

            //echo $sql;

            $conn->query($sql);





            $sql = "insert into s_sttr(ST_REFNO, ST_DATE, ST_INVONO, ST_PAID, ST_CHNO, st_chdate, ST_FLAG, st_days, ap_days, st_chbank, cus_code, DEV, del_days, deliin_days, deliin_amo, deliin_lock, department) values

      ('" . $recno . "', '" . $_GET["invdate"] . "', '" . $invno . "', " . $mgrand_tot . ", '', '" . $_GET["invdate"] . "', 'CAS', '', '', '', '" . trim($_GET["customercode"]) . "', '" . $_SESSION['dev'] . "', 0, 0, 0, '0', 'O')";

            //  echo $sql;

            $conn->query($sql);

            //-------------cash-gl                
            $fulltot = $mgrand_tot;

            $l_lmem = "Cash recieved from " . $_GET["customercode"];
            $ldate = $_GET["invdate"];
            $ayear = ac_year($ldate);

            $sql19 = "insert into ledger(l_refno, l_date, l_code, l_amount, l_flag, l_flag1, l_lmem, l_flag2, l_flag3, l_yearfl, comcode, chno, recdate, acyear, c_remarks) values ('" . trim($recno) . "', '" . $ldate . "', '1013', " . $fulltot . ", 'REC', 'CRE', '" . $l_lmem . "', '0', 'R', 0, '" . $_SESSION['company'] . "', '', '0', '$ayear','" . $_GET["customercode"] . "')";
            $conn->query($sql19);

            $sql20 = "insert into ledger(l_refno, l_date, l_code, l_amount, l_flag, l_flag1, l_lmem, l_flag2, l_flag3, l_yearfl, comcode, chno, recdate, acyear, c_remarks) values ('" . trim($recno) . "', '" . $ldate . "', '1010', " . $fulltot . ", 'REC', 'DEB', '" . $l_lmem . "', '0', 'R', 0, '" . $_SESSION['company'] . "', '', '0', '$ayear','" . $_GET["customercode"] . "')";
            $conn->query($sql20);
            //-------------cash-gl



            $sql = "update s_salma set TOTPAY=TOTPAY + " . $mgrand_tot . " where REF_NO = '" . $invno . "'";

            $conn->query($sql);





            $sql = "update s_salma set CASH=CASH + " . $mgrand_tot . " where REF_NO = '" . $invno . "'";

            $conn->query($sql);



            $sql = "update invpara set INV_RECNO=INV_RECNO + 1 where COMCODE='" . $_SESSION['company'] . "'";

            $conn->query($sql);
        }



        $sql = "insert into entry_log(refno, username, docname, trnType, stime, sdate) values ('" . trim($invno) . "', '" . $_SESSION["CURRENT_USER"] . "', 'Invoice', 'Save', '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d") . "')";

        $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_domas 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 .= "<str_crecash><![CDATA[" . $row['TYPE'] . "]]></str_crecash>";

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

        $ResponseXML .= "<name><![CDATA[" . $row["CUS_NAME"] . "]]></name>";

        $ResponseXML .= "<txt_remarks><![CDATA[" . $row["ORD_NO"] . "]]></txt_remarks>";
        $sql = "select ADD1 from vendor where CODE = '".$row["C_CODE"]."'";
        $result = $conn->query($sql);
        $rowAdd = $result->fetch();
        $ResponseXML .= "<Attn><![CDATA[" . $rowAdd['ADD1'] . "]]></Attn>";

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



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

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

        $ResponseXML .= "<department><![CDATA[" . $row["DEPARTMENT"] . "]]></department>";

//        $ResponseXML .= "<DANO><![CDATA[" . $row["dele_no"] . "]]></DANO>";



        $ResponseXML .= "<salesrep><![CDATA[" . $row["SAL_EX"] . "]]></salesrep>";





        $msg = "";

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

            $msg = "Cancelled";
        }

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



        $sql = "delete from tmp_po_data where tmp_no='" . $_SESSION["newInvTmpNo"] . "'";

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





        $sql = "Select * from s_dotrn where REF_NO='" . $row["REF_NO"] . "'";

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

            $subtotal = $row1['QTY'] * $row1['PRICE'];

            $sql = "Insert into tmp_po_data (stk_no, descript, qty, rate,subtot, tmp_no) values 

			('" . $row1['STK_NO'] . "', '" . $row1['DESCRIPT'] . "', " . $row1['PRICE'] . ", " . $row1['QTY'] . ",'" . $subtotal . "','" . $_SESSION["newInvTmpNo"] . "') ";

            $result_t = $conn->query($sql);
        }





        $ResponseXML .= "<sales_table><![CDATA[<table class=\"table\">

					<tr>

						<td style=\"width: 90px;\">Item</td>

						<td>Description</td>

						<td style=\"width: 60px;\">Qty</td>

						<td style=\"width: 100px;\">Rate</td>

						<td style=\"width: 100px;\">Sub Total</td>

						<td style=\"width: 10px;\"></td>

					</tr>";



        $i = 1;

        $mtot = 0;

        $sql = "Select * from tmp_po_data where tmp_no='" . $_SESSION["newInvTmpNo"] . "'";

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



            $ResponseXML .= "<tr>                              

                             <td>" . $row1['stk_no'] . "</td>

							 <td>" . $row1['descript'] . "</td>

							 <td>" . number_format($row1['rate'], 2, ".", ",") . "</td>

							 <td>" . number_format($row1['qty'], 2, ".", ",") . "</td>

							 <td>" . number_format($row1['subtot'], 2, ".", ",") . "</td>

							 <td><a class=\"btn btn-danger btn-xs\" onClick=\"del_item('" . $row1['stk_no'] . "')\"> <span class='fa fa-remove'></span></a></td>

							 </tr>";



            $mtot = $mtot + $row1['subtot'];

            $i = $i + 1;
        }



        $mtot1 = 0;





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



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

        $ResponseXML .= "<vattot><![CDATA[" . number_format($row['VAT'], 2, ".", "") . "]]></vattot>";

        $ResponseXML .= "<nbt><![CDATA[" . number_format($row['BTT'], 2, ".", ",") . "]]></nbt>";

        $ResponseXML .= "<gtot><![CDATA[" . number_format($row['GRAND_TOT'], 2, ".", ",") . "]]></gtot>";

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









    $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\">Amount</th>  

                    </tr>";





    $sql = "select * from s_domas where CANCELL='0' ";



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

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

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

    $stname = $_GET['stname'];



    $sql .= " order by SDATE desc limit 50";


//    echo $sql;
    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');\">" . $row['GRAND_TOT'] . "</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 from s_salma where tmp_no ='" . $_GET['tmpno'] . "'";

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

        if ($row = $result->fetch()) {





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

                echo "Already Enterd";

                exit();
            }

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

                echo "Already Cancelled";

                exit();
            }



            if ($row['TOTPAY'] > 0) {

                echo "Already Paid";

                exit();
            }



            $invno = $row['REF_NO'];







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

            $conn->exec($sql);



            $sql = "DELETE from ledger WHERE l_refno='" . $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 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 set CANCELL='1' where ref_no = '" . $row['REF_NO'] . "'";

            $conn->exec($sql);



            echo "ok";

            $conn->commit();
        } else {

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

        $conn->rollBack();

        echo $e;
    }
}
?>
