List of usage examples for org.hibernate SQLQuery setResultTransformer
@Deprecated Query<R> setResultTransformer(ResultTransformer transformer);
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); } }