<?php
class M_home extends CI_Model{
		function getmenu($headeractive){
			$this->db->select('title, description');
			$this->db->from('menu');						
			$this->db->where('menu', $headeractive);	
			 $query = $this->db->get();			   
			$row = $query->row();		
			return $row;	
		}	
			

		function fasilitas(){
			$this->db->select('*');
			$this->db->from('fasilitas');						
			$this->db->where('status', '1');	
			$this->db->where('waktu', '0');	
			$this->db->where('batasmax  ', '1');	
			$this->db->where('umum  ', '0');
			$this->db->where('aktif', 'Y');			
			 $query = $this->db->get();					
			return $query ;
		}
		function getkategorifas($idfasilitas){
			$hasil2x= $this->db->query("select a.idkategori 
													from fasilitas as a 
													LEFT JOIN kategori as b ON b.idkategori = a.idkategori 
													WHERE b.wajib ='1' AND   a.idfasilitas = '$idfasilitas' ");	
			$row = $hasil2x->row();	
			return $row;
		}

 
		
		function getjadwalkress($idkategori,$x){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.status ='1' AND  c.disetujui = '1' ");	
			$row = $hasil2x->result_array();	
			return $row;
		}
		
		function getjadwalkressaula($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.siangmalam = '$waktu'  AND c.status ='1' AND  c.disetujui = '1'");	
			$row = $hasil2x->result_array();	
			return $row;
		}

		function getjadwalkressaulafull($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.siangmalam = '2'  AND c.status ='1' AND  c.disetujui = '1'");	
			$row = $hasil2x->result_array();	
			return $row;
		}

		function getjadwalkressaulasiang($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x'  AND c.siangmalam = '0' AND c.status ='1' AND  c.disetujui = '1' ");	
			$row = $hasil2x->result_array();	
			return $row;
		}

		function getjadwalkressaulamalam($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.siangmalam = '1' AND c.status ='1' AND  c.disetujui = '1' ");	
			$row = $hasil2x->result_array();	
			return $row;
		}
		//admin 
		function getjadwalkressaulafull_admin($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.status !='2' AND c.siangmalam = '2' AND c.disetujui !='0'  ");	
			$row = $hasil2x->result_array();	
			return $row;
		}

		function getjadwalkressaulasiang_admin($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.status !='2' AND c.siangmalam = '0' AND c.disetujui !='0' ");	
			$row = $hasil2x->result_array();	
			return $row;
		}

		function getjadwalkressaulamalam_admin($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.status !='2' AND c.siangmalam = '1' AND c.disetujui !='0' ");	
			$row = $hasil2x->result_array();	
			return $row; 
		}		

		function getjadwalkressaula_admin($idkategori,$x,$waktu){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' AND c.status !='2' AND c.siangmalam = '$waktu' AND c.disetujui !='0'  ");	
			$row = $hasil2x->result_array();	
			return $row;
		}
		
		function getjadwalkressperpesan($idkategori,$x,$idpesan){
			$hasil2x= $this->db->query("select * 
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											JOIN pesan as c ON c.idpesan = a.idpesan
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' and a.idpesan != '$idpesan' AND c.status !='2'");	
			$row = $hasil2x->result_array();	
			return $row;
		}
		function getjadwalkamar($idkategori,$x){
			$hasil2x= $this->db->query("select SUM(jumlah) as jumlahKamar
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											where  b.idkategori = '$idkategori' and a.tanggal = '$x'");	
			$row = $hasil2x->row();	
			return $row;
		}
		function getjadwalkamarperpesan($idkategori,$x,$idpesan){
			$hasil2x= $this->db->query("select SUM(jumlah) as jumlahKamar
											from pesandetail as a 
											JOIN fasilitas as b ON a.idfasilitas = b.idfasilitas
											where  b.idkategori = '$idkategori' and a.tanggal = '$x' and a.idpesan != '$idpesan'");	
			$row = $hasil2x->row();	
			return $row;
		}
		function getfaswajib($idkategori){
			$hasil2x= $this->db->query("select * 
											from kategori
											where  idkategori = '$idkategori' and wajib = '1'");	
			$row = $hasil2x->result_array();	
			return $row;
		}	
		
		function daftarhargaAula(){
			$query = $this->db->query("SELECT *
															FROM fasilitas AS a
															LEFT JOIN kategori AS b ON b.idkategori = a.idkategori
															WHERE 
															(a.waktu IN ('0', '1', '2') AND a.idkategori = '1' AND a.status = '1' AND a.harga != '0' AND a.aktif = 'Y')
															AND a.namafasilitas LIKE '%Aula Gedung Diklat%' ");	
			//$row = $hasil2x->result_array();	
			return $query;
		}

		function daftarhargaFasilitas(){
			$query = $this->db->query("SELECT *
														FROM fasilitas AS a
														LEFT JOIN kategori AS b ON b.idkategori = a.idkategori
														WHERE 
														( a.idkategori  IN ('2', '9', '4', '5', '6') AND a.status = '1' AND a.harga != '0' AND a.aktif = 'Y')
														GROUP BY a.namafasilitas ");	
			//$row = $hasil2x->result_array();	
			return $query;
		}


		function daftarhargaLoby(){
			$query = $this->db->query("SELECT *
															FROM fasilitas AS a
															LEFT JOIN kategori AS b ON b.idkategori = a.idkategori
															WHERE 
															(a.waktu IN ('0', '1', '2') AND a.idkategori = '1' AND a.status = '1' AND a.harga != '0' AND a.aktif = 'Y')
															AND a.namafasilitas LIKE '%Loby%';");	
			//$row = $hasil2x->result_array();	
			return $query;
		}
		
		function fasilitas2(){
			$this->db->select('*');
			$this->db->from('fasilitas');						
			$this->db->where('status', '1');	
			$this->db->where('waktu', '0');	
			$this->db->where('batasmax > ', '1');	
			$this->db->where('umum  ', '0');
			$this->db->where('aktif', 'Y');			
			 $query = $this->db->get();				
			return $query ;
		}	
		function fasilitasmalam(){
			$this->db->select('*');
			$this->db->from('fasilitas');						
			$this->db->where('status', '1');	
			$this->db->where('waktu', '1');	
			$this->db->where('batasmax  ', '1');	
			$this->db->where('umum  ', '0');	
			$this->db->where('aktif', 'Y');						
			 $query = $this->db->get();					
			return $query ;
		}	
		function fasilitasmalam2(){
			$this->db->select('*');
			$this->db->from('fasilitas');						
			$this->db->where('status', '1');	
			$this->db->where('waktu', '1');	
			$this->db->where('batasmax > ', '1');		
			$this->db->where('umum  ', '0');		
			$this->db->where('aktif', 'Y');						
			 $query = $this->db->get();				
			return $query ;
		}

		function fasilitassiangmalam(){
			$this->db->select('*');
			$this->db->from('fasilitas');						
			$this->db->where('status', '1');	
			$this->db->where('waktu', '2');	
			$this->db->where('batasmax  ', '1');	
			$this->db->where('umum  ', '0');
			$this->db->where('aktif', 'Y');						
			 $query = $this->db->get();					
			return $query ;
		}	
		function fasilitasmalam3(){
			$this->db->select('*');
			$this->db->from('fasilitas');						
			$this->db->where('status', '1');	
			$this->db->where('waktu', '2');	
			$this->db->where('batasmax > ', '1');		
			$this->db->where('umum  ', '0');	
   			$this->db->where('aktif', 'Y');			
			 $query = $this->db->get();				
			return $query ;
		}		

		function getidpesan(){
			$date = date('ymd');
			$check = "P".$date;
			//K2004040002
			$hasil2x= $this->db->query("select max(idpesan) as KODE1 from pesan where  idpesan LIKE '$check%'");
			foreach($hasil2x->result_array() as $row){
			//$ar1=$row->result_array();
			$id_kode1= $row['KODE1'];
			$urut1= substr($id_kode1, 7,11);
			$urut1++;
			if($urut1 == ''){$urut1 = 1;}
			$id_baru1=$check.sprintf("%04s", $urut1); 	
			return $kodetampil = $id_baru1;
			}
		}		
		function getidpesandetail(){
			$date = date('ymd');
			$check = "D".$date;
			//K2004040002
			$hasil2x= $this->db->query("select max(idpesandetail) as KODE1 from pesandetail where  idpesandetail LIKE '$check%'");
			foreach($hasil2x->result_array() as $row){
			//$ar1=$row->result_array();
			$id_kode1= $row['KODE1'];
			$urut1= substr($id_kode1, 7,11);
			$urut1++;
			if($urut1 == ''){$urut1 = 1;}
			$id_baru1=$check.sprintf("%04s", $urut1); 	
			return $kodetampil = $id_baru1;
			}
		}		
		function grafikpemakai($bln, $thn){
			$query= $this->db->query("SELECT tglpakai FROM `pesan` WHERE month(tglpakai) = '$bln' AND YEAR(tglpakai)='$thn' AND status='1' AND disetujui='1' ");	
			$cek = $query->num_rows();
			return $cek;
		}	
		function jumlahditerima($tgl){
			$this->db->select('tglpesan ');
			$this->db->from('pesan');						
			//$this->db->where('DATE(tglpesan)', $tgl);	
			$this->db->where('status', '1');			
			$this->db->where('notif', '0');			
			$query = $this->db->get();	
			$row = $query->num_rows();
			return $row ;
		}	

		function pesantanggal($status, $tgl){
			$this->db->select('*');
			$this->db->from('pesan');						
			$this->db->where('status', $status);	
			$this->db->where('notif', 0);	
			$this->db->order_by('tglpesan', 'ASC');
			$query = $this->db->get();			   
			//$row = $query->row();		
			return $query;	
		}
			function cekPilih($idpesan){			
			$query = $this->db->query("
			SELECT * FROM pesan as a  
			JOIN pesandetail as b ON b.idpesan = a.idpesan 
			WHERE  a.idpesan = '$idpesan'
			");								
			$cek = $query->num_rows();
			if($cek >0){
				return 1; 
			}else{
				return 0;
			}	
		} 
	
		function cekDataDuplicate(){
			$query= $this->db->query("SELECT * FROM `pesan` WHERE status = '1' ");
			foreach($query->result() as $row){ 
			$idpesan = $row->idpesan;
				$query1 = $this->db->query("SELECT `idpesandetail`, `idpesan`, `tanggal`, `idfasilitas`, `hari`, `jumlah`, `harga`, `subtotal` FROM `pesandetail` WHERE idpesan='$idpesan' ");	
				$cek = $query1->num_rows();
				if($cek ==0){
				$query= $this->db->query("DELETE FROM `pesan` WHERE idpesan = '$idpesan' ");
				}
			}
		}	
	
		function in($data_save,$table){
					$this->db->insert($table,$data_save);
		} 	
		function update($where,$data,$table){
				$this->db->where($where);
				$this->db->update($table,$data);
		}	
			function hapus($where,$table){
				$this->db->where($where);
				$this->db->delete($table);
		}			
}
