<?php 
class Ref_ppdb_to_sis_model extends CI_Model {
	
	function getWaliKelasEdit($id_smk, $id_jur,$kelas) {
		$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_jur."'
		");
		
		$data = $q->result_array();
		foreach ($data as $val) {
			//if ($idMappingClass == $val['id']) {
				//$return .='<option value="'.$val['id'].'" selected>'.$val['nama_karyawan'].' - '.$val['ruang_kelas'].'</option>';
			//} else {
				$return .='<option value="'.$val['id'].'">'.$val['nama_karyawan'].' - '.$val['ruang_kelas'].'</option>';
			//}
			
		} 
		return $return;
	}
	
	function cekKelas($id_smk){
		$return='';
		$this->db->select('*');
		$this->db->from("ref_kelas");
		$this->db->where('id_smk',$id_smk);
		$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 getKelas($id_smk){
		$this->db->select('*');
		$this->db->from("ref_kelas");
		$this->db->where('id_smk',$id_smk);
		$num = $this->db->get()->result_array();
		return $num;
	}
	function getDataSMK(){
		$this->db->select('*');
		$this->db->from("ref_smk");
		$num = $this->db->get()->result_array();
		return $num;
	}
	
	function getDataJurusan(){
		$this->db->select('*');
		$this->db->from("ref_jur");
		$num = $this->db->get()->result_array();
		return $num;
	}
	
	function getType($id_smk){
		$return='';
		//echo "SELECT DISTINCT(no_type)  FROM ref_komponen_cicilan WHERE id_smk='".$id_smk."'";die();
		$thn = $this->get_tahun_ajaran_ppdb();
		$q = $this->db->query("SELECT DISTINCT(no_type), jenis_pembayaran  FROM ref_komponen_cicilan WHERE id_smk='".$id_smk."' and tahun_ajaran='".$thn."' order by no_type asc");
		
		$num = $q->result_array();
		
			
		return $num;
	}
	function cekType($id_smk){
		$return='';
		//echo "SELECT DISTINCT(no_type)  FROM ref_komponen_cicilan WHERE id_smk='".$id_smk."'";die();
		$q = $this->db->query("SELECT DISTINCT(no_type), jenis_pembayaran  FROM ref_komponen_cicilan WHERE id_smk='".$id_smk."' order by no_type asc");
		
		$num = $q->result_array();
		
		
		
		foreach ($num as $v_kls) {
			if($v_kls['no_type']==2){
				$return.='<option value="'.$v_kls['no_type'].'" selected="selected">'.$v_kls['no_type'].' - '.$v_kls['jenis_pembayaran'].'</option>';
			}else{
				$return.='<option value="'.$v_kls['no_type'].'">'.$v_kls['no_type'].' - '.$v_kls['jenis_pembayaran'].'</option>';
			}
		}
								
		return $return;
	}
	function getTypeSPP($id_smk){
		$return = '';
		$q = $this->db->query("select * from ref_spp_type where id_smk='".$id_smk."'");
		
		$data = $q->result_array();
		foreach ($data as $val) {
			$return .='<option value="'.$val['id'].'">'.$val['ref_name'].'</option>';
		} 
		return $return;
	}
	function getTypeSPPEdit($id_smk){
		$this->db->select('*');
		$this->db->from('ref_spp_type');
		$this->db->where('id_smk',$id_smk);
		$data = $this->db->get()->result_array();
		return $data;
	}
	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 getJurEdit($id_smk){
		$this->db->select('*');
		$this->db->from('ref_jur');
		$this->db->where('id_smk',$id_smk);
		$data = $this->db->get()->result_array();
		return $data;
	}
	
	function getWaliKelas($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['id'].'">'.$val['nama_karyawan'].' - '.$val['ruang_kelas'].'</option>';
		} 
		return $return;
	}
	
	function cekNis($nis) {
		$this->db->select("nis");
		$this->db->from("ref_siswa");
		$this->db->where("nis",$nis);
		$num = $this->db->get()->num_rows();
		return $num;
	
	}
	
	function cekNisUpdate($nis,$id) {
		
		$this->db->select("nis");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		$oldName = $this->db->get()->row()->nis;
		if ($oldName == $nis) {
			$num = 0;
		} else {
			
			$this->db->select("nis");
			$this->db->from("ref_siswa");
			$this->db->where("nis",$nis);
			$num = $this->db->get()->num_rows();
			
		}		
		return $num;
		
	}
	
	function cekNisn($nisn) {
		$this->db->select("nisn");
		$this->db->from("ref_siswa");
		$this->db->where("nisn",$nisn);
		$num = $this->db->get()->num_rows();
		return $num;
	
	}
	
	function cekNisnUpdate($nisn,$id) {
		
		$this->db->select("nisn");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		$oldName = $this->db->get()->row()->nisn;
		if ($oldName == $nisn) {
			$num = 0;
		} else {
			
			$this->db->select("nisn");
			$this->db->from("ref_siswa");
			$this->db->where("nisn",$nisn);
			$num = $this->db->get()->num_rows();
			
		}		
		return $num;
		
	}
	
	function get_value_edit($id) {
		$this->db->select('*');
		$this->db->from('ref_siswa');
		$this->db->where('id_siswa',$id);
		$data = $this->db->get()->row();
		return $data;
	}
	
	function get_fancy() {
		
		$param = $this->input->post(array('id_siswa'));
        $sql = 'select seq_number, image_name, size from image_siswa where id_siswa =?';
        $result_array = $this->db->query($sql, array($param['id_siswa']));
        $response['images'] = $result_array->result();
        return $response;		
		
	}
	
	function _get_last($id_siswa) {
		$this->db->select_max('seq_number');
		$this->db->where('id_siswa', $id_siswa);
		$get = $this->db->get('image_siswa');
		
		if ($get->num_rows() > 0) {
			$last_id = $get->result_array();
			$return = $last_id[0]['seq_number'];
			
			return $return;
		} else {
			return 1;
		}
	}
	
	function upload_image($id_siswa) {
		
		if (isset($_FILES['myfile'])) {
			$this->load->library('uploaderfiler');
			$data = $this->uploaderfiler->upload($_FILES['myfile'], $this->uploaderfiler->getConfigAdmin(DIR_SISWA));
			// get success upload
			if ($data['isComplete']) {
				$files = $data['data'];
				// loop upload image data, temp image
				$imagesMeta = array();				
				$i = $this->_get_last($id_siswa);
				
				foreach ($files['metas'] as $key => $value) {
					//array_push($imagesMeta, array('id_siswa' => $id_daftar, 'seq_number' => $i, 'image_name' => $value['name'], 'size' => $value['size']));
					$insert_image = array (
						'id_siswa' => $id_siswa, 
						'seq_number' => $i, 
						'image_name' => $value['name'], 
						'size' => $value['size']
					);
						
					$this->db->insert('image_siswa',$insert_image);
					$i++;
				}
				
				//print_r($imagesMeta); die();
				//$this->db->insert_batch('image_siswa', $imagesMeta);
			}
			if ($data['hasErrors']) {
				$errors = $data['errors'];
				print_r($errors);
			}
		}
		
	}
	
	function get_list_data() {
		$thn_ppdb = $this->get_tahun_ajaran_ppdb();
		$param = $this->input->post(array('page', 'rows', 'sort', 'order', 'filterRules'));
        $offset = intval(($param['page'] - 1) * $param['rows']);
		
		// clause filter, array not null
        $cond = '';
        if (count($param['filterRules']) > 0) {
            $filter = json_decode($param['filterRules']);
            $loop = 0;
            foreach ($filter as $json) {
                // convert to array
                $rule = get_object_vars($json);
                // declare variable
				if($rule['field']=='nama_smk'){
					$field = 'c.name';
				}else if($rule['field']=='nama_jur'){
					$field = 'd.nama_jurusan';
				}else{
					$field = $rule['field'];
				}
                
                $opt = $rule['op'];
                $value = $rule['value'];
                if ($loop == 0) {
                    // user where
                    if (!empty($value)) {
                        if ($opt == 'contains') {
                            $cond .= "where ($field like '%$value%')";
                        } else if ($opt == 'greater') {
                            $cond .= "where $field > '$value'";
                        } else if ($opt == 'less') {
                            $cond .= "where $field < '$value'";
                        } else if ($opt == 'notequal') {
                            $cond .= "where $field != '$value'";
                        } else if ($opt == 'equal') {
                            $cond .= "where $field = '$value'";
                        }
                        $loop++; // flag where
                    }
                } else {
                    // user and
                    if (!empty($value)) {
                        if ($opt == 'contains') {
                            $cond .= " and ($field like '%$value%')";
                        } else if ($opt == 'greater') {
                            $cond .= " and $field > '$value'";
                        } else if ($opt == 'less') {
                            $cond .= " and $field < '$value'";
                        } else if ($opt == 'notequal') {
                            $cond .= " and $field != '$value'";
                        } else if ($opt == 'equal') {
                            $cond .= " and $field = '$value'";
                        }
                    }
                }
            }
        }
		$response = array();
		$table = 'ref_siswa'; 
		$table2 = 'ref_mapping_kelas';
		$table3 = 'ref_smk';
		$table4 = 'ref_jur';
		 
        if (empty($param['sort'])) {
            if (empty($cond)) {
                $sql = "select a.*, c.name nama_smk from $table a, $table3 c where a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."' order by c.name, id_siswa limit ?, ?";
                $sqlcount = "select a.*, c.name nama_smk from $table a, $table3 c where a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."'";
            } else {
                $sql = "select a.*, c.name nama_smk from $table a, $table3 c $cond and a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."' order by c.name, id_siswa limit ?, ?";
                $sqlcount = "select a.*, c.name nama_smk from $table a, $table3 c $cond and a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."' order by c.name, id_siswa";
            }
            $result_array = $this->db->query($sql, array($offset, intval($param['rows'])));
            $response['total'] = $this->db->query($sqlcount)->num_rows();
            $response['rows'] = $result_array->result();
        } else {
            $sort = $param['sort'];
            $order = $param['order'];
            if (empty($cond)) {
                $sql = "select a.*, c.name nama_smk from $table a, $table3 c where a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."' order by $sort $order limit ?, ?";
                $sqlcount = "select a.*, c.name nama_smk from $table a, $table3 c where a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."'";
            } else {
                $sql = "select a.*, c.name nama_smk from $table a, $table3 c $cond and a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."' order by c.name, id_siswa order by $sort $order limit ?, ?";
                $sqlcount = "select a.*, c.name nama_smk from $table a, $table3 c $cond and a.status=2 and a.id_smk=c.id and a.tahun_ajaran='".$thn_ppdb."' order by c.name, id_siswa";
            }
            $result_array = $this->db->query($sql, array($offset, intval($param['rows'])));
            $response['total'] = $this->db->query($sqlcount)->num_rows();
            $response['rows'] = $result_array->result();
        }
        return $response;
	}
	
	function update_data() {
		
		$id = $this->input->post('id_siswa');
		$thn_ppdb = $this->get_tahun_ajaran_ppdb();
		$thn = $this->get_tahun_ajaran();
		
		$this->db->select("kelas, no_type");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		$this->db->where("status",2);
		
		$row_bef = $this->db->get()->row();
		$kelas_bef = $row_bef->kelas;
		$no_type_bef = $row_bef->no_type;
		
		if($thn_ppdb==$thn){
			/*$t_lahir = $this->input->post('tanggal_lahir');
			$t_lahir = date_create_from_format('d-m-Y', $t_lahir);		
			$tanggal_lahir = date_format($t_lahir, 'Y-m-d');*/
			$data['no_type'] 		= $this->input->post('id_mapping_type');
			$data['id_spp_type'] 	= $this->input->post('id_type_spp');
			$data['status'] 		= $this->input->post('status');
			$tanggal_lahir 			= $this->input->post('tanggal_lahir');
			$data['id_smk'] 		= $this->input->post('smk');
			$data['id_jur'] 		= $this->input->post('id_mapping_jur');
			$data['kelas'] 			= $this->input->post('kelas');
			$data['nama'] 			= $this->input->post('nama');
			$data['nis'] 			= $this->input->post('nis');
			$data['tempat_lahir']	= $this->input->post('tempat_lahir');
			$data['nisn']			= $this->input->post('nisn');
			$data['nama_ayah']		= $this->input->post('nama_ayah');
			$data['nama_ibu']		= $this->input->post('nama_ibu');
			$data['telepon']		= $this->input->post('telepon');
			$data['tanggal_lahir']	= $tanggal_lahir;
			$data['alamat']			= $this->input->post('alamat');		
			$data['jenis_kelamin']	= $this->input->post('jenis_kelamin');
			$data['nama_wali']		= $this->input->post('nama_wali');
			$data['telepon_wali']	= $this->input->post('telepon_wali');
			$data['id_mapping_kelas']	= $this->input->post('id_mapping_kelas');
			$data['id_wali_kelas']		= $this->getidWaliKelas($data['id_mapping_kelas']);
			$data['alamat_wali']	= $this->input->post('alamat_wali');
			$data['syn_status']		= "0";
			$data['agama']			= $this->input->post('agama');
			$data['pekerjaan_ibu'] 			= $this->input->post('ker_ayah');
			$data['pekerjaan_ayah'] 			= $this->input->post('ker_ibu');
			$data['asal_sekolah'] 				= $this->input->post('asal');
			$data['modified_date']	= date("Y-m-d H:i:s");
			$data['modified_by']	= $this->session->userdata('username');
			
			$this->db->where("id_siswa",$id);
			$q = $this->db->update("ref_siswa",$data);
			
			if ($q) {
				//$this->upload_image($id);
				$this->insertRepSpp($id, $this->input->post('id_type_spp'));
				$this->insertRepTabsisPSB($id);
				$this->insert_map_biaya_siswa($id, $kelas_bef);
				$this->update_trans_spp($id, $this->input->post('nis'));
				$this->insert_map_cicilan($id, $no_type_bef);
				$this->approve_siswa($id);
			}
			return $q;	
		}else{
			return 0;	
		}
	}
	
	function approve_siswa($id) {
			
		$this->db->select("id_ppdb");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		
		$q = $this->db->get()->row();
		$id = $q->id_ppdb;
		
		$approve = false;
		if (!empty($id)) {
			
			$data['approved_date']			= date("Y-m-d H:i:s");
			$data['approved_by']			= $this->session->userdata('username');
			$data['approval_status']		= 2;
			
			$this->db->where("id_daftar",$id);
			$approve = $this->db->update("ppdb_siswa",$data);		
			
		}
		
		return $approve;
	}
	
	function insert_map_cicilan($id, $no_type_bef) {
		//get tahun ajaran
		$thn = $this->get_tahun_ajaran();
		//get data siswa
		$this->db->select("id_siswa, id_ppdb, no_type, id_smk, kelas");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		
		$q = $this->db->get()->row();
		//$nis = $q->nis;
		$id_siswa = $q->id_siswa;
		$id_ppdb = $q->id_ppdb;
		$kelas = $q->kelas;
		$smk = $q->id_smk;
		$no_type = $q->no_type;
		
		if($no_type_bef==0){
			$this->db->select("id, jenis_pembayaran, id_smk, besarnya");
			$this->db->from("ref_komponen_cicilan");
			$this->db->where("id_smk",$smk);
			$this->db->where("tahun_ajaran",$thn);
			$this->db->where("no_type",$no_type);
			$datas = $this->db->get()->result_array();
			
			foreach ($datas as $data) {
				$this->db->select("*");
				//$this->db->from("ref_mapping_cicilan_siswa");
				$this->db->from("ref_mapping_biaya_siswa");
				$this->db->where("tahun_ajaran",$thn);
				$this->db->where("status",0);
				$this->db->where("sts_dsp",1);
				$this->db->where("id_ppdb",$id_ppdb);
				$this->db->where("id_coa",$data['id']);
				$q = $this->db->get()->row();
				if(empty($q)){
					$data = array(
						"tahun_ajaran" => $thn,
						"id_siswa"  => $id_siswa,
						"jenis_pembayaran"  => $data['jenis_pembayaran'],
						"kelas"  => $kelas,
						"id_ppdb"  => $id_ppdb,
						"id_smk"  => $data['id_smk'],
						"id_coa"  => $data['id'],
						"besarnya"  => $data['besarnya'],
						"status"  => 1,
						"sts_dsp"  => 1
					);
					$q = $this->db->insert("ref_mapping_biaya_siswa",$data);
				}else{
					$data_b['id_siswa']		= $id_siswa;
					$data_b['status']		= 1;
					$data_b['jenis_pembayaran']		= $data['jenis_pembayaran'];
					$this->db->where("id_ppdb",$id_ppdb);
					$this->db->where("tahun_ajaran",$thn);
					$this->db->where("status",0);
					$this->db->where("sts_dsp",1);
					$this->db->where("id_coa",$data['id']);
					$q = $this->db->update("ref_mapping_biaya_siswa",$data_b);
				}
			}
		}else if($no_type_bef==$no_type){
			$this->db->select("id, jenis_pembayaran, id_smk, besarnya");
			$this->db->from("ref_komponen_cicilan");
			$this->db->where("id_smk",$smk);
			$this->db->where("tahun_ajaran",$thn);
			$this->db->where("no_type",$no_type);
			$datas = $this->db->get()->result_array();
			
			foreach ($datas as $data) {
				$this->db->select("*");
				$this->db->from("ref_mapping_biaya_siswa");
				$this->db->where("tahun_ajaran",$thn);
				$this->db->where("status",0);
				$this->db->where("sts_dsp",1);
				$this->db->where("id_ppdb",$id_ppdb);
				$this->db->where("id_coa",$data['id']);
				$q = $this->db->get()->row();
				if(empty($q)){
					$data = array(
						"tahun_ajaran" => $thn,
						"id_siswa"  => $id_siswa,
						"jenis_pembayaran"  => $data['jenis_pembayaran'],
						"kelas"  => $kelas,
						"id_ppdb"  => $id_ppdb,
						"id_smk"  => $data['id_smk'],
						"id_coa"  => $data['id'],
						"besarnya"  => $data['besarnya'],
						"status"  => 1,
						"sts_dsp"  => 1
					);
					$q = $this->db->insert("ref_mapping_biaya_siswa",$data);
				}else{
					$data_b['id_siswa']		= $id_siswa;
					$data_b['status']		= 1;
					$data_b['jenis_pembayaran']		= $data['jenis_pembayaran'];
					$this->db->where("id_ppdb",$id_ppdb);
					$this->db->where("tahun_ajaran",$thn);
					$this->db->where("status",0);
					$this->db->where("sts_dsp",1);
					$this->db->where("id_coa",$data['id']);
					$q = $this->db->update("ref_mapping_biaya_siswa",$data_b);
				}
			}
		}else if($no_type_bef!=$no_type){
			$this->db->where('id_ppdb', $id_ppdb);
			$this->db->where("sts_dsp",1);
			$this->db->delete('ref_mapping_biaya_siswa'); 
		
			
			$this->db->select("id, jenis_pembayaran, id_smk, besarnya");
			$this->db->from("ref_komponen_cicilan");
			$this->db->where("id_smk",$smk);
			$this->db->where("tahun_ajaran",$thn);
			$this->db->where("no_type",$no_type);
			$datas = $this->db->get()->result_array();
			
			foreach ($datas as $data) {
				$id_ref_list_biaya=$data['id']; 
				$this->db->select("*");
				$this->db->from("ref_mapping_biaya_siswa");
				$this->db->where("tahun_ajaran",$thn);
				$this->db->where("status",0);
				$this->db->where("sts_dsp",1);
				$this->db->where("id_ppdb",$id_ppdb);
				$this->db->where("id_coa",$id_ref_list_biaya);
				$q = $this->db->get()->row();
				if(empty($q)){
					$data = array(
						"tahun_ajaran" => $thn,
						"id_siswa"  => $id_siswa,
						"jenis_pembayaran"  => $data['jenis_pembayaran'],
						"kelas"  => $kelas,
						"id_ppdb"  => $id_ppdb,
						"id_smk"  => $data['id_smk'],
						"id_coa"  => $id_ref_list_biaya,
						"besarnya"  => $data['besarnya'],
						"status"  => 1,
						"sts_dsp"  => 1
					);
					$q = $this->db->insert("ref_mapping_biaya_siswa",$data);
					
					$temp_id = $this->db->insert_id();
					$data_b['id_list_biaya']		= $temp_id;
					$data_b['id_ref_list_biaya']	= $id_ref_list_biaya;
					$this->db->where("id_siswa",$id_siswa);
					$this->db->where("cicilan",1);
					$this->db->where("sts_ppdb",0);
					$q = $this->db->update("trans_siswa",$data_b);
					
				}else{
					$data_b['id_siswa']		= $id_siswa;
					$data_b['status']		= 1;
					$data_b['jenis_pembayaran']		= $data['jenis_pembayaran'];
					$this->db->where("id_ppdb",$id_ppdb);
					$this->db->where("tahun_ajaran",$thn);
					$this->db->where("status",0);
					$this->db->where("sts_dsp",1);
					$this->db->where("id_coa",$data['id']);
					$q = $this->db->update("ref_mapping_biaya_siswa",$data_b);
				}
			}
		}
		
	}
	
	function update_trans_spp($id, $nis){
		$this->db->select("id_ppdb");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		$id_ppdb = $this->db->get()->row()->id_ppdb;
		
		$data['sts_ppdb']	= 0;
		$data['id_siswa']	= $id;
		$data['nis']		= $nis;
		
		$this->db->where("id_siswa",$id_ppdb);
		$this->db->where("sts_ppdb",1);
		$q = $this->db->update("trans_spp",$data);
		
		$data2['sts_ppdb']	= 0;
		$data2['id_siswa']	= $id;
		
		$this->db->where("id_siswa",$id_ppdb);
		$this->db->where("sts_ppdb",1);
		$q2 = $this->db->update("trans_siswa",$data2);
		
		$data3['status']	= 1;
		$data3['id_siswa']	= $id;
		$data3['nis']		= $nis;
		
		$this->db->where("id_ppdb",$id_ppdb);
		$q3 = $this->db->update("rep_spp",$data3);
		
		$data4['sts_ppdb']	= 0;
		$data4['id_siswa']	= $id;
		$data4['nis']		= $nis;
		
		$this->db->where("id_siswa",$id_ppdb);
		$this->db->where("sts_ppdb",1);
		$q4 = $this->db->update("trans_tabsis",$data4);
			
			
		$data5['status']	= 1;
		$data5['id_siswa']	= $id;
		$data5['nis']		= $nis;
		
		$this->db->where("id_ppdb",$id_ppdb);
		$q5 = $this->db->update("rep_tabsis",$data5);
		
		return 0;
	}
	
	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_tahun_ajaran_ppdb() {
		
		$this->db->select("tahun_ajaran");
		$this->db->from("ref_tahun_ajaran");
		$this->db->where("status","U");
		$this->db->where("sts_ppdb",1);
		$data = $this->db->get()->row()->tahun_ajaran;
		return $data;
		
	}
	
	function insert_data() {
		
		/*$t_lahir = $this->input->post('tanggal_lahir');
		$t_lahir = date_create_from_format('d-m-Y', $t_lahir);		
		$tanggal_lahir = date_format($t_lahir, 'Y-m-d');*/
		$tanggal_lahir 			= $this->input->post('tanggal_lahir');
		$data['id_jur'] 		= $this->input->post('id_mapping_jur');
		$data['status'] 		= $this->input->post('status');
		$data['id_smk'] 		= $this->input->post('smk');
		$data['id_spp_type'] 	= $this->input->post('id_type_spp');
		$data['kelas'] 			= $this->input->post('kelas');
		$data['nama'] 			= $this->input->post('nama');
		$data['nis'] 			= $this->input->post('nis');
		$data['tempat_lahir']	= $this->input->post('tempat_lahir');
		$data['nisn']			= $this->input->post('nisn');
		$data['nama_ayah']		= $this->input->post('nama_ayah');
		$data['nama_ibu']		= $this->input->post('nama_ibu');
		$data['nama_wali']		= $this->input->post('nama_wali');
		$data['telepon_wali']	= $this->input->post('telepon_wali');
		$data['id_mapping_kelas']	= $this->input->post('id_mapping_kelas');
		$data['alamat_wali']	= $this->input->post('alamat_wali');
		$data['telepon']		= $this->input->post('telepon');
		$data['tanggal_lahir']	= $tanggal_lahir;
		$data['alamat']			= $this->input->post('alamat');
		$data['id_wali_kelas']	= $this->getidWaliKelas($data['id_mapping_kelas']);
		$data['tahun_ajaran']	= $this->get_tahun_ajaran();
		$data['jenis_kelamin']	= $this->input->post('jenis_kelamin');
		$data['created_date']	= date("Y-m-d H:i:s");
		$data['created_by']		= $this->session->userdata('username');
		$data['syn_status']		= "0";
		$data['agama']			= $this->input->post('agama');
		$data['pekerjaan_ibu'] 			= $this->input->post('ker_ayah');
		$data['pekerjaan_ayah'] 			= $this->input->post('ker_ibu');
		$data['asal_sekolah'] 				= $this->input->post('asal');
		
		$q = $this->db->insert("ref_siswa",$data);
				
		if ($q) {
			$id = $this->db->insert_id();
			//$this->upload_image($id);
			//insert ke rep_spp jika belum terdaftar	
					
			$this->insertRepSpp($id, $this->input->post('id_type_spp'));
			$this->insertRepTabsisPSB($id);
			$this->insert_map_biaya_siswa($id, 0);
			$this->insert_map_cicilan($id, 0);
		}
		
		return $q;	
		
	}
	
	function insertRepTabsisPSB($id) {
		
		//get tahun ajaran
		$thn = $this->get_tahun_ajaran();
		//get data siswa
		$this->db->select("id_siswa,id_smk,kelas, id_ppdb, nis");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		$q = $this->db->get()->row();
		
		//print_r($q); die();
		//$nis = $q->nis;
		$id_siswa = $q->id_siswa;
		$kelas = $q->kelas;
		$smk = $q->id_smk;
		$id_ppdb = $q->id_ppdb;
		$nis = $q->nis;
		/*
		if ($smk=='1') {
			$kdsub = 10;
		} else if ($smk=='2') { 
			$kdsub = 11;
		} else if ($smk=='3') { 
			$kdsub = 12;
		} else{
			$kdsub = 0;
		}
		*/
		
		
		if ($kelas == '10' && $smk=='1') {
			$kdsub = 10;
		} else if ($kelas == '11' && $smk=='1') { 
			$kdsub = 11;
		} else if ($kelas == '12' && $smk=='1') { 
			$kdsub = 12;
		} else if ($kelas == '10' && $smk=='2') { 
			$kdsub = 13;
		} else if ($kelas == '11' && $smk=='2') { 
			$kdsub = 14;
		} else if ($kelas == '12' && $smk=='2') { 
			$kdsub = 15;
		} else if ($kelas == '7' && $smk='3') { 
			$kdsub = 16;
		} else if ($kelas == '8' && $smk=='3') { 
			$kdsub = 17;
		} else if ($kelas == '9' && $smk=='3') { 
			$kdsub = 18;
		} else{
			$kdsub = 0;
		}
		
		//get besarnya 
		$this->db->select("besarnya");
		$this->db->from("ref_tabsis");
		$this->db->where("kodesub",$kdsub);
		$this->db->where("tahun_ajaran",$thn);
		$temp_bes = $this->db->get()->row();
		
		//$this->db->where("tahun_ajaran",$tahun);
		if($kdsub == 0){
			$besar = 0;
		}else{
			$besar = $temp_bes->besarnya;
		}
		
		//cek 
		$this->db->select("id");
		$this->db->from("rep_tabsis");
		$this->db->where(
			array(
				"tahun_ajaran" => $thn,
				//"nis"		   => $nis,
				"id_ppdb"	   => $id_ppdb,
				"status"	   => 0
			)
		);
		$num = $this->db->get()->num_rows();
		
		if ($num == 0) {
			$data = array(
				"tahun_ajaran" => $thn,
				"id_ppdb"  => $id_ppdb,
				"id_siswa"  => $id_siswa,
				"biaya"  	=> $besar,
				"nis"  	=> $nis,
				"status"  	=> 1
			);
			$q = $this->db->insert("rep_tabsis",$data);
		}else{
			$data_b['biaya']	= $besar;
			$data_b['status']	= 1;
			$data_b['id_siswa']	= $id_siswa;
			$data_b['nis']		= $nis;
			$this->db->where("id_ppdb",$id_ppdb);
			$this->db->where("tahun_ajaran",$thn);
			$this->db->where("status",0);
			$q = $this->db->update("rep_tabsis",$data_b);
		}
		 
		//return true;
		
	}
	
	function insert_map_biaya_siswa($id, $kelas_bef) {
		//get tahun ajaran
		$thn = $this->get_tahun_ajaran();
		//get data siswa
		$this->db->select("nis, id_siswa, id_ppdb, id_smk, kelas");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		$q = $this->db->get()->row();
		$nis = $q->nis;
		$id_siswa = $q->id_siswa;
		$id_ppdb = $q->id_ppdb;
		$kelas = $q->kelas;
		$smk = $q->id_smk;
		
				
		
		//cek 
		$this->db->select("id");
		$this->db->from("ref_mapping_biaya_siswa");
		$this->db->where(
			array(
				"tahun_ajaran" => $thn,
				"id_ppdb"	   => $id_ppdb,
				"status"	   => 0,
				"sts_dsp"	   => 0
			)
		);
		$num = $this->db->get()->num_rows();
		
		
		
		if($num<=0){
			if(($kelas>10&&$smk==1)||($kelas>10&&$smk==2)||($kelas>7&&$smk==3)){
				$this->db->select("id");
				$this->db->from("ref_list_biaya");
				$this->db->where(
					array(
						//45 id pendaftran
						"id_coa"		=> 45,
						"id_smk"	   => $smk,
						"tahun_ajaran"	   => $thn
					)
				);
				$id_rlb = $this->db->get()->row()->id;
				
				$this->db->select("id");
				$this->db->from("ref_mapping_biaya_siswa");
				$this->db->where(
					array(
						"id_coa"		=> $id_rlb,
						"id_ppdb"	   	=> $id_ppdb,
						"sts_dsp"	   	=> 0	
					)
				);
				$num_rmbs = $this->db->get()->num_rows();
				if($num_rmbs<=0){
					$this->db->select("id, jenispembayaran, id_smk, besarnya, periode, id_coa, dicicil");
					$this->db->from("ref_list_biaya");
					$this->db->where("id_smk",$smk);
					$this->db->where("tahun_ajaran",$thn);
					$this->db->where("id_coa",45);
					$datas = $this->db->get()->result_array();
					foreach ($datas as $data) {
						$this->db->select("*");
						$this->db->from("ref_mapping_biaya_siswa");
						$this->db->where("tahun_ajaran",$thn);
						$this->db->where("status",0);
						$this->db->where("sts_dsp",0);
						$this->db->where("id_ppdb",$id_ppdb);
						$this->db->where("id_coa",$data['id']);
						$q = $this->db->get()->row();
						if(empty($q)){
							$data = array(
								"tahun_ajaran" => $thn,
								"id_siswa"  => $id_siswa,
								"id_ppdb"  => $id_ppdb,
								"dicicil"  => $data['dicicil'],
								"jenis_pembayaran"  => $data['jenispembayaran'],
								"kelas"  => $kelas,
								"id_smk"  => $data['id_smk'],
								"id_coa"  => $data['id'],
								"periode"  => $data['periode'],
								"besarnya"  => $data['besarnya'],
								"status"  => 1,
								"sts_dsp" => 0
							);
							$q = $this->db->insert("ref_mapping_biaya_siswa",$data);
						}
					}
				}
			}
			
			$this->db->select("id, jenispembayaran, id_smk, besarnya, periode, id_coa, dicicil");
			$this->db->from("ref_list_biaya");
			$this->db->where("id_smk",$smk);
			$this->db->where("kelas",$kelas);
			$this->db->where("tahun_ajaran",$thn);
			$datas = $this->db->get()->result_array();
			
			foreach ($datas as $data) {
				
				$this->db->select("*");
				$this->db->from("ref_mapping_biaya_siswa");
				$this->db->where("tahun_ajaran",$thn);
				$this->db->where("status",0);
				$this->db->where("sts_dsp",0);
				$this->db->where("id_ppdb",$id_ppdb);
				$this->db->where("id_coa",$data['id']);
				$q = $this->db->get()->row();
				if(empty($q)){
					$data = array(
						"tahun_ajaran" => $thn,
						"id_ppdb"  => $id_ppdb,
						"id_siswa"  => $id_siswa,
						"jenis_pembayaran"  => $data['jenispembayaran'],
						"dicicil"  => $data['dicicil'],
						"kelas"  => $kelas,
						"id_smk"  => $data['id_smk'],
						"id_coa"  => $data['id'],
						"periode"  => $data['periode'],
						"besarnya"  => $data['besarnya'],
						"status"  => 1,
						"sts_dsp"  => 0
					);
					$q = $this->db->insert("ref_mapping_biaya_siswa",$data);
				}
			}
		}else{
			
			if($kelas!=$kelas_bef){
				$this->db->select("id");
				$this->db->from("ref_list_biaya");
				$this->db->where(
					array(
						//45 id pendaftran
						"id_coa"		=> 45,
						"id_smk"	   => $smk,
						"tahun_ajaran"	   => $thn
					)
				);
				$id_rlb2 = $this->db->get()->row()->id;
				
				$data_bb['kelas']	= $kelas;
				$data_bb['id_siswa']	= $id_siswa;
				$this->db->where("id_ppdb",$id_ppdb);
				$this->db->where("id_coa",$id_rlb2);
				$this->db->where("sts_dsp",0);
				$q = $this->db->update("ref_mapping_biaya_siswa",$data_bb);
				
				$this->db->where('tahun_ajaran', $thn);
				$this->db->where('id_ppdb', $id_ppdb);
				$this->db->where('id_coa !=', $id_rlb2);
				$this->db->where('status', 0);
				$this->db->where('sts_dsp', 0);
				$this->db->delete('ref_mapping_biaya_siswa'); 
				
				
				
				
				$this->db->select("id, jenispembayaran, id_smk, besarnya, periode, id_coa, dicicil");
				$this->db->from("ref_list_biaya");
				$this->db->where("id_smk",$smk);
				$this->db->where("kelas",$kelas);
				$this->db->where("tahun_ajaran",$thn);
				$datas = $this->db->get()->result_array();
				foreach ($datas as $data) {
					$this->db->select("*");
					$this->db->from("ref_mapping_biaya_siswa");
					$this->db->where("tahun_ajaran",$thn);
					$this->db->where("status",0);
					$this->db->where("sts_dsp",0);
					$this->db->where("id_ppdb",$id_ppdb);
					$this->db->where("id_coa",$data['id']);
					$q = $this->db->get()->row();
					if(empty($q)){
						$data = array(
							"tahun_ajaran" => $thn,
							"id_ppdb"  => $id_ppdb,
							"id_siswa"  => $id_siswa,
							"jenis_pembayaran"  => $data['jenispembayaran'],
							"dicicil"  => $data['dicicil'],
							"kelas"  => $kelas,
							"id_smk"  => $data['id_smk'],
							"id_coa"  => $data['id'],
							"periode"  => $data['periode'],
							"besarnya"  => $data['besarnya'],
							"status"  => 1,
							"sts_dsp"  => 0
						);
						$q = $this->db->insert("ref_mapping_biaya_siswa",$data);
					}
				}
				$data_bbbb['status']		= 1;
				$this->db->where("id_ppdb",$id_ppdb);
				$this->db->where("id_coa",$id_rlb2);
				$this->db->where("status",0);
				$this->db->where("sts_dsp",0);
				$q = $this->db->update("ref_mapping_biaya_siswa",$data_bbbb);
			}else if($kelas==$kelas_bef){
				$data_b['id_siswa']		= $id_siswa;
				$data_b['status']		= 1;
				$this->db->where("id_ppdb",$id_ppdb);
				$this->db->where("tahun_ajaran",$thn);
				$this->db->where("status",0);
				$this->db->where("sts_dsp",0);
				$q = $this->db->update("ref_mapping_biaya_siswa",$data_b);
			}
		
			
		}
	}
	
	function getidWaliKelas($idMapping) {
		
		$this->db->select("id_karyawan");
		$this->db->from("ref_mapping_kelas");
		$this->db->where("id",$idMapping);
		$idKar = $this->db->get()->row()->id_karyawan;
		return $idKar;
	}
	
	function insertRepSpp($id, $type_spp) {
		
		//get tahun ajaran
		$thn = $this->get_tahun_ajaran();
		//get data siswa
		$this->db->select("nis, id_siswa, id_smk, kelas, id_ppdb");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id);
		$q = $this->db->get()->row();
		
		//print_r($q); die();
		$nis = $q->nis;
		$id_siswa = $q->id_siswa;
		$kelas = $q->kelas;
		$smk = $q->id_smk;
		$id_ppdb = $q->id_ppdb;
		
		if ($kelas == '10' && $smk=='1') {
			$kdsub = 1;
		} else if ($kelas == '11' && $smk=='1') { 
			$kdsub = 2;
		} else if ($kelas == '12' && $smk=='1') { 
			$kdsub = 3;
		} else if ($kelas == '10' && $smk=='2') { 
			$kdsub = 4;
		} else if ($kelas == '11' && $smk=='2') { 
			$kdsub = 5;
		} else if ($kelas == '12' && $smk=='2') { 
			$kdsub = 6;
		} else if ($kelas == '7' && $smk=='3') { 
			$kdsub = 7;
		} else if ($kelas == '8' && $smk=='3') { 
			$kdsub = 8;
		} else if ($kelas == '9' && $smk=='3') { 
			$kdsub = 9;
		} else{
			$kdsub = 0;
		}
		
		$this->db->select("type_dis, besarnya");
		$this->db->from("ref_spp_type");
		$this->db->where("id", $type_spp);
		$temp_dis = $this->db->get()->row();
		$type_dis = $temp_dis->type_dis;
		$besar_dis = $temp_dis->besarnya;
		
		//get besarnya 
		$this->db->select("besarnya");
		$this->db->from("ref_spp");
		$this->db->where("kodesub", $kdsub);
		$this->db->where("tahun_ajaran",$thn);
		$temp_bes = $this->db->get()->row();
		
		//$this->db->where("tahun_ajaran",$tahun);
			
		if($type_dis == 1){
			if($kdsub == 0){
				$besar = 0;
			}else{
				$besar = $temp_bes->besarnya;
			}
		}else if($type_dis == 2){
			$besar = $besar_dis;
		}else if($type_dis == 3){
			$besar = 0;
		}else{
			if($kdsub == 0){
				$besar = 0;
			}else{
				$besar = $temp_bes->besarnya;
			}
		}
			
		//cek 
		$this->db->select("id");
		$this->db->from("rep_spp");
		$this->db->where(
			array(
				"tahun_ajaran" 	=> $thn,
				"status" 		=> 0,
				"id_ppdb"	   	=> $id_ppdb	
			)
		);
		$num = $this->db->get()->num_rows();
		
		if ($num == 0) {
			$data = array(
				"tahun_ajaran" 	=> $thn,
				"nis" 			=> $nis,
				"status" 		=> 1,
				"id_siswa"  	=> $id_siswa,
				"biaya"  		=> $besar
			);
			$q = $this->db->insert("rep_spp",$data);
		}else{
			$data_b['biaya']	= $besar;
			$data_b['status']	= 1;
			$data_b['nis']		= $nis;
			$data_b['id_siswa']	= $id_siswa;
			$this->db->where("id_ppdb",$id_ppdb);
			$this->db->where("tahun_ajaran",$thn);
			$this->db->where("status",0);
			$q = $this->db->update("rep_spp",$data_b);
		}
		 
		//return true;
		
	}
	
}