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 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 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 KEMSA Update Order Delivery"; $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); } }