<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Reporting extends CI_Controller {

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    public function isijam()
    {
        set_time_limit(0);

        // Hapus data reporting hari ini
        $this->db->query("
            DELETE FROM reporting 
            WHERE tgl = CURDATE()
        ");

        // Insert penjualan mie
        $this->db->query("
            INSERT IGNORE INTO reporting
            (tgl,kdcabang,jam,jenis,qty,qtydi,qtyta,qtypr,qtyky,qtybo,qtymewah,qtyojol)
            SELECT 
                CURDATE(),
                b.kdcabang,
                CURTIME(),
                '1',
                SUM(a.jumlah) AS ttqty,
                SUM(IF(a.takeaway='1', a.jumlah, 0)) AS diqty,
                SUM(IF(a.takeaway<>'1', IF(a.qtyta>0,a.qtyta,a.jumlah),0)) AS taqty,
                SUM(IF(a.jpromo='P',(a.jumlah-a.qty),0)) AS pmqty,
                SUM(IF(a.kdpromo='048',(a.jumlah-a.qty),0)) AS kyqty,
                SUM(IF(a.jpromo='B',(a.jumlah-a.qty),0)) AS boqty,
                SUM(IF((a.jnsbarang='J10' OR a.jnsbarang='J11'),(a.jumlah-a.qty),0)) AS mewahqty,
                SUM(IF((b.kdharga<>'KH01' AND b.kdharga<>'KH06'),a.jumlah,0)) AS ojolqty
            FROM tabel_rinci_penjualan a
            LEFT JOIN tabel_penjualan b 
                ON a.no_faktur_penjualan=b.no_faktur_penjualan
            WHERE b.tgl_penjualan=CURDATE()
            AND a.idmenu='4'
            AND b.selesai='1'
            AND a.jnsbarang<>'J04'
            AND a.jnsbarang<>'J06'
            AND a.jnsbarang<>'J12'
            GROUP BY b.kdcabang
        ");

        // Insert Juice dan Mentai
        $this->db->query("
            INSERT IGNORE INTO reporting
            (tgl,kdcabang,jam,jenis,juice,mentai)
            SELECT 
                CURDATE(),
                b.kdcabang,
                CURTIME(),
                '3',
                SUM(IF((c.jenisbrg='J04' AND c.nolap='E41'),(a.jumlah-a.qty),0)) AS juice,
                SUM(IF((c.jenismenu='J21'),(a.jumlah-a.qty),0)) AS mentai
            FROM tabel_rinci_penjualan a
            LEFT JOIN tabel_penjualan b
                ON a.no_faktur_penjualan=b.no_faktur_penjualan
            LEFT JOIN tabel_menu c
                ON a.kd_barang=c.kode_menu
            WHERE b.tgl_penjualan=CURDATE()
            AND a.jnsbarang<>'J12'
            AND b.selesai='1'
            GROUP BY b.kdcabang
        ");

        // Update paket
        $this->db->query("
            UPDATE reporting a
            SET a.paket=
            (
                IFNULL(
                    (
                        SELECT SUM(b.qty)
                        FROM tabel_rinci_promo b
                        JOIN tabel_penjualan c
                            ON b.nobukti=c.no_faktur_penjualan
                        WHERE b.kdbrg1 LIKE '9a%'
                        AND c.tgl_penjualan=CURDATE()
                        AND c.selesai='1'
                        AND c.kdcabang=a.kdcabang
                    ),0
                )
            )
            WHERE a.tgl=CURDATE()
            AND a.jenis='1'
        ");

        // Insert stok mie
        $this->db->query("
            INSERT IGNORE INTO reporting
            (tgl,kdcabang,jam,jenis,qty)
            SELECT
                CURDATE(),
                kd_toko,
                CURTIME(),
                '2',
                stok
            FROM tabel_stok_toko_new
            WHERE kd_barang='2a10'
        ");

        // Update jumlah delivery order
        $this->db->query("
            UPDATE reporting a
            SET a.jdo=
            IFNULL(
                (
                    SELECT COUNT(*)
                    FROM tabel_penjualan
                    WHERE tgl_penjualan=CURDATE()
                    AND layanan='3'
                    AND kdcabang=a.kdcabang
                ),0
            )
            WHERE a.tgl=CURDATE()
            AND a.jenis='1'
        ");

        echo 'Proses reporting selesai : '.date('Y-m-d H:i:s');
    }
function isiwa() {
    $this->db->query("
    insert ignore into reportwa(tgl,jam,stat)
    select curdate(),curtime(),'0';");
    echo 'Proses reporting selesai : '.date('Y-m-d H:i:s');
}    
}