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 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');
}