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.GoodsDAO.java

License:Open Source License

public List<GoodsDTO> getListGoodsWithCustId(String custId) {
    List<GoodsDTO> lstGoodsDTO = new ArrayList();
    List lstParams = new ArrayList();
    //        //  w  w w  .  j ava 2 s .c  om
    if (DataUtil.isStringNullOrEmpty(custId)) {
        return lstGoodsDTO;
    }
    StringBuilder sql = new StringBuilder();
    //        
    sql.append("SELECT   a.goods_id goodsId, a.code code, a.name name, ");
    sql.append("         a.goods_type goodsType, a.unit_type unitType, ");
    sql.append("         a.is_serial isSerial, a.is_serial_strip isSerialStrip  ");
    sql.append("    FROM   goods a ");
    sql.append("    WHERE   a.cust_id = ? AND a.status = '1' ");
    //ChuDV add : 11/12/2015 -- 
    sql.append(
            "    AND EXISTS (SELECT   1 FROM   stock_goods_total WHERE cust_id = ? AND goods_id = a.goods_id AND (amount != 0 OR amount_issue != 0))");

    //Tim kiem theo khach hang
    lstParams.add(custId.trim());
    lstParams.add(custId.trim());
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(GoodsDTO.class));
    query.addScalar("goodsId", new StringType());
    query.addScalar("code", new StringType());
    query.addScalar("name", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("unitType", new StringType());
    query.addScalar("isSerial", new StringType());
    query.addScalar("isSerialStrip", new StringType());

    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //Tra ve danh sach hang hoa
    return query.list();
}

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

License:Open Source License

/**
 * NgocND6 get list goods serial by cust id
 * @param custId/*from  ww  w. ja v  a2 s.  c  o m*/
 * @param isSerial
 * @param isSerialStrip
 * @return
 */
public List<GoodsDTO> getListGoodsSerialByCustId(String custId, String isSerial, String isSerialStrip) {
    List<GoodsDTO> lstGoodsDTO = new ArrayList();
    List lstParams = new ArrayList();
    if (DataUtil.isStringNullOrEmpty(custId)) {
        return lstGoodsDTO;
    }
    StringBuilder sql = new StringBuilder();
    //        
    sql.append("SELECT   a.goods_id goodsId, a.code code, a.name name, ");
    sql.append("         a.unit_type unitType, ");
    sql.append("         a.is_serial isSerial, a.is_serial_strip isSerialStrip, a.goods_type goodsType  ");
    sql.append("    FROM   goods a ");
    sql.append("    WHERE   a.cust_id = ? AND a.status = '1' ");
    lstParams.add(custId.trim());
    if (!DataUtil.isStringNullOrEmpty(isSerial)) {
        sql.append("     AND a.is_serial = ? ");
        lstParams.add(isSerial);
    }
    if (!DataUtil.isStringNullOrEmpty(isSerialStrip)) {
        sql.append("     AND a.is_serial_strip = ? ");
        lstParams.add(isSerialStrip);
    }
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(GoodsDTO.class));
    query.addScalar("goodsId", new StringType());
    query.addScalar("code", new StringType());
    query.addScalar("name", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("unitType", new StringType());
    query.addScalar("isSerial", new StringType());
    query.addScalar("isSerialStrip", new StringType());

    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //Tra ve danh sach hang hoa
    return query.list();
}

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

License:Open Source License

public List<InventoryResultDTO> getLstCheckedResult(String inventoryActionId) {
    List<InventoryResultDTO> lstInvenResult = new ArrayList<>();
    StringBuffer sql = new StringBuffer();
    //create sql/* ww w  . j ava 2 s .  c o  m*/
    sql.append(" SELECT  ");
    sql.append(" NVL(a.inventory_result_id, b.inventory_result_id) inventoryResultId ");
    sql.append(" ,NVL(a.inventory_action_id, b.inventory_action_id) inventoryActionId ");
    sql.append(" ,NVL(a.goods_code, b.goods_code) goodsCode ");
    sql.append(" ,NVL(a.goods_name, b.goods_name) goodsName ");
    sql.append(" ,NVL(a.goods_id, b.goods_id) goodsId ");
    sql.append(" ,NVL(a.unit_id, b.unit_id) unitId  ");
    sql.append(" ,NVL(a.unit_name, b.unit_name) unitName ");
    sql.append(" ,NVL(a.cell_id, b.cell_id) cellId ");
    sql.append(" ,NVL(a.cell_code, b.cell_code) cellCode ");
    sql.append(" ,NVL(a.barcode, b.barcode) barcode ");
    sql.append(" ,NVL(a.from_serial, b.from_serial) fromSerial ");
    sql.append("  ,NVL(a.to_serial, b.to_serial) toSerial ");
    sql.append(" ,NVL(b.amount_inventory,0) amountInventory  ");
    sql.append(" ,NVL(a.amount,0) amount ");
    sql.append(" ,NVL(a.note, b.note) note  ");
    sql.append(" ,NVL(b.amount_inventory,0) - NVL(a.amount,0) as amountFalse  ");
    sql.append(" ,NVL(a.type, b.type) type  ");
    sql.append(" FROM (select * from inventory_result where inventory_action_id = ? and type = '1') a  ");
    sql.append(" full join (select * from inventory_result where inventory_action_id = ? and type = '2') b ");
    sql.append(" on NVL(a.goods_code, 'ABC') = NVL(b.goods_code , 'ABC') ");
    sql.append(" and   NVL(a.cell_code, 'ABC')= NVL(b.cell_code, 'ABC') ");
    sql.append(" and   NVL(a.barcode, 'ABC')= NVL(b.barcode , 'ABC') ");
    sql.append(" and   NVL(a.from_serial, 'ABC')= NVL(b.from_serial , 'ABC') ");
    sql.append(" and   NVL(a.to_serial, 'ABC')= NVL(b.to_serial, 'ABC')  ");

    //SET PARAMETTER
    SQLQuery query = getSession().createSQLQuery(sql.toString());

    query.setResultTransformer(Transformers.aliasToBean(InventoryResultDTO.class));
    query.addScalar("inventoryResultId", new StringType());
    query.addScalar("inventoryActionId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("unitId", new StringType());
    query.addScalar("unitName", new StringType());
    query.addScalar("cellId", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("barcode", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("amountInventory", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("note", new StringType());
    query.addScalar("amountFalse", new StringType());
    query.addScalar("type", new StringType());

    int inventoryId = 0;
    try {
        inventoryId = Integer.parseInt(inventoryActionId);
    } catch (Exception e) {
    }
    //SET PARAMETER
    query.setParameter(0, inventoryId);
    query.setParameter(1, inventoryId);
    //
    lstInvenResult = query.list();
    //List list;
    //        if(lstInvenResult == null || lstInvenResult.size()==0)
    //        {
    //            return lstInvenResult;
    //        }
    return lstInvenResult;
}

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

License:Open Source License

public List<InventoryResultDTO> getInventoryResultByDisplayField(String inventoryActionId,
        List<String> lstShowField) {
    List<InventoryResultDTO> lstInvenResult = new ArrayList<>();
    StringBuffer sql = new StringBuffer();
    String sqlStr = "";
    ///* ww  w  .j ava 2s .c  o m*/
    boolean isShowAll = false;
    //create sql
    //init show file check
    boolean isShowLoaction = false;
    boolean isShowGoodsInfo = false;
    boolean isShowBincode = false;
    boolean isShowAmount = false;
    boolean isShowSerial = false;
    if (lstShowField != null && lstShowField.size() > 0) {
        for (String z : lstShowField) {
            switch (z) {
            case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.LOCATION:
                isShowLoaction = true;
                break;
            case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.GOODS_INFO:
                isShowGoodsInfo = true;
                break;
            case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.BINCODE_BARCODE:
                isShowBincode = true;
                break;
            case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.AMOUNT:
                isShowAmount = true;
                break;
            case ParamUtils.INVENTORY_EXPORT_SHOW_FIELD.SERIAL:
                isShowSerial = true;
                break;
            }
        }
    } else {
        isShowAll = true;
    }

    if (isShowAll) {
        //CREATE SQL
        sql.append(" SELECT distinct from ");
        sql.append(" a.goods_code goodsCode,");
        sql.append(" a.goods_name goodsName,");
        sql.append(" a.cell_code cellCode,");
        sql.append(" a.barcode barcode,");
        sql.append(" a.from_serial fromSerial,");
        sql.append(" a.to_serial toSerial, ");
        sql.append(" sum (a.amount) as amount ");
        sql.append(" from  inventory_result a ");
        sql.append(
                " where inventory_action_id = ? and a.type = ? group by goods_code,goods_name,cell_code,barcode,from_serial,to_serial ");
        sql.append(" order by  goods_code ");

    } else {
        boolean isHaveCondition = false;
        //
        sql.append(" SELECT distinct ");
        if (isShowGoodsInfo) {
            if (isHaveCondition) {
                sql.append(" ,a.goods_code goodsCode ");
                sql.append(" ,a.goods_name goodsName ");
            } else {
                sql.append(" a.goods_code goodsCode, ");
                sql.append(" a.goods_name goodsName ");
            }
            isHaveCondition = true;
        }
        if (isShowLoaction) {
            if (isHaveCondition) {
                sql.append(" ,a.cell_code cellCode ");
            } else {
                sql.append(" a.cell_code cellCode ");
            }
            isHaveCondition = true;
        }
        if (isShowBincode) {
            if (isHaveCondition) {
                sql.append(" ,a.barcode barcode ");
            } else {
                sql.append(" a.barcode barcode ");
            }
            isHaveCondition = true;
        }
        if (isShowSerial) {
            if (isHaveCondition) {
                sql.append(" ,a.from_serial fromSerial ");
                sql.append(" ,a.to_serial toSerial  ");
            } else {
                sql.append(" a.from_serial fromSerial, ");
                sql.append(" a.to_serial toSerial ");
            }
            isHaveCondition = true;
        }

        if (isShowAmount) {
            if (isHaveCondition) {
                sql.append(" ,sum (a.amount) as amount ");
            } else {
                sql.append(" sum (a.amount) as amount ");
            }
            isHaveCondition = true;
        }

        sql.append(" from  inventory_result a ");
        sql.append(" where inventory_action_id = ? and a.type = ? group by ");

        if (isShowGoodsInfo) {
            sql.append(" goods_code,goods_name,");
        }
        if (isShowLoaction) {
            sql.append(" cell_code,");
        }
        if (isShowBincode) {
            sql.append(" barcode,");
        }
        if (isShowSerial) {
            sql.append(" from_serial,to_serial,");
        }
        if (isShowAmount) {
            sql.append(" amount,");
        }

        sqlStr = sql.substring(0, sql.length() - 1);
        if (isShowGoodsInfo) {
            sqlStr += " order by  goods_code ";
        }
    }

    //SET PARAMETTER
    SQLQuery query = getSession().createSQLQuery(sqlStr);

    query.setResultTransformer(Transformers.aliasToBean(InventoryResultDTO.class));
    if (isShowAll) {
        query.addScalar("goodsCode", new StringType());
        query.addScalar("goodsName", new StringType());
        query.addScalar("cellCode", new StringType());
        query.addScalar("barcode", new StringType());
        query.addScalar("fromSerial", new StringType());
        query.addScalar("toSerial", new StringType());
        query.addScalar("amount", new StringType());
    } else {
        if (isShowGoodsInfo) {
            query.addScalar("goodsCode", new StringType());
            query.addScalar("goodsName", new StringType());
        }
        if (isShowLoaction) {
            query.addScalar("cellCode", new StringType());
        }
        if (isShowBincode) {
            query.addScalar("barcode", new StringType());
        }
        if (isShowSerial) {
            query.addScalar("fromSerial", new StringType());
            query.addScalar("toSerial", new StringType());
        }
        if (isShowAmount) {
            query.addScalar("amount", new StringType());
        }
    }

    int inventoryId = 0;
    try {
        inventoryId = Integer.parseInt(inventoryActionId);
    } catch (Exception e) {
    }
    //SET PARAMETER
    query.setParameter(0, inventoryId);
    query.setParameter(1, "1");
    //
    lstInvenResult = query.list();

    return lstInvenResult;
}

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

License:Open Source License

public List<MapStaffGoodsDTO> getListStaffByGoods(String codeList, String custId) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    sql.append(" SELECT  DISTINCT m.staff_id staffId,");
    sql.append("         m.staff_code staffCode,");
    sql.append("         m.staff_name staffName,");
    sql.append("         m.staff_type staffType,");
    sql.append("         m.staff_email staffEmail");
    sql.append("  FROM   map_staff_goods m, goods g");
    sql.append("  WHERE m.goods_id = g.goods_id ");
    sql.append("  AND  g.status = 1");
    sql.append("  AND  m.staff_email IS NOT NULL");
    sql.append("  AND  g.cust_id = ?");
    lstParams.add(custId);/*w w w.j  a v  a 2 s . c  o  m*/
    sql.append("  AND m.goods_code IN (:idx)");

    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(MapStaffGoodsDTO.class));
    query.addScalar("staffId", new StringType());
    query.addScalar("staffCode", new StringType());
    query.addScalar("staffName", new StringType());
    query.addScalar("staffType", new StringType());
    query.addScalar("staffEmail", new StringType());

    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings().splitToList(codeList);
    query.setParameterList("idx", lst);
    return query.list();
}

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

License:Open Source License

public String getListCellCode(PickingList pickingList, PickingListGoods pickingListGoods) {
    String listCellCode = "";
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    //Mat hang khong co serial
    if (!pickingListGoods.getGoodsIsSerial().equals(Constants.IS_SERIAL)) {
        sql.append("SELECT   cell_code || ': ' || amount ");
        sql.append("FROM   (SELECT   change_date, ");
        sql.append("     cell_code, ");
        sql.append("     amount, ");
        sql.append("     SUM (amount) OVER (PARTITION BY 1 ORDER BY change_date, cell_code) ");
        sql.append("         running_total ");
        sql.append("FROM   (  SELECT   1, ");
        sql.append("       change_date, ");
        sql.append("       cell_code, ");
        sql.append("       SUM (amount) amount ");
        sql.append("FROM stock_goods WHERE 1=1 ");
        sql.append("     AND cust_id = ? ");
        sql.append("     AND owner_id = ? ");
        sql.append("     AND owner_type = ? ");
        sql.append("     AND goods_id = ? ");
        sql.append("     AND goods_state = ? AND status = 1  ");
        //bo sung them so luong lon hon 0
        sql.append("     AND amount > 0  ");
        ///*from w w  w.  j  av a  2s  .c  om*/
        sql.append("GROUP BY   change_date, cell_code ");
        sql.append("ORDER BY   change_date, cell_code)) ");
        sql.append("WHERE   running_total < ? + amount ");
        lstParams.add(pickingList.getCustId());
        lstParams.add(pickingList.getStockId());
        lstParams.add(Constants.STOCK_OWNER);
        lstParams.add(pickingListGoods.getGoodsId());
        lstParams.add(pickingListGoods.getGoodsState());
        lstParams.add(pickingListGoods.getAmount());
    } //Mat hang quan ly serial theo dai
    else if (pickingListGoods.getGoodsIsSerialStrip().equals(Constants.IS_SERIAL_STRIP)) {
        if (DataUtil.isStringNullOrEmpty(pickingListGoods.getFromSerial())) {
            //ney hang theo dai nhung thong tin serial == null
            sql.append("SELECT   cell_code || ': ' || amount ");
            sql.append("  FROM   (SELECT   import_date, ");
            sql.append("                   cell_code, ");
            sql.append("                   amount, ");
            sql.append(
                    "                   SUM (amount) OVER (PARTITION BY 1 ORDER BY import_date, cell_code) ");//ThienNg1 14/08/2015 bo sung cell_code
            sql.append("                       running_total ");
            sql.append("            FROM   (  SELECT   1, ");
            sql.append("                               import_date, ");
            sql.append("                               cell_code, ");
            sql.append("                               sum (quantity) amount ");
            sql.append("                        FROM   stock_goods_serial_strip ");
            sql.append("                       WHERE   cust_id = ? AND owner_id = ? AND owner_type = ? ");
            sql.append("                               AND goods_id = ? AND goods_state = ? AND status = 1 ");//ThienNg1 14/08/2015 bo sung dieu kien status =1
            sql.append("                    GROUP BY   import_date, cell_code ");
            sql.append("                    ORDER BY   import_date, cell_code)) ");
            sql.append(" WHERE   running_total < ? + amount");
            lstParams.add(pickingList.getCustId());
            lstParams.add(pickingList.getStockId());
            lstParams.add(Constants.STOCK_OWNER);
            lstParams.add(pickingListGoods.getGoodsId());
            lstParams.add(pickingListGoods.getGoodsState());
            lstParams.add(pickingListGoods.getAmount());
        } else {// hang theo dai co day du thong tin serial
            sql.append("SELECT   cell_code || ': ' || ?");
            sql.append("  FROM   (SELECT   cell_code");
            sql.append("            FROM   (  SELECT   1, cell_code");
            sql.append("                        FROM   stock_goods_serial_strip");
            sql.append("                       WHERE       cust_id = ?");
            sql.append("                               AND owner_id = ?");
            sql.append("                               AND owner_type = ?");
            sql.append("                               AND goods_id = ?");
            sql.append("                               AND goods_state = ?");
            sql.append("                               AND status = 1");
            sql.append("                               AND from_serial <=");
            sql.append("                                      TO_NUMBER (?)");
            sql.append("                               AND to_serial >= TO_NUMBER (?)");
            sql.append("                    GROUP BY   cell_code");
            sql.append("                    ORDER BY   cell_code))");

            lstParams.add(pickingListGoods.getAmount());
            lstParams.add(pickingList.getCustId());
            lstParams.add(pickingList.getStockId());
            lstParams.add(Constants.STOCK_OWNER);
            lstParams.add(pickingListGoods.getGoodsId());
            lstParams.add(pickingListGoods.getGoodsState());
            lstParams.add(pickingListGoods.getToSerial());
            lstParams.add(pickingListGoods.getFromSerial());
        }
    } else //Mat hang quan ly serial don le
    {
        List<String> lstSerial = new ArrayList();
        if (!DataUtil.isStringNullOrEmpty(pickingListGoods.getFromSerial())) {//Neu thong tin hang hoa co thong tin serial
            if (pickingListGoods.getFromSerial().equals(pickingListGoods.getToSerial())) {//Neu from_serial trung to_serial
                sql.append("SELECT   cell_code || ': ' || amount ");
                sql.append("  FROM   (SELECT   import_date, ");
                sql.append("                   cell_code, ");
                sql.append("                   amount, ");
                sql.append(
                        "                   SUM (amount) OVER (PARTITION BY 1 ORDER BY import_date, cell_code) ");//ThienNg1 14/08/2015 bo sung cell_code
                sql.append("                       running_total ");
                sql.append("            FROM   (  SELECT   1, ");
                sql.append("                               import_date, ");
                sql.append("                               cell_code, ");
                sql.append("                               COUNT ( * ) amount ");
                sql.append("                        FROM   stock_goods_serial ");
                sql.append("                       WHERE   cust_id = ? AND owner_id = ? AND owner_type = ? ");
                sql.append(
                        "                               AND goods_id = ? AND goods_state = ? AND status = 1 ");//ThienNg1 14/08/2015 bo sung dieu kien status =1
                //addby ThienNg1 - them dieu kien neu hang co thong tin serial
                sql.append("                               AND serial in (?) ");
                //
                sql.append("                    GROUP BY   import_date, cell_code ");
                sql.append("                    ORDER BY   import_date, cell_code)) ");
                sql.append(" WHERE   running_total < ? + amount");
                lstParams.add(pickingList.getCustId());
                lstParams.add(pickingList.getStockId());
                lstParams.add(Constants.STOCK_OWNER);
                lstParams.add(pickingListGoods.getGoodsId());
                lstParams.add(pickingListGoods.getGoodsState());
                //them param serial
                lstParams.add(pickingListGoods.getFromSerial());
                //
                lstParams.add(pickingListGoods.getAmount());
            } else {//neu from_serial khac to_serial
                int sizeSearchIn = 995;
                String serial;
                String prefixSerial = "";
                String suffixFromSerial = "";
                String suffixToSerial = "";
                String fromSerial = pickingListGoods.getFromSerial();
                String toSerial = pickingListGoods.getToSerial();
                //Serial chua ky tu
                if (!StringUtils.isInteger(fromSerial) || !StringUtils.isInteger(toSerial)) {
                    serial = fromSerial;
                    lstSerial.add(serial);
                } else { //Serial dang so 
                    //Kiem tra do dai serial kneu >19 thi cat do kieu Long chi co do dai toi da 19
                    int iLengthSuffixSerial = 0;
                    if (fromSerial.length() > Constants.SERIAL_LIMIT) {
                        prefixSerial = fromSerial.substring(0, fromSerial.length() - Constants.SERIAL_LIMIT);
                        suffixFromSerial = fromSerial.substring(fromSerial.length() - Constants.SERIAL_LIMIT,
                                fromSerial.length());
                        suffixToSerial = toSerial.substring(toSerial.length() - Constants.SERIAL_LIMIT,
                                toSerial.length());
                        iLengthSuffixSerial = suffixFromSerial.length();
                    } else {
                        suffixFromSerial = fromSerial;
                        suffixToSerial = toSerial;
                        iLengthSuffixSerial = fromSerial.length();
                    }
                    //
                    String tmpSuffixSerial;
                    for (Long lSerial = Long.parseLong(suffixFromSerial); lSerial <= Long
                            .parseLong(suffixToSerial); lSerial++) {
                        tmpSuffixSerial = DataUtil.lPad(lSerial.toString(), "0", iLengthSuffixSerial);
                        serial = prefixSerial + tmpSuffixSerial;
                        lstSerial.add(serial);
                    }
                }
                int numberSheet = 0;
                if (!DataUtil.isListNullOrEmpty(lstSerial)) {
                    if (lstSerial.size() % sizeSearchIn == 0) {
                        numberSheet = lstSerial.size() / sizeSearchIn;
                    } else {
                        numberSheet = lstSerial.size() / sizeSearchIn + 1;
                    }
                }
                sql.append("SELECT   cell_code || ': ' || quantity ");
                sql.append("  FROM   (SELECT   ");
                sql.append("                   cell_code,");
                sql.append("                   quantity,");
                sql.append("                   SUM (quantity)");
                sql.append("                       OVER (PARTITION BY 1 ORDER BY  cell_code)");
                sql.append("                       running_total");
                sql.append("            FROM   (  SELECT   1,       ");
                sql.append("                               cell_code,");
                sql.append("                               SUM (amount) quantity");
                sql.append("                        FROM   ( ");

                for (int index = 0; index < numberSheet; index++) {
                    //moi vong truy van 1 luot
                    if (index == numberSheet - 1) {
                        StringBuilder sb = new StringBuilder();
                        for (int i = sizeSearchIn * index; i < lstSerial.size(); i++) {
                            sb.append(",");
                            sb.append(lstSerial.get(i));
                        }
                        String serialSearch = sb.toString().replaceFirst(",", "");
                        if (numberSheet != 1) {
                            sql.append("  UNION ALL ");
                        }
                        sql.append("   SELECT   1,");
                        sql.append("   cell_code,");
                        sql.append("    COUNT ( * ) amount");
                        sql.append("   FROM   stock_goods_serial");
                        sql.append("      WHERE       cust_id = ?");
                        sql.append("            AND owner_id = ?");
                        sql.append("          AND owner_type = ?");
                        sql.append("          AND goods_id = ?");
                        sql.append("          AND goods_state = ?");
                        sql.append("           AND status = 1");
                        sql.append("            AND serial IN (" + serialSearch + ") ");
                        sql.append("   GROUP BY    cell_code)");
                        //
                        lstParams.add(pickingList.getCustId());
                        lstParams.add(pickingList.getStockId());
                        lstParams.add(Constants.STOCK_OWNER);
                        lstParams.add(pickingListGoods.getGoodsId());
                        lstParams.add(pickingListGoods.getGoodsState());
                    } else {
                        StringBuilder sb = new StringBuilder();
                        for (int i = sizeSearchIn * index; i < sizeSearchIn * (index + 1); i++) {
                            sb.append(",");
                            sb.append(lstSerial.get(i));
                        }
                        String serialSearch = sb.toString().replaceFirst(",", "");
                        if (index != 0) {
                            sql.append("  UNION ALL ");
                        }
                        sql.append("   SELECT   1,     ");
                        sql.append("    cell_code,");
                        sql.append("   COUNT ( * ) amount");
                        sql.append("   FROM   stock_goods_serial");
                        sql.append("   WHERE       cust_id = ?");
                        sql.append("   AND owner_id = ?");
                        sql.append("   AND owner_type = ?");
                        sql.append("   AND goods_id = ?");
                        sql.append("   AND goods_state = ?");
                        sql.append("   AND status = 1");
                        sql.append("            AND serial IN (" + serialSearch + ") ");
                        sql.append("   GROUP BY    cell_code ");
                        //
                        lstParams.add(pickingList.getCustId());
                        lstParams.add(pickingList.getStockId());
                        lstParams.add(Constants.STOCK_OWNER);
                        lstParams.add(pickingListGoods.getGoodsId());
                        lstParams.add(pickingListGoods.getGoodsState());
                    }
                }
                sql.append("           GROUP BY    cell_code))");
                sql.append(" WHERE   running_total < ? + quantity");
                lstParams.add(pickingListGoods.getAmount());
            }
        } else {//Neu thong tin hang hoa khong co thong tin serial
            //
            sql.append("SELECT   cell_code || ': ' || amount ");
            sql.append("  FROM   (SELECT   import_date, ");
            sql.append("                   cell_code, ");
            sql.append("                   amount, ");
            sql.append(
                    "                   SUM (amount) OVER (PARTITION BY 1 ORDER BY import_date, cell_code) ");//ThienNg1 14/08/2015 bo sung cell_code
            sql.append("                       running_total ");
            sql.append("            FROM   (  SELECT   1, ");
            sql.append("                               import_date, ");
            sql.append("                               cell_code, ");
            sql.append("                               COUNT ( * ) amount ");
            sql.append("                        FROM   stock_goods_serial ");
            sql.append("                       WHERE   cust_id = ? AND owner_id = ? AND owner_type = ? ");
            sql.append("                               AND goods_id = ? AND goods_state = ? AND status = 1 ");//ThienNg1 14/08/2015 bo sung dieu kien status =1
            sql.append("                    GROUP BY   import_date, cell_code ");
            sql.append("                    ORDER BY   import_date, cell_code)) ");
            sql.append(" WHERE   running_total < ? + amount");
            lstParams.add(pickingList.getCustId());
            lstParams.add(pickingList.getStockId());
            lstParams.add(Constants.STOCK_OWNER);

            lstParams.add(pickingListGoods.getGoodsId());
            lstParams.add(pickingListGoods.getGoodsState());
            lstParams.add(pickingListGoods.getAmount());
        }
    }
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    List<List> lstList = query.list();
    //List list;
    //
    if (lstList != null && lstList.size() > 0) {
        for (int j = 0; j < lstList.size(); j++) {
            if (listCellCode.equals("")) {
                listCellCode = lstList.get(j) + "";
            } else {
                listCellCode = listCellCode + "\r\n" + lstList.get(j);
                //                    if (j == lstList.size() - 1) {
                //                        listCellCode = listCellCode + lstList.get(j);
                //                    } else {
                //                        listCellCode = listCellCode + "\r\n" + lstList.get(j);
                //                    }
            }
        }
    }
    return listCellCode;
}

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

License:Open Source License

public List<ProposedReceipt> getListProposedReceiptByCondition(ProposedReceiptDTO proposedReceiptDTO) {
    List<ProposedReceipt> lstProposedReceiptDTOs = new ArrayList<>();
    try {/*from ww w.j a  v a 2 s  .  co m*/
        StringBuilder sql = new StringBuilder();
        List params = new ArrayList();
        sql.append(" SELECT  a.proposed_receipt_id proposedReceiptId,");
        sql.append("         a.shipment_code shipmentCode,");
        sql.append("         a.proposed_receipt_code proposedReceiptCode,");
        sql.append("         a.stock_id stockId,");
        sql.append("         a.customer_id customerId,");
        sql.append("         a.number_invoice numberInvoice,");
        sql.append("         a.expected_date expectedDate,");
        sql.append("         a.create_date createDate,");
        sql.append("         a.implementer_name implementerName,");
        sql.append("         a.note note,");
        sql.append("         a.contact_person contactPerson,");
        sql.append("         a.status status,");
        sql.append("         a.receive_location receiveLocation,");
        sql.append("         a.attach_file_list attachFileList,");
        sql.append("         a.recent_history_id recentHistoryId");
        sql.append("    FROM   proposed_receipt a");
        sql.append("    WHERE  1=1");

        //Tim kiem theo mat hang
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getGoodsName())) {
            sql.append(" AND a.proposed_receipt_id IN (SELECT DISTINCT  c.request_id ");
            sql.append("                                    FROM   goods_handover c");
            sql.append("                                    WHERE   lower(c.goods_name) LIKE lower(?) ");
            sql.append("                                        AND c.type = '1')");
            params.add("%" + proposedReceiptDTO.getGoodsName() + "%");
            //
        }
        //Tim kiem theo nguoi tao
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getImplementerName())) {
            sql.append(" AND a.implementer_name = ?");
            params.add(proposedReceiptDTO.getImplementerName());
        }
        //Tim kiem theo kho
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getStockId())) {
            sql.append(" AND a.stock_id = ?");
            params.add(proposedReceiptDTO.getStockId());
        }
        //Tim kiem theo khach hang
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCustomerId())) {
            sql.append(" AND a.customer_id = ?");
            params.add(proposedReceiptDTO.getCustomerId());
        }
        //Tim kiem theo trang thai
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getStatus())) {
            sql.append(" AND a.status = ?");
            params.add(proposedReceiptDTO.getStatus());
        }
        //Tim kiem theo ngay du kien nhap tu ngay
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getExpectedDate())) {
            sql.append(" AND a.expected_date >= to_date(?,'dd/mm/yyyy')");
            params.add(proposedReceiptDTO.getExpectedDate());
        }
        //Tim kiem theo ngay du kien nhap den ngay
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getExpectedDateToDate())) {
            sql.append(" AND a.expected_date <= to_date(?,'dd/mm/yyyy') + 1");
            params.add(proposedReceiptDTO.getExpectedDateToDate());
        }
        //Tim kiem theo ngay tao tu ngay
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCreateDate())) {
            sql.append(" AND a.create_date >= to_date(?,'dd/mm/yyyy')");
            params.add(proposedReceiptDTO.getCreateDate());
        }
        //Tim kiem theo ngay tao den ngay
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getCreateDateToDate())) {
            sql.append(" AND a.create_date <= to_date(?,'dd/mm/yyyy') + 1");
            params.add(proposedReceiptDTO.getCreateDateToDate());
        }
        //Tim kiem theo ma phieu
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getProposedReceiptCode())) {
            sql.append(" AND lower(a.proposed_receipt_code) like lower(?)");
            params.add("%" + proposedReceiptDTO.getProposedReceiptCode() + "%");
        }
        //Tim kiem theo ma lo hang
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getShipmentCode())) {
            sql.append(" AND lower(a.shipment_code) like lower(?)");
            params.add("%" + proposedReceiptDTO.getShipmentCode() + "%");
        }
        //Tim kiem theo so invoice
        if (!DataUtil.isStringNullOrEmpty(proposedReceiptDTO.getNumberInvoice())) {
            sql.append(" AND lower(a.number_invoice) like lower(?) ");
            params.add("%" + proposedReceiptDTO.getNumberInvoice() + "% \n");
        }
        //Sap xep theo ma phieu
        sql.append(" ORDER BY a.create_date DESC");
        //Tao cau lenh sql
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(ProposedReceipt.class));
        query.addScalar("proposedReceiptId", new LongType());
        query.addScalar("shipmentCode", new StringType());
        query.addScalar("proposedReceiptCode", new StringType());
        query.addScalar("stockId", new LongType());
        query.addScalar("customerId", new LongType());
        query.addScalar("numberInvoice", new StringType());
        query.addScalar("expectedDate", new DateType());
        query.addScalar("createDate", new DateType());
        query.addScalar("implementerName", new StringType());
        query.addScalar("note", new StringType());
        query.addScalar("contactPerson", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("receiveLocation", new StringType());
        query.addScalar("attachFileList", new StringType());
        query.addScalar("recentHistoryId", new LongType());
        //Truyen cac tham so truyen vao de thuc hien tim kiem
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i, params.get(i));
        }
        //Day du lieu ra danh sach doi tuong
        lstProposedReceiptDTOs = query.list();

    } catch (Exception e) {
        lstProposedReceiptDTOs = new ArrayList<>();
        e.printStackTrace();
    }
    return lstProposedReceiptDTOs;
}

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

License:Open Source License

public List<SerialInventoryReportDTO> getListSerialInventoryReportType1(SerialInventoryDTO serialInventoryDTO) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    sql.append(/*from  w  w w .j a v  a  2 s.c  om*/
            " select NVL(a.goods_id,b.goods_id) as goodsId ,NVL (a.goods_Code,b.goods_code) as goodsCode , ");
    sql.append("        NVL( a.goods_name,b.goods_name) as goodsName, ");
    sql.append("        NVL( a.goods_unit_type_name,b.goods_unit_type_name) as goodsUnitTypeName, ");
    sql.append("        NVL(quantity_state_1,0) as quantityState1,NVL(quantity_state_2,0) as quantityState2, ");
    sql.append("        (NVL(quantity_state_1,0) + NVL( quantity_state_2,0)) as total ");
    sql.append("        FROM ( ");
    sql.append(
            "        select  goods_id, goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_1 from serial_inventory  ");
    sql.append("        where cust_id = ? and stock_id  = ? and goods_state = 1  ");
    //------------------------------------------------------------------
    lstParams.add(serialInventoryDTO.getCustId());
    lstParams.add(serialInventoryDTO.getStockId());
    //dieu kien inventory code
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) {
        sql.append("   AND inventory_code = ? ");
        lstParams.add(serialInventoryDTO.getInventoryCode());
    }
    //dieu kien ma hang
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) {
        sql.append("   AND goods_code in ( ");
        sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode()));
        sql.append(" ) ");
    }
    //dieu dieu kien tu ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) {
        sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getCreateDate());
    }
    //dieu kien den ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) {
        sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getStaffId());
    }
    //dieu kien nguoi tao
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) {
        sql.append("  AND staff_name like ('%");
        sql.append(serialInventoryDTO.getStaffName());
        sql.append("%') ");
    }
    //------------------------------------------------------------------
    sql.append("   group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state) a  ");
    sql.append("   FULL JOIN  (  ");
    sql.append(
            "   select  goods_id,goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_2 from serial_inventory  ");
    sql.append("   where cust_id = ? and stock_id  = ? and goods_state   = 2  ");
    //------------------------------------------------------------------
    lstParams.add(serialInventoryDTO.getCustId());
    lstParams.add(serialInventoryDTO.getStockId());
    //dieu kien inventory code
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) {
        sql.append("   AND inventory_code = ? ");
        lstParams.add(serialInventoryDTO.getInventoryCode());
    }
    //dieu kien ma hang
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) {
        sql.append("   AND goods_code in ( ");
        sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode()));
        sql.append(" ) ");
    }
    //dieu dieu kien tu ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) {
        sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getCreateDate());
    }
    //dieu kien den ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) {
        sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getStaffId());
    }
    //dieu kien nguoi tao
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) {
        sql.append("  AND staff_name like ('%");
        sql.append(serialInventoryDTO.getStaffName());
        sql.append("%') ");
    }
    //------------------------------------------------------------------
    sql.append("   group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state ) b  ");
    sql.append("   ON a.goods_id = b.goods_id  ");
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(SerialInventoryReportDTO.class));

    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsUnitTypeName", new StringType());
    query.addScalar("quantityState1", new StringType());
    query.addScalar("quantityState2", new StringType());
    query.addScalar("total", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    List<SerialInventoryReportDTO> lstResult = query.list();
    for (SerialInventoryReportDTO i : lstResult) {
        i.setGoodsType("1");
    }
    return lstResult;
}

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

License:Open Source License

public List<SerialInventoryReportDTO> getListSerialInventoryReportType2(SerialInventoryDTO serialInventoryDTO) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    sql.append(//from  w  ww  .j a v a2  s. c  om
            " select NVL(a.goods_id,b.goods_id) as goodsId ,NVL (a.goods_Code,b.goods_code) as goodsCode , ");
    sql.append("        NVL( a.goods_name,b.goods_name) as goodsName, ");
    sql.append("        NVL( a.goods_unit_type_name,b.goods_unit_type_name) as goodsUnitTypeName, ");
    sql.append("        NVL(quantity_state_1,0) as quantityState1,NVL(quantity_state_2,0) as quantityState2, ");
    sql.append("        (NVL(quantity_state_1,0) + NVL( quantity_state_2,0)) as total ");
    sql.append("        FROM ( ");
    sql.append(
            "        select  goods_id, goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_1 from no_serial_inventory  ");
    sql.append("        where cust_id = ? and stock_id  = ? and goods_state = 1  ");
    //------------------------------------------------------------------
    lstParams.add(serialInventoryDTO.getCustId());
    lstParams.add(serialInventoryDTO.getStockId());
    //dieu kien inventory code
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) {
        sql.append("   AND inventory_code = ? ");
        lstParams.add(serialInventoryDTO.getInventoryCode());
    }
    //dieu kien ma hang
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) {
        sql.append("   AND goods_code in ( ");
        sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode()));
        sql.append(" ) ");
    }
    //dieu dieu kien tu ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) {
        sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getCreateDate());
    }
    //dieu kien den ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) {
        sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getStaffId());
    }
    //dieu kien nguoi tao
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) {
        sql.append("  AND staff_name like ('%");
        sql.append(serialInventoryDTO.getStaffName());
        sql.append("%') ");
    }
    //------------------------------------------------------------------
    sql.append("   group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state) a  ");
    sql.append("   FULL JOIN  (  ");
    sql.append(
            "   select  goods_id,goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_2 from no_serial_inventory  ");
    sql.append("   where cust_id = ? and stock_id  = ? and goods_state   = 2  ");
    //------------------------------------------------------------------
    lstParams.add(serialInventoryDTO.getCustId());
    lstParams.add(serialInventoryDTO.getStockId());
    //dieu kien inventory code
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getInventoryCode())) {
        sql.append("   AND inventory_code = ? ");
        lstParams.add(serialInventoryDTO.getInventoryCode());
    }
    //dieu kien ma hang
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getGoodsCode())) {
        sql.append("   AND goods_code in ( ");
        sql.append(parameterToINSQL(serialInventoryDTO.getGoodsCode()));
        sql.append(" ) ");
    }
    //dieu dieu kien tu ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getCreateDate())) {
        sql.append(" AND create_date > to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getCreateDate());
    }
    //dieu kien den ngay
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffId())) {
        sql.append(" AND create_date < to_date(?,'dd/MM/yyyy hh24:mi:ss') ");
        lstParams.add(serialInventoryDTO.getStaffId());
    }
    //dieu kien nguoi tao
    if (!DataUtil.isStringNullOrEmpty(serialInventoryDTO.getStaffName())) {
        sql.append("  AND staff_name like ('%");
        sql.append(serialInventoryDTO.getStaffName());
        sql.append("%') ");
    }
    //------------------------------------------------------------------
    sql.append("   group by goods_id,goods_code,goods_name,goods_unit_type_name,goods_state ) b  ");
    sql.append("   ON a.goods_id = b.goods_id  ");
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(SerialInventoryReportDTO.class));

    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsUnitTypeName", new StringType());
    query.addScalar("quantityState1", new StringType());
    query.addScalar("quantityState2", new StringType());
    query.addScalar("total", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    List<SerialInventoryReportDTO> lstResult = query.list();
    for (SerialInventoryReportDTO i : lstResult) {
        i.setGoodsType("2");
    }
    return lstResult;

}

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

License:Open Source License

public List getListErrorSerialInventory(String inventoryCode) {
    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.stock_id ownerId,");
    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.quantity quantity,");
    sql.append("         a.barcode barcode,");
    sql.append("         a.note notes,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         a.goods_code goodsCode,");
    sql.append("         a.goods_name goodsName ");
    sql.append("  FROM   err$_serial_inventory a");
    sql.append("  ");
    if (!DataUtil.isStringNullOrEmpty(inventoryCode)) {
        sql.append("  WHERE   a.inventory_code = ? ");
        lstParams.add(inventoryCode);/* w  w w .  ja  v a 2 s .  c  o  m*/
    }
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", 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("quantity", new StringType());
    query.addScalar("notes", 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();
}