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);
}
}
