Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. setParameter

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setParameter.

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

From source file:com.viettel.logistic.wms.dao.StockGoodsSerialStripDAO.java

License:Open Source License

public List getListSerilStripErrorImportStockCust(String stockTransId, String from_serial, String to_serial) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();

    sql.append("SELECT   ");
    sql.append("         a.ora_err_mesg$ addInfor,");
    sql.append("         a.cust_id custId,");
    sql.append("         a.owner_id ownerId,");
    sql.append("         a.owner_type ownerType,");
    sql.append("         a.goods_id goodsId,");
    sql.append("         a.goods_state goodsState,");
    sql.append("         a.status status,");
    sql.append("         a.from_serial fromSerial,");
    sql.append("         a.to_serial toSerial,");
    sql.append("         a.barcode barcode,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         g.code goodsCode,");
    sql.append("         g.name goodsName ");
    sql.append("  FROM   err$_stock_goods_serial_strip a, goods g ");
    sql.append("  WHERE   a.goods_id = g.goods_id ");
    ///* ww  w . j  av  a 2s  . c  om*/
    if (!DataUtil.isStringNullOrEmpty(stockTransId)) {
        sql.append("  AND a.import_stock_trans_id = ? ");
        lstParams.add(stockTransId);
    }
    sql.append("  AND   a.from_serial = ? ");
    sql.append("  AND   a.to_serial = ? ");
    lstParams.add(from_serial);
    lstParams.add(to_serial);
    //
    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("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("addInfor", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("barcode", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //
    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.StockGoodsSerialStripDAO.java

License:Open Source License

public Double getAmountInStockGoodsTotal(ChangePositionDTO changePositionDTO) {
    StringBuilder sql = new StringBuilder();
    sql.append("       SELECT   sum(a.quantity) ");
    sql.append("       FROM   wms_owner.stock_goods_serial_strip a");
    sql.append("       WHERE       a.goods_id = ? ");
    sql.append("            AND a.owner_type = ? ");
    sql.append("            AND a.owner_id = ? ");
    sql.append("            AND a.cust_id = ? ");
    sql.append("            AND a.cell_code = ? ");
    sql.append("            AND a.status = '1' ");
    List lstParams = new ArrayList<>();
    lstParams.add(changePositionDTO.getGoodsId());
    lstParams.add(changePositionDTO.getOwnerType());
    lstParams.add(changePositionDTO.getStockId());
    lstParams.add(changePositionDTO.getCustomerId());
    lstParams.add(changePositionDTO.getCellCodeOld());
    if (!DataUtil.isStringNullOrEmpty(changePositionDTO.getBarcode())) {
        sql.append("            AND a.barcode = ? ");
        lstParams.add(changePositionDTO.getBarcode());
    }//from  w  w  w.  j a  v a 2  s  .c  om
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    for (int idx = 0; idx < lstParams.size(); idx++) {
        query.setParameter(idx, lstParams.get(idx));
    }
    List listResult = query.list();
    BigDecimal result;
    if (listResult != null && listResult.size() > 0) {
        result = (BigDecimal) listResult.get(0);
        return result.doubleValue();
    }
    return 0D;
}

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();
    ///*from   www. ja va 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();
    ////w  w w  .j a v a 2 s . c o  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.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();
    ///* w  w  w .j  a v a  2  s  . c  o 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  a v  a2 s  . c  o  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("  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.StockGoodsSerialStripDAO.java

License:Open Source License

public List<StockGoodsInforDTO> getListStockGoodsByZoneByStaff(
        StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    //Mat hang ko theo serial
    sql.append(" SELECT zoneName,cellCode,goodsType,goodsCode,goodsName,amount,custId,goodsId, ");
    sql.append(" staffId, staffCode, staffName  FROM ");
    sql.append("( SELECT     z.name zoneName,");
    sql.append("            sg.cell_code cellCode,");
    sql.append("           g.goods_type goodsType,");
    sql.append("           g.code goodsCode,");
    sql.append("           g.name goodsName,");
    sql.append("           sg.goods_id goodsId,");
    sql.append("           sg.cust_id custId,");
    sql.append(//from   ww w  . j  av  a  2 s  . c  o  m
            "           SUM (amount) amount,  msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName ");
    sql.append("    FROM   cells c,");
    sql.append("           zones z,");
    sql.append("           goods g,");
    sql.append("           stock_goods sg, map_staff_goods msg ");
    sql.append("   WHERE   c.zone_id = z.zone_id");
    sql.append("           AND g.cust_id = sg.cust_id");
    sql.append("           AND g.goods_id = sg.goods_id");
    sql.append("           AND c.code = sg.cell_code ");
    sql.append("           AND sg.goods_id = msg.goods_id(+)");
    //Tim kiem theo nham 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 sg.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append(" AND sg.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append(" AND sg.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        //sql.append("AND sg.goods_id = ? ");
        //lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
        sql.append("AND sg.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        sql.append("AND g.goods_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //Tim kiem trang thai serial hang hoa - QuyenDM them ngay 05/10/2015
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sg.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kien gan dung theo cellCode
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sg.cell_code LIKE ? ");
        lstParams.add("%" + stockGoodsSerialStripDTO.getCellCode() + "%");
    }
    sql.append(
            " GROUP BY z.name, sg.cell_code,g.goods_type,g.code,g.name,sg.goods_id,sg.cust_id,msg.staff_id, msg.staff_code, msg.staff_name ");
    //Mat hang theo serial don le
    sql.append(" UNION ALL ");
    sql.append("SELECT     z.name zoneName,");
    sql.append("           sgs.cell_code cellCode,");
    sql.append("           g.goods_type goodsType,");
    sql.append("           g.code goodsCode,");
    sql.append("           g.name goodsName,");
    sql.append("           sgs.goods_id goodsId,");
    sql.append("           sgs.cust_id custId,");
    sql.append(
            "           COUNT ( * ) amount, msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName  ");
    sql.append("    FROM   cells c,");
    sql.append("           zones z,");
    sql.append("           goods g,");
    sql.append("           stock_goods_serial sgs, map_staff_goods msg ");
    sql.append("   WHERE   c.zone_id = z.zone_id");
    sql.append("           AND g.cust_id = sgs.cust_id");
    sql.append("           AND g.goods_id = sgs.goods_id");
    sql.append("           AND c.code = sgs.cell_code ");
    sql.append("           AND sgs.goods_id = msg.goods_id(+)");
    //Tim kiem theo nham 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 sgs.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND sgs.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND sgs.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sgs.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        //sql.append("AND sgs.goods_id = ? ");
        //lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
        sql.append("AND sgs.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        sql.append("AND g.goods_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //Tim kien gan dung theo cellCode
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sgs.cell_code LIKE ? ");
        lstParams.add("%" + stockGoodsSerialStripDTO.getCellCode() + "%");
    }
    sql.append(
            " GROUP BY z.name,sgs.cell_code,g.goods_type,g.code,g.name,sgs.goods_id,sgs.cust_id,msg.staff_id, msg.staff_code, msg.staff_name ");
    //
    //Mat hang serial theo dai
    sql.append(" UNION ALL ");
    sql.append("SELECT     z.name zoneName,");
    sql.append("           sgst.cell_code cellCode,");
    sql.append("           g.goods_type goodsType,");
    sql.append("           g.code goodsCode,");
    sql.append("           g.name goodsName,");
    sql.append("           sgst.goods_id goodsId,");
    sql.append("           sgst.cust_id custId,");
    sql.append(
            "           SUM (sgst.quantity) amount, msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName ");
    sql.append("    FROM   cells c,");
    sql.append("           zones z,");
    sql.append("           goods g,");
    sql.append("           stock_goods_serial_strip sgst, map_staff_goods msg ");
    sql.append("   WHERE   c.zone_id = z.zone_id");
    sql.append("           AND g.cust_id = sgst.cust_id");
    sql.append("           AND g.goods_id = sgst.goods_id");
    sql.append("           AND c.code = sgst.cell_code ");
    sql.append("           AND sgst.goods_id = msg.goods_id(+)");
    //Tim kiem theo nham 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 sgst.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang    
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND sgst.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND sgst.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sgst.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        //sql.append("AND sgst.goods_id = ? ");
        //lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
        sql.append("AND sgst.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        sql.append("AND g.goods_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //Tim kien gan dung theo cellCode
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sgst.cell_code LIKE ? ");
        lstParams.add("%" + stockGoodsSerialStripDTO.getCellCode() + "%");
    }
    sql.append(
            " GROUP BY z.name,sgst.cell_code,g.goods_type,g.code,g.name,sgst.goods_id,sgst.cust_id,msg.staff_id, msg.staff_code, msg.staff_name ");
    sql.append(" ) ");
    sql.append(" WHERE amount > 0 ");
    sql.append(" ORDER BY zoneName,goodsType,cellCode,goodsCode ");

    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class));

    query.addScalar("zoneName", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("custId", new StringType());
    query.addScalar("staffId", new StringType());
    query.addScalar("staffCode", new StringType());
    query.addScalar("staffName", new StringType());
    //
    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.StockGoodsSerialStripDAO.java

License:Open Source License

public List<StockGoodsInforDTO> getListStockGoodsByZoneAll(StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    //Mat hang ko theo serial
    sql.append(" SELECT zoneName,cellCode,goodsType,goodsCode,goodsName,amount,custId,goodsId ");
    sql.append("  FROM ( SELECT     z.name zoneName,");
    sql.append("            sg.cell_code cellCode,");
    sql.append("           g.goods_type goodsType,");
    sql.append("           g.code goodsCode,");
    sql.append("           g.name goodsName,");
    sql.append("           sg.goods_id goodsId,");
    sql.append("           sg.cust_id custId,");
    sql.append("           SUM (amount) amount ");
    sql.append("    FROM   cells c,");
    sql.append("           zones z,");
    sql.append("           goods g,");
    sql.append("           stock_goods sg ");
    sql.append("   WHERE   c.zone_id = z.zone_id");
    sql.append("           AND g.cust_id = sg.cust_id");
    sql.append("           AND g.goods_id = sg.goods_id");
    sql.append("           AND c.code = sg.cell_code ");
    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) {
        sql.append(" AND sg.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }/*w w  w .ja v a2  s .c  o m*/
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append(" AND sg.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append(" AND sg.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        sql.append("AND sg.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        sql.append("AND g.goods_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //Tim kiem trang thai serial hang hoa - QuyenDM them ngay 05/10/2015
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sg.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kien gan dung theo cellCode
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sg.cell_code LIKE ? ");
        lstParams.add("%" + stockGoodsSerialStripDTO.getCellCode() + "%");
    }
    sql.append(" GROUP BY z.name, sg.cell_code,g.goods_type,g.code,g.name,sg.goods_id,sg.cust_id ");
    //Mat hang theo serial don le
    sql.append(" UNION ALL ");
    sql.append("SELECT     z.name zoneName,");
    sql.append("           sgs.cell_code cellCode,");
    sql.append("           g.goods_type goodsType,");
    sql.append("           g.code goodsCode,");
    sql.append("           g.name goodsName,");
    sql.append("           sgs.goods_id goodsId,");
    sql.append("           sgs.cust_id custId,");
    sql.append("           COUNT ( * ) amount  ");
    sql.append("    FROM   cells c,");
    sql.append("           zones z,");
    sql.append("           goods g,");
    sql.append("           stock_goods_serial sgs ");
    sql.append("   WHERE   c.zone_id = z.zone_id");
    sql.append("           AND g.cust_id = sgs.cust_id");
    sql.append("           AND g.goods_id = sgs.goods_id");
    sql.append("           AND c.code = sgs.cell_code ");
    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) {
        sql.append("AND sgs.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND sgs.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND sgs.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sgs.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        //sql.append("AND sgs.goods_id = ? ");
        //lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
        sql.append("AND sgs.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        sql.append("AND g.goods_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //Tim kien gan dung theo cellCode
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sgs.cell_code LIKE ? ");
        lstParams.add("%" + stockGoodsSerialStripDTO.getCellCode() + "%");
    }
    sql.append(" GROUP BY z.name,sgs.cell_code,g.goods_type,g.code,g.name,sgs.goods_id,sgs.cust_id ");
    //
    //Mat hang serial theo dai
    sql.append(" UNION ALL ");
    sql.append("SELECT     z.name zoneName,");
    sql.append("           sgst.cell_code cellCode,");
    sql.append("           g.goods_type goodsType,");
    sql.append("           g.code goodsCode,");
    sql.append("           g.name goodsName,");
    sql.append("           sgst.goods_id goodsId,");
    sql.append("           sgst.cust_id custId,");
    sql.append("           SUM (sgst.quantity) amount ");
    sql.append("    FROM   cells c,");
    sql.append("           zones z,");
    sql.append("           goods g,");
    sql.append("           stock_goods_serial_strip sgst ");
    sql.append("   WHERE   c.zone_id = z.zone_id");
    sql.append("           AND g.cust_id = sgst.cust_id");
    sql.append("           AND g.goods_id = sgst.goods_id");
    sql.append("           AND c.code = sgst.cell_code ");

    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) {
        sql.append("AND sgst.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang    
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND sgst.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND sgst.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sgst.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        //sql.append("AND sgst.goods_id = ? ");
        //lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
        sql.append("AND sgst.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        sql.append("AND g.goods_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //Tim kien gan dung theo cellCode
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sgst.cell_code LIKE ? ");
        lstParams.add("%" + stockGoodsSerialStripDTO.getCellCode() + "%");
    }
    sql.append(" GROUP BY z.name,sgst.cell_code,g.goods_type,g.code,g.name,sgst.goods_id,sgst.cust_id ");
    sql.append(" ) ");
    sql.append(" WHERE amount > 0 ");
    sql.append(" ORDER BY zoneName,goodsType,cellCode,goodsCode ");

    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class));

    query.addScalar("zoneName", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("custId", new StringType());
    //
    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.StockGoodsSerialStripDAO.java

License:Open Source License

public List<StockGoodsSerialStripDTO> getListStockGoodsSerialBySerial(
        StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    List<StockGoodsSerialStripDTO> lstStockGoodsSerialStrips = null;
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    ///*from   w  w w .ja  v a2  s  .c o 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.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 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 = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
    }
    //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());
    }

    //
    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 WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id ");
    lstParams.add(stockGoodsSerialStripDTO.getQuantity());
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsGroup())) {
        sql.append("AND g.goods_group = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsGroup());
    }
    sql.append(
            " ORDER BY a.cust_id,a.owner_id,a.owner_type,a.cell_code, g.goods_type,g.code,a.state,a.status ");

    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialStripDTO.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());
    //
    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.StockGoodsSerialStripDAO.java

License:Open Source License

public List getListSerilStripErrorImportRevoke(String stockTransId) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();

    sql.append("SELECT   ");
    sql.append("         a.ora_err_mesg$ addInfor,");
    sql.append("         a.cust_id custId,");
    sql.append("         a.owner_id ownerId,");
    sql.append("         a.owner_type ownerType,");
    sql.append("         a.goods_id goodsId,");
    sql.append("         a.goods_state goodsState,");
    sql.append("         a.status status,");
    sql.append("         a.from_serial fromSerial,");
    sql.append("         a.to_serial toSerial,");
    sql.append("         a.barcode barcode,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         g.code goodsCode,");
    sql.append("         g.name goodsName ");
    sql.append("  FROM   err$_stock_goods_serial_strip a, goods g ");
    sql.append("  WHERE   a.goods_id = g.goods_id ");
    ///*from   w  w w.j av  a  2s  . c om*/
    if (!DataUtil.isStringNullOrEmpty(stockTransId)) {
        sql.append("  AND a.import_stock_trans_id = ? ");
        lstParams.add(stockTransId);
    }
    //
    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("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("addInfor", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("barcode", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    return query.list();
}