<?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'];
    $_SESSION["newInvTmpNo"] = $tono;



    $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"]);
        $actrate = str_replace(",", "", $_GET["itemPrice"]);

        if ($_GET['vat'] != "non") {
            $sql = "select vatrate,nbt from invpara where COMCODE='".$_SESSION['company']."'"; 
            $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);
            }
        }
        
            $sql="Select * from s_mas where STK_NO='".$_GET['itemCode']."'";
            $result = $conn->query($sql); 
            $row = $result->fetch();
			 $stock="0";
			 
			$sql_brand="Select * from brand_mas where barnd_name='".$row['BRAND_NAME']."'";
            $result_brand = $conn->query($sql_brand); 
            $row_brand = $result_brand->fetch(); 
              
			if($row_brand['lock'] =="1"){
			    
// 			if(($row['BRAND_NAME'] =="WHITE BOX") or ($row['BRAND_NAME'] =="YSK") or ($row['BRAND_NAME'] =="DU") or ($row['BRAND_NAME'] =="NUK")){
			    $sqlqty1="select * from s_submas where STK_NO='".$_GET["itemCode"]."' AND STO_CODE='".$_GET["department"]."'"; 
			    $resultqty1 = $conn->query($sqlqty1);
                if ($rowqty1 = $resultqty1->fetch()) { 
			    
    			    if (($rowqty1["QTYINHAND"]<$_GET["qty"]) ){ 
    			        $ResponseXML .= "<stock><![CDATA[Insuffisant Stock In Your Department]]></stock>"; 	
    			         $stock="1";
    			     //  
    			    } else{
    			         $stock="0";
    			        $ResponseXML .= "<stock><![CDATA[]]></stock>"; 		
    			    }
    			}else{
    			     $stock="1";
    			    $ResponseXML .= "<stock><![CDATA[Insuffisant Stock In Your Department]]></stock>"; 		
    			}
			}else{
			     $stock="0";
			    $ResponseXML .= "<stock><![CDATA[]]></stock>"; 		
			}
			
			
         if($stock=="0"){    
            $discount = 0;
            $subtotal = $rate * $qty;
            $sql = "Insert into tmp_po_data (stk_no, descript, qty, rate,act_rate,subtot, tmp_no)values 
    			('" . $_GET['itemCode'] . "', '" . $_GET['itemDesc'] . "', '" . $rate . "', '" . $_GET['qty'] . "','" . $actrate . "','" . $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 where COMCODE='".$_SESSION['company']."'"; 
        $result = $conn->query($sql);
        if ($row = $result->fetch()) {
            $mtot1 = $mtot * ($row['vatrate'] / 100);
        }
    }
    $sql = "select vatrate,nbt from invpara where COMCODE='".$_SESSION['company']."'"; 
    $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'];
            }
            
            
        
        // if ($i == 0) {
        //     exit("Items Not Found");
        // }
    


            $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);
        }
        
         $insuf_qty = "";
        $sqltmp1 = "select * from tmp_po_data where tmp_no='" . $_GET["tmpno"] . "'";  
        $i = 0;  
        foreach ($conn->query($sqltmp1) as $rowtmp1) {
            $sql="Select * from s_mas where STK_NO='".$rowtmp1['stk_no']."'";  
            $result = $conn->query($sql); 
            $row = $result->fetch(); 
            
            $sql_brand="Select * from brand_mas where barnd_name='".$row['BRAND_NAME']."'";
            $result_brand = $conn->query($sql_brand); 
            $row_brand = $result_brand->fetch(); 
              
// 			if($row_brand['lock'] =="1"){
                $sql12 = "select QTYINHAND from s_submas where STK_NO='" . $rowtmp1['stk_no'] . "' AND STO_CODE='" . $_GET["department"] . "'";    
                $result12 = $conn->query($sql12);
                if ($row12 = $result12->fetch()) {
                    if ($row12["QTYINHAND"] < $rowtmp1["rate"]) {
                        $insuf_qty = $rowtmp1['stk_no'];
                    }
                }
// 			}
            $i = $i + 1;
        }

         
 
        if ($insuf_qty != "") {
            exit("Under Stock");
        }
        
        // if ($_GET["paymethod"] != "CA") {
        //     $sql123 = "select * from  view_salma_s_sttr where  C_CODE='".trim($_GET["customercode"])."' and ST_FLAG='CHK' and st_chdate>'".date('Y-m-d')."'    order by st_chdate desc" ; 
        //     $resultnew = $conn->query($sql123); 
        //     if ($row123 = $resultnew->fetch()) {  
        //         $date1 = $row123["st_chdate"];
        //         $date2 = $row123["SDATE"];
                
        //         $diff = abs(strtotime($date1) - strtotime($date2));
        //         // echo $diff;
        //         if($diff>30){
        //             exit("Over 30 Dated + Cheque Found");
        //         } 
        //     } 
        // }


        if(($_SESSION["CURRENT_USER"]=="ratahava") or ($_SESSION["CURRENT_USER"]=="dilshan_a") or ($_SESSION["CURRENT_USER"]=="dilshan_b") or ($_SESSION["CURRENT_USER"]=="dilshan_c") or ($_SESSION["CURRENT_USER"]=="dilshan_r")){
            
        }else{
            $dd=date('Y-m-d');
            	$sqlnew =  "select C_CODE ,SDATE,DATEDIFF('$dd',SDATE) as days,crprd from view_salma_vendor where CANCELL='0' and apcperiod='1' and    C_CODE='".$_GET["customercode"]."' and   (GRAND_TOT-TOTPAY)>10 order by  C_CODE,SDATE asc";
                  
                $resultnew = $conn->query($sqlnew); 
                   if ($rownew = $resultnew->fetch()) { 
                	     if($rownew['days']>$rownew['crprd']){
                	         echo "Cant Save.. 120+ days  .Please Unblock Or Settle Amount";
    
                           exit();
                	     }  
                	} 
        }
                
            	
        // $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 >50 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;
            $pending=0;
            $currtot=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();
            
            $sql1 = "Select sum(che_amount) as tot from s_invcheq where cus_code='".$cuscode."'  and che_date>='".date('Y-m-d')."' ";  
            $row1 = $conn->query($sql1)->fetch();
            
            $pending=$row1["tot"];
            
            $OutREtAmt=$row["tot"];
            
            if($_GET["paymethod"]=="CR"){
                $currtot=$_GET["gtot"];
            }
            
            $chkOstValue =   $OutInvAmt + $OutREtAmt+$currtot+$pending;
            // echo $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"]);


        $sql16 = "update vendor set  apcperiod='1' where CODE='".$_GET["customercode"]."'";

            $conn->exec($sql16);


        $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,subtot) values 
		('" . trim($invno) . "', '" . $_GET['invdate'] . "','" . $row["stk_no"] . "','" . $row["descript"] . "', " . $row["rate"] . "," . $row["act_rate"] . "," . $row["subtot"] . ")";
            $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 where COMCODE='".$_SESSION['company']."'"; 

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

        $row = $result->fetch();



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

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

            $mtot1 = ($mtot + $nbt) * ($row['vatrate'] / 100);
        }
        
        $vattype=0;
        if ($_GET['vat'] != "non") {
            $vattype=1;
        }


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



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

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

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

        $sql = "update s_domas set CANCELL='2' where REF_NO = '".$_GET['DANO']."'";
        $result = $conn->query($sql);
        $sql = "update s_dotrn set CANCELL='2' where REF_NO = '".$_GET['DANO']."'";
        $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_salma 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["REMARK"] . "]]></txt_remarks>";

        $ResponseXML .= "<Attn><![CDATA[" . $row['C_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='" . $row["tmp_no"] . "'";

        $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;\">Dis</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 s_invo where REF_NO='" . $row["REF_NO"] . "'";


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



            $ResponseXML .= "<tr>                              

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

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

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

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

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

							 <td></td>

							 </tr>";



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

            $i = $i + 1;
        }



        $mtot1 = 0;





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



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

        $ResponseXML .= "<vat><![CDATA[" . number_format($row['VAT'], 2, ".", "") . "]]></vat>";
        $ResponseXML .= "<vattot><![CDATA[" . number_format($row['VAT_VAL'], 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\">DA 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 REF_NO,dele_no, SDATE,C_CODE,CUS_NAME,GRAND_TOT from s_salma where company = '".$_SESSION["company"]."' and trn_type = 'INV'";



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

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

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

    $stname = $_GET['stname'];



    $sql .= " and company = '".$_SESSION["company"]."' ORDER BY id 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['dele_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,dele_no 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);

            $sql1 = "update s_domas set CANCELL='0' where REF_NO = '".$row['dele_no']."'";
            $conn->exec($sql1);
            $sql1 = "update s_dotrn set CANCELL='0' where REF_NO = '".$row['dele_no']."'";
            $conn->exec($sql1);
            
            $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($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 = "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;
    }
}
?>