public function district_fcdrr($district, $year, $month, $commodity_id = null)
{
// looking for orders that are in a district within a particular month of a particular year
$distname = districts::get_district_name($district);
$districtname = $distname[0]['district'];
$district_id = $district;
$returnable = array();
$returnable['district_name'] = $districtname;
$returnable['district_id'] = $district;
$returnable['commodity_issued_total'] = 0;
$returnable['commodity_name'] = 0;
$returnable['beginning_bal'] = 0;
$returnable['q_received'] = 0;
$returnable['q_used'] = 0;
$returnable['no_of_tests_done'] = 0;
$returnable['losses'] = 0;
$returnable['positive_adj'] = 0;
$returnable['negative_adj'] = 0;
$returnable['closing_stock'] = 0;
$returnable['q_expiring'] = 0;
$returnable['days_out_of_stock'] = 0;
$returnable['q_requested'] = 0;
$returnable['allocated'] = 0;
$returnable['district_orders'] = array();
// Sets the timezone and date variables for last day of previous month and this month
date_default_timezone_set('EUROPE/moscow');
$month = $month + 1;
$prev_month = $month - 1;
$last_day_current_month = date('Y-m-d', mktime(0, 0, 0, $month, 0, $year));
$last_day_previous_month = date('Y-m-d', mktime(0, 0, 0, $prev_month, 0, $year));
$lastday_thismonth = date('Y-m-d', strtotime("last day of this month"));
$month -= 1;
$q = "SELECT DISTINCT lab_commodity_orders.facility_code, lab_commodity_orders.id as order_id,lab_commodity_orders.order_date\n FROM lab_commodity_orders, districts, counties\n WHERE districts.id = lab_commodity_orders.district_id\n AND districts.county = counties.id\n AND districts.id = {$district}\n AND lab_commodity_orders.order_date\n BETWEEN '{$last_day_previous_month}'\n AND '{$last_day_current_month}'";
$q_res1 = $this->db->query($q);
$total_reported_facilities = $q_res1->num_rows();
foreach ($q_res1->result_array() as $vals) {
$order_id = $vals['order_id'];
$order_vals = $this->fcdrr_values($order_id, $commodity_id);
$vals['order_details'] = $order_vals;
$returnable['commodity_name'] = $vals['order_details'][0]['commodity_name'];
$returnable['beginning_bal'] += $vals['order_details'][0]['beginning_bal'];
$returnable['q_received'] += $vals['order_details'][0]['q_received'];
$returnable['q_used'] += $vals['order_details'][0]['q_used'];
$returnable['no_of_tests_done'] += $vals['order_details'][0]['no_of_tests_done'];
$returnable['losses'] += $vals['order_details'][0]['losses'];
$returnable['positive_adj'] += $vals['order_details'][0]['positive_adj'];
$returnable['negative_adj'] += $vals['order_details'][0]['negative_adj'];
$returnable['closing_stock'] += $vals['order_details'][0]['closing_stock'];
$returnable['q_expiring'] += $vals['order_details'][0]['q_expiring'];
$returnable['days_out_of_stock'] += $vals['order_details'][0]['days_out_of_stock'];
$returnable['q_requested'] += $vals['order_details'][0]['q_requested'];
$returnable['allocated'] += $vals['order_details'][0]['allocated'];
array_push($returnable['district_orders'], $vals);
}
return $returnable;
}
public function consumption_stats_graph($commodity_id = null, $category_id = null, $district_id = null, $facility_code = null, $option = null, $from = null, $to = null, $report_type = null, $tracer = null)
{
//reset the values here
// echo $report_type;exit;
$commodity_id = $commodity_id == "NULL" ? null : $commodity_id;
$district_id = isset($district_id) && $district_id ? $district_id : $this->session->userdata('district_id');
//$district_id = ($district_id=="NULL") ? null :$district_id;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$option = $option == "NULL" || $option == "null" ? null : $option;
// $from = ($from == "NULL") ? strtotime(date('01-m-y')) : strtotime(urldecode($from));
// $to = ($to == "NULL") ? strtotime(date('d-m-y')) : strtotime(urldecode($to));
// echo "$from";die;
$from = $from == "NULL" ? strtotime(date('Y-m-01')) : strtotime(urldecode($from));
$to = $to == "NULL" ? strtotime(date('Y-m-d')) : strtotime(urldecode($to));
$category_id = $category_id == "NULL" ? null : $category_id;
$county_id = $this->session->userdata('county_id');
$county_name = counties::get_county_name($county_id);
$category_data = $series_data = $graph_data = $series_data_ = array();
//check if the district is set
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " subcounty" : null;
$option_new = isset($option) ? $option : "packs";
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code)->toArray() : null;
$facility_name = $facility_code_[0]['facility_name'];
$commodity_name = isset($commodity_id) ? Commodities::get_details($commodity_id)->toArray() : null;
$category_name_ = @$commodity_name[0]['commodity_name'];
$commodity_name = isset($category_name_) ? " for " . $category_name_ : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : ($district_id > 0 && !isset($facility_code) ? "{$district_name_}" : "{$county_name['county']} county");
$time = "between " . date('j M y', $from) . " and " . date('j M y', $to);
$consumption_data = Facility_stocks::get_county_consumption_level_new($facility_code, $district_id, $county_id, $category_id, $commodity_id, $option_new, $from, $to, $report_type, $tracer);
// echo "<pre>";
// print_r($consumption_data);die;
foreach ($consumption_data as $data) {
if ($report_type == "table_data") {
if ($commodity_id > 0) {
array_push($series_data, array($data['district'], $data["facility_name"], $data["facility_code"], $data['commodity'], (int) $data['total']));
} else {
array_push($series_data, array($data["name"], $data['commodity'], (int) $data['total']));
}
} else {
$series_data = array_merge($series_data, array((int) $data['total']));
$series_data_ = array_merge($series_data_, array(array($data["district"], $data["facility_name"], $data['commodity_name'], (int) $data['total'])));
$category_data = array_merge($category_data, array($data["name"]));
}
//
}
if ($report_type == "csv_data") {
//seth
$excel_data = array('doc_creator' => $this->session->userdata('full_name'), 'doc_title' => "Consumption level {$commodity_name} {$title} {$time}", 'file_name' => "consumption_level_{$commodity_name_}{$title_}{$time}");
$row_data = array();
$column_data = array("District", "Facility", "Commodity", "Consumption Level in {$option_new}");
$excel_data['column_data'] = $column_data;
$row_data = array_merge($row_data, $series_data_);
//echo "<pre>";print_r($row_data);echo "</pre>";exit;
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
//echo $commodity_name.$title.$time;exit;
} elseif ($report_type == "table_data") {
if ($commodity_id > 0) {
$category_data = array(array("Sub-county", "Facility Name", "Mfl", "Commodity Name", "TOTAL " . $option_new));
} else {
// array_push($category_data, array("Commodity Name", "stocks worth in $option_new"));
array_push($category_data, array("Consumption level {$commodity_name} {$title} {$time}", "Commodity Name", "stocks worth in {$option_new}"));
}
$graph_data = array_merge($graph_data, array("table_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("table_header" => $category_data));
$graph_data = array_merge($graph_data, array("table_body" => $series_data));
$data['table'] = $this->hcmp_functions->create_data_table($graph_data);
$data['table_id'] = "dem_graph_";
return $this->load->view("shared_files/report_templates/data_table_template_v", $data);
} else {
$graph_type = 'bar';
// $graph_type = 'column';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("graph_title" => "Consumption level {$commodity_name} {$title} {$time}"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Commodity Consumption level in {$option_new}"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('total' => $series_data)));
$data = array();
$data['graph_id'] = 'dem_graph_';
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
return $this->load->view("shared_files/report_templates/high_charts_template_v", $data);
}
}
public function tracer_item_stock_level($county_id = null, $district_id = null, $facility_code = null, $commodity_id = null, $graph_type = null)
{
$district_id = $district_id == "NULL" ? null : $district_id;
$graph_type = $graph_type == "NULL" ? null : $graph_type;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
$commodity_id = $commodity_id == "ALL" || $commodity_id == "NULL" ? null : $commodity_id;
// echo $commodity_id;die;
$and_data = $district_id > 0 ? " AND d.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND ct.id='{$county_id}'" : null;
// $and_data .= ($county_id > 0) ? " AND counties.id='$county_id'" : null;
$and_data = isset($and_data) ? $and_data : null;
$from_others = null;
$from_others .= $district_id > 0 ? "districts d," : null;
$from_others .= $county_id > 0 ? "counties ct," : null;
if ($graph_type == 'excel') {
$and_data .= isset($commodity_id) ? "AND d.id ={$commodity_id}" : "AND d.tracer_item =1";
// $and_data .= isset($commodity_id) ? "AND commodities.id =$commodity_id" : "AND d.tracer_item =1";
} else {
$and_data .= isset($commodity_id) ? "AND commodities.id ={$commodity_id}" : "AND commodities.tracer_item =1";
}
$group_by = $district_id > 0 && isset($county_id) && !isset($facility_code) ? " ,d.id" : null;
$group_by .= $facility_code > 0 && isset($district_id) ? " ,f.facility_code" : null;
$group_by .= $county_id > 0 && !isset($district_id) ? " ,c_.id" : null;
$group_by = isset($group_by) ? $group_by : " ,c_.id";
$title = '';
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name['county'];
$title = "{$name} County";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "National";
}
if ($graph_type != "excel") {
$commodity_array;
$getdates = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT MIN(created_at) as EarliestDate,MAX(created_at) as LatestDate\n\t\t\tFROM facility_issues");
//echo '<pre>'; print_r($getdates);echo '<pre>'; exit;
$early = $getdates[0]['EarliestDate'];
$late = $getdates[0]['LatestDate'];
$now = time();
$my_date = strtotime($early);
$datediff = ($now - $my_date) / (60 * 60 * 24);
//in days
$datediff = round($datediff, 1);
if (isset($county_id)) {
$getdates = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT MIN(created_at) as EarliestDate,MAX(created_at) as LatestDate\n\t\t\t\t\tFROM facility_issues \n\t\t\t\t\tinner join facilities on facility_issues.facility_code=facilities.facility_code\n\t\t\t\t\tinner join districts on facilities.district=districts.id\n\t\t\t\t\tinner join counties on districts.county=counties.id\n\t\t\t\t\tinner join commodities on facility_issues.commodity_id=commodities.id where counties.id={$county_id}");
//echo '<pre>'; print_r($getdates);echo '<pre>'; exit;
$early = $getdates[0]['EarliestDate'];
$late = $getdates[0]['LatestDate'];
$now = time();
$my_date = strtotime($early);
$datediff = ($now - $my_date) / (60 * 60 * 24);
//in days
$datediff = round($datediff, 1);
$get_amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT commodities.id,commodities.commodity_name,avg(facility_issues.qty_issued) as total_units_consumed,\n\t\t\t\t(sum(facility_issues.qty_issued)*30/{$datediff})/commodities.total_commodity_units as amc_packs,commodities.total_commodity_units FROM hcmp_rtk.facility_issues \n\t\t\t\tinner join facilities on facility_issues.facility_code=facilities.facility_code\n\t\t\t\tinner join districts on facilities.district=districts.id\n\t\t\t\tinner join counties on districts.county=counties.id inner join commodities on facility_issues.commodity_id=commodities.id where s11_No IN('internal issue','(-ve Adj) Stock Deduction')\n\t\t\t\t{$and_data} group by commodities.id");
$get_amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll($new_sql_amc);
//return $get_amc ;
$get_totals = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT commodities.id,commodities.commodity_name,sum(facility_stocks.current_balance) \n\t\t\t\tas total_bal_units, sum(facility_stocks.current_balance)/commodities.total_commodity_units as cur_bal_packs,commodities.total_commodity_units FROM hcmp_rtk.facility_stocks \n\t\t\t\tinner join facilities on facility_stocks.facility_code=facilities.facility_code\n\t\t\t\tinner join districts on facilities.district=districts.id\n\t\t\t\tinner join counties on districts.county=counties.id inner join commodities on facility_stocks.commodity_id=commodities.id \n\t\t\t\twhere commodities.status=1 {$and_data} group by commodities.id");
} else {
$new_sql_amc = "SELECT commodities.id,commodities.commodity_name,CEIL(AVG(facility_issues.qty_issued)) AS total_units_consumed, \n\t\t\t\tCEIL((SUM(facility_issues.qty_issued) / 3)) AS amc_units,\n\t\t\t\tCEIL((SUM(facility_issues.qty_issued) / 3) / commodities.total_commodity_units) AS amc_packs,commodities.total_commodity_units\n\t\t\t\tFROM facility_issues {$from_others} INNER JOIN commodities ON facility_issues.commodity_id = commodities.id \n\t\t\t\tWHERE s11_No IN ('internal issue' , '(-ve Adj) Stock Deduction') {$and_data} \n\t\t\t\tAND facility_issues.expiry_date > '2016-04-21' \n\t\t\t\tAND facility_issues.date_issued between '2016-02-31' and '2016-04-31'\n\t\t\t\tGROUP BY commodities.id order by commodities.id asc";
// echo $new_sql_amc;die;
$get_amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll($new_sql_amc);
//return $get_amc ;
echo $and_data;
exit;
$new_sql_totals = "SELECT commodities.id,commodities.commodity_name,CEIL(sum(facility_stocks.current_balance))\n\t\t\t\tas total_bal_units, CEIL(sum(facility_stocks.current_balance)/commodities.total_commodity_units) as cur_bal_packs,commodities.total_commodity_units FROM facility_stocks {$from_others} inner join commodities on facility_stocks.commodity_id=commodities.id \n\t\t\t\twhere commodities.status=1 {$and_data} AND facility_stocks.expiry_date > '2016-04-21' group by commodities.id order by commodities.id asc";
$get_totals = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll($new_sql_totals);
}
//return $get_totals ;
$combine = array();
for ($i = 0; $i < sizeof($get_totals); $i++) {
//array_push($combine,$get_totals[$i ],$get_amc[$i ]);
$combine[] = array_merge($get_totals[$i], $get_amc[$i]);
}
// echo '<pre>'; print_r($combine);echo '<pre>'; exit;
$category_data = array();
$series_data = $series_data_ = array();
$temp_array = $temp_array_ = array();
$graph_data = array();
$graph_type = '';
foreach ($combine as $data) {
$series_data = array_merge($series_data, array($data["commodity_name"] => (int) $data['cur_bal_packs'] / (int) $data['amc_packs']));
$category_data = array_merge($category_data, array($data["commodity_name"]));
}
// echo "<pre>";print_r($series_data);echo "</pre>";exit;
$graph_type = 'bar';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_mos'));
$graph_data = array_merge($graph_data, array("graph_title" => "{$title} Stock Level in Months of Stock (MOS)"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("color" => "['#4572A7','#FFF263', '#6AF9C4']"));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "MOS"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('total' => $series_data)));
$data = array();
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
//
$data['graph_id'] = 'dem_graph_mos';
return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data);
//
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "Stock Level in Months of Stock {$title}", 'file_name' => $title . ' MOS');
$row_data = array();
$column_data = array("County", "Sub-County", "Facility Name", "Facility Code", "Item Name", "MOS(packs)");
$excel_data['column_data'] = $column_data;
//echo '' ; exit;
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT d.id,ct.county,sc.district,f.facility_code,\n\t\t\t\tf.facility_name,sum(fs.current_balance) as bal\n\t\t\t\t,sum(fs.current_balance)/d.total_commodity_units as packs,d.total_commodity_units,fs.batch_no,fs.expiry_date,d.commodity_name\n\t\t\t\tFROM hcmp_rtk.facility_stocks fs\n\t\t\t\tINNER JOIN facilities f ON fs.facility_code=f.facility_code\n\t\t\t\tINNER JOIN commodities d ON fs.commodity_id=d.id\n\t\t\t\tINNER JOIN districts sc ON f.district=sc.id\n\t\t\t\tINNER JOIN counties ct ON sc.county=ct.id\n\t\t\t\t{$and_data} AND fs.status=1 group by fs.batch_no order by ct.id asc\n\t\t\t\t");
$r_data = array();
$counter = 0;
foreach ($commodity_array as $key) {
$commodity = $key['id'];
$f_code = $key['facility_code'];
$batch_n = $key['batch_no'];
$amc = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n\t\t\t\t\tSELECT sum(qty_issued) as amc FROM hcmp_rtk.facility_issues where commodity_id={$commodity} and facility_code={$f_code} AND batch_no='{$batch_n}' AND\n\t\t\t\t\tdate_issued\t> DATE_SUB(CURDATE(), INTERVAL 31 DAY)\n\t\t\t\t\t");
foreach ($amc as $val) {
$amc = $val['amc'];
if ($amc < 0) {
$amc = $amc * -1;
}
$amc_packs = round($amc / $key['total_commodity_units']);
if ($amc_packs < 0) {
$amc_packs = $amc_packs * -1;
}
if ($key['bal'] < 0) {
$bal = $key['bal'] * -1;
} else {
$bal = $key['bal'];
}
if ($key['packs'] < 0) {
$packs = $key['packs'] * -1;
} else {
$packs = $key['packs'];
}
}
$r_data[$counter]["county"] = $key['county'];
$r_data[$counter]["district"] = $key['district'];
$r_data[$counter]["facility_code"] = $key['facility_code'];
$r_data[$counter]["facility_name"] = $key['facility_name'];
$r_data[$counter]["commodity_name"] = $key['commodity_name'];
$r_data[$counter]["amc_packs"] = $amc_packs;
// $r_data[$counter]["amc_units"] = $key['bal'];
$counter = $counter + 1;
}
array_push($row_data, $r_data);
$excel_data['row_data'] = $r_data;
// echo "<pre>";print_r($row_data);echo "</pre>";exit;
$this->hcmp_functions->create_excel($excel_data);
}
}
public function expiry($year = null, $county_id = null, $district_id = null, $facility_code = null, $graph_type = null)
{
$year = $year == "NULL" ? date('Y') : $year;
/*//Get the current month
$datetime1 = new DateTime('Y-10');
$datetime2 = new DateTime('Y-12');
$interval = $datetime2->diff($datetime1);
echo $interval->format('%R%a days');exit;
$current_month = date("Y-m");
$end = date('Y-12');
$interval = $current_month->diff($end);
echo $interval;exit;*/
//check if the district is set
$district_id = $district_id == "NULL" ? null : $district_id;
// $option=($optionr=="NULL") ? null :$option;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
// $months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$month_ = isset($month) ? $months[(int) $month - 1] : null;
$category_data = array();
$series_data = $series_data2 = $series_data_ = $series_data_2 = array();
$temp_array = $temp_array2 = $temp_array_ = array();
$graph_data = array();
$title = '';
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name['county'];
$title = "{$name} County";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "";
}
$months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$category_data = array_merge($category_data, $months);
$and_data = $district_id > 0 ? " AND d1.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND d1.county='{$county_id}'" : null;
$and_data = isset($and_data) ? $and_data : null;
$group_by = $district_id > 0 && isset($county_id) && !isset($facility_code) ? " ,d1.id" : null;
$group_by .= $facility_code > 0 && isset($district_id) ? " ,f.facility_code" : null;
$group_by .= $county_id > 0 && !isset($district_id) ? " ,c.id" : null;
$group_by = isset($group_by) ? $group_by : " ,c.id";
if ($graph_type != "excel") {
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select DATE_FORMAT( temp.expiry_date, '%b' ) AS cal_month,\n\t \t\t\tsum(temp.total) as total\n\t\t\t\tfrom\n\t\t\t\t districts d1,\n\t\t\t\t facilities f\n\t\t\t\t left join\n\t\t\t\t (select ROUND(SUM(f_s.current_balance / d.total_commodity_units) * d.unit_cost, 1) AS total,\n\t\t\t\t f_s.facility_code,f_s.expiry_date\n\t\t\t\t from\n\t\t\t\t facility_stocks f_s, commodities d\n\t\t\t\t where\n\t\t\t\t f_s.expiry_date < NOW()\n\t\t\t\t and d.id = f_s.commodity_id\n\t\t\t\t and year(f_s.expiry_date) = {$year}\n\t\t\t\t AND (f_s.status =1 or f_s.status =2 )\n\t\t\t\t GROUP BY d.id , f_s.facility_code having total > 1) \n\t\t\t temp ON temp.facility_code = f.facility_code\n\t\t\t\t\twhere\n\t\t\t\t\t f.district = d1.id\n\t\t\t\t\t {$and_data}\n\t\t\t\t\t and temp.total > 0\n\t\t\t\t\tgroup by month(temp.expiry_date)");
$commodity_array2 = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n\t\t\tselect \n\t\t\t DATE_FORMAT( temp.expiry_date, '%b' ) AS cal_month,\n\t\t\t sum(temp.total) as total\n\t\t\tfrom\n\t\t\t districts d1,\n\t\t\t facilities f\n\t\t\t left join\n\t\t\t (select \n\t\t\t ROUND(SUM(f_s.current_balance / d.total_commodity_units) * d.unit_cost, 1) AS total,\n\t\t\t f_s.facility_code,f_s.expiry_date\n\t\t\t from\n\t\t\t facility_stocks f_s, commodities d\n\t\t\t where\n\t\t\t f_s.expiry_date >= NOW()\n\t\t\t and d.id = f_s.commodity_id\n\t\t\t AND f_s.status = (1 or 2)\n\t\t\t\t\t\tAND year(f_s.expiry_date) = {$year}\n\t\t\t GROUP BY d.id , f_s.facility_code\n\t\t\t having total > 1) temp ON temp.facility_code = f.facility_code\n\t\t\twhere\n\t\t\t f.district = d1.id\n\t\t\t {$and_data}\n\t\t\t and temp.total > 0\n\t\t\tgroup by month(temp.expiry_date)\n\t\t ");
foreach ($commodity_array as $data) {
$temp_array = array_merge($temp_array, array($data["cal_month"] => $data['total']));
}
foreach ($commodity_array2 as $data2) {
$temp_array2 = array_merge($temp_array2, array($data2["cal_month"] => $data2['total']));
//$series_data2 = array_merge($series_data2, array($data2["cal_month"] => (int)$data2['total']));
//$category_data = array_merge($category_data, array($data2["cal_month"]));
}
// echo "<pre>";print_r($temp_array2);echo "</pre>";exit;
foreach ($months as $key => $data) {
//for expiries
$val = array_key_exists($data, $temp_array) ? (int) $temp_array[$data] : (int) 0;
$series_data = array_merge($series_data, array($val));
array_push($series_data_, array($data, $val));
//for potential expiries
$val2 = array_key_exists($data, $temp_array2) ? (int) $temp_array2[$data] : (int) 0;
$series_data2 = array_merge($series_data2, array($val2));
array_push($series_data_2, array($data, $val2));
}
$graph_type = 'column';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("color" => "['#6AF9C4','#4b0082', '#6AF9C4']"));
$graph_data = array_merge($graph_data, array("graph_title" => "Expiries in {$title} {$year}"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "KSH"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array()));
//$default_expiries=array_merge($default_expiries,array("series_data"=>array()));
$graph_data['series_data'] = array_merge($graph_data['series_data'], array("Potential Expiries" => $series_data2, "Actual Expiries" => $series_data));
//echo "<pre>";print_r($graph_data);echo "</pre>";exit;
$data = array();
$data['graph_id'] = 'dem_graph_';
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
// print_r($data['high_graph']);
//exit;
return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data);
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "Expiry {$title}", 'file_name' => "Stock Expired in {$title} {$year}");
$row_data = array();
$column_data = array("Commodity", "Unit Size", "Quantity (Packs)", "Quantity (Units)", "Unit Cost (Ksh)", "Total Cost Expired (Ksh)", "Date of Expiry", "Supplier", "Manufacturer", "Facility Name", "Facility Code", "Sub-County", "County");
$excel_data['column_data'] = $column_data;
//echo ; exit;
$facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select c.county, d1.district as subcounty ,temp.drug_name,\n f.facility_code, f.facility_name,temp.manufacture, sum(temp.total) as total_ksh,temp.units,\ntemp.unit_cost,temp.expiry_date,temp.unit_size,\ntemp.packs\nfrom districts d1, counties c, facilities f left join\n (\nselect ROUND( SUM(\nf_s.current_balance / d.total_commodity_units ) * d.unit_cost, 1) AS total,\n ROUND( SUM( f_s.current_balance / d.total_commodity_units ), 1) as packs,\nSUM( f_s.current_balance) as units,\nf_s.facility_code,d.id,d.commodity_name as drug_name, f_s.manufacture,\nf_s.expiry_date,d.unit_size,d.unit_cost\n\n from facility_stocks f_s, commodities d\nwhere f_s.expiry_date < NOW( ) \nand d.id=f_s.commodity_id\nand year(f_s.expiry_date) !=1970\nAND (f_s.status =1 or f_s.status =2)\nGROUP BY d.id,f_s.facility_code having total >1\n\n ) temp\n on temp.facility_code = f.facility_code\nwhere f.district = d1.id\nand c.id=d1.county\nand temp.total>0\n{$and_data}\ngroup by temp.id,f.facility_code\norder by temp.drug_name asc,temp.total asc, temp.expiry_date desc\n ");
array_push($row_data, array("The below commodities have expired {$title} {$year}"));
foreach ($facility_stock_data as $facility_stock_data_item) {
array_push($row_data, array($facility_stock_data_item["drug_name"], $facility_stock_data_item["unit_size"], $facility_stock_data_item["packs"], $facility_stock_data_item["units"], $facility_stock_data_item["unit_cost"], $facility_stock_data_item["total_ksh"], $facility_stock_data_item["expiry_date"], "KEMSA", $facility_stock_data_item["manufacture"], $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["subcounty"], $facility_stock_data_item["county"]));
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
}
}
public function confirm_external_issue_edit($editable = null)
{
$facility_code = $this->session->userdata('facility_id');
$subcounty_id = $this->session->userdata('district_id');
$data['title'] = "Edit Redistribution";
$data['banner_text'] = "Edit Redistribution";
$data['redistribution_data'] = redistribution_data::get_all_active_edit($facility_code, $editable);
// $data['redistribution_data'] = redistribution_data::get_all_active($facility_code, $editable);
$districts_data = districts::get_district_name($subcounty_id);
$district_name = '';
foreach ($districts_data as $value) {
$district_name = $value->district;
}
$data['district_name'] = $district_name;
$data['district_id'] = $subcounty_id;
$data['editable'] = $editable;
$data['subcounties'] = districts::getAll();
$data['content_view'] = "facility/facility_issues/facility_redistribute_items_confirmation_edit_v";
$this->load->view("shared_files/template/template", $data);
}
public function order($year = null, $county_id = null, $district_id = null, $facility_code = null, $graph_type = null)
{
$district_id = $district_id == "NULL" ? null : $district_id;
$graph_type = $graph_type == "NULL" ? null : $graph_type;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
$year = $year == "NULL" ? date('Y') : $year;
$and_data = $district_id > 0 ? " AND d.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND c.id='{$county_id}'" : null;
$and_data .= $year > 0 ? " and year(o.`order_date`) ={$year}" : null;
$and_data = isset($year) ? $and_data : null;
//echo ; exit;
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT \n sum(o.`order_total`) as total,DATE_FORMAT( o.`order_date`, '%b' ) AS cal_month\n FROM\n facilities f, districts d, counties c,`facility_orders` o\n WHERE\n o.facility_code=f.facility_code\n and f.district=d.id and d.county=c.id\n {$and_data}\n group by month(o.`order_date`)\n ");
$commodity_array_2 = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT \n sum(o.`order_total`) as total,DATE_FORMAT( o.`order_date`, '%b' ) AS cal_month\n FROM\n facilities f, districts d, counties c,`facility_orders` o\n WHERE\n o.facility_code=f.facility_code\n and f.district=d.id and d.county=c.id and o.status = 4\n {$and_data}\n group by month(o.`order_date`)\n ");
// var_dump($commodity_array_2);
// exit;
$category_data = array();
$series_data = $series_data_ = array();
$temp_array = $temp_array_ = array();
$graph_data = array();
$title = '';
if ($graph_type != "excel") {
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name['county'];
$title = "{$name} county";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "National";
}
foreach ($commodity_array as $data) {
$series_data = array_merge($series_data, array($data["cal_month"] => (int) $data['total']));
$category_data = array_merge($category_data, array($data["cal_month"]));
}
$series_data2 = $series_data_2 = $category_data_2 = array();
foreach ($commodity_array_2 as $data_2) {
$series_data_2 = array_merge($series_data_2, array($data_2["cal_month"] => (int) $data_2['total']));
$category_data_2 = array_merge($category_data_2, array($data_2["cal_month"]));
}
//$graph_details = array('' => , );;
// array_merge($series_data,$series_data_2);
// echo "<pre>";print_r($series_data_2);echo "</pre>";exit;
$graph_type = 'column';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_order'));
$graph_data = array_merge($graph_data, array("graph_title" => "{$year} {$title} Order Cost"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("color" => "['#92A8CD', '#A47D7C']"));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Cost in KSH"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('Cost of Orders Made' => $series_data, 'Cost of Orders delivered' => $series_data_2)));
$data = array();
//seth
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
// echo $data['high_graph'];exit;
$data['graph_id'] = 'dem_graph_order';
return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data);
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "{$year} {$title} Order Cost", 'file_name' => "{$year} {$title} Order Cost (KSH)");
$row_data = array();
$column_data = array("Date of Order Placement", "Date of Order Approval", "Total Order Cost (Ksh)", "Date of Delivery", "Lead Time (Order Placement to Delivery)", "Supplier", "Facility Name", "Facility Code", "Sub-County", "County");
$excel_data['column_data'] = $column_data;
//echo ; exit;
$facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT c.county,d.district as sub_county, f.facility_name, f.facility_code, \n DATE_FORMAT(`order_date`,'%d %b %y') as order_date, \n DATE_FORMAT(`approval_date`,'%d %b %y') as approval_date,\n DATE_FORMAT(`deliver_date`,'%d %b %y') as delivery_date, \n DATEDIFF(`approval_date`,`order_date`) as tat_order_approval,\n DATEDIFF(`deliver_date`,`approval_date`) as tat_approval_deliver,\n DATEDIFF(`deliver_date`,`order_date`) as tat_order_delivery\n , sum(o.`order_total`) as total \n from facility_orders o, facilities f, districts d, counties c \n where f.facility_code=o.facility_code and f.district=d.id \n and c.id=d.county {$and_data}\n group by o.id order by c.county asc ,d.district asc , \n f.facility_name asc \n ");
array_push($row_data, array("The orders below were placed {$year} {$title}"));
foreach ($facility_stock_data as $facility_stock_data_item) {
array_push($row_data, array($facility_stock_data_item["order_date"], $facility_stock_data_item["approval_date"], $facility_stock_data_item["total"], $facility_stock_data_item["delivery_date"], $facility_stock_data_item["tat_order_delivery"], "KEMSA", $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["sub_county"], $facility_stock_data_item["county"]));
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
}
}
public function rtk_summary_district($district, $year, $month)
{
$distname = districts::get_district_name($district);
$districtname = $distname[0]['district'];
$district_id = $district;
$returnable = array();
$nonreported;
$reported_percentage;
$late_percentage;
// Sets the timezone and date variables for last day of previous month and this month
date_default_timezone_set('EUROPE/moscow');
$month = $month + 1;
$prev_month = $month - 1;
$last_day_current_month = date('Y-m-d', mktime(0, 0, 0, $month, 0, $year));
$first_day_current_month = date('Y-m-', mktime(0, 0, 0, $month, 0, $year));
$first_day_current_month .= '1';
$lastday_thismonth = date('Y-m-d', strtotime("last day of this month"));
$month -= 1;
$day10 = $year . '-' . $month . '-10';
$day11 = $year . '-' . $month . '-11';
$day12 = $year . '-' . $month . '-12';
$late_reporting = 0;
$text_month = date('F', strtotime($day10));
$q = 'SELECT *
FROM facilities, districts, counties
WHERE facilities.district = districts.id
AND districts.county = counties.id
AND districts.id = ' . $district . '
AND facilities.rtk_enabled =1
ORDER BY `facilities`.`facility_name` ASC ';
$q_res = $this->db->query($q);
$total_reporting_facilities = $q_res->num_rows();
$q = "SELECT DISTINCT lab_commodity_orders.facility_code, lab_commodity_orders.id,lab_commodity_orders.order_date\n FROM lab_commodity_orders, districts, counties\n WHERE districts.id = lab_commodity_orders.district_id\n AND districts.county = counties.id\n AND districts.id = {$district}\n AND lab_commodity_orders.order_date\n BETWEEN '{$first_day_current_month}'\n AND '{$last_day_current_month}'";
$q_res1 = $this->db->query($q);
$total_reported_facilities = $q_res1->num_rows();
foreach ($q_res1->result_array() as $vals) {
// echo "<pre>";var_dump($vals);echo "</pre>";
if ($vals['order_date'] == $day10 || $vals['order_date'] == $day11 || $vals['order_date'] == $day12) {
$late_reporting += 1;
// echo "<pre>";var_dump($vals);echo "</pre>";
}
}
$nonreported = $total_reporting_facilities - $total_reported_facilities;
if ($total_reporting_facilities == 0) {
$non_reported_percentage = 0;
} else {
$non_reported_percentage = $nonreported / $total_reporting_facilities * 100;
}
$non_reported_percentage = number_format($non_reported_percentage, 0);
if ($total_reporting_facilities == 0) {
$reported_percentage = 0;
} else {
$reported_percentage = $total_reported_facilities / $total_reporting_facilities * 100;
}
$reported_percentage = number_format($reported_percentage, 0);
if ($total_reporting_facilities == 0) {
$late_percentage = 0;
} else {
$late_percentage = $late_reporting / $total_reporting_facilities * 100;
}
$late_percentage = number_format($late_percentage, 0);
if ($total_reported_facilities > $total_reporting_facilities) {
$reported_percentage = 100;
$nonreported = 0;
$total_reported_facilities = $total_reporting_facilities;
}
if ($late_reporting > $total_reporting_facilities) {
$late_reporting = $total_reporting_facilities;
$late_percentage = $reported_percentage;
}
$returnable = array('Month' => $text_month, 'Year' => $year, 'district' => $districtname, 'district_id' => $district_id, 'total_facilities' => $total_reporting_facilities, 'reported' => $total_reported_facilities, 'reported_percentage' => $reported_percentage, 'nonreported' => $nonreported, 'nonreported_percentage' => $non_reported_percentage, 'late_reports' => $late_reporting, 'late_reports_percentage' => $late_percentage);
return $returnable;
}
public function get_district_facility_mapping_($district_id)
{
$facility_data = facilities::getFacilities($district_id);
$table_body = "";
$dpp_details = user::get_dpp_details($district_id)->toArray();
$district_name = districts::get_district_name($district_id)->toArray();
$dpp_fname = '';
$dpp_lname = '';
$dpp_phone = '';
$dpp_email = '';
if (count($dpp_details) > 0) {
$dpp_fname = $dpp_details[0]['fname'];
$dpp_lname = $dpp_details[0]['lname'];
$dpp_phone = $dpp_details[0]['telephone'];
$dpp_email = $dpp_details[0]['email'];
}
$indicator = "District";
$no_of_facility_users = 0;
$no_of_facility_users_online = 0;
$no_of_facilities = 0;
$no_of_facilities_using = 0;
foreach ($facility_data as $facility_detail) {
$facility_code = $facility_detail->facility_code;
$facility_extra_data = facilities::get_facility_status_no_users_status($facility_code);
$no_of_facility_users = $no_of_facility_users + $facility_extra_data[0]['number_of_users'];
$no_of_facility_users_online = $no_of_facility_users_online + $facility_extra_data[0]['number_of_users_online'];
$no_of_facilities = $no_of_facilities + 1;
if ($facility_extra_data[0]['number_of_users'] > 0) {
$no_of_facilities_using = $no_of_facilities_using + 1;
}
$table_body .= "<tr>";
$status = null;
$temp = $facility_extra_data[0]['status'];
$temp == "Active" ? $status = "<span class='label label-success'>{$temp}</span>" : ($status = "<span class='label label-important'>{$temp}</span>");
$table_body .= "<td>{$facility_code}</td>\n\t <td>{$facility_detail->facility_name}</td>\n\t <td>{$facility_detail->owner}</td>\n\t <td>{$status}</td>\n\t <td>" . $facility_extra_data[0]['number_of_users'] . "</td>\n\t <td>" . $facility_extra_data[0]['number_of_users_online'] . "</td>";
$table_body .= "</tr>";
}
$stats_data = '
<table style="float:left">
<tr>
<td><label style=" font-weight: ">' . $district_name[0]['district'] . ' ' . $indicator . ' Pharmacist :</label></td>
<td><a class="badge">' . $dpp_fname . ' ' . $dpp_lname . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Phone No.</label></td>
<td><a class="badge">' . $dpp_phone . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Email Address</label></td>
<td><a class="badge">' . $dpp_email . '</a></td>
</tr>
</table>
<table style="float:left">
<tr>
<td><label style=" font-weight: ">Total No of Facilities in The ' . $indicator . ' </label></td>
<td><a class="badge" >' . $no_of_facilities . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Total No of Facilities in The ' . $indicator . ' Using HCMP </label></td>
<td> <a class="badge">' . $no_of_facilities_using . '</a></td>
</tr>
</table>
<table style="float:left">
<tr>
<td><label style="font-weight: ">Total No of Users in The ' . $indicator . ' </label></td>
<td><a class="badge" >' . $no_of_facility_users . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Users online in The ' . $indicator . '</label></td>
<td><a class="badge" >' . $no_of_facility_users_online . '</a></td>
</tr>
</table>
';
$data['stats_data'] = $stats_data;
$data['table_body'] = $table_body;
$this->load->view("county/ajax_view/facility_mapping_v", $data);
}
public function orders($year = null, $county_id = null, $district_id = null, $facility_code = null, $graph_type = null)
{
$district_id = $district_id == "NULL" ? null : $district_id;
$graph_type = $graph_type == "NULL" ? null : $graph_type;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $county_id == "NULL" ? null : $county_id;
$year = $year == "NULL" ? date('Y') : $year;
$and_data = $district_id > 0 ? " AND d.id = '{$district_id}'" : null;
$and_data .= $facility_code > 0 ? " AND f.facility_code = '{$facility_code}'" : null;
$and_data .= $county_id > 0 ? " AND c.id='{$county_id}'" : null;
$and_data .= $year > 0 ? " and year(o.`order_date`) ={$year}" : null;
$and_data = isset($year) ? $and_data : null;
//echo ; exit;
$commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT \n sum(o.`order_total`) as total,DATE_FORMAT( o.`order_date`, '%b' ) AS cal_month\nFROM\n facilities f, districts d, counties c,`facility_orders` o\nWHERE\n o.facility_code=f.facility_code\n and f.district=d.id and d.county=c.id\n {$and_data}\ngroup by month(o.`order_date`)\n ");
//var_dump($commodity_array);
//exit;
$category_data = array();
$series_data = $series_data_ = array();
$temp_array = $temp_array_ = array();
$graph_data = array();
$title = '';
if ($graph_type != "excel") {
if (isset($county_id)) {
$county_name = counties::get_county_name($county_id);
$name = $county_name[0]['county'];
$title = "{$name} county";
} elseif (isset($district_id)) {
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " Subcounty" : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : (isset($district_id) && !isset($facility_code) ? "{$district_name_}" : "{$name} County");
} elseif (isset($facility_code)) {
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code) : null;
$title = $facility_code_['facility_name'];
} else {
$title = "National";
}
$arrayseries = array();
foreach ($commodity_array as $data) {
$arrayseries[] = (int) $data['total'];
//$series_data = array_merge($series_data, array($data["drug_name"] => (int)$data['total']));
$category_data = array_merge($category_data, array($data["cal_month"]));
}
$data['graph_type'] = 'spline';
$data['graph_title'] = "{$year} {$title} Order Cost";
$data['graph_yaxis_title'] = "Cost in Ksh";
$data['graph_id'] = "orders";
$data['legend'] = "Ksh";
$data['colors'] = "['#4b0082', '#DDDF00', '#FFF263', '#6AF9C4']";
$data['category_data'] = json_encode($category_data);
$data['series_data'] = json_encode($arrayseries);
return $this->load->view("national/ajax/bar_template", $data);
} else {
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => "{$year} {$title} Order Cost", 'file_name' => "{$year} {$title} Order Cost (KSH)");
$row_data = array();
$column_data = array("Date of Order Placement", "Date of Order Approval", "Total Order Cost (Ksh)", "Date of Delivery", "Lead Time (Order Placement to Delivery)", "Supplier", "Facility Name", "Facility Code", "Sub-County", "County");
$excel_data['column_data'] = $column_data;
//echo ; exit;
$facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT c.county,d.district as sub_county, f.facility_name, f.facility_code, \n DATE_FORMAT(`order_date`,'%d %b %y') as order_date, \n DATE_FORMAT(`approval_date`,'%d %b %y') as approval_date,\n DATE_FORMAT(`deliver_date`,'%d %b %y') as delivery_date, \n DATEDIFF(`approval_date`,`order_date`) as tat_order_approval,\n DATEDIFF(`deliver_date`,`approval_date`) as tat_approval_deliver,\n DATEDIFF(`deliver_date`,`order_date`) as tat_order_delivery\n , sum(o.`order_total`) as total \n from facility_orders o, facilities f, districts d, counties c \n where f.facility_code=o.facility_code and f.district=d.id \n and c.id=d.county {$and_data}\n group by o.id order by c.county asc ,d.district asc , \n f.facility_name asc \n ");
array_push($row_data, array("The orders below were placed {$year} {$title}"));
foreach ($facility_stock_data as $facility_stock_data_item) {
array_push($row_data, array($facility_stock_data_item["order_date"], $facility_stock_data_item["approval_date"], $facility_stock_data_item["total"], $facility_stock_data_item["delivery_date"], $facility_stock_data_item["tat_order_delivery"], "KEMSA", $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["sub_county"], $facility_stock_data_item["county"]));
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
}
}
