<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Perfomance_VCH extends CI_Model {

    public function __construct()
    {
        parent::__construct();
    }

    // Listing
    public function get_VCH($dari,$sampai)
    {
        if(!empty($dari) && !empty($sampai))
        {
            $this->db->where('substr(kjnt_form_2.TGLTRX,1,10) >=', $dari);
            $this->db->where('substr(kjnt_form_2.TGLTRX,1,10) <=', $sampai);

        } else {
            $this->db->where('substr(kjnt_form_2.TGLTRX,1,10) >=',date("Y-m-d"));
        }
        $this->db->select('kjnt_form_6.USERID as NMSAL,kjnt_form_6.KELURAHAN,kjnt_form_6.KECAMATAN,kjnt_form_6.CABANG,SUM(kjnt_form_2.TOTAL-(FISIK*RFISIK)) as TOTAL_VCH,kjnt_form_6.TARGET_VCH,(SUM(kjnt_form_2.TOTAL)-SUM(kjnt_form_2.FISIK*kjnt_form_2.RFISIK))/kjnt_form_6.TARGET_VCH*100 AS ACH_VCH,IF ((SUM(kjnt_form_2.TOTAL)-SUM(kjnt_form_2.FISIK*kjnt_form_2.RFISIK))/kjnt_form_6.TARGET_VCH*100 >= 100,30,(SUM(kjnt_form_2.TOTAL)-SUM(kjnt_form_2.FISIK*kjnt_form_2.RFISIK))/kjnt_form_6.TARGET_VCH*0.3) AS POINT_VCH,
        SUM(rs_FISIK.TOTAL) as TOTAL_RS_FISIK,kjnt_form_6.TARGET_RS_FISIK,(SUM(rs_FISIK.TOTAL)/kjnt_form_6.TARGET_RS_FISIK)*100 AS ACH_RS_FISIK,IF ((SUM(rs_FISIK.TOTAL)/kjnt_form_6.TARGET_RS_FISIK)*100 >= 95,10,(SUM(rs_FISIK.TOTAL)/kjnt_form_6.TARGET_RS_FISIK)*0.1) AS POINT_RS_FISIK,
        SUM(kjnt_form_2.FISIK*RFISIK) as TOTAL_RP_FISIK,kjnt_form_6.TARGET_RP_FISIK,SUM(kjnt_form_2.FISIK*kjnt_form_2.RFISIK)/kjnt_form_6.TARGET_RP_FISIK*100 AS ACH_RP_FISIK,IF (SUM(kjnt_form_2.FISIK*kjnt_form_2.RFISIK)/kjnt_form_6.TARGET_RP_FISIK*100 >= 100,15,SUM(kjnt_form_2.FISIK*kjnt_form_2.RFISIK)/kjnt_form_6.TARGET_RP_FISIK*0.15) AS POINT_RP_FISIK');
        $this->db->from('kjnt_form_2');
        $this->db->join('kjnt_form_6', 'kjnt_form_2.NOHP = kjnt_form_6.NOHP');
        $this->db->join('rs_FISIK', 'kjnt_form_6.NOHP = rs_FISIK.NOHP');
        $this->db->order_by('kjnt_form_6.NOHP', 'asc');
        $this->db->group_by('kjnt_form_6.NOHP');
        $query = $this->db->get();
        return $query->result();
    }
}