<?php
class M_pesan extends CI_Model{
	
	
		function pesan($status){
			$this->db->select('*');
			$this->db->from('pesan as a ');						
			$this->db->join('pesandetail as b', 'b.idpesan = a.idpesan', '');
			$this->db->where('a.status', $status);	
			$this->db->order_by('a.tglpakai', 'DESC');	
			$this->db->group_by('a.idpesan');
			$query = $this->db->get();			   	
			return $query;		
		}	
		function pesan_lama(){
			$this->db->select('*');
			$this->db->from('pesan as a ');						
			$this->db->join('pesandetail as b', 'b.idpesan = a.idpesan', '');
			$this->db->where('a.status', '1');	
			$this->db->where('a.disetujui','0');	
			$this->db->order_by('a.tglpakai', 'DESC');	
			$query = $this->db->get();			   	
			return $query;		
		}	
		function pesandetail($idpesan){
			$this->db->select('*');
			$this->db->from('pesan');						
			$this->db->where('idpesan', $idpesan);	
			 $query = $this->db->get();			   
			return $query;	
		}
		function fasilitaspilih($idfasilitas, $idpesan){
			$idfasilitas;
			$this->db->select('*');
			$this->db->from('pesandetail');						
			$this->db->where('idfasilitas', $idfasilitas);
			$this->db->where('idpesan', $idpesan);
			$query = $this->db->get();			   
			$row = $query->row();		
			return $row;	
		}		
		function totalharga($idpesan){
			$this->db->select('SUM(subtotal) as totalharga');
			$this->db->from('pesandetail');
			$this->db->where('idpesan', $idpesan);
			$query = $this->db->get();						   
			$row = $query->row();		
			return $row;	
		}	
		/*
			SELECT c.namafasilitas, b.harga 
			FROM pesan as a 
			LEFT JOIN pesandetail as b ON b.idpesan = a.idpesan
			LEFT JOIN fasilitas as c ON c.idfasilitas = b.idfasilitas
			WHERE a.idpesan = ''		
		*/
		function cetakpesan($idpesan){
			$this->db->select('c.namafasilitas, b.harga, b.jumlah, b.subtotal, a.pemkot');
			$this->db->from('pesan as a ');						
			$this->db->join('pesandetail as b', 'b.idpesan = a.idpesan', 'left');
			$this->db->join('fasilitas as c', 'c.idfasilitas = b.idfasilitas', 'left');
			$this->db->where('a.idpesan', $idpesan);
			$this->db->group_by('c.idfasilitas');
			$query = $this->db->get();			   	
			return $query;	
		}
		function detailfasilitas($idpesan){
			$query = $this->db->query("
			SELECT a.idpesan, a.tglpakai, a.tglselesai, c.idkategori, c.namafasilitas
			FROM pesan as a 
			JOIN pesandetail as b ON b.idpesan = a.idpesan 
			JOIN fasilitas as c ON c.idfasilitas =  b.idfasilitas
			WHERE  a.idpesan = '$idpesan' GROUP BY b.idfasilitas
			");								
			$cek = $query->num_rows();		   	
			return $query;	
		}
		
		function ttdpesan($bagian){
			$this->db->select('*');
			$this->db->from('ttd');						
			$this->db->where('status', $bagian);
			 $query = $this->db->get();			   
			$row = $query->row();		
			return $row;	
		}	
		function jadwalaktif($bln,$thn){
			$this->db->select('*');
			$this->db->from('pesan');						
			$this->db->where('MONTH(tglpakai)', $bln);	
			$this->db->where('YEAR(tglpakai)', $thn);	
			$this->db->where('status', '1');	
			$this->db->where('disetujui', '1');	
			$this->db->order_by("tglpakai", "ASC");
			$query = $this->db->get();			   	
			return $query;	
		} 	

		function jadwalfull(){
			$this->db->select('*');
			$this->db->from('pesan');						
			$this->db->where('status', '1');	
			$this->db->where('disetujui', '1');	
			$this->db->order_by("tglpakai", "ASC");
			$query = $this->db->get();			   	
			return $query;	
		} 		
		/*
		aula ->siang/malam
		kelas->siang/malam
		*/
		function cektanggal($tglpakai, $tglselesai, $waktu, $idkategori){			
			$query = $this->db->query("
			SELECT a.idpesan, a.tglpakai, a.tglselesai, c.idkategori FROM pesan as a 
			JOIN pesandetail as b ON b.idpesan = a.idpesan 
			JOIN fasilitas as c ON c.idfasilitas =  b.idfasilitas
			WHERE  
			a.tglpakai<='$tglpakai'  AND  a.tglselesai >= '$tglselesai'  AND a.status='1'  AND c.idkategori ='$idkategori'
			OR 
			a.tglpakai>='$tglpakai'  AND   a.tglpakai <= '$tglselesai'  AND a.status='1' AND c.idkategori ='$idkategori'
			");								
			$cek = $query->num_rows();
			if($cek >0){
				return 1; 
			}else{
				return 0;
			}	
		} 
		function cektanggal2($tglpakai, $tglselesai, $waktu, $idkategori, $idpesan){	
			$query = $this->db->query("
			SELECT a.idpesan, a.tglpakai, a.tglselesai, c.idkategori FROM pesan as a 
			JOIN pesandetail as b ON b.idpesan = a.idpesan 
			JOIN fasilitas as c ON c.idfasilitas =  b.idfasilitas
			WHERE  
			a.tglpakai<='$tglpakai'  AND  a.tglselesai >= '$tglselesai'  AND a.status='1'  AND c.idkategori ='$idkategori' AND a.idpesan !='$idpesan'
			OR 
			a.tglpakai>='$tglpakai'  AND   a.tglpakai <= '$tglselesai'  AND a.status='1' AND c.idkategori ='$idkategori' AND a.idpesan !='$idpesan'
			");								
			$cek = $query->num_rows();
			if($cek >0){
				return 1; 
			}else{
				return 0;
			}	
		} 	
		function ambiltanpaidkategori($idfasilitas){
			$idfasilitas;
			/*
			$this->db->select('idkategori');
			$this->db->from('fasilitas  ');
			$this->db->where('idfasilitas', $idfasilitas);		
			$this->db->where('idkategori', '!="1" ');		
			$this->db->where('idkategori', '!="1" ');		
			$query = $this->db->get();			   
			$row = $query->row();	
			return		$row;	
			*/
			//$query = $this->db->get();			   
			$query = $this->db->query("select idkategori from fasilitas where
			idfasilitas = '$idfasilitas' AND wajib ='1'  ");								
			$cek = $query->num_rows();
			if($cek >0){
				return 1; 
			}else{
				return 0;
			}			
		}		
		function ambilidkategori($idfasilitas){
			$idfasilitas;
			/*
			$this->db->select('idkategori');
			$this->db->from('fasilitas  ');
			$this->db->where('idfasilitas', $idfasilitas);		
			$this->db->where('idkategori', '!="1" ');		
			$this->db->where('idkategori', '!="1" ');		
			$query = $this->db->get();			   
			$row = $query->row();	
			return		$row;	
			*/
			//$query = $this->db->get();			   
			$query = $this->db->query("select idkategori from fasilitas where
			idfasilitas = '$idfasilitas' AND wajib ='1'  ");								
			$row = $query->row();     		
			return $row;				
		}	

		function tglpesan($idpesan){		   
			$query = $this->db->query("SELECT * FROM `pesan` WHERE idpesan = '$idpesan' ");								
			$row = $query->row();     		
			return $row->tglpesan;				
		}


		function totalharga_lunas($idpesan){
			$this->db->select('SUM(harga) as totalharga');
			$this->db->from('pesandetail');
			$this->db->where('idpesan', $idpesan);
			$query = $this->db->get();						   
			$row = $query->row();		
			return $row->totalharga;	
		}
		
		function pamkot($idpesan){		   
			$query = $this->db->query("SELECT * FROM `pesan` WHERE idpesan = '$idpesan' ");										
			$row = $query->row();     		
			return $row->pamkot;				
		}

		function hitungJumlahBaris() {
			// Melakukan query untuk mengambil semua baris yang memenuhi kondisi
			$query = $this->db->query("
				SELECT * 
				FROM `pesan` 
				WHERE `status` = 1 
				AND `statusdp` = 1 
				AND `disetujui` = 1 
				AND `tglpakai` BETWEEN '2024-01-01' AND CURDATE()
			");
			
			// Menghitung jumlah baris yang dihasilkan oleh query
			$jumlah_baris = $query->num_rows();
			
			// Mengembalikan jumlah baris
			return $jumlah_baris +1;
		}

		function noskrdbaru(){		   
			$tahun = date('Y');
			$query = $this->db->query("SELECT noskrd 
															FROM pesan  
															WHERE YEAR(tglpakai) = '$tahun'  
															ORDER BY `pesan`.`noskrd` DESC ");										
			$row = $query->row();     		
			return $row->noskrd+1;				
		}

		
		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);
		}			
} 
