<?php

class Model_data extends CI_model
{
    public function __construct()
    {
            // Set table name
        $this->table = 'tb_users';
        $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
    }
    
    public function getDetailLaporan($array)
    {
        $this->db->select(' 
           `invoice`.`id_transaksi`,
           `invoice`.`tgl_trx`,
           `bayar_invoice_detail`.`tgl_bayar`,
           `jenis_bayar`.`nama_bayar`,
           `konsumen`.`nama`');
        $this->db->select_sum('`bayar_invoice_detail`.`jml_bayar`');
        $this->db->from('invoice');
        $this->db->join(
            'bayar_invoice_detail',
            '`invoice`.`id_invoice` = `bayar_invoice_detail`.`id_invoice`');
        $this->db->join(
            'jenis_bayar',
            '`bayar_invoice_detail`.`id_bayar` = `jenis_bayar`.`id`');
        $this->db->join(
            '`konsumen`',
            '`invoice`.`id_konsumen` = `konsumen`.`id`');
        $this->db->where_in('`invoice`.`id_invoice`', $array);
        $group = ['`invoice`.`id_transaksi`', '`invoice`.`tgl_trx`',
        '`bayar_invoice_detail`.`tgl_bayar`',
        '`jenis_bayar`.`nama_bayar`',
        '`konsumen`.`nama`',
    ];
    $this->db->group_by($group);
    $query = $this->db->get();
    $result = $query->num_rows() > 0 ? $query->result_array() : false;
    
    return $result;
}

public function getHarga($params = [])
{
    $type_harga = $params['type_harga'];
    $id_bahan = $params['id_bahan'];
    $id_member = $params['id_member'];
    $jumlah = $params['jumlah'];

    if ((int) $type_harga === 6) {
        $id_satuan = isset($params['id_satuan']) ? $params['id_satuan'] : 0;
        $type_harga = resolve_semua_metode_harga($id_bahan, $id_member, $id_satuan, $jumlah);
    }
    
    if ($type_harga == 1) 
    {
        $sql1 = $this->db->query("SELECT 
            `satuan`.`id` AS idsatuan,
            `satuan`.`satuan`,
            `satu_harga`.`harga_jual`,
            `bahan`.`title`
            FROM
            `bahan`
            INNER JOIN `satu_harga` ON (`bahan`.`id` = `satu_harga`.`id_bahan`)
            INNER JOIN `satuan` ON (`satu_harga`.`id_satuan` = `satuan`.`id`)
            WHERE
            `bahan`.`id` = $id_bahan");
        $harga_jual = $sql1->row()->harga_jual;
        $satuan = $sql1->row()->idsatuan;
    } elseif ($type_harga == 2) 
    {
        $default = $this->db->query("select id_satuan,harga_jual from bahan where id=$id_bahan")->row();
        $sql2 = $this->db->query("SELECT 
            `satuan`.`id` AS idsatuan,
            `satuan`.`satuan`,
            `harga_satuan`.`id_satuan`,
            `bahan`.`title`,
            `harga_satuan`.`harga_jual`,
            `harga_satuan`.`id_bahan`
            FROM
            `satuan`
            INNER JOIN `harga_satuan` ON (`satuan`.`id` = `harga_satuan`.`id_satuan`)
            INNER JOIN `bahan` ON (`harga_satuan`.`id_bahan` = `bahan`.`id`)
            WHERE
            `harga_satuan`.`id_bahan` = $id_bahan AND `harga_satuan`.`id_satuan` = {$default->id_satuan}");
        if ($sql2->num_rows() > 0) {
            $harga_jual = $sql2->row()->harga_jual;
            $satuan = $sql2->row()->idsatuan;
        } else {
            $harga_jual = $default->harga_jual;
            $satuan = $default->id_satuan;
        }
    } elseif ($type_harga == 3) 
    {
        $sql3 = $this->db->query("SELECT 
            `harga_member`.`id_bahan`,
            `harga_member`.`id_satuan`,
            `harga_member`.`id_member`,
            `harga_member`.`harga_jual`
            FROM
            `satuan`
            INNER JOIN `harga_member` ON (`satuan`.`id` = `harga_member`.`id_satuan`)
            INNER JOIN `bahan` ON (`harga_member`.`id_bahan` = `bahan`.`id`)
            INNER JOIN `konsumen` ON (`harga_member`.`id_member` = `konsumen`.`jenis_member`)
            WHERE
            `harga_member`.`id_member` = $id_member AND 
            `harga_member`.`id_bahan` = $id_bahan
            GROUP BY
            `harga_member`.`id_bahan`,
            `harga_member`.`id_satuan`,
            `harga_member`.`id_member`,
            `harga_member`.`harga_jual`");
        if ($sql3->num_rows() > 0) {
            $harga_jual = $sql3->row()->harga_jual;
            $satuan = $sql3->row()->id_satuan;
        }else{
            $sql = $this->db->query("select * from bahan where id=$id_bahan");
            $harga_jual = $sql->row()->harga_jual;
            $satuan = $sql->row()->id_satuan;
        }
    } elseif ($type_harga == 4) 
    {
        $sql = $this->db->query("select id_satuan,harga_jual from range_harga where id_bahan=$id_bahan AND $jumlah between jumlah_minimal and jumlah_maksimal");
        $harga_jual = $sql->row()->harga_jual;
        $satuan = $sql->row()->id_satuan;
    } elseif ($type_harga == 5)
    {
        $sql = $this->db->query("select id_satuan,harga_jual from harga_range_member where id_bahan=$id_bahan AND id_member=$id_member AND $jumlah between jumlah_minimal and jumlah_maksimal");
        $harga_jual = $sql->row()->harga_jual;
        $satuan = $sql->row()->id_satuan;
    } else {
        $sql = $this->db->query("select * from bahan where id=$id_bahan");
        $harga_jual = $sql->row()->harga_jual;
        $satuan = $sql->row()->id_satuan;
    }
    $data = ['status' => true, 'id_bahan' => $id_bahan, 'title' => getBahan($id_bahan), 'harga' => $harga_jual, 'satuan' => $satuan];
    
    return $data;
}

public function getBahanAjax($searchTerm = '')
{
            // Fetch users
    $this->db->select('*');
    $this->db->where("title like '%".$searchTerm."%' ");
    $fetched_records = $this->db->get('bahan');
    $users = $fetched_records->result_array();
    
            // Initialize Array with fetched data
    $data = [];
    foreach ($users as $user) {
        $data[] = ['id' => $user['id'], 'text' => $user['title']];
    }
    
    return $data;
}

public function load_cc($ord = 'title')
{
    $sql = query("SELECT * FROM bahan WHERE status_stok ='Y' ORDER BY $ord");
    
    return $sql->result();
}

public function detailBayar($params = [])
{
            // print_r($params);
    $this->db->select(' 
        `bayar_invoice_detail`.`id_invoice`,
        `konsumen`.`nama`,
        `invoice`.`tgl_trx`,
        `invoice`.`id_transaksi`,
        `bayar_invoice_detail`.`tgl_bayar`,
        `bayar_invoice_detail`.`jam_bayar`,
        `bayar_invoice_detail`.`jml_bayar`,
        `jenis_bayar`.`nama_bayar`,
        `bayar_invoice_detail`.`id`,
        `bayar_invoice_detail`.`rekap`,
        `bayar_invoice_detail`.`id_bayar`,
        `bayar_invoice_detail`.`lampiran`,
        `bayar_invoice_detail`.`id_akun`');
    $this->db->from('invoice');
    $this->db->join(
        'bayar_invoice_detail',
        '`invoice`.`id_invoice` = `bayar_invoice_detail`.`id_invoice`',
        'RIGHT outer'
    );
    $this->db->join(
        'jenis_bayar',
        '`bayar_invoice_detail`.`id_bayar` = `jenis_bayar`.`id`',
        'INNER'
    );
    $this->db->join(
        '`konsumen`',
        '`invoice`.`id_konsumen` = `konsumen`.`id`',
        'INNER'
    );
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
        $this->db->where(
            '`bayar_invoice_detail`.`tgl_bayar` BETWEEN "'.
            date('Y-m-d', strtotime($params['search']['dari'])).
            '" and "'.
            date('Y-m-d', strtotime($params['search']['sampai'])).
            '"'
        );
    }
    
    $this->db->order_by('`bayar_invoice_detail`.`id`');
    $query = $this->db->get();
    $result = $query->num_rows() > 0 ? $query->result_array() : false;
    
    return $result;
}

public function getDesain($params = [])
{
            // print_r($params);
            // $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
    
    $this->db->select('
        `tb_users`.`nama_lengkap`,
        `invoice`.`id_invoice`,
        `invoice`.`id_transaksi`,
        `invoice`.`id_user`,
        `invoice`.`id_desain`,
        `invoice`.`tgl_trx`,
        `invoice`.`status`,
        `konsumen`.`nama`,
        `invoice`.`total_bayar`');
    $this->db->from('invoice');
            // $this->db->join('invoice_detail', '`invoice`.`id_invoice` = `invoice_detail`.`id_invoice`','left');
    $this->db->join('tb_users', '`invoice`.`id_user` = `tb_users`.`id_user`');
    $this->db->join('konsumen', '`invoice`.`id_konsumen` = `konsumen`.`id`');
            // $this->db->join('`tb_users` `tb_users1`', '`invoice`.`id_desain` = `tb_users1`.`id_user`');
            // $this->db->join('invoice_detail', '`invoice`.`id_invoice` = `invoice_detail`.`id_invoice`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['user'])) {
            $this->db->where('invoice.id_desain', $params['search']['user']);
                    // print_r($params);
        }
        
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $this->db->where('invoice.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
        }
        if (!empty($params['search']['dari']) and empty($params['search']['sampai'])) {
            $this->db->where('invoice.tgl_trx', $params['search']['dari']);
        }
        if (!empty($params['search']['sortBy'])) {
            $this->db->order_by('`invoice`.`id_invoice`', $params['search']['sortBy']);
        }
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            if (!empty($params['id'])) {
                $this->db->where('invoice.id_invoice', $params['id']);
            }
            $query = $this->db->get();
            $result = $query->row_array();
        } else {
            $this->db->order_by('invoice.id_invoice', 'desc');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function uang_setoran($params = [])
{
    $this->db->select(' 
        `tb_users`.`nama_lengkap` AS nama,
        `laporan_penerimaan`.`id`,
        `laporan_penerimaan`.`id_penerima`,
        `laporan_penerimaan`.`total`,
        `laporan_penerimaan`.`tanggal`,
        `laporan_penerimaan`.`status`');
    
    $this->db->from('tb_users');
    $this->db->join('laporan_penerimaan', '`tb_users`.`id_user` = `laporan_penerimaan`.`id_user`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $this->db->where('laporan_penerimaan.tanggal BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
        }
    }
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('`laporan_penerimaan`.`id`', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $this->db->order_by('laporan_penerimaan.id', 'DESC');
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('laporan_penerimaan.id', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getPengguna($params = [])
{
    $dbprefix = $this->db->dbprefix('tb_users');
    
    $this->db->select('tb_users.*, hak_akses.nama AS nama_level');
    $this->db->from($dbprefix);
    $this->db->join('hak_akses', 'hak_akses.id_level = tb_users.id_level', 'left');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    if (array_key_exists('where_in', $params)) {
        foreach ($params['where_in'] as $key => $val) {
            if (!empty($val) && is_array($val)) {
                $this->db->where_in($key, $val);
            }
        }
    }
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('nama_lengkap', $params['search']['keywords']);
        }
    }
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('`tb_users`.`id_user`', $params['search']['sortBy']);
    }
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            if (!empty($params['id'])) {
                $this->db->where('tb_users.id_user', $params['id']);
            }
            $query = $this->db->get();
            $result = $query->row_array();
        } else {
            $this->db->order_by('tb_users.id_user', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function printPengeluaran($params = [])
{
            // print_r($params);
    $this->db->select('*');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_pengeluaran between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get('pengeluaran');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function printPembelian($params = [])
{
            // print_r($params);
    $this->db->select('*');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_pembelian between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get('pembelian');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function printKasKeluar($params = [])
{
            // print_r($params);
    $this->db->select('*');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tanggal between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get('kas_keluar');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function getLabaRugi($params = [])
{
            // print_r($params);
    $this->db->select_sum('jml_bayar');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_bayar between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get('bayar_invoice_detail');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function getLaporanPergrup($params = [])
{
            // print_r($params);
    $this->db->select("SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `total`,
        SUM(`invoice_detail`.`jumlah`) AS `jumlah`,
        SUM(CASE
          WHEN LOWER(COALESCE(`satuan`.`satuan`, '')) = 'cm' AND `invoice_detail`.`status_hitung` = 2
            THEN `invoice_detail`.`tot_ukuran` / 100
          WHEN LOWER(COALESCE(`satuan`.`satuan`, '')) = 'cm'
            THEN `invoice_detail`.`tot_ukuran` / 10000
          WHEN `invoice_detail`.`status_hitung` = 2 AND COALESCE(`satuan`.`jumlah`, 1) > 1
            THEN `invoice_detail`.`tot_ukuran` / `satuan`.`jumlah`
          WHEN COALESCE(`satuan`.`jumlah`, 1) > 1
            THEN `invoice_detail`.`tot_ukuran` / POW(`satuan`.`jumlah`, 2)
          ELSE `invoice_detail`.`tot_ukuran`
        END) AS `total_ukuran`,
        produk.title", false);
    $this->db->from('produk');
    $this->db->join('invoice_detail', 'produk.id = invoice_detail.id_produk');
    $this->db->join('satuan', 'satuan.id = invoice_detail.id_satuan', 'left');
    $this->db->join('invoice', 'invoice_detail.id_invoice = invoice.id_invoice');
    $this->db->group_by('produk.title');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where_in($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_trx between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get();
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function getLabaRugiBiaya($params = [])
{
    $this->db->select_sum('jml_bayar');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_bayar between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get('bayar_pengeluaran');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function getLaporanPenjualan($bulan, $tahun)
{
    $this->db->select("SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `total`,
        SUM(`invoice_detail`.`jumlah`) AS `jumlah`,
        SUM(CASE
          WHEN LOWER(COALESCE(`satuan`.`satuan`, '')) = 'cm' AND `invoice_detail`.`status_hitung` = 2
            THEN `invoice_detail`.`tot_ukuran` / 100
          WHEN LOWER(COALESCE(`satuan`.`satuan`, '')) = 'cm'
            THEN `invoice_detail`.`tot_ukuran` / 10000
          WHEN `invoice_detail`.`status_hitung` = 2 AND COALESCE(`satuan`.`jumlah`, 1) > 1
            THEN `invoice_detail`.`tot_ukuran` / `satuan`.`jumlah`
          WHEN COALESCE(`satuan`.`jumlah`, 1) > 1
            THEN `invoice_detail`.`tot_ukuran` / POW(`satuan`.`jumlah`, 2)
          ELSE `invoice_detail`.`tot_ukuran`
        END) AS `total_ukuran`,
        produk.title", false);
    $this->db->from('produk');
    $this->db->join('invoice_detail', 'produk.id = invoice_detail.id_produk');
    $this->db->join('satuan', 'satuan.id = invoice_detail.id_satuan', 'left');
    $this->db->join('invoice', 'invoice_detail.id_invoice = invoice.id_invoice');
    $this->db->where("invoice.tgl_trx between '$bulan' AND '$tahun'");
    $this->db->where('invoice.status', 'simpan');
    $this->db->group_by('produk.title');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

public function getLaporanPenjualanJenis($dari, $sampai)
{
    $this->db->select('`jenis_cetakan`.`jenis_cetakan` AS title,
       SUM(`invoice_detail`.`jumlah`) AS `jumlah`,
       SUM((`invoice_detail`.`jumlah` * `invoice_detail`.`harga` * `invoice_detail`.`diskon`/100))  AS diskon,
       SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `total`');
    $this->db->from('jenis_cetakan');
    $this->db->join('invoice_detail', '`jenis_cetakan`.`id_jenis` = `invoice_detail`.`jenis_cetakan`');
    $this->db->join('invoice', '`invoice_detail`.`id_invoice` = `invoice`.`id_invoice`');
    $this->db->where("invoice.tgl_trx between '$dari' AND '$sampai'");
    $this->db->where('invoice.status', 'simpan');
    $this->db->group_by('`jenis_cetakan`.`jenis_cetakan`');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

        // gel laporan sort kategori
public function getLaporanPenjualanGrup($id, $dari, $sampai)
{
    $arr = explode(',', $id);
    $this->db->select('SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `total`,SUM(`invoice_detail`.`jumlah`) AS `jumlah`, produk.title');
    $this->db->from('produk');
    $this->db->join('invoice_detail', 'produk.id = invoice_detail.id_produk');
    $this->db->join('invoice', 'invoice_detail.id_invoice = invoice.id_invoice');
    $this->db->where("invoice.tgl_trx between '$dari' AND '$sampai'");
    $this->db->where('invoice.status', 'simpan');
    if (!empty($id)) {
        $this->db->where_in('`invoice_detail`.`jenis_cetakan`', $arr);
    }
    $this->db->group_by('produk.title');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

public function getSurat($params = [])
{
            // print_r($params);
    $this->db->select('`invoice`.`id_invoice`,
       `invoice`.`id_transaksi`,
       `invoice`.`tgl_trx`,
       `surat_jalan`.`id`,
       `surat_jalan`.`tanggal`,
	   CASE WHEN `konsumen`.`tampil` = 1 AND TRIM(COALESCE(`konsumen`.`perusahaan`, \'\')) <> \'\' THEN `konsumen`.`perusahaan` ELSE `konsumen`.`nama` END AS `nama_pelanggan`,
	   `tb_users`.`nama_lengkap`');
    $this->db->from('invoice');
    $this->db->join('surat_jalan', '`invoice`.`id_invoice` = `surat_jalan`.`id_invoice`');
    $this->db->join('konsumen', '`invoice`.`id_konsumen` = `konsumen`.`id`');
	$this->db->join('tb_users', '`surat_jalan`.`id_user` = `tb_users`.`id_user`', 'left');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
		if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
			$minvalue = $params['search']['dari'];
			$maxvalue = $params['search']['sampai'];
			$this->db->where('surat_jalan.tanggal >=', $minvalue);
			$this->db->where('surat_jalan.tanggal <=', $maxvalue);
		}
		if (!empty($params['search']['keyword'])) {
			$keyword = trim($params['search']['keyword']);
			$this->db->group_start();
			$this->db->like('invoice.id_transaksi', $keyword);
			$this->db->or_like('surat_jalan.id', $keyword);
			$this->db->or_like('konsumen.nama', $keyword);
			$this->db->or_like('konsumen.perusahaan', $keyword);
			$this->db->group_end();
		}
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            if (!empty($params['id'])) {
                $this->db->where('surat_jalan.id', $params['id']);
            }
            $query = $this->db->get();
            $result = $query->row_array();
        } else {
			$sortBy = !empty($params['search']['sortBy']) && in_array(strtoupper($params['search']['sortBy']), array('ASC', 'DESC'), true)
				? strtoupper($params['search']['sortBy'])
				: 'DESC';
			$this->db->order_by('surat_jalan.id', $sortBy);
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getLaporanPenjualanx($params = [])
{
            // print_r($params);
    $this->db->select(' `produk`.`title`,
       `produk`.`id`,
       `invoice_detail`.`id_rincianinvoice`,
       `invoice_detail`.`ukuran`,
       `invoice_detail`.`id_produk`,
       `invoice_detail`.`id_bahan`,
       `invoice_detail`.`harga`,
       `invoice_detail`.`jumlah`');
    $this->db->from('produk');
    $this->db->join('invoice_detail', '`produk`.`id` = `invoice_detail`.`id_produk`');
    $this->db->join('invoice', '`invoice_detail`.`id_invoice` = `invoice`.`id_invoice`');
            // $this->db->group_by(array("`produk`.`title`","`produk`.`id`"));
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where('invoice.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
                    // $this->db->where("invoice.tgl_trx between '$minvalue' AND '$maxvalue'");
        }
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('`invoice`.`tgl_trx`', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getLog($params = [])
{
            // print_r($params);
    $this->db->select('`tb_users`.`nama_lengkap`,
       `kas_masuk`.`pemasukan`,
       `kas_masuk`.`pengeluaran`,
       `kas_masuk`.`catatan`,
       `kas_masuk`.`create_date`,
       id_generate');
    $this->db->from('tb_users');
    $this->db->join('kas_masuk', '`tb_users`.`id_user` = `kas_masuk`.`id_user`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $start_date = $params['search']['dari'];
            $end_date = $params['search']['sampai'];
            if ($params['search']['dari'] == $params['search']['sampai']) {
                $this->db->where("create_date >= DATE('$start_date')");
            } else {
                        // $this->db->where("DATE(create_date) BETWEEN '$start_date' AND '$end_date'");
                $this->db->where('DATE(create_date) >=', $start_date);
                $this->db->where('DATE(create_date) <=', $end_date);
            }
        }
    }
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('kas_masuk.create_date', $params['search']['sortBy']);
    }
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            if (!empty($params['id'])) {
                $this->db->where('id', $params['id']);
            }
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('`kas_masuk`.`create_date`', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getLaporanPerkategori($params = [])
{
    $this->db->select('`jenis_cetakan`.`jenis_cetakan`,
       SUM(`invoice_detail`.`jumlah`) AS `jumlah`,
       SUM((`invoice_detail`.`jumlah` * `invoice_detail`.`harga` * `invoice_detail`.`diskon`/100))  AS diskon,
       SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `total`');
    $this->db->from('jenis_cetakan');
    $this->db->join('invoice_detail', '`jenis_cetakan`.`id_jenis` = `invoice_detail`.`jenis_cetakan`');
    $this->db->join('invoice', '`invoice_detail`.`id_invoice` = `invoice`.`id_invoice`');
    
    $this->db->group_by('`jenis_cetakan`.`jenis_cetakan`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_trx between '$minvalue' AND '$maxvalue'");
        }
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('`invoice`.`tgl_trx`', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getLaporanPerBahan($params = [])
{
            // print_r($params);
    $this->db->select('`bahan`.`title`,
       SUM(`invoice_detail`.`jumlah`) AS `jumlah`,
       SUM(CASE
         WHEN LOWER(COALESCE(`satuan`.`satuan`, \'\')) = \'cm\' AND `invoice_detail`.`status_hitung` = 2
           THEN `invoice_detail`.`tot_ukuran` / 100
         WHEN LOWER(COALESCE(`satuan`.`satuan`, \'\')) = \'cm\'
           THEN `invoice_detail`.`tot_ukuran` / 10000
         WHEN `invoice_detail`.`status_hitung` = 2 AND COALESCE(`satuan`.`jumlah`, 1) > 1
           THEN `invoice_detail`.`tot_ukuran` / `satuan`.`jumlah`
         WHEN COALESCE(`satuan`.`jumlah`, 1) > 1
           THEN `invoice_detail`.`tot_ukuran` / POW(`satuan`.`jumlah`, 2)
         ELSE `invoice_detail`.`tot_ukuran`
       END) AS `total_ukuran`,
       SUM((`invoice_detail`.`jumlah` * `invoice_detail`.`harga` * `invoice_detail`.`diskon`/100))  AS diskon,
       SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `total`', false);
    $this->db->from('bahan');
    $this->db->join('invoice_detail', '`bahan`.`id` = `invoice_detail`.`id_bahan`');
    $this->db->join('satuan', '`satuan`.`id` = `invoice_detail`.`id_satuan`', 'left');
    $this->db->join('invoice', '`invoice_detail`.`id_invoice` = `invoice`.`id_invoice`');
    
    $this->db->group_by('`invoice_detail`.`id_bahan`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_trx between '$minvalue' AND '$maxvalue'");
        }
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('`invoice`.`tgl_trx`', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function arusKas($params = [])
{
    $this->db->select('`kas_kecil`.`id`,
       `kas_kecil`.`no_reff`,
       `kas_kecil`.`jumlah`,
       `kas_kecil`.`create_date`,
       `tb_users`.`nama_lengkap`');
    $this->db->from('tb_users');
    $this->db->join('kas_kecil', '`tb_users`.`id_user` = `kas_kecil`.`id_user`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $this->db->where('create_date BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
        }
        if (!empty($params['search']['dari']) and empty($params['search']['sampai'])) {
            $this->db->where('create_date', $params['search']['dari']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('create_date', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('create_date', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getMutasi($params = [])
{
    $this->db->select("
        km.id_generate AS no_trans,
        km.create_date AS create_date,
        km.catatan AS catatan,
        km.pengeluaran AS jumlah,
        km.id_user AS id_user,
        km.id_jenis AS dari_kas,
        km.id_sub_bayar AS dari_sub,
        km_in.id_jenis AS tujuan_kas,
        km_in.id_sub_bayar AS tujuan_sub,
        u.nama_lengkap AS user_name,
        COALESCE(rb_from.inisial, jk_from.title, 'Kas') AS dari_label,
        COALESCE(rb_to.inisial, jk_to.title, 'Kas') AS tujuan_label
        ", false);

    $this->db->from('kas_masuk km');

    // pasangan pemasukan untuk mutasi (1 transaksi = 1 keluar + 1 masuk, sama2 id_generate)
    $this->db->join(
        'kas_masuk km_in',
        "km_in.id_generate = km.id_generate
        AND km_in.id <> km.id
        AND km_in.pemasukan > 0
        AND km_in.id_bayar = 0",
        'left'
    );

    $this->db->join('tb_users u', 'u.id_user = km.id_user', 'left');

    // label rekening bank (kalau mutasi bank)
    $this->db->join('rekening_bank rb_from', 'rb_from.id = km.id_sub_bayar', 'left');
    $this->db->join('rekening_bank rb_to', 'rb_to.id = km_in.id_sub_bayar', 'left');

    // label kas non-bank (jenis_kas)
    $this->db->join('jenis_kas jk_from', 'jk_from.id_akun = km.id_jenis', 'left');
    $this->db->join('jenis_kas jk_to', 'jk_to.id_akun = km_in.id_jenis', 'left');

    // hanya baris "keluar" untuk mutasi
    $this->db->where('km.pengeluaran >', 0);
    $this->db->where('km.id_bayar', 0);

    // filter tambahan (jika suatu saat dipakai)
    if (array_key_exists('where', $params) && is_array($params['where'])) {
        foreach ($params['where'] as $key => $val) {
            // kalau key tidak pakai alias, default ke km.*
            if (strpos($key, '.') === false) {
                $key = 'km.' . $key;
            }
            $this->db->where($key, $val);
        }
    }

    // filter tanggal
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) && !empty($params['search']['sampai'])) {
            $start_date = $params['search']['dari'];
            $end_date   = $params['search']['sampai'];

            $this->db->where('DATE(km.create_date) >=', $start_date);
            $this->db->where('DATE(km.create_date) <=', $end_date);
        }
    }

    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        return $this->db->count_all_results();
    }

    $this->db->order_by('km.create_date', 'DESC');

    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
        $this->db->limit($params['limit'], $params['start']);
    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
        $this->db->limit($params['limit']);
    }

    $query = $this->db->get();
    return ($query->num_rows() > 0) ? $query->result_array() : false;
}

public function getMutasiTimeline($params = [])
{
    $dari   = isset($params['search']['dari']) ? $params['search']['dari'] : '';
    $sampai = isset($params['search']['sampai']) ? $params['search']['sampai'] : '';

    if ($dari === '' || $sampai === '') return false;

    // 1) MUTASI (keluar -> masuk) : ambil baris pengeluaran saja
    $sqlMutasi = "
        SELECT
            km.id_generate AS no_trans,
            km.create_date AS create_date,
            km.catatan     AS catatan,
            km.pengeluaran AS jumlah,
            u.nama_lengkap AS user_name,
            COALESCE(rb_from.inisial, jk_from.title, 'Kas') AS dari_label,
            COALESCE(rb_to.inisial, jk_to.title, 'Kas')     AS tujuan_label,
            j.bukti        AS bukti
        FROM kas_masuk km
        LEFT JOIN kas_masuk km_in
            ON km_in.id_generate = km.id_generate
            AND km_in.id <> km.id
            AND km_in.pemasukan > 0
            AND km_in.id_bayar = 0
        LEFT JOIN tb_users u ON u.id_user = km.id_user
        LEFT JOIN rekening_bank rb_from ON rb_from.id = km.id_sub_bayar
        LEFT JOIN rekening_bank rb_to   ON rb_to.id   = km_in.id_sub_bayar
        LEFT JOIN jenis_kas jk_from ON jk_from.id_akun = km.id_jenis
        LEFT JOIN jenis_kas jk_to   ON jk_to.id_akun   = km_in.id_jenis
        LEFT JOIN jurnal j ON j.no_trans = km.id_generate
        WHERE km.pengeluaran > 0
          AND km.id_bayar = 0
          AND km.id_parent > 0
          AND DATE(km.create_date) >= ?
          AND DATE(km.create_date) <= ?
    ";

    // 2) TAMBAH MODAL (pemasukan langsung ke kas/bank)
    $sqlModal = "
        SELECT
            km.id_generate AS no_trans,
            km.create_date AS create_date,
            km.catatan     AS catatan,
            km.pemasukan   AS jumlah,
            u.nama_lengkap AS user_name,
            'TAMBAH MODAL' AS dari_label,
            COALESCE(rb_to.inisial, jk_to.title, 'Kas') AS tujuan_label,
            j.bukti        AS bukti
        FROM kas_masuk km
        LEFT JOIN tb_users u ON u.id_user = km.id_user
        LEFT JOIN rekening_bank rb_to ON rb_to.id = km.id_sub_bayar
        LEFT JOIN jenis_kas jk_to ON jk_to.id_akun = km.id_jenis
        LEFT JOIN jurnal j ON j.no_trans = km.id_generate
        WHERE km.pemasukan > 0
          AND km.id_bayar = 1
          AND km.id_parent > 0
          AND km.catatan LIKE 'Tambah Modal:%'
          AND DATE(km.create_date) >= ?
          AND DATE(km.create_date) <= ?
    ";

    $union = "($sqlMutasi) UNION ALL ($sqlModal)";

    // COUNT
    if (isset($params['returnType']) && $params['returnType'] === 'count') {
        $q = $this->db->query(
            "SELECT COUNT(1) AS cnt FROM ($union) x",
            [$dari, $sampai, $dari, $sampai]
        );
        return (int)$q->row()->cnt;
    }

    // DATA (pagination)
    $start = isset($params['start']) ? (int)$params['start'] : 0;
    $limit = isset($params['limit']) ? (int)$params['limit'] : 10;

    $q = $this->db->query(
        "SELECT * FROM ($union) x ORDER BY x.create_date DESC LIMIT ?, ?",
        [$dari, $sampai, $dari, $sampai, $start, $limit]
    );

    return ($q->num_rows() > 0) ? $q->result_array() : false;
}




public function get_supplier($params = [])
{
    $this->db->select('*');
    $this->db->from('supplier');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('nama_perusahaan', $params['search']['keywords']);
            $this->db->or_like('telp', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id_supplier', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('id_supplier', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function produk()
{
    $this->db->select('`jenis_cetakan`.`jenis_cetakan`,
       `produk`.`id` AS idp,
       `produk`.`title` AS nproduk,
       `produk`.`pub`');
    $this->db->from('produk');
    $this->db->join('jenis_cetakan', '`produk`.`id_jenis` = `jenis_cetakan`.`id_jenis`');
    $this->db->where('produk.kunci=0');
    $this->db->order_by('`produk`.`id`', 'DESC');
    
    return $this->db->get()->result_array();
}

public function detail_supplier($where)
{
    $this->db->select('`jenis_cetakan`.`jenis_cetakan`,
       `produk`.`id` AS idp,
       `produk`.`title` AS nproduk,
       `produk`.`pub`');
    $this->db->from('produk');
    $this->db->join('jenis_cetakan', '`produk`.`id_jenis` = `jenis_cetakan`.`id_jenis`');
    $this->db->where('produk.kunci=0');
    $this->db->order_by('`produk`.`id`', 'DESC');
    
    return $this->db->get()->result_array();
}

public function getProduk($params = [])
{
            // print_r($params);
    $this->db->select('`jenis_cetakan`.`jenis_cetakan`,
       `produk`.`id`,
       `produk`.`barcode`,
       `produk`.`title`,
       `produk`.`pub`');
    $this->db->from('produk');
    $this->db->join('jenis_cetakan', '`produk`.`id_jenis` = `jenis_cetakan`.`id_jenis`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('produk.title', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('produk.id', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('produk.id', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getRange($params = [])
{
            // print_r($params);
    $this->db->select(' `bahan`.`title`,
       `range_harga`.`id`,
       `range_harga`.`jumlah_minimal`,
       `range_harga`.`jumlah_maksimal`,
       `range_harga`.`pub`,
       `range_harga`.`harga_jual`');
    $this->db->from('bahan');
    $this->db->join('range_harga', '`bahan`.`id` = `range_harga`.`id_bahan`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('bahan.title', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('range_harga.id', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('range_harga.id', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getJenis($params = [])
{
    $this->db->select('*');
    $this->db->from('akun');
    $this->db->join('jenis_cetakan', '`akun`.`id_akun` = `jenis_cetakan`.`id_akun`', 'right');
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('jenis_cetakan', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id_jenis', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('id_jenis', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getJenisPengeluaran($params = [])
{
    $this->db->select('*');
    $this->db->from('akun');
    $this->db->join('jenis_pengeluaran', '`akun`.`no_reff` = `jenis_pengeluaran`.`id_akun`');
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('title', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id_jenis', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('id_jenis', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getBahan($params = [])
{
    $this->db->select('`bahan`.`title`,
        `bahan`.`id_jenis`,
        `bahan`.`id_produk`,
        `bahan`.`harga`,
        `bahan`.`harga_modal`,
        `bahan`.`harga_jual`, 
        `bahan`.`id_satuan`,
        `bahan`.`status_stok`,
        `bahan`.`type_harga`,
        `bahan`.`barcode`,
        `bahan`.`komisi_desain_aktif`,
        `bahan`.`parent_bahan_id`,
        `bahan`.`varian_aktif`,
        `bahan`.`varian_warna`,
        `bahan`.`varian_ukuran`,
        `satuan`.`satuan`,
        `bahan`.`pub`,
        `bahan`.`featured`,
        `bahan`.`id`');

    if ($this->db->field_exists('foto', 'bahan')) {
        $this->db->select('`bahan`.`foto`');
    } else {
        $this->db->select("'' AS `foto`", false);
    }

    $this->db->from('satuan');
    $this->db->join('bahan', '`satuan`.`id` = `bahan`.`id_satuan`', 'right');

    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }

    if ($this->db->field_exists('parent_bahan_id', 'bahan')) {
        $this->db->group_start();
        $this->db->where('bahan.parent_bahan_id', 0);
        $this->db->or_where('bahan.pub', 1);
        $this->db->group_end();
    }

    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->group_start();
            $this->db->like('bahan.title', $params['search']['keywords']);
            $this->db->or_like('bahan.varian_warna', $params['search']['keywords']);
            $this->db->or_like('bahan.varian_ukuran', $params['search']['keywords']);
            $this->db->group_end();
        }
    }

    // Sort data by ascending or descending order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id', $params['search']['sortBy']);
    }

    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('id', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }

            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }

    return $result;
}


public function getStokBahan($params = [])
{
    $this->db->from('bahan');

    // JOIN satuan (ambil unit)
    $this->db->join('satuan st', 'st.id = bahan.id_satuan', 'left');

    // JOIN agregat stok masuk/keluar
    $this->db->join(
        '(SELECT id_bahan, SUM(jumlah) AS jml FROM stok_masuk GROUP BY id_bahan) sm',
        'sm.id_bahan = bahan.id',
        'left',
        false
    );
    $this->db->join(
        '(SELECT id_bahan, SUM(jumlah) AS jml FROM stok_keluar GROUP BY id_bahan) sk',
        'sk.id_bahan = bahan.id',
        'left',
        false
    );

    // SELECT fields
    $this->db->select('bahan.*');
    $this->db->select('st.satuan AS satuan', false);         // contoh: MTR/PCS/CM
    $this->db->select('st.nama_satuan AS nama_satuan', false);

    $this->db->select('IFNULL(sm.jml,0) AS stok_masuk', false);
    $this->db->select('IFNULL(sk.jml,0) AS stok_keluar', false);
    $this->db->select('(IFNULL(sm.jml,0) - IFNULL(sk.jml,0)) AS total_stok', false);

    // WHERE
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }

    if ($this->db->field_exists('varian_aktif', 'bahan')) {
        $this->db->where('bahan.varian_aktif !=', 1);
    }

    if (array_key_exists('id', $params) && $params['id'] !== '') {
        $this->db->where('bahan.id', $params['id']);
    }

    // SEARCH
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->group_start();
            $this->db->like('bahan.title', $params['search']['keywords']);
            $this->db->or_like('bahan.varian_warna', $params['search']['keywords']);
            $this->db->or_like('bahan.varian_ukuran', $params['search']['keywords']);
            $this->db->group_end();
        }
    }

    // COUNT
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        return $this->db->count_all_results();
    }

    // SORT DIRECTION
    $dir = 'DESC';
    if (!empty($params['search']['sortBy'])) {
        $s = strtoupper(trim($params['search']['sortBy']));
        if ($s === 'ASC' || $s === 'DESC') $dir = $s;
    }

    // SORT FIELD (default total_stok)
    $field = 'total_stok';
    if (!empty($params['search']['sortField'])) {
        $sf = $params['search']['sortField'];
        if (in_array($sf, ['total_stok','stok_masuk','stok_keluar'], true)) {
            $field = $sf;
        }
    }

    $this->db->order_by($field, $dir);
    $this->db->order_by('bahan.id', 'DESC'); // tie-breaker

    // LIMIT/OFFSET
    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
        $this->db->limit($params['limit'], $params['start']);
    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
        $this->db->limit($params['limit']);
    }

    $query = $this->db->get();
    return ($query->num_rows() > 0) ? $query->result() : false;
}



public function getStokMasuk($params = [])
{
    $this->db->select('*');
    $this->db->from('stok_masuk');
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('create_date', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('`create_date`', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getStokKeluar($params = [])
{
    $this->db->select('*');
    $this->db->from('stok_keluar');
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('create_date', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('`create_date`', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getSatuan($params = [])
{
    $this->db->select('*');
    $this->db->from('satuan');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['keywords'])) {
            $this->db->like('satuan', $params['search']['keywords']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        } else {
            $this->db->order_by('id', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function get_harian($params = [])
{
    
            // $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
    $this->db->select(' 
       `invoice`.`id_invoice`,
       `invoice`.`id_transaksi`,
       `invoice`.`oto`,
       `invoice`.`total_bayar`,
       `invoice`.`potongan_harga`,
       `invoice`.`cashback`,
       `invoice`.`status`,
       `konsumen`.`nama`,
       `konsumen`.`tampil`,
       `konsumen`.`perusahaan`,
       `konsumen`.`no_hp`,
       `tb_users`.`id_user`,
       `tb_users`.`nama_lengkap` AS kasir,
       `invoice`.`pajak`,
       `invoice`.`tgl_ambil`,
       `invoice`.`tgl_trx`');
    $this->db->from('tb_users');
    $this->db->join('invoice', '`tb_users`.`id_user` = `invoice`.`id_user`');
    $this->db->join('konsumen', 'invoice.id_konsumen = konsumen.id');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['user'])) {
            $this->db->where('invoice.id_user', $params['search']['user']);
        }
        if (!empty($params['search']['pelanggan'])) {
            $this->db->where('invoice.id_konsumen', $params['search']['pelanggan']);
            
        }
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("invoice.tgl_trx between '$minvalue' AND '$maxvalue'");
                    // $this->db->where('invoice.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
        }
        if (!empty($params['search']['dari']) and empty($params['search']['sampai'])) {
            $this->db->where('invoice.tgl_trx', $params['search']['dari']);
        }
        
    }
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('`invoice`.`id_invoice`', $params['search']['sortBy']);
    }
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $this->db->order_by('invoice.id_invoice', 'DESC');
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('invoice.id_invoice', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function get_laporan_harian($params = [])
{
    
            // $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
    $this->db->select(' 
       `invoice`.`id_invoice`,
       `invoice`.`id_transaksi`,
       `invoice`.`oto`,
       `invoice`.`total_bayar`,
       `invoice`.`potongan_harga`,
       `invoice`.`cashback`,
       `invoice`.`status`,
       `referal`.`title`,
       `tb_users`.`id_user`,
       `tb_users`.`nama_lengkap` AS kasir,
       `invoice`.`pajak`,
       `invoice`.`tgl_ambil`,
       `invoice`.`tgl_trx`');
    $this->db->from('tb_users');
    $this->db->join('invoice', '`tb_users`.`id_user` = `invoice`.`id_user`');
    $this->db->join('referal', '`referal`.`id` = `invoice`.`id_referal`','left');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['user'])) {
            $this->db->where('invoice.id_user', $params['search']['user']);
        }
        if (!empty($params['search']['referal'])) {
            $this->db->where('invoice.id_referal', $params['search']['referal']);
        }
        if (!empty($params['search']['tanggal'])) {
            $this->db->where('invoice.tgl_trx', $params['search']['tanggal']);
        }
        
    }
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('`invoice`.`id_invoice`', $params['search']['sortBy']);
    }
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $this->db->order_by('invoice.id_invoice', 'DESC');
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('invoice.id_invoice', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function get_operator($params = [])
{
    // FIX: filter produk harus memfilter query header (invoice) juga,
    // dan keyword LIKE harus di-group agar tidak "membobol" filter lain (produk/status/tanggal).

    $this->db->select(' 
       `konsumen`.`nama`,
       `konsumen`.`no_hp`,
       `tb_users`.`nama_lengkap` AS `kasir`,
       `invoice`.`id_invoice`,
       `invoice`.`id_transaksi`,
       `invoice`.`total_bayar`,
       `invoice`.`tgl_trx`,
       `invoice`.`jam_order`,
       `invoice`.`tgl_ambil`,
       `invoice`.`oto`,
       `invoice_detail`.`id_operator`');
    $this->db->from('tb_users');
    $this->db->join('invoice', '`invoice`.`id_user` = `tb_users`.`id_user`');
    $this->db->join('konsumen', 'invoice.id_konsumen = konsumen.id');
    $this->db->join('invoice_detail', '`invoice`.`id_invoice` = `invoice_detail`.`id_invoice`');
    $this->db->group_by('invoice.id_invoice');

    // Where conditions
    if (array_key_exists('where', $params) && is_array($params['where'])) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }

    // Search / Filters
    if (array_key_exists('search', $params) && is_array($params['search'])) {
        // Keyword (GROUPED)
        if (!empty($params['search']['keywords'])) {
            $kw = $params['search']['keywords'];
            $this->db->group_start();
            $this->db->like('`invoice`.`id_invoice`', $kw);
            $this->db->or_like('`konsumen`.`nama`', $kw);
            $this->db->or_like('`konsumen`.`no_hp`', $kw);
            $this->db->or_like('`tb_users`.`nama_lengkap`', $kw);
            $this->db->or_like('`invoice`.`id_transaksi`', $kw);
            $this->db->or_like('`invoice`.`total_bayar`', $kw);
            $this->db->group_end();
        }

        // Date range
        if (!empty($params['search']['dari']) && !empty($params['search']['sampai'])) {
            $this->db->where('invoice.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
        }
        if (!empty($params['search']['dari']) && empty($params['search']['sampai'])) {
            $this->db->where('invoice.tgl_trx', $params['search']['dari']);
        }

        // Status (support string '0')
        if (isset($params['search']['status']) && $params['search']['status'] !== '' && $params['search']['status'] !== null) {
            $this->db->where('invoice_detail.status', $params['search']['status']);
        }

        // Produk (FIX UTAMA)
        if (!empty($params['search']['produk'])) {
            $this->db->where('invoice_detail.id_produk', $params['search']['produk']);
        }
    }

    // Sorting
    if (isset($params['search']['sortBy']) && $params['search']['sortBy'] !== '') {
        $this->db->order_by('`invoice`.`id_invoice`', $params['search']['sortBy']);
    }

    // Return
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
        return $result;
    }

    $this->db->order_by('invoice.id_invoice', 'DESC');
    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
        $this->db->limit($params['limit'], $params['start']);
    } elseif (array_key_exists('limit', $params)) {
        $this->db->limit($params['limit']);
    }
    $query = $this->db->get();
    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
    return $result;
}

public function get_operator_preload(array $invoiceIds, $produk = 0)
{
    $ids = array_values(array_unique(array_filter(array_map('intval', $invoiceIds), static function ($id) {
        return $id > 0;
    })));

    $data = [
        'details'  => [],
        'totals'   => [],
        'payments' => [],
        'progress' => [],
        'proofs'   => [],
    ];

    if (empty($ids)) {
        return $data;
    }

    $hasBuktiTable = $this->db->table_exists('invoice_detail_bukti');
    $buktiSelect = $hasBuktiTable
        ? ", COALESCE((
                SELECT ib.filename
                FROM invoice_detail_bukti ib
                WHERE ib.id_rincianinvoice = d.id_rincianinvoice
                ORDER BY COALESCE(ib.created_at, ib.updated_at) DESC, ib.id DESC
                LIMIT 1
            ), '') AS bukti_file"
        : ", '' AS bukti_file";

    $this->db
        ->select("
            d.*,
            COALESCE(NULLIF(TRIM(p.title), ''), 'Produk') AS nama_produk,
            COALESCE(p.id_jenis, 0) AS id_jenis_produk,
            COALESCE(NULLIF(TRIM(b.title), ''), '-') AS nama_bahan,
            COALESCE(b.kunci, 0) AS cek_bahan,
            COALESCE(NULLIF(TRIM(u.nama_lengkap), ''), '-') AS nama_operator
            {$buktiSelect}
        ", false)
        ->from('invoice_detail d')
        ->join('produk p', 'p.id = d.id_produk', 'left')
        ->join('bahan b', 'b.id = d.id_bahan', 'left')
        ->join('tb_users u', 'u.id_user = d.id_operator', 'left');

    $this->db->where_in('d.id_invoice', $ids);
    if (!empty($produk) && (string)$produk !== '0') {
        $this->db->where('d.id_produk', (int)$produk);
    }
    $this->db->order_by('d.id_invoice ASC, d.id_rincianinvoice ASC');

    $detailIds = [];
    foreach ($this->db->get()->result() as $row) {
        $idInvoice = (int)$row->id_invoice;
        $detailIds[] = (int)$row->id_rincianinvoice;
        if (!isset($data['details'][$idInvoice])) {
            $data['details'][$idInvoice] = [];
        }
        $data['details'][$idInvoice][] = $row;
    }

    $detailIds = array_values(array_unique(array_filter($detailIds)));
    if ($hasBuktiTable && !empty($detailIds)) {
        $captionSelect = $this->db->field_exists('keterangan', 'invoice_detail_bukti')
            ? 'keterangan'
            : "'' AS keterangan";
        $this->db
            ->select("id, id_rincianinvoice, filename, {$captionSelect}, created_at, updated_at", false)
            ->from('invoice_detail_bukti')
            ->where_in('id_rincianinvoice', $detailIds)
            ->order_by('COALESCE(created_at, updated_at) ASC, id ASC', '', false);

        foreach ($this->db->get()->result_array() as $proof) {
            $detailId = (int)$proof['id_rincianinvoice'];
            if (!isset($data['proofs'][$detailId])) {
                $data['proofs'][$detailId] = [];
            }
            $proof['url'] = base_url('uploads/lampiran/progress/' . rawurlencode($proof['filename']));
            $proofTime = !empty($proof['created_at']) ? $proof['created_at'] : $proof['updated_at'];
            $proof['uploaded_at'] = $proofTime;
            $proof['uploaded_at_label'] = !empty($proofTime)
                ? date('d/m/Y H:i:s', strtotime($proofTime)) . ' WIB'
                : '-';
            $data['proofs'][$detailId][] = $proof;
        }
    }

    $this->db
        ->select('id_invoice, SUM(jumlah * harga) AS total_order, SUM(jumlah * harga * diskon / 100) AS total_diskon', false)
        ->from('invoice_detail')
        ->where_in('id_invoice', $ids)
        ->group_by('id_invoice');
    foreach ($this->db->get()->result_array() as $row) {
        $data['totals'][(int)$row['id_invoice']] = [
            'total_order'  => (int)$row['total_order'],
            'total_diskon' => (int)$row['total_diskon'],
        ];
    }

    $this->db
        ->select('id_invoice, SUM(jml_bayar) AS total_bayar', false)
        ->from('bayar_invoice_detail')
        ->where_in('id_invoice', $ids)
        ->group_by('id_invoice');
    foreach ($this->db->get()->result_array() as $row) {
        $data['payments'][(int)$row['id_invoice']] = (int)$row['total_bayar'];
    }

    if ($this->db->table_exists('transaksi_progress')) {
        $this->db
            ->select('id_invoice, checks_json, progress_pct')
            ->from('transaksi_progress')
            ->where_in('id_invoice', $ids);
        foreach ($this->db->get()->result_array() as $row) {
            $indices = json_decode((string)$row['checks_json'], true);
            if (!is_array($indices)) {
                $indices = [];
            }
            $data['progress'][(int)$row['id_invoice']] = [
                'indices' => array_values(array_map('intval', $indices)),
                'pct'     => (int)$row['progress_pct'],
            ];
        }
    }

    return $data;
}


public function get_harians($where)
{
    $qry = 'SELECT invoice.id_invoice,
    invoice.tgl_trx,
    invoice.total_bayar,
    invoice.oto,
    invoice.pajak,
    invoice_detail.id_rincianinvoice,
    invoice_detail.diskon AS disc,
    
    sum(invoice_detail.jumlah * invoice_detail.harga) AS Tot,
    SUM((`invoice_detail`.`jumlah`)*(`invoice_detail`.`harga` * `invoice_detail`.`diskon`/100)) AS sisa,
    round(SUM(`invoice_detail`.`harga` * `invoice_detail`.`diskon`/100)) AS diskon,
    konsumen.nama,
    tb_users.nama_lengkap AS kasir
    FROM
    invoice
    INNER JOIN invoice_detail ON (invoice.id_invoice = invoice_detail.id_invoice)
    LEFT OUTER JOIN konsumen ON (invoice.id_konsumen = konsumen.id)
    LEFT OUTER JOIN tb_users ON (invoice.id_user = tb_users.id_user)
    '.$where.' 
    GROUP BY 
    invoice.id_invoice,
    konsumen.nama,
    tb_users.nama_lengkap ORDER BY invoice.id_invoice DESC';
    $query = $this->db->query($qry);
    
            // if($query->num_rows() > 0){
    return $query->result_array();
            // }
}

public function get_desain($where)
{
    $qry = "SELECT 
    `invoice_detail`.`id_invoice`,
    `jenis_cetakan`.`jenis_cetakan`,
    `invoice`.`id_transaksi`,
    `invoice`.`tgl_trx`,
    `invoice`.`id_desain`,
    `invoice_detail`.`keterangan`
    FROM
    `invoice_detail`
    INNER JOIN `invoice` ON (`invoice_detail`.`id_invoice` = `invoice`.`id_invoice`)
    INNER JOIN `jenis_cetakan` ON (`invoice_detail`.`jenis_cetakan` = `jenis_cetakan`.`id_jenis`)
    WHERE $where AND `invoice_detail`.`jenis_cetakan` = 9";
    $query = $this->db->query($qry);
    if ($query->num_rows() > 0) {
        return $query->row();
    }
}

public function get_detail($where)
{
    $qry = "SELECT 
    `produk`.`title` AS `produk`,
    `invoice_detail`.`id_rincianinvoice`,
    `invoice_detail`.`id_invoice`,
    `invoice_detail`.`keterangan`,
    `invoice_detail`.`detail`,
    `invoice_detail`.`jumlah`,
    `invoice_detail`.`ukuran`,
    `invoice_detail`.`satuan`,
    `invoice_detail`.`status_hitung`,
    `invoice`.`id_transaksi`,
    `invoice`.`tgl_ambil`,
    `bahan`.`title` AS `bahan`,
    `tb_users`.`nama_lengkap` AS `fo`
    FROM
    `produk`
    INNER JOIN `invoice_detail` ON (`produk`.`id` = `invoice_detail`.`id_produk`)
    INNER JOIN `invoice` ON (`invoice_detail`.`id_invoice` = `invoice`.`id_invoice`)
    INNER JOIN `bahan` ON (`invoice_detail`.`id_bahan` = `bahan`.`id`)
    INNER JOIN `tb_users` ON (`invoice`.`id_user` = `tb_users`.`id_user`)
    WHERE
    $where AND `invoice_detail`.`jenis_cetakan`!='9'";
    $query = $this->db->query($qry);
    
    return $query->result();
}

public function piutang($where)
{
            // echo $where;
    $qry = "SELECT 
    `invoice_detail`.`id_invoice`,
    `invoice_detail`.`harga`,
    `invoice_detail`.`jumlah`,
    `invoice_detail`.`diskon`,
    `invoice`.`total_bayar`, 
    `invoice`.`potongan_harga`, 
    `invoice`.`id_transaksi`, 
    `invoice`.`pajak`, 
    `invoice`.`tgl_trx`, 
    `invoice`.`status`, 
    `konsumen`.`nama` AS namak,
    `konsumen`.`no_hp`,
    `konsumen`.`perusahaan`, 
    IFNULL(`tb_users`.`nama_lengkap`,`invoice`.`id_user`)  as fo, 
    
    SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `totalbeli`,
    IFNULL(A.totalbayar,0) AS `totalbayar`,
    SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) -  IFNULL(A.totalbayar,0) - SUM(`invoice`.`potongan_harga`) AS sisa
    
    FROM
    `invoice_detail`
    LEFT OUTER JOIN (SELECT `bayar_invoice_detail`.`id_invoice`, SUM(`bayar_invoice_detail`.`jml_bayar`) AS `totalbayar`
       FROM `bayar_invoice_detail` GROUP BY  `bayar_invoice_detail`.`id_invoice`) A   
    ON (`invoice_detail`.`id_invoice` = A.`id_invoice`)
    
    LEFT OUTER JOIN  
    `invoice` ON 
    (`invoice`.`id_invoice` = `invoice_detail`.`id_invoice`)
    
    LEFT OUTER JOIN  
    `konsumen` ON 
    (`konsumen`.`id` = `invoice`.`id_konsumen`)
    
    LEFT OUTER JOIN  
    `tb_users` ON 
    (`tb_users`.`id_user` = `invoice`.`id_user`)
    
    $where GROUP BY
    `invoice_detail`.`id_invoice`, `invoice`.`id_invoice`
    HAVING sisa > 0 ORDER by  `konsumen`.`nama` ASC";
    $query = $this->db->query($qry);
    
    return $query->result_array();
}

public function carabayar($where)
{
    $qry = "SELECT 
    `jenis_bayar`.`nama_bayar`,
    `jenis_bayar`.`id`
    FROM
    `jenis_bayar`
    RIGHT OUTER JOIN `bayar_invoice_detail` ON (`jenis_bayar`.`id` = `bayar_invoice_detail`.`id_bayar`)
    $where
    GROUP BY `jenis_bayar`.`id`,
    `jenis_bayar`.`nama_bayar`";
    $query = $this->db->query($qry);
    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
    
    return $result;
}

public function cara_bayar($dari, $sampai, $setor)
{
    $qry = "SELECT 
    SUM(CASE WHEN bayar_invoice_detail.id_bayar='1' THEN bayar_invoice_detail.jml_bayar END) as tunai,
    SUM(CASE WHEN bayar_invoice_detail.id_bayar='2' 
       THEN bayar_invoice_detail.jml_bayar END) as transfer,
    `tb_users`.`nama_lengkap`,
    `bayar_invoice_detail`.`id_user`
    FROM
    `invoice`
    INNER JOIN `bayar_invoice_detail` ON (`invoice`.`id_invoice` = `bayar_invoice_detail`.`id_invoice`)
    INNER JOIN `tb_users` ON (`bayar_invoice_detail`.`id_user` = `tb_users`.`id_user`)
    WHERE `bayar_invoice_detail`.`tgl_bayar` BETWEEN CAST('$dari' AS DATE) AND CAST('$sampai' AS DATE) AND `bayar_invoice_detail`.setor='$setor' AND `invoice`.`status` = 'simpan' AND bayar_invoice_detail.hapus=0
    GROUP BY `tb_users`.`nama_lengkap`,`bayar_invoice_detail`.`id_user`";
    $query = $this->db->query($qry);
    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
    
    return $result;
}

public function data_invoice($dari, $sampai, $user, $setor, $jenis_bayar)
{
    if (empty($jenis_bayar)) {
        $jenis_bayar = '`bayar_invoice_detail`.`id_bayar`';
    } else {
        $jenis_bayar = $jenis_bayar;
    }
    
    $qry = "SELECT 
    `jenis_bayar`.`nama_bayar`,
    `jenis_bayar`.`id`
    FROM
    `jenis_bayar`
    RIGHT OUTER JOIN `bayar_invoice_detail` ON (`jenis_bayar`.`id` = $jenis_bayar)
    WHERE `bayar_invoice_detail`.`tgl_bayar` BETWEEN CAST('$dari' AS DATE) AND CAST('$sampai' AS DATE) AND `bayar_invoice_detail`.setor='$setor' AND `bayar_invoice_detail`.id_user = '$user'
    GROUP BY `jenis_bayar`.`id`,
    `jenis_bayar`.`nama_bayar`
    ";
    $query = $this->db->query($qry);
    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
    
    return $result;
}

public function simpan_setor($params = [])
{
            // print_r($params);
    $this->db->select('id_bayar,id_user,setor,tgl_bayar,jml_bayar');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_bayar between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get('bayar_invoice_detail');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function setor_to_owner($params = [])
{
            // print_r($params);
    $this->db->select('id_bayar,id_user,setor,tgl_bayar,jml_bayar');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tanggal_verifikasi between '$minvalue' AND '$maxvalue'");
        }
    }
    
    $query = $this->db->get('laporan_penerimaan');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function update_setor($params = [])
{
            // print_r($params);
    $data = [
        'id_user' => $params['id_user'],
        'setor' => 'Y',
        'tgl_setor' => day_ymd(),
    ];
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_bayar between '$minvalue' AND '$maxvalue'");
        }
    }
    $this->db->update('bayar_invoice_detail', $data);
}

public function rekap_debit($where)
{
    $qry = 'SELECT 
    SUM(`bayar_invoice_detail`.`jml_bayar`) AS `total`,
    `bayar_invoice_detail`.`tgl_bayar`
    FROM
    `invoice`
    INNER JOIN `bayar_invoice_detail` ON (`invoice`.`id_invoice` = `bayar_invoice_detail`.`id_invoice`)
    '.$where.'
    GROUP BY
    `bayar_invoice_detail`.`tgl_bayar`';
    $query = $this->db->query($qry);
    if ($query->num_rows() > 0) {
        return $query->row();
    }
}

public function rekap_kredit($where)
{
    $qry = 'SELECT 
    SUM(`pengeluaran_detail`.`jumlah` * `pengeluaran_detail`.`harga`) AS `total`,
    `pengeluaran`.`tgl_pengeluaran`
    FROM
    `pengeluaran`
    RIGHT OUTER JOIN `pengeluaran_detail` ON (`pengeluaran`.`id_pengeluaran` = `pengeluaran_detail`.`id_pengeluaran`)
    '.$where.'
    GROUP BY
    `pengeluaran`.`tgl_pengeluaran`';
    $query = $this->db->query($qry);
    if ($query->num_rows() > 0) {
        return $query->row();
    }
}

        // pembelian`
public function getPembelian($params = [])
{
            // print_r($params);
    $this->db->select('*');
    $this->db->from('pembelian');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = date('Y-m-d', strtotime($params['search']['dari']));
            $maxvalue = date('Y-m-d', strtotime($params['search']['sampai']));
            if ($minvalue == $maxvalue) {
                $this->db->where("tgl_pembelian = '$minvalue'");
            } else {
                $this->db->where("tgl_pembelian between '$minvalue' AND '$maxvalue'");
            }
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id_pembelian', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('id_pembelian', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

        // pembelian`
public function getSupplier($params = [])
{
    
    $this->db->select('
       `pembelian_detail`.`no`,
       `pembelian_detail`.`id_pembelian`,
       `pembelian_detail`.`id_bahan`,
       `pembelian_detail`.`id_biaya`,
       `pembelian_detail`.`id_supplier`,
       `pembelian_detail`.`no_invo`,
       `pembelian_detail`.`keterangan`,
       `pembelian_detail`.`jumlah`,
       `pembelian_detail`.`harga`,
       `pembelian_detail`.`satuan`,
       `pembelian_detail`.`id_pemesan`,
       `pembelian_detail`.`no_order`,
       `pembelian_detail`.`create_date`,
       `item_pembelian`.`title` AS nama_bahan,
       `supplier`.`id_supplier`,
       `supplier`.`nama_perusahaan`
       ');
    $this->db->from('pembelian_detail');
    $this->db->join('item_pembelian', '`item_pembelian`.`id` = `pembelian_detail`.`id_bahan`');
    $this->db->join('supplier', '`supplier`.`id_supplier` = `pembelian_detail`.`id_supplier`');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = date('Y-m-d', strtotime($params['search']['dari']));
            $maxvalue = date('Y-m-d', strtotime($params['search']['sampai']));
            if ($minvalue == $maxvalue) {
                $this->db->where("tgl_pembelian = '$minvalue'");
            } else {
                $this->db->where("tgl_pembelian between '$minvalue' AND '$maxvalue'");
            }
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id_pembelian', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('id_pembelian', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

        // pengeluaran_detail`
public function getPengeluaran($params = [])
{
            // print_r($params);
    $this->db->select('*');
    $this->db->from('pengeluaran');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = date('Y-m-d', strtotime($params['search']['dari']));
            $maxvalue = date('Y-m-d', strtotime($params['search']['sampai']));
            if ($minvalue == $maxvalue) {
                $this->db->where("tgl_pengeluaran = '$minvalue'");
            } else {
                $this->db->where("tgl_pengeluaran between '$minvalue' AND '$maxvalue'");
            }
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('id_pengeluaran', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('id_pengeluaran', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

        // piutang usaha
public function getPiutangUsaha($params = [])
{
            // print_r($params);
    $this->db->select('`jenis_kas`.`title`,
       `pengeluaran`.`id_pengeluaran`,
       `pengeluaran`.`total_bayar`,
       `pengeluaran_detail`.`jumlah`,
       `pengeluaran_detail`.`harga`,
       `pengeluaran_detail`.`keterangan`,
       `pengeluaran`.`id_bayar`,
       `pengeluaran`.`id_kas`,
       `pengeluaran`.`tgl_pengeluaran`,
       `tb_users`.`id_user`,
       `tb_users`.`nama_lengkap`,
       `supplier`.`nama_perusahaan`');
    $this->db->from('pengeluaran');
    $this->db->join('pengeluaran_detail', '`pengeluaran`.`id_pengeluaran` = `pengeluaran_detail`.`id_pengeluaran`');
    $this->db->join('jenis_pengeluaran', '`pengeluaran_detail`.`id_biaya` = `jenis_pengeluaran`.`id_jenis`');
    $this->db->join('jenis_kas', '`jenis_pengeluaran`.`id_akun` = `jenis_kas`.`id_akun`');
    $this->db->join('tb_users', '`pengeluaran`.`id_user` = `tb_users`.`id_user`');
    $this->db->join('supplier', '`pengeluaran_detail`.`id_supplier` = `supplier`.`id_supplier`');
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = $params['search']['dari'];
            $maxvalue = $params['search']['sampai'];
            $this->db->where("tgl_pengeluaran between '$minvalue' AND '$maxvalue'");
        }
        if (!empty($params['search']['dari']) and empty($params['search']['sampai'])) {
            $this->db->where('tgl_pengeluaran', $params['search']['dari']);
        }
        
        if (!empty($params['search']['user'])) {
            $this->db->like('pengeluaran.id_user', $params['search']['user']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('pengeluaran.id_pengeluaran', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $this->db->where('pos', 'Y');
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
            if (!empty($params['id'])) {
                $this->db->where('id_pengeluaran', $params['id']);
            }
            $this->db->where('pos', 'Y');
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->where('pos', 'Y');
            $this->db->order_by('id_pengeluaran', 'DESC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function getPengeluaranPerproduk($params = [])
{
            // print_r($params);
    $this->db->select('`pengeluaran_detail`.`no`,
       `pengeluaran_detail`.`id_biaya`,
       `pengeluaran`.`id_user`,
       `pengeluaran`.`id_bayar`,
       `pengeluaran`.`id_kas`,
       `pengeluaran`.`id_pengeluaran`,
       `pengeluaran`.`tgl_pengeluaran`,
       `pengeluaran`.`lunas`,
       `pengeluaran`.`tgl_jatuhtempo`,
       `pengeluaran`.`total_bayar`');
    $this->db->from('tb_users');
    $this->db->join('pengeluaran', '`tb_users`.`id_user` = `pengeluaran`.`id_user`');
    $this->db->join('pengeluaran_detail', '`pengeluaran`.`id_pengeluaran` = `pengeluaran_detail`.`id_pengeluaran`');
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $this->db->where('tgl_pengeluaran BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
        }
        if (!empty($params['search']['dari']) and empty($params['search']['sampai'])) {
            $this->db->where('tgl_pengeluaran', $params['search']['dari']);
        }
        
        if (!empty($params['search']['user'])) {
            $this->db->where('tb_users.id_user', $params['search']['user']);
        }
        
        if (!empty($params['search']['jenis'])) {
            $this->db->where('pengeluaran_detail.id_biaya', $params['search']['jenis']);
        }
    }
    
            // Sort data by ascending or desceding order
    if (!empty($params['search']['sortBy'])) {
        $this->db->order_by('pengeluaran.id_pengeluaran', $params['search']['sortBy']);
    }
    
    if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
        $result = $this->db->count_all_results();
    } else {
        if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    // if(!empty($params['id'])){
                    // $this->db->where('id_pengeluaran', $params['id']);
                    // }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        } else {
            $this->db->order_by('pengeluaran.id_pengeluaran', 'ASC');
            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result_array() : false;
        }
    }
    
            // Return fetched data
    return $result;
}

public function get_Satu($where)
{
    $this->db->select('`pengeluaran`.`id_pengeluaran`,
       `pengeluaran`.`id_user`,
       `pengeluaran`.`tgl_pengeluaran`');
    $this->db->from('pengeluaran');
    $this->db->where($where);
    $this->db->order_by('`pengeluaran`.`tgl_pengeluaran`', 'DESC');
    
    return $this->db->get()->result_array();
}

public function grafik_perbulan($bulan, $tahun)
{
    $this->db->select('YEAR(tgl_bayar) as tahun, MONTH(tgl_bayar) as bulan, DAY(tgl_bayar) as hari ');
    $this->db->select_sum('jml_bayar');
    $this->db->from('bayar_invoice_detail');
    $this->db->where('MONTH(tgl_bayar)', $bulan);
    $this->db->where('YEAR(tgl_bayar)', $tahun);
    $this->db->group_by('tgl_bayar');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

public function grafik_perbulan_desain($bulan, $tahun, $where)
{
    $this->db->select('count(id_desain) as counter, YEAR(tgl_trx) as tahun, MONTH(tgl_trx) as bulan, DAY(tgl_trx) as hari ');
    $this->db->from('invoice');
    $this->db->where($where);
    $this->db->where('MONTH(tgl_trx)', $bulan);
    $this->db->where('YEAR(tgl_trx)', $tahun);
    $this->db->group_by('tgl_trx');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

public function grafik_omset($bulan, $tahun)
{
    $this->db->select('SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `Total`, YEAR(invoice.tgl_trx) as tahun, MONTH(invoice.tgl_trx) as bulan, DAY(invoice.tgl_trx) as hari ');
    $this->db->from('invoice_detail');
    $this->db->join('invoice', 'invoice_detail.id_invoice = invoice.id_invoice');
    $this->db->where('MONTH(invoice.tgl_trx)', $bulan);
    $this->db->where('YEAR(invoice.tgl_trx)', $tahun);
    $this->db->where('invoice.status!=', 'baru');
    $this->db->where('invoice.status!=', 'batal');
    $this->db->group_by('invoice.tgl_trx');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

 public function grafik_omset_produk($bulan, $tahun, $id_bahan = 0, $id_kategori = 0, $id_produk = 0, $group_by = 'produk')
{
    $group_by = in_array($group_by, array('kategori', 'produk', 'bahan'), true) ? $group_by : 'produk';
    $groupConfig = array(
        'kategori' => array(
            'title' => "COALESCE(jenis_cetakan.jenis_cetakan, 'Tanpa Kategori')",
            'field' => 'produk.id_jenis'
        ),
        'produk' => array(
            'title' => "COALESCE(produk.title, 'Tanpa Produk')",
            'field' => 'produk.id'
        ),
        'bahan' => array(
            'title' => "COALESCE(bahan.title, 'Tanpa Bahan')",
            'field' => 'invoice_detail.id_bahan'
        )
    );
    $group = $groupConfig[$group_by];

    $this->db->select('SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`) AS `Total`, ' . $group['title'] . ' AS title, YEAR(invoice.tgl_trx) as tahun, MONTH(invoice.tgl_trx) as bulan, DAY(invoice.tgl_trx) as hari ', false);
    $this->db->from('produk');
    $this->db->join('invoice_detail', 'produk.id = invoice_detail.id_produk');
    $this->db->join('invoice', 'invoice_detail.id_invoice = invoice.id_invoice');
    $this->db->join('jenis_cetakan', 'jenis_cetakan.id_jenis = produk.id_jenis', 'left');
    $this->db->join('bahan', 'bahan.id = invoice_detail.id_bahan', 'left');
    $this->db->where('MONTH(invoice.tgl_trx)', $bulan);
    $this->db->where('YEAR(invoice.tgl_trx)', $tahun);
    $this->db->where('invoice.status!=', 'baru');
    if ((int)$id_bahan > 0) {
        $this->db->where('invoice_detail.id_bahan', (int)$id_bahan);
    }
      if ((int)$id_kategori > 0) {
          $this->db->where('produk.id_jenis', (int)$id_kategori);
      }
      if ((int)$id_produk > 0) {
          $this->db->where('produk.id', (int)$id_produk);
      }
    $this->db->group_by($group['field']);
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

public function grafik_pertahun($tahun)
{
    $this->db->select('YEAR(tgl_bayar) as tahun, MONTH(tgl_bayar) as bulan');
    $this->db->select_sum('jml_bayar');
    $this->db->from('bayar_invoice_detail');
    $this->db->where('YEAR(tgl_bayar)', $tahun);
    $this->db->group_by(['MONTH(tgl_bayar)', 'YEAR(tgl_bayar)']);
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        return false;
    }
}

public function getRowsOrder($params = [])
{
            // print_r($params);
    $this->db->select('id_invoice,pajak');
    
    if (array_key_exists('where', $params)) {
        foreach ($params['where'] as $key => $val) {
            $this->db->where($key, $val);
        }
    }
    
    if (array_key_exists('search', $params)) {
        if (!empty($params['search']['trx'])) {
            if ($params['search']['trx'] == 1) {
                $this->db->where('`invoice`.`status`', 'simpan');
                $this->db->like('`invoice`.`lunas`', $params['search']['trx']);
            } elseif ($params['search']['trx'] == 2) {
                $this->db->like('`invoice`.`lunas`', 0);
            } else {
                $this->db->like('`invoice`.`status`', $params['search']['trx']);
            }
        }
        if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
            $minvalue = date('Y-m-d', strtotime($params['search']['dari']));
            $maxvalue = date('Y-m-d', strtotime($params['search']['sampai']));
            if ($minvalue == $maxvalue) {
                $this->db->where("tgl_trx = '$minvalue'");
            } else {
                $this->db->where("tgl_trx between '$minvalue' AND '$maxvalue'");
            }
        }
    }
    
    $query = $this->db->get('invoice');
    $result = ($query->num_rows() > 0) ? $query->result() : false;
    
    return $result;
}

public function getRows($params = [])
{
    $this->db->select('
        `i`.`tgl_trx`,
        `i`.`id_transaksi`,
        `i`.`id_konsumen`,
        `i`.`id_dropshiper`,
        `i`.`potongan_harga`,
        `i`.`cashback`,
        `i`.`pajak`,
        `i`.`status`,
        `i`.`oto`,
        `i`.`lunas`,
        `i`.`cetak`,
        `k`.`id`,
        `k`.`nama`,
        `k`.`no_hp`,
        `k`.`tampil`,
        `k`.`perusahaan`,
        i.id_invoice,
        k.nama AS konsumen_nama,
        u.nama_lengkap AS user_nama,
        SUM(id.jumlah * id.harga) AS total_order,
        i.potongan_harga AS total_potongan_harga,
        (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
        WHERE bd.id_invoice = i.id_invoice) AS total_bayar,
        (SUM(id.jumlah * id.harga) - 
        (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
        WHERE bd.id_invoice = i.id_invoice)) AS sisa_bayar
        ');
    
            // From invoice table and joining with konsumen and tb_users
    $this->db->from('invoice i');
    $this->db->join('konsumen k', 'k.id = i.id_konsumen', 'left');
            $this->db->join('tb_users u', 'u.id_user = i.id_marketing', 'left'); // Adjust this join condition as needed
            $this->db->join('invoice_detail id', 'id.id_invoice = i.id_invoice', 'left');
            
            // Group by invoice ID
            $this->db->group_by('i.id_invoice');
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`i`.`id_invoice`', $params['search']['keywords']);
                    $this->db->or_like('`k`.`nama`', $params['search']['keywords']);
                    $this->db->or_like('`k`.`no_hp`', $params['search']['keywords']);
                    $this->db->or_like('`u`.`nama_lengkap`', $params['search']['keywords']);
                    $this->db->or_like('`i`.`id_transaksi`', $params['search']['keywords']);
                    $this->db->or_like('`i`.`total_bayar`', $params['search']['keywords']);
                }
                if (!empty($params['search']['statusFilter'])) {
                    $statusGroups = [
                        'success' => ['sent', 'delivered', 'read'],
                        'waiting' => ['pending', 'waiting', 'processing'],
                        'failed' => ['invalid', 'expired', 'failed', 'url unreachable']
                    ];
                    $filter = $params['search']['statusFilter'];
                    if (isset($statusGroups[$filter])) {
                        $this->db->where_in('LOWER(report_pesan.status)', $statusGroups[$filter]);
                    }
                }
                if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
                    $this->db->where('i.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
                }
                if (!empty($params['search']['trx'])) {
                    if ($params['search']['trx'] == 1) {
                        $this->db->where('`i`.`status`', 'simpan');
                        $this->db->like('`i`.`lunas`', $params['search']['trx']);
                    } elseif ($params['search']['trx'] == 2) {
                        $this->db->like('`i`.`lunas`', 0);
                    } else {
                        $this->db->like('`i`.`status`', $params['search']['trx']);
                    }
                }
            }
            if (!empty($params['search']['sortBy'])) {
                if ($params['search']['sortBy'] == 'min_order') {
                    $this->db->order_by('`i`.`total_bayar`', 'ASC');
                } elseif ($params['search']['sortBy'] == 'max_order') {
                    $this->db->order_by('`i`.`total_bayar`', 'DESC');
                } else {
                    $this->db->order_by('`i`.`id_invoice`', $params['search']['sortBy']);
                }
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('i.id_invoice', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                } else {
                    $this->db->order_by('i.id_invoice', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function getRowsLaporan($params = [])
        {
            // print_r($params);
            $this->db->select('*');
            $this->db->from('laporan_stok');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`title`', $params['search']['keywords']);
                }
                
                if (!empty($params['search']['tanggal'])) {
                    $this->db->where('tanggal', $params['search']['tanggal']);
                }
            }
            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('id', $params['search']['sortBy']);
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('id', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                } else {
                    $this->db->order_by('id', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        
        public function getKonsumenOrder($params = [])
        {
            // print_r($params);
            
            $this->db->select(' `k`.`id`,
                `k`.`nama`,
                `k`.`no_hp`,
                `k`.`tgl_daftar`,
                `k`.`jenis`,
                `k`.`jenis_member`,
                `i`.`id_invoice`,
                IFNULL(`i`.`total_bayar`, 0) AS total_bayar,
                IFNULL(SUM(`i`.`potongan_harga`), 0) AS potongan,
                IFNULL(SUM(`invoice_detail`.`jumlah` * `invoice_detail`.`harga`), 0) AS total_order');
            $this->db->from('konsumen k');
            $this->db->join('invoice i', '`k`.`id` = `i`.`id_konsumen`', 'left outer');
            $this->db->join('invoice_detail', '`i`.`id_invoice` = `invoice_detail`.`id_invoice`', 'left outer');
            $this->db->where('k.hapus', 0);
            $this->db->group_by('k.id');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`k`.`nama`', $params['search']['keywords']);
                    $this->db->or_like('`k`.`no_hp`', $params['search']['keywords']);
                }
            }
            if (!empty($params['search']['sortBy'])) {
                if ($params['search']['sortBy'] == 'last_register') {
                    $this->db->order_by('`k`.`tgl_daftar`', 'DESC');
                    
                } elseif ($params['search']['sortBy'] == 'last_order') {
                    $this->db->order_by('`i`.`tgl_trx`', 'DESC');
                } elseif ($params['search']['sortBy'] == 'min_order') {
                    $this->db->order_by('total_order', 'ASC');
                } elseif ($params['search']['sortBy'] == 'max_order') {
                    $this->db->order_by('total_order', 'DESC');
                } else {
                    $this->db->order_by('`k`.`nama`', $params['search']['sortBy']);
                }
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                // $this->db->group_by(array("`konsumen`.`nama`", "`konsumen`.`no_hp`", "`konsumen`.`tgl_daftar`"));
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('k.id', $params['id']);
                        // print_r($params);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                } else {
                    // $this->db->group_by(array("`konsumen`.`nama`", "`konsumen`.`no_hp`", "`konsumen`.`tgl_daftar`"));
                    // $this->db->order_by('konsumen.last_update', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            return $result;
        }
        
        public function getKonsumen($params = [])
        {
            // print_r($params);
            if (!empty($params['search']['sortBy']) and $params['search']['sortBy'] == 'max_order' or !empty($params['search']['sortBy']) and $params['search']['sortBy'] == 'min_order' or !empty($params['search']['sortBy']) and $params['search']['sortBy'] == 'last_order') {
                $this->db->select('`konsumen`.`nama`,
                    `konsumen`.`tgl_daftar`,
                    `konsumen`.`no_hp`,
                    `konsumen`.`id`,
                    `konsumen`.`jenis`,
                    `konsumen`.`jenis_member`,
                    `invoice`.`id_invoice`,
                    `invoice`.`tgl_trx`,
                    `invoice`.`potongan_harga`,
                    `invoice`.`cashback`,
                    `invoice`.`pajak`,
                    `invoice`.`total_bayar`');
                $this->db->from('konsumen');
                $this->db->join('invoice', '`konsumen`.`id` = `invoice`.`id_konsumen`', 'left outer');
                $this->db->where('konsumen.hapus', 0);
            } else {
                $this->db->select('`konsumen`.`nama`,
                    `konsumen`.`tgl_daftar`,
                    `konsumen`.`no_hp`,
                    `konsumen`.`id`,
                    `konsumen`.`jenis`,
                    `konsumen`.`jenis_member`,
                    `invoice`.`id_invoice`,
                    `invoice`.`tgl_trx`,
                    `invoice`.`potongan_harga`,
                    `invoice`.`cashback`,
                    `invoice`.`pajak`,
                    `invoice`.`total_bayar`');
                $this->db->from('konsumen');
                $this->db->join('invoice', '`konsumen`.`id` = `invoice`.`id_konsumen`', 'left outer');
                $this->db->where('konsumen.hapus', 0);
                $this->db->group_by('invoice.id_konsumen');
            }
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`konsumen`.`nama`', $params['search']['keywords']);
                    $this->db->or_like('`konsumen`.`no_hp`', $params['search']['keywords']);
                }
            }
            if (!empty($params['search']['sortBy'])) {
                if ($params['search']['sortBy'] == 'last_register') {
                    $this->db->order_by('`konsumen`.`tgl_daftar`', 'DESC');
                } elseif ($params['search']['sortBy'] == 'min_order') {
                    $this->db->order_by('`invoice`.`total_bayar`', 'ASC');
                } elseif ($params['search']['sortBy'] == 'last_order') {
                    $this->db->order_by('`invoice`.`tgl_trx`', 'DESC');
                } elseif ($params['search']['sortBy'] == 'max_order') {
                    $this->db->order_by('`invoice`.`total_bayar`', 'DESC');
                } else {
                    $this->db->order_by('`konsumen`.`nama`', $params['search']['sortBy']);
                }
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                // $this->db->group_by(array("`konsumen`.`nama`", "`konsumen`.`no_hp`", "`konsumen`.`tgl_daftar`"));
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('konsumen.id', $params['id']);
                        // print_r($params);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                } else {
                    // $this->db->group_by(array("`konsumen`.`nama`", "`konsumen`.`no_hp`", "`konsumen`.`tgl_daftar`"));
                    // $this->db->order_by('konsumen.last_update', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            return $result;
        }
        
        public function getHistory($params = [])
        {
            $this->db->select('user_agent.*, tb_users.nama_lengkap AS nama_user');
            $this->db->from('user_agent');
            $this->db->join('tb_users', 'tb_users.id_user = user_agent.id_user', 'left');

            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }

            if (array_key_exists('search', $params)) {

            // filter: online saja
            if (!empty($params['search']['onlineOnly'])) {
                // ONLINE jika last_activity >= NOW - 3 menit
                $this->db->where('user_agent.last_activity >=', date('Y-m-d H:i:s', time() - 180));
            }

            if (!empty($params['search']['keywords'])) {
                $kw = $params['search']['keywords'];
                $this->db->group_start();
                $this->db->like('user_agent.ip', $kw);
                $this->db->or_like('user_agent.browser', $kw);
                $this->db->or_like('user_agent.os', $kw);
                $this->db->or_like('tb_users.nama_lengkap', $kw);
                $this->db->group_end();
            }
        }


            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('user_agent.update_date', $params['search']['sortBy']);
                $this->db->order_by('user_agent.id', $params['search']['sortBy']);
            } else {
                $this->db->order_by('user_agent.last_activity', 'desc');
                $this->db->order_by('user_agent.update_date', 'desc');
            }

            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                return $this->db->count_all_results();
            }

            if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                if (!empty($params['id'])) $this->db->where('user_agent.id', $params['id']);
                return $this->db->get()->row_array();
            }

            if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                $this->db->limit($params['limit'], $params['start']);
            } elseif (array_key_exists('limit', $params)) {
                $this->db->limit($params['limit']);
            }

            $q = $this->db->get();
            return ($q->num_rows() > 0) ? $q->result_array() : false;
        }


        
        public function getNomor($params = [])
        {
            $this->db->select('*');
            $this->db->from('antrian');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`nomor`', $params['search']['keywords']);
                }
            }
            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('`id`', $params['search']['sortBy']);
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('id', $params['id']);
                        // print_r($params);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                } else {
                    $this->db->order_by('tanggal', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            return $result;
        }
        
        public function getDetail($params = [])
        {
            // print_r($params);
            $this->db->select('`konsumen`.`nama`,
               `konsumen`.`no_hp`,
               `invoice`.`id_invoice`,
               `invoice`.`id_transaksi`,
               `invoice`.`total_bayar`,
               `invoice`.`potongan_harga`,
               `invoice`.`cashback`,
               `invoice`.`pajak`,
               `invoice`.`tgl_trx`,
               `invoice`.`oto`,
               `tb_users`.`nama_lengkap`');
            $this->db->from('konsumen');
            $this->db->join('invoice', '`konsumen`.`id` = `invoice`.`id_konsumen`');
            $this->db->join('tb_users', '`invoice`.`id_marketing` = `tb_users`.`id_user`');
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $keyword = trim((string) $params['search']['keywords']);
                    $keywordLike = $this->db->escape('%'.$this->db->escape_like_str($keyword).'%');

                    $this->db->group_start();
                    $this->db->like('`invoice`.`id_invoice`', $keyword);
                    $this->db->or_like('`invoice`.`id_transaksi`', $keyword);
                    $this->db->or_like('`konsumen`.`nama`', $keyword);
                    $this->db->or_like('`konsumen`.`no_hp`', $keyword);
                    $this->db->or_like('`tb_users`.`nama_lengkap`', $keyword);
                    $this->db->or_where(
                        'EXISTS (
                            SELECT 1
                            FROM `invoice_detail` AS `detail_cari`
                            LEFT JOIN `bahan` AS `bahan_cari`
                                ON `bahan_cari`.`id` = `detail_cari`.`id_bahan`
                            LEFT JOIN `produk` AS `produk_cari`
                                ON `produk_cari`.`id` = `detail_cari`.`id_produk`
                            WHERE `detail_cari`.`id_invoice` = `invoice`.`id_invoice`
                              AND (
                                  `bahan_cari`.`title` LIKE '.$keywordLike.' ESCAPE \'!\'
                                  OR `produk_cari`.`title` LIKE '.$keywordLike.' ESCAPE \'!\'
                                  OR `detail_cari`.`keterangan` LIKE '.$keywordLike.' ESCAPE \'!\'
                              )
                        )',
                        NULL,
                        FALSE
                    );
                    $this->db->group_end();
                }
                if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
                    $this->db->where(
                        '`invoice`.`tgl_trx` BETWEEN "'.
                        date('Y-m-d', strtotime($params['search']['dari'])).
                        '" and "'.
                        date('Y-m-d', strtotime($params['search']['sampai'])).
                        '"'
                    );
                }
            }
            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('`invoice`.`id_invoice`', $params['search']['sortBy']);
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $this->db->where('konsumen.id', $params['id']);
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('konsumen.id', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->result_array();
                } else {
                    $this->db->order_by('invoice.id_invoice', 'ASC');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function getCari($params = [])
        {
            $this->db->select('`invoice`.`tgl_trx`,
               `invoice`.`id_invoice`,
               `invoice`.`total_bayar`,
               `invoice`.`status`,
               `invoice`.`oto`,
               `invoice`.`lunas`,
               `invoice`.`cetak`,
               `konsumen`.`id`,
               `konsumen`.`nama`,
               `konsumen`.`no_hp`,
               `tb_users`.`nama_lengkap`');
            $this->db->from($this->table);
            $this->db->join('invoice', '`tb_users`.`id_user` = `invoice`.`id_marketing`');
            $this->db->join('konsumen', '`invoice`.`id_konsumen` = `konsumen`.`id`');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`invoice`.`id_invoice`', $params['search']['keywords']);
                    $this->db->or_like('`konsumen`.`nama`', $params['search']['keywords']);
                    $this->db->or_like('`konsumen`.`no_hp`', $params['search']['keywords']);
                    $this->db->or_like('`tb_users`.`nama_lengkap`', $params['search']['keywords']);
                }
            }
            
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('konsumen.id', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                } else {
                    $this->db->order_by('invoice.id_invoice', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->row_array() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function getCariInv($params = [])
        {
            $this->db->select('`invoice`.`tgl_trx`,
               `invoice`.`id_invoice`,
               `invoice`.`id_transaksi`,
               `invoice`.`total_bayar`,
               `invoice`.`status`,
               `invoice`.`oto`,
               `invoice`.`lunas`,
               `invoice`.`cetak`,
               `konsumen`.`id`,
               `konsumen`.`nama`,
               `konsumen`.`no_hp`,
               `tb_users`.`nama_lengkap`');
            $this->db->from($this->table);
            $this->db->join('invoice', '`tb_users`.`id_user` = `invoice`.`id_marketing`');
            $this->db->join('konsumen', '`invoice`.`id_konsumen` = `konsumen`.`id`');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['nama'])) {
                    $this->db->or_like('`konsumen`.`nama`', $params['search']['nama']);
                    $this->db->or_like('`konsumen`.`no_hp`', $params['search']['nama']);
                }
                // dump($params);
            }
            
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('konsumen.id', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->result_array();
                } else {
                    $this->db->order_by('invoice.id_invoice', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function detailMerchant()
        {
            $this->db->select('`rekening_bank`.`inisial`,
               SUM(`kas_masuk`.`pemasukan`) AS `total`,
               SUM(`kas_masuk`.`pengeluaran`) AS `total_pengeluaran`,
               `kas_masuk`.`id_sub_bayar`,
               `kas_masuk`.`id_jenis`,
               `kas_masuk`.`no_reff`,
               `kas_masuk`.`pemasukan`,
               `kas_masuk`.`pengeluaran`
               ');
            $this->db->from('rekening_bank');
            $this->db->join('kas_masuk', '`rekening_bank`.`id` = `kas_masuk`.`id_sub_bayar`');
            
            $this->db->group_by(['`rekening_bank`.`inisial`', '`kas_masuk`.`id_sub_bayar`']);
            
            $query = $this->db->get();
            $result = ($query->num_rows() > 0) ? $query->result() : false;
            
            return $result;
        }
        
        public function getRowsHarian($params = [])
        {
            $this->db->select('`invoice`.`tgl_trx`,
               `invoice`.`id_invoice`,
               `invoice`.`total_bayar`,
               `invoice`.`status`,
               `invoice`.`oto`,
               `invoice`.`lunas`,
               `invoice`.`cetak`,
               `konsumen`.`id`,
               `konsumen`.`nama`,
               `konsumen`.`no_hp`,
               `tb_users`.`nama_lengkap`');
            $this->db->from($this->table);
            $this->db->join('invoice', '`tb_users`.`id_user` = `invoice`.`id_marketing`');
            $this->db->join('konsumen', '`invoice`.`id_konsumen` = `konsumen`.`id`');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('`invoice`.`id_invoice`', $params['search']['keywords']);
                    $this->db->or_like('`konsumen`.`nama`', $params['search']['keywords']);
                    $this->db->or_like('`konsumen`.`no_hp`', $params['search']['keywords']);
                    $this->db->or_like('`tb_users`.`nama_lengkap`', $params['search']['keywords']);
                }
                if (!empty($params['search']['trx'])) {
                    if ($params['search']['trx'] == 1) {
                        $this->db->like('`invoice`.`lunas`', $params['search']['trx']);
                    } else {
                        $this->db->like('`invoice`.`status`', $params['search']['trx']);
                    }
                }
                if (!empty($params['search']['dari'])) {
                    $this->db->where('tgl_trx', $params['search']['dari']);
                }
            }
            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('`invoice`.`id_invoice`', $params['search']['sortBy']);
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('invoice.id_invoice', $params['id']);
                    }
                    $query = $this->db->get();
                    $result = $query->row_array();
                } else {
                    $this->db->order_by('invoice.id_invoice', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function getMonthPembelian($bulan, $tahun)
        {
            return $this->db->select('*')
            ->from('pembelian')
            ->where('MONTH(tgl_pembelian)', $bulan)
            ->where('YEAR(tgl_pembelian)', $tahun)
            ->order_by('id_pembelian', 'ASC')
            ->get()
            ->result();
        }
        
        public function getReport($params = [])
        {
            // print_r($params);
            
            $this->db->select('
               `report_pesan`.`message`,
               `report_pesan`.`id`,
               `report_pesan`.`id_kirim`,
               `report_pesan`.`id_konsumen`,
               `report_pesan`.`target`,
               `report_pesan`.`status`,
               `report_pesan`.`create_date`');

            // Tetap kompatibel dengan database lama ketika kolom tracking
            // belum ditambahkan di hosting.
            if ($this->db->field_exists('state', 'report_pesan')) {
                $this->db->select('`report_pesan`.`state`');
            }
            if ($this->db->field_exists('stateid', 'report_pesan')) {
                $this->db->select('`report_pesan`.`stateid`');
            }
            $this->db->from('report_pesan');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['user'])) {
                    $this->db->where('report_pesan.target', $params['search']['user']);
                }
                if (!empty($params['search']['keywords'])) {
                    $this->db->group_start()
                        ->like('report_pesan.message', $params['search']['keywords'])
                        ->or_like('report_pesan.target', $params['search']['keywords'])
                        ->group_end();
                }
                if (!empty($params['search']['statusFilter'])) {
                    $statusGroups = [
                        'success' => ['sent', 'delivered', 'read'],
                        'waiting' => ['pending', 'waiting', 'processing'],
                        'failed' => ['invalid', 'expired', 'failed', 'url unreachable']
                    ];
                    $filter = $params['search']['statusFilter'];
                    if (isset($statusGroups[$filter])) {
                        $this->db->group_start()
                            ->where_in('LOWER(report_pesan.status)', $statusGroups[$filter]);
                        if ($this->db->field_exists('state', 'report_pesan')) {
                            $this->db->or_where_in('LOWER(report_pesan.state)', $statusGroups[$filter]);
                        }
                        $this->db->group_end();
                    }
                }
            }
            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('`report_pesan`.`id`', $params['search']['sortBy']);
            }
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    $this->db->order_by('report_pesan.id', 'DESC');
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->row() : false;
                } else {
                    $this->db->order_by('report_pesan.id', 'DESC');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function getBackup($params = [])
        {
            $this->db->select('*');
            $this->db->from('backup_data');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
                }
            }
            
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->like('title', $params['search']['keywords']);
                }
            }
            
            // Sort data by ascending or desceding order
            if (!empty($params['search']['sortBy'])) {
                $this->db->order_by('id', $params['search']['sortBy']);
            }
            
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
            } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
                } else {
                    $this->db->order_by('id', 'DESC');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                    } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
                    }
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result() : false;
                }
            }
            
            // Return fetched data
            return $result;
        }
        
        public function cek_barcode($id, $barcode)
        {
            
            $query = $this->db
            ->select('id')
            ->where("BINARY barcode = '$barcode'", null, false)
            ->get('produk');
            
            if (
                $query->num_rows() == 1
                && $query->row_array()['id'] == $id
                || $query->num_rows() == 0
            ) {
                return true;
            } else {
                return false;
            }
        }
        public function cek_barcode_bahan($id, $barcode)
        {
            
            $query = $this->db
            ->select('id')
            ->where("BINARY barcode = '$barcode'", null, false)
            ->get('bahan');
            
            if (
                $query->num_rows() == 1
                && $query->row_array()['id'] == $id
                || $query->num_rows() == 0
            ) {
                return true;
            } else {
                return false;
            }
        }
        public function cek_nama($id, $nama)
        {
            $title = strtoupper($nama);
            $query = $this->db
            ->select('id')
            ->where("BINARY UPPER(title) = '$title'", null, false)
            ->get('produk');
            
            if (
                $query->num_rows() == 1
                && $query->row_array()['id'] == $id
                || $query->num_rows() == 0
            ) {
                return true;
            } else {
                return false;
            }
        }
        public function cek_title($id, $nama)
        {
            $title = strtoupper($nama);
            $query = $this->db
            ->select('id')
            ->where("BINARY UPPER(title) = '$title'", null, false)
            ->get('bahan');
            
            if (
                $query->num_rows() == 1
                && $query->row_array()['id'] == $id
                || $query->num_rows() == 0
            ) {
                return true;
            } else {
                return false;
            }
        }
        public function cek_telp($id, $no_hp)
        {
            $query = $this->db
            ->select('id')
            ->where("BINARY no_hp = '$no_hp'", null, false)
            ->get('konsumen');
            
            if (
                $query->num_rows() == 1
                && $query->row_array()['id'] == $id
                || $query->num_rows() == 0
            ) {
                return true;
            } else {
                return false;
            }
        }
        public function get_daily_sales() {
            // Query to get sales data aggregated by day
            $this->db->select("DATE(i.tgl_trx) AS date, 
                SUM(id.jumlah * id.harga) AS total_sales");
            $this->db->from('invoice i');
            $this->db->join('invoice_detail id', 'id.id_invoice = i.id_invoice', 'left');
            $this->db->group_by("date");
            $this->db->order_by("date", "ASC");
            
            $query = $this->db->get();
            return $query->result();
        }
        
        public function get_daily_payments($bulan,$tahun) {
            // Query to get daily payments aggregated by day
            $this->db->select("DATE(tgl_bayar) AS date, 
                SUM(jml_bayar) AS total_payments");
            $this->db->from('bayar_invoice_detail');
            $this->db->where('MONTH(tgl_bayar)', $bulan);
            $this->db->where('YEAR(tgl_bayar)', $tahun);
            $this->db->group_by("date");
            $this->db->order_by("date", "ASC");
            
            $query = $this->db->get();
            return $query->result();
        }
    }
