FileMgr

✏️ /home/mien4214/public_html/eorder/application/models/m_dashboard.php

✕ Tutup

<?php
class M_dashboard extends CI_Model
{
    function get_cabang_performance()
    {
        $query = $this->db->query("
            SELECT 
                t.kd_toko, 
                t.nm_toko,
                
                -- Omset HARI INI
                COALESCE(SUM(CASE 
                    WHEN DATE(p.tgl_penjualan) = CURDATE() THEN p.netto 
                    ELSE 0 
                END), 0) as omset_harian,

-- Omset KEMARIN
                COALESCE(SUM(CASE 
                    WHEN DATE(p.tgl_penjualan) = SUBDATE(CURDATE(), 1) THEN p.netto 
                    ELSE 0 
                END), 0) as omset_kemarin,
                
                -- Omset BULAN INI
                COALESCE(SUM(CASE 
                    WHEN DATE_FORMAT(p.tgl_penjualan, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m') THEN p.netto 
                    ELSE 0 
                END), 0) as omset_bulan_ini,
                
                -- Omset BULAN LALU
                COALESCE(SUM(CASE 
                    WHEN DATE_FORMAT(p.tgl_penjualan, '%Y-%m') = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m') THEN p.netto 
                    ELSE 0 
                END), 0) as omset_bulan_lalu,
                
                -- AMBIL TARGET BULAN INI DARI tabel_target
                COALESCE((
                    SELECT MAX(tg.nilai) 
                    FROM tabel_target tg 
                    WHERE tg.kdcabang = t.kd_toko 
                      AND tg.bulan = MONTH(CURDATE()) 
                      AND tg.tahun = YEAR(CURDATE())
                      AND tg.jenis = '1' -- Sesuai data dump yang jenisnya '1'
                ), 0) as target_nilai

FROM tabel_toko t
            LEFT JOIN tabel_penjualan p ON t.kd_toko = p.kdcabang 
                AND p.stat != '4' -- Abaikan yang batal
            
            WHERE t.kd_toko NOT IN ('00000', 'YOG00', 'YOG09') AND t.stat = 2
            
            GROUP BY t.kd_toko, t.nm_toko
            ORDER BY omset_bulan_ini DESC
        ");

return $query->result_array();
    }
    
   function get_today_orders_all_branches()
    {
        $query = $this->db->query("
            SELECT 
                p.no_faktur_penjualan, p.waktu, p.netto, 
                p.selesai, 
                p.stat,   
                p.layanan, 
                p.kdcabang, 
                p.npelanggan, 
                p.tglbayar, 
                p.keter,   
                t.nm_toko
            FROM tabel_penjualan p
            JOIN tabel_toko t ON p.kdcabang = t.kd_toko
            WHERE p.tgl_penjualan = CURDATE()
            ORDER BY p.waktu DESC
        ");
        return $query;
    }

// STATISTIK STATUS (PIE CHART)
    function get_today_status_stats()
    {
        $query = $this->db->query("
            SELECT 
                COALESCE(SUM(CASE WHEN selesai = '1' THEN 1 ELSE 0 END), 0) as selesai,
                COALESCE(SUM(CASE WHEN selesai = '3' THEN 1 ELSE 0 END), 0) as pending,
                COALESCE(SUM(CASE WHEN selesai = '0' THEN 1 ELSE 0 END), 0) as baru, 
                COALESCE(SUM(CASE WHEN selesai = '4' THEN 1 ELSE 0 END), 0) as batal
            FROM tabel_penjualan
            WHERE tgl_penjualan = CURDATE()
        ");
        return $query->row(); 
    }
    
    // SUMMARY GLOBAL
    function get_global_monthly_summary()
    {
        $query = $this->db->query("
            SELECT 
                COALESCE(SUM(netto), 0) as omset_bulan_ini,
                COUNT(no_faktur_penjualan) as total_pelanggan_bulan_ini,
                (SELECT COALESCE(SUM(jumlah),0) FROM tabel_rinci_penjualan pd 
                 JOIN tabel_penjualan p2 ON pd.no_faktur_penjualan = p2.no_faktur_penjualan 
                 WHERE DATE_FORMAT(p2.tgl_penjualan, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')) as total_porsi_bulan_ini
            FROM tabel_penjualan
            WHERE DATE_FORMAT(tgl_penjualan, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
            AND stat != '4'
        ");
        return $query->row();
    }
    
    function get_active_branches()
    {
        $this->db->select('kd_toko, nm_toko');
        $this->db->where('stat', 2);
        $this->db->where_not_in('kd_toko', ['00000', 'YOG00', 'YOG09']);
        return $this->db->get('tabel_toko')->result_array();
    }

function get_daily_sales_per_branch($bulan, $tahun)
    {
        // SELECT menggunakan FALSE agar tidak di-escape
        $this->db->select("
            DATE_FORMAT(p.tgl_penjualan, '%e') as tgl, 
            t.nm_toko, 
            SUM(p.netto) as total
        ", FALSE); 
        
        $this->db->from('tabel_penjualan p');
        $this->db->join('tabel_toko t', 'p.kdcabang = t.kd_toko');
        $this->db->where('MONTH(p.tgl_penjualan)', $bulan);
        $this->db->where('YEAR(p.tgl_penjualan)', $tahun);
        $this->db->where('p.stat !=', '4'); 
        
        $this->db->where_not_in('t.kd_toko', ['00000', 'YOG00', 'YOG09']); 
        
        $this->db->group_by('tgl'); 
        $this->db->group_by('t.nm_toko');
        
        return $this->db->get()->result_array();
    }
    
    // [UPDATE] Mengambil statistik Porsi dan Breakdown per Jenis Layanan (Grab, Shopee, Reguler, dll)
    function get_detailed_stats_comparison()
    {
        $q_porsi = $this->db->query("
            SELECT 
                COALESCE(SUM(CASE WHEN p.tgl_penjualan = CURDATE() THEN rp.jumlah ELSE 0 END), 0) as today,
                COALESCE(SUM(CASE WHEN p.tgl_penjualan = SUBDATE(CURDATE(), 1) THEN rp.jumlah ELSE 0 END), 0) as yesterday
            FROM tabel_penjualan p
            JOIN tabel_rinci_penjualan rp ON p.no_faktur_penjualan = rp.no_faktur_penjualan
            WHERE p.stat != '4'
            AND p.tgl_penjualan >= SUBDATE(CURDATE(), 1) -- INI KUNCINYA
        ")->row_array();

$q_types = $this->db->query("
            SELECT 
                keter,
                COUNT(CASE WHEN date(tgl_penjualan) = CURDATE() THEN 1 END) as today,
                COUNT(CASE WHEN date(tgl_penjualan) = SUBDATE(CURDATE(), 1) THEN 1 END) as yesterday
            FROM tabel_penjualan
            WHERE stat != '4' AND keter != ''
            GROUP BY keter
        ")->result_array();

$q_total = $this->db->query("
            SELECT 
                COUNT(CASE WHEN date(tgl_penjualan) = CURDATE() THEN 1 END) as today,
                COUNT(CASE WHEN date(tgl_penjualan) = SUBDATE(CURDATE(), 1) THEN 1 END) as yesterday
            FROM tabel_penjualan
            WHERE stat != '4'
        ")->row_array();

return [
            'porsi' => $q_porsi, 
            'types' => $q_types, 
            'total' => $q_total
        ];
    }
    
    // [BARU] Top 5 Menu Mie Terlaris Hari Ini
    function get_top_selling_mie()
    {
        $this->db->select('rp.nm_barang, SUM(rp.jumlah) as qty');
        $this->db->from('tabel_rinci_penjualan rp');
        $this->db->join('tabel_penjualan p', 'rp.no_faktur_penjualan = p.no_faktur_penjualan');
        $this->db->join('jenismenu jm', 'rp.jnsbarang = jm.kdjenis');
        
        $this->db->where('p.tgl_penjualan', date('Y-m-d')); 
        $this->db->where('p.stat !=', '4'); 
        
        $this->db->like('jm.keterangan', 'Mie'); 
        
        $this->db->group_by('rp.nm_barang');
        $this->db->order_by('qty', 'DESC');
        $this->db->limit(5); 
        
        return $this->db->get()->result_array();
    }
    
    // [BARU] Ambil Detail Transaksi untuk Modal
    function get_order_detail($no_faktur)
    {
        // 1. Ambil Header (Data Pelanggan & Transaksi)
        $this->db->select('p.*, t.nm_toko');
        $this->db->from('tabel_penjualan p');
        $this->db->join('tabel_toko t', 'p.kdcabang = t.kd_toko', 'left');
        $this->db->where('p.no_faktur_penjualan', $no_faktur);
        $header = $this->db->get()->row_array();

// 2. Ambil Item (Rincian Menu)
        $this->db->select('*');
        $this->db->from('tabel_rinci_penjualan');
        $this->db->where('no_faktur_penjualan', $no_faktur);
        $items = $this->db->get()->result_array();

return [
            'header' => $header,
            'items'  => $items
        ];
    }
    // 1. Query Agregat Ringan (Hanya header transaksi hari ini)
    function get_global_daily_summary_light() {
        return $this->db->query("
            SELECT 
                COALESCE(SUM(netto), 0) as total_omset,
                COUNT(no_faktur_penjualan) as total_trx
            FROM tabel_penjualan
            WHERE tgl_penjualan = CURDATE() 
            AND stat != '4'
        ")->row();
    }

// 2. Query Performa Cabang (Lebih ringan tanpa perbandingan tanggal kompleks)
    function get_cabang_performance_light() {
        return $this->db->query("
            SELECT 
                t.nm_toko,
                COALESCE(SUM(p.netto), 0) as omset,
                COUNT(p.no_faktur_penjualan) as trx
            FROM tabel_toko t
            LEFT JOIN tabel_penjualan p ON t.kd_toko = p.kdcabang 
                AND p.tgl_penjualan = CURDATE()
                AND p.stat != '4'
            WHERE t.stat = 2
            AND t.kd_toko NOT IN ('00000', 'YOG00', 'YOG09')
            GROUP BY t.kd_toko, t.nm_toko
            ORDER BY omset DESC
        ")->result_array();
    }

// 3. Query Top Menu (Hanya scan transaksi yang aktif hari ini)
    function get_top_selling_mie_light() {
        return $this->db->query("
            SELECT rp.nm_barang, SUM(rp.jumlah) as qty
            FROM tabel_rinci_penjualan rp
            JOIN tabel_penjualan p ON rp.no_faktur_penjualan = p.no_faktur_penjualan
            WHERE p.tgl_penjualan = CURDATE()
            AND p.stat != '4'
            GROUP BY rp.nm_barang
            ORDER BY qty DESC
            LIMIT 5
        ")->result_array();
    }
}

Batal

⬅ application 14 item

Nama	Tipe	Ukuran	Diubah	Perm	Aksi
🌐 index.html	html	114 B	17/08/24 07:14	0644	✏️ ⬇
🐘 m_dashboard.php	php	9.9 KB	01/05/26 15:25	0644	✏️ ⬇
🐘 m_gallery.php	php	944 B	17/08/24 07:14	0644	✏️ ⬇
🐘 m_kategori.php	php	860 B	19/02/26 09:00	0644	✏️ ⬇
🐘 m_konfirmasi.php	php	2 KB	14/02/26 15:29	0644	✏️ ⬇
🐘 m_login.php	php	740 B	17/08/24 07:14	0644	✏️ ⬇
🐘 m_menu.php	php	66.5 KB	19/02/26 14:46	0644	✏️ ⬇
📄 m_menu.php.bak	bak	4 KB	17/08/24 07:14	0644	✏️ ⬇
🐘 m_menu1.php	php	44 KB	17/08/24 07:14	0644	✏️ ⬇
🐘 m_order.php	php	23.7 KB	07/02/26 08:32	0644	✏️ ⬇
🐘 m_pelanggan.php	php	5 KB	17/02/26 15:00	0644	✏️ ⬇
🐘 m_pengguna.php	php	2.9 KB	17/08/24 07:14	0644	✏️ ⬇
🐘 m_rekening.php	php	1.4 KB	17/08/24 07:14	0644	✏️ ⬇
🐘 m_status.php	php	723 B	17/08/24 07:14	0644	✏️ ⬇

✏️ Rename

🔑 Ubah Permission