<?php 
class Cicilan_keuangan_model_ppdb extends CI_Model {
	
	function getDiterima($faktur) {
		$q = $this->db->query("
			select distinct diserahkan from trans_uang_keluar
			    where no='".$faktur."'
		");
		$diterima = $q->row()->diserahkan;
		return $diterima;
	}
	function get_last_seq() {
		
		//compare date
		$now = date("Y-m-d");		
		$this->db->select('*');
		$this->db->from('ref_sequence_trans');
		$this->db->where('nama_transaksi','uang_keluar');
		$row = $this->db->get()->row();
		$last_date = $row->last_date;
		$last_seq = $row->last_seq;
		if ($now != $last_date) {
			$update['last_seq'] = 0;
			$update['last_date'] = $now;
			
			$this->db->where('nama_transaksi','uang_keluar');
			$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_keluar');
		$data = $this->db->get()->row()->last_seq;
		
		
		
		return $data;
	}
	
	function get_last_seq_insert() {
		
		//compare date
		$now = date("Y-m-d");		
		$this->db->select('*');
		$this->db->from('ref_sequence_trans');
		$this->db->where('nama_transaksi','uang_masuk');
		$row = $this->db->get()->row();
		$last_date = $row->last_date;
		$last_seq = $row->last_seq;
		$last_seq += 1;
		$update['last_seq'] = $last_seq;
		$update['last_date'] = $now;
		$this->db->where('nama_transaksi','uang_masuk');
		$this->db->update('ref_sequence_trans',$update);
		
		
		return $last_seq;
	}
	
	function get_siswa() {
		/*
		$q = $this->db->query("
							select a.id_daftar as id_siswa, 
							a.nama from 
							(
							select id_daftar, nama from ppdb_siswa where approval_status in ('0','1')
								union all
							select id_siswa, nama from ref_siswa) a order by a.nama
						");
						
						
		
		$data = $q->result_array();*/
		$tahunAjaran 			= $this->get_tahun_ajaran();				
		$this->db->select("id_daftar as id_siswa, nama, nisn");
		$this->db->from("ppdb_siswa");
		$this->db->where_in("approval_status",array("0","1"));
		$this->db->where("tahun_ajaran", $tahunAjaran);
		$data = $this->db->get()->result_array();
		return $data;
	}
	/*function get_kd_detail() {
		$this->db->select("kodesub,daftarlist");
		$this->db->from("ref_list_uang_masuk");
		$data = $this->db->get()->result_array();
		return $data;
	}*/
	
	function get_value_edit($no_trans) {
		$this->db->distinct();
		$this->db->select('no,tanggaltransaksi,diserahkan,diterima');
		$this->db->from('trans_uang_masuk');
		$this->db->where('no',$no_trans);
		$data = $this->db->get()->row();
		//echo $this->db->last_query(); die();
		return $data;
	}
	
	function get_trans_detail($no_trans) {
		$this->db->select('kodedesk,kodesub,besarnya');
		$this->db->from('trans_uang_masuk');
		$this->db->where('no',$no_trans);
		$data = $this->db->get()->result_array();
		//echo $this->db->last_query(); die();
		return $data;
	}
	
	function get_kodedesk() {
		$this->db->select("kodedesk,deskripsi");
		$this->db->from("ref_uang_masuk");
		$data = $this->db->get()->result_array();
		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{
					//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_tabsis'; 
		 
        if (empty($param['sort'])) {
            if (empty($cond)) {
                $sql = "
				select distinct a.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                'PENARIKAN TABSIS' as jenis_pembayaran, 
                a.tanggal_transaksi, 
                a.besarnya, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date ,
				c.name
                from trans_tabsis a, 
                ppdb_siswa b, ref_smk c
                where a.id_siswa = b.id_daftar  and b.id_smk=c.id
                and b.approval_status in ('0','1') and a.sts_ppdb='1' and SUBSTRING(a.no_faktur, 1, 2)='FK'
				order by id limit ?, ?";
				
                $sqlcount = "select distinct no_faktur from $table  a, 
                ppdb_siswa b, ref_smk c
                where a.id_siswa = b.id_daftar  and b.id_smk=c.id and SUBSTRING(a.no_faktur, 1, 2)='FK'
                and b.approval_status in ('0','1') and a.sts_ppdb='1'";
            } else {
                $sql = "
				 select distinct a.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                'PENARIKAN TABSIS' as jenis_pembayaran, 
                a.tanggal_transaksi, 
                a.besarnya, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date,
				c.name
                from trans_tabsis a, 
                ppdb_siswa b, ref_smk c
                $cond and a.id_siswa = b.id_daftar and b.id_smk=c.id
                and b.approval_status in ('0','1') and a.sts_ppdb='1' and SUBSTRING(a.no_faktur, 1, 2)='FK'
				 order by id limit ?, ?";
                $sqlcount = "select distinct no_faktur from $table  a, 
                ppdb_siswa b, ref_smk c
                 $cond and a.id_siswa = b.id_daftar  and b.id_smk=c.id and SUBSTRING(a.no_faktur, 1, 2)='FK'
                and b.approval_status in ('0','1') and a.sts_ppdb='1'";
            }
            $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 distinct a.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                'PENARIKAN TABSIS' as jenis_pembayaran, 
                a.tanggal_transaksi, 
                a.besarnya, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date ,
				c.name
                from trans_tabsis a, 
                ppdb_siswa b, ref_smk c
                where a.id_siswa = b.id_daftar  and b.id_smk=c.id and SUBSTRING(a.no_faktur, 1, 2)='FK'
                and b.approval_status in ('0','1') and a.sts_ppdb='1'	
				order by $sort $order limit ?, ?";
				
                $sqlcount = "select distinct no_faktur from $table a, 
                ppdb_siswa b, ref_smk c
                where a.id_siswa = b.id_daftar  and b.id_smk=c.id and SUBSTRING(a.no_faktur, 1, 2)='FK'
                and b.approval_status in ('0','1') and a.sts_ppdb='1'";
            } else {
                $sql = "
				select distinct a.id,
				b.nama,
				a.no_faktur, 
                a.tahun_ajaran, 
                'PENARIKAN TABSIS' as jenis_pembayaran, 
                a.tanggal_transaksi, 
                a.besarnya, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date,
				c.name
                from trans_tabsis a, 
                ppdb_siswa b, ref_smk c
                $cond and a.id_siswa = b.id_daftar  and b.id_smk=c.id
                and b.approval_status in ('0','1') and a.sts_ppdb='1' and SUBSTRING(a.no_faktur, 1, 2)='FK'		
				 order by $sort $order limit ?, ?";
                $sqlcount = "select distinct no_faktur from $table  a, 
                ppdb_siswa b, ref_smk c
                $cond and a.id_siswa = b.id_daftar  and b.id_smk=c.id and SUBSTRING(a.no_faktur, 1, 2)='FK'
                and b.approval_status in ('0','1') and a.sts_ppdb='1'";
            }
            $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() {
		
		$trans_id = $this->input->post('no');	
		$this->db->where('no',$trans_id);
		$del = $this->db->delete('trans_uang_masuk');
		
		if ($del) {
			$t_trans = $this->input->post('tanggaltransaksi');
			$t_trans = date_create_from_format('d-m-Y', $t_trans);		
			$tanggaltransaksi = date_format($t_trans, 'Y-m-d');
			
			
			$last_id = $this->input->post('last_id');
			
			$kodedesk = array();
			$kodesub = array();
			$besarnya = array();
			
			
			$kodedesk = $this->input->post('kodedesk');
			$kodesub = $this->input->post('kodesub');
			$besarnya = $this->input->post('besarnya');
			$count = count($kodedesk);
			
			
			for ($i=0; $i<$count; $i++) {
				
				$data['no']		= $this->input->post('no');
				$data['tanggaltransaksi']	= $tanggaltransaksi;
				$data['tanggalinput']	= date('Y-m-d');
				$data['diserahkan']	= $this->input->post('diserahkan');
				$data['diterima']		= $this->input->post('diterima');
				$data['tahunajaran']	= $this->get_tahun_ajaran();
				$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'] = $besarnya[$i];
				
				$q = $this->db->insert("trans_uang_masuk",$data);
			}
		}
		
		return true;	
		
	}*/
	
	function get_kelas($id) {
		
			
		$q = $this->db->query("
							select a.kelas, a.nama from 
							(select kelas, nama from ppdb_siswa where id_daftar = ".$id." and status=1 ) a order by a.nama
						");
		
		
		$kelas = $q->row()->kelas;
		return $kelas;	
	}
	
	function get_nis($id) {
		
			
		$q = $this->db->query("select nis from ref_siswa where id_siswa = ".$id." and status=1");
		
		
		$kelas = $q->row()->nis;
		return $kelas;	
	}
	
	function get_kode($id) {
		$this->db->select("id_coa");
		$this->db->from("ref_komponen_cicilan");
		$this->db->where("id",$id);
		$data = $this->db->get()->row();
		
		$this->db->select("kodedesk,kodesub,id");
		$this->db->from("ref_list_uang_masuk");
		$this->db->where("id",$data->id_coa);
		$data = $this->db->get()->row();
		return $data;
	}
	
	function get_jenis_pembayaran($id) {
		$this->db->select("jenis_pembayaran,  id_coa");
		$this->db->from("ref_mapping_cicilan_siswa");
		$this->db->where("id",$id);
		$data = $this->db->get()->row();
		return $data;
	}
	
	function getIdRefListBiaya($id) {
		$this->db->select("id_coa");
		$this->db->from("ref_mapping_cicilan_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 'FK'.date('Ymd').$num.$last_id;
	}
	function insert_data($id_fak) {
		
		//$data = array();
		$last_id = $this->get_last_seq();
		$last_id = $last_id + 1;
		//$t_trans = $this->input->post('tanggal_pembayaran');
		//$t_trans = date_create_from_format('d-m-Y', $t_trans);		
		//$tanggal_transaksi = date_format($t_trans, 'Y-m-d');
		
		$id_siswa = $this->input->post('id_siswa');
		//$sisa = $this->input->post('sisa');
		$besarnya = $this->input->post('besarnya');
		//$get_kelas = $this->get_kelas($id_siswa);
		//$get_nis = $this->get_nis($id_siswa);
		
		//$id_refList					= $this->getIdRefListBiaya($this->input->post('id_mapping_cicilan'));
		//$jen_pem = $this->get_jenis_pembayaran($this->input->post('id_mapping_cicilan'));
		//$id_coa = $jen_pem->id_coa;
		//$get_kode = $this->get_kode($id_coa);
		//$data['nis'] = $get_nis;
		$data['tahun_ajaran']	= $this->get_tahun_ajaran();
		$data['tanggal_transaksi']	= date('Y-m-d');
		$data['besarnya']	= $this->input->post('besarnya');
		$data['tanggal_input']	= date('Y-m-d');
		$data['no_faktur']	= $id_fak;
		$data['kodesub'] = "1";
		$data['kodedesk'] = "IV";	
		$data['id_siswa']	= $id_siswa;
		//$data['id_ref_list_biaya'] = $id_refList;
		$data['sts_ppdb'] = 1;
		//$data['jenis_pembayaran'] = $jen_pem->jenis_pembayaran;
		
		
		
		
		
		
		//$data['kelas'] = $get_kelas;
		$data['created_date']	= date("Y-m-d H:i:s");
		$data['created_by']	= $this->session->userdata('username');
		//$data['kodedesk']	= $get_kode->kodedesk;
		//$data['kodesub']	= $get_kode->kodesub;
		
		$q = $this->db->insert("trans_tabsis",$data);
		
		if ($q) {
				$diserahkan = $this->getDiserahkan($id_fak);
				//insert trans uang masuk
				$data_masuk['kodedesk'] 		= "IV";	
				$data_masuk['kodesub'] 			= "1";
				$data_masuk['tanggaltransaksi'] =  date('Y-m-d');
				
				$data_masuk['diserahkan'] 		= $this->session->userdata('username');
				$data_masuk['diterima'] 		= $diserahkan;
				
				$data_masuk['besarnya'] 		= $besarnya;
				$data_masuk['tanggalinput'] 	=  date('Y-m-d');
				$data_masuk['tahunajaran'] 		= $this->get_tahun_ajaran();
				$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_keluar",$data_masuk);
		}
			
		$update_sq['last_seq'] = $last_id;
		$this->db->where("nama_transaksi","uang_keluar");
		$this->db->update("ref_sequence_trans",$update_sq);	
		/*
		if($sisa==$besarnya){
			$this->update_approval_status($id_siswa);
		}
		*/
		//$last_id = $this->get_last_seq_insert();
		return true;	
		
		/*$t_trans = $this->input->post('tanggaltransaksi');
		$t_trans = date_create_from_format('d-m-Y', $t_trans);		
		$tanggaltransaksi = date_format($t_trans, 'Y-m-d');
		
		
		$last_id = $this->input->post('last_id');
		
		$kodedesk = array();
		$kodesub = array();
		$besarnya = array();
		
		
		$kodedesk = $this->input->post('kodedesk');
		$kodesub = $this->input->post('kodesub');
		$besarnya = $this->input->post('besarnya');
		$count = count($kodedesk);
		
		
		for ($i=0; $i<$count; $i++) {
			
			$data['no']		= $this->input->post('no');
			$data['tanggaltransaksi']	= $tanggaltransaksi;
			$data['tanggalinput']	= date('Y-m-d');
			$data['diserahkan']	= $this->input->post('diserahkan');
			$data['diterima']		= $this->input->post('diterima');
			$data['tahunajaran']	= $this->get_tahun_ajaran();
			$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'] = $besarnya[$i];
			
			$q = $this->db->insert("trans_uang_masuk",$data);
		}
		
		//update last_sequence
		$update_sq['last_seq'] = $last_id;
		$this->db->where("nama_transaksi","uang_masuk");
		$this->db->update("ref_sequence_trans",$update_sq);	
		
		//$q = $this->db->insert("trans_uang_masuk",$data);
		return true;*/	
		
	}
	
	function update_approval_status($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);
		$this->db->update("ppdb_siswa",$data);	
		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",1);
		$return = $this->db->get()->row()->tahun_ajaran;
		
		return $return;
	}
	
	function getDetailCicilan($idSiswa) {
		
		$q=$this->db->query("select sum(a.besarnya) as total from (select besarnya from trans_tabsis where id_siswa=".$idSiswa." and SUBSTRING(no_faktur, 1, 2)='FM' and sts_ppdb=1) a");
						
		$row = $q->row();
		$totalTabsis = $row->total;
		if(empty($totalTabsis)||$totalTabsis==NULL||$totalTabsis==0){
			$totalTabsis=0;
		}else{
			$totalTabsis=$totalTabsis;
		}
			
		
		
		$q=$this->db->query("select sum(a.besarnya) as total from (select besarnya from trans_tabsis where id_siswa=".$idSiswa." and SUBSTRING(no_faktur, 1, 2)='FK' and sts_ppdb=1) a");
						
		$row = $q->row();
		$totalTabsisMin = $row->total;
		if(empty($totalTabsisMin)||$totalTabsisMin==NULL||$totalTabsisMin==0){
			$totalTabsisMin=0;
		}else{
			$totalTabsisMin=$totalTabsisMin;
		}
		
		$total=$totalTabsis-$totalTabsisMin;
		//echo $tahun_ajaran;die();
		//total cicilan
		/*
		$this->db->select("sum(besarnya) total_cicilam");
		$this->db->from("ref_komponen_cicilan");
		$this->db->where("tahun_ajaran",$tahun_ajaran);
		$totalCicilan = $this->db->get()->row()->total_cicilam;
		*/
		//total transaksi
		
		
		return $total;
	}
	
	function getCicilan() {
		
		$this->db->select("Id, kodedesk, kodesub, daftarlist");
		$this->db->from("ref_list_uang_keluar");
		$this->db->where("kodedesk","IV");
		$this->db->where("kodesub","1");
		$datas = $this->db->get()->result_array();
		return $datas;
	}
	
	function getDetail($faktur) {
		
		//get siswa 
		$this->db->select("'PENARIKAN TABSIS' as jenis_pembayaran, besarnya, id_siswa"); 
		$this->db->from("trans_tabsis");
		$this->db->where("no_faktur",$faktur);
		
		$data = $this->db->get()->row();
		return $data;
		
	}
	
	function getDiserahkan($faktur) {
		$q=$this->db->query("
							select nama 
							from ppdb_siswa 
							where id_daftar = (select distinct id_siswa from trans_tabsis where no_faktur = '".$faktur."') 
						");
		$nama = $q->row()->nama;
		return $nama;
	}
	
}