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

class Laporan_model extends CI_Model {

	public function get_toko() {
	    $kdtoko = $this->session->userdata('kd_toko');
		$query = $this->db->query("SELECT * FROM tabel_toko where kd_toko='$kdtoko'");
		return $query->row();
	}
	public function get_tokoku1($kdtoko) {
		$query = $this->db->query("SELECT * FROM tabel_toko1 where kd_toko='$$kdtoko';");
		return $query->row();
	}
	public function get_tokoku($kdtoko) {
		$query = $this->db->query("SELECT * FROM tabel_toko where kd_toko='$kdtoko';");
		return $query->row();
	}

	public function getDataRetur($tanggal) {
		$this->db->where('tgl', $tanggal);
		return $this->db->get('tabel_retur');
	}

	public function getDataReturFilter($tgl_awal, $tgl_akhir) {
		$this->db->where('tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"');
		return $this->db->get('tabel_retur');
	}

	public function getDataPengeluaranRekap($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('*')
			->where('tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('kdcabang', $kdtoko)
			->group_by('tgl')
			->get('tabel_biaya');
	}
	public function getDataalurkasRekap($tgl_awal, $tgl_akhir) {
		return $this->db3->select('a.nilai,a.rekno,a.nilai,a.cc,b.tgl,b.reff,a.nobukti')
			->join('kasumumheader as b','a.nobukti=b.nobukti','left')	
			->where('b.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->group_by('b.tgl')
			->get('kasumumdetail as a');
	}

	
	public function getDataalurkasRinci($tgl_awal, $tgl_akhir) {
	    return $this->db3->select('a.nilai,a.rekno,a.nilai,a.cc,b.tgl,b.reff,a.nobukti,c.nmcolector')
			->join('kasumumheader as b','a.nobukti=b.nobukti','left')	
			->join('colector as c','c.kdcolector=b.reff','left')	
			->where('b.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->group_by('b.tgl')
			->group_by('b.reff')
			->get('kasumumdetail as a');
	}
	public function getDatamasukTransaksiFilter($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
	    if($hak !='kasir') {
		return $this->db4->select('a.*,b.*,if(a.st="6","Aprovel","Input") as stat')
			->join('stokdetaila AS b', 'a.nobukti = b.nobukti')
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM stokdetaila AS b WHERE a.nobukti=b.nobukti)')
			->group_by('a.nobukti')
			->get('stokheadera AS a');
	    } else  {
	        return $this->db4->select('a.*,b.*,if(a.st="6","Aprovel","Input") as stat')
			->join('stokdetaila AS b', 'a.nobukti = b.nobukti')
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM stokdetaila AS b WHERE a.nobukti=b.nobukti)')
			->where('a.kegd', $kdtoko)
			->group_by('a.nobukti')
			->get('stokheadera AS a');
	    }
	}
	public function getDatamasukTransaksi($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
	    if($hak !='kasir' ) {
		return $this->db4->select('a.*,b.*,if(a.st="6","Aprovel","Input") as stat')
			->join('stokdetaila AS b', 'a.nobukti = b.nobukti')
			->where('a.tgl', $tanggal)
			->where('EXISTS (SELECT 1 FROM stokdetaila AS b WHERE a.nobukti=b.nobukti)')
			->group_by('a.nobukti')
			->get('stokheadera AS a');
	    } else {
	        return $this->db4->select('a.*,b.*,if(a.st="6","Aprovel","Input") as stat')
			->join('stokdetaila AS b', 'a.nobukti = b.nobukti')
			->where('a.tgl', $tanggal)
			->where('EXISTS (SELECT 1 FROM stokdetaila AS b WHERE a.nobukti=b.nobukti)')
			->where('a.kegd', $kdtoko)
			->group_by('a.nobukti')
			->get('stokheadera AS a');
	    }	
	}
	
	public function getDataPengeluaranRinci($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
            ->join('tabel_toko as b','a.kdcabang =b.kd_toko','left')		
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('b.stat','2')
			->order_by('a.tgl')
			->order_by('a.kdcabang')
			->get('tabel_biaya as a');
	}
	public function getDataPengeluaranRincip($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
            ->join('tabel_toko as b','a.kdcabang =b.kd_toko','left')		
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('a.kode','A')
			->where('a.nppn >',0)
			->where('b.stat','2')
			->order_by('a.tgl')
			->order_by('a.kdcabang')
			->get('rekapharian as a');
	}

	public function getNilaiPersediaan() {
		return $this->db->select('a.kd_barang,a.nm_barang,a.hrg_jual,a.hrg_beli,b.stok,a.modal_per_porsi')
			->join('tabel_stok_toko AS b', 'a.kd_barang = b.kd_barang', 'LEFT')
			->where('b.stok > 0')
			->order_by('a.kd_barang')
			->get('tabel_barang AS a');
	}

	public function getDataPembelian($tanggal) {
		return $this->db->select('a.no_faktur_pembelian,a.tgl_pembelian,b.kd_barang,b.nm_barang,b.satuan,b.jumlah,b.harga,b.sub_total_beli')
			->join('tabel_rinci_pembelian AS b', 'a.no_faktur_pembelian = b.no_faktur_pembelian')
			->where('a.tgl_pembelian', $tanggal)
			->where('a.selesai', '1')
			->order_by('a.no_faktur_pembelian')
			->get('tabel_pembelian AS a');
	}

	public function getDataPembelianFilter($tgl_awal, $tgl_akhir) {
		return $this->db->select('a.no_faktur_pembelian,a.tgl_pembelian,b.kd_barang,b.nm_barang,b.satuan,b.jumlah,b.harga,b.sub_total_beli')
			->join('tabel_rinci_pembelian AS b', 'a.no_faktur_pembelian = b.no_faktur_pembelian')
			->where('a.tgl_pembelian BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('a.selesai', '1')
			->get('tabel_pembelian AS a');
	}

	public function getDataPenjualanTransaksiFilter($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->group_by('a.no_faktur_penjualan')
			->get('tabel_penjualan AS a');
	}

	public function getDataPenjualanTransaksi($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->group_by('a.no_faktur_penjualan')
			->get('tabel_penjualan AS a');
	}
	public function getkdpromo() {
	    $this->db->order_by('idprogram,tglawal');
		return $this->db->get('promoheader');
	}
	public function getDataPenjualanpromo($tanggal,$kdpromo) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->join('tabel_penjualan AS a', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan', $tanggal)
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			 ->where('a.promo', '2')
			 ->where('a.idpromo', $kdpromo)
			 ->where('b.kdpromo', $kdpromo)
			->get('tabel_rinci_penjualan AS b');
	}
	public function getDataPenjualanpromoFilter($tgl_awal, $tgl_akhir,$kdpromo,$kdcabang) {
	    $kdtoko = $kdcabang;
		return $this->db->select('a.*,b.*')
			->join('tabel_penjualan AS a', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('a.selesai', '1')
			 ->where('a.promo', '2')
			 ->where('a.idpromo', $kdpromo)
			 ->where('b.kdpromo', $kdpromo)
			 ->where('a.kdcabang', $kdcabang)
			 ->where('b.jumlah>', 0)
			 ->order_by('a.kdcabang')
			->get('tabel_rinci_penjualan AS b');
	}
    public function getDataPenjualanpromoFilter1($tgl_awal, $tgl_akhir,$kdpromo,$kdcabang) {
	    $kdtoko = $this->session->userdata('kd_toko');
		    return $this->db->select('a.*')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('a.selesai', '1')
			 ->where('a.promo', '2')
			 ->where('a.idpromo', $kdpromo)
			 ->where('a.netto>', '0')
			 ->where('a.kdcabang', $kdcabang)
			 ->order_by('a.kdcabang')
			->get('tabel_penjualan AS a');
	}
	public function getDataPenjualanBarang2($tanggal,$kdtoko) {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,
		                        SUM(b.jumlah) as tqyt,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",if(b.jnsbarang<>"JP1",b.jumlah,0),0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",if(b.jnsbarang="JP1",b.jumlah,0),0),0),0)) AS prex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(a.promo="1",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                 SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",if(b.jnsbarang<>"JP1",(b.sub_total_jual),0),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",if(b.jnsbarang="JP1",(b.sub_total_jual),0),0),0),0)) AS rprex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(a.promo="1",(b.sub_total_jual),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",(b.sub_total_jual),0),0)) AS rpr
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->group_by('b.kd_barang')
			->order_by('c.nolap')
			->get('tabel_penjualan AS a');
	}
	public function getDataPenjualanBarangFilter2($tgl_awal, $tgl_akhir,$jual,$kdtoko) {
	     if ($jual=='Z') {
	        if($kdtoko=='99') { 
	                return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,a.kdcabang,d.keterangan,b.jnsbarang,e.kd_toko,e.nm_toko,
		                        SUM(b.jumlah) as tqty,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",b.jumlah,0),0),0)) AS prex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="P"),b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="B"),b.jumlah,0),0)) AS bpr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrg,
                                 SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rprex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="P"),b.sub_total_jual,0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="B"),b.sub_total_jual,0),0)) AS brpr
                                ')
                                        
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')
				->join('tabel_toko as e','a.kdcabang=e.kd_toko','left')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang !=', 'TRI01')
			->order_by('c.nolap')
			->order_by('b.jnsbarang')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    } 
	    if($kdtoko=='98') { 
	                return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,a.kdcabang,d.keterangan,c.jenismenu jnsbarang,e.nm_toko,
	                            SUM(b.jumlah) as tqty,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",b.jumlah,0),0),0)) AS prex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="P"),b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="B"),b.jumlah,0),0)) AS bpr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrg,
                                 SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rprex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrd,
                                 SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="P"),b.sub_total_jual,0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="B"),b.sub_total_jual,0),0)) AS brpr
                                ')
                                        
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=c.jenismenu','left')
			->join('tabel_toko as e','e.kd_toko=a.kdcabang','left')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang !=', 'TRI01')
			->order_by('a.kdcabang')
			->order_by('c.nolap')
			->order_by('b.jnsbarang')
			->group_by('a.kdcabang')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    }
	    
	    if($kdtoko<>'98' && $kdtoko<>'99') { 
	      return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,a.kdcabang,d.keterangan,b.jnsbarang,e.kd_toko,e.nm_toko,
		                        SUM(b.jumlah) as tqty,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",b.jumlah,0),0),0)) AS prex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="P"),b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="B"),b.jumlah,0),0)) AS bpr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrg,
                                 SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rprex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="P"),b.sub_total_jual,0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="B"),b.sub_total_jual,0),0)) AS brpr
                                ')
                                        
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')
			->join('tabel_toko as e','e.kd_toko=a.kdcabang','left')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->order_by('c.nolap')
			->order_by('b.jnsbarang')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');  
	    } 
	     }
	    if ($jual !='Z') {
	if($kdtoko=='99') { 
	                return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,a.kdcabang,d.keterangan,b.jnsbarang,e.kd_toko,e.nm_toko,
		                        SUM(b.jumlah) as tqty,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",b.jumlah,0),0),0)) AS prex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="P"),b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="B"),b.jumlah,0),0)) AS bpr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrg,
                                 SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rprex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrd,
                                 SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="P"),b.sub_total_jual,0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="B"),b.sub_total_jual,0),0)) AS brpr
                                ')
                                        
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')
			 ->join('tabel_toko as e','e.kd_toko=a.kdcabang','left')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang !=', 'TRI01')
			->where('b.idmenu', $jual)
			->order_by('c.nolap')
			->order_by('b.jnsbarang')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    } 
	    if($kdtoko=='98') { 
	                return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,a.kdcabang,d.keterangan,b.jnsbarang,e.kd_toko,e.nm_toko,
		                        SUM(b.jumlah) as tqty,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",b.jumlah,0),0),0)) AS prex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="P"),b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="B"),b.jumlah,0),0)) AS bpr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrg,
                                 SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rprex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrd,
                                 SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="P"),b.sub_total_jual,0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="B"),b.sub_total_jual,0),0)) AS brpr
                                ')
                                        
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
	        ->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')			
	        	->join('tabel_menu as e','a.kdcabang=e.kd_toko','left')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang !=', 'TRI01')
			->where('b.idmenu', $jual)
			->order_by('a.kdcabang')
			->order_by('c.nolap')
			->order_by('b.jnsbarang')
			->group_by('a.kdcabang')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    }
	    
	    if($kdtoko<>'98' && $kdtoko<>'99') { 
	      return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,a.kdcabang,d.keterangan,b.jnsbarang,e.kd_toko,e.nm_toko,
		                        SUM(b.jumlah) as tqty,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",b.jumlah,0),0),0)) AS prex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="P"),b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.jpromo="B"),b.jumlah,0),0)) AS bpr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="1",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrg,
                                 SUM(IF(a.kdharga="KH01",IF(a.layanan="2",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rprex,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.sub_total_jual),0),0),0)) AS rrd,
                                 SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="P"),b.sub_total_jual,0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and jpromo="B"),b.sub_total_jual,0),0)) AS brpr
                                ')
                                        
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')
			->join('tabel_toko as e','e.kd_toko=a.kdcabang','left')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('b.idmenu', $jual)
			->order_by('c.nolap')
			->order_by('b.jnsbarang')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');  
	    } 
	     }
	 
	}
	
		public function getDataPenjualanBarangFilter1p1($tgl_awal, $tgl_akhir,$jual,$kdtoko) {
	   // $kdtoko = $this->session->userdata('kd_toko');
	    
		return $this->db->select('a.no_faktur_penjualan,b.kdbrg, b.kdbrg1,c.nama_menu as nm_barang,0 AS jum_retur,
		                        SUM(b.qty) as jum_item,
		                        0 as jum_rp,
		                        0 as brt_rp,
		                        0 as ppn_rp,
		                        sum(qty) as pk,
		                        0 as rpk
                                ')
                                
			->join('tabel_rinci_promo AS b', 'a.no_faktur_penjualan = b.nobukti')
			->join('tabel_menu as c','b.kdbrg1=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_promo AS b WHERE a.no_faktur_penjualan=b.nobukti)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('b.kdbrg', '9A95')
			->order_by('c.nolap')
			->group_by('b.kdbrg')
			->group_by('b.kdbrg1')
			->get('tabel_penjualan AS a');
	    
	}
	public function getDataPenjualanBarangFilter1p2($tgl_awal, $tgl_akhir,$jual,$kdtoko) {
	   // $kdtoko = $this->session->userdata('kd_toko');
	    
		return $this->db->select('a.no_faktur_penjualan,b.kdbrg, b.kdbrg1,c.nama_menu as nm_barang,0 AS jum_retur,
		                        SUM(b.qty) as jum_item,
		                        0 as jum_rp,
		                        0 as brt_rp,
		                        0 as ppn_rp,
		                        sum(qty) as pk,
		                        0 as rpk
                                ')
                                
			->join('tabel_rinci_promo AS b', 'a.no_faktur_penjualan = b.nobukti')
			->join('tabel_menu as c','b.kdbrg1=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_promo AS b WHERE a.no_faktur_penjualan=b.nobukti)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('b.kdbrg', '9A96')
			->order_by('c.nolap')
			->group_by('b.kdbrg')
			->group_by('b.kdbrg1')
			->get('tabel_penjualan AS a');
	    
	}
		public function getDataPenjualanBarangFilter1p($tgl_awal, $tgl_akhir,$jual,$kdtoko) {
	   // $kdtoko = $this->session->userdata('kd_toko');
	    
		return $this->db->select('a.no_faktur_penjualan,b.kdbrg, b.kdbrg1,c.nama_menu as nm_barang,0 AS jum_retur,
		                        SUM(b.qty) as jum_item,
		                        0 as jum_rp,
		                        0 as brt_rp,
		                        0 as ppn_rp,
		                        sum(qty) as pk,
		                        0 as rpk
                                ')
                                
			->join('tabel_rinci_promo AS b', 'a.no_faktur_penjualan = b.nobukti')
			->join('tabel_menu as c','b.kdbrg1=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_promo AS b WHERE a.no_faktur_penjualan=b.nobukti)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->order_by('c.nolap')
			->group_by('b.kdbrg')
			->group_by('b.kdbrg1')
			->get('tabel_penjualan AS a');
	    
	}
	
	public function getDataPenjualanBarangFilter($tgl_awal, $tgl_akhir,$jual) {
	    $kdtoko = $this->session->userdata('kd_toko');
	     if ($jual=='s3') {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,d.keterangan as ketmenu,e.nm_toko,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.jumlah-b.qty),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",((b.jumlah-b.qty)*b.harga),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.qty*b.harga1),0),0)) AS rrg1
                                
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')
			->join('tabel_toko as e','a.kdcabang=e.kd_toko','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    } 
	    if ($jual=='s1') {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,d.keterangan as ketmenu,e.nm_toko,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.nppn) as ppn_rp,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.jumlah-b.qty),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",((b.jumlah-b.qty)*b.harga),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.qty*b.harga1),0),0)) AS rrg1
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')
			->join('tabel_toko as e','a.kdcabang=e.kd_toko','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('a.waktu <', '18:01:00')
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    } 
	   if ($jual=='s2')  {
	       return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,d.keterangan as ketmenu,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.nppn) as ppn_rp,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.jumlah-b.qty),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",((b.jumlah-b.qty)*b.harga),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.qty*b.harga1),0),0)) AS rrg1
                                ')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan','left')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->join('jenismenu as d','d.kdjenis=b.jnsbarang','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('a.waktu >=', '18:01:00')
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	       
	   }
	    
	}
	
	public function getDataPenjualanBarangexcelall($tgl_awal, $tgl_akhir,$jual) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,b.nm_barang,SUM(b.retur) AS jum_retur,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.jumlah-b.qty),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",((b.jumlah-b.qty)*b.harga),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.qty*b.harga1),0),0)) AS rrg1
                                
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	     
	}
	public function getDataPenjualanBarangexcelsiang($tgl_awal, $tgl_akhir,$jual) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,b.nm_barang,SUM(b.retur) AS jum_returs,
		                        SUM(b.jumlah) as tqyts,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rps,
		                        SUM(b.sub_total_jual) as brt_rps,
		                        SUM(b.nppn) as ppn_rps,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gfs,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS grs,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sfs,
		                        SUM(b.sub_total_jual) as brt_rps,
		                        SUM(b.nppn) as ppn_rps,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rgs,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rds,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.jumlah-b.qty),0),0)) AS prs,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1s,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgfs,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgrs,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsfs,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrgs,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrds,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",((b.jumlah-b.qty)*b.harga),0),0)) AS rprs,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.qty*b.harga1),0),0)) AS rrg1s
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('a.waktu <', '18:01:00')
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	}
	public function getDataPenjualanBarangexcelmalam($tgl_awal, $tgl_akhir,$jual) {
	        $kdtoko = $this->session->userdata('kd_toko');
	       return $this->db->select('a.no_faktur_penjualan, b.kd_barang,b.nm_barang,SUM(b.retur) AS jum_returm,
		                        SUM(b.jumlah) as jum_itemm,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rpm,
		                        SUM(b.sub_total_jual) as brt_rpm,
		                        SUM(b.nppn) as ppn_rpm,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gfm,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS grm,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sfm,
		                        SUM(b.sub_total_jual) as brt_rpm,
		                        SUM(b.nppn) as ppn_rpm,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rgm,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rdm,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.jumlah-b.qty),0),0)) AS prm,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1m,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgfm,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgrm,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsfm,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrgm,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",(b.jumlah*b.harga),0),0),0)) AS rrdm,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",((b.jumlah-b.qty)*b.harga),0),0)) AS rprm,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.qty*b.harga1),0),0)) AS rrg1m
                                ')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan','left')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('a.waktu >=', '18:01:00')
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	}	
	

	public function getDataPenjualanBarangFilter1($tgl_awal, $tgl_akhir,$jual,$kdtoko) {
	     if ($jual=='s3') {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF((b.kdpromo=""),b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF((a.layanan="3" and b.jpromo<>"B"),b.jumlah,0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(((b.kdpromo<>"" and b.kdpromo<>"048" and b.kdpromo<>"054") and b.jpromo="P"),(b.jumlah-b.qty),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF(((b.kdpromo<>"" and b.kdpromo<>"048") and b.jpromo="B"),(b.jumlah-b.qty),0),0)) AS bpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo="048"),(b.jumlah-b.qty),0),0)) AS ky,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF((b.kdpromo="" and b.jpromo=""),(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF((a.layanan="3" and b.jpromo<>"B"),(b.jumlah*b.harga),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(((b.kdpromo<>"" and b.kdpromo<>"048" and b.kdpromo<>"054") and b.jpromo="P") ,(((b.jumlah-b.qty)*b.harga)-b.diskonrp),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(((b.kdpromo<>"" and b.kdpromo<>"048") and b.jpromo="B") ,(((b.jumlah-b.qty)*b.harga)-b.diskonrp),0),0)) AS brpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo="048") ,(((b.jumlah-b.qty)*b.harga)-b.diskonrp),0),0)) AS rky,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" and b.Jpromo=""),(b.qty*b.harga1),0),0)) AS rrg1
                                
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    } 
	    if ($jual=='s1') {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(a.promo="1",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo="",b.jumlah,0),0),0)) AS rgp,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo<>"",b.jumlah,0),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",(b.sub_total_jual),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(a.promo="1",(b.sub_total_jual),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo<>"",b.sub_total_jual,0),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo="",b.sub_total_jual,0),0),0)) AS rrgp
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('a.waktu <', '18:01:00')
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	    } 
	   if ($jual=='s2')  {
	      	return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
								SUM(b.sub_total_jual) as brt_rp,
								SUM(b.nppn) as ppn_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(a.promo="1",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo="",b.jumlah,0),0),0)) AS rgp,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo<>"",b.jumlah,0),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF(a.promo="1",(b.sub_total_jual),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(a.promo="1",(b.sub_total_jual),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo<>"",b.sub_total_jual,0),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",if(b.kdpromo="",b.sub_total_jual,0),0),0)) AS rrgp
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tgl_akhir)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->where('a.waktu >=', '18:01:00')
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	       
	   }
	    
	}
	

	public function getDataPenjualanBarang($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.nppn) as ppn_rp,
		                        SUM(b.sub_total_jual) as brt_rp,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
		                        SUM(b.sub_total_jual) as brt_rp,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF((b.kdpromo="" and b.jpromo=""),b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.jumlah-b.qty),0),0)) AS pr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",b.qty,0),0)) AS rg1,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF((b.kdpromo="" and b.jpromo=""),(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF((b.kdpromo="" and b.jpromo="P"),(b.jumlah*b.harga),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",((b.jumlah-b.qty)*b.harga),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF(b.kdpromo<>"",(b.qty*b.harga1),0),0)) AS rrg1
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan','left')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	}

public function getDataPenjualanBarang1($tanggal,$kdtoko) {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,c.nama_menu as nm_barang,SUM(b.retur) AS jum_retur,
		                        SUM(b.jumlah) as jum_item,
		                        SUM(b.sub_total_jual+b.nppn) as jum_rp,
		                        SUM(IF(a.kdharga="KH02",b.jumlah,0)) AS gf,
		                        SUM(IF(a.kdharga="KH03",b.jumlah,0)) AS gr,
		                        SUM(IF(a.kdharga="KH04",b.jumlah,0)) AS sf,
		                        SUM(IF(a.kdharga="KH06",b.jumlah,0)) AS mx,
		                        SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF((b.kdpromo="" and jpromo =""),b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF(a.promo="1",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",b.jumlah,0),0)) AS pr,
                                SUM(IF(a.kdharga="KH02",(b.sub_total_jual+b.nppn),0)) AS rgf,
                                SUM(IF(a.kdharga="KH03",(b.sub_total_jual+b.nppn),0)) AS rgr,
		                        SUM(IF(a.kdharga="KH04",(b.sub_total_jual+b.nppn),0)) AS rsf,
		                        SUM(IF(a.kdharga="KH06",(b.sub_total_jual+b.nppn),0)) AS rmx,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan<>"3",IF((b.kdpromo="" and b.jpromo=""),(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF(a.layanan="3",IF((b.kdpromo="" and b.jpromo="P"),(b.jumlah*b.harga),0),0),0)) AS rrd,
                                SUM(IF(a.kdharga="KH01",IF(a.promo="2",(b.sub_total_jual+b.nppn),0),0)) AS rpr
                                ')
                                
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->join('tabel_menu as c','b.kd_barang=c.kode_menu','left')
			->where('a.tgl_penjualan', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->order_by('c.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	}

	public function getDataProfit($tgl_awal, $tgl_akhir) {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,b.nm_barang,b.harga_modal,b.harga,b.retur AS jum_retur, b.jumlah AS jum_item')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->order_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	}

	public function getDiskonBarang($tgl_awal, $tgl_akhir) {
		return $this->db->select('SUM(b.diskonrp) AS disk1')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->get('tabel_penjualan AS a');
	}

	public function getDiskonAkhir($tgl_awal, $tgl_akhir) {
		return $this->db->select('SUM(diskon) AS diska')
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->get('tabel_penjualan AS a');
	}

	public function getDataProfit1($tanggal) {
		return $this->db->select('a.no_faktur_penjualan, b.kd_barang,b.nm_barang,b.harga_modal,b.harga,b.retur AS jum_retur, b.jumlah AS jum_item')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->order_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	}

	public function getDiskonBarang1($tanggal) {
		return $this->db->select('SUM(b.diskonrp) AS disk1')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('a.tgl_penjualan', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->get('tabel_penjualan AS a');
	}

	public function getDiskonAkhir1($tanggal) {
		return $this->db->select('SUM(diskon) AS diska')
			->where('a.tgl_penjualan', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_penjualan AS b WHERE a.no_faktur_penjualan=b.no_faktur_penjualan)')
			->where('a.selesai', '1')
			->get('tabel_penjualan AS a');
	}

	public function getDataPengeluaranRinci1($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('*')
			->where('tgl', $tanggal)
			->where('kdcabang', $kdtoko)
			->get('tabel_biaya');
	}

	public function getDataRekap($tahun, $bulan) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.tgl_penjualan, SUM(b.harga*b.jumlah) AS tot_jual, SUM(b.jumlah*b.harga_modal) AS tot_modal, SUM(b.diskonrp) AS tot_diskon1')
			->join('tabel_rinci_penjualan AS b', 'a.no_faktur_penjualan = b.no_faktur_penjualan')
			->where('MONTH(a.tgl_penjualan)', $bulan)
			->where('YEAR(a.tgl_penjualan)', $tahun)
			->where('a.selesai', '1')
			->where('a.kdcabang', $kdtoko)
			->group_by('a.tgl_penjualan')
			->get('tabel_penjualan AS a');
	}

	public function getDiskon($tahun, $bulan) {
		return $this->db->select('SUM(diskon) AS tot_diskon2')
			->where('MONTH(a.tgl_penjualan)', $bulan)
			->where('YEAR(a.tgl_penjualan)', $tahun)
			->where('a.selesai', '1')
			->group_by('a.tgl_penjualan')
			->get('tabel_penjualan AS a');
	}

	public function getDataPengeluaranRekapitulasi($tahun, $bulan) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('*')
			->where('MONTH(tgl)', $bulan)
			->where('YEAR(tgl)', $tahun)
			->where('kdcabang', $kdtoko)
			->group_by('tgl')
			->get('tabel_biaya');
	}

}

/* End of file Laporan_model.php */
/* Location: ./application/models/Laporan_model.php */