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

class App_Model extends CI_Model {

	/**
	 * @keterangan : Model untuk menangani semua query database aplikasi
	 **/
	
	public function getAllData($table)
	{
		return $this->db->get($table);
	}
	
	public function getAllDataLimited($table,$limit,$offset)
	{
		return $this->db->get($table, $limit, $offset);
	}
	
	public function getSelectedDataLimited($table,$data,$limit,$offset)
	{
		return $this->db->get_where($table, $data, $limit, $offset);
	}
		
	//select table
	public function getSelectedData($table,$data)
	{
		return $this->db->get_where($table, $data);
	}
	
	//update table
	function updateData($table,$data,$field_key)
	{
		$this->db->update($table,$data,$field_key);
	}
	function deleteData($table,$data)
	{
		$this->db->delete($table,$data);
	}
	
	function insertData($table,$data)
	{
		$this->db->insert($table,$data);
	}
	
	//Query manual
	function manualQuery($q)
	{
		return $this->db->query($q);
	}
	
	public function NamaSupp($id){
		$t = "SELECT * FROM supplier WHERE kode_supplier='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->nama_supplier;
			}
		}else{
			$hasil = '';
		}
		return $hasil;
	}
	
	public function NamaCustomer($id){
		$t = "SELECT * FROM customer WHERE kode_customer='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->nama_customer;
			}
		}else{
			$hasil = '';
		}
		return $hasil;
	}
	
	public function CariLevel($id){
		$t = "SELECT * FROM level WHERE id_level='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->level;
			}
		}else{
			$hasil = '';
		}
		return $hasil;
	}
	
	public function ItemBeli($id){
		$t = "SELECT kodebeli FROM d_beli WHERE kodebeli='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			$hasil = $r;
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function ItemPesan($id){
		$t = "SELECT kodepesan FROM d_pesan WHERE kodepesan='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			$hasil = $r;
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function JmlBeli($id){
		$t = "SELECT sum(jmlbeli * hargabeli) as jml FROM d_beli WHERE kodebeli='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
		public function JmlPesan($id){
		$t = "SELECT sum(jmlbeli * hargabeli) as jml FROM d_pesan WHERE kodepesan='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function ItemJual($id){
		$t = "SELECT kodejual FROM d_jual WHERE kodejual='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			$hasil = $r;
		}else{
			$hasil = 0;
		}
		return $hasil;
	}

    public function TglSoSebelum(){
		$t = "SELECT DISTINCT(tgl_penyesuaian) FROM tbpenyesuaian_stok ORDER BY tgl_penyesuaian DESC LIMIT 1,1";
		$d = $this->app_model->manualQuery($t);
        $r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->tgl_penyesuaian;
			}
		}else{
			$hasil = '';
		}
		return $hasil;
	}
	
	public function JmlJual($id){
		$t = "SELECT sum(jmljual * hargajual) as jml FROM d_jual WHERE kodejual='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function CariStokAwal($kode){
		$t = "SELECT kode_barang,stok_awal FROM barang WHERE kode_barang='$kode'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->stok_awal;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function CariJmlBeli($kode){
	    $tgl = $this->app_model->CariTglStokOpname();
		$t = "SELECT kode_barang,sum(jmlbeli) as jml FROM vwpembelian WHERE kode_barang='$kode' AND tglbeli>'$tgl' GROUP BY kode_barang";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function CariJmlJual($kode){
	    $tgl = $this->app_model->CariTglStokOpname();
		$t = "SELECT kode_barang,sum(jmljual) as jml FROM vwdetail_jual WHERE kode_barang='$kode' AND tgljual>'$tgl' GROUP BY kode_barang";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function CariTglStokOpname(){
		$t = "SELECT tgl_penyesuaian FROM tbpenyesuaian_stok ORDER BY tgl_penyesuaian DESC LIMIT 1";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->tgl_penyesuaian;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function CariStokAkhirSesuaiStok($kode){
		$tgl = $this->app_model->CariTglStokOpname();
		$t = " SELECT
	barang.kode_barang AS kode_barang,
	barang.nama_barang AS nama_barang,
	barang.satuan AS satuan,
	barang.harga_beli AS harga_beli,
	barang.harga_jual AS harga_jual,
	barang.stok_awal AS stok_awal,
	barang.kode_jenis AS kode_jenis,
	tbjenis.jenis AS jenis,
	tbpersen_laba.kategori AS kategori,
	tbpersen_laba.persen_laba AS persen_laba,
	barang.kode_kategori AS kode_kategori,
	barang.barcode AS barcode,
	IFNULL(c.jml,0) AS stok_op_terakhir
FROM
	(
		(
			barang
			LEFT JOIN tbjenis ON (
				barang.kode_jenis = tbjenis.kode_jenis
			)
		)
		LEFT JOIN tbpersen_laba ON (
			tbpersen_laba.kode_kategori = barang.kode_kategori
		)
	)
LEFT JOIN (
	SELECT
		*
	FROM
		tbpenyesuaian_stok
	WHERE
		tgl_penyesuaian = '$tgl'
) AS c ON c.kode_barang = barang.kode_barang
WHERE barang.kode_barang='$kode'
GROUP BY
	kode_barang
ORDER BY
kode_barang";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$awal = $h->stok_op_terakhir;
				$beli = $this->app_model->CariJmlBeli($kode);
				$jual = $this->app_model->CariJmlJual($kode);
				$hasil = $awal + $beli - $jual;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function GrafikBeli($bln,$thn){
		$t = "SELECT month(a.tglbeli) as bln, year(a.tglbeli) as th, count(*) as jml 
			FROM h_beli as a
			JOIN d_beli as b
			ON a.kodebeli=b.kodebeli 
			WHERE month(a.tglbeli)='$bln' AND year(a.tglbeli)='$thn'
			GROUP BY month(a.tglbeli),year(a.tglbeli)";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function GrafikJual($bln,$thn){
		$t = "SELECT month(a.tgljual) as bln, year(a.tgljual) as th, count(*) as jml 
			FROM h_jual as a
			JOIN d_jual as b
			ON a.kodejual=b.kodejual 
			WHERE month(a.tgljual)='$bln' AND year(a.tgljual)='$thn'
			GROUP BY month(a.tgljual),year(a.tgljual)";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function CariStokAkhir($kode){
		$awal = $this->app_model->CariStokAwal($kode);
		$beli = $this->app_model->CariJmlBeli($kode);
		$jual = $this->app_model->CariJmlJual($kode);
		$hasil = ($awal+$beli)-$jual;
		return $hasil;
	}
	
	public function CariNamaPengguna(){
		$id = $this->session->userdata('username');
		$t = "SELECT * FROM admins WHERE username='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->nama_lengkap;
			}
		}else{
			$hasil = '';
		}
		return $hasil;
	}
	
	public function CariFotoPengguna(){
		$id = $this->session->userdata('username');
		$t = "SELECT * FROM admins WHERE username='$id'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->foto;
			}
		}else{
			$hasil = '';
		}
		return $hasil;
	}
	
	public function TotalBeli($tgl1,$tgl2){
		$t = "SELECT sum(a.jmlbeli * a.hargabeli) as jml 
			FROM d_beli as a
			JOIN h_beli as b
			ON a.kodebeli=a.kodebeli 
			WHERE b.tglbeli BETWEEN '$tgl1' AND '$tgl2'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function TotalJual($tgl1,$tgl2){
		$t = "SELECT sum(a.jmljual * a.hargajual) as jml 
				FROM d_jual as a
				JOIN h_jual as b
				ON a.kodejual=b.kodejual 
				WHERE b.tgljual BETWEEN '$tgl1' AND '$tgl2'";
		$d = $this->app_model->manualQuery($t);
		$r = $d->num_rows();
		if($r>0){
			foreach($d->result() as $h){
				$hasil = $h->jml;
			}
		}else{
			$hasil = 0;
		}
		return $hasil;
	}
	
	public function MaxKodeBarang(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kode_barang) as no FROM barang";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,2,5))+1;
				$hasil = 'B'.sprintf("%05s", $tmp);
			}
		}else{
			$hasil = 'B'.'00001';
		}
		return $hasil;
	}
	
	public function MaxKodeSupplier(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kode_supplier) as no FROM supplier";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,2,3))+1;
				$hasil = 'S'.sprintf("%03s", $tmp);
			}
		}else{
			$hasil = 'S'.'001';
		}
		return $hasil;
	}
	
	public function MaxKodeJnsBarang(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kode_jenis) as no FROM tbjenis";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,2,3))+1;
				$hasil = 'J'.sprintf("%03s", $tmp);
			}
		}else{
			$hasil = 'J'.'001';
		}
		return $hasil;
	}
	
	public function MaxKodeKategoriBarang(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kode_kategori) as no FROM tbpersen_laba";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,2,3))+1;
				$hasil = 'K'.sprintf("%03s", $tmp);
			}
		}else{
			$hasil = 'K'.'001';
		}
		return $hasil;
	}
	
	public function MaxKodeCust(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kode_customer) as no FROM customer";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,2,6))+1;
				$hasil = sprintf("%06s", $tmp);
			}
		}else{
			$hasil = '000001';
		}
		return $hasil;
	}
	
	public function MaxKodeBeli(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kodebeli) as no FROM h_beli";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,2,5))+1;
				$hasil = 'BL'.sprintf("%05s", $tmp);
			}
		}else{
			$hasil = 'BL'.'00001';
		}
		return $hasil;
	}
	
	public function MaxKodeJual(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kodejual) as no FROM h_jual";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,2,8))+1;
				$hasil = 'SL'.sprintf("%08s", $tmp);
			}
		}else{
			$hasil = 'SL'.'00000001';
		}
		return $hasil;
	}
	
	public function MaxKodeJual3(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kodejual) as no FROM h_jual WHERE kodejual LIKE 'JL".$th.$bln."%'";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,6,5))+1;
				$hasil = 'JL'.$th.$bln.sprintf("%05s", $tmp);
			}
		}else{
			$hasil = 'JL'.$th.$bln.'00001';
		}
		return $hasil;
	}
	
	public function MaxKodePesan(){
	    date_default_timezone_set("Asia/Jakarta");
		$bln = date('m');
		$th = date('y');
		$text = "SELECT max(kodepesan) as no FROM h_pesan";
		$data = $this->app_model->manualQuery($text);
		if($data->num_rows() > 0 ){
			foreach($data->result() as $t){
				$no = $t->no; 
				$tmp = ((int) substr($no,5,5))+1;
				$hasil = 'P'.$th.$bln.sprintf("%05s", $tmp);
			}
		}else{
			$hasil = 'P'.$th.$bln.'00001';
		}
		return $hasil;
	}
		
	//Konversi tanggal
	public function tgl_sql($date){
		$exp = explode('-',$date);
		if(count($exp) == 3) {
			$date = $exp[2].'-'.$exp[1].'-'.$exp[0];
		}
		return $date;
	}
	public function tgl_str($date){
		$exp = explode('-',$date);
		if(count($exp) == 3) {
			$date = $exp[2].'-'.$exp[1].'-'.$exp[0];
		}
		return $date;
	}
	
	public function ambilTgl($tgl){
		$exp = explode('-',$tgl);
		$tgl = $exp[2];
		return $tgl;
	}
	
	public function ambilBln($tgl){
		$exp = explode('-',$tgl);
		$tgl = $exp[1];
		$bln = $this->app_model->getBulan($tgl);
		$hasil = substr($bln,0,3);
		return $hasil;
	}
	
	public function tgl_indo($tgl){
			$jam = substr($tgl,11,10);
			$tgl = substr($tgl,0,10);
			$tanggal = substr($tgl,8,2);
			$bulan = $this->app_model->getBulan(substr($tgl,5,2));
			$tahun = substr($tgl,0,4);
			return $tanggal.' '.$bulan.' '.$tahun.' '.$jam;		 
	}	

	public function getBulan($bln){
		switch ($bln){
			case 1: 
				return "Januari";
				break;
			case 2:
				return "Februari";
				break;
			case 3:
				return "Maret";
				break;
			case 4:
				return "April";
				break;
			case 5:
				return "Mei";
				break;
			case 6:
				return "Juni";
				break;
			case 7:
				return "Juli";
				break;
			case 8:
				return "Agustus";
				break;
			case 9:
				return "September";
				break;
			case 10:
				return "Oktober";
				break;
			case 11:
				return "November";
				break;
			case 12:
				return "Desember";
				break;
		}
	} 
	
	public function hari_ini($hari){
		date_default_timezone_set('Asia/Jakarta'); // PHP 6 mengharuskan penyebutan timezone.
		$seminggu = array("Minggu","Senin","Selasa","Rabu","Kamis","Jumat","Sabtu");
		//$hari = date("w");
		$hari_ini = $seminggu[$hari];
		return $hari_ini;
	}
	
	//query login
	public function getLoginData($usr,$psw)
	{
		//$u = mysqli_real_escape_string($usr);
		//$p = md5(mysqli_real_escape_string($psw));
		$u = $usr;
		$p = md5($psw);
		$q_cek_login = $this->db->get_where('admins', array('username' => $u, 'password' => $p));
		if(count($q_cek_login->result())>0)
		{
			foreach($q_cek_login->result() as $qck)
			{
					foreach($q_cek_login->result() as $qad)
					{
						$sess_data['logged_in'] = 'aingLoginYeuh';
						$sess_data['username'] = $qad->username;
						$sess_data['nama_lengkap'] = $qad->nama_lengkap;
						$sess_data['foto'] = $qad->foto;
						$sess_data['level'] = $qad->level;
						$this->session->set_userdata($sess_data);
					}
					header('location:'.base_url().'index.php/home');
			}
		}
		else
		{
			$this->session->set_flashdata('result_login', '<br>Username atau Password yang anda masukkan salah.');
			header('location:'.base_url().'index.php/login');
		}
	}
}
	
/* End of file app_model.php */
/* Location: ./application/models/app_model.php */