<?php 
class Ppdb_keuangan_model extends CI_Model {
	
	
	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_masuk');
		$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_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 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();*/				
		$this->db->select("id_daftar as id_siswa,nama, nisn");
		$this->db->from("ppdb_siswa");
		$this->db->where_in("approval_status",array("0","1"));
		$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_siswa'; 
		 
        if (empty($param['sort'])) {
            if (empty($cond)) {
                $sql = "
				select distinct 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 ,
				c.name
                from trans_siswa a, 
                ppdb_siswa b, ref_smk c
                where a.id_siswa = b.id_daftar  and b.id_smk=c.id and a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','1')
				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 a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','1')";
            } else {
                $sql = "
				 select distinct 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,
				c.name
                from trans_siswa a, 
                ppdb_siswa b, ref_smk c
                $cond and a.id_siswa = b.id_daftar and b.id_smk=c.id and a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','1')
				 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 a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','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, 
                a.jenis_pembayaran, 
                a.tanggal_pembayaran, 
                a.besarnya, 
				a.kelas, 
                a.status, 
                a.created_by, 
                a.created_date, 
                a.modified_by, 
                a.modified_date ,
				c.name
                from trans_siswa a, 
                ppdb_siswa b, ref_smk c
                where a.id_siswa = b.id_daftar  and b.id_smk=c.id and a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','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 a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','1')";
            } else {
                $sql = "
				select distinct 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,
				c.name
                from trans_siswa a, 
                ppdb_siswa b, ref_smk c
                $cond and a.id_siswa = b.id_daftar  and b.id_smk=c.id and a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','1')			
				 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 a.cicilan='1' and a.sts_ppdb='1'
                and b.approval_status in ('0','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 approval_status in ('0','1') and id_daftar = ".$id.") a order by a.nama
						");
		
		
		$kelas = $q->row()->kelas;
		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 insert_data() {
		
		//$data = array();
		$last_id = $this->input->post('last_id');
		
		$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);
		$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);
		$id_refList					= $this->getIdRefListBiaya($this->input->post('id_mapping_cicilan'));
		//$data['kodesub'] = "1";
		//$data['kodedesk'] = "II";	
		$data['id_ref_list_biaya'] = $id_refList;
		$data['id_list_biaya'] = $this->input->post('id_mapping_cicilan');
		$data['cicilan'] = 1;
		$data['sts_ppdb'] = 1;
		$data['jenis_pembayaran'] = $jen_pem->jenis_pembayaran;
		$data['tanggal_pembayaran']	= $tanggal_transaksi;
		$data['no_faktur']	= $this->input->post('no_faktur');
		$data['id_siswa']	= $id_siswa;
		$data['besarnya']	= $this->input->post('besarnya');
		$data['tahun_ajaran']	= $this->get_tahun_ajaran();
		$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_siswa",$data);
		
		
		if ($q) {
				$diserahkan = $this->getDiserahkan($this->input->post('no_faktur'));
				//insert trans uang masuk
				$data_masuk['kodedesk'] 		= $get_kode->kodedesk;
				$data_masuk['kodesub'] 			= $get_kode->kodesub;
				$data_masuk['tanggaltransaksi'] = $tanggal_transaksi;
				
				$data_masuk['diserahkan'] 		= $diserahkan;
				$data_masuk['diterima'] 		= $this->session->userdata('username');
				
				$data_masuk['besarnya'] 		= $besarnya;
				$data_masuk['tanggalinput'] 	= $tanggal_transaksi;
				$data_masuk['tahunajaran'] 		= $this->get_tahun_ajaran();
				$data_masuk['no'] 				= $this->input->post('no_faktur');
				$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_sq['last_seq'] = $last_id;
		$this->db->where("nama_transaksi","uang_masuk");
		$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, $cicilan) {
		
		$this->db->select("tahun_ajaran, jenis_pembayaran, besarnya");
		$this->db->from("ref_mapping_cicilan_siswa");
		$this->db->where("id_ppdb",$idSiswa);
		$this->db->where("status",0);
		$this->db->where("id",$cicilan);
		$row = $this->db->get()->row();
		$tahun_ajaran = $row->tahun_ajaran;
		$totalCicilan = $row->besarnya;
		
		//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
		$this->db->select("sum(besarnya) total_trans");
		$this->db->from("trans_siswa");
		$this->db->where(
			array(
				"id_siswa" 		=> $idSiswa,
				//"kodedesk" 		=> "II",
				//"kodesub"		=> "1"	,
				"cicilan"		=> '1',
				"sts_ppdb"		=> '1',
				"id_list_biaya"	=>	$cicilan
			)
						);
		$getTrans = $this->db->get();
		$num = $getTrans->num_rows();
		if ($num < 1) {
			$sisaCicilan = 0;
		} else {
			$toTrans = $getTrans->row()->total_trans;
			$sisaCicilan = $totalCicilan - $toTrans; 
		}

		$data = array (
			"sisa" 	=> $sisaCicilan,
			"total" => $totalCicilan
		);	
		
		return $data;
	}
	
	function getCicilan($idSiswa) {
		
		$this->db->select("id, id_coa, tahun_ajaran, jenis_pembayaran");
		$this->db->from("ref_mapping_cicilan_siswa");
		$this->db->where("id_ppdb",$idSiswa);
		$this->db->where("status",0);
		$datas = $this->db->get()->result_array();
		return $datas;
	}
	
	function getDetail($faktur) {
		
		//get siswa 
		$this->db->select("jenis_pembayaran, besarnya, id_siswa"); 
		$this->db->from("trans_siswa");
		$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_siswa where no_faktur = '".$faktur."' and sts_ppdb=1) 
						");
		$nama = $q->row()->nama;
		return $nama;
	}
	
}