<?php
/*
Fungsi : Model untuk modul admin
*/
class Fin_model extends CI_Model {
	/**
	 * Constructor
	 */
	function Fin_model()
	{
		parent::__construct();
	}
	
/* Insialiasi nama table */

	var $table_cpv			= 't_cpv';
	var $table_cabang 		= 'm_cab';
	var $table_pc_typ 		= 'm_pc_typ';
	var $table_bank_bal 	= 't_bankbal';
	
	var $table_rmb_header         = 't_rmb_header';
	var $table_rmb_detail         = 't_rmb_detail';

	var $table_cpv_sppv = 'sppv.cpv';
	var $table_cpv_det_sppv = 'sppv.detail_cpv';
	
/*-----------------------SPPV MODEL---------------------------*/

function get_last_num_cpv_header_sppv()
		{
			$dbs =  $this->config->item('DB_SPPV');
			$dbs->select('max(no_cpv) as no_cpv');
			$dbs->from('sppv.cpv');
			$query = $dbs->get();
			return $query;
		}
		
	function insert_cpv_detail_sppv($cpv)
	{
		$dbs =  $this->config->item('DB_SPPV');
  		$dbs->insert($this->table_cpv_det_sppv, $cpv);
	}
	
	function insert_cpv_header_sppv($cpv)
	{
		$dbs =  $this->config->item('DB_SPPV');
  		$dbs->insert($this->table_cpv_sppv, $cpv);
	}
/*-----------------------SPPV MODEL---------------------------*/

/*-----------------------REMBUIRSEMENT MODEL---------------------------*/	
function get_rmb_header_bynocpv($nocpv)
		{
			$this->db->where('nocpvform', $nocpv);
			return $this->db->get($this->table_rmb_header);
		}
/*Function get bpkk header detail from rmb*/
function get_detail_bpkk_header($idcab, $normb,$year)
		{
			$this->db->select('t_bpkk_header.nobpkk, 
								t_bpkk_header.idcab,
								t_bpkk_header.nobpkkform, 
								t_bpkk_header.total,  
								t_bpkk_header.year,
								t_bpkk_header.addby,
								t_bpkk_header.adddate');
			$this->db->from('t_rmb_detail');
			//$this->db->join('t_bpkk_header', 't_rmb_detail.nobpkk = t_bpkk_header.nobpkk ');
			/*--Update 18.11.2013----*/
			$this->db->join('t_bpkk_header', 't_rmb_detail.nobpkk = t_bpkk_header.nobpkk  AND t_bpkk_header.idcab = t_rmb_detail.idcab  AND t_rmb_detail.year = t_bpkk_header.year');
			/*--Update 18.11.2013----*/
			$this->db->where('t_rmb_detail.idcab', $idcab); 
			$this->db->where('t_rmb_detail.normb', $normb); 
			$this->db->where('t_rmb_detail.year', $year); 
			$query = $this->db->get();
			return $query;
		}
		
function get_rmb_header_detail($idcab, $rmbno, $year)
{
	$this->db->select('m_cab.cabname,
								t_rmb_header.idcab,
								t_rmb_header.normb,
								t_rmb_header.normbform,
								t_rmb_header.total,
								t_rmb_header.addby,
								t_rmb_header.adddate,
								t_rmb_header.year');
			$this->db->from('t_rmb_header');
			$this->db->join('m_cab', 'm_cab.idcab = t_rmb_header.idcab');
			$this->db->where('t_rmb_header.status', 0); 
			$this->db->where('t_rmb_header.idcab', $idcab);
			$this->db->where('t_rmb_header.normb', $rmbno);
			$this->db->where('t_rmb_header.year', $year);
			$query = $this->db->get();
			return $query;
}

/*Get list pending rembuirsement*/
	function get_pending_rmb($limit, $offset)
		{
			$this->db->select('m_cab.cabname,
								t_rmb_header.idcab,
								t_rmb_header.normb,
								t_rmb_header.normbform,
								t_rmb_header.total,
								t_rmb_header.addby,
								t_rmb_header.adddate,
								t_rmb_header.year');
			$this->db->from('t_rmb_header');
			$this->db->join('m_cab', 'm_cab.idcab = t_rmb_header.idcab');
			$this->db->where('t_rmb_header.status', 0); 
			$this->db->order_by('t_rmb_header.normb', 'DESC'); 
			$this->db->order_by('t_rmb_header.year', 'DESC'); 
			$this->db->limit($limit, $offset);
			$query = $this->db->get();
			return $query;
		}
		
/*Get Count list rembuirsement pending*/
	function get_pending_rmb_count()
		{
			$this->db->select('*');
			$this->db->from('t_rmb_header');
			$this->db->join('m_cab', 'm_cab.idcab = t_rmb_header.idcab');
			$this->db->where('t_rmb_header.status', 0); 
			$this->db->order_by('t_rmb_header.normb', 'DESC'); 
			$this->db->order_by('t_rmb_header.year', 'DESC'); 
			$query = $this->db->get();
			return $query;
		}

/*-----------------------REMBUIRSEMENT MODEL---------------------------*/	

/*-----------------------BANK BALANCE MODEL---------------------------*/	

/*-----------------------BANK BALANCE MODEL---------------------------*/	
	/*Add Balance*/
  function add_bankbal($bankbal)
	{
  		$this->db->insert($this->table_bank_bal,$bankbal);
	}
/*-----------------------BANK BALANCE MODEL---------------------------*/	

/*-----------------------CPV MODEL---------------------------*/	
	/*Get list cabang*/
  /*function get_cabang_list()
	{
  		$sql_cabang = $this->db->get($this->table_cabang);
		$this->db->where('active', 1);
  		return $sql_cabang;
	}*/
	

	/*Add CPV*/
  function add_cpv($cpv)
	{
  		$this->db->insert($this->table_cpv,$cpv);
	}
	
	
/*Add CPV by ID & year*/
  function get_cpv_bynoyear($nocpv, $year)
	{
  		$this->db->where('nocpv', $nocpv);
		$this->db->where('year', $year);
		return $this->db->get($this->table_cpv);
	}


//update cpv
function update_cpv($nocpv, $year, $cpv)
	{
		$this->db->where('nocpv', $nocpv);
		$this->db->where('year', $year);
		$this->db->update($this->table_cpv, $cpv);
	}
	
	/*Get list petty cash type*/
  function get_pc_typ_list()
	{
  		$sql_pc_typ = $this->db->get($this->table_pc_typ);
  		return $sql_pc_typ;
	}
	
	
	/*Get petty cash type by ID*/
  function get_pc_typ_byidpctyp($idpctyp)
	{
  		$this->db->where('idpctyp', $idpctyp);
		return $this->db->get($this->table_pc_typ);
	}
	
	/*Get list pending CPV*/
	function get_list_cpv_pend_paging($limit, $offset)
		{
			$this->db->select('m_cab.cabname,
							  t_cpv.nocpv,
							  t_cpv.nocpvform,
							  t_cpv.miro,
							  t_cpv.amount_trf,
							  t_cpv.amount_req,
							  t_cpv.`desc`,
							  t_cpv.status,
							  t_cpv.pctyp,
							  t_cpv.year,
							  t_cpv.addby,
							  t_cpv.chby,
							  t_cpv.chdate,
							  t_cpv.adddate,
							  t_cpv.pctrans,
							  m_pc_typ.`desc` as desc2,
							  m_pc_trans.`desc` as desc3');
			$this->db->from('t_cpv');
			$this->db->join('m_cab', 'm_cab.idcab = t_cpv.idcab');
			$this->db->join('m_pc_typ', 't_cpv.pctyp = m_pc_typ.idpctyp');
			$this->db->join('m_pc_trans', 't_cpv.pctrans = m_pc_trans.idpctrans');
			$this->db->where('t_cpv.status', 0);
			$this->db->order_by('t_cpv.nocpv', 'DESC'); 
			$this->db->order_by('t_cpv.year', 'DESC'); 
			$this->db->limit($limit, $offset);
			$query = $this->db->get();
			return $query;
		}
	/*Get list pending CPV count*/	
	function get_list_cpv_count()
		{
			$this->db->select('m_cab.cabname,
							  t_cpv.nocpv,
							  t_cpv.nocpvform,
							  t_cpv.miro,
							  t_cpv.amount_trf,
							  t_cpv.amount_req,
							  t_cpv.`desc` as cpv_desc,
							  t_cpv.status,
							  t_cpv.pctyp,
							  t_cpv.addby,
							  t_cpv.chby,
							  t_cpv.chdate,
							  t_cpv.adddate,
							  t_cpv.pctrans,
							  t_cpv.year,
							  m_pc_typ.`desc`,
							  m_pc_trans.`desc`');
			$this->db->from('t_cpv');
			$this->db->join('m_cab', 'm_cab.idcab = t_cpv.idcab');
			$this->db->join('m_pc_typ', 't_cpv.pctyp = m_pc_typ.idpctyp');
			$this->db->join('m_pc_trans', 't_cpv.pctrans = m_pc_trans.idpctrans');
			$this->db->where('t_cpv.status', 0);
			$query = $this->db->get();
			return $query->num_rows;
		}

/*Get list pending CPV by CPV no & year*/
	function get_detail_cpv_bynoyear($cpvno, $year)
		{
			$this->db->select('m_cab.cabname,
								m_cab.idcab,
							  t_cpv.nocpv,
							  t_cpv.nocpvform,
							  t_cpv.miro,
							  t_cpv.amount_trf,
							  t_cpv.amount_req,
							  t_cpv.`desc`,
							  t_cpv.status,
							  t_cpv.pctyp,
							  t_cpv.year,
							  t_cpv.addby,
							  t_cpv.chby,
							  t_cpv.chdate,
							  t_cpv.adddate,
							  t_cpv.pctrans,
							  m_pc_typ.`desc` as desc2,
							  m_pc_trans.`desc` as desc3');
			$this->db->from('t_cpv');
			$this->db->join('m_cab', 'm_cab.idcab = t_cpv.idcab');
			$this->db->join('m_pc_typ', 't_cpv.pctyp = m_pc_typ.idpctyp');
			$this->db->join('m_pc_trans', 't_cpv.pctrans = m_pc_trans.idpctrans');
			$this->db->where('t_cpv.status', 0);
			$this->db->where('t_cpv.nocpv', $cpvno);
			$this->db->where('t_cpv.year', $year);
			$query = $this->db->get();
			return $query;
		}
		
		/*Get list CPV Print*/
	function get_list_cpv_print_paging($limit, $offset)
		{
			$this->db->select('m_cab.cabname,
			 				   m_cab.pcbankglacc,
							  t_cpv.nocpv,
							  t_cpv.nocpdu,
							  t_cpv.nocpvform,
							  t_cpv.miro,
							  t_cpv.amount_trf,
							  t_cpv.amount_req,
							  t_cpv.`desc`,
							  t_cpv.status,
							  t_cpv.pctyp,
							  t_cpv.year,
							  t_cpv.addby,
							  t_cpv.chby,
							  t_cpv.chdate,
							  t_cpv.adddate,
							  t_cpv.appby,
							  t_cpv.appdate,
							  t_cpv.pctrans,
							  m_pc_typ.`desc` as desc2,
							  m_pc_trans.`desc` as desc3');
			$this->db->from('t_cpv');
			$this->db->join('m_cab', 'm_cab.idcab = t_cpv.idcab');
			$this->db->join('m_pc_typ', 't_cpv.pctyp = m_pc_typ.idpctyp');
			$this->db->join('m_pc_trans', 't_cpv.pctrans = m_pc_trans.idpctrans');
			$this->db->where('t_cpv.status', 1);
			//$this->db->order_by('t_cpv.nocpv', 'DESC'); 
			//$this->db->order_by('t_cpv.year', 'DESC'); 
			$this->db->order_by('t_cpv.adddate', 'DESC'); 
			$this->db->limit($limit, $offset);
			$query = $this->db->get();
			return $query;
		}
	/*Get list pending CPV print count*/	
	function get_list_cpv_print_count()
		{
			$this->db->select('m_cab.cabname,
							  t_cpv.nocpv,
							  t_cpv.nocpvform,
							  t_cpv.miro,
							  t_cpv.amount_trf,
							  t_cpv.amount_req,
							  t_cpv.`desc` as cpv_desc,
							  t_cpv.status,
							  t_cpv.pctyp,
							  t_cpv.appby,
							  t_cpv.chby,
							  t_cpv.chdate,
							  t_cpv.appdate,
							  t_cpv.pctrans,
							  t_cpv.year,
							  m_pc_typ.`desc`,
							  m_pc_trans.`desc`');
			$this->db->from('t_cpv');
			$this->db->join('m_cab', 'm_cab.idcab = t_cpv.idcab');
			$this->db->join('m_pc_typ', 't_cpv.pctyp = m_pc_typ.idpctyp');
			$this->db->join('m_pc_trans', 't_cpv.pctrans = m_pc_trans.idpctrans');
			$this->db->where('t_cpv.status', 1);
			$query = $this->db->get();
			return $query->num_rows;
		}

function get_list_cpv_gl_pend_pag($limit, $offset)
{
	$where = "t_cpv.nocpdu <> '' AND m_pc_typ.idpctyp not in (0, 1)";
	$this->db->select('m_cab.cabname,
							  t_cpv.nocpv,
							  t_cpv.idcab,
							  t_cpv.nocpvform,
							  t_cpv.miro,
							  t_cpv.amount_trf,
							  t_cpv.amount_req,
							  t_cpv.`desc`,
							  t_cpv.status,
							  t_cpv.pctyp,
							  t_cpv.appby,
							  t_cpv.chby,
							  t_cpv.chdate,
							  t_cpv.appdate,
							  t_cpv.pctrans,
							  t_cpv.year,
							  t_cpv.clrno,
							  m_pc_typ.`desc` as desc2,
							  m_pc_trans.`desc` as desc3');
			$this->db->from('t_cpv');
			$this->db->join('m_cab', 'm_cab.idcab = t_cpv.idcab');
			$this->db->join('m_pc_typ', 't_cpv.pctyp = m_pc_typ.idpctyp');
			$this->db->join('m_pc_trans', 't_cpv.pctrans = m_pc_trans.idpctrans');
			$this->db->where($where);
			$this->db->where('t_cpv.accglpost', 0);
			$this->db->where('t_cpv.status', 1);
			$this->db->order_by('t_cpv.nocpv', 'DESC'); 
			$this->db->order_by('t_cpv.year', 'DESC'); 
			$this->db->limit($limit, $offset);
			$query = $this->db->get();
			return $query;
	
}
	
	/*Get list pending CPV G/L post count*/	
	function get_list_cpv_gl_pend_count()
		{	
			$where = "t_cpv.nocpdu <> '' AND m_pc_typ.idpctyp not in (0, 1)";
			$this->db->select('m_cab.cabname,
							  t_cpv.nocpv,
							  t_cpv.nocpvform,
							  t_cpv.miro,
							  t_cpv.amount_trf,
							  t_cpv.amount_req,
							  t_cpv.`desc` as cpv_desc,
							  t_cpv.status,
							  t_cpv.pctyp,
							  t_cpv.appby,
							  t_cpv.chby,
							  t_cpv.chdate,
							  t_cpv.appdate,
							  t_cpv.pctrans,
							  t_cpv.year,
							  m_pc_typ.`desc`,
							  m_pc_trans.`desc`');
			$this->db->from('t_cpv');
			$this->db->join('m_cab', 'm_cab.idcab = t_cpv.idcab');
			$this->db->join('m_pc_typ', 't_cpv.pctyp = m_pc_typ.idpctyp');
			$this->db->join('m_pc_trans', 't_cpv.pctrans = m_pc_trans.idpctrans');
			$this->db->where($where);
			$this->db->where('t_cpv.accglpost', 0);
			$this->db->where('t_cpv.status', 1);
			$query = $this->db->get();
			return $query->num_rows;
		}
/*-----------------------CPV MODEL---------------------------*/	

}