View Code - 20260722

expense_by_department.php | 20260722_120325.php Initial Domain Snapshot

<?php
require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('expense_by_department');

$pdo = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];

require_once __DIR__ . '/vendor/autoload.php';
require_once __DIR__ . '/partials/header.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

/* ========================
   Date Filter
======================== */
$from = $_GET['from_date'] ?? '';
$to   = $_GET['to_date'] ?? '';

/* ========================
   Account → Department Map
======================== */
$map = [];

$stmt = $pdo->prepare("
    SELECT account_no, department_id
    FROM account_department_map
    WHERE company_id = :cid AND is_active = 1
");
$stmt->execute([':cid' => $company_id]);

while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $acc = preg_replace('/\D/', '', (string)$r['account_no']);
    if ($acc !== '') {
        $map[$acc] = (int)$r['department_id'];
    }
}

/* ========================
   Department Names
======================== */
$deptNames = [];

$stmt = $pdo->prepare("
    SELECT id, name 
    FROM company_departments 
    WHERE company_id = :cid
");
$stmt->execute([':cid' => $company_id]);

while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $deptNames[(int)$r['id']] = $r['name'];
}

/* ========================
   Excel Read
======================== */
$folder = __DIR__ . '/excel';
$files = glob($folder . '/*.xlsx');

$fileSummary = [];   // file + dept rows
$deptSummary = [];   // overall dept total
$seen = [];

foreach ($files as $file) {

$fileDate = '';
    $fileDeptBreakup = []; // 👉 key part

try {
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $data = $sheet->toArray();

for ($i = 1; $i < count($data); $i++) {

$r = $data[$i];

$accountRaw = (string)($r[1] ?: $r[0]);
            $account = preg_replace('/\D/', '', $accountRaw);

if (!$account) continue;
            if (!isset($map[$account])) continue;

$deptId = $map[$account];

$amount = (float)$r[3];
            if ($amount <= 0) continue;

$dateRaw = $r[5];
            if (!$dateRaw) continue;

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

if ($from && $date < $from) continue;
            if ($to && $date > $to) continue;

// duplicate logic
            $month  = date('Y-m', strtotime($date));
            $day    = (int)date('d', strtotime($date));
            $period = ($day <= 15) ? 'P1' : 'P2';

$key = $account . '_' . $amount . '_' . $month . '_' . $period;
            if (isset($seen[$key])) continue;
            $seen[$key] = true;

// file date
            if (!$fileDate) {
                $fileDate = date('d-M-Y', strtotime($date));
            }

// ========================
            // FILE → DEPARTMENT TOTAL
            // ========================
            if (!isset($fileDeptBreakup[$deptId])) {
                $fileDeptBreakup[$deptId] = 0;
            }
            $fileDeptBreakup[$deptId] += $amount;

// ========================
            // OVERALL DEPARTMENT TOTAL
            // ========================
            if (!isset($deptSummary[$deptId])) {
                $deptSummary[$deptId] = 0;
            }
            $deptSummary[$deptId] += $amount;
        }

// ========================
        // SAVE FILE → MULTI DEPT
        // ========================
        foreach ($fileDeptBreakup as $deptId => $deptTotal) {

$fileSummary[] = [
                'file' => basename($file),
                'date' => $fileDate,
                'department' => $deptNames[$deptId] ?? ('Dept '.$deptId),
                'total' => $deptTotal
            ];
        }

} catch (Exception $e) {
        continue;
    }
}
?>

<div class="card">
    <h3>Expense Summary</h3>

<div style="align-self:end;">
            <button class="btn">Filter</button>
            <a href="expense_by_department.php" class="btn">Reset</a>
        </div>
    </form>
</div>

<div class="card">
    <h4>File-wise Department Summary</h4>

<table class="table">
        <thead>
            <tr>
                <th>File</th>
                <th>Date</th>
                <th>Department</th>
                <th>Total</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($fileSummary as $f): ?>
                <tr>
                    <td><?= htmlspecialchars($f['file']) ?></td>
                    <td><?= htmlspecialchars($f['date']) ?></td>
                    <td><?= htmlspecialchars($f['department']) ?></td>
                    <td><?= number_format($f['total'], 2) ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<div class="card">
    <h4>Department-wise Total</h4>

<table class="table">
        <thead>
            <tr>
                <th>Department</th>
                <th>Total</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($deptSummary as $deptId => $total): ?>
                <tr>
                    <td><?= htmlspecialchars($deptNames[$deptId] ?? ('Dept '.$deptId)) ?></td>
                    <td><?= number_format($total, 2) ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<?php require_once __DIR__ . '/partials/footer.php'; ?>