Java tutorial
/* * eGov SmartCity eGovernance suite aims to improve the internal efficiency,transparency, * accountability and the service delivery of the government organizations. * * Copyright (C) 2017 eGovernments Foundation * * The updated version of eGov suite of products as by eGovernments Foundation * is available at http://www.egovernments.org * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see http://www.gnu.org/licenses/ or * http://www.gnu.org/licenses/gpl.html . * * In addition to the terms of the GPL license to be adhered to in using this * program, the following additional terms are to be complied with: * * 1) All versions of this program, verbatim or modified must carry this * Legal Notice. * Further, all user interfaces, including but not limited to citizen facing interfaces, * Urban Local Bodies interfaces, dashboards, mobile applications, of the program and any * derived works should carry eGovernments Foundation logo on the top right corner. * * For the logo, please refer http://egovernments.org/html/logo/egov_logo.png. * For any further queries on attribution, including queries on brand guidelines, * please contact contact@egovernments.org * * 2) Any misrepresentation of the origin of the material is prohibited. It * is required that all modified versions of this material be marked in * reasonable ways as different from the original version. * * 3) This license does not grant any rights to any user of the program * with regards to rights under trademark law for use of the trade names * or trademarks of eGovernments Foundation. * * In case of any queries, you can reach eGovernments Foundation at contact@egovernments.org. * */ package org.egov.pgr.dashboard.repository; import org.egov.pgr.config.properties.GrievanceApplicationSettings; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.joda.time.DateTime; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import java.util.Date; import java.util.List; import static org.egov.infra.utils.DateUtils.endOfGivenDate; import static org.egov.infra.utils.DateUtils.startOfGivenDate; @Repository public class DashboardRepository { @PersistenceContext private EntityManager entityManager; @Autowired private GrievanceApplicationSettings grievanceApplicationSettings; public List<Object[]> fetchComplaintResolutionTrendBetween(final Date fromDate, final Date toDate) { return fetchDateRangeData("pgr.comp.resolution.weekly.trend", fromDate, toDate); } public List<Object[]> fetchComplaintRegistrationTrendBetween(final Date fromDate, final Date toDate) { return fetchDateRangeData("pgr.comp.reg.weekly.trend", fromDate, toDate); } public List<Object[]> fetchMonthlyAggregateBetween(final Date fromDate, final Date toDate) { return fetchDateRangeData("pgr.comp.six.month.aggr", fromDate, toDate); } public List<Object[]> fetchComplaintTypeWiseBetween(final Date fromDate, final Date toDate) { return fetchDateRangeData("pgr.comp.type.wise.perc", fromDate, toDate); } public List<Object[]> fetchWardwisePerformanceTill(final DateTime toDate) { final SQLQuery qry = getQuery("pgr.wardwiseperformance"); qry.setParameter("thirteenDaysBefore", endOfGivenDate(toDate.minusDays(13)).toDate()); qry.setParameter("fourteenDaysBefore", startOfGivenDate(toDate.minusDays(14)).toDate()); qry.setParameter("currentDate", endOfGivenDate(toDate).toDate()); return qry.list(); } public Object[] fetchComplaintAgeing(final String querykey, final String wardName) { final SQLQuery qry = getQuery(querykey); final DateTime currentDate = new DateTime(); qry.setParameter("grtthn90", endOfGivenDate(currentDate.minusDays(90)).toDate()); qry.setParameter("lsthn90", endOfGivenDate(currentDate.minusDays(90)).toDate()); qry.setParameter("grtthn45", endOfGivenDate(currentDate.minusDays(45)).toDate()); qry.setParameter("grtthn15", endOfGivenDate(currentDate.minusDays(15)).toDate()); qry.setParameter("lsthn45", endOfGivenDate(currentDate.minusDays(45)).toDate()); qry.setParameter("lsthn15", endOfGivenDate(currentDate.minusDays(15)).toDate()); qry.setParameter("currdate", endOfGivenDate(currentDate).toDate()); if (wardName != null) qry.setParameter("wardName", wardName); return (Object[]) qry.uniqueResult(); } public List<Object[]> fetchOpenComplaintAggregateBetween(final DateTime fromDate, final DateTime toDate) { return fetchDateRangeData("pgr.open.comp.aggr", fromDate.toDate(), toDate.toDate()); } public List<Object[]> fetchComplaintsByComplaintTypeGroupByWard(final Long complaintTypeId, final DateTime fromDate, final DateTime toDate) { final SQLQuery qry = getQuery("pgr.bndry.wise.perc"); qry.setParameter("fromDate", fromDate.toDate()); qry.setParameter("toDate", toDate.toDate()); qry.setParameter("compTypeId", complaintTypeId); return qry.list(); } public List<Object[]> fetchTopComplaintsBetween(final Date fromDate, final Date toDate) { return fetchDateRangeData("pgr.top.comp.types", fromDate, toDate); } private List<Object[]> fetchDateRangeData(final String query, final Date fromDate, final Date toDate) { final SQLQuery qry = getQuery(query); qry.setParameter("fromDate", fromDate); qry.setParameter("toDate", toDate); return qry.list(); } public List<Object[]> fetchGISCompPerPropertyWardWise() { final SQLQuery qry = getQuery("pgr.comp.per.property.six.month.wardwise"); return qry.list(); } public List<Object[]> fetchGISCompRedressedWardWise() { final SQLQuery qry = getQuery("pgr.comp.redressed.six.month.wardwise"); return qry.list(); } public List<Object[]> fetchGISRegCompWardWise() { final SQLQuery qry = getQuery("pgr.comp.reg.six.month.wardwise"); return qry.list(); } private SQLQuery getQuery(final String sqlKey) { return entityManager.unwrap(Session.class).createSQLQuery(grievanceApplicationSettings.getValue(sqlKey)); } public List<Object[]> fetchTopComplaintsForCurrentMonthBetween(final Date fromDate, final Date toDate) { return fetchDateRangeData("pgr.top.comp.types.current.month", fromDate, toDate); } }