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

// Set headers to download CSV
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=MIS-Report.csv');

// Open output stream
$output = fopen("php://output", "w");

// XSS sanitization function
function xss_clean($data)
{
    $data = trim($data);
    $data = str_replace(['&amp;', '&lt;', '&gt;'], ['&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');
    $data = preg_replace('#(<[^>]+?[\x00-\x20"\'])(?:on|xmlns)[^>]*+>#iu', '$1>', $data);
    $data = preg_replace('#([a-z]*)[\x00-\x20]*=[\x00-\x20]*([`\'"]*)[\x00-\x20]*javascript:#iu', '$1=$2nojavascript...', $data);
    $data = preg_replace('#([a-z]*)[\x00-\x20]*=([\'"]*)[\x00-\x20]*vbscript:#iu', '$1=$2novbscript...', $data);
    $data = preg_replace('#([a-z]*)[\x00-\x20]*=([\'"]*)-moz-binding:#u', '$1=$2nomozbinding...', $data);
    $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?expression\([^>]*+>#i', '$1>', $data);
    $data = preg_replace('#</*\w+:\w[^>]*+>#i', '', $data);

    do {
        $old_data = $data;
        $data = preg_replace('#</*(?:applet|base|bgsound|link|meta|object|script|style|iframe|frame|frameset|ilayer|layer|title|xml)[^>]*+>#i', '', $data);
    } while ($old_data !== $data);

    return $data;
}

// Set default filter values
$from_date = $_POST['from_date'] ?? date('Y-m-d');
$to_date = $_POST['to_date'] ?? date('Y-m-d');
$new_client = $_POST['new_client'] ?? 0;
$branch_id = $_POST['branch_id'] ?? 0;
$client_id = $_POST['client_id'] ?? 0;
$courier_id = $_POST['courier_id'] ?? 0;
$customer_id = $_POST['customer_id'] ?? 0;
$status = $_POST['status'] ?? 0;

// Start building condition string
$condition = " WHERE pickup_entry.date_added BETWEEN :from_date AND :to_date";

$params = [
    ':from_date' => $from_date,
    ':to_date' => $to_date
];

if ($branch_id !== 'all' && $branch_id !== '') {
    $condition .= " AND pickup_entry.branch_id = :branch_id";
    $params[':branch_id'] = $branch_id;
}

if ($new_client !== 'all' && $new_client !== '') {
    $condition .= " AND pickup_entry.new_client_id = :new_client";
    $params[':new_client'] = $new_client;
}

if ($client_id !== 'all' && $client_id !== '') {
    $condition .= " AND pickup_entry.client_id = :client_id";
    $params[':client_id'] = $client_id;
}

if ($courier_id !== 'all' && $courier_id !== '') {
    $condition .= " AND pickup_entry.courier_id = :courier_id";
    $params[':courier_id'] = $courier_id;
}

if ($customer_id !== 'all' && $customer_id !== '') {
    $condition .= " AND pickup_entry.customer_id = :customer_id";
    $params[':customer_id'] = $customer_id;
}

if ($status !== 'all') {
    $condition .= " AND pickup_entry.status_code = :status";
    $params[':status'] = $status;
}

// Session-based filtering
if (!empty($session_client_ids) && $session_client_ids != 0) {
    $condition .= " AND tbl_client_new.id IN ($session_client_ids)";
}

if (isset($session['id']) && $session['id'] != 1 && $session_branch_ids !== '0') {
    $condition .= " AND tbl_branch.id IN ($session_branch_ids)";
}

// Main SQL query
$sql = "SELECT 
            e.validUpto,
            e.ewbDate,
            e.ewbNo	,
            pickup_entry.*,
            tbl_client_new.name AS new_client_name,
            tbl_user.full_name AS client_name,
            tbl_status.name AS status_name,
            tbl_branch.name AS branch_name
        FROM pickup_entry
        LEFT JOIN tbl_user ON pickup_entry.client_id = tbl_user.id
        LEFT JOIN tbl_branch ON pickup_entry.branch_id = tbl_branch.id
        LEFT JOIN tbl_status ON tbl_status.id = pickup_entry.status_code
        LEFT JOIN tbl_client_new ON tbl_client_new.id = pickup_entry.new_client_id
        LEFT JOIN ewaybills e ON e.ewbNo = pickup_entry.ewaybill
        $condition";

// Prepare and execute
$statement = $pdo->prepare($sql);
$statement->execute($params);
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

// CSV Header
fputcsv($output, [
    'S.No', 'DATE', 'AWB NO', 'CLIENT NAME', 'SENDER NAME', 'INVOICE NUMBER',
    'INVOICE VALUE', 'RECEIVER NAME', 'SENDER LOCATION', 'RECEIVER LOCATION',
    'TOTAL BOX', 'TOTAL WEIGHT', 'SHIPPING MODE', 'BRANCH', 'SHIPMENT STATUS',
    'SHIPMENT LAST UPDATE DATE', 'POD UPLOAD','EWBNO', 'VALIDUPTO', 'CREATED AT'
]);

// CSV Data
$s_no = 1;
foreach ($rows as $row) {
    fputcsv($output, [
        $s_no++,
        date('d-m-Y', strtotime($row['date_added'] ?? '')),
        $row['cscs_awb'],
        $row['new_client_name'],
        $row['shipper_name'],
        $row['invoice_no'],
        $row['invoice_value'],
        $row['consignee_name'],
        $row['shipper_location'],
        $row['consignee_location'],
        $row['boxes'],
        $row['a_weight'],
        strtoupper($row['business_mode']),
        $row['branch_name'],
        $row['status_name'],
        $row['last_updated_status_date'],
        $row['pod_status'],
        $row['ewbNo'],
        $row['validUpto'],
        $row['ewbDate']
    ]);
}

fclose($output);
exit;
?>
