public List<NearbySchoolList> getNearbySchoolByLatitudeByLogitude(SearchRequest searchRequest) { HibernateUtil hibernateUtil = new HibernateUtil(); Session session = hibernateUtil.getSessionFactory().openSession(); String distance = "ROUND(6371 * " + " ACOS(COS( RADIANS(" + searchRequest.getLatitude() + ") ) * COS( RADIANS( s.latitude ) ) * " + " COS(RADIANS( s.longitude ) - RADIANS(" + searchRequest.getLongitude() + ") ) " + " + SIN(RADIANS(" + searchRequest.getLatitude() + ")) * SIN(RADIANS(s.latitude)) ),6)"; String hql = "SELECT s.schoolId as schoolId, " + " s.name as name, " + " s.localityName as localityName, " + " s.cityName as cityName, " + " s.rating as rating, " + " s.logo as homeImage, " + " s.mediums as mediums, " + " s.boardName as boardName, " + " ci.vacantSeat as vacantSeat, " + " ci.totalFee as totalFee, " + " ci.standardType.id as standardId, " + distance + " as distance" + " FROM SchoolSearch s, ClassInfo ci" + " WHERE ci.school.id = s.schoolId"; if (searchRequest.getStandardId() != 0) { hql = hql + " AND ci.standardType.id = :standard_id "; } if (searchRequest.getLatitude() != null && searchRequest.getLongitude() != null) { hql = hql + " AND " + distance + " < 6"; } hql = hql + " GROUP BY s.schoolId "; Query query = session .createQuery(hql) .setResultTransformer(Transformers.aliasToBean(NearbySchoolList.class)); if (searchRequest.getStandardId() != 0) { query.setParameter("standard_id", searchRequest.getStandardId()); } List<NearbySchoolList> nearbySchools = query.list(); session.close(); return nearbySchools; }
public List<SchoolList> fetchSchoolListByLattitudeByLongitude(SearchRequest searchRequest) { HibernateUtil hibernateUtil = new HibernateUtil(); Session session = hibernateUtil.getSessionFactory().openSession(); String distance = "0"; String queryJoin = ""; String queryCondition = ""; String orderBy = ""; if (searchRequest.getLatitude().trim().length() > 0 && searchRequest.getLongitude().trim().length() > 0) { distance = "ROUND(6371 * " + " ACOS(COS( RADIANS(" + searchRequest.getLatitude() + ") ) * COS( RADIANS( s.latitude ) ) * " + " COS(RADIANS( s.longitude ) - RADIANS(" + searchRequest.getLongitude() + ") ) " + " + SIN(RADIANS(" + searchRequest.getLatitude() + ")) * SIN(RADIANS(s.latitude)) ),3)"; queryCondition += " AND " + distance + " < 3"; } if (searchRequest.getStandardId() > 0) { queryCondition += " AND ci.standardType.id = " + searchRequest.getStandardId(); } if (searchRequest.getBoardId() != "0") { queryCondition += " AND s.boardId IN(" + searchRequest.getBoardId() + ")"; } if (searchRequest.getTaId() != "0") { queryCondition += " AND ta.id IN(" + searchRequest.getTaId() + ")"; } if (searchRequest.getTypeId() != "0") { queryCondition += " AND s.schoolTypeId IN(" + searchRequest.getTypeId() + ")"; } if (searchRequest.getMediumId() != "0") { queryCondition += " AND sm.mediumType.id IN(" + searchRequest.getMediumId() + ")"; } if (searchRequest.getCategoryId() != "0") { queryCondition += " AND s.categoryId IN(" + searchRequest.getCategoryId() + ")"; } if (searchRequest.getClassificationId() != "0") { queryCondition += " AND s.classificationId IN(" + searchRequest.getClassificationId() + ")"; } if (searchRequest.getSafetyId() != "0") { queryCondition += " AND sc.safetyCategoryItem.id IN(" + searchRequest.getSafetyId() + ")"; queryJoin += " JOIN ss.schoolSafetyCatItems sc"; } if (searchRequest.getActivityId() != "0") { queryCondition += " AND ac.activityCategoryItem.id IN(" + searchRequest.getActivityId() + ")"; queryJoin += " JOIN ss.schoolActivityCatItems ac"; } if (searchRequest.getInfraId() != "0") { queryCondition += " AND ic.infrastructureCategoryItem.id IN(" + searchRequest.getInfraId() + ")"; queryJoin += " JOIN ss.schoolInfrastructureCatItems ic"; } if (Integer.parseInt(searchRequest.getMinFee()) >= 0 && Integer.parseInt(searchRequest.getMaxFee()) > 0) { queryCondition += " AND ci.totalFee >= " + searchRequest.getMinFee() + " AND ci.totalFee <= " + searchRequest.getMaxFee(); } if (searchRequest.getFee().equalsIgnoreCase("ASC") || searchRequest.getFee().equalsIgnoreCase("DESC")) { orderBy += " ci.totalFee " + searchRequest.getFee(); } if (searchRequest.getRating().equalsIgnoreCase("ASC") || searchRequest.getRating().equalsIgnoreCase("DESC")) { if (orderBy != "") orderBy += ","; orderBy += " s.rating " + searchRequest.getRating(); } if (searchRequest.getDistance().equalsIgnoreCase("ASC") || searchRequest.getDistance().equalsIgnoreCase("DESC")) { if (orderBy != "") orderBy += ","; orderBy += " " + distance + " " + searchRequest.getDistance(); } if (searchRequest.getSeats().equalsIgnoreCase("ASC") || searchRequest.getSeats().equalsIgnoreCase("DESC")) { if (orderBy != "") orderBy += ","; orderBy += " ci.vacantSeat " + searchRequest.getSeats(); } String finalOrder = ""; if (orderBy != "") { finalOrder = " ORDER BY" + orderBy; } String isShortlistedQuery = ""; if (searchRequest.getUserId().equals("0") == false) { isShortlistedQuery = ", CASE WHEN NULL = (FROM ShortListedSchool sls WHERE s.schoolId = sls.school.id AND sls.userRegistrationInfo.id = " + searchRequest.getUserId() + ") THEN false ELSE true END as isShortlisted "; } Query query = session .createQuery( "SELECT s.schoolId as schoolId, s.name as name,s.alias as alias, s.latitude as latitude," + " s.longitude as longitude, s.tagLine as tagLine, s.aboutSchool as aboutSchool," + " s.homeImage as homeImage,s.logo as logo, s.establishmentType as establishmentType," + " s.streetName as streetName, s.pincode as pincode, s.localityName as localityName," + " s.cityName as cityName,s.boardName as boardName,s.mediums as mediums," + " s.schoolCategory as schoolCategory,s.schoolClassification as schoolClassification," + " s.schoolType as schoolType,s.rating as rating,s.galeryImages as galeryImages,s.reviews as reviews, " + distance + " as distance,ci.totalFee as totalFee,ci.vacantSeat as seats," + " ci.standardType.id as standardId, st.name as standardName, ta.name as teachingApproach," + " COALESCE( (SELECT con.mobileNo FROM ContactInfo con WHERE s.schoolId = con.school.id AND con.isPrimary = 1 AND con.type = 1), '') as primaryContactNo " + isShortlistedQuery + " FROM SchoolSearch s, School ss JOIN ss.classInfos ci JOIN ci.standardType st" + " JOIN ss.schoolMediums sm JOIN ci.teachingApproachType ta" + queryJoin + " WHERE s.schoolId = ss.id " + queryCondition + " Group By ss.id" + finalOrder) .setResultTransformer(Transformers.aliasToBean(SchoolList.class)); List<SchoolList> resultRaw = query.list(); session.close(); return resultRaw; }