Example usage for org.hibernate.transform Transformers aliasToBean

List of usage examples for org.hibernate.transform Transformers aliasToBean

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.transform.*
  5. Transformers
  6. aliasToBean

Introduction

In this page you can find the example usage for org.hibernate.transform Transformers aliasToBean.

Prototype

public static ResultTransformer aliasToBean(Class target)

Source Link

Document

Creates a resulttransformer that will inject aliased values into instances of Class via property methods or fields.

Usage

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

License:Open Source License

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

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

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

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

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

License:Open Source License

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

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

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

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

License:Open Source License

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

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

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

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

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

License:Open Source License

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

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

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

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

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

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

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

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

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

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

    return lstInvenResult;
}

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

License:Open Source License

public List<MapStaffGoodsDTO> getListStaffByGoods(String codeList, String custId) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();
    sql.append(" SELECT  DISTINCT m.staff_id staffId,");
    sql.append("         m.staff_code staffCode,");
    sql.append("         m.staff_name staffName,");
    sql.append("         m.staff_type staffType,");
    sql.append("         m.staff_email staffEmail");
    sql.append("  FROM   map_staff_goods m, goods g");
    sql.append("  WHERE m.goods_id = g.goods_id ");
    sql.append("  AND  g.status = 1");
    sql.append("  AND  m.staff_email IS NOT NULL");
    sql.append("  AND  g.cust_id = ?");
    lstParams.add(custId);/*  w w w.jav  a2  s  .co  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  w w  . j av  a 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(//from  w w  w.ja  va 2 s .  com
            " 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 ww w.jav a 2  s.  c om
            " select NVL(a.goods_id,b.goods_id) as goodsId ,NVL (a.goods_Code,b.goods_code) as goodsCode , ");
    sql.append("        NVL( a.goods_name,b.goods_name) as goodsName, ");
    sql.append("        NVL( a.goods_unit_type_name,b.goods_unit_type_name) as goodsUnitTypeName, ");
    sql.append("        NVL(quantity_state_1,0) as quantityState1,NVL(quantity_state_2,0) as quantityState2, ");
    sql.append("        (NVL(quantity_state_1,0) + NVL( quantity_state_2,0)) as total ");
    sql.append("        FROM ( ");
    sql.append(
            "        select  goods_id, goods_code,goods_name,goods_unit_type_name,sum(quantity) as quantity_state_1 from 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   ww  w  . java  2  s .co m
    }
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsSerialInforDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("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();
    //        // w  w w .ja v  a  2 s.c  o  m
    sql.append("SELECT   sg.cust_id custId,");
    sql.append("         s.stock_id ownerId,");
    sql.append("         sg.owner_type ownerType,");
    sql.append("         s.code ownerCode,");
    sql.append("         s.name ownerName,");
    sql.append("         g.goods_type goodsType,");
    sql.append("         g.goods_id goodsId,");
    sql.append("         g.code goodsCode,");
    sql.append("         g.name goodsName,");
    sql.append("         sg.goods_state goodsState,");
    sql.append("         SUM(sg.amount) amount,");
    sql.append("         SUM(sg.amount_issue) amountIssue,");
    sql.append("         g.unit_type unitType,");
    sql.append("         msg.staff_id staffId, msg.staff_code staffCode, msg.staff_name staffName ");
    sql.append("  FROM   stock_goods_total sg, goods g, stock s, map_staff_goods msg ");

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

    //
    return query.list();
}