<?php
ob_start();
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
require("includes/config.php");
require("includes/function.php");

$para      = isset($_POST['para']) ? trim($_POST['para']) : 'R';
$formname1 = ($para == 'P') ? 'Outstanding Payable' : 'Outstanding Receivable';

$zdb = $_SESSION["zdbyear_" . $dbname];
mysqli_select_db($conn, $zdb);

// ── Inputs from filter form ──────────────────────────────────────────────
$facct  = isset($_POST['acname']) ? (int)$_POST['acname'] : 0;
$dtype  = isset($_POST['dtype']) ? trim($_POST['dtype']) : '1';   // 1=Summary, 2=Detail
$fdays  = isset($_POST['days']) && $_POST['days'] !== '' ? (int)$_POST['days'] : 0;

function d2sql($d) {
    if ($d != '' && preg_match('/^(\d{2})-(\d{2})-(\d{4})$/', $d, $m)) {
        return $m[3] . '-' . $m[2] . '-' . $m[1];
    }
    return '';
}
$fasondate    = isset($_POST['asondate']) ? trim($_POST['asondate']) : '';
$fasondateSql = d2sql($fasondate);

if ($fasondateSql == '') {
    browse("As On Date Cannot Be Empty");
    header("refresh:1; url=osbill.php?para=$para");
    exit;
}

function pf($v) {
    $v = (float)$v;
    return $v == 0 ? '' : number_format($v, 2);
}

$formname = "$formname1 - " . (($dtype == '2') ? 'Detail' : 'Summary');

$rows = [];
$custGroups = [];   // customer accode => ['name'=>.., 'bills'=>[...], 'totbill'=>.., 'totrecd'=>.., 'totbal'=>..]
$grandBill = 0;
$grandSettled = 0;
$grandOutstanding = 0;

if ($para != 'P') {
    // ── Receivable: outstanding Sale invoices, as on a cutoff date ────────────
    $acctcond = ($facct > 0) ? " AND s.`customer`='$facct' " : '';

    $sql = "SELECT s.`vouchno`, s.`vouchdt`, s.`customer`, c.`name` AS acctname, s.`totamt`,
                   COALESCE((SELECT SUM(bs.`settleamt`) FROM `billsettle` bs
                             WHERE bs.`saleinvno`=s.`vouchno` AND bs.`vouchdt` <= '$fasondateSql'), 0) AS settled
            FROM `sale` s
            LEFT JOIN `accmast` c ON c.accode = s.customer
            WHERE s.`vouchdt` <= '$fasondateSql' $acctcond
            ORDER BY c.`name` ASC, s.`vouchdt` ASC, s.`vouchno` ASC";
    $res = mysqli_query($conn, $sql) or die(mysqli_error($conn));
    while ($r = mysqli_fetch_assoc($res)) {
        $outstanding = (float)$r['totamt'] - (float)$r['settled'];
        if ($outstanding <= 0.005) continue;
        $dt = $r['vouchdt'] != '0000-00-00' ? date('d-m-Y', strtotime($r['vouchdt'])) : '';
        $ageDays = ($r['vouchdt'] != '0000-00-00') ? (int)((strtotime($fasondateSql) - strtotime($r['vouchdt'])) / 86400) : 0;

        if ($fdays > 0 && $ageDays <= $fdays) continue;

        $row = [
            'vouchno'     => $r['vouchno'],
            'vouchdt'     => $dt,
            'acctname'    => $r['acctname'],
            'totamt'      => (float)$r['totamt'],
            'settled'     => (float)$r['settled'],
            'outstanding' => $outstanding,
            'age'         => $ageDays,
        ];
        $rows[] = $row;

        $custKey = (int)$r['customer'];
        if (!isset($custGroups[$custKey])) {
            $custGroups[$custKey] = [
                'name'    => $r['acctname'],
                'bills'   => [],
                'totbill' => 0,
                'totrecd' => 0,
                'totbal'  => 0,
            ];
        }
        $custGroups[$custKey]['bills'][]   = $row;
        $custGroups[$custKey]['totbill']  += (float)$r['totamt'];
        $custGroups[$custKey]['totrecd']  += (float)$r['settled'];
        $custGroups[$custKey]['totbal']   += $outstanding;

        $grandBill        += (float)$r['totamt'];
        $grandSettled     += (float)$r['settled'];
        $grandOutstanding += $outstanding;
    }
}

$mtdt = date('d-m-Y', strtotime($fasondateSql));

// ── CSV Export ───────────────────────────────────────────────────────────
if (isset($_POST['excel'])) {
    header('Content-Type: text/csv; charset=utf-8');
    $suffix = ($dtype == '2') ? 'Detail' : 'Summary';
    header('Content-Disposition: attachment; filename="' . str_replace(' ', '_', $formname1) . '_' . $suffix . '_' . date('d-m-Y') . '.csv"');
    $out = fopen('php://output', 'w');
    if ($para != 'P') {
        if ($dtype == '2') {
            fputcsv($out, ['Customer', 'Date', 'Bill No.', 'Bill Amount', 'Received', 'Balance', 'Age (Days)']);
            foreach ($custGroups as $grp) {
                foreach ($grp['bills'] as $b) {
                    fputcsv($out, [$grp['name'], $b['vouchdt'], $b['vouchno'], number_format($b['totamt'],2,'.',''), number_format($b['settled'],2,'.',''), number_format($b['outstanding'],2,'.',''), $b['age']]);
                }
                fputcsv($out, ['', '', 'Total', number_format($grp['totbill'],2,'.',''), number_format($grp['totrecd'],2,'.',''), number_format($grp['totbal'],2,'.',''), '']);
                fputcsv($out, []);
            }
        } else {
            fputcsv($out, ['Customer', 'Bill Amount', 'Received', 'Balance']);
            foreach ($custGroups as $grp) {
                fputcsv($out, [$grp['name'], number_format($grp['totbill'],2,'.',''), number_format($grp['totrecd'],2,'.',''), number_format($grp['totbal'],2,'.','')]);
            }
        }
        fputcsv($out, ['Grand Total', number_format($grandBill,2,'.',''), number_format($grandSettled,2,'.',''), number_format($grandOutstanding,2,'.','')]);
    }
    fclose($out);
    exit;
}

include("includes/linkshead.php");
?>
<style>
    body { background:#f0f2f5; margin:0; padding:10px 14px; }

    .sim-card {
        background:#fff; border-radius:10px;
        box-shadow:0 1px 3px rgba(0,0,0,.08), 0 4px 16px rgba(0,0,0,.06);
        overflow:hidden;
    }
    .sim-card-header {
        background:#fff; border-bottom:2px solid #f1f5f9; padding:12px 20px;
        display:flex; align-items:center; justify-content:space-between;
    }
    .sim-card-header h2 { font-size:15px; font-weight:700; color:#1e293b; margin:0; }
    .sim-card-header .sub { font-size:11.5px; color:#94a3b8; margin-top:2px; }

    .sim-body { padding:18px 20px; }

    .btn { padding:8px 16px; border:none; border-radius:6px; font-size:12.5px; font-weight:600; cursor:pointer; display:inline-flex; align-items:center; gap:6px; text-decoration:none; }
    .btn-export { background:#16a34a; color:#fff; }
    .btn-export:hover { background:#15803d; }
    .btn-print { background:#4a5759; color:#fff; }
    .btn-print:hover { background:#374043; }

    table.reg { width:100%; border-collapse:collapse; font-size:12px; }
    table.reg th {
        background:#f8fafc; color:#374151; font-weight:700; font-size:11px;
        text-transform:uppercase; letter-spacing:.04em; padding:8px 10px;
        border-bottom:2px solid #e2e8f0; text-align:left; white-space:nowrap;
    }
    table.reg th.num { text-align:right; }
    table.reg td { padding:6px 10px; border-bottom:1px solid #f1f5f9; white-space:nowrap; }
    table.reg td.num { text-align:right; }
    .num { text-align:right; }
    table.reg tbody tr.mainrow { cursor:pointer; }
    table.reg tbody tr.mainrow:hover { background:#eff6ff; }
    table.reg tfoot td { font-weight:700; background:#f8fafc; border-top:2px solid #e2e8f0; }

    .age-pill { font-size:10.5px; font-weight:700; border-radius:20px; padding:2px 10px; color:#fff; display:inline-block; }
    .age-ok    { background:#16a34a; }
    .age-warn  { background:#f59e0b; }
    .age-late  { background:#dc2626; }

    .reg-wrap { overflow:auto; }

    .acc-block { border:1px solid #e2e8f0; border-radius:8px; margin-bottom:18px; overflow:hidden; }
    .acc-head {
        background:#eff6ff; padding:9px 14px; display:flex; justify-content:space-between; align-items:center;
        border-bottom:1px solid #e2e8f0;
    }
    .acc-head .acc-name { font-size:13px; font-weight:700; color:#1e293b; }
    tr.closerow td { background:#dbeafe; font-weight:700; border-top:2px solid #e2e8f0; }

    .empty-state { text-align:center; padding:60px 20px; color:#94a3b8; }
    .empty-state i { font-size:32px; margin-bottom:12px; display:block; }

    @media print {
        body { background:#fff; padding:0; }
        .no-print { display:none !important; }
        .sim-card { box-shadow:none; border-radius:0; }
        table.reg tbody tr.mainrow:hover { background:none; }
    }
</style>
</head>
<body>
<div class="sim-card">
    <div class="sim-card-header">
        <div>
            <h2><i class="fa fa-hourglass-half" style="color:#3b82f6;margin-right:8px;font-size:14px;"></i><?php echo $formname; ?></h2>
            <div class="sub">As on <?php echo $mtdt; ?><?php echo $fdays > 0 ? ' &middot; more than ' . $fdays . ' days outstanding' : ''; ?></div>
        </div>
        <?php if ($para != 'P'): ?>
        <div class="no-print" style="display:flex;gap:8px;">
            <button type="button" class="btn btn-print" onclick="window.print()"><i class="fa fa-print"></i> Print</button>
    <button onclick="if(window.parent&&window.parent!==window){window.parent.document.getElementById('rptframe').src='about:blank';}else{window.close();}" style="padding:8px 22px;background:#f1f5f9;color:#374151;border:none;border-radius:8px;font-size:13px;font-weight:600;cursor:pointer;margin-left:8px;"><i class="fa fa-arrow-left"></i> Go Back</button>
            <form method="POST" style="margin:0;">
                <?php foreach ($_POST as $k => $v) { if ($k != 'view') echo '<input type="hidden" name="' . htmlspecialchars($k) . '" value="' . htmlspecialchars($v) . '">'; } ?>
                <button type="submit" name="excel" value="1" class="btn btn-export"><i class="fa fa-file-csv"></i> Export CSV</button>
            </form>
        </div>
        <?php endif; ?>
    </div>

    <div class="sim-body">

        <?php if ($para == 'P'): ?>
        <div class="empty-state">
            <i class="fa fa-tools"></i>
            Outstanding Payable tracking isn't built yet.<br>
            <span style="font-size:11.5px;">Bill settlement currently only tracks Sale invoices against receipts. Let me know if you'd like Purchase bills tracked the same way against payments.</span>
        </div>
        <?php elseif (count($custGroups) == 0): ?>
        <div class="empty-state">
            <i class="fa fa-check-circle" style="color:#16a34a;"></i>
            No outstanding receivables for the selected filters.
        </div>

        <?php elseif ($dtype == '1'): ?>
        <!-- ── SUMMARY: one row per customer ───────────────────────────────── -->
        <div class="reg-wrap">
        <table class="reg">
            <thead>
                <tr>
                    <th>Customer</th>
                    <th class="num">Bill Amount</th>
                    <th class="num">Received</th>
                    <th class="num">Balance</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($custGroups as $caccode => $grp): ?>
                <tr class="mainrow" onclick="filterByCustomer(<?php echo $caccode; ?>)">
                    <td style="font-weight:600;color:#1e293b;"><?php echo htmlspecialchars($grp['name'] ?? ''); ?></td>
                    <td class="num"><?php echo pf($grp['totbill']); ?></td>
                    <td class="num"><?php echo pf($grp['totrecd']); ?></td>
                    <td class="num" style="font-weight:700;color:#dc2626;"><?php echo pf($grp['totbal']); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
            <tfoot>
                <tr>
                    <td style="text-align:right;">Grand Total (<?php echo count($custGroups); ?> customers)</td>
                    <td class="num"><?php echo pf($grandBill); ?></td>
                    <td class="num"><?php echo pf($grandSettled); ?></td>
                    <td class="num"><?php echo pf($grandOutstanding); ?></td>
                </tr>
            </tfoot>
        </table>
        </div>

        <?php else: ?>
        <!-- ── DETAIL: grouped by customer, billwise underneath ───────────────── -->
        <?php foreach ($custGroups as $caccode => $grp): ?>
        <div class="acc-block">
            <div class="acc-head">
                <span class="acc-name"><?php echo htmlspecialchars($grp['name'] ?? ''); ?></span>
            </div>
            <div class="reg-wrap">
            <table class="reg">
                <thead>
                    <tr>
                        <th>Date</th>
                        <th>Bill No.</th>
                        <th class="num">Bill Amount</th>
                        <th class="num">Received</th>
                        <th class="num">Balance</th>
                        <th style="text-align:center;">Age</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($grp['bills'] as $b): ?>
                    <tr class="mainrow" onclick="window.open('sale.php?mode=edit&vouchno=<?php echo $b['vouchno']; ?>','_blank')">
                        <td><?php echo $b['vouchdt']; ?></td>
                        <td style="font-weight:600;color:#2563eb;"><?php echo $b['vouchno']; ?></td>
                        <td class="num"><?php echo pf($b['totamt']); ?></td>
                        <td class="num"><?php echo pf($b['settled']); ?></td>
                        <td class="num" style="font-weight:700;color:#dc2626;"><?php echo pf($b['outstanding']); ?></td>
                        <td style="text-align:center;">
                            <?php $agecls = $b['age'] <= 30 ? 'age-ok' : ($b['age'] <= 60 ? 'age-warn' : 'age-late'); ?>
                            <span class="age-pill <?php echo $agecls; ?>"><?php echo $b['age']; ?>d</span>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                    <tr class="closerow">
                        <td colspan="2" style="text-align:right;">Total</td>
                        <td class="num"><?php echo pf($grp['totbill']); ?></td>
                        <td class="num"><?php echo pf($grp['totrecd']); ?></td>
                        <td class="num"><?php echo pf($grp['totbal']); ?></td>
                        <td></td>
                    </tr>
                </tbody>
            </table>
            </div>
        </div>
        <?php endforeach; ?>

        <div style="margin-top:8px;padding:12px 16px;background:#1e293b;color:#fff;border-radius:8px;display:flex;justify-content:flex-end;gap:24px;font-size:12.5px;font-weight:600;">
            <span><span style="color:#94a3b8;margin-right:6px;font-weight:500;">Total Bill Amount</span><?php echo number_format($grandBill, 2); ?></span>
            <span><span style="color:#94a3b8;margin-right:6px;font-weight:500;">Total Received</span><?php echo number_format($grandSettled, 2); ?></span>
            <span><span style="color:#94a3b8;margin-right:6px;font-weight:500;">Total Balance</span><?php echo number_format($grandOutstanding, 2); ?></span>
        </div>
        <?php endif; ?>

    </div>
</div>

<?php include("includes/linksfoot.php"); ?>
<script>
function filterByCustomer(accode) {
    var f = document.createElement('form');
    f.method = 'POST';
    f.action = 'osbill_fr.php';
    f.target = '_blank';
    var fields = <?php echo json_encode($_POST); ?>;
    fields['acname'] = accode;
    fields['dtype']  = '2';
    for (var k in fields) {
        var inp = document.createElement('input');
        inp.type = 'hidden';
        inp.name = k;
        inp.value = fields[k];
        f.appendChild(inp);
    }
    document.body.appendChild(f);
    f.submit();
}
</script>
</body>
</html>
