public function index($checker = NULL)
{
$facility_code = $this->session->userdata('facility_id');
switch ($checker) {
case 'internal':
$data['content_view'] = "facility/facility_issues/facility_issues_service_points_v";
$data['title'] = "Issues to service points";
$data['banner_text'] = "Issues to service points";
break;
case 'external':
$data['content_view'] = "facility/facility_issues/facility_redistribute_items_v";
$data['subcounties'] = districts::getAll();
$data['banner_text'] = "Redistribute Commodities";
$data['title'] = "Redistribute Commodities";
break;
default:
}
$data['service_point'] = service_points::get_all_active($facility_code);
$data['commodities'] = facility_stocks::get_distinct_stocks_for_this_facility($facility_code, 1);
$data_ = facility_stocks::get_distinct_stocks_for_this_facility($facility_code, "batch_data");
foreach ($data_ as $key => $data_1) {
$data_[$key]['commodity_name'] = preg_replace('/[^A-Za-z0-9\\-]/', ' ', $data_1['commodity_name']);
}
$data['facility_stock_data'] = json_encode($data_);
$this->load->view("shared_files/template/template", $data);
}
public function index()
{
$county_id = $this->session->userdata('county_id');
$data['district_data'] = districts::getDistrict($county_id);
$data['banner_text'] = "Facility Training Evaluation Results";
$data['title'] = "Facility Training Evaluation Results";
$data['report_view'] = "subcounty/reports/analysis_home";
$data['sidebar'] = "shared_files/report_templates/side_bar_sub_county_v";
$data['content_view'] = "facility/facility_reports/reports_v";
$data['report_view'] = "subcounty/reports/analysis_home";
$this->load->view('shared_files/template/template', $data);
}
public function index($checker = NULL, $pop_up_msg = NULL)
{
$facility = $this->session->userdata('news');
//$facility=$this->uri->segment(4);
switch ($checker) {
case 'Internal':
$data['content_view'] = "facility/facility_data/facility_issues/IssueInternal_v";
$data['title'] = "Stock";
$data['banner_text'] = "Issue";
$data['link'] = "IssuesnReceipts";
$data['quick_link'] = "IssueInternal_v";
break;
case 'External':
$data['content_view'] = "facility/facility_data/facility_issues/IssueExternal_v";
//$data['content_view'] = "IssueExternal_v";
$county = districts::get_county_id($this->session->userdata('district1'));
$data['district'] = districts::getAll();
$data['banner_text'] = "Redistribute Commodities";
$data['title'] = "Redistribute Commodities";
$data['quick_link'] = "IssueExternal_v";
break;
case 'Donation':
$data['title'] = "Update Stock Level: External source";
$data['content_view'] = "facility/facility_data/facility_issues/update_stock_donation_v";
$data['banner_text'] = "Update Stock Level: External source";
$data['drug_categories'] = Drug::getAll();
$data['quick_link'] = "update_stock_level";
break;
default:
$data['content_view'] = "facility/facility_data/facility_issues/issuesnRecpt";
$data['banner_text'] = "Issues Home";
$data['title'] = "Stock";
$data['quick_link'] = "issuenRecpt";
$data['popout'] = $pop_up_msg;
}
$data['service'] = Service::getall($facility);
$data['drugs'] = Facility_Stock::getAllStock($facility);
$this->load->view("template", $data);
}
public function user_create_multiple($facility_code = null)
{
//get user details in session
$identifier = $this->session->userdata('user_indicator');
$user_type_id = $this->session->userdata('user_type_id');
$district = $this->session->userdata('district_id');
$county = $this->session->userdata('county_id');
$facility = $this->session->userdata('facility_id');
//query to get user listing by type of user
switch ($identifier) {
case 'moh':
$permissions = 'moh_permissions';
$template = 'shared_files/template/dashboard_template_v';
break;
case 'facility_admin':
$permissions = 'facilityadmin_permissions';
$data['listing'] = Users::get_user_list_facility($facility);
$template = 'shared_files/template/template';
break;
case 'district':
$permissions = 'district_permissions';
$data['listing'] = Users::get_user_list_district($district);
if ($facility_code == 0) {
$data['facilities'] = Facilities::getFacilities($district);
$facility_name = null;
$facility_banner_text = null;
$no_of_facilities = 0;
} else {
$data['facilities'] = Facilities::getFacilities_from_facility_code($facility_code);
$facility_banner_text = ' to: ' . $data['facilities'][0]['facility_name'];
$facility_name = $data['facilities'][0]['facility_name'];
$no_of_facilities = 1;
}
$data['counts'] = Users::get_users_district($district);
$template = 'shared_files/template/template';
break;
case 'moh_user':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/dashboard_template_v';
break;
case 'district_tech':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/template';
break;
case 'rtk_manager':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/template';
break;
case 'super_admin':
$permissions = 'super_permissions';
$data['title'] = "Users";
$data['content_view'] = "Admin/users_v";
$data['listing'] = Users::get_user_list_all();
$data['counts'] = Users::get_users_count();
$data['counties'] = Counties::getAll();
$template = 'shared_files/template/dashboard_v';
break;
case 'allocation_committee':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/template';
break;
case 'county':
$permissions = 'county_permissions';
if ($facility_code == 0) {
$data['facilities'] = Facilities::getFacilities($district);
$facility_name = null;
$facility_banner_text = null;
$district_name = null;
$district_id = null;
$no_of_facilities = 0;
} else {
$data['facilities'] = Facilities::getFacilities_from_facility_code($facility_code);
$facility_banner_text = ' to: ' . $data['facilities'][0]['facility_name'];
$facility_name = $data['facilities'][0]['facility_name'];
$district_id = $data['facilities'][0]['district'];
$district_data = Districts::get_district_name($district_id);
$district_name = $district_data[0]['district'];
$no_of_facilities = 1;
}
$data['listing'] = Users::get_user_list_county($county);
$data['district_data'] = districts::getDistrict($county);
$data['counts'] = Users::get_users_county($county);
$template = 'shared_files/template/template';
break;
}
$data['title'] = "Add Multiple Users";
$data['user_types'] = Access_level::get_access_levels($permissions);
$data['banner_text'] = "Multiple User Addition";
$data['facility_name'] = $facility_name;
$data['facility_code'] = $facility_code;
$data['facility_banner_text'] = $facility_banner_text;
$data['district_name'] = $district_name;
$data['district_id'] = $district_id;
$data['no_of_facilities'] = $no_of_facilities;
$data['content_view'] = "shared_files/add_users_multiple";
$this->load->view($template, $data);
}
public function compare_counties($county1, $county2, $month = null, $year = null)
{
if ($month == null) {
$year = date('Y', time());
$month = date('m', time());
} else {
$year = $year;
$month = $month;
}
$current_month_year = $year . '-' . $month . '-01';
$period = $month . $year;
$month_prev = date('Y-m-d', strtotime("last month", strtotime($current_month_year)));
$month_prev = date('m', strtotime("{$month_prev}"));
//echo "$month_prev";die();
$year_prev = date('Y', strtotime("last month", strtotime($current_month_year)));
$period_prev = $month_prev . $year_prev;
$previous_month_year = date('Y-m-d', strtotime("last month", strtotime($current_month_year)));
$month_prev1 = date('m', strtotime("last month", strtotime($previous_month_year)));
$year_prev1 = date('Y', strtotime("last month", strtotime($previous_month_year)));
$period_prev1 = $month_prev1 . $year_prev1;
$period = $month . '' . $year;
$county_one_current = $this->_requested_vs_allocated($year, $month, $county1);
$county_one_previous = $this->_requested_vs_allocated($year_prev, $month_prev, $county1);
$county_one_previous1 = $this->_requested_vs_allocated($year_prev1, $month_prev1, $county1);
$county_two_current = $this->_requested_vs_allocated($year, $month, $county2);
$county_two_previous = $this->_requested_vs_allocated($year_prev, $month_prev, $county2);
$county_two_previous1 = $this->_requested_vs_allocated($year_prev1, $month_prev1, $county2);
$districts_in_county1 = count(districts::getDistrict($county1));
$districts_in_county2 = count(districts::getDistrict($county2));
$county1_facilities = count($this->_facilities_in_county($county1));
$county2_facilities = count($this->_facilities_in_county($county2));
$total_districts = array($districts_in_county1, $districts_in_county2);
$total_facilities = array($county1_facilities, $county2_facilities);
$returnable = array('Period' => $period, 'district_totals' => $total_districts, 'facility_totals' => $total_facilities, 'first_county' => $county_one_current, 'second_county' => $county_two_current);
$returnable_prev = array('Period' => $period_prev, 'district_totals' => $total_districts, 'facility_totals' => $total_facilities, 'first_county' => $county_one_previous, 'second_county' => $county_two_previous);
$returnable_prev1 = array('Period' => $period_prev1, 'district_totals' => $total_districts, 'facility_totals' => $total_facilities, 'first_county' => $county_one_previous1, 'second_county' => $county_two_previous1);
$returnable_final = array('Current Month' => $returnable, 'Previous Month' => $returnable_prev, 'Two Months Ago' => $returnable_prev1);
//$stats = $this->_county_allocation_stats($county1);
return $returnable_final;
}
public function password_recovery()
{
$email = $_POST['username'];
if ($email != NULL) {
$password = '******';
$mycount = User::check_user_exist($email);
if ($mycount > 0) {
$account_details = User::get_user_details($email)->toArray();
$access_level = access_level::get_access_level_name($account_details[0]['usertype_id']);
$access_level = $access_level['level'];
switch ($account_details[0]['usertype_id']) {
case 2:
$facility_name = facilities::get_facility_name_($account_details[0]['facility']);
$user_delegation = "Facility: {$facility_name['facility_name']}";
$user_level = "Facility Level";
break;
case 5:
$facility_name = facilities::get_facility_name_($account_details[0]['facility']);
$user_delegation = "Facility: {$facility_name['facility_name']}";
$user_level = "Facility Level";
break;
case 3:
$district_name = districts::get_district_name_($account_details[0]['district']);
$user_level = "District Level";
$user_delegation = "District: {$district_name['district']}";
break;
default:
break;
}
$subject = "Password reset";
$message = 'Hello ' . $account_details[0]['fname'] . 'you requested for a password reset check you email address for more details (HCMP)';
$message_1 = 'Hello ' . $account_details[0]['fname'] . ', <br> <br> You requested for a password reset on the Health Commodities Management Platform (HCMP).
<br>
<br>
Web link: http://health-cmp.or.ke/
<br>
<br>
Please find your log in credentials below:
<br>
<br>
' . $user_delegation . '
<br>
User Level: ' . $user_level . '
<br>
User Type: ' . $access_level . '
<br>
User Name: ' . $email . '
<br>
Password: '******'
<br>
<br>';
//hash then reset password
$salt = '#*seCrEt!@-*%';
$value = md5($salt . $password);
$updatep = Doctrine_Manager::getInstance()->getCurrentConnection();
$updatep->execute("UPDATE user SET password='******' WHERE username='******' or email='{$email}'; ");
//send mail
$response = $this->send_email($email, $message_1, $subject);
$this->send_sms($account_details[0]['telephone'], $message);
$data['email'] = $email;
$data['popup'] = "Successpopup";
$this->load->view("login_v", $data);
} else {
$data['popup'] = "errorpopup";
$this->load->view("forgotpassword_v", $data);
}
} else {
$data['popup'] = "errorpopup";
$this->load->view("forgotpassword_v", $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 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 district_store_internal()
{
//THIS FUNCTION VANISHES WHEN A COLLINS RELATED PULL GOES DOWN
//#collins_repo #my_function_my_choice #hahahaha # this was some random thing so that i can commit this function. Cheers
$district_id = $this->session->userdata('district_id');
$dist = districts::get_district_name_($district_id);
$data['district_id'] = $this->session->userdata('district_id');
$data['district_data'] = districts::get_district_name_($district_id);
$data['content_view'] = "subcounty/drug_store/drug_store_internal";
$data['donate_destination'] = "facility";
$data['subcounties'] = districts::getAll();
$data['banner_text'] = "Redistribute Commodities to District Stores";
$data['title'] = "Redistribute Commodities";
//$data['service_point']=service_points::get_all_active($facility_code);
$data['commodities'] = facility_stocks::get_distinct_stocks_for_this_district_store($district_id, 1);
// echo "<pre>";print_r($data['commodities']);echo "</pre>";exit;
$data['facility_stock_data'] = json_encode(facility_stocks::get_distinct_stocks_for_this_district_store($district_id, "batch_data"));
$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 send_system_usage_specific($county_id = NULL, $district_id = NULL, $facility_code = NULL, $listing_email_address = NULL)
{
//Karsan
$time = date('M , d Y');
$county_name = counties::get_county_name($county_id);
$district_name = districts::get_district_name_($district_id);
$cp_email_query = "SELECT * FROM email_listing_new WHERE county = {$county_id} AND usertype = 10";
$cp_emails = $this->db->query($cp_email_query)->result_array();
$scp_email_query = "SELECT * FROM email_listing_new WHERE county = {$county_id} AND usertype = 3";
$scp_emails = $this->db->query($scp_email_query)->result_array();
// echo "<pre>";print_r($cp_emails);exit;
//CP
$cp_emails_str = '';
foreach ($cp_emails as $emails => $value) {
$cp_emails_str = $cp_emails_str . ',' . $value['email'];
// echo "<pre>";print_r($value['email']);exit;
}
$cp_emails_str = ltrim($cp_emails_str, ',');
//SCP
$scp_emails_str = '';
foreach ($scp_emails as $emails => $value) {
$scp_emails_str = $scp_emails_str . ',' . $value['email'];
// echo "<pre>";print_r($value['email']);exit;
}
$scp_emails_str = ltrim($scp_emails_str, ',');
// echo $scp_emails_str;exit;
// echo "<pre>";print_r($scp_emails);exit;
// echo "<pre>";print_r($district_name);
// echo "<pre>OVER ";
$appended = !empty($district_name) ? $district_name['district'] . " Subcounty" : $county_name['county'] . " County";
$excel_title = 'HCMP facility activity log summary for ' . $appended;
$email_subject = 'HCMP System usage report for ' . $appended . ' as at ' . $time;
// echo $county_name.' '.$district_name;
// echo $excel_title;exit;
$active_facilities = Facilities::getAll_($county_id, $district_id);
// echo "<pre>";print_r($active_facilities);echo "</pre>";exit;
$last_seen = Facilities::get_facility_data_specific(NULL, $county_id, $district_id, $facility_code, 'all');
$last_issued = Facilities::get_facility_data_specific('last_issued', $county_id, $district_id, $facility_code, 'all');
$last_ordered = Facilities::get_facility_data_specific('last_ordered', $county_id, $district_id, $facility_code, 'all');
$decommissioned = Facilities::get_facility_data_specific('last_decommissioned', $county_id, $district_id, $facility_code, 'all');
$redistributed = Facilities::get_facility_data_specific('last_redistributed', $county_id, $district_id, $facility_code, 'all');
$added_stock = Facilities::get_facility_data_specific('last_added_stock', $county_id, $district_id, $facility_code, 'all');
// $all_faciliteis = Facilities::getAll_();
// echo "<pre>";print_r($active_facilities);echo "</pre>";exit;
$facility_count = empty($active_facilities) ? NULL : count($active_facilities);
$final_array = array();
$last_seen_count = count($last_seen);
$last_issued_count = count($last_issued);
$last_ordered_count = count($last_ordered);
$decommissioned_count = count($decommissioned);
$redistributed_count = count($redistributed);
$added_stock_count = count($added_stock);
foreach ($active_facilities as $a_c) {
$final_array[] = array('Facility Name' => $a_c['facility_name'], 'Facility Code' => $a_c['facility_code'], 'County' => $a_c['county'], 'Sub-County' => $a_c['subcounty']);
}
//active_facilities foreach
$final_array_count = count($final_array);
$last_seen_time = NULL;
$last_issued_time = NULL;
$last_order_time = NULL;
$last_deccommissioned_time = NULL;
$last_redistributed_time = NULL;
$last_added_stock_time = NULL;
foreach ($final_array as $keyy => $value) {
foreach ($last_seen as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $last_seen[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($last_seen[$key]['last_seen'] > $last_seen_time) {
$last_seen_time = $last_seen[$key]['last_seen'];
$days_last_seen = $last_seen[$key]['difference_in_days'];
// echo "<pre>".$last_seen_time;
}
$final_array[$keyy]['Date Last Seen'] = $last_seen_time;
$final_array[$keyy]['Days From Last Seen'] = $days_last_seen;
// echo "<pre>".$last_seen_time;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_seen_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
// echo "<pre>";print_r($final_array);echo "</pre>";exit;
//last issued time
foreach ($final_array as $keyy => $value) {
foreach ($last_issued as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $last_issued[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($last_issued[$key]['last_seen'] > $last_issued_time) {
$last_issued_time = $last_issued[$key]['last_seen'];
$days = $last_issued[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Issued'] = $last_issued_time;
$final_array[$keyy]['Days From Last Issue'] = $days;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_issued_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
//last ordered
foreach ($final_array as $keyy => $value) {
foreach ($last_ordered as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $last_ordered[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($last_ordered[$key]['last_seen'] > $last_order_time) {
$last_order_time = $last_ordered[$key]['last_seen'];
$days_last_ordered = $last_ordered[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Ordered'] = $last_order_time;
$final_array[$keyy]['Days From Last Order'] = $days_last_ordered;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_order_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
foreach ($final_array as $keyy => $value) {
foreach ($decommissioned as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $decommissioned[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($decommissioned[$key]['last_seen'] > $last_deccommissioned_time) {
$last_deccommissioned_time = $decommissioned[$key]['last_seen'];
$days_last_decommissioned = $decommissioned[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Decommissioned'] = $last_deccommissioned_time;
$final_array[$keyy]['Days From Last Decommissioned'] = $days_last_decommissioned;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_deccommissioned_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
foreach ($final_array as $keyy => $value) {
foreach ($redistributed as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $redistributed[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($redistributed[$key]['last_seen'] > $last_redistributed_time) {
$last_redistributed_time = $redistributed[$key]['last_seen'];
$days_last_redistributed = $redistributed[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Redistributed'] = $last_redistributed_time;
$final_array[$keyy]['Days From Last Redistributed'] = $days_last_redistributed;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_redistributed_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
foreach ($final_array as $keyy => $value) {
foreach ($added_stock as $key => $value) {
if ($final_array[$keyy]['Facility Code'] == $added_stock[$key]['facility_code']) {
// echo "<pre>".$last_ordered[$j]['last_seen']." ".$last_ordered[$j]['facility_code'];
if ($added_stock[$key]['last_seen'] > $last_added_stock_time) {
$last_added_stock_time = $added_stock[$key]['last_seen'];
$days_last_added_stock = $added_stock[$key]['difference_in_days'];
// echo "<pre>".$last_order_time;
}
$final_array[$keyy]['Date Last Received Order'] = $last_added_stock_time;
$final_array[$keyy]['Days From Last Received Order'] = $days_last_added_stock;
// $final_array[$i]['Days From Last Seen'] = abs($last_seen_time - $now);
$last_added_stock_time = NULL;
}
//end of facility code if
}
//end of last seen foreach
}
// echo "<pre>";print_r($final_array);exit;
$row_data = array();
$counterrrr = 0;
foreach ($final_array as $facility) {
// echo "<pre>". $counterrrr . "</pre>";
// $counterrrr = $counterrrr + 1;
//random code to allow for commit
$issue_date = isset($facility['Date Last Issued']) ? date('Y-m-d', strtotime($facility['Date Last Issued'])) : "No Data Available";
$last_seen = isset($facility['Date Last Seen']) ? date('Y-m-d', strtotime($facility['Date Last Seen'])) : "No Data Available";
$redistribution = isset($facility['Date Last Redistributed']) ? date('Y-m-d', strtotime($facility['Date Last Redistributed'])) : "No Data Available";
$order_date = isset($facility['Date Last Ordered']) ? date('Y-m-d', strtotime($facility['Date Last Ordered'])) : "No Data Available";
$decommission_date = isset($facility['Date Last Decommissioned']) ? date('Y-m-d', strtotime($facility['Date Last Decommissioned'])) : "No Data Available";
$date_order = isset($facility['Date Last Received Order']) ? date('Y-m-d', strtotime($facility['Date Last Received Order'])) : "No Data Available";
$days_from_last_seen = isset($facility['Days From Last Seen']) ? $facility['Days From Last Seen'] : ' - ';
$days_from_last_issued = isset($facility['Days From Last Issue']) ? $facility['Days From Last Issue'] : ' - ';
$days_from_last_redist = isset($facility['Days From Last Redistributed']) ? $facility['Days From Last Redistributed'] : ' - ';
$days_from_last_ordered = isset($facility['Days From Last Order']) ? $facility['Days From Last Order'] : ' - ';
$decomissioned_days = isset($facility['Days From Last Decommissioned']) ? $facility['Days From Last Decommissioned'] : ' - ';
$days_from_last_recieved = isset($facility['Days From Last Received Order']) ? $facility['Days From Last Received Order'] : ' - ';
array_push($row_data, array($facility['Facility Name'], $facility['Facility Code'], $facility['Sub-County'], $facility['County'], $last_seen, $days_from_last_seen, $issue_date, $days_from_last_issued, $redistribution, $days_from_last_redist, $order_date, $days_from_last_ordered, $decommission_date, $decomissioned_days, $date_order, $days_from_last_recieved));
}
$excel_data = array();
// $excel_data = array('doc_creator' => 'HCMP ', 'doc_title' => 'System Usage Breakdown ', 'file_name' => 'system usage breakdown');
$excel_data = array('doc_creator' => 'HCMP-Kenya', 'doc_title' => $excel_title, 'file_name' => 'HCMP_Facility_Activity_Log_Summary_as_at_' . $time);
$column_data = array("Facility Name", "Facility Code", "Sub County", "County", "Date Last Logged In", "Days From Last Log In", "Date Last Issued", "Days From Last Issue", "Date Last Redistributed", "Days From Last Redistribution", "Date Last Ordered", "Days From Last Order", "Date Last Decommissioned", "Days From Last Decommission", "Date Last Received Order", "Days From Last Stock Addition");
$excel_data['column_data'] = $column_data;
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = 'Log Summary';
// echo "<pre>";print_r($excel_data);echo "</pre>";exit;
$res = $this->hcmp_functions->create_excel($excel_data);
// echo 'This '.$res;exit;
$message = '';
$message .= "<style> table {\n\t\t\tborder-collapse: collapse; \n\t\t}td,th{\n\t\t\tpadding: 12px;\n\t\t\ttext-align:center;\n\t\t}\n\n\t\t*{margin:0;padding:0}*{font-family:'Helvetica Neue',Helvetica,Helvetica,Arial,sans-serif}img{max-width:100%}.collapse{padding:0}body{-webkit-font-smoothing:antialiased;-webkit-text-size-adjust:none;width:100%!important;height:100%}a{color:#2BA6CB}.btn{text-decoration:none;color:#FFF;background-color:#666;padding:10px 16px;font-weight:700;margin-right:10px;text-align:center;cursor:pointer;display:inline-block}p.callout{padding:15px;background-color:#ECF8FF;margin-bottom:15px}.callout a{font-weight:700;color:#2BA6CB}table.social{background-color:#ebebeb}.social .soc-btn{padding:3px 7px;font-size:12px;margin-bottom:10px;text-decoration:none;color:#FFF;font-weight:700;display:block;text-align:center}a.fb{background-color:#3B5998!important}a.tw{background-color:#1daced!important}a.gp{background-color:#DB4A39!important}a.ms{background-color:#000!important}.sidebar .soc-btn{display:block;width:100%}table.head-wrap{width:100%}.header.container table td.logo{padding:15px}.header.container table td.label{padding:15px 15px 15px 0}table.body-wrap{width:100%}table.footer-wrap{width:100%;clear:both!important}.footer-wrap .container td.content p{border-top:1px solid #d7d7d7;padding-top:15px;font-size:9px;font-weight:500}h1,h2,h3,h4,h5,h6{font-family:HelveticaNeue-Light,'Helvetica Neue Light','Helvetica Neue',Helvetica,Arial,'Lucida Grande',sans-serif;line-height:1.1;margin-bottom:15px;color:#000}h1 small,h2 small,h3 small,h4 small,h5 small,h6 small{font-size:60%;color:#6f6f6f;line-height:0;text-transform:none}h1{font-weight:200;font-size:44px}h2{font-weight:200;font-size:37px}h3{font-weight:500;font-size:27px}h4{font-weight:500;font-size:23px}h5{font-weight:900;font-size:17px}h6{font-weight:900;font-size:14px;text-transform:uppercase;color:#444}.collapse{margin:0!important}p,ul{margin-bottom:10px;font-weight:400;font-size:14px;line-height:1.6}p.lead{font-size:17px}p.last{margin-bottom:0}ul li{margin-left:5px;list-style-position:inside}ul.sidebar{background:#ebebeb;display:block;list-style-type:none}ul.sidebar li{display:block;margin:0}ul.sidebar li a{text-decoration:none;color:#666;padding:10px 16px;cursor:pointer;border-bottom:1px solid #777;border-top:1px solid #FFF;display:block;margin:0}ul.sidebar li a.last{border-bottom-width:0}ul.sidebar li a h1,ul.sidebar li a h2,ul.sidebar li a h3,ul.sidebar li a h4,ul.sidebar li a h5,ul.sidebar li a h6,ul.sidebar li a p{margin-bottom:0!important}.container{display:block!important;max-width:100%!important;margin:0 auto!important;clear:both!important}.content{padding:15px;max-width:80%px;margin:0 auto;display:block}.content table{width:100%}.column{width:300px;float:left}.column tr td{padding:15px}.column-wrap{padding:0!important;margin:0 auto;max-width:600px!important}.column table{width:100%}.social .column{width:280px;min-width:279px;float:left}.clear{display:block;clear:both}@media only screen and (max-width:600px){a[class=btn]{display:block!important;margin-bottom:10px!important;background-image:none!important;margin-right:0!important}div[class=column]{width:auto!important;float:none!important}table.social div[class=column]{width:auto!important}}</style>";
$message .= '
<tr>
<td colspan="12">
</tr>
</tbody>
</table>';
$message .= "<!-- BODY -->\n\t\t<table class='body-wrap'>\n\t\t\t<tr>\n\t\t\t\t<td></td>\n\t\t\t\t<td class='container' bgcolor='#FFFFFF'>\n\n\t\t\t\t\t<div class='content'>\n\t\t\t\t\t\t<table>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td>\n\n\t\t\t\t\t\t\t\t\t<p class='lead'>Find attached a summary of Facility Activity Log, as at {$time}</p>\n\n\t\t\t\t\t\t\t\t\t<table class='social' width='100%'>\n\t\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t\t<td>\n\n\t\t\t\t\t\t\t\t\t\t\t\t<!-- column 1 -->\n\t\t\t\t\t\t\t\t\t\t\t\t<table align='left' class='column'>\n\n\t\t\t\t\t\t\t\t\t\t\t\t</table><!-- /column 1 -->\t\n\n\t\t\t\t\t\t\t\t\t\t\t\t<!-- column 2 -->\n\t\t\t\t\t\t\t\t\t\t\t\t<table align='left' class='column'>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<tr>\n\n\t\t\t\t\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t\t\t\t</table><!-- /column 2 -->\n\n\t\t\t\t\t\t\t\t\t\t\t\t<span class='clear'></span>\t\n\n\t\t\t\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t</table><!-- /social & contact -->\n\n\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t</table>\n\t\t\t\t\t</div><!-- /content -->\n\n\t\t\t\t</td>\n\t\t\t\t<td></td>\n\t\t\t</tr>\n\t\t</table><!-- /BODY -->";
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
// $subject = $excel_title." as at ".$time;
// $email_address = "smutheu@clintonhealthaccess.org,sethrichard40@gmail.com,ttunduny@gmail.com,teddyodera@gmail.com";
// $email_address = $listing_email_address;
$email_address = $cp_emails_str;
$cc_email = $scp_emails_str;
$bcc_email = "smutheu@clintonhealthaccess.org,karsanrichard@gmail.com,ttunduny@gmail.com,teddyodera@gmail.com,ronohb@gmail.com,odiwuorybrian@gmail.com,mwakiojoy@gmail.com,kevgithuka@gmail.com,margie.odora@gmail.com,kiganyastephenthua@gmail.com";
// $email_address = "*****@*****.**";
// $bcc_email = "";
// $cc_email = "";
// $email_address = "karsanrichard@gmail.com,ttunduny@gmail.com";
// $email_address = "*****@*****.**";
//$bcc = "";
if (!empty($facility_count) && $facility_count > 0) {
$status = $this->hcmp_functions->send_email($email_address, $message, $email_subject, $handler, $bcc_email, $cc_email);
}
// echo "I work till here";exit;
// echo "<pre>";print_r($status);exit;
// redirect('sms/new_weekly_usage');
}
public function facility_profile($mfl)
{
$data = array();
$lastday = date('Y-m-d', strtotime("last day of previous month"));
$County = $this->session->userdata('county_name');
$Countyid = $this->session->userdata('county_id');
$districts = districts::getDistrict($Countyid);
$sql = "select * from facilities where facility_code={$mfl}";
$facility = $this->db->query($sql)->result_array();
$mfl = $facility[0]['facility_code'];
$data['reports'] = $this->_monthly_facility_reports($mfl);
$data['facility_county'] = $data['reports'][0]['county'];
$data['facility_district'] = $data['reports'][0]['district'];
$data['district_id'] = $data['reports'][0]['district_id'];
$data['facilities_in_district'] = json_encode($this->_facilities_in_district($data['district_id']));
$data['facilities_in_district'] = str_replace('"', "'", $data['facilities_in_district']);
$data['county_id'] = $data['reports'][0]['county_id'];
$data['districts'] = $districts;
$data['county'] = $County;
$data['mfl'] = $mfl;
$data['countyid'] = $Countyid;
$data['title'] = $facility[0]['facility_name'] . '-' . $mfl;
$data['facility_name'] = $facility[0]['facility_name'];
$data['banner_text'] = 'Facility Profile: ' . $facility[0]['facility_name'] . '-' . $mfl;
$data['content_view'] = "rtk/rtk/shared/facility_profile_view";
$this->load->view("rtk/template", $data);
}
public function get_county_district_access_list()
{
$first_day_of_the_month = date("Y-m-1", strtotime("this month"));
$last_day_of_the_month = date("Y-m-t", strtotime("this month"));
$date_1 = new DateTime($first_day_of_the_month);
$date_2 = new DateTime($last_day_of_the_month);
$county_id = $this->session->userdata('county_id');
$district_data = districts::getDistrict($county_id);
$series_data = array();
$category_data = array();
$interval = $date_1->diff($date_2);
for ($i = 0; $i < $interval->d; $i++) {
$day = 1 + $i;
$new_date = date("Y") . "-" . date("m") . "-" . $day;
$new_date = date('Y-m-d', strtotime($new_date));
if (date('N', strtotime($new_date)) < 6) {
//echo $new_date."<BR>";
$date_ = date('D d', strtotime($new_date));
$category_data = array_merge($category_data, array($date_));
$temp_1 = array();
foreach ($district_data as $district_) {
$district_id = $district_->id;
$district_name = $district_->district;
$county_data = Log::get_county_login_count($county_id, $district_id, $new_date);
array_key_exists($district_name, $series_data) ? $series_data[$district_name] = array_merge($series_data[$district_name], array((int) $county_data[0]['total'])) : ($series_data = array_merge($series_data, array($district_name => array((int) $county_data[0]['total']))));
}
} else {
// do nothing
}
}
$string = null;
print_r($series_data);
exit;
foreach ($series_data as $key => $raw_data) {
/*$string .="{
name: '$key',
data:[
";*/
print_r($raw_data);
exit;
/* foreach ($raw_data as $key_data):
$string .="$key_data,";
endforeach;
$string .="]},";*/
}
echo $string;
}
public function user_create()
{
//get user details in session
$identifier = $this->session->userdata('user_indicator');
$user_type_id = $this->session->userdata('user_type_id');
$district = $this->session->userdata('district_id');
$county = $this->session->userdata('county_id');
$facility = $this->session->userdata('facility_id');
//query to get user listing by type of user
switch ($identifier) {
case 'moh':
$permissions = 'moh_permissions';
$template = 'shared_files/template/dashboard_template_v';
break;
case 'facility_admin':
$permissions = 'facilityadmin_permissions';
$data['listing'] = Users::get_user_list_facility($facility);
$template = 'shared_files/template/template';
break;
case 'district':
$permissions = 'district_permissions';
$data['listing'] = Users::get_user_list_district($district);
$data['facilities'] = Facilities::getFacilities($district);
$data['counts'] = Users::get_users_district($district);
$template = 'shared_files/template/template';
break;
case 'moh_user':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/dashboard_template_v';
break;
case 'district_tech':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/template';
break;
case 'rtk_manager':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/template';
break;
case 'super_admin':
$permissions = 'super_permissions';
$data['title'] = "Users";
$data['content_view'] = "Admin/users_v";
$data['listing'] = Users::get_user_list_all();
$data['counts'] = Users::get_users_count();
$data['counties'] = Counties::getAll();
$template = 'shared_files/template/dashboard_v';
break;
case 'allocation_committee':
$data['listing'] = Users::get_user_list($user_type_id);
$template = 'shared_files/template/template';
break;
case 'county':
$permissions = 'county_permissions';
$data['listing'] = Users::get_user_list_county($county);
$data['district_data'] = districts::getDistrict($county);
$data['counts'] = Users::get_users_county($county);
$template = 'shared_files/template/template';
break;
}
$data['title'] = "User Management";
$data['user_types'] = Access_level::get_access_levels($permissions);
$data['banner_text'] = "User Management";
$data['content_view'] = "shared_files/user_creation_v";
$this->load->view($template, $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 get_district_facility_mapping_($district_id)
{
$facility_data = facilities::getFacilities($district_id);
$dpp_details = Users::get_dpp_details($district_id)->toArray();
$district_name = districts::getDistrict(null, $district_id)->toArray();
$table_body = "";
$dpp_fname = '';
$dpp_lname = '';
$dpp_phone = '';
$dpp_email = '';
$indicator = "SubCounty";
$no_of_facility_users = 0;
$no_of_facility_users_online = 0;
$no_of_facilities = 0;
$no_of_facilities_using = 0;
$no_of_facilities_using_targetted = 0;
$series_data = $graph_data = array();
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'];
}
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;
$using = $facility_detail->using_hcmp;
$status_radio = $facility_detail->targetted == 1 ? 'checked="true"' : null;
$using == 1 ? $no_of_facilities_using = $no_of_facilities_using + 1 : ($status = null);
$temp = $facility_extra_data[0]['status'];
$status_using = $using == 1 ? 'checked="true"' : null;
$a_date = strtotime($facility_detail->date_of_activation) ? date('d M, Y', strtotime($facility_detail->date_of_activation)) : "N/A";
$using == 1 ? $status = "<span class='label label-success'>Active</span>" : ($status = "<span class='label label-warning'>Inactive</span>");
$facility_detail->targetted == 1 ? $no_of_facilities_using_targetted = $no_of_facilities_using_targetted + 1 : null;
array_push($series_data, array($district_name[0]['district'], $facility_detail->facility_name, $facility_code, $facility_detail->owner, $status, "<input id='{$facility_detail->id}' type='checkbox' name='targetted' class='checkbox' {$status_radio}/>", "<input id='{$facility_detail->id}' name='using_hcmp' type='checkbox' class='checkbox' {$status_using}/>", $a_date, $facility_extra_data[0]['number_of_users']));
}
$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</label></td>
<td><a class="badge" >' . $no_of_facilities . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Total No of Facilities Targeted</label></td>
<td> <a class="badge">' . $no_of_facilities_using_targetted . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Total No of Facilities Using HCMP </label></td>
<td> <a class="badge">' . $no_of_facilities_using . '</a></td>
</tr>
<tr>
<td><label style="font-weight: ">Total No of Users</label></td>
<td><a class="badge" >' . $no_of_facility_users . '</a></td>
</tr>
</table>
</br><p>';
$category_data = array(array("Sub County", 'Facility Name', "MLF No", "Owner", "Facility Status", "Targeted For Roll Out", "Using HCMP", "Date Activated", "No. Facility Users"));
$graph_data = array_merge($graph_data, array("table_id" => 'dem_graph_1'));
$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_1";
$data['stats_data'] = $stats_data;
return $this->load->view("shared_files/report_templates/data_table_template_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);
}
}
