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

$formname = 'Ledger';

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

// ── Inputs from filter form ──────────────────────────────────────────────
$group    = isset($_POST['group'])    ? trim($_POST['group'])    : '';
$acname   = isset($_POST['acname'])   ? (int)$_POST['acname']     : 0;
$city     = isset($_POST['city'])     ? trim($_POST['city'])     : '';
$frmdt    = isset($_POST['frmdt'])    ? trim($_POST['frmdt'])    : '';
$todt     = isset($_POST['todt'])     ? trim($_POST['todt'])     : '';
$dtype    = isset($_POST['dtype'])    ? trim($_POST['dtype'])    : '2';   // 2=Detail, 1=Summary
$orderby  = isset($_POST['orderby'])  ? trim($_POST['orderby'])  : '2';   // 2=Name, 1=City
$prinnarr = isset($_POST['prinnarr']) ? trim($_POST['prinnarr']) : '1';
$prinzero = isset($_POST['prinzero']) ? trim($_POST['prinzero']) : '1';

function d2sql($d) {
    if ($d != '' && preg_match('/^(\d{2})-(\d{2})-(\d{4})$/', $d, $m)) {
        return $m[3] . '-' . $m[2] . '-' . $m[1];
    }
    return '';
}
$fromsql = d2sql($frmdt);
$tosql   = d2sql($todt);

if ($fromsql == '') {
    browse("From Date Cannot Be Empty");
    header("refresh:1; url=ledger.php");
    exit;
}
if ($tosql == '') {
    browse("To Date Cannot Be Empty");
    header("refresh:1; url=ledger.php");
    exit;
}

// ── Build account list per filters (group / single account / city) ──────────
$where = ["`grpcode` > '002'"];
if ($acname > 0)   $where[] = "`accode` = '$acname'";
if ($group != '')  $where[] = "`grpcode` = '" . mysqli_real_escape_string($conn, $group) . "'";
if ($city  != '')  $where[] = "`city` = '"    . mysqli_real_escape_string($conn, $city)  . "'";
$whereSql = "WHERE " . implode(' AND ', $where);

$orderSql = ($orderby == '1') ? "`city` ASC, `name` ASC" : "`name` ASC";

$accounts = [];
$ares = mysqli_query($conn, "SELECT `accode`,`name`,`grpcode`,`city`,`openbal`,`dc`,`currency` FROM `accmast` $whereSql ORDER BY $orderSql") or die(mysqli_error($conn));
while ($r = mysqli_fetch_assoc($ares)) { $accounts[] = $r; }

function pf($v) {
    $v = (float)$v;
    return $v == 0 ? '' : number_format($v, 2);
}
function balstr($v) {
    $v = (float)$v;
    if (abs($v) < 0.005) return '0.00';
    return number_format(abs($v), 2) . ' ' . ($v < 0 ? 'Dr' : 'Cr');
}

$sourceMap = [
    'BR'  => ['label' => 'Bank Receipt', 'file' => 'bankrecp.php'],
    'BP'  => ['label' => 'Bank Payment', 'file' => 'bankpay.php'],
    'CR'  => ['label' => 'Cash Receipt', 'file' => 'cashrecp.php'],
    'CP'  => ['label' => 'Cash Payment', 'file' => 'cashpay.php'],
    'PUR' => ['label' => 'Purchase',     'file' => 'purchentry.php'],
    'LP'  => ['label' => 'Loading Plan', 'file' => 'loadingplan.php'],
    'ARR' => ['label' => 'Arrival',      'file' => 'despatch.php'],
    'DSP' => ['label' => 'Despatch',     'file' => 'despatch.php'],
];

// ── LP narration fallback (for old entries where narr was not stored) ────
// Build a map keyed by [vouchno][accode] with per-field narrations
// Since we can't know trnno from old data, we store ALL possible narrations per accode
// and match by the sign/magnitude of the actran amount at row-build time using lpLineAmts
$lpNarr    = [];   // [vouchno][accode] = ['amt'=>..., 'adv'=>..., 'our'=>..., 'shpa'=>...]
$lpLineAmts= [];   // [vouchno][accode] = ['amt'=>val, 'adv'=>val, 'our'=>val, 'shpa'=>val]

$lpRes = mysqli_query($conn,
    "SELECT lt.vouchno, lt.supplier, lt.customer, lt.amt, lt.advance, lt.our, lt.shipperamount,
            c.name AS custname, s.name AS supname,
            lp.shipper, ag.name AS agentname,
            lp.adduser
     FROM loadingplantr lt
     LEFT JOIN loadingplan lp ON lp.vouchno = lt.vouchno
     LEFT JOIN accmast c  ON c.accode  = lt.customer
     LEFT JOIN accmast s  ON s.accode  = lt.supplier
     LEFT JOIN accmast ag ON ag.accode = lp.shipper
     WHERE lt.supplier > 0");
if ($lpRes) {
    while ($lr = mysqli_fetch_assoc($lpRes)) {
        $vn   = (int)$lr['vouchno'];
        $sup  = (int)$lr['supplier'];
        $cust = (int)$lr['customer'];
        $cn   = $lr['custname']  ?? '';
        $sn   = $lr['supname']   ?? '';
        $an   = $lr['agentname'] ?? '';

        $amt  = (float)$lr['amt'];
        $adv  = (float)$lr['advance'];
        $our  = (float)$lr['our'];
        $shpa = (float)$lr['shipperamount'];

        // Supplier: store narr keyed by field name + store actual amounts for matching
        if ($sup > 0) {
            $lpNarr[$vn][$sup] = [
                'amt'  => $amt  > 0 ? 'Paid by ' . ($cn ?: 'Customer')                     : '',
                'adv'  => $adv  > 0 ? 'Client Adv. paid by ' . ($cn ?: 'Customer')          : '',
                'our'  => $our  > 0 ? 'Paid by Us'                                          : '',
                'shpa' => $shpa > 0 ? 'Shipper Amt. paid to ' . ($an ?: 'Shipper')          : '',
            ];
            $lpLineAmts[$vn][$sup] = ['amt'=>$amt, 'adv'=>$adv, 'our'=>$our, 'shpa'=>$shpa];
        }
        // Customer: client adv credit
        if ($cust > 0 && $adv > 0) {
            $lpNarr[$vn][$cust]['adv'] = 'Client Adv. to ' . ($sn ?: 'Supplier');
            $lpLineAmts[$vn][$cust] = ['adv'=>$adv];
        }
        // Shipper/agent: shipper amt credit
        $shipperAc = (int)($lr['shipper'] ?? 0);
        if ($shipperAc > 0 && $shpa > 0) {
            $lpNarr[$vn][$shipperAc]['shpa'] = 'Received from ' . ($sn ?: 'Supplier');
            $lpLineAmts[$vn][$shipperAc] = ['shpa'=>$shpa];
        }
    }
}

// ── Compute ledger data per account ──────────────────────────────────────────
$ledgerData = [];   // accode => ['info'=>..., 'opening'=>..., 'rows'=>[...], 'closing'=>..., 'totDr'=>..., 'totCr'=>...]
$grandTotDr = 0; $grandTotCr = 0;

foreach ($accounts as $acc) {
    $accode = (int)$acc['accode'];
    $signedOpenBal = ($acc['dc'] == 'D') ? -1 * (float)$acc['openbal'] : (float)$acc['openbal'];

    $opening = $signedOpenBal;
    $oqRes = mysqli_query($conn, "SELECT SUM(`amount`) AS s FROM `actran` WHERE `accode`='$accode' AND `vouchdt` < '" . mysqli_real_escape_string($conn, $fromsql) . "'") or die(mysqli_error($conn));
    $oqRow = mysqli_fetch_assoc($oqRes);
    $opening += (float)($oqRow['s'] ?? 0);

    $tres = mysqli_query($conn, "SELECT * FROM `actran` WHERE `accode`='$accode' AND `vouchdt` >= '" . mysqli_real_escape_string($conn, $fromsql) . "' AND `vouchdt` <= '" . mysqli_real_escape_string($conn, $tosql) . "' ORDER BY `vouchdt` ASC, `srl` ASC") or die(mysqli_error($conn));

    $running = $opening;
    $totDr = 0; $totCr = 0;
    $rows = [];
    while ($t = mysqli_fetch_assoc($tres)) {
        $amt = (float)$t['amount'];
        $dr = $amt < 0 ? abs($amt) : 0;
        $cr = $amt > 0 ? $amt : 0;
        $totDr += $dr;
        $totCr += $cr;
        $running += $amt;

        // Build narration — use stored narr, or fallback for old LP entries
        $narr = $t['narr'];
        if ($narr == '' && $t['source'] === 'LP') {
            $vn    = (int)$t['vouchno'];
            $acMap = $lpNarr[$vn][$accode]     ?? [];
            $amts  = $lpLineAmts[$vn][$accode] ?? [];
            $absAmt = abs($amt);
            // Match this actran row to the correct LP field by comparing amount values
            if     ($absAmt > 0 && isset($amts['amt'])  && abs((float)$amts['amt'])  == $absAmt && !empty($acMap['amt']))  $narr = $acMap['amt'];
            elseif ($absAmt > 0 && isset($amts['adv'])  && abs((float)$amts['adv'])  == $absAmt && !empty($acMap['adv']))  $narr = $acMap['adv'];
            elseif ($absAmt > 0 && isset($amts['our'])  && abs((float)$amts['our'])  == $absAmt && !empty($acMap['our']))  $narr = $acMap['our'];
            elseif ($absAmt > 0 && isset($amts['shpa']) && abs((float)$amts['shpa']) == $absAmt && !empty($acMap['shpa'])) $narr = $acMap['shpa'];
            // If still empty just show first available narration
            elseif (!empty($acMap['amt']))  $narr = $acMap['amt'];
            elseif (!empty($acMap['adv']))  $narr = $acMap['adv'];
            elseif (!empty($acMap['our']))  $narr = $acMap['our'];
            elseif (!empty($acMap['shpa'])) $narr = $acMap['shpa'];
        }

        $rows[] = [
            'vouchdt' => $t['vouchdt'],
            'vouchno' => $t['vouchno'],
            'source'  => $t['source'],
            'narr'    => $narr,
            'dr'      => $dr,
            'cr'      => $cr,
            'balance' => $running,
        ];
    }

    if ($prinzero == '2' && count($rows) == 0 && abs($opening) < 0.005) {
        continue; // skip zero-balance, no-activity accounts
    }

    $ledgerData[$accode] = [
        'info'    => $acc,
        'opening' => $opening,
        'rows'    => $rows,
        'closing' => $running,
        'totDr'   => $totDr,
        'totCr'   => $totCr,
    ];
    $grandTotDr += $totDr;
    $grandTotCr += $totCr;
}

$mfdt = date('d-m-Y', strtotime($fromsql));
$mtdt = date('d-m-Y', strtotime($tosql));

// ── CSV Export ───────────────────────────────────────────────────────────
if (isset($_POST['excel'])) {
    header('Content-Type: text/csv; charset=utf-8');
    $suffix = ($dtype == '1') ? 'Summary' : 'Detail';
    header('Content-Disposition: attachment; filename="Ledger_' . $suffix . '_' . date('d-m-Y') . '.csv"');
    $out = fopen('php://output', 'w');

    if ($dtype == '1') {
        fputcsv($out, ['Account','City','Opening Bal','Debit','Credit','Closing Bal']);
        foreach ($ledgerData as $accode => $d) {
            fputcsv($out, [
                $d['info']['name'],
                $d['info']['city'],
                balstr($d['opening']),
                number_format($d['totDr'], 2, '.', ''),
                number_format($d['totCr'], 2, '.', ''),
                balstr($d['closing']),
            ]);
        }
    } else {
        fputcsv($out, ['Account','Date','Voucher No','Type','Narration','Debit','Credit','Balance']);
        foreach ($ledgerData as $accode => $d) {
            fputcsv($out, [$d['info']['name'], '', '', 'Opening Balance', '', '', '', balstr($d['opening'])]);
            foreach ($d['rows'] as $r) {
                fputcsv($out, [
                    '',
                    $r['vouchdt'] != '0000-00-00' ? date('d-m-Y', strtotime($r['vouchdt'])) : '',
                    $r['vouchno'],
                    $sourceMap[$r['source']]['label'] ?? $r['source'],
                    ($prinnarr == '1') ? $r['narr'] : '',
                    number_format($r['dr'], 2, '.', ''),
                    number_format($r['cr'], 2, '.', ''),
                    balstr($r['balance']),
                ]);
            }
            fputcsv($out, ['', '', '', 'Closing Balance', '', number_format($d['totDr'],2,'.',''), number_format($d['totCr'],2,'.',''), balstr($d['closing'])]);
            fputcsv($out, []);
        }
    }
    fclose($out);
    exit;
}

include("includes/linkshead.php");
?>
<style>
    body { padding-bottom:70px !important; 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; }

    .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; }
    .acc-head .acc-city { font-size:11px; color:#64748b; margin-left:8px; }

    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.clickable { cursor:pointer; }
    table.reg tbody tr.clickable:hover { background:#eff6ff; }
    table.reg tr.openrow td { background:#fef9c3; font-weight:600; }
    table.reg tr.closerow td { background:#dbeafe; font-weight:700; border-top:2px solid #e2e8f0; }
    table.reg tfoot td { font-weight:700; background:#f8fafc; border-top:2px solid #e2e8f0; }

    .reg-wrap { overflow:auto; }
    .grand-total {
        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;
    }
    .grand-total span.lbl { color:#94a3b8; margin-right:6px; font-weight:500; }
</style>

<body>
<div class="sim-card">
    <div class="sim-card-header">
        <div>
            <h2><i class="fa fa-book-open" style="color:#3b82f6;margin-right:8px;font-size:14px;"></i><?php echo $formname; ?> (<?php echo $dtype=='1' ? 'Summary' : 'Detail'; ?>)</h2>
            <div class="sub"><?php echo $mfdt; ?> to <?php echo $mtdt; ?></div>
        </div>
        <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>

    <div class="sim-body">

        <?php if (count($ledgerData) == 0): ?>
        <div style="text-align:center;padding:40px;color:#94a3b8;">No accounts found for the selected filters.</div>
        <?php else: ?>

        <?php if ($dtype == '1'): ?>
        <!-- ── SUMMARY VIEW ─────────────────────────────────────────────── -->
        <div class="reg-wrap">
        <table class="reg">
            <thead>
                <tr>
                    <th>Account</th>
                    <th>City</th>
                    <th class="num">Opening Bal</th>
                    <th class="num">Debit</th>
                    <th class="num">Credit</th>
                    <th class="num">Closing Bal</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($ledgerData as $accode => $d): ?>
                <tr class="clickable" onclick="window.open('ledger.php?accode=<?php echo $accode; ?>','_blank')">
                    <td><?php echo htmlspecialchars($d['info']['name']); ?></td>
                    <td><?php echo htmlspecialchars($d['info']['city']); ?></td>
                    <td class="num"><?php echo balstr($d['opening']); ?></td>
                    <td class="num"><?php echo pf($d['totDr']); ?></td>
                    <td class="num"><?php echo pf($d['totCr']); ?></td>
                    <td class="num"><?php echo balstr($d['closing']); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
            <tfoot>
                <tr>
                    <td colspan="3" style="text-align:right;">Grand Total (<?php echo count($ledgerData); ?> accounts)</td>
                    <td class="num"><?php echo pf($grandTotDr); ?></td>
                    <td class="num"><?php echo pf($grandTotCr); ?></td>
                    <td></td>
                </tr>
            </tfoot>
        </table>
        </div>

        <?php else: ?>
        <!-- ── DETAIL VIEW ──────────────────────────────────────────────── -->
        <?php foreach ($ledgerData as $accode => $d): ?>
        <div class="acc-block">
            <div class="acc-head">
                <span class="acc-name"><?php echo htmlspecialchars($d['info']['name']); ?><span class="acc-city"><?php echo htmlspecialchars($d['info']['city']); ?></span></span>
                <span style="font-size:11px;color:#64748b;"><?php echo htmlspecialchars($d['info']['currency']); ?></span>
            </div>
            <div class="reg-wrap">
            <table class="reg">
                <thead>
                    <tr>
                        <th>Date</th>
                        <th>Voucher No</th>
                        <th>Type</th>
                        <?php if ($prinnarr == '1'): ?><th>Narration</th><?php endif; ?>
                        <th class="num">Debit</th>
                        <th class="num">Credit</th>
                        <th class="num">Balance</th>
                    </tr>
                </thead>
                <tbody>
                    <tr class="openrow">
                        <td colspan="<?php echo $prinnarr=='1' ? 3 : 2; ?>" style="text-align:right;">Opening Balance</td>
                        <?php if ($prinnarr == '1'): ?><td></td><?php endif; ?>
                        <td></td><td></td>
                        <td class="num"><?php echo balstr($d['opening']); ?></td>
                    </tr>
                    <?php if (count($d['rows']) == 0): ?>
                    <tr><td colspan="<?php echo $prinnarr=='1' ? 7 : 6; ?>" style="text-align:center;padding:18px;color:#94a3b8;">No transactions found.</td></tr>
                    <?php else: foreach ($d['rows'] as $r):
                        $src = $sourceMap[$r['source']] ?? ['label' => $r['source'], 'file' => ''];
                    ?>
                    <tr class="<?php echo $src['file'] ? 'clickable' : ''; ?>"
                        <?php if ($src['file']): ?>onclick="window.open('<?php echo $src['file']; ?>?mode=edit&vouchno=<?php echo $r['vouchno']; ?>','_blank')"<?php endif; ?>>
                        <td><?php echo $r['vouchdt'] != '0000-00-00' ? date('d-m-Y', strtotime($r['vouchdt'])) : ''; ?></td>
                        <td style="font-weight:600;color:#2563eb;"><?php echo $r['vouchno']; ?></td>
                        <td><?php echo htmlspecialchars($src['label']); ?></td>
                        <?php if ($prinnarr == '1'): ?><td><?php echo htmlspecialchars($r['narr']); ?></td><?php endif; ?>
                        <td class="num"><?php echo pf($r['dr']); ?></td>
                        <td class="num"><?php echo pf($r['cr']); ?></td>
                        <td class="num"><?php echo balstr($r['balance']); ?></td>
                    </tr>
                    <?php endforeach; endif; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <td colspan="<?php echo $prinnarr=='1' ? 3 : 2; ?>" style="text-align:right;">Total (<?php echo count($d['rows']); ?> entries)</td>
                        <?php if ($prinnarr == '1'): ?><td></td><?php endif; ?>
                        <td class="num"><?php echo pf($d['totDr']); ?></td>
                        <td class="num"><?php echo pf($d['totCr']); ?></td>
                        <td></td>
                    </tr>
                    <tr class="closerow">
                        <td colspan="<?php echo $prinnarr=='1' ? 6 : 5; ?>" style="text-align:right;">Closing Balance</td>
                        <td class="num"><?php echo balstr($d['closing']); ?></td>
                    </tr>
                </tfoot>
            </table>
            </div>
        </div>
        <?php endforeach; ?>

        <div class="grand-total">
            <span><span class="lbl">Total Debit</span><?php echo pf($grandTotDr); ?></span>
            <span><span class="lbl">Total Credit</span><?php echo pf($grandTotCr); ?></span>
        </div>
        <?php endif; ?>

        <?php endif; ?>

    </div>
</div>

<?php include("includes/linksfoot.php"); ?>

<div style="position:fixed;bottom:0;left:0;right:0;background:#fff;border-top:1px solid #e2e8f0;padding:10px 16px;display:flex;gap:10px;justify-content:center;z-index:999;box-shadow:0 -2px 12px rgba(0,0,0,.08);">
    <button onclick="window.print()" style="padding:8px 22px;background:#1e293b;color:#fff;border:none;border-radius:8px;font-size:13px;font-weight:600;cursor:pointer;display:flex;align-items:center;gap:7px;"><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;display:flex;align-items:center;gap:7px;"><i class="fa fa-arrow-left"></i> Go Back</button>
</div>
</body>
</html>
