<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_acc extends CI_Model{

	function __construct()
	{
		parent::__construct();
	}
	### ================================== BEGIN View Suplyer ============================================ ###
	#! View data Supplyer join tipe sup ---------------------
	function view_suplyer(){		
		$this->db
			->select("*")
			->from("m_suply")
			->join("m_tipe_sup", "m_suply.tipe_sup = m_tipe_sup.id_tipe_sup");
			
			if($this->page){
				$this->db->limit($this->limit, $this->page);
			}else{
				$this->db->limit($this->limit);
			}

		$q = $this->db->get()->result();
					
		return $q;
	}
	function totalSuplyer(){
		return $this->db
					->select("id_sup")
					->from("m_suply")
					->get()->num_rows();
	}
	#! Untuk Edit Detail ------------------------------------
	function view_suplyer_det($id){
		return $this->db
					->select("*")
					->from("m_suply")
					->join("m_tipe_sup", "m_tipe_sup.id_tipe_sup = m_suply.tipe_sup", "left")
					->where("m_suply.id_sup", $id)
					->get()->row();
	}
	#! Untuk Option Tipe Sup ---------------------------
	function tipe_sup(){
		$q = $this->db->get('m_tipe_sup');
		return $q->result();
	}
	### ============================================ END View Suplyer ============================================ ###
	
	### ============================================ BEGIN View Barang ============================================ ###
	function barangAll(){
		$this->db
				->select("*")
				->from("stokBarang");
				if($this->page){
					$this->db->limit($this->limit, $this->page);
				}else{
					$this->db->limit($this->limit);
				}
			$q = $this->db->get()->result();
			return $q;
	}
	function totalStokBarang(){
		return $this->db
					->select("kode")
					->from("stokBarang")
					->get()->num_rows();
	}

	function barangAll_det_in($id){
		$this->db
			->select("*")
			->from("brng_in")
			->join("m_brng", "brng_in.id_brng = m_brng.kode_br")
			->join("gudang", "brng_in.u_id = gudang.id_gdng")
			->join("satuan", "brng_in.id_sat_in = satuan.id")
			->where("brng_in.id_brng", $id);
			if($this->page){
				$this->db->limit($this->limit, $this->page);
			}else{
				$this->db->limit($this->limit);
			}
			$q = $this->db->get()->result();
			return $q;
	}
	function totalStokBarangIn($id){
		return $this->db
					->select("id_brng")
					->from("brng_in")
					->where("id_brng", $id)
					->get()->num_rows();
	}
	function barangAll_det_out($id){
		$this->db
			->select("*")
			->from("brng_out")
			->join("m_brng", "brng_out.id_brng = m_brng.kode_br")
			->join("gudang", "brng_out.u_id = gudang.id_gdng")
			->join("satuan", "brng_out.id_sat_out = satuan.id")
			->where("brng_out.id_brng", $id);
			if($this->page){
				$this->db->limit($this->limit, $this->page);
			}else{
				$this->db->limit($this->limit);
			}
			$q = $this->db->get()->result();
			return $q;
			
	}
	function totalStokBarangOut($id){
		return $this->db
					->select("id_brng")
					->from("brng_out")
					->where("id_brng", $id)
					->get()->num_rows();
	}
	function view_brng(){
		$tabel1 = "m_brng";
		$tabel2 = "gudang";
		$tabel3 = "satuan";
		$this->db->select('*');
		$this->db->from($tabel1);
		$this->db->join($tabel2, $tabel1.'.id_gd = '.$tabel2.'.id_gdng');
		$this->db->join($tabel3, $tabel1.'.id_sat = '.$tabel3.'.id');
		$q = $this->db->get();
		return $q;
	}
	
	#! Save Master Barang n Set Stok 0 --------------------
	function brng_save($nama_br, $kode_br, $id_sat, $id_sup, $ket, $id_gd, $sub_gd, $user){
		$tabel1 	= "m_brng";
		$tabel2	 	= "stok_brng";
		
		$dataS = array(
                'nama_br'		=> $nama_br,
				'kode_br'		=> $kode_br,
				'id_sat'		=> $id_sat,
                'id_gd'			=> $id_gd,
				'keterangan' 	=> $ket,
                'id_subgd'		=> $sub_gd,
				'tgl_in'		=> date("Y-m-d H:i:s"),	
				'id_sup' 		=> $id_sup,
				'user_in' 		=> $user
        );
		
		$dataS1 = array(
                'tgl_in'	=> date("Y-m-d H:i:s"),
				'kode_br'	=> $kode_br,
				'id_sup'	=> $id_sup,
                'id_gd'		=> $id_gd,
                'id_sat'	=> $id_sat,
        );
		
		$this->db->insert($tabel1, $dataS);
		$this->db->insert($tabel2, $dataS1);
	}
	### ============================================ END View Barang ============================================ ###
	
	/*===============================================START CUSSTOMER=================================================*/
	function view_customer(){
		$this->db
			->select("id_cust, kode_cust, nama_cust, alamat_cust, no_telp, no_fax, cp_cust, ket_cust")
			->from("customer");
			if($this->page){
				$this->db->limit($this->limit, $this->page);
			}else{
				$this->db->limit($this->limit);
			}
			$q = $this->db->get()->result();
			return $q;
	}
	
	function totalCustomer(){
		return $this->db->select("id_cust")->from("customer")->get()->num_rows();
	}
	function getDetailCustomer($kode_customer){
		return $this->db
					->select("id_cust, kode_cust, nama_cust, alamat_cust, no_telp, no_fax, cp_cust, ket_cust")
					->from("customer")
					->where("kode_cust", $kode_customer)
					->limit(1)
					->get()->row();
	}
	function save_edit_customer($id, $dataCustomer){
		return $this->db
					->where("id_cust", $id)
					->update("customer", $dataCustomer);
	}
        
     	function last_kode_customer(){
		//SELECT kode_cust FROM customer ORDER BY id_cust DESC LIMIT 1
		return $this->db->select("kode_cust")->from("customer")->order_by("id_cust", "DESC")->limit(1)->get()->row();
	}
	function save_new_customer($dataMasuk){
		return $this->db->insert("customer", $dataMasuk);
	}
	function get_all_customer(){
		return $this->db->get("customer")->result();
	}
	
	/*===============================================END CUSTOMER====================================================*/
	
	### ============================================ BEGIN function Kontrak ========================================== ###
	## view
	/*
	function view_kon($regexp){
		$q = $this->db->query("SELECT * FROM permintaan
									 JOIN `user` ON permintaan.`u_id`=`user`.`id_user`
									 where no_permin NOT REGEXP '$regexp';");
		return $q;
	}
	*/
	function view_kon($regexp){
		$this->db
			->select("*")
			->from("permintaan")
			->join("user", "permintaan.u_id = user.id_user")
			->where("no_permin NOT REGEXP ", "'$regexp'", FALSE);
			if($this->page){
				$this->db->limit($this->limit, $this->page);
			}else{
				$this->db->limit($this->limit);
			}
			$q = $this->db->get()->result();
			return $q;
	}
	function totalViewKon($regexp){
		return $this->db
					->select("no_permin")
					->from("permintaan")
					->join("user", "permintaan.u_id = user.id_user")
					->where("no_permin NOT REGEXP ", "'$regexp'", FALSE)
					->get()->num_rows();
	}
	function view_kon_br($regexp){
		# Tabel Properti
		$tabel1 = "permintaan_brng";
		$tabel2 = "m_brng";
		$tabel3 = "satuan";
		$kol = "no_permin";
		$k_out = $regexp;
      
		$q = $this->db->query("
			SELECT * FROM (`$tabel1`)
			JOIN `$tabel2` ON `$tabel1`.`id_br` = `$tabel2`.`kode_br`
			JOIN `$tabel3` ON `$tabel1`.`id_sat` = `$tabel3`.`id`
			WHERE $tabel1.`no_permin` NOT REGEXP '$k_out';
		");
		return $q;
	}
	
	## Approve permintaan PPB
	function approve($id){
		$tabel1 = 'permintaan';
		$rel = 'no_permin';
        $v_acc = array(
               'v_acc' => "y",
				'tgl_vacc' => date("Y-m-d H:i:s")
            );
		$this->db->where($rel, $id);
		$this->db->update($tabel1, $v_acc); 
	}
	function cancelApprove($id){
		$tabel1 = 'permintaan';
		$rel = 'no_permin';
        $v_acc = array(
               'v_acc' => "n",
				'tgl_vacc' => date("Y-m-d H:i:s")
            );
		$this->db->where($rel, $id);
		$this->db->update($tabel1, $v_acc); 
	}
	### ============================================ END function Kontrak ============================================ ###

	### ============================================ BEGIN function Permintaan ========================================== ###
	## view
	function view_ppb($regexp){
		#$q = $this->db->query("SELECT * FROM permintaan
		#							 JOIN `user` ON permintaan.`u_id`=`user`.`id_user`
		#							 where no_permin REGEXP '$regexp';");
		#return $q;
		
		$this->db
			->select("*")
			->from("permintaan")
			->join("user", "permintaan.u_id = user.id_user")
			->where("no_permin REGEXP ", "'$regexp'", FALSE);
			if($this->page){
				$this->db->limit($this->limit, $this->page);
			}else{
				$this->db->limit($this->limit);
			}
			$q = $this->db->get()->result();
			return $q;
	}
	function totalPPB($regexp){
		return $this->db
					->select("permintaan.no_permin")
					->from("permintaan")
					->join("user", "permintaan.u_id=user.id_user")
					->where("permintaan.no_permin REGEXP ", "'$regexp'", FALSE)
					->get()->num_rows();
	}
	function view_ppb_br($regexp){
		return $this->db
					->select("*")
					->from("permintaan_brng")
					->join("m_brng","permintaan_brng.id_br = m_brng.kode_br")
					->join("satuan","permintaan_brng.id_sat = satuan.id")
					->where("permintaan_brng.no_permin REGEXP ","'$regexp'", FALSE)
					->get()->result();
	}
	
	## Approve permintaan PPB
	function ppb_approve($id){
		$tabel1 	= 'permintaan';
		$rel 		= 'no_permin';
        $v_acc 		= array(
				'v_acc' => "y",
				'tgl_vacc' => date("Y-m-d H:i:s")
            );
		$this->db->where($rel, $id);
		$this->db->update($tabel1, $v_acc); 
	}
	function ppb_cancelApprove($id){
		$tabel1	= 'permintaan';
		$rel 	= 'no_permin';
        $v_acc 	= array(
				'v_acc' => "n",
				'tgl_vacc' => date("Y-m-d H:i:s")
            );
		$this->db->where($rel, $id);
		$this->db->update($tabel1, $v_acc); 
	}
	### ============================================ END function Permintaaan ============================================ ###	


	
	### ============================================ BEGIN function Penerimaan ========================================== ###
	#! view 4 penerimaan msk ---------------
	function view_lpb($regexp){
		$this->db
				->select("*")
				->from("penerimaan")
				->join("user", "penerimaan.u_id=user.id_user")
				->where("penerimaan.no_lpb REGEXP ", "'$regexp'", FALSE)				
				->where("penerimaan.no_permin REGEXP ", "PPB")				
				//WHERE penerimaan.`no_lpb` REGEXP  'LPB' AND penerimaan.`no_permin` REGEXP 'PPB'; 
				->order_by("tgl", "DESC");

				if($this->page){
					$this->db->limit($this->limit, $this->page);
				}else{
					$this->db->limit($this->limit);
				}

				$q = $this->db->get()->result();
				return $q;
	}
	function totalViewLpb(){
		return $this->db
					->select("penerimaan.no_lpb")
					->from("penerimaan")
					->join("user", "penerimaan.u_id=user.id_user")
					->where("no_permin REGEXP ","PPB")
					->get()->num_rows();
	}
	function view_lpb_br(){
		# Tabel Properti
		/*
		$tabel1 = "penerimaan_brng";
		$tabel2 = "m_brng";
		$tabel3 = "satuan";
		$kol 	= "no_lpb";
		//$k_out = $regexp;
      
		$q = $this->db->query("
			SELECT * FROM (`$tabel1`)
			JOIN `$tabel2` ON `$tabel1`.`id_br` = `$tabel2`.`kode_br`
			JOIN `$tabel3` ON `$tabel1`.`id_sat` = `$tabel3`.`id`;
		");
		return $q;
		*/
		return $this->db
					->select("*")
					->from("penerimaan_brng")
					->join("m_brng", "penerimaan_brng.id_br = m_brng.kode_br")
					->join("satuan", "penerimaan_brng.id_sat = satuan.id")
					->get()->result();
	}
	# ------------------------------------!#
	#! View 4 Update Hrg -------------------
	function view_lpb1($no_lpb){
	$q = $this->db->query("SELECT `penerimaan`.`no_lpb`, `penerimaan`.`no_permin`, `penerimaan`.`no_lpb`, `penerimaan`.`tgl`,
						  `penerimaan`.`no_sj`, `m_suply`.`nama_sup` FROM `penerimaan`
						  JOIN `user` ON penerimaan.`u_id`=`user`.`id_user`
						  JOIN `m_suply` ON `m_suply`.`id_sup` = `penerimaan`.`id_sup`
						  WHERE `no_lpb` = '$no_lpb';");
		return $q;
	}
	function view_lpb1_br($no_lpb){
		# Tabel Properti
		$tabel1 = "penerimaan_brng";
		$tabel2 = "m_brng";
		$tabel3 = "satuan";
		$kol 	= "no_lpb";
		//$k_out = $regexp;
      
		$q = $this->db->query("
			SELECT `$tabel2`.`kode_br`, `$tabel2`.`nama_br`, `$tabel1`.`qty`, `$tabel3`.`nama` FROM `$tabel1`
			JOIN `$tabel2` ON `$tabel1`.`id_br` = `$tabel2`.`kode_br`
			JOIN `$tabel3` ON `$tabel1`.`id_sat` = `$tabel3`.`id`
			WHERE `$tabel1`.`$kol` = '$no_lpb';
		");
		return $q;
	}
	# ------------------------------------!#
	
	## Approve permintaan PPB
	function lpb_approve($id_br, $hrg, $form, $no_lpb, $no_per){
		//$expl	= explode("/", $id);
		//$id_br	= $expl[1];
		$tabel1 = 'penerimaan';
		$tabel2 = 'penerimaan_brng';
		$tabel3 = 'stok_brng';
		$tabel4 = 'permintaan_brng';
		$rel 	= 'no_lpb';
        $v_acc 	= array(
			'v_acc' 	=> "y",
			'tgl_vacc' 	=> date("Y-m-d H:i:s")
        );
		$this->db->where($rel, $no_lpb);
		$this->db->update($tabel1, $v_acc);
		
		
		$qu  = $this->db->query("select * from $tabel2 where no_lpb = '$no_lpb'");
		$qu1 = $this->db->query("select * from $tabel3");
		
		foreach($qu->result() as $q){
			$id_br 	= $q->id_br;
			$qty	= $q->qty;
			foreach($qu1->result() as $q1){
				if($id_br == $q1->kode_br){
					$nstk = $q1->qty_stk + $qty;
					$this->db->query("update $tabel3 set qty_stk = '$nstk' where kode_br = '$id_br'");
					$a = count($hrg);
					for($i=$a-1; $i<$a; $i++){
						$this->db->query("update $tabel3 set hrg_sat = '$hrg[$i]' where kode_br = '$id_br'");
						$this->db->query("update $tabel4 set harga_sat = '$hrg[$i]' where id_br = '$id_br' and no_permin = '$no_per'");
					}
				}
			}
		}
		
	}
	
	function getDetail_lpb($no_lpb){
		$q = $this->db->query("
			SELECT
			m_brng.`nama_br`, penerimaan_brng.`qty`, satuan.`nama`,
			DATE_FORMAT(penerimaan_brng.`tgl`, '%d-%M-%y') AS tgl_masuk
			FROM penerimaan_brng
			JOIN m_brng ON penerimaan_brng.`id_br`=m_brng.`kode_br`
			JOIN satuan ON penerimaan_brng.`id_sat`=satuan.`id`
			WHERE penerimaan_brng.`no_lpb`='$no_lpb'");
		return $q->result();
	}
	
	### ============================================ END function Penerimaan ============================================ ###
	
	### ============================================ BEGIN PPS ============================================ ###
		## view
	function view_pps(){
		$this->db
			->select("*")
			->from("pengeluaran")
			->join("user", "pengeluaran.u_id = user.id_user")
			->order_by("tgl", "DESC");
			if($this->page){
				$this->db->limit($this->limit, $this->page);
			}else{
				$this->db->limit($this->limit);
			}
			$q = $this->db->get()->result();
			return $q;
	}
	function totalViewPps(){
		return $this->db
					->select("pengeluaran.no_kelbr")
					->from("pengeluaran")
					->join("user", "pengeluaran.u_id = user.id_user")
					->get()->num_rows();
	}
	function view_pps_br(){
		# Tabel Properti
		$tabel1 = "pengeluaran_brng";
		$tabel2 = "m_brng";
		$tabel3 = "satuan";
      
		$q = $this->db->query("
			SELECT * FROM (`$tabel1`)
			JOIN `$tabel2` ON `$tabel1`.`id_br` = `$tabel2`.`kode_br`
			JOIN `$tabel3` ON `$tabel1`.`id_sat` = `$tabel3`.`id`;
		");
		return $q;
	}
	### ============================================ END PPS ============================================ ###
	
	
	### ============================================ BEGIN function get gudang ============================================ ###
	function gudang(){
		$tabel = "gudang";
		$q = $this->db->query("select * from $tabel");
		return $q;
	}
	function sub_gudang($id_sub){
		$tabel = "sub_gudang";
		$rel = "id_gd";
		$q = $this->db->query("select * from $tabel where $rel='$id_sub'");
		return $q;
	}
	function kode_br($kode){
		$tabel = "m_brng";
		$rel = "kode_br";
		$this->db->like($rel,$kode);
		$this->db->from($tabel);
		$q = $this->db->get();
		return $q;
	}
	### ============================================ END function get gudang ============================================ ###



	
	### ============================================ BEGIN Get satuan ============================================ ###
	function getSatuan(){
		$tabel = "satuan";
		$q = $this->db->query("SELECT * FROM $tabel");
		return $q;
	}
	### ============================================ END Get satuan ============================================ ###



	# # ==================================== BEGIN Kontrak ================================================ # #
	function savekonPerBrgPer($u_id, $nm_supplier, $id_gd, $no_trans, $tgl_kon, $v_acc, $v_dir){
		$tgl = date('Y-m-d', strtotime($tgl_kon));
		
		# Table Propertie
		$tabel = "permintaan";
		$dataS = array(
				'no_permin' => $no_trans,
				'pemohon' 	=> $nm_supplier,
				'tgl' 		=> $tgl,
				'u_id' 		=> $u_id,
				'devisi' 	=> $id_gd,
				'tgl_vacc' 	=> $tgl,
				'v_acc' 	=> $v_acc,
				//'tgl_vdir'	=> $tgl,
				'v_dir'		=> $v_dir,
			);
		
		# Table Query
		$this->db->insert($tabel, $dataS);
	}
	
        function save_tr($kode_masuk,$kode_br,$nama,$jml,$harga,$tot,$date){
		$q = $this->db->query("INSERT INTO transaksi_brg VALUES ('','$kode_masuk','$kode_br','$nama','$jml','$harga','$tot','$date')");
		return $q;
	}
           function savefinance($blnth,$KodeFinan,$Total,$catatan)
           {
            
            $tabel = "finance_tx";
            $dataS 	= array(
                                'blnth'       => $blnth,
                                'kode'        => $KodeFinan,
                                'pengeluaran' => $Total,
                                'keterangan'  => $catatan,
                
			);
            $this->db->insert($tabel, $dataS);
          }
        
      
	/*function savekonPerBrg($idbr, $nabar, $jml, $sat, $ket, $u_id, $v_dir, $no_trans, $tgl_kon, $harga, $total){
		$tgl = date('Y-m-d', strtotime($tgl_kon));
		# Table Properties
		$tabel = "permintaan_brng";
		$dataS = array(
				'no_permin'	=> $no_trans,
				'id_br'		=> $idbr,
				'nama_brng'	=> $nabar,
				'qty'		=> $jml,
				'qtyp'		=> $jml,
				'id_sat'	=> $sat,
				'keterangan'=> $ket,
				'tgl'		=> $tgl,
				'u_id'		=> $u_id,
				//'v_dir'		=> $v_dir,
				'harga_sat' => $harga,
				//'harga_tot' => $total
			);
		
		# Table Query
		$this->db->insert($tabel, $dataS);
	}
	# # ==================================== END Kontrak ================================================ # #
	
/**
* this start sales model-view-controller
*/	
	function get_all_data_sales(){
		return $this->db->order_by("nama", "ASC")->get("dtSales")->result();
	}
	function get_all_sales(){
		//SELECT * FROM dtSales ORDER BY nama ASC;			
		$this->db
				->select("*")
				->from("dtSales")
				->order_by("nama", "ASC");

				if($this->page){
					$this->db->limit($this->limit, $this->page);
				}else{
					$this->db->limit($this->limit);
				}
		$q = $this->db->get()->result();
		return $q;
	}
	
	function totalAllSales(){
		return $this->db
					->select("id")
					->from("dtSales")
					->get()->num_rows();
	}
	
	
	function getbulan(){
				$tabel = "m_bulan";
				$q = $this->db->query("select * from $tabel");
				return $q;
		}		
		
	
	
	function save_new_sales($dataSales){
		return $this->db->insert("dtSales", $dataSales);
	}
        
        function get_customer($id){
		return $this->db
					->select("*")
					->from("customer")
					->where("id_cust", $id)
					->get()->row();
	}
        
	function get_sales($id){
		return $this->db
					->select("*")
					->from("dtSales")
					->where("id", $id)
					->get()->row();
	}
        
        function get_warna($id){
		return $this->db
					->select("*")
					->from("master_warna")
					->where("id_warna", $id)
					->get()->row();
	}
	function save_edit_sales($id, $dataSales){
		return $this->db
					->where("id", $id)
					->update("dtSales", $dataSales);
	}
	function delete_sales($id){
		return $this->db->delete("dtSales", array("id"=>$id));
	}
        
        function delete_customer($id){
		 return $this->db->delete("customer",array("id_cust"=>$id));
	} 
    
        
        function save_edit_warna($id, $datawarna){
		return $this->db
					->where("id_warna", $id)
					->update("master_warna", $datawarna);
	}
        
        function delete_warna($id){
		return $this->db->delete("master_warna", array("id_warna"=>$id));
	}
	function getSupplier(){
		return $this->db->get('m_suply')->result();
	}
	function getAllWarna(){
		return $this->db					
					->order_by("warna","DSC")
					->get("master_warna")
					->result();
	}
        
        function masterbiayaproduksi(){
				$tabel = "m_biaya";
                $q = $this->db->query("SELECT * FROM $tabel WHERE KODE REGEXP'P'");
                return $q;
	}
       
        function masterbiayalain(){
				$tabel = "m_biaya";
                $q = $this->db->query("SELECT * FROM $tabel WHERE KODE REGEXP'L'");
                return $q;
	}
        
       
}