public static function update_orders_table($data_array)
{
$o = new facility_orders();
$o->fromArray($data_array);
$o->save();
return TRUE;
}
public function notification_dashboard()
{
$facility_code = !$this->session->userdata('facility_id') ? null : $this->session->userdata('facility_id');
$district_id = !$this->session->userdata('district_id') ? null : $this->session->userdata('district_id');
$county_id = !$this->session->userdata('county_id') ? null : $this->session->userdata('county_id');
//compute stocked out items
$items_stocked_out_in_facility = count(facility_stocks::get_items_that_have_stock_out_in_facility($facility_code, $district_id, $county_id));
//get order information from the db
$facility_order_count_ = facility_orders::get_facility_order_summary_count($facility_code, $district_id, $county_id);
$facility_order_count = array();
foreach ($facility_order_count_ as $facility_order_count_) {
$facility_order_count[$facility_order_count_['status']] = $facility_order_count_['total'];
}
//get potential expiries infor here
$potential_expiries = count(Facility_stocks::get_potential_expiry_summary($county_id, 6, $district_id, $facility_code));
//get actual Expiries infor here
$actual_expiries = count(Facility_stocks::get_county_expiries($county_id, date('Y'), $district_id, $facility_code));
//get items they have been donated for
$facility_donations = count(redistribution_data::get_redistribution_data($facility_code, $district_id, $county_id, date('Y')));
$identifier = $this->session->userdata('user_indicator');
//get the roll out status here
$facility_roll_out_status = Facilities::get_tragetted_rolled_out_facilities($facility_code, $district_id, $county_id, $identifier);
$data['county_dashboard_notifications'] = array('items_stocked_out_in_facility' => $items_stocked_out_in_facility, 'facility_order_count' => $facility_order_count, 'potential_expiries' => $potential_expiries, 'actual_expiries' => $actual_expiries, 'facility_donations' => $facility_donations, 'facility_roll_out_status' => $facility_roll_out_status);
return $this->load->view("subcounty/ajax/county_notification_v", $data);
}
public function get_facility_dashboard_notifications_graph_data()
{
//format the graph here
$facility_code = $this->session->userdata('facility_id');
$facility_stock_ = facility_stocks::get_facility_stock_amc($facility_code);
$facility_stock_count = count($facility_stock_);
$graph_data = array();
$graph_data = array_merge($graph_data, array("graph_id" => 'container'));
$graph_data = array_merge($graph_data, array("graph_title" => 'Facility stock level'));
$graph_data = array_merge($graph_data, array("color" => "['#4b0082','#FFF263', '#6AF9C4']"));
$graph_data = array_merge($graph_data, array("graph_type" => 'bar'));
$graph_data = array_merge($graph_data, array("graph_yaxis_title" => 'Total stock level (values in packs)'));
$graph_data = array_merge($graph_data, array("graph_categories" => array()));
$graph_data = array_merge($graph_data, array("series_data" => array("Current Balance" => array(), "AMC" => array())));
$graph_data['stacking'] = 'normal';
foreach ($facility_stock_ as $facility_stock_) {
$graph_data['graph_categories'] = array_merge($graph_data['graph_categories'], array($facility_stock_['commodity_name']));
$graph_data['series_data']['Current Balance'] = array_merge($graph_data['series_data']['Current Balance'], array((double) $facility_stock_['pack_balance']));
$graph_data['series_data']['AMC'] = array_merge($graph_data['series_data']['AMC'], array((double) $facility_stock_['amc']));
}
//echo "<pre>";print_r($facility_stock_);echo "</pre>";exit;
//create the graph here
$faciliy_stock_data = $this->hcmp_functions->create_high_chart_graph($graph_data);
$loading_icon = base_url('assets/img/no-record-found.png');
$faciliy_stock_data = $facility_stock_count > 0 ? $faciliy_stock_data : "\$('#container').html('<img src={$loading_icon}>');";
//compute stocked out items
$items_stocked_out_in_facility = count(facility_stocks::get_items_that_have_stock_out_in_facility($facility_code));
//get order information from the db
$facility_order_count_ = facility_orders::get_facility_order_summary_count($facility_code);
//echo "<pre>";print_r($facility_order_count_);echo "<pre>";exit;
$facility_order_count = array();
foreach ($facility_order_count_ as $facility_order_count_) {
$facility_order_count[$facility_order_count_['status']] = $facility_order_count_['total'];
}
//get potential expiries infor here
$potential_expiries = Facility_stocks::potential_expiries($facility_code)->count();
//get actual Expiries infor here
$actual_expiries = count(Facility_stocks::All_expiries($facility_code));
//get items they have been donated for
$facility_donations = redistribution_data::get_all_active($facility_code, "to-me")->count();
//get items they have been donated and are pending
$facility_donations_pending = redistribution_data::get_all_active($facility_code)->count();
//get stocks from v1
$stocks_from_v1 = 0;
if ($facility_stock_count == 0 && $facility_donations == 0 && $facility_donations_pending == 0) {
$stocks_from_v1 = count(facility_stocks::import_stock_from_v1($facility_code));
}
return array('facility_stock_count' => $facility_stock_count, 'faciliy_stock_graph' => $faciliy_stock_data, 'items_stocked_out_in_facility' => $items_stocked_out_in_facility, 'facility_order_count' => $facility_order_count, 'potential_expiries' => $potential_expiries, 'actual_expiries' => $actual_expiries, 'facility_donations' => $facility_donations, 'facility_donations_pending' => $facility_donations_pending, 'stocks_from_v1' => $stocks_from_v1);
}
public function create_order_pdf_template($order_no)
{
$from_order_table = facility_orders::get_order_($order_no);
//get the order data here
$from_order_details_table = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("SELECT \n\n a.sub_category_name,\n b.commodity_name,\n b.commodity_code,\n b.unit_size,\n b.unit_cost,\n c.quantity_ordered_pack,\n c.quantity_ordered_unit,\n c.price,\n c.quantity_recieved,\n c.o_balance,\n c.t_receipts,\n c.t_issues,\n c.adjustnve,\n c.adjustpve,\n c.losses,\n c.days,\n c.comment,\n c.c_stock,\n c.s_quantity\n \nFROM\n commodity_sub_category a,\n commodities b,\n facility_order_details c\nWHERE\n c.order_number_id = {$order_no}\n AND b.id = c.commodity_id\n AND a.id = b.commodity_sub_category_id\ngroup by b.id\nORDER BY a.id ASC , b.commodity_name ASC ");
// get the order details here
$from_order_details_table_count = count($from_order_details_table);
foreach ($from_order_table as $order) {
$o_no = $order->order_no;
$o_bed_capacity = $order->bed_capacity;
$o_workload = $order->workload;
$o_date = $order->order_date;
$a_date = $order->approval_date;
$a_date = strtotime($a_date) ? date('d M, Y', strtotime($a_date)) : "N/A";
//checkdate ( (int) date('m', $unixtime) , (int) date('d', $unixtime) , date('y', $unixtime ) ) ? date('d M, Y',$unixtime) : "N/A";
$o_total = $order->order_total;
$d_rights = $order->drawing_rights;
$bal = $d_rights - $o_total;
$creator = $order->ordered_by;
$approver = $order->approved_by;
$mfl = $order->facility_code;
$myobj = Doctrine::getTable('Facilities')->findOneByfacility_code($mfl);
$sub_county_id = $myobj->district;
// get the order form details here
$myobj1 = Doctrine::getTable('Districts')->find($sub_county_id);
$sub_county_name = $myobj1->district;
$county = $myobj1->county;
$myobj2 = Doctrine::getTable('Counties')->find($county);
$county_name = $myobj2->county;
$myobj_order = Doctrine::getTable('users')->find($creator);
$creator_email = $myobj_order->email;
$creator_name1 = $myobj_order->fname;
$creator_name2 = $myobj_order->lname;
$creator_telephone = $myobj_order->telephone;
$myobj_order_ = Doctrine::getTable('users')->find($approver);
$approver_email = $myobj_order_->email;
$approver_name1 = $myobj_order_->fname;
$approver_name2 = $myobj_order_->lname;
$approver_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/> {$facility_name}</td>\n<td>Total OPD Visits & Revisits: {$o_workload} </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: {$sub_county_name}</td>\n<td>In-patient Bed Days :{$o_bed_capacity} </td>\n<td>Order Date:<br/> " . date('d M, Y', strtotime($o_date)) . " </td>\n<td>Order no. {$o_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 .= "\n<table class='data-table'>\n<thead><tr><th><b>KEMSA Code</b></th><th><b>Description</b></th><th><b>Order Unit Size</b>\n</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(-ve)</b></th><th><b>Adjustments(+ve)</b></th>\n<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 (Units)</b></th><th>\n<b>Order cost(Ksh)</b></th><th><b>Comment</b></th></tr> </thead><tbody>";
$html_body .= '<ol type="a">';
for ($i = 0; $i < $from_order_details_table_count; $i++) {
if ($i == 0) {
$html_body .= '<tr style="background-color:#C6DEFF;"> <td colspan="16" >
<li> ' . $from_order_details_table[$i]['sub_category_name'] . ' </li> </td></tr>';
} else {
if ($from_order_details_table[$i]['sub_category_name'] != $from_order_details_table[$i - 1]['sub_category_name']) {
$html_body .= '<tr style="background-color:#C6DEFF;"> <td colspan="15">
<li> ' . $from_order_details_table[$i]['sub_category_name'] . ' </li> </td></tr>';
}
}
$adjpve = $from_order_details_table[$i]['adjustpve'];
$adjnve = $from_order_details_table[$i]['adjustnve'];
$c_stock = $from_order_details_table[$i]['c_stock'];
$o_t = $from_order_details_table[$i]['quantity_ordered_pack'];
$o_q = $from_order_details_table[$i]['price'];
$o_bal = $from_order_details_table[$i]['o_balance'];
$t_re = $from_order_details_table[$i]['t_receipts'];
$t_issues = $from_order_details_table[$i]['t_issues'];
$losses = $from_order_details_table[$i]['losses'];
$adj = $adjpve + $adjnve;
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>" . $from_order_details_table[$i]['commodity_code'] . "</td>";
$html_body .= "<td>" . $from_order_details_table[$i]['commodity_name'] . "</td>";
$html_body .= "<td>" . $from_order_details_table[$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>" . $adjnve . "</td>";
$html_body .= "<td>" . $adjpve . "</td>";
$html_body .= "<td>" . $losses . "</td>";
$html_body .= "<td>" . $c_stock . "</td>";
$html_body .= "<td>" . $from_order_details_table[$i]['days'] . "</td>";
$html_body .= "<td>{$o_t}</td>";
$html_body .= "<td>" . $from_order_details_table[$i]['quantity_ordered_unit'] . "</td>";
$html_body .= "<td>{$ot}</td>";
$html_body .= "<td>" . $from_order_details_table[$i]['comment'] . "</td></tr>";
}
$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="width:100%">
<div style="float:right; width: 40%;"> Total Order Value:</div>
<div style="float:right; width: 40%;" >KSH ' . number_format($order_total, 2, '.', ',') . '</div> </div></td></tr>
<tr style="background-color: #FFFFFF;" >
<td colspan="4" ><div>
<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_email . '</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) ' . $approver_name1 . ' ' . $approver_name2 . '
<br/>
<br/>Email: ' . $approver_email . '</td><td>Tel: ' . $approver_telephone . '</td><td>Date: ' . $a_date . '</td><td>Signature</td>
</tr>
<tr><td>Authorised by (Name/DMoH)
<br/>
<br/>Email:</td><td>Tel: </td><td>Date:</td><td>Signature</td>
</tr>
</table>';
return $html_body . $html_body1;
}
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);
}
