public static function get_commodities_for_ordering_report($facility_code, $commodity_division, $for_a_facility = null)
{
if (isset($for_a_facility)) {
// hack to ensure that when you are ordering for a facility that is not using hcmp they have all the items
$items = Commodities::get_all_from_supllier(1);
$temp = array();
foreach ($items as $data) {
array_push($temp, array('sub_category_name' => $data['sub_category_name'], 'commodity_name' => $data['commodity_name'], 'unit_size' => $data['unit_size'], 'unit_cost' => $data['unit_cost'], 'commodity_code' => $data['commodity_code'], 'commodity_id' => $data['commodity_id'], 'total_commodity_units' => $data['total_commodity_units'], 'opening_balance' => 0, 'total_receipts' => 0, 'total_issues' => 0, 'quantity_ordered' => 0, 'comment' => '', 'closing_stock_' => 0, 'closing_stock' => 0, 'days_out_of_stock' => 0, 'date_added' => '', 'losses' => 0, 'status' => 0, 'adjustmentpve' => 0, 'adjustmentnve' => 0, 'historical' => 0));
}
return $temp;
}
$inserttransaction = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("select `c`.`facility_code` AS `facility_code`,\n`a`.`sub_category_name` AS `sub_category_name`,\n`b`.`commodity_name` AS `commodity_name`,\n`b`.`unit_size` AS `unit_size`,\n`b`.`unit_cost` AS `unit_cost`,\n`b`.`commodity_code` AS `commodity_code`,\n`b`.`id` AS `commodity_id`,\n`b`.total_commodity_units,\n`c`.`opening_balance` AS `opening_balance`,\n`c`.`total_receipts` AS `total_receipts`,\n`c`.`total_issues` AS `total_issues`,\n`c`.`quantity_ordered` AS `quantity_ordered`,\n`c`.`comment` AS `comment`,\nceiling((`c`.`closing_stock` / `b`.`total_commodity_units`)) AS `closing_stock_`,\n`c`.`closing_stock` AS `closing_stock`,\n`c`.`days_out_of_stock` AS `days_out_of_stock`,\n`c`.`date_added` AS `date_added`,\n`c`.`losses` AS `losses`,\n`c`.`status` AS `status`,\n`c`.`adjustmentpve` AS `adjustmentpve`,\n`c`.`adjustmentnve` AS `adjustmentnve`,\nifnull(ceiling(sum((`h`.`total_units` / `b`.`total_commodity_units`))),0) AS `historical` \nfrom `commodities` `b`,`commodity_sub_category` `a` ,`facility_transaction_table` `c`\nleft join `facility_monthly_stock` `h` on (h.`facility_code`={$facility_code}\nand `h`.`commodity_id` = `c`.`commodity_id`)\nwhere (`b`.`id` = `c`.`commodity_id`\nand `c`.`status` = '1' \nand `a`.`id` = `b`.`commodity_sub_category_id` \nand c.`facility_code`={$facility_code}\nand b.`commodity_division` = {$commodity_division})\ngroup by `c`.`facility_code`,`c`.`commodity_id` \norder by `a`.`sub_category_name` desc");
return $inserttransaction;
}
public function county_stock_level()
{
$county_id = $this->session->userdata('county_id');
$data['district_data'] = districts::getDistrict($county_id);
$data['c_data'] = Commodities::get_all_2();
$data['title'] = "Stocking Levels";
$data['banner_text'] = "Stocking Levels";
$data['content_view'] = "facility/facility_reports/reports_v";
$data['report_view'] = "subcounty/reports/county_stock_level_filter_v";
$data['sidebar'] = "shared_files/report_templates/side_bar_sub_county_v";
$data['active_panel'] = 'stocking_levels';
$this->load->view("shared_files/template/template", $data);
}
public function upload_new_list()
{
if (isset($_FILES['file']) && $_FILES['file']['size'] > 0) {
$item_details = Commodities::get_all_from_supllier(1);
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
$excel2 = $objPHPExcel = $excel2->load($_FILES["file"]["tmp_name"]);
// Empty Sheet
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$temp = $super_cat = $sub_cat = array();
$temp['Updated commodity'] = array();
$temp['Added New commodity_category'] = array();
$temp['Added New commodity_sub_category'] = array();
$temp['Added New commodities'] = array();
$row_has_no_commodities = false;
// Loop through each row of the worksheet in turn
for ($row = 15; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if (isset($rowData[0][1]) && $rowData[0][1] != '') {
$cell = $sheet->getCell('C' . $row);
// super category
// Check if cell is merged super category
foreach ($sheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
// check for the super cat name
$data_new = $rowData[0][2];
$q = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n select * from commodity_category where category_name like '%{$data_new}%'");
$does_it_exits = count($q);
if ($does_it_exits == 0) {
//set the super cat id here
$this->db->insert('commodity_category', array('category_name' => $rowData[0][2], 'status' => 1));
$super_cat = array_merge($super_cat, array($rowData[0][2] => $this->db->insert_id()));
array_push($temp['Added New commodity_category'], $rowData[0][2]);
} else {
$super_cat = array_merge($super_cat, array($rowData[0][2] => $q[0]['id']));
}
} else {
$row_has_no_commodities = false;
}
}
if ($rowData[0][4] && $rowData[0][4] != '') {
// check for the sub cat name
$data_new = $rowData[0][4];
$q = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n select * from commodity_sub_category where sub_category_name like '%{$data_new}%'");
$does_it_exits = count($q);
if ($does_it_exits == 0) {
//set the sub cat id here
$ar_k = array_keys($super_cat);
$lastindex = $ar_k[count($ar_k) - 1];
$new_word = ucwords(strtolower($data_new));
// captialize the first word of each letter
$this->db->insert('commodity_sub_category', array('sub_category_name' => $new_word, 'status' => 1, 'commodity_category_id' => $super_cat[$lastindex]));
array_push($temp['Added New commodity_sub_category'], $rowData[0][4]);
$sub_cat = array_merge($sub_cat, array($data_new => $this->db->insert_id()));
} else {
$sub_cat = array_merge($sub_cat, array($data_new => $q[0]['id']));
//print_r($sub_cat); exit;
}
}
// now for the commodities
if ($rowData[0][5] && $rowData[0][5] != '') {
$data_new = preg_replace('/[^A-Za-z0-9\\-]/', ' ', $rowData[0][2]);
$unit_size = $rowData[0][5];
$unit_cost = $rowData[0][6];
$total_commodity_units = $rowData[0][7];
$new_unit_size = mysql_escape_string($unit_size);
$q = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAll("\n select * from commodities where commodity_code like '%{$data_new}%' and unit_size like '%{$new_unit_size}%' ");
$does_it_exits = count($q);
if ($does_it_exits == 0) {
//set the sub cat id here
// echo " $does_it_exits
//select * from commodities where commodity_code like \'%$data_new%\' and unit_size like \'%$new_unit_size%\' "; exit;
$new_word = $data_new;
// captialize the first word of each letter
$this->db->insert('commodities', array('commodity_name' => $rowData[0][3], 'unit_size' => $unit_size, 'unit_cost' => $unit_cost, 'commodity_code' => $data_new, 'commodity_sub_category_id' => $sub_cat[$rowData[0][4]], 'total_commodity_units' => $total_commodity_units, 'commodity_source_id' => 1, 'tracer_item' => 0, 'status' => 1));
array_push($temp['Added New commodities'], $rowData[0][3]);
} else {
$array_update = array('commodity_name' => $rowData[0][3], 'unit_size' => $unit_size, 'unit_cost' => $unit_cost, 'commodity_code' => $data_new, 'commodity_sub_category_id' => $sub_cat[$rowData[0][4]], 'total_commodity_units' => $total_commodity_units, 'commodity_source_id' => 1);
$array_where = array('unit_size' => $unit_size, 'commodity_code' => $data_new);
$this->db->where($array_where);
$this->db->update("commodities", $array_update);
if ($this->db->affected_rows() > 0) {
array_push($temp['Updated commodity'], $rowData[0][3] . " unit price " . $sub_cat[$rowData[0][4]]);
}
}
}
}
// end if
}
// end for loop
echo "<pre>";
print_r($temp);
echo "</pre>";
unset($objPHPExcel);
}
}
public function kemsa_excel_order_uploader($inputFileName)
{
// $inputFileName = 'print_docs/excel/excel_template/KEMSA Customer Order Form.xlsx';
if (isset($inputFileName)) {
$item_details = Commodities::get_all_from_supllier(1);
//$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
//$excel2 = PHPExcel_IOFactory::createReader($inputFileType);
$ext = pathinfo($_FILES["file"]['name'], PATHINFO_EXTENSION);
if ($ext == 'xls') {
$excel2 = PHPExcel_IOFactory::createReader('Excel5');
} else {
if ($ext == 'xlsx') {
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
} else {
die('Invalid file format given' . $_FILES['file']);
}
}
//exit;
$excel2 = $objPHPExcel = $excel2->load($inputFileName);
// Empty Sheet
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$temp = array();
$facility_code = $sheet->getCell('H4')->getValue();
// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if (isset($rowData[0][2]) && $rowData[0][2] != 'Product Code') {
foreach ($item_details as $key => $data) {
if (in_array($rowData[0][2], $data)) {
array_push($temp, array('sub_category_name' => $data['sub_category_name'], 'commodity_name' => $data['commodity_name'], 'unit_size' => $data['unit_size'], 'unit_cost' => $data['unit_cost'], 'commodity_code' => $data['commodity_code'], 'commodity_id' => $data['commodity_id'], 'total_commodity_units' => $data['total_commodity_units'], 'opening_balance' => 0, 'total_receipts' => 0, 'total_issues' => 0, 'quantity_ordered' => $rowData[0][7], 'comment' => '', 'closing_stock_' => 0, 'closing_stock' => 0, 'days_out_of_stock' => 0, 'date_added' => '', 'losses' => 0, 'status' => 0, 'adjustmentpve' => 0, 'adjustmentnve' => 0, 'historical' => 0));
unset($item_details[$key]);
}
}
}
}
unset($objPHPExcel);
return array('row_data' => $temp, 'facility_code' => $facility_code);
}
}
public function update_meds_order_delivery($order_id)
{
$facility_code = $this->session->userdata('facility_id');
$data['content_view'] = "facility/facility_orders/update_order_delivery_from_meds_v";
$data['title'] = "Facility Update Order Delivery";
$data['facility_commodity_list'] = Commodities::get_all_from_meds(2);
$data['order_details'] = facility_order_details::get_order_details($order_id);
$data['general_order_details'] = facility_orders::get_order_($order_id);
$data['banner_text'] = "Facility MEDS Update Order Delivery";
$ordered_by = $data['general_order_details'][0]['ordered_by'];
$data['ordered_by'] = users::get_user_names($ordered_by);
$this->load->view('shared_files/template/template', $data);
}
public function patient_history()
{
$facility_code = $this->session->userdata('facility_id');
$data['title'] = "Dispensing Reports";
$data['banner_text'] = "Facility Consumption";
$data['c_data'] = Commodities::get_facility_commodities($facility_code);
$data['sidebar'] = "facility/facility_dispensing/dispensing_side_bar_v";
$data['report_view'] = "facility/facility_dispensing/patient_history_report";
$data['content_view'] = "facility/facility_reports/reports_v";
$view = 'shared_files/template/template';
$data['active_panel'] = 'consumption';
$this->load->view($view, $data);
}
public function update_order_delivery($order_id)
{
$facility_code = $this->session->userdata('facility_id');
$data['content_view'] = "facility/facility_orders/update_order_delivery_from_kemsa_v";
$data['title'] = "Facility Update Order Delivery";
$data['facility_commodity_list'] = Commodities::get_all_from_supllier(1);
$data['order_details'] = facility_order_details::get_order_details($order_id);
$data['general_order_details'] = facility_orders::get_order_($order_id);
$data['banner_text'] = "Facility Update Order Delivery";
$this->load->view('shared_files/template/template', $data);
}
public function commodities_json()
{
echo json_encode(Commodities::getAll_json());
}
public function ors_zinc_redistribution_report()
{
//Set the current year
$year = date("Y");
$county_total = array();
$excel_data = array();
$column_data = array();
$excel_data = array('doc_creator' => "HCMP", 'doc_title' => 'ors zinc redistribution report ', 'file_name' => 'ors zinc redistribution report');
$row_data = array();
$column_data = array("Sender Name", "Receiver Name", "Source Facility Code", "Source Facility Name", "Source Sub County", "Receiver Facility Name", "Receiver Facility Code", "Receiver Sub County", "Commodity Name", "Commodity Code", "Unit Size", "Unit Cost(KES)", "Quantity Sent(Units)", "Quantity Sent(Packs)", "Quantity Received(Units)", "Quantity Received(Packs)", "Manufacturer", "Batch Number", "Expiry Date", "Status", "Date Sent", "HCMP Supported Facility", "Date Received");
$excel_data['column_data'] = $column_data;
//the commodities variable will hold the values for the three commodities ie ORS and Zinc
$commodities = array(51, 267, 36, 456);
foreach ($commodities as $commodities) {
$commodity_stock_level = array();
//holds the data for the entire county
//once it is done executing for one commodity it will reset to zero
$commodity_total = array();
//pick the commodity names and details
//get the redistribution report for the specified commodities
$commodity_redistribution = redistribution_data::get_redistribution_data_ors_zinc($commodities);
//Start building the excel file
foreach ($commodity_redistribution as $commodity_redistribution) {
//format the names for the receiver and sender accordingly
$sender = $commodity_redistribution["sender_name_fname"] . " " . $commodity_redistribution["sender_name_lname"];
$receiver = $commodity_redistribution["receiver_name_fname"] . " " . $commodity_redistribution["receiver_name_lname"];
//then get the quantity of the commodities in packs
$total_commodity_units = "";
$total_commodity_units = Commodities::get_commodity_unit($commodities);
$total_commodity_units = $total_commodity_units[0]['total_commodity_units'];
//get the quantity received in packs
$quantity_sent_packs = $commodity_redistribution["quantity_sent"] / $total_commodity_units;
$quantity_received_packs = $commodity_redistribution["quantity_received"] / $total_commodity_units;
//now let us set the the status
$status = "";
$status = $commodity_redistribution["status"] != 0 ? "Received" : "Not Received";
//check if the facility is supported facility
$active = "";
$active = Facilities::check_active_facility($commodity_redistribution["receiver_facility_code"]);
$active = $active[0]["HCMP Supported"];
//format the date received
$date_received = "";
$date_received = $commodity_redistribution["date_received"] != 0 ? $commodity_redistribution["date_received"] : "Not Received";
//echo "<pre>";print_r($date_received);exit;
//pick the facility code from the data
/*$facility_code = $commodity_redistribution["facility_code"];
//get the last date of issue from the database
$date_last_issue = Facilities::get_last_redistribution($facility_code);
//ensure that if the date is null change the message
$date_of_last_issue = ($date_last_issue[0]['Date Last Redistributed']!=0) ? date('j M, Y', strtotime($date_last_issue[0]['Date Last Redistributed'])) : "No Data Found";
$days_since_last_issue = ($date_last_issue[0]['Days From Last Redistribution']!=0) ? $date_last_issue[0]['Days From Last Redistribution'] : 0;
*/
//"Date Sent","HCMP Supported Facility","Date Received");
//push the result from the array into row data that will be dumped into the excel file
array_push($row_data, array($sender, $receiver, $commodity_redistribution["source_facility_code"], $commodity_redistribution["source_facility_name"], $commodity_redistribution["source_district"], $commodity_redistribution["receiver_facility_name"], $commodity_redistribution["receiver_facility_code"], $commodity_redistribution["receiver_district"], $commodity_redistribution["commodity_name"], $commodity_redistribution["commodity_code"], $commodity_redistribution["unit_size"], $commodity_redistribution["unit_cost"], $commodity_redistribution["quantity_sent"], $quantity_sent_packs, $commodity_redistribution["quantity_received"], $quantity_received_packs, $commodity_redistribution["manufacturer"], $commodity_redistribution["batch_no"], $commodity_redistribution["expiry_date"], $status, $commodity_redistribution["date_sent"], $active, $date_received));
//echo "<pre>";print_r($row_data);exit;
}
}
$excel_data['row_data'] = $row_data;
$excel_data['report_type'] = "download_file";
$excel_data['file_name'] = "Zinc ORS Redistribution Report";
$excel_data['excel_title'] = "Redistribution Report for Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality (100) & (50) for the year " . date("Y");
//Start the email section of the report
//Get the number of facilities using HCMP
$no_of_facilities = Facilities::get_all_on_HCMP();
$subject = "Redistribution Report: Zinc sulphate Tablets 20mg and ORS sachet (for 500ml) low osmolality ";
$message = "<p>Find attached an excel sheet with a Redistribution Report for\nZinc Sulphate 20mg, ORS sachet (for 500ml) low osmolality (100 & 50) and ORS 4 Satchets & Zinc 10 Tablets 20 Mg as at " . date("jS F Y") . "</p>";
$message .= "<p>Number of facilities using HCMP: " . $no_of_facilities . "</p>";
$message .= $message_body;
$message .= "\n<p>You may log onto health-cmp.or.ke for follow up.</p>\n\n<p>----</p>\n\n<p>HCMP</p>\n\n<p>This email was automatically generated. Please do not respond to this email address or it will be ignored.</p>";
$report_type = "ors_report";
$this->create_excel($excel_data, $report_type);
//exit;
//path for windows
$handler = "./print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
//path for Mac
//$handler = "/Applications/XAMPP/xamppfiles/htdocs/hcmp/print_docs/excel/excel_files/" . $excel_data['file_name'] . ".xls";
$email_address = "*****@*****.**";
$bcc = "karsanrichard@gmail.com,kelvinmwas@gmail.com";
$this->hcmp_functions->send_email($email_address, $message, $subject, $handler, $bcc);
}
public function get_consumption_chart_ajax($sub_category = null)
{
$commodity_id = $this->input->post('commodity_id');
$from = $this->input->post('from');
$to = $this->input->post('to');
$total = 0;
$to = $to == "NULL" || !isset($to) ? date('Y-m-d 23:59:59') : date('Y-m-d 23:59:59', strtotime(urldecode($to)));
$from = $from == "NULL" || !isset($from) ? date('Y-m-d 00:00:00') : date('Y-m-d 00:00:00', strtotime(urldecode($from)));
$consumption = facility_stocks::get_dispensing_consumption_by_age($commodity_id, $from, $to, $sub_category);
$commodity_name = '';
if ($commodity_id != '') {
$commodity_details = Commodities::get_commodity_name($commodity_id);
$commodity_name = $commodity_details[0]['commodity_name'];
} else {
$commodity_details = commodity_sub_category::get_dets_one($sub_category);
}
$val1 = intval($consumption['5']);
$val2 = intval($consumption['above']);
$graph_id = 'dem_graph_';
$data['graph_id'] = 'dem_graph_';
$text_title = $commodity_name . ' consumption by Age between ' . date('F d Y', strtotime($from)) . ' and ' . date('F d Y', strtotime($to));
$result_chart = " \$('#{$graph_id}').highcharts({\n chart: {\n plotBackgroundColor: null,\n plotBorderWidth: null,\n plotShadow: false,\n type: 'pie'\n },\n title: {\n text: '{$text_title}'\n },\n tooltip: {\n pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'\n },\n plotOptions: {\n pie: {\n allowPointSelect: false,\n cursor: 'pointer',\n dataLabels: {\n enabled: true\n },\n showInLegend: true\n }\n },\n series: [{\n name: '{$commodity_name}',\n colorByPoint: true,\n data: [{\n name: 'Under 5 years ({$val1} units)',\n y: {$val1}\n }, {\n name: 'Over 5 Years ({$val2} units)',\n y:{$val2},\n sliced: true,\n selected: true\n }]\n }]\n });";
// $result_table .= '
$data['high_graph'] = $result_chart;
return $this->load->view("shared_files/report_templates/high_charts_template_v", $data);
echo $result_chart;
}
public function county_consumption()
{
$county_id = $this->session->userdata('county_id');
$data['district_data'] = districts::getDistrict($county_id);
$data['c_data'] = Commodities::get_all_2();
$data['categories'] = commodity_sub_category::get_all_pharm();
$data['title'] = "Consumption";
$data['banner_text'] = "Consumption";
$data['content_view'] = "facility/facility_reports/reports_v";
$data['report_view'] = "subcounty/reports/county_consumption_data_filter_v";
$data['sidebar'] = "shared_files/report_templates/side_bar_sub_county_v";
$data['active_panel'] = 'consumption';
$this->load->view("shared_files/template/template", $data);
}
public function dispense_to_patient($patient_id = NULL)
{
$commodities = Commodities::get_all();
}
