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 aggragate_order_new_sorf($order_id) { $order_id_array = explode("_", $order_id); $facility_name = array(); $order_id = array(); $order_total = array("", "", "", ""); $order_total_all = 0; $order_total_all_items = 0; foreach ($order_id_array as $single_order_id) { //get the facility names from the orders if ($single_order_id > 0) { $test = facility_orders::get_order_($single_order_id); $order_id = array_merge($order_id, array($single_order_id)); foreach ($test as $test_) { $order_total = array_merge($order_total, array("", $test_->order_total)); $order_total_all = $order_total_all + $test_->order_total; foreach ($test_->facility_detail as $facility_data) { array_push($facility_name, "{$facility_data->facility_name}", ''); } } } } array_push($order_total, $order_total_all); array_push($facility_name, "TOTAL"); //combine all of them $stock_data = Commodities::get_all_from_supllier(1); $from_stock_data = count($stock_data); //get items from a supplier $excel_data = array('doc_creator' => "HCMP", 'doc_title' => 'test ', 'file_name' => 'test'); $row_data = array(array("Product Code", "Item description(Name/form/strength)", "Order unit size", "Price")); foreach ($order_id as $order_id_) { array_push($row_data[0], "Quantity to Order", "Total"); //push this to go with each facility } $column_data = array("", "", "FACILITY NAME", ""); $column_data = array_merge($column_data, $facility_name); $excel_data['column_data'] = $column_data; for ($i = 0; $i < $from_stock_data; $i++) { $total_all = 0; $temp_array = array(); $temp_array_ = array(); if ($i == 0) { //push the first sub category array_push($row_data, array($stock_data[$i]["sub_category_name"], "", "", "", "")); } else { if ($stock_data[$i]['sub_category_name'] != $stock_data[$i - 1]['sub_category_name']) { //push the first sub category array_push($row_data, array($stock_data[$i]["sub_category_name"], "", "", "", "")); } } foreach ($order_id as $order_id_) { $total = facility_order_details::get_order_details_from_order($order_id_, $stock_data[$i]["commodity_id"]); if (count($total) == 0) { array_push($temp_array, 0, 0); } else { $total_ = $total[0]['total'] * str_replace(",", '', $stock_data[$i]["unit_cost"]); array_push($temp_array, $total[0]['total'], $total_); $total_all = $total_all + $total_; $order_total_all_items = $order_total_all_items + $total_; } } $temp_array_ = array($stock_data[$i]["commodity_code"], $stock_data[$i]["commodity_name"], $stock_data[$i]["unit_size"], $stock_data[$i]["unit_cost"]); $temp_array_ = array_merge($temp_array_, $temp_array); array_push($temp_array_, $total_all); array_push($row_data, $temp_array_); } array_push($row_data, $order_total); $excel_data['row_data'] = $row_data; $this->hcmp_functions->create_excel($excel_data); }
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); }