public function get_order_details_report()
{
$order_id = 0;
$kemsa_id = NULL;
$order_id = $this->uri->segment(3);
$kemsa_id = $this->uri->segment(4);
$detail_list = Orderdetails::get_order($order_id);
$table_body = "";
$total_fill_rate = 0;
$order_value = 0;
$tester = count($detail_list);
if ($tester == 0) {
} else {
foreach ($detail_list as $rows) {
//setting the values to display
$received = $rows->quantityRecieved;
$price = $rows->price;
$ordered = $rows->quantityOrdered;
$code = $rows->kemsa_code;
$total = $price * $ordered;
if ($ordered == 0) {
$ordered = 1;
}
$fill_rate = round($received / $ordered * 100, 0, PHP_ROUND_HALF_UP);
$total_fill_rate = $total_fill_rate + $fill_rate;
foreach ($rows->Code as $drug) {
$drug_name = $drug->Drug_Name;
$kemsa_code = $drug->Kemsa_Code;
$unit_size = $drug->Unit_Size;
foreach ($drug->Category as $cat) {
$cat_name = $cat;
}
}
switch ($fill_rate) {
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>' . $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>' . $fill_rate . '% ' . '</td>';
$table_body .= '</tr>';
break;
case $fill_rate == 100.01 || $fill_rate > 100.01:
$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>' . $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>' . $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>' . $fill_rate . '% ' . '</td>';
$table_body .= '</tr>';
break;
}
}
$order_value = round($total_fill_rate / count($detail_list), 0, PHP_ROUND_HALF_UP);
}
$table_head = '<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:#D8D8D8;}</style><div > Fill Rate = (Quantity Received / Quantity Ordered ) * 100</div>
<caption ><p style="letter-spacing: 1px;font-weight: bold;text-shadow: 0 1px rgba(0, 0, 0, 0.1);font-size: 14px; " >Facility Order No ' . $order_id . ' | KEMSA Order No ' . $kemsa_id . ' |Order Fill Rate ' . $order_value . '%</p></caption><table class="data-table" style="margin-left: 0px">
<tr>
<td width="50px" style="background-color: #C3FDB8; "></td><td>Full Delivery 100%</td><td width="50px" style="background-color:#FFFFFF"></td><td>Ok Delivery 60%-less than 100%</td><td width="50px" style="background-color:#FAF8CC;"></td><td>Partial Delivery less than 60% </td><td width="50px" style="background-color:#FBBBB9;"></td><td>Problematic Delivery 0% or over 100%</td>
</tr></table>
<table class="data-table" width="100%">
<thead>
<tr>
<th><strong>Category</strong></th>
<th><strong>Description</strong></th>
<th><strong>KEMSA Code</strong></th>
<th><strong>Unit Size</strong></th>
<th><strong>Unit Cost Ksh</strong></th>
<th><strong>Quantity Ordered</strong></th>
<th><strong>Total Cost</strong></th>
<th><strong>Quantity Received</strong></th>
<th><strong>Fill rate</strong></th>
</tr>
</thead>
<tbody>';
//echo $table_body;
$table_foot = '</tbody></table>';
$report_name = "Order-{$order_id}-detail-fill-rate";
$title = "Order number {$order_id} fill rate";
$html_data = $table_head . $table_body . $table_foot;
$report_type = 'Download PDF';
$this->generate_pdf($report_name, $title, $html_data, $report_type);
}
public function dist_order_details()
{
$delivery = $this->uri->segment(3);
$data['title'] = "Order detail View";
$data['content_view'] = "dist_orderdetail_v";
$data['banner_text'] = "Order detail View";
$data['link'] = "home";
$data['quick_link'] = "moh_order_v";
$data['detail_list'] = Orderdetails::get_order($delivery);
$this->load->view("template", $data);
}
public function update_order()
{
$this->load->helper('file');
$this->load->library('mpdf');
$new_value = @$_POST['quantity'];
$price = @$_POST['price'];
$order_id = @$_POST['order_id'];
$value = count($new_value);
$code = @$_POST['f_order_id'];
$s_quantity = @$_POST['actual_quantity'];
$order_total = 0;
$reject_order = @$_POST['reject_order_status'];
$value == 0 ? redirect("order_approval/district_orders") : $blank_data;
$user_id = $facility_c = $this->session->userdata('user_id');
if ($reject_order == 1) {
// do not update the order
} else {
for ($i = 0; $i < $value; $i++) {
$myobj = Doctrine::getTable('Orderdetails')->find($order_id[$i]);
$myobj->quantityOrdered = $new_value[$i];
$myobj->s_quantity = $s_quantity[$i];
$myobj->save();
//$total=$total*($new_value[$i]*$price[$i]);
//echo $new_value[$i];
}
}
$from_ordertbl = Ordertbl::get_order($code, $user_id);
$from_order_details = Orderdetails::get_order($code);
$total = 0;
//update the order based on how the district pham has rationalized it
$checker = 1;
$in = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT a.category_name, b.drug_name,b.total_units, b.kemsa_code, b.unit_size, b.unit_cost, \n\t\tc.quantityOrdered, c.price, c.orderNumber, c.quantityRecieved,\n\t\tc.o_balance, c.t_receipts, c.t_issues, c.adjust, c.losses, c.days, c.comment, c.c_stock,c.s_quantity\nFROM drug_category a, drug b, orderdetails c\nWHERE c.orderNumber ={$code}\nAND b.id = c.kemsa_code\nAND a.id = b.drug_category\nORDER BY a.id ASC , b.drug_name ASC ");
$jay = count($in);
//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 <div style='text-align:center; font-size: 14px;display: block;font-weight: bold;'>Order Report</div>\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><hr /> ";
//get the facility name and MFL code
foreach ($from_ordertbl as $order) {
$o_date = $order->orderDate;
$a_date = $order->approvalDate;
$o_total = $order->orderTotal;
$d_rights = $order->drawing_rights;
$bal = $d_rights - $o_total;
$creator = $order->orderby;
$approver = $order->approveby;
foreach ($order->Code as $f_name) {
$fac_name = $f_name->facility_name;
}
$mfl = $order->facilityCode;
$myobj = Doctrine::getTable('Facilities')->findOneByfacility_code($mfl);
$diasto = $myobj->district;
$myobj1 = Doctrine::getTable('Districts')->find($diasto);
$disto_name = $myobj1->district;
$county = $myobj1->county;
$myobj2 = Doctrine::getTable('Counties')->find($county);
$county_name = $myobj2->county;
$myobj_order = Doctrine::getTable('User')->find($creator);
$creator_name = $myobj_order->email;
$creator_name1 = $myobj_order->fname;
$creator_name2 = $myobj_order->lname;
$creator_telephone = $myobj_order->telephone;
}
//create the table for displaying the order details
$html_body = "<table class='data-table' width=100%>\n<tr>\n<td>MFL No: {$mfl}</td> \n<td >Health Facility Name:<br/> {$fac_name}</td>\n<td >Total OPD Visits & Revisits: </td>\n<td >Level:</td>\n<td>Dispensary</td>\n<td >Health Centre</td>\n</tr>\n<tr>\n<td>County: {$county_name}</td> \n<td > District: {$disto_name}</td>\n<td >In-patient Bed Days : </td>\n<td >Order Date:<br/> " . date('d M, Y', strtotime($o_date)) . " </td>\n<td>Order no.</td>\n<td >Reporting Period <br/>\nStart Date: <br/> End Date: " . date('d M, Y', strtotime($o_date)) . "\n</td>\n</tr>\n</table>";
$html_body .= "<style>table.data-table {border: 1px solid #DDD;margin: 10px auto;border-spacing: 0px;}\ntable.data-table th {border: none;color: #036;text-align: center;background-color: \t#FFF380;border: 1px solid #DDD;border-top: none;max-width: 450px;}\ntable.data-table td, table th {padding: 4px;}\ntable.data-table td {border: none;border-left: 1px solid #DDD;border-right: 1px solid #DDD;height: 30px;margin: 0px;border-bottom: 1px solid #DDD;}\n</style>\n<table class='data-table'>\n\n\n<thead><tr><th><b>KEMSA Code</b></th><th><b>Description</b></th><th><b>Order Unit Size</b></th><th><b>Order Unit Cost</b></th><th ><b>Opening Balance</b></th>\n<th ><b>Total Receipts</b></th><th><b>Total issues</b></th><th><b>Adjustments</b></th><th><b>Losses</b></th><th><b>Closing Stock</b></th><th><b>No days out of stock</b></th>\n<th><b>Order Quantity (Packs)</b></th><th><b>Order Quantity (Actual Units)</b></th><th><b>Order cost(Ksh)</b></th><th><b>Comment</b></th></tr> </thead><tbody>";
$html_body .= '<ol type="a">';
for ($i = 0; $i < $jay; $i++) {
if ($i == 0) {
$html_body .= '<tr style="background-color:#C6DEFF;"> <td colspan="15" >
<li> ' . $in[$i]['category_name'] . ' </li> </td></tr>';
} else {
if ($in[$i]['category_name'] != $in[$i - 1]['category_name']) {
$html_body .= '<tr style="background-color:#C6DEFF;"> <td colspan="15">
<li> ' . $in[$i]['category_name'] . ' </li> </td></tr>';
}
}
$adj = $in[$i]['adjust'];
$c_stock = $in[$i]['c_stock'];
$o_t = $in[$i]['quantityOrdered'];
$o_q = $in[$i]['price'];
$o_bal = $in[$i]['o_balance'];
$t_re = $in[$i]['t_receipts'];
$t_issues = $in[$i]['t_issues'];
$losses = $in[$i]['losses'];
$total = $o_t * $in[$i]['total_units'];
if ($o_bal == 0 && $t_re == 0 && $t_issues > 0) {
$adj = $t_issues;
}
$c_stock = $o_bal + $t_re + $adj - $losses - $t_issues;
if ($c_stock < 0) {
$adj = $c_stock * -1;
}
$c_stock = $o_bal + $t_re + $adj - $losses - $t_issues;
$html_body .= "<tr>";
$html_body .= "<td>" . $in[$i]['kemsa_code'] . "</td>";
$html_body .= "<td>" . $in[$i]['drug_name'] . "</td>";
$html_body .= "<td>" . $in[$i]['unit_size'] . "</td>";
$ot = number_format($o_t * $o_q, 2, '.', ',');
$order_total = $order_total + $o_t * $o_q;
$html_body .= "<td>{$o_q}</td>";
$html_body .= "<td>" . $o_bal . "</td>";
$html_body .= "<td>" . $t_re . "</td>";
$html_body .= "<td>" . $t_issues . "</td>";
$html_body .= "<td>" . $adj . "</td>";
$html_body .= "<td>" . $losses . "</td>";
$html_body .= "<td>" . $c_stock . "</td>";
$html_body .= "<td>" . $in[$i]['days'] . "</td>";
$html_body .= "<td>{$o_t}</td>";
$html_body .= "<td>{$total}</td>";
$html_body .= "<td>{$ot}</td>";
$html_body .= "<td>" . $in[$i]['comment'] . "</td></tr>";
}
$myobj_approve = Doctrine::getTable('User')->find($approver);
$approve_name = $myobj_approve->email;
$approve_name1 = $myobj_approve->fname;
$approve_name2 = $myobj_approve->lname;
$approve_telephone = $myobj_approve->telephone;
$bal = $d_rights - $order_total;
$html_body .= '</tbody></table></ol>';
$html_body1 = '<table class="data-table" width="100%" style="background-color: #FFF380;">
<tr style="background-color: #FFFFFF;" > <td colspan="4" ><div style="float: left" > Total Order Value:</div><div style="float: right" >KSH ' . number_format($order_total, 2, '.', ',') . '</div> </td></tr>
<tr style="background-color: #FFFFFF;" > <td colspan="4" ><div style="float: left" > Drawing Rights Available Balance:</div><div style="float: right" >KSH ' . number_format($bal, 2, '.', ',') . '</div> </td></tr>
<tr><td>FACILITY TEL NO:</td><td colspan="3">FACILITY EMAIL:</td>
</tr>
<tr><td >Prepared by (Name/Designation) ' . $creator_name1 . ' ' . $creator_name2 . '
<br/>
<br/>Email: ' . $creator_name . '</td><td>Tel: ' . $creator_telephone . '</td><td>Date: ' . date('d M, Y', strtotime($o_date)) . '</td><td>Signature</td>
</tr>
<tr><td>Checked by (Name/DPF/DPHN)
<br/>
<br/>Email</td><td>Tel</td><td>Date</td><td>Signature</td>
</tr>
<tr><td>Authorised by (Name/DMoH) ' . $approve_name1 . ' ' . $approve_name2 . '
<br/>
<br/>Email: ' . $approve_name . '</td><td>Tel: ' . $approve_telephone . '</td><td>Date: ' . date('d M, Y', strtotime($a_date)) . '</td><td>Signature</td>
</tr>
</table>';
$myobj = Doctrine::getTable('Ordertbl')->find($code);
$myobj->orderTotal = $order_total;
$myobj->save();
//now ganerate an order pdf from the generated report
$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->AddPage();
$this->mpdf->WriteHTML($html_body1);
if ($reject_order == 1) {
$status = 'rejected';
} else {
$status = 'approved';
}
$report_name = $status . '_facility_order_no_' . $code . '_date_' . date('d-m-y');
//$this->mpdf->Output('$report_name','I');
//exit;
if (!write_file('./pdf/' . $report_name . '.pdf', $this->mpdf->Output('$report_name', 'S'))) {
$this->session->set_flashdata('system_error_message', 'An error occured');
redirect("order_approval/district_orders");
} else {
$subject = $reject_order == 1 ? 'Rejected Order Report For ' . $fac_name : 'Approved Order Report For ' . $fac_name;
$attach_file = './pdf/' . $report_name . '.pdf';
$message = $html_title . $html_body . $html_body1;
$message_1 = $reject_order == 0 ? '<br>Please find the approved Order for ' . $fac_name . '
<br>
<br>' : '<br>Please note the order for ' . $fac_name . ' Has been rejected by ' . $approve_name1 . ' ' . $approve_name2 . '.
<br>
Find the attached order, correct it
<br>';
$response = $this->send_order_approval_email($message_1 . $message, $subject, $attach_file, $mfl, $reject_order);
if ($response) {
delete_files('./pdf/' . $report_name . '.pdf');
}
}
if ($reject_order == 1) {
$myobj = Doctrine::getTable('Ordertbl')->find($code);
$myobj->orderStatus = "rejected";
$myobj->save();
$this->send_order_approval_sms($mfl, $reject_order);
$this->session->set_flashdata('system_success_message', "Order No {$code} has been rejected and the facility personel have been notified");
} else {
$this->send_order_approval_sms($mfl, $reject_order);
$this->session->set_flashdata('system_success_message', "Order No {$code} has been approved");
}
redirect("order_approval/district_orders");
}