public function get_mos_of_commodity_in_county($county, $period, $commodity_id) { $period1 = subtract_date($period, 1); $period2 = subtract_date($period, 2); $period3 = subtract_date($period, 3); $period4 = subtract_date($period, 4); $period5 = subtract_date($period, 5); $sql = "SELECT\n drug_id AS com_id,\n drug_value AS physical_count,\n county_id as coun_id,\n\n (SELECT\n mapping_name\n FROM lab_mapping_drugs_category\n\n WHERE mapping_id = com_id) AS commodity_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,\n\n (\n (\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period}' and county_id=coun_id))*100\n )\n\n )/1\n )\n AS adjusted_county_amc\n\n\nFROM lab_county_level_data\nWHERE period = '{$period}' and drug_category_id='ZxUQw1ay1cw' and county_id='{$county}' and drug_id='{$commodity_id}'"; if (county_level_period_exists($period1) == true) { $sql = "SELECT\n drug_id AS com_id,\n drug_value AS physical_count,\n county_id as coun_id,\n\n (SELECT\n mapping_name\n FROM lab_mapping_drugs_category\n\n WHERE mapping_id = com_id) AS commodity_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,\n\n (\n (\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period1}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period1}' and county_id=coun_id))*100\n )\n\n\n )/2\n )\n AS adjusted_county_amc\n\n\nFROM lab_county_level_data\nWHERE period = '{$period}' and drug_category_id='ZxUQw1ay1cw' and county_id='{$county}' and drug_id='{$commodity_id}' "; } if (county_level_period_exists($period2) == true) { $sql = "SELECT\n drug_id AS com_id,\n drug_value AS physical_count,\n county_id as coun_id,\n\n (SELECT\n mapping_name\n FROM lab_mapping_drugs_category\n\n WHERE mapping_id = com_id) AS commodity_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,\n\n (\n (\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period1}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period1}' and county_id=coun_id))*100\n )\n\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period2}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period2}' and county_id=coun_id))*100\n )\n\n )/3\n )\n AS adjusted_county_amc\n\n\nFROM lab_county_level_data\nWHERE period = '{$period}' and drug_category_id='ZxUQw1ay1cw' and county_id='{$county}' and drug_id='{$commodity_id}' "; } if (county_level_period_exists($period3) == true) { $sql = "SELECT\n drug_id AS com_id,\n drug_value AS physical_count,\n county_id as coun_id,\n\n (SELECT\n mapping_name\n FROM lab_mapping_drugs_category\n\n WHERE mapping_id = com_id) AS commodity_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,(SELECT alt_name FROM malaria_commodities WHERE commodity_id = com_id) AS alt_name,\n\n (\n (\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period1}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period1}' and county_id=coun_id))*100\n )\n\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period2}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period2}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period3}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period3}' and county_id=coun_id))*100\n )\n\n )/4\n )\n AS adjusted_county_amc\n\n\nFROM lab_county_level_data\nWHERE period = '{$period}' and drug_category_id='ZxUQw1ay1cw' and county_id='{$county}' and drug_id='{$commodity_id}' "; } if (county_level_period_exists($period4) == true) { $sql = "SELECT\n drug_id AS com_id,\n drug_value AS physical_count,\n county_id as coun_id,\n\n (SELECT\n mapping_name\n FROM lab_mapping_drugs_category\n WHERE mapping_id = com_id) AS commodity_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,\n (\n (\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period1}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period1}' and county_id=coun_id))*100\n )\n\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period2}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period2}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period3}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period3}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period4}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period4}' and county_id=coun_id))*100\n )\n\n )/5\n )\n AS adjusted_county_amc\n\n\nFROM lab_county_level_data\nWHERE period = '{$period}' and drug_category_id='ZxUQw1ay1cw' and county_id='{$county}' and drug_id='{$commodity_id}' "; } if (county_level_period_exists($period5) == true) { $sql = "SELECT\n drug_id AS com_id,\n drug_value AS physical_count,\n county_id as coun_id,\n\n (SELECT\n mapping_name\n FROM lab_mapping_drugs_category\n WHERE mapping_id = com_id) AS commodity_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,(SELECT alt_name FROM lab_commodities WHERE commodity_id = com_id) AS alt_name,\n (\n (\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period1}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period1}' and county_id=coun_id))*100\n )\n\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period2}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period2}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period3}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period3}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period = '{$period4}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period4}' and county_id=coun_id))*100\n )\n +\n (\n ((SELECT\n drug_value\n FROM lab_county_level_data\n WHERE drug_category_id = 'EJ8nUfcupBq'\n AND drug_id = com_id\n AND period ='{$period5}' and county_id=coun_id) / (SELECT\n lab_county_level_reporting_rates.reporting_rate_value\n FROM lab_county_level_reporting_rates\n WHERE period = '{$period5}' and county_id=coun_id))*100\n )\n )/6\n )\n AS adjusted_county_amc\n\n\nFROM lab_county_level_data\nWHERE period = '{$period}' and drug_category_id='ZxUQw1ay1cw' and county_id='{$county}' and drug_id='{$commodity_id}' "; } $result = $this->db->query($sql); $result = $result->result(); if (!empty($result[0]->physical_count) && !empty($result[0]->adjusted_county_amc)) { $mos = $result[0]->physical_count / $result[0]->adjusted_county_amc; } else { $mos = null; } return $mos; }
?> </th> <th style="text-align:right"> <?php $month = subtract_date($p, 2); $date = DateTime::createFromFormat('Ym', $month); $monthName = $date->format('MY'); // will get Month name echo $monthName; ?> </th> <th style="text-align:right"> <?php $month = subtract_date($p, 1); $date = DateTime::createFromFormat('Ym', $month); $monthName = $date->format('MY'); // will get Month name echo $monthName; ?> </th> <th style="text-align:right"> <?php $month = $p; $date = DateTime::createFromFormat('Ym', $month); $monthName = $date->format('MY'); // will get Month name echo $monthName;