/** * 一覧のデータを返す * @access public * @return array 一覧 * @see Admin_ActionClass::perform() */ public function perform() { // get request params ( search ) $warehouse_id = $this->af->get('warehouse_id'); $terminal_id = $this->af->get('terminal_id'); $order_no = $this->af->get('order_no'); $keyword = $this->af->get('keyword'); // get request params ( pager ) $limit = $this->af->get('limit'); // default 50 $page = $this->af->get('page'); // default 1 $order = $this->af->get('order'); // default desc $column = $this->af->get('column'); // default uriage_bi // get session params $company_id = $this->session->get('company_id'); // setting pager $start_page = ($page - 1) * $limit + 1; $end_page = ($page - 1) * $limit + $limit; $output = array(); try { $ordering = DaoFactory::OrderingD(); //検索 $params = array(); $params = array('company_id' => $company_id, 'start_page' => $start_page, 'warehouse_id' => $warehouse_id, 'terminal_id' => $terminal_id, 'order_no' => $order_no, 'end_page' => $end_page, 'order' => $order, 'column' => $column, 'keyword' => $keyword); $params2 = array('company_id' => $company_id, 'warehouse_id' => $warehouse_id, 'terminal_id' => $terminal_id, 'order_no' => $order_no); $total = $ordering->getOrderingList($params2); $list = $ordering->getOrderingDetailData($params); // pager array setting $pagerData = array('result_page' => $page, 'result_start_num' => $start_page, 'result_end_num' => $end_page, 'result_all_count' => count($list) ? $list[0]['FOUND_ROWS'] : 0, 'result_get_count' => count($list), 'result_limit' => $limit); // output にセット $output['totalData'] = $total[0]; $output['listData'] = $list; $output['pagerData'] = $pagerData; } catch (Exception $e) { // 致命的なエラーが発生 $this->logger->log(LOG_DEBUG, $e->getTraceAsString()); return array('500', $e->getMessage()); } return array('json', $output); }
public function OrderingMD_Get($params) { $where_ai = ''; $bindarr = array(); if (!empty($params['ai_arr'])) { $where_ai = 'AND ' . implode(' AND ', array_fill(0, count($params['ai_arr']), ' MD.BARCODE LIKE ? ')); $bindarr = $params['ai_arr']; } $sql = "\r\n\t\t\t\tSELECT\r\n\t\t\t\t\t MD.COMPANY_ID\r\n\t\t\t\t ,MD.WAREHOUSE_ID\r\n\t\t\t\t ,MD.TERMINAL_ID\r\n\t\t\t\t\t,MD.ORDER_NO\r\n\t\t\t\t\t,MD.SEQ\r\n\t\t\t\t\t,MD.BARCODE\r\n\t\t\t\t\t,MD.RFID_PRODUCTITEM_ID\r\n\t\t\t\t\t,MD.SEND_FLG\r\n\t\t\t\t\t,TO_CHAR(MD.SEND_DATE,'yyyy/mm/dd hh24:mi:ss') AS SEND_DATE\r\n\t\t\t\t\t,MD.PRODUCT_ID AS SHOHIN_CD\r\n\t\t\t\t\t,MD.PRODUCT_NAME AS SHOHIN_MEI\r\n\t\t\t\t\t,MD.AMOUNT\r\n\t\t\t\t\t,MD.AMOUNT SURYO\r\n\t\t\t\t\t,TO_CHAR(D.ORDERING_DATE,'yyyy/mm/dd hh24:mi:ss') AS ORDERING_DATE\r\n\t\t\t\t ,W.WAREHOUSE_NAME\r\n\t\t\t\t\t,T.TERMINAL_NAME\r\n\t\t\t\t\t,B.BUYER_NAME\r\n\t\t\t\t\t,S.STAFF_NAME\r\n\t\t\t\t ,K.KBN_NAME\r\n\t\t\t\t ,D.BUYER_CD\r\n\t\t\t\t\t,DOWNLOAD_FLG\r\n\t\t\t\t ,CASE WHEN DOWNLOAD_FLG ='1' THEN '完了' ELSE '未完了' END AS DOWNLOAD_NAME\r\n\t\t\t\t\t,D.REMARKS\r\n\t\t\t\t\t,MD.RETAIL_PRICE\r\n\t\t\t\t\t,MD.INVENTORY_VALUE\r\n\t\t\t\t\t,MD.SCANNED_BC\r\n\t\t\t\tFROM ORDERING_D D\r\n\t\t\t\tINNER JOIN ORDERING_MD MD\r\n\t\t\t\t\tON D.COMPANY_ID = MD.COMPANY_ID\r\n\t\t\t\t AND D.WAREHOUSE_ID = MD.WAREHOUSE_ID\r\n\t\t\t\t AND D.TERMINAL_ID = MD.TERMINAL_ID\r\n\t\t\t\t AND D.ORDER_NO = MD.ORDER_NO\r\n\t\t\t\tLEFT JOIN TERMINAL_MST T\r\n\t\t\t\t\tON D.COMPANY_ID = T.COMPANY_ID\r\n\t\t\t\t AND D.TERMINAL_ID = T.TERMINAL_ID\r\n\t\t\t\tLEFT JOIN WAREHOUSE_MST W\r\n\t\t\t\t\tON D.COMPANY_ID = W.COMPANY_ID\r\n\t\t\t\t AND D.WAREHOUSE_ID = W.WAREHOUSE_ID\r\n\t\t\t\tINNER JOIN (\r\n\t\t\t\t\tSELECT MST.*\r\n\t\t\t\t\tFROM BUYER_MST MST\r\n\t\t\t\t\tINNER JOIN USER_BUYER UB\r\n\t\t\t\t\t ON MST.COMPANY_ID =UB.COMPANY_ID AND UB.BUYER_CD=MST.BUYER_CD\r\n\t\t\t\t\tWHERE UB.USER_ID = ?\r\n\t\t\t\t\tAND MST.COMPANY_ID = ?\r\n\t\t\t\t\tUNION ALL\r\n\t\t\t\t\tSELECT MST.*\r\n\t\t\t \t \tFROM BUYER_MST MST\r\n\t\t\t\t\tWHERE MST.COMPANY_ID = ?\r\n\t\t\t\t\tAND NOT EXISTS(SELECT COMPANY_ID,USER_ID,CNT \r\n\t\t\t\t\t\t\t FROM (SELECT UB.COMPANY_ID, UB.USER_ID,COUNT(*) AS CNT \r\n\t\t\t\t\t\t\t\t\t FROM USER_BUYER UB\r\n\t\t\t\t\t\t\t\t\t WHERE UB.USER_ID = ?\r\n\t\t\t\t\t\t\t\t\t GROUP BY UB.COMPANY_ID, UB.USER_ID\r\n\t\t\t\t\t\t\t\t\t) T\r\n\t\t\t\t\t\t\t WHERE T.COMPANY_ID =MST.COMPANY_ID\r\n\t\t\t\t\t\t\t GROUP BY COMPANY_ID,USER_ID,CNT\r\n\t\t\t\t\t\t\t HAVING SUM(CNT)>0)) B\r\n\t\t\t\t\tON D.COMPANY_ID = B.COMPANY_ID\r\n\t\t\t\t AND D.BUYER_CD = B.BUYER_CD\r\n\t\t\t\tLEFT JOIN STAFF_MST S\r\n\t\t\t\t\tON D.COMPANY_ID = S.COMPANY_ID\r\n\t\t\t\t AND D.STAFF_ID = S.STAFF_ID\r\n\t\t\t\tLEFT JOIN KBN_MST K\r\n\t\t\t\t\tON D.COMPANY_ID = K.COMPANY_ID\r\n\t\t\t\t AND D.KBN_ID = K.KBN_ID\r\n\t\t\t\t\tAND K.CATEGORY_ID\t= ?\r\n\t\t\t\tWHERE\r\n\t\t\t\t\t1 = 1\r\n\t\t\t\t\t{$where_ai}\r\n\t\t\t\t\t"; $opt = array(); $opt[] = $params['user_id']; $opt[] = $params['company_id']; $opt[] = $params['company_id']; $opt[] = $params['user_id']; $opt[] = KBN_CATEGORY_ORDER; $opt = array_merge($opt, $bindarr); if ($params['download_flg'] == '1') { $sql .= "AND NVL(D.DOWNLOAD_FLG,' ') <> '1'\r\n\t\t\t\t\t"; } if ('' != $params['company_id']) { $opt[] = $params['company_id']; $sql .= "AND MD.COMPANY_ID = ? \r\n\t\t\t\t\t"; } if ('' != $params['seq']) { $opt[] = $params['seq']; $sql .= " AND MD.SEQ = ? \r\n\t\t\t\t\t"; } if ('' != $params['order_no']) { $opt[] = '%' . $params['order_no'] . '%'; $sql .= "AND D.ORDER_NO LIKE ? \r\n\t\t\t\t\t"; } if ('' != $params['warehouse_id']) { $opt[] = '%' . $params['warehouse_id'] . '%'; $sql .= "AND D.WAREHOUSE_ID LIKE ? \r\n\t\t\t\t\t"; } if ('' != $params['staff_id']) { $opt[] = '%' . $params['staff_id'] . '%'; $sql .= "AND D.STAFF_ID LIKE ? \r\n\t\t\t\t\t"; } if ('' != $params['buyer_cd']) { $opt[] = '%' . $params['buyer_cd'] . '%'; $sql .= "AND D.BUYER_CD LIKE ? \r\n\t\t\t\t\t"; } if ('' != $params['barcode']) { $opt[] = '%' . $params['barcode'] . '%'; $sql .= "AND MD.BARCODE LIKE ? \r\n\t\t\t\t\t"; } if ($params['shohin_cd'] != '') { $opt[] = '%' . $this->sqlesc($params['shohin_cd']) . '%'; $sql .= ' AND MD.PRODUCT_ID LIKE ?' . " ESCAPE '|' "; } if ($params['shohin_mei'] != '') { $opt[] = '%' . $this->sqlesc($params['shohin_mei']) . '%'; $sql .= ' AND MD.PRODUCT_NAME LIKE ?' . " ESCAPE '|' "; } if (!empty($params['start_date'])) { $opt[] = $params['start_date']; $sql .= " AND TO_CHAR(D.ORDERING_DATE,'YYYY/MM/DD') >= ?\r\n \t\t\t\t"; } if (!empty($params['end_date'])) { $opt[] = $params['end_date']; $sql .= " AND TO_CHAR(D.ORDERING_DATE,'YYYY/MM/DD') <= ?\r\n \t\t\t\t"; } if ('' != $params['terminal_arr'] && '' != $params['warehouse_arr'] && '' != $params['order_no_arr'] && '' == $params['seq_arr']) { $tmp = array(); for ($i = 0; $i < count($params['terminal_arr']); $i++) { $opt[] = $params['terminal_arr'][$i]; $opt[] = $params['warehouse_arr'][$i]; $opt[] = $params['order_no_arr'][$i]; $tmp[] = ' (MD.TERMINAL_ID = ? AND MD.WAREHOUSE_ID = ? AND MD.ORDER_NO = ? ) '; } $sql .= " AND (" . implode(' OR ', $tmp) . ") "; } else { if ('' != $params['terminal_arr'] && '' != $params['warehouse_arr'] && '' != $params['order_no_arr'] && '' != $params['seq_arr']) { $tmp = array(); for ($i = 0; $i < count($params['terminal_arr']); $i++) { $opt[] = $params['terminal_arr'][$i]; $opt[] = $params['warehouse_arr'][$i]; $opt[] = $params['order_no_arr'][$i]; $opt[] = $params['seq_arr'][$i]; $tmp[] = ' (MD.TERMINAL_ID = ? AND MD.WAREHOUSE_ID = ? AND MD.ORDER_NO = ? AND MD.SEQ = ?) '; } $sql .= " AND (" . implode(' OR ', $tmp) . ") "; } else { if ('' != $params['order_no_op'] && '' != $params['warehouse_id_op'] && '' != $params['terminal_id_op']) { $opt[] = $params['warehouse_id_op']; $opt[] = $params['terminal_id_op']; $opt[] = $params['order_no_op']; $sql .= " AND D.WAREHOUSE_ID = ? AND D.TERMINAL_ID = ? AND D.ORDER_NO = ? "; } } } // add order $order = " ORDER BY SEQ ASC "; if (!empty($params['column']) && !empty($params['order'])) { $order = " ORDER BY {$params['column']} {$params['order']} "; } $sql = " SELECT RET.*, COUNT(*) OVER() FOUND_ROWS, ROW_NUMBER() OVER( {$order} ) RN FROM ( {$sql} ) RET WHERE 1 = 1 "; if ('' != $params['keyword']) { $cols = array('WAREHOUSE_ID', 'WAREHOUSE_NAME', 'TERMINAL_NAME', 'TERMINAL_ID', 'BUYER_CD', 'ORDER_NO', 'KBN_NAME', 'STAFF_NAME', 'BUYER_NAME', 'ORDERING_DATE', 'SEQ', 'BARCODE', 'AMOUNT', "DOWNLOAD_NAME", 'SHOHIN_CD', 'SHOHIN_MEI'); $tmp = array(); foreach ($cols as $col) { $opt[] = '%' . $params['keyword'] . '%'; $tmp[] = $col . ' LIKE ? '; } $sql .= " AND (\r\n\t\t\t" . implode(' OR ', $tmp) . ")"; } // add pager if (intval($params['start_page']) > 0 && intval($params['end_page']) >= intval($params['start_page'])) { $opt[] = $params['start_page']; $opt[] = $params['end_page']; $sql = " SELECT * FROM ( {$sql} ) WHERE RN >= ? AND RN <= ? " . $order; } // if( true === $params['csv'] && '1' == $params['shuukei'] ){ if (true === $params['csv']) { $stmt = $this->db->prepare($sql); $stmt->execute($opt); $updateDao = DaoFactory::OrderingD(); $updateDao->BeginTransaction(); $updateDao->OrderingD_UpdateDLFlg($params, $sql, $opt); $updateDao->CommitTransaction(); return $stmt; } $stmt = $this->db->prepare($sql); $stmt->execute($opt); return $stmt->fetchAll(PDO::FETCH_ASSOC); }
/** * 代行発注のデータをJSON形式で返す * @access public * @return array 代行発注 * @see Admin_ActionClass::perform() */ public function perform() { // get request params ( search ) $hyoji_kbn = $this->af->get('hyoji_kbn'); $start_date = $this->af->get('start_date'); $end_date = $this->af->get('end_date'); $buyer_cd = $this->af->get('buyer_cd'); $order_no = $this->af->get('order_no'); $staff_id = $this->af->get('staff_id'); $warehouse_id = $this->af->get('warehouse_id'); $terminal_id = $this->af->get('terminal_id'); $barcode = $this->af->get('barcode'); $ai = $this->af->get('ai'); $ai_cd = $this->af->get('ai_cd'); $download_flg = $this->af->get('download_flg'); $shohin_cd = trim($this->af->get('shohin_cd')); $shohin_mei = trim($this->af->get('shohin_mei')); // get request params ( pager ) $limit = $this->af->get('limit'); $page = $this->af->get('page'); $order = $this->af->get('order'); $column = $this->af->get('column'); $keyword = $this->af->get('keyword'); if ($hyoji_kbn == '1') { if ($column == 'shohin_cd' || $column == 'shohin_mei' || $column == 'barcode' || $column == 'amount') { $column = ''; } } elseif ($hyoji_kbn == '2') { if ($column == 'quantum') { $column = ''; } } elseif ($hyoji_kbn == '3') { if ($column != 'shohin_cd' && $column != 'shohin_mei' && $column != 'amount' && $column != 'barcode') { $column = ''; } } if ($column == 'order_no') { $column = 'to_number(order_no)'; } $company_id = $this->session->get('company_id'); $user_id = $this->session->get('user_id'); // pager setting $start_page = ($page - 1) * $limit + 1; $end_page = ($page - 1) * $limit + $limit; $ai_arr = array(); if ('' != $ai) { $tmparr = explode(';', $ai); foreach ($tmparr as $value) { $row = explode('=', $value); $ai_arr[] = '%(' . str_replace('y', '_', $row[0]) . ')' . $row[1] . '%'; } } try { $params = array('company_id' => $company_id, 'user_id' => $user_id, 'start_date' => $start_date, 'end_date' => $end_date, 'buyer_cd' => $buyer_cd, 'order_no' => $order_no, 'staff_id' => $staff_id, 'warehouse_id' => $warehouse_id, 'barcode' => $barcode, 'ai_arr' => $ai_arr, 'download_flg' => $download_flg, 'shohin_cd' => $shohin_cd, 'shohin_mei' => $shohin_mei, 'keyword' => $keyword, 'order' => $order, 'column' => $column, 'start_page' => $start_page, 'end_page' => $end_page); // 一覧を取得 if ($hyoji_kbn == '1') { $list = DaoFactory::OrderingD()->getOrderingList($params); } elseif ($hyoji_kbn == '2') { $list = DaoFactory::OrderingMD()->OrderingMD_Get($params); } else { $list = DaoFactory::OrderingMD()->OrderingMD_GetGroupByBarcode($params); } // ページ情報を設定 $pager = array('result_page' => $page, 'result_start_num' => $start_page, 'result_end_num' => $end_page, 'result_all_count' => count($list) ? $list[0]['FOUND_ROWS'] : 0, 'result_get_count' => count($list), 'result_limit' => $limit, 'hyoji_kbn' => $hyoji_kbn); // output にセット $output['totalData'] = array(); $output['listData'] = $list; $output['pagerData'] = $pager; } catch (Exception $e) { // 致命的なエラーが発生 $this->logger->log(LOG_DEBUG, $e->getTraceAsString()); return array('500', $e->getMessage()); } return array('json', $output); }