<?php 
class Rep_tunggakan_ppdb_model extends CI_Model {
	
	function get_header($id_smk) {
		
		$tahun_ajaran = $this->get_tahun_ajaran();
		//get ref transaksi
		if($id_smk=='' || empty($id_smk)){
			$this->db->select("jenispembayaran, id_smk, kelas, besarnya, periode");
			$this->db->from("ref_list_biaya");
			//$this->db->where("tahun_ajaran",$tahun_ajaran);
			$this->db->order_by("id_smk","asc");
			$this->db->order_by("kelas","asc");
			$this->db->order_by("periode","asc");
		}else{
			$this->db->select("jenispembayaran, id_smk, kelas, besarnya, periode");
			$this->db->from("ref_list_biaya");
			//$this->db->where("tahun_ajaran",$tahun_ajaran);
			$this->db->where("id_smk",$id_smk);
			$this->db->order_by("id_smk","asc");
			$this->db->order_by("kelas","asc");
			$this->db->order_by("periode","asc");
		}
		
		$data = $this->db->get()->result_array();
		return $data;
		
	}
	

	function get_count_header($id_smk) {
		
		$tahun_ajaran = $this->get_tahun_ajaran();
		//get ref transaksi
		if(($id_smk=='') || (empty($id_smk))){
			$this->db->select("id_smk, id_smk, count(distinct jenispembayaran) as jenis_pem");
			$this->db->from("ref_list_biaya");
			//$this->db->where("tahun_ajaran",$tahun_ajaran);
			$this->db->group_by('id_smk');
			$this->db->order_by("id_smk","asc");
		}else{
			$this->db->select("id_smk, id_smk, count(distinct jenispembayaran) as jenis_pem");
			$this->db->from("ref_list_biaya");
			//$this->db->where("tahun_ajaran",$tahun_ajaran);
			$this->db->where("id_smk",$id_smk);
			$this->db->group_by('id_smk');
			$this->db->order_by("id_smk","asc");
		}
		
		$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($smk, $jur) {
		if($smk==''){
			$q = $this->db->query("
				select * from ppdb_siswa where (approval_status=0 or approval_status=1)");
		}else if($jur==''){
			$q = $this->db->query("
				select * from ppdb_siswa where id_smk='".$smk."' and (approval_status=0 or approval_status=1)");
		}else{
			$q = $this->db->query("
				select * from ppdb_siswa where id_smk='".$smk."' and id_jur='".$jur."' and (approval_status=0 or approval_status=1)");
		}
		
		return $q->result_array();	
	}
	
	function get_count_cicilan_siswa($id) {
		$this->db->select("count(DISTINCT(id_list_biaya)) as jml");
		$this->db->from("trans_siswa");
		$this->db->where("cicilan",1);
		$this->db->where("id_siswa",$id);
		$this->db->where("sts_ppdb",0);
		
		$data = $this->db->get()->row()->jml;
		return $data;
	}
	
	
	function get_cicilan_siswa($id) {
		$this->db->select("DISTINCT(id_list_biaya) id, jenis_pembayaran");
		$this->db->from("trans_siswa");
		$this->db->where("cicilan",1);
		$this->db->where("id_siswa",$id);
		$this->db->where("sts_ppdb",0);
	
		$data = $this->db->get()->result_array();
		return $data;
	}
	
	function get_total_spp($id_siswa, $id) {
		$q = $this->db->query("select sum(besarnya) total from trans_siswa where cicilan=1 and id_siswa = ".$id_siswa." and id_list_biaya=".$id." and sts_ppdb=0");
		if(empty($q->row()->total)||$q->row()->total==NULL){
			return 0;
		}else{
			return $q->row()->total; 
		}
		
		
	}
	
	function get_spp_rep($id_siswa) {
		$tahun_ajaran = $this->get_tahun_ajaran();
		
		$this->db->select("*");
		$this->db->from("rep_spp");
		$this->db->where("id_siswa",$id_siswa);
		//$this->db->where("tahun_ajaran",$tahun_ajaran);
		$data = $this->db->get()->result_array();
		return $data;	
	}
	
	function get_detail($id_siswa) {
		//get nama siswa
		$this->db->select("nama");
		$this->db->from("ppdb_siswa");
		$this->db->where("id_daftar",$id_siswa);
		$return = $this->db->get()->row()->nama.'<br>';
		
		/* //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 getDataSMK(){
		$this->db->select('*');
		$this->db->from("ref_smk");
		$num = $this->db->get()->result_array();
		return $num;
	}
	
	function getSMK($id){
		$this->db->select('name');
		$this->db->from("ref_smk");
		$this->db->where("id",$id);
		$return = $this->db->get()->row();
		return $return;
	}
	
	function getJrs($id){
		$this->db->select('nama_jurusan');
		$this->db->from("ref_jur");
		$this->db->where("id",$id);
		$return = $this->db->get()->row();
		return $return;
	}
	
	function get_kelas($id_siswa) {
		 $q = $this->db->query("select kelas from ref_siswa where id_siswa=".$id_siswa);
		//echo $this->db->last_query(); die(); 					
		return $q->row()->kelas;					
	}
	
	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 total_spp_harian($periode) {
		$q = $this->db->query("select sum(besarnya) total from trans_spp where tanggal_transaksi = '".$periode."'");
		return $q->row()->total; 
	}
	
	function get_total_ppdb($id_siswa) {
		$q = $this->db->query("select sum(besarnya) total from trans_siswa where id_siswa = ".$id_siswa." and jenis_pembayaran ='Cicilan PPDB'");
		return $q->row()->total; 
		
	}
	
	function get_total_trans_harian($jenis,$periode, $smk) {
		if($smk==''){
			/*
			$q = $this->db->query("select sum(a.besarnya) total, b.id_smk from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.jenis_pembayaran = '".$jenis."' and a.tanggal_pembayaran = '".$periode."'" );
			*/
			$q = $this->db->query("select sum(a.besarnya) total, b.id_smk from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.jenis_pembayaran = '".$jenis."'" );
		}else{
			/*
			$q = $this->db->query("select sum(a.besarnya) total, b.id_smk 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 );
			*/
			$q = $this->db->query("select sum(a.besarnya) total, b.id_smk from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.jenis_pembayaran = '".$jenis."' and b.id_smk=".$smk );
		}
		return $q->row();
	}
	
	function get_total_trans($id_siswa,$jenis) {
		$q = $this->db->query("select sum(besarnya) total, b.id_smk from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.id_siswa = ".$id_siswa." and a.jenis_pembayaran = '".$jenis."'");
		return $q->row();
	}
	
	function getJurusan($id_smk) {
		$return = '';
		$q = $this->db->query("select a.name, a.id as id_smk, b.id as id_jur, b.nama_jurusan from ref_smk a, ref_jur b where a.id = b.id_smk and a.id='".$id_smk."'");
		
		$data = $q->result_array();
		foreach ($data as $val) {
			$return .='<option value="'.$val['id_jur'].'">'.$val['nama_jurusan'].'</option>';
		} 
		return $return;
	}
	function cekKelas(){
		$return='';
		$this->db->select('*');
		$this->db->from("ref_kelas");
		$num = $this->db->get()->result_array();
		
		foreach ($num as $v_kls) {
			$return.='<option value="'.$v_kls['kelas'].'">Kelas '.$v_kls['kelas'].'</option>';
		}
								
		return $return;
	}
	function getRuang($kelas, $id_mapping_jur) {
		$return = '';
		$q = $this->db->query("select a.nama_karyawan, b.id, b.ruang_kelas from ref_karyawan a, ref_mapping_kelas b 
		where a.id = b.id_karyawan
		and b.kelas = '".$kelas."' and b.id_jur='".$id_mapping_jur."'");
		
		
		
		$data = $q->result_array();
		foreach ($data as $val) {
			$return .='<option value="'.$val['ruang_kelas'].'">'.$val['ruang_kelas'].'</option>';
		} 
		return $return;
	}
}