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);
}
}
