<?php 
class Rep_spp_model_harian extends CI_Model {
	
	function get_header() {
		
		$tahun_ajaran = $this->get_tahun_ajaran();
		//get ref transaksi
		$this->db->select("jenispembayaran");
		$this->db->from("ref_list_biaya");
		$this->db->where("tahun_ajaran",$tahun_ajaran);
		
		$data = $this->db->get()->result_array();
		return $data;
		
	}
	
	function get_tahun_ajaran() {
		$this->db->select("tahun_ajaran");
		$this->db->from("ref_tahun_ajaran");
		$this->db->where("status","U");
		$data = $this->db->get()->row()->tahun_ajaran;
		return $data;
	}
	
	function get_id_siswa($periode, $smk) {
		if($smk==''){
			$q = $this->db->query("
				select DISTINCT a.id_siswa, a.sts_ppdb from ( 
					SELECT id_siswa, sts_ppdb from trans_spp where tanggal_transaksi = '".$periode."' 
				) a");
		}else{
			$q = $this->db->query("select DISTINCT a.id_siswa, a.sts_ppdb from ( 
					SELECT a.id_siswa, a.sts_ppdb from trans_spp a, ref_siswa b where a.id_siswa=b.id_siswa and tanggal_transaksi = '".$periode."' and b.id_smk='".$smk."' and a.sts_ppdb='0'
			) a
			
			UNION ALL
				select DISTINCT a.id_siswa, a.sts_ppdb from ( 
					SELECT a.id_siswa, a.sts_ppdb from trans_spp a, ppdb_siswa b where a.id_siswa=b.id_daftar and a.tanggal_transaksi = '".$periode."' and b.id_smk='".$smk."' and a.sts_ppdb='1'
			) a
			
			");
			
		}
		
		return $q->result_array();	
	}
	
	function total_spp_harian($periode, $smk) {
		if($smk==''){
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ref_siswa b where a.tanggal_transaksi = '".$periode."' and a.id_siswa=b.id_siswa and sts_ppdb=0");
		}else{
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ref_siswa b where a.tanggal_transaksi = '".$periode."' and a.id_siswa=b.id_siswa and b.id_smk='".$smk."' and a.sts_ppdb=0");
		}
		
		return $q->row()->total; 
	}
	
	function total_spp_harian_ppdb($periode, $smk) {
		if($smk==''){
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ppdb_siswa b where a.tanggal_transaksi = '".$periode."' and a.id_siswa=b.id_daftar and sts_ppdb=1");
		}else{
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ppdb_siswa b where a.tanggal_transaksi = '".$periode."' and a.id_siswa=b.id_daftar and b.id_smk='".$smk."' and a.sts_ppdb=1");
		}
		
		return $q->row()->total; 
	}
	
	function get_detail($id_siswa) {
		
		//get nama siswa
		$this->db->select("nama, nis");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id_siswa);
		$return = $this->db->get()->row();
		
		/* //get trans siswa
		$this->db->select("jenis_pembayaran,besarnya");
		$this->db->from("trans_siswa");
		$this->db->where("id_siswa",$id_siswa);
		$t_sis = $this->db->get()->result_array();
		
		foreach ($t_sis as $v_sis) {
			$return .= '--'.$v_sis['jenis_pembayaran'].' - '.$v_sis['besarnya'].'<br>';		
		}

		//get pem spp 	
		$this->db->select("bulan,besarnya");
		$this->db->from("trans_spp");
		$this->db->where("id_siswa",$id_siswa);
		$t_spp = $this->db->get()->result_array();
		
		foreach ($t_spp as $v_spp) {
			$return .= '-- SPP Bulan '.$v_spp['bulan'].' - '.$v_spp['besarnya'].'<br>';		
		} */
		
		return $return;
			
	}
	
	function get_detail_ppdb($id_siswa) {
		
		//get nama siswa
		$this->db->select("nama, nisn");
		$this->db->from("ppdb_siswa");
		$this->db->where("id_daftar",$id_siswa);
		$return = $this->db->get()->row();
		
		return $return;
			
	}
	function getDataSMK(){
		$this->db->select('*');
		$this->db->from("ref_smk");
		$num = $this->db->get()->result_array();
		return $num;
	}
	function get_kelas($id_siswa) {
		 $q = $this->db->query("select a.kelas, b.ruang_kelas, c.nama_jurusan  from ref_siswa a, ref_mapping_kelas b, ref_jur c where a.id_mapping_kelas =b.id and b.id_jur=c.id and a.id_siswa=".$id_siswa);
		//echo $this->db->last_query(); die(); 					
		return $q->row();					
	}
	
	function get_kelas_ppdb($id_siswa) {
		 $q = $this->db->query("SELECT a.kelas, b.nama_jurusan FROM ppdb_siswa a, ref_jur b WHERE a.id_jur=b.id and id_daftar=".$id_siswa);
		//echo $this->db->last_query(); die(); 					
		return $q->row();					
	}
	
	function get_ruang_kelas($id_siswa) {
		/*
		$this->db->select("id_wali_kelas");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id_siswa);
		$id_wali = $this->db->get()->row()->id_wali_kelas;
		
		$this->db->select("ruang_kelas");
		$this->db->from("ref_mapping_kelas");
		$this->db->where("id_karyawan",$id_wali);		
		$ruang = $this->db->get()->row()->ruang_kelas;
		*/
		
		//echo $ruang; die();
		//echo $this->db->last_query(); die();
		//return $ruang;
		
		 $q = $this->db->query("select b.name ruang_kelas from ref_siswa a, ref_smk b where a.id_smk=b.id and a.id_siswa=".$id_siswa);
		//echo $this->db->last_query(); die(); 					
		return $q->row()->ruang_kelas;					
	}
	
	function get_ruang_kelas_ppdb($id_siswa) {
		 $q = $this->db->query("select b.name ruang_kelas from ppdb_siswa a, ref_smk b where a.id_smk=b.id and a.id_daftar=".$id_siswa);
		//echo $this->db->last_query(); die(); 					
		return $q->row()->ruang_kelas;					
	}
	
	
	
	function get_total_spp($id_siswa, $periode) {
		$q = $this->db->query("select tanggal_transaksi, sum(besarnya) as total, no_faktur, tahun_ajaran from trans_spp where id_siswa = ".$id_siswa." and tanggal_transaksi = '".$periode."' and sts_ppdb=0 GROUP BY tanggal_transaksi, no_faktur");
		return $q->result_array(); 
		
	}
	
	function get_total_spp_ppdb($id_siswa, $periode) {
		$q = $this->db->query("select tanggal_transaksi, sum(besarnya) as total, no_faktur, tahun_ajaran from trans_spp where id_siswa = ".$id_siswa." and tanggal_transaksi = '".$periode."' and sts_ppdb=1 GROUP BY tanggal_transaksi, no_faktur");
		return $q->result_array(); 
		
	}
	function get_numrow_spp($id_siswa, $periode) {
		$q = $this->db->query("select tanggal_transaksi, sum(besarnya) as total, no_faktur from trans_spp where id_siswa = ".$id_siswa." and tanggal_transaksi = '".$periode."' and sts_ppdb=0 GROUP BY tanggal_transaksi, no_faktur");
		
$num_rows = $q->num_rows();


		return $num_rows; 
		
	}
	function get_numrow_spp_ppdb($id_siswa, $periode) {
		$q = $this->db->query("select tanggal_transaksi, sum(besarnya) as total, no_faktur from trans_spp where id_siswa = ".$id_siswa." and tanggal_transaksi = '".$periode."' and sts_ppdb=1 GROUP BY tanggal_transaksi, no_faktur");
		
$num_rows = $q->num_rows();


		return $num_rows; 
		
	}
	
	function get_numrow_spp_bln_ppdb($faktur) {
		$q = $this->db->query("select bulan, tahun_ajaran from trans_spp where no_faktur = '".$faktur."' and sts_ppdb=1");
				
		$num_rows = $q->result_array();
		$html='';
		foreach ($num_rows as $nr) {
			$html.=$nr['bulan']." (".$nr['tahun_ajaran'].")<br>";
		}
		return $html; 
		
	}
	
	function get_numrow_spp_bln($faktur) {
		$q = $this->db->query("select bulan, tahun_ajaran from trans_spp where no_faktur = '".$faktur."' and sts_ppdb=0");
				
		$num_rows = $q->result_array();
		$html='';
		foreach ($num_rows as $nr) {
			$html.=$nr['bulan']." (".$nr['tahun_ajaran'].")<br>";
		}
		return $html; 
		
	}
	
	function get_total_trans_harian($jenis,$periode, $smk) {
		if($smk==''){
			$q = $this->db->query("select sum(besarnya) total from trans_siswa where jenis_pembayaran = '".$jenis."' and tanggal_pembayaran = '".$periode."'" );
		}else{
			
			$q = $this->db->query("select sum(besarnya) total from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.jenis_pembayaran = '".$jenis."' and a.tanggal_pembayaran = '".$periode."' and b.id_smk=".$smk );
		}
		return $q->row()->total;
	}
	
	function get_total_trans($id_siswa,$jenis) {
		$q = $this->db->query("select sum(besarnya) total from trans_siswa where id_siswa = ".$id_siswa." and jenis_pembayaran = '".$jenis."'");
		return $q->row()->total;
	}
	
}