List of usage examples for org.hibernate SQLQuery setResultTransformer
@Deprecated Query<R> setResultTransformer(ResultTransformer transformer);
From source file:com.vega.service.configuration.SQLQueryBuilder.java
public static <T> Object buildQuery(Class<T> classe, String sql) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction();/*from ww w.j ava2s . c om*/ ResultTransformer resultTransformer = new AliasToBeanResultTransformer(classe); Field[] fields = classe.getDeclaredFields(); SQLQuery sqlQuery = session.createSQLQuery(sql); for (Field field : fields) { if (field.getType().getSimpleName().equals("Integer")) { sqlQuery.addScalar(field.getName(), new IntegerType()); } else if (field.getType().getSimpleName().equals("GregorianCalendar")) { sqlQuery.addScalar(field.getName(), new DateType()); } else if (field.getType().getSimpleName().equals("BigDecimal")) { sqlQuery.addScalar(field.getName(), new BigDecimalType()); } else if (field.getType().getSimpleName().equals("String")) { sqlQuery.addScalar(field.getName(), new StringType()); } } Object object = sqlQuery.setResultTransformer(resultTransformer).list().get(0); session.getTransaction().commit(); return object; }
From source file:com.viettel.logistic.wms.dao.GoodsDAO.java
License:Open Source License
public List<GoodsDTO> getListGoods(GoodsDTO goodsDTO) { List<GoodsDTO> lstGoodsDTO = new ArrayList(); List lstParams = new ArrayList(); // //from w ww. ja v a 2 s .c o m StringBuffer sql = new StringBuffer(); // sql.append("SELECT g.cust_id custId,"); sql.append(" g.goods_id goodsId,"); sql.append(" FROM goods g, stock_goods_total sg"); sql.append(" WHERE s.stock_id = sg.owner_id"); sql.append(" AND g.goods_id = sg.goods_id"); sql.append(" AND sg.amount_issue > 0"); //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(goodsDTO.getCustId())) { sql.append(" AND sg.cust_id = ?"); sql.append(" AND g.cust_id = ?"); lstParams.add(goodsDTO.getCustId()); lstParams.add(goodsDTO.getCustId()); } // sql.append(" GROUP BY sg.cust_id"); sql.append(" ORDER BY sg.cust_id"); //Map DTO SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("ownerCode", new StringType()); query.addScalar("ownerName", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("amount", new StringType()); query.addScalar("amountIssue", new StringType()); query.addScalar("unitType", new StringType()); //Set cac gia tri tham so for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } // return query.list(); }
From source file:com.viettel.logistic.wms.dao.GoodsDAO.java
License:Open Source License
public List<GoodsDTO> getListGoodsWithCustId(String custId) { List<GoodsDTO> lstGoodsDTO = new ArrayList(); List lstParams = new ArrayList(); // // w ww .ja v a 2 s . com if (DataUtil.isStringNullOrEmpty(custId)) { return lstGoodsDTO; } StringBuilder sql = new StringBuilder(); // sql.append("SELECT a.goods_id goodsId, a.code code, a.name name, "); sql.append(" a.goods_type goodsType, a.unit_type unitType, "); sql.append(" a.is_serial isSerial, a.is_serial_strip isSerialStrip "); sql.append(" FROM goods a "); sql.append(" WHERE a.cust_id = ? AND a.status = '1' "); //ChuDV add : 11/12/2015 -- sql.append( " AND EXISTS (SELECT 1 FROM stock_goods_total WHERE cust_id = ? AND goods_id = a.goods_id AND (amount != 0 OR amount_issue != 0))"); //Tim kiem theo khach hang lstParams.add(custId.trim()); lstParams.add(custId.trim()); //Map DTO SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(GoodsDTO.class)); query.addScalar("goodsId", new StringType()); query.addScalar("code", new StringType()); query.addScalar("name", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("unitType", new StringType()); query.addScalar("isSerial", new StringType()); query.addScalar("isSerialStrip", new StringType()); //Set cac gia tri tham so for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } //Tra ve danh sach hang hoa return query.list(); }
From source file:com.viettel.logistic.wms.dao.GoodsDAO.java
License:Open Source License
/** * NgocND6 get list goods serial by cust id * @param custId/*from w w w . ja va 2 s. c o m*/ * @param isSerial * @param isSerialStrip * @return */ public List<GoodsDTO> getListGoodsSerialByCustId(String custId, String isSerial, String isSerialStrip) { List<GoodsDTO> lstGoodsDTO = new ArrayList(); List lstParams = new ArrayList(); if (DataUtil.isStringNullOrEmpty(custId)) { return lstGoodsDTO; } StringBuilder sql = new StringBuilder(); // sql.append("SELECT a.goods_id goodsId, a.code code, a.name name, "); sql.append(" a.unit_type unitType, "); sql.append(" a.is_serial isSerial, a.is_serial_strip isSerialStrip, a.goods_type goodsType "); sql.append(" FROM goods a "); sql.append(" WHERE a.cust_id = ? AND a.status = '1' "); lstParams.add(custId.trim()); if (!DataUtil.isStringNullOrEmpty(isSerial)) { sql.append(" AND a.is_serial = ? "); lstParams.add(isSerial); } if (!DataUtil.isStringNullOrEmpty(isSerialStrip)) { sql.append(" AND a.is_serial_strip = ? "); lstParams.add(isSerialStrip); } //Map DTO SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(GoodsDTO.class)); query.addScalar("goodsId", new StringType()); query.addScalar("code", new StringType()); query.addScalar("name", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("unitType", new StringType()); query.addScalar("isSerial", new StringType()); query.addScalar("isSerialStrip", new StringType()); //Set cac gia tri tham so for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } //Tra ve danh sach hang hoa return query.list(); }
From source file:com.viettel.logistic.wms.dao.InventoryResultDAO.java
License:Open Source License
public List<InventoryResultDTO> getLstCheckedResult(String inventoryActionId) { List<InventoryResultDTO> lstInvenResult = new ArrayList<>(); StringBuffer sql = new StringBuffer(); //create sql// w w w . ja v a2 s . c o m sql.append(" SELECT "); sql.append(" NVL(a.inventory_result_id, b.inventory_result_id) inventoryResultId "); sql.append(" ,NVL(a.inventory_action_id, b.inventory_action_id) inventoryActionId "); sql.append(" ,NVL(a.goods_code, b.goods_code) goodsCode "); sql.append(" ,NVL(a.goods_name, b.goods_name) goodsName "); sql.append(" ,NVL(a.goods_id, b.goods_id) goodsId "); sql.append(" ,NVL(a.unit_id, b.unit_id) unitId "); sql.append(" ,NVL(a.unit_name, b.unit_name) unitName "); sql.append(" ,NVL(a.cell_id, b.cell_id) cellId "); sql.append(" ,NVL(a.cell_code, b.cell_code) cellCode "); sql.append(" ,NVL(a.barcode, b.barcode) barcode "); sql.append(" ,NVL(a.from_serial, b.from_serial) fromSerial "); sql.append(" ,NVL(a.to_serial, b.to_serial) toSerial "); sql.append(" ,NVL(b.amount_inventory,0) amountInventory "); sql.append(" ,NVL(a.amount,0) amount "); sql.append(" ,NVL(a.note, b.note) note "); sql.append(" ,NVL(b.amount_inventory,0) - NVL(a.amount,0) as amountFalse "); sql.append(" ,NVL(a.type, b.type) type "); sql.append(" FROM (select * from inventory_result where inventory_action_id = ? and type = '1') a "); sql.append(" full join (select * from inventory_result where inventory_action_id = ? and type = '2') b "); sql.append(" on NVL(a.goods_code, 'ABC') = NVL(b.goods_code , 'ABC') "); sql.append(" and NVL(a.cell_code, 'ABC')= NVL(b.cell_code, 'ABC') "); sql.append(" and NVL(a.barcode, 'ABC')= NVL(b.barcode , 'ABC') "); sql.append(" and NVL(a.from_serial, 'ABC')= NVL(b.from_serial , 'ABC') "); sql.append(" and NVL(a.to_serial, 'ABC')= NVL(b.to_serial, 'ABC') "); //SET PARAMETTER SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(InventoryResultDTO.class)); query.addScalar("inventoryResultId", new StringType()); query.addScalar("inventoryActionId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("unitId", new StringType()); query.addScalar("unitName", new StringType()); query.addScalar("cellId", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("barcode", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("amountInventory", new StringType()); query.addScalar("amount", new StringType()); query.addScalar("note", new StringType()); query.addScalar("amountFalse", new StringType()); query.addScalar("type", new StringType()); int inventoryId = 0; try { inventoryId = Integer.parseInt(inventoryActionId); } catch (Exception e) { } //SET PARAMETER query.setParameter(0, inventoryId); query.setParameter(1, inventoryId); // lstInvenResult = query.list(); //List list; // if(lstInvenResult == null || lstInvenResult.size()==0) // { // return lstInvenResult; // } return lstInvenResult; }
From source file:com.viettel.logistic.wms.dao.InventoryResultDAO.java
License:Open Source License
public List<InventoryResultDTO> getInventoryResultByDisplayField(String inventoryActionId, List<String> lstShowField) { List<InventoryResultDTO> lstInvenResult = new ArrayList<>(); StringBuffer sql = new StringBuffer(); String sqlStr = ""; //// w ww . j a v a 2 s .c o m boolean isShowAll = false; //create sql //init show file check boolean isShowLoaction = false; boolean isShowGoodsInfo = false; boolean isShowBincode = false; boolean isShowAmount = false; boolean isShowSerial = false; if (lstShowField != null && lstShowField.size() > 0) { for (String z : lstShowField) { switch (z) { case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.LOCATION: isShowLoaction = true; break; case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.GOODS_INFO: isShowGoodsInfo = true; break; case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.BINCODE_BARCODE: isShowBincode = true; break; case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.AMOUNT: isShowAmount = true; break; case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.SERIAL: isShowSerial = true; break; } } } else { isShowAll = true; } if (isShowAll) { //CREATE SQL sql.append(" SELECT distinct from "); sql.append(" a.goods_code goodsCode,"); sql.append(" a.goods_name goodsName,"); sql.append(" a.cell_code cellCode,"); sql.append(" a.barcode barcode,"); sql.append(" a.from_serial fromSerial,"); sql.append(" a.to_serial toSerial, "); sql.append(" sum (a.amount) as amount "); sql.append(" from inventory_result a "); sql.append( " where inventory_action_id = ? and a.type = ? group by goods_code,goods_name,cell_code,barcode,from_serial,to_serial "); sql.append(" order by goods_code "); } else { boolean isHaveCondition = false; // sql.append(" SELECT distinct "); if (isShowGoodsInfo) { if (isHaveCondition) { sql.append(" ,a.goods_code goodsCode "); sql.append(" ,a.goods_name goodsName "); } else { sql.append(" a.goods_code goodsCode, "); sql.append(" a.goods_name goodsName "); } isHaveCondition = true; } if (isShowLoaction) { if (isHaveCondition) { sql.append(" ,a.cell_code cellCode "); } else { sql.append(" a.cell_code cellCode "); } isHaveCondition = true; } if (isShowBincode) { if (isHaveCondition) { sql.append(" ,a.barcode barcode "); } else { sql.append(" a.barcode barcode "); } isHaveCondition = true; } if (isShowSerial) { if (isHaveCondition) { sql.append(" ,a.from_serial fromSerial "); sql.append(" ,a.to_serial toSerial "); } else { sql.append(" a.from_serial fromSerial, "); sql.append(" a.to_serial toSerial "); } isHaveCondition = true; } if (isShowAmount) { if (isHaveCondition) { sql.append(" ,sum (a.amount) as amount "); } else { sql.append(" sum (a.amount) as amount "); } isHaveCondition = true; } sql.append(" from inventory_result a "); sql.append(" where inventory_action_id = ? and a.type = ? group by "); if (isShowGoodsInfo) { sql.append(" goods_code,goods_name,"); } if (isShowLoaction) { sql.append(" cell_code,"); } if (isShowBincode) { sql.append(" barcode,"); } if (isShowSerial) { sql.append(" from_serial,to_serial,"); } if (isShowAmount) { sql.append(" amount,"); } sqlStr = sql.substring(0, sql.length() - 1); if (isShowGoodsInfo) { sqlStr += " order by goods_code "; } } //SET PARAMETTER SQLQuery query = getSession().createSQLQuery(sqlStr); query.setResultTransformer(Transformers.aliasToBean(InventoryResultDTO.class)); if (isShowAll) { query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("barcode", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("amount", new StringType()); } else { if (isShowGoodsInfo) { query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); } if (isShowLoaction) { query.addScalar("cellCode", new StringType()); } if (isShowBincode) { query.addScalar("barcode", new StringType()); } if (isShowSerial) { query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); } if (isShowAmount) { query.addScalar("amount", new StringType()); } } int inventoryId = 0; try { inventoryId = Integer.parseInt(inventoryActionId); } catch (Exception e) { } //SET PARAMETER query.setParameter(0, inventoryId); query.setParameter(1, "1"); // lstInvenResult = query.list(); return lstInvenResult; }
From source file:com.viettel.logistic.wms.dao.MapStaffGoodsDAO.java
License:Open Source License
public List<MapStaffGoodsDTO> getListStaffByGoods(String codeList, String custId) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append(" SELECT DISTINCT m.staff_id staffId,"); sql.append(" m.staff_code staffCode,"); sql.append(" m.staff_name staffName,"); sql.append(" m.staff_type staffType,"); sql.append(" m.staff_email staffEmail"); sql.append(" FROM map_staff_goods m, goods g"); sql.append(" WHERE m.goods_id = g.goods_id "); sql.append(" AND g.status = 1"); sql.append(" AND m.staff_email IS NOT NULL"); sql.append(" AND g.cust_id = ?"); lstParams.add(custId);//from w w w. jav a2s.c om sql.append(" AND m.goods_code IN (:idx)"); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(MapStaffGoodsDTO.class)); query.addScalar("staffId", new StringType()); query.addScalar("staffCode", new StringType()); query.addScalar("staffName", new StringType()); query.addScalar("staffType", new StringType()); query.addScalar("staffEmail", new StringType()); for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings().splitToList(codeList); query.setParameterList("idx", lst); return query.list(); }
From source file:com.viettel.logistic.wms.dao.ProposedReceiptDAO.java
License:Open Source License
public List<ProposedReceipt> getListProposedReceiptByCondition(ProposedReceiptDTO proposedReceiptDTO) { List<ProposedReceipt> lstProposedReceiptDTOs = new ArrayList<>(); try {/*from www . j a v a 2 s. c o m*/ StringBuilder sql = new StringBuilder(); List params = new ArrayList(); sql.append(" SELECT a.proposed_receipt_id proposedReceiptId,"); sql.append(" a.shipment_code shipmentCode,"); sql.append(" a.proposed_receipt_code proposedReceiptCode,"); sql.append(" a.stock_id stockId,"); sql.append(" a.customer_id customerId,"); sql.append(" a.number_invoice numberInvoice,"); sql.append(" a.expected_date expectedDate,"); sql.append(" a.create_date createDate,"); sql.append(" a.implementer_name implementerName,"); sql.append(" a.note note,"); sql.append(" a.contact_person contactPerson,"); sql.append(" a.status status,"); sql.append(" a.receive_location receiveLocation,"); sql.append(" a.attach_file_list attachFileList,"); sql.append(" a.recent_history_id recentHistoryId"); sql.append(" FROM proposed_receipt a"); sql.append(" WHERE 1=1"); //Tim kiem theo mat hang if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getGoodsName())) { sql.append(" AND a.proposed_receipt_id IN (SELECT DISTINCT c.request_id "); sql.append(" FROM goods_handover c"); sql.append(" WHERE lower(c.goods_name) LIKE lower(?) "); sql.append(" AND c.type = '1')"); params.add("%" + proposedReceiptDTO.getGoodsName() + "%"); // } //Tim kiem theo nguoi tao if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getImplementerName())) { sql.append(" AND a.implementer_name = ?"); params.add(proposedReceiptDTO.getImplementerName()); } //Tim kiem theo kho if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getStockId())) { sql.append(" AND a.stock_id = ?"); params.add(proposedReceiptDTO.getStockId()); } //Tim kiem theo khach hang if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCustomerId())) { sql.append(" AND a.customer_id = ?"); params.add(proposedReceiptDTO.getCustomerId()); } //Tim kiem theo trang thai if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getStatus())) { sql.append(" AND a.status = ?"); params.add(proposedReceiptDTO.getStatus()); } //Tim kiem theo ngay du kien nhap tu ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getExpectedDate())) { sql.append(" AND a.expected_date >= to_date(?,'dd/mm/yyyy')"); params.add(proposedReceiptDTO.getExpectedDate()); } //Tim kiem theo ngay du kien nhap den ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getExpectedDateToDate())) { sql.append(" AND a.expected_date <= to_date(?,'dd/mm/yyyy') + 1"); params.add(proposedReceiptDTO.getExpectedDateToDate()); } //Tim kiem theo ngay tao tu ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCreateDate())) { sql.append(" AND a.create_date >= to_date(?,'dd/mm/yyyy')"); params.add(proposedReceiptDTO.getCreateDate()); } //Tim kiem theo ngay tao den ngay if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCreateDateToDate())) { sql.append(" AND a.create_date <= to_date(?,'dd/mm/yyyy') + 1"); params.add(proposedReceiptDTO.getCreateDateToDate()); } //Tim kiem theo ma phieu if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getProposedReceiptCode())) { sql.append(" AND lower(a.proposed_receipt_code) like lower(?)"); params.add("%" + proposedReceiptDTO.getProposedReceiptCode() + "%"); } //Tim kiem theo ma lo hang if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getShipmentCode())) { sql.append(" AND lower(a.shipment_code) like lower(?)"); params.add("%" + proposedReceiptDTO.getShipmentCode() + "%"); } //Tim kiem theo so invoice if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getNumberInvoice())) { sql.append(" AND lower(a.number_invoice) like lower(?) "); params.add("%" + proposedReceiptDTO.getNumberInvoice() + "% \n"); } //Sap xep theo ma phieu sql.append(" ORDER BY a.create_date DESC"); //Tao cau lenh sql SQLQuery query = getSession().createSQLQuery(sql.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(ProposedReceipt.class)); query.addScalar("proposedReceiptId", new LongType()); query.addScalar("shipmentCode", new StringType()); query.addScalar("proposedReceiptCode", new StringType()); query.addScalar("stockId", new LongType()); query.addScalar("customerId", new LongType()); query.addScalar("numberInvoice", new StringType()); query.addScalar("expectedDate", new DateType()); query.addScalar("createDate", new DateType()); query.addScalar("implementerName", new StringType()); query.addScalar("note", new StringType()); query.addScalar("contactPerson", new StringType()); query.addScalar("status", new StringType()); query.addScalar("receiveLocation", new StringType()); query.addScalar("attachFileList", new StringType()); query.addScalar("recentHistoryId", new LongType()); //Truyen cac tham so truyen vao de thuc hien tim kiem for (int i = 0; i < params.size(); i++) { query.setParameter(i, params.get(i)); } //Day du lieu ra danh sach doi tuong lstProposedReceiptDTOs = query.list(); } catch (Exception e) { lstProposedReceiptDTOs = new ArrayList<>(); e.printStackTrace(); } return lstProposedReceiptDTOs; }
From source file:com.viettel.logistic.wms.dao.SerialInventoryDAO.java
License:Open Source License
public List<SerialInventoryReportDTO> getListSerialInventoryReportType1(SerialInventoryDTO serialInventoryDTO) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append(//from w w w . j av a 2 s . com " select NVL(a.goods_id,b.goods_id) as goodsId ,NVL (a.goods_Code,b.goods_code) as goodsCode , "); sql.append(" NVL( a.goods_name,b.goods_name) as goodsName, "); sql.append(" NVL( a.goods_unit_type_name,b.goods_unit_type_name) as goodsUnitTypeName, "); sql.append(" NVL(quantity_state_1,0) as quantityState1,NVL(quantity_state_2,0) as quantityState2, "); sql.append(" (NVL(quantity_state_1,0) + NVL( quantity_state_2,0)) as total "); sql.append(" FROM ( "); sql.append( " select goods_id, goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_1 from serial_inventory "); sql.append(" where cust_id = ? and stock_id = ? and goods_state = 1 "); //------------------------------------------------------------------ lstParams.add(serialInventoryDTO.getCustId()); lstParams.add(serialInventoryDTO.getStockId()); //dieu kien inventory code if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) { sql.append(" AND inventory_code = ? "); lstParams.add(serialInventoryDTO.getInventoryCode()); } //dieu kien ma hang if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) { sql.append(" AND goods_code in ( "); sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode())); sql.append(" ) "); } //dieu dieu kien tu ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) { sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getCreateDate()); } //dieu kien den ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) { sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getStaffId()); } //dieu kien nguoi tao if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) { sql.append(" AND staff_name like ('%"); sql.append(serialInventoryDTO.getStaffName()); sql.append("%') "); } //------------------------------------------------------------------ sql.append(" group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state) a "); sql.append(" FULL JOIN ( "); sql.append( " select goods_id,goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_2 from serial_inventory "); sql.append(" where cust_id = ? and stock_id = ? and goods_state = 2 "); //------------------------------------------------------------------ lstParams.add(serialInventoryDTO.getCustId()); lstParams.add(serialInventoryDTO.getStockId()); //dieu kien inventory code if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) { sql.append(" AND inventory_code = ? "); lstParams.add(serialInventoryDTO.getInventoryCode()); } //dieu kien ma hang if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) { sql.append(" AND goods_code in ( "); sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode())); sql.append(" ) "); } //dieu dieu kien tu ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) { sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getCreateDate()); } //dieu kien den ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) { sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getStaffId()); } //dieu kien nguoi tao if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) { sql.append(" AND staff_name like ('%"); sql.append(serialInventoryDTO.getStaffName()); sql.append("%') "); } //------------------------------------------------------------------ sql.append(" group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state ) b "); sql.append(" ON a.goods_id = b.goods_id "); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(SerialInventoryReportDTO.class)); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsUnitTypeName", new StringType()); query.addScalar("quantityState1", new StringType()); query.addScalar("quantityState2", new StringType()); query.addScalar("total", new StringType()); // for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } List<SerialInventoryReportDTO> lstResult = query.list(); for (SerialInventoryReportDTO i : lstResult) { i.setGoodsType("1"); } return lstResult; }
From source file:com.viettel.logistic.wms.dao.SerialInventoryDAO.java
License:Open Source License
public List<SerialInventoryReportDTO> getListSerialInventoryReportType2(SerialInventoryDTO serialInventoryDTO) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append(/*from ww w . j av a2 s. c o m*/ " select NVL(a.goods_id,b.goods_id) as goodsId ,NVL (a.goods_Code,b.goods_code) as goodsCode , "); sql.append(" NVL( a.goods_name,b.goods_name) as goodsName, "); sql.append(" NVL( a.goods_unit_type_name,b.goods_unit_type_name) as goodsUnitTypeName, "); sql.append(" NVL(quantity_state_1,0) as quantityState1,NVL(quantity_state_2,0) as quantityState2, "); sql.append(" (NVL(quantity_state_1,0) + NVL( quantity_state_2,0)) as total "); sql.append(" FROM ( "); sql.append( " select goods_id, goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_1 from no_serial_inventory "); sql.append(" where cust_id = ? and stock_id = ? and goods_state = 1 "); //------------------------------------------------------------------ lstParams.add(serialInventoryDTO.getCustId()); lstParams.add(serialInventoryDTO.getStockId()); //dieu kien inventory code if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) { sql.append(" AND inventory_code = ? "); lstParams.add(serialInventoryDTO.getInventoryCode()); } //dieu kien ma hang if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) { sql.append(" AND goods_code in ( "); sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode())); sql.append(" ) "); } //dieu dieu kien tu ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) { sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getCreateDate()); } //dieu kien den ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) { sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getStaffId()); } //dieu kien nguoi tao if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) { sql.append(" AND staff_name like ('%"); sql.append(serialInventoryDTO.getStaffName()); sql.append("%') "); } //------------------------------------------------------------------ sql.append(" group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state) a "); sql.append(" FULL JOIN ( "); sql.append( " select goods_id,goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_2 from no_serial_inventory "); sql.append(" where cust_id = ? and stock_id = ? and goods_state = 2 "); //------------------------------------------------------------------ lstParams.add(serialInventoryDTO.getCustId()); lstParams.add(serialInventoryDTO.getStockId()); //dieu kien inventory code if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) { sql.append(" AND inventory_code = ? "); lstParams.add(serialInventoryDTO.getInventoryCode()); } //dieu kien ma hang if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) { sql.append(" AND goods_code in ( "); sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode())); sql.append(" ) "); } //dieu dieu kien tu ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) { sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getCreateDate()); } //dieu kien den ngay if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) { sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') "); lstParams.add(serialInventoryDTO.getStaffId()); } //dieu kien nguoi tao if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) { sql.append(" AND staff_name like ('%"); sql.append(serialInventoryDTO.getStaffName()); sql.append("%') "); } //------------------------------------------------------------------ sql.append(" group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state ) b "); sql.append(" ON a.goods_id = b.goods_id "); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(SerialInventoryReportDTO.class)); query.addScalar("goodsId", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("goodsUnitTypeName", new StringType()); query.addScalar("quantityState1", new StringType()); query.addScalar("quantityState2", new StringType()); query.addScalar("total", new StringType()); // for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } List<SerialInventoryReportDTO> lstResult = query.list(); for (SerialInventoryReportDTO i : lstResult) { i.setGoodsType("2"); } return lstResult; }