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;
}