public static function update_issues_table($data_array)
{
$o = new facility_issues();
$o->fromArray($data_array);
$o->save();
return TRUE;
}
public function internal_issue()
{
//security check
if ($this->input->post('service_point')) {
$facility_code = $this->session->userdata('facility_id');
$service_points = array_values($this->input->post('service_point'));
$commodity_id = array_values($this->input->post('desc'));
$commodity_balance_before = array_values($this->input->post('commodity_balance'));
$facility_stock_id = array_values($this->input->post('facility_stock_id'));
$batch_no = array_values($this->input->post('batch_no'));
$expiry_date = array_values($this->input->post('expiry_date'));
$commodity_unit_of_issue = array_values($this->input->post('commodity_unit_of_issue'));
$quantity_issued = array_values($this->input->post('quantity_issued'));
$clone_datepicker_normal_limit_today = array_values($this->input->post('clone_datepicker_normal_limit_today'));
$total_units = array_values($this->input->post('total_units'));
$total_items = count($facility_stock_id);
print_r($total_units);
for ($i = 0; $i < $total_items; $i++) {
//compute the actual stock
$total_items_issues = $commodity_unit_of_issue[$i] == 'Pack_Size' ? $quantity_issued[$i] * $total_units[$i] : $quantity_issued[$i];
//prepare the issues data
$mydata = array('facility_code' => $facility_code, 's11_No' => 'internal issue', 'batch_no' => $batch_no[$i], 'commodity_id' => $commodity_id[$i], 'expiry_date' => date('y-m-d', strtotime($expiry_date[$i])), 'qty_issued' => $total_items_issues, 'issued_to' => $service_points[$i], 'balance_as_of' => $commodity_balance_before[$i], 'date_issued' => date('y-m-d', strtotime($clone_datepicker_normal_limit_today[$i])), 'issued_by' => $this->session->userdata('user_id'));
// update the issues table
facility_issues::update_issues_table($mydata);
// reduce the stock levels
$a = Doctrine_Manager::getInstance()->getCurrentConnection();
$a->execute("UPDATE `facility_stocks` SET `current_balance` = `current_balance`-{$total_items_issues} where id='{$facility_stock_id[$i]}'");
//update the transaction table here
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection();
$inserttransaction->execute(" UPDATE `facility_transaction_table` SET `total_issues` = `total_issues`+{$total_items_issues},\n\t\t\t`closing_stock`=`closing_stock`-{$total_items_issues}\n WHERE `commodity_id`= '{$commodity_id[$i]}' and status='1' and facility_code='{$facility_code}';");
}
//$user = $this -> session -> userdata('user_id');
//$user_action = "issue";
//Log::log_user_action($user, $user_action);
$this->session->set_flashdata('system_success_message', "You have issued {$total_items} item(s)");
redirect(home);
}
redirect(home);
}
public function facility_transaction_data($source = NULL)
{
$source = isset($source) ? $source : 'KEMSA';
//KEMSA by default
$facility_code = $this->session->userdata('facility_id');
$data['facility_stock_data'] = facility_transaction_table::get_all($facility_code);
$data['last_issued_data'] = facility_issues::get_last_time_facility_issued($facility_code);
$data['title'] = "Facility Stock Summary";
$data['content_view'] = "facility/facility_reports/facility_transaction_data_v";
$data['banner_text'] = "Facility Stock Summary";
$data['source'] = $source;
$this->load->view("shared_files/template/template", $data);
}
public function edit_facility_stock_data()
{
//security check
if ($this->input->post('id')) {
$facility_code = $this->session->userdata('facility_id');
$stock_id = $this->input->post('id');
$expiry_date = $this->input->post('expiry_date');
$batch_no = $this->input->post('batch_no');
$delete = $this->input->post('edit');
$manufacturer = $this->input->post('manufacturer');
$commodity_id = $this->input->post('commodity_id');
$commodity_balance_units = $this->input->post('commodity_balance_units');
for ($key = 0; $key < count($stock_id); $key++) {
if ($delete[$key] == 2) {
//check the total stock balance of the commodity
$facility_stock = facility_stocks::get_facility_commodity_total($facility_code, $commodity_id[$key]);
$commodity_balance = $commodity_balance_units[$key] * -1;
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("update facility_transaction_table t\n\t\t set t. `closing_stock`=`closing_stock`-{$commodity_balance_units[$key]},`adjustmentnve`={$commodity_balance}\n\t\t where t.facility_code='{$facility_code}' and t.commodity_id={$commodity_id[$key]} and t.status=1");
// prepare the data to save
$commodity_balance = $commodity_balance_units[$key] * -1;
$mydata = array('facility_code' => $facility_code, 's11_No' => 'Deleted Commodity', 'commodity_id' => $commodity_id[$key], 'batch_no' => $batch_no[$key], 'expiry_date' => date('y-m-d', strtotime(str_replace(",", " ", $expiry_date[$key]))), 'balance_as_of' => $facility_stock[0]['commodity_balance'], 'adjustmentnve' => $commodity_balance, 'date_issued' => date('y-m-d'), 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('user_id'));
// update the issues table
facility_issues::update_issues_table($mydata);
//delete the record
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("delete from facility_stocks where id={$stock_id[$key]}");
} else {
$myobj = Doctrine::getTable('facility_stocks')->find($stock_id[$key]);
$myobj->batch_no = $batch_no[$key];
$myobj->manufacture = $manufacturer[$key];
$myobj->expiry_date = date('y-m-d', strtotime(str_replace(",", " ", $expiry_date[$key])));
$myobj->save();
}
}
//$this-> hcmp_functions ->send_stock_update_sms();
$this->session->set_flashdata('system_success_message', "Facility Stock data has Been Updated");
redirect('reports/facility_stock_data');
}
redirect();
}
public function db_service_pts()
{
//Karsan
// $all_issues = facility_issues::get_all();
$alpha_data = facility_issues::get_all_issue_data();
$beta_data = facility_issues::get_all_service_points();
// echo "<pre>";print_r($beta_data);exit;
$alphacount = count($alpha_data);
$betacount = count($beta_data);
$omega_data = array();
$epsilon_data = array();
//for generic service points
for ($a = 0; $a < $alphacount; $a++) {
for ($b = 0; $b < $betacount; $b++) {
if ($alpha_data[$a]['issued_to'] == $beta_data[$b]['service_point_name'] && $alpha_data[$a]['facility_code'] == $beta_data[$b]['facility_code']) {
$omega_data[$a]['issue_id'] = $alpha_data[$a]['id'];
$omega_data[$a]['sp_id'] = $beta_data[$b]['id'];
}
}
for ($b = 0; $b < $betacount; $b++) {
if ($alpha_data[$a]['issued_to'] == $beta_data[$b]['service_point_name'] && $alpha_data[$a]['facility_code'] != $beta_data[$b]['facility_code']) {
$epsilon_data[$a]['issue_id'] = $alpha_data[$a]['id'];
$epsilon_data[$a]['sp_id'] = $beta_data[$b]['id'];
break;
echo "<pre>";
echo $beta_data[$b]['id'] . ' ' . $b;
echo "</pre>";
}
}
}
// echo "<pre>";print_r($omega_data);exit;
// echo "<pre>";print_r($epsilon_data);exit;
foreach ($omega_data as $omegakey => $omegavalue) {
$issue_id = $omegavalue['issue_id'];
$service_point_id = $omegavalue['sp_id'];
$updater = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("\n\t\t\t\tUPDATE facility_issues SET issued_to = {$service_point_id} WHERE id = {$issue_id}\n\t\t\t\t");
}
//end of foreach
$omega_affected = $this->db->affected_rows();
echo "OMEGA AFFECTED: " . $omega_affected;
echo "<br>THE -OMEGA- UPDATE WAS SUCCESSFUL. </br>-EPSILON DATA- UPDATE COMMENCING</br>";
foreach ($epsilon_data as $epsilonkey => $epsilonvalue) {
$issue_id = $epsilonvalue['issue_id'];
$service_point_id = $epsilonvalue['sp_id'];
$updater = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("UPDATE facility_issues SET issued_to = {$service_point_id} WHERE id = {$issue_id}");
}
//end of foreach
$epsilon_affected = $this->db->affected_rows();
echo "EPSILON AFFECTED: " . $epsilon_affected;
echo "<br>THE -EPSILON- UPDATE WAS SUCCESSFUL </br>";
echo "THE UPDATE WAS SUCCESSFUL. GOD SPEED. </br>";
}
public function ors_zinc_consumption_report()
{
//Set the current year
$year = date("Y");
$county_total = array();
$excel_data = array();
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => ' consumption report ', 'file_name' => 'stock level report');
$row_data = array();
$column_data = array("County", "Sub-County", "Facility Code", "Facility Name", "Commodity Name", "Unit Cost(KES)", "Supplier", "Batch Number", "Quantity Consumed (Packs)", "Quantity Consumed (Units)", "Date Last Issued", "Days From Last Issue");
$excel_data['column_data'] = $column_data;
//the commodities variable will hold the values for the three commodities ie ORS and Zinc
$commodities = array(51, 267, 36, 456);
foreach ($commodities as $commodities) {
$commodity_stock_level = array();
//holds the data for the entire county
//once it is done executing for one commodity it will reset to zero
$commodity_total = array();
//pick the commodity names and details
//get the consumption for that commodity
$commodity_consumption = facility_issues::get_consumption_report_ors_zinc($commodities);
//Start building the excel file
foreach ($commodity_consumption as $commodity_consumption) {
//pick the facility code from the data
$facility_code = $commodity_consumption["facility_code"];
//get the last date of issue from the database
$date_last_issue = Facilities::get_last_issue($facility_code);
//ensure that if the date is null change the message
$date_of_last_issue = $date_last_issue[0]['Date Last Issued'] != 0 ? date('j M, Y', strtotime($date_last_issue[0]['Date Last Issued'])) : "No Data Found";
$days_since_last_issue = $date_last_issue[0]['Days From Last Issue'] != 0 ? $date_last_issue[0]['Days From Last Issue'] : 0;
array_push($row_data, array($commodity_consumption["county"], $commodity_consumption["district"], $commodity_consumption["facility_code"], $commodity_consumption["facility_name"], $commodity_consumption["commodity_name"], $commodity_consumption["unit_cost"], $commodity_consumption["source_name"], $commodity_consumption["batch_no"], $commodity_consumption["total_packs"], $commodity_consumption["total_units"], $date_of_last_issue, $days_since_last_issue));
}
}
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = "Consumption Report For Zinc & ORS";
$excel_data['excel_title'] = "Consumption Report for Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality (100) & (50) as at " . date("jS F Y");
//Start the email section of the report
//Get the number of facilities using HCMP
$no_of_facilities = Facilities::get_all_on_HCMP();
$subject = "Consumption Report: Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality ";
$message = "<p>Find attached an excel sheet with a Consumption Report for\n\tZinc Sulphate 20mg, ORS sachet (for 500ml) low osmolality (100 & 50) and ORS 4 Satchets & Zinc 10 Tablets 20 Mg as at " . date("jS F Y") . "</p>";
$message .= "<p>Number of facilities using HCMP: " . $no_of_facilities . "</p>";
$message .= $message_body;
$message .= "\n\t<p>You may log onto health-cmp.or.ke for follow up.</p>\n\n\t<p>----</p>\n\n\t<p>HCMP</p>\n\n\t<p>This email was automatically generated. Please do not respond to this email address or it will be ignored.</p>";
$report_type = "ors_report";
$this->create_excel($excel_data, $report_type);
//exit;
//path for windows
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
//path for Mac
//$handler = "/Applications/XAMPP/xamppfiles/htdocs/hcmp/print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$email_address = "*****@*****.**";
$bcc = "karsanrichard@gmail.com,kelvinmwas@gmail.com";
$this->hcmp_functions->send_email($email_address, $message, $subject, $handler, $bcc);
}
public function stock_levels_report()
{
//Set the current year
$year = date("Y");
$picurl = base_url() . 'assets/img/coat_of_arms-resized1.png';
//get the facilities in the district
$counties = Facilities::get_counties_all_using_HCMP();
foreach ($counties as $counties) {
//holds the data for the entire county
//once it is done executing for one county it is reset to zero
$county_total = array();
//pick the county nae and county ID accordingly
$county_id = $counties['county'];
$county_name = $counties['county_name'];
//Get all the districts in that particular county
$districts = Facilities::get_all_using_HCMP($county_id);
//holds the data for all the districts in a particular county
$district_total = array();
foreach ($districts as $districts) {
$district_id = $districts['district'];
$district_name = $districts['name'];
//get all facilities in that district
$facilities = Facilities::getFacilities_for_email($district_id);
//holds all the data for all facilities in a particular district
$facility_total = array();
foreach ($facilities as $facilities_) {
//holds the total value of expiries for that particular facility in that district
$facility_potential_expiries_total = 0;
//$facility_potential_expiries = array();
$facility_code = $facilities_->facility_code;
$facility_name = Facilities::get_facility_name2($facility_code);
$facility_name = $facility_name['facility_name'];
//get the stocking levels in that particular facility
$facility_consumption = facility_issues::get_consumption_report_facility($facility_code);
//push the result into another array that will be used by the distrct
array_key_exists($facility_name, $facility_total) ? $facility_total[$facility_name] = array_merge($facility_total[$facility_name], array($facility_consumption)) : ($facility_total = array_merge($facility_total, array($facility_name => array($facility_consumption))));
//Start buliding the excel file
$excel_data = array();
$excel_data = array('doc_creator' => $facility_name, 'doc_title' => 'facility stock level report ', 'file_name' => 'facility stock level report');
$row_data = array();
$column_data = array("Commodity Name", "Unit Size", "Quantity (units)", "Quantity (packs)", "Unit Cost(KSH)", "Total Cost(KSH)", "Supplier", "Facility Name", "MFL Code", "Sub County", "County");
$excel_data['column_data'] = $column_data;
foreach ($facility_consumption as $facility_consumption) {
array_push($row_data, array($facility_consumption["commodity_name"], $facility_consumption["unit_size"], $facility_consumption["total_units"], $facility_consumption["total_packs"], $facility_consumption["unit_cost"], $facility_consumption["total_cost"], $facility_consumption["source_name"], $facility_consumption["facility_name"], $facility_consumption["facility_code"], $facility_consumption["district"], $facility_consumption["county"]));
$facility_potential_expiries_total += $facility_consumption["total_cost"];
}
if (empty($row_data)) {
//do nothing
} else {
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = $facility_name . "_Stock_Level_Report";
$excel_data['excel_title'] = "Stock Levels Report for " . $facility_name . " for the month as at " . date("jS F Y");
$subject = "Stock Levels: " . $facility_name;
$message = "Dear " . $facility_name . ",\n\t\t\t\t\t\t\t\t<p>Find attached an excel sheet with the " . $facility_name . " breakdown of Stock Levels.\n\t\t\t\t\t\t\t\t</p>\n\t\t\t\t\t\t\t\t<p>You may log onto health-cmp.or.ke for follow up.</p>\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t<p>----</p>\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t<p>HCMP</p>\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t<p>This email was automatically generated. Please do not respond to this email address or it will be ignored.</p>";
$report_type = "stock_level";
$this->create_excel($excel_data, $report_type);
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
//$email_address = $this->get_facility_email($facility_code);
//$this -> hcmp_functions -> send_email($email_address, $message, $subject, $handler);
}
//End foreach for facility
}
array_key_exists($district_name, $district_total) ? $district_total[$district_name] = array_merge($district_total[$district_name], array($facility_total)) : ($district_total = array_merge($district_total, array($district_name => array($facility_total))));
//Building the excel sheet to be sent to the district admin
$excel_data = array();
$excel_data = array('doc_creator' => $district_name, 'doc_title' => 'sub county stock level report ', 'file_name' => 'sub county stock level report');
$row_data = array();
$column_data = array("Commodity Name", "Unit Size", "Quantity (units)", "Quantity (packs)", "Unit Cost(KSH)", "Total Cost(KSH)", "Supplier", "Facility Name", "MFL Code", "Sub County", "County");
$excel_data['column_data'] = $column_data;
foreach ($facility_total as $facility_total_1) {
foreach ($facility_total_1 as $facility_total_2) {
foreach ($facility_total_2 as $facility_total1) {
array_push($row_data, array($facility_total1["commodity_name"], $facility_total1["unit_size"], $facility_total1["total_units"], $facility_total1["total_packs"], $facility_total1["unit_cost"], $facility_total1["total_cost"], $facility_total1["source_name"], $facility_total1["facility_name"], $facility_total1["facility_code"], $facility_total1["district"], $facility_total1["county"]));
}
}
}
if (empty($row_data)) {
//do nothing
} else {
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = $district_name . "_Sub_County_Stock_Level_Report";
$excel_data['excel_title'] = "Stock Levels Report for " . $district_name . " Sub County as at " . date("jS F Y");
//Create the excel file here
$report_type = "stock_level";
$this->create_excel($excel_data, $report_type);
//exit;
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$subject = "Stock Levels: " . $district_name . " Sub County";
$message = "<p>Dear " . $district_name . " Sub County,</p>\n\t\t\t\t\t\t\t\t<p>Find attached an excel sheet with the " . $district_name . " Sub County's breakdown of Stock Levels.\n\t\t\t\t\t\t\t\t</p>\n\t\t\t\t\t\t\t\t<p>You may log onto health-cmp.or.ke for follow up.</p>\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t<p>----</p>\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t<p>HCMP</p>\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t<p>This email was automatically generated. Please do not respond to this email address or it will be ignored.</p>";
//$email_address = $this -> get_ddp_email($district_id);
//$this -> hcmp_functions -> send_email($email_address, $message, $subject, $handler);
}
}
//Building the excel sheet to be sent to the County Admin
$excel_data = array();
$excel_data = array('doc_creator' => $county_name, 'doc_title' => 'county stock levels report ', 'file_name' => 'county stock levels report');
$row_data = array();
$column_data = array("Commodity Name", "Unit Size", "Quantity (units)", "Quantity (packs)", "Unit Cost(KSH)", "Total Cost(KSH)", "Supplier", "Facility Name", "MFL Code", "Sub County", "County");
$excel_data['column_data'] = $column_data;
foreach ($district_total as $facility_total_1) {
foreach ($facility_total_1 as $facility_total_2) {
foreach ($facility_total_2 as $facility_total_3) {
foreach ($facility_total_3 as $facility_total_4) {
foreach ($facility_total_4 as $facility_total1) {
array_push($row_data, array($facility_total1["commodity_name"], $facility_total1["unit_size"], $facility_total1["total_units"], $facility_total1["total_packs"], $facility_total1["unit_cost"], $facility_total1["total_cost"], $facility_total1["source_name"], $facility_total1["facility_name"], $facility_total1["facility_code"], $facility_total1["district"], $facility_total1["county"]));
}
}
}
}
}
if (empty($row_data)) {
//do nothing
} else {
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = $county_name . "_County_Stock_Level_Report";
$excel_data['excel_title'] = "Stock Level Report for " . $county_name . " County as at " . date("jS F Y");
//create the excel file
$report_type = "stock_level";
$this->create_excel($excel_data, $report_type);
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$subject = "Stock Level: " . $county_name . " County";
//exit;
$message = "<p>Dear " . $county_name . " County,</p>\n\t\t\t\t\t\t\t<p>Find attached an excel sheet with the " . $county_name . " County's breakdown of Stock Level.\n\t\t\t\t\t\t\t</p>\n\t\t\t\t\t\t\t<p>You may log onto health-cmp.or.ke for follow up.</p>\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t<p>----</p>\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t<p>HCMP</p>\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t<p>This email was automatically generated. Please do not respond to this email address or it will be ignored.</p>";
//$email_address = $this -> get_county_email($county_id);
//$email_address = "smutheu@clintonhealthaccess.org,kelvinmwas@gmail.com,collinsojenge@gmail.com";
$bcc = $this->get_bcc_notifications();
if ($county_id == 1) {
$cc_email = $this->get_bcc_notifications();
//$cc_email = "";
} else {
$cc_email = "";
}
$this->hcmp_functions->send_email($email_address, $message, $subject, $handler, $bcc, $cc_email);
}
}
}
