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

License:Open Source License

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

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

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

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

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

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

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

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

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

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

    return lstInvenResult;
}

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

License:Open Source License

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

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

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

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

License:Open Source License

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

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

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

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

License:Open Source License

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

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

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

License:Open Source License

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

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

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

}

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

License:Open Source License

public List getListErrorSerialInventory(String inventoryCode) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();

    sql.append("SELECT  ");
    sql.append("         a.ora_err_mesg$ addInfor,");
    sql.append("         a.cust_id custId,");
    sql.append("         a.stock_id ownerId,");
    sql.append("         a.goods_id goodsId,");
    sql.append("         a.goods_state goodsState,");
    sql.append("         a.status status,");
    sql.append("         a.from_serial fromSerial,");
    sql.append("         a.to_serial toSerial,");
    sql.append("         a.quantity quantity,");
    sql.append("         a.barcode barcode,");
    sql.append("         a.note notes,");
    sql.append("         a.cell_code cellCode,");
    sql.append("         a.goods_code goodsCode,");
    sql.append("         a.goods_name goodsName ");
    sql.append("  FROM   err$_serial_inventory a");
    sql.append("  ");
    if (!DataUtil.isStringNullOrEmpty(inventoryCode)) {
        sql.append("  WHERE   a.inventory_code = ? ");
        lstParams.add(inventoryCode);/*from w  w w .  j  a v a  2s .c  o  m*/
    }
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("status", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("quantity", new StringType());
    query.addScalar("notes", new StringType());
    query.addScalar("addInfor", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("barcode", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    return query.list();
}

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  w ww .ja  v  a  2s.  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 ");

    // 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();
    //        /*w w  w.  ja  va2s  .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 ww . ja  v a2  s  .  co 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());
    }/* ww w  . j  a va2s. 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();
}