<?php

session_start();
require_once("./connection_sql.php");
header('Content-Type: text/xml'); 
date_default_timezone_set('Asia/Colombo');

if ($_POST["Command"] == "load_list1") {
      try {
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->beginTransaction();
        $sql = "update s_salma set trans_cancell='0'   ";  echo $sql;
        $result = $conn->query($sql);
        
         $conn->commit();
           
        echo "Saved";
    } catch (Exception $e) {
        $conn->rollBack();
        echo $e;
    }
} 

if ($_POST["Command"] == "select_list") {
    try {
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->beginTransaction();
        

        $sql1 = "Select * from s_salma where  id='" . $_POST["refno"] ."' "; 
        $result1 = $conn->query($sql1);
        if ($row1 = $result1->fetch()) {
            
            if($_POST["invcheck"] =='Y'){
                $sql = "update s_salma set trans_cancell='1' where id='" . $_POST["refno"] ."' "; 
                $result = $conn->query($sql);
            }else{
                $sql = "update s_salma set trans_cancell='0' where id='" . $_POST["refno"] ."' ";  
                $result = $conn->query($sql);
            }
            
            
            
            echo "Saved";
           
            $conn->commit();
           
        } else {
            echo "No Result Found...";
        }
    } catch (Exception $e) {
        $conn->rollBack();
        echo $e;
    }
}
if ($_POST["Command"] == "down_excel") {

    header('Content-Type: text/xml');
    echo "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";

    $ResponseXML = "";
    $ResponseXML .= "<salesdetails>";
        // $div_table= $_POST["div_table"];
        $ResponseXML .= "<div_table><![CDATA[<table class=\"table table-bordered\">";
         $ResponseXML .= "<tr>                                    
                        <td>Inv No</td> 
                        <td>STK No</td> 
                        <td>Description</td>  
                        <td>Barcode</td>  
                        </tr>";
     $sql_search =  "Select * from s_salma where CANCELL='0' AND trans_cancell='1' "; 
       foreach ($conn->query($sql_search) as $row_search) {
 
                    
                     
                    
                    
               
                        
                         
                    $sql_get =  "Select * from s_invo where CANCELL='0' AND REF_NO='" . $row_search["REF_NO"] ."' order BY REF_NO,STK_NO";
                    foreach ($conn->query($sql_get) as $row_get) {
                        $sql_mas= "SELECT * FROM s_mas where STK_NO='".$row_get['STK_NO']."'";
                        $result_mas = $conn->query($sql_mas); 
                        $row_mas = $result_mas->fetch();
                        
                        $sql1 = "SELECT SUM(QTY) as COUNT FROM s_invo where REF_NO='" . $row_search["REF_NO"] ."' and STK_NO='".$row_get['STK_NO']."' GROUP BY STK_NO";
                        $result1 = $conn->query($sql1); 
                        $row1 = $result1->fetch();
                            
                        for($i = 1; $i<=$row1['COUNT']; $i++) {
                        $ResponseXML .= "<tr>                                    
                            <td>" . $row_get['REF_NO'] . "</td>                  
                            <td>" . $row_get['STK_NO'] . "</td> 
                            <td>" . $row_mas['DESCRIPT'] . "</td>  
                             <td></td>  
                            </tr>  ";
               
                         }
                    }
                         
                    
              }
$ResponseXML .= " 
                        </table>]]></div_table>";
 

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