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

class Perfomance_mkios extends CI_Model {

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

    // Listing
    public function get_mkios($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-(BULK*RBULK)) as TOTAL_MKIOS,kjnt_form_6.TARGET_MKIOS,(SUM(kjnt_form_2.TOTAL)-SUM(kjnt_form_2.BULK*kjnt_form_2.RBULK))/kjnt_form_6.TARGET_MKIOS*100 AS ACH_MKIOS,IF ((SUM(kjnt_form_2.TOTAL)-SUM(kjnt_form_2.BULK*kjnt_form_2.RBULK))/kjnt_form_6.TARGET_MKIOS*100 >= 100,30,(SUM(kjnt_form_2.TOTAL)-SUM(kjnt_form_2.BULK*kjnt_form_2.RBULK))/kjnt_form_6.TARGET_MKIOS*0.3) AS POINT_MKIOS,
        SUM(rs_bulk.TOTAL) as TOTAL_RS_BULK,kjnt_form_6.TARGET_RS_BULK,(SUM(rs_bulk.TOTAL)/kjnt_form_6.TARGET_RS_BULK)*100 AS ACH_RS_BULK,IF ((SUM(rs_bulk.TOTAL)/kjnt_form_6.TARGET_RS_BULK)*100 >= 95,10,(SUM(rs_bulk.TOTAL)/kjnt_form_6.TARGET_RS_BULK)*0.1) AS POINT_RS_BULK,
        SUM(kjnt_form_2.BULK*RBULK) as TOTAL_RP_BULK,kjnt_form_6.TARGET_RP_BULK,SUM(kjnt_form_2.BULK*kjnt_form_2.RBULK)/kjnt_form_6.TARGET_RP_BULK*100 AS ACH_RP_BULK,IF (SUM(kjnt_form_2.BULK*kjnt_form_2.RBULK)/kjnt_form_6.TARGET_RP_BULK*100 >= 100,15,SUM(kjnt_form_2.BULK*kjnt_form_2.RBULK)/kjnt_form_6.TARGET_RP_BULK*0.15) AS POINT_RP_BULK');
        $this->db->from('kjnt_form_2');
        $this->db->join('kjnt_form_6', 'kjnt_form_2.NOHP = kjnt_form_6.NOHP');
        $this->db->join('rs_bulk', 'kjnt_form_6.NOHP = rs_bulk.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();
    }
}