<?php 
class Rep_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");
			$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");
		}else{
			$this->db->select("jenispembayaran, id_smk, kelas");
			$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");
		}
		
		$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_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()->result_array();
		return $data;
	}
	
	function get_count_cicilan_siswa_ppdb($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",1);
		
		$data = $this->db->get()->result_array();
		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_list_biaya !=",NULL);
			$this->db->where("id_siswa",$id);
			$this->db->where("sts_ppdb",0);
		
		$data = $this->db->get()->result_array();
		return $data;
		
	}
	
	function get_cicilan_siswa_ppdb($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_list_biaya !=",NULL);
			$this->db->where("id_siswa",$id);
			$this->db->where("sts_ppdb",1);
		
		$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, '0' as mk from ( 
					SELECT id_siswa, sts_ppdb from trans_siswa where tanggal_pembayaran = '".$periode."' and cicilan= 1 and id_list_biaya IS NOT NULL
				) a
				UNION ALL
				
				select DISTINCT a.id_siswa, a.sts_ppdb, '1' as mk from ( 
					SELECT id_siswa, sts_ppdb from trans_siswa where tanggal_pembayaran = '".$periode."' and cicilan= 1 and id_list_biaya IS NULL
				) a
				");
		}else{
			$q = $this->db->query("select DISTINCT a.id_siswa, a.sts_ppdb, '0' as mk  from ( 
				SELECT a.id_siswa, a.sts_ppdb from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.tanggal_pembayaran = '".$periode."' and b.id_smk='".$smk."' and a.sts_ppdb=0 and cicilan= 1 and a.id_list_biaya IS NOT NULL
				UNION ALL 
				SELECT a.id_siswa, a.sts_ppdb from trans_siswa a, ppdb_siswa b where a.id_siswa=b.id_daftar and a.tanggal_pembayaran = '".$periode."' and b.id_smk='".$smk."' and a.sts_ppdb=1 and cicilan= 1 and id_list_biaya IS NOT NULL
			) a
			UNION ALL
			select DISTINCT a.id_siswa, a.sts_ppdb, '1' as mk from ( 
				SELECT a.id_siswa, a.sts_ppdb from trans_siswa a, ref_siswa b where a.id_siswa=b.id_siswa and a.tanggal_pembayaran = '".$periode."' and b.id_smk='".$smk."' and a.sts_ppdb=0 and cicilan= 1 and a.id_list_biaya IS NULL
				UNION ALL 
				SELECT a.id_siswa, a.sts_ppdb from trans_siswa a, ppdb_siswa b where a.id_siswa=b.id_daftar and a.tanggal_pembayaran = '".$periode."' and b.id_smk='".$smk."' and a.sts_ppdb=1 and cicilan= 1 and id_list_biaya IS NULL
			) a
			");
		}
		
		return $q->result_array();	
	}
	
	function get_detail($id_siswa) {
		
		$this->db->select("nama");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id_siswa);
		$data = $this->db->get()->row()->nama;
		return $data;
	}
	
	function get_detail_ppdb($id_siswa) {
		
		$this->db->select("nama");
		$this->db->from("ppdb_siswa");
		$this->db->where("id_daftar",$id_siswa);
		$data = $this->db->get()->row()->nama;
		return $data;
	}
	
	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 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 total_spp_harian($periode) {
		$q = $this->db->query("select sum(besarnya) total from trans_siswa where cicilan=1 and tanggal_pembayaran = '".$periode."' and SUBSTRING(no_faktur, 1, 2)='FM'");
		
		$q1 = $this->db->query("select sum(besarnya) total from trans_siswa where cicilan=1 and tanggal_pembayaran = '".$periode."' and SUBSTRING(no_faktur, 1, 2)='FK'");
		
		$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_spp($id_siswa, $id, $mk) {
		if($mk=='0'){
			$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 and id_list_biaya IS NOT NULL");
			
		}else{
			$q = $this->db->query("select sum(besarnya) total from trans_siswa where cicilan=1 and id_siswa = ".$id_siswa."  and sts_ppdb=0 and id_list_biaya IS NULL");
		}
		
		return $q->row()->total;
	}
	
	function get_total_spp_ppdb($id_siswa, $id, $mk) {
		if($mk=='0'){
			$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=1 and id_list_biaya IS NOT NULL");
		}else{
			$q = $this->db->query("select sum(besarnya) total from trans_siswa where cicilan=1 and id_siswa = ".$id_siswa." and sts_ppdb=1 and id_list_biaya IS NULL");
		}
		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."'" );
		}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 );
		}
		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();
	}
	
}