public ResponseMessage getUriByLatitudeLongitudeByStandard(
String latitude, String longitude, Short standardId) {
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.getSessionFactory().openSession();
String sql =
"SELECT "
+ " CONCAT(REPLACE(c.name, ' ', '-') , '/', REPLACE(l.name,' ', '-'), '/', REPLACE(st.name, ' ', '-')) as Uri "
+ " FROM locality l JOIN city c ON l.city_id = c.id, standard_type st "
+ " WHERE st.id = :standardId "
+ " AND (ROUND(6371 * ACOS(COS( RADIANS( :latitude ) ) * COS( RADIANS( l.latitude ) ) * COS(RADIANS( l.longitude ) - RADIANS(:longitude) ) + SIN(RADIANS( :latitude)) * SIN(RADIANS(l.latitude)) ),3 )) < 3 "
+ " ORDER BY (ROUND(6371 * ACOS(COS( RADIANS( :latitude ) ) * COS( RADIANS( l.latitude ) ) * COS(RADIANS( l.longitude ) - RADIANS(:longitude) ) + SIN(RADIANS( :latitude)) * SIN(RADIANS(l.latitude)) ),3 )) ASC "
+ " LIMIT 1";
Query query =
session
.createSQLQuery(sql)
.setParameter("standardId", standardId)
.setParameter("latitude", latitude)
.setParameter("longitude", longitude)
.setResultTransformer(Transformers.aliasToBean(UriData.class));
UriData result = (UriData) query.uniqueResult();
ResponseMessage responseMessage = new ResponseMessage();
if (result == null) {
responseMessage.setMessage("No school found.");
responseMessage.setStatus(0);
} else {
Gson gson = new Gson();
responseMessage.setMessage("School found.");
responseMessage.setData(result);
responseMessage.setStatus(1);
}
session.close();
return responseMessage;
}
public ResponseMessage shortlistSchool(Integer schoolId, Integer userId) {
ResponseMessage responseMessage = new ResponseMessage();
ArrayList<String> errors = new ArrayList<String>();
if (schoolId != null && userId != null) {
String hql =
" FROM ShortListedSchool sls "
+ " WHERE sls.school.id = :schoolId AND sls.userRegistrationInfo.id = :userId";
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.openSession();
Query query =
session
.createQuery(hql)
.setParameter("schoolId", schoolId)
.setParameter("userId", userId);
ShortListedSchool shortListedSchool = (ShortListedSchool) query.uniqueResult();
session.close();
try {
Session newSession = hibernateUtil.openSession();
newSession.beginTransaction();
if (shortListedSchool != null) {
newSession.delete(shortListedSchool);
responseMessage.setStatus(1);
responseMessage.setMessage("School delisted successfully.");
} else {
shortListedSchool = new ShortListedSchool();
School school = new School();
school.setId(schoolId);
UserRegistrationInfo userRegistrationInfo = new UserRegistrationInfo();
userRegistrationInfo.setId(userId);
shortListedSchool.setSchool(school);
shortListedSchool.setUserRegistrationInfo(userRegistrationInfo);
newSession.save(shortListedSchool);
responseMessage.setStatus(1);
responseMessage.setMessage("School shortlisted successfully.");
}
newSession.getTransaction().commit();
newSession.flush();
newSession.close();
} catch (Exception e) {
errors.add(e.getMessage());
responseMessage.setErrors(errors);
responseMessage.setStatus(0);
responseMessage.setMessage("Failed to shortlist/delist school.");
}
} else {
errors.add("School id and user id required.");
responseMessage.setErrors(errors);
responseMessage.setStatus(0);
responseMessage.setMessage("Failed to shortlist/delist school.");
}
return responseMessage;
}
public ResponseMessage updateContactClicksBySchoolId(Integer schoolId) {
String hql = "FROM SchoolAnalytics WHERE school.id = :school_id";
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.openSession();
Query query = session.createQuery(hql).setParameter("school_id", schoolId);
List<SchoolAnalytics> result = query.list();
ResponseMessage responseMessage = new ResponseMessage();
try {
if (result.isEmpty() != true) {
Integer contactClicks = 0;
for (int i = 0; i < result.size(); i++) {
contactClicks = result.get(i).getContactClicks();
}
++contactClicks;
hql =
"UPDATE SchoolAnalytics SET contact_clicks = :contact_clicks WHERE school.id = :school_id";
query =
session
.createQuery(hql)
.setParameter("contact_clicks", contactClicks)
.setParameter("school_id", schoolId);
session.beginTransaction();
query.executeUpdate();
session.getTransaction().commit();
session.flush();
} else {
SchoolAnalytics schoolAnalytics = new SchoolAnalytics();
schoolAnalytics.setContactClicks(1);
hql = "FROM School where id = :school_id";
query = session.createQuery(hql).setParameter("school_id", schoolId);
schoolAnalytics.setSchool((School) query.uniqueResult());
session.beginTransaction();
session.save(schoolAnalytics);
session.getTransaction().commit();
session.flush();
}
responseMessage.setMessage("success");
responseMessage.setStatus(1);
} catch (Exception e) {
responseMessage.setMessage(e.getMessage());
responseMessage.setStatus(0);
}
session.close();
return responseMessage;
}
public ResponseMessage postContactRequest(ContactUs contactUs) {
ResponseMessage response = new ResponseMessage();
HibernateUtil hibernateUtil = new HibernateUtil();
Session newsession = hibernateUtil.openSession();
newsession.beginTransaction();
newsession.save(contactUs);
newsession.getTransaction().commit();
newsession.close();
response.setStatus(1);
response.setMessage("Success");
return response;
}
public ResponseMessage addSchoolRating(RatingData ratingData) {
ResponseMessage msg = new ResponseMessage();
ArrayList<String> errors = new ArrayList<String>();
if (ratingData.getSchoolId() > 0
&& ratingData.getUserId() > 0
&& ratingData.getRatings().size() > 0) {
try {
String HQL =
"SELECT ur.id as id, ur.ratingCategoryType.id as catid "
+ " FROM UserRating ur WHERE ur.school.id = :schoolId AND ur.userRegistrationInfo.id = :userId ";
HibernateUtil hibernateUtil = new HibernateUtil();
Session sess = hibernateUtil.openSession();
Query query =
sess.createQuery(HQL)
.setResultTransformer(Transformers.aliasToBean(Rating.class))
.setParameter("schoolId", ratingData.getSchoolId())
.setParameter("userId", ratingData.getUserId());
List<Rating> ratings = query.list();
sess.close();
School school = new School();
school.setId(ratingData.getSchoolId());
UserRegistrationInfo userRegistrationInfo = new UserRegistrationInfo();
userRegistrationInfo.setId(ratingData.getUserId());
SchoolRating schoolRating = new SchoolRating();
schoolRating.setSchool(school);
Session session = hibernateUtil.openSession();
session.beginTransaction();
for (int i = 0; i < ratingData.getRatings().size(); i++) {
UserRating userRating = new UserRating();
if (ratings.isEmpty() == false) {
for (int j = 0; j < ratings.size(); j++) {
if (ratingData.getRatings().get(i).getCatid() == ratings.get(j).getCatid()) {
userRating.setId(ratings.get(j).getId());
}
}
}
userRating.setSchool(school);
userRating.setUserRegistrationInfo(userRegistrationInfo);
userRating.setRating((float) ratingData.getRatings().get(i).getRating());
userRating.setAddedDate(new Date());
RatingCategoryType ratingCategoryType = new RatingCategoryType();
ratingCategoryType.setId(ratingData.getRatings().get(i).getCatid());
userRating.setRatingCategoryType(ratingCategoryType);
session.saveOrUpdate(userRating);
}
session.getTransaction().commit();
session.flush();
updateSchoolFinalRating(schoolRating);
msg.setStatus(1);
msg.setMessage("Rating saved successfully.");
} catch (Exception e) {
errors.add(e.getMessage());
msg.setStatus(0);
msg.setErrors(errors);
}
} else {
if (ratingData.getSchoolId() <= 0) {
errors.add("School Id Can not be empty");
}
if (ratingData.getUserId() <= 0) {
errors.add("User Id Can not be empty");
}
if (ratingData.getRatings().size() <= 0) {
errors.add("Rating data can not be empty");
}
msg.setStatus(0);
msg.setErrors(errors);
}
return msg;
}