Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

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

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

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

License:Open Source License

public List<StockGoodsSerialInforDTO> getStockGoodsSerialForInventoryNotSerial(
        StockGoodsSerialInforDTO inforDTO) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    sql.append(" SELECT  NVL (a.goodsid, b.goodsid) goodsid,");
    sql.append("         NVL (a.goodscode, b.goodscode) goodscode,");
    sql.append("         NVL (a.goodsname, b.goodsname) goodsname,");
    //sql.append("         NVL (a.goodsstate, b.goodsstate) goodsState,");
    sql.append("         NVL (a.goodsUnitType, b.goodsUnitType) goodsUnitType,");
    sql.append("         NVL (a.amount, 0) amount,");
    sql.append("         NVL (b.amount, 0) amountinventory,");
    sql.append("         NVL (b.amount, 0) - NVL (a.amount, 0) AS amountfalse");
    sql.append("  FROM       (SELECT   sgs.goods_id goodsid,");
    sql.append("                       g.code goodscode,");
    sql.append("                       g.name goodsname,");
    sql.append("                       g.unit_Type goodsUnitType,");
    //sql.append("                       sgs.goods_state goodsstate,");
    sql.append("                       Count(*) amount");
    sql.append("                FROM   stock_goods_serial sgs, goods g");
    sql.append("               WHERE       sgs.goods_id = g.goods_id");
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustId())) {
        sql.append("   AND sgs.cust_id = ? ");
        lstParams.add(inforDTO.getCustId());
    }//from   w w  w . ja  v  a2  s  .c  o  m
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerId())) {
        sql.append("    AND sgs.owner_id = ? ");
        lstParams.add(inforDTO.getOwnerId());
    }
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) {
        sql.append("    AND g.code IN (");
        sql.append(inforDTO.getGoodsCode());
        sql.append(" ) ");
    }
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) {
        sql.append("    AND sgs.status IN ( ");
        sql.append(inforDTO.getStatus());
        sql.append(" ) ");
    }
    sql.append("         GROUP BY sgs.goods_id,g.code, g.name, g.unit_type ) a ");
    sql.append("         FULL JOIN");
    sql.append("             (SELECT   sgs.goods_id goodsid,");
    sql.append("                       g.code goodscode,");
    sql.append("                       g.name goodsname,");
    sql.append("                       g.unit_Type goodsUnitType,");
    //sql.append("                       sgs.goods_state goodsstate,");
    sql.append("                       Count(*) amount");
    sql.append("                FROM   stock_goods_serial sgs, goods g");
    sql.append("               WHERE       sgs.goods_id = g.goods_id");
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustIdInventory())) {
        sql.append("    AND sgs.cust_id = ? ");
        lstParams.add(inforDTO.getCustIdInventory());
    }
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerIdInventory())) {
        sql.append("    AND sgs.owner_id = ? ");
        lstParams.add(inforDTO.getOwnerIdInventory());
    }
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getGoodsCode())) {
        sql.append("    AND g.code IN (");
        sql.append(inforDTO.getGoodsCode());
        sql.append(" ) ");
    }
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getStatus())) {
        sql.append("    AND sgs.status IN ( ");
        sql.append(inforDTO.getStatus());
        sql.append(" ) ");
    }
    sql.append("         GROUP BY sgs.goods_id,g.code, g.name,g.unit_type ) b");
    sql.append("         ON NVL (a.goodsCode, 'ABC') = NVL (b.goodsCode, 'ABC')");
    //sql.append("         AND NVL (a.goodsstate,'1') =  NVL (b.goodsstate,'1') ");
    sql.append("         ORDER BY goodsId");
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class));

    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //query.addScalar("goodsState", new StringType());
    query.addScalar("goodsUnitType", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("amountInventory", new StringType());
    query.addScalar("amountFalse", 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.StockGoodsSerialDAO.java

License:Open Source License

public List getListErrorImportRevoke(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.serial fromSerial,");
    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 a, goods g");
    sql.append("  WHERE   a.goods_id = g.goods_id");
    if (!DataUtil.isStringNullOrEmpty(stockTransId)) {
        sql.append("  AND a.import_stock_trans_id = ? ");
        lstParams.add(stockTransId);//from  www .  j a  v  a 2  s.co  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("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("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.StockGoodsSerialDAO.java

License:Open Source License

/**
 * thienng1 lay danh sach hang theo theo dieu kien tim kiem
 *///  w  ww.  ja  v  a2  s.c o  m
public List<StockGoodsSerialDTO> getListStockGoodsSerial(ChangeGoods changeGoods) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    StringBuilder path1 = new StringBuilder();
    StringBuilder path2 = new StringBuilder();
    StringBuilder path3 = new StringBuilder();
    path1.append(" SELECT sts.id id, sts.cust_id custId, sts.owner_id ownerId, sts.goods_id goodsId,");
    path1.append(
            "        sts.goods_state goodsState, sts.status status, sts.serial serial, sts.cell_code cellCode ");
    path1.append("        FROM stock_goods_serial sts, goods g ");

    path2.append(" WHERE sts.goods_id = g.goods_id ");

    path3.append("    AND g.code = ? ");
    lstParams.add(changeGoods.getOldGoodsCode());

    if (!DataUtil.isStringNullOrEmpty(changeGoods.getStockCode())) {
        path1.append(", stock s ");
        path2.append("    AND sts.owner_id = s.stock_id ");
        path3.append("    AND s.code = UPPER(?) ");
        lstParams.add(changeGoods.getStockCode());
    }
    if (!DataUtil.isStringNullOrEmpty(changeGoods.getCellCode())) {
        path3.append("    AND sts.cell_code = UPPER(?) ");
        lstParams.add(changeGoods.getCellCode());
    }
    if (!DataUtil.isStringNullOrEmpty(changeGoods.getCustId())) {
        path3.append("    AND sts.cust_id = ? ");
        lstParams.add(changeGoods.getCustId());
    }

    if (!DataUtil.isStringNullOrEmpty(changeGoods.getOldFromSerial())) {
        path3.append("    AND sts.serial = UPPER(?) ");
        lstParams.add(changeGoods.getOldFromSerial());
    }
    sql.append(path1);
    sql.append(path2);
    sql.append(path3);

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

    query.addScalar("id", new StringType());
    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("serial", new StringType());
    query.addScalar("cellCode", 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.StockGoodsSerialDAO.java

License:Open Source License

public List<StockTransGoodsDTO> getListStockTransGoods2Report(String lstStockTransCodes) {
    StringBuilder sqlGetStockTransGoods = new StringBuilder();
    sqlGetStockTransGoods.append("SELECT   st.stock_trans_type stockTransType, ");
    sqlGetStockTransGoods.append("         st.stock_trans_status stockTransStatus, ");
    sqlGetStockTransGoods.append("         st.order_code orderCode, ");
    sqlGetStockTransGoods.append("         st.order_action_code orderActionCode, ");
    sqlGetStockTransGoods.append("         st.stock_trans_code stockTransCode, ");
    sqlGetStockTransGoods.append("         st.syn_trans_code synTransCode, ");
    sqlGetStockTransGoods// ww  w .  j ava2s . co  m
            .append("         TO_CHAR (st.real_stock_trans_date, 'dd/MM/yyyy') realStockTransDate, ");
    sqlGetStockTransGoods.append("         TO_CHAR (st.stock_trans_date, 'dd/MM/yyyy') stockTransDate, ");
    sqlGetStockTransGoods.append("         st.trans_user_name transUserName, ");
    sqlGetStockTransGoods.append("         st.notes notes, ");
    sqlGetStockTransGoods.append("         st.order_id_list orderId,");
    sqlGetStockTransGoods.append("         st.owner_id stockId, ");
    sqlGetStockTransGoods.append("         a.code stockCode, ");
    sqlGetStockTransGoods.append("         o.receive_name receiveName, ");
    sqlGetStockTransGoods.append("         std.goods_code goodsCode, ");
    sqlGetStockTransGoods.append("         std.goods_name goodsName, ");
    sqlGetStockTransGoods.append("         g.unit_type goodsUnitType, ");
    sqlGetStockTransGoods.append("         std.goods_unit_type_name goodsUnitTypeName, ");
    sqlGetStockTransGoods.append("         std.amount_real amountReal ");
    sqlGetStockTransGoods.append("  FROM               stock_trans st ");
    sqlGetStockTransGoods.append("                   JOIN ");
    sqlGetStockTransGoods.append("                       stock_trans_detail std ");
    sqlGetStockTransGoods.append("                   ON std.stock_trans_id = st.stock_trans_id ");
    sqlGetStockTransGoods.append("               JOIN goods g ");
    sqlGetStockTransGoods.append("                   ON std.goods_id = g.goods_id");
    sqlGetStockTransGoods.append("               JOIN ");
    sqlGetStockTransGoods.append("                   stock a ");
    sqlGetStockTransGoods.append("               ON a.stock_id = st.owner_id ");
    sqlGetStockTransGoods.append("           LEFT JOIN  ");
    sqlGetStockTransGoods.append("               cms_owner.orders o ");
    sqlGetStockTransGoods.append("           ON o.order_id = st.order_id_list ");
    sqlGetStockTransGoods.append(" WHERE       st.stock_trans_code in ( :idx0 ) ");
    sqlGetStockTransGoods.append("GROUP BY   st.owner_id, ");
    sqlGetStockTransGoods.append("           st.order_id_list, ");
    sqlGetStockTransGoods.append("           o.receive_name, ");
    sqlGetStockTransGoods.append("           a.code, ");
    sqlGetStockTransGoods.append("           st.order_code, ");
    sqlGetStockTransGoods.append("           st.order_action_code, ");
    sqlGetStockTransGoods.append("           st.stock_trans_code, ");
    sqlGetStockTransGoods.append("           st.stock_trans_type, ");
    sqlGetStockTransGoods.append("           st.syn_trans_code, ");
    sqlGetStockTransGoods.append("           st.real_stock_trans_date, ");
    sqlGetStockTransGoods.append("           st.stock_trans_date, ");
    sqlGetStockTransGoods.append("           std.goods_code, ");
    sqlGetStockTransGoods.append("           std.goods_name, ");
    sqlGetStockTransGoods.append("           g.unit_type, ");
    sqlGetStockTransGoods.append("           std.goods_unit_type_name, ");
    sqlGetStockTransGoods.append("           std.amount_real, ");
    sqlGetStockTransGoods.append("           st.trans_user_name, ");
    sqlGetStockTransGoods.append("           st.notes, ");
    sqlGetStockTransGoods.append("           st.stock_trans_status ");
    sqlGetStockTransGoods.append("ORDER BY  st.stock_trans_date desc, stockTransCode, goodsName ");
    List lstParams = new ArrayList<>();
    if (lstStockTransCodes.contains(ParamUtils.SPLITTER)) {
        lstParams.add(lstStockTransCodes.split(ParamUtils.SPLITTER));
    } else {
        lstParams.add(lstStockTransCodes);
    }
    SQLQuery query = getSession().createSQLQuery(sqlGetStockTransGoods.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockTransGoodsDTO.class));
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsUnitType", new StringType());
    query.addScalar("goodsUnitTypeName", new StringType());
    query.addScalar("amountReal", new DoubleType());
    query.addScalar("stockTransType", new StringType());
    query.addScalar("orderCode", new StringType());
    query.addScalar("orderActionCode", new StringType());
    query.addScalar("stockTransCode", new StringType());
    query.addScalar("synTransCode", new StringType());
    query.addScalar("realStockTransDate", new StringType());
    query.addScalar("stockTransDate", new StringType());
    query.addScalar("transUserName", new StringType());
    query.addScalar("notes", new StringType());
    query.addScalar("orderId", new StringType());
    query.addScalar("stockId", new StringType());
    query.addScalar("stockCode", new StringType());
    query.addScalar("receiveName", new StringType());
    query.addScalar("stockTransStatus", new StringType());

    for (int i = 0; i < lstParams.size(); i++) {
        if (lstParams.get(i) instanceof String[]) {
            query.setParameterList("idx" + String.valueOf(i), (Object[]) lstParams.get(i));
        } else {
            query.setParameter("idx" + String.valueOf(i), lstParams.get(i));
        }
    }

    return query.list();
}

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

License:Open Source License

public List<StockGoodsSerialStrip> getListStockGoodsSerial(StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    List<StockGoodsSerialStrip> lstStockGoodsSerialStrip = null;
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    ///* w w w. j  av  a 2  s  . c om*/
    sql.append("SELECT   cust_id custId,");
    sql.append("         owner_id ownerId,");
    sql.append("         owner_type ownerType,");
    sql.append("         cell_code cellCode,");
    sql.append("         goods_id goodsId,");
    sql.append("         state goodsState,");
    sql.append("         status,");
    sql.append("         channel_type_id channelTypeId,");
    sql.append("         from_serial fromSerial,");
    sql.append("         to_serial toSerial, quantity ");
    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());
    }
    //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 cust_id,owner_id,owner_type,cell_code,goods_id,goods_state,status,serial),");
    sql.append("                    TO_NUMBER (?))) a");
    lstParams.add(stockGoodsSerialStripDTO.getQuantity());
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialStrip.class));

    query.addScalar("custId", new LongType());
    query.addScalar("ownerId", new LongType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("goodsId", new LongType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("channelTypeId", new StringType()); // sua lai LongType -> StringType (tiepnv6)
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("quantity", new LongType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    lstStockGoodsSerialStrip = query.list();
    //
    return lstStockGoodsSerialStrip;
}

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 ");
    ///*from w w w . j  a v a  2  s. co  m*/
    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 List<StockGoodsSerialInforDTO> getListStockGoodsSerialInforByStaff(
        StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    List<StockGoodsSerialInforDTO> lstStockGoodsSerialInfor = null;
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    //// w w  w  .j ava2s  .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("         msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName ");
    sql.append("  FROM   table(show_serial_strip (cursor (  SELECT   sts.cust_id,");
    sql.append("                                                     sts.owner_id,");
    sql.append("                                                     sts.owner_type,");
    sql.append("                                                     sts.cell_code,");
    sql.append("                                                     sts.goods_id,");
    sql.append("                                                     sts.goods_state,");
    sql.append("                                                     sts.status,");
    sql.append("                                                     sts.channel_type_id,");
    sql.append("                                                     sts.serial");
    sql.append("                                              FROM   stock_goods_serial sts");
    sql.append("                                             WHERE   1 = 1 ");
    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) {
        sql.append("AND sts.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND sts.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND sts.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        sql.append("AND sts.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim theo tinh trang hang hoa        
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) {
        sql.append("AND sts.goods_state = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsState());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sts.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kiem theo Kenh
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) {
        sql.append("AND sts.channel_type_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId());
    }
    //Tim kiem theo from serial
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) {
        sql.append("AND sts.serial >= ? ");
        lstParams.add(stockGoodsSerialStripDTO.getFromSerial());
    }
    //Tim kiem theo to serial
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) {
        sql.append("AND sts.serial <= ? ");
        lstParams.add(stockGoodsSerialStripDTO.getToSerial());
    }
    //Tim theo cell         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sts.cell_code = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCellCode());
    }
    //Tim theo Barcode         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) {
        sql.append("AND sts.barcode = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getBarcode());
    }
    //Tim theo Bincode         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) {
        sql.append("AND sts.bincode = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getBincode());
    }
    //
    sql.append(" ORDER BY sts.cust_id,sts.owner_id,sts.owner_type,sts.cell_code,");
    sql.append("          sts.goods_id,sts.goods_state,sts.status,sts.serial),");
    sql.append("                    TO_NUMBER (?))) a, goods g, map_staff_goods msg ");

    sql.append("  WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id ");
    sql.append("         AND a.goods_id = msg.goods_id(+)");
    lstParams.add(stockGoodsSerialStripDTO.getQuantity());
    //tim kiem theo nhan vien
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStaffId())) {
        sql.append("    AND msg.staff_id = ?");
        lstParams.add(stockGoodsSerialStripDTO.getStaffId());
    }
    //ThienNG1- Begin AddBy 18/06/2015 
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        //sql.append("AND g.goods_type in (?) ");
        if (stockGoodsSerialStripDTO.getGoodsType().contains(",")) {
            sql.append("AND g.goods_type IN (:gdstype) ");
        } else {
            sql.append("AND g.goods_type IN (?) ");
        }
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //End
    sql.append(
            " ORDER BY a.cust_id,a.owner_id,a.owner_type,g.goods_type,g.code,a.cell_code,a.state,a.from_serial,a.status ");

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

    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("channelTypeId", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("quantity", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsGroup", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("staffId", new StringType());
    query.addScalar("staffCode", new StringType());
    query.addScalar("staffName", new StringType());
    //ThienNG1 update 03/08/2015
    for (int i = 0; i < lstParams.size(); i++) {
        if (lstParams.get(i) != null) {
            if (lstParams.get(i).toString().contains(",")) {
                List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings()
                        .splitToList(stockGoodsSerialStripDTO.getGoodsType().toString());
                query.setParameterList("gdstype", lst);
            } else {
                query.setParameter(i, lstParams.get(i));
            }
        } else {
            query.setParameter(i, lstParams.get(i));
        }
    }

    //
    return query.list();
}

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

License:Open Source License

public List<StockGoodsSerialInforDTO> getListStockGoodsSerialInforAll(
        StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    List<StockGoodsSerialInforDTO> lstStockGoodsSerialInfor = null;
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    ////from  w w w .j av a2s.  co  m
    sql.append("SELECT   a.cust_id custId,");
    sql.append("         a.owner_id ownerId,");
    sql.append("         a.owner_type ownerType,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         a.goods_id goodsId,");
    sql.append("         a.state goodsState,");
    sql.append("         a.status,");
    sql.append("         a.channel_type_id channelTypeId,");
    sql.append("         a.from_serial fromSerial,");
    sql.append("         a.to_serial toSerial, a.quantity, ");
    sql.append("         g.goods_type goodsType, g.goods_group goodsGroup, ");
    sql.append("         g.code goodsCode, g.name goodsName ");
    sql.append("  FROM   table(show_serial_strip (cursor (  SELECT   sts.cust_id,");
    sql.append("                                                     sts.owner_id,");
    sql.append("                                                     sts.owner_type,");
    sql.append("                                                     sts.cell_code,");
    sql.append("                                                     sts.goods_id,");
    sql.append("                                                     sts.goods_state,");
    sql.append("                                                     sts.status,");
    sql.append("                                                     sts.channel_type_id,");
    sql.append("                                                     sts.serial");
    sql.append("                                              FROM   stock_goods_serial sts");
    sql.append("                                             WHERE   1 = 1 ");
    //Tim kien theo mat hang
    //        if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
    //            sql.append("AND sts.goods_id = ? ");
    //            lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
    //        }
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        sql.append("AND sts.goods_id IN (");
        sql.append(stockGoodsSerialStripDTO.getGoodsId());
        sql.append(") ");
    }
    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) {
        sql.append("AND sts.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND sts.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND sts.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim theo tinh trang hang hoa        
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) {
        sql.append("AND sts.goods_state = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsState());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sts.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kiem theo Kenh
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) {
        sql.append("AND sts.channel_type_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId());
    }
    //Tim kiem theo from serial
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) {
        sql.append("AND sts.serial >= ? ");
        lstParams.add(stockGoodsSerialStripDTO.getFromSerial());
    }
    //Tim kiem theo to serial
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) {
        sql.append("AND sts.serial <= ? ");
        lstParams.add(stockGoodsSerialStripDTO.getToSerial());
    }
    //Tim theo cell         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND sts.cell_code = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCellCode());
    }
    //Tim theo Barcode         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) {
        sql.append("AND sts.barcode = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getBarcode());
    }
    //Tim theo Bincode         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) {
        sql.append("AND sts.bincode = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getBincode());
    }
    //
    sql.append(" ORDER BY sts.cust_id,sts.owner_id,sts.owner_type,sts.cell_code,");
    sql.append("          sts.goods_id,sts.goods_state,sts.status,sts.serial),");
    sql.append("                    TO_NUMBER (?))) a, goods g ");

    sql.append("  WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id ");
    lstParams.add(stockGoodsSerialStripDTO.getQuantity());

    //ThienNG1- Begin AddBy 18/06/2015 
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        //sql.append("AND g.goods_type in (?) ");
        if (stockGoodsSerialStripDTO.getGoodsType().contains(",")) {
            sql.append("AND g.goods_type IN (:gdstype) ");
        } else {
            sql.append("AND g.goods_type IN (?) ");
        }
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //End
    sql.append(
            " ORDER BY a.cust_id,a.owner_id,a.owner_type,g.goods_type,g.code,a.cell_code,a.state,a.from_serial,a.status ");

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

    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("channelTypeId", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("quantity", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsGroup", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //ThienNG1 update 03/08/2015

    for (int i = 0; i < lstParams.size(); i++) {
        if (lstParams.get(i) != null) {
            if (lstParams.get(i).toString().contains(",")) {
                List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings()
                        .splitToList(stockGoodsSerialStripDTO.getGoodsType().toString());
                query.setParameterList("gdstype", lst);
            } else {
                query.setParameter(i, lstParams.get(i));
            }
        } else {
            query.setParameter(i, lstParams.get(i));
        }
    }

    //
    return query.list();
}

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

License:Open Source License

public List<StockGoodsSerialInforDTO> getListStockGoodsSerialStripInforByStaff(
        StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    List<StockGoodsSerialInforDTO> lstStockGoodsSerialInfor = null;
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    ////from  ww  w . j  av a 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.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();
    ///*from  w ww. j ava  2s  .com*/
    sql.append("SELECT   a.cust_id custId,");
    sql.append("         a.owner_id ownerId,");
    sql.append("         a.owner_type ownerType,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         a.goods_id goodsId,");
    sql.append("         a.goods_state goodsState,");
    sql.append("         a.status,");
    sql.append("         a.channel_type_id channelTypeId,");
    sql.append("         a.from_serial fromSerial,");
    sql.append("         a.to_serial toSerial, a.quantity, ");
    sql.append("         g.goods_type goodsType, g.goods_group goodsGroup, ");
    sql.append("         g.code goodsCode, g.name goodsName ");
    sql.append("  FROM   stock_goods_serial_strip a, goods g ");
    sql.append("  WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id ");

    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) {
        sql.append("AND a.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND a.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND a.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        sql.append("AND a.goods_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
    }
    //Tim theo tinh trang hang hoa        
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) {
        sql.append("AND a.goods_state = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsState());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND a.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kiem theo Kenh
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) {
        sql.append("AND a.channel_type_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId());
    }
    //        //Tim kiem theo from serial
    //        if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) {
    //            sql.append("AND sts.serial >= ? ");
    //            lstParams.add(stockGoodsSerialStripDTO.getFromSerial());
    //        }
    //        //Tim kiem theo to serial
    //        if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) {
    //            sql.append("AND sts.serial <= ? ");
    //            lstParams.add(stockGoodsSerialStripDTO.getToSerial());
    //        }
    //Tim theo cell         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCellCode())) {
        sql.append("AND a.cell_code = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCellCode());
    }
    //Tim theo Barcode         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBarcode())) {
        sql.append("AND a.barcode = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getBarcode());
    }
    //Tim theo Bincode         
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getBincode())) {
        sql.append("AND a.bincode = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getBincode());
    }
    //ThienNG1- Begin AddBy 18/06/2015 
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsType())) {
        //ThienNG1 Update 03/08/2015
        if (stockGoodsSerialStripDTO.getGoodsType().contains(",")) {
            sql.append("AND g.goods_type IN (:gdstype) ");
        } else {
            sql.append("AND g.goods_type IN (?) ");
        }
        lstParams.add(stockGoodsSerialStripDTO.getGoodsType());
    }
    //End
    sql.append(
            " ORDER BY a.cust_id,a.owner_id,a.owner_type,g.goods_type,g.code,a.goods_state,a.cell_code,a.from_serial,a.status ");
    //lstParams.add(stockGoodsSerialStripDTO.getQuantity());
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class));

    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("channelTypeId", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("quantity", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsGroup", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //ThienNG1 Update 03/08/2015
    for (int i = 0; i < lstParams.size(); i++) {
        if (lstParams.get(i).toString().contains(",")) {
            List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings()
                    .splitToList(stockGoodsSerialStripDTO.getGoodsType().toString());
            query.setParameterList("gdstype", lst);
        } else {
            query.setParameter(i, lstParams.get(i));
        }
    }

    //
    return query.list();
}