Ejemplo n.º 1
0
  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;
  }
Ejemplo n.º 2
0
  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;
  }