List of usage examples for org.hibernate SQLQuery setParameterList
@Override
NativeQuery<T> setParameterList(String name, Object[] values);
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java
License:Open Source License
public List<StockGoodsSerialInforDTO> getListStockGoodsSerialInforByStaff( StockGoodsSerialStripDTO stockGoodsSerialStripDTO) { List<StockGoodsSerialInforDTO> lstStockGoodsSerialInfor = null; StringBuffer sql = new StringBuffer(); List lstParams = new ArrayList(); ///* w w w. java 2 s .c om*/ sql.append("SELECT a.cust_id custId,"); sql.append(" a.owner_id ownerId,"); sql.append(" a.owner_type ownerType,"); sql.append(" a.cell_code cellCode,"); sql.append(" a.goods_id goodsId,"); sql.append(" a.state goodsState,"); sql.append(" a.status,"); sql.append(" a.channel_type_id channelTypeId,"); sql.append(" a.from_serial fromSerial,"); sql.append(" a.to_serial toSerial, a.quantity, "); sql.append(" g.goods_type goodsType, g.goods_group goodsGroup, "); sql.append(" g.code goodsCode, g.name goodsName, "); sql.append(" msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName "); sql.append(" FROM table(show_serial_strip (cursor ( SELECT sts.cust_id,"); sql.append(" sts.owner_id,"); sql.append(" sts.owner_type,"); sql.append(" sts.cell_code,"); sql.append(" sts.goods_id,"); sql.append(" sts.goods_state,"); sql.append(" sts.status,"); sql.append(" sts.channel_type_id,"); sql.append(" sts.serial"); sql.append(" FROM stock_goods_serial sts"); sql.append(" WHERE 1 = 1 "); //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) { sql.append("AND sts.cust_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getCustId()); } //Tim kiem theo kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) { sql.append("AND sts.owner_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerId()); } //Tim kiem theo loai kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) { sql.append("AND sts.owner_type = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerType()); } //Tim kien theo mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) { sql.append("AND sts.goods_id IN ("); sql.append(stockGoodsSerialStripDTO.getGoodsId()); sql.append(") "); } //Tim theo tinh trang hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) { sql.append("AND sts.goods_state = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsState()); } //Tim kiem trang thai serial hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) { sql.append("AND sts.status = ? "); lstParams.add(stockGoodsSerialStripDTO.getStatus()); } //Tim kiem theo Kenh if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) { sql.append("AND sts.channel_type_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId()); } //Tim kiem theo from serial if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) { sql.append("AND sts.serial >= ? "); lstParams.add(stockGoodsSerialStripDTO.getFromSerial()); } //Tim kiem theo to serial if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) { sql.append("AND sts.serial <= ? "); lstParams.add(stockGoodsSerialStripDTO.getToSerial()); } //Tim theo cell if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) { sql.append("AND sts.cell_code = ? "); lstParams.add(stockGoodsSerialStripDTO.getCellCode()); } //Tim theo Barcode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) { sql.append("AND sts.barcode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBarcode()); } //Tim theo Bincode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) { sql.append("AND sts.bincode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBincode()); } // sql.append(" ORDER BY sts.cust_id,sts.owner_id,sts.owner_type,sts.cell_code,"); sql.append(" sts.goods_id,sts.goods_state,sts.status,sts.serial),"); sql.append(" TO_NUMBER (?))) a, goods g, map_staff_goods msg "); sql.append(" WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id "); sql.append(" AND a.goods_id = msg.goods_id(+)"); lstParams.add(stockGoodsSerialStripDTO.getQuantity()); //tim kiem theo nhan vien if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStaffId())) { sql.append(" AND msg.staff_id = ?"); lstParams.add(stockGoodsSerialStripDTO.getStaffId()); } //ThienNG1- Begin AddBy 18/06/2015 //Tim kien theo loai mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) { //sql.append("AND g.goods_type in (?) "); if (stockGoodsSerialStripDTO.getGoodsType().contains(",")) { sql.append("AND g.goods_type IN (:gdstype) "); } else { sql.append("AND g.goods_type IN (?) "); } lstParams.add(stockGoodsSerialStripDTO.getGoodsType()); } //End sql.append( " ORDER BY a.cust_id,a.owner_id,a.owner_type,g.goods_type,g.code,a.cell_code,a.state,a.from_serial,a.status "); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("channelTypeId", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("quantity", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsGroup", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("staffId", new StringType()); query.addScalar("staffCode", new StringType()); query.addScalar("staffName", new StringType()); //ThienNG1 update 03/08/2015 for (int i = 0; i < lstParams.size(); i++) { if (lstParams.get(i) != null) { if (lstParams.get(i).toString().contains(",")) { List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings() .splitToList(stockGoodsSerialStripDTO.getGoodsType().toString()); query.setParameterList("gdstype", lst); } else { query.setParameter(i, lstParams.get(i)); } } else { query.setParameter(i, lstParams.get(i)); } } // return query.list(); }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java
License:Open Source License
public List<StockGoodsSerialInforDTO> getListStockGoodsSerialInforAll( StockGoodsSerialStripDTO stockGoodsSerialStripDTO) { List<StockGoodsSerialInforDTO> lstStockGoodsSerialInfor = null; StringBuffer sql = new StringBuffer(); List lstParams = new ArrayList(); ///*from ww w . ja va2s .c om*/ sql.append("SELECT a.cust_id custId,"); sql.append(" a.owner_id ownerId,"); sql.append(" a.owner_type ownerType,"); sql.append(" a.cell_code cellCode,"); sql.append(" a.goods_id goodsId,"); sql.append(" a.state goodsState,"); sql.append(" a.status,"); sql.append(" a.channel_type_id channelTypeId,"); sql.append(" a.from_serial fromSerial,"); sql.append(" a.to_serial toSerial, a.quantity, "); sql.append(" g.goods_type goodsType, g.goods_group goodsGroup, "); sql.append(" g.code goodsCode, g.name goodsName "); sql.append(" FROM table(show_serial_strip (cursor ( SELECT sts.cust_id,"); sql.append(" sts.owner_id,"); sql.append(" sts.owner_type,"); sql.append(" sts.cell_code,"); sql.append(" sts.goods_id,"); sql.append(" sts.goods_state,"); sql.append(" sts.status,"); sql.append(" sts.channel_type_id,"); sql.append(" sts.serial"); sql.append(" FROM stock_goods_serial sts"); sql.append(" WHERE 1 = 1 "); //Tim kien theo mat hang // if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) { // sql.append("AND sts.goods_id = ? "); // lstParams.add(stockGoodsSerialStripDTO.getGoodsId()); // } if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) { sql.append("AND sts.goods_id IN ("); sql.append(stockGoodsSerialStripDTO.getGoodsId()); sql.append(") "); } //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) { sql.append("AND sts.cust_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getCustId()); } //Tim kiem theo kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) { sql.append("AND sts.owner_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerId()); } //Tim kiem theo loai kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) { sql.append("AND sts.owner_type = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerType()); } //Tim theo tinh trang hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) { sql.append("AND sts.goods_state = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsState()); } //Tim kiem trang thai serial hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) { sql.append("AND sts.status = ? "); lstParams.add(stockGoodsSerialStripDTO.getStatus()); } //Tim kiem theo Kenh if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) { sql.append("AND sts.channel_type_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId()); } //Tim kiem theo from serial if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) { sql.append("AND sts.serial >= ? "); lstParams.add(stockGoodsSerialStripDTO.getFromSerial()); } //Tim kiem theo to serial if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) { sql.append("AND sts.serial <= ? "); lstParams.add(stockGoodsSerialStripDTO.getToSerial()); } //Tim theo cell if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) { sql.append("AND sts.cell_code = ? "); lstParams.add(stockGoodsSerialStripDTO.getCellCode()); } //Tim theo Barcode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) { sql.append("AND sts.barcode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBarcode()); } //Tim theo Bincode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) { sql.append("AND sts.bincode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBincode()); } // sql.append(" ORDER BY sts.cust_id,sts.owner_id,sts.owner_type,sts.cell_code,"); sql.append(" sts.goods_id,sts.goods_state,sts.status,sts.serial),"); sql.append(" TO_NUMBER (?))) a, goods g "); sql.append(" WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id "); lstParams.add(stockGoodsSerialStripDTO.getQuantity()); //ThienNG1- Begin AddBy 18/06/2015 //Tim kien theo loai mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) { //sql.append("AND g.goods_type in (?) "); if (stockGoodsSerialStripDTO.getGoodsType().contains(",")) { sql.append("AND g.goods_type IN (:gdstype) "); } else { sql.append("AND g.goods_type IN (?) "); } lstParams.add(stockGoodsSerialStripDTO.getGoodsType()); } //End sql.append( " ORDER BY a.cust_id,a.owner_id,a.owner_type,g.goods_type,g.code,a.cell_code,a.state,a.from_serial,a.status "); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("channelTypeId", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("quantity", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsGroup", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); //ThienNG1 update 03/08/2015 for (int i = 0; i < lstParams.size(); i++) { if (lstParams.get(i) != null) { if (lstParams.get(i).toString().contains(",")) { List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings() .splitToList(stockGoodsSerialStripDTO.getGoodsType().toString()); query.setParameterList("gdstype", lst); } else { query.setParameter(i, lstParams.get(i)); } } else { query.setParameter(i, lstParams.get(i)); } } // return query.list(); }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java
License:Open Source License
public List<StockGoodsSerialInforDTO> getListStockGoodsSerialStripInforByStaff( StockGoodsSerialStripDTO stockGoodsSerialStripDTO) { List<StockGoodsSerialInforDTO> lstStockGoodsSerialInfor = null; StringBuffer sql = new StringBuffer(); List lstParams = new ArrayList(); ////from w w w . ja v a 2 s. co m sql.append("SELECT a.cust_id custId,"); sql.append(" a.owner_id ownerId,"); sql.append(" a.owner_type ownerType,"); sql.append(" a.cell_code cellCode,"); sql.append(" a.goods_id goodsId,"); sql.append(" a.goods_state goodsState,"); sql.append(" a.status,"); sql.append(" a.channel_type_id channelTypeId,"); sql.append(" a.from_serial fromSerial,"); sql.append(" a.to_serial toSerial, a.quantity, "); sql.append(" g.goods_type goodsType, g.goods_group goodsGroup, "); sql.append(" g.code goodsCode, g.name goodsName, "); sql.append(" msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName "); sql.append(" FROM stock_goods_serial_strip a, goods g, map_staff_goods msg "); sql.append(" WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id "); sql.append(" AND a.goods_id = msg.goods_id(+) "); //tim kiem theo nhan vien if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStaffId())) { sql.append(" AND msg.staff_id = ?"); lstParams.add(stockGoodsSerialStripDTO.getStaffId()); } //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) { sql.append("AND a.cust_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getCustId()); } //Tim kiem theo kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) { sql.append("AND a.owner_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerId()); } //Tim kiem theo loai kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) { sql.append("AND a.owner_type = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerType()); } //Tim kien theo mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) { sql.append("AND a.goods_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsId()); } //Tim theo tinh trang hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) { sql.append("AND a.goods_state = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsState()); } //Tim kiem trang thai serial hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) { sql.append("AND a.status = ? "); lstParams.add(stockGoodsSerialStripDTO.getStatus()); } //Tim kiem theo Kenh if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) { sql.append("AND a.channel_type_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId()); } // //Tim kiem theo from serial // if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) { // sql.append("AND sts.serial >= ? "); // lstParams.add(stockGoodsSerialStripDTO.getFromSerial()); // } // //Tim kiem theo to serial // if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) { // sql.append("AND sts.serial <= ? "); // lstParams.add(stockGoodsSerialStripDTO.getToSerial()); // } //Tim theo cell if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) { sql.append("AND a.cell_code = ? "); lstParams.add(stockGoodsSerialStripDTO.getCellCode()); } //Tim theo Barcode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) { sql.append("AND a.barcode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBarcode()); } //Tim theo Bincode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) { sql.append("AND a.bincode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBincode()); } //ThienNG1- Begin AddBy 18/06/2015 //Tim kien theo loai mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) { //ThienNG1 Update 03/08/2015 if (stockGoodsSerialStripDTO.getGoodsType().contains(",")) { sql.append("AND g.goods_type IN (:gdstype) "); } else { sql.append("AND g.goods_type IN (?) "); } lstParams.add(stockGoodsSerialStripDTO.getGoodsType()); } //End sql.append( " ORDER BY a.cust_id,a.owner_id,a.owner_type,g.goods_type,g.code,a.goods_state,a.cell_code,a.from_serial,a.status "); //lstParams.add(stockGoodsSerialStripDTO.getQuantity()); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("channelTypeId", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("quantity", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsGroup", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); query.addScalar("staffId", new StringType()); query.addScalar("staffCode", new StringType()); query.addScalar("staffName", new StringType()); //ThienNG1 Update 03/08/2015 for (int i = 0; i < lstParams.size(); i++) { if (lstParams.get(i).toString().contains(",")) { List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings() .splitToList(stockGoodsSerialStripDTO.getGoodsType().toString()); query.setParameterList("gdstype", lst); } else { query.setParameter(i, lstParams.get(i)); } } // return query.list(); }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java
License:Open Source License
public List<StockGoodsSerialInforDTO> getListStockGoodsSerialStripInforAll( StockGoodsSerialStripDTO stockGoodsSerialStripDTO) { List<StockGoodsSerialInforDTO> lstStockGoodsSerialInfor = null; StringBuffer sql = new StringBuffer(); List lstParams = new ArrayList(); ///* w ww .j av a 2s . com*/ sql.append("SELECT a.cust_id custId,"); sql.append(" a.owner_id ownerId,"); sql.append(" a.owner_type ownerType,"); sql.append(" a.cell_code cellCode,"); sql.append(" a.goods_id goodsId,"); sql.append(" a.goods_state goodsState,"); sql.append(" a.status,"); sql.append(" a.channel_type_id channelTypeId,"); sql.append(" a.from_serial fromSerial,"); sql.append(" a.to_serial toSerial, a.quantity, "); sql.append(" g.goods_type goodsType, g.goods_group goodsGroup, "); sql.append(" g.code goodsCode, g.name goodsName "); sql.append(" FROM stock_goods_serial_strip a, goods g "); sql.append(" WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id "); //Tim kiem theo khach hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) { sql.append("AND a.cust_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getCustId()); } //Tim kiem theo kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) { sql.append("AND a.owner_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerId()); } //Tim kiem theo loai kho hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) { sql.append("AND a.owner_type = ? "); lstParams.add(stockGoodsSerialStripDTO.getOwnerType()); } //Tim kien theo mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) { sql.append("AND a.goods_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsId()); } //Tim theo tinh trang hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) { sql.append("AND a.goods_state = ? "); lstParams.add(stockGoodsSerialStripDTO.getGoodsState()); } //Tim kiem trang thai serial hang hoa if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) { sql.append("AND a.status = ? "); lstParams.add(stockGoodsSerialStripDTO.getStatus()); } //Tim kiem theo Kenh if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) { sql.append("AND a.channel_type_id = ? "); lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId()); } // //Tim kiem theo from serial // if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) { // sql.append("AND sts.serial >= ? "); // lstParams.add(stockGoodsSerialStripDTO.getFromSerial()); // } // //Tim kiem theo to serial // if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) { // sql.append("AND sts.serial <= ? "); // lstParams.add(stockGoodsSerialStripDTO.getToSerial()); // } //Tim theo cell if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) { sql.append("AND a.cell_code = ? "); lstParams.add(stockGoodsSerialStripDTO.getCellCode()); } //Tim theo Barcode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) { sql.append("AND a.barcode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBarcode()); } //Tim theo Bincode if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) { sql.append("AND a.bincode = ? "); lstParams.add(stockGoodsSerialStripDTO.getBincode()); } //ThienNG1- Begin AddBy 18/06/2015 //Tim kien theo loai mat hang if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) { //ThienNG1 Update 03/08/2015 if (stockGoodsSerialStripDTO.getGoodsType().contains(",")) { sql.append("AND g.goods_type IN (:gdstype) "); } else { sql.append("AND g.goods_type IN (?) "); } lstParams.add(stockGoodsSerialStripDTO.getGoodsType()); } //End sql.append( " ORDER BY a.cust_id,a.owner_id,a.owner_type,g.goods_type,g.code,a.goods_state,a.cell_code,a.from_serial,a.status "); //lstParams.add(stockGoodsSerialStripDTO.getQuantity()); // SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class)); query.addScalar("custId", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("cellCode", new StringType()); query.addScalar("goodsId", new StringType()); query.addScalar("goodsState", new StringType()); query.addScalar("status", new StringType()); query.addScalar("channelTypeId", new StringType()); query.addScalar("fromSerial", new StringType()); query.addScalar("toSerial", new StringType()); query.addScalar("quantity", new StringType()); query.addScalar("goodsType", new StringType()); query.addScalar("goodsGroup", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsName", new StringType()); //ThienNG1 Update 03/08/2015 for (int i = 0; i < lstParams.size(); i++) { if (lstParams.get(i).toString().contains(",")) { List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings() .splitToList(stockGoodsSerialStripDTO.getGoodsType().toString()); query.setParameterList("gdstype", lst); } else { query.setParameter(i, lstParams.get(i)); } } // return query.list(); }
From source file:com.viettel.logistic.wms.dao.StockTransDAO.java
License:Open Source License
public List<StockTransDTO> getListStockTrans2Inventory(StockTransDTO stockTrans, String fromDate, String toDate, String stockTransType) {//from w ww. j ava 2s . c o m List<StockTransDTO> lstReturns = null; try { StringBuilder sql = new StringBuilder(); sql.append("SELECT a.STOCK_TRANS_ID stockTransId, "); sql.append(" a.stock_trans_code stockTransCode, "); sql.append(" a.DEPT_ID deptId, "); sql.append(" a.STOCK_TRANS_TYPE stockTransType, "); sql.append(" to_char(a.stock_trans_date,'dd/MM/yyyy hh24:mi:ss') stockTransDate, "); sql.append(" a.OWNER_ID ownerId, "); sql.append(" a.owner_type ownerType, "); sql.append(" a.IE_OWNER_ID ieOwnerId, "); sql.append(" a.IE_OWNER_TYPE ieOwnerType, "); sql.append(" a.CUST_CODE custCode, "); sql.append(" a.CUST_ID custId, "); sql.append(" a.REASON_ID reasonId, "); sql.append(" a.STOCK_TRANS_STATUS stockTransStatus, "); sql.append(" a.FROM_STOCK_TRANS_ID fromStockTransId, "); sql.append(" a.NOTES notes, "); sql.append(" a.ORDER_ID_LIST orderIdList, "); sql.append(" to_char(a.ORDER_DATE,'dd/MM/yyyy hh24:mi:ss') orderDate, "); sql.append(" a.ORDER_CODE orderCode, "); sql.append(" to_char(a.CREATE_DATETIME,'dd/MM/yyyy hh24:mi:ss') createDatetime, "); sql.append(" a.CUST_NAME custName, "); sql.append(" a.IS_UPDATE_CELL isUpdateCell, "); sql.append(" a.PARTNER_ID partnerId, "); sql.append(" to_char(a.REAL_STOCK_TRANS_DATE,'dd/MM/yyyy hh24:mi:ss') realStockTransDate, "); sql.append(" a.ATTACH_FILE_NAME attachFileName, "); sql.append(" a.ORDER_ACTION_CODE orderActionCode, "); sql.append(" a.TRANS_USER_ID transUserId, "); sql.append(" a.TRANS_USER_NAME transUserName, "); sql.append(" a.SYN_TRANS_CODE synTransCode, "); sql.append(" a.ADD_INFOR addInfor, "); sql.append(" a.VOFFICE_TRANSACTION_CODE vofficeTransCode, "); sql.append(" a.ORDER_COMMAND_CODE orderCommandCode "); sql.append(" FROM stock_trans a "); sql.append(" WHERE a.CUST_ID = :idx0 "); sql.append(" AND a.OWNER_ID = :idx1 "); sql.append(" AND a.STOCK_TRANS_TYPE IN ( :idx2 ) "); sql.append(" AND a.STOCK_TRANS_STATUS IN ( :idx3 ) "); sql.append(" AND TRUNC(a.CREATE_DATETIME) >= TO_DATE(:idx4, 'dd/MM/yyyy') "); sql.append(" AND TRUNC(a.CREATE_DATETIME) <= TO_DATE(:idx5, 'dd/MM/yyyy') "); sql.append(" AND EXISTS "); sql.append(" (SELECT st.STOCK_TRANS_CODE "); sql.append(" FROM stock_trans st "); sql.append(" WHERE a.STOCK_TRANS_CODE = st.STOCK_TRANS_CODE "); sql.append(" GROUP BY st.STOCK_TRANS_CODE "); sql.append(" HAVING COUNT(st.STOCK_TRANS_CODE) <= 1 "); sql.append(" )"); sql.append(" ORDER By a.stock_trans_date asc, stockTransType asc "); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(StockTransDTO.class)); //Truyen cac tham so List lstParams = new ArrayList(); lstParams.add(stockTrans.getCustId()); lstParams.add(stockTrans.getOwnerId()); lstParams.add(DataUtil.parseInputListString(stockTransType)); //Chi lay cac giao dich co trang thai = 1, 2 String stockTransStatus = "1,2"; lstParams.add(DataUtil.parseInputListString(stockTransStatus)); lstParams.add(fromDate); lstParams.add(toDate); //Kieu cua du lieu tra ra query.addScalar("stockTransId", new StringType()); query.addScalar("stockTransCode", new StringType()); query.addScalar("deptId", new StringType()); query.addScalar("stockTransType", new StringType()); query.addScalar("stockTransDate", new StringType()); query.addScalar("ownerId", new StringType()); query.addScalar("ownerType", new StringType()); query.addScalar("ieOwnerId", new StringType()); query.addScalar("ieOwnerType", new StringType()); query.addScalar("custCode", new StringType()); query.addScalar("custId", new StringType()); query.addScalar("reasonId", new StringType()); query.addScalar("stockTransStatus", new StringType()); query.addScalar("fromStockTransId", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("orderIdList", new StringType()); query.addScalar("orderDate", new StringType()); query.addScalar("orderCode", new StringType()); query.addScalar("createDatetime", new StringType()); query.addScalar("isUpdateCell", new StringType()); query.addScalar("partnerId", new StringType()); query.addScalar("realStockTransDate", new StringType()); query.addScalar("attachFileName", new StringType()); query.addScalar("orderActionCode", new StringType()); query.addScalar("transUserId", new StringType()); query.addScalar("transUserName", new StringType()); query.addScalar("synTransCode", new StringType()); query.addScalar("addInfor", new StringType()); query.addScalar("vofficeTransCode", new StringType()); query.addScalar("orderCommandCode", new StringType()); for (int i = 0; i < lstParams.size(); i++) { if (lstParams.get(i) instanceof String[]) { query.setParameterList("idx" + String.valueOf(i), (Object[]) lstParams.get(i)); } else { query.setParameter("idx" + String.valueOf(i), lstParams.get(i)); } } lstReturns = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstReturns; }
From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java
License:Open Source License
public List<Map<String, Object>> getReportTotal(Date fromDate, Date toDate, String by, String bankCode, ArrayList<Long> lstCpId) { List<Map<String, Object>> lst = new ArrayList(); try {//from w ww.j a v a 2 s . c om // String where = " where cp.trans_status = 2 and (cp.trans_type = 0 or cp.trans_type = 2) "; String where = " where (bp.error_code = '00' or bp.correct_code = '00') and (cp.trans_type = 0 or cp.trans_type = 2) "; where += " and cp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') and cp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss')"; where += " and cp.content_provider_id in (:cpId) "; if (bankCode != null && !bankCode.equals("")) { where += " and upper(bp.bank_code) = :bankCode "; } String groupBy = ""; String orderBy = ""; String sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, "; if (by.equals("MON")) { //Chuyen ngay thang thanh thang Calendar cal = Calendar.getInstance(); cal.setTime(fromDate); Calendar calFrom = Calendar.getInstance(); calFrom.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMinimum(Calendar.DAY_OF_MONTH), 0, 0, 0); cal.setTime(toDate); Calendar calTo = Calendar.getInstance(); calTo.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMaximum(Calendar.DAY_OF_MONTH), 23, 59, 59); fromDate = calFrom.getTime(); toDate = calTo.getTime(); sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, "; groupBy = " group by to_char(cp.request_date, 'MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name "; orderBy = " order by to_date(monDay, 'MM/yyyy') desc"; } else { sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'dd/MM/yyyy') monDay, "; groupBy = " group by to_char(cp.request_date, 'dd/MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name "; orderBy = " order by to_date(monDay, 'dd/MM/yyyy') desc"; } sql += " SUM(CASE cp.trans_type WHEN 0 THEN 1 ELSE 0 END) totalPay,"; sql += " SUM(CASE cp.trans_type WHEN 2 THEN 1 ELSE 0 END) totalRefund,"; sql += " SUM(CASE cp.trans_type WHEN 0 THEN cp.AMOUNT ELSE 0 END) payAmount,"; sql += " SUM(CASE cp.trans_type WHEN 2 THEN cp.AMOUNT ELSE 0 END) refundAmount,"; sql += " SUM(nvl(bp.fee,0)) totalFee,"; sql += " p.cp_code cpCode, p.cp_name cpName "; sql += " from trans_cp cp left join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id"; sql += " left join content_provider p on cp.content_provider_id = p.content_provider_id "; sql += where; sql += groupBy; sql += orderBy; log.debug(sql); SQLQuery query = getSession().createSQLQuery(sql); // query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class)); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss"); query.setParameter("fromDate", sdf.format(fromDate)); query.setParameter("toDate", sdf.format(toDate)); // query.setParameter("fromDate", fromDate); // query.setParameter("toDate", toDate); query.setParameterList("cpId", lstCpId); if (bankCode != null && !bankCode.equals("")) { query.setParameter("bankCode", bankCode.toUpperCase()); } lst = query.list(); } catch (Exception ex) { log.error("getReportTotal: ", ex); } finally { DAOFactory.commitCurrentSessions(); } return lst; }
From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java
License:Open Source License
public List<Map<String, Object>> getReportRefund(Date fromDate, Date toDate, String by, String bankCode, ArrayList<Long> lstCpId) { List<Map<String, Object>> lst = new ArrayList(); try {//from ww w.j a v a 2s . c o m String where = " where cp.trans_type = 2 "; where += " and cp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') and cp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss')"; // where += " and cp.request_date >= :fromDate and cp.request_date <= :toDate"; where += " and cp.content_provider_id in (:cpId) "; if (bankCode != null && !bankCode.equals("")) { where += " and upper(bp.bank_code) = :bankCode "; } String groupBy = ""; String orderBy = ""; String sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, "; if (by.equals("MON")) { //Chuyen ngay thang thanh thang Calendar cal = Calendar.getInstance(); cal.setTime(fromDate); Calendar calFrom = Calendar.getInstance(); calFrom.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMinimum(Calendar.DAY_OF_MONTH), 0, 0, 0); cal.setTime(toDate); Calendar calTo = Calendar.getInstance(); calTo.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMaximum(Calendar.DAY_OF_MONTH), 23, 59, 59); fromDate = calFrom.getTime(); toDate = calTo.getTime(); sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, "; groupBy = " group by to_char(cp.request_date, 'MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name"; orderBy = " order by to_date(monDay, 'MM/yyyy') desc"; } else { sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'dd/MM/yyyy') monDay, "; groupBy = " group by to_char(cp.request_date, 'dd/MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name"; orderBy = " order by to_date(monDay, 'dd/MM/yyyy') desc"; } sql += " SUM(CASE cp.trans_status WHEN '0' THEN 1 ELSE 0 END) totalpending,"; sql += " SUM(CASE cp.trans_status WHEN '2' THEN 1 ELSE 0 END) totalsuccess,"; sql += " SUM(CASE cp.trans_status WHEN '4' THEN 1 ELSE 0 END) totalfailure,"; sql += " SUM(CASE cp.trans_status WHEN '0' THEN cp.amount ELSE 0 END) amountpending,"; sql += " SUM(CASE cp.trans_status WHEN '2' THEN cp.amount ELSE 0 END) amountsuccess,"; sql += " SUM(CASE cp.trans_status WHEN '4' THEN cp.amount ELSE 0 END) amountfailure,"; sql += " p.cp_code cpCode, p.cp_name cpName "; // sql += " from trans_cp cp left join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id"; sql += " from trans_cp cp"; sql += " join trans_cp cp2 on cp.original_trans_id = cp2.trans_id "; sql += " left join trans_bankplus bp on cp2.trans_cp_id = bp.trans_cp_id "; sql += " left join content_provider p on cp.content_provider_id = p.content_provider_id "; sql += where; sql += groupBy; sql += orderBy; log.debug(sql); SQLQuery query = getSession().createSQLQuery(sql); // query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class)); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss"); query.setParameter("fromDate", sdf.format(fromDate)); query.setParameter("toDate", sdf.format(toDate)); // query.setParameter("fromDate", fromDate); // query.setParameter("toDate", toDate); query.setParameterList("cpId", lstCpId); if (bankCode != null && !bankCode.equals("")) { query.setParameter("bankCode", bankCode.toUpperCase()); } lst = query.list(); } catch (Exception ex) { log.error("getReportRefund: ", ex); } finally { DAOFactory.commitCurrentSessions(); } return lst; }
From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java
License:Open Source License
public List<Map<String, Object>> getReportEVNNPCBackup(Date fromDate, Date toDate, String status, ArrayList<Long> lstCpId) { List<Map<String, Object>> lst = new ArrayList(); log.debug("status = " + status); try {// w w w. j a v a 2 s . co m String where = "where bp.request_date >= :fromDate and bp.request_date <= :toDate"; where += " and cp.content_provider_id in (:cpId) "; if (!status.equals("NONE")) { where += " and cp.TRANS_STATUS = :transStatus "; } where += " and bp.process_code='300001'"; where += " and orgbp.process_code='300000'"; String groupBy = ""; String orderBy = ""; String sql = "select to_char(cp.request_date, 'dd/MM/yyyy') reqDate,bs.order_id orderId,bp.BILLING_CODE billingCode,bp.CUSTOMER_NAME customerName, "; sql += "bs.CUST_ADDRESS custAddress,bs.NUMBER_GCS numberGCS,bp.AMOUNT amount,bs.maCN maCN,cp.TRANS_ID transId,bp.MSISDN msisdn,bp.BANK_CODE bankCode,bp.TRANS_STATUS transStatus,cp.TRANS_STATUS cpTranstatus"; orderBy = " order by bs.NUMBER_GCS, to_date(reqDate, 'dd/MM/yyyy') desc"; sql += " from trans_bankplus bp"; sql += " join trans_bankplus orgbp on bp.original_request_id=orgbp.request_id"; sql += " join trans_cp cp on cp.TRANS_CP_ID = bp.TRANS_CP_ID "; sql += " join billing_service bs on bs.TRANS_BANKPLUS_ID = orgbp.TRANS_BANKPLUS_ID "; sql += where; sql += orderBy; log.debug(sql); SQLQuery query = getSession().createSQLQuery(sql); // query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class)); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); query.setParameter("fromDate", fromDate); query.setParameter("toDate", toDate); query.setParameterList("cpId", lstCpId); if (!status.equals("NONE")) { query.setParameter("transStatus", status); } lst = query.list(); } catch (Exception ex) { log.error("getReportRefund: ", ex); } finally { DAOFactory.commitCurrentSessions(); } return lst; }
From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java
License:Open Source License
public List<Map<String, Object>> getReportEVNNPC(Date fromDate, Date toDate, String status, ArrayList<Long> lstCpId) { List<Map<String, Object>> lst = new ArrayList(); log.debug("status = " + status); try {/*from w w w.j a v a 2 s . com*/ String sqlPart1 = "select xxxxxx.reqDate,xxxxxx.billingCode,\n" + "yyyyyy.customerName, \n" + "xxxxxx.amount,xxxxxx.transId,xxxxxx.msisdn,xxxxxx.bankCode,\n" + "xxxxxx.transStatus,xxxxxx.cpTranstatus,yyyyyy.custAddress,yyyyyy.numberGCS,yyyyyy.maCN,yyyyyy.orderId,xxxxxx.original_request_id,xxxxxx.request_id\n" + "from (select to_char(cp.request_date, 'dd/MM/yyyy') reqDate,UPPER(bp.BILLING_CODE) billingCode,\n" + "bp.CUSTOMER_NAME customerName, \n" + "bp.AMOUNT amount,cp.TRANS_ID transId,bp.MSISDN msisdn,bp.BANK_CODE bankCode,\n" + "" + "(case nvl(bp.error_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when '32' then \n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end)\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "else TO_CHAR(4)\n" + "end\n" + ")" + " transStatus," + "" + "(case nvl(bp.error_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when '32' then \n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end)\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "else TO_CHAR(4)\n" + "end\n" + ")" + " cpTranstatus,bp.original_request_id original_request_id, bp.request_id request_id\n" + "from trans_bankplus bp \n" + "join trans_cp cp on cp.TRANS_CP_ID = bp.TRANS_CP_ID \n" + "where bp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') \n" + "and bp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss') \n" + " and cp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') \n" + "and cp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss') \n" + "and cp.content_provider_id in (:cpId) \n"; String sqlPart2 = "and bp.process_code='300001'\n" + ") xxxxxx\n" + "join\n" + "(select bs.CUSTOMER_NAME customerName, bs.CUST_ADDRESS custAddress,bs.NUMBER_GCS numberGCS ,bs.maCN maCN,bs.order_id orderId,orgbp.request_id request_id\n" + "from trans_bankplus orgbp\n" + "join billing_service bs\n" + "on bs.TRANS_BANKPLUS_ID = orgbp.TRANS_BANKPLUS_ID\n" + "where orgbp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') \n" + "and orgbp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss') \n" + "and orgbp.process_code='300000') yyyyyy\n" + "on xxxxxx.original_request_id = yyyyyy.request_id" + " order by yyyyyy.numberGCS, to_date(xxxxxx.reqDate, 'dd/MM/yyyy') desc"; String sqlNew = sqlPart1; if (!status.equals("NONE")) { if (status.equals("2")) { sqlNew += " and (bp.ERROR_CODE = '00' OR bp.CORRECT_CODE = '00') "; } else if (status.equals("1")) { sqlNew += " and (bp.ERROR_CODE = '32' OR bp.ERROR_CODE is null) "; sqlNew += " and bp.CORRECT_CODE is null "; } else if (status.equals("0")) { sqlNew += " and cp.TRANS_STATUS = 0 "; } else if (status.equals("3")) { sqlNew += " and cp.TRANS_STATUS = 3 "; } else if (status.equals("4")) { sqlNew += " and ((bp.ERROR_CODE = '32' or bp.ERROR_CODE is null) and bp.CORRECT_CODE = '23')" + " and bp.ERROR_CODE <> '00' " + " and (bp.CORRECT_CODE is null or bp.CORRECT_CODE = '23' "; } // sqlNew += " and cp.TRANS_STATUS = :transStatus "; } sqlNew += sqlPart2; // String where = "where bp.request_date >= :fromDate and bp.request_date <= :toDate"; // where += " and cp.content_provider_id in (:cpId) "; // if (!status.equals("NONE")) { // where += " and cp.TRANS_STATUS = :transStatus "; // } // where += " and bp.process_code='300001'"; // where += " and orgbp.process_code='300000'"; // // String groupBy = ""; // String orderBy = ""; // String sql = "select to_char(cp.request_date, 'dd/MM/yyyy') reqDate,bs.order_id orderId,bp.BILLING_CODE billingCode,bp.CUSTOMER_NAME customerName, "; // sql += "bs.CUST_ADDRESS custAddress,bs.NUMBER_GCS numberGCS,bp.AMOUNT amount,bs.maCN maCN,cp.TRANS_ID transId,bp.MSISDN msisdn,bp.BANK_CODE bankCode,bp.TRANS_STATUS transStatus,cp.TRANS_STATUS cpTranstatus"; // // orderBy = " order by bs.NUMBER_GCS, to_date(reqDate, 'dd/MM/yyyy') desc"; // sql += " from trans_bankplus bp"; // sql += " join trans_bankplus orgbp on bp.original_request_id=orgbp.request_id"; // sql += " join trans_cp cp on cp.TRANS_CP_ID = bp.TRANS_CP_ID "; // sql += " join billing_service bs on bs.TRANS_BANKPLUS_ID = orgbp.TRANS_BANKPLUS_ID "; // sql += where; // sql += orderBy; log.debug(sqlNew); SQLQuery query = getSession().createSQLQuery(sqlNew); // query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class)); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); query.setParameter("fromDate", fromDate); query.setParameter("toDate", toDate); query.setParameterList("cpId", lstCpId); // if (!status.equals("NONE")) { // // query.setParameter("transStatus", status); // } lst = query.list(); } catch (Exception ex) { log.error("getReportRefund: ", ex); } finally { DAOFactory.commitCurrentSessions(); } return lst; }
From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java
License:Open Source License
public List<HashMap> getReport(String sql, HashMap param) { List lst = new ArrayList(); try {/*from ww w . j a v a 2 s.co m*/ SQLQuery query = getSession().createSQLQuery(sql); query.setResultTransformer(Transformers.aliasToBean(Transaction.class)); for (Object object : param.keySet()) { String key = object.toString(); Object val = param.get(key); if (val instanceof ArrayList) { //For select in query.setParameterList(key, (ArrayList) val); } else { query.setParameter(key, param.get(key)); } } query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); log.info(query.toString()); lst = query.list(); } catch (Exception ex) { log.error("getReport: ", ex); } finally { DAOFactory.commitCurrentSessions(); } return lst; }