public List<Rating> getSchoolRating(Integer schoolId) {
String HQL =
"SELECT ratingCategoryType.id as catid, ratingCategoryType.categoryName as name, avg(rating) as rating, count(ratingCategoryType.id) as ratingCount, COALESCE(ratingCategoryType.image,'') as image from UserRating where school.id = :schoolId GROUP BY ratingCategoryType.id";
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.openSession();
Query query =
session.createQuery(HQL).setResultTransformer(Transformers.aliasToBean(Rating.class));
query.setParameter("schoolId", schoolId);
session.flush();
List<Rating> schoolRatings = query.list();
if (schoolRatings.size() <= 0) {
String newhql =
"SELECT id as catid, categoryName as name, 0.0 as rating, 0L as ratingCount, COALESCE(image,'') as image from RatingCategoryType";
Session newsession = hibernateUtil.openSession();
Query newquery =
newsession
.createQuery(newhql)
.setResultTransformer(Transformers.aliasToBean(Rating.class));
newsession.flush();
schoolRatings = newquery.list();
}
return schoolRatings;
}
@Scheduled(fixedDelay = 60000)
public void changeSessionStatus() {
List<CurrentUsersDTO> allList = null;
List<CurrentUsersDTO> activeList = null;
String currentTime = getTime();
String beforeTime = getBeforeTime();
String beforeHeartBeatTime = getBeforeHeartBeatTime();
Session session = getSession();
session.getTransaction().begin();
Criteria criteria =
session
.createCriteria(SessionDetails.class, "sessionDetails")
.add(
Restrictions.between(
"sessionDetails.heartBeatTime", beforeHeartBeatTime, currentTime))
.add(Restrictions.between("sessionDetails.lastAccessTime", beforeTime, currentTime));
criteria.addOrder(Order.asc("SID"));
criteria.setProjection(
Projections.projectionList()
.add(Projections.property("SID").as("sid"))
.add(Projections.property("lastAccessTime").as("lastAccessTime")));
activeList =
criteria.setResultTransformer(Transformers.aliasToBean(CurrentUsersDTO.class)).list();
Criteria criteria1 = session.createCriteria(SessionDetails.class, "sessionDetails");
criteria1.addOrder(Order.asc("SID"));
criteria1.setProjection(
Projections.projectionList()
.add(Projections.property("SID").as("sid"))
.add(Projections.property("lastAccessTime").as("lastAccessTime")));
allList =
criteria1.setResultTransformer(Transformers.aliasToBean(CurrentUsersDTO.class)).list();
for (CurrentUsersDTO dto : allList) {
try {
SessionDetails sd = getById(dto.getSid());
sd.setStatus("INACTIVE");
saveSessionDetails(sd);
} catch (Exception e) {
System.out.println("E :" + e);
}
}
for (CurrentUsersDTO dto : activeList) {
try {
SessionDetails sd = getById(dto.getSid());
sd.setStatus("ACTIVE");
saveSessionDetails(sd);
} catch (Exception e) {
System.out.println("E :" + e);
}
}
session.getTransaction().commit();
session.close();
}
public RatingReviewData getSchoolRatingAndReviewByUser(Integer schoolId, Integer userId) {
RatingReviewData ratingReviewData = new RatingReviewData();
if (schoolId != null && userId != null) {
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.getSessionFactory().openSession();
String hql =
"SELECT sr.school.id as schoolId, "
+ " sr.userRegistrationInfo.id as userId, "
+ " sr.title as title, "
+ " sr.review as review,"
+ " sr.id as reviewId "
+ " FROM SchoolReview sr "
+ " WHERE sr.school.id = :schoolId AND sr.userRegistrationInfo.id = :userId";
Query query =
session
.createQuery(hql)
.setParameter("schoolId", schoolId)
.setParameter("userId", userId)
.setResultTransformer(Transformers.aliasToBean(RatingReviewData.class));
ratingReviewData = (RatingReviewData) query.uniqueResult();
String sql =
"SELECT "
+ " ur.id as id, rct.id as catid, rct.category_name as name, "
+ " rct.image as image, COALESCE(ur.rating, 0 ) as rating "
+ " FROM rating_category_type rct LEFT JOIN user_rating ur "
+ " ON( rct.id = ur.rating_category_type_id AND ur.user_id =:userId AND ur.school_id = :schoolId) ";
query =
session
.createSQLQuery(sql)
.setParameter("schoolId", schoolId)
.setParameter("userId", userId)
.setResultTransformer(Transformers.aliasToBean(Rating.class));
List<Rating> ratings = query.list();
if (ratings.isEmpty() == false) {
if (ratingReviewData == null) {
ratingReviewData = new RatingReviewData();
ratingReviewData.setSchoolId(schoolId);
ratingReviewData.setUserId(userId);
ratingReviewData.setReview("");
ratingReviewData.setTitle("");
}
ratingReviewData.setRatings(ratings);
}
session.close();
}
return ratingReviewData;
}
public List<SchoolList> fetchSchoolListById(int schoolId, int standardId) {
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.getSessionFactory().openSession();
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, "
+ " 0.0 as distance,ci.totalFee as totalFee,ci.vacantSeat as seats,"
+ " ci.standardType.id as standardId,cf.campusSize as campusSize,au.name as unitName,"
+ " cf.totalStudents as students,(cf.totalMaleTeacher+cf.totalFemaleTeacher) as teachers"
+ " FROM SchoolSearch s, School ss JOIN ss.classInfos ci JOIN ss.campusInfos cf JOIN cf.areaUnit au"
+ " WHERE s.schoolId = ss.id AND ci.standardType.id = "
+ standardId
+ " AND ss.id = "
+ schoolId)
.setResultTransformer(Transformers.aliasToBean(SchoolList.class));
List<SchoolList> resultRaw = query.list();
session.close();
return resultRaw;
}
protected Query buildQuery() {
Query query =
session
.createSQLQuery(sql)
.setResultTransformer(Transformers.aliasToBean(GroupedDataTuple.class));
return query;
}
private Criteria getCriteriaByThesaurusAndTopConcept(
Thesaurus thesaurus, boolean topConcept, String like) {
Criteria criteria = getCurrentSession().createCriteria(ThesaurusConcept.class, "tc");
if (null != like) {
criteria =
getCurrentSession()
.createCriteria(ThesaurusTerm.class, "tt")
.add(Restrictions.isNotNull("tt.concept"))
.createCriteria("concept", "tc", JoinType.RIGHT_OUTER_JOIN);
criteria
.setProjection(
Projections.projectionList()
.add(Projections.property("tt.lexicalValue"))
.add(Projections.property("tc.identifier").as("identifier")))
.setResultTransformer(Transformers.aliasToBean(ThesaurusConcept.class));
conceptNameIsLike(criteria, like);
}
selectThesaurus(criteria, thesaurus.getIdentifier());
selectOrphans(criteria, !topConcept);
selectNoParents(criteria);
return criteria;
}
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;
}
@Override
@SuppressWarnings({"rawtypes", "unchecked"})
public List<IpMessageVO> findBySQL(String sql, Map<String, ?> map) {
try {
SQLQuery query = getSession().createSQLQuery(sql);
// Set<String> keys = map.keySet();
query
.addScalar("userName", Hibernate.STRING)
.addScalar("userProperties", Hibernate.STRING)
.addScalar("userAddress", Hibernate.STRING)
.addScalar("contactName", Hibernate.STRING)
.addScalar("contactPhone", Hibernate.STRING)
.addScalar("contactDocumentNum", Hibernate.STRING)
.addScalar("installedAddress", Hibernate.STRING)
.addScalar("installedDate", Hibernate.TIMESTAMP)
.addScalar("startIp", Hibernate.STRING)
.addScalar("endIp", Hibernate.STRING);
query.setResultTransformer(Transformers.aliasToBean(IpMessageVO.class));
// for (String key : keys) {
// Object o = map.get(key);
// if (o instanceof Collection) {
// query.setParameterList(key, (Collection) map.get(key));
// } else if(o instanceof Page){
// query.setFirstResult((((Page) o).getPageNo()-1)*((Page) o).getPageSize());
// query.setMaxResults(((Page) o).getPageSize());
// }else {
// query.setParameter(key, map.get(key));
// }
// }
return query.list();
} catch (Exception e) {
throw new HsCloudException(BillConstant.ACCOUNT_DATABASE_ERROR, e.getMessage(), logger, e);
}
}
@SuppressWarnings("unchecked")
public List<T> findByCriteria(
final int firstResult,
final int maxResults,
final ProjectionList projections,
final Criterion... criterion) {
Session session = (Session) getEntityManager().getDelegate();
Criteria crit = session.createCriteria(getEntityClass());
crit.setProjection(projections);
crit.setResultTransformer(Transformers.aliasToBean(getEntityClass()));
for (final Criterion c : criterion) {
crit.add(c);
}
if (firstResult > 0) {
crit.setFirstResult(firstResult);
}
if (maxResults > 0) {
crit.setMaxResults(maxResults);
}
final List<T> result = crit.list();
return result;
}
public List<UserCountDTO> getCurrentUserCountList(
String sortField, int order, int start, int gridTableSize, String searchq) throws Exception {
int count = start;
List<UserCountDTO> dtoList = null;
List<UserCountDTO> dtoListCount = new ArrayList<UserCountDTO>();
Session session = getSession();
session.getTransaction().begin();
Criteria criteria =
session
.createCriteria(SessionDetails.class, "sessionDetails")
.add(Restrictions.eq("status", "ACTIVE"));
criteria.addOrder(Order.asc("SID"));
criteria.setFirstResult(start).setMaxResults(gridTableSize);
criteria.setProjection(Projections.projectionList().add(Projections.property("SID").as("sid")));
dtoList = criteria.setResultTransformer(Transformers.aliasToBean(UserCountDTO.class)).list();
session.getTransaction().commit();
session.close();
if (dtoList.size() > 0) {
for (UserCountDTO dto : dtoList) {
dto.setCountId(count + 1);
dtoListCount.add(dto);
count += 1;
}
}
return dtoListCount;
}
public List<SessionTimeOutDTO> getSessionIDListBySID(Long sid) {
List<SessionTimeOutDTO> dtoList = null;
SessionTimeOutDTO dto = null;
Session session = getSession();
session.getTransaction().begin();
Criteria criteria =
session
.createCriteria(SessionDetails.class, "sessionDetails")
.add(Restrictions.eq("SID", sid));
criteria.setProjection(
Projections.projectionList()
.add(Projections.property("SID").as("sid"))
.add(Projections.property("sessionId").as("sessionId")));
dto =
(SessionTimeOutDTO)
criteria
.setResultTransformer(Transformers.aliasToBean(SessionTimeOutDTO.class))
.uniqueResult();
session.getTransaction().commit();
session.close();
dtoList = getList(dto);
return dtoList;
}
public Page<CommonSport> queryTerminalByBusiness(
final Page page,
String terminalProtectedArea,
String terminalTimeSearch,
Integer intType,
Integer dataType) {
String sql =
this.buildTerminalByBusinessSql(
terminalProtectedArea, terminalTimeSearch, page, intType, dataType);
String sqls = sql.split("order by")[0];
String totalSql = "select count(1) from ( " + sqls + " ) as totalCount";
Object totalObj = this.getSession().createSQLQuery(totalSql).uniqueResult();
Integer total =
Integer.parseInt(
null == totalObj.toString() || "".equals(totalObj.toString())
? "0"
: totalObj.toString());
List<CommonSport> list =
this.getSession()
.createSQLQuery(sql)
.addScalar("statdate", Hibernate.STRING)
.addScalar("context1", Hibernate.STRING)
.addScalar("context", Hibernate.STRING)
.addScalar("detail", Hibernate.STRING)
.setResultTransformer(Transformers.aliasToBean(CommonSport.class))
.setFirstResult(page.getFirst())
.setMaxResults(page.getPageSize())
.list();
Page newPage = new Page();
newPage.setPageSize(page.getPageSize());
newPage.setTotalCount(total);
newPage.setResult(buildList1(list, intType, dataType));
newPage.setPageNo(page.getPageNo());
return newPage;
}
public void updateSchoolFinalRating(SchoolRating schoolRating) {
String HQL =
"SELECT COUNT(id) as userCount, SUM(rating) as totalRating from UserRating where school.id = :schoolId";
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.openSession();
Query query =
session.createQuery(HQL).setResultTransformer(Transformers.aliasToBean(TotalRating.class));
query.setParameter("schoolId", schoolRating.getSchool().getId());
List<TotalRating> totalRatings = query.list();
session.flush();
System.out.println(
"total rating:"
+ totalRatings.get(0).getTotalRating()
+ " count"
+ totalRatings.get(0).getUserCount());
if (totalRatings.size() > 0) {
String sql = "from SchoolRating where school.id =" + schoolRating.getSchool().getId();
Session sqlseSession = hibernateUtil.openSession();
Query sqlQuery = sqlseSession.createQuery(sql);
boolean newRating = sqlQuery.list().size() > 0 ? true : false;
sqlseSession.flush();
/*String newhql = "SELECT id from RatingCategoryType";
Session newsession1 = hibernateUtil.openSession();
Query newquery1 = newsession1.createQuery(newhql);
int catSize = newquery1.list().size() > 0 ?newquery1.list().size():0;
newsession1.flush();*/
if (newRating) {
long calculated_rating =
Math.round(
totalRatings.get(0).getTotalRating() / totalRatings.get(0).getUserCount() * 100);
float final_rating = (float) calculated_rating / 100;
System.out.println("Rating:" + final_rating);
String updatehql =
"UPDATE SchoolRating set rating="
+ final_rating
+ " where school.id="
+ schoolRating.getSchool().getId();
Session newsession = hibernateUtil.openSession();
newsession.beginTransaction();
Query newquery = newsession.createQuery(updatehql);
newquery.executeUpdate();
newsession.getTransaction().commit();
newsession.flush();
} else {
long calculated_rating =
Math.round(
totalRatings.get(0).getTotalRating() / totalRatings.get(0).getUserCount() * 100);
float final_rating = (float) calculated_rating / 100;
System.out.println("Rating:" + final_rating);
schoolRating.setRating(final_rating);
Session newsession = hibernateUtil.openSession();
newsession.beginTransaction();
newsession.save(schoolRating);
newsession.getTransaction().commit();
newsession.flush();
}
}
}
public static <T> void setResultTransformer(final Class<T> entityClass, final Query query) {
if (entityClass != null) {
if (entityClass.equals(Map.class)) {
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
} else {
query.setResultTransformer(Transformers.aliasToBean(entityClass));
}
}
}
public List<NameList> getCastList() {
String hql = "select id as id, name as name from Cast";
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.openSession();
Query query =
session.createQuery(hql).setResultTransformer(Transformers.aliasToBean(NameList.class));
List<NameList> result = query.list();
session.close();
return result;
}
private <P> Criteria buildProjectionCriteria(
Class<P> projectClass, Criteria criteria, Projection projections, boolean distinctResult) {
if (distinctResult) {
criteria.setProjection(Projections.distinct(projections));
} else {
criteria.setProjection(projections);
}
return criteria.setResultTransformer(Transformers.aliasToBean(projectClass));
}
public List<DmFolderNode> getFirstLevelFolderNodeList(String accountId) {
List<DmFolderNode> folderNodeList = null;
String sqlSub =
"SELECT"
+ " count(*)"
+ " from dm_folder fChild"
// + " left join dm_folder_permission fpChild"
// + " on fChild.df_id = fpChild.df_id"
+ " where fChild.df_parent_id = f.df_id"
// + " and fpChild.da_id = :accountSharedId"
// + " and fpChild.da_id <> :accountOwnerId"
;
// String sqlSub2 = "SELECT"
// + " fpPrnt2.df_id"
// + " FROM dm_folder_permission fpPrnt2"
// + " WHERE fpPrnt2.da_id = :accountSharedId"
// + " and fpPrnt2.da_id <> :accountOwnerId"
// ;
String sql =
"SELECT"
+ " f.df_id as \"id\", f.df_name as \"name\", ("
+ sqlSub
+ ") as \"numChildren\""
+ ", f.da_id_owner as \"ownerAccountId\", fp.dfp_permission_type as \"permissionType\""
+ " FROM dm_folder_permission fp"
+ " LEFT JOIN dm_folder f"
+ " on fp.df_id = f.df_id and fp.da_id = f.da_id_owner"
// + " LEFT JOIN dm_folder fPrnt"
// + " on f.df_parent_id = fPrnt.df_id"
+ " WHERE f.da_id_owner = :accountId"
// + " AND fp.da_id <> :accountOwnerId"
// + " AND f.da_id_owner = :accountId"
+ " AND f.df_parent_id is null"
// + " AND ((fPrnt.df_id IS NULL) OR (fPrnt.df_id NOT IN (" + sqlSub2 + ")))"
+ " ORDER BY f.df_name asc";
SQLQuery query = getSession().createSQLQuery(sql);
query.setString("accountId", accountId);
query.addScalar("id", Hibernate.STRING);
query.addScalar("name", Hibernate.STRING);
query.addScalar("numChildren", Hibernate.INTEGER);
query.addScalar("ownerAccountId", Hibernate.STRING);
query.addScalar("permissionType", Hibernate.INTEGER);
query.setResultTransformer(Transformers.aliasToBean(DmFolderNode.class));
folderNodeList = query.list();
return folderNodeList;
}
@Override
public Member getMemberByLogin(String memberLogin) {
Query query =
sessionFactory
.getCurrentSession()
.createQuery(
"select memberId as memberId, memberName as memberName,memberLogin as memberLogin, memberEmail as memberEmail,memberPassword as memberPassword from Member where memberLogin = :memberLogin")
.setResultTransformer(Transformers.aliasToBean(Member.class));
query.setParameter("memberLogin", memberLogin);
return (Member) query.uniqueResult();
}
public <N extends Object> List<N> listBySql(
String sql, Object[] args, Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
sql = initSort(sql);
SQLQuery sq = getSession().createSQLQuery(sql);
setAliasParameter(sq, alias);
setParameter(sq, args);
if (hasEntity) {
sq.addEntity(clz);
} else sq.setResultTransformer(Transformers.aliasToBean(clz));
return sq.list();
}
@Override
public User getUserByLogin(String userName) {
Query query =
sessionFactory
.getCurrentSession()
.createQuery(
"select userId as userId, userName as userName,userGroupId as userGroupId,userPassword as userPassword, userDescribe as userDescribe from User where userName = :userName")
.setResultTransformer(Transformers.aliasToBean(User.class));
query.setParameter("userName", userName);
return (User) query.uniqueResult();
}
/* 229: */
/* 230: */ public List<InquiryDto> getInquiry(Integer personnelId, Integer userId)
/* 231: */ {
/* 232:222 */ Session session = getSession();
/* 233:223 */ Query query =
session
.getNamedQuery("getPersonnelInquiry")
.setResultTransformer(Transformers.aliasToBean(InquiryDto.class));
/* 234:224 */ query.setParameter("user_Id", userId);
/* 235:225 */ query.setParameter("personnel_Id", personnelId);
/* 236:226 */ List<InquiryDto> indlst = query.list();
/* 237:227 */ return indlst;
/* 238: */ }
@Override
public List<Object> getReviewReport(List<ETMetaDataTable> tables) {
Session sess = ETDBConnectionManager.getCurrentSession();
sess.getTransaction();
String field = "et.product_et_id";
List<ETReviewReport> report = new ArrayList<ETReviewReport>();
String str =
"SELECT r.review_et_id as reviewId, "
+ " r.review_title as etTituloCalificacion, "
+ " r.review_comment as etComentarioGeneral, "
+ " r.product_review as etCalificacionProducto,"
+ " r.esq_review as etCalificacionServicioCliente,"
+ " r.satisfaction_review as etSatisfaccionGeneral, "
+ " r.pros_review as etProsProducto, "
+ " r.cons_review as etContrasProducto,"
+ " r.best_usage as etMejorasProducto, "
+ " r.recommend_firend as etRecomendarAmigo, "
+ " r.buy_again as etComprariaDeNuevo, CAST(r.review_date as CHAR) as reviewDate, r.review_product_utility as etUtilidadProducto, "
+ " r.review_type as reviewType, r.reviewCalification as reviewCalification"
+ " FROM et_reviews r";
Query q =
sess.createSQLQuery(str)
.setResultTransformer(Transformers.aliasToBean(ETReviewReport.class));
report = q.list();
for (ETReviewReport review : report) {
String select = "Select ";
String from = " FROM et_reviews et, ";
String where = " WHERE et.review_et_id = :reviewId AND ";
for (ETMetaDataTable table : tables) {
from += " " + table.getTableName();
for (ETMetaDataColumn col : table.getColumns()) {
select += table.getTableName() + "." + col.getColumnName() + ",";
if (col.getColumnKey() != null && col.getColumnKey().equals("PRI")) {
where += field + "=" + col.getColumnName();
}
}
}
String str2 = select.substring(0, select.length() - 1) + from + where;
Query det = sess.createSQLQuery(str2).setInteger("reviewId", review.getReviewId());
Iterator i = det.list().iterator();
while (i.hasNext()) {
Object[] tuple = (Object[]) i.next();
List<ETMetaDataColumn> columns = tables.get(0).getColumns();
for (int b = 0; b < columns.size(); b++) {
review
.getEcommerceInfo()
.put(columns.get(b).getColumnName(), (String) tuple[b].toString());
}
}
}
return new ArrayList<Object>(report);
}
public List<SchoolList> compareSchools(
List<Integer> schoolIds, String latitude, String longitude) {
List<SchoolList> resultRaw = new ArrayList<SchoolList>();
if (schoolIds.isEmpty() == false) {
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.getSessionFactory().openSession();
String distance = "";
if (latitude.trim().length() > 0 && longitude.trim().length() > 0) {
distance =
", ROUND(6371 * "
+ " ACOS(COS( RADIANS("
+ latitude
+ ") ) * COS( RADIANS( s.latitude ) ) * "
+ " COS(RADIANS( s.longitude ) - RADIANS("
+ longitude
+ ") ) "
+ " + SIN(RADIANS("
+ latitude
+ ")) * SIN(RADIANS(s.latitude)) ),2) as distance";
}
String hql =
"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.rating as rating,s.galeryImages as galeryImages,s.reviews as reviews, "
+ " s.schoolType as schoolType, "
+ " ci.totalFee as totalFee, "
+ " ci.vacantSeat as seats,"
+ " ci.standardType.id as standardId "
+ distance
+ " FROM SchoolSearch s, ClassInfo ci"
+ " WHERE s.schoolId = ci.school.id AND s.schoolId IN(:schoolIds)"
+ " GROUP BY s.schoolId";
Query query =
session
.createQuery(hql)
.setResultTransformer(Transformers.aliasToBean(SchoolList.class))
.setParameterList("schoolIds", schoolIds);
resultRaw = query.list();
session.close();
}
return resultRaw;
}
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<CommonSport> listData1(
String common_search, int timeLevel, String sTime, String eTime, Integer intType) {
int dataType = 2;
String sql = this.buildSql1(common_search, timeLevel, sTime, eTime, null, intType, dataType);
List<CommonSport> list =
this.getSession()
.createSQLQuery(sql)
.addScalar("statdate", Hibernate.STRING)
.addScalar("context", Hibernate.STRING)
.addScalar("detail", Hibernate.STRING)
.setResultTransformer(Transformers.aliasToBean(CommonSport.class))
.list();
return buildList1(list, intType, dataType);
}
public List<UserDetailsDTO> analyseUserBySessionId(Long sid) {
List<UserDetailsDTO> list = null;
Session session = getSession();
session.getTransaction().begin();
Criteria criteria = session.createCriteria(BrowserDetails.class, "browserDetails");
criteria.createAlias("browserDetails.sessionDetails", "sessionDetails");
criteria.createAlias("browserDetails.deviceDetails", "deviceDetails");
criteria.createAlias("browserDetails.eventDetails", "eventDetails");
criteria.add(Restrictions.eq("sessionDetails.SID", sid));
criteria.addOrder(Order.desc("eventDetails.EID"));
criteria.setProjection(
Projections.projectionList()
.add(Projections.property("sessionDetails.SID").as("sid"))
.add(Projections.property("sessionDetails.lastAccessTime").as("lastAccessTime"))
.add(Projections.property("sessionDetails.sessionCreatedTime").as("firstAccessTime"))
.add(Projections.property("eventDetails.EID").as("eid"))
.add(Projections.property("eventDetails.triggeredTime").as("eventTriggeredTime"))
.add(Projections.property("eventDetails.eventTypes").as("eventName"))
.add(Projections.property("eventDetails.coordinateX").as("coordinateX"))
.add(Projections.property("eventDetails.coordinateY").as("coordinateY"))
.add(Projections.property("eventDetails.screenWidth").as("screenWidth"))
.add(Projections.property("eventDetails.screenHeight").as("screenHeight"))
.add(Projections.property("eventDetails.orientation").as("orientation"))
.add(Projections.property("eventDetails.viewportHeight").as("viewportHeight"))
.add(Projections.property("eventDetails.viewportWidth").as("viewportWidth"))
.add(Projections.property("eventDetails.numOfTaps").as("numOfTaps"))
.add(Projections.property("eventDetails.tagName").as("tagName"))
.add(Projections.property("eventDetails.scrollTop").as("scrollTop"))
.add(Projections.property("eventDetails.timeZone").as("timeZone"))
.add(Projections.property("eventDetails.zoneDateTime").as("zoneDateTime"))
.add(Projections.property("eventDetails.imageName").as("imageName"))
.add(Projections.property("BID").as("bid"))
.add(Projections.property("browserName").as("browserName"))
.add(Projections.property("browserVersion").as("browserVersion"))
.add(Projections.property("userAgetntId").as("userAgentId"))
.add(Projections.property("deviceDetails.DID").as("did"))
.add(Projections.property("deviceDetails.osName").as("osName"))
.add(Projections.property("deviceDetails.deviceName").as("deviceName")));
list = criteria.setResultTransformer(Transformers.aliasToBean(UserDetailsDTO.class)).list();
session.getTransaction().commit();
session.close();
list = getPID(list);
return list;
}
public List<DmFolderNode> getChildFolderNodeList(String folderIdParent) {
DmFolder parentFolder = null;
if (folderIdParent != null) {
parentFolder = findById(folderIdParent);
}
List<DmFolderNode> folderNodeList = null;
if (parentFolder != null) {
String sqlSub =
"SELECT"
+ " count(*)"
+ " from dm_folder fChild"
// + " left join dm_folder_permission fChildPerm"
// + " on fChild.df_id = fChildPerm.df_id"
+ " where fChild.df_parent_id = f.df_id"
// + " and fChildPerm.da_id = :accountSharedId"
// + " and fChildPerm.da_id <> :accountOwnerId"
;
String sql =
"SELECT"
+ " f.df_id as \"id\", f.df_name as \"name\", ("
+ sqlSub
+ ") as \"numChildren\""
+ ", f.da_id_owner as \"ownerAccountId\", fp.dfp_permission_type as \"permissionType\""
+ " FROM dm_folder_permission fp"
+ " LEFT JOIN dm_folder f"
+ " on fp.df_id = f.df_id and fp.da_id = f.da_id_owner"
+ " WHERE f.df_parent_id = :folderIdParent"
+ " ORDER BY f.df_name asc";
SQLQuery query = getSession().createSQLQuery(sql);
query.setString("folderIdParent", folderIdParent);
// query.setString("accountOwnerId", parentFolder.getOwner().getId());
query.addScalar("id", Hibernate.STRING);
query.addScalar("name", Hibernate.STRING);
query.addScalar("numChildren", Hibernate.INTEGER);
query.addScalar("ownerAccountId", Hibernate.STRING);
query.addScalar("permissionType", Hibernate.INTEGER);
query.setResultTransformer(Transformers.aliasToBean(DmFolderNode.class));
folderNodeList = query.list();
}
return folderNodeList;
}
public List<SchoolPanoramicImage> getSchoolPanorama(int schoolId) {
String HQL =
"SELECT id as id,title as title, panoImage as panoImage from SchoolPanoramicImage where school.id = :schoolId";
HibernateUtil hibernateUtil = new HibernateUtil();
Session session = hibernateUtil.openSession();
Query query =
session
.createQuery(HQL)
.setResultTransformer(Transformers.aliasToBean(SchoolPanoramicImage.class));
query.setParameter("schoolId", schoolId);
List<SchoolPanoramicImage> schoolImageGalleryList = query.list();
session.close();
return schoolImageGalleryList;
}
public List<Mobile> takeMobiles() {
List<Mobile> lst = null;
try {
Session session = getSession();
String sql = takeSQL("getData");
Query query =
session
.createSQLQuery(sql.toString())
.addScalar("id", Hibernate.LONG)
.addScalar("link", Hibernate.STRING)
.addScalar("web", Hibernate.STRING)
.addScalar("type", Hibernate.STRING)
.addScalar("brand", Hibernate.STRING)
.addScalar("price", Hibernate.STRING)
.addScalar("priceNumber", Hibernate.LONG)
.addScalar("itemCode", Hibernate.STRING)
.addScalar("name", Hibernate.STRING)
.addScalar("model", Hibernate.STRING)
.addScalar("storage", Hibernate.STRING)
.addScalar("ram", Hibernate.STRING)
.addScalar("screen", Hibernate.STRING)
.addScalar("cpu", Hibernate.STRING)
.addScalar("backCamera", Hibernate.STRING)
.addScalar("frontCamera", Hibernate.STRING)
.addScalar("os", Hibernate.STRING)
.addScalar("battery", Hibernate.STRING)
.addScalar("sim", Hibernate.STRING)
.addScalar("promotion", Hibernate.STRING)
.addScalar("color", Hibernate.STRING)
.addScalar("lastUpdate", Hibernate.DATE)
.setResultTransformer(Transformers.aliasToBean(Mobile.class));
for (int i = 0; i < param.size(); i++) {
query.setParameter(i, param.get(i));
}
if (startval >= 0) {
query.setFirstResult(startval);
}
if (count >= 0) {
query.setMaxResults(count);
}
lst = query.list();
} catch (Exception ex) {
ex.printStackTrace();
}
return lst;
}
public List<CommonSport> listDataTerminalByBusiness(
String terminalProtectedArea, String terminalTimeSearch, Integer intType) {
int dataType = 2;
String sql =
this.buildTerminalByBusinessSql(
terminalProtectedArea, terminalTimeSearch, null, intType, dataType);
List<CommonSport> list =
this.getSession()
.createSQLQuery(sql)
.addScalar("statdate", Hibernate.STRING)
.addScalar("context", Hibernate.STRING)
.addScalar("detail", Hibernate.STRING)
.setResultTransformer(Transformers.aliasToBean(CommonSport.class))
.list();
return buildList1(list, intType, dataType);
}