JdbcEventRepository.java

/*
 /*
 * Copyright 2010 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.springsource.greenhouse.events;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.LocalDate;
import org.joda.time.MutableDateTime;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.JoinRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.springsource.greenhouse.utils.Location;
import com.springsource.greenhouse.utils.ResourceReference;
import com.springsource.greenhouse.utils.SubResourceReference;

/**
 * EventRepository implementation that stores Event data in a relational database using the JDBC API.
 * @author Keith Donald
 */
@Repository
public class JdbcEventRepository implements EventRepository {

	private final JdbcTemplate jdbcTemplate;

	@Inject
	public JdbcEventRepository(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Event> findUpcomingEvents(Long afterMillis) {
		return jdbcTemplate.query(SELECT_UPCOMING_EVENTS, eventMapper.list(), new Date(afterMillis != null ? afterMillis : System.currentTimeMillis()));
	}
	
	public Event findEventBySlug(String groupSlug, Integer year, Integer month, String slug) {
		return jdbcTemplate.queryForObject(SELECT_EVENT_BY_SLUG, eventMapper.single(), groupSlug, year, month, slug);
	}

	public String findEventSearchString(Long eventId) {
		return jdbcTemplate.queryForObject("select g.hashtag from Event e, MemberGroup g where e.id = ? and e.memberGroup = g.id", String.class, eventId);
	}

	public String findSessionSearchString(Long eventId, Integer sessionId) {
		return jdbcTemplate.queryForObject("select (select g.hashtag from Event e, MemberGroup g where e.id = ? and e.memberGroup = g.id) || ' ' || hashtag from EventSession where event = ? and id = ?", String.class, eventId, eventId, sessionId);
	}

	@Transactional
	public List<EventSession> findSessionsOnDay(Long eventId, LocalDate day, Long attendeeId) {
		DateTimeZone eventTimeZone = DateTimeZone.forID(jdbcTemplate.queryForObject("select timezone from Event where id = ?", String.class, eventId));
		DateTime dayStart = day.toDateTimeAtStartOfDay(eventTimeZone);
		DateTime dayEnd = dayStart.plusDays(1);
		return jdbcTemplate.query(SELECT_SESSIONS_ON_DAY, eventSessionMapper.list(), attendeeId, eventId, dayStart.toDate(), dayEnd.toDate());
	}

	public List<EventSession> findEventFavorites(Long eventId, Long attendeeId) {
		return jdbcTemplate.query(SELECT_EVENT_FAVORITES, eventSessionMapper.list(), eventId, attendeeId, eventId);
	}

	public List<EventSession> findAttendeeFavorites(Long eventId, Long attendeeId) {
		return jdbcTemplate.query(SELECT_ATTENDEE_FAVORITES, eventSessionMapper.list(), attendeeId, eventId);
	}

	@Transactional
	public boolean toggleFavorite(Long eventId, Integer sessionId, Long attendeeId) {
		boolean favorite = jdbcTemplate.queryForObject("select exists(select 1 from EventSessionFavorite where event = ? and session = ? and attendee = ?)", Boolean.class, eventId, sessionId, attendeeId);
		if (favorite) {
			jdbcTemplate.update("delete from EventSessionFavorite where event = ? and session = ? and attendee = ?", eventId, sessionId, attendeeId);
		} else {
			jdbcTemplate.update("insert into EventSessionFavorite (event, session, attendee) values (?, ?, ?)", eventId, sessionId, attendeeId);
		}
		return !favorite;
	}

	@Transactional
	public Float rate(Long eventId, Integer sessionId, Long attendeeId, Rating rating) throws RatingPeriodClosedException {
		if (!isSessionEnded(eventId, sessionId)) {
			throw new RatingPeriodClosedException(eventId, sessionId);
		}
		boolean rated = jdbcTemplate.queryForObject("select exists(select 1 from EventSessionRating where event = ? and session = ? and attendee = ?)", Boolean.class, eventId, sessionId, attendeeId);
		if (rated) {
			jdbcTemplate.update("update EventSessionRating set rating = ?, comment = ? where event = ? and session = ? and attendee = ?", rating.getValue(), rating.getComment(), eventId, sessionId, attendeeId);			
		} else {
			jdbcTemplate.update("insert into EventSessionRating (event, session, attendee, rating, comment) values (?, ?, ?, ?, ?)", eventId, sessionId, attendeeId, rating.getValue(), rating.getComment());			
		}
		Float newAvgRating = jdbcTemplate.queryForObject("select round(avg(cast(rating as double)) * 2, 0) / 2 from EventSessionRating where event = ? and session = ? group by event, session", Float.class, eventId, sessionId);
		jdbcTemplate.update("update EventSession set rating = ? where event = ? and id = ?", newAvgRating, eventId, sessionId);
		return newAvgRating;
	}
	
	@Transactional
	public long addEvent() {
		return 0;
	}
   
	// internal helpers
	
	private boolean isSessionEnded(Long eventId, Integer sessionId) {
		Date endTime = jdbcTemplate.queryForObject("select ts.endTime from EventTimeSlot ts, EventSession s where s.event = ? and s.id = ? and ts.id = s.timeSlot", Date.class, eventId, sessionId);
		return new Date().after(endTime);
	}

	private final JoinRowMapper<Event, Long> eventMapper = new JoinRowMapper<Event, Long>() {
		protected Long mapId(ResultSet rs) throws SQLException {
			return rs.getLong("id");
		}
		protected Event mapRoot(Long id, ResultSet rs) throws SQLException {
			String eventTimeZone = rs.getString("timeZone");
			return new Event(id, rs.getString("title"), DateTimeZone.forID(eventTimeZone), adjustEventTimeToUTC(rs.getTimestamp("startTime"), eventTimeZone), adjustEventTimeToUTC(rs.getTimestamp("endTime"), eventTimeZone),
					rs.getString("slug"), rs.getString("description"), rs.getString("hashtag"), new ResourceReference<String>(rs.getString("groupSlug"), rs.getString("groupName")));
		}
		protected void addChild(Event event, ResultSet rs) throws SQLException {
			event.addVenue(new Venue(rs.getLong("venueId"), rs.getString("venueName"), rs.getString("venuePostalAddress"), 
					new Location(rs.getDouble("venueLatitude"), rs.getDouble("venueLongitude")), rs.getString("venueLocationHint")));
		}
	};

	private final JoinRowMapper<EventSession, Integer> eventSessionMapper = new JoinRowMapper<EventSession, Integer>() {
		protected Integer mapId(ResultSet rs) throws SQLException {
			return rs.getInt("id");
		}
		protected EventSession mapRoot(Integer id, ResultSet rs) throws SQLException {
			String eventTimeZone = "America/New_York"; // HACK: For now hard-code to S2GX 2012's value while sorting this out.			
			return new EventSession(id, rs.getString("title"), adjustEventTimeToUTC(rs.getTimestamp("startTime"), eventTimeZone), adjustEventTimeToUTC(rs.getTimestamp("endTime"), eventTimeZone),
					rs.getString("description"), rs.getString("hashtag"), rs.getFloat("rating"), new SubResourceReference<Long, Integer>(rs.getLong("venue"), rs.getInt("room"), rs.getString("roomName")), rs.getBoolean("favorite"));
		}
		protected void addChild(EventSession session, ResultSet rs) throws SQLException {
			session.addLeader(new EventSessionLeader(rs.getString("name")));					
		}
	};
	
	private static DateTime adjustEventTimeToUTC(Timestamp timestamp, String eventTimeZone) {
		MutableDateTime mutableDateTime = new DateTime(timestamp).toMutableDateTime();
		mutableDateTime.setZoneRetainFields(DateTimeZone.forID(eventTimeZone));
		DateTime utcAdjustedDateTime = mutableDateTime.toDateTime().toDateTime(DateTimeZone.UTC);
		return utcAdjustedDateTime;
	}

	private static final String SELECT_FROM_EVENT_SESSION = "select s.id, s.title, ts.startTime, ts.endTime, s.description, s.hashtag, s.rating, s.venue, s.room, r.name as roomName, (f.attendee is not null) as favorite, l.name from EventSession s ";
	
	private static final String SELECT_EVENT = "select e.id, e.title, e.timeZone, e.startTime, e.endTime, e.slug, e.description, g.hashtag, g.slug as groupSlug, g.name as groupName, " + 
		"v.id as venueId, v.name as venueName, v.postalAddress as venuePostalAddress, v.latitude as venueLatitude, v.longitude as venueLongitude, v.locationHint as venueLocationHint from Event e " + 
		"inner join MemberGroup g on e.memberGroup = g.id " + 
		"inner join EventVenue ev on e.id = ev.event " + 
		"inner join Venue v on ev.venue = v.id";
		
	private static final String SELECT_UPCOMING_EVENTS = SELECT_EVENT + " where e.endTime > ? order by e.startTime";

	private static final String SELECT_EVENT_BY_SLUG = SELECT_EVENT + " where g.slug = ? and extract(year from e.startTime) = ? and extract(month from e.startTime) = ? and e.slug = ?";

	private static final String SELECT_SESSIONS_ON_DAY = SELECT_FROM_EVENT_SESSION +
		"left outer join VenueRoom r on s.venue = r.venue and s.room = r.id " +
		"left outer join EventSessionFavorite f on s.event = f.event and s.id = f.session and f.attendee = ? " +
		"inner join EventSessionLeader sl on s.event = sl.event and s.id = sl.session " +
		"inner join Leader l on sl.leader = l.id " + 
		"inner join EventTimeSlot ts on ts.id = s.timeSlot " +
		"where s.event = ? and ts.startTime >= ? and ts.endTime <= ? " +
		"order by ts.startTime, s.id, sl.rank";

	private static final String SELECT_EVENT_FAVORITES = SELECT_FROM_EVENT_SESSION +
		"inner join (select top 10 session, count(*) as favoriteCount from EventSessionFavorite where event = ? group by session) top on s.id = top.session " +
		"left outer join VenueRoom r on s.venue = r.venue and s.room = r.id " +
		"left outer join EventSessionFavorite f on s.event = f.event and s.id = f.session and f.attendee = ? " +
		"inner join EventSessionLeader sl on s.event = sl.event and s.id = sl.session " +
		"inner join Leader l on sl.leader = l.id " + 
		"inner join EventTimeSlot ts on ts.id = s.timeSlot " +
		"where s.event = ? " +
		"order by top.favoriteCount desc, s.id, sl.rank";

	private static final String SELECT_ATTENDEE_FAVORITES = SELECT_FROM_EVENT_SESSION +
		"left outer join VenueRoom r on s.venue = r.venue and s.room = r.id " +
		"inner join EventSessionFavorite f on s.event = f.event and s.id = f.session and f.attendee = ? " +	
		"inner join EventSessionLeader sl on s.event = sl.event and s.id = sl.session " +
		"inner join Leader l on sl.leader = l.id " + 
		"inner join EventTimeSlot ts on ts.id = s.timeSlot " +
		"where s.event = ? " +
		"order by f.rank, s.id, sl.rank";
	
}