<?php
ob_start();
session_start();
include("../inc/config.php");
include("../inc/session.php");

error_reporting(E_ALL);
ini_set('display_errors', 1);


    $now = gmdate("YmdHis");
	header('Content-Type: text/csv; charset=utf-8');  
	header('Content-Disposition: attachment; filename=Eway-Bill-Update-Report.csv');  
	$output = fopen("php://output", "w");  

    function xss_clean($data){
            $data = trim($data);
            // Fix &entity\n;
            $data = str_replace(array('&amp;', '&lt;', '&gt;'), array('&amp;amp;', '&amp;lt;', '&amp;gt;'), $data);
            $data = preg_replace('/(&#*\w+)[\x00-\x20]+;/u', '$1;', $data);
            $data = preg_replace('/(&#x*[0-9A-F]+);*/iu', '$1;', $data);
            $data = html_entity_decode($data, ENT_COMPAT, 'UTF-8');
    
            // Remove any attribute starting with "on" or xmlns
            $data = preg_replace('#(<[^>]+?[\x00-\x20"\'])(?:on|xmlns)[^>]*+>#iu', '$1>', $data);
    
            // Remove javascript: and vbscript: protocols
            $data = preg_replace('#([a-z]*)[\x00-\x20]*=[\x00-\x20]*([`\'"]*)[\x00-\x20]*j[\x00-\x20]*a[\x00-\x20]*v[\x00-\x20]*a[\x00-\x20]*s[\x00-\x20]*c[\x00-\x20]*r[\x00-\x20]*i[\x00-\x20]*p[\x00-\x20]*t[\x00-\x20]*:#iu', '$1=$2nojavascript...', $data);
            $data = preg_replace('#([a-z]*)[\x00-\x20]*=([\'"]*)[\x00-\x20]*v[\x00-\x20]*b[\x00-\x20]*s[\x00-\x20]*c[\x00-\x20]*r[\x00-\x20]*i[\x00-\x20]*p[\x00-\x20]*t[\x00-\x20]*:#iu', '$1=$2novbscript...', $data);
            $data = preg_replace('#([a-z]*)[\x00-\x20]*=([\'"]*)[\x00-\x20]*-moz-binding[\x00-\x20]*:#u', '$1=$2nomozbinding...', $data);
    
            // Only works in IE: <span style="width: expression(alert('Ping!'));"></span>
            $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?expression[\x00-\x20]*\([^>]*+>#i', '$1>', $data);
            $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?behaviour[\x00-\x20]*\([^>]*+>#i', '$1>', $data);
            $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?s[\x00-\x20]*c[\x00-\x20]*r[\x00-\x20]*i[\x00-\x20]*p[\x00-\x20]*t[\x00-\x20]*:*[^>]*+>#iu', '$1>', $data);
    
            // Remove namespaced elements (we do not need them)
            $data = preg_replace('#</*\w+:\w[^>]*+>#i', '', $data);
    
            do {
                // Remove really unwanted tags
                $old_data = $data;
                $data = preg_replace('#</*(?:applet|b(?:ase|gsound|link)|embed|frame(?:set)?|i(?:frame|layer)|l(?:ayer|ink)|meta|object|s(?:cript|tyle)|title|xml)[^>]*+>#i', '', $data);
            } while ($old_data !== $data);
    
            // we are done...
            return $data;
        }
    
    $requestData= $_REQUEST;
    
    $condition = '';

    /*Fetch Database column Details*/
    $columns = [
        0 => 'p.id',
        3 => 'p.cscs_awb',
        4 => 'e.ewbNo',
        7 => 'p.ewbn_expiry',
        9 => 'p.ewayBillDate'
    ];
    /*Fetch All Data Start*/ 
    
    
    $condition=' ';
    
    $from_date = $_POST['start_date'] ?? '2025-04-01';
    $to_date = $_POST['end_date'] ?? date('Y-m-d');
    
    if($from_date!='' && $to_date!=''){
      $condition = " WHERE STR_TO_DATE(e.docDate, '%d/%m/%Y') BETWEEN '$from_date' AND '$to_date' ";
    }
    
    $sql = "SELECT e.*, 
               p.invoice_date, 
               p.invoice_no, 
               CASE 
                   WHEN p.invoice_no = e.docNo
                   THEN 'generated' ELSE 'not generated' 
               END AS rhd_ewaybill,
               CASE 
                   WHEN v.pid IS NOT NULL 
                   THEN 1 ELSE 0 
               END AS vehicle_updated 
        FROM ewaybills e
        LEFT JOIN pickup_entry p ON p.invoice_no = e.docNo
        LEFT JOIN vehicle_update_details v ON v.pid = e.ewbNo
        $condition  GROUP BY e.ewbNo";
        
    $statement->execute();
    $query = $statement->fetchAll(PDO::FETCH_ASSOC);
    $totalData = $statement->rowCount();
    $totalFiltered = $totalData;
            
    /*Fetch Filter Data End*/       
            
    $sql="SELECT e.*, 
               p.invoice_date, 
               p.invoice_no, 
               CASE 
                   WHEN p.invoice_no = e.docNo
                   THEN 'generated' ELSE 'not generated' 
               END AS rhd_ewaybill,
               CASE 
                   WHEN v.pid IS NOT NULL 
                   THEN 1 ELSE 0 
               END AS vehicle_updated 
        FROM ewaybills e
        LEFT JOIN pickup_entry p ON p.invoice_no = e.docNo
        LEFT JOIN vehicle_update_details v ON v.pid = e.ewbNo $condition "; 

    $sql.=" GROUP BY e.ewbNo ORDER BY p.id  desc";        
    
    $statement = $pdo->prepare($sql);
    $statement->execute();
	$query = $statement->fetchAll(PDO::FETCH_ASSOC);
    $totalFiltered = $statement->rowCount();
    
    fputcsv($output, array('EWBN','GENGST', 'INVOICE', 'INVOICE DATE','EwayBill Date','EwayBill Date Expiry','RHD STATUS','PART B'));

    foreach($query as $row) { 
        fputcsv($output, array(
            strtoupper($row["ewbNo"]),
            strtoupper($row["genGstin"]),
            strtoupper($row["docNo"]),
            strtoupper($row["docDate"]),
            strtoupper($row["ewbDate"]),
            strtoupper($row["validUpto"]),
            strtoupper($row["rhd_ewaybill"]),
            strtoupper($row["vehicle_updated"])
        )); 
    }
   
   	fclose($output);
?>