Example usage for org.hibernate SQLQuery setResultTransformer

List of usage examples for org.hibernate SQLQuery 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:org.egov.wtms.application.service.BaseRegisterReportService.java

License:Open Source License

@ReadOnly
public List<BaseRegisterResult> getBaseRegisterReportDetails(final String ward) throws ParseException {

    final StringBuilder queryStr = new StringBuilder();
    queryStr.append(/*w  ww  .  j  a v a 2  s.  c om*/
            "select dcbinfo.hscno as \"consumerNo\", dcbinfo.oldhscno as \"oldConsumerNo\", dcbinfo.propertyid as \"assementNo\", ");
    queryStr.append(
            "dcbinfo.username as \"ownerName\",dcbinfo.categorytype as \"categoryType\",dcbinfo.username as \"period\",dcbinfo.houseno as \"doorNo\",");
    queryStr.append(
            " dcbinfo.connectiontype as \"connectionType\" , dcbinfo.arr_demand as \"arrears\" ,  dcbinfo.curr_demand as \"current\" , ");
    queryStr.append(
            " dcbinfo.arr_coll as \"arrearsCollection\" ,  dcbinfo.curr_coll as \"currentCollection\" , ");
    queryStr.append(
            " dcbinfo.arr_demand+dcbinfo.curr_demand as \"totalDemand\" , dcbinfo.usagetype as \"usageType\" , dcbinfo.waterSource as \"waterSource\" ,");
    queryStr.append(
            " dcbinfo.pipesize as \"pipeSize\" , dcbinfo.arr_coll+dcbinfo.curr_coll as \"totalCollection\" , wrd.monthlyrate as \"monthlyRate\" ");
    queryStr.append(
            " from  egwtr_usage_type ut, egwtr_water_source wt, egwtr_pipesize ps, egwtr_water_rates_header wrh,egwtr_water_rates_details wrd ,");
    queryStr.append(
            " egwtr_mv_dcb_view dcbinfo INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id ");
    queryStr.append(" INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id");
    queryStr.append(
            " where ut.id=wrh.usagetype and wt.id=wrh.watersource and ps.id=wrh.pipesize and dcbinfo.usagetype =ut.name and  dcbinfo.watersource = wt.watersourcetype and dcbinfo.pipesize = ps.code and wrd.waterratesheader=wrh.id and wrh.active=true  and wrd.fromdate <= now() and  wrd.todate >= now() and dcbinfo.connectionstatus = '"
                    + ConnectionStatus.ACTIVE.toString() + "'");
    if (StringUtils.isNotBlank(ward))
        queryStr.append(" and wardboundary.id = :ward");
    queryStr.append(
            " UNION select dcbinfo.hscno as \"consumerNo\", dcbinfo.oldhscno as \"oldConsumerNo\", dcbinfo.propertyid as \"assementNo\", ");
    queryStr.append(
            "dcbinfo.username as \"ownerName\",dcbinfo.categorytype as \"categoryType\",dcbinfo.username as \"period\",dcbinfo.houseno as \"doorNo\",");
    queryStr.append(
            " dcbinfo.connectiontype as \"connectionType\" , dcbinfo.arr_demand as \"arrears\" ,  dcbinfo.curr_demand as \"current\" , ");
    queryStr.append(
            " dcbinfo.arr_coll as \"arrearsCollection\" ,  dcbinfo.curr_coll as \"currentCollection\" , ");
    queryStr.append(
            " dcbinfo.arr_demand+dcbinfo.curr_demand as \"totalDemand\" , dcbinfo.usagetype as \"usageType\" , dcbinfo.waterSource as \"waterSource\" ,");
    queryStr.append(
            " dcbinfo.pipesize as \"pipeSize\" , dcbinfo.arr_coll+dcbinfo.curr_coll as \"totalCollection\" , 0 as \"monthlyRate\" ");
    queryStr.append(
            " from  egwtr_usage_type ut, egwtr_water_source wt, egwtr_pipesize ps, egwtr_water_rates_header wrh,egwtr_water_rates_details wrd ,");
    queryStr.append(
            " egwtr_mv_dcb_view dcbinfo INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id ");
    queryStr.append(" INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id");
    queryStr.append(
            " where ut.id=wrh.usagetype and wt.id=wrh.watersource and ps.id=wrh.pipesize and dcbinfo.usagetype =ut.name and  dcbinfo.watersource = wt.watersourcetype and dcbinfo.pipesize = ps.code and wrd.waterratesheader=wrh.id and wrh.active=false and dcbinfo.connectionstatus = '"
                    + ConnectionStatus.ACTIVE.toString() + "'" + " and dcbinfo.hscno not in (");
    queryStr.append(
            " select dcbinfo.hscno from  egwtr_usage_type ut, egwtr_water_source wt, egwtr_pipesize ps, egwtr_water_rates_header wrh,egwtr_water_rates_details wrd ,");
    queryStr.append(
            " egwtr_mv_dcb_view dcbinfo INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id ");
    queryStr.append(" INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id");
    queryStr.append(
            " where ut.id=wrh.usagetype and wt.id=wrh.watersource and ps.id=wrh.pipesize and dcbinfo.usagetype =ut.name and  dcbinfo.watersource = wt.watersourcetype and dcbinfo.pipesize = ps.code and wrd.waterratesheader=wrh.id and wrh.active=true  and wrd.fromdate <= now() and  wrd.todate >= now() and dcbinfo.connectionstatus = '"
                    + ConnectionStatus.ACTIVE.toString() + "')");
    if (StringUtils.isNotBlank(ward))
        queryStr.append(" and wardboundary.id = :ward");
    final SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    if (StringUtils.isNotBlank(ward))
        query.setLong("ward", Long.valueOf(ward));
    query.setResultTransformer(new AliasToBeanResultTransformer(BaseRegisterResult.class));
    return query.list();

}

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

License:Open Source License

@ReadOnly
public List<DailyWTCollectionReport> getCollectionDetails(final Date fromDate, final Date toDate,
        final String collectionMode, final String collectionOperator, final String status)
        throws ParseException {
    final StringBuilder queryStr = new StringBuilder(500);

    queryStr.append(//w  w w.jav a  2  s . c o m
            "select distinct receiptheader from ReceiptHeader receiptheader inner join fetch receiptheader.receiptInstrument instHeader"
                    + " inner join fetch instHeader.instrumentType instType where receiptheader.service.name =:service and (receiptdate between :fromDate and :toDate) ");
    if (StringUtils.isNotBlank(collectionMode))
        queryStr.append(" and receiptheader.source =:mode ");
    if (StringUtils.isNotBlank(collectionOperator))
        queryStr.append(" and receiptheader.createdBy.id =:operator ");
    if (StringUtils.isNotBlank(status))
        queryStr.append(" and receiptheader.status.id =:status ");
    queryStr.append(" order by instHeader ");
    final Query query = getCurrentSession().createQuery(queryStr.toString());
    query.setString("service", WaterTaxConstants.EGMODULES_NAME);
    query.setDate("fromDate", new DateTime(fromDate).withTimeAtStartOfDay().toDate());
    query.setDate("toDate", new DateTime(toDate).plusDays(1).toDate());
    if (StringUtils.isNotBlank(collectionMode))
        query.setString("mode", collectionMode);
    if (StringUtils.isNotBlank(collectionOperator))
        query.setLong("operator", Long.valueOf(collectionOperator));
    if (StringUtils.isNotBlank(status))
        query.setLong("status", Long.valueOf(status));
    final List<ReceiptHeader> receiptHeaderList = query.list();
    final List<DailyWTCollectionReport> dailyWTCollectionReportList = new ArrayList<DailyWTCollectionReport>(0);

    for (final ReceiptHeader receiptHeader : receiptHeaderList) {
        BigDecimal currCollection = BigDecimal.ZERO;
        BigDecimal arrCollection = BigDecimal.ZERO;
        final DailyWTCollectionReport result = new DailyWTCollectionReport();
        result.setReceiptNumber(receiptHeader.getReceiptnumber());
        result.setReceiptDate(receiptHeader.getReceiptdate());
        result.setConsumerCode(receiptHeader.getConsumerCode());
        result.setConsumerName(receiptHeader.getPayeeName());
        result.setPaidAt(receiptHeader.getSource());
        final WaterConnectionDetails waterConnection = waterConnectionDetailsService
                .findByApplicationNumberOrConsumerCode(receiptHeader.getConsumerCode());
        if (null != waterConnection)
            result.setConnectionType(waterConnection.getConnectionType().toString());
        final StringBuilder queryString = new StringBuilder();
        queryString.append(
                "select wardboundary.name as \"wardName\",dcbinfo.houseno as \"houseNo\" from egwtr_mv_dcb_view dcbinfo"
                        + " INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id  where dcbinfo.hscno = '"
                        + receiptHeader.getConsumerCode() + "'");
        final SQLQuery finalQuery = getCurrentSession().createSQLQuery(queryString.toString());
        finalQuery.setResultTransformer(new AliasToBeanResultTransformer(DefaultersReport.class));
        List<DefaultersReport> listforWardAndHsc = new ArrayList<DefaultersReport>();
        listforWardAndHsc = finalQuery.list();
        if (!listforWardAndHsc.isEmpty()) {
            result.setDoorNumber(listforWardAndHsc.get(0).getHouseNo());
            result.setWardName(listforWardAndHsc.get(0).getWardName());
        }
        result.setTotal(receiptHeader.getTotalAmount());
        result.setStatus(receiptHeader.getStatus().getDescription());
        if ("CANCELLED".equalsIgnoreCase(receiptHeader.getStatus().getCode()))
            result.setCancellationDetails(receiptHeader.getReasonForCancellation());
        else
            result.setCancellationDetails("N/A");
        final StringBuilder paymentMode = new StringBuilder(30);
        int count = 0;
        for (final InstrumentHeader instrument : receiptHeader.getReceiptInstrument()) {
            final int instrumentSize = receiptHeader.getReceiptInstrument().size();
            paymentMode.append(instrument.getInstrumentType().getType());
            if (instrumentSize > 1 && count < instrumentSize - 1) {
                paymentMode.append(",");
                count++;
            }
        }
        result.setPaidAt(receiptHeader.getSource());
        result.setPaymentMode(paymentMode.toString());
        final List<ReceiptDetail> receiptDetailsList = new ArrayList<ReceiptDetail>(
                receiptHeader.getReceiptDetails());
        final int lastindex = receiptDetailsList.size() - 2;
        if (null != receiptDetailsList.get(0).getDescription()) {
            final int index = receiptDetailsList.get(0).getDescription().indexOf("-");
            final int hashIndex = receiptDetailsList.get(0).getDescription().indexOf("#");
            final String instDesc = receiptDetailsList.get(0).getDescription().substring(index + 1, hashIndex);
            result.setFromInstallment(instDesc);
        }
        if (null != receiptDetailsList.get(lastindex).getDescription()) {
            final int index = receiptDetailsList.get(lastindex).getDescription().indexOf("-");
            final int hashIndex = receiptDetailsList.get(lastindex).getDescription().indexOf("#");
            final String instDesc = receiptDetailsList.get(lastindex).getDescription().substring(index + 1,
                    hashIndex);
            result.setToInstallment(instDesc);
        }
        for (final ReceiptDetail receiptDetail : receiptHeader.getReceiptDetails()) {
            final String rdesc = receiptDetail.getDescription();
            if (null != rdesc) {
                final String receiptDmdRsnDesc = rdesc.substring(0, receiptDetail.getDescription().indexOf("-"))
                        .trim();
                String currentInstallment = null;
                if (Arrays.asList(WaterTaxConstants.CREATECONNECTIONDMDDESC).contains(receiptDmdRsnDesc))
                    currentInstallment = connectionDemandService
                            .getCurrentInstallment(WaterTaxConstants.MODULE_NAME, WaterTaxConstants.YEARLY,
                                    new Date())
                            .getDescription();
                else if (Arrays.asList(WaterTaxConstants.WATERCHARGESDMDDESC).contains(receiptDmdRsnDesc))
                    if (ConnectionType.METERED.equals(waterConnection.getConnectionType()))
                        currentInstallment = connectionDemandService
                                .getCurrentInstallment(WaterTaxConstants.MODULE_NAME, WaterTaxConstants.MONTHLY,
                                        new Date())
                                .getDescription();
                    else if (ConnectionType.NON_METERED.equals(waterConnection.getConnectionType()))
                        currentInstallment = connectionDemandService
                                .getCurrentInstallment(WaterTaxConstants.PROPERTY_MODULE_NAME, null, new Date())
                                .getDescription();

                if (null != rdesc && rdesc.substring(rdesc.indexOf("-") + 1, rdesc.indexOf("#")).trim()
                        .equals(currentInstallment))
                    currCollection = currCollection.add(receiptDetail.getCramount());
                else if (null != rdesc && !rdesc.substring(rdesc.indexOf("-") + 1, rdesc.indexOf("#")).trim()
                        .equals(currentInstallment))
                    arrCollection = arrCollection.add(receiptDetail.getCramount());
            }
        }
        result.setArrearTotal(null != arrCollection ? arrCollection : new BigDecimal(0));
        result.setCurrentTotal(currCollection);
        result.setTotal(currCollection.add(arrCollection));
        dailyWTCollectionReportList.add(result);
    }

    return dailyWTCollectionReportList;
}

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

License:Open Source License

@ReadOnly
public List<DataEntryConnectionReport> getDataEntryConnectionReportDetails(final String ward)
        throws ParseException {
    final StringBuilder queryStr = new StringBuilder();
    queryStr.append(//w w w  . java  2 s  .c  o  m
            "select dcbinfo.hscno as \"hscNo\", dcbinfo.propertyid as \"assessmentNo\", dcbinfo.username as \"ownerName\", zoneboundary.name as \"zone\", wardboundary.name as \"revenueWard\","
                    + "blockboundary.name as \"block\",localboundary.localname as \"locality\",dcbinfo.address as \"address\" , dcbinfo.mobileno as \"mobileNumber\", dcbinfo.watersource as \"waterSource\" ,  "
                    + "dcbinfo.propertytype as \"propertyType\" , dcbinfo.applicationtype as \"applicationType\", dcbinfo.connectiontype as \"connectionType\",  "
                    + "dcbinfo.usagetype as \"usageType\" , dcbinfo.categorytype as \"category\", dcbinfo.pipesize as \"pipeSizeInInch\",  "
                    + "dcbinfo.aadharno as \"aadharNumber\" ,  dcbinfo.numberofperson as \"noOfPersons\" , dcbinfo.numberofrooms as \"noOfRooms\" , dcbinfo.sumpcapacity as \"sumpCapacity\" , "
                    + " dcbinfo.executiondate as \"connectionDate\"  ,dcbinfo.arr_balance+dcbinfo.curr_balance as \"waterTaxDue\" , "
                    + "dcbinfo.pt_firsthalf_demand + dcbinfo.pt_secondhalf_demand - dcbinfo.pt_firsthalf_collection - dcbinfo.pt_secondhalf_collection as \"propertyTaxDue\" "
                    + "from egwtr_mv_dcb_view dcbinfo"
                    + " INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id INNER JOIN eg_boundary zoneboundary on dcbinfo.zoneid = zoneboundary.id"
                    + " INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id  INNER JOIN eg_boundary blockboundary on dcbinfo.block = blockboundary.id");
    queryStr.append(
            " where dcbinfo.connectionstatus = 'ACTIVE' and dcbinfo.legacy = true and dcbinfo.approvalnumber IS NULL  and dcbinfo.connectiontype = 'NON_METERED' ");
    if (ward != null && !ward.isEmpty())
        queryStr.append(" and wardboundary.name = " + "'" + ward + "'");
    final SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    query.setResultTransformer(new AliasToBeanResultTransformer(DataEntryConnectionReport.class));
    return query.list();

}

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

License:Open Source License

@ReadOnly
public List<DefaultersReport> getDefaultersReportDetails(final String fromAmount, final String toAmount,
        final String ward, final String topDefaulters, final int startsFrom, final int maxResults)
        throws ParseException {
    StringBuilder queryStr = new StringBuilder();
    queryStr = queryStr.append(//from   ww w  .j  av a2s  .c  o  m
            "select dcbinfo.hscno as \"hscNo\", dcbinfo.demand as \"demandId\", dcbinfo.username as \"ownerName\",wardboundary.name as \"wardName\", ")
            .append("dcbinfo.houseno as \"houseNo\" , localboundary.localname as \"locality\", dcbinfo.mobileno as \"mobileNumber\", ")
            .append("dcbinfo.arr_balance as \"arrearsDue\" ,  dcbinfo.curr_balance as \"currentDue\" , dcbinfo.arr_balance+dcbinfo.curr_balance as \"totalDue\" ")
            .append("from egwtr_mv_dcb_view dcbinfo INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id");

    if (Double.parseDouble(toAmount) == 0)
        queryStr.append(" where dcbinfo.arr_balance+dcbinfo.curr_balance >=" + fromAmount);
    else
        queryStr.append(" where dcbinfo.arr_balance+dcbinfo.curr_balance >=" + fromAmount
                + " and dcbinfo.arr_balance+dcbinfo.curr_balance <=" + toAmount);
    queryStr.append(" and dcbinfo.connectionstatus = '" + ConnectionStatus.ACTIVE.toString() + "'");
    if (ward != null && !ward.isEmpty())
        queryStr.append(" and wardboundary.id = '" + ward + "'");

    queryStr.append(" and dcbinfo.demand IS NOT NULL");
    if (!topDefaulters.isEmpty())
        queryStr.append(" order by dcbinfo.arr_balance+dcbinfo.curr_balance desc ");
    final SQLQuery finalQuery = getCurrentSession().createSQLQuery(queryStr.toString());
    finalQuery.setFirstResult(startsFrom);
    finalQuery.setMaxResults(maxResults);
    finalQuery.setResultTransformer(new AliasToBeanResultTransformer(DefaultersReport.class));
    return finalQuery.list();
}

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

License:Open Source License

public List<GenerateConnectionBill> getBillReportDetails(final String zone, final String ward,
        final String propertyType, final String applicationType, final String connectionType,
        final String consumerCode, final String houseNumber, final String assessmentNumber)
        throws ParseException {
    final long startTime = System.currentTimeMillis();
    final StringBuilder queryStr = new StringBuilder();
    queryStr.append(// w  ww .j av a2 s.  c  om
            "select dcbinfo.hscno as \"hscNo\", dcbinfo.username as \"ownerName\",dcbinfo.propertyid as \"assessmentNo\","
                    + "dcbinfo.houseno as \"houseNumber\" , localboundary.localname as \"locality\", dcbinfo.applicationtype as \"applicationType\" , "
                    + " dcbinfo.connectiontype as  \"connectionType\" from egwtr_mv_dcb_view dcbinfo"
                    + " INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id INNER JOIN eg_boundary localboundary on"
                    + " dcbinfo.locality = localboundary.id  INNER JOIN eg_boundary zoneboundary on dcbinfo.zoneid = zoneboundary.id ");
    queryStr.append(" where dcbinfo.connectionstatus = '" + ConnectionStatus.ACTIVE.toString() + "' ");
    if (ward != null && !ward.isEmpty())
        queryStr.append(" and wardboundary.name = " + "'" + ward + "'");
    if (zone != null && !zone.isEmpty())
        queryStr.append(" and zoneboundary.name = " + "'" + zone + "'");
    if (consumerCode != null && !consumerCode.isEmpty())
        queryStr.append(" and dcbinfo.hscno = " + "'" + consumerCode + "'");
    if (assessmentNumber != null && !assessmentNumber.isEmpty())
        queryStr.append(" and dcbinfo.propertyid = " + "'" + assessmentNumber + "'");
    if (houseNumber != null && !houseNumber.isEmpty())
        queryStr.append(" and dcbinfo.houseno = " + "'" + houseNumber + "'");
    if (connectionType != null && !connectionType.isEmpty())
        queryStr.append(" and dcbinfo.connectiontype = " + "'" + connectionType + "'");
    if (applicationType != null && !applicationType.isEmpty())
        queryStr.append(" and dcbinfo.applicationtype = " + "'" + applicationType + "'");
    if (propertyType != null && !propertyType.isEmpty())
        queryStr.append(" and dcbinfo.propertytype = " + "'" + propertyType + "'");

    final SQLQuery finalQuery = entityQueryService.getSession().createSQLQuery(queryStr.toString());
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("GenerateConnectionBill -- Search Result " + queryStr.toString());
    finalQuery.setResultTransformer(new AliasToBeanResultTransformer(GenerateConnectionBill.class));
    final List<GenerateConnectionBill> generateConnectionBillList = finalQuery.list();
    final long endTime = System.currentTimeMillis();
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("GenerateBill | SearchResult | Time taken(ms) " + (endTime - startTime));
        LOGGER.debug("Exit from SearchResult method");
    }
    return generateConnectionBillList;
}

From source file:org.egov.wtms.masters.service.BoundaryWiseReportService.java

License:Open Source License

private SQLQuery setParameterForDrillDownReportQuery(final String querykey, final String ward,
        final String block) {
    final SQLQuery query = entityQueryService.getSession().createSQLQuery(querykey);
    if (StringUtils.isNotBlank(ward))
        query.setLong("ward", Long.valueOf(ward));
    if (StringUtils.isNotBlank(block))
        query.setLong("block", Long.valueOf(block));
    query.setResultTransformer(Transformers.aliasToBean(WaterConnectionReportResult.class));
    return query;

}

From source file:org.egov.wtms.web.controller.reports.BaseRegisterReportService.java

License:Open Source License

public SQLQuery getBaseRegisterReportDetails(final String ward) throws ParseException {

    final StringBuilder queryStr = new StringBuilder();
    queryStr.append(//from  w  ww. j  av  a  2  s .  c  om
            "select dcbinfo.hscno as \"consumerNo\",dcbinfo.propertyid as \"assementNo\", dcbinfo.username as \"ownerName\",dcbinfo.categorytype as \"categoryType\",dcbinfo.username as \"period\","
                    + "dcbinfo.houseno as \"doorNo\",   "
                    + "dcbinfo.arr_balance as \"arrears\" ,  dcbinfo.curr_balance as \"current\" , dcbinfo.arr_balance+dcbinfo.curr_balance as \"totalDemand\"  "
                    + "from egwtr_mv_dcb_view dcbinfo"
                    + " INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id");

    if (ward != null && !ward.isEmpty())
        queryStr.append(" and wardboundary.name = " + "'" + ward + "'");

    final SQLQuery finalQuery = getCurrentSession().createSQLQuery(queryStr.toString());
    finalQuery.setResultTransformer(new AliasToBeanResultTransformer(BaseRegisterResult.class));

    return finalQuery;

}

From source file:org.egov.wtms.web.controller.reports.DCBReportController.java

License:Open Source License

private SQLQuery prepareQuery(final String paramList, final String connectionType, final String mode,
        final String reportType) {
    StringBuilder query = new StringBuilder();
    final StringBuilder selectQry1 = new StringBuilder();
    final StringBuilder selectQry2 = new StringBuilder();
    StringBuilder fromQry = new StringBuilder();
    StringBuilder whereQry = new StringBuilder();
    final StringBuilder groupByQry = new StringBuilder();
    selectQry2.append(/*from w  w  w  . j  a v a 2  s.  co  m*/
            "  cast(SUM(arr_demand) as bigint) AS arr_demand,cast(SUM(curr_demand) as bigint) AS curr_demand,cast(SUM(arr_coll) as bigint) AS arr_coll,cast(SUM(curr_coll) as bigint) AS curr_coll,"
                    + "cast(SUM(arr_balance) as bigint) AS arr_balance,cast(SUM(curr_balance) as bigint) AS curr_balance ");
    fromQry = new StringBuilder(" from egwtr_mv_dcb_view dcbinfo,eg_boundary boundary ");
    if (mode.equalsIgnoreCase(ZONEWISE)) {
        selectQry1.append(
                "select  distinct cast(dcbinfo.zoneid as integer) as \"zoneid\",boundary.name as \"boundaryName\", count(hscno) as countofconsumerno,");
        groupByQry.append(" group by dcbinfo.zoneid,boundary.name order by boundary.name");
        whereQry.append(" where dcbinfo.zoneid=boundary.id ");
        if (paramList != null && !paramList.equalsIgnoreCase(""))
            whereQry = whereQry.append(" and dcbinfo.zoneid in (" + paramList + ")");
    } else if (mode.equalsIgnoreCase(WARDWISE)) {
        selectQry1.append(
                "select distinct cast(dcbinfo.wardid as integer) as \"wardid\",boundary.name as \"boundaryName\",count(hscno) as countofconsumerno, ");
        groupByQry.append(" group by dcbinfo.wardid,boundary.name order by boundary.name");
        whereQry.append(" where dcbinfo.wardid=boundary.id ");
        if (paramList != null && !paramList.equalsIgnoreCase("") && reportType.equalsIgnoreCase("wardWise"))
            whereQry = whereQry.append(" and dcbinfo.wardid in (" + paramList + ")");
        if (paramList != null && !paramList.equalsIgnoreCase("") && !reportType.equalsIgnoreCase("wardWise"))
            whereQry = whereQry.append(" and dcbinfo.zoneid in (" + paramList + ")");
    } else if (mode.equalsIgnoreCase(BLOCKWISE)) {
        selectQry1.append(
                "select  distinct cast(dcbinfo.block as integer) as \"wardid\",boundary.name as \"boundaryName\", count(hscno) as countofconsumerno,");
        groupByQry.append(" group by dcbinfo.block,boundary.name order by boundary.name");
        whereQry.append(" where dcbinfo.block=boundary.id ");
        if (paramList != null && !paramList.equalsIgnoreCase("") && reportType.equalsIgnoreCase("blockWise"))
            whereQry = whereQry.append(" and dcbinfo.block in (" + paramList + ")");
        if (paramList != null && !paramList.equalsIgnoreCase("") && !reportType.equalsIgnoreCase("blockWise"))
            whereQry = whereQry.append(" and dcbinfo.wardid in (" + paramList + ")");
    } else if (mode.equalsIgnoreCase(LOCALITYWISE)) {
        selectQry1.append(
                "select  distinct cast(dcbinfo.locality as integer) as \"locality\",boundary.name as \"boundaryName\",dcbinfo.username as \"username\", count(hscno) as countofconsumerno, ");
        groupByQry.append(" group by dcbinfo.locality,boundary.name,dcbinfo.username order by boundary.name");
        whereQry.append(" where dcbinfo.locality=boundary.id and dcbinfo.locality in (" + paramList + ")");
    } else if (mode.equalsIgnoreCase(PROPERTY)) {
        selectQry1.append(
                "select distinct dcbinfo.hscno as hscno,dcbinfo.propertyid as \"propertyid\" ,dcbinfo.username as \"username\", ");
        fromQry = new StringBuilder(" from egwtr_mv_dcb_view dcbinfo ");
        groupByQry.append("group by dcbinfo.hscno,dcbinfo.propertyid,dcbinfo.username ");
        whereQry.append(" where dcbinfo.hscno is not null  ");
        if (paramList != null && !paramList.equalsIgnoreCase("") && reportType.equalsIgnoreCase("localityWise"))
            whereQry = whereQry.append(" and dcbinfo.locality in (" + paramList + ")");
        else
            whereQry = whereQry.append(" and dcbinfo.block in (" + paramList + ")");
    }
    if (!connectionType.equalsIgnoreCase(""))
        whereQry.append(" and dcbinfo.connectiontype = '" + connectionType + "'");
    whereQry.append(" and dcbinfo.connectionstatus = 'ACTIVE'");
    query = selectQry1.append(selectQry2).append(fromQry).append(whereQry).append(groupByQry);
    final SQLQuery finalQuery = entityManager.unwrap(Session.class).createSQLQuery(query.toString());
    finalQuery.setResultTransformer(new AliasToBeanResultTransformer(DCBReportResult.class));
    return finalQuery;
}

From source file:org.egov.wtms.web.controller.reports.NumberOfConnectionReportController.java

License:Open Source License

@ExceptionHandler(Exception.class)
@RequestMapping(value = "/connectionReportList", method = RequestMethod.GET)
public @ResponseBody void springPaginationDataTablesUpdate(@RequestParam final String ward,
        @RequestParam final String block, final HttpServletRequest request, final HttpServletResponse response)
        throws IOException {

    SQLQuery drillDownreportQuery = null;
    String result = null;//from  www.  j  a  va  2s.c om
    drillDownreportQuery = boundaryWiseReportService.getDrillDownReportQuery(ward, block);
    drillDownreportQuery.setResultTransformer(Transformers.aliasToBean(WaterConnectionReportResult.class));
    final List<WaterConnectionReportResult> drillDownresult = drillDownreportQuery.list();
    result = new StringBuilder("{ \"data\":").append(toJSON(drillDownresult)).append("}").toString();
    response.setContentType(MediaType.APPLICATION_JSON_VALUE);
    IOUtils.write(result, response.getWriter());

}

From source file:org.generationcp.middleware.dao.dms.DmsProjectDao.java

License:Open Source License

public List<MeasurementVariable> getObservationSetVariables(final List<Integer> observationSetIds,
        final List<Integer> variableTypes) {

    try {/*from   ww w. ja  va2 s  .c o m*/
        final String query = " SELECT distinct " //
                + "   pp.variable_id AS " + OBS_SET_VARIABLE_ID + ", " //
                + "   variable.name AS " + OBS_SET_VARIABLE_NAME + ", " //
                + "   variable.definition AS " + OBS_SET_DESCRIPTION + ", " //
                + "   pp.alias AS " + OBS_SET_ALIAS + ", " //
                + "   pp.value as " + OBS_SET_VALUE + ", " + "   variableType.cvterm_id AS "
                + OBS_SET_VARIABLE_TYPE_ID + ", " //
                + "   scale.name AS " + OBS_SET_SCALE + ", " //
                + "   method.name AS " + OBS_SET_METHOD + ", " //
                + "   property.name AS " + OBS_SET_PROPERTY + ", " //
                + "   dataType.cvterm_id AS " + OBS_SET_DATA_TYPE_ID + ", " //
                + "   category.cvterm_id AS " + OBS_SET_CATEGORY_ID + ", " //
                + "   category.name AS " + OBS_SET_CATEGORY_NAME + ", " //
                + "   category.definition AS " + OBS_SET_CATEGORY_DESCRIPTION + ", " //
                + "   (SELECT formula_id FROM formula WHERE target_variable_id = pp.variable_id and active = 1 LIMIT 1) AS "
                + OBS_SET_FORMULA_ID + ", " + "   scaleMinRange.value AS " + OBS_SET_SCALE_MIN_RANGE + ", " //
                + "   scaleMaxRange.value AS " + OBS_SET_SCALE_MAX_RANGE + ", " //
                + "   vo.expected_min AS " + OBS_SET_EXPECTED_MIN + ", " //
                + "   vo.expected_max AS " + OBS_SET_EXPECTED_MAX + ", " //
                + "   cropOntology.value AS " + OBS_SET_CROP_ONTOLOGY_ID + "," + "   pp.value as "
                + OBS_SET_VARIABLE_VALUE + " FROM project dataset " //
                + "   INNER JOIN projectprop pp ON dataset.project_id = pp.project_id " //
                + "   INNER JOIN cvterm variable ON pp.variable_id = variable.cvterm_id " //
                + "   INNER JOIN cvterm variableType ON pp.type_id = variableType.cvterm_id " //
                + "   INNER JOIN cvterm_relationship cvtrscale ON variable.cvterm_id = cvtrscale.subject_id " //
                + "                                            AND cvtrscale.type_id = "
                + TermId.HAS_SCALE.getId() //
                + "   INNER JOIN cvterm scale ON cvtrscale.object_id = scale.cvterm_id " //
                + "   INNER JOIN cvterm_relationship cvtrmethod ON variable.cvterm_id = cvtrmethod.subject_id " //
                + "                                             AND cvtrmethod.type_id = "
                + TermId.HAS_METHOD.getId() //
                + "   INNER JOIN cvterm method ON cvtrmethod.object_id = method.cvterm_id " //
                + "   INNER JOIN cvterm_relationship cvtrproperty ON variable.cvterm_id = cvtrproperty.subject_id " //
                + "                                               AND cvtrproperty.type_id = "
                + TermId.HAS_PROPERTY.getId() //
                + "   INNER JOIN cvterm property ON cvtrproperty.object_id = property.cvterm_id " //
                + "   INNER JOIN cvterm_relationship cvtrdataType ON scale.cvterm_id = cvtrdataType.subject_id " //
                + "                                               AND cvtrdataType.type_id = "
                + TermId.HAS_TYPE.getId() //
                + "   INNER JOIN cvterm dataType ON cvtrdataType.object_id = dataType.cvterm_id " //
                + "   LEFT JOIN cvterm_relationship cvtrcategory ON scale.cvterm_id = cvtrcategory.subject_id "
                + "                                              AND cvtrcategory.type_id = "
                + TermId.HAS_VALUE.getId() //
                + "   LEFT JOIN cvterm category ON cvtrcategory.object_id = category.cvterm_id " //
                + "   LEFT JOIN cvtermprop scaleMaxRange on scale.cvterm_id = scaleMaxRange.cvterm_id " //
                + "                                         AND scaleMaxRange.type_id = "
                + TermId.MAX_VALUE.getId() //
                + "   LEFT JOIN cvtermprop scaleMinRange on scale.cvterm_id = scaleMinRange.cvterm_id " //
                + "                                         AND scaleMinRange.type_id = "
                + TermId.MIN_VALUE.getId() //
                + "   LEFT JOIN variable_overrides vo ON variable.cvterm_id = vo.cvterm_id " //
                + "                                      AND dataset.program_uuid = vo.program_uuid " //
                + "   LEFT JOIN cvtermprop cropOntology ON cropOntology.cvterm_id = variable.cvterm_id" //
                + "        AND cropOntology.type_id = " + TermId.CROP_ONTOLOGY_ID.getId() + " WHERE " //
                + "   dataset.project_id in (:observationSetIds) " //
                + "   AND pp.type_id in (:variableTypes) " + " ORDER BY pp.rank ";

        final SQLQuery sqlQuery = this.getSession().createSQLQuery(query);
        sqlQuery.setParameterList("observationSetIds", observationSetIds);
        sqlQuery.setParameterList("variableTypes", variableTypes);
        sqlQuery.addScalar(OBS_SET_VARIABLE_ID).addScalar(OBS_SET_VARIABLE_NAME).addScalar(OBS_SET_DESCRIPTION)
                .addScalar(OBS_SET_ALIAS).addScalar(OBS_SET_VALUE).addScalar(OBS_SET_VARIABLE_TYPE_ID)
                .addScalar(OBS_SET_SCALE).addScalar(OBS_SET_METHOD).addScalar(OBS_SET_PROPERTY)
                .addScalar(OBS_SET_DATA_TYPE_ID).addScalar(OBS_SET_CATEGORY_ID).addScalar(OBS_SET_CATEGORY_NAME)
                .addScalar(OBS_SET_CATEGORY_DESCRIPTION).addScalar(OBS_SET_SCALE_MIN_RANGE, new DoubleType())
                .addScalar(OBS_SET_SCALE_MAX_RANGE, new DoubleType())
                .addScalar(OBS_SET_EXPECTED_MIN, new DoubleType())
                .addScalar(OBS_SET_EXPECTED_MAX, new DoubleType())
                .addScalar(OBS_SET_FORMULA_ID, new IntegerType()).addScalar(OBS_SET_CROP_ONTOLOGY_ID)
                .addScalar(OBS_SET_VARIABLE_VALUE);

        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        final List<Map<String, Object>> results = sqlQuery.list();

        final Map<Integer, MeasurementVariable> variables = new LinkedHashMap<>();

        for (final Map<String, Object> result : results) {
            final Integer variableId = (Integer) result.get("variableId");

            if (!variables.containsKey(variableId)) {
                variables.put(variableId, new MeasurementVariable());

                final MeasurementVariable measurementVariable = variables.get(variableId);

                measurementVariable.setTermId(variableId);
                measurementVariable.setName((String) result.get(OBS_SET_VARIABLE_NAME));
                measurementVariable.setAlias((String) result.get(OBS_SET_ALIAS));
                measurementVariable.setValue((String) result.get(OBS_SET_VALUE));
                measurementVariable.setDescription((String) result.get(OBS_SET_DESCRIPTION));
                measurementVariable.setScale((String) result.get(OBS_SET_SCALE));
                measurementVariable.setMethod((String) result.get(OBS_SET_METHOD));
                measurementVariable.setProperty((String) result.get(OBS_SET_PROPERTY));
                final VariableType variableType = VariableType
                        .getById((Integer) result.get(OBS_SET_VARIABLE_TYPE_ID));
                measurementVariable.setVariableType(variableType);
                //TODO: fix the saving of Treatment Factor Variables in the projectprop table.
                // Right now, the saved typeid is 1100. It should be 1809(VariableType.TREATMENT_FACTOR.getid())
                if (variableType != null) {
                    measurementVariable.setFactor(!variableType.getRole().equals(PhenotypicType.VARIATE));
                }
                final DataType dataType = DataType.getById((Integer) result.get(OBS_SET_DATA_TYPE_ID));
                measurementVariable.setDataType(dataType.getName());
                measurementVariable.setDataTypeId(dataType.getId());

                final Integer formulaId = (Integer) result.get(OBS_SET_FORMULA_ID);
                if (formulaId != null) {
                    final Formula formula = (Formula) this.getSession().createCriteria(Formula.class)
                            .add(Restrictions.eq("formulaId", formulaId)).add(Restrictions.eq("active", true))
                            .uniqueResult();
                    if (formula != null) {
                        measurementVariable.setFormula(FormulaUtils.convertToFormulaDto(formula));
                    }
                }

                final Double scaleMinRange = (Double) result.get(OBS_SET_SCALE_MIN_RANGE);
                final Double scaleMaxRange = (Double) result.get(OBS_SET_SCALE_MAX_RANGE);
                final Double expectedMin = (Double) result.get(OBS_SET_EXPECTED_MIN);
                final Double expectedMax = (Double) result.get(OBS_SET_EXPECTED_MAX);

                measurementVariable.setMinRange(expectedMin != null ? expectedMin : scaleMinRange);
                measurementVariable.setMaxRange(expectedMax != null ? expectedMax : scaleMaxRange);
                measurementVariable.setScaleMinRange(scaleMinRange);
                measurementVariable.setScaleMaxRange(scaleMaxRange);
                measurementVariable.setVariableMinRange(expectedMin);
                measurementVariable.setVariableMaxRange(expectedMax);
                measurementVariable.setCropOntology((String) result.get(OBS_SET_CROP_ONTOLOGY_ID));
            }

            final MeasurementVariable measurementVariable = variables.get(variableId);

            if (measurementVariable.getValue() == null || measurementVariable.getValue().isEmpty()) {
                measurementVariable.setValue((String) result.get(OBS_SET_VARIABLE_VALUE));
            }

            final Object categoryId = result.get(OBS_SET_CATEGORY_ID);
            if (categoryId != null) {
                if (measurementVariable.getPossibleValues() == null
                        || measurementVariable.getPossibleValues().isEmpty()) {
                    measurementVariable.setPossibleValues(new ArrayList<ValueReference>());
                }
                final ValueReference valueReference = //
                        new ValueReference((Integer) categoryId, //
                                Objects.toString(result.get(OBS_SET_CATEGORY_NAME)), //
                                Objects.toString(result.get(OBS_SET_CATEGORY_DESCRIPTION)));
                if (!measurementVariable.getPossibleValues().contains(valueReference)) {
                    measurementVariable.getPossibleValues().add(valueReference);
                }
            }
        }

        return new ArrayList<>(variables.values());
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error getting datasets variables for dataset=" + observationSetIds + ": " + e.getMessage(), e);
    }
}