<?php 
class Rep_tunggakan_sis_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");
			$this->db->order_by("periode","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");
			$this->db->order_by("periode","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");
		$this->db->where("sts_ppdb",0);
		$data = $this->db->get()->row()->tahun_ajaran;
		return $data;
	}
	
	function get_id_siswa($smk, $id) {
		$q = $this->db->query("
				select * from ref_siswa where status=1 and id_smk='".$smk."' and id_siswa='".$id."'");
		//$data = $this->db->get()->row()->tahun_ajaran;	
		return $q->row();
	}
	
	function get_thn_ajaran($id_siswa) {
		$q = $this->db->query("
				SELECT DISTINCT tahun_ajaran FROM rep_spp WHERE id_siswa='".$id_siswa."' order by tahun_ajaran");
		$data = $q->result_array();
		return $data;	
	}
	
	function get_jum_thn_ajaran($id_siswa) {
		$q = $this->db->query("
				SELECT DISTINCT tahun_ajaran FROM rep_spp WHERE id_siswa='".$id_siswa."' order by tahun_ajaran");
		$data = $q->num_rows();
		return $data;	
	}
	
	function get_spp_rep($id_siswa, $thn) {
		//$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",$thn);
		$data = $this->db->get()->row();
		return $data;	
	}
	
	function get_detail($id_siswa) {
		//get nama siswa
		$this->db->select("nama");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$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 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_spp($id_siswa) {
		$q = $this->db->query("select sum(besarnya) total from trans_spp where id_siswa = ".$id_siswa);
		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,$id, $thn) {
		$q = $this->db->query("select sum(besarnya) as total, b.id_smk, id_list_biaya from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.id_siswa = ".$id_siswa." and a.id_list_biaya = '".$id."' and a.tahun_ajaran ='".$thn."' and cicilan=0 order by id_list_biaya desc");
		$data = $q->row();
		return $data;
	}
	
	function get_total_trans_opsi($id_siswa,$id, $thn) {
		$q = $this->db->query("select sum(besarnya) as total, b.id_smk, id_list_biaya from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.id_siswa = ".$id_siswa." and a.id_list_biaya = '".$id."' and a.tahun_ajaran ='".$thn."' order by id_list_biaya desc");
		$data = $q->row();
		return $data;
	}
	
	function get_total_trans_cicilan($id_siswa,$id) {
		$q = $this->db->query("select sum(besarnya) as total, b.id_smk, id_list_biaya from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.id_siswa = ".$id_siswa." and a.id_list_biaya = '".$id."' and cicilan=1 and sts_ppdb=0 and SUBSTRING(a.no_faktur, 1, 2)='FM' order by id_list_biaya desc");
		
		$q1 = $this->db->query("select sum(besarnya) as total, b.id_smk, id_list_biaya from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.id_siswa = ".$id_siswa." and a.kodesub = '1' and a.kodedesk = 'III' and cicilan=1 and sts_ppdb=0 and SUBSTRING(a.no_faktur, 1, 2)='FK' order by id_list_biaya desc");
		$temp1=0;
		$temp2=0;
		if(($q->row()->total==0)||($q->row()->total==NULL)){
			$temp1 = 0;
		}else{
			$temp1 = $q->row()->total; 
		}
		
		if(($q1->row()->total==0)||($q1->row()->total==NULL)){
			$temp2 = 0;
		}else{
			$temp2 = $q1->row()->total; 
		}
		//print_r($temp1);die();
		return $temp1-$temp2;
	}
	
	function get_total_cicilan($id_siswa,$id, $thn) {
		$q = $this->db->query("select besarnya as total, b.id_smk, id_list_biaya from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.id_siswa = ".$id_siswa." and a.id_list_biaya = '".$id."' and a.tahun_ajaran ='".$thn."' and cicilan = 1 order by id_list_biaya desc");
		$data = $q->row();
		return $data;
	}
	
	function get_total_map_biaya_siswa($id_siswa, $thn) {
		$q = $this->db->query("select id, jenis_pembayaran, besarnya as total, id_smk, periode, dana_opsional, kelas, tahun_ajaran from ref_mapping_biaya_siswa where id_siswa = ".$id_siswa." and tahun_ajaran = '".$thn."'  and sts_dsp=0 order by kelas asc, periode asc");
		$data = $q->result_array();
		return $data;
	}
	
	function get_total_map_cicilan_siswa($id_siswa, $thn) {
		$q = $this->db->query("select id, jenis_pembayaran, besarnya as total, id_smk from ref_mapping_biaya_siswa where id_siswa = ".$id_siswa." and sts_dsp=1 order by id asc");
		$data = $q->result_array();
		return $data;
	}
	
	function get_total_map_biaya_siswa_smk($id_siswa, $jenis, $id_smk, $kelas) {
		$q = $this->db->query("select sum(besarnya) total, id_smk from ref_mapping_biaya_siswa where id_siswa = ".$id_siswa." and jenis_pembayaran = '".$jenis."' and id_smk='".$id_smk."' and sts_dsp=0 order by id desc");
		$data = $q->row();
		return $data;
	}
	
	
	function getSis($id_smk) {
		///
		$return = '';
		$q = $this->db->query("SELECT * FROM ref_siswa WHERE id_smk='".$id_smk."' and status=1");
		$data = $q->result_array();
		foreach ($data as $val) {
			$return .='<option value="'.$val['id_siswa'].'">'.$val['nama'].' ( '.$val['nis'].' )'.'</option>';
		} 
		return $return;
		///
		/*
		$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;
	}
}