Example usage for org.hibernate Query setResultTransformer

List of usage examples for org.hibernate Query setResultTransformer

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

Introduction

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

Prototype

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

Source Link

Document

Set a strategy for handling the query results.

Usage

From source file:org.egov.ptis.domain.service.report.ReportService.java

License:Open Source License

@ReadOnly
public List<BillCollectorDailyCollectionReportResult> getBillCollectorWiseDailyCollection(final Date date,
        final BillCollectorDailyCollectionReportResult bcDailyCollectionReportResult) {
    boolean whereConditionAdded = false;
    List<BillCollectorDailyCollectionReportResult> listBcPayment = new ArrayList<BillCollectorDailyCollectionReportResult>(
            0);/*from  ww w. jav a  2 s  . c om*/
    int noofDays = 0;
    final StringBuilder queryBuilder = new StringBuilder(
            " select distinct district,ulbname  \"ulbName\" ,ulbcode \"ulbCode\" ,collectorname,mobilenumber,sum(target_arrears_demand) \"target_arrears_demand\",sum(target_current_demand) \"target_current_demand\",sum(today_arrears_collection) \"today_arrears_collection\",sum(today_currentyear_collection) \"today_currentyear_collection\", "
                    + " sum(cummulative_arrears_collection) \"cummulative_arrears_collection\",sum(cummulative_currentyear_collection) \"cummulative_currentyear_collection\",sum(lastyear_collection) \"lastyear_collection\",sum(lastyear_cummulative_collection) \"lastyear_cummulative_collection\"   "
                    + "from " + environmentSettings.statewideSchemaName() + ".billColl_DialyCollection_view ");
    final String value_ALL = "ALL";

    if (bcDailyCollectionReportResult != null) {
        if (bcDailyCollectionReportResult.getCity() != null
                && !bcDailyCollectionReportResult.getCity().equals("")
                && !bcDailyCollectionReportResult.getCity().equalsIgnoreCase(value_ALL)) {
            whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
            queryBuilder.append("  lower(ulbname)=:cityName  ");
        } else if (bcDailyCollectionReportResult.getDistrict() != null
                && !bcDailyCollectionReportResult.getDistrict().equals("")
                && !bcDailyCollectionReportResult.getDistrict().equalsIgnoreCase(value_ALL)) {
            if (whereConditionAdded)
                queryBuilder.append(" and  lower(district)=:districtName ");
            else {
                whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
                queryBuilder.append("  lower(district)=:districtName  ");
            }
        } else if (bcDailyCollectionReportResult.getRegion() != null
                && !bcDailyCollectionReportResult.getRegion().equals("")
                && !bcDailyCollectionReportResult.getRegion().equalsIgnoreCase(value_ALL))
            if (whereConditionAdded)
                queryBuilder.append(" and  lower(district) in (:districtNames) ");
            else {
                whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
                queryBuilder.append("   lower(district) in (:districtNames) ");
            }

        if (bcDailyCollectionReportResult.getType() != null
                && !bcDailyCollectionReportResult.getType().equals("")
                && !bcDailyCollectionReportResult.getType().equalsIgnoreCase(value_ALL))
            if (whereConditionAdded)
                queryBuilder.append(" and type =:typeOfSearch ");
            else {
                whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
                queryBuilder.append(" type =:typeOfSearch ");
            }

    }
    queryBuilder.append(
            " group by district,ulbname ,ulbcode  ,collectorname,mobilenumber  order by district,ulbname,collectorname ");
    final Query query = propPerServ.getSession().createSQLQuery(queryBuilder.toString());
    if (bcDailyCollectionReportResult != null) {
        if (bcDailyCollectionReportResult.getCity() != null
                && !bcDailyCollectionReportResult.getCity().equals("")
                && !bcDailyCollectionReportResult.getCity().equalsIgnoreCase(value_ALL))
            query.setString("cityName", bcDailyCollectionReportResult.getCity().toLowerCase());
        else if (bcDailyCollectionReportResult.getDistrict() != null
                && !bcDailyCollectionReportResult.getDistrict().equals("")
                && !bcDailyCollectionReportResult.getDistrict().equalsIgnoreCase(value_ALL))
            query.setString("districtName", bcDailyCollectionReportResult.getDistrict().toLowerCase());
        else if (bcDailyCollectionReportResult.getRegion() != null
                && !bcDailyCollectionReportResult.getRegion().equals("")
                && !bcDailyCollectionReportResult.getRegion().equalsIgnoreCase(value_ALL)) {
            final LinkedList<String> districtlist = new LinkedList<>();
            if (regionalHeirarchyService != null) {
                final List<RegionalHeirarchy> regions = regionalHeirarchyService
                        .getActiveChildRegionHeirarchyByPassingParentNameAndType(RegionalHeirarchyType.DISTRICT,
                                bcDailyCollectionReportResult.getRegion());
                if (regions != null && !regions.isEmpty()) {
                    for (final RegionalHeirarchy regiion : regions)
                        districtlist.add(regiion.getName().toLowerCase());
                    query.setParameterList("districtNames", districtlist);
                }

            }

        }

        if (bcDailyCollectionReportResult.getType() != null
                && !bcDailyCollectionReportResult.getType().equals("")
                && !bcDailyCollectionReportResult.getType().equalsIgnoreCase(value_ALL))
            query.setString("typeOfSearch", bcDailyCollectionReportResult.getType());
    }

    query.setResultTransformer(
            new AliasToBeanResultTransformer(BillCollectorDailyCollectionReportResult.class));

    listBcPayment = query.list();

    if (financialYearDAO != null && !listBcPayment.isEmpty()) {

        final CFinancialYear currentFinancialYear = financialYearDAO.getFinancialYearByDate(new Date());
        if (currentFinancialYear != null)
            noofDays = DateUtils.daysBetween(new Date(), currentFinancialYear.getEndingDate());
    }
    buildCollectionReport(listBcPayment, noofDays);
    return listBcPayment;

}

From source file:org.egov.ptis.domain.service.report.ReportService.java

License:Open Source License

@ReadOnly
public List<BillCollectorDailyCollectionReportResult> getUlbWiseDailyCollection(final Date date) {

    List<BillCollectorDailyCollectionReportResult> listBcPayment = new ArrayList<BillCollectorDailyCollectionReportResult>(
            0);/*from  ww w  .jav a  2  s  . c  o  m*/
    int noofDays = 0;
    final StringBuilder queryBuilder = new StringBuilder(

            " select distinct district,ulbname \"ulbName\" ,ulbcode \"ulbCode\"  ,  collectorname \"collectorname\" ,mobilenumber \"mobilenumber\",  "
                    + "target_arrears_demand,target_current_demand,today_arrears_collection,today_currentyear_collection,   "
                    + "cummulative_arrears_collection,cummulative_currentyear_collection,lastyear_collection,lastyear_cummulative_collection  "
                    + "from " + environmentSettings.statewideSchemaName()
                    + ".ulbWise_DialyCollection_view  order by district,ulbname ");
    final Query query = propPerServ.getSession().createSQLQuery(queryBuilder.toString());
    query.setResultTransformer(
            new AliasToBeanResultTransformer(BillCollectorDailyCollectionReportResult.class));

    listBcPayment = query.list();

    if (financialYearDAO != null && !listBcPayment.isEmpty()) {

        final CFinancialYear currentFinancialYear = financialYearDAO.getFinancialYearByDate(new Date());
        if (currentFinancialYear != null)
            noofDays = DateUtils.daysBetween(new Date(), currentFinancialYear.getEndingDate());
    }
    buildCollectionReport(listBcPayment, noofDays);
    return listBcPayment;

}

From source file:org.egov.ptis.domain.service.report.ReportService.java

License:Open Source License

@ReadOnly
public List<BillCollectorDailyCollectionReportResult> getUlbWiseDcbCollection(final Date date,
        final BillCollectorDailyCollectionReportResult bcDailyCollectionReportResult) {

    boolean whereConditionAdded = false;
    List<BillCollectorDailyCollectionReportResult> listBcPayment = new ArrayList<BillCollectorDailyCollectionReportResult>(
            0);/*from   w  w  w  . j  av a  2  s. c  o  m*/
    final StringBuilder queryBuilder = new StringBuilder(
            " select distinct district,ulbname  \"ulbName\" ,ulbcode \"ulbCode\",collectorname,mobilenumber ,sum(totalaccessments) \"totalaccessments\" , sum(current_demand) \"current_demand\", sum(arrears_demand) \"arrears_demand\", sum(current_demand_collection) \"current_demand_collection\" ,sum(arrears_demand_collection) \"arrears_demand_collection\" , sum(current_penalty) \"current_penalty\", sum(arrears_penalty) \"arrears_penalty\"  , sum(current_penalty_collection) \"current_penalty_collection\"  , sum(arrears_penalty_collection) \"arrears_penalty_collection\"  "
                    + "from " + environmentSettings.statewideSchemaName() + ".ulbWise_DCBCollection_view ");

    final String valueAll = "ALL";

    if (bcDailyCollectionReportResult != null) {
        if (bcDailyCollectionReportResult.getCity() != null
                && !bcDailyCollectionReportResult.getCity().equals("")
                && !bcDailyCollectionReportResult.getCity().equalsIgnoreCase(valueAll)) {
            whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
            queryBuilder.append("  lower(ulbname)=:cityName  ");
        } else if (bcDailyCollectionReportResult.getDistrict() != null
                && !bcDailyCollectionReportResult.getDistrict().equals("")
                && !bcDailyCollectionReportResult.getDistrict().equalsIgnoreCase(valueAll)) {
            if (whereConditionAdded)
                queryBuilder.append(" and  lower(district)=:districtName ");
            else {
                whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
                queryBuilder.append("  lower(district)=:districtName  ");
            }
        } else if (bcDailyCollectionReportResult.getRegion() != null
                && !bcDailyCollectionReportResult.getRegion().equals("")
                && !bcDailyCollectionReportResult.getRegion().equalsIgnoreCase(valueAll))
            if (whereConditionAdded)
                queryBuilder.append(" and  lower(district) in (:districtNames) ");
            else {
                whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
                queryBuilder.append("   lower(district) in (:districtNames) ");
            }

        if (bcDailyCollectionReportResult.getType() != null
                && !bcDailyCollectionReportResult.getType().equals("")
                && !bcDailyCollectionReportResult.getType().equalsIgnoreCase(valueAll))
            if (whereConditionAdded)
                queryBuilder.append(" and category in (:typeOfSearch) ");
            else {
                whereConditionAdded = addWhereCondition(whereConditionAdded, queryBuilder);
                queryBuilder.append(" category in (:typeOfSearch) ");
            }

    }
    queryBuilder.append(
            " group by district,ulbname ,ulbcode  ,collectorname,mobilenumber  order by district,ulbname,collectorname ");
    final Query query = propPerServ.getSession().createSQLQuery(queryBuilder.toString());
    if (bcDailyCollectionReportResult != null) {
        if (bcDailyCollectionReportResult.getCity() != null
                && !bcDailyCollectionReportResult.getCity().equals("")
                && !bcDailyCollectionReportResult.getCity().equalsIgnoreCase(valueAll))
            query.setString("cityName", bcDailyCollectionReportResult.getCity().toLowerCase());
        else if (bcDailyCollectionReportResult.getDistrict() != null
                && !bcDailyCollectionReportResult.getDistrict().equals("")
                && !bcDailyCollectionReportResult.getDistrict().equalsIgnoreCase(valueAll))
            query.setString("districtName", bcDailyCollectionReportResult.getDistrict().toLowerCase());
        else if (bcDailyCollectionReportResult.getRegion() != null
                && !bcDailyCollectionReportResult.getRegion().equals("")
                && !bcDailyCollectionReportResult.getRegion().equalsIgnoreCase(valueAll)) {
            final LinkedList<String> districtlist = new LinkedList<>();
            if (regionalHeirarchyService != null) {
                final List<RegionalHeirarchy> regions = regionalHeirarchyService
                        .getActiveChildRegionHeirarchyByPassingParentNameAndType(RegionalHeirarchyType.DISTRICT,
                                bcDailyCollectionReportResult.getRegion());
                if (regions != null && !regions.isEmpty()) {
                    for (final RegionalHeirarchy regiion : regions)
                        districtlist.add(regiion.getName().toLowerCase());
                    query.setParameterList("districtNames", districtlist);
                }

            }

        }

        if (bcDailyCollectionReportResult.getType() != null
                && !bcDailyCollectionReportResult.getType().equals("")
                && !bcDailyCollectionReportResult.getType().equalsIgnoreCase(valueAll))
            query.setParameterList("typeOfSearch",
                    prepareTypeOfSearch(bcDailyCollectionReportResult.getType()));
    }

    query.setResultTransformer(
            new AliasToBeanResultTransformer(BillCollectorDailyCollectionReportResult.class));

    listBcPayment = query.list();

    buildCollectionReportForUlbWiseDCb(listBcPayment);
    return listBcPayment;

}

From source file:org.egov.ptis.domain.service.report.ReportService.java

License:Open Source License

/**
 * @param zoneId// w  w w  .  j ava  2s. c om
 * @param wardId
 * @param areaId
 * @param localityId
 * @return
 */
@ReadOnly
public List<PropertyMaterlizeView> prepareQueryforArrearRegisterReport(final Long zoneId, final Long wardId,
        final Long areaId, final Long localityId) {
    // Get current installment
    final Installment currentInst = propertyTaxCommonUtils.getCurrentInstallment();
    final StringBuffer query = new StringBuffer(300);
    // Query that retrieves all the properties that has arrears.
    query.append("select distinct pmv from PropertyMaterlizeView pmv,InstDmdCollMaterializeView idc where "
            + "pmv.basicPropertyID = idc.propMatView.basicPropertyID and pmv.isActive = true and idc.installment.fromDate not between  ('"
            + currentInst.getFromDate() + "') and ('" + currentInst.getToDate() + "') ");
    if (propertyTaxUtil.isWard(localityId))
        query.append(" and pmv.locality.id= :localityId ");
    if (propertyTaxUtil.isWard(zoneId))
        query.append(" and pmv.zone.id= :zoneId ");
    if (propertyTaxUtil.isWard(wardId))
        query.append("  and pmv.ward.id= :wardId ");
    if (propertyTaxUtil.isWard(areaId))
        query.append("  and pmv.block.id= :areaId ");
    query.append(" order by pmv.basicPropertyID ");
    final Query qry = propPerServ.getSession().createQuery(query.toString());
    if (propertyTaxUtil.isWard(localityId))
        qry.setParameter("localityId", localityId);
    if (propertyTaxUtil.isWard(zoneId))
        qry.setParameter("zoneId", zoneId);
    if (propertyTaxUtil.isWard(wardId))
        qry.setParameter("wardId", wardId);
    if (propertyTaxUtil.isWard(areaId))
        qry.setParameter("areaId", areaId);
    @SuppressWarnings("unchecked")
    final List<PropertyMaterlizeView> propertyViewList = qry
            .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY).list();
    return propertyViewList;
}

From source file:org.egov.services.report.DEReportService.java

License:Open Source License

public List<DepartmentwiseExpenditureReport> getConcurrenceGivenFortheFinancialYearTillGivenDate(
        final DepartmentwiseExpenditureReport deObject) {
    final List<DepartmentwiseExpenditureReport> departmentwiseExpList = new ArrayList<DepartmentwiseExpenditureReport>();
    StringBuffer stringQry = new StringBuffer();
    String fundcondition = "";
    String fmDate = "";
    String toDate = "";
    Query query = null;
    if (!deObject.getFundId().equals("") || deObject.getFundId().equals("0"))
        fundcondition = " and vh.fundId=" + deObject.getFundId();
    if (deObject.getPeriod().equalsIgnoreCase("current")) {
        fmDate = getFormattedDate(getFinancialYearStartDate(deObject.getFromDate()));
        toDate = getFormattedDate(deObject.getFromDate());
    } else if (deObject.getPeriod().equalsIgnoreCase("previous")) {
        fmDate = getFormattedDate(getFinancialYearStartDate(getPreviousYearFor(deObject.getFromDate())));
        toDate = getFormattedDate(getPreviousYearFor(deObject.getFromDate()));
    }/*from   w  w w.j  a  va 2  s  . c  om*/
    if (!(deObject.getAssetCode() != null && deObject.getAssetCode().equals("0"))) {
        // String oppCode=deObject.getAssetCode().equals("412")?"410":"412";
        stringQry = stringQry.append(" SELECT dept.dept_name as departmentName, "
                + "  ROUND(SUM(gl.debitamount)/100000,2) AS concurrenceAmount "
                + "  FROM voucherheader vh,  generalledger gl, vouchermis mis,eg_department dept,"
                + " paymentheader ph   WHERE vh.id= gl.voucherheaderid "
                + "  AND vh.id  =mis.voucherheaderid AND vh.id  = ph.voucherheaderid AND dept.id_dept =mis.departmentid ")
                .append(" and TO_date(ph.concurrenceDate) >= TO_date('" + fmDate
                        + "','dd-Mon-yyyy') and  TO_date(ph.concurrenceDate)" + "<TO_date('" + toDate
                        + "','dd-Mon-yyyy') and gl.debitamount!=0  and gl.glcode like '"
                        + deObject.getAssetCode() + "%'")
                // .append(" AND gl1.glcode LIKE '"+oppCode+"%' AND gl1.creditamount! =0 and gl1.voucherheaderid= gl.voucherheaderid")
                .append(fundcondition + " AND vh.status  =0 AND vh.name!='"
                        + FinancialConstants.PAYMENTVOUCHER_NAME_DIRECTBANK + "' AND vh.type ='"
                        + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' group by dept.dept_name"
                        + "  order by dept.dept_name ");
        query = persistenceService.getSession().createSQLQuery(stringQry.toString()).addScalar("departmentName")
                .addScalar("concurrenceAmount");
    } else {
        stringQry = stringQry.append(" SELECT dept.dept_name as departmentName, "
                + "  ROUND(SUM(gl.debitamount)/100000,2) AS concurrenceAmount "
                + "  FROM voucherheader vh,  generalledger gl,  vouchermis mis,eg_department dept,paymentheader ph "
                + "  WHERE vh.id= gl.voucherheaderid "
                + "  AND vh.id  =mis.voucherheaderid AND vh.id  = ph.voucherheaderid AND dept.id_dept =mis.departmentid ")
                .append(" and TO_date(ph.concurrenceDate) >= TO_date('" + fmDate
                        + "','dd-Mon-yyyy') and  TO_date(ph.concurrenceDate)" + " <=TO_date('" + toDate
                        + "','dd-Mon-yyyy')")
                .append(fundcondition + " AND vh.status  =0 AND vh.name!='"
                        + FinancialConstants.PAYMENTVOUCHER_NAME_DIRECTBANK + "' AND vh.type ='"
                        + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' group by dept.dept_name"
                        + "  order by dept.dept_name ");
        query = persistenceService.getSession().createSQLQuery(stringQry.toString()).addScalar("departmentName")
                .addScalar("concurrenceAmount");
    }
    query.setResultTransformer(Transformers.aliasToBean(DepartmentwiseExpenditureReport.class));
    departmentwiseExpList.addAll(query.list());
    return departmentwiseExpList;
}

From source file:org.egov.services.report.DEReportService.java

License:Open Source License

public List<DepartmentwiseExpenditureReport> getConcurrenceGivenForthePeriodQuery(
        final DepartmentwiseExpenditureReport deObject) {
    final List<DepartmentwiseExpenditureReport> deList = new ArrayList<DepartmentwiseExpenditureReport>();
    StringBuffer stringQry = new StringBuffer();
    String fundcondition = " ";
    String fmDate = "";
    String toDate = "";
    Query query = null;
    if (!deObject.getFundId().equals("") || deObject.getFundId().equals("0"))
        fundcondition = " and vh.fundId=" + deObject.getFundId();
    if (deObject.getPeriod().equalsIgnoreCase("current")) {
        fmDate = getFormattedDate(deObject.getFromDate());
        toDate = getFormattedDate(deObject.getToDate());
    } else if (deObject.getPeriod().equalsIgnoreCase("previous")) {
        fmDate = getFormattedDate(getPreviousYearFor(deObject.getFromDate()));
        toDate = getFormattedDate(getPreviousYearFor(deObject.getToDate()));
    }//w w w.  java  2s  . co  m
    if (!(deObject.getAssetCode() != null && deObject.getAssetCode().equals("0"))) {
        // String oppCode=deObject.getAssetCode().equals("412")?"410":"412";

        stringQry = stringQry.append(" SELECT dept.dept_name as departmentName, "
                + " ROUND(SUM(gl.debitamount)/100000,2) AS concurrenceAmount ,"
                + "  TO_date(ph.concurrenceDate)  as concurrenceDate "
                + "  FROM voucherheader vh,  generalledger gl, vouchermis mis,  eg_department dept,  paymentheader ph"
                + "  WHERE vh.id   = gl.voucherheaderid AND vh.id   =mis.voucherheaderid"
                + " AND vh.id   = ph.voucherheaderid AND dept.id_dept = mis.departmentid "
                + " and gl.glcode like '" + deObject.getAssetCode() + "%' and gl.debitamount!=0 ")
                // .append(" AND gl1.glcode LIKE '"+oppCode+"%' AND gl1.creditamount! =0 and gl1.voucherheaderid= gl.voucherheaderid")
                .append(" and TO_date(ph.concurrenceDate) >= TO_date('" + fmDate
                        + "','dd-Mon-yyyy') and  TO_date(ph.concurrenceDate)" + " <=TO_date('" + toDate
                        + "','dd-Mon-yyyy')")
                .append(fundcondition + " AND vh.status  =0 AND vh.name!='"
                        + FinancialConstants.PAYMENTVOUCHER_NAME_DIRECTBANK + "' AND vh.type ='"
                        + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT
                        + "' group by dept.dept_name,  TO_date(ph.concurrenceDate)"
                        + " order by  TO_date(ph.concurrenceDate) ");
        query = persistenceService.getSession().createSQLQuery(stringQry.toString()).addScalar("departmentName")
                .addScalar("concurrenceAmount").addScalar("concurrenceDate");
        query.setResultTransformer(Transformers.aliasToBean(DepartmentwiseExpenditureReport.class));

    } else {
        stringQry = stringQry.append(" SELECT dept.dept_name as departmentName, "
                + " ROUND(SUM(gl.debitamount)/100000,2) AS concurrenceAmount ,"
                + "  TO_date(ph.concurrenceDate) as concurrenceDate "
                + "  FROM voucherheader vh,  generalledger gl,  vouchermis mis,  eg_department dept,  paymentheader ph"
                + " WHERE vh.id   = gl.voucherheaderid AND vh.id   =mis.voucherheaderid"
                + "  AND vh.id   = ph.voucherheaderid AND dept.id_dept = mis.departmentid  ")
                .append(" and TO_date(ph.concurrenceDate) >= TO_date('" + fmDate
                        + "','dd-Mon-yyyy') and  TO_date(ph.concurrenceDate)" + " <=TO_date('" + toDate
                        + "','dd-Mon-yyyy')")
                .append(fundcondition + " AND vh.status  =0 AND vh.name!='"
                        + FinancialConstants.PAYMENTVOUCHER_NAME_DIRECTBANK + "' AND vh.type ='"
                        + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT
                        + "' group by dept.dept_name, TO_date(ph.concurrenceDate)"
                        + " order by  TO_date(ph.concurrenceDate) ");
        query = persistenceService.getSession().createSQLQuery(stringQry.toString()).addScalar("departmentName")
                .addScalar("concurrenceAmount").addScalar("concurrenceDate");
        query.setResultTransformer(Transformers.aliasToBean(DepartmentwiseExpenditureReport.class));
    }

    deList.addAll(query.list());

    return deList;
}

From source file:org.egov.wtms.application.service.ConnectionDetailService.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<SearchWaterTaxBillDetail> getValueByModuleType() {
    final StringBuilder queryStr = new StringBuilder(800);
    CFinancialYear finYear = financialYearDAO.getFinancialYearByDate(new Date());
    queryStr.append(//from   w  w  w . ja v  a2 s  .  c  o  m
            "select bill.consumer_id as \"consumerNumber\", usr.id as \"userId\",bill.bill_no as \"billNo\",dcbview.curr_balance as  \"dueAmount\"  ")
            .append(" from eg_bill bill, egwtr_mv_dcb_view dcbview, egpushbox_userfcmdevice event, eg_user usr, ")
            .append(" egpt_basic_property basicproperty, egpt_property_owner_info ownerinfo")
            .append(" where dcbview.hscno= bill.consumer_id AND event.userId = usr.id AND dcbview.propertyid=basicproperty.propertyid AND")
            .append(" ownerinfo.basicproperty=basicproperty.id AND ownerinfo.owner=usr.id AND usr.type =:userType ")
            .append(" AND bill.id_bill_type=(select id from eg_bill_type where code=:billType)")
            .append(" AND bill.issue_date>=:startDate AND bill.issue_date<=:endDate")
            .append(" AND bill.module_id =(select id from eg_module where name =:moduleName) order By bill.consumer_id ");
    final Query query = entityManager.unwrap(Session.class).createSQLQuery(queryStr.toString());
    query.setParameter("userType", ROLE_CITIZEN);
    query.setParameter("moduleName", MODULE_NAME);
    query.setParameter("billType", BILLTYPE_MANUAL);
    query.setParameter("startDate", finYear.getStartingDate());
    query.setParameter("endDate", finYear.getEndingDate());
    query.setResultTransformer(new AliasToBeanResultTransformer(SearchWaterTaxBillDetail.class));

    return query.list();
}

From source file:org.egov.wtms.application.service.SearchNoticeService.java

License:Open Source License

@ReadOnly
@SuppressWarnings("unchecked")
public List<SearchNoticeDetails> getBillReportDetails(final SearchNoticeDetails searchNoticeDetails) {
    final long startTime = System.currentTimeMillis();
    final StringBuilder queryStr = new StringBuilder();
    queryStr.append(/*from  w  w  w  . j a v  a 2 s .c  o m*/
            "select distinct dcbinfo.hscno as \"hscNo\", dcbinfo.username as \"ownerName\",dcbinfo.propertyid as \"assessmentNo\",dcbinfo.demanddocumentnumber as \"fileStoreID\",");
    queryStr.append(
            "dcbinfo.houseno as \"houseNumber\" , localboundary.localname as \"locality\", dcbinfo.applicationtype as \"applicationType\" , ");
    queryStr.append(
            " dcbinfo.connectiontype as  \"connectionType\" , bill.bill_no as \"billNo\" , bill.issue_date as \"billDate\" from egwtr_mv_bill_view dcbinfo");
    queryStr.append(
            " INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id");
    queryStr.append(
            " INNER JOIN eg_bill bill on dcbinfo.hscno = bill.consumer_id and dcbinfo.demand= bill.id_demand");
    queryStr.append(" INNER JOIN eg_boundary zoneboundary on dcbinfo.zoneid = zoneboundary.id ");
    queryStr.append(" where dcbinfo.connectionstatus = '" + ConnectionStatus.ACTIVE.toString() + "' ");
    queryStr.append(" and bill.module_id = (select id from eg_module where name ='Water Tax Management')");
    queryStr.append(" and bill.id_bill_type = (select id from eg_bill_type  where code ='MANUAL')");
    queryStr.append(" and bill.is_cancelled ='N' ");
    if (isNotBlank(searchNoticeDetails.getRevenueWard()))
        queryStr.append(" and wardboundary.name =:ward");
    if (isNotBlank(searchNoticeDetails.getZone()))
        queryStr.append(" and zoneboundary.name =:zone");
    if (isNotBlank(searchNoticeDetails.getHscNo()))
        queryStr.append(" and dcbinfo.hscno =:consumerCode");
    if (isNotBlank(searchNoticeDetails.getAssessmentNo()))
        queryStr.append(" and dcbinfo.propertyid =:assessmentNumber");
    if (isNotBlank(searchNoticeDetails.getHouseNumber()))
        queryStr.append(" and dcbinfo.houseno =:houseNumber");
    if (isNotBlank(searchNoticeDetails.getConnectionType()))
        queryStr.append(" and dcbinfo.connectiontype =:connectionType");
    if (isNotBlank(searchNoticeDetails.getApplicationType()))
        queryStr.append(" and dcbinfo.applicationtype =:applicationType");
    if (isNotBlank(searchNoticeDetails.getPropertyType()))
        queryStr.append(" and dcbinfo.propertytype =:propertyType");

    final Query query = entityManager.unwrap(Session.class).createSQLQuery(queryStr.toString());
    setSearchQueryParameters(searchNoticeDetails, null, null, query);
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("GenerateConnectionBill -- Search Result " + queryStr.toString());
    query.setResultTransformer(new AliasToBeanResultTransformer(SearchNoticeDetails.class));
    final long endTime = System.currentTimeMillis();
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("GenerateBill | SearchResult | Time taken(ms) " + (endTime - startTime));
        LOGGER.debug("Exit from SearchResult method");
    }

    return query.list();
}

From source file:org.fourthline.konto.server.dao.AccountDAO.java

License:Open Source License

public List<Account> getAccounts(Long... ids) {
    if (ids == null)
        return null;
    StringBuilder sb = new StringBuilder();
    sb.append("select a, mu from Account a, MonetaryUnit mu");
    sb.append(" where mu.id = a.monetaryUnitId and a.id in (:ids) order by a.id asc");
    Query q = getCurrentSession().createQuery(sb.toString());
    q.setParameterList("ids", ids);
    q.setResultTransformer(new ResultTransformer() {
        @Override//from  w w  w. j av  a 2 s. co  m
        public Object transformTuple(Object[] objects, String[] strings) {
            Account account = (Account) objects[0];
            account.setMonetaryUnit((MonetaryUnit) objects[1]);
            return account;
        }

        @Override
        public List transformList(List list) {
            return list;
        }
    });
    return q.list();
}

From source file:org.fourthline.konto.server.dao.AccountDAO.java

License:Open Source License

public List<Account> getAccounts(AccountsQueryCriteria criteria) {

    StringBuilder sb = new StringBuilder();
    sb.append("select a, mu from ");
    sb.append(getAccountEntity(criteria.getType())).append(" a, ");
    sb.append("MonetaryUnit mu");

    sb.append(" where mu.id = a.monetaryUnitId");

    // TODO: SQL IN clause, need to split huge lists of identifiers
    if (!criteria.isListOfIdentifiersEmpty()) {
        sb.append(" and a.id in(:ids)");

    } else if (criteria.getStringFilter() != null) {
        sb.append(" and (lower(a.name) like :nameFilter");
        sb.append(" or lower(a.groupName) like :nameFilter)");
    }//  w  w w  . j ava 2 s  . com

    sb.append(" order by ");
    if (criteria.getOrderBy() != null && !criteria.getOrderBy().equals(Account.Property.name)) {
        if (criteria.getOrderBy().equals(Account.Property.groupName)) {
            sb.append(" lower(a.groupName)");
            sb.append(criteria.isSortAscending() ? " asc" : " desc");
            sb.append(" , lower(a.name)");
            sb.append(criteria.isSortAscending() ? " asc" : " desc");
        }

    } else {
        sb.append(" lower(a.name)");
        sb.append(criteria.isSortAscending() ? " asc" : " desc");
    }

    Query q = getCurrentSession().createQuery(sb.toString());

    if (!criteria.isListOfIdentifiersEmpty()) {
        q.setParameterList("ids", criteria.getListOfIdentifiers());
    } else if (criteria.getStringFilter() != null) {
        q.setString("nameFilter",
                (criteria.isSubstringQuery() ? "%" : "") + criteria.getStringFilter().toLowerCase() + "%");
    }

    if (criteria.getMaxResults() != null) {
        q.setMaxResults(criteria.getMaxResults());
    }

    q.setResultTransformer(new ResultTransformer() {
        @Override
        public Object transformTuple(Object[] objects, String[] strings) {
            Account account = (Account) objects[0];
            account.setMonetaryUnit((MonetaryUnit) objects[1]);
            return account;
        }

        @Override
        public List transformList(List list) {
            return list;
        }
    });

    return q.list();
}