<?php
 class Model_transaksi extends CI_Model {
 	public function __construct() {
		
	}

	public function databarang($req,$tipe) {
		$kolom=array('kode_barang','barcode','barang','harga_jual1','stok');
		$this->db->select('tb_barang.kode_barang,barcode,barang,harga_jual1,stok');
		$this->db->from('tb_barang');
		$this->db->join('tb_satuanbarang','tb_satuanbarang.kode_barang=tb_barang.kode_barang');
		$this->db->join('tb_kategori','tb_kategori.id_kategori=tb_barang.id_kategori');
		$this->db->where('tb_barang.aktif',1);
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_barang.kode_barang'=>$cari,'barang'=>$cari,'barcode'=>$cari,'kategori'=>$cari,'tb_satuanbarang.harga_jual1'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function caripotongan($kunci=array(),$tanggal) {
		$this->db->select('potongan,jumlah_beli');
		$this->db->from('tb_potongan');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$this->db->group_start();
		$this->db->where('mulai<=',$tanggal);
		$this->db->where('selesai>=',$tanggal);
		$this->db->group_end();
		$query=$this->db->get();
		return $query;
	}

	public function caribonus($kunci=array(),$tanggal) {
		$this->db->select('bonus,jumlah_beli');
		$this->db->from('tb_bonus');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$this->db->group_start();
		$this->db->where('mulai<=',$tanggal);
		$this->db->where('selesai>=',$tanggal);
		$this->db->group_end();
		$query=$this->db->get();
		return $query;
	}

	public function carimaster($kunci,$table) {
		$this->db->from($table);
		$this->db->where('aktif',1);
		if ($kunci) {
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		$query=$this->db->get();
		return $query;
	}

	public function datapermintaan($req,$tipe,$kunci=array()) {
		$kolom=array('tb_permintaan.tanggal','suplier','tb_permintaan.kode_permintaan','COUNT(kode_barang)','keterangan','status');
		$this->db->select('tb_permintaan.tanggal,tb_permintaan.kode_permintaan,COUNT(kode_barang) AS barang,keterangan,status,suplier');
		$this->db->from('tb_permintaan');
		$this->db->join('tb_permintaanmeta','tb_permintaanmeta.kode_permintaan=tb_permintaan.kode_permintaan');
		$this->db->join('tb_suplier','tb_suplier.kode_suplier=tb_permintaan.kode_suplier');
		if ($kunci) {
			$this->db->where($kunci);
		}
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_permintaan.tanggal'=>$cari,'tb_permintaan.kode_permintaan'=>$cari,'keterangan'=>$cari,'suplier'=>$cari,'status'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		$this->db->group_by('tb_permintaan.kode_permintaan');
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function databarangpermintaan($selek,$kunci) {
		if ($selek) {
			$this->db->select($selek);
		}
		$this->db->from('tb_permintaan');
		$this->db->join('tb_permintaanmeta','tb_permintaanmeta.kode_permintaan=tb_permintaan.kode_permintaan');
		//$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_permintaan.kode_pegawai');
		$this->db->join('tb_barang','tb_barang.kode_barang=tb_permintaanmeta.kode_barang');
		$this->db->join('tb_satuanbarang','tb_satuanbarang.kode_barang=tb_barang.kode_barang','LEFT');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$query=$this->db->get();
		return $query;
	}
	
	public function datamasuk($req,$tipe,$kunci=array()) {
		$kolom=array('tb_masuk.tanggal','tb_masuk.kode_masuk','tb_permintaan.kode_permintaan','suplier','COUNT(kode_barang)','tb_masuk.total');
		$this->db->select('tb_masuk.kode_masuk,tb_masuk.tanggal,tb_permintaan.kode_permintaan,COUNT(kode_barang) AS barang,tb_masuk.total,suplier');
		$this->db->from('tb_masuk');
		$this->db->join('tb_permintaan','tb_permintaan.kode_permintaan=tb_masuk.kode_permintaan');
		$this->db->join('tb_permintaanmeta','tb_permintaanmeta.kode_permintaan=tb_permintaan.kode_permintaan');
		$this->db->join('tb_suplier','tb_suplier.kode_suplier=tb_permintaan.kode_suplier');
		if ($kunci) {
			$this->db->where($kunci);
		}
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_masuk.tanggal'=>$cari,'tb_masuk.kode_masuk','tb_permintaan.kode_permintaan'=>$cari,'suplier'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		$this->db->group_by('tb_masuk.kode_masuk');
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function datamutasikeluar($req,$tipe) {
		$kolom=array('tb_mutasikeluar.tanggal','toko','tb_mutasikeluar.kode_mutasikeluar','COUNT(kode_barang)','pegawai');
		$this->db->select('tb_mutasikeluar.tanggal,tb_mutasikeluar.kode_mutasikeluar,COUNT(kode_barang) AS barang,toko,pegawai');
		$this->db->from('tb_mutasikeluar');
		$this->db->join('tb_mutasikeluarmeta','tb_mutasikeluarmeta.kode_mutasikeluar=tb_mutasikeluar.kode_mutasikeluar');
		$this->db->join('tb_toko','tb_toko.kode_toko=tb_mutasikeluar.kode_toko');
		$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_mutasikeluar.kode_pegawai');
		$this->db->group_by('tb_mutasikeluar.kode_mutasikeluar');
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_mutasikeluar.tanggal'=>$cari,'tb_mutasikeluar.kode_mutasikeluar'=>$cari,'toko'=>$cari,'tb_mutasikeluar.kode_toko'=>$cari,'pegawai'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function databarangmutasikeluar($selek,$kunci) {
		if ($selek) {
			$this->db->select($selek);
		}
		$this->db->from('tb_mutasikeluar');
		$this->db->join('tb_mutasikeluarmeta','tb_mutasikeluarmeta.kode_mutasikeluar=tb_mutasikeluar.kode_mutasikeluar');
		$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_mutasikeluar.kode_pegawai');
		$this->db->join('tb_barang','tb_barang.kode_barang=tb_mutasikeluarmeta.kode_barang');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$query=$this->db->get();
		return $query;
	}

	public function datamutasimasuk($req,$tipe) {
		$kolom=array('tb_mutasimasuk.tanggal','toko','tb_mutasimasuk.kode_mutasimasuk','COUNT(kode_barang)','pegawai');
		$this->db->select('tb_mutasimasuk.tanggal,tb_mutasimasuk.kode_mutasimasuk,COUNT(kode_barang) AS barang,toko,pegawai');
		$this->db->from('tb_mutasimasuk');
		$this->db->join('tb_mutasimasukmeta','tb_mutasimasukmeta.kode_mutasimasuk=tb_mutasimasuk.kode_mutasimasuk');
		$this->db->join('tb_toko','tb_toko.kode_toko=tb_mutasimasuk.kode_toko');
		$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_mutasimasuk.kode_pegawai');
		$this->db->group_by('tb_mutasimasuk.kode_mutasimasuk');
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_mutasimasuk.tanggal'=>$cari,'tb_mutasimasuk.kode_mutasimasuk'=>$cari,'toko'=>$cari,'tb_mutasimasuk.kode_toko'=>$cari,'pegawai'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function databarangmutasimasuk($selek,$kunci) {
		if ($selek) {
			$this->db->select($selek);
		}
		$this->db->from('tb_mutasimasuk');
		$this->db->join('tb_mutasimasukmeta','tb_mutasimasukmeta.kode_mutasimasuk=tb_mutasimasuk.kode_mutasimasuk');
		$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_mutasimasuk.kode_pegawai');
		$this->db->join('tb_barang','tb_barang.kode_barang=tb_mutasimasukmeta.kode_barang');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$query=$this->db->get();
		return $query;
	}

	public function datasetoran($req,$tipe) {
		$kp=$this->session->userdata('kp');
		$kolom=array('tanggal','pendapatan_jual','pendapatan_tunai','returan','uang_cash','(uang_cash-(pendapatan_tunai-returan))','pegawai');
		$this->db->select('id_setoran,tanggal,pendapatan_jual,pendapatan_tunai,returan,uang_cash,(uang_cash-(pendapatan_tunai-returan)) AS selisih,pegawai');
		$this->db->from('tb_setoran');
		$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_setoran.kode_pegawai');
		//$this->db->where('kode_pegawai',$kp);
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tanggal'=>$cari,'tb_setoran.kode_pegawai'=>$cari,'pegawai'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}
	
	public function datadrafpembelian($kunci=array()) {
		$kolom=array('tb_drafpembelian.token','tb_drafpembelian.kode_barang','barcode','barang','tb_drafpembelian.jumlah','tb_drafpembelian.harga_satuan','tb_drafpembelian.total_harga','tb_drafpembelian.harga_eceran');
		$this->db->select('tb_drafpembelian.*,barcode,barang');
		$this->db->from('tb_drafpembelian');
		$this->db->join('tb_barang','tb_barang.kode_barang=tb_drafpembelian.kode_barang');
		$this->db->where($kunci);
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_drafpembelian.kode_barang'=>$cari,'barang'=>$cari,'barcode'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		$this->db->order_by('tb_drafpembelian.token','DESC');
		$query=$this->db->get();
		return $query->result();
	}
	
	public function datareturbeli($req,$tipe) {
		$kolom=array('tb_returpembelian.tanggal','suplier','COUNT(tb_returpembelianmeta.kode_barang)','SUM(total_harga)');
		$this->db->select('tb_returpembelian.id_returpembelian,tb_returpembelian.tanggal,COUNT(tb_returpembelianmeta.kode_barang) AS jumbarang,SUM(total_harga) AS total,suplier');
		$this->db->from('tb_returpembelian');
		$this->db->join('tb_returpembelianmeta','tb_returpembelianmeta.id_returpembelian=tb_returpembelian.id_returpembelian');
		$this->db->join('tb_suplier','tb_suplier.kode_suplier=tb_returpembelian.kode_suplier');
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_returpembelian.tanggal'=>$cari,'suplier'=>$cari);
			$this->db->or_like($kunci);
		}
		$this->db->group_by('tb_returpembelian.id_returpembelian');
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function databarangreturpembelian($selek,$kunci) {
		if ($selek) {
			$this->db->select($selek);
		}
		$this->db->from('tb_returpembelian');
		$this->db->join('tb_returpembelianmeta','tb_returpembelianmeta.id_returpembelian=tb_returpembelian.id_returpembelian');
		//$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_returpembelian.kode_pegawai');
		$this->db->join('tb_barang','tb_barang.kode_barang=tb_returpembelianmeta.kode_barang');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$query=$this->db->get();
		return $query;
	}

	public function databarangreturjual($selek,$kunci) {
		if ($selek) {
			$this->db->select($selek);
		}
		$this->db->from('tb_returpenjualan');
		$this->db->join('tb_returpenjualanmeta','tb_returpenjualanmeta.id_returpenjualan=tb_returpenjualan.id_returpenjualan');
		$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_returpenjualan.kode_pegawai');
		$this->db->join('tb_barang','tb_barang.kode_barang=tb_returpenjualanmeta.kode_barang');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$query=$this->db->get();
		return $query;
	}

	public function datakerjang($kunci) {
		$kolom=array('tb_keranjang.kode_barang','barcode','barang','jumlah+bonus','jum_harga','');
		$this->db->select('tb_barang.kode_barang,barcode,tb_barang.barang,tb_barang.harga_beli,token,tb_keranjang.jumlah,tb_keranjang.bonus,tb_keranjang.satuan,tb_keranjang.konversi,tb_keranjang.potongan,tb_keranjang.jum_harga,tb_keranjang.kode_pegawai,pegawai');
		$this->db->from('tb_keranjang');
		$this->db->join('tb_barang','tb_keranjang.kode_barang=tb_barang.kode_barang');
		$this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_keranjang.kode_pegawai');
		$this->db->where($kunci);
		$this->db->order_by('token','DESC');
		$query=$this->db->get();
		return $query->result();
	}

	public function datakeranjang($kunci) {
		$this->db->select('tb_barang.kode_barang,barcode,tb_barang.barang,tb_barang.harga_beli AS beli,token,tb_keranjang.jumlah,tb_keranjang.satuan,tb_keranjang.potongan,tb_keranjang.jum_harga');
		$this->db->from('tb_keranjang');
		$this->db->join('tb_barang','tb_keranjang.kode_barang=tb_barang.kode_barang');
		$this->db->order_by('tb_keranjang.token','DESC');
		$this->db->where($kunci);
		$query=$this->db->get();
		return $query->result();
	}

	public function databayarpiutang($req,$tipe) {
		$kolom=array('tb_piutang.tanggal','tb_piutang.faktur_jual','tb_penjualan.kode_member','member','tb_piutang.piutang','tb_piutang.sisa','(tb_piutang.piutang-tb_piutang.sisa)');
		$this->db->select('tb_piutang.id_piutang,tb_piutang.tanggal,tb_piutang.faktur_jual,tb_penjualan.kode_member,member,tb_piutang.piutang,tb_piutang.sisa');
		$this->db->from('tb_piutang');
        $this->db->join('tb_penjualan','tb_penjualan.faktur_jual=tb_piutang.faktur_jual');
        $this->db->join('tb_member','tb_member.kode_member=tb_penjualan.kode_member','LEFT');
        $this->db->where('tb_piutang.sisa<>',0);
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_piutang.tanggal'=>$cari,'tb_piutang.faktur_jual'=>$cari,'tb_penjualan.kode_member'=>$cari,'member'=>$cari,'piutang'=>$cari,'sisa'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function databayarhutang($req,$tipe) {
		$kolom=array('tb_hutang.tanggal','tb_hutang.kode_masuk','tb_masuk.no_nota','suplier','tb_hutang.hutang','tb_hutang.sisa','(tb_hutang.hutang-tb_hutang.sisa)','tempo');
		$this->db->select('tb_hutang.id_hutang,tb_hutang.tanggal,tb_hutang.kode_masuk,tb_hutang.hutang,tb_hutang.sisa,tb_hutang.tempo,suplier,tb_masuk.no_nota,');
		$this->db->from('tb_hutang');
        $this->db->join('tb_masuk','tb_masuk.kode_masuk=tb_hutang.kode_masuk');
        $this->db->join('tb_permintaan','tb_permintaan.kode_permintaan=tb_masuk.kode_permintaan');
        $this->db->join('tb_suplier','tb_suplier.kode_suplier=tb_permintaan.kode_suplier');
        $this->db->where('tb_hutang.sisa<>',0);
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_hutang.tanggal'=>$cari,'tb_hutang.kode_masuk'=>$cari,'tb_permintaan.kode_suplier'=>$cari,'suplier'=>$cari,'hutang'=>$cari,'sisa'=>$cari,'tempo'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function datanota($req,$tipe) {
		$kolom=array('tb_penjualan.faktur_jual','tanggal','COUNT(tb_penjualanmeta.kode_barang)','total');
		$this->db->select('tb_penjualan.faktur_jual,tanggal,COUNT(tb_penjualanmeta.kode_barang) AS jum_barang,total');
		$this->db->from('tb_penjualan');
		$this->db->join('tb_penjualanmeta','tb_penjualanmeta.faktur_jual=tb_penjualan.faktur_jual');
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_penjualan.faktur_jual'=>$cari,'tanggal'=>$cari,'total'=>$cari);
			$this->db->group_start();
			$this->db->or_like($kunci);
			$this->db->group_end();
		}
		$this->db->group_by('tb_penjualan.faktur_jual');
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}

	public function datanotabeli($req,$tipe) {
		$kolom=array('tb_masuk.no_nota','tb_masuk.tanggal','tb_masuk.kode_permintaan','COUNT(tb_permintaanmeta.kode_barang)','tb_masuk.total');
		$this->db->select('tb_masuk.no_nota,tb_masuk.kode_masuk,,tb_masuk.kode_permintaan,tb_masuk.tanggal,COUNT(tb_permintaanmeta.kode_barang) AS jum_barang,total');
		$this->db->from('tb_permintaan');
		$this->db->join('tb_permintaanmeta','tb_permintaanmeta.kode_permintaan=tb_permintaan.kode_permintaan');
		$this->db->join('tb_masuk','tb_masuk.kode_permintaan=tb_permintaan.kode_permintaan');
		//pencarian data
		if (@$req['search']['value']) {
			$cari=$req['search']['value'];
			$kunci=array('tb_permintaan.kode_permintaan'=>$cari,'tb_masuk.kode_permintaan'=>$cari,'tb_masuk.tanggal'=>$cari,'tb_masuk.total'=>$cari);
			$this->db->or_like($kunci);
		}
		$this->db->group_by('tb_masuk.no_nota');
		if ($tipe=='jumlah') {
			$query=$this->db->get();
			return $query->num_rows();
		} else {
			//urutan data
			$this->db->order_by($kolom[$req['order'][0]['column']],$req['order'][0]['dir']);
			$this->db->limit($req['length'],$req['start']);
			$query=$this->db->get();
			return $query->result();
		}
	}
	
	public function datariwayatkoin($selek='',$kunci=array()) {
		if ($selek) {
			$this->db->select($selek);
		}
		$this->db->from('tb_riwayatkoin');
		$this->db->join('tb_penjualan','tb_penjualan.faktur_jual=tb_riwayatkoin.faktur_jual');
		$this->db->join('tb_member','tb_member.kode_member=tb_riwayatkoin.kode_member');
		$this->db->join('tb_pegawai','tb_penjualan.kode_pegawai=tb_pegawai.kode_pegawai');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$this->db->order_by('tb_riwayatkoin.tanggal','ASC');
		$query=$this->db->get();
		return $query;
	}
}