<?php
class M_order extends CI_Model{

	function get_myefood($kopel){
		$hsl=$this->db->query("SELECT * FROM tbl_invoice WHERE inv_plg_id='$kopel' ORDER BY DATE(inv_tanggal) DESC");
		return $hsl;
	}

	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,3)) AS kd_max FROM tbl_belanja where date(tgl)=CURDATE()");
        $kd = "";
        if($q->num_rows()>0){
            foreach($q->result() as $k){
                $tmp = ((int)$k->kd_max)+1;
                $kd = sprintf("%03s", $tmp);
            }
        }else{
            $kd = "001";
        }
        return $kdcabang.date('dmy').$kd;
	}
	function order_produk($no_invoice,$total){
		$kopel=$this->session->userdata('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=$this->session->userdata('kopel');
		$ab=$this->db->query("select * from tbl_pelanggan where plg_id='$kopel'");
		$i=$ab->row_array();
		$kdcabang=$i['kdtoko'];
		$sha1 = sha1($nobukti);
		$napel=$this->session->userdata('nama_pel');
		$this->db->query("update tbl_belanja set stat='6' where nobukti='$nobukti';");
		$this->db->query("update tbl_invoice set no_sha='$sha1' where inv_no='$nobukti';");
		return true;
	}
	function order_produk1($no_invoice){
		$kopel=$this->session->userdata('kopel');
		$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=$this->session->userdata('nama_pel');
		$this->db->query("INSERT ignore INTO tbl_belanja (kdcabang,nobukti,tgl,custno,custname,ketstatus) VALUES ('$kdcabang','$no_invoice','$tgl','$kopel','$napel','Menunggu Konfirmasi')");
		return true;
	}
	public function cek_sudah_ada($kdbrg, $nobukti) {
		return $this->db->query("SELECT * FROM tbl_belanja_detail WHERE kdbrg='$kdbrg' AND nobukti='$nobukti'");
	}
	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->where('nobukti', $noresi);
		$this->db->where('custno', $custno);
		$this->db->where('stat', '0');
		return $this->db->get('tbl_belanja');
	}
	public function getListPenjualan($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 FROM tbl_belanja_detail a
		left join tbl_menu b on a.kdbrg=b.menu_id
		WHERE nobukti='$noresi' ORDER BY nourut");
	}
	public function getTotalBelanja($noresi) {
		return $this->db->query("SELECT SUM(bruto) AS tot_bel,SUM(bruto+nppn) AS tot_belppn FROM tbl_belanja_detail WHERE 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 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 inv_no='$no_invoice'");
		return $hsl;
    }			
   function get_checkout1($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_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=$this->session->userdata('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){
	    $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'];
        $text = 'https://enambelasapp.my.id/eorder/mobile/menu/lihat_invoice/'. sha1($nobukti);
        $DB1 = $this->load->database('db3', TRUE);
		return $DB1->query("INSERT INTO outbox(wa_mode, wa_no, wa_text) VALUES(0,'$no','$text')");
	}


}