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

class Model_claim_report extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }


    public function get_business_entity_summary($business_entity_id,$month,$year)
    {
        $records = $this->db->select('a.*')
                ->where('a.business_entity_id',$business_entity_id)
                ->where('a.year',$year)
                ->where('a.month',$month)
                ->get('view_report_claim a')
                ->result_array();

        return (sizeof($records) > 0)?$records:null;
    }

    public function get_department_summary($department_id,$month,$year)
    {
        $records = $this->db->select('a.*')
                ->where('a.department_id',$department_id)
                ->where('a.year',$year)
                ->where('a.month',$month)
                ->get('view_report_claim a')
                ->result_array();

        return (sizeof($records) > 0)?$records:null;
    }
    
    public function get_claim_type_total($month,$year)
    {
        $records = $this->db->select('
                            sum(a.overtime) as overtime, 
                            sum(a.dental_approved) as dental_approved,
                            sum(a.hospitalization_approved) as hospitalization_approved,
                            sum(a.outpatient_approved) as outpatient_approved,
                            sum(a.medical_screening_approved) as medical_screening_approved,
                            sum(a.meals_approved) as meals_approved,
                            sum(a.others_tng) as others_tng,
                            sum(a.others_purchase) as others_purchase,
                            sum(a.others_call) as others_call,
                            sum(a.others_airport_taxes) as others_airport_taxes,
                            sum(a.others_service_taxes) as others_service_taxes,
                            sum(a.others_tips_and_portage) as others_tips_and_portage,
                            sum(a.others_laundry) as others_laundry,
                            sum(a.others_excess_baggage) as others_excess_baggage,
                            sum(a.others_loss_in_foreign_exchange) as others_loss_in_foreign_exchange,
                            sum(a.others_firm_event) as others_firm_event,
                            sum(a.others_motor_expenses) as others_motor_expenses,
                            sum(a.others_ssm_search) as others_ssm_search,
                            sum(a.others_book) as others_book,
                            sum(a.others_stationary) as others_stationary,
                            sum(a.others_membership) as others_membership,
                            sum(a.others_printing) as others_printing,
                            sum(a.others_stamping) as others_stamping,
                            sum(a.others_license) as others_license,
                            sum(a.mileage_approved) as mileage_approved,
                            sum(a.accomodation_claim_approved) as accomodation_claim_approved,
                            sum(a.subsistence_outstation) as subsistence_outstation,
                            sum(a.subsistence_meals) as subsistence_meals,
                            sum(a.transportation_car_rental_approved) as transportation_car_rental_approved,
                            sum(a.transportation_flight_approved) as transportation_flight_approved,
                            sum(a.transportation_public_transport_approved) as transportation_public_transport_approved,
                            sum(a.transportation_e_hailing_approved) as transportation_e_hailing_approved,
                            sum(a.transportation_trains_approved) as transportation_trains_approved,
                            sum(a.parking_claim_approved) as parking_claim_approved,
                            sum(a.toll_claim_approved) as toll_claim_approved,
                            sum(a.trainer_claim_approved) as trainer_claim_approved,
                            sum(a.panel_claim) as panel_claim,
                            sum(a.petrol_claim_approved) as petrol_claim_approved,
                            sum(a.entertainment_claim_approved) as entertainment_claim_approved,
                            sum(a.other_courier) as other_courier,
                            sum(a.dressing_approved) as dressing_approved,
                            sum(a.gadget_approved) as gadget_approved,
                            ')
                    ->where('a.year',$year)
                    ->where('a.month',$month)
                    ->get('view__report_department_claim a')
                    ->result_array();

        return (sizeof($records) > 0)?$records[0]:null;
    }

    public function get_user_ot_by_department_id($month,$year,$department_id,$not_in = array())
    {
        $this->db->select('a.*,b.name')
            ->where('a.year',$year)
            ->where('a.month',$month)
            ->where('a.is_delete',0);

            if(!empty($not_in))
            {
                $this->db->where_not_in('a.peopleID',$not_in);
            }
            $result = $this->db->join('user__employment_detail b','b.user_id = a.peopleID and b.department_id = '.$department_id)
            ->get('hr__ot a')
            ->result_array();

        return $result;
    }

    public function get_user_ot_by_business_entity_id($month,$year,$business_entity_id,$not_in = array())
    {
        $this->db->select('a.*,b.name')
            ->where('a.year',$year)
            ->where('a.month',$month)
            ->where('a.is_delete',0);

            if(!empty($not_in))
            {
                $this->db->where_not_in('a.peopleID',$not_in);
            }
            $result = $this->db->join('user__employment_detail b','b.user_id = a.peopleID and b.business_entity_id = '.$business_entity_id)
            ->get('hr__ot a')
            ->result_array();

        return $result;
    }

    public function get_claim_origin($month,$year)
    {
        $records = $this->db->select('a.user_id,a.name,a.department_id,a.business_entity_id,a.business_entity_name,a.year,a.month,a.overtime,
                                    (a.dental_approved 
                                    + a.hospitalization_approved 
                                    + a.meals_approved 
                                    + a.medical_screening_approved 
                                    + a.others_tng 
                                    + a.others_purchase 
                                    + a.others_call 
                                    + a.others_airport_taxes
                                    + a.others_service_taxes
                                    + a.others_tips_and_portage
                                    + a.others_laundry
                                    + a.others_excess_baggage
                                    + a.others_loss_in_foreign_exchange
                                    + a.others_firm_Event
                                    + a.others_motor_expenses
                                    + a.others_ssm_search
                                    + a.other_book
                                    + a.other_stationary
                                    + a.other_membership
                                    + a.other_printing
                                    + a.other_stamping
                                    + a.other_license
                                    + a.total_others_approved
                                    + a.outpatient_approved
                                    + a.mileage_approved
                                    + a.accomodation_claim_approved
                                    + a.subsistence_outstation
                                    + a.subsistence_meals
                                    + a.transportation_car_rental_approved
                                    + a.transportation_flight_approved
                                    + a.transportation_public_transport_approved
                                    + a.transportation_e_hailing_approved
                                    + a.transportation_trains_approved
                                    + a.parking_claim_approved
                                    + a.toll_claim_approved
                                    + a.trainer_claim_approved
                                    + a.petrol_claim_approved
                                    + a.entertainment_claim_approved) as monthly_claim')
                ->where('a.year',$year)
                ->where('a.month',$month)
                ->order_by('a.business_entity_id')
                ->get('view_report_claim a')
                ->result_array();
        
        return (sizeof($records) > 0)?$records:null;
    }

    public function get_claim($month,$year)
    {
        $records = $this->db->select('a.user_id,a.name,a.department_id,a.business_entity_id,a.business_entity_name,a.year,a.month,a.overtime,
                                    (ifnull(a.dental_approved,0.00) 
                                    + ifnull(a.hospitalization_approved,0.00) 
                                    + ifnull(a.meals_approved,0.00) 
                                    + ifnull(a.medical_screening_approved,0.00) 
                                    + ifnull(a.total_others_approved,0.00)
                                    + ifnull(a.outpatient_approved,0.00)
                                    + ifnull(a.mileage_approved,0.00)
                                    + ifnull(a.accomodation_claim_approved,0.00)
                                    + ifnull(a.subsistence_outstation,0.00)
                                    + ifnull(a.subsistence_meals,0.00)
                                    + ifnull(a.transportation_car_rental_approved,0.00)
                                    + ifnull(a.transportation_flight_approved,0.00)
                                    + ifnull(a.transportation_public_transport_approved,0.00)
                                    + ifnull(a.transportation_e_hailing_approved,0.00)
                                    + ifnull(a.transportation_trains_approved,0.00)
                                    + ifnull(a.parking_claim_approved,0.00)
                                    + ifnull(a.toll_claim_approved,0.00)
                                    + ifnull(a.trainer_claim_approved,0.00)
                                    + ifnull(a.petrol_claim_approved,0.00)
                                    + ifnull(a.entertainment_claim_approved,0.00)
                                    + ifnull(a.dressing_approved,0.00)
                                    + ifnull(a.gadget_approved,0.00)
                                ) as monthly_claim')
                ->where('a.year',$year)
                ->where('a.month',$month)
                ->order_by('a.business_entity_id')
                ->get('view_report_claim a')
                ->result_array();
        
        return (sizeof($records) > 0)?$records:null;
    }

    public function get_business_entity_summary_v2($business_entity_id,$month,$year)
    {
        $records = $this->db->select('a.department_id, b.name as dept_name, c.name as branch_name, a.business_entity_id, d.name as business_entity_name, a.user_id, a.name, ifnull(f.year, g.year) as year, ifnull(f.month, g.month) as month')
                ->select('sum(ifnull(f.overtime,0)) + sum(if(f.overtime > 0, 0, ifnull(g.totalClaim,0))) as overtime')
                ->select('sum(ifnull(f.dental_approved,0)) as dental_approved, sum(ifnull(f.hospitalization_approved,0)) as hospitalization_approved, sum(ifnull(f.meals_approved,0)) as meals_approved, sum(ifnull(f.medical_screening_approved,0)) as medical_screening_approved, sum(ifnull(f.others_tng,0)) as others_tng, sum(ifnull(f.others_purchase,0)) as others_purchase, sum(ifnull(f.others_call,0)) as others_call, sum(ifnull(f.others_airport_taxes,0)) as others_airport_taxes, sum(ifnull(f.others_service_taxes,0)) as others_service_taxes, sum(ifnull(f.others_tips_and_portage,0)) as others_tips_and_portage, sum(ifnull(f.others_laundry,0)) as others_laundry, sum(ifnull(f.others_excess_baggage,0)) as others_excess_baggage, sum(ifnull(f.others_loss_in_foreign_exchange,0)) as others_loss_in_foreign_exchange, sum(ifnull(f.others_firm_Event,0)) as others_firm_Event, sum(ifnull(f.others_motor_expenses,0)) as others_motor_expenses, sum(ifnull(f.others_ssm_search,0)) as others_ssm_search, sum(ifnull(f.other_book,0)) as other_book, sum(ifnull(f.other_stationary,0)) as other_stationary, sum(ifnull(f.other_membership,0)) as other_membership, sum(ifnull(f.other_printing,0)) as other_printing, sum(ifnull(f.other_stamping,0)) as other_stamping, sum(ifnull(f.other_license,0)) as other_license, sum(ifnull(f.other_courier,0)) as other_courier, sum(ifnull(f.total_others_approved,0)) as total_others_approved, sum(ifnull(f.outpatient_approved,0)) as outpatient_approved, sum(ifnull(f.mileage_approved,0)) as mileage_approved, sum(ifnull(f.accomodation_claim_approved,0)) as accomodation_claim_approved, sum(ifnull(f.subsistence_outstation,0)) as subsistence_outstation, sum(ifnull(f.subsistence_meals,0)) as subsistence_meals, sum(ifnull(f.transportation_car_rental_approved,0)) as transportation_car_rental_approved, sum(ifnull(f.transportation_flight_approved,0)) as transportation_flight_approved, sum(ifnull(f.transportation_public_transport_approved,0)) as transportation_public_transport_approved, sum(ifnull(f.transportation_e_hailing_approved,0)) as transportation_e_hailing_approved, sum(ifnull(f.transportation_trains_approved,0)) as transportation_trains_approved, sum(ifnull(f.parking_claim_approved,0)) as parking_claim_approved, sum(ifnull(f.toll_claim_approved,0)) as toll_claim_approved, sum(ifnull(f.trainer_claim_approved,0)) as trainer_claim_approved, sum(ifnull(f.panel_claim,0)) as panel_claim, sum(ifnull(f.petrol_claim_approved,0)) as petrol_claim_approved, sum(ifnull(f.entertainment_claim_approved,0)) as entertainment_claim_approved, sum(ifnull(f.entertainment_claim_client,0)) as entertainment_claim_client, sum(ifnull(f.entertainment_claim_staff,0)) as entertainment_claim_staff, sum(ifnull(f.dressing_approved,0)) as dressing_approved, sum(ifnull(f.gadget_approved,0)) as gadget_approved')
                ->join('company__department b','b.id = a.department_id','left')
                ->join('company__branch c','c.id = b.branch_id','left')
                ->join('company__business_entity d','d.id = a.business_entity_id','left')
                ->join('view_report_claim f','f.user_id = a.user_id and f.year = '.$year.' and f.month = '.$month,'left')
                ->join('hr__ot g','g.peopleID = a.user_id and g.year = '.$year.' and g.month = '.$month,'left')
                ->where('a.business_entity_id',$business_entity_id)
                ->where('(f.year = '.$year.' or g.year = '.$year.')')
                ->where('(f.month = '.$month.' or g.month = '.$month.')')
                ->group_by('a.business_entity_id, a.department_id')
                ->order_by('a.department_id')
                ->get('user__employment_detail a')
                ->result_array();

        return (sizeof($records) > 0)?$records:null;
    }
}
?>