<?php

class Model_dashboard  extends CI_Model
{
	public function __construct() {
        parent::__construct();
        $this->load->model('Model_infection'); // Load Model1
    }

	public function get_counts()
	{
		$result = new stdClass();

		// ------------Total HAI count--------------
		$dashboardObj = new Model_infection();
		$haiArray = $dashboardObj->view_patient_with_hai('none', 'none');
		// log_message('debug',  print_r($haiArray, true));

		$result->hai_count = count($haiArray);
		// log_message('debug',  $this->db->last_query());

		// -----------Total mdr count------------

		$mdrArray = $dashboardObj->view_patient_with_mdro('none', 'none');
		// log_message('debug',  print_r($haiArray, true));

		$result->mdr_count = count($mdrArray);


		// -----------Total no of patients--------
		$this->db->select('count(distinct(mr_no)) AS patient_count');
		$this->db->from('ic_surveillance_form sf');

		$result->patient_count = $this->db->get()->result_array()[0]['patient_count'];


		// log_message('debug',  $this->db->last_query());
		return (array)$result;
	}

	public function get_hai_graph($infectionName){
		$year = date('Y');
		$rawQ = "SELECT
					months.month,
					months.month_name,
					w.ward_depart,
					COALESCE(COUNT(sf.form_id), 0) AS count_phlebitis
				FROM (
					SELECT '01' AS MONTH, 'January' AS month_name
					UNION SELECT '02', 'February'
					UNION SELECT '03', 'March'
					UNION SELECT '04', 'April'
					UNION SELECT '05', 'May'
					UNION SELECT '06', 'June'
					UNION SELECT '07', 'July'
					UNION SELECT '08', 'August'
					UNION SELECT '09', 'September'
					UNION SELECT '10', 'October'
					UNION SELECT '11', 'November'
					UNION SELECT '12', 'December'
				) months
				CROSS JOIN ic_ward w
				LEFT JOIN (
					SELECT 
					sf.form_id, 
					sf.ward_no,
					DATE_FORMAT(sf.admission_date, '%m') AS MONTH
					FROM ic_surveillance_form sf
					LEFT JOIN ic_infection_devices id ON id.form_id = sf.form_id
					WHERE DATE(sf.admission_date) <= DATE(DATE_SUB(NOW(), INTERVAL 48 HOUR))
					AND id.`check` = 'yes'
					AND id.`device_name` = '$infectionName'
					AND YEAR(admission_date) = $year
				) sf ON w.ward_id = sf.ward_no AND months.month = sf.month
				GROUP BY months.month, w.ward_depart
				ORDER BY months.month, w.ward_depart;";
		
		$result = $this->db->query($rawQ)->result_array();

		// log_message('debug',  "get_hai_graph" . print_r($rawQ,true));
		return $result;

	} 

	public function get_mdro_graph()
	{
		$this->db->select('w.`ward_depart`, COALESCE(COUNT(cr.`culture_id`),0) AS mdro_count');
		$this->db->from('ic_surveillance_form sf');
		$this->db->join('ic_culture_report cr', 'cr.form_id = sf.form_id', 'inner');
		$this->db->join('ic_ward w', 'w.`ward_id` = sf.`ward_no`', 'right');
		$this->db->group_by('w.`ward_depart`');

		$result = $this->db->get()->result_array();
		log_message('debug',  "get_mdro_graph" . $this->db->last_query());

		return $result;
	}

	public function get_mom_mdr_graph(){
		$year = date('Y');
		$rawQ = "SELECT 
					m.month_name AS MONTH,
					w.ward_depart, 
					COALESCE(COUNT(cr.culture_id), 0) AS mdro_count
				FROM 
					(SELECT '01' AS month_num, 'January' AS month_name
					UNION SELECT '02', 'February'
					UNION SELECT '03', 'March'
					UNION SELECT '04', 'April'
					UNION SELECT '05', 'May'
					UNION SELECT '06', 'June'
					UNION SELECT '07', 'July'
					UNION SELECT '08', 'August'
					UNION SELECT '09', 'September'
					UNION SELECT '10', 'October'
					UNION SELECT '11', 'November'
					UNION SELECT '12', 'December') m
					CROSS JOIN ic_ward w
					LEFT JOIN ic_surveillance_form sf ON w.ward_id = sf.ward_no
					LEFT JOIN ic_culture_report cr ON sf.form_id = cr.form_id 
					AND MONTH(sf.admission_date) = m.month_num
					AND YEAR(sf.`admission_date`) = '$year'
				GROUP BY 
					m.month_num
				ORDER BY 
					m.month_num;
		";
		
		$result = $this->db->query($rawQ)->result_array();

		// log_message('debug',  "get_mom_mdr_graph" . print_r($rawQ,true));
		return $result;

	} 

	public function get_mom_hai_graph(){
		$year = date('Y');
		$rawQ = "SELECT
					months.month,
					months.month_name,
					COALESCE(COUNT(sf.form_id), 0) AS hai_count
				FROM (
					SELECT '01' AS MONTH, 'January' AS month_name
					UNION SELECT '02', 'February'
					UNION SELECT '03', 'March'
					UNION SELECT '04', 'April'
					UNION SELECT '05', 'May'
					UNION SELECT '06', 'June'
					UNION SELECT '07', 'July'
					UNION SELECT '08', 'August'
					UNION SELECT '09', 'September'
					UNION SELECT '10', 'October'
					UNION SELECT '11', 'November'
					UNION SELECT '12', 'December'
				) months
				CROSS JOIN ic_ward w
				LEFT JOIN (
					SELECT 
					sf.form_id, 
					sf.ward_no,
					DATE_FORMAT(sf.admission_date, '%m') AS MONTH
					FROM ic_surveillance_form sf
					LEFT JOIN ic_infection_devices id ON id.form_id = sf.form_id
					WHERE DATE(sf.admission_date) <= DATE(DATE_SUB(NOW(), INTERVAL 48 HOUR))
					AND id.`check` = 'yes'
					AND YEAR(admission_date) = $year
				) sf ON w.ward_id = sf.ward_no AND months.month = sf.month
				GROUP BY months.month
				ORDER BY months.month;";
		
		$result = $this->db->query($rawQ)->result_array();

		return $result;

	} 
}
