public function kemsa()
{
$data['title'] = "Stock";
$data['content_view'] = "potentialExp_v";
$data['banner_text'] = "Potential Expiries";
$data['link'] = "potentialExp_v";
$data['stocks'] = Facility_Stock::Allexpiries();
//echo count($stocks);
//$data['tester']=Drug::getSome();
$data['quick_link'] = "potentialExp_v";
$this->load->view("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 potentialExpiries()
{
//New
$facility_c = $this->session->userdata('news');
$data['title'] = "Stock";
$data['content_view'] = "potentialExp";
$data['banner_text'] = "Potential Expiries";
$data['link'] = "order_management";
$data['stocks'] = Facility_Stock::expiries($facility_c);
//$data['tester']=Drug::getSome();
$data['quick_link'] = "stock_view";
$this->load->view("template", $data);
}
public function get_county_cost_of_expiries_new($year = null, $month = null, $district_id = null, $option = null, $facility_code = null)
{
$county_id = $this->session->userdata('county_id');
$county_name = counties::get_county_name($county_id);
$category_data = array();
$series_data = array();
$temp_array = array();
$months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$district_data = isset($district_id) && $district_id > 0 ? districts::get_district_name($district_id)->toArray() : null;
$district_name_ = isset($district_data) ? " :" . $district_data[0]['district'] . " subcounty" : null;
$year = $year != "null" ? $year : date("Y");
$month = isset($month) ? $month : date("m");
$option_new = isset($option) && $option != 'null' ? $option : "ksh";
$facility_code = (int) $facility_code > 0 ? facilities::get_facility_name($facility_code)->toArray() : facilities::get_facilities_which_are_online($district_id);
$district_id = isset($district_id) && $district_id > 0 ? array('id' => $district_id) : districts::getDistrict($county_id)->toArray();
if (count($district_id) > 1 && $month == "null") {
$category_data = array_merge($category_data, $months);
$commodity_array = Facility_Stock::get_county_cost_of_exipries_new($county_id, $year, $month, "all", $option_new, null);
foreach ($commodity_array as $data) {
$temp_array = array_merge($temp_array, array($data["cal_month"] => $data['total']));
}
foreach ($months as $key => $data) {
$val = array_key_exists($data, $temp_array) ? (int) $temp_array[$data] : (int) 0;
$series_data = array_merge($series_data, array($val));
}
}
if (count($facility_code) == 1 && count($district_id) == 1) {
$commodity_array = Facility_Stock::get_county_cost_of_exipries_new($county_id, $year, $month, 'facility', $option_new, $facility_code[0]['facility_code']);
foreach ($commodity_array as $facility_data) {
$category_data = array_merge($category_data, array($facility_data['drug_name']));
$series_data = array_merge($series_data, array(array((int) $facility_data['total'])));
}
}
if (count($district_id) == 1 && count($facility_code) >= 1 && count($category_data) == 0) {
foreach ($facility_code as $facility_) {
$category_data = array_merge($category_data, array($facility_['facility_name']));
$commodity_array = Facility_Stock::get_county_cost_of_exipries_new($county_id, $year, $month, $facility_['district'], $option_new, $facility_['facility_code']);
$series_data = array_merge($series_data, array(array((int) $commodity_array[0]['total'])));
}
}
if (count($district_id) > 1 && $month != "null") {
$district_ = districts::getDistrict($county_id)->toArray();
foreach ($district_ as $data) {
$category_data = array_merge($category_data, array($data['district']));
$commodity_array = Facility_Stock::get_county_cost_of_exipries_new($county_id, $year, $month, $data['id'], $option_new, "null");
$series_data = array_merge($series_data, array(array((int) $commodity_array[0]['total'])));
}
}
$data = array();
$data['category_data'] = stripslashes(json_encode($category_data));
$data['series_data'] = json_encode($series_data);
$data['year'] = $year;
$data['month'] = date("F", strtotime(date($year . "-" . $month)));
$data['total'] = 2;
$data['expiry_option'] = $option_new;
$data['county'] = $county_name[0]['county'];
$data['consumption_option'] = $option_new;
$this->load->view("county/ajax_view/county_expiries_graphical_data_v", $data);
}
public function donation()
{
$facility_c = $this->session->userdata('news');
$usernow = $this->session->userdata('identity');
$kemsa_code = $_POST['drug_id'];
$expiry_date = $_POST['expiry_date'];
$batch_no = $_POST['batchNo'];
$manuf = $_POST['manuf'];
$a_stock = $_POST['qreceived'];
$source = $_POST['source'];
$count = count($kemsa_code);
$orderDate = date('y-m-d H:i:s');
for ($i = 0; $i <= $count; $i++) {
if (isset($kemsa_code[$i]) && $kemsa_code[$i] != '') {
$mydata = array('facility_code' => $facility_c, 'kemsa_code' => $kemsa_code[$i], 'batch_no' => $batch_no[$i], 'manufacture' => $manuf[$i], 'expiry_date' => date('y-m-d', strtotime($expiry_date[$i])), 'balance' => $a_stock[$i], 'quantity' => $a_stock[$i], 'stock_date' => $orderDate, 'sheet_no' => $source[$i], 'source' => $source[$i]);
////get the current balance of the same commodity before adding new stock level
$current_bal = Facility_Stock::getAll($kemsa_code[$i], $facility_c);
///updating the facility stock with the new data from the donation
Facility_Stock::update_facility_stock($mydata);
//adding data to issues table
$mydata = array('facility_code' => $facility_c, 'kemsa_code' => $kemsa_code[$i], 's11_No' => "(+ve Adj) Stock Addition", 'batch_no' => $batch_no[$i], 'expiry_date' => date('y-m-d', strtotime($expiry_date[$i])), 'qty_issued' => 0, 'balanceAsof' => $current_bal[0]['total_balance'], 'receipts' => $a_stock[$i], 'date_issued' => date('y-m-d'), 'issued_to' => "Source :" . $source[$i], 'issued_by' => $usernow);
$u = new Facility_Issues();
$u->fromArray($mydata);
$u->save();
}
}
//getting facility regarding the stocks added above ^
$data = Facility_Stock::count_facility_stock($facility_c, $orderDate);
foreach ($data as $infor) {
$qty = $infor->quantity1;
$kemsa_code_ = $infor->kemsa_code;
$facility_has_commodity = Facility_Transaction_Table::get_if_drug_is_in_table($facility_c, $kemsa_code_);
if ($facility_has_commodity > 0) {
$inserttransaction_1 = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select `adj` from `facility_transaction_table`\n WHERE `kemsa_code`= '{$kemsa_code_}' and availability='1' and facility_code={$facility_c}; ");
$new_value = $inserttransaction_1[0]['adj'] + $qty;
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection();
$inserttransaction->execute("UPDATE `facility_transaction_table` SET adj ={$new_value}\n WHERE `kemsa_code`= '{$kemsa_code_}' and availability='1' and facility_code={$facility_c}; ");
$inserttransaction1 = Doctrine_Manager::getInstance()->getCurrentConnection();
$inserttransaction1->execute("UPDATE `facility_transaction_table` SET closing_stock = (SELECT SUM(balance)\n\t\t\t FROM facility_stock WHERE kemsa_code = '{$kemsa_code_}' and status='1' and facility_code='{$facility_c}')\n WHERE `kemsa_code`= '{$kemsa_code_}' and availability='1' and facility_code ='{$facility_c}'; ");
} else {
$mydata2 = array('Facility_Code' => $facility_c, 'Kemsa_Code' => $kemsa_code_, 'Opening_Balance' => 0, 'Total_Issues' => 0, 'Total_Receipts' => 0, 'Adj' => $qty, 'Closing_Stock' => $qty, 'availability' => 1);
Facility_Transaction_Table::update_facility_table($mydata2);
}
}
$this->send_stock_update_sms();
$this->session->set_flashdata('system_success_message', "You have received {$count} item(s)");
redirect('issues_main');
}
public function stock_level($msg = Null)
{
$facility_c = $this->session->userdata('news');
$checker = $this->uri->segment(3);
$data['title'] = "Stock";
$data['content_view'] = "facility/facility_data/stock_level_v";
$data['banner_text'] = "Physical Stock";
$data['link'] = "order_management";
if (isset($msg)) {
$data['msg'] = $msg;
$data['update'] = 'update stock levels';
}
if ($msg == NULL) {
$data['update'] = NULL;
$data['msg'] = " ";
$data['checker'] = "no_order";
}
if ($checker == "v") {
$data['msg'] = "Verify that the system stock levels are the same as your physical stock count";
$data['update'] = 'update stock levels';
}
if ($msg == 'c0N123') {
$data['update'] = NULL;
$data['msg'] = "Please confirm your stock details before placing your order";
}
$data['facility_order'] = Facility_Transaction_Table::get_all($facility_c);
$data['max_date'] = Facility_Stock::get_max_date($facility_c)->toArray();
$data['name_of_person'] = Facility_Issues::get_last_person_who_issues($facility_c);
//$data['quick_link'] = "stock_level";
$this->load->view("template", $data);
}
public function gen_pdf()
{
$timeinterval = $_POST['timer'];
$facility_c = $this->session->userdata('news');
$report = Facility_Stock::expiries($facility_c, $timeinterval);
$report_name = 'Potential Expiries ' . $facility_c . ' Next ' . $timeinterval . 'Months';
$title = 'test';
/**************************************set the style for the table****************************************/
$html_data = '<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>';
$html_data1 = '';
/*****************************setting up the report*******************************************/
$html_data1 .= '<table class="data-table"><thead>
<tr >
<th><strong>Kemsa Code</strong></th>
<th><strong>Description</strong></th>
<th><strong>Unit size</strong></th>
<th><strong>Unit Cost</strong></th>
<th><strong>Batch No</strong></th>
<th><strong>Expiry Date</strong></th>
<th><strong><b>Units</b></strong></th>
<th><strong><b>Stock Worth(Ksh)</b></strong></th>
</tr><tbody>';
/*******************************begin adding data to the report*****************************************/
foreach ($report as $drug) {
foreach ($drug->Code as $d) {
$name = $d->Drug_Name;
$code = $d->Kemsa_Code;
$unitS = $d->Unit_Size;
$unitC = $d->Unit_Cost;
$calc = $drug->balance;
$thedate = $drug->expiry_date;
$formatme = new DateTime($thedate);
$myvalue = $formatme->format('d M Y');
$html_data1 .= '<tr>
<td>' . $code . '</td>
<td>' . $name . '</td>
<td>' . $unitS . '</td>
<td>' . $unitC . '</td>
<td>' . $drug->batch_no . '</td>
<td>' . $myvalue . '</td>
<td>' . $drug->balance . '</td>
<td >' . $calc * $unitC . '</td>
</tr>';
/***********************************************************************************************/
}
}
$html_data1 .= '</tbody></table>';
$html_data .= $html_data1;
$date = new DateTime();
$interval = new DateInterval('P' . $timeinterval . 'M');
$date->add($interval);
$formateddate = $date->format('M-d-Y') . "\n";
$this->generatePE_pdf($report_name, $title, $html_data, $facility_c, $timeinterval, $formateddate);
}
