Modul 6 - Laporan & Ringkasan

1. Halaman Laporan: laporan/index.php

📄 laporan/index.php

<?php
// ============================================
// FILE: laporan/index.php
// Laporan penjualan dengan filter tanggal
// ============================================

$pageTitle = 'Laporan Penjualan';
require_once '../config/database.php';
require_once '../includes/functions.php';
require_once '../includes/header.php';

// Filter tanggal (default: hari ini)
$tgl_dari  = $_GET['dari']  ?? date('Y-m-01'); // Awal bulan ini
$tgl_sampai = $_GET['sampai'] ?? date('Y-m-d'); // Hari ini

// ============================================
// QUERY 1: Ringkasan periode
// ============================================
$stmtRingkasan = $pdo->prepare(
    "SELECT 
        COUNT(*)           AS total_transaksi,
        SUM(total)         AS total_omzet,
        AVG(total)         AS rata_rata,
        MAX(total)         AS transaksi_terbesar
     FROM transaksi
     WHERE DATE(tanggal) BETWEEN :dari AND :sampai"
);
$stmtRingkasan->execute([':dari' => $tgl_dari, ':sampai' => $tgl_sampai]);
$ringkasan = $stmtRingkasan->fetch();

// ============================================
// QUERY 2: Produk terlaris
// ============================================
$stmtTerlaris = $pdo->prepare(
    "SELECT 
        dt.nama_produk,
        SUM(dt.qty)      AS total_qty,
        SUM(dt.subtotal) AS total_penjualan
     FROM detail_transaksi dt
     JOIN transaksi t ON dt.id_transaksi = t.id
     WHERE DATE(t.tanggal) BETWEEN :dari AND :sampai
     GROUP BY dt.id_produk, dt.nama_produk
     ORDER BY total_qty DESC
     LIMIT 5"
);
$stmtTerlaris->execute([':dari' => $tgl_dari, ':sampai' => $tgl_sampai]);
$produkTerlaris = $stmtTerlaris->fetchAll();

// ============================================
// QUERY 3: Riwayat transaksi
// ============================================
$stmtRiwayat = $pdo->prepare(
    "SELECT t.*, u.nama as kasir,
            (SELECT COUNT(*) FROM detail_transaksi 
             WHERE id_transaksi = t.id) AS jumlah_item
     FROM transaksi t 
     LEFT JOIN users u ON t.id_user = u.id
     WHERE DATE(t.tanggal) BETWEEN :dari AND :sampai
     ORDER BY t.tanggal DESC"
);
$stmtRiwayat->execute([':dari' => $tgl_dari, ':sampai' => $tgl_sampai]);
$riwayat = $stmtRiwayat->fetchAll();
?>

<div class="d-flex justify-content-between align-items-center mb-4">
    <h4 class="fw-bold mb-0">📊 Laporan Penjualan</h4>
</div>

<!-- Form Filter Tanggal -->
<div class="card border-0 shadow-sm mb-4">
    <div class="card-body">
        <form method="GET" class="row g-3 align-items-end">
            <div class="col-md-4">
                <label class="form-label fw-semibold small">Dari Tanggal</label>
                <input type="date" name="dari" class="form-control"
                       value="<?= $tgl_dari ?>">
            </div>
            <div class="col-md-4">
                <label class="form-label fw-semibold small">Sampai Tanggal</label>
                <input type="date" name="sampai" class="form-control"
                       value="<?= $tgl_sampai ?>">
            </div>
            <div class="col-md-4">
                <button type="submit" class="btn btn-primary w-100">
                    <i class="fas fa-filter me-2"></i>Filter
                </button>
            </div>
        </form>
    </div>
</div>

<!-- Kartu Ringkasan -->
<div class="row g-3 mb-4">
    <div class="col-md-3">
        <div class="card border-0 shadow-sm text-center p-3">
            <div class="text-muted small">Total Transaksi</div>
            <div class="h3 fw-bold text-primary">
                <?= $ringkasan['total_transaksi'] ?>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card border-0 shadow-sm text-center p-3">
            <div class="text-muted small">Total Omzet</div>
            <div class="h5 fw-bold text-success">
                <?= formatRupiah($ringkasan['total_omzet'] ?? 0) ?>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card border-0 shadow-sm text-center p-3">
            <div class="text-muted small">Rata-rata/Transaksi</div>
            <div class="h5 fw-bold text-info">
                <?= formatRupiah($ringkasan['rata_rata'] ?? 0) ?>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card border-0 shadow-sm text-center p-3">
            <div class="text-muted small">Transaksi Terbesar</div>
            <div class="h5 fw-bold text-warning">
                <?= formatRupiah($ringkasan['transaksi_terbesar'] ?? 0) ?>
            </div>
        </div>
    </div>
</div>

<!-- 2 Kolom: Terlaris & Riwayat -->
<div class="row g-4">
    
    <!-- Produk Terlaris -->
    <div class="col-md-5">
        <div class="card border-0 shadow-sm">
            <div class="card-header fw-bold">🏆 Top 5 Produk Terlaris</div>
            <div class="card-body p-0">
                <table class="table table-sm mb-0">
                    <thead class="table-light">
                        <tr><th>Produk</th><th class="text-center">Qty</th>
                            <th class="text-end">Total</th></tr>
                    </thead>
                    <tbody>
                    <?php if (empty($produkTerlaris)): ?>
                        <tr><td colspan="3" class="text-center text-muted py-3">
                            Belum ada data</td></tr>
                    <?php else: ?>
                        <?php foreach ($produkTerlaris as $i => $p): ?>
                        <tr>
                            <td>
                                <span class="badge bg-warning text-dark me-1">
                                    #<?= $i+1 ?>
                                </span>
                                <?= htmlspecialchars($p['nama_produk']) ?>
                            </td>
                            <td class="text-center"><?= $p['total_qty'] ?></td>
                            <td class="text-end small">
                                <?= formatRupiah($p['total_penjualan']) ?>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    
    <!-- Riwayat Transaksi -->
    <div class="col-md-7">
        <div class="card border-0 shadow-sm">
            <div class="card-header fw-bold">📋 Riwayat Transaksi</div>
            <div class="card-body p-0">
                <div class="table-responsive" style="max-height:400px;overflow-y:auto">
                    <table class="table table-sm mb-0">
                        <thead class="table-light sticky-top">
                            <tr><th>No. Transaksi</th><th>Waktu</th>
                                <th class="text-end">Total</th><th>Aksi</th></tr>
                        </thead>
                        <tbody>
                        <?php if (empty($riwayat)): ?>
                            <tr><td colspan="4" class="text-center text-muted py-3">
                                Belum ada transaksi di periode ini</td></tr>
                        <?php else: ?>
                            <?php foreach ($riwayat as $r): ?>
                            <tr>
                                <td><code class="small"><?= $r['no_transaksi'] ?></code></td>
                                <td class="small">
                                    <?= date('d/m H:i', strtotime($r['tanggal'])) ?>
                                </td>
                                <td class="text-end fw-bold small">
                                    <?= formatRupiah($r['total']) ?>
                                </td>
                                <td>
                                    <a href="../transaksi/detail.php?id=<?= $r['id'] ?>"
                                       class="btn btn-sm btn-outline-primary">
                                        <i class="fas fa-eye"></i>
                                    </a>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<?php require_once '../includes/footer.php'; ?>

2. Penjelasan Query SQL Lanjutan

-- LEFT JOIN: ambil semua produk,
-- bahkan yang tidak punya kategori
SELECT p.nama_produk, k.nama_kategori
FROM produk p
LEFT JOIN kategori k ON p.id_kategori = k.id

-- Hasilnya:
-- Indomie Goreng | Makanan
-- Barang Baru    | NULL  ← produk tanpa kategori tetap muncul

-- GROUP BY: kelompokkan data berdasarkan produk
-- SUM(): jumlahkan dalam kelompok tersebut
SELECT 
    nama_produk,
    SUM(qty)      AS total_terjual,
    SUM(subtotal) AS total_pendapatan
FROM detail_transaksi
GROUP BY id_produk, nama_produk
ORDER BY total_terjual DESC

-- BETWEEN: filter data antara 2 nilai
SELECT * FROM transaksi
WHERE DATE(tanggal) BETWEEN '2025-01-01' AND '2025-01-31'

-- DATE(): ambil bagian tanggal saja (tanpa waktu)
-- Karena kolom tanggal bertipe DATETIME (mengandung jam:menit:detik)

-- Ekuivalen dengan:
WHERE tanggal >= '2025-01-01 00:00:00' 
  AND tanggal <= '2025-01-31 23:59:59'

3. Ringkasan Materi: Apa yang Sudah Dipelajari

🎉 Selamat! Kamu sudah belajar:

📦 Database & SQL

Membuat database & tabel MySQL
Primary Key & Foreign Key
INSERT, SELECT, UPDATE, DELETE
JOIN tabel, GROUP BY, Aggregate
Filter dengan WHERE & BETWEEN

🔌 PDO PHP

Koneksi PDO ke MySQL
Prepared Statements (aman dari SQL Injection)
fetch(), fetchAll(), fetchColumn()
lastInsertId()
PDO Transaction (begin/commit/rollback)

🔐 Login & Session

Session PHP ($_SESSION)
password_hash() & password_verify()
Proteksi halaman (cek login)
Logout yang benar
Keamanan dasar (XSS, SQL Injection)

🎨 Bootstrap 5 + PHP

Navbar responsif
Form dengan validasi
Tabel data + alert
Kartu statistik dashboard
Sistem POS (keranjang, nota)

4. Langkah Selanjutnya

🚀 Tingkatkan Aplikasi POS Ini!

📸 Upload Foto Produk

Tambahkan kolom gambar dan fitur upload foto ke produk

📄 Export Excel/PDF

Export laporan ke Excel dengan library PhpSpreadsheet

👥 Multi User

Manajemen user & hak akses (admin vs kasir)

📊 Grafik Chart

Tambahkan grafik penjualan dengan Chart.js

🔔 Notifikasi Stok

Alert otomatis saat stok produk menipis

🌐 Pelajari Laravel

Framework PHP populer dengan Eloquent ORM

📚 Referensi Belajar Lanjutan

📊 Modul 6: Laporan & Ringkasan

🔹 JOIN — Menggabungkan Data dari Beberapa Tabel