View Code - 20260722

parcel_reports_body.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ======================================================================
   PARCEL REPORTS – BODY (FINAL STABLE VERSION WITH STRICT DATE CONVERSION)
   Simple Join + Correct Daily + Fully Working Pending with Date Filter
   ====================================================================== */

$isPrint = isset($_GET['print_view']) || isset($_GET['export']);

$tableClass = "table table-sm table-bordered table-hover table-striped align-middle mb-0";
$theadClass = "table-light text-uppercase small text-center";

// ----------------------------------------------------------------------
// CRITICAL FIX: Convert UI Dates (e.g., 01-Jun-2026) to MySQL Format (2026-06-01)
// ----------------------------------------------------------------------
$db_from = (isset($from) && $from != '') ? date('Y-m-d', strtotime($from)) : date('Y-m-01');
$db_to   = (isset($to) && $to != '')     ? date('Y-m-d', strtotime($to))   : date('Y-m-t');

/* ================================================================
   COMMON JOIN (NO TRIM, NO COLLATION DRAMA)
================================================================ */
$joinCondition = "
  LEFT JOIN parcel_send_entry pse
    ON pse.company_id = psi.company_id
   AND psi.bill_no = pse.bill_no
";

/* ================================================================
   1. SEND REPORT
================================================================ */
if ($report_type === 'send') {

$st = $pdo->prepare("
    SELECT
      pse.bill_no,
      pse.vehicle_no,
      pse.send_date,
      pse.remark,
      psi.transport,
      psi.station
    FROM parcel_send_entry pse
    LEFT JOIN parcel_stock_in psi
      ON psi.company_id = pse.company_id
     AND psi.bill_no = pse.bill_no
    WHERE pse.company_id = ?
      AND pse.send_date BETWEEN ? AND ?
    ORDER BY pse.send_date
  ");

$st->execute([$company_id, $db_from, $db_to]);

echo "<table class='{$tableClass}'>
  <thead class='{$theadClass}'><tr>
  <th>Bill No</th><th>Transport</th><th>Station</th>
  <th>Vehicle</th><th>Send Date</th><th>Remark</th>
  </tr></thead><tbody>";

foreach ($st as $r) {
    echo "<tr>
      <td>".h($r['bill_no'])."</td>
      <td>".h($r['transport'])."</td>
      <td>".h($r['station'])."</td>
      <td>".h($r['vehicle_no'])."</td>
      <td>".h($r['send_date'])."</td>
      <td>".h($r['remark'])."</td>
    </tr>";
  }

echo "</tbody></table>";
}

/* ================================================================
   2. BILL DETAIL
================================================================ */
elseif ($report_type === 'bill') {

$st = $pdo->prepare("
    SELECT bill_no,bill_date,party,transport,station
    FROM parcel_stock_in
    WHERE company_id = ?
      AND bill_date BETWEEN ? AND ?
    ORDER BY bill_date
  ");

$st->execute([$company_id, $db_from, $db_to]);

echo "<table class='{$tableClass}'>
  <thead class='{$theadClass}'><tr>
  <th>Bill No</th><th>Bill Date</th>
  <th>Party</th><th>Transport</th><th>Station</th>
  </tr></thead><tbody>";

foreach ($st as $r) {
    echo "<tr>
      <td>".h($r['bill_no'])."</td>
      <td>".h($r['bill_date'])."</td>
      <td>".h($r['party'])."</td>
      <td>".h($r['transport'])."</td>
      <td>".h($r['station'])."</td>
    </tr>";
  }

echo "</tbody></table>";
}

/* ================================================================
   3. STOCK (PENDING PHYSICAL)
================================================================ */
elseif ($report_type === 'stock') {

$st = $pdo->prepare("
    SELECT psi.bill_no,psi.bill_date,psi.party,
           psi.transport,psi.station
    FROM parcel_stock_in psi
    {$joinCondition}
    WHERE psi.company_id = ?
      AND pse.id IS NULL
      AND psi.cleared_unknown = 0
    ORDER BY psi.bill_date
  ");

$st->execute([$company_id]);

echo "<table class='{$tableClass}'>
  <thead class='{$theadClass}'><tr>
  <th>Bill No</th><th>Date</th>
  <th>Party</th><th>Transport</th><th>Station</th>
  </tr></thead><tbody>";

echo "</tbody></table>";
}

/* ================================================================
   4. DELAY REPORT
================================================================ */
elseif ($report_type === 'delay') {

$st = $pdo->prepare("
    SELECT psi.bill_no,psi.bill_date,psi.party,
           psi.transport,psi.station,
           DATEDIFF(CURDATE(), psi.bill_date) AS delay_days
    FROM parcel_stock_in psi
    {$joinCondition}
    WHERE psi.company_id = ?
      AND pse.id IS NULL
      AND psi.cleared_unknown = 0
    ORDER BY delay_days DESC
  ");

$st->execute([$company_id]);

echo "<table class='{$tableClass}'>
  <thead class='{$theadClass}'><tr>
  <th>Bill No</th><th>Date</th>
  <th>Party</th><th>Transport</th>
  <th>Station</th><th>Delay</th>
  </tr></thead><tbody>";

foreach ($st as $r) {

$d = (int)$r['delay_days'];
    $class = ($d > 2 && !$isPrint)
        ? "bg-danger text-white fw-bold text-center"
        : "text-center";

echo "<tr>
      <td>".h($r['bill_no'])."</td>
      <td>".h($r['bill_date'])."</td>
      <td>".h($r['party'])."</td>
      <td>".h($r['transport'])."</td>
      <td>".h($r['station'])."</td>
      <td class='{$class}'>{$d}</td>
    </tr>";
  }

echo "</tbody></table>";
}

/* ================================================================
   5. DAILY SUMMARY (CORRECT – NO NEGATIVE)
================================================================ */
elseif ($report_type === 'daily') {

$st = $pdo->prepare("
    SELECT 
        psi.bill_date AS d,
        COUNT(*) AS total_bills,
        SUM(CASE WHEN pse.id IS NOT NULL THEN 1 ELSE 0 END) AS sent_bills
    FROM parcel_stock_in psi
    {$joinCondition}
    WHERE psi.company_id = ?
      AND psi.bill_date BETWEEN ? AND ?
    GROUP BY psi.bill_date
    ORDER BY psi.bill_date
  ");

$st->execute([$company_id, $db_from, $db_to]);

echo "<table class='{$tableClass}'>
  <thead class='{$theadClass}'><tr>
  <th>Date</th><th>Total Bills</th>
  <th>Sent</th><th>Pending</th>
  </tr></thead><tbody>";

foreach ($st as $r) {

$total = (int)$r['total_bills'];
    $sent  = (int)$r['sent_bills'];
    $pending = $total - $sent;

echo "<tr>
      <td>".h($r['d'])."</td>
      <td class='text-center'>{$total}</td>
      <td class='text-center'>{$sent}</td>
      <td class='text-center fw-bold'>{$pending}</td>
    </tr>";
  }

echo "</tbody></table>";
}

/* ================================================================
   6. PENDING REPORT (SHOWS ALL PENDING - IGNORES DATE FILTER)
================================================================ */
elseif ($report_type === 'pending') {

// Yahan se BETWEEN date filter hata diya hai taaki lifetime pending dikhe
  $st = $pdo->prepare("
    SELECT psi.id, psi.bill_no, psi.bill_date,
           psi.party, psi.transport, psi.station
    FROM parcel_stock_in psi
    {$joinCondition}
    WHERE psi.company_id = ?
      AND pse.id IS NULL
      AND psi.cleared_unknown = 0
    ORDER BY psi.bill_date
  ");

// Execute me sirf company_id pass hogi
  $st->execute([$company_id]);

echo "<table class='{$tableClass}'>
  <thead class='{$theadClass}'><tr>
  <th>Bill No</th><th>Date</th>
  <th>Party</th><th>Transport</th>
  <th>Station</th>";

if (!$isPrint) echo "<th>Action</th>";

echo "</tr></thead><tbody>";

if (!$isPrint) {
      echo "<td>
        <form method='post' action='parcel_clear.php'>
          <input type='hidden' name='id' value='".(int)$r['id']."'>
          <button type='submit' class='btn btn-sm btn-success'>
            Clear
          </button>
        </form>
      </td>";
    }

echo "</tr>";
  }

echo "</tbody></table>";
}
?>