<?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');
	     if($kdtoko=='zzzzz') {$kdtoko='00000';}
		$query = $this->db->query("SELECT * FROM tabel_toko where kd_toko='$kdtoko'");
		return $query->row();
	}
	public function get_tokoku($kdcabang) {
		$query = $this->db->query("SELECT * FROM tabel_toko where kd_toko='$kdcabang'");
		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 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_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')
			->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 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 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="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="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('b.nolap')
			->get('tabel_penjualan AS a');
	}
	public function getDatastokTransaksiFilter($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
	    if($hak !='kasir') {
		return $this->db->select('a.*,b.*,c.nm_toko,if(a.stat="6","Aprovel","Input") as stat,"Input Co" as keterangan' )
			->join('stokopname AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdtoko = c.kdcabang')
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM stokopname AS b WHERE a.nobukti=b.nobukti)')
			->where('a.jenis','1')
			->group_by('a.nobukti')
			->get('stokopnameheader AS a');
	    } else  {
	       return $this->db->select('a.*,b.*,c.nm_toko,if(a.stat="6","Aprovel","Input") as stat,"Input Co" as keterangan' )
			->join('stokopname AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdtoko = c.kdcabang')
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM stokopname AS b WHERE a.nobukti=b.nobukti)')
			->where('a.kdtoko', $kdtoko)
			->where('a.jenis','1')
			->group_by('a.nobukti')
			->get('stokopnameheader AS a');
	    }
	}	
public function getDatastokTransaksi($tanggal) {    
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
	    if($hak !='kasir') {
		return $this->db->select('a.*,b.*,c.nm_toko,if(a.stat="6","Aprovel","Input") as stat,"Input Co" as keterangan' )
			->join('stokopname AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdtoko = c.kdcabang')
			->where('a.tgl', $tanggal)
			->where('EXISTS (SELECT 1 FROM stokopname AS b WHERE a.nobukti=b.nobukti)')
			->group_by('a.nobukti')
			->where('a.jenis','1')
			->get('stokopnameheader AS a');
	    } else {
	        return $this->db->select('a.*,b.*,c.nm_toko,if(a.stat="6","Aprovel","Input") as stat,"Input Co" as keterangan' )
			->join('stokopname AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdtoko = c.kdcabang')
			->where('a.tgl', $tanggal)
			->where('EXISTS (SELECT 1 FROM stokopname AS b WHERE a.nobukti=b.nobukti)')
			->where('a.kdtoko', $kdtoko)
			->where('a.jenis','1')
			->group_by('a.nobukti')
			->get('stokopnameheader AS a');
	    }	
	}
public function getDatastokTransaksiFilter1($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
		return $this->db->select('a.*,b.*,c.nm_toko,if(a.stat="6","Aprovel","Input") as stat,"Input Co" as keterangan' )
			->join('stokopname AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdtoko = c.kdcabang')
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM stokopname AS b WHERE a.nobukti=b.nobukti)')
			->where('a.jenis','2')
			->group_by('a.nobukti')
			->get('stokopnameheader AS a');
	}	
	

	
	public function getDataPengeluaranRekap($tgl_awal, $tgl_akhir,$kdcabang) {
	    $kdtoko = $kdcabang;
		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 getDatapromoTransaksiFilter($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->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('EXISTS (SELECT 1 FROM tabel_rinci_pembelian AS b WHERE a.no_faktur_pembelian=b.no_faktur_pembelian)')
			->where('a.jtran', '3')
			->where('a.kdcabang', $kdtoko)
			->group_by('a.no_faktur_pembelian')
			->get('tabel_pembelian AS a');
	}
	public function getDatapromoTransaksiFiltertr($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->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('EXISTS (SELECT 1 FROM tabel_rinci_pembelian AS b WHERE a.no_faktur_pembelian=b.no_faktur_pembelian)')
			->where('a.jtran', '3')
			->where('a.tujuan', $kdtoko)
			->where_in('a.selesai',array(1,2))
			->group_by('a.no_faktur_pembelian')
			->get('tabel_pembelian AS a');
	}
	public function getsjTransaksiFilter($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
	    if($hak !='kasir') {
		return $this->db->select('a.*,b.*,c.*,if(a.stat="6","Aprovel","Input") as stat')
			->join('suratjalandetail AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdcabang = c.kd_toko')
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM suratjalandetail AS b WHERE a.nobukti=b.nobukti)')
			->group_by('a.nobukti')
			->get('suratjalanheader AS a');
	    } else  {
	        return $this->db->select('a.*,b.*,c.*,if(a.stat="6","Aprovel","Input") as stat')
			->join('suratjalandetail AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdcabang = c.kd_toko')
			->where('a.tgl BETWEEN "' . date('Y-m-d', strtotime($tgl_awal)) . '" and "' . date('Y-m-d', strtotime($tgl_akhir)) . '"')
			->where('EXISTS (SELECT 1 FROM suratjalandetail AS b WHERE a.nobukti=b.nobukti)')
			->where('a.kdcabang', $kdtoko)
			->group_by('a.nobukti')
			->get('suratjalanheader AS a');
	    }
	}
		public function getsaldoBarangFilterno($nobukti) {
		return $this->db->select("a.id,a.stn,a.sreal,a.kdbrg,a.namabrg nm_barang,a.stokmax,a.kdcabang,b.tgl,a.masuk,a.keluar,a.stok,a.estimasi,a.qty,a.nobukti,a.koreksi,qty AS besar,(a.qty-(c.isi3*FLOOR(a.qty/c.isi3))) AS kecil,
		                ")
		    ->join('suratjalanheader as b','a.nobukti=b.nobukti','left')            
		    ->join('barang as c','a.kdbrg=c.kdbrg','left')            
			->where('a.nobukti', $nobukti)
			->order_by('a.nourut')
			->get('suratjalandetail AS a');
	   
	}
	public function suratjalan($nosj) {
		return $this->db->select('a.*')
			->where('a.nobukti', $nosj)
			->order_by('a.nourut')
			->get('suratjalandetail AS a');
	}
	public function getsjTransaksi($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
	    if($hak !='kasir') {
		return $this->db->select('a.*,b.*,c.*,if(a.stat="6","Aprovel","Input") as stat')
			->join('suratjalandetail AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdcabang = c.kd_toko')
			->where('a.tgl', $tanggal)
			->where('EXISTS (SELECT 1 FROM suratjalandetail AS b WHERE a.nobukti=b.nobukti)')
			->group_by('a.nobukti')
			->get('suratjalanheader AS a');
	    } else {
	        return $this->db->select('a.*,b.*,c.*,if(a.stat="6","Aprovel","Input") as stat')
			->join('suratjalandetail AS b', 'a.nobukti = b.nobukti')
			->join('tabel_toko AS c', 'a.kdcabang = c.kd_toko')
			->where('a.tgl', $tanggal)
			->where('EXISTS (SELECT 1 FROM suratjalandetail AS b WHERE a.nobukti=b.nobukti)')
			->where('a.kdcabang', $kdtoko)
			->group_by('a.nobukti')
			->get('suratjalanheader AS a');
	    }	
	}

	public function getDatamasukTransaksiFilter($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
	    $hak = $this->session->userdata('akses');
	    if($hak=='spv' || $hak=='mgr' || $hak=='logis' || $hak=='inv') {
		return $this->db->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->db->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=='spv') {
		return $this->db->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->db->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 getDatakeluarTransaksiFilter($tgl_awal, $tgl_akhir) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->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('EXISTS (SELECT 1 FROM tabel_rinci_pembelian AS b WHERE a.no_faktur_pembelian=b.no_faktur_pembelian)')
			->where('a.kdcabang', $kdtoko)
			->where('a.jtran', '6')
			->group_by('a.no_faktur_pembelian')
			->get('tabel_pembelian AS a');
	}

	public function getDatapromoTransaksi($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->join('tabel_rinci_pembelian AS b', 'a.no_faktur_pembelian = b.no_faktur_pembelian')
			->where('a.tgl_pembelian', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_pembelian AS b WHERE a.no_faktur_pembelian=b.no_faktur_pembelian)')
			->where('a.jtran', '3')
			->where('a.kdcabang', $kdtoko)
			->group_by('a.no_faktur_pembelian')
			->get('tabel_pembelian AS a');
	}
	public function getDatapromoTransaksitr($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->join('tabel_rinci_pembelian AS b', 'a.no_faktur_pembelian = b.no_faktur_pembelian')
			->where('a.tgl_pembelian', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_pembelian AS b WHERE a.no_faktur_pembelian=b.no_faktur_pembelian)')
			->where('a.jtran', '3')
			->where('a.tujuan', $kdtoko)
			->group_by('a.no_faktur_pembelian')
			->get('tabel_pembelian AS a');
	}
		public function getDatakeluarTransaksi($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.*,b.*')
			->join('tabel_rinci_pembelian AS b', 'a.no_faktur_pembelian = b.no_faktur_pembelian')
			->where('a.tgl_pembelian', $tanggal)
			->where('EXISTS (SELECT 1 FROM tabel_rinci_pembelian AS b WHERE a.no_faktur_pembelian=b.no_faktur_pembelian)')
			->where('a.kdcabang', $kdtoko)
			->where('a.jtran', '6')
			->group_by('a.no_faktur_pembelian')
			->get('tabel_pembelian AS a');
	}

	public function getDataPengeluaranRinci($tgl_awal, $tgl_akhir,$kdcabang) {
	    $kdtoko = $kdcabang;
		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)
			->get('tabel_biaya');
	}

	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) {
		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) {
	    $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 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)
			 ->order_by('a.kdcabang')
			->get('tabel_rinci_penjualan AS b');
	}
    public function getDataPenjualanpromoFilter1($tgl_awal, $tgl_akhir,$kdpromo) {
	    $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)
			 
			 ->order_by('a.kdcabang')
			->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,
		                        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="" 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<>"" or b.jpromo="P"),(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="" and b.jpromo=""),(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<>"" or 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.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('b.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,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(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')
			->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 <', '16:01:00')
			->order_by('b.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,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(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','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 >=', '16:01:00')
			->order_by('b.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')
			->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('b.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 jum_items,
		                        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')
			->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 <', '16:01:00')
			->order_by('b.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')
			->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 >=', '16:01:00')
			->order_by('b.nolap')
			->group_by('b.kd_barang')
			->get('tabel_penjualan AS a');
	}	
	

//public function getDataPakaiBarangFilter($tgl_awal,$tgl_akhir,$kdcabang) {
//	    $kdtoko = $kdcabang;
//		return $this->db->select('a.kdbrg,a.namabrg nm_barang,a.stokmin,a.stokmax,DATEDIFF("'.$tgl_akhir.'", "'.$tgl_awal.'") as jhari,
//		      ifnull((select sum(masuk) from stoktanggal where kdbrg=a.kdbrg and tgl>="'.$tgl_awal.'" and tgl<="'.$tgl_akhir.'" and kdtoko="'.$kdtoko.'"),0) as masuk, 
//		      ifnull((select sum(keluar) from stoktanggal where kdbrg=a.kdbrg and tgl>="'.$tgl_awal.'" and tgl<="'.$tgl_akhir.'" and kdtoko="'.$kdtoko.'"),0) as keluar,
//		      ifnull((select stok from saldoawal where kdbrg=a.kdbrg and tgl="'.$tgl_akhir.'" and kdcabang="'.$kdtoko.'" limit 1),0) as stok  
//		')
//			->where('a.kdcabang', $kdtoko)
//			->order_by('a.nourut')
//			->get('stokbuffer AS a');
	   
//	}

public function getDataPakaiBarangFilter($tgl_awal,$tgl_akhir,$kdcabang) {
	    $kdtoko = $kdcabang;
		return $this->db->select('a.kdbrg,a.namabrg nm_barang,a.stokmin,a.stokmax,DATEDIFF("'.$tgl_akhir.'", "'.$tgl_awal.'") as jhari,
		      ifnull((select sum(masuk) from tabel_kartu_stok where kode_barang=a.kdbrg and waktu="'.$tgl_awal.'" and kode_toko="'.$kdtoko.'"),0) as masuk, 
		      ifnull((select sum(keluar) from tabel_kartu_stok where kode_barang=a.kdbrg and waktu="'.$tgl_awal.'" and kode_toko="'.$kdtoko.'"),0) as keluar,
		      ifnull((select stok from saldoawal where kdbrg=a.kdbrg and tgl="'.$tgl_awal.'" and kdcabang="'.$kdtoko.'"),0) as stok  
		')
			->where('a.kdcabang', $kdtoko)
			->order_by('a.nourut')
			->get('stokbuffer AS a');
	   
	}	
	public function dataTransaksiHariIni($tgl,$stgl) {
		return $this->db->select('no_faktur_penjualan,waktu,tgl_penjualan,a.kdcabang,if(feedback="1","Suka","Tidak Suka") as suka,comment,comment_detail,b.nm_toko,nomeja,notelp,npelanggan,keter')
			->join('tabel_toko as b','a.kdcabang=b.kd_toko','left')  
			->where('a.tgl_penjualan BETWEEN "' . date('Y-m-d', strtotime($tgl)) . '" and "' . date('Y-m-d', strtotime($stgl)) . '"')
			->where('a.selesai', '1')
			->where('a.feedback !=', 'null')
			->order_by('no_faktur_penjualan')
			->get('tabel_penjualan as a');
    }	
public function getsaldoBarangFilter($tgl_awal,$kdcabang,$kirim,$buffer) {
	    $kdtoko = $kdcabang;
	    $tglb=date('ymd', strtotime($tgl_awal));
	    $nobukti=$kdcabang.$tglb.$kirim;
	    $this->session->set_userdata('nosj', $nobukti); 
	    $this->session->set_userdata('cabsj', $kdcabang); 
	    $this->session->set_userdata('tglsj', $tgl_awal); 
	    $tglstok=date('Y-m-d', strtotime('-1 days', strtotime( $tgl_awal )));
	    $kduser = $this->session->userdata('ses_username');
	    $kd=$this->db->query("insert ignore into suratjalanheader(kdcabang,nobukti,tgl,kduser) value ('$kdcabang','$nobukti','$tgl_awal','$kduser')");
	    $kdd=$this->db->query("insert ignore into suratjalandetail(kdcabang,nobukti,nourut,kdbrg,namabrg,stn,stokmax,stok,sreal,estimasi,qty,masuk,keluar)
                            select '$kdcabang','$nobukti',a.nourut,a.kdbrg,a.namabrg,stn,(stokmax*$buffer) as stokbuf,
                            ifnull((select stok from saldoawal where kdbrg=a.kdbrg and tgl='".$tgl_awal."' and kdcabang='".$kdtoko."'),0) as stok,
                            ifnull((select sreal from stokopname where kdbrg=a.kdbrg and tgl='".$tglstok."' and kdtoko='".$kdtoko."'),0) as sreal,
                            ifnull((a.stokmax*'$buffer')-(ifnull((select sreal from stokopname where kdbrg=a.kdbrg and tgl='".$tglstok."' and kdtoko='".$kdtoko."'),0)),0) as estimasi,
                            ifnull((a.stokmax*'$buffer')-(ifnull((select sreal from stokopname where kdbrg=a.kdbrg and tgl='".$tglstok."' and kdtoko='".$kdtoko."'),0)),0) as qty,
                            0 as masuk, 
		                    0 as keluar
                            from stokbuffer a where a.kdcabang='$kdcabang';
	                        ");
	    
	    $upp=$this->db->query("update suratjalandetail a,barang b set qty=((b.isi3*FLOOR(a.qty/b.isi3))) where nobukti='$nobukti' and a.kdbrg=b.kdbrg;");
	    $upp=$this->db->query("update suratjalandetail set qty=0 where qty<0 and nobukti='$nobukti';");
	    $upp=$this->db->query("update suratjalandetail set koreksi=qty-estimasi where nobukti='$nobukti';");
	   // ifnull((select masuk from stoktanggal where kdbrg=a.kdbrg and tgl='".$tgl_awal."' and kdtoko='".$kdtoko."'),0) as masuk, 
		                  //  ifnull((select keluar from stoktanggal where kdbrg=a.kdbrg and tgl='".$tgl_awal."' and kdtoko='".$kdtoko."'),0) as keluar
	    
		return $this->db->select("a.id,a.stn,a.sreal,a.kdbrg,a.namabrg nm_barang,a.stokmax,a.kdcabang,'$tgl_awal' as tgl,a.masuk,a.keluar,a.stok,a.estimasi,a.qty,a.nobukti,a.koreksi,qty AS besar,(a.qty-(c.isi3*FLOOR(a.qty/c.isi3))) AS kecil")
            ->join('barang as c','a.kdbrg=c.kdbrg','left') 		                
			->where('a.nobukti', $nobukti)
			->order_by('a.nourut')
			->get('suratjalandetail AS a');
	   
	}	
public function getsaldoBarangFilter_1811($tgl_awal,$kdcabang) {
	    $kdtoko = $kdcabang;
		return $this->db->select('a.kdbrg,a.namabrg nm_barang,a.stokmin,a.stokmax,a.kdcabang,
	          ifnull((select tgl from stoktanggal where kdbrg=a.kdbrg and tgl="'.$tgl_awal.'" and kdtoko="'.$kdtoko.'"),0) as tgl, 
		      ifnull((select masuk from stoktanggal where kdbrg=a.kdbrg and tgl="'.$tgl_awal.'" and kdtoko="'.$kdtoko.'"),0) as masuk, 
		      ifnull((select keluar from stoktanggal where kdbrg=a.kdbrg and tgl="'.$tgl_awal.'" and kdtoko="'.$kdtoko.'"),0) as keluar,
		      ifnull((select stok from saldoawal where kdbrg=a.kdbrg and tgl="'.$tgl_awal.'" and kdcabang="'.$kdtoko.'"),0) as stok  
		')
			->where('a.kdcabang', $kdtoko)
			->order_by('a.nourut')
			->get('stokbuffer AS a');
	   
	}

public function getDataPakaiBarang($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db->select('a.kdbrg,b.nm_barang,a.masuk,a.keluar')
			->join('tabel_barang AS b', 'a.kdbrg = b.kd_barang')
			->where('a.tgl', $tanggal)
			->where('a.kdtoko', $kdtoko)
			->order_by('b.nourut')
			->get('stoktanggal 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(b.sub_total_jual) as brt_rp,
                                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(b.kdpromo="",b.jumlah,0),0),0)) AS rd,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" or b.jpromo="P"),(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="" 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<>"" or b.jpromo="P"),(((b.jumlah-b.qty)*b.harga)-diskonrp),0),0)) AS rpr,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" or b.jpromo=""),(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('b.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');
	}
	public function getDataPenjualanBarangbkad($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db4->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 jpromo ="" and b.paket = 0),b.jumlah,0),0),0)) AS rg,
                                SUM(IF(a.kdharga="KH01",IF((b.paket = 1),b.jumlah,0),0)) AS pk,
                                SUM(IF(a.kdharga="KH01",IF((a.layanan="3" and b.jpromo<>"B" and b.kdpromo="" and b.paket = 0),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="" and b.paket = 0),(b.jumlah*b.harga),0),0),0)) AS rrg,
                                SUM(IF(a.kdharga="KH01",IF((b.paket = 1),(b.jumlah*b.harga),0),0)) AS rpk,
                                SUM(IF(a.kdharga="KH01",IF((a.layanan="3" and b.jpromo<>"B" AND b.kdpromo="" and b.paket = 0),(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)-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)-diskonrp),0),0)) AS brpr,
                                SUM(IF(a.kdharga="KH01",IF(((b.kdpromo="048")),(((b.jumlah-b.qty)*b.harga)-diskonrp),0),0)) AS rky,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" or b.jpromo=""),(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 getDataPenjualanBarangtbkad($tanggal) {
	    $kdtoko = $this->session->userdata('kd_toko');
		return $this->db4->select('a.no_faktur_penjualan, b.kd_barang,if(c.ketjenis<>"",c.ketjenis,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 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/1.1) as brt_rp,
                                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" 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)-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)-diskonrp),0),0)) AS brpr,
                                SUM(IF(a.kdharga="KH01",IF(((b.kdpromo="048")),(((b.jumlah-b.qty)*b.harga)-diskonrp),0),0)) AS rky,
                                SUM(IF(a.kdharga="KH01",IF((b.kdpromo<>"" or b.jpromo=""),(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.idmenu')
			->get('tabel_penjualan AS a');
	}	
	public function datarekapPengeluaranHariansum($tgl) {
	$kdtoko = $this->session->userdata('kd_toko');
    $this->db->select('(SUM(nilai)*0.1) as totalpromosi');
    $this->db->where('jenis', '2');
    $this->db->where('kdcabang', $kdtoko);
    $this->db->where('DATE(tgl)', $tgl); // Jika tgl bertipe DATE/DATETIME
    $query = $this->db->get('rekapharian')->row();
    
    return $query->totalpromosi ?? 0; // Mengembalikan angka langsung
    }
	 public function getDataPromo($tanggal){
	    $kdtoko = $this->session->userdata('kd_toko');
        return $this->db->select('a.idprogram,a.namapromo')
        ->join('promoarea b','b.nobukti=a.nobukti')
        ->join('promodetail c','c.nobukti=a.nobukti')
        ->where('a.tglawal <=',$tanggal)
        ->where('a.tglakhir >=',$tanggal)
        // ->where('a.jnspromo',1)
		->order_by('a.nobukti','desc')
		->group_by('a.nobukti')
        ->where('b.kdwil',$kdtoko)->get('promoheader a');
    }
}

/* End of file Laporan_model.php */
/* Location: ./application/models/Laporan_model.php */