<?php 
class Rep_spp_model_tahun 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) {
		$date = new DateTime($periode);
		$resultM = $date->format('m');
		$resultY = $date->format('Y');
		
		if($smk==''){
			
			$q = $this->db->query("
				select DISTINCT MONTHNAME(STR_TO_DATE(a.bln, '%m')) mn_name, a.bln from (SELECT month(tanggal_transaksi) bln FROM trans_spp WHERE  year(tanggal_transaksi)='".$resultY."')a ORDER BY a.bln");
		}else{
			$q = $this->db->query("select * from (select DISTINCT MONTHNAME(STR_TO_DATE(c.bln, '%m')) mn_name, c.bln from (SELECT month(a.tanggal_transaksi) bln FROM trans_spp a, ref_siswa b  WHERE a.id_siswa=b.id_siswa and year(a.tanggal_transaksi)='".$resultY."' and b.id_smk='".$smk."')c
			UNION ALL
			select DISTINCT MONTHNAME(STR_TO_DATE(c.bln, '%m')) mn_name, c.bln from (SELECT month(a.tanggal_transaksi) bln FROM trans_spp a, ppdb_siswa b  WHERE a.id_siswa=b.id_daftar and year(a.tanggal_transaksi)='".$resultY."' and b.id_smk='".$smk."')c )d order by d.bln
			");
			
			
		}
		
		return $q->result_array();	
	}
	
	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 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_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, $smk) {
		$date = new DateTime($periode);
		$resultM = $date->format('m');
		$resultY = $date->format('Y');
		
		
		
		if($smk==''){
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ref_siswa b where  year(a.tanggal_transaksi)='".$resultY."' and a.id_siswa=b.id_siswa and a.sts_ppdb=0");
		}else{
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ref_siswa b where  year(a.tanggal_transaksi)='".$resultY."' 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) {
		$date = new DateTime($periode);
		$resultM = $date->format('m');
		$resultY = $date->format('Y');
		
		if($smk==''){
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ppdb_siswa b where year(a.tanggal_transaksi)='".$resultY."' and a.id_siswa=b.id_daftar and a.sts_ppdb=1");
		}else{
			$q = $this->db->query("select sum(besarnya) total from trans_spp a, ppdb_siswa b where year(a.tanggal_transaksi)='".$resultY."' and a.id_siswa=b.id_daftar and b.id_smk='".$smk."' and a.sts_ppdb=1");
		}
		
		return $q->row()->total; 
	}
	
	function get_total_spp($periode, $smk) {
		
		if(empty($smk)||$smk==''||$smk==NULL){
			$q = $this->db->query("select month(tanggal_transaksi) bulan, sum(besarnya) as total from trans_spp where month(tanggal_transaksi)='".$periode."' GROUP BY month(tanggal_transaksi)");
			//$return = $this->db->get()->row();
		}else{
			$q = $this->db->query("select month(a.tanggal_transaksi) bulan, sum(a.besarnya) as total from trans_spp a, ref_siswa b where a.id_siswa=b.id_siswa and b.id_smk='".$smk."' and month(a.tanggal_transaksi)='".$periode."' GROUP BY month(a.tanggal_transaksi) and a.sts_ppdb=0
			
			UNION
			
			select month(a.tanggal_transaksi) bulan, sum(a.besarnya) as total from trans_spp a, ppdb_siswa b where a.id_siswa=b.id_daftar and b.id_smk='".$smk."' and month(a.tanggal_transaksi)='".$periode."' GROUP BY month(a.tanggal_transaksi) and a.sts_ppdb=1");
			
		}
		return $q->row();
		
	}
	function get_numrow_spp($id_siswa, $periode) {
		$q = $this->db->query("select tanggal_transaksi, sum(besarnya) as total, no_faktur, bulan from trans_spp where id_siswa = ".$id_siswa." and tanggal_transaksi = '".$periode."' GROUP BY tanggal_transaksi, no_faktur");
		
$num_rows = $q->num_rows();


		return $num_rows; 
		
	}
	
	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;
	}
	
}