public function create_excel_facility_program_report($report_id, $facility_code, $report_type)
{
if ($report_type == "malaria") {
$facility_details = Facilities::get_facility_name_($facility_code)->toArray();
$from_malaria_data_table = Malaria_Data::get_facility_report($report_id, $facility_code);
$from_malaria_data_table_count = count(Malaria_Data::get_facility_report($report_id, $facility_code));
$excel_data = array('doc_creator' => $facility_details[0]['facility_name'], 'doc_title' => 'facility programm report template ', 'file_name' => $facility_details[0]['facility_name'] . 'facility programm report template ');
$row_data = array();
$column_data = array("Product Code", "Item description(Name/form/strength)", "Beginning Balance", "Quantity Received", "Quantity Dispensed", "Losses Excluding Expiries", "Adjustments", "Physical Count", "Amount Expired", "Days Out of Stock", "Report Total");
$excel_data['column_data'] = $column_data;
$from_malaria_data_table_count = count(Malaria_Data::get_facility_report($report_id, $facility_code));
for ($i = 0; $i < $from_malaria_data_table_count; $i++) {
$adjs = $from_malaria_data_table[$i]['Positive_Adjustments'] + $from_malaria_data_table[$i]['Negative_Adjustments'];
$mydrug_name = Doctrine::getTable('Malaria_drugs')->findOneBykemsa_code($from_malaria_data_table[$i]['Kemsa_Code']);
array_push($row_data, array($from_malaria_data_table[$i]["Kemsa_Code"], $commodityname = $mydrug_name->drug_name, $from_malaria_data_table[$i]["Beginning_Balance"], $from_malaria_data_table[$i]["Quantity_Received"], $from_malaria_data_table[$i]["Quantity_Dispensed"], $from_malaria_data_table[$i]["Losses_Excluding_Expiries"], $adjs, $from_malaria_data_table[$i]["Physical_Count"], $from_malaria_data_table[$i]["Expired_Drugs"], $from_malaria_data_table[$i]["Days_Out_Stock"], $from_malaria_data_table[$i]["Report_Total"]));
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
} elseif ($report_type == "RH") {
$facility_details = Facilities::get_facility_name_($facility_code)->toArray();
$from_RH_data_table = RH_Drugs_Data::get_facility_report($report_id, $facility_code);
$from_RH_data_table_count = count(RH_Drugs_Data::get_facility_report($report_id, $facility_code));
$excel_data = array('doc_creator' => $facility_details[0]['facility_name'], 'doc_title' => 'facility program report ', 'file_name' => $facility_details[0]['facility_name'] . 'facility program report template');
$row_data = array();
$column_data = array("Beginning Balance", "Received This Month", "Dispensed", "Losses", "Adjustments", "AEnding Balance", "Quantity Requested");
$excel_data['column_data'] = $column_data;
$from_RH_data_table_count = count(RH_Drugs_Data::get_facility_report($report_id, $facility_code));
for ($i = 0; $i < $from_RH_data_table_count; $i++) {
array_push($row_data, array($from_RH_data_table[$i]["Beginning_Balance"], $from_RH_data_table[$i]["Received_This_Month"], $from_RH_data_table[$i]["Dispensed"], $from_RH_data_table[$i]["Losses"], $from_RH_data_table[$i]["Adjustments"], $from_RH_data_table[$i]["Ending_Balance"], $from_RH_data_table[$i]["Quantity_Requested"]));
//
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
} elseif ($report_type == "TB") {
$facility_details = Facilities::get_facility_name_($facility_code)->toArray();
$from_TB_data_table = tb_data::get_all_other($report_id);
$from_TB_data_table_2 = tb_data::get_all_other_2($report_id);
$tb_drug_names = tb_data::get_tb_drug_names();
$excel_data = array('doc_creator' => $facility_details[0]['facility_name'], 'doc_title' => 'facility program report ', 'file_name' => $facility_details[0]['facility_name'] . 'facility program report template');
$row_data = array();
$column_data = array("Drug Name", "Pack", "Beginning Date", "Quantity Recieved This Month", "Ending Date", "Beginning Balance", "Quantity Dispensed", "Positive Adjustments", "Negative Adjustments", "Losses", "Physical Count", "Earliest Expiry", "Quantity Requested", "Report Date");
$excel_data['column_data'] = $column_data;
$from_TB_data_table_count = count(tb_data::get_all_other($report_id));
for ($i = 0; $i < $from_TB_data_table_count; $i++) {
array_push($row_data, array($tb_drug_names[$i]["drug_name"], $tb_drug_names[$i]['unit_size'], $from_TB_data_table[$i]['beginning_date'], $from_TB_data_table[$i]['currently_recieved'], $from_TB_data_table[$i]['ending_date'], $from_TB_data_table[$i]['beginning_balance'], $from_TB_data_table[$i]['quantity_dispensed'], $from_TB_data_table[$i]['positive_adjustment'], $from_TB_data_table[$i]['negative_adjustment'], $from_TB_data_table[$i]['losses'], $from_TB_data_table[$i]['physical_count'], $from_TB_data_table[$i]['earliest_expiry'], $from_TB_data_table[$i]['quantity_needed'], $from_TB_data_table[$i]["report_date"]));
//
}
$excel_data['row_data'] = $row_data;
$this->hcmp_functions->create_excel($excel_data);
}
}
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 edit_report($time)
{
$time = urldecode($time);
$malaria_name = Malaria_Drugs::getName();
$malaria_array = array();
$facility = isset($facility_code) ? $facility_code : $this->session->userdata('news');
$district = isset($district_id) ? $facility_code : $this->session->userdata('district');
$facility_detail = Facilities::get_facility_name_($facility);
$user_id = $this->session->userdata('user_id');
$facility_detail = Facilities::get_facility_name_($facility);
$district = $facility_detail['district'];
$data['drug_rows'] = Malaria_Drugs::getName();
$data['malaria_data'] = $malaria_array;
$data['facility_data'] = $facility_detail;
$data['where_time'] = $time;
$data['records'] = Malaria_Data::getall_time($time);
$data['facility_data'] = $facility_detail;
$data['user_data'] = Malaria_Data::getall($user_id);
$data['content_view'] = "facility/facility_reports/test_edit_report_v";
$view = 'shared_files/template/template';
$this->load->view($view, $data);
}
public function facility_order($source = NULL, $facility_code = null)
{
header('Content-Type: text/html; charset=UTF-8');
//pick the facility code from the session as it is set
if (!isset($facility_code)) {
$facility_code = $this->session->userdata('facility_id');
$data['other_facility_code'] = $facility_code;
}
$amc_calc = $this->amc($county, $district, $facility_code);
//echo '<pre>'; print_r($amc_calc);echo '<pre>'; exit;
$items = isset($source) && $source == 2 ? Facility_Transaction_Table::get_commodities_for_ordering_meds($facility_code, $source) : Facility_Transaction_Table::get_commodities_for_ordering($facility_code);
// echo '<pre>'; print_r($items);echo '<pre>'; exit;
if (isset($_FILES['file']) && $_FILES['file']['size'] > 0) {
$ext = pathinfo($_FILES["file"]['name'], PATHINFO_EXTENSION);
//echo $ext;exit;
//echo $_FILES["file"]["tmp_name"];exit;
if ($ext == 'xls') {
$excel2 = PHPExcel_IOFactory::createReader('Excel5');
} else {
if ($ext == 'xlsx') {
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
} else {
die('Invalid file format given' . $_FILES['file']);
}
}
$excel2 = $objPHPExcel = $excel2->load($_FILES["file"]["tmp_name"]);
// Empty Sheet
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$temp = array();
if ($sheet->getCell('H4')->getValue() != '') {
$facility_code = $sheet->getCell('H4')->getValue();
} else {
$facility_code = $sheet->getCell('J4')->getValue();
}
$checker = $sheet->getCell('E17')->getValue();
// Loop through each row of the worksheet in turn
$array_code = array();
$array_commodity = array();
$array_category = array();
$array_pack = array();
$array_price = array();
$array_order_qty = array();
$array_order_val = array();
//$array_code=array();
for ($row = 17; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $objPHPExcel->getActiveSheet()->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if ($checker == '#REF!' || $checker == '=VLOOKUP(C17,#REF!,1,FALSE)') {
unset($rowData[0][4]);
unset($rowData[0][5]);
foreach ($rowData as $key => $value) {
unset($rowData);
$rowData[] = array_values($value);
}
}
//count($rowData);
$code = preg_replace('/\\s+/ ', '', $rowData[0][2]);
$code = str_replace('-', '', $code);
$array_index[] = $rowData[0][1] - 1;
$array_code[] = $code;
$array_commodity[] = $rowData[0][3];
$array_category[] = $rowData[0][4];
$array_price[] = $rowData[0][6];
$array_order_qty[] = (int) $rowData[0][7];
$array_order_val[] = $rowData[0][8];
$array_pack[] = $rowData[0][5];
}
// echo '<pre>'; print_r($array_order_qty);echo '<pre>'; exit;
foreach ($array_order_qty as $id => $key) {
array_push($temp, array('sub_category_name' => $array_category[$id], 'commodity_name' => $array_commodity[$id], 'unit_size' => $array_pack[$id], 'unit_cost' => $array_price[$id] == '' ? 0 : (double) $array_price[$id], 'commodity_code' => preg_replace('/[\\x00-\\x1F\\x80-\\xFF]/', '', $array_code[$id]), 'commodity_id' => $data['commodity_id'], 'quantity_ordered' => $array_order_qty[$id] == '' ? 0 : (int) $array_order_qty[$id], 'total_commodity_units' => 0, 'opening_balance' => 0, 'total_receipts' => 0, 'total_issues' => 0, 'comment' => '', 'closing_stock_' => 0, 'closing_stock' => 0, 'days_out_of_stock' => 0, 'date_added' => '', 'losses' => 0, 'status' => 0, 'adjustmentpve' => 0, 'adjustmentnve' => 0, 'historical' => 0));
}
foreach ($temp as $key => $value) {
if ($value['commodity_code'] == "" || $value['quantity_ordered'] == 0) {
unset($temp[$key]);
}
}
$array_id = array();
$array_codes = array();
$main_array = array();
$weka = array();
$k = 0;
// echo "<pre>";print_r($temp);echo "</pre>";exit;
foreach ($temp as $keys) {
$kemsa = $keys['commodity_code'];
$kemsa = preg_replace('/[\\x00-\\x1F\\x80-\\xFF]/', '', $kemsa);
$unit_cost = (int) $keys['unit_cost'];
//echo $unit_cost.'-'.$k++;echo '<pre>';
$get_id = Commodities::get_id($kemsa, $unit_cost);
//print_r( $get_id.$k++);echo '<pre>';
$weka[] = $get_id;
$array_codes[] = $kemsa;
$main_array[] = $keys;
foreach ($get_id as $key2) {
$array_id[] = $key2['id'];
$array_total_units[] = $key2['total_commodity_units'];
//echo '<pre>'; print_r($key2.'-'.$k++);echo '<pre>';
}
}
//exit;
$array_combined = array();
$id_count = count($main_array);
// echo '<pre>'; print_r($id_count);echo '<pre>'; exit;
for ($i = 1; $i < $id_count; $i++) {
$main_array[$i]['commodity_id'] = $array_id[$i];
$main_array[$i]['total_commodity_units'] = $array_total_units[$i];
}
$data['order_details'] = $data['facility_order'] = $main_array;
} else {
//create new array to hold pushed amc values
$new = array();
foreach ($items as $value) {
$drud_id = $value['commodity_id'];
$historical = $value['historical'];
for ($i = 0; $i < count($items); $i++) {
if ($drud_id == $amc_calc[$i]['commodity_id']) {
$historical = $amc_calc[$i]['amc_packs'];
}
}
$unit_size = $value['unit_size'];
$commodity_name = $value['commodity_name'] . ' (' . $unit_size . ')';
array_push($new, array('sub_category_name' => $value['sub_category_name'], 'commodity_name' => $commodity_name, 'unit_size' => $unit_size, 'unit_cost' => $value['unit_cost'], 'commodity_code' => $value['commodity_code'], 'commodity_id' => $value['commodity_id'], 'quantity_ordered' => $value['quantity_ordered'], 'total_commodity_units' => $value['total_commodity_units'], 'opening_balance' => $value['opening_balance'], 'total_receipts' => $value['total_receipts'], 'total_issues' => $value['total_issues'], 'comment' => $value['comment'], 'closing_stock_' => $value['closing_stock_'], 'closing_stock' => $value['closing_stock'], 'days_out_of_stock' => $value['days_out_of_stock'], 'date_added' => $value['date_added'], 'losses' => $value['losses'], 'status' => $value['status'], 'adjustmentpve' => $value['adjustmentpve'], 'adjustmentnve' => $value['adjustmentnve'], 'historical' => round($historical)));
// array_push($new, array('sub_category_name' => $value['sub_category_name'], 'commodity_name' => $value['commodity_name'], 'unit_size' => $value['unit_size'], 'unit_cost' => $value['unit_cost'], 'commodity_code' => $value['commodity_code'], 'commodity_id' => $value['commodity_id'], 'quantity_ordered' => $value['quantity_ordered'], 'total_commodity_units' => $value['total_commodity_units'], 'opening_balance' => $value['opening_balance'], 'total_receipts' => $value['total_receipts'], 'total_issues' => $value['total_issues'], 'comment' => $value['comment'], 'closing_stock_' => $value['closing_stock_'], 'closing_stock' => $value['closing_stock'], 'days_out_of_stock' => $value['days_out_of_stock'], 'date_added' => $value['date_added'], 'losses' => $value['losses'], 'status' => $value['status'], 'adjustmentpve' => $value['adjustmentpve'], 'adjustmentnve' => $value['adjustmentnve'], 'historical' => round($historical)));
}
$items = $new;
$data['order_details'] = $data['facility_order'] = $items;
}
// $data['facility_order'] = $items;
// echo '<pre>'; print_r($items);echo '<pre>'; exit;
// $facility_code = $this -> session -> userdata('facility_id');
$facility_data = Facilities::get_facility_name_($facility_code);
// echo "<pre>";print_r($facility_data);
$source_name = $source == 2 ? 'MEDS' : 'KEMSA';
$data['content_view'] = $source == 2 ? "facility/facility_orders/facility_order_meds_new" : "facility/facility_orders/facility_order_from_kemsa_v";
$data['facility_code'] = $facility_code;
$data['title'] = "Facility New Order";
$data['banner_text'] = "Facility New Order " . $source_name;
$data['drawing_rights'] = $facility_data[0]['drawing_rights'];
$data['facility_commodity_list'] = $source == 2 ? Commodities::get_meds_commodities_not_in_facility($facility_code, $source) : Commodities::get_commodities_not_in_facility($facility_code);
// echo '<pre>'; print_r($data['facility_commodity_list']);echo '<pre>'; exit;
$this->load->view('shared_files/template/template', $data);
}
public function create_order_delivery_color_coded_table($order_id)
{
// get the order and order details here
$detail_list = facility_order_details::get_order_details($order_id, true);
$dates = facility_orders::get_order_($order_id)->toArray();
$facility_name = Facilities::get_facility_name_($dates[0]['facility_code'])->toArray();
$facility_name = $facility_name[0]['facility_name'];
//set up the details
$table_body = "";
$total_fill_rate = 0;
$order_value = 0;
//get the lead time
$ts1 = strtotime(date($dates[0]["order_date"]));
$ts2 = strtotime(date($dates[0]["deliver_date"]));
$seconds_diff = $ts2 - $ts1;
//strtotime($a_date) ? date('d M, Y', strtotime($a_date)) : "N/A";
$date_diff = strtotime($dates[0]["deliver_date"]) ? floor($seconds_diff / 3600 / 24) : "N/A";
$order_date = strtotime($dates[0]["order_date"]) ? date('D j M, Y', $ts1) : "N/A";
$deliver_date = strtotime($dates[0]["deliver_date"]) ? date('D j M, Y', $ts2) : "N/A";
$kemsa_order_no = $dates[0]['kemsa_order_id'];
$order_total = number_format($dates[0]['order_total'], 2, '.', ',');
$actual_order_total = number_format($date[0]['deliver_total'], 2, '.', ',');
$tester = count($detail_list);
if ($tester == 0) {
} else {
foreach ($detail_list as $rows) {
//setting the values to display
$received = $rows['quantity_recieved'];
$price = $rows['unit_cost'];
$ordered = $rows['quantity_ordered_unit'];
$code = $rows['commodity_id'];
$drug_name = $rows['commodity_name'];
$kemsa_code = $rows['commodity_code'];
$unit_size = $rows['unit_size'];
$total_units = $rows['total_commodity_units'];
$cat_name = $rows['sub_category_name'];
$received = round(@$received / $total_units);
$fill_rate = round(@($received / $ordered) * 100);
$total = $price * $ordered;
$total_ = $price * $received;
$total_fill_rate = $total_fill_rate + $fill_rate;
switch (true) {
case $fill_rate == 0:
$table_body .= "<tr style='background-color: #FBBBB9;'>";
$table_body .= "<td>{$cat_name}</td>";
$table_body .= '<td>' . $drug_name . '</td><td>' . $kemsa_code . '</td>' . '<td>' . $unit_size . '</td>';
$table_body .= '<td>' . $price . '</td>';
$table_body .= '<td>' . $ordered . '</td>';
$table_body .= '<td>' . number_format($total, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $received . '</td>';
$table_body .= '<td>' . number_format($total_, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $fill_rate . '% ' . '</td>';
$table_body .= '</tr>';
break;
case $fill_rate <= 60:
$table_body .= "<tr style=' background-color: #FAF8CC;'>";
$table_body .= "<td>{$cat_name}</td>";
$table_body .= '<td>' . $drug_name . '</td><td>' . $kemsa_code . '</td>' . '<td>' . $unit_size . '</td>';
$table_body .= '<td>' . $price . '</td>';
$table_body .= '<td>' . $ordered . '</td>';
$table_body .= '<td>' . number_format($total, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $received . '</td>';
$table_body .= '<td>' . number_format($total_, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $fill_rate . '% ' . '</td>';
$table_body .= '</tr>';
break;
case $fill_rate > 100.01:
case $fill_rate == 100.01:
$table_body .= "<tr style='background-color: #ea1e17'>";
$table_body .= "<td>{$cat_name}</td>";
$table_body .= '<td>' . $drug_name . '</td><td>' . $kemsa_code . '</td>' . '<td>' . $unit_size . '</td>';
$table_body .= '<td>' . $price . '</td>';
$table_body .= '<td>' . $ordered . '</td>';
$table_body .= '<td>' . number_format($total, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $received . '</td>';
$table_body .= '<td>' . number_format($total_, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $fill_rate . '% ' . '</td>';
$table_body .= '</tr>';
break;
case $fill_rate == 100:
$table_body .= "<tr style=' background-color: #C3FDB8;'>";
$table_body .= "<td>{$cat_name}</td>";
$table_body .= '<td>' . $drug_name . '</td><td>' . $kemsa_code . '</td>' . '<td>' . $unit_size . '</td>';
$table_body .= '<td>' . $price . '</td>';
$table_body .= '<td>' . $ordered . '</td>';
$table_body .= '<td>' . number_format($total, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $received . '</td>';
$table_body .= '<td>' . number_format($total_, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $fill_rate . '% ' . '</td>';
$table_body .= '</tr>';
break;
default:
$table_body .= "<tr>";
$table_body .= "<td>{$cat_name}</td>";
$table_body .= '<td>' . $drug_name . '</td><td>' . $kemsa_code . '</td>' . '<td>' . $unit_size . '</td>';
$table_body .= '<td>' . $price . '</td>';
$table_body .= '<td>' . $ordered . '</td>';
$table_body .= '<td>' . number_format($total, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $received . '</td>';
$table_body .= '<td>' . number_format($total_, 2, '.', ',') . '</td>';
$table_body .= '<td>' . $fill_rate . '% ' . '</td>';
$table_body .= '</tr>';
break;
}
}
$order_value = round($total_fill_rate / count($detail_list), 0, PHP_ROUND_HALF_UP);
}
$message = <<<HTML_DATA
\t<table>
\t\t\t<tr>
\t\t<th colspan='11'>
\t\t<p>{$facility_name}</p>
\t\t<p>Fill rate(Quantity Ordered/Quantity Received)</p>
<p style="letter-spacing: 1px;font-weight: bold;text-shadow: 0 1px rgba(0, 0, 0, 0.1);">
Facility Order No {$order_id}| KEMSA Order No {$kemsa_order_no} | Total ordered value(ksh) {$order_total} | Total recieved order value(ksh) {$actual_order_total} |Date Ordered {$order_date}| Date Delivered {$deliver_date}| Order lead Time {$date_diff}; day(s)</p>
\t\t</th>
\t\t</tr>
\t\t<tr>
\t\t<th width="50px" style="background-color: #C3FDB8; "></th>
\t\t<th>Full Delivery 100%</th>
\t\t<th width="50px" style="background-color:#FFFFFF"></th>
\t\t<th>Ok Delivery 60%-less than 100%</th>
\t\t<th width="50px" style="background-color:#FAF8CC;"></th>
\t\t<th>Partial Delivery less than 60% </th>
\t\t<th width="50px" style="background-color:#FBBBB9;"></th>
\t\t<th>Problematic Delivery 0% </th>
\t\t<th width="50px" style="background-color:#ea1e17;"></th>
\t\t<th>Problematic Delivery over 100%</th>
\t\t</tr></table> </br></n>
<table id="main1" width="100%" class="row-fluid table table-bordered">
\t<thead>
\t\t<tr>
\t\t<th><strong>Category</strong></th>
\t\t<th><strong>Description</strong></th>
\t\t<th><strong>Commodity Code</strong></th>
\t\t<th><strong>Unit Size</strong></th>
\t\t<th><strong>Unit Cost Ksh</strong></th>
\t\t<th><strong>Quantity Ordered</strong></th>
\t\t<th><strong>Total Cost</strong></th>
\t\t<th><strong>Quantity Received</strong></th>
\t\t<th><strong>Total Cost</strong></th>
\t\t<th><strong>Fill rate</strong></th>\t
\t\t</tr>
\t</thead>
\t<tbody>\t
\t\t {$table_body}\t
\t</tbody>
</table>
<br></n>
HTML_DATA;
return array('table' => $message, 'date_ordered' => $order_date, 'date_received' => $deliver_date, 'order_total' => $order_total, 'actual_order_total' => $actual_order_total, 'lead_time' => $date_diff, 'facility_name' => $facility_name);
}
public function facility_order()
{
//$this -> load -> library('PHPExcel');
//ini_set("max_execution_time", "1000000");
$facility_code = $this->session->userdata('facility_id');
$items = Facility_Transaction_Table::get_commodities_for_ordering($facility_code);
if (isset($_FILES['file']) && $_FILES['file']['size'] > 0) {
$ext = pathinfo($_FILES["file"]['name'], PATHINFO_EXTENSION);
//echo $ext;
if ($ext == 'xls') {
$excel2 = PHPExcel_IOFactory::createReader('Excel5');
} else {
if ($ext == 'xlsx') {
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
} else {
die('Invalid file format given' . $_FILES['file']);
}
}
$excel2 = $objPHPExcel = $excel2->load($_FILES["file"]["tmp_name"]);
// Empty Sheet
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$temp = array();
$facility_code = $sheet->getCell('H4')->getValue();
// Loop through each row of the worksheet in turn
$array_code = array();
$array_commodity = array();
$array_category = array();
$array_pack = array();
$array_price = array();
$array_order_qty = array();
$array_order_val = array();
$array_index = array();
//$array_code=array();
for ($row = 17; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $objPHPExcel->getActiveSheet()->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
//var_dump($rowData);
//count($rowData);
$array_index[] = $rowData[0][1];
$array_code[] = $rowData[0][2];
$array_commodity[] = $rowData[0][3];
$array_category[] = $rowData[0][4];
$array_price[] = $rowData[0][6];
$array_order_qty[] = (int) $rowData[0][7];
$array_order_val[] = $rowData[0][8];
$array_pack[] = $rowData[0][5];
//if(isset($rowData[0][2]) && $rowData[0][2]!='Product Code'){
//echo '<pre>';print_r($rowData[0][7]); echo '</pre>';
//foreach($items as $key=> $data){
//echo '<pre>';print_r($rowData); echo '</pre>';
//}
//}
}
foreach ($array_index as $id => $key) {
//foreach($items as $key=> $data){
array_push($temp, array('sub_category_name' => $array_category[$key], 'commodity_name' => $array_commodity[$key], 'unit_size' => $array_pack[$key], 'unit_cost' => $array_price[$key], 'commodity_code' => $array_code[$key], 'commodity_id' => $data['commodity_id'], 'quantity_ordered' => $array_order_qty[$key] == '' ? 0 : (int) $array_order_qty[$key], 'total_commodity_units' => $array_price[$key] * (int) $array_order_qty[$key], 'opening_balance' => 0, 'total_receipts' => 0, 'total_issues' => 0, 'comment' => '', 'closing_stock_' => 0, 'closing_stock' => 0, 'days_out_of_stock' => 0, 'date_added' => '', 'losses' => 0, 'status' => 0, 'adjustmentpve' => 0, 'adjustmentnve' => 0, 'historical' => 0));
//unset($items[$key]);
// }
}
foreach ($temp as $key => $value) {
//echo '<pre>';print_r($value['commodity_code']); echo '</pre>';
if ($value['commodity_code'] == "" || $value['quantity_ordered'] == "") {
unset($temp[$key]);
}
}
//$c = array_combine($array_code, $array_commodity);
echo '<pre>';
print_r($temp);
echo '</pre>';
//var_dump();
exit;
//unset($objPHPExcel);
$data['order_details'] = $data['facility_order'] = $temp;
} else {
$data['order_details'] = $data['facility_order'] = $items;
}
//var_dump($temp);exit;
$facility_code = $this->session->userdata('facility_id');
$facility_data = Facilities::get_facility_name_($facility_code)->toArray();
$data['content_view'] = "facility/facility_orders/facility_order_from_kemsa_v";
$data['title'] = "Facility New Order";
$data['banner_text'] = "Facility New Order";
$data['drawing_rights'] = $facility_data[0]['drawing_rights'];
$data['facility_commodity_list'] = Commodities::get_commodities_not_in_facility($facility_code);
$this->load->view('shared_files/template/template', $data);
}
public function Decommission()
{
//Change status of commodities to decommissioned
$date = date('Y-m-d');
$facility = $this->session->userdata('news');
$facility_code = $this->session->userdata('news');
$user_id = $this->session->userdata('user_id');
$facility_name_array = Facilities::get_facility_name_($facility_code);
$facility_name = $facility_name_array['facility_name'];
$districtName = $this->session->userdata('full_name');
$myobj1 = Doctrine::getTable('Districts')->find($facility_name_array['district']);
$disto_name = $myobj1->district;
$county = $myobj1->county;
$myobj2 = Doctrine::getTable('Counties')->find($county);
$county_name = $myobj2->county;
$myobj3 = Doctrine::getTable('user')->find($user_id);
$creator_name1 = $myobj3->fname;
$creator_name2 = $myobj3->lname;
$total = 0;
//Create PDF of Expired Drugs that are to be decommisioned. check here
$decom = Facility_Stock::get_facility_expired_stuff($date, $facility);
//create the report title
$html_title = "<div ALIGN=CENTER><img src='Images/coat_of_arms.png' height='70' width='70'style='vertical-align: top;' > </img></div>\n \n <div style='text-align:center; font-family: arial,helvetica,clean,sans-serif;display: block; font-weight: bold; font-size: 14px;'>\n Ministry of Health</div>\n <div style='text-align:center; font-family: arial,helvetica,clean,sans-serif;display: block; font-weight: bold;display: block; font-size: 13px;'>Health Commodities Management Platform</div>\n <div style='text-align:center; font-family: arial,helvetica,clean,sans-serif;display: block; font-weight: bold;display: block; font-size: 13px;'>Expired Commodities Between " . date("F", strtotime("-1 month")) . " - " . date("F") . " " . date('Y') . "</div><hr /> ";
/*****************************setting up the report*******************************************/
$html_body = '';
$html_body .= '<style>table.data-table {border: 1px solid #DDD;margin: 10px auto;border-spacing: 0px;}
table.data-table th {border: none;color: #036;text-align: center;background-color: #F5F5F5;border: 1px solid #DDD;border-top: none;max-width: 450px;}
table.data-table td, table th {padding: 4px;}
table.data-table td {border: none;border-left: 1px solid #DDD;border-right: 1px solid #DDD;height: 30px;margin: 0px;border-bottom: 1px solid #DDD;}
.col5{background:#C9C299;}</style>' . "<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>Unit Size</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(Unit Size)</strong></th>
<th><strong>Unit Cost (Ksh)</strong></th>
<th><strong>Cost of Expired (Ksh)</strong></th>
</tr> </thead><tbody>';
/*******************************begin adding data to the report*****************************************/
foreach ($decom as $drug) {
$drug_id = $drug['drug_id'];
$batch = $drug['batch_no'];
$mau = $drug['manufacture'];
$name = $drug['drug_name'];
$code = $drug['kemsa_code'];
$code = isset($code) ? "KEMSA: code" . $code : '';
$unitS = $drug['unit_size'];
$unitC = $drug['unit_cost'];
$calc = $drug['balance'];
$total_units = $drug['total_units'];
$thedate = $drug['expiry_date'];
$balance = round($calc / $total_units, 1);
$cost = $balance * $unitC;
$formatme = new DateTime($thedate);
$myvalue = $formatme->format('d M Y');
$total = $total + $cost;
//get the current balance of the commodity
$facility_stock = Facility_Stock::get_facility_drug_total($facility, $drug_id)->toArray();
$mydata3 = array('facility_code' => $facility, 's11_No' => '(Loss) Expiry', 'kemsa_code' => $drug_id, 'batch_no' => $batch, 'expiry_date' => $thedate, 'receipts' => $calc * -1, 'balanceAsof' => $facility_stock[0]['balance'], 'date_issued' => date('y-m-d'), 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('identity'));
$seconds_diff = strtotime(date("y-m-d")) - strtotime($myvalue);
$date_diff = floor($seconds_diff / 3600 / 24);
Facility_Issues::update_issues_table($mydata3);
$inserttransaction_1 = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select `losses` from `facility_transaction_table`\n WHERE `kemsa_code`= '{$drug_id}' and availability='1' and facility_code={$facility}; ");
$new_value = $inserttransaction_1[0]['losses'] + $calc;
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection();
$inserttransaction1 = Doctrine_Manager::getInstance()->getCurrentConnection();
$inserttransaction2 = Doctrine_Manager::getInstance()->getCurrentConnection();
// update the transaction table with the loss
$inserttransaction->execute("UPDATE `facility_transaction_table` SET losses ={$new_value}\n WHERE `kemsa_code`= '{$drug_id}' and availability='1' and facility_code={$facility}; ");
// update the transaction table with the new closing balance
$inserttransaction1->execute("UPDATE `facility_transaction_table` SET closing_stock = (SELECT SUM(balance)\n\t\t\t FROM facility_stock WHERE kemsa_code = '{$drug_id}' and status='1' and facility_code='{$facility}')\n WHERE `kemsa_code`= '{$drug_id}' and availability='1' and facility_code ='{$facility}'; ");
/// update the facility issues and set the commodity to expired
$inserttransaction->execute("UPDATE `facility_stock` SET status =2\n WHERE `kemsa_code`= '{$drug_id}' and facility_code={$facility}; ");
$html_body .= '<tr><td>' . $code . '</td>
<td>' . $name . '</td>
<td >' . $unitS . '</td>
<td>' . $batch . '</td>
<td>' . $mau . '</td>
<td>' . $myvalue . '</td>
<td>' . $date_diff . '</td>
<td >' . $calc . '</td>
<td >' . $unitC . '</td>
<td >' . number_format($cost, 2, '.', ',') . '</td>
</tr>';
/***********************************************************************************************/
}
$html_body .= '
<tr>
<td colspan="10">
<b style="float: right; margin-right:5.0em">TOTAL cost(Ksh) of Expiries: ' . number_format($total, 2, '.', ',') . '</b>
</tr>
</tbody>
</table>';
$this->load->library('mpdf');
$this->load->helper('file');
$this->mpdf = new mPDF('', 'A4-L', 0, '', 15, 15, 16, 16, 9, 9, '');
$this->mpdf->WriteHTML($html_title);
$this->mpdf->defaultheaderline = 1;
$this->mpdf->simpleTables = true;
$this->mpdf->WriteHTML($html_body);
$this->mpdf->SetFooter("{DATE d/m/Y }|{PAGENO}/{nb}|Prepared by: {$creator_name1} {$creator_name2}");
$report_name = 'Facility_Expired_Commodities_' . $facility . "_" . $date . "_" . $facility_name;
if (!write_file('./pdf/' . $report_name . '.pdf', $this->mpdf->Output('$report_name', 'S'))) {
$this->session->set_flashdata('system_error_message', 'An error occured, when creating a PDF contact system ADMIN');
redirect("/");
} else {
if (!$this->send_stock_decommission_email($html_body, 'Decommission Report For ' . $facility, './pdf/' . $report_name . '.pdf')) {
delete_files('./pdf/' . $report_name . '.pdf');
$this->session->set_flashdata('system_success_message', 'Stocks Have Been Decommissioned');
redirect("/");
} else {
$this->session->set_flashdata('system_error_message', 'An error occured, the items were decommissioned but there was a problem in sending an email file:' . $report_name . '.pdf');
redirect("/");
}
}
}
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');
}