<?php
class M_order extends CI_Model
{

    function get_myefood($kopel)
    {
        $hsl = $this->db->query("SELECT a.*,b.nm_toko FROM tbl_invoice a
		JOIN tabel_toko b ON b.kd_toko = a.kdcabang
		WHERE inv_plg_id='$kopel' 
		ORDER BY inv_tanggal DESC limit 10");
        return $hsl;
    }
    
    function get_mypoint($kopel){
        $hsl = $this->db->query("SELECT b.*,c.nm_toko FROM tabelpoint b
        JOIN tabel_toko c ON c.kd_toko = SUBSTRING(b.nobukti,1,5)
        WHERE b.custno = '$kopel' ORDER BY b.tgl DESC limit 10");
        return $hsl;
    }
    
    function get_mypointdetail($faktur){
        $hsl = $this->db->query("SELECT a.*,c.nm_toko,if(a.layanan='1','Dine In',if(a.layanan='2','Take Away','Delivery Order')) as layan
        ,d.nm_barang,d.jumlah,d.harga,d.sub_total_jual
        FROM tabel_penjualan a 
        JOIN tabelpoint b ON b.nobukti = a.no_faktur_penjualan 
        JOIN tabel_toko c ON c.kd_toko = a.kdcabang
        JOIN tabel_rinci_penjualan d ON d.no_faktur_penjualan = a.no_faktur_penjualan
        WHERE a.no_faktur_penjualan = '$faktur'");
        return $hsl;
    }
    
    function get_mylistdo($kdkedai)
    {
        $hsl = $this->db->query("SELECT b.waktu_kirim,b.status_kirim,a.no_faktur_penjualan,mid(a.no_faktur_penjualan,8,7) as nota, b.nobukti, a.npelanggan, b.alamat, a.notelp, b.lat, b.lng,a.total_penjualan_sdiskon,a.diskon
	FROM tabel_penjualan a
	JOIN tbl_belanja1 b ON b.nobukti = a.noorder
	where a.kdcabang = '$kdkedai' AND a.layanan = '3' AND date(a.tgl_penjualan)=CURDATE() AND a.kdharga = 'KH01' AND a.noorder !='' AND a.selesai = '1'");
        return $hsl;
    }

    public function getPenjualanSelesai($nofaktur)
    {
        $this->db->where('no_faktur_penjualan', $nofaktur);
        return $this->db->get('tabel_penjualan');
    }

    public function getProdukDijual($nofaktur)
    {
        $this->db->where('no_faktur_penjualan', $nofaktur);
        $this->db->order_by('nourut');
        return $this->db->get('tabel_rinci_penjualan');
    }
    
    function get_menu_paket($nobukti){
        $this->db->select('b.menu_nama,a.qty,a.kdbrg')->where('a.nobukti', $nobukti)->join('tbl_menu b','b.menu_id = a.kdbrg1');
        return $this->db->get('tbl_belanja_detail_promo a');
    }

    function get_tatal_pelanggan()
    {
        $hsl = $this->db->query("SELECT COUNT(plg_id) AS tot_pelanggan FROM tbl_pelanggan");
        return $hsl;
    }

    function get_total_porsi_terjual_bulan_ini()
    {
        $hsl = $this->db->query("SELECT SUM(detail_porsi) AS total_porsi FROM tbl_invoice JOIN tbl_detail ON inv_no=detail_inv_no WHERE MONTH(inv_tanggal)=MONTH(CURDATE())");
        return $hsl;
    }

    function get_total_penjualan_bulan_lalu()
    {
        $hsl = $this->db->query("SELECT SUM(inv_total) AS total_penjualan FROM tbl_invoice WHERE MONTH(inv_tanggal)=MONTH(CURDATE())-1");
        return $hsl;
    }

    function get_total_penjualan_sekarang()
    {
        $hsl = $this->db->query("SELECT SUM(inv_total) AS total_penjualan FROM tbl_invoice WHERE MONTH(inv_tanggal)=MONTH(CURDATE())");
        return $hsl;
    }


    function get_grafik_penjualan()
    {
        $query = $this->db->query("SELECT DATE_FORMAT(inv_tanggal,'%d') AS tanggal,SUM(inv_total) AS total FROM tbl_invoice WHERE DATE_FORMAT(inv_tanggal,'%M %Y')=DATE_FORMAT(CURDATE(),'%M %Y') GROUP BY DAY(inv_tanggal)");

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $data) {
                $hasil[] = $data;
            }
            return $hasil;
        }
    }

    function get_all_rekening()
    {
        $hsl = $this->db->query("select * from tbl_rekening");
        return $hsl;
    }

    function get_all_order()
    {
        $hsl = $this->db->query("SELECT inv_no,DATE_FORMAT(inv_tanggal,'%d %M %Y') AS inv_tanggal,inv_plg_id,inv_plg_nama,inv_status,inv_total,inv_rek_id,inv_rek_bank FROM tbl_invoice ORDER BY DATE(inv_tanggal) DESC");
        return $hsl;
    }

    function update_order($kode, $status)
    {
        $hsl = $this->db->query("UPDATE tbl_invoice SET inv_status='$status' WHERE inv_no='$kode'");
        return $hsl;
    }

    function get_invoice($kdcabang)
    {
        $q = $this->db->query("SELECT MAX(RIGHT(nobukti,4)) AS kd_max FROM tbl_belanja1 where date(tgl)=CURDATE() and kdcabang='$kdcabang';");
        $kd = "";
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $k) {
                $tmp = ((int)$k->kd_max) + 1;
                $kd = sprintf("%04s", $tmp);
            }
        } else {
            $kd = "0001";
        }
        return $kdcabang . date('dmy') . $kd;
    }
    function order_produk($no_invoice, $total)
    {
        $kopel = $_COOKIE['kopel'];
        $ab = $this->db->query("select * from tbl_pelanggan where plg_id='$kopel'");
        $i = $ab->row_array();
        $kdcabang = $i['kdtoko'];
        $napel = $this->session->userdata('nama_pel');
        $this->db->query("INSERT INTO tbl_invoice (inv_no,inv_plg_id,inv_plg_nama,inv_status,inv_total,kdcabang) VALUES ('$no_invoice','$kopel','$napel','Menunggu Konfirmasi','$total','$kdcabang')");
        foreach ($this->cart->contents() as $item) {
            $data = array(
                'detail_menu_id'     =>    $item['id'],
                'detail_menu_nama'   =>    $item['name'],
                'detail_harjul'         =>    $item['price'],
                'detail_porsi'       =>    $item['qty'],
                'detail_subtotal'    =>    $item['subtotal'],
                'detail_inv_no'      =>    $no_invoice

            );
            $this->db->insert('tbl_detail', $data);
        }
        return true;
    }
    function updateorder($nobukti)
    {
        $kopel = $_COOKIE['kopel'];
        $napel = $this->session->userdata('nama_pel');
        $sha1 = sha1($nobukti);
        $tgl = date('Y-m-d');
        // $ab = $this->db->query("select * from tbl_pelanggan where plg_id='$kopel'");
        // $i = $ab->row_array();
        // $kdcabang = $i['kdtoko'];

        $querybelanja = $this->db->query("select promo,item,maxbonus,idpromo from tbl_belanja1 where nobukti='$nobukti'");
        $x = $querybelanja->row_array();
        $promo = $x['promo'];
        $item = $x['item'];
        $maxbonus = $x['maxbonus'];
        $kdpromo = $x['idpromo'];



        $this->db->query("update tbl_belanja1 set stat='6' where nobukti='$nobukti';");
        $this->db->query("update tbl_invoice set no_sha='$sha1', promo = '$promo' ,kdpromo = '$kdpromo',item = '$item',maxbonus = '$maxbonus' where inv_no='$nobukti';");
        $this->db->query("update tbl_pelanggan_promo set nobukti='$nobukti', tgl = '$tgl' ,stat = 1 where plg_id='$kopel' and kode_promo = '$kdpromo' and stat = 0;");
        return true;
    }
    function updateorderbatal($nobukti)
    {
        $kopel = $_COOKIE['kopel'];
        // $napel = $this->session->userdata('nama_pel');
        $sha1 = sha1($nobukti);

        $ab = $this->db->query("select * from tbl_pelanggan where plg_id='$kopel'");
        $i = $ab->row_array();
        $kdcabang = $i['kdtoko'];

        $querybelanja = $this->db->query("select promo,item,maxbonus,idpromo from tbl_belanja1 where nobukti='$nobukti'");
        $x = $querybelanja->row_array();
        $promo = $x['promo'];
        $item = $x['item'];
        $maxbonus = $x['maxbonus'];
        $kdpromo = $x['idpromo'];


        $this->db->query("UPDATE tbl_invoice SET stat='5' WHERE inv_no='$nobukti' and kdcabang='$kdcabang'");
        $this->db->query("update tbl_belanja1 set stat='5' where nobukti='$nobukti';");
        return true;
    }
    function order_produk1($no_invoice,$kdcabang)
    {
        $kopel = $_COOKIE['kopel'];
        $layanan = $this->session->userdata('typeorder');
        $tgl = date('Y-m-d');
        // $ab = $this->db->query("select * from tbl_pelanggan where plg_id='$kopel'");
        // $total = 0;
        // $i = $ab->row_array();
        // $kdcabang = $i['kdtoko'];
        $napel = strip_tags(str_replace("'", "", $this->session->userdata('nama_pel'))); ;
        $this->db->query("INSERT ignore INTO tbl_belanja1 (kdcabang,nobukti,tgl,custno,custname,ketstatus,layanan) VALUES ('$kdcabang','$no_invoice','$tgl','$kopel','$napel','Menunggu Konfirmasi','$layanan')");
        return true;
    }

    public function cek_menu_toping($kdbrg1, $kdbrg2)
    {
        return $this->db->query("SELECT * FROM tbl_menu WHERE kdbrg1='$kdbrg1' AND kdbrg2='$kdbrg2'");
    }

    public function cek_sudah_ada($kdbrg, $nobukti)
    {
        return $this->db->query("SELECT * FROM tbl_belanja_detail1 WHERE kdbrg='$kdbrg' AND nobukti='$nobukti'");
    }
    public function cek_sudah_adab($kdbrg, $nobukti, $jpromo)
    {
        return $this->db->query("SELECT * FROM tbl_belanja_detail1 WHERE kdbrg='$kdbrg' AND nobukti='$nobukti' and nourut='$jpromo';");
    }
    public function cek_sudah_ada_nota($kopel, $kdcabang)
    {
        $tgl = date('Y-m-d');
        return
            $this->db->query("SELECT a.*")
            ->join('tbl_belanja_detail1 AS b', 'a.nobukti = b.nobukti')
            ->where('a.tgl', $tgl)
            ->where('EXISTS (SELECT 1 FROM tbl_belanja_detail1 AS b WHERE a.nobukti=b.nobukti)')
            ->where('a.custno', $kopel)
            ->where('a.kdcabang', $kdcabang)
            ->where('a.netto>', 0)
            ->get('tbl_belanja1 AS a');
    }
    public function cek_sudah_ada_promo($kdbrg, $nobukti, $jpromo)
    {
        $kdtoko = $this->session->userdata('cabang');
        return $this->db->query("SELECT qty FROM tbl_belanja_detail1 WHERE kdbrg='$kdbrg' AND nobukti='$nobukti' AND jpromo = '$jpromo' AND kdcabang = '$kdtoko'");
    }
    public function cek_sudah_ada_promop($nobukti, $jpromo)
    {
        return $this->db->query("SELECT a.* FROM tbl_belanja_detail1 a
		WHERE a.nobukti='$nobukti' AND a.jpromo = '$jpromo';");
    }
    public function getbarang($kdbrg)
    {
        $kdcabang = $this->session->userdata('cabang');
        $tgl = date('Y-m-d');
        $this->db->where('kdharga', 'KH01');
        $this->db->where('kdbrg', $kdbrg);
        $this->db->where('kdcabang', $kdcabang);
        $this->db->where('tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"');
        return $this->db->get('hargajual');
    }
    public function getDataPenjualan($noresi, $custno)
    {
        $this->db->select('bruto,nppn,netto,ndisc,bayarpoint,custno,nobukti,layanan,ongkir,idpromo,kdcabang,jnspromo,item');
        $this->db->where('nobukti', $noresi);
        $this->db->where('custno', $custno);
        $this->db->where('stat', '0');
        return $this->db->get('tbl_belanja1');
    }
    public function getListPenjualan1($noresi)
    {
        return $this->db->query("SELECT a.kdbrg,b.menu_nama,a.qty,a.nobukti,a.nourut,a.harga,a.bruto,a.nppn,b.menu_gambar as gambar,a.jpromo FROM tbl_belanja_detail1 a
		left join tbl_menu b on a.kdbrg=b.menu_id
		WHERE nobukti='$noresi' ORDER BY nourut");
    }
    public function getListPenjualan($noresi)
    {
        return $this->db->query("SELECT a.kdbrg,a.nama_menu,a.qty,a.nobukti,a.nourut,a.harga,a.bruto,a.nppn,b.menu_gambar as gambar,a.kdpromo,a.jpromo,a.paket FROM tbl_belanja_detail1 a
		left join tbl_menu b on a.kdbrg=b.menu_id
		WHERE nobukti='$noresi' ORDER BY nourut");
    }

    public function getListPenjualanhapus($noresi, $kdbrg1)
    {
        return $this->db->query("SELECT a.kdbrg,a.nama_menu,a.qty,a.nobukti,a.nourut,a.kdpromo,a.harga,a.bruto,a.nppn,b.menu_gambar as gambar,a.jpromo FROM tbl_belanja_detail1 a
		left join tbl_menu b on a.kdbrg=b.menu_id
		WHERE nobukti='$noresi' AND a.kdbrg1='$kdbrg1' ORDER BY nourut")->result_array();
    }

    public function getTotalBelanja($noresi)
    {
        return $this->db->query("SELECT SUM(a.bruto) AS tot_bel,SUM(a.bruto+a.nppn) AS tot_belppn,b.ongkir,a.jpromo FROM tbl_belanja_detail1 a
		left join tbl_belanja1 b on a.nobukti=b.nobukti
		WHERE b.nobukti='$noresi'");
    }
    function set_pembayaran_cod($no_invoice)
    {
        $hsl = $this->db->query("UPDATE tbl_invoice SET inv_rek_id='COD',jnsbayar='1' WHERE inv_no='$no_invoice'");
        return $hsl;
    }

    function get_checkout($no_invoice)
    {
        $hsl = $this->db->query("SELECT ongkir,alamat,notelp,layanan,stat,inv_plg_id,if(layanan='1','Dine In',if(layanan='2','Take Away','Delivery Order')) as layan,inv_no,
        DATE_FORMAT(inv_tanggal,'%d/%m/%Y') AS tanggal,comment,comment_detail,feedback,inv_plg_nama,inv_status,kdcabang,
        inv_total,disc,ndisc,a.nppn,netto,inv_rek_id,inv_rek_no,inv_rek_bank,inv_rek_nama,inv_rek_cabang,detail_menu_id,detail_menu_nama,detail_harjul,
        detail_porsi,detail_subtotal,(detail_subtotal*(10/100)) as nppn FROM tbl_invoice a JOIN tbl_detail ON inv_no=detail_inv_no WHERE inv_no='$no_invoice'");
        return $hsl;
    }
    
    function get_nota($no_invoice)
    {
        $hsl = $this->db->query("SELECT inv_total,disc,ndisc,nppn,netto FROM tbl_invoice WHERE inv_no='$no_invoice'");
        return $hsl;
    }
    
    
    function get_checkout1($no_invoice)
    {
        $hsl = $this->db->query("SELECT ongkir,alamat,notelp,layanan,stat,inv_plg_id,if(layanan='1','Dine In',if(layanan='2','Take Away','Delivery Order')) as layan,inv_no,DATE_FORMAT(inv_tanggal,'%d/%m/%Y') AS tanggal,comment,comment_detail,feedback,inv_plg_nama,inv_status,kdcabang,inv_total,inv_rek_id,inv_rek_no,inv_rek_bank,inv_rek_nama,inv_rek_cabang,detail_menu_id,detail_menu_nama,detail_harjul,detail_porsi,detail_subtotal,(detail_subtotal*(10/100)) as nppn FROM tbl_invoice JOIN tbl_detail ON inv_no=detail_inv_no WHERE no_sha='$no_invoice'");
        return $hsl;
    }

    function get_tracker($no_invoice)
    {
        $hsl = $this->db->query("SELECT a.alamat,a.ongkir,a.notelp,a.layanan, a.inv_plg_id,if(a.layanan='1','Dine In',if(a.layanan='2','Take Away','Delivery Order')) as layan, 
		a.inv_no,DATE_FORMAT(a.inv_tanggal,'%d/%m/%Y') AS tanggal,a.comment, a.comment_detail, a.feedback, a.inv_plg_nama, a.stat, a.inv_status, a.kdcabang, a.inv_total,a.ongkir,ROUND(a.disc) as disc,a.ndisc
		,a.nppn,a.netto, a.inv_rek_id, a.inv_rek_no,c.waiter1,
		a.inv_rek_bank, a.inv_rek_nama, a.inv_rek_cabang, b.detail_menu_id, b.detail_menu_nama, b.detail_harjul, b.detail_porsi, b.detail_subtotal,a.nppn, c.stbayar
        FROM tbl_invoice a 
        JOIN tbl_detail b ON a.inv_no=b.detail_inv_no 
        LEFT JOIN tabel_penjualan c ON c.noorder = a.inv_no 
        WHERE inv_no='$no_invoice';");
        return $hsl;
    }
    
    function get_checkoutpenjualan($no_invoice){
		$hsl=$this->db->query("SELECT a.id_user,a.no_faktur_penjualan,DATE_FORMAT(a.tgl_penjualan,'%d/%m/%Y') AS tanggal,a.comment,a.comment_detail,a.feedback,a.npelanggan,a.kdcabang,a.netto,a.notelp,a.nbayar,a.nkembali,if(a.layanan='1','Dine In',if(a.layanan='2','Take Away','DO')) as layan,
		if(a.jnsbayar='1','Cash',if(a.jnsbayar='2','Debit',if(a.jnsbayar='3','Gopay',if(a.jnsbayar='4','Ovo',if(a.jnsbayar='5','Shopeepay',if(a.jnsbayar='6','Shopeepay',if(a.jnsbayar='7','OVO','Q-Ris'))))))) as jnsbayar,a.nomeja,
		b.kd_barang,b.nm_barang,b.harga,b.jumlah,a.ndisc,b.diskonrp,b.diskonpersen,b.sub_total_jual,a.nppn,a.ongkir FROM tabel_penjualan a
		left JOIN tabel_rinci_penjualan b ON a.no_faktur_penjualan=b.no_faktur_penjualan
		WHERE a.no_sha='$no_invoice'");
		return $hsl;
	}

    function get_checkoutnota($no_invoice)
    {
        $hsl = $this->db->query("SELECT a.no_faktur_penjualan,DATE_FORMAT(a.tgl_penjualan,'%d/%m/%Y') AS tanggal,a.comment,a.comment_detail,a.feedback,a.npelanggan,a.kdcabang,a.netto,a.notelp,a.nbayar,a.nkembali,if(a.layanan='1','Dine In',if(a.layanan='2','Take Away','DO')) as layan,
		if(a.jnsbayar='1','Cash',if(a.jnsbayar='2','Debit',if(a.jnsbayar='3','Gopay',if(a.jnsbayar='4','Ovo',if(a.jnsbayar='5','Shopeepay',if(a.jnsbayar='6','Shopeepay',if(a.jnsbayar='7','OVO','Q-Ris'))))))) as jnsbayar,a.nomeja,c.nm_user,
		b.kd_barang,b.nm_barang,b.harga,b.jumlah,b.diskonrp,b.diskonpersen,b.sub_total_jual,a.nppn FROM tabel_penjualan a
		left JOIN tabel_rinci_penjualan b ON a.no_faktur_penjualan=b.no_faktur_penjualan
		left join tabel_user c on a.id_user=c.id_user
		WHERE a.no_sha='$no_invoice'");
        return $hsl;
    }
    function get_report($tglreport, $kode)
    {
        if ($kode != '0') {
            $hsl = $this->db->query("SELECT a.* FROM reportharian a,tabel_toko b   WHERE a.tgl='$tglreport' and b.kd_toko=a.kdcabang and b.divisi='$kode';");
            return $hsl;
        } else {
            $hsl = $this->db->query("SELECT * FROM reportharian WHERE tgl='$tglreport';");
            return $hsl;
        }
    }
    function get_ttl($no_invoice)
    {
        $hsl = $this->db->query("SELECT inv_no,DATE_FORMAT(inv_tanggal,'%d/%m/%Y') AS tanggal,SUM(detail_subtotal) as total FROM tbl_invoice JOIN tbl_detail ON inv_no=detail_inv_no WHERE inv_no='$no_invoice'");
        return $hsl;
    }
    function get_rekening($pem_id)
    {
        $hsl = $this->db->query("select * from tbl_rekening where rek_id='$pem_id'");
        return $hsl;
    }
    function get_cabang($kopel)
    {
        $hsl = $this->db->query("select kdtoko from tbl_pelanggan where plg_id='$kopel'");
        return $hsl;
    }
    function set_pembayaran_transfer($no_invoice, $rek_id, $rek_no, $rek_bank, $rek_nama, $rek_cabang, $jnsbayar)
    {
        $kopel = $_COOKIE['kopel'];
        $ab = $this->db->query("select a.*,b.* from tbl_pelanggan a,tabel_toko b  where a.kdtoko=b.kd_toko and a.plg_id='$kopel'");
        $i = $ab->row_array();
        $kdst = $i['stat'];
        $hsl = $this->db->query("UPDATE tbl_invoice SET inv_rek_id='$rek_id',inv_rek_no='$rek_no',inv_rek_bank='$rek_bank',inv_rek_nama='$rek_nama',inv_rek_cabang='$rek_cabang',jnsbayar='$jnsbayar',stat='2' WHERE inv_no='$no_invoice'");
        return $hsl;
    }

    function hapus_order($kode)
    {
        $this->db->trans_start();
        $this->db->query("delete from tbl_detail where detail_inv_no='$kode'");
        $this->db->query("delete from tbl_invoice where inv_no='$kode'");
        $this->db->trans_complete();
        if ($this->db->trans_status() == true)
            return true;
        else
            return false;
    }

    function kirim_struk($nobukti)
    {
        $struk = $this->db->query("SELECT status_wa,layanan FROM tbl_belanja1 WHERE nobukti = '$nobukti'")->row();
        if($struk->layanan == 3){
         
        if ($struk->status_wa == 0) {
            $DB1 = $this->load->database('db3', TRUE);
            $kopel = $_COOKIE['kopel'];
            // $ab = $this->db->query("select * from tbl_pelanggan where plg_id='$kopel'");
            // $i = $ab->row();
            // $no = $i->plg_notelp;
            // $kdtoko = $i->kdtoko;
            
            
            $no = $_COOKIE['telepon'];
            $kdtoko = $this->session->userdata('cabang');
            
            $wakd = $this->db->query("select tlp_toko from tabel_toko where kd_toko='$kdtoko'");
            $wa = $wakd->row();
            $notoko = $wa->tlp_toko;
            $link = sha1($nobukti);
            $text = 'MIE NYINYIR Pelanggan '.$no.' Order dengan Rincian klik https://tinyurl.com/2fppu9mt'.'/'. $link;
            
            $textkd = 'MIE NYINYIR Pelanggan '.$no.' Order dengan Rincian klik https://tinyurl.com/2fppu9mt'.'/'. $link;
            $isiwa = $DB1->query("INSERT INTO outbox(wa_mode, wa_no, wa_text) VALUES(0,'$notoko','$textkd')");
            $this->db->query("UPDATE tbl_belanja1 set status_wa = 1 WHERE nobukti  = '$nobukti'");
        }
        }
    }
    
    function kirim_struk1($nobukti){
	    $DB1 = $this->load->database('db3', TRUE);
	    $kopel = $this->session->userdata('kopel');
        $ab = $this->db->query("select * from tbl_pelanggan where plg_id='$kopel'");
        $i = $ab->row_array();
        $no = $i['plg_notelp'];
        $kdtoko=$i['kdtoko'];
        $wakd=$this->db->query("select tlp_toko from tabel_toko where kd_toko='$kdtoko'");
        $wa=$wakd->row_array();
        $nowa=$wa['tlp_toko'];
        $textkd = 'MIE NYINYIR Pelanggan '.$no.' Order dengan Rincian klik https://tinyurl.com/2fppu9mt'.'/'. sha1($nobukti);
        return  $isiwa=$DB1->query("INSERT INTO outbox(wa_mode, wa_no, wa_text) VALUES(0,'$nowa','$textkd')");
     
	}
	
	function get_list_cabang() {
        $this->db->distinct();
        $this->db->select('a.kdcabang, b.nm_toko, b.stat'); 
        $this->db->from('tabel_penjualan a');
        $this->db->join('tabel_toko b', 'a.kdcabang = b.kdcabang', 'left');
        $this->db->order_by('a.kdcabang', 'ASC');
        return $this->db->get()->result();
    }

    function get_penjualan_filtered($limit, $start, $kdcabang, $search = null, $tgl_awal = null, $tgl_akhir = null, $order_by = 'DESC', $status = null) {
        $this->db->select('*');
        $this->db->from('tabel_penjualan');
        
        if (!empty($kdcabang)) {
            $this->db->where('kdcabang', $kdcabang);
        }

        if (!empty($tgl_awal) && !empty($tgl_akhir)) {
            $this->db->where('tgl_penjualan >=', $tgl_awal);
            $this->db->where('tgl_penjualan <=', $tgl_akhir);
        }

        if ($status !== null && $status !== '') {
            $this->db->where('selesai', $status); 
        }
        
        if (!empty($search)) {
            $search_safe = $this->db->escape_like_str($search);
            $this->db->where("(no_faktur_penjualan LIKE '%$search_safe%' OR npelanggan LIKE '%$search_safe%')");
        }

        $this->db->order_by('tgl_penjualan', $order_by);
        $this->db->order_by('waktu', 'DESC');
        
        $this->db->limit($limit, $start);
        return $this->db->get()->result();
    }

    function count_penjualan_filtered($kdcabang, $search = null, $tgl_awal = null, $tgl_akhir = null, $status = null) {
        $this->db->from('tabel_penjualan');
        
        if (!empty($kdcabang)) {
            $this->db->where('kdcabang', $kdcabang);
        }

        if (!empty($tgl_awal) && !empty($tgl_akhir)) {
            $this->db->where('tgl_penjualan >=', $tgl_awal);
            $this->db->where('tgl_penjualan <=', $tgl_akhir);
        }

        if ($status !== null && $status !== '') {
            $this->db->where('selesai', $status);
        }
        if (!empty($search)) {
            $search_safe = $this->db->escape_like_str($search);
            $this->db->where("(no_faktur_penjualan LIKE '%$search_safe%' OR npelanggan LIKE '%$search_safe%')");
        }

        return $this->db->count_all_results();
    }

    function get_counts_per_branch($search = null, $tgl_awal = null, $tgl_akhir = null, $status = null) {
        $this->db->select('kdcabang, COUNT(*) as total');
        $this->db->from('tabel_penjualan');
        
        if (!empty($tgl_awal) && !empty($tgl_akhir)) {
            $this->db->where('tgl_penjualan >=', $tgl_awal);
            $this->db->where('tgl_penjualan <=', $tgl_akhir);
        }

        if ($status !== null && $status !== '') {
            $this->db->where('selesai', $status);
        }

        if (!empty($search)) {
            $this->db->group_start();
            $this->db->like('no_faktur_penjualan', $search);
            $this->db->or_like('npelanggan', $search);
            $this->db->group_end();
        }
        
        $this->db->group_by('kdcabang');
        return $this->db->get()->result();
    }
    
    // untuk adm ubah status selesai ke batal
    function update_status_batal($kode){
        $this->db->set('selesai', '4');
        $this->db->set('keter', 'Dibatalkan oleh Admin'); 
        $this->db->where('no_faktur_penjualan', $kode);
        return $this->db->update('tabel_penjualan');
    }
   
}