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

class Mconsole extends CI_Model{

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



  function get_projects() {
     $page = $this->input->get('pageno');
     $limit = $this->input->get('limit');

     $page = !empty($page) && is_int($page) ? $page : 1;
     $limit = !empty($limit) && is_init($limit) ? $limit: 15;

     $query = $this->db->select('projects.*, applications.*, states.*')
              ->distinct()
              ->from('projects')
              ->join('applications_projects', 'applications_projects.project_id = projects.id')
              ->join('applications', 'applications.application_num = applications_projects.application_num')
              ->join('states','states.district_code = projects.district_id')
              ->get();

              if ( $query->num_rows() > 0) {
                return $query->result_array();
             }
  }



  function get_state_wise_projects($state_code) {
    $page = $this->input->get('pageno');
    $limit = $this->input->get('limit');

    $page = !empty($page) && is_int($page) ? $page : 1;
    $limit = !empty($limit) && is_init($limit) ? $limit: 15;

    $query = $this->db->select('projects.*, applications.*, states.*')
             ->distinct()
             ->from('projects')
             ->join('applications_projects', 'applications_projects.project_id = projects.id')
             ->join('applications', 'applications.application_num = applications_projects.application_num')
             ->join('states','states.district_code = projects.district_id')
             ->where('states.state_code ='.$state_code)
             ->get();

             if ( $query->num_rows() > 0) {
               return $query->result_array();
            }
 }


  function get_status_count_fi_data($type) {
        $query = "SELECT bank_code, COUNT(*) as total FROM bank_applications WHERE status = $type GROUP BY bank_code";
        $sql = $this->db->query($query);
        if ( $sql->num_rows() > 0) {
            return $sql->result_array();
        }
  }

  function get_sanction_detail($state_code = NULL) {
    $query = "";
    if(empty($state_code)) {
      $query = "SELECT bank_code, SUM(loan_sanctioned) AS sanctioned, COUNT(*) as total, sum(loan_requested) AS loan FROM bank_applications
      WHERE status = 'sanctioned' OR status = 'disbursed'  GROUP BY bank_code";
    } else {
      $query = "SELECT ba.bank_code, SUM(ba.loan_sanctioned) AS sanctioned, COUNT(*) as total, sum(ba.loan_requested) AS loan FROM bank_applications AS ba JOIN applications AS app
     ON app.application_num = ba.application_id WHERE ba.status = 'sanctioned' AND app.company_state = $state_code GROUP BY bank_code";
    }
    $sql = $this->db->query($query);
    if ( $sql->num_rows() > 0) {
        return $sql->result_array();
    }
   }


   function get_disbursed_detail($state_code = NULL) {
    if(empty($state_code)) {
      $query = "SELECT financed_bank, SUM(loan_disbursed) AS loan_disbursed, COUNT(*) as total FROM applications WHERE status = 'disbursed' GROUP BY financed_bank";
    } else {
      $query = "SELECT financed_bank, SUM(loan_disbursed) AS loan_disbursed, COUNT(*) as total FROM applications WHERE status = 'disbursed' AND  company_state = $state_code GROUP BY financed_bank";
    }
    $sql = $this->db->query($query);
    if ( $sql->num_rows() > 0) {
        return $sql->result_array();
    }
   }


    function get_financed() {
      $query = "SELECT bank_code, SUM(loan_approved) AS sanctioned, COUNT(*) as total, sum(loan_requested) AS loan FROM bank_financed GROUP BY bank_code";
      $sql = $this->db->query($query);
      if ( $sql->num_rows() > 0) {
          return $sql->result_array();
      }
    }


    function get_total_financed() {
      $query = "SELECT COUNT(*) as total FROM bank_financed";
      $sql = $this->db->query($query);
      if ( $sql->num_rows() > 0) {
          return $sql->row_array();
      }
    }



    function get_sanction_statewise() {
    $query = "SELECT app.company_state, SUM(ba.loan_sanctioned) AS sanctioned, COUNT(*) as total, sum(ba.loan_requested) AS loan FROM bank_applications AS ba 
      JOIN applications AS app ON app.application_num = ba.application_id WHERE ba.status = 'sanctioned' OR ba.status = 'disbursed'  GROUP BY app.company_state";
      $sql = $this->db->query($query);
      if ( $sql->num_rows() > 0) {
          return $sql->result_array();
      }
   }


   function get_disbursed_statewise() {
    $query = "SELECT company_state, SUM(loan_disbursed) AS loan_disbursed, COUNT(*) as total FROM applications WHERE status = 'disbursed' GROUP BY company_state";
    $sql = $this->db->query($query);
    if ( $sql->num_rows() > 0) {
        return $sql->result_array();
    }
   }



   // Districtwise Financed and Disbursed loan detail

   
   function get_sanction_districtwise($state_code) {
    $query = "SELECT app.company_state, app.company_dist, SUM(ba.loan_sanctioned) AS sanctioned, COUNT(*) as total, sum(app.loan_requested) AS loan FROM bank_applications AS ba 
      JOIN applications AS app ON app.application_num = ba.application_id WHERE ba.status = 'sanctioned' OR ba.status = 'disbursed'  AND app.company_state = $state_code GROUP BY app.company_dist";
      $sql = $this->db->query($query);
      if ( $sql->num_rows() > 0) {
          $data = $sql->result_array();

          $sanction = [];
          if(!empty($data)) {
              foreach($data as $res => $val) {
                 if(!empty($val['company_dist'])) {
                  $keyname = trim($val['company_dist']);
                  $sanction[$keyname]['num'] = $val['total'];
                  $sanction[$keyname]['amount'] = $val['sanctioned'];
                 }
              }
          }
          return $sanction;
      }
   }


   function get_disbursed_districtwise($state_code) {
    $query = "SELECT company_state, company_dist, SUM(loan_disbursed) AS loan_disbursed, COUNT(*) as total FROM applications WHERE status = 'disbursed' AND company_state = $state_code GROUP BY company_dist";
    $sql = $this->db->query($query);
    if ( $sql->num_rows() > 0) {
        $data1 = $sql->result_array();

          $disbursed = [];
          if(!empty($data1)) {
              foreach($data1 as $res => $val) {
                  if(!empty($val['company_dist'])) {
                      $keyname = $val['company_dist'];
                      $disbursed[$keyname]['num'] = $val['total'];
                      $disbursed[$keyname]['amount'] = $val['loan_disbursed'];
                  }
              }
          }
          return $disbursed;
    }
   }


   function get_district_name($code) {
        $this->db->where('district_code', $code);
        $sql = $this->db->get('states');
        if ( $sql->num_rows() > 0) {
            return $sql->row_array();
        }
     }


}