Example usage for org.hibernate SQLQuery setResultTransformer

List of usage examples for org.hibernate SQLQuery setResultTransformer

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

Introduction

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

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

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  av  a 2  s .  c o  m
    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 {/*  w  ww  .j a v  a2 s .  c  o  m*/
        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 {//ww  w .  ja v a  2  s. c o m
        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  w ww . ja va  2 s.  c om
        //            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  ww w  . ja v a  2  s .com*/
        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   w  ww . ja  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 {//w w  w.j  a  v a 2s  . c o  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;
}

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

License:Open Source License

public List<HashMap> getReport(String sql, HashMap param) {
    List lst = new ArrayList();
    try {//  w w  w. j a v  a2 s .  c  om

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

        query.setResultTransformer(Transformers.aliasToBean(Transaction.class));
        for (Object object : param.keySet()) {
            String key = object.toString();
            Object val = param.get(key);
            if (val instanceof ArrayList) { //For select in
                query.setParameterList(key, (ArrayList) val);
            } else {
                query.setParameter(key, param.get(key));
            }
        }
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        log.info(query.toString());
        lst = query.list();
    } catch (Exception ex) {
        log.error("getReport: ", ex);
    } finally {
        DAOFactory.commitCurrentSessions();
    }
    return lst;
}

From source file:com.wso2telco.services.dep.sandbox.dao.hibernate.HibernateCustomerInfoDAO.java

License:Open Source License

@Override

public CustomerInfoDTO getProfileData(String msisdn, User user) throws Exception {
    Session session = getSession();/*from w w  w  .  j  a  v  a  2  s  .  c  o  m*/
    CustomerInfoDTO customerInfoDTO = null;

    try {
        StringBuilder hqlBuilder = new StringBuilder();

        hqlBuilder.append("SELECT msisdn,");
        hqlBuilder.append(" MAX(IF(column_name = 'title', value, NULL)) title, ");
        hqlBuilder.append(" MAX(IF(column_name = 'firstName',value,NULL)) firstName, ");
        hqlBuilder.append(" MAX(IF(column_name = 'lastName',value,NULL)) lastName,");
        hqlBuilder.append(" MAX(IF(column_name = 'dob', value, NULL)) dob, ");
        hqlBuilder.append(" MAX(IF(column_name = 'identificationType', value, NULL)) identificationType, ");
        hqlBuilder.append(" MAX(IF(column_name = 'identificationNumber',value,NULL)) identificationNumber, ");
        hqlBuilder.append(" MAX(IF(column_name = 'address', value, NULL)) address, ");
        hqlBuilder.append(" MAX(IF(column_name = 'additionalInfo',value,NULL)) additionalInfo, ");
        hqlBuilder.append(" MAX(IF(column_name = 'ownerType',value,NULL)) ownerType, ");
        hqlBuilder.append(" MAX(IF(column_name = 'accountType',value,NULL)) accountType, ");
        hqlBuilder.append(" MAX(IF(column_name = 'status', value, NULL)) status ");
        hqlBuilder.append(" FROM ");
        hqlBuilder.append(" (SELECT numbers.number AS msisdn, ");
        hqlBuilder.append(" attr.name AS column_name, ");
        hqlBuilder.append(" attval.value AS value ");
        hqlBuilder.append(" FROM ");
        hqlBuilder.append(" sbxapitypes api, sbxapiservicecalls serviceCalls, ");
        hqlBuilder.append(" sbtattributedistribution attdis, sbxattribute attr, ");
        hqlBuilder.append(" sbxattributevalue attval, user usr, numbers numbers ");
        hqlBuilder.append(" WHERE ");
        hqlBuilder.append(" api.apiname = '" + RequestType.CUSTOMERINFO.toString() + "'");
        hqlBuilder
                .append(" AND serviceCalls.service = '" + CustomerInfoRequestType.GETPROFILE.toString() + "'");
        hqlBuilder.append(" AND api.id = serviceCalls.apitypesdid ");
        hqlBuilder.append(" AND serviceCalls.sbxapiservicecallsdid= attdis.apiservicecallsdid ");
        hqlBuilder.append(" AND attdis.attributedid = attr.sbxattributedid ");
        hqlBuilder.append(" AND attval.attributedistributiondid = attdis.sbtattributedistributiondid ");
        hqlBuilder.append(" AND usr.user_name =:userName");
        hqlBuilder.append(" AND usr.id = attval.ownerdid ");
        hqlBuilder.append(" AND attval.tobject = '" + TableName.USER.toString() + "'");
        hqlBuilder.append(" AND numbers.user_id = usr.id ");
        hqlBuilder.append(" AND numbers.number =:msisdn ");
        hqlBuilder.append(") d GROUP BY msisdn");

        SQLQuery query = session.createSQLQuery(hqlBuilder.toString());
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

        query.setParameter("userName", user.getUserName());
        query.setParameter("msisdn", msisdn);

        List resultList = query.list();
        if (resultList.size() > 0) {
            Object result = resultList.get(0);
            customerInfoDTO = new CustomerInfoDTO();
            Map resultMap = (Map) result;
            customerInfoDTO.setMsisdn((String) resultMap.get("msisdn"));
            customerInfoDTO.setTitle((String) resultMap.get("title"));
            customerInfoDTO.setFirstName((String) resultMap.get("firstName"));
            customerInfoDTO.setLastName((String) resultMap.get("lastName"));
            //String dateOfBirth = (String) resultMap.get("dob");
            //if (CommonUtil.getNullOrTrimmedValue(dateOfBirth) != null) {
            //    Date date;
            //    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            //    date = dateFormat.parse(dateOfBirth);
            customerInfoDTO.setDob((String) resultMap.get("dob"));

            customerInfoDTO.setMsisdn((String) resultMap.get("msisdn"));
            customerInfoDTO.setAddress((String) resultMap.get("address"));
            customerInfoDTO.setAccountType((String) resultMap.get("accountType"));
            customerInfoDTO.setOwnerType((String) resultMap.get("ownerType"));
            customerInfoDTO.setAdditionalInfo((String) resultMap.get("additionalInfo"));
            customerInfoDTO.setStatus((String) resultMap.get("status"));
            customerInfoDTO.setIdentificationType((String) resultMap.get("identificationType"));
            customerInfoDTO.setIdentificationNumber((String) resultMap.get("identificationNumber"));
        }

    } catch (Exception ex) {
        LOG.error("###CUSTOMERINFO### Error in Get Profile Data", ex);
        throw ex;
    }

    return customerInfoDTO;
}