<?php
    include_once 'opendb.php';
    $filter="";
    $xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
    $root_element = "Sales"; //fruits
    $xml         .= "<".$root_element.">";
    $Last_syncNo=$_GET["last_web_no"]; 
    $CompID=$_GET["comp_id"]; 
    $filter.=" and comp_id=" .$CompID;
  
    $Qry="SELECT M.Entry_no as Entry_No, Entry_Date,Notes,Amount,Tax_Amt,SGST_AMT,CGST_Amt,disc_amt,Cess_amt,pay_mode ,Voucher_no,Sub_Code,M.Acc_no,Cust_Name,Cust_Area,Cust_Phone,cust_ref,Counter_Code,sync_time,additions,prod_code,SL_No,Quantity,Rate,Unit,COST,pack_qty,discount,TaxRate,SGST_Rate,CGST_Rate,Foc_Qty,Foc_Unit,Loc_code,Batch,UnitIndex FROM sales_return M LEFT  JOIN  sales_return_sub  S ON M.Entry_No=S.Entry_No ";
    $Qry.=" where M.Entry_no in (select min(Entry_No) from sales_return where entry_no > $Last_syncNo )  ".$filter ;  
    //echo $Qry;
    $result = $conn->query($Qry) or die(mysqli_error($conn));
   header("Content-Type: application/xml");
   echo sqlToXml($result, "Sales", "Rows");
  
   if($result->num_rows == 0){
      clear_old_records($CompID, $conn);
   }
   mysqli_free_result($result);
   
   $conn->close(); 
  
function sqlToXml($queryResult, $rootElementName, $childElementName)
{ 
    $xmlData = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n"; 
    $xmlData .= "<" . $rootElementName . ">";
    
    while ($row = mysqli_fetch_assoc($queryResult))
    {
            $xmlData .= "<" . $childElementName . ">";
            foreach( $row as $field => $value) {
             
            // echo "<"."{$field}".">"."  - {$value}<br>";

                $xmlData .= "<{$field}>{$value}</{$field}>";
            }
            $xmlData .= "</" . $childElementName . ">"; 
    }
 
   
    $xmlData .= "</" . $rootElementName . ">"; 
 
    return $xmlData; 
}

function clear_old_records($CompID, $conn){
    $Set_Name="SR_CLRDATE";
    $isClear=1;
    $sql = "SELECT * FROM appsettings WHERE set_category='GENERAL' and set_name='".$Set_Name."' and comp_id=" .$CompID;
    $result = $conn->query($sql);
    if ($result->num_rows == 0 ) {
           $sql = "INSERT INTO appsettings (set_category,set_name,set_value,val_type,comp_id) VALUES ('GENERAL','".$Set_Name."','','S',".$CompID.")";
           if ($conn->query($sql) === TRUE)
           {} else {
              die( "Error: Inerting settings " . $sql . "<br>" . $conn->error);
           } 
    } else if ($nrow=mysqli_fetch_assoc($result)) {
        if ($nrow['set_value']==date('Y-m-d')){
            $isClear=0;        
        }
    }
    
    if ($isClear==1){
        $cutDate=date('Y-m-d',strtotime('-50 day'));
        $sql="select entry_no from sales_return where sync_entry_no > 0 and Entry_date < '$cutDate' and comp_id=$CompID";
        $mresult = $conn->query($sql);
        while ($row = mysqli_fetch_assoc($mresult)){
             $mENo=$row['entry_no'];
             $mqry="delete from sales_return_sub where entry_no=$mENo";
             $conn->query($mqry);
             $mqry="delete from sales_return where entry_no=$mENo";
             $conn->query($mqry);
        }
        mysqli_free_result($mresult);
        $sql = "update appsettings set set_value='". date('Y-m-d') ."'  WHERE set_category='GENERAL' and set_name='".$Set_Name."' and comp_id=" .$CompID;
        $conn->query($sql);
    }
  
}
    
    
?>