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

License:Open Source License

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

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

    query.addScalar("zoneName", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("custId", new StringType());
    query.addScalar("staffId", new StringType());
    query.addScalar("staffCode", new StringType());
    query.addScalar("staffName", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    //

    return query.list();
}

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

License:Open Source License

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

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

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

    query.addScalar("zoneName", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("custId", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    //

    return query.list();
}

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

License:Open Source License

public List<StockGoodsSerialStripDTO> getListStockGoodsSerialBySerial(
        StockGoodsSerialStripDTO stockGoodsSerialStripDTO) {
    List<StockGoodsSerialStripDTO> lstStockGoodsSerialStrips = null;
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    ///*ww  w.j av a  2s  .c  o m*/
    sql.append("SELECT   a.cust_id custId,");
    sql.append("         a.owner_id ownerId,");
    sql.append("         a.owner_type ownerType,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         a.goods_id goodsId,");
    sql.append("         a.state goodsState,");
    sql.append("         a.status,");
    sql.append("         a.channel_type_id channelTypeId,");
    sql.append("         a.from_serial fromSerial,");
    sql.append("         a.to_serial toSerial, a.quantity, ");
    sql.append("         g.goods_type goodsType, g.goods_group goodsGroup, ");
    sql.append("         g.code goodsCode, g.name goodsName ");
    sql.append("  FROM   table(show_serial_strip (cursor (  SELECT   sts.cust_id,");
    sql.append("                                                     sts.owner_id,");
    sql.append("                                                     sts.owner_type,");
    sql.append("                                                     sts.cell_code,");
    sql.append("                                                     sts.goods_id,");
    sql.append("                                                     sts.goods_state,");
    sql.append("                                                     sts.status,");
    sql.append("                                                     sts.channel_type_id,");
    sql.append("                                                     sts.serial");
    sql.append("                                              FROM   stock_goods_serial sts");
    sql.append("                                             WHERE   1 = 1 ");
    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getCustId())) {
        sql.append("AND sts.cust_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getCustId());
    }
    //Tim kiem theo kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerId())) {
        sql.append("AND sts.owner_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerId());
    }
    //Tim kiem theo loai kho hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getOwnerType())) {
        sql.append("AND sts.owner_type = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getOwnerType());
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsId())) {
        sql.append("AND sts.goods_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsId());
    }
    //Tim theo tinh trang hang hoa        
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsState())) {
        sql.append("AND sts.goods_state = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsState());
    }
    //Tim kiem trang thai serial hang hoa
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getStatus())) {
        sql.append("AND sts.status = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getStatus());
    }
    //Tim kiem theo Kenh
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getChannelTypeId())) {
        sql.append("AND sts.channel_type_id = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getChannelTypeId());
    }
    //Tim kiem theo from serial
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getFromSerial())) {
        sql.append("AND sts.serial >= ? ");
        lstParams.add(stockGoodsSerialStripDTO.getFromSerial());
    }
    //Tim kiem theo to serial
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getToSerial())) {
        sql.append("AND sts.serial <= ? ");
        lstParams.add(stockGoodsSerialStripDTO.getToSerial());
    }

    //
    sql.append(" ORDER BY sts.cust_id,sts.owner_id,sts.owner_type,sts.cell_code, ");
    sql.append("          sts.goods_id,sts.goods_state,sts.status,sts.serial),");
    sql.append(
            "                    TO_NUMBER (?))) a, goods g WHERE g.cust_id = a.cust_id AND g.goods_id = a.goods_id ");
    lstParams.add(stockGoodsSerialStripDTO.getQuantity());
    //Tim kien theo loai mat hang
    if (!StringUtils.isStringNullOrEmpty(stockGoodsSerialStripDTO.getGoodsGroup())) {
        sql.append("AND g.goods_group = ? ");
        lstParams.add(stockGoodsSerialStripDTO.getGoodsGroup());
    }
    sql.append(
            " ORDER BY a.cust_id,a.owner_id,a.owner_type,a.cell_code, g.goods_type,g.code,a.state,a.status ");

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

    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("channelTypeId", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("quantity", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsGroup", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //
    return query.list();
}

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

License:Open Source License

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

    sql.append("SELECT   ");
    sql.append("         a.ora_err_mesg$ addInfor,");
    sql.append("         a.cust_id custId,");
    sql.append("         a.owner_id ownerId,");
    sql.append("         a.owner_type ownerType,");
    sql.append("         a.goods_id goodsId,");
    sql.append("         a.goods_state goodsState,");
    sql.append("         a.status status,");
    sql.append("         a.from_serial fromSerial,");
    sql.append("         a.to_serial toSerial,");
    sql.append("         a.barcode barcode,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         g.code goodsCode,");
    sql.append("         g.name goodsName ");
    sql.append("  FROM   err$_stock_goods_serial_strip a, goods g ");
    sql.append("  WHERE   a.goods_id = g.goods_id ");
    ////from w ww  . j  a  v a 2s.c  o m
    if (!DataUtil.isStringNullOrEmpty(stockTransId)) {
        sql.append("  AND a.import_stock_trans_id = ? ");
        lstParams.add(stockTransId);
    }
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class));

    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("addInfor", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("barcode", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    return query.list();
}

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

License:Open Source License

/**
 * Thienng1 addby 28/03/2016 Kiem tra xem Kit da co trong kho hay khong
 *
 * @param orderId//  www . jav  a 2  s. c  o  m
 * @param stockTransSerialDTO
 * @param session
 * @return
 */
//Tim kiem serial theo dai theo session
public List<StockGoodsSerialStrip> getListSerialStripKitInStockByOrderId(String orderId,
        StockTransSerialDTO stockTransSerialDTO, Session session) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    //
    sql.append(" SELECT sts.goods_id goodsId, sts.goods_state goodsState,");
    sql.append(
            " sts.status status, sts.from_serial fromSerial, sts.to_serial toSerial, sts.quantity quantity ");
    sql.append("  FROM Stock_Goods_Serial_Strip sts WHERE 1=1 ");
    sql.append("AND sts.status = 1 ");//trang thai cho kho

    if (!StringUtils.isStringNullOrEmpty(orderId)) {
        sql.append("AND sts.order_id = ? ");
        lstParams.add(orderId);
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsId())) {
        sql.append("AND sts.goods_id = ? ");
        lstParams.add(stockTransSerialDTO.getGoodsId());
    }
    //Tim theo tinh trang hang hoa        
    if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsState())) {
        sql.append("AND sts.goods_state = ? ");
        lstParams.add(stockTransSerialDTO.getGoodsState());
    }
    //Tim kiem theo from serial
    if (!StringUtils.isNullOrEmpty(stockTransSerialDTO.getFromSerial())) {
        sql.append("AND sts.to_serial >= ? ");
        lstParams.add(stockTransSerialDTO.getFromSerial());
    }
    //Tim kiem theo to serial
    if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getToSerial())) {
        sql.append("AND sts.from_serial <= ? ");
        lstParams.add(stockTransSerialDTO.getToSerial());
    }

    SQLQuery query = session.createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialStrip.class));

    query.addScalar("goodsId", new LongType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    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));
    }
    //
    return query.list();
}

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

License:Open Source License

/**
 * Thienng1 addby 28/03/2016 Kiem tra xem Kit da co trong kho hay khong(hang
 * serial don le)// w ww  .ja  va2 s.  c  o  m
 *
 * @param stockTransSerialDTO
 * @param orderId
 * @param session
 * @return
 */
//Tim kiem serial theo dai theo session
public List<StockGoodsSerial> getListSerialKitInStockByOrderId(String orderId,
        StockTransSerialDTO stockTransSerialDTO, Session session) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    //
    sql.append(" SELECT * FROM sts.goods_id goodsId, sts.goods_state goodsState, ");
    sql.append(" sts.status status, sts.serial serial, sts.quantity quantity");
    sql.append("Stock_Goods_Serial sts WHERE 1=1 ");
    sql.append("AND sts.status = 1 ");

    //tim kiem theo yeu cau
    if (!StringUtils.isStringNullOrEmpty(orderId)) {
        sql.append("AND sts.order_id = ? ");
        lstParams.add(orderId);
    }
    //Tim kien theo mat hang
    if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsId())) {
        sql.append("AND sts.goods_id = ? ");
        lstParams.add(stockTransSerialDTO.getGoodsId());
    }
    //Tim theo tinh trang hang hoa        
    if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getGoodsState())) {
        sql.append("AND sts.goods_state = ? ");
        lstParams.add(stockTransSerialDTO.getGoodsState());
    }

    //Tim kiem theo serial
    if (!StringUtils.isStringNullOrEmpty(stockTransSerialDTO.getFromSerial())) {
        sql.append("AND sts.serial = ? ");
        lstParams.add(stockTransSerialDTO.getFromSerial());
    }

    SQLQuery query = session.createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerial.class));

    query.addScalar("goodsId", new LongType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("serial", new StringType());
    query.addScalar("quantity", new LongType());

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

License:Open Source License

public List<StockGoodsInforDTO> getSumListGoodsAll(StockGoodsInforDTO stockGoodsInforDTO) {
    List lstParams = new ArrayList();
    StringBuilder sql = new StringBuilder();
    //        /*from  w w w .ja  v a  2  s. 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("  FROM   stock_goods_total sg, goods g, stock s ");
    sql.append(" WHERE   s.stock_id = sg.owner_id");
    sql.append("         AND g.goods_id = sg.goods_id");
    //ChuDV add 11/12/2015  --Lay cac mat hang con trong kho       
    sql.append("         AND (sg.amount != 0 OR sg.amount_issue != 0) ");
    //
    //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());
        sql.append("AND g.goods_id IN (");
        sql.append(stockGoodsInforDTO.getGoodsId());
        sql.append(") ");
    }
    //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(" 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());
    //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.StockGoodsTotalDAO.java

License:Open Source License

public List<StockGoodsInforDTO> getSumListGoodsByStaff(StockGoodsInforDTO stockGoodsInforDTO) {
    List lstParams = new ArrayList();
    StringBuilder sql = new StringBuilder();
    //        /*from   w  w  w  .  jav  a  2 s . c om*/
    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 ");
    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(+)");
    //
    //ChuDV add 11/12/2015  --Lay cac mat hang con trong kho       
    sql.append("         AND (sg.amount != 0 OR sg.amount_issue != 0) ");
    //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());
        sql.append("AND g.goods_id IN (");
        sql.append(stockGoodsInforDTO.getGoodsId());
        sql.append(") ");
    }
    //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.StockTransDAO.java

License:Open Source License

public List<StockTransInforDTO> getListStockTransInfor(StockTransInforDTO stockTransInforDTO) {
    List<StockTransInforDTO> lstStockGoodsSerialStrip = null;
    StringBuffer sql = new StringBuffer();
    List lstParams = new ArrayList();
    ////from w  w  w .ja  v  a  2  s.  c om
    sql.append("SELECT   st.stock_trans_id stockTransId, ");
    sql.append("         to_char(st.stock_trans_date,'dd/mm/yyyy') stockTransDate, ");
    sql.append("         st.stock_trans_status stockTransStatus, ");
    sql.append("         st.cust_id custId, ");
    sql.append("         st.owner_id ownerId, ");
    sql.append("         st.owner_type ownerType, ");
    sql.append("         std.goods_id goodsId, ");
    sql.append("         std.goods_code goodsCode, ");
    sql.append("         std.goods_name goodsName, ");
    sql.append("         std.goods_is_serial goodsIsSerial, ");
    sql.append("         std.goods_is_serial_strip goodsIsSerialStrip, ");
    sql.append("         std.goods_state goodsState, ");
    sql.append("         '' barcode, ");
    sql.append("         '' cellCode ");
    sql.append("  FROM   stock_trans st, stock_trans_detail std ");
    sql.append(" WHERE       st.stock_trans_id = std.stock_trans_id ");
    sql.append("         AND st.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') ");
    sql.append("         AND st.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 ");
    sql.append("         AND std.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') ");
    sql.append("         AND std.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 ");
    sql.append("         AND std.goods_is_serial != '1' ");
    sql.append("         AND st.stock_trans_id = ? ");
    sql.append("UNION ALL ");
    sql.append("SELECT   st.stock_trans_id stockTransId, ");
    sql.append("         to_char(st.stock_trans_date,'dd/mm/yyyy') stockTransDate, ");
    sql.append("         st.stock_trans_status stockTransStatus, ");
    sql.append("         st.cust_id custId, ");
    sql.append("         st.owner_id ownerId, ");
    sql.append("         st.owner_type ownerType, ");
    sql.append("         std.goods_id goodsId, ");
    sql.append("         std.goods_code goodsCode, ");
    sql.append("         std.goods_name goodsName, ");
    sql.append("         std.goods_is_serial goodsIsSerial, ");
    sql.append("         std.goods_is_serial_strip goodsIsSerialStrip, ");
    sql.append("         std.goods_state goodsState, ");
    sql.append("         '' barcode, ");
    sql.append("         '' cellCode ");
    sql.append("  FROM   stock_trans st, stock_trans_detail std, stock_trans_serial sts ");
    sql.append(" WHERE       st.stock_trans_id = sts.stock_trans_id ");
    sql.append("         AND std.stock_trans_detail_id = sts.stock_trans_detail_id ");
    sql.append("         AND st.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') ");
    sql.append("         AND st.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 ");
    sql.append("         AND std.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') ");
    sql.append("         AND std.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 ");
    sql.append("         AND sts.stock_trans_date >= TO_DATE (?, 'dd/mm/yyyy') ");
    sql.append("         AND sts.stock_trans_date < TO_DATE (?, 'dd/mm/yyyy') + 1 ");
    sql.append("         AND st.stock_trans_id = ? ");
    //        
    lstParams.add(stockTransInforDTO.getFromDate());
    lstParams.add(stockTransInforDTO.getToDate());
    lstParams.add(stockTransInforDTO.getFromDate());
    lstParams.add(stockTransInforDTO.getToDate());
    lstParams.add(stockTransInforDTO.getStockTransId());
    //
    lstParams.add(stockTransInforDTO.getFromDate());
    lstParams.add(stockTransInforDTO.getToDate());
    lstParams.add(stockTransInforDTO.getFromDate());
    lstParams.add(stockTransInforDTO.getToDate());
    lstParams.add(stockTransInforDTO.getFromDate());
    lstParams.add(stockTransInforDTO.getToDate());
    lstParams.add(stockTransInforDTO.getStockTransId());
    //
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockTransInforDTO.class));
    //
    query.addScalar("stockTransId", new StringType());
    query.addScalar("stockTransDate", new StringType());
    query.addScalar("stockTransStatus", new StringType());
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsIsSerial", new StringType());
    query.addScalar("goodsIsSerialStrip", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("barcode", new StringType());
    query.addScalar("cellCode", new StringType());
    //
    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.StockTransDAO.java

License:Open Source License

public List<CardStockInforDTO> getListCardStockInfor(List<CardStockInforDTO> lstCardStockInfor) {
    List<CardStockInforDTO> lstCardStockInforDTO = new ArrayList();
    StringBuffer sql = new StringBuffer();
    List lstParams = null;/*from ww  w.j av  a2 s  .  c  o  m*/
    //hang co serial
    sql.append("  SELECT   stock_trans_id stocktransid,");
    sql.append("           goods_id goodsid,");
    sql.append("           goods_code goodscode,");
    sql.append("           goods_name goodsname,");
    sql.append("           goods_state goodsstate,");
    sql.append("           stock_trans_date stocktransdate,");
    sql.append("           stock_trans_type stocktranstype,");
    sql.append("           stock_trans_code stocktranscode,");
    sql.append("           dept_id deptid,");
    sql.append("           trans_user_id transuserid,");
    sql.append("           notes notes,");
    sql.append("           amount_remain amountremain,");
    sql.append("           amount_import amountimport,");
    sql.append("           amount_export amountexport  ");
    sql.append("    FROM   (  SELECT   NULL stock_trans_id,");
    sql.append("                       sdr.goods_id,");
    sql.append("                       sdr.goods_code,");
    sql.append("                       sdr.goods_name,");
    sql.append("                       sdr.goods_state,");
    sql.append("                       remain_date stock_trans_date,");
    sql.append("                       NULL stock_trans_type,");
    sql.append("                       NULL stock_trans_code,");
    sql.append("                       NULL dept_id,");
    sql.append("                       NULL trans_user_id,");
    sql.append("                       NULL notes,");
    sql.append("                       NVL (SUM (amount), 0) amount_remain,");
    sql.append("                       0 amount_import,");
    sql.append("                       0 amount_export ");
    sql.append("                FROM   stock_daily_remain sdr");
    sql.append("               WHERE       remain_date = TO_DATE (?, 'dd/mm/yyyy') - 1");
    sql.append("                       AND cust_id = ?");
    sql.append("                       AND owner_type = ?");
    sql.append("                       AND owner_id = ?");
    sql.append("                       AND (sdr.goods_id = ? AND sdr.goods_state = ?)");
    //sql.append("                            OR (sdr.goods_id = ? AND sdr.goods_state = '2'))");
    sql.append("            GROUP BY   remain_date,");
    sql.append("                       sdr.goods_id,");
    sql.append("                       sdr.goods_code,");
    sql.append("                       sdr.goods_name,");
    sql.append("                       sdr.goods_state");
    sql.append("            UNION ALL");
    sql.append("              SELECT   st.stock_trans_id,");
    sql.append("                       std.goods_id,");
    sql.append("                       std.goods_code,");
    sql.append("                       std.goods_name,");
    sql.append("                       std.goods_state,");
    sql.append("                       st.stock_trans_date,");
    sql.append("                       st.stock_trans_type,");
    sql.append("                       st.stock_trans_code,");
    sql.append("                       st.dept_id,");
    sql.append("                       st.trans_user_id,");
    sql.append("                       st.notes,");
    sql.append("                       0 amount_remain,");
    sql.append("                       (CASE");
    sql.append("                            WHEN (st.stock_trans_type = '1' OR st.stock_trans_type = '4')");
    sql.append("                            THEN");
    sql.append("                                SUM (std.amount_real)");
    sql.append("                            ELSE");
    sql.append("                                0");
    sql.append("                        END)");
    sql.append("                           amount_import,");
    sql.append("                       (CASE");
    sql.append("                            WHEN (st.stock_trans_type = '2' OR st.stock_trans_type = '5')");
    sql.append("                            THEN");
    sql.append("                                SUM (std.amount_real)");
    sql.append("                            ELSE");
    sql.append("                                0");
    sql.append("                        END)");
    sql.append("                           amount_export ");
    sql.append("                FROM   stock_trans st,");
    sql.append("                       stock_trans_detail std");
    sql.append("               WHERE       st.stock_trans_id = std.stock_trans_id ");
    sql.append("                       AND st.stock_trans_status = '1'");
    sql.append("                       AND std.amount_real > 0 ");
    sql.append("                       AND st.cust_id = ?");
    sql.append("                       AND st.owner_type = ?");
    sql.append("                       AND st.owner_id = ?");
    sql.append("                       AND st.stock_trans_date >=");
    sql.append("                              TO_DATE (?, 'dd/mm/yyyy')");
    sql.append("                       AND st.stock_trans_date <");
    sql.append("                              TO_DATE (?, 'dd/mm/yyyy') + 1");
    sql.append("                       AND std.stock_trans_date >=");
    sql.append("                              TO_DATE (?, 'dd/mm/yyyy')");
    sql.append("                       AND std.stock_trans_date <");
    sql.append("                              TO_DATE (?, 'dd/mm/yyyy') + 1");
    sql.append("                       AND  (std.goods_id = ? AND std.goods_state = ?)");
    // sql.append("                            OR (std.goods_id = ? AND std.goods_state = '2'))");
    sql.append("            GROUP BY   st.cust_id,");
    sql.append("                       st.owner_type,");
    sql.append("                       st.owner_id,");
    sql.append("                       st.stock_trans_id,");
    sql.append("                       std.goods_id,");
    sql.append("                       std.goods_code,");
    sql.append("                       std.goods_name,");
    sql.append("                       std.goods_state,");
    sql.append("                       st.stock_trans_date,");
    sql.append("                       st.stock_trans_code,");
    sql.append("                       st.dept_id,");
    sql.append("                       st.trans_user_id,");
    sql.append("                       st.notes,");
    sql.append("                       st.stock_trans_type)");
    sql.append("ORDER BY   goods_id,");
    sql.append("           goods_state,");
    sql.append("           stock_trans_date,");
    sql.append("           stock_trans_id");

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

    for (CardStockInforDTO cardStockInforDTO : lstCardStockInfor) {
        lstParams = new ArrayList();
        //
        lstParams.add(cardStockInforDTO.getFromDate());
        lstParams.add(cardStockInforDTO.getCustId());
        lstParams.add(cardStockInforDTO.getOwnerType());
        lstParams.add(cardStockInforDTO.getOwnerId());
        lstParams.add(cardStockInforDTO.getGoodsId());
        lstParams.add(cardStockInforDTO.getGoodsState());
        //hang co serial
        lstParams.add(cardStockInforDTO.getCustId());
        lstParams.add(cardStockInforDTO.getOwnerType());
        lstParams.add(cardStockInforDTO.getOwnerId());
        lstParams.add(cardStockInforDTO.getFromDate());
        lstParams.add(cardStockInforDTO.getToDate());
        lstParams.add(cardStockInforDTO.getFromDate());
        lstParams.add(cardStockInforDTO.getToDate());
        lstParams.add(cardStockInforDTO.getGoodsId());
        lstParams.add(cardStockInforDTO.getGoodsState());

        //
        //
        query.addScalar("stockTransId", new StringType());
        query.addScalar("goodsId", new StringType());
        query.addScalar("goodsCode", new StringType());
        query.addScalar("goodsName", new StringType());
        query.addScalar("goodsState", new StringType());
        query.addScalar("stockTransDate", new StringType());
        query.addScalar("stockTransType", new StringType());
        query.addScalar("stockTransCode", new StringType());
        query.addScalar("deptId", new StringType());
        query.addScalar("transUserId", new StringType());
        query.addScalar("notes", new StringType());
        query.addScalar("amountRemain", new StringType());
        query.addScalar("amountImport", new StringType());
        query.addScalar("amountExport", new StringType());
        //
        for (int i = 0; i < lstParams.size(); i++) {
            query.setParameter(i, lstParams.get(i));
        }
        lstCardStockInforDTO.addAll(query.list());
    }
    return lstCardStockInforDTO;
}