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

class Mwillingstatus extends CI_Model{

    function get_all_status($appNumber) {
        $this->db->where('app_number', $appNumber);
        $sql = $this->db->get('user_fc_status');
        if($sql->num_rows()>0){
            return $sql->result_array();
        }
    }


    function create_status($appNumber) {
        $status = [
            'status'        =>   $this->input->post('pmu_approve_status'),
            'remarks'       =>   $this->input->post('remarks'),
            'app_number'    =>   $appNumber,
            'created_by'    =>   $this->session->user_id
        ];

        $this->db->insert('user_fc_status', $status);
        if($this->db->affected_rows() > 0) {
            $this->session->set_flashdata('success','Successfully updated status');
        }
    }

    function get_app_statuscount() {
       $query ="SELECT pmu_approve_status, COUNT(*) as total FROM applications GROUP BY pmu_approve_status";
       $sql = $this->db->query($query);
      return $sql->result_array();
    }

    function get_final_appl_status($status = NULL) {
        return $this->db->where('status', $status)->get('application_statuses')->num_rows();
     }

    function statewise_count_bee() { 
        $query ="SELECT company_state, COUNT(*) as total FROM applications GROUP BY company_state ";
        $sql = $this->db->query($query);
        return $sql->result_array();
    }


    function statewise_count() { 
        $query ="SELECT company_state, COUNT(*) as total FROM applications WHERE applications.pmu_approve_status = 'approved' GROUP BY company_state ";
        $sql = $this->db->query($query);
        return $sql->result_array();
    }

    function statewise_with_projectcost() { 
        $query ="SELECT company_state, COUNT(*) as total FROM applications GROUP BY company_state ";
        $sql = $this->db->query($query);
        return $sql->result_array();
    }


    function statewise_wiseprojectcost() { 
        $query ="SELECT state_id, project_cost FROM projects";
        $sql = $this->db->query($query);
        return $sql->result_array();
    }

    function statewise_sanction_count() {
        $query ="SELECT company_state, COUNT(*) as total FROM applications GROUP BY company_state";
        $sql = $this->db->query($query);
        return $sql->result_array();
    }

    function get_state_name() {
        $this->db->distinct('state_code');
        $this->db->select('state_code,state_name');
        $this->db->order_by('state_name', 'ASC');
        $sql = $this->db->get('states');
        if($sql->num_rows()>0){
            return $sql->result_array();
        }
    }


    // Districtwise count

    function district_wise_count($state_code) { 
        $query ="SELECT company_dist, COUNT(*) as total FROM applications  WHERE company_state = $state_code  GROUP BY company_dist";
        $sql = $this->db->query($query);
        return $sql->result_array();
    }

    function district_sanction_count($state_code) {
        $query ="SELECT company_dist, COUNT(*) as total FROM applications WHERE company_state = $state_code GROUP BY company_dist";
        $sql = $this->db->query($query);
        return $sql->result_array();
    }

    function get_district_name($state_code) {
        $this->db->distinct('company_dist');
        $this->db->select('state_code, state_name, district_name, district_code');
        $this->db->order_by('district_name', 'ASC');
        $this->db->where('state_code', $state_code);
        $sql = $this->db->get('states');
        if($sql->num_rows()>0){
            return $sql->result_array();
        }
    }


    function get_total_application() {
      return  $this->db->get('applications')->num_rows();
    }
    

    function get_pi_statewise() {
        $sql = "SELECT DISTINCT applications.company_state, projects.project_cost FROM `applications_projects` 
        JOIN projects ON applications_projects.project_id = projects.id 
        JOIN applications ON applications_projects.application_num = applications.application_num";
       $query = $this->db->query($sql);
        if ( $query->num_rows() > 0) {
            return $query->result_array();
        }
    }

}