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 add_stock_level() { $facility_c = $this->session->userdata('news'); $kemsa_code = $_POST['drug_id']; $expiry_date = $_POST['expiry_date']; $batch_no = $_POST['batchNo']; $manuf = $_POST['manuf']; $a_stock = $_POST['qreceived']; $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); Facility_Stock::update_facility_stock($mydata); $kemsa_code_ = $kemsa_code[$i]; $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 `opening_balance` from `facility_transaction_table`\n WHERE `kemsa_code`= '{$kemsa_code_}' and availability='1' and facility_code={$facility_c}; "); $new_value = $inserttransaction_1[0]['opening_balance'] + $a_stock[$i]; $inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection(); $inserttransaction->execute("UPDATE `facility_transaction_table` SET `opening_balance` ={$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 availability='1' and facility_code='{$facility_c}')\n WHERE `kemsa_code`= '{$kemsa_code_}' and availability='1' and facility_code ='{$facility_c}'; "); $facility_stock = Facility_Stock::get_facility_drug_total($facility_c, $kemsa_code_)->toArray(); $mydata = array('facility_code' => $facility_c, 's11_No' => 'Update Stock Level', 'kemsa_code' => $kemsa_code[$i], 'receipts' => $a_stock[$i], 'batch_no' => $batch_no[$i], 'expiry_date' => date('y-m-d ,', strtotime($expiry_date[$i])), 'balanceAsof' => $facility_stock[0]['balance'], 'date_issued' => date('y-m-d'), 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('identity')); Facility_Issues::update_issues_table($mydata); } else { $mydata3 = array('facility_code' => $facility_c, 's11_No' => 'Physical Stock Count', 'kemsa_code' => $kemsa_code_, 'batch_no' => $batch_no[$i], 'expiry_date' => date('y-m-d ,', strtotime($expiry_date[$i])), 'qty_issued' => 0, 'balanceAsof' => $a_stock[$i], 'date_issued' => date('y-m-d'), 'issued_to' => 'N/A', 'issued_by' => $this->session->userdata('identity')); Facility_Issues::update_issues_table($mydata3); $mydata2 = array('Facility_Code' => $facility_c, 'Kemsa_Code' => $kemsa_code_, 'Opening_Balance' => $a_stock[$i], 'Total_Issues' => 0, 'Total_Receipts' => 0, 'Adj' => 0, 'Closing_Stock' => $a_stock[$i], 'availability' => 1); Facility_Transaction_Table::update_facility_table($mydata2); } } } Update_stock_first_temp::delete_facility_temp(NULL, $facility_c); //test ////////////////////////////////////////////////////////////////////////////////////////// $this->send_stock_update_sms(); $this->session->set_flashdata('system_success_message', "Stock Levels Have Been Updated"); redirect('stock_management/stock_level'); //////////////////////////////////////////////////////////////////////////////////////// }