public function get_facility_bin_card_pdf($facility_code, $report_type, $commodity_id) { $county_id = $this->session->userdata('county_id'); $district_id = $this->session->userdata('district_id'); $param = implode('/', func_get_args()); $args = array(); $args = func_get_args(); $facility_code = $args[0]; $commodity_id = $args[1]; $from_ = array($args[2], $args[3], $args[4]); $to_ = array($args[5], $args[6], $args[7]); $from = implode('/', $from_); $to = implode('/', $to_); $commodity_name = Commodities::get_commodity_name($commodity_id); $county = Counties::get_county_name($county_id); $district = Districts::get_district_name_($district_id); $county_name = $county['county']; $district_name = $district['district']; $bin_card_data = Facility_issues::get_bin_card($facility_code, $commodity_id, $from, $to); $myobj = Doctrine::getTable('Facilities')->findOneByfacility_code($facility_code); $facility_name = $myobj->facility_name; $bin_card_data_count = count(Facility_issues::get_bin_card($facility_code, $commodity_id, $from, $to)); //create the table for displaying the order details $html_body = "<table class='data-table' width=100%>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>MFL No: {$facility_code}</td> \n\t\t\t\t\t\t\t<td>Health Facility Name:<br/> {$facility_name}</td>\n\t\t\t\t\t\t\t<td>Level:</td>\n\t\t\t\t\t\t\t<td>Dispensary</td>\n\t\t\t\t\t\t\t<td>Health Centre</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>County: {$county_name}</td> \n\t\t\t\t\t\t\t<td> District: {$district_name}</td>\n\t\t\t\t\t\t\t<td >Reporting Period <br/>\n\t\t\t\t\t\t\t\tStart Date: <br/> End Date: " . date('d M, Y') . "\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>"; $html_body .= "\n\t\t\t\t\t<table class='data-table'>\n\t\t\t\t\t\t<thead>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th><b>Commodity Name</b></th>\n\t\t\t\t\t\t\t\t<th><b>Date of Issue</b></th>\n\t\t\t\t\t\t\t\t<th><b>Reference No/S11 No</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Commodity Unit Size</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Batch No -Issued</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Expiry Date</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Opening Bal.</b></th>\n\t\t\t\t\t\t\t\t<th ><b>+ADJ</b></th>\n\t\t\t\t\t\t\t\t<th ><b>-ADJ</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Receipts/Issues</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Closing Bal.</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Service Point</b></th>\n\t\t\t\t\t\t\t\t<th ><b>Issuing/Receiving Officer</b></th>\n\t\t\t\t\t\t\t</tr> \n\t\t\t\t\t\t</thead>\n\t\t\t\t\t\t<tbody>"; $html_body .= '<ol type="a">'; for ($i = 0; $i < $bin_card_data_count; $i++) { $closing_balance = $bin_card_data[$i]['balance_as_of'] - $bin_card_data[$i]['qty_issued']; $mydrug_name = $commodity_name; $html_body .= "<tr>"; $html_body .= "<td>" . $commodity_name[0]['commodity_name'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['date_issued'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['s11_No'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['unit_size'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['batch_no'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['expiry_date'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['balance_as_of'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['adjustmentpve'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['adjustmentnve'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['qty_issued'] . "</td>"; $html_body .= "<td>" . $closing_balance . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['service_point_name'] . "</td>"; $html_body .= "<td>" . $bin_card_data[$i]['fname'] . " " . $bin_card_data[$i]['fname'] . "</td>"; $html_body .= "</tr>"; } $html_body .= '</tbody></table></ol>'; $pdf_body = $html_body; //end of bin card pdf $file_name = $facility_name . '_facility_program_report_date_created_' . date('d-m-y'); $pdf_data = array("pdf_title" => "Program Report For {$facility_name}", 'pdf_html_body' => $pdf_body, 'pdf_view_option' => 'download', 'file_name' => $file_name); $this->hcmp_functions->create_pdf($pdf_data); redirect(); }
public function get_consumption_chart_ajax($sub_category = null) { $commodity_id = $this->input->post('commodity_id'); $from = $this->input->post('from'); $to = $this->input->post('to'); $total = 0; $to = $to == "NULL" || !isset($to) ? date('Y-m-d 23:59:59') : date('Y-m-d 23:59:59', strtotime(urldecode($to))); $from = $from == "NULL" || !isset($from) ? date('Y-m-d 00:00:00') : date('Y-m-d 00:00:00', strtotime(urldecode($from))); $consumption = facility_stocks::get_dispensing_consumption_by_age($commodity_id, $from, $to, $sub_category); $commodity_name = ''; if ($commodity_id != '') { $commodity_details = Commodities::get_commodity_name($commodity_id); $commodity_name = $commodity_details[0]['commodity_name']; } else { $commodity_details = commodity_sub_category::get_dets_one($sub_category); } $val1 = intval($consumption['5']); $val2 = intval($consumption['above']); $graph_id = 'dem_graph_'; $data['graph_id'] = 'dem_graph_'; $text_title = $commodity_name . ' consumption by Age between ' . date('F d Y', strtotime($from)) . ' and ' . date('F d Y', strtotime($to)); $result_chart = " \$('#{$graph_id}').highcharts({\n chart: {\n plotBackgroundColor: null,\n plotBorderWidth: null,\n plotShadow: false,\n type: 'pie'\n },\n title: {\n text: '{$text_title}'\n },\n tooltip: {\n pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'\n },\n plotOptions: {\n pie: {\n allowPointSelect: false,\n cursor: 'pointer',\n dataLabels: {\n enabled: true\n },\n showInLegend: true\n }\n },\n series: [{\n name: '{$commodity_name}',\n colorByPoint: true,\n data: [{\n name: 'Under 5 years ({$val1} units)',\n y: {$val1}\n }, {\n name: 'Over 5 Years ({$val2} units)',\n y:{$val2},\n sliced: true,\n selected: true\n }]\n }]\n });"; // $result_table .= ' $data['high_graph'] = $result_chart; return $this->load->view("shared_files/report_templates/high_charts_template_v", $data); echo $result_chart; }
public function consumption($county_id = NULL, $district_id = NULL, $facility_code = NULL, $commodity_id = NULL, $graph_type = NULL, $from = NULL, $to = NULL, $division = NULL) { // http://localhost/HCMP/dashboard/consumption/NULL/NULL/NULL/12/excel/NULL $title = ''; $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 > 0 ? $commodity_id : NULL; //by default set to ORS Co Pack $commodity_id = isset($commodity_id) && $commodity_id > 0 ? $commodity_id : $this->division_preferred_commodity_check($division); // echo $commodity_id;exit; $division = $division == "NULL" ? null : $division; $year = date('Y'); $commodity_array = explode(',', $commodity_id); $count_commodities = count($commodity_array); $year = $year == "NULL" || !isset($year) ? date('Y') : $year; $to = $to == "NULL" || !isset($to) ? date('Y-m-d') : date('Y-m-d', strtotime(urldecode($to))); $from = $from == "NULL" || !isset($from) ? date('Y-01-01') : date('Y-m-d', strtotime(urldecode($from))); $category_data = array(); $series_data = $series_data_ = array(); $temp_array = $temp_array_ = array(); $graph_data = array(); // $graph_type = ''; $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 c.id='{$county_id}'" : null; $and_data = isset($and_data) ? $and_data : null; if ($count_commodities > 1) { } else { $and_data .= $commodity_id > 0 ? "AND d.id ={$commodity_id}" : "AND d.tracer_item =1"; } // echo $graph_type;exit; /*$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";*/ $time = "Between " . date('j M y', strtotime(urldecode($from))) . " and " . date('j M y', strtotime(urldecode($to))); if ($commodity_id > 0) { $commodity_details = Dashboard_model::get_commodity_details($commodity_id); // echo "<pre>";print_r($commodity_details);exit; $commodity_name = $commodity_details[0]['commodity_name']; $title .= $commodity_name; } else { // $title .= ($tracer > 0)? "Tracer Commodity " :NULL; } // echo htmlspecialchars($title,ENT_QUOTES);exit; if (isset($county_id)) { $county_name = counties::get_county_name($county_id); $name = $county_name['county']; $title .= " {$name} County"; //print_r($name);exit; } 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'] . " Sub-County" : 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 { // echo "I work here";exit; $title .= ""; } //county id isset if ($graph_type != "excel") { //if you need change it,refer to national controller,function naming are similar // echo "This ".$graph_type;exit; $filter = $commodity_id > 0 ? "AND d.id = {$commodity_id}" : NULL; //default to ORS Co pack $commodity_array = $this->db->query("\n\t\t\t\tSELECT \n\t\t\t\t\td.id,\n\t\t\t\t d.commodity_name AS drug_name, \n\t\t\t\t ROUND(( IFNULL(SUM(f_i.qty_issued),0) / IFNULL(d.total_commodity_units, 0) ),0) as total,\n \t\t\t\tDATE_FORMAT(f_i.created_at, '%b') AS cal_month\n\t\t\t\tFROM\n\t\t\t\t facilities f,\n\t\t\t\t districts d1,\n\t\t\t\t counties c,\n\t\t\t\t commodities d\n\t\t\t\t LEFT JOIN\n\t\t\t\t facility_issues f_i ON f_i.commodity_id = d.id\n\t\t\t\tWHERE\n\t\t\t\t f_i.facility_code = f.facility_code\n\t\t\t\t AND f.district = d1.id\n\t\t\t\t AND d1.county = c.id\n\t\t\t\t AND f_i.`qty_issued` > 0\n\t\t\t\t AND YEAR(f_i.created_at) = {$year}\n\t\t\t\t {$filter}\n\t\t\t\t GROUP BY MONTH(f_i.created_at)\n\t\t\t\t ")->result_array(); // echo "<pre>";print_r($commodity_array);exit; $months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'); $category_data = array_merge($category_data, $months); $temp_array = array(); foreach ($commodity_array as $data) { // echo "<pre>";print_r($data); $temp_array = array_merge($temp_array, array($data["cal_month"] => $data['total'])); } // echo "<pre>";print_r($temp_array);exit; foreach ($months as $key => $data) { $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)); } $title = trim($title); $graph_type = 'spline'; $graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_consuption')); $graph_data = array_merge($graph_data, array("graph_title" => $title . " Consumption (Packs) for {$year}")); $graph_data = array_merge($graph_data, array("graph_type" => $graph_type)); $graph_data = array_merge($graph_data, array("color" => "['#7CB5EC', '#434348']")); $graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Packs")); $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(); // echo "<pre>";print_r($graph_data['graph_title']);exit; $data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data); $data['graph_id'] = 'dem_graph_consuption'; return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data); // echo "Graph data";exit; } else { // echo "Excel data";exit; $excel_data = array('doc_creator' => "HCMP", 'doc_title' => "{$title} Consumption (Packs) {$time}", 'file_name' => $title . ' Consumption'); $row_data = array(); $column_data = array("County", "Sub-County", "Facility Name", "Facility Code", "Item Name", "Consumption (Packs)"); for ($i = 1; $i < $count_commodities; $i++) { $item_name = "Item Name"; $consumption = "Consumption (Packs)"; array_push($column_data, $item_name); array_push($column_data, $consumption); } // echo "<pre>"; // print_r($column_data);die; $excel_data['column_data'] = $column_data; // echo ; exit; if ($count_commodities > 1) { $sql = "SELECT c.county,d1.district AS subcounty,f.facility_name,f.facility_code FROM\n\t\t\tfacilities f,districts d1,counties c WHERE f.district = d1.id AND d1.county = c.id {$and_data}\t\t\t\t\t \n\t\t\tORDER BY c.county ASC , d1.district ASC"; // echo "$sql";die; $facility_data = $this->db->query($sql)->result_array(); // array_push($final_array[0], "The below commodities were consumed $time"); $final_array[] = array("The below commodities were consumed {$time}", null, null, null); foreach ($facility_data as $key => $value) { $county = $value['county']; $subcounty = $value['subcounty']; $facility_name = $value['facility_name']; $facility_code = $value['facility_code']; $final_array[$facility_code] = array($county, $subcounty, $facility_name, $facility_code); for ($i = 0; $i < $count_commodities; $i++) { $commodity_id = $commodity_array[$i]; $commodity_details = Commodities::get_commodity_name($commodity_id); $drug_name = $commodity_details[0]['commodity_name']; array_push($final_array[$facility_code], $drug_name); $sql_commodity_details = "SELECT ROUND(AVG(IFNULL(ABS(f_i.`qty_issued`), 0) / IFNULL(d.total_commodity_units, 0)),1) AS total\n\t\t\t\t\tFROM commodities d LEFT JOIN facility_issues f_i ON f_i.`commodity_id` = d.id\n\t\t\t\t\tWHERE f_i.facility_code = '{$facility_code}'\n\t\t\t\t\tAND f_i.`qty_issued` > 0\n\t\t\t\t\tand f_i.created_at between '{$from}' and '{$to}'\n\t\t\t\t\tAND d.id = '{$commodity_id}'\n\t\t\t\t\tGROUP BY d.id , f_i.facility_code"; echo $sql_commodity_details; die; $consuption_details = $this->db->query($sql_commodity_details)->result_array(); if (count($consuption_details) == 0) { $total = 'No Data Available'; array_push($final_array[$facility_code], $total); } else { foreach ($consuption_details as $keys => $values) { $total = $values['total']; $total = $total == '' ? 'No Data Available' : $total; array_push($final_array[$facility_code], $total); } } } } $row_data = $final_array; } else { /*echo "select c.county,d1.district as subcounty, f.facility_name,f.facility_code, d.commodity_name as drug_name, round(avg(IFNULL(ABS(f_i.`qty_issued`), 0) / IFNULL(d.total_commodity_units, 0)), 1) as total from facilities f, districts d1, counties c, commodities d left join facility_issues f_i on f_i.`commodity_id`=d.id where f_i.facility_code = f.facility_code and f.district=d1.id and d1.county=c.id and f_i.`qty_issued`>0 and f_i.created_at between '$from' and '$to' $and_data group by d.id , f.facility_code order by c.county asc , d1.district asc";exit;*/ array_push($row_data, array("The below commodities were consumed {$time}")); $commodity_id = $commodity_array[0]; $facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n\t\t\t\tselect \n\t\t\t\tc.county,d1.district as subcounty, f.facility_name,f.facility_code, d.commodity_name as drug_name,\n\t\t\t\tround((IFNULL(SUM(f_i.`qty_issued`), 0) / IFNULL(d.total_commodity_units, 0)),\n\t\t\t\t1) as total\n\t\t\t\tfrom\n\t\t\t\tfacilities f,\n\t\t\t\tdistricts d1,\n\t\t\t\tcounties c,\n\t\t\t\tcommodities d\n\t\t\t\tleft join facility_issues f_i on f_i.`commodity_id`=d.id \n\t\t\t\twhere f_i.facility_code = f.facility_code \n\t\t\t\tand f.district=d1.id \n\t\t\t\tand d1.county=c.id \n\t\t\t\tand f_i.`qty_issued`>0\n\t\t\t\tand f_i.created_at between '{$from}' and '{$to}'\n\t\t\t\t{$and_data}\n\t\t\t\tgroup by d.id , f.facility_code\n\t\t\t\torder by c.county asc , d1.district asc\n\t\t\t\t"); foreach ($facility_stock_data as $facility_stock_data_item) { array_push($row_data, array($facility_stock_data_item["county"], $facility_stock_data_item["subcounty"], $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["drug_name"], $facility_stock_data_item["total"])); } } // echo "<pre>";print_r($row_data);exit; $excel_data['row_data'] = $row_data; $this->hcmp_functions->create_excel($excel_data); } }
public function consumption($county_id = null, $district_id = null, $facility_code = null, $commodity_id = null, $graph_type = null, $from = null, $to = null) { $title = ''; $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 == "NULL" ? null : $commodity_id; $commodity_array = explode(',', $commodity_id); $count_commodities = count($commodity_array); $year = $year == "NULL" || !isset($year) ? date('Y') : $year; $to = $to == "NULL" || !isset($to) ? date('Y-m-d') : date('Y-m-d', strtotime(urldecode($to))); $from = $from == "NULL" || !isset($from) ? date('Y-m-d') : date('Y-m-d', strtotime(urldecode($from))); $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 c.id='{$county_id}'" : null; $and_data = isset($and_data) ? $and_data : null; if ($count_commodities > 1) { } else { $and_data .= $commodity_id > 0 ? "AND d.id ={$commodity_id}" : "AND d.tracer_item =1"; // $and_data .= isset($commodity_id) ? "AND d.id =$commodity_id" : "AND d.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";*/ $time = "Between " . date('j M y', strtotime(urldecode($from))) . " and " . date('j M y', strtotime(urldecode($to))); if (isset($county_id)) { $county_name = counties::get_county_name($county_id); $name = $county_name['county']; $title = "{$name} County"; //print_r($name);exit; } 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") { // echo .$to; exit; $commodity_array = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select d.commodity_name as drug_name, \n\t\t \t\tround(avg(IFNULL(ABS(f_i.`qty_issued`),0) / IFNULL(d.total_commodity_units,0)),1) as total\n\t\t \t\tfrom facilities f, districts d1, counties c, commodities d left join facility_issues f_i on f_i.`commodity_id`=d.id \n\t\t \t\twhere f_i.facility_code = f.facility_code \n\t\t \t\tand f.district=d1.id \n\t\t \t\tand d1.county=c.id \n\t\t \t\tand f_i.`qty_issued`>0\n\t\t \t\tand YEAR(f_i.created_at)={$year} \n\t\t \t\t{$and_data}\n\t\t \t\tgroup by d.id\n\t\t \t\t"); $category_data = array(); $series_data = $series_data_ = array(); $temp_array = $temp_array_ = array(); $graph_data = array(); $graph_type = ''; foreach ($commodity_array as $data) { $series_data = array_merge($series_data, array($data["drug_name"] => (int) $data['total'])); $category_data = array_merge($category_data, array($data["drug_name"])); } $graph_type = 'bar'; $graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_consuption')); $graph_data = array_merge($graph_data, array("graph_title" => "{$title} Consumption (Packs) for {$year}")); $graph_data = array_merge($graph_data, array("graph_type" => $graph_type)); $graph_data = array_merge($graph_data, array("color" => "['#AA4643', '#89A54E', '#80699B']")); $graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Packs")); $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_consuption'; return $this->load->view("shared_files/report_templates/high_charts_template_v_national", $data); } else { $excel_data = array('doc_creator' => "HCMP", 'doc_title' => "{$title} Consumption (Packs) {$time}", 'file_name' => $title . ' Consumption'); $row_data = array(); $column_data = array("County", "Sub-County", "Facility Name", "Facility Code", "Item Name", "Consumption (Packs)"); for ($i = 1; $i < $count_commodities; $i++) { $item_name = "Item Name"; $consumption = "Consumption (Packs)"; array_push($column_data, $item_name); array_push($column_data, $consumption); } // echo "<pre>"; // print_r($column_data);die; $excel_data['column_data'] = $column_data; // echo ; exit; if ($count_commodities > 1) { $sql = "SELECT c.county,d1.district AS subcounty,f.facility_name,f.facility_code FROM\n\t\t \tfacilities f,districts d1,counties c WHERE f.district = d1.id AND d1.county = c.id {$and_data}\t\t\t\t\t \n\t\t \tORDER BY c.county ASC , d1.district ASC"; // echo "$sql";die; $facility_data = $this->db->query($sql)->result_array(); // array_push($final_array[0], "The below commodities were consumed $time"); $final_array[] = array("The below commodities were consumed {$time}", null, null, null); foreach ($facility_data as $key => $value) { $county = $value['county']; $subcounty = $value['subcounty']; $facility_name = $value['facility_name']; $facility_code = $value['facility_code']; $final_array[$facility_code] = array($county, $subcounty, $facility_name, $facility_code); for ($i = 0; $i < $count_commodities; $i++) { $commodity_id = $commodity_array[$i]; $commodity_details = Commodities::get_commodity_name($commodity_id); $drug_name = $commodity_details[0]['commodity_name']; array_push($final_array[$facility_code], $drug_name); $sql_commodity_details = "SELECT ROUND(AVG(IFNULL(ABS(f_i.`qty_issued`), 0) / IFNULL(d.total_commodity_units, 0)),1) AS total\n\t\t \t\t\tFROM commodities d LEFT JOIN facility_issues f_i ON f_i.`commodity_id` = d.id\n\t\t \t\t\tWHERE f_i.facility_code = '{$facility_code}'\n\t\t \t\t\tAND f_i.`qty_issued` > 0\n\t\t \t\t\tand f_i.created_at between '{$from}' and '{$to}'\n\t\t \t\t\tAND d.id = '{$commodity_id}'\n\t\t \t\t\tGROUP BY d.id , f_i.facility_code"; // echo $sql_commodity_details;die; $consuption_details = $this->db->query($sql_commodity_details)->result_array(); if (count($consuption_details) == 0) { $total = 'No Data Available'; array_push($final_array[$facility_code], $total); } else { foreach ($consuption_details as $keys => $values) { $total = $values['total']; $total = $total == '' ? 'No Data Available' : $total; array_push($final_array[$facility_code], $total); } } } } $row_data = $final_array; } else { array_push($row_data, array("The below commodities were consumed {$time}")); $commodity_id = $commodity_array[0]; $facility_stock_data = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select \n\t\t \t\tc.county,d1.district as subcounty, f.facility_name,f.facility_code, d.commodity_name as drug_name,\n\t\t \t\tround(avg(IFNULL(ABS(f_i.`qty_issued`), 0) / IFNULL(d.total_commodity_units, 0)),\n\t\t \t\t\t1) as total\n\t\t \tfrom\n\t\t \tfacilities f,\n\t\t \tdistricts d1,\n\t\t \tcounties c,\n\t\t \tcommodities d\n\t\t \tleft join facility_issues f_i on f_i.`commodity_id`=d.id \n\t\t \twhere f_i.facility_code = f.facility_code \n\t\t \tand f.district=d1.id \n\t\t \tand d1.county=c.id \n\t\t \tand f_i.`qty_issued`>0\n\t\t \tand f_i.created_at between '{$from}' and '{$to}'\n\t\t \t{$and_data}\n\t\t \tgroup by d.id , f.facility_code\n\t\t \torder by c.county asc , d1.district asc\n\t\t \t"); foreach ($facility_stock_data as $facility_stock_data_item) { array_push($row_data, array($facility_stock_data_item["county"], $facility_stock_data_item["subcounty"], $facility_stock_data_item["facility_name"], $facility_stock_data_item["facility_code"], $facility_stock_data_item["drug_name"], $facility_stock_data_item["total"])); } } $excel_data['row_data'] = $row_data; $this->hcmp_functions->create_excel($excel_data); } }