public function consumption_stats_graph($commodity_id = null, $category_id = null, $district_id = null, $facility_code = null, $option = null, $from = null, $to = null, $report_type = null, $tracer = null)
{
//reset the values here
// echo $report_type;exit;
$commodity_id = $commodity_id == "NULL" ? null : $commodity_id;
$district_id = isset($district_id) && $district_id ? $district_id : $this->session->userdata('district_id');
//$district_id = ($district_id=="NULL") ? null :$district_id;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$option = $option == "NULL" || $option == "null" ? null : $option;
// $from = ($from == "NULL") ? strtotime(date('01-m-y')) : strtotime(urldecode($from));
// $to = ($to == "NULL") ? strtotime(date('d-m-y')) : strtotime(urldecode($to));
// echo "$from";die;
$from = $from == "NULL" ? strtotime(date('Y-m-01')) : strtotime(urldecode($from));
$to = $to == "NULL" ? strtotime(date('Y-m-d')) : strtotime(urldecode($to));
$category_id = $category_id == "NULL" ? null : $category_id;
$county_id = $this->session->userdata('county_id');
$county_name = counties::get_county_name($county_id);
$category_data = $series_data = $graph_data = $series_data_ = array();
//check if the district is set
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " subcounty" : null;
$option_new = isset($option) ? $option : "packs";
$facility_code_ = isset($facility_code) ? facilities::get_facility_name_($facility_code)->toArray() : null;
$facility_name = $facility_code_[0]['facility_name'];
$commodity_name = isset($commodity_id) ? Commodities::get_details($commodity_id)->toArray() : null;
$category_name_ = @$commodity_name[0]['commodity_name'];
$commodity_name = isset($category_name_) ? " for " . $category_name_ : null;
$title = isset($facility_code) && isset($district_id) ? "{$district_name_} : {$facility_name}" : ($district_id > 0 && !isset($facility_code) ? "{$district_name_}" : "{$county_name['county']} county");
$time = "between " . date('j M y', $from) . " and " . date('j M y', $to);
$consumption_data = Facility_stocks::get_county_consumption_level_new($facility_code, $district_id, $county_id, $category_id, $commodity_id, $option_new, $from, $to, $report_type, $tracer);
// echo "<pre>";
// print_r($consumption_data);die;
foreach ($consumption_data as $data) {
if ($report_type == "table_data") {
if ($commodity_id > 0) {
array_push($series_data, array($data['district'], $data["facility_name"], $data["facility_code"], $data['commodity'], (int) $data['total']));
} else {
array_push($series_data, array($data["name"], $data['commodity'], (int) $data['total']));
}
} else {
$series_data = array_merge($series_data, array((int) $data['total']));
$series_data_ = array_merge($series_data_, array(array($data["district"], $data["facility_name"], $data['commodity_name'], (int) $data['total'])));
$category_data = array_merge($category_data, array($data["name"]));
}
//
}
if ($report_type == "csv_data") {
//seth
$excel_data = array('doc_creator' => $this->session->userdata('full_name'), 'doc_title' => "Consumption level {$commodity_name} {$title} {$time}", 'file_name' => "consumption_level_{$commodity_name_}{$title_}{$time}");
$row_data = array();
$column_data = array("District", "Facility", "Commodity", "Consumption Level in {$option_new}");
$excel_data['column_data'] = $column_data;
$row_data = array_merge($row_data, $series_data_);
//echo "<pre>";print_r($row_data);echo "</pre>";exit;
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
//echo $commodity_name.$title.$time;exit;
} elseif ($report_type == "table_data") {
if ($commodity_id > 0) {
$category_data = array(array("Sub-county", "Facility Name", "Mfl", "Commodity Name", "TOTAL " . $option_new));
} else {
// array_push($category_data, array("Commodity Name", "stocks worth in $option_new"));
array_push($category_data, array("Consumption level {$commodity_name} {$title} {$time}", "Commodity Name", "stocks worth in {$option_new}"));
}
$graph_data = array_merge($graph_data, array("table_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("table_header" => $category_data));
$graph_data = array_merge($graph_data, array("table_body" => $series_data));
$data['table'] = $this->hcmp_functions->create_data_table($graph_data);
$data['table_id'] = "dem_graph_";
return $this->load->view("shared_files/report_templates/data_table_template_v", $data);
} else {
$graph_type = 'bar';
// $graph_type = 'column';
$graph_data = array_merge($graph_data, array("graph_id" => 'dem_graph_'));
$graph_data = array_merge($graph_data, array("graph_title" => "Consumption level {$commodity_name} {$title} {$time}"));
$graph_data = array_merge($graph_data, array("graph_type" => $graph_type));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => "Commodity Consumption level in {$option_new}"));
$graph_data = array_merge($graph_data, array("graph_categories" => $category_data));
$graph_data = array_merge($graph_data, array("series_data" => array('total' => $series_data)));
$data = array();
$data['graph_id'] = 'dem_graph_';
$data['high_graph'] = $this->hcmp_functions->create_high_chart_graph($graph_data);
return $this->load->view("shared_files/report_templates/high_charts_template_v", $data);
}
}
public function send_order_approval_email($message, $subject, $attach_file, $facility_code, $reject_order = null)
{
//set cc email as blank
$cc_email = "";
$bcc_email = "*****@*****.**";
/*
$bcc_email = 'kelvinmwas@gmail.com,smutheu@clintonhealthaccess.org,collinsojenge@gmail.com,tngugi@clintonhealthaccess.org,
bwariari@clintonhealthaccess.org,
amwaura@clintonhealthaccess.org,
eongute@clintonhealthaccess.org,
rkihoto@clintonhealthaccess.org';
*/
$data = facilities::get_facility_name_($facility_code)->toArray();
$data = $data[0];
if ($reject_order == "Rejected" || $reject_order == "Updated") {
$email_address = $this->get_facility_email($facility_code);
$cc_email .= $this->get_ddp_email($data['district']);
} else {
$email_address = 'shamim.kuppuswamy@kemsa.co.ke,
jmunyu@kemsa.co.ke,
imugada@kemsa.co.ke,
laban.okune@kemsa.co.ke,
samuel.wataku@kemsa.co.ke,';
$cc_email .= $this->get_ddp_email($data['district']);
$cc_email .= $this->get_facility_email($facility_code);
$cc_email .= $this->get_county_email($data['district']);
}
return $this->send_email(substr($email_address, 0, -1), $message, $subject, $attach_file, $bcc_email, substr($cc_email, 0, -1));
}
public function county_profile($county)
{
$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);
$facility = facilities::get_facility_name_($mfl);
$data['county'] = $County;
$data['countyid'] = $Countyid;
$data['title'] = 'Facility Profile: ' . $facility['facility_name'];
$data['banner_text'] = 'Facility Profile: ' . $facility['facility_name'];
$data['content_view'] = "rtk/rtk/county_profile_view";
$this->load->view("rtk/template", $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 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 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 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_order_approval_email($message, $subject, $attach_file, $facility_code, $reject_order = null)
{
$cc_email = "";
$data = facilities::get_facility_name_($facility_code)->toArray();
$data = $data[0];
if ($reject_order == "Rejected" || $reject_order == "Updated") {
$email_address = $this->get_facility_email($facility_code);
$cc_email .= $this->get_ddp_email($data['district']);
} else {
$email_address = $this->test_mode ? 'kelvinmwas@gmail.com,smutheu@clintonhealthaccess.org,collinsojenge@gmail.com,
' : 'shamim.kuppuswamy@kemsa.co.ke,
samuel.wataku@kemsa.co.ke,
jmunyu@kemsa.co.ke,
imugada@kemsa.co.ke,
laban.okune@kemsa.co.ke,
samuel.wataku@kemsa.co.ke,';
$cc_email .= $this->get_ddp_email($data['district']);
$cc_email .= $this->get_facility_email($facility_code);
$cc_email .= $this->get_county_email($data['district']);
}
return $this->send_email(substr($email_address, 0, -1), $message, $subject, $attach_file, null, substr($cc_email, 0, -1));
}
public function get_stock_status_ajax($option = NULL, $facility_code = NULL, $year = NULL)
{
$facility_code = $facility_code;
$district = $this->session->userdata('district1');
$county_id = $this->session->userdata('county_id');
$width = "100%";
$height = "100%";
if ($option == NULL) {
$commodity_array = facility_stock::get_district_stock_level($district);
} elseif ($option == "ajax-request_facility") {
$district_name = facilities::get_facility_name_($facility_code);
$title = $district_name["facility_name"];
$commodity_array = facility_stock::get_facility_stock_level($facility_code);
if (count($commodity_array) < 20) {
$width = "100%";
$height = "50%";
}
if (count($commodity_array) > 20 && count($commodity_array) < 40) {
$width = "100%";
$height = "100%";
}
if (count($commodity_array) > 50) {
$width = "100%";
$height = "400%";
}
$commodity_name = array();
$current_values = array();
$monthly_values = array();
foreach ($commodity_array as $data) {
array_push($commodity_name, $data['drug_name']);
array_push($current_values, isset($data['total']) ? (int) $data['total'] : (int) 0);
array_push($monthly_values, isset($data['consumption_level']) ? (int) $data['consumption_level'] : (int) 0);
}
$data['width'] = $width;
$data['height'] = $height;
$data['title_1'] = "Current Balance";
$data['title_2'] = "Average Monthly Consumption";
$data['commodity_name'] = stripslashes(json_encode($commodity_name));
$data['current_values'] = json_encode($current_values);
$data['monthly_values'] = json_encode($monthly_values);
return $this->load->view("facility/facility_reports/facility_commodity_stock_level_v", $data);
} elseif ($option == "ajax-request_county") {
$commodity_array = isset($facility_code) ? facility_stock::get_county_drug_stock_level($county_id, $facility_code) : facility_stock::get_county_drug_stock_level($county_id);
} elseif ($option == "ajax-request_drug") {
$commodity_array = facility_stock::get_district_drug_stock_level($district, $facility_code);
} elseif ($option == "consumption") {
$commodity_array = facility_stock::get_county_drug_consumption_level($county_id, $facility_code, $year);
}
if (count($commodity_array) < 20) {
$width = "100%";
$height = "50%";
}
if (count($commodity_array) > 20 && count($commodity_array) < 50) {
$width = "100%";
$height = "100%";
}
if (count($commodity_array) > 50) {
$width = "100%";
$height = "300%";
}
$data['width'] = $width;
$data['height'] = $height;
$data['facilities'] = Facilities::getFacilities($district);
$data['option'] = $option;
$data['facility_code'] = $facility_code;
$data['year_selection'] = $year;
$this->load->view("district/ajax_view/stock_status_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);
}
}