datetime('now', '-4 days'))";
$params = [];
if ($audience === 'vendor') {
$where = "j.vendor_id = ? AND ($where)";
$params[] = $vendor_id;
}
$sql = "
SELECT j.*, v.slug AS vendor_slug, v.name AS vendor_name,
CASE WHEN j.due_date IS NULL THEN ''
ELSE CAST(CAST(strftime('%m', j.due_date) AS INTEGER) AS TEXT) || '-' ||
CAST(CAST(strftime('%d', j.due_date) AS INTEGER) AS TEXT)
END AS due_short,
(SELECT MAX(changed_at) FROM job_history
WHERE job_id = j.id AND field = 'status' AND new_value = 'Finished') AS finished_at,
(SELECT MAX(changed_at) FROM job_history
WHERE job_id = j.id AND field = 'status' AND new_value = 'Shipped') AS shipped_at,
(SELECT MAX(changed_at) FROM job_history
WHERE job_id = j.id AND field = 'status' AND new_value = 'Received') AS received_at
FROM jobs j
JOIN vendors v ON v.id = j.vendor_id
WHERE $where
ORDER BY (j.status = '') DESC,
(j.status = 'Finished') DESC,
(j.status = 'Shipped') DESC,
(j.ack = 'new') DESC,
j.due_date IS NULL,
j.due_date ASC,
j.job
";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();
ob_start();
?>