Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

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

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

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

License:Open Source License

public List<StockGoodsInforDTO> getSumListStockGoods(StockGoodsInforDTO stockGoodsInforDTO) {
    List lstParams = new ArrayList();
    StringBuilder sql = new StringBuilder();
    //        //from   ww w .  j ava2s .  co m
    sql.append("SELECT   sg.cust_id custId,");
    sql.append("         s.stock_id ownerId,");
    sql.append("         sg.owner_type ownerType,");
    sql.append("         s.code ownerCode,");
    sql.append("         s.name ownerName,");
    sql.append("         g.goods_type goodsType,");
    sql.append("         g.goods_id goodsId,");
    sql.append("         g.code goodsCode,");
    sql.append("         g.name goodsName,");
    sql.append("         sg.goods_state goodsState,");
    sql.append("         SUM(sg.amount) amount,");
    sql.append("         SUM(sg.amount_issue) amountIssue,");
    sql.append("         g.unit_type unitType,");
    sql.append("         msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName ");
    sql.append("  FROM   stock_goods_total sg, goods g, stock s, map_staff_goods msg ");

    // if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getStaffId())) {
    // sql.append("   ");
    //}
    sql.append(" WHERE   s.stock_id = sg.owner_id");
    sql.append("         AND g.goods_id = sg.goods_id");
    sql.append("         AND sg.goods_id = msg.goods_id(+)");
    //Tim kiem theo nhan vien
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getStaffId())) {
        sql.append("     AND msg.staff_id = ?");
        lstParams.add(stockGoodsInforDTO.getStaffId());
    }
    //tim kiem theo dieu kien hang hoa co hieu luc
    sql.append("         AND g.status = ?");
    lstParams.add(Constants.ACTIVE_STATUS);
    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getCustId())) {
        sql.append("     AND sg.cust_id = ?");
        sql.append("     AND g.cust_id = ?");
        lstParams.add(stockGoodsInforDTO.getCustId());
        lstParams.add(stockGoodsInforDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getOwnerId())) {
        sql.append("     AND sg.owner_id = ?");
        lstParams.add(stockGoodsInforDTO.getOwnerId());
    }
    //Tim kiem theo loai kho
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getOwnerType())) {
        sql.append("     AND sg.owner_type = ?");
        lstParams.add(stockGoodsInforDTO.getOwnerType());
    }
    //Tim kiem theo nhom mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsType())) {
        sql.append("      AND g.goods_type = ?");
        lstParams.add(stockGoodsInforDTO.getGoodsType());
    }
    //Tim kiem theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsId())) {
        sql.append("      AND g.goods_id = ?");
        lstParams.add(stockGoodsInforDTO.getGoodsId());
    }
    //Tim kiem theo trang thai hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getGoodsState())) {
        sql.append("      AND sg.goods_state = ?");
        lstParams.add(stockGoodsInforDTO.getGoodsState());
    }
    //Tim kiem theo don vi tinh
    if (!StringUtils.isStringNullOrEmpty(stockGoodsInforDTO.getUnitType())) {
        sql.append("      AND g.unit_type = ?");
        lstParams.add(stockGoodsInforDTO.getUnitType());
    }
    //
    sql.append(
            " GROUP BY sg.cust_id,s.stock_id,sg.owner_type,s.code,s.name,g.goods_type,g.goods_id,g.code, g.name,sg.goods_state,g.unit_type, ");
    sql.append("          msg.staff_id, msg.staff_code, msg.staff_name ");
    sql.append(" ORDER BY sg.cust_id,s.code,g.goods_type,g.code,g.name,sg.goods_state,g.unit_type");
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("ownerCode", new StringType());
    query.addScalar("ownerName", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("amountIssue", new StringType());
    query.addScalar("unitType", new StringType());
    query.addScalar("staffId", new StringType());
    query.addScalar("staffCode", new StringType());
    query.addScalar("staffName", new StringType());
    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //
    return query.list();
}

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

License:Open Source License

public List<StockGoodsInforDTO> getSumListStockGoodsExportExcel(StockGoodsSerialDTO stockGoodsSerialDTO,
        StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    List lstParams = new ArrayList();
    //        //from   w  w  w.  j a va  2 s  .  c o  m
    StringBuilder sql = new StringBuilder();
    //

    //Tim kiem serial don le theo trang thai
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialDTO.getStatus())) {
        sql.append("         AND sgs.status = ?");
        lstParams.add(stockGoodsSerialDTO.getStatus());
    }
    //Tim kiem serial dai theo trang thai
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("         AND sgst.status = ?");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }

    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class));
    query.addScalar("zoneName", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("amount", new StringType());
    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //
    return query.list();
}

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

License:Open Source License

public List<StockGoodsSerialInforDTO> getStockGoodsForInventory(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   sg.goods_id goodsid,");
    //sql.append("                         sg.goods_state goodsstate,");
    sql.append("                         g.code goodsCode,");
    sql.append("                         g.name goodsName,");
    sql.append("                         g.unit_Type goodsUnitType,");
    sql.append("                         SUM (sg.amount) amount");
    sql.append("                  FROM   stock_goods sg, goods g");
    sql.append("                 WHERE   sg.goods_id =  g.goods_id ");
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustId())) {
        sql.append("    AND sg.cust_id = ? ");
        lstParams.add(inforDTO.getCustId());
    }/*from w w  w  . j av a2  s.c o  m*/
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerId())) {
        sql.append("    AND sg.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 sg.status IN ( ");
        sql.append(inforDTO.getStatus());
        sql.append(" ) ");
    }
    sql.append("         GROUP BY   sg.goods_id, g.code, g.name, g.unit_Type) a");
    sql.append("         FULL JOIN");
    sql.append("             (  SELECT   sg.goods_id goodsid,");
    //sql.append("                         sg.goods_state goodsstate,");
    sql.append("                         g.code goodsCode,");
    sql.append("                         g.name goodsName,");
    sql.append("                         g.unit_Type goodsUnitType,");
    sql.append("                         SUM (sg.amount) amount");
    sql.append("                  FROM   stock_goods sg, goods g");
    sql.append("                 WHERE   sg.goods_id =  g.goods_id ");
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getCustIdInventory())) {
        sql.append("   AND sg.cust_id = ? ");
        lstParams.add(inforDTO.getCustIdInventory());
    }
    if (!DataUtil.isStringNullOrEmpty(inforDTO.getOwnerIdInventory())) {
        sql.append("    AND sg.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 sg.status IN ( ");
        sql.append(inforDTO.getStatus());
        sql.append(" ) ");
    }
    sql.append("              GROUP BY   sg.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.StockGoodsDAO.java

License:Open Source License

public List<StockGoodsSerialInforDTO> getStockGoodsSerialForInventory(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.serial, b.serial) fromSerial,");
    sql.append("         NVL (a.serial, b.serial) toSerial,");
    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("                         UPPER(sgs.serial) serial,");
    sql.append("                         TO_NUMBER(1) 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());
    }//  w  w  w . ja va2 s.co 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("         ) 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("                         UPPER(sgs.serial) serial,");
    sql.append("                         TO_NUMBER(1) 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("         ) 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("         AND NVL (a.serial, 'ABC') = NVL (b.serial, 'ABC') ");
    sql.append("         ORDER BY goodsId, fromSerial");
    //
    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("fromSerial", new StringType());
    query.addScalar("toSerial", 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.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());
    }/*ww w .  j ava 2s.  c om*/
    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 Double getAmountInStockGoodsTotal(ChangePositionDTO changePositionDTO) {
    StringBuilder sql = new StringBuilder();
    sql.append("       SELECT   count(*) ");
    sql.append("       FROM   wms_owner.stock_goods_serial a");
    sql.append("       WHERE       a.goods_id = ? ");
    sql.append("            AND a.owner_type = ? ");
    sql.append("            AND a.owner_id = ? ");
    sql.append("            AND a.cust_id = ? ");
    sql.append("            AND a.cell_code = ? ");
    sql.append("            AND a.status = ? ");
    List lstParams = new ArrayList<>();
    lstParams.add(changePositionDTO.getGoodsId());
    lstParams.add(changePositionDTO.getOwnerType());
    lstParams.add(changePositionDTO.getStockId());
    lstParams.add(changePositionDTO.getCustomerId());
    lstParams.add(changePositionDTO.getCellCodeOld());
    lstParams.add("1");
    if (!DataUtil.isStringNullOrEmpty(changePositionDTO.getBarcode())) {
        sql.append("            AND a.barcode = ? ");
        lstParams.add(changePositionDTO.getBarcode());
    }//  ww  w. j a v a  2s  . co  m
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    for (int idx = 0; idx < lstParams.size(); idx++) {
        query.setParameter(idx, lstParams.get(idx));
    }
    List listResult = query.list();
    BigDecimal result;
    if (listResult != null && listResult.size() > 0) {
        result = (BigDecimal) listResult.get(0);
        return result.doubleValue();
    }
    return 0D;
}

From source file:com.viettel.logistic.wms.dao.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  w  w  w  . j  a v a2s.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("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
 *//*  ww w .j  a v  a 2s  . 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/*from   www. j a v a 2  s.c o 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();
    ////from   ww w.j av a2s.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;
}