<?php
class M_laporan extends CI_Model{
	function tampillaporan($status, $bln, $thn){
		if($status=='ALL'){
			$query = $this->db->query("
			select *
			from pesan					
			where MONTH (tglpakai) = '$bln' AND 
			YEAR (tglpakai)='$thn' 
			");	
		}else{
			$query = $this->db->query("
			select *
			from pesan					
			where MONTH (tglpakai) = '$bln' AND 
			YEAR (tglpakai)='$thn'  AND status = '$status'
			");		
		}   
		//$row = $query->row();		
		return $query;		
		}

	function tampillaporanLunas($status, $bln, $thn){
			$query = $this->db->query("
			select *
			from pesan					
			where MONTH (tglpakai) = '$bln' AND 
			YEAR (tglpakai)='$thn'  AND status = '1' AND statusdp = '1'  AND disetujui = '1'
			");		  		
		return $query;		
		}

	function tampillaporanBelumLunas($status, $bln, $thn){
			$query = $this->db->query("
			select *
			from pesan					
			where MONTH (tglpakai) = '$bln' AND 
			YEAR (tglpakai)='$thn'  AND status = '1' AND statusdp = '0'  AND disetujui = '1'
			");		  		
		return $query;		
		}

	function tampillaporanBelumLunasAll($status, $bln, $thn){
			$query = $this->db->query("
			select *
			from pesan					
			where  status = '1' AND statusdp = '0'  AND disetujui = '1'
			");		  		
		return $query;		
		}

	function tampillaporanLunasAll($status, $bln, $thn){
			$query = $this->db->query("
			select *
			from pesan					
			where  status = '1' AND statusdp = '1'  AND disetujui = '1' AND YEAR (tglpakai)='$thn' 
			");		  		
		return $query;		
		}
		
	function tampillaporanLunasSampai($status, $bln1, $bln2, $thn){
			$query = $this->db->query("
			select *
			from pesan					
			WHERE MONTH(tglpakai) BETWEEN '$bln1' AND '$bln2' AND YEAR(tglpakai) = '$thn'  
			AND status = '1' AND statusdp = '1'  AND disetujui = '1'
			");		  		
		return $query;		
		}	
		
	function tampillaporanBelumLunasSampai($status, $bln1, $bln2, $thn){
			$query = $this->db->query("
			select *
			from pesan					
			WHERE MONTH(tglpakai) BETWEEN '$bln1' AND '$bln2' AND YEAR(tglpakai) = '$thn'  
			AND status = '1' AND statusdp = '0'  AND disetujui = '1'
			");		  		
		return $query;		
		}
		
		function tampillaporandetail($idpesan){
			$this->db->select('c.namafasilitas, b.harga, b.jumlah, b.subtotal, a.idpesan ');
			$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);
			$query = $this->db->get();			   	
			return $query;	
		}	

	
 		function fasilitas(){
			$this->db->select('*');
			$this->db->from('fasilitas');						
			$this->db->where('status', '1');
			$this->db->order_by("waktu", "ASC");
			$query = $this->db->get();			   	
			return $query;		
		}	

		function jumlahpesan($idfasilitas, $thn){
			$query = $this->db->query("
			SELECT COUNT(*) as jumlah 
			FROM pesandetail as a 
			JOIN pesan as b ON b.idpesan =a.idpesan 
			JOIN fasilitas as c ON c.idfasilitas = a.idfasilitas
			WHERE b.disetujui = '1' AND b.status = '1' 
			AND c.idfasilitas = '$idfasilitas'  AND b.tglpakai LIKE '%$thn%'
			");								
			$row = $query->row();		
			return $row;		
		}

		function total($idpesan){
			$query = $this->db->query("
			SELECT SUM(b.subtotal) as total FROM pesan as a 
			LEFT JOIN pesandetail as b ON b.idpesan = a.idpesan 
			WHERE a.idpesan = '$idpesan'
			");								
			$row = $query->row();		
			return $row;		
		}

		function tampilfasilitas($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  a.idpesan = '$idpesan'
											GROUP BY b.idfasilitas
											");	
			//$row = $hasil2x->result_array();	
			return $hasil2x;
		}
		
		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);
		}				
}