<?php 
class Trans_siswa_ppdb_model extends CI_Model {
	
	function getDiserahkan($faktur) {
		$q=$this->db->query("
							select nama 
							from ppdb_siswa 
							where id_daftar = (select distinct id_siswa from trans_siswa where no_faktur = '".$faktur."' and sts_ppdb=1) 
						");
		$nama = $q->row()->nama;
		return $nama;
	}
	
	function getAsalSekolah($faktur) {
		$q=$this->db->query("
							select asal_sekolah 
							from ppdb_siswa 
							where id_daftar = (select distinct id_siswa from trans_siswa where no_faktur = '".$faktur."' and sts_ppdb=1) 
						");
		$nama = $q->row()->asal_sekolah;
		return $nama;
	}
	function getDiterima($faktur) {
		$q = $this->db->query("
			select distinct diterima from trans_uang_masuk
			    where no='".$faktur."'
		");
		$diterima = $q->row()->diterima;
		return $diterima;
	}
	
	function getTotal($faktur) {
	
		$this->db->select("sum(besarnya) as total");
		$this->db->from("trans_siswa");
		$this->db->where("no_faktur",$faktur);
		$total = $this->db->get()->row()->total;
		return $total;
	}
	
	function getDetailFaktur($faktur) {
		
		$this->db->select("jenis_pembayaran,besarnya, id_jenis_bayar");
		$this->db->from("trans_siswa");
		$this->db->where("no_faktur",$faktur);
		$q = $this->db->get()->result_array();
		return $q;
	}
	
	function get_last_seq() {
		
		//compare date
		$now = date("Y-m-d");		
		$this->db->select('last_date');
		$this->db->from('ref_sequence_trans');
		$this->db->where('nama_transaksi','uang_masuk');
		$last_date = $this->db->get()->row()->last_date;
		if ($now != $last_date) {
			$update['last_seq'] = 0;
			$update['last_date'] = $now;
			
			$this->db->where('nama_transaksi','uang_masuk');
			$this->db->update('ref_sequence_trans',$update);
		}
		
		//get sequnce
		$this->db->select('last_seq');
		$this->db->from('ref_sequence_trans');
		$this->db->where('nama_transaksi','uang_masuk');
		$data = $this->db->get()->row()->last_seq;
		
		
		
		return $data;
	}
	
	function getDetail($idCoa) {
		
		$return = "";
		$total  = 0;
		$i=1;
		//$tahunAjaran = $this->get_tahun_ajaran();
		
		if ($idCoa == "") {			
			
			$return .='<table class="table table-bordered">
				<thead>
					<tr>
						<td>Detail List</td>
						<td>Keterangan</td>
						<td>Jenis Pembayaran</td>
						<td>Tahun Ajaran</td>
						<td>Besarnya</td>
					</tr>
				</thead>
				<tbody>';			 
			$return .='<tr><td colspan="4" align="right" >Total</td><td>Rp. '.$total.'<input id="totalBayar" type="hidden" name="totalBayar" value="'.$total.'" /></td></tr>';
			$return .='</tbody></table>';
		
		} else { 
			/*
			$q = $this->db->query("	
								select a.daftarlist,a.keterangan,b.jenispembayaran, a.kodedesk, a.kodesub, b.besarnya, b.tahun_ajaran 
								from ref_list_uang_masuk a,
									 ref_list_biaya b
								where a.Id = b.id_coa	
								and b.tahun_ajaran = '".$tahunAjaran."'
								and b.id in (".$idCoa.")
							");
		*/
		$q = $this->db->query("	
								select a.daftarlist,a.keterangan,b.jenis_pembayaran, a.kodedesk, a.kodesub, b.besarnya, b.tahun_ajaran, c.dicicil , b.dana_opsional, b.id_ppdb as id_siswa, b.id, b.sts_dsp, '0' as no_type
								from ref_list_uang_masuk a,
									 ref_mapping_biaya_siswa b,
									 ref_list_biaya c
								where c.id = b.id_coa and a.Id=c.id_coa
								and b.id in (".$idCoa.") and status=0 and b.sts_dsp=0
								
								UNION ALL
								
								select a.daftarlist,a.keterangan,b.jenis_pembayaran, a.kodedesk, a.kodesub, b.besarnya, b.tahun_ajaran, '2' as dicicil , b.dana_opsional, b.id_ppdb as id_siswa, b.id, b.sts_dsp, c.no_type
								from ref_list_uang_masuk a,
									 ref_mapping_biaya_siswa b,
									 ref_komponen_cicilan c
								where c.id = b.id_coa and a.Id=c.id_coa
								and b.id in (".$idCoa.") and status=0 and b.sts_dsp=1
							");
			$jml = $q->num_rows();
			$get = $q->result_array();
			$return .='<table class="table table-bordered">
				<thead>
					<tr>
						<td>Detail List</td>
						<td>Jenis Pembayaran</td>
						<td>Tahun Ajaran</td>
						<td>Besarnya</td>
						<td>Dibayarkan</td>
					</tr>
				</thead>
				<tbody>';
			$temp_1=0;
			foreach ($get as $val) {
				$temp_1=$temp_1+1;
				$return .='<tr>';
				$return	.='<td>'.$val['daftarlist'].'</td>';
				//$return	.='<td>'.$val['keterangan'].'</td>';
				$return	.='<td>'.$val['jenis_pembayaran'].'</td>';
				$return	.='<td>'.$val['tahun_ajaran'].'</td>';
				$return	.='<td>';
				$return .='<input id="kodedesk_'.$i.'" type="hidden" name="kodedesk[]" value="'.$val['kodedesk'].'" />';
				$return .='<input id="kodesub_'.$i.'" type="hidden" name="kodesub[]" value="'.$val['kodesub'].'" />';
				$return .='<input id="besarnya_'.$i.'" type="hidden" name="besarnya[]" value="'.$val['besarnya'].'" />';
				$return .='<input id="dicicil_'.$i.'" type="hidden" name="dicicil[]" value="'.$val['dicicil'].'" />';
				if($val['sts_dsp']==1)	{
				
						$total_cicil_pemb = $this->total_trans_siswa_cicil_pemb($val['id_siswa'], $val['id']);
						if(empty($total_cicil_pemb)||($total_cicil_pemb==NULL)||($total_cicil_pemb==0)){
							$total_cicil_pemb=0;
						}
						
						$tahunAjaran_siswa 		= $this->get_tahun_ajaran_siswa_pemb($val['id']);
						$tahunAjaran 			= $this->get_tahun_ajaran();
						if($tahunAjaran_siswa==$tahunAjaran){
							$id_smk = $this->getSMK($val['id_siswa']);
							if($val['no_type']==2){
								$this->db->select("besarnya, jenis_pembayaran, t_akhir, no_type");
								$this->db->from("ref_komponen_cicilan");
								$this->db->where("id_smk",$id_smk);
								$this->db->where("no_type",1);
								$gel1 = $this->db->get()->row();
								$t_a_gel1 = $gel1->t_akhir;
								$time_w = strtotime($t_a_gel1);
								$newakhir = date('Y-m-d',$time_w);
								$today = date("Y-m-d");
								$date = $newakhir;
								$cek=$this->compare_date($today, $date);
								//print_r($today);
								//print_r($date);die();
								if($cek){
									$this->db->select("besarnya, jenis_pembayaran, t_akhir");
									$this->db->from("ref_komponen_cicilan");
									$this->db->where("id_smk",$id_smk);
									$this->db->where("no_type",1);
									$gel1 = $this->db->get()->row();
									$besarnya_gel1 = $gel1->besarnya;
								}else{
									$this->db->select("besarnya, jenis_pembayaran, t_akhir");
									$this->db->from("ref_komponen_cicilan");
									$this->db->where("id_smk",$id_smk);
									$this->db->where("no_type",$val['no_type']);
									$gel1 = $this->db->get()->row();
									
									
									$this->db->select("besarnya");
									$this->db->from("ref_mapping_biaya_siswa");
									$this->db->where("id_ppdb",$val['id_siswa']);
									$this->db->where("sts_dsp",1);
									$gel_1 = $this->db->get()->row();
									$besarnya_gel1 = $gel_1->besarnya;
								}
							}else{
								$this->db->select("besarnya, jenis_pembayaran, t_akhir, no_type");
								$this->db->from("ref_komponen_cicilan");
								$this->db->where("id_smk",$id_smk);
								$this->db->where("no_type",$val['no_type']);
								$gel1 = $this->db->get()->row();
								
								$this->db->select("besarnya");
								$this->db->from("ref_mapping_biaya_siswa");
								$this->db->where("id_ppdb",$val['id_siswa']);
								$this->db->where("sts_dsp",1);
								$gel_1 = $this->db->get()->row();
								$besarnya_gel1 = $gel_1->besarnya;
							}
							//$total_sis_cicil_pemb = $besarnya_gel1;
							
							$total_sis_cicil_pemb=$besarnya_gel1-$total_cicil_pemb;
						}else{
							$total_sis_cicil_pemb=$val['besarnya']-$total_cicil_pemb;
						}
						
						
							$return .='Rp. '.number_format($total_sis_cicil_pemb,2,",",".").'
						<input id="total_sis_cicil_pemb_'.$i.'" type="hidden" name="total_sis_cicil_pemb[]" value="'.$total_sis_cicil_pemb.'" /></td>';
						
						$return	.='<td>Rp. <input id="total_bayar_'.$i.'" type="text" name="total_bayar[]" value="'.$total_sis_cicil_pemb.'" onchange="getTotalBiayaCicilan('.$jml.');" /></td></tr>';	
						$total = $total + $total_sis_cicil_pemb; 
						
					
				}else{
					if($val['dana_opsional']==1){
						$total_cicil = $this->total_trans_siswa_cicil($val['id_siswa'], $val['id']);
						if(empty($total_cicil)||($total_cicil==NULL)){
							$total_cicil=0;
						}
						$total_sis_cicil=$val['besarnya']-$total_cicil;
						$return .='<b>Telah Dibayar </b>Rp. '.number_format($total_cicil,2,",",".").'';	
						$return .='<input id="total_sis_cicil_pemb_'.$i.'" type="hidden" name="total_sis_cicil_pemb[]" value="0" /></td>';
						
						$return	.='<td>Rp. <input id="total_bayar_'.$i.'" type="text" name="total_bayar[]" value="0" onchange="getTotalBiayaCicilan('.$jml.');" /></td></tr>';	
						$total = $total; 
					}else{
						if($val['dicicil']==0){
						$return .='Rp. '.number_format($val['besarnya'],2,",",".").'';	
						$return .='<input id="total_sis_cicil_pemb_'.$i.'" type="hidden" name="total_sis_cicil_pemb[]" value="'.$val['besarnya'].'" /></td>';
						$return .='<td>
						<input id="total_bayar_'.$i.'" type="hidden" name="total_bayar[]" value="'.$val['besarnya'].'" onchange="getTotalBiayaCicilan(this.value);" />
						Rp. '.number_format($val['besarnya'],2,",",".").'</td></td></tr>';
						$total = $total + $val['besarnya'];  	
					}else if ($val['dicicil']==1){
						$total_cicil = $this->total_trans_siswa_cicil($val['id_siswa'], $val['id']);
						if(empty($total_cicil)||($total_cicil==NULL)){
							$total_cicil=0;
						}
						$total_sis_cicil=$val['besarnya']-$total_cicil;
						$return .='Rp. '.number_format($total_sis_cicil,2,",",".").'';	
						$return .='<input id="total_sis_cicil_pemb_'.$i.'" type="hidden" name="total_sis_cicil_pemb[]" value="'.$total_sis_cicil.'" /></td>';
						
						$return	.='<td>Rp. <input id="total_bayar_'.$i.'" type="text" name="total_bayar[]" value="'.$total_sis_cicil.'" onchange="getTotalBiayaCicilan('.$jml.');" /></td></tr>';	
						$total = $total + $total_sis_cicil; 
					}else if ($val['dicicil']==2){
						$total_cicil_pemb = $this->total_trans_siswa_cicil_pemb($val['id_siswa'], $val['id']);
						if(empty($total_cicil_pemb)||($total_cicil_pemb==NULL)||($total_cicil_pemb==0)){
							$total_cicil_pemb=0;
						}
						
						$tahunAjaran_siswa 		= $this->get_tahun_ajaran_siswa_pemb($val['id']);
						$tahunAjaran 			= $this->get_tahun_ajaran();
						if($tahunAjaran_siswa==$tahunAjaran){
							$id_smk = $this->getSMK($val['id_siswa']);
							if($val['no_type']==2){
								$this->db->select("besarnya, jenis_pembayaran, t_akhir, no_type");
								$this->db->from("ref_komponen_cicilan");
								$this->db->where("id_smk",$id_smk);
								$this->db->where("no_type",1);
								$gel1 = $this->db->get()->row();
								$t_a_gel1 = $gel1->t_akhir;
								$time_w = strtotime($t_a_gel1);
								$newakhir = date('Y-m-d',$time_w);
								$today = date("Y-m-d");
								$date = $newakhir;
								$cek=$this->compare_date($today, $date);
								//print_r($today);
								//print_r($date);die();
								if($cek){
									$this->db->select("besarnya, jenis_pembayaran, t_akhir");
									$this->db->from("ref_komponen_cicilan");
									$this->db->where("id_smk",$id_smk);
									$this->db->where("no_type",1);
									$gel1 = $this->db->get()->row();
									$besarnya_gel1 = $gel1->besarnya;
								}else{
									$this->db->select("besarnya, jenis_pembayaran, t_akhir");
									$this->db->from("ref_komponen_cicilan");
									$this->db->where("id_smk",$id_smk);
									$this->db->where("no_type",$val['no_type']);
									$gel1 = $this->db->get()->row();
									$besarnya_gel1 = $gel1->besarnya;
								}
							}else{
								$this->db->select("besarnya, jenis_pembayaran, t_akhir, no_type");
								$this->db->from("ref_komponen_cicilan");
								$this->db->where("id_smk",$id_smk);
								$this->db->where("no_type",$val['no_type']);
								$gel1 = $this->db->get()->row();
								$besarnya_gel1 = $gel1->besarnya;
							}
							//$total_sis_cicil_pemb = $besarnya_gel1;
							
							$total_sis_cicil_pemb=$besarnya_gel1-$total_cicil_pemb;
						}else{
							$total_sis_cicil_pemb=$val['besarnya']-$total_cicil_pemb;
						}
						
						
							$return .='Rp. '.number_format($total_sis_cicil_pemb,2,",",".").'
						<input id="total_sis_cicil_pemb_'.$i.'" type="hidden" name="total_sis_cicil_pemb[]" value="'.$total_sis_cicil_pemb.'" /></td>';
						
						$return	.='<td>Rp. <input id="total_bayar_'.$i.'" type="text" name="total_bayar[]" value="'.$total_sis_cicil_pemb.'" onchange="getTotalBiayaCicilan('.$jml.');" /></td></tr>';	
						$total = $total + $total_sis_cicil_pemb; 
					}
					}
					
				}
				$i++;			
			} 
			
			$return .='<tr><td colspan="4" align="right" >Total</td><td><div id="id-MappingTBC">Rp. '.number_format($total,2,",",".").'</div><input id="totalBayar" type="hidden" name="totalBayar" value="'.$total.'" /></td></tr>';
			/*
			$return .='<tr><td colspan="4" align="right" >Total</td><td>Rp. '.$total.'<input id="totalBayar" type="hidden" name="totalBayar" value="'.$total.'" /></td></tr>';*/
			$return .='</tbody></table>';
		
		}
		return $return;		
		
	}
	
	function getTrans($idSiswa) {
		$return = "";
		$tahunAjaran = $this->get_tahun_ajaran();
		
		$this->db->select("id_smk, kelas");
		$this->db->from("ppdb_siswa");
		$this->db->where("id_daftar",$idSiswa);
		$this->db->where_in("approval_status",array("0","1"));
		$rows = $this->db->get()->row();
		$id_smk = $rows->id_smk;
		$kelas = $rows->kelas;
		
		
		$this->db->select("name");
		$this->db->from("ref_smk");
		$this->db->where("id",$id_smk);
		$smk = $this->db->get()->row()->name;
		
		/*
		$q = $this->db->query("	select id,jenispembayaran 
								from ref_list_biaya	
								where id not in (
									select id_list_biaya from trans_siswa where id_siswa = ".$idSiswa."
									and tahun_ajaran = '".$tahunAjaran."'
								) and id_smk = '".$id_smk."' and kelas = '".$kelas."' and tahun_ajaran='".$tahunAjaran."'");
		*/
		/*
		$q = $this->db->query("	select id,jenispembayaran 
								from ref_list_biaya	
								where id not in (
									select id_list_biaya from trans_siswa where id_siswa = ".$idSiswa."
								) and id_smk = '".$id_smk."'");
		*/
		$q = $this->db->query("	select id, jenis_pembayaran , tahun_ajaran, dicicil, dana_opsional, besarnya, sts_dsp
								from ref_mapping_biaya_siswa	
								where id not in (
									select id_list_biaya from trans_siswa a, ref_mapping_biaya_siswa b where a.id_siswa = ".$idSiswa." and a.id_list_biaya=b.id and b.dicicil=0
								) and id_smk = '".$id_smk."' and id_ppdb = ".$idSiswa." and status=0 and sts_dsp=0
								
								UNION ALL
								
								select id, jenis_pembayaran , tahun_ajaran, '2' as dicicil, dana_opsional, besarnya, sts_dsp
								from ref_mapping_biaya_siswa where id_smk = '".$id_smk."' and id_ppdb = ".$idSiswa." and status=0 and sts_dsp=1");
												
		$get = $q->result_array();
		foreach($get as $val) {
			if($val['dana_opsional']==1){
				$return .="<option value=".$val['id'].">".$val['jenis_pembayaran']." (".$smk." - ".$val['tahun_ajaran'].")</option>";
			}else{
				if($val['sts_dsp']==1){
				$total_cicil = $this->total_trans_siswa_cicil_pemb($idSiswa, $val['id']);
				if($total_cicil<$val['besarnya']){
					$return .="<option value=".$val['id'].">".$val['jenis_pembayaran']." (".$smk." - ".$val['tahun_ajaran'].")</option>";
				}		
			}else{
				if($val['dicicil']==0){
					$return .="<option value=".$val['id'].">".$val['jenis_pembayaran']." (".$smk." - ".$val['tahun_ajaran'].")</option>";
				}else if($val['dicicil']==1){
					$total_cicil = $this->total_trans_siswa_cicil($idSiswa, $val['id']);
					if($total_cicil<$val['besarnya']){
						$return .="<option value=".$val['id'].">".$val['jenis_pembayaran']." (".$smk." - ".$val['tahun_ajaran'].")</option>";
					}
				}else if($val['dicicil']==2){
					$total_cicil = $this->total_trans_siswa_cicil_pemb($idSiswa, $val['id']);
					if($total_cicil<$val['besarnya']){
						$return .="<option value=".$val['id'].">".$val['jenis_pembayaran']." (".$smk." - ".$val['tahun_ajaran'].")</option>";
					}
				}
			}
			}
			
		}
		return $return;
		
	}
	
	function total_trans_siswa_cicil($id_siswa,$id_cicil){
		$this->db->select("sum(besarnya) as total");
		$this->db->from("trans_siswa");
		$this->db->where("id_siswa",$id_siswa);
		$this->db->where("id_list_biaya",$id_cicil);
		$this->db->where("cicilan",0);
		$this->db->where("sts_ppdb",1);
		$data = $this->db->get()->row();		
		return $data->total;
	}
	function total_trans_siswa_cicil_pemb($id_siswa,$id_cicil){
		
		$q = $this->db->query("select sum(besarnya) as total, b.id_smk, id_list_biaya from trans_siswa a, ppdb_siswa b where a.id_siswa=b.id_daftar and a.id_siswa = ".$id_siswa." and a.id_list_biaya = '".$id_cicil."' and cicilan=1 and sts_ppdb=1 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, ppdb_siswa b where a.id_siswa=b.id_daftar and a.id_siswa = ".$id_siswa." and a.kodesub = '1' and a.kodedesk = 'III' and cicilan=1 and sts_ppdb=1 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($temp2);die();
		return $temp1-$temp2;
		/*
		$this->db->select("sum(besarnya) as total");
		$this->db->from("trans_siswa");
		$this->db->where("id_siswa",$id_siswa);
		$this->db->where("id_list_biaya",$id_cicil);
		$this->db->where("cicilan",1);
		$this->db->where("sts_ppdb",0);
		$data = $this->db->get()->row();		
		return $data->total;
		*/
	}
	function getSiswa() {
		
		$tahunAjaran = $this->get_tahun_ajaran();
		
		$this->db->select("id_siswa,nama, nisn, nis");
		$this->db->from("ref_siswa");
		$this->db->where("tahun_ajaran",$tahunAjaran);
		$data = $this->db->get()->result_array();		
		return $data;
	}
	
	function getSMK($id_siswa) {
		$this->db->select("id_smk");
		$this->db->from("ppdb_siswa");
		$this->db->where("id_daftar",$id_siswa);
		$data = $this->db->get()->row()->id_smk;		
		return $data;
	}
	
	function get_list_data() {
	
		$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']=='kelas'){
					$field = 'a.kelas';
				}else if($rule['field']=='created_by'){
					$field = 'a.created_by';
				}else if($rule['field']=='jenis'){
					$field = 'e.jenis';
				}else{
					// declare variable
                $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 = 'trans_siswa'; 
		 $table2 = 'ref_jur'; 
		 $table3 = 'ref_smk'; 
		 
        if (empty($param['sort'])) {
            if (empty($cond)) {
                $sql = "
				select a.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                a.jenis_pembayaran, 
                a.tanggal_pembayaran, 
                a.besarnya, 
				a.kelas, 
                a.status, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date ,
				d.name,
                e.jenis
                from trans_siswa a, 
                ppdb_siswa b, ref_smk d, ref_jenis_bayar e
                where a.id_siswa = b.id_daftar and b.id_smk=d.id and a.sts_ppdb=1 and a.id_jenis_bayar = e.id_jenis_bayar
				order by id limit ?, ?";
                $sqlcount = "select no_faktur from $table a, ppdb_siswa b, $table3 d where a.id_siswa = b.id_daftar and b.id_smk=d.id and a.sts_ppdb=1";
            } else {
                $sql = "
				 select a.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                a.jenis_pembayaran, 
                a.tanggal_pembayaran, 
                a.besarnya, 
				a.kelas, 
                a.status, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date,
				d.name,
				e.jenis
                from trans_siswa a, 
                ppdb_siswa b, ref_smk d, ref_jenis_bayar e
                $cond and a.id_siswa = b.id_daftar and b.id_smk=d.id and a.sts_ppdb=1 and a.id_jenis_bayar = e.id_jenis_bayar
				 order by id limit ?, ?";
                $sqlcount = "select no_faktur from $table a, ppdb_siswa b, $table3 d, ref_jenis_bayar e $cond and a.id_siswa = b.id_daftar and b.id_smk=d.id and a.sts_ppdb=1 and a.id_jenis_bayar = e.id_jenis_bayar";
				
            }
            $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.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                a.jenis_pembayaran, 
                a.tanggal_pembayaran, 
                a.besarnya, 
				a.kelas, 
                a.status, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date ,
				d.name,
				e.jenis
                from trans_siswa a, 
                ppdb_siswa b, ref_smk d, ref_jenis_bayar e
                where a.id_siswa = b.id_daftar and b.id_smk=d.id and a.sts_ppdb=1 and a.id_jenis_bayar = e.id_jenis_bayar
                order by $sort $order limit ?, ?";
                $sqlcount = "select no_faktur from $table a, ppdb_siswa b, $table3 d, ref_jenis_bayar e where a.id_siswa = b.id_daftar and a.sts_ppdb=1 and b.id_smk=d.id and a.id_jenis_bayar = e.id_jenis_bayar";
            } else {
                $sql = "
				select a.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                a.jenis_pembayaran, 
                a.tanggal_pembayaran, 
                a.besarnya, 
				a.kelas, 
                a.status, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date ,
				d.name,
				e.jenis
                from trans_siswa a, 
                ppdb_siswa b, ref_smk d, ref_jenis_bayar e
                $cond and a.id_siswa = b.id_daftar and b.id_smk=d.id and a.sts_ppdb=1 and a.id_jenis_bayar = e.id_jenis_bayar
				 order by $sort $order limit ?, ?";
                $sqlcount = "select no_faktur from $table a, ppdb_siswa b, $table3 d, ref_jenis_bayar e $cond and a.id_siswa = b.id_daftar and a.sts_ppdb=1 and b.id_smk=d.id and a.id_jenis_bayar = e.id_jenis_bayar";
				
            }
            $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 updateStatusFaktur($faktur) {
		
		$data['status'] = 'CETAK';
		$this->db->where("no_faktur",$faktur);
		$q = $this->db->update("trans_siswa",$data);
		return $q;
		
	}
	
	function getKelas($id_siswa) {
	
		$this->db->select("kelas");
		$this->db->from("ppdb_siswa");
		$this->db->where("id_daftar",$id_siswa);
		$kelas = $this->db->get()->row()->kelas;
		return $kelas;
		
	}
	
	function getIdWaliKelas($id_siswa) {
		$this->db->select("id_wali_kelas");
		$this->db->from("ref_siswa");
		$this->db->where("id_siswa",$id_siswa);
		$idWali = $this->db->get()->row()->id_wali_kelas;
		return $idWali; 
	}
	
	function getIdRefListBiaya($id) {
		$this->db->select("id_coa");
		$this->db->from("ref_mapping_biaya_siswa");
		$this->db->where("id",$id);
		$id_coa = $this->db->get()->row()->id_coa;
		
		$this->db->select("id");
		$this->db->from("ref_list_biaya");
		$this->db->where("id",$id_coa);
		$res = $this->db->get()->row()->id;
		return $res; 
	}
	function getIdRefListBiayaPemb($id) {
		$this->db->select("id_coa");
		$this->db->from("ref_mapping_biaya_siswa");
		$this->db->where("id",$id);
		$id_coa = $this->db->get()->row()->id_coa;
		
		$this->db->select("id");
		$this->db->from("ref_komponen_cicilan");
		$this->db->where("id",$id_coa);
		$res = $this->db->get()->row()->id;
		return $res; 
	}
	function next_faktur(){
		$last_id = $this->get_last_seq();
					
		$last_id = $last_id + 1;
		$length = strlen($last_id);
					
		if ($length == 1) {
			$num = '0000';
		} else if ($length == 2){
			$num = '000';
		} else if ($length == 3) {
			$num = '00';
		}else if ($length == 4) {
			$num = '0';		
		} else {
			$num = '';
		}	
		//print_r(date('Ymd').$num.$last_id);die();
		return 'FM'.date('Ymd').$num.$last_id;
	}
	function insert_data($id_fak) {
		
		//print_r($_POST); die();
		/*
		$t_trans = $this->input->post('tanggal_transaksi');
		$t_trans = date_create_from_format('d-m-Y', $t_trans);		
		$tanggaltransaksi = date_format($t_trans, 'Y-m-d');
		*/
		
		$last_id = $this->get_last_seq();
		$last_id = $last_id + 1;
		$kodedesk = array();
		$kodesub = array();
		$besarnya = array();
		$idList = array();
		
		$total_sis_cicil_pemb = $this->input->post('total_sis_cicil_pemb');
		$dicicil = $this->input->post('dicicil');
		$kodedesk = $this->input->post('kodedesk');
		$kodesub = $this->input->post('kodesub');
		$besarnya = $this->input->post('besarnya');
		$total_bayar = $this->input->post('total_bayar');
		$idList = $this->input->post('id');
		$id_siswa = $this->input->post('id_siswa');
		$kelas = $this->getKelas($id_siswa);
		
		//$idWaliKelas = $this->getIdWaliKelas($id_siswa);
		$tahunAjaran = $this->get_tahun_ajaran();
		$metode_bayar = $this->input->post('metode_bayar');
		//$tahunAjaran_siswa = $this->get_tahun_ajaran();
		
		
		$count = count($kodedesk);
		
		$temp_cicil=1;
		
		for ($i=0; $i<$count; $i++) {
			$temp_id_map_c=0;
			if(($dicicil[$i]==0)||($dicicil[$i]==1)){
				$id_refList					= $this->getIdRefListBiaya($idList[$i]);
				$tahunAjaran_siswa 			= $this->get_tahun_ajaran_siswa($idList[$i]);
				$jPem 						= $this->getJepem($idList[$i]);	
				$data['cicilan'] 			= 0;
			}else{
				$id_refList					= $this->getIdRefListBiayaPemb($idList[$i]);
				$tahunAjaran_siswa 			= $this->get_tahun_ajaran_siswa_pemb($idList[$i]);
				$jPem 						= $this->getJepem_pemb($idList[$i]);	
				$data['cicilan'] 			= 1;
				
				$this->db->select("no_type");
				$this->db->from("ppdb_siswa");
				$this->db->where("id_daftar",$id_siswa);
				$no_type_cicil = $this->db->get()->row()->no_type;
				
				if($no_type_cicil==2){	
				//echo $total_bayar[$i]."-".$total_sis_cicil_pemb[$i];
					if($total_bayar[$i]>=$total_sis_cicil_pemb[$i]){
						$temp_cicil=2;
						if($tahunAjaran_siswa==$tahunAjaran){
							$id_smk = $this->getSMK($id_siswa);
							$this->db->select("t_akhir");
							$this->db->from("ref_komponen_cicilan");
							$this->db->where("id_smk",$id_smk);
							$this->db->where("no_type",1);
							$tgl_akh = $this->db->get()->row()->t_akhir;
							$time_w = strtotime($tgl_akh);
							$newakhir = date('Y-m-d',$time_w);;
					
							$today = date("Y-m-d");
							$date = $newakhir;
							$cek=$this->compare_date($today, $date);
							if($cek){
								$id_map_c = $this->update_map_cicilan($id_siswa);
								$temp_id_map_c=1;
							}
						}
					}
				}
			}
			$data['no_faktur']			= $id_fak;
			$data['kelas']				= $kelas;
			$data['id_siswa']			= $id_siswa;
			//$data['id_wali_kelas']		= $idWaliKelas;
			
			$data['tanggal_pembayaran']	= date("Y-m-d");
			$data['id_list_biaya']		= $idList[$i];
			if($temp_id_map_c==1){
				$data['id_ref_list_biaya']	= $id_map_c;
			}else{
				$data['id_ref_list_biaya']	= $id_refList;
			}
			
			$data['tahun_ajaran']		= $tahunAjaran_siswa;
			$data['created_date']		= date("Y-m-d H:i:s");
			$data['created_by']			= $this->session->userdata('username');		
			
			$data['kodedesk']			= $kodedesk[$i]; 
			$data['kodesub'] 			= $kodesub[$i]; 
			$data['besarnya'] 			= $total_bayar[$i];
			$data['sts_ppdb'] 			= 1;
			
			$data['jenis_pembayaran']	= $jPem;
			$data['id_jenis_bayar']		= $metode_bayar;
			$q = $this->db->insert("trans_siswa",$data);
			if ($q) {
				$diserahkan = $this->getDiserahkan($id_fak);
				//insert trans uang masuk
				$data_masuk['kodedesk'] 		= $kodedesk[$i];
				$data_masuk['kodesub'] 			= $kodesub[$i];
				$data_masuk['tanggaltransaksi'] = date("Y-m-d");
				
				$data_masuk['diserahkan'] 		= $diserahkan;
				$data_masuk['diterima'] 		= $this->session->userdata('username');
				
				
				$data_masuk['besarnya'] 		= $total_bayar[$i];
				$data_masuk['tanggalinput'] 	= date("Y-m-d");
				$data_masuk['tahunajaran'] 		= $tahunAjaran_siswa;
				$data_masuk['no'] 				= $id_fak;
				$data_masuk['created_date']		= date("Y-m-d H:i:s");
				$data_masuk['created_by']		= $this->session->userdata('username');		
				$this->db->insert("trans_uang_masuk",$data_masuk);
			}
		}
		
		//update last_sequence
		$update_sq['last_seq'] = $last_id;
		$this->db->where("nama_transaksi","uang_masuk");
		$this->db->update("ref_sequence_trans",$update_sq);	
		return true;	
		
	}
	
	function update_map_cicilan($id_siswa) {
			$no_type=1;
			
			$this->db->select("id_smk");
			$this->db->from("ppdb_siswa");
			$this->db->where("id_daftar",$id_siswa);
			$smk = $this->db->get()->row()->id_smk;
			
			$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()->row();
			$id_kom=$datas->id;
			$jenis_kom=$datas->jenis_pembayaran;
			$besar_kom=$datas->besarnya;
			
			$data_b['id_coa']		= $id_kom;
			$data_b['besarnya']	= $besar_kom;
			$data_b['jenis_pembayaran']	= $jenis_kom;
			$this->db->where("id_ppdb",$id_siswa);
			$this->db->where("sts_dsp",1);
			$q = $this->db->update("ref_mapping_biaya_siswa",$data_b);
			
			//$data_b['id_list_biaya']		= $temp_id;
			
			$data_b_ts['id_ref_list_biaya']	= $id_kom;
			$this->db->where("id_siswa",$id_siswa);
			$this->db->where("cicilan",1);
			$this->db->where("sts_ppdb",1);
			$q = $this->db->update("trans_siswa",$data_b_ts);
			
			$data_b_ppdb['no_type']	= 1;
			$this->db->where("id_daftar",$id_siswa);
			$q = $this->db->update("ppdb_siswa",$data_b_ppdb);
			
			$data_b_sis['no_type']	= 1;
			$this->db->where("id_ppdb",$id_siswa);
			$q = $this->db->update("ref_siswa",$data_b_sis);
			
			return $id_kom;
	}
	
	
	
	function compare_date($date1, $date2){
		if ($date1 <= $date2) {
			return true;
		}else{
			return false;
		}
	}
	
	function getJepem($id) {
		$this->db->select("jenis_pembayaran");
		$this->db->from("ref_mapping_biaya_siswa");
		$this->db->where("id",$id);
		$ret = $this->db->get()->row()->jenis_pembayaran;
		return $ret;
	}
	
	function getJepem_pemb($id) {
		/*
		$this->db->select("jenis_pembayaran");
		$this->db->from("ref_mapping_cicilan_siswa");
		$this->db->where("id",$id);
		$ret = $this->db->get()->row()->jenis_pembayaran;
		*/
		$q=$this->db->query("
							select c.daftarlist 
							from ref_mapping_biaya_siswa a, ref_komponen_cicilan b, ref_list_uang_masuk c
							where a.id = '".$id."' and a.id_coa=b.id and b.id_coa=c.Id");
		
		return $q->row()->daftarlist;
		
		//return $ret;
	}
	
	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",1);
		$return = $this->db->get()->row()->tahun_ajaran;
		
		return $return;
	}
	function get_tahun_ajaran_siswa($idList) {
	
		$q=$this->db->query("
							select tahun_ajaran 
							from ref_mapping_biaya_siswa 
							where id = '".$idList."' and status=0");
		
		return $q->row()->tahun_ajaran;
	}
	function get_tahun_ajaran_siswa_pemb($idList) {
	
		$q=$this->db->query("
							select tahun_ajaran 
							from ref_mapping_biaya_siswa 
							where id = '".$idList."' and status=0");
		
		return $q->row()->tahun_ajaran;
	}
	
	function getDataSMK(){
		$this->db->select('*');
		$this->db->from("ref_smk");
		$num = $this->db->get()->result_array();
		return $num;
	}
	function getNama($id_smk) {
		$tahunAjaran 			= $this->get_tahun_ajaran();
		$return = '';
		$q = $this->db->query("SELECT * FROM ppdb_siswa WHERE id_smk='".$id_smk."' and tahun_ajaran='".$tahunAjaran."' and (approval_status='0' or approval_status='1')");
		$data = $q->result_array();
		foreach ($data as $val) {
			$return .='<option value="'.$val['id_daftar'].'">'.$val['nama'].' ( '.$val['nisn'].' )'.'</option>';
		} 
		return $return;
	}
	
	
	//faktur
	
	function getSiswaFaktur($faktur){
		$this->db->select("*");
		$this->db->from("trans_siswa");
		$this->db->where("no_faktur",$faktur);
		$this->db->group_by("no_faktur");
		$q = $this->db->get()->row();
		return $q;
	}
	
	function getDetSiswaFaktur($id_siswa, $sts_ppdb){
		if($sts_ppdb==0){
			$this->db->select("nis as id, kelas,id_smk");
			$this->db->from("ref_siswa");
			$this->db->where("id_siswa",$id_siswa);
		}else{
			$this->db->select("nisn as id, kelas,id_smk");
			$this->db->from("ppdb_siswa");
			$this->db->where("id_daftar",$id_siswa);
		}
		$q = $this->db->get()->row();
		return $q;
	}
	function getSMKFaktur($id){
		$this->db->select("name");
		$this->db->from("ref_smk");
		$this->db->where("id",$id);
		$q = $this->db->get()->row()->name;
		return $q;
	}
	
	function delete_data() {
		
		$del = false;
		$faktur = $this->input->post('faktur');
		if (!empty($faktur)) {
			$this->db->where("no_faktur",$faktur);
			$this->db->delete("trans_siswa");
			$this->db->where("no",$faktur);
			$del = $this->db->delete("trans_uang_masuk");
		}
		
		return $del;
	}
	function getNamaMetodePembayaran($id){
		$this->db->select("jenis");
		$this->db->from("ref_jenis_bayar");
		$this->db->where("id_jenis_bayar",$id);
		$q = $this->db->get()->row()->jenis;
		return $q;
	}
	function getMetodePembayaran() {
		
		$tahunAjaran = $this->get_tahun_ajaran();
		
		$this->db->select("id_jenis_bayar,jenis");
		$this->db->from("ref_jenis_bayar");
		$data = $this->db->get()->result_array();		
		return $data;
	}
	//faktur
	
	
}