<?php
ob_start();
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
require("includes/config.php");
require("includes/function.php");
$zdb = $_SESSION["zdbyear_".$dbname];
mysqli_select_db($conn, $zdb);
$frmdate  = $_POST['frmdate'] ?? date('Y-m-01');
$todate   = $_POST['todate']  ?? date('Y-m-d');
$customer = (int)($_POST['customer'] ?? 0);
// Arrived containers not yet in sale
$where = "WHERE d.arrivaldate IS NOT NULL AND d.arrivaldate!='0000-00-00'
          AND d.arrivaldate BETWEEN '$frmdate' AND '$todate'
          AND d.containerno NOT IN (SELECT containerno FROM sale WHERE containerno!='' )";
if ($customer) $where .= " AND d.customer='$customer'";
$res = mysqli_query($conn,
    "SELECT d.vouchno, d.vouchdt, d.arrivaldate, d.containerno,
            c.name AS custname, a.name AS agentname,
            DATEDIFF(CURDATE(), d.arrivaldate) AS days_since_arrival,
            (SELECT SUM(ctn) FROM packlist WHERE dspvouchno=d.vouchno) AS totctn,
            (SELECT SUM(tqty) FROM packlist WHERE dspvouchno=d.vouchno) AS tottqty,
            lp.totamt AS lpcost
     FROM despatch d
     LEFT JOIN accmast c ON c.accode=d.customer
     LEFT JOIN loadingplan lp ON lp.containerno=d.containerno AND d.containerno!=''
     LEFT JOIN accmast a ON a.accode=lp.shipper
     $where ORDER BY d.arrivaldate DESC") or die(mysqli_error($conn));
$rows=[]; while($r=mysqli_fetch_assoc($res)) $rows[]=$r;
?>
<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><title>Pending Sale Bill</title>
<?php include("includes/rpt_style.php"); ?>
</head><body>
<div class="rpt-wrap">
<div class="rpt-header">
    <div class="rpt-title"><i class="fa fa-file-invoice" style="color:#f43f5e;"></i> Pending Sale Bill</div>
    <div class="rpt-meta">Arrival: <?php echo date('d-m-Y',strtotime($frmdate)); ?> to <?php echo date('d-m-Y',strtotime($todate)); ?> &nbsp;·&nbsp; <?php echo count($rows); ?> records</div>
</div>
<?php if (!$rows): ?><div class="empty">No pending sale bills found.</div><?php else: ?>
<table>
    <thead><tr><th>#</th><th>DSP#</th><th>Dispatch Date</th><th>Arrival Date</th><th>Days Since Arrival</th><th>Container No.</th><th>Customer</th><th>Shipper</th><th class="num">CTN</th><th class="num">T.QTY</th><th class="num">LP Cost</th></tr></thead>
    <tbody>
    <?php $tC=$tQ=$tCost=0; foreach($rows as $i=>$r):
        $tC+=(float)$r['totctn']; $tQ+=(float)$r['tottqty']; $tCost+=(float)$r['lpcost'];
        $age=(int)$r['days_since_arrival']; $ageCls=$age>30?'br':($age>14?'ba':'bg');
    ?>
    <tr>
        <td><?php echo $i+1; ?></td>
        <td><span class="badge bb">DSP#<?php echo $r['vouchno']; ?></span></td>
        <td><?php echo $r['vouchdt']?date('d-m-Y',strtotime($r['vouchdt'])):''; ?></td>
        <td style="color:#10b981;font-weight:600;"><?php echo $r['arrivaldate']?date('d-m-Y',strtotime($r['arrivaldate'])):''; ?></td>
        <td class="ctr"><span class="badge <?php echo $ageCls; ?>"><?php echo $age; ?> days</span></td>
        <td style="font-weight:600;"><?php echo htmlspecialchars($r['containerno']??''); ?></td>
        <td><?php echo htmlspecialchars($r['custname']??''); ?></td>
        <td><?php echo htmlspecialchars($r['agentname']??''); ?></td>
        <td class="num"><?php echo $r['totctn']?number_format((float)$r['totctn'],0):'—'; ?></td>
        <td class="num"><?php echo $r['tottqty']?number_format((float)$r['tottqty'],0):'—'; ?></td>
        <td class="num">¥<?php echo number_format((float)$r['lpcost'],2); ?></td>
    </tr>
    <?php endforeach; ?>
    </tbody>
    <tfoot><tr><td colspan="8" class="num">Total</td><td class="num"><?php echo number_format($tC,0); ?></td><td class="num"><?php echo number_format($tQ,0); ?></td><td class="num">¥<?php echo number_format($tCost,2); ?></td></tr></tfoot>
</table>
<?php endif; ?>
</div>
<div class="rpt-actions">
    <button class="btn-print" onclick="window.print()"><i class="fa fa-print"></i> Print</button>
    <button class="btn-back" onclick="if(window.parent&&window.parent!==window){window.parent.document.getElementById('rptframe').src='about:blank';}else{window.close();}"><i class="fa fa-arrow-left"></i> Go Back</button>
</div>
</body></html>
