<?php

require("includes/check_auth_user.php");
require("includes/dbconnect.php");
include('includes/function.php');

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear"];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);

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

$success = false;

$usertype = $_SESSION["usertype_" . $dbname];
$ztit = $_SESSION["tit_" . $dbname];
$useraccode = $_SESSION["customer_code"];

$maindb = 'shipment';
$trandb = 'shiptran';

$file = 'trackingstatus.php';

$keyfld = "vouchno";

$origin = $_REQUEST["origin"];
$rangeFrom = $_REQUEST["rangeFrom"];
$rangeTo = $_REQUEST["rangeTo"];

// Map fields to database columns (same as filtering)
$fields = [
    1 => 'a.vouchno',
    2 => 'b.name',
    3 => 'a.containerno',
    4 => 'a.destination'
];

// 1️⃣ Filter condition
$searchTerm = $_REQUEST["ssearch"];
$operator = $_REQUEST["soperator"];
$sfield = $_REQUEST["sfield"];

$field = $fields[$sfield] ?? 'a.vouchno';

$cond = "";
if ($sfield == 1) {
    $normalized = str_replace("GL-", "", strtoupper($searchTerm));
    $cond = ($operator == "=") ? "AND $field = '$normalized'" : "AND $field LIKE '%$normalized%'";
} else {
    $cond = ($operator == "=") ? "AND $field = '$searchTerm'" : "AND $field LIKE '%$searchTerm%'";
}

// 2️⃣ Sorting
$sortBy = $_REQUEST['sortBy'];
$sortField = $fields[$sortBy] ?? 'a.vouchdt,a.vouchno';
$orderAs = strtolower($_REQUEST['orderAs'] ?? 'asc');
$ordcond = ($orderAs === 'desc') ? 'DESC' : 'ASC';
$sortcond = "ORDER BY $sortField $ordcond";


$query = "SELECT a.*,DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt,b.name as custname, c.name as statusnm
                FROM `$maindb` a
                LEFT JOIN accmast b ON a.customer = b.accode
                LEFT JOIN status c ON a.status = c.code
                WHERE a.origin='$origin' AND a.customer = '$useraccode'
                $cond $sortcond LIMIT $rangeFrom, $rangeTo";

$result = mysqli_query($conn, $query) or die(mysqli_error($conn));

$rows = array();
$ctr = 0;
while ($r = mysqli_fetch_assoc($result)) {

    // if ($r['sector'] == "New DELHI") {
    //     $colr = "style='color:red'";
    // } else {
    $colr = "";
    // }

   $bgcolor = "";

    // Change color based on status
    if ($r['status'] == 5) {
        $bgcolor = "background-color:#cfe4fa;"; // light blue
    } elseif ($r['status'] == 7) {
        $bgcolor = "background-color:#ffffcc;"; // light yellow
    } elseif ($r['status'] == 9) {
        $bgcolor = "background-color:#ccffcc;"; // light green
    }


    $ctr++;

    $rows[] = [
        'success' => true,
        'ctr' => $ctr,
        'vouchno' => "GL-" . $r['vouchno'],
        'vouchdt' => $r['mydt'],
        'customer' => $r['custname'],
        'nocart' => $r['nocart'],
        'supplier' => $r['supplier'],
        'description' => $r['description'],
        'pcs' => $r['pcs'],
        'unit' => $r['unit'],
        'marka' => $r['marka'],
        'totcbm' => $r['totcbm'],
        'weight' => $r['weight'],
        'containerno' => $r['containerno'],
        'saildate' => format_date($r['saildate']),
        'destination' => $r['destination'],
        'estimdate' => format_date($r['estimdate']),
        'delvdate' => format_date($r['delvdate']),
        'mobile' => $r['mobile'],
        'status' => $r['statusnm'],
        'colr' => $colr,
        'usertype' => $usertype,
        'bgcolr' => $bgcolor,
        'action' => "$file?vouchno=$r[vouchno]",
    ];
}
//browse($rows);
header("Content-type: application\json");
echo json_encode($rows);
