public function potential_expiries_analysis()
{
//Set the current year
$year = date("Y");
$county_total = array();
$excel_data = array();
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => $commodity_name['commodity_name'] . ' stock level report ', 'file_name' => 'stock level report');
$row_data = array();
$column_data = array("County", "Sub-County", "Facility Code", "Facility Name", "Commodity Name", "Unit Size", "Unit Cost(KES)", "Supplier", "Manufacturer", "Batch Number", "Expiry Date", "Stock at Hand (units)", "Stock at Hand (packs)", "Total Cost");
$excel_data['column_data'] = $column_data;
//the commodities variable will hold the values for the three commodities ie ORS and Zinc
$commodities = array(51, 36, 267);
foreach ($commodities as $commodity) {
$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
//$commodity_name = Commodities::get_commodity_name($commodities);
//get the stock level for that commodity
$commodity_stock_level = Facility_stocks::get_commodity_stock_level($commodity);
if ($commodity == 51) {
//change the stock level for ORS 100 to match the one for 100 by dividing it by two
//Start buliding the excel file
foreach ($commodity_stock_level as $commodity_stock_level) {
$commodity_name = "ORS sachet (for 500ml) low osmolality (50)";
$commodity_stock_level_units = $commodity_stock_level["balance_units"] / 2;
$commodity_stock_level_packs = $commodity_stock_level_units / 50;
$commodity_cost = 204;
$commodity_size = "50s";
$commodity_total_cost = $commodity_cost * $commodity_stock_level_units;
array_push($row_data, array($commodity_stock_level["county"], $commodity_stock_level["subcounty"], $commodity_stock_level["facility_code"], $commodity_stock_level["facility_name"], $commodity_name, $commodity_size, $commodity_cost, $commodity_stock_level["supplier"], $commodity_stock_level["manufacture"], $commodity_stock_level["batch_no"], $commodity_stock_level["expiry_date"], $commodity_stock_level_units, $commodity_stock_level_packs));
}
} else {
//Start buliding the excel file
foreach ($commodity_stock_level as $commodity_stock_level) {
array_push($row_data, array($commodity_stock_level["county"], $commodity_stock_level["subcounty"], $commodity_stock_level["facility_code"], $commodity_stock_level["facility_name"], $commodity_stock_level["commodity_name"], $commodity_stock_level["unit_size"], $commodity_stock_level["unit_cost"], $commodity_stock_level["supplier"], $commodity_stock_level["manufacture"], $commodity_stock_level["batch_no"], $commodity_stock_level["expiry_date"], $commodity_stock_level["balance_units"], $commodity_stock_level["balance_packs"], $commodity_stock_level["amc_units"], $commodity_stock_level["amc"], $commodity_stock_level["mos"]));
}
}
}
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = "Potential Expiries Report";
$excel_data['excel_title'] = "Potential Expiries Report for Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality (100) & (50) as at " . date("jS F Y");
$report_type = "ors_report";
$this->create_excel($excel_data, $report_type);
//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";
}
public function stock_out_reports($district_id = null, $facility_code = null)
{
$district_id = $district_id == "NULL" ? null : $district_id;
$facility_code = $facility_code == "NULL" ? null : $facility_code;
$county_id = $this->session->userdata('county_id');
$stock_out_array = Facility_stocks::get_items_that_have_stock_out_in_facility($facility_code, $district_id, $county_id);
$graph_data = $series_data = array();
foreach ($stock_out_array as $facility_stock_data) {
$day = date('j M, Y ', strtotime($facility_stock_data['last_day']));
$ts1 = strtotime($facility_stock_data['last_day']);
$ts2 = strtotime(date("Y/m/d"));
$seconds_diff = $ts2 - $ts1;
$days = floor($seconds_diff / 3600 / 24);
array_push($series_data, array($facility_stock_data['district'], $facility_stock_data['facility_name'], $facility_stock_data['facility_code'], $facility_stock_data['commodity_name'], $facility_stock_data['commodity_code'], $day, $days));
}
$category_data = array(array("Sub County", 'Facility Name', "MLF No.", "Commodity Name", "Commodity Code", "Last day of usage", "No. days out of stock"));
$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";
return $this->load->view("shared_files/report_templates/data_table_template_v", $data);
}
public function get_facility_dashboard_notifications_graph_data()
{
//format the graph here
$facility_code = $this->session->userdata('facility_id');
$facility_stock_ = facility_stocks::get_facility_stock_amc($facility_code);
$facility_stock_count = count($facility_stock_);
$graph_data = array();
$graph_data = array_merge($graph_data, array("graph_id" => 'container'));
$graph_data = array_merge($graph_data, array("graph_title" => 'Facility stock level'));
$graph_data = array_merge($graph_data, array("color" => "['#4b0082','#FFF263', '#6AF9C4']"));
$graph_data = array_merge($graph_data, array("graph_type" => 'bar'));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => 'Total stock level (values in packs)'));
$graph_data = array_merge($graph_data, array("graph_categories" => array()));
$graph_data = array_merge($graph_data, array("series_data" => array("Current Balance" => array(), "AMC" => array())));
$graph_data['stacking'] = 'normal';
foreach ($facility_stock_ as $facility_stock_) {
$graph_data['graph_categories'] = array_merge($graph_data['graph_categories'], array($facility_stock_['commodity_name']));
$graph_data['series_data']['Current Balance'] = array_merge($graph_data['series_data']['Current Balance'], array((double) $facility_stock_['pack_balance']));
$graph_data['series_data']['AMC'] = array_merge($graph_data['series_data']['AMC'], array((double) $facility_stock_['amc']));
}
//echo "<pre>";print_r($facility_stock_);echo "</pre>";exit;
//create the graph here
$faciliy_stock_data = $this->hcmp_functions->create_high_chart_graph($graph_data);
$loading_icon = base_url('assets/img/no-record-found.png');
$faciliy_stock_data = $facility_stock_count > 0 ? $faciliy_stock_data : "\$('#container').html('<img src={$loading_icon}>');";
//compute stocked out items
$items_stocked_out_in_facility = count(facility_stocks::get_items_that_have_stock_out_in_facility($facility_code));
//get order information from the db
$facility_order_count_ = facility_orders::get_facility_order_summary_count($facility_code);
//echo "<pre>";print_r($facility_order_count_);echo "<pre>";exit;
$facility_order_count = array();
foreach ($facility_order_count_ as $facility_order_count_) {
$facility_order_count[$facility_order_count_['status']] = $facility_order_count_['total'];
}
//get potential expiries infor here
$potential_expiries = Facility_stocks::potential_expiries($facility_code)->count();
//get actual Expiries infor here
$actual_expiries = count(Facility_stocks::All_expiries($facility_code));
//get items they have been donated for
$facility_donations = redistribution_data::get_all_active($facility_code, "to-me")->count();
//get items they have been donated and are pending
$facility_donations_pending = redistribution_data::get_all_active($facility_code)->count();
//get stocks from v1
$stocks_from_v1 = 0;
if ($facility_stock_count == 0 && $facility_donations == 0 && $facility_donations_pending == 0) {
$stocks_from_v1 = count(facility_stocks::import_stock_from_v1($facility_code));
}
return array('facility_stock_count' => $facility_stock_count, 'faciliy_stock_graph' => $faciliy_stock_data, 'items_stocked_out_in_facility' => $items_stocked_out_in_facility, 'facility_order_count' => $facility_order_count, 'potential_expiries' => $potential_expiries, 'actual_expiries' => $actual_expiries, 'facility_donations' => $facility_donations, 'facility_donations_pending' => $facility_donations_pending, 'stocks_from_v1' => $stocks_from_v1);
}
public function decommission()
{
//Change status of commodities to decommissioned
$date = date('y-m-d');
$facility_code = $this->session->userdata('facility_id');
$user_id = $this->session->userdata('user_id');
$facility_name_array = Facilities::get_facility_name_($facility_code)->toArray();
$facility_name = $facility_name_array[0]['facility_name'];
$myobj1 = Doctrine::getTable('Districts')->find($facility_name_array[0]['district']);
$disto_name = $myobj1->district;
$county = $myobj1->county;
$myobj2 = Doctrine::getTable('Counties')->find($county);
$county_name = $myobj2->county;
$total = 0;
//Create PDF of Expired Drugs that are to be decommisioned. check here
$decom = Facility_stocks::get_facility_expired_stuff($facility_code);
/*****************************setting up the report*******************************************/
if (count($decom) > 0) {
$body = '';
$body .= "<style> table {\n border-collapse: collapse;\n}td,th{\n\tpadding: 12px;\n\ttext-align:center;\n}\n\n*{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>";
$body .= "<table class='' style='background-color: #ECF8FF;' >\n<tr>\n<td>MFL No: <strong>{$facility_code}</strong> </td>\n<td>Health Facility Name: <strong>{$facility_name}</strong></td>\n<td>County: <strong>{$county_name}</strong></td>\n<td>Subcounty: <strong>{$disto_name}</strong></td>\n</tr>\n</table>" . '
<table class="" style="margin:8px;word-wrap: break-word;">
<thead style="font-size:12px">
<tr><th><strong>Source</strong></th>
<th><strong>Description</strong></th>
<th><strong>Commodity Code</strong></th>
<th><strong>Unit Cost(Ksh)</strong></th>
<th><strong>Batch Affected</strong></th>
<th><strong>Manufacturer</strong></th>
<th><strong>Expiry Date</strong></th>
<th><strong>Expiry Days</strong></th>
<th><strong>Expired(Pack Size)</strong></th>
<th><strong>Expired(Unit Size)</strong></th>
<th><strong>Cost of Expired (Ksh)</strong></th>
</tr> </thead><tbody>';
/*******************************begin adding data to the report*****************************************/
foreach ($decom as $drug) {
$commodity_id = $drug['commodity_id'];
$batch = $drug['batch_no'];
$mau = $drug['manufacture'];
$commodity_name = $drug['commodity_name'];
$commodity_code = $drug['commodity_code'];
$unit_size = $drug['unit_size'];
$unit_cost = str_replace(",", '', $drug['unit_cost']);
$current_balance = $drug['current_balance'];
$total_commodity_units = $drug['total_commodity_units'];
$expiry_date = $drug['expiry_date'];
$current_balance_pack = round($current_balance / $total_commodity_units, 1);
$cost = $current_balance_pack * $unit_cost;
$formatme = new DateTime($expiry_date);
$newdate = $formatme->format('d M Y');
$facility_stock_id = $drug['facility_stock_id'];
$total = $total + $cost;
$source = $drug['source_name'];
//get the current balance of the commodity
$facility_stock = Facility_Stocks::get_facility_commodity_total($facility, $commodity_id)->toArray();
$mydata3 = array('facility_code' => $facility_code, 's11_No' => '(Loss) Expiry', 'commodity_id' => $commodity_id, 'batch_no' => $batch, 'expiry_date' => date('y-m-d', strtotime(str_replace(",", " ", $expiry_date))), 'balance_as_of' => $facility_stock[0]['commodity_balance'], 'date_issued' => date('y-m-d'), 'qty_issued' => 0, 'adjustmentnve' => $current_balance * -1, 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('user_id'));
$seconds_diff = strtotime(date("y-m-d")) - strtotime($expiry_date);
$date_diff = floor($seconds_diff / 3600 / 24);
Facility_Issues::update_issues_table($mydata3);
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("UPDATE `facility_transaction_table` SET losses =losses+{$current_balance}, closing_stock=closing_stock-{$current_balance}\n WHERE `commodity_id`= '{$commodity_id}' and status='1' and facility_code={$facility_code} ");
/// update the facility issues and set the commodity to expired
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("UPDATE `facility_stocks` SET status =2 WHERE `id`= '{$facility_stock_id}'");
if ($current_balance_pack > 0) {
$body .= '<tr style="font-size:12px;padding:4px"><td>' . $source . '</td>
<td>' . $commodity_name . '</td>
<td>' . $commodity_code . '</td>
<td>' . $unit_cost . '(' . $unit_size . ')' . '</td>
<td>' . $batch . '</td>
<td>' . $mau . '</td>
<td>' . $newdate . '</td>
<td>' . $date_diff . '</td>
<td>' . $current_balance_pack . '</td>
<td>' . $current_balance . '</td>
<td>' . number_format($cost, 2, '.', ',') . '</td>
</tr>';
}
}
$body .= '
<tr>
<td colspan="12">
<b style="float: right; margin:1.0em;font-size:14px">TOTAL cost(Ksh) of Expiries: ' . number_format($total, 2, '.', ',') . '</b>
</tr>
</tbody>
</table>';
$html_body .= "<!-- BODY -->\n<table class='body-wrap'>\n\t<tr>\n\t\t<td></td>\n\t\t<td class='container' bgcolor='#FFFFFF'>\n\n\t\t\t<div class='content'>\n\t\t\t<table>\n\t\t\t\t<tr>\n\t\t\t\t\t<td>\n\t\t\t\t\t\t<h3>Hello,</h3>\n\t\t\t\t\t\t<p class='lead'>Find attached decommissioned commodities for {$facility_name} ({$facility_code}).</p>\n\t\t\t\t\t\t<p>{$body}</p>\n\t\t\t\t\t\t<!-- Callout Panel -->\n\t\t\t\t\t\t<p class='callout'>\n\t\t\t\t\t\t\t<a href='health-cmp.or.ke'>Click here! »</a> to follow up.\n\t\t\t\t\t\t</p><!-- /Callout Panel -->\n\n\t\t\t\t\t\t<!-- social & contact -->\n\t\t\t\t\t\t<table class='social' width='100%'>\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<!-- column 1 -->\n\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</table><!-- /column 1 -->\n\n\t\t\t\t\t\t\t\t\t<!-- column 2 -->\n\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<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<h5 class=''>Contact Info:</h5>\n\t\t\t\t\t\t\t\t\t\t\t\t<p>Phone: <strong>+254720167245</strong><br/>\n Email: <strong><a href='emailto:hcmpkenya@gmail.com'>hcmpkenya@gmail.com</a></strong></p>\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><!-- /column 2 -->\n\n\t\t\t\t\t\t\t\t\t<span class='clear'></span>\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><!-- /social & contact -->\n\n\t\t\t\t\t</td>\n\t\t\t\t</tr>\n\t\t\t</table>\n\t\t\t</div><!-- /content -->\n\n\t\t</td>\n\t\t<td></td>\n\t</tr>\n</table><!-- /BODY -->";
$file_name = 'Facility_Expired_Commodities_' . $facility_name . "_" . $facility_code . "_" . $date;
$pdf_data = array("pdf_title" => "Facility Expired Commodities For {$facility_name}", 'pdf_html_body' => $body, 'pdf_view_option' => 'save_file', 'file_name' => $file_name);
$this->hcmp_functions->create_pdf($pdf_data);
if ($this->hcmp_functions->send_stock_decommission_email($html_body, 'Decommission Report For ' . $facility_name, './pdf/' . $file_name . '.pdf')) {
delete_files('./pdf/' . $file_name . '.pdf');
$this->session->set_flashdata('system_success_message', 'Stocks Have Been Decommissioned');
}
//updates the log tables with the action
$user = $this->session->userdata('user_id');
$user_action = "decommissioned";
//updates the log table accordingly based on the action carried out by the user involved
$update = Doctrine_Manager::getInstance()->getCurrentConnection();
$update->execute("update log set {$user_action} = 1\n\t\t\twhere `user_id`= {$user}\n\t\t\tAND action = 'Logged In'\n\t\t\tand UNIX_TIMESTAMP( `end_time_of_event`) = 0");
}
redirect('reports/facility_stock_data');
}
public function get_district_dashboard_notifications_graph_data()
{
//format the graph here
//$facility_code=$this -> session -> userdata('facility_id');
$district_id = $this->session->userdata('district_id');
$district_stock_ = facility_stocks::get_district_stock_amc($district_id);
$district_stock_count = count($district_stock_);
$graph_data = array();
$graph_data = array_merge($graph_data, array("graph_id" => 'container'));
$graph_data = array_merge($graph_data, array("graph_title" => 'District Store Stock level'));
$graph_data = array_merge($graph_data, array("graph_type" => 'bar'));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => 'Total stock level (values in packs)'));
$graph_data = array_merge($graph_data, array("graph_categories" => array()));
$graph_data = array_merge($graph_data, array("series_data" => array("Current Pack Balance" => array(), "Current Unit Balance" => array())));
$graph_data['stacking'] = 'normal';
foreach ($district_stock_ as $district_stock_) {
$graph_data['graph_categories'] = array_merge($graph_data['graph_categories'], array($district_stock_['commodity_name']));
$graph_data['series_data']['Current Pack Balance'] = array_merge($graph_data['series_data']['Current Pack Balance'], array((double) $district_stock_['pack_balance']));
$graph_data['series_data']['Current Unit Balance'] = array_merge($graph_data['series_data']['Current Unit Balance'], array((double) $district_stock_['commodity_balance']));
// $graph_data['series_data'] = array_merge($graph_data['series_data'], array("Potential Expiries" => $series_data2, "Actual Expiries" => $series_data));
}
//create the graph here
//echo "I WORK";exit;
$district_stock_data = $this->hcmp_functions->create_high_chart_graph($graph_data);
$loading_icon = base_url('assets/img/no-record-found.png');
$district_stock_data = $district_stock_count > 0 ? $district_stock_data : "\$('#container').html('<img src={$loading_icon}>');";
//get potential expiries info here
$potential_expiries_ = count(Facility_stocks::drug_store_commodity_expiries($district_id));
//get actual Expiries info here
$actual_expiries = count(Facility_stocks::drug_store_commodity_expiries($district_id));
//get items they have been donated for
$facility_donations = redistribution_data::get_all_active_drug_store($district_id, "to-me")->count();
// echo "<pre>";print_r($facility_donations);echo "</pre>";exit;
//seth
//get stocks from v1
$stocks_from_v1 = 0;
return array('district_stock_count' => $district_stock_count, 'district_stock_graph' => $district_stock_data, 'potential_expiries' => $potential_expiries_, 'actual_expiries' => $actual_expiries, 'facility_donations' => $facility_donations, 'stocks_from_v1' => $stocks_from_v1);
}
public function expiries_report()
{
//Set the current year
$year = date("Y");
//get the facilities in the district
$counties = Facilities::get_counties_all_using_HCMP();
foreach ($counties as $counties) {
//holds the dat 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 ddistricts 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'];
//$facility_potential_expiries = Facility_stocks::get_items_that_have_stock_out_in_facility(17401, 88, null);
$facility_potential_expiries = Facility_stocks::All_expiries_email($facility_code, 'all');
//get potential expiries in that particular facility
//$facility_potential_expiries = Facility_stocks::potential_expiries_email($district_id, $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_potential_expiries)) : ($facility_total = array_merge($facility_total, array($facility_name => array($facility_potential_expiries))));
//Start buliding the excel file
$excel_data = array();
$excel_data = array('doc_creator' => $facility_name, 'doc_title' => 'facility expiries weekly report ', 'file_name' => 'facility weekly report');
$row_data = array();
$column_data = array("Facility Code", "Facility Name", "Commodity Code", "Commodity Name", "Expiry Date", "Batch No", "Manufacturer", "Unit Size", "Current Balance");
$excel_data['column_data'] = $column_data;
foreach ($facility_potential_expiries as $facility_potential_expiries) {
array_push($row_data, array($facility_potential_expiries["facility_code"], $facility_potential_expiries["facility_name"], $facility_potential_expiries["commodity_code"], $facility_potential_expiries["commodity_name"], $facility_potential_expiries["expiry_date"], $facility_potential_expiries["batch_no"], $facility_potential_expiries["manufacture"], $facility_potential_expiries["unit_size"], $facility_potential_expiries["current_balance"]));
}
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 . "_Expiries_Report";
$message = "Find attached an excel sheet with the breakdown for the Expiries in the facility";
$this->hcmp_functions->create_excel($excel_data);
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$subject = "Expiries: " . $facility_name;
//$email_address = "*****@*****.**";
$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' => 'district stock outs weekly report ', 'file_name' => 'district weekly report');
$row_data = array();
$column_data = array("Facility Code", "Facility Name", "Commodity Code", "Commodity Name", "Expiry Date", "Batch No", "Manufacturer", "Unit Size", "Current Balance");
$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["facility_code"], $facility_total1["facility_name"], $facility_total1["commodity_code"], $facility_total1["commodity_name"], $facility_total1["expiry_date"], $facility_total1["batch_no"], $facility_total1["manufacture"], $facility_total1["unit_size"], $facility_total1["current_balance"]));
}
}
}
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 . "_Weekly_District_Stock_Outs_Report";
$this->hcmp_functions->create_excel($excel_data);
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$subject = "Expiries: " . $district_name . " Sub County";
$message = $district_name . "'s Weekly Expiries Report";
$message .= "Find attached an excel sheet with the breakdown for the Expiries for the district";
//$email_address = "*****@*****.**";
$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 district admin
$excel_data = array();
$excel_data = array('doc_creator' => $district_name, 'doc_title' => 'district stock outs weekly report ', 'file_name' => 'district weekly report');
$row_data = array();
$column_data = array("Facility Code", "Facility Name", "Commodity Code", "Commodity Name", "Expiry Date", "Batch No", "Manufacturer", "Unit Size", "Current Balance");
$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["facility_code"], $facility_total1["facility_name"], $facility_total1["commodity_code"], $facility_total1["commodity_name"], $facility_total1["expiry_date"], $facility_total1["batch_no"], $facility_total1["manufacture"], $facility_total1["unit_size"], $facility_total1["current_balance"]));
}
}
}
}
}
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 . "_Weekly_County_Expiries_Report";
$this->hcmp_functions->create_excel($excel_data);
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$subject = "Expiries: " . $county_name . " County";
$message = $county_name . "'s Weekly Expiries Report";
$message .= "Find attached an excel sheet with the breakdown for the Expiries for the county";
$email_address = $this->get_county_email($county_id);
$bcc = $this->get_bcc_notifications();
$cc_email = "";
$this->hcmp_functions->send_email($email_address, $message, $subject, $handler, $bcc, $cc_email);
}
}
}
public function ors_zinc_report()
{
//Set the current year
$year = date("Y");
$county_total = array();
$excel_data = array();
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => ' stock level report ', 'file_name' => 'stock level report');
$row_data = array();
$column_data = array("County", "Sub-County", "Facility Code", "Facility Name", "Commodity Name", "Unit Size", "Unit Cost(KES)", "Supplier", "Manufacturer", "Batch Number", "Expiry Date", "Stock at Hand (units)", "Stock at Hand (packs)", "AMC (units)", "AMC (packs)", "Stock at Hand MOS(packs)", "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 stock level for that commodity
$commodity_stock_level = Facility_stocks::get_commodity_stock_level($commodities);
//echo "<pre>";print_r($commodity_stock_level);exit;
//Start buliding the excel file
foreach ($commodity_stock_level as $commodity_stock_level) {
//pick the facility code from the data
$facility_code = $commodity_stock_level["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_stock_level["county"], $commodity_stock_level["subcounty"], $commodity_stock_level["facility_code"], $commodity_stock_level["facility_name"], $commodity_stock_level["commodity_name"], $commodity_stock_level["unit_size"], $commodity_stock_level["unit_cost"], $commodity_stock_level["supplier"], $commodity_stock_level["manufacture"], $commodity_stock_level["batch_no"], $commodity_stock_level["expiry_date"], $commodity_stock_level["balance_units"], $commodity_stock_level["balance_packs"], $commodity_stock_level["amc_units"], $commodity_stock_level["amc"], $commodity_stock_level["mos"], $date_of_last_issue, $days_since_last_issue));
}
//Switch statement to build on the remaining part of the message body
//get the number of facilities stocked out on a specific commodity
$no_of_stock_outs = Facility_stocks::facilities_stocked_specific_commodity($commodities);
//get the number of facilities reporting on a specific commodity
$no_of_facilities_reporting = Facility_stocks::facilities_reporting_on_a_specific_commodity($commodities);
//get the number of batches expiring within 3 months
$no_of_batches = Facility_stocks::batches_expiring_specific_commodities($commodities);
switch ($commodities) {
case 51:
$message_body .= "<p>Number of Facilities Reporting on ORS Satchets (100):" . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on ORS Satchets (100): " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of ORS (100) Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
case 267:
$message_body .= "<p>Number of Facilities Reporting on ORS Satchets (50):" . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on ORS Satchets (50): " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of ORS (50) Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
case 36:
$message_body .= "<p>Number of Facilities Reporting on Zinc Sulphate 20mg: " . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on Zinc Sulphate 20mg: " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of Zinc Sulphate Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
case 456:
$message_body .= "<p>Number of Facilities Reporting on ORS 4 Satchets & Zinc 10 Tablets 20 Mg: " . $no_of_facilities_reporting . "</p>";
$message_body .= "<p>Number of Facilities Stocked out on ORS 4 Satchets & Zinc 10 Tablets 20 Mg: " . $no_of_stock_outs . "</p>";
$message_body .= "<p>Number of ORS 4 Satchets & Zinc 10 Tablets 20 Mg Batches expiring in the next 3 months: " . $no_of_batches . "</p>";
break;
}
}
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = "Stock_Level_Report";
$excel_data['excel_title'] = "Stock Level 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 = "Stock Level Report: Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality ";
$message = "<p>Find attached an excel sheet with a Stock Level Report for \n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\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 \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<p>You may log onto health-cmp.or.ke for follow up.</p>\n\n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<p>----</p>\n\n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<p>HCMP</p>\n\n\t \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\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 = "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 = "smutheu@clintonhealthaccess.org,jaynerawz@gmail.com";
$bcc = "karsanrichard@gmail.com,kelvinmwas@gmail.com";
$this->hcmp_functions->send_email($email_address, $message, $subject, $handler, $bcc);
}
public function decommission()
{
//Change status of commodities to decommissioned
$date = date('y-m-d');
$facility_code = $this->session->userdata('facility_id');
$user_id = $this->session->userdata('user_id');
$facility_name_array = Facilities::get_facility_name_($facility_code)->toArray();
$facility_name = $facility_name_array[0]['facility_name'];
$myobj1 = Doctrine::getTable('Districts')->find($facility_name_array[0]['district']);
$disto_name = $myobj1->district;
$county = $myobj1->county;
$myobj2 = Doctrine::getTable('Counties')->find($county);
$county_name = $myobj2->county;
$total = 0;
//Create PDF of Expired Drugs that are to be decommisioned. check here
$decom = Facility_stocks::get_facility_expired_stuff($facility_code);
/*****************************setting up the report*******************************************/
if (count($decom) > 0) {
$html_body = '';
$html_body .= "<table class='data-table' width=100%>\n<tr>\n<td>MFL No: {$facility_code}</td> \n<td>Health Facility Name: {$facility_name}</td>\n<td>County: {$county_name}</td> \n<td>Subcounty: {$disto_name}</td>\n</tr>\n</table>" . '
<table class="data-table" width=100%>
<thead>
<tr><th><strong>Source</strong></th>
<th><strong>Description</strong></th>
<th><strong>Commodity Code</strong></th>
<th><strong>Unit Size</strong></th>
<th><strong>Unit Cost (Ksh)</strong></th>
<th><strong>Batch No Affected</strong></th>
<th><strong>Manufacturer</strong></th>
<th><strong>Expiry Date</strong></th>
<th><strong># of Days From Expiry</strong></th>
<th><strong>Stock Expired(Pack Size)</strong></th>
<th><strong>Stock Expired(Unit Size)</strong></th>
<th><strong>Cost of Expired (Ksh)</strong></th>
</tr> </thead><tbody>';
/*******************************begin adding data to the report*****************************************/
foreach ($decom as $drug) {
$commodity_id = $drug['commodity_id'];
$batch = $drug['batch_no'];
$mau = $drug['manufacture'];
$commodity_name = $drug['commodity_name'];
$commodity_code = $drug['commodity_code'];
$unit_size = $drug['unit_size'];
$unit_cost = str_replace(",", '', $drug['unit_cost']);
$current_balance = $drug['current_balance'];
$total_commodity_units = $drug['total_commodity_units'];
$expiry_date = $drug['expiry_date'];
$current_balance_pack = round($current_balance / $total_commodity_units, 1);
$cost = $current_balance_pack * $unit_cost;
$formatme = new DateTime($expiry_date);
$newdate = $formatme->format('d M Y');
$facility_stock_id = $drug['facility_stock_id'];
$total = $total + $cost;
$source = $drug['source_name'];
//get the current balance of the commodity
$facility_stock = Facility_Stocks::get_facility_commodity_total($facility, $commodity_id)->toArray();
$mydata3 = array('facility_code' => $facility_code, 's11_No' => '(Loss) Expiry', 'commodity_id' => $commodity_id, 'batch_no' => $batch, 'expiry_date' => date('y-m-d', strtotime(str_replace(",", " ", $expiry_date))), 'balance_as_of' => $facility_stock[0]['commodity_balance'], 'date_issued' => date('y-m-d'), 'qty_issued' => 0, 'adjustmentnve' => $current_balance * -1, 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('user_id'));
$seconds_diff = strtotime(date("y-m-d")) - strtotime($expiry_date);
$date_diff = floor($seconds_diff / 3600 / 24);
Facility_Issues::update_issues_table($mydata3);
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("UPDATE `facility_transaction_table` SET losses =losses+{$current_balance}, closing_stock=closing_stock-{$current_balance}\n WHERE `commodity_id`= '{$commodity_id}' and status='1' and facility_code={$facility_code} ");
/// update the facility issues and set the commodity to expired
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->execute("UPDATE `facility_stocks` SET status =2 WHERE `id`= '{$facility_stock_id}'");
if ($cost > 0) {
$html_body .= '<tr><td>' . $source . '</td>
<td>' . $commodity_name . '</td>
<td>' . $commodity_code . '</td>
<td>' . $unit_size . '</td>
<td>' . $unit_cost . '</td>
<td>' . $batch . '</td>
<td>' . $mau . '</td>
<td>' . $newdate . '</td>
<td>' . $date_diff . '</td>
<td>' . $current_balance_pack . '</td>
<td>' . $current_balance . '</td>
<td>' . number_format($cost, 2, '.', ',') . '</td>
</tr>';
}
}
$html_body .= '
<tr>
<td colspan="12">
<b style="float: right; margin-right:5.0em">TOTAL cost(Ksh) of Expiries: ' . number_format($total, 2, '.', ',') . '</b>
</tr>
</tbody>
</table>';
$file_name = 'Facility_Expired_Commodities_' . $facility_name . "_" . $facility_code . "_" . $date;
$pdf_data = array("pdf_title" => "Facility Expired Commodities For {$facility_name}", 'pdf_html_body' => $html_body, 'pdf_view_option' => 'save_file', 'file_name' => $file_name);
$this->hcmp_functions->create_pdf($pdf_data);
if ($this->hcmp_functions->send_stock_decommission_email($html_body, 'Decommission Report For ' . $facility_name, './pdf/' . $file_name . '.pdf')) {
delete_files('./pdf/' . $file_name . '.pdf');
$this->session->set_flashdata('system_success_message', 'Stocks Have Been Decommissioned');
}
$user = $this->session->userdata('user_id');
$user_action = "decommission";
Log::log_user_action($user, $user_action);
}
redirect('reports/facility_stock_data');
}
