<?php

class Dashboard_model extends CI_Model {

	public function select_tahun()
	{		
		$sql = "SELECT q.tahun AS id, q.tahun AS value FROM (SELECT YEAR(tgl_daftar) AS tahun FROM data_pendaftaran group by YEAR(tgl_daftar)
		UNION 
		SELECT YEAR(tgl_pemeriksaan) AS tahun FROM data_pemeriksaan group by YEAR(tgl_pemeriksaan)) AS q ORDER BY q.tahun DESC";

		return $this->db->query($sql)->result();
	}

	public function pendaftaran_per_debitur($context)
	{
		$jml_hari = cal_days_in_month(CAL_GREGORIAN, $context['bulan'], $context['tahun']);

		for ($i=1; $i <= $jml_hari; $i++)
		{
			if($i == 1)
			{
				$select = 'SUM(if(DAY(d_pen.tgl_daftar)=1,1,0)) AS hari_1';
			}
			else $select .= ', SUM(if(DAY(d_pen.tgl_daftar)='.$i.',1,0)) AS hari_'.$i;
		}

		$this->db->select($select.', a_d.value AS debitur');
		$this->db->from('data_pendaftaran d_pen');	
		$this->db->join('asset_debitur a_d', 'a_d.id=d_pen.debitur', 'left');		
		$this->db->where('MONTH(d_pen.tgl_daftar)', $context['bulan']);		
		$this->db->where('YEAR(d_pen.tgl_daftar)', $context['tahun']);	
		$this->db->group_by('d_pen.debitur');	

		return $this->db->get()->result();
	}
}	