Java tutorial
/* * eGov suite of products aim to improve the internal efficiency,transparency, * accountability and the service delivery of the government organizations. * * Copyright (C) <2015> 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. * * 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.ptis.domain.dao.property; import org.apache.log4j.Logger; import org.egov.commons.Installment; import org.egov.commons.dao.InstallmentDao; import org.egov.infra.admin.master.entity.Boundary; import org.egov.infra.admin.master.entity.Module; import org.egov.infra.admin.master.service.BoundaryService; import org.egov.infra.admin.master.service.ModuleService; import org.egov.infra.exception.ApplicationRuntimeException; import org.egov.infra.persistence.entity.Address; import org.egov.infra.validation.exception.ValidationException; import org.egov.ptis.constants.PropertyTaxConstants; import org.egov.ptis.domain.dao.demand.PtDemandDao; import org.egov.ptis.domain.entity.objection.RevisionPetition; import org.egov.ptis.domain.entity.property.BasicProperty; import org.egov.ptis.domain.entity.property.Property; import org.egov.ptis.domain.entity.property.PropertyAddress; import org.egov.ptis.domain.entity.property.PropertyID; import org.egov.ptis.domain.entity.property.PropertyImpl; import org.egov.ptis.domain.entity.property.SearchResult; import org.egov.ptis.exceptions.PropertyNotFoundException; import org.hibernate.Criteria; import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.criterion.DetachedCriteria; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import org.hibernate.criterion.Subqueries; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import java.math.BigDecimal; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import java.util.StringTokenizer; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_BOUNDARY_ID; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_DEFAULTER_FROM_AMOUNT; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_DEFAULTER_TO_AMOUNT; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_DEMAND_FROM_AMOUNT; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_DEMAND_TO_AMOUNT; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_NEW_HOUSE_NO; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_OWNER_NAME; import static org.egov.ptis.constants.PropertyTaxConstants.SRCH_PROPERTY_TYPE; @Repository(value = "searchPropertyDAO") @Transactional(readOnly = true) public class SearchPropertyHibernateDAO implements SearchPropertyDAO { private static final Logger LOGGER = Logger.getLogger(SearchPropertyHibernateDAO.class); @PersistenceContext private EntityManager entityManager; private Session getCurrentSession() { return entityManager.unwrap(Session.class); } @Autowired private BoundaryService boundaryService; @Autowired private ModuleService moduleDao; @Autowired private InstallmentDao installmentDao; @Autowired private BasicPropertyDAO basicPropertyDAO; @Autowired private PtDemandDao ptDemandDAO; @Autowired private PropertyIDDAO propertyIDDAO; @Autowired private PropertyDAO propertyDAO; @Override public SearchResult getBasicPropertyByRegNum(String regNum) throws PropertyNotFoundException { if (regNum == null || regNum.equals("")) { throw new ApplicationRuntimeException("Reg Num /Folio No is not Set during PropertySearch !!"); } LOGGER.debug("getBasicPropertyByRegNum : regNum : " + regNum); BasicProperty basicProperty = null; Property property = null; SearchResult retSearchResult = null; try { basicProperty = basicPropertyDAO.getBasicPropertyByRegNum(regNum); if (basicProperty != null) { property = basicProperty.getProperty(); if (property != null) { retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(regNum); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); retSearchResult.setAddress(basicProperty.getAddress().toString()); retSearchResult.setBasicPropertyId(String.valueOf(basicProperty.getId())); } } else { throw new PropertyNotFoundException( "Search Failed No Such Property Existing : BasicProperty is Null"); } } catch (HibernateException e) { PropertyNotFoundException pnf = new PropertyNotFoundException( "Hibernate Exception In getBasicPropertyByRegNum: " + e.getMessage()); pnf.initCause(e); throw pnf; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getBasicPropertyByRegNum : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getBasicPropertyByRegNum"); } catch (Exception e) { LOGGER.error("Exception in getBasicPropertyByRegNum : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getBasicPropertyByRegNum"); } return retSearchResult; } @Override public SearchResult getPropertyByPropertyId(String propertyId) throws PropertyNotFoundException { if (propertyId == null || propertyId.trim().equals("")) { throw new ApplicationRuntimeException("propertyId is not Set during PropertySearch !!"); } BasicProperty basicProperty = null; PropertyImpl property = null; SearchResult retSearchResult = null; BigDecimal currDemand = BigDecimal.ZERO; BigDecimal currDemandDue = BigDecimal.ZERO; BigDecimal currCollection = BigDecimal.ZERO; BigDecimal arrearsDue = BigDecimal.ZERO; BigDecimal arrDemand = BigDecimal.ZERO; BigDecimal arrCollection = BigDecimal.ZERO; try { basicProperty = basicPropertyDAO.getAllBasicPropertyByPropertyID(propertyId); if (basicProperty != null) { property = (PropertyImpl) basicProperty.getProperty(); if (property != null) { LOGGER.debug("getPropertyByPropertyId : property id : " + property.getId()); Map<String, BigDecimal> DmdCollMap = ptDemandDAO.getDemandCollMap(property); currDemand = DmdCollMap.get(PropertyTaxConstants.CURR_FIRSTHALF_DMD_STR); arrDemand = DmdCollMap.get(PropertyTaxConstants.ARR_DMD_STR); currCollection = DmdCollMap.get(PropertyTaxConstants.CURR_FIRSTHALF_COLL_STR); arrCollection = DmdCollMap.get(PropertyTaxConstants.ARR_COLL_STR); currDemandDue = currDemand.subtract(currCollection); arrearsDue = arrDemand.subtract(arrCollection); PropertyAddress propertyAddress = basicProperty.getAddress(); retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(basicProperty.getOldMuncipalNum()); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); // building Owner name either from normal or local retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); // building address according to the property id which gives // basic property retSearchResult.setAddress(propertyAddress.toString()); if (currDemand != null) { retSearchResult.setCurrDemand(currDemand); } if (currDemandDue != null) { retSearchResult.setCurrDemandDue(currDemandDue); } if (arrearsDue != null) { retSearchResult.setArrearDue(arrearsDue); } retSearchResult.setBasicPropertyId(String.valueOf(basicProperty.getId())); } } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getPropertyByPropertyId: " + e.getMessage()); exception.initCause(e); throw exception; } catch (Exception e) { LOGGER.error("Exception in getPropertyByPropertyId : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByPropertyId"); } return retSearchResult; } /* * -------------------------------------------------------------------------- * ---------------------------------------------------------------------- */ @Override public List getPropertyByBoundry(Integer zoneID, Integer wardID, Integer colonyID) throws PropertyNotFoundException { if (zoneID == null || wardID == null || colonyID == null) { throw new ApplicationRuntimeException("Parameters not Set during PropertySearch based on Boundry!!"); } LOGGER.info(">>>>>>>>>>>>>colonyId" + colonyID); BasicProperty basicProperty = null; PropertyID propertyID = null; PropertyImpl property = null; SearchResult retSearchResult = null; List retList = null; try { List propIdList = propertyIDDAO.getPropertyIDByBoundry(zoneID, wardID, colonyID); if (propIdList != null && !propIdList.isEmpty()) { retList = new ArrayList(); for (Iterator iter = propIdList.iterator(); iter.hasNext();) { propertyID = (PropertyID) iter.next(); basicProperty = propertyID.getBasicProperty(); LOGGER.info(">>>>>>>>>>>>>5basicProperty :" + basicProperty); if (basicProperty != null) { property = (PropertyImpl) basicProperty.getProperty(); if (property != null) { LOGGER.info("::::::::::::::::::::property" + property.getId()); retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(basicProperty.getOldMuncipalNum()); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); retSearchResult.setAddress(basicProperty.getAddress().toString()); // Get the Basic Property ID and set it retSearchResult.setBasicPropertyId(String.valueOf(basicProperty.getId()));// Added // by // Rajalskhmi // D.N. retList.add(retSearchResult); } } } } else { throw new PropertyNotFoundException( "Search Failed No Such Property Existing :Searching Property Based on Colony No !!"); } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getPropertyByBoundry: " + e.getMessage()); exception.initCause(e); throw exception; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getPropertyByBoundry : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getPropertyByBoundry"); } catch (Exception e) { LOGGER.error("Exception in getPropertyByBoundry : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByBoundry"); } return retList; } /* * -------------------------------------------------------------------------- * -- * ------------------------------------------------------------------------ * --------- */ @Override public List getPropertyIDByBoundryForWardBlockStreet(Integer wardID, Integer blockID, Integer streetID) throws PropertyNotFoundException { if (wardID == null || blockID == null || streetID == null) { throw new ApplicationRuntimeException("Parameters not Set during PropertySearch based on Boundry!!"); } LOGGER.info(">>>>>>>>>>>>>streetID" + streetID); LOGGER.info("inside ward block street "); BasicProperty basicProperty = null; PropertyID propertyID = null; PropertyImpl property = null; SearchResult retSearchResult = null; List retList = null; try { List propIdList = propertyIDDAO.getPropertyIDByBoundryForWardBlockStreet(wardID, blockID, streetID); if (propIdList != null && !propIdList.isEmpty()) { retList = new ArrayList(); for (Iterator iter = propIdList.iterator(); iter.hasNext();) { propertyID = (PropertyID) iter.next(); basicProperty = propertyID.getBasicProperty(); LOGGER.info(">>>>>>>>>>>>>5basicProperty :" + basicProperty); if (basicProperty != null) { property = (PropertyImpl) basicProperty.getProperty(); if (property != null) { LOGGER.info("::::::::::::::::::::property" + property.getId()); retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(basicProperty.getOldMuncipalNum()); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); retSearchResult.setAddress(basicProperty.getAddress().toString()); // Get the Basic Property ID and set it retSearchResult.setBasicPropertyId(String.valueOf(basicProperty.getId()));// Added // by // Rajalskhmi // D.N. retList.add(retSearchResult); } } } } else { throw new PropertyNotFoundException( "Search Failed No Such Property Existing :Searching Property Based on Colony No !!"); } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getPropertyByBoundry: " + e.getMessage()); exception.initCause(e); throw exception; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getPropertyByBoundry : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getPropertyByBoundry"); } catch (Exception e) { LOGGER.error("Exception in getPropertyByBoundry : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByBoundry"); } return retList; } /* * -------------------------------------------------------------------------- * -- * ------------------------------------------------------------------------ * --------- */ @Override public SearchResult getPropertyByBoundryAndMunNo(Integer zoneID, Integer wardID, Integer colonyID, Integer munNo) throws PropertyNotFoundException { if (zoneID == null || wardID == null || colonyID == null || munNo == null) { throw new ApplicationRuntimeException("Parameters not Set during PropertySearch based on Boundry!!"); } BasicProperty basicProperty = null; PropertyID propertyID = null; Property property = null; SearchResult retSearchResult = null; try { // These are IDs not Numbers but in database FK is number not id so // search acc to boundry numbers propertyID = propertyIDDAO.getPropertyByBoundryAndMunNo(zoneID, wardID, colonyID, munNo); if (propertyID != null) { basicProperty = propertyID.getBasicProperty(); if (basicProperty != null) { property = basicProperty.getProperty(); if (property != null) { retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(basicProperty.getOldMuncipalNum()); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); retSearchResult.setAddress(basicProperty.getAddress().toString()); // Get the Basic Property ID and set it retSearchResult.setBasicPropertyId(String.valueOf(basicProperty.getId()));// Added } } } else { throw new PropertyNotFoundException( "Search Failed No Such Property Existing : Searching Property Based on Colony No,Please Try Later !!"); } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getPropertyByBoundryAndMunNo: " + e.getMessage()); exception.initCause(e); throw exception; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getPropertyByBoundry : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getPropertyByBoundry"); } catch (Exception e) { LOGGER.error("Exception in getPropertyByBoundryAndMunNo : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByBoundryAndMunNo"); } return retSearchResult; } /* * -------------------------------------------------------------------------- * -- * ------------------------------------------------------------------------ * --------- */ /* * This API returns the list of non history default properties in the given * boundary which match the given owner name and phone number It matches the * ownerName field individually in first, middle and last name for all the * owners of the property. It matches the phNumber with the home, office and * Mobile numbers for all the owners of the property returns list of * ownerNames and property info . */ @Override public List getPropertyByBoundryAndOwnerName(Integer boundryID, String ownerName, String phNumber) throws PropertyNotFoundException { if (boundryID == null) { throw new ApplicationRuntimeException("Parameters not Set during PropertySearch based on Boundry!!"); } Query qry; try { LOGGER.info("Executing testChequeQry query................................................"); Boundary boundary = boundaryService.getBoundaryById(boundryID.longValue()); LOGGER.info("testQry executed......................................................"); // seems to be best. other thing is add distinct to the query to not // get duplicate recored (since the // query yields to a cartiesian product). // qrySecTest = // session.createQuery("From PropertyImpl pi where pi.id_Adm_Bndry = // :boundryID and pi.propertyOwnerSet.firstName like :firstName"); StringBuffer qryStr = new StringBuffer(2000); LOGGER.info("-----------search by boundary--------"); qryStr.append( "select distinct pi From PropertyImpl pi left join fetch pi.basicProperty bp where bp.boundary = :boundary and pi.status='A' and pi.isDefaultProperty='Y' and bp.active='Y' ");// order // by // bp.upicNo // asc LOGGER.info("searching for boundary" + qryStr.toString() + "boundaryId" + boundryID); qry = getCurrentSession().createQuery(qryStr.toString()); // ownername is available create query and set ownername if (ownerName != null && !ownerName.equals("")) { boolean phNumFound = false; qryStr.append( " and (pi.propertyOwnerSet.firstName like :firstName or pi.propertyOwnerSet.middleName like :firstName or pi.propertyOwnerSet.lastName like :firstName )");// or // pi.propertyOwnerSet.Owner.middleName // like // :middleName // or // pi.propertyOwnerSet.Owner.lastName // like // :lastName LOGGER.info("Query String for ownername" + qryStr.toString() + " ........ " + ownerName + "///////" + ownerName); qry = getCurrentSession().createQuery(qryStr.toString()); // phoneNumber is not null create query and set phone number to // that. if (phNumber != null && !phNumber.equals("")) { phNumFound = true; qryStr.append( " and (pi.propertyOwnerSet.homePhone =:homePhone or pi.propertyOwnerSet.officePhone =:homePhone or pi.propertyOwnerSet.mobilePhone =:homePhone)"); LOGGER.info("----------Searching With Phnumber-----" + qryStr.toString()); LOGGER.debug("allvalues set for phnumber"); } qryStr.append(" order by bp.upicNo asc"); qry = getCurrentSession().createQuery(qryStr.toString()); qry.setString("firstName", ownerName + "%");// ptisCacheInterface.encodingName(ownerName) if (phNumFound) { qry.setString("homePhone", phNumber); } LOGGER.debug("allvalues set for ownerFullName"); } qry.setEntity("boundary", boundary); List propertyList = qry.list(); LOGGER.info("query given list of props" + propertyList.size()); if (propertyList != null) { return getSearchResultList(propertyList); } else { throw new PropertyNotFoundException( "Internal Server Error in Searching Property Based on Colony No,Please Try Later !!"); } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getPropertyByBoundryAndOwnerName: " + e.getMessage()); exception.initCause(e); throw exception; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getPropertyByBoundryAndOwnerName : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getPropertyByBoundryAndOwnerName"); } catch (Exception e) { LOGGER.error("Exception in getPropertyByBoundryAndOwnerName : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByBoundryAndOwnerName"); } } /* * -------------------------------------------------------------------------- * ------------------------------------------------------------------------ */ /* * added by suhasini by passing oldMuncipalNo as parameter ,this method will * give list of BasicProperties ,through each BasicProperty getting address * object ,Property object and siteNo,KhataNo .Property will give owners * information. */ @Override public List getPropertyByOldMuncipalNo(String oldMuncipalNo) throws PropertyNotFoundException { if (oldMuncipalNo == null || oldMuncipalNo.trim().equals("")) { throw new ApplicationRuntimeException("oldMuncipalNo is not Set during PropertySearch !!"); } LOGGER.info(">>>>>>>>>>>>>oldMuncipalNo" + oldMuncipalNo); BasicProperty basicProperty = null; Property property = null; SearchResult retSearchResult = null; List retList = null; try { List basicPropertyList = basicPropertyDAO.getBasicPropertyByOldMunipalNo(oldMuncipalNo); if (basicPropertyList != null) { retList = new ArrayList(); for (Iterator iter = basicPropertyList.iterator(); iter.hasNext();) { basicProperty = (BasicProperty) iter.next(); property = basicProperty.getProperty(); if (property != null) { retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(basicProperty.getOldMuncipalNum()); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); retSearchResult.setAddress(basicProperty.getAddress().toString()); // Get the Basic Property ID and set it retSearchResult.setBasicPropertyId(String.valueOf(basicProperty.getId()));// Added // by // Rajalskhmi // D.N. retList.add(retSearchResult); } } } else { throw new PropertyNotFoundException( "Search Failed No Such Property Existing : Searching Property Based on oldMuncipalNo,Please Try Later !!"); } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getPropertyByOldMuncipalNo: " + e.getMessage()); exception.initCause(e); throw exception; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getPropertyByOldMuncipalNo : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getPropertyByOldMuncipalNo"); } catch (Exception e) { LOGGER.error("Exception in getPropertyByOldMuncipalNo : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByOldMuncipalNo"); } return retList; } /* * ward or block search by passing the child boundaries list,owner * name,phoneNo */ @Override public List getPropertiesById(List lstboundaries, String OwnerName, String phoneNumber) throws PropertyNotFoundException { StringBuffer qryStr = new StringBuffer(2000); List propertyList = null; List streetIds = null; Query qry; LOGGER.info("-----------search by boundary--------"); /* * select distinct prop.id from BasicPropertyImpl BP left join * BP.property prop left join prop.propertyOwnerSet po where * BP.boundary.id in (:bndryIdList) and po.id in (select own.id from * Owner own where own.firstName like :firstStr and own.id = po.id) */ try { if (lstboundaries != null) { LOGGER.info(">>>>>>>>getting list of boundary Ids from boundary list"); streetIds = new ArrayList(); for (Iterator lstItr = lstboundaries.iterator(); lstItr.hasNext();) { List resultList = (List) lstItr.next(); for (Iterator resItr = resultList.iterator(); resItr.hasNext();) { Boundary bndry = (Boundary) resItr.next(); Long bndryId = bndry.getId(); LOGGER.debug("<<<<<<< bndryId = " + bndryId); if (bndryId != null) { if (!streetIds.contains(bndryId)) { streetIds.add(bndryId); } } } } } /* * Here the search name is checked for any white space between first * r middle r last name and if found white space is replaced by '%' * and the resulting search string is passed into the query along * with the original search string. Added by yashodha */ String OwnerNewName = null; if (OwnerName != null) { StringBuffer OwnName = new StringBuffer(1500); StringTokenizer strToken = new StringTokenizer(OwnerName); while (strToken.hasMoreTokens()) { OwnName.append(strToken.nextToken(" ")); OwnName.append("%"); } OwnerNewName = OwnName.toString(); LOGGER.info("The Rearranged String is!!!!!!!!!!!!" + OwnerNewName); } /* * qryStr.append("select distinct prop.id from BasicPropertyImpl BP * left join BP.property prop left join prop.propertyOwnerSet * po" + " where BP.boundary.id in (:streetIds) and po.id in(select * own.id from Owner own where own.firstName like :OwnerName and * own.id = po.id)" ); */ // qryStr.append("select distinct prop from BasicPropertyImpl BP // left join BP.property prop where BP.boundary.id in // (:streetIds)"); qryStr.append( "select distinct prop from PropertyImpl prop left join prop.basicProperty BP left join prop.propertyOwnerSet po" + " where BP.boundary.id in (:streetIds) and po.id in(select distinct own.id from Owner own where upper(own.firstName) like :OwnerName or upper(own.middleName) like :OwnerName or upper(own.lastName) like :OwnerName " + "or upper(own.firstName)||upper(own.middleName) like :OwnName or upper(own.firstName)||upper(own.lastName) like :OwnName or upper(own.firstName)||upper(own.middleName)||upper(own.lastName) like :OwnName or upper(own.middleName)||upper(own.lastName) like :OwnName)"); /* * previously working query. commented by yashodha on 15/03/2007 * qryStr.append( "select distinct prop from PropertyImpl prop left * join prop.basicProperty BP left join prop.propertyOwnerSet * po" + " where BP.boundary.id in (:streetIds) and po.id in(select * distinct own.id from Owner own where upper(own.firstName) like * :OwnerName or upper(own.middleName) like :OwnerName or * upper(own.lastName) like :OwnerName )" ); */ qry = getCurrentSession().createQuery(qryStr.toString()); // qry.setEntity("streetIds", streetIds); if (phoneNumber != null && !phoneNumber.equals("")) { qryStr.append( " and po.id in(select distinct own.id from Owner own where own.homePhone =:phoneNumber or own.officePhone =:phoneNumber or own.mobilePhone =:phoneNumber) "); LOGGER.info("----------Searching With Phnumber-----" + qryStr.toString()); qry = getCurrentSession().createQuery(qryStr.toString()); qry.setString("phoneNumber", phoneNumber); LOGGER.debug("allvalues set for phnumber"); } qry.setParameterList("streetIds", streetIds); qry.setString("OwnerName", OwnerName.toUpperCase() + "%"); qry.setString("OwnName", OwnerNewName.toUpperCase()); propertyList = qry.list(); } catch (Exception e) { LOGGER.error("error in query" + e.getMessage()); } return getSearchResultList(propertyList); } /* * added by suhasini by passing khataNumber as parameter api rerurns * searchresult obj,which contains propertyid,owner name,proeprty address * and khata number */ @Override public SearchResult getPropertyByKhataNumber(String khataNumber) throws PropertyNotFoundException { if (khataNumber == null || khataNumber.trim().equals("")) { throw new ApplicationRuntimeException("khataNumber is not Set during PropertySearch !!"); } LOGGER.info(">>>>>>>>>>>>>khataNumber" + khataNumber); BasicProperty basicProperty = null; PropertyImpl property = null; SearchResult retSearchResult = null; try { basicProperty = basicPropertyDAO.getBasicPropertyByRegNum(khataNumber); if (basicProperty != null) { property = (PropertyImpl) basicProperty.getProperty(); if (property != null) { LOGGER.info("property id" + property.getId()); retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(basicProperty.getOldMuncipalNum()); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); // building Owner name either from normal or local retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); retSearchResult.setAddress(basicProperty.getAddress().toString()); } } else { throw new PropertyNotFoundException( "Search Failed No Such Property Existing : Searching Property Based on Khata Number,Please Try Later !!"); } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getPropertyByKhataNumber: " + e.getMessage()); exception.initCause(e); throw exception; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getPropertyByKhataNumber : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getPropertyByKhataNumber"); } catch (Exception e) { LOGGER.error("Exception in getPropertyByKhataNumber : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByKhataNumber"); } return retSearchResult; } @Override public List getPropertyByRvAmout(Integer boundaryID, Character RvSel, String lowVal, String HighVal) throws PropertyNotFoundException { LOGGER.info(">>>>>>>>>>inside getPropertyByRvAmout>>>>>>>>>>>>>>"); if (RvSel == null || RvSel.equals("")) { throw new ApplicationRuntimeException( "RV amout selection was not Set during PropertySearch based on Boundry!!"); } LOGGER.info("after query execution--------------RvSel--" + RvSel.charValue() + "---------lowVal----------" + lowVal + "--------HighVal--------" + HighVal); Query qry; try { LOGGER.info("Executing testChequeQry query..............."); Boundary boundary = boundaryService.getBoundaryById(boundaryID.longValue()); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("dd/MM/yyyy"); String finEndDate = sdf.format(new Date());//TODO WHEN REQUIRED removing getFinancialYear sdf.format(DateUtils.getFinancialYear().getEndOnOnDate()); StringBuffer qryStr = new StringBuffer(2000); qryStr.append("select distinct pi From PropertyImpl pi inner join pi.basicProperty bp inner join " + "pi.ptDemandARVSet rv where rv.toDate = to_date('" + finEndDate + "','dd/mm/yyyy') and " + "pi.status='A' and bp.active='Y' "); boolean rvLess = false; boolean rvGreater = false; boolean rvBetween = false; boolean rvEqual = false; boolean seatFound = false; if (boundaryID != null && boundaryID.intValue() != 0) { seatFound = true; qryStr.append(" and bp.boundary = :boundary"); LOGGER.info( ">>>>>>>>>>>>>>Search by seat no>>>>>>>>>>>>> " + qryStr.toString() + "....." + boundaryID); } if (RvSel.charValue() == '1') { rvLess = true; qryStr.append(" and rv.arv <= :highVal "); LOGGER.info(">>>>>>>>>>>>>>Search by arv amount rvsel--- 1"); } if (RvSel.charValue() == '2') { rvGreater = true; qryStr.append(" and rv.arv >= :lowVal "); LOGGER.info(">>>>>>>>>>>>>>Search by arv amount rvsel--- 2"); } if (RvSel.charValue() == '3') { rvBetween = true; qryStr.append(" and rv.arv >= :lowVal and rv.arv <= :highVal "); LOGGER.info(">>>>>>>>>>>>>>Search by arv amount rvsel--- 3"); } if (RvSel.charValue() == '4') { rvEqual = true; qryStr.append(" and rv.arv = :lowVal "); LOGGER.info(">>>>>>>>>>>>>>Search by arv amount rvsel--- 2"); } LOGGER.info("query string final--------------" + qryStr.toString()); qry = getCurrentSession().createQuery(qryStr.toString()); if (seatFound) { qry.setEntity("boundary", boundary); } if (rvLess) { qry.setBigDecimal("highVal", new BigDecimal(HighVal)); } if (rvGreater) { qry.setBigDecimal("lowVal", new BigDecimal(lowVal)); } if (rvBetween) { qry.setBigDecimal("lowVal", new BigDecimal(lowVal)); qry.setBigDecimal("highVal", new BigDecimal(HighVal)); } if (rvEqual) { qry.setBigDecimal("lowVal", new BigDecimal(lowVal)); } LOGGER.info("before query execution"); List propertyList = qry.list(); if (propertyList == null) { throw new PropertyNotFoundException( "No Properties Found with the matching Criteria: lowval:" + lowVal + ",highval:" + HighVal); } else { if (propertyList.size() > 200) { List errorList = new ArrayList(); errorList.add(0, "more props"); return errorList; } return getSearchResultList(propertyList); } } catch (Exception e) { LOGGER.info("Excetion in getPropertyByRvAmout----------------" + e); throw new ApplicationRuntimeException("Error in getPropertyByRvAmout", e); } } @Override public List getPropertyByDmdAmout(Integer boundaryID, Character DmdSel, Character DmdChoice, String lowVal, String HighVal) throws PropertyNotFoundException { Map dmdMap = new HashMap(); LOGGER.info("inside getPropertyByDmdAmout"); List propList = new ArrayList(); BigDecimal lowerLimit = BigDecimal.ZERO; BigDecimal upperLimit = BigDecimal.ZERO; LOGGER.info("lowerLimit -----------" + lowerLimit + "---upperLimit-----------" + upperLimit); LOGGER.info( "DmdSel------------" + DmdSel.charValue() + "------DmdChoice-----------" + DmdChoice.charValue()); // --new DCB changes SearchPropertyByDemand searchPropertyByDemand = new // SearchPropertyByDemand(); // --new DCB changes dmdMap = // searchPropertyByDemand.getPropertyDemand(boundaryID,DmdChoice,DmdSel,lowVal,HighVal); try { LOGGER.info("dmdMap.size()----------------------------------------" + dmdMap.size()); if (dmdMap.size() > 200) { List errorList = new ArrayList(); errorList.add(0, "more props"); return errorList; } if (dmdMap != null && !dmdMap.isEmpty()) { Iterator dmdItr = dmdMap.keySet().iterator(); while (dmdItr.hasNext()) { String pid = (String) dmdItr.next(); BasicProperty basiProperty = basicPropertyDAO.findById(Integer.valueOf(pid), false); PropertyImpl property = (PropertyImpl) basiProperty.getProperty(); propList.add(property); } } LOGGER.info("the size of the list-->" + propList.size()); if (propList == null) { throw new PropertyNotFoundException("No Properties Found with the matching Criteria: lowerLimit:" + lowVal + ",upperLimit:" + HighVal); } else { return getSearchResultList(propList); } } catch (Exception e) { LOGGER.info("Excetion in getPropertyByDmdAmout---------" + e); throw new ApplicationRuntimeException("Error in getPropertyByDmdAmout", e); } } /* * getSearchResultList returns List of searchResults by taking properties as * list */ public List getSearchResultList(List propertyList) { BasicProperty basicProperty = null; Set bpSet = new HashSet(); Address address = null; List retList = new ArrayList(); BigDecimal existARV = BigDecimal.ZERO; if (propertyList != null) { for (Iterator iter = propertyList.iterator(); iter.hasNext();) { Property property = (PropertyImpl) iter.next(); if (property != null) { basicProperty = property.getBasicProperty(); // to remove duplicate property Ids this checking taken care if (!bpSet.contains(basicProperty.getUpicNo())) { bpSet.add(basicProperty.getUpicNo()); if (basicProperty != null) { BigDecimal demand = BigDecimal.ZERO; BigDecimal rebate = BigDecimal.ZERO; BigDecimal collection = BigDecimal.ZERO; BigDecimal currDemand = BigDecimal.ZERO; SearchResult retSearchResult = new SearchResult(); retSearchResult.setFolioNumber(basicProperty.getOldMuncipalNum()); retSearchResult.setUpicNumber(basicProperty.getUpicNo()); address = basicProperty.getAddress(); if (address != null) { retSearchResult.setAddress(basicProperty.getAddress().toString()); } retSearchResult.setAssesseeFullName(basicProperty.getFullOwnerName()); // Get the Basic Property ID and set it retSearchResult.setBasicPropertyId(String.valueOf(basicProperty.getId()));// Added // by // Rajalskhmi // D.N. // FIX ME /* * Set<PtDemandARV> ptDemandARVSet = * basicProperty.getProperty() .getPtDemandARVSet(); * if (ptDemandARVSet != null && * !ptDemandARVSet.isEmpty()) { for (PtDemandARV * ptDmdARV : ptDemandARVSet) { if (ptDmdARV != * null) { if (ptDmdARV.getArv() != null && * !ptDmdARV.getArv().equals("")) { existARV = * ptDmdARV.getArv(); } } } } */ retSearchResult.setCurrYearArv(existARV.toString()); demand = (BigDecimal) propertyDAO.getPropertyDemand(basicProperty.getUpicNo()).get(0); rebate = (BigDecimal) propertyDAO.getPropertyRebate(basicProperty.getUpicNo()).get(0); collection = (BigDecimal) propertyDAO.getPropertyCollection(basicProperty.getUpicNo()) .get(0); if (demand != null && !demand.equals(BigDecimal.ZERO)) { currDemand = demand; } if (rebate != null && !rebate.equals(BigDecimal.ZERO)) { currDemand = demand.subtract(rebate); } if (collection != null && !collection.equals(BigDecimal.ZERO)) { currDemand = currDemand.subtract(collection); } retSearchResult.setCurrDemand(currDemand); retList.add(retSearchResult); } } } } } return retList; } /** * This API is used to get List of InActive Properties from * EGPT_BASIC_PROPERTY table and set it to SearchResult obj and return it * * @author Iffath * @param lstBoundaries * @param List * lstBoundaries * @return SearchResult */ @Override public List getInActivePropertyByBoundary(List lstboundaries) throws PropertyNotFoundException { LOGGER.info("getInActivePropertyByBoundary "); List propertyList; List streetIds = null; Query qry; try { LOGGER.info("Executing boundary query................................................"); if (lstboundaries != null) { LOGGER.info(">>>>>>>>getting list of boundary Ids from boundary list"); streetIds = new ArrayList(); for (Iterator lstItr = lstboundaries.iterator(); lstItr.hasNext();) { List resultList = (List) lstItr.next(); for (Iterator resItr = resultList.iterator(); resItr.hasNext();) { Boundary bndry = (Boundary) resItr.next(); Long bndryId = bndry.getId(); LOGGER.debug("<<<<<<< bndryId = " + bndryId); if (bndryId != null) { if (!streetIds.contains(bndryId)) { streetIds.add(bndryId); } } } } } StringBuffer qryStr = new StringBuffer(2000); qryStr.append( "select distinct pi From PropertyImpl pi left join fetch pi.basicProperty bp where bp.boundary.id in (:streetIds) and pi.status='A' and bp.active='N'");// order // by // bp.upicNo // asc qry = getCurrentSession().createQuery(qryStr.toString()); qry.setParameterList("streetIds", streetIds); propertyList = qry.list(); if (propertyList != null) { return getSearchResultList(propertyList); } else { throw new PropertyNotFoundException( "Internal Server Error in Searching InActive Property Based on Boundary,Please Try Later !!"); } } catch (HibernateException e) { PropertyNotFoundException exception = new PropertyNotFoundException( "Hibernate Exception In getInActivePropertyByBoundary: " + e.getMessage()); exception.initCause(e); throw exception; } catch (PropertyNotFoundException e) { LOGGER.error("PropertyNotFoundException in getInActivePropertyByBoundary : " + e.getMessage()); throw new PropertyNotFoundException("Exception in getInActivePropertyByBoundary"); } catch (Exception e) { LOGGER.error("Exception in getInActivePropertyByBoundary : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getInActivePropertyByBoundary"); } } /** * This method called getPropertyByMobileNumber gets List of Property * Objects * * <p> * This method returns List of Property Objects for given mobile Number. * </p> * * @param java * .lang.String mobileNum * * @throws org.egov.ptis.PropertyNotFoundException. */ @Override public List getPropertyByMobileNumber(String mobileNum) throws PropertyNotFoundException { List propList = new ArrayList(); StringBuffer qryStr = new StringBuffer(2000); try { Module module = moduleDao.getModuleByName(PropertyTaxConstants.PTMODULENAME); Installment installment = installmentDao.getInsatllmentByModuleForGivenDate(module, new Date()); if (mobileNum != null && !mobileNum.equals("")) { Query qry = null; qryStr.append( "select distinct pi From PropertyImpl pi left join fetch pi.basicProperty bp left join fetch bp.address ad where ad.mobileNo like :mobileNum and pi.status='A' and pi.installment = :Installment "); qry = getCurrentSession().createQuery(qryStr.toString()); qry.setString("mobileNum", "%" + mobileNum + "%"); qry.setEntity("Installment", installment); propList = qry.list(); } } catch (Exception e) { LOGGER.error("Exception in getPropertyByMobileNumber : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByMobileNumber"); } return propList; } /** * This method called getPropertyByBillNumber gets List of Property Objects * * <p> * This method returns List of Property Objects for given billNumber. * </p> * * @param java * .lang.String billNumber * * @throws org.egov.ptis.PropertyNotFoundException. */ @Override public List getPropertyByBillNumber(String billNumber) throws PropertyNotFoundException { List propList = new ArrayList(); StringBuffer qryStr = new StringBuffer(2000); try { if (billNumber != null && !billNumber.equals("")) { // Getting current installment property always Module module = moduleDao.getModuleByName(PropertyTaxConstants.PTMODULENAME); Installment installment = installmentDao.getInsatllmentByModuleForGivenDate(module, new Date()); Query qry = null; qryStr.append( "From PropertyImpl pi left join fetch pi.basicProperty bp where bp.upicNo like :billNumber and pi.isDefaultProperty='Y' and pi.status='A' and pi.installment = :Installment "); qry = getCurrentSession().createQuery(qryStr.toString()); qry.setString("billNumber", billNumber + "%"); qry.setEntity("Installment", installment); propList = qry.list(); } } catch (Exception e) { LOGGER.error("Exception in getPropertyByBillNumber : " + e.getMessage()); throw new ApplicationRuntimeException("Exception in getPropertyByBillNumber"); } return propList; } /** * This method called getPropertyByBoundryAndOwnerNameAndHouseNo gets List * of Property Objects * * <p> * This method returns List of Property Objects for given * boundaryId,ownerName,newHouseNo.,oldHouseNo. . * </p> * * @param java * .lang.Integer boundryID * * @param java * .lang.String ownerName * * @param java * .lang.String houseNo * * @param java * .lang.String oldHouseNo * * @throws org.egov.ptis.PropertyNotFoundException. */ @Override public List getPropertyByBoundryAndOwnerNameAndHouseNo(Integer boundryID, String ownerName, String newHouseNo, String oldHouseNo) throws PropertyNotFoundException { Query qry; List propertyList = new ArrayList(); try { if (boundryID != null && !boundryID.equals("")) { Boundary boundary = boundaryService.getBoundaryById(boundryID.longValue()); LOGGER.info("boundary.obj................" + boundary); StringBuffer qryStr = new StringBuffer(2000); qryStr.append( "select distinct pi From PropertyImpl pi left join fetch pi.basicProperty bp left join fetch bp.propertyID ppid left join pi.propertyOwnerSet ownerSet where pi.status='A' and pi.isDefaultProperty='Y' and bp.active='Y' "); LOGGER.info("searching for boundary " + qryStr.toString() + "boundaryId" + boundryID); boolean bndryFound = false; boolean wardBndryFound = false; boolean areaBndryFound = false; boolean ownerFound = false; boolean houseFound = false; boolean oldHouseNum = false; if (boundary != null) { String boundryType = boundary.getBoundaryType().getName(); LOGGER.info("testQry executed.......boundryType................." + boundryType); if (boundryType != null && boundryType.endsWith("Ward")) { wardBndryFound = true; qryStr.append(" and ppid.ward = :boundary"); } else if (boundryType != null && boundryType.endsWith("Area")) { areaBndryFound = true; qryStr.append(" and ppid.area = :boundryID"); } else if (boundryType != null && boundryType.endsWith("Locality")) { bndryFound = true; qryStr.append(" and ppid.locality = :boundryID"); } else if (boundryType != null && boundryType.endsWith("Street")) { bndryFound = true; qryStr.append(" and ppid.street = :boundryID"); } } if (ownerName != null && !ownerName.equals("")) { ownerFound = true; qryStr.append( " and (upper(ownerSet.firstName) like :firstName or upper(ownerSet.middleName) like :firstName or upper(ownerSet.lastName) like :firstName) "); // :lastName } if (newHouseNo != null && !newHouseNo.equals("")) { houseFound = true; qryStr.append(" and (upper(pi.basicProperty.address.houseNo) like :houseno ) "); } if (oldHouseNo != null && !oldHouseNo.equals("")) { oldHouseNum = true; qryStr.append(" and (upper(pi.basicProperty.address.doorNumOld) like :oldHouseNo) "); } qryStr.append(" order by bp.id "); LOGGER.debug("query string final--------------" + qryStr.toString()); qry = getCurrentSession().createQuery(qryStr.toString()); if (wardBndryFound) { qry.setEntity("boundary", boundary); } if (areaBndryFound) { qry.setInteger("boundryID", boundryID); } if (bndryFound) { qry.setInteger("boundryID", boundryID); } if (ownerFound) { qry.setString("firstName", "%" + ownerName.toUpperCase() + "%"); } if (houseFound) { qry.setString("houseno", newHouseNo.toUpperCase() + "%"); } if (oldHouseNum) { qry.setString("oldHouseNo", oldHouseNo.toUpperCase() + "%"); } propertyList = qry.list(); } return propertyList; } catch (Exception e) { throw new ApplicationRuntimeException("Error in getPropertyByBoundryAndOwnerNameAndHouseNo", e); } } /** * either objectionNumber or any of the date fields are mandatory * * @param propertyTypeMasterId * @param objectionNumber * @param fromObjection * @param toObjection * @return * @throws ValidationException * when mandatory fields not passed */ @Override public List<Property> getPropertyByObjectionDetails(Long propertyTypeMasterId, String objectionNumber, Date fromObjection, Date toObjection) throws ValidationException { if ((objectionNumber == null || objectionNumber.trim().isEmpty()) && (fromObjection == null && toObjection == null)) throw new ValidationException("ObjectioNumber or ObjectionDate is mandatory", "ObjectioNumber or ObjectionDate is mandatory"); Criteria propertyCriteria = getCurrentSession().createCriteria(PropertyImpl.class, "propertyImpl") .add(Restrictions.eq("status", PropertyTaxConstants.STATUS_ISACTIVE)); DetachedCriteria detachCrtObjection = DetachedCriteria.forClass(RevisionPetition.class); detachCrtObjection.setProjection(Projections.projectionList().add(Projections.property("basicProperty"))); if (propertyTypeMasterId != null && propertyTypeMasterId > 0) { propertyCriteria.createAlias("propertyDetail", "propertyDetail"); propertyCriteria.createAlias("propertyDetail.propertyTypeMaster", "propertyTypeMaster"); propertyCriteria.add(Restrictions.eq("propertyTypeMaster.id", propertyTypeMasterId)); } if (objectionNumber != null && !objectionNumber.trim().isEmpty()) { detachCrtObjection.add(Restrictions.ilike("objectionNumber", objectionNumber)); } if (fromObjection != null && toObjection != null) { detachCrtObjection.add(Restrictions.between("recievedOn", fromObjection, toObjection)); } else if (fromObjection != null) { detachCrtObjection.add(Restrictions.ge("recievedOn", fromObjection)); } else if (toObjection != null) { detachCrtObjection.add(Restrictions.le("recievedOn", toObjection)); } propertyCriteria.add(Subqueries.propertyIn("basicProperty", detachCrtObjection)); return propertyCriteria.list(); } // -------------------------------------------- /** * */ public List getPropertyByBoundryAndQueryParamMap(Map<String, Object> queryParamMap) throws PropertyNotFoundException { String ownerName = null; String newHouseNo = null; String oldHouseNo = null; Integer propTypeId = null; ; BigDecimal dmdFrmAmt = null; BigDecimal dmdToAmt = null; BigDecimal defaultFrmamt = null; BigDecimal defaultToAmt = null; Query qry; List propertyList = new ArrayList(); try { if (queryParamMap.get(SRCH_BOUNDARY_ID) != null) { Integer boundryID = (Integer) queryParamMap.get(SRCH_BOUNDARY_ID); if (boundryID != null && !boundryID.equals("")) { Boundary boundary = boundaryService.getBoundaryById(boundryID.longValue()); LOGGER.info("boundary.obj................" + boundary); if (boundary != null) { String boundryType = boundary.getBoundaryType().getName(); LOGGER.info("testQry executed.......boundryType................." + boundryType); StringBuffer qryStr = new StringBuffer(2000); qryStr.append( "select EGPTP.ID_PROPERTY from egpt_property egptp left outer join egpt_basic_property egptb") .append(" on EGPTP.ID_BASIC_PROPERTY=EGPTB.ID_BASIC_PROPERTY") .append(" left outer join egpt_propertyid egptpid on EGPTB.ID_PROPERTYID=EGPTPID.ID") .append(" left outer join egpt_property_detail egptpd on EGPTP.ID_PROPERTY=EGPTPD.ID_PROPERTY") .append(" left outer join egpt_ptdemand egptde on EGPTP.ID_PROPERTY=EGPTDE.ID_PROPERTY") .append(" left outer join EG_DEMAND_DETAILS egdet on EGPTDE.ID_DEMAND=EGDET.ID_DEMAND") .append(" left outer join eg_demand_reason egdr on EGDET.ID_DEMAND_REASON=egdr.id") .append(" left outer join eg_address egadd on EGPTB.ADDRESSID=EGADD.ADDRESSID") .append(" left outer join EGPT_PROPERTY_OWNER egptprown on EGPTP.ID_PROPERTY=EGPTPROWN.ID_PROPERTY") .append(" left outer join eg_citizen egciti on EGPTPROWN.OWNERID=EGCITI.CITIZENID") .append(" where EGPTP.STATUS='A' and EGPTP.IS_DEFAULT_PROPERTY='Y' and EGPTB.IS_ACTIVE='Y'"); LOGGER.debug("searching for boundary " + qryStr.toString() + "boundaryId" + boundryID); boolean zoneBndryFound = false; boolean bndryFound = false; boolean wardBndryFound = false; boolean areaBndryFound = false; boolean ownerFound = false; boolean houseFound = false; boolean propTypeFound = false; boolean demandFound = false; boolean defaulterFound = false; if (boundryType != null && boundryType.endsWith("Revenue Zone")) { zoneBndryFound = true; qryStr.append(" and EGPTPID.ZONE_NUM= :boundryID"); } else if (boundryType != null && boundryType.endsWith("Revenue Ward")) { wardBndryFound = true; qryStr.append(" and EGPTPID.WARD_ADM_ID = :boundryID"); } else if (boundryType != null && boundryType.endsWith("Revenue Area")) { areaBndryFound = true; qryStr.append(" and EGPTPID.ADM1 = :boundryID"); } else if (boundryType != null && boundryType.endsWith("Revenue Locality")) { bndryFound = true; qryStr.append(" and EGPTPID.ADM2 = :boundryID"); } else if (boundryType != null && boundryType.endsWith("Revenue Street")) { bndryFound = true; qryStr.append(" and EGPTPID.ADM3 = :boundryID"); } if (queryParamMap.get(SRCH_OWNER_NAME) != null) { ownerName = queryParamMap.get(SRCH_OWNER_NAME).toString(); if (!ownerName.equals("")) { ownerFound = true; qryStr.append(" and upper(EGCITI.FIRSTNAME) like :firstName "); // :lastName } } if (queryParamMap.get(SRCH_NEW_HOUSE_NO) != null) { newHouseNo = queryParamMap.get(SRCH_NEW_HOUSE_NO).toString(); if (!newHouseNo.equals("")) { houseFound = true; qryStr.append(" and upper(EGADD.HOUSENO) like :houseno "); } } if (queryParamMap.get(SRCH_PROPERTY_TYPE) != null) { propTypeId = (Integer) queryParamMap.get(SRCH_PROPERTY_TYPE); if (propTypeId != -1) { propTypeFound = true; qryStr.append(" and EGPTPD.ID_PROPERTYTYPEMASTER =:propType "); } } qryStr.append( " and EGDR.ID_INSTALLMENT = (select id_installment from eg_installment_master where start_date<=sysdate and end_date>=sysdate") .append(" and id_module=(select id_module from eg_module where module_name='Property Tax')) ") .append(" group by EGDR.ID_INSTALLMENT,EGPTP.ID_PROPERTY"); if (queryParamMap.get(SRCH_DEMAND_FROM_AMOUNT) != null || queryParamMap.get(SRCH_DEMAND_TO_AMOUNT) != null) { dmdFrmAmt = (BigDecimal) queryParamMap.get(SRCH_DEMAND_FROM_AMOUNT); dmdToAmt = (BigDecimal) queryParamMap.get(SRCH_DEMAND_TO_AMOUNT); demandFound = true; qryStr.append(" having sum( EGDET.AMOUNT) BETWEEN :dmdFrmAmt and :dmdToAmt "); } if (queryParamMap.get(SRCH_DEFAULTER_FROM_AMOUNT) != null || queryParamMap.get(SRCH_DEFAULTER_TO_AMOUNT) != null) { defaultFrmamt = (BigDecimal) queryParamMap.get(SRCH_DEFAULTER_FROM_AMOUNT); defaultToAmt = (BigDecimal) queryParamMap.get(SRCH_DEFAULTER_TO_AMOUNT); defaulterFound = true; qryStr.append( " having sum(EGDET.AMOUNT)- sum(EGDET.AMT_COLLECTED) between :defaultFrmAmt and :defaultToAmt "); } LOGGER.info("query string final--------------" + qryStr.toString()); qry = getCurrentSession().createSQLQuery(qryStr.toString()); if (zoneBndryFound) { qry.setInteger("boundryID", boundryID); } if (wardBndryFound) { qry.setInteger("boundryID", boundryID); } if (areaBndryFound) { qry.setInteger("boundryID", boundryID); } if (bndryFound) { qry.setInteger("boundryID", boundryID); } if (ownerFound) { qry.setString("firstName", "%" + ownerName.toUpperCase() + "%"); } if (houseFound) { qry.setString("houseno", newHouseNo.toUpperCase() + "%"); } if (propTypeFound) { qry.setInteger("propType", propTypeId); } if (demandFound) { qry.setBigDecimal("dmdFrmAmt", dmdFrmAmt); qry.setBigDecimal("dmdToAmt", dmdToAmt); } if (defaulterFound) { qry.setBigDecimal("defaultFrmAmt", defaultFrmamt); qry.setBigDecimal("defaultToAmt", defaultToAmt); } propertyList = qry.list(); } } } return propertyList; } catch (Exception e) { throw new ApplicationRuntimeException("Error in getPropertyByBoundryAndOwnerNameAndHouseNo", e); } } }