Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

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

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

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

License:Open Source License

public List<StockTransSerialDTO> getListStockTransSerialByOrderId(String orderId) {
    StringBuilder sql = new StringBuilder();
    List lstParams = new ArrayList();

    sql.append("  SELECT    sts.stock_trans_serial_id stockTransSerialId, ");
    sql.append("            sts.stock_trans_id stockTransId,");
    sql.append("            sts.stock_trans_detail_id stockTransDetailId,");
    sql.append("            sts.goods_id goodsId,");
    sql.append("            sts.goods_code goodsCode,");
    sql.append("            sts.goods_name goodsName,");
    sql.append("            sts.goods_state goodsState,");
    sql.append("            sts.from_serial fromSerial,");
    sql.append("            sts.to_serial toSerial,");
    sql.append("            sts.amount_order amountOrder,");
    sql.append("            sts.amount_real amountReal");
    sql.append("    FROM   stock_trans st,");
    sql.append("           stock_trans_serial sts");
    sql.append("   WHERE       st.stock_trans_id = sts.stock_trans_id");
    sql.append("           AND st.stock_trans_status <> 0");
    sql.append("           AND st.order_id_list = ?");

    lstParams.add(orderId);/*from w  w  w.  j a v a2  s.  c o  m*/

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

    query.addScalar("stockTransSerialId", new StringType());
    query.addScalar("stockTransId", new StringType());
    query.addScalar("stockTransDetailId", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("amountOrder", new StringType());
    query.addScalar("amountReal", new StringType());

    //
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }
    return query.list();
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.provider.ContentProviderDAO.java

License:Open Source License

public List<HashMap<String, String>> getTreeProvider(Long startWith) {
    List lst;/*w  w  w.j a  v  a2 s.  com*/
    SQLQuery sqlQuery = getSession().createSQLQuery(
            "select content_provider_id, cp_code, cp_name from CONTENT_PROVIDER CONNECT BY PRIOR content_provider_id = parent start with content_provider_id = :cpId");
    sqlQuery.setParameter("cpId", startWith);
    sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    lst = sqlQuery.list();
    DAOFactory.commitCurrentSessions();
    return lst;
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

License:Open Source License

public List<Transaction> getTransEVN(Date toDate, Date fromDate, String bankCode) {
    List lst = new ArrayList();
    try {/*from www  . j ava  2  s . c  om*/
        String sql = " bp.BANK_CODE bankCode, bp.billing_code billingCode, to_char(bp.request_date, 'dd/MM/yyyy hh24:mi:ss')  requestDate, bs.order_id orderId, bs.order_amount amount";
        sql += " from trans_bankplus bp ";
        sql += " left join (select aa.*, bb.order_id, bb.amount order_amount, cc.num_order from trans_bankplus aa ";
        sql += " inner join billing_service bb on aa.trans_bankplus_id = bb.trans_bankplus_id";
        sql += " inner join (select trans_bankplus_id, count(*) num_order from billing_service group by trans_bankplus_id) cc";
        sql += " on aa.trans_bankplus_id = cc.trans_bankplus_id where aa.error_code = '00') bs";
        sql += " on bp.original_request_id = bs.request_id where bp.process_code = '300001' and bp.original_request_id is not null";
        sql += " and bp.error_code = '00' and bp.cp_code = 'EVNHCM' ";
        sql += " and bp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') and bp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss')";

        if (bankCode != null && !bankCode.equals("")) {
            sql += " and upper(bp.bank_code) = :bankCode";
        }
        sql += " order by bp.request_date asc";

        SQLQuery query = getSession().createSQLQuery(sql);

        query.setResultTransformer(Transformers.aliasToBean(Transaction.class));
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

        query.setParameter("fromDate", sdf.format(fromDate));
        query.setParameter("toDate", sdf.format(toDate));
        if (bankCode != null && !bankCode.equals("")) {
            query.setParameter("bankCode", bankCode);
        }
        log.info(query.toString());
        lst = query.list();
    } catch (Exception ex) {
        log.error("getTrans: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

License:Open Source License

public List<Map<String, Object>> getTransEVN2(Date toDate, Date fromDate, String bankCode) {
    // List lst = new ArrayList();
    List<Map<String, Object>> lst = new ArrayList();
    try {/*  w w w. j a v a  2 s . c  om*/
        String sql = " select bp.BANK_CODE bankCode, bp.billing_code billingCode, to_char(bp.request_date, 'dd/MM/yyyy hh24:mi:ss')  requestDate, bs.order_id orderId, bs.order_amount amount";
        sql += " from trans_bankplus bp ";
        sql += " left join (select aa.*, bb.order_id, bb.amount order_amount, cc.num_order from trans_bankplus aa ";
        sql += " inner join billing_service bb on aa.trans_bankplus_id = bb.trans_bankplus_id";
        sql += " inner join (select trans_bankplus_id, count(*) num_order from billing_service group by trans_bankplus_id) cc";
        sql += " on aa.trans_bankplus_id = cc.trans_bankplus_id where aa.error_code = '00') bs";
        sql += " on bp.original_request_id = bs.request_id where bp.process_code = '300001' and bp.original_request_id is not null";
        sql += " and bp.error_code = '00' and bp.cp_code = 'EVNHCM' ";
        sql += " and bp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') and bp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss')";

        if (bankCode != null && !bankCode.equals("")) {
            sql += " and upper(bp.bank_code) = :bankCode";
        }
        sql += " order by bp.request_date asc";

        SQLQuery query = getSession().createSQLQuery(sql);
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        //query.setResultTransformer(Transformers.aliasToBean(Transaction.class));

        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

        query.setParameter("fromDate", sdf.format(fromDate));
        query.setParameter("toDate", sdf.format(toDate));
        //            
        //            query.setParameter("fromDate", fromDate);
        //            query.setParameter("toDate", toDate);
        if (bankCode != null && !bankCode.equals("")) {
            query.setParameter("bankCode", bankCode);
        }
        log.info(query.toString());
        lst = query.list();
    } catch (Exception ex) {
        log.error("getTrans: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

License:Open Source License

public List<Map<String, Object>> getReportTotal(Date fromDate, Date toDate, String by, String bankCode,
        ArrayList<Long> lstCpId) {
    List<Map<String, Object>> lst = new ArrayList();
    try {//from  ww w  .ja  v  a  2 s. c  o  m
        //            String where = " where cp.trans_status = 2 and (cp.trans_type = 0 or cp.trans_type = 2) ";
        String where = " where (bp.error_code = '00' or bp.correct_code = '00') and (cp.trans_type = 0 or cp.trans_type = 2) ";
        where += " and cp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') and cp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss')";
        where += " and cp.content_provider_id in (:cpId) ";
        if (bankCode != null && !bankCode.equals("")) {
            where += " and upper(bp.bank_code) = :bankCode ";
        }
        String groupBy = "";
        String orderBy = "";
        String sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, ";
        if (by.equals("MON")) {
            //Chuyen ngay thang thanh thang
            Calendar cal = Calendar.getInstance();
            cal.setTime(fromDate);
            Calendar calFrom = Calendar.getInstance();
            calFrom.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMinimum(Calendar.DAY_OF_MONTH),
                    0, 0, 0);

            cal.setTime(toDate);
            Calendar calTo = Calendar.getInstance();
            calTo.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMaximum(Calendar.DAY_OF_MONTH),
                    23, 59, 59);

            fromDate = calFrom.getTime();
            toDate = calTo.getTime();

            sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, ";
            groupBy = " group by  to_char(cp.request_date, 'MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name ";
            orderBy = " order by to_date(monDay, 'MM/yyyy') desc";
        } else {
            sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'dd/MM/yyyy') monDay, ";
            groupBy = " group by to_char(cp.request_date, 'dd/MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name ";
            orderBy = " order by to_date(monDay, 'dd/MM/yyyy') desc";
        }
        sql += " SUM(CASE cp.trans_type WHEN 0 THEN 1 ELSE 0 END) totalPay,";
        sql += " SUM(CASE cp.trans_type WHEN 2 THEN 1 ELSE 0 END) totalRefund,";
        sql += " SUM(CASE cp.trans_type WHEN 0 THEN cp.AMOUNT ELSE 0 END) payAmount,";
        sql += " SUM(CASE cp.trans_type WHEN 2 THEN cp.AMOUNT ELSE 0 END) refundAmount,";
        sql += " SUM(nvl(bp.fee,0)) totalFee,";
        sql += " p.cp_code cpCode, p.cp_name cpName ";
        sql += " from trans_cp cp left join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id";
        sql += " left join content_provider p on cp.content_provider_id = p.content_provider_id ";
        sql += where;
        sql += groupBy;
        sql += orderBy;
        log.debug(sql);
        SQLQuery query = getSession().createSQLQuery(sql);
        //        query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class));
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

        query.setParameter("fromDate", sdf.format(fromDate));
        query.setParameter("toDate", sdf.format(toDate));

        //            query.setParameter("fromDate", fromDate);
        //            query.setParameter("toDate", toDate);
        query.setParameterList("cpId", lstCpId);
        if (bankCode != null && !bankCode.equals("")) {
            query.setParameter("bankCode", bankCode.toUpperCase());
        }
        lst = query.list();
    } catch (Exception ex) {
        log.error("getReportTotal: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

License:Open Source License

public List<Map<String, Object>> getReportTotalEVN(Date fromDate, Date toDate, String by, String bankCode,
        Long cpId) {/*from   w ww.j a va 2 s  .  c  o m*/
    List<Map<String, Object>> lst = new ArrayList();
    try {

        String where = " where cp.trans_status = 2 and (cp.trans_type = 0 or cp.trans_type = 2) ";
        where += " and cp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') and cp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss')";
        //            where += " and cp.request_date >= :fromDate and cp.request_date <= :toDate";
        where += " and cp.content_provider_id = :cpId ";
        where += " and bp.process_code = '300001' ";
        where += " and bp.error_code = '00'  ";

        if (bankCode != null && !bankCode.equals("")) {
            where += " and upper(bp.bank_code) = :bankCode ";
        }
        String groupBy = " group by  to_char(cp.request_date, 'dd/MM/yyyy'), bp.bank_code";
        String orderBy = "";
        String sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, sum(bs.num_order) totalBill, ";
        if (by.equals("MON")) {
            //Chuyen ngay thang thanh thang
            Calendar cal = Calendar.getInstance();
            cal.setTime(fromDate);
            Calendar calFrom = Calendar.getInstance();
            calFrom.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMinimum(Calendar.DAY_OF_MONTH),
                    0, 0, 0);

            cal.setTime(toDate);
            Calendar calTo = Calendar.getInstance();
            calTo.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMaximum(Calendar.DAY_OF_MONTH),
                    23, 59, 59);

            fromDate = calFrom.getTime();
            toDate = calTo.getTime();

            sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, sum(bs.num_order) totalBill, ";
            groupBy = " group by  to_char(cp.request_date, 'MM/yyyy'), bp.bank_code";
            orderBy = " order by to_date(monDay, 'MM/yyyy') desc";
        } else {
            sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'dd/MM/yyyy') monDay, sum(bs.num_order) totalBill, ";
            groupBy = " group by  to_char(cp.request_date, 'dd/MM/yyyy'), bp.bank_code";
            orderBy = " order by to_date(monDay, 'dd/MM/yyyy') desc";
        }
        sql += " SUM(CASE cp.trans_type WHEN 0 THEN 1 ELSE 0 END) totalPay,";
        sql += " SUM(CASE cp.trans_type WHEN 2 THEN 1 ELSE 0 END) totalRefund,";
        sql += " SUM(CASE cp.trans_type WHEN 0 THEN bp.amount ELSE 0 END) payAmount,";
        sql += " SUM(CASE cp.trans_type WHEN 2 THEN bp.amount ELSE 0 END) refundAmount";

        sql += " from trans_cp cp left join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id";
        sql += " left join (select aa.*, cc.num_order from trans_bankplus aa ";
        //sql += " inner join billing_service bb on aa.trans_bankplus_id = bb.trans_bankplus_id";
        sql += " inner join (select trans_bankplus_id, count(*) num_order from billing_service group by trans_bankplus_id) cc";
        sql += " on aa.trans_bankplus_id = cc.trans_bankplus_id where aa.error_code = '00') bs";
        sql += " on bp.original_request_id = bs.request_id ";

        sql += where;
        sql += groupBy;
        sql += orderBy;
        log.debug(sql);
        SQLQuery query = getSession().createSQLQuery(sql);
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

        query.setParameter("fromDate", sdf.format(fromDate));
        query.setParameter("toDate", sdf.format(toDate));
        //            query.setParameter("fromDate", fromDate);
        //            query.setParameter("toDate", toDate);
        query.setParameter("cpId", cpId);

        if (bankCode != null && !bankCode.equals("")) {
            query.setParameter("bankCode", bankCode.toUpperCase());
        }
        lst = query.list();
    } catch (Exception ex) {
        log.error("getReportTotal: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

License:Open Source License

public List<Map<String, Object>> getReportRefund(Date fromDate, Date toDate, String by, String bankCode,
        ArrayList<Long> lstCpId) {
    List<Map<String, Object>> lst = new ArrayList();
    try {//from   w  w  w.j  av  a 2  s.  c  o m
        String where = " where cp.trans_type = 2 ";
        where += " and cp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') and cp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss')";
        //            where += " and cp.request_date >= :fromDate and cp.request_date <= :toDate";
        where += " and cp.content_provider_id in (:cpId) ";
        if (bankCode != null && !bankCode.equals("")) {
            where += " and upper(bp.bank_code) = :bankCode ";
        }
        String groupBy = "";
        String orderBy = "";
        String sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, ";
        if (by.equals("MON")) {
            //Chuyen ngay thang thanh thang
            Calendar cal = Calendar.getInstance();
            cal.setTime(fromDate);
            Calendar calFrom = Calendar.getInstance();
            calFrom.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMinimum(Calendar.DAY_OF_MONTH),
                    0, 0, 0);

            cal.setTime(toDate);
            Calendar calTo = Calendar.getInstance();
            calTo.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.getMaximum(Calendar.DAY_OF_MONTH),
                    23, 59, 59);

            fromDate = calFrom.getTime();
            toDate = calTo.getTime();

            sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'MM/yyyy') monDay, ";
            groupBy = " group by  to_char(cp.request_date, 'MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name";
            orderBy = " order by to_date(monDay, 'MM/yyyy') desc";
        } else {
            sql = "select bp.bank_code bankCode, to_char(cp.request_date, 'dd/MM/yyyy') monDay, ";
            groupBy = " group by  to_char(cp.request_date, 'dd/MM/yyyy'), bp.bank_code, p.cp_code, p.cp_name";
            orderBy = " order by to_date(monDay, 'dd/MM/yyyy') desc";
        }
        sql += " SUM(CASE cp.trans_status WHEN '0' THEN 1 ELSE 0 END) totalpending,";
        sql += " SUM(CASE cp.trans_status WHEN '2' THEN 1 ELSE 0 END) totalsuccess,";
        sql += " SUM(CASE cp.trans_status WHEN '4' THEN 1 ELSE 0 END) totalfailure,";
        sql += " SUM(CASE cp.trans_status WHEN '0' THEN cp.amount ELSE 0 END) amountpending,";
        sql += " SUM(CASE cp.trans_status WHEN '2' THEN cp.amount ELSE 0 END) amountsuccess,";
        sql += " SUM(CASE cp.trans_status WHEN '4' THEN cp.amount ELSE 0 END) amountfailure,";
        sql += " p.cp_code cpCode, p.cp_name cpName ";
        //            sql += " from trans_cp cp left join trans_bankplus bp on cp.trans_cp_id = bp.trans_cp_id";
        sql += " from trans_cp cp";
        sql += " join trans_cp cp2 on cp.original_trans_id = cp2.trans_id ";
        sql += " left join trans_bankplus bp on cp2.trans_cp_id = bp.trans_cp_id ";
        sql += " left join content_provider p on cp.content_provider_id = p.content_provider_id ";
        sql += where;
        sql += groupBy;
        sql += orderBy;
        log.debug(sql);
        SQLQuery query = getSession().createSQLQuery(sql);
        //        query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class));
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

        query.setParameter("fromDate", sdf.format(fromDate));
        query.setParameter("toDate", sdf.format(toDate));
        //            query.setParameter("fromDate", fromDate);
        //            query.setParameter("toDate", toDate);
        query.setParameterList("cpId", lstCpId);
        if (bankCode != null && !bankCode.equals("")) {
            query.setParameter("bankCode", bankCode.toUpperCase());
        }
        lst = query.list();
    } catch (Exception ex) {
        log.error("getReportRefund: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

License:Open Source License

public List<Map<String, Object>> getReportEVNNPCBackup(Date fromDate, Date toDate, String status,
        ArrayList<Long> lstCpId) {
    List<Map<String, Object>> lst = new ArrayList();
    log.debug("status = " + status);
    try {/*from   ww w  .  j  a  v  a 2  s  .  co  m*/

        String where = "where bp.request_date >= :fromDate and bp.request_date <= :toDate";
        where += " and cp.content_provider_id in (:cpId) ";
        if (!status.equals("NONE")) {
            where += " and cp.TRANS_STATUS = :transStatus ";
        }
        where += " and bp.process_code='300001'";
        where += " and orgbp.process_code='300000'";

        String groupBy = "";
        String orderBy = "";
        String sql = "select to_char(cp.request_date, 'dd/MM/yyyy') reqDate,bs.order_id orderId,bp.BILLING_CODE billingCode,bp.CUSTOMER_NAME customerName, ";
        sql += "bs.CUST_ADDRESS custAddress,bs.NUMBER_GCS numberGCS,bp.AMOUNT amount,bs.maCN maCN,cp.TRANS_ID transId,bp.MSISDN msisdn,bp.BANK_CODE bankCode,bp.TRANS_STATUS transStatus,cp.TRANS_STATUS cpTranstatus";

        orderBy = " order by bs.NUMBER_GCS, to_date(reqDate, 'dd/MM/yyyy') desc";
        sql += " from trans_bankplus bp";
        sql += " join trans_bankplus orgbp on bp.original_request_id=orgbp.request_id";
        sql += " join trans_cp cp on cp.TRANS_CP_ID = bp.TRANS_CP_ID ";
        sql += " join billing_service bs on bs.TRANS_BANKPLUS_ID = orgbp.TRANS_BANKPLUS_ID ";
        sql += where;
        sql += orderBy;
        log.debug(sql);
        SQLQuery query = getSession().createSQLQuery(sql);
        //        query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class));
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        query.setParameter("fromDate", fromDate);
        query.setParameter("toDate", toDate);
        query.setParameterList("cpId", lstCpId);
        if (!status.equals("NONE")) {
            query.setParameter("transStatus", status);
        }

        lst = query.list();
    } catch (Exception ex) {
        log.error("getReportRefund: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}

From source file:com.viettel.ttbankplus.servicegw.hibernate.dao.transaction.TransCPDAO.java

License:Open Source License

public List<Map<String, Object>> getReportEVNNPC(Date fromDate, Date toDate, String status,
        ArrayList<Long> lstCpId) {
    List<Map<String, Object>> lst = new ArrayList();
    log.debug("status = " + status);
    try {/*from  w w w.j  a v  a2 s.  co  m*/

        String sqlPart1 = "select xxxxxx.reqDate,xxxxxx.billingCode,\n" + "yyyyyy.customerName, \n"
                + "xxxxxx.amount,xxxxxx.transId,xxxxxx.msisdn,xxxxxx.bankCode,\n"
                + "xxxxxx.transStatus,xxxxxx.cpTranstatus,yyyyyy.custAddress,yyyyyy.numberGCS,yyyyyy.maCN,yyyyyy.orderId,xxxxxx.original_request_id,xxxxxx.request_id\n"
                + "from (select to_char(cp.request_date, 'dd/MM/yyyy') reqDate,UPPER(bp.BILLING_CODE) billingCode,\n"
                + "bp.CUSTOMER_NAME customerName, \n"
                + "bp.AMOUNT amount,cp.TRANS_ID transId,bp.MSISDN msisdn,bp.BANK_CODE bankCode,\n" + ""
                + "(case nvl(bp.error_code,'null')\n" + "when '00' then TO_CHAR(2)\n" + "when '32' then \n"
                + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end)\n" + "when 'null' then\n"
                + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "when 'null' then\n"
                + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "else TO_CHAR(4)\n"
                + "end\n" + ")" + " transStatus," + "" + "(case nvl(bp.error_code,'null')\n"
                + "when '00' then TO_CHAR(2)\n" + "when '32' then \n" + "(case nvl(bp.correct_code,'null')\n"
                + "when '00' then TO_CHAR(2)\n" + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end)\n"
                + "when 'null' then\n" + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "when 'null' then\n"
                + "(case nvl(bp.correct_code,'null')\n" + "when '00' then TO_CHAR(2)\n"
                + "when 'null' then TO_CHAR(1)\n" + "else TO_CHAR(4) end\n" + ")\n" + "else TO_CHAR(4)\n"
                + "end\n" + ")"
                + " cpTranstatus,bp.original_request_id original_request_id, bp.request_id request_id\n"
                + "from trans_bankplus bp \n" + "join trans_cp cp on cp.TRANS_CP_ID = bp.TRANS_CP_ID \n"
                + "where bp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') \n"
                + "and bp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss') \n"
                + " and cp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') \n"
                + "and cp.request_date <= to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss') \n"
                + "and cp.content_provider_id in (:cpId) \n";

        String sqlPart2 = "and bp.process_code='300001'\n" + ") xxxxxx\n" + "join\n"
                + "(select bs.CUSTOMER_NAME customerName, bs.CUST_ADDRESS custAddress,bs.NUMBER_GCS numberGCS ,bs.maCN maCN,bs.order_id orderId,orgbp.request_id request_id\n"
                + "from trans_bankplus orgbp\n" + "join  billing_service bs\n"
                + "on bs.TRANS_BANKPLUS_ID = orgbp.TRANS_BANKPLUS_ID\n"
                + "where orgbp.request_date >= to_date(:fromDate, 'dd/MM/yyyy hh24:mi:ss') \n"
                + "and orgbp.request_date <=  to_date(:toDate, 'dd/MM/yyyy hh24:mi:ss') \n"
                + "and orgbp.process_code='300000') yyyyyy\n"
                + "on xxxxxx.original_request_id = yyyyyy.request_id"
                + " order by yyyyyy.numberGCS, to_date(xxxxxx.reqDate, 'dd/MM/yyyy') desc";
        String sqlNew = sqlPart1;
        if (!status.equals("NONE")) {
            if (status.equals("2")) {
                sqlNew += " and (bp.ERROR_CODE = '00' OR bp.CORRECT_CODE = '00') ";
            } else if (status.equals("1")) {
                sqlNew += " and (bp.ERROR_CODE = '32' OR bp.ERROR_CODE is null) ";
                sqlNew += " and bp.CORRECT_CODE is null ";
            } else if (status.equals("0")) {
                sqlNew += " and cp.TRANS_STATUS = 0 ";
            } else if (status.equals("3")) {
                sqlNew += " and cp.TRANS_STATUS = 3 ";
            } else if (status.equals("4")) {
                sqlNew += " and ((bp.ERROR_CODE = '32' or bp.ERROR_CODE is null) and bp.CORRECT_CODE = '23')"
                        + " and bp.ERROR_CODE <> '00' "
                        + " and (bp.CORRECT_CODE is null or bp.CORRECT_CODE = '23' ";
            }
            //                sqlNew += " and cp.TRANS_STATUS = :transStatus ";
        }
        sqlNew += sqlPart2;
        //            String where = "where bp.request_date >= :fromDate and bp.request_date <= :toDate";
        //            where += " and cp.content_provider_id in (:cpId) ";
        //            if (!status.equals("NONE")) {
        //                where += " and cp.TRANS_STATUS = :transStatus ";
        //            }
        //            where += " and bp.process_code='300001'";
        //            where += " and orgbp.process_code='300000'";
        //
        //            String groupBy = "";
        //            String orderBy = "";
        //            String sql = "select to_char(cp.request_date, 'dd/MM/yyyy') reqDate,bs.order_id orderId,bp.BILLING_CODE billingCode,bp.CUSTOMER_NAME customerName, ";
        //            sql += "bs.CUST_ADDRESS custAddress,bs.NUMBER_GCS numberGCS,bp.AMOUNT amount,bs.maCN maCN,cp.TRANS_ID transId,bp.MSISDN msisdn,bp.BANK_CODE bankCode,bp.TRANS_STATUS transStatus,cp.TRANS_STATUS cpTranstatus";
        //
        //            orderBy = " order by bs.NUMBER_GCS, to_date(reqDate, 'dd/MM/yyyy') desc";
        //            sql += " from trans_bankplus bp";
        //            sql += " join trans_bankplus orgbp on bp.original_request_id=orgbp.request_id";
        //            sql += " join trans_cp cp on cp.TRANS_CP_ID = bp.TRANS_CP_ID ";
        //            sql += " join billing_service bs on bs.TRANS_BANKPLUS_ID = orgbp.TRANS_BANKPLUS_ID ";
        //            sql += where;
        //            sql += orderBy;
        log.debug(sqlNew);
        SQLQuery query = getSession().createSQLQuery(sqlNew);
        //        query.setResultTransformer(Transformers.aliasToBean(ReportTotal.class));
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        query.setParameter("fromDate", fromDate);
        query.setParameter("toDate", toDate);
        query.setParameterList("cpId", lstCpId);
        //            if (!status.equals("NONE")) {
        //                
        //                query.setParameter("transStatus", status);
        //            }

        lst = query.list();
    } catch (Exception ex) {
        log.error("getReportRefund: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}