<?php 
class Uang_masuk_model extends CI_Model {
	
	
	function getTransaksi($faktur) {
		$q = $this->db->query("
			select a.besarnya, b.daftarlist 
			from ref_list_uang_masuk b,
			trans_uang_masuk a
			where a.kodesub = b.kodesub
			and a.kodedesk = b.kodedesk
			and a.no = '".$faktur."'
						");
		return $q->result_array();				
	}
	
	function updateStatusFaktur($faktur) {
		
		$data['status'] = 'CETAK';
		$this->db->where("no",$faktur);
		$q = $this->db->update("trans_uang_masuk",$data);
		return $q;
		
	}
	
	function getTotal($faktur) {
		$q = $this->db->query("
			select sum(besarnya) as total from trans_uang_masuk
			    where no='".$faktur."'
		");
		$total = $q->row()->total;
		return $total;		
	}
	
	function getDiserahkan($faktur) {
		$q = $this->db->query("
			select distinct diserahkan from trans_uang_masuk
			    where no='".$faktur."'
		");
		$diserahkan = $q->row()->diserahkan;
		return $diserahkan;
	}
	
	function getDiterima($faktur) {
		$q = $this->db->query("
			select distinct diterima from trans_uang_masuk
			    where no='".$faktur."'
		");
		$diterima = $q->row()->diterima;
		return $diterima;
	}
	
	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'] = 1;
			$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_kd_detail($kd_desk) {
		$this->db->select("kodesub,daftarlist");
		$this->db->from("ref_list_uang_masuk");
		$this->db->where("kodedesk",$kd_desk);
		$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
                $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_uang_masuk'; 
		 
        if (empty($param['sort'])) {
            if (empty($cond)) {
                $sql = "select tanggaltransaksi, tanggalinput, besarnya, 
				diserahkan, diterima, tahunajaran, no, status, created_by, created_date from $table where diserahkan IS NOT NULL and diterima IS NOT NULL order by id limit ?, ?";
                $sqlcount = "select no from $table ";
            } else {
                $sql = "select status, tanggaltransaksi, tanggalinput,besarnya, 
				diserahkan, diterima, tahunajaran, no, created_by, created_date from $table $cond and diserahkan IS NOT NULL and diterima IS NOT NULL order by id limit ?, ?";
                $sqlcount = "select no from $table $cond";
            }
            $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 status, tanggaltransaksi, tanggalinput, besarnya, diserahkan, diterima, tahunajaran, no, created_by, created_date from $table where diserahkan IS NOT NULL and diterima IS NOT NULL order by $sort $order limit ?, ?";
                $sqlcount = "select no from $table";
            } else {
                $sql = "select status, tanggaltransaksi, tanggalinput, besarnya, 
				diserahkan, diterima, tahunajaran, no, created_by, created_date from $table $cond and diserahkan IS NOT NULL and diterima IS NOT NULL order by $sort $order limit ?, ?";
                $sqlcount = "select no from $table $cond";
            }
            $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 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) {
		/*
		$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->get_last_seq();
		$last_id 		= $last_id + 1;
		$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']		= $id_fak;
			$data['tanggaltransaksi']	= date('Y-m-d');
			$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 get_tahun_ajaran() {
	
		$this->db->select("tahun_ajaran");
		$this->db->from("ref_tahun_ajaran");
		$this->db->where("status","U");
		$return = $this->db->get()->row()->tahun_ajaran;
		
		return $return;
	}
	
	
}