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

	public function databelidetail($selek,$kunci) {
		if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_pembelian');
        $this->db->join('tb_pembelianmeta','tb_pembelianmeta.nota_beli=tb_pembelian.nota_beli');
        $this->db->join('tb_barang','tb_barang.kode_barang=tb_pembelianmeta.kode_barang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_pembelian.kode_pegawai');
        $this->db->join('tb_suplier','tb_suplier.kode_suplier=tb_pembelian.kode_suplier');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_pembelian.tgl_beli','ASC');
        $query=$this->db->get();
        return $query;
	}

	public function datajualdetail($selek,$kunci) {
		if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_penjualan');
        $this->db->join('tb_penjualanmeta','tb_penjualanmeta.faktur_jual=tb_penjualan.faktur_jual');
        $this->db->join('tb_barang','tb_barang.kode_barang=tb_penjualanmeta.kode_barang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_penjualan.kode_pegawai');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_penjualan.tanggal','ASC');
        $query=$this->db->get();
        return $query;
	}

    public function datamasukdetail($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_masuk','tb_masuk.kode_permintaan=tb_permintaan.kode_permintaan');
        $this->db->join('tb_suplier','tb_suplier.kode_suplier=tb_permintaan.kode_suplier');
		//$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');
		if ($kunci) {
			$this->db->where($kunci);
		}
		$query=$this->db->get();
		return $query;
	}

    public function datareturjualdetail($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_penjualan','tb_penjualan.faktur_jual=tb_returpenjualan.faktur_jual');
        $this->db->join('tb_barang','tb_barang.kode_barang=tb_returpenjualanmeta.kode_barang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_returpenjualan.kode_pegawai');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_returpenjualan.tanggal','ASC');
        $query=$this->db->get();
        return $query;
	}
	
	public function datajualstruk($selek,$kunci) {
        if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_penjualan');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_penjualan.kode_pegawai');
        $this->db->join('tb_member','tb_member.kode_member=tb_penjualan.kode_member','LEFT');
        $this->db->join('tb_piutang','tb_piutang.faktur_jual=tb_penjualan.faktur_jual','LEFT');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_penjualan.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }

    public function datarekapjual($selek,$kunci) {
        if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_penjualan');
        $this->db->join('tb_penjualanmeta','tb_penjualanmeta.faktur_jual=tb_penjualan.faktur_jual');
        $this->db->join('tb_barang','tb_barang.kode_barang=tb_penjualanmeta.kode_barang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_penjualan.kode_pegawai');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->group_by('tb_penjualan.faktur_jual');
        $this->db->order_by('tb_penjualan.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }
    
    public function datarekapjualbarang($kunci) {
        $this->db->select('SUM((jumlah_jual*tb_penjualanmeta.konversi)) AS jumlah,SUM(tb_penjualanmeta.total_harga) AS jual,SUM((jumlah_jual*tb_penjualanmeta.konversi)*tb_penjualanmeta.harga_beli) AS beli,tb_penjualanmeta.kode_barang,barcode,barang,kategori');
        $this->db->from('tb_penjualanmeta');
        $this->db->join('tb_penjualan','tb_penjualan.faktur_jual=tb_penjualanmeta.faktur_jual');
        $this->db->join('tb_barang','tb_barang.kode_barang=tb_penjualanmeta.kode_barang');
        $this->db->join('tb_kategori','tb_barang.id_kategori=tb_kategori.id_kategori');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->group_by('tb_barang.kode_barang');
        $this->db->order_by('SUM((jumlah_jual*tb_penjualanmeta.konversi))','DESC');
        $query=$this->db->get();
        return $query;
    }

    public function datajualbarang($kunci) {
        $this->db->select('SUM((jumlah_jual*tb_penjualanmeta.konversi)) AS jumlah,SUM(tb_penjualanmeta.total_harga) AS jual,SUM((jumlah_jual*tb_penjualanmeta.konversi)*tb_penjualanmeta.harga_beli) AS beli,tb_penjualanmeta.kode_barang,barcode,barang,kategori');
        $this->db->from('tb_penjualanmeta');
        $this->db->join('tb_penjualan','tb_penjualan.faktur_jual=tb_penjualanmeta.faktur_jual');
        $this->db->join('tb_barang','tb_barang.kode_barang=tb_penjualanmeta.kode_barang');
        $this->db->join('tb_kategori','tb_barang.id_kategori=tb_kategori.id_kategori');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->group_by("DATE_FORMAT(tb_penjualan.tanggal,'%Y-%m-%d')");
        $query=$this->db->get();
        return $query;
    }
    
    public function datareturbelidetail($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_barang','tb_barang.kode_barang=tb_returpembelianmeta.kode_barang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_returpembelian.kode_pegawai');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_returpembelian.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }

    public function datamutasidetail($selek,$kunci) {
        if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_mutasi');
        $this->db->join('tb_mutasimeta','tb_mutasimeta.kode_mutasi=tb_mutasi.kode_mutasi');
        $this->db->join('tb_barang','tb_barang.kode_barang=tb_mutasimeta.kode_barang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_mutasi.kode_pegawai');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_mutasi.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }

    public function datapiutang($selek,$kunci) {
        if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_piutang');
        $this->db->join('tb_penjualan','tb_penjualan.faktur_jual=tb_piutang.faktur_jual');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_penjualan.kode_pegawai');
        $this->db->join('tb_member','tb_member.kode_member=tb_penjualan.kode_member','LEFT');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_penjualan.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }

    public function datahutang($selek,$kunci) {
        if ($selek) {
            $this->db->select($selek);
        }
        $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');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_hutang.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }

    public function databayarpiutang($selek,$kunci) {
        if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_piutang');
        $this->db->join('tb_bayarpiutang','tb_bayarpiutang.id_piutang=tb_piutang.id_piutang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_bayarpiutang.kode_pegawai');
        $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');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_bayarpiutang.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }
    
    public function databayarhutang($selek,$kunci) {
        if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_hutang');
        $this->db->join('tb_bayarhutang','tb_bayarhutang.id_hutang=tb_hutang.id_hutang');
        $this->db->join('tb_pegawai','tb_pegawai.kode_pegawai=tb_bayarhutang.kode_pegawai');
        $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');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_bayarhutang.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }
    
    public function datariwayatkoin($selek,$kunci) {
         if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_riwayatkoin');
        $this->db->join('tb_member','tb_member.kode_member=tb_riwayatkoin.kode_member');
        $this->db->join('tb_penjualan','tb_penjualan.faktur_jual=tb_riwayatkoin.faktur_jual');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_riwayatkoin.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }
    
    public function datariwayatpoin($selek,$kunci) {
         if ($selek) {
            $this->db->select($selek);
        }
        $this->db->from('tb_riwayatpoin');
        $this->db->join('tb_member','tb_member.kode_member=tb_riwayatpoin.kode_member');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $this->db->order_by('tb_riwayatpoin.tanggal','ASC');
        $query=$this->db->get();
        return $query;
    }
    
    public function databarang($kunci) {
        $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');
        if ($kunci) {
            $this->db->where($kunci);
        }
        $query=$this->db->get();
        return $query;
    }
}