/**
* 前台查询酒店的级别,设备,类型
*
* @param propertyName
* @return
*/
@SuppressWarnings("unchecked")
public Map<String, Integer> findByHotelInfo(final String propertyName) {
final String queryString = "select new Map(? as hotelInfoName,count(?) as hotelInfoCount ) from Hotel group by ?";
return (Map<String, Integer>) this.getHibernateTemplate().execute(
new HibernateCallback() {
@Override
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createQuery(queryString);
query.setString(0, propertyName);
query.setString(1, propertyName);
query.setString(2, propertyName);
return query.uniqueResult();
}
});
}
// 价格部分,是否有效价格的过滤(ordinal最高)
exists函数的使用如下:
sb.append(" and exists (\r\n");
sb.append(" select s2.roomTypeId, s2.statusDate, max(p2.ordinal)" +
" from RoomStatus s2, RoomTypePrice p2 \r\n" +
" where s2.roomTypeId = p2.roomTypeId and s2.statusDate between p2.from and p2.to \r\n" +
" and s2.roomTypeId = s.roomTypeId and s2.statusDate = s.statusDate \r\n" +
" and s.statusDate >= :checkInDate and s.statusDate < :checkOutDate\r\n" +
" group by s.roomTypeId, s.statusDate\r\n" +
" having p.ordinal = max(p2.ordinal))\r\n");
// 价格部分,最高最低区间
if (condition.getBreakfirst())
sb.append(" and ((0 not in elements(p.prepayBreakfastTypes)" +
" or 0 not in elements(p.cashBreakfastTypes))" +
" or (ri.cashBreakfast <> 0 or ri.prepayBreakfast <>0 ))\n");