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

class Claim_dashboard extends Action_controller{	
    public function __construct(){
        parent::__construct('claim_dashboard');
    }
    
    // LOAD PAGE
    public function index(){
        if(!$this->Appconfig->isAppvalid()){
            redirect('config');
        }
        
        $data['title']      = 'Claim Dashboard';
        $data['page_title'] = 'Claims & Reimbursement Dashboard';
        
        // Get branch list
        $branch_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_branch` where trans_status = 1')");
        $branch_result = $branch_info->result();
        $branch_info->next_result();
        $branch_list[""] = "-- All --";
        foreach($branch_result as $for){
            $branch_id               = $for->prime_branch_id;
            $branch_name             = $for->branch;
            $branch_list[$branch_id] = $branch_name;
        }
        $data['branch_list'] = $branch_list;
        
        $this->load->view("$this->control_name/manage",$data);
    }
    
    // GET CLAIM MASTER COUNT
    public function get_claim_master_count(){
        $from_date  = date("Y-m-01");
        $to_date    = date('Y-m-t');        
        $claim_master_qry = 'SELECT SUM(CASE WHEN trans_status = 1 THEN 1 ELSE 0 END) AS total_claims,SUM(CASE WHEN approval_status = 1 THEN 1 ELSE 0 END) AS pending_claims,SUM(CASE WHEN approval_status = 2 THEN 1 ELSE 0 END) AS approved_claims,SUM(CASE WHEN approval_status = 3 THEN 1 ELSE 0 END) AS rejected_claims,SUM(CASE WHEN trans_status = 1 THEN amount_claimed ELSE 0 END) AS total_amount_claimed,SUM(CASE WHEN approval_status = 2 THEN approved_amount ELSE 0 END) AS total_amount_approved,COUNT(DISTINCT CASE WHEN category != "" AND trans_status = 1 THEN category END) AS total_categories,COUNT(DISTINCT CASE WHEN employee_code != "" AND trans_status = 1 THEN employee_code END) AS total_employees FROM cw_claim_approval WHERE DATE(trans_created_date) BETWEEN "'.$from_date.'" AND "'.$to_date.'"';        
        $claim_master_info = $this->db->query("CALL sp_a_run ('SELECT','$claim_master_qry')");
        $claim_master_rslt = $claim_master_info->result_array();
        $claim_master_info->next_result();
        
        echo json_encode(array("success" => TRUE, 'table_data' => $claim_master_rslt[0]));
    }
    
    // GET CLAIM CATEGORY DATA
    public function get_claim_category_data(){
        $branch = $this->input->post('branch');
        
        $branch_condition = '';
        if($branch){
            $branch_condition = ' AND cw_employees.branch = "'.$branch.'"';
        }
        
        $category_qry   = 'SELECT  c.category_name,SUM(CASE WHEN approval_status = 1 THEN 1 ELSE 0 END) AS pending_count,SUM(CASE WHEN approval_status = 2 THEN 1 ELSE 0 END) AS approved_count,SUM(CASE WHEN approval_status = 3 THEN 1 ELSE 0 END) AS rejected_count,SUM(amount_claimed) AS total_claimed,SUM(approved_amount) AS total_approved FROM cw_claim_approval INNER JOIN cw_employees ON cw_employees.employee_code = cw_claim_approval.employee_code INNER JOIN cw_category c ON c.prime_category_id = cw_claim_approval.category WHERE cw_claim_approval.trans_status = 1 AND cw_claim_approval.trans_created_date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()'.$branch_condition.'GROUP BY c.prime_category_id ,c.category_name HAVING (pending_count >= 1 OR approved_count >= 1 OR rejected_count >= 1)';    

        $category_info = $this->db->query("CALL sp_a_run ('SELECT','$category_qry')");
        $category_rslt = $category_info->result_array();
        $category_info->next_result();
        
        echo json_encode(array("success" => TRUE, 'table_data' => $category_rslt));
    }
    
    // GET CLAIM STATUS CHART DATA
    public function get_claim_status_chart_data(){
        $start_date = $this->input->post('start_date');
        $end_date   = $this->input->post('end_date');
        $branch     = $this->input->post('branch');

        $date_condition = '';
        if(!empty($start_date) && !empty($end_date)){
            $start_date     = date("Y-m-d", strtotime($start_date));
            $end_date       = date("Y-m-d", strtotime($end_date));
            $date_condition = "AND DATE(cw_claim_approval.trans_created_date)BETWEEN '$start_date' AND '$end_date'";
        }

        $branch_condition = '';
        if(!empty($branch) && $branch != 0 && strtolower($branch) != 'all'){
            $branch_condition = " AND cw_employees.branch = '$branch' ";
        }

        $status_qry = "SELECT cw_claim_approval.approval_status,COUNT(*) AS count FROM cw_claim_approval
        JOIN cw_employees ON cw_employees.employee_code = cw_claim_approval.employee_code WHERE cw_claim_approval.trans_status = 1 $date_condition $branch_condition GROUP BY cw_claim_approval.approval_status";

        $status_qry = str_replace("'", "''", $status_qry);

        $status_info = $this->db->query("CALL sp_a_run ('SELECT','$status_qry')");
        $status_rslt = $status_info->result_array();
        $status_info->next_result();

        echo json_encode(["success" => true, "table_data" => $status_rslt]);
    }
 
    // GET MONTHLY APPROVAL TREND
    public function get_monthly_approval_trend(){
        $start_date         = $this->input->post('start_date');
        $end_date           = $this->input->post('end_date');
        $branch             = $this->input->post('branch');
        if(!empty($start_date) && !empty($end_date)){
            $start_date     = date("Y-m-d", strtotime($start_date));
            $end_date       = date("Y-m-d", strtotime($end_date));
        }

        $branch_condition = '';
        if($branch){
            $branch_condition = ' AND cw_employees.branch = '.$branch.' ';
        }
        
        $trend_qry = 'SELECT cw_claim_approval.approval_status ,COUNT(*) as status_count FROM cw_claim_approval INNER JOIN cw_employees ON cw_employees.employee_code = cw_claim_approval.employee_code WHERE cw_claim_approval.trans_status = 1 
        AND cw_claim_approval.trans_created_date between "'.$start_date.'" and "'.$end_date.'"'.$branch_condition.'
        GROUP BY cw_claim_approval.approval_status';
            
        $trend_info = $this->db->query("CALL sp_a_run ('SELECT','$trend_qry')");
        $trend_rslt = $trend_info->result_array();
        $trend_info->next_result();
        
        echo json_encode(array("success" => TRUE, 'table_data' => $trend_rslt));
    }
    
    // GET TEAM DATA
    public function get_team_data(){
        $start_date         = $this->input->post('start_date');
        $end_date           = $this->input->post('end_date');

        $date_condition     = '';
        if(!empty($start_date) && !empty($end_date)){
            $start_date     = date("Y-m-d", strtotime($start_date));
            $end_date       = date("Y-m-d", strtotime($end_date));
            $date_condition = "AND DATE(cw_claim_approval.trans_created_date) BETWEEN '$start_date' AND '$end_date'";
        }

       
        $team_qry = "SELECT cw_department.department AS team,COUNT(CASE WHEN cw_claim_approval.approval_status = 2 THEN 1 
        END) AS count FROM cw_department LEFT JOIN cw_employees ON cw_employees.department = cw_department.prime_department_id
        $branch_condition LEFT JOIN cw_claim_approval ON cw_claim_approval.employee_code = cw_employees.employee_code
        AND cw_claim_approval.trans_status = 1 $date_condition GROUP BY cw_department.department";

        $team_qry  = str_replace("'", "''", $team_qry);
        $team_info = $this->db->query("CALL sp_a_run ('SELECT','$team_qry')");
        $team_rslt = $team_info->result_array();
        $team_info->next_result();

        echo json_encode(["success" => true, "table_data" => $team_rslt]);
    }

}