/*
* #%L
* Course Signup Implementation
* %%
* Copyright (C) 2010 - 2013 University of Oxford
* %%
* Licensed under the Educational Community 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://opensource.org/licenses/ecl2
*
* 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.
* #L%
*/
package uk.ac.ox.oucs.vle;
import org.hibernate.*;
import org.hibernate.criterion.*;
import org.hibernate.sql.JoinFragment;
import org.joda.time.DateTimeConstants;
import org.joda.time.LocalDate;
import org.joda.time.MonthDay;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import uk.ac.ox.oucs.vle.CourseSignupService.Range;
import uk.ac.ox.oucs.vle.CourseSignupService.Status;
import java.sql.SQLException;
import java.util.*;
public class CourseDAOImpl extends HibernateDaoSupport implements CourseDAO {
private static final MonthDay FIRST_DAY_OF_ACADEMIC_YEAR = new MonthDay(DateTimeConstants.SEPTEMBER, 1);
// Set lastYear to 1st September (start of last academic year)
public static LocalDate getPreviousYearBeginning(LocalDate currentDate) {
int currentCivilYear = currentDate.getYear();
int previousAcademicYear;
// If we've started a new civil year and haven't changed the academic year yet, go back one more year.
if (currentDate.isBefore(FIRST_DAY_OF_ACADEMIC_YEAR.toLocalDate(currentCivilYear))) {
previousAcademicYear = currentCivilYear - 2;
} else {
previousAcademicYear = currentCivilYear - 1;
}
return FIRST_DAY_OF_ACADEMIC_YEAR.toLocalDate(previousAcademicYear);
}
public CourseGroupDAO findCourseGroupById(final String courseId) {
return (CourseGroupDAO) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Criteria criteria = session.createCriteria(CourseGroupDAO.class).add(Restrictions.eq("courseId", courseId));
return criteria.uniqueResult();
}
});
}
public List<CourseComponentDAO> findCourseComponents(final String courseId, final Range range, final Date now) {
return (List<CourseComponentDAO>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(CourseComponentDAO.class);
Criteria subCriteria;
switch (range) {
case NOTSTARTED:
case UPCOMING:
criteria.add(Restrictions.or(
Restrictions.gt("baseDate", now),
Restrictions.and(Restrictions.isNull("baseDate"), Restrictions.isNotNull("startsText"))));
break;
case PREVIOUS:
Date startLastYear = getPreviousYearBeginning(new LocalDate(now)).toDate();
criteria.add(
Restrictions.or(
Restrictions.and(Restrictions.le("baseDate", now), Restrictions.gt("baseDate", startLastYear)),
Restrictions.and(Expression.isNull("baseDate"), Restrictions.isNull("startsText"))
));
break;
}
subCriteria = criteria.createCriteria("groups", JoinFragment.INNER_JOIN);
subCriteria.add(Restrictions.eq("courseId", courseId));
subCriteria.add(Restrictions.eq("hideGroup", false));
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return criteria.list();
}
});
}
@SuppressWarnings("unchecked")
public CourseGroupDAO findUpcomingComponents(String courseId, Date available) {
List<CourseGroupDAO> courseGroups = getHibernateTemplate().findByNamedParam(
"select distinct cg from CourseGroupDAO cg left join fetch cg.components as component where cg.courseId = :courseId and component.closes > :closes",
new String[]{"courseId", "closes"}, new Object[]{courseId, available});
int results = courseGroups.size();
if (results > 0) {
if (results > 1) {
throw new IllegalStateException("To many results ("+ results + ") found for "+ courseId );
}
return courseGroups.get(0);
}
return null;
}
public List<CourseComponentDAO> findOpenComponents(String id, Date at) {
// TODO Auto-generated method stub
return null;
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findAllGroups() {
return getHibernateTemplate().loadAll(CourseGroupDAO.class);
}
@SuppressWarnings("unchecked")
public List<CourseComponentDAO> findAllComponents() {
return getHibernateTemplate().loadAll(CourseComponentDAO.class);
}
public CourseGroupDAO findAvailableCourseGroupById(String courseId) {
// TODO Auto-generated method stub
return null;
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findCourseGroupByDept(final String deptId, final Range range, final Date now, final boolean external) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
/**
* Note:
* This can't be easily migrated to Hibernate Query API as collections are not supported
* org.hibernate.MappingException: collection was not an association: uk.ac.ox.oucs.vle.CourseGroupDAO.otherDepartments
*/
// Need the DISTINCT ROOT ENTITY filter.
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Date startLastYear = getPreviousYearBeginning(LocalDate.fromDateFields(now)).toDate();
StringBuffer querySQL = new StringBuffer();
querySQL.append("SELECT DISTINCT cg.* ");
querySQL.append("FROM course_group cg ");
querySQL.append("LEFT JOIN course_group_otherDepartment cgd on cgd.courseGroupMuid = cg.muid ");
querySQL.append("LEFT JOIN course_group_component cgc on cgc.courseGroupMuid = cg.muid ");
querySQL.append("LEFT JOIN course_component cc on cgc.courseComponentMuid = cc.muid ");
querySQL.append("WHERE ");
querySQL.append("visibility != 'PR' AND ");
if (external) {
querySQL.append("visibility != 'RS' AND ");
}
querySQL.append("hideGroup = false AND ");
switch (range) {
case UPCOMING:
querySQL.append("((cc.baseDate is null AND cc.startsText is not null) OR cc.baseDate > :now) AND ");
break;
case PREVIOUS:
querySQL.append("((cc.baseDate is null AND cc.startsText is null) OR (cc.baseDate <= :now AND cc.baseDate >= :lastYear)) AND ");
break;
}
querySQL.append("(otherDepartment = :deptId ");
querySQL.append("OR (dept = :deptId and (subunit is NULL or subunit = ''))) ");
querySQL.append("ORDER BY cg.title ");
Query query = session.createSQLQuery(querySQL.toString()).addEntity(CourseGroupDAO.class);
query.setString("deptId", deptId);
query.setDate("now", now);
if (range.equals(range.PREVIOUS)) {
query.setDate("lastYear", startLastYear);
}
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findCourseGroupBySubUnit(final String subunitId, final Range range, final Date now, final boolean external) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
// Need the DISTINCT ROOT ENTITY filter.
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Date startLastYear = getPreviousYearBeginning(LocalDate.now()).toDate();
Criteria criteria = session.createCriteria(CourseGroupDAO.class);
criteria.add(Restrictions.eq("subunit", subunitId));
criteria.add(Restrictions.ne("visibility", "PR"));
if (external) {
criteria.add(Restrictions.ne("visibility", "RS"));
}
criteria.add(Restrictions.eq("hideGroup", false));
switch (range) {
case UPCOMING:
criteria = criteria.createCriteria("components", JoinFragment.LEFT_OUTER_JOIN).add(
Expression.or(Expression.gt("baseDate", now), Expression.and(Expression.isNull("baseDate"), Expression.isNotNull("startsText"))));
break;
case PREVIOUS:
criteria = criteria.createCriteria("components", JoinFragment.LEFT_OUTER_JOIN).add(
Expression.or(
Expression.and(Expression.le("baseDate", now), Expression.gt("baseDate", startLastYear)),
Expression.and(Expression.isNull("baseDate"), Expression.isNull("startsText"))
));
break;
}
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.addOrder(Order.asc("title"));
return criteria.list();
}
});
}
/**
* Find all courseGroups that share a component with Id componentId
*/
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findCourseGroupByComponent(final String componentId) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery(
"select * from course_component " +
"left join course_group_component on course_group_component.courseComponentMuid = course_component.muid " +
"left join course_group on course_group_component.courseGroupMuid = course_group.muid " +
"where course_component.presentationId = :componentId").addEntity(CourseGroupDAO.class);
query.setString("componentId", componentId);
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<Object[]> findSubUnitByDept(final String deptId) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
// Need the DISTINCT ROOT ENTITY filter.
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createQuery("select distinct subunit, subunitName " +
"from CourseGroupDAO cg " +
"where cg.dept = :deptId and cg.subunit <> '' order by 2");
query.setString("deptId", deptId);
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseDepartmentDAO> findAllDepartments() {
return getHibernateTemplate().executeFind(new HibernateCallback() {
// Need the DISTINCT ROOT ENTITY filter.
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Query query = session.createSQLQuery("select * from course_department").addEntity(CourseDepartmentDAO.class);
return query.list();
}
});
}
public CourseComponentDAO findCourseComponent(final String id) {
return (CourseComponentDAO) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
Criteria criteria = session.createCriteria(CourseComponentDAO.class);
criteria.add(Expression.eq("presentationId", id));
criteria.setResultTransformer(Criteria.ROOT_ENTITY);
return criteria.uniqueResult();
}
});
}
public CourseSignupDAO newSignup(String userId, String supervisorId, Date now) {
CourseSignupDAO signupDao = new CourseSignupDAO();
signupDao.setUserId(userId);
signupDao.setSupervisorId(supervisorId);
signupDao.setCreated(now);
return signupDao;
}
public String save(CourseSignupDAO signupDao) {
return getHibernateTemplate().save(signupDao).toString();
}
public void save(final CourseComponentDAO componentDao) {
getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session session) throws HibernateException, SQLException {
session.saveOrUpdate(componentDao);
for (CourseGroupDAO group: componentDao.getGroups()) {
session.refresh(group);
}
return null;
}
});
getHibernateTemplate().saveOrUpdate(componentDao);
}
public CourseSignupDAO findSignupById(String signupId) {
return (CourseSignupDAO) getHibernateTemplate().get(CourseSignupDAO.class, signupId);
}
public CourseSignupDAO findSignupByEncryptId(String signupId) {
return (CourseSignupDAO) getHibernateTemplate().get(CourseSignupDAO.class, signupId);
}
@SuppressWarnings("unchecked")
public List<CourseSignupDAO> findSignupForUser(final String userId, final Set<Status> statuses) {
return (List<CourseSignupDAO>)getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Criteria criteria = session.createCriteria(CourseSignupDAO.class);
criteria.add(Expression.eq("userId", userId));
if (!statuses.isEmpty()) {
criteria.add(Expression.in("status", statuses.toArray()));
}
criteria.setFetchMode("components", FetchMode.JOIN);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return criteria.list();
}
});
}
public CourseGroupDAO newCourseGroup(String id, String title, String dept, String subunit) {
CourseGroupDAO groupDao = new CourseGroupDAO();
groupDao.setCourseId(id);
groupDao.setTitle(title);
groupDao.setDept(dept);
groupDao.setSubunit(subunit);
return groupDao;
}
public void save(CourseGroupDAO groupDao) {
getHibernateTemplate().save(groupDao);
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findAdminCourseGroups(final String userId) {
// Finds all the coursegroups this user can admin.
List<CourseGroupDAO> adminGroups = findAdministratorCourseGroups(userId);
List<CourseGroupDAO> superGroups = findSuperUserCourseGroups(userId);
Set<CourseGroupDAO> allGroups = new HashSet<CourseGroupDAO>(superGroups);
allGroups.addAll(adminGroups);
return new ArrayList<CourseGroupDAO>(allGroups);
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findAdministratorCourseGroups(final String userId) {
// Finds all the coursegroups this user can admin.
return getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createSQLQuery("select * from course_group, " +
"(select courseGroupMuid from course_group_administrator " +
"where administrator = :userId) admins " +
"where course_group.muid = admins.courseGroupMuid " +
"and course_group.hideGroup = false").addEntity(CourseGroupDAO.class);
query.setString("userId", userId);
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findSuperUserCourseGroups(final String userId) {
// Finds all the coursegroups this user can superuser.
return getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createSQLQuery("select * from course_group, " +
"(select courseGroupMuid from course_group_superuser " +
"where superuser = :userId) admins " +
"where course_group.muid = admins.courseGroupMuid").addEntity(CourseGroupDAO.class);
query.setString("userId", userId);
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findLecturingCourseGroups(final String userId) {
// Finds all the coursegroups this user is teaching.
return getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createSQLQuery("select distinct cg.* " +
"from course_group cg " +
"LEFT JOIN course_group_component cgc on cgc.courseGroupMuid = cg.muid " +
"LEFT JOIN course_component cc on cgc.courseComponentMuid = cc.muid " +
"where cc.teacher = :userId").addEntity(CourseGroupDAO.class);
query.setString("userId", userId);
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseSignupDAO> findSignupByCourse(final String userId, final String courseId, final Set<Status> statuses) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query;
if (null != statuses && !statuses.isEmpty()) {
query = session.createSQLQuery("select * from course_signup " +
"left join course_group on course_signup.courseGroupMuid = course_group.muid " +
"where course_group.courseId = :courseId " +
"and course_signup.status in (:statuses)").addEntity(CourseSignupDAO.class);
Set<String> statusString = new HashSet<String>();
for (Status status : statuses) {
statusString.add(status.toString());
}
query.setParameterList("statuses", statusString);
} else {
query = session.createSQLQuery("select * from course_signup " +
"left join course_group on course_signup.courseGroupMuid = course_group.muid " +
"where course_group.courseId = :courseId").addEntity(CourseSignupDAO.class);
}
//query.setString("userId", userId);
query.setString("courseId", courseId);
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public Integer countSignupByCourse(final String courseId, final Set<Status> statuses, final Date now) {
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createQuery("select count(signup.id) from CourseSignupDAO signup "+
"left join signup.components component "+
"left join signup.group grp "+
"where grp.courseId = :courseId "+
"and component.starts > :now "+
"and signup.status in (:statuses)");
query.setString("courseId", courseId);
query.setDate("now", now);
query.setParameterList("statuses", statuses);
return ((Number)query.uniqueResult()).intValue();
}
});
}
public List<CourseSignupDAO> findSignupByComponent(final String componentId, final Set<Status> statuses) {
return findSignupByComponent(componentId, statuses, null);
}
// This has to load components and go from there because if a signup has multiple components it should appear under
// both of them.
public List<Map> findComponentSignups(final String componentId, final Set<Status> statuses, final Integer year) {
// This is an optimisation for exports. It orders by component then
return getHibernateTemplate().execute(new HibernateCallback<List<Map>>() {
public List<Map> doInHibernate(Session session)
throws HibernateException, SQLException {
Criteria find = session.createCriteria(CourseComponentDAO.class)
.createAlias("signups", "signup", CriteriaSpecification.LEFT_JOIN)
.createAlias("signups.group", "group", CriteriaSpecification.LEFT_JOIN)
.addOrder(Order.desc("presentationId"))
.addOrder(Order.desc("group.id"))
.addOrder(Order.desc("signup.id"))
.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
// If componentId is null then return all.
if (componentId != null && !"all".equals(componentId)) {
find.add(Restrictions.eq("presentationId", componentId));
}
if (null != statuses && !statuses.isEmpty()) {
find.add(Restrictions.in("signup.status", statuses));
}
if (null != year) {
LocalDate startYear = FIRST_DAY_OF_ACADEMIC_YEAR.toLocalDate(year);
LocalDate endYear = FIRST_DAY_OF_ACADEMIC_YEAR.toLocalDate(year+1);
find.add(Restrictions.between("starts", startYear.toDate(), endYear.toDate()));
}
Object result = find.list();
return (List<Map>) result;
}
});
}
@SuppressWarnings("unchecked")
public List<CourseSignupDAO> findSignupByComponent(final String componentId, final Set<Status> statuses, final Integer year) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query;
LocalDate startYear = null;
LocalDate endYear = null;
StringBuffer querySQL = new StringBuffer();
querySQL.append("select cs from CourseSignupDAO cs " +
"inner join fetch cs.components cc " +
"where cc.presentationId = :componentId");
if (null != statuses && !statuses.isEmpty()) {
querySQL.append(" and cs.status in (:statuses)");
}
if (null != year) {
startYear = FIRST_DAY_OF_ACADEMIC_YEAR.toLocalDate(year);
endYear = FIRST_DAY_OF_ACADEMIC_YEAR.toLocalDate(year+1);
querySQL.append(" and cc.starts between :starts and :ends");
}
query = session.createQuery(querySQL.toString());
query.setString("componentId", componentId);
if (null != statuses && !statuses.isEmpty()) {
query.setParameterList("statuses", statuses);
}
if (null != year) {
query.setDate("starts", startYear.toDate());
query.setDate("ends", endYear.toDate());
}
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseSignupDAO> findSignupPending(final String userId) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery(
"select distinct cs.id, cs.userId, cs.status, cs.created, cs.amended, cs.message, cs.supervisorId, cs.courseGroupMuid, cs.department, cs.specialReq " +
"from course_signup cs " +
"left join course_group_administrator ca on cs.courseGroupMuid = ca.courseGroupMuid " +
"inner join course_component_signup cp on cs.id = cp.signup " +
"inner join course_component cc on cp.courseComponentMuid = cc.muid " +
"where (ca.administrator = :userId and cs.status = :adminStatus) or (cs.supervisorId = :userId and cs.status = :supervisorStatus)").addEntity(CourseSignupDAO.class);
query.setString("userId", userId);
query.setParameter("adminStatus", Status.PENDING.name());
query.setParameter("supervisorStatus", Status.ACCEPTED.name());
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseSignupDAO> findSignupStillPendingOrAccepted(final Integer period) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery(
"select distinct cs.id, cs.userId, cs.status, cs.created, cs.amended, cs.message, cs.supervisorId, cs.courseGroupMuid, cs.department, cs.specialReq " +
"from course_signup cs " +
"left join course_group_administrator ca on cs.courseGroupMuid = ca.courseGroupMuid " +
"inner join course_component_signup cp on cs.id = cp.signup " +
"inner join course_component cc on cp.courseComponentMuid = cc.muid " +
"where (date_sub(curdate(), interval :period day) >= cs.amended " +
"or date_sub(curdate(), interval :period day) <= cc.starts) " +
"and (curdate() < cc.starts) " +
"and ((cs.status = :adminStatus) or (cs.status = :supervisorStatus))").addEntity(CourseSignupDAO.class);
query.setInteger("period", period);
query.setParameter("adminStatus", Status.PENDING.name());
query.setParameter("supervisorStatus", Status.ACCEPTED.name());
return query.list();
}
});
}
@SuppressWarnings("unchecked")
public List<CourseSignupDAO> findSignupApproval(final String userId) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery(
"select distinct cs.id, cs.userId, cs.status, cs.created, cs.amended, cs.message, cs.supervisorId, cs.courseGroupMuid, cs.department, cs.specialReq " +
"from course_signup cs " +
"left join course_group_administrator ca on cs.courseGroupMuid = ca.courseGroupMuid " +
"inner join course_component_signup cp on cs.id = cp.signup " +
"inner join course_component cc on cp.courseComponentMuid = cc.muid " +
"inner join course_department_approver da on da.department = cs.department " +
"where da.approver = :userId and cs.status = :approverStatus").addEntity(CourseSignupDAO.class);
query.setString("userId", userId);
query.setParameter("approverStatus", Status.APPROVED.name());
return query.list();
}
});
}
public CourseComponentDAO newCourseComponent(String id) {
CourseComponentDAO componentDao = new CourseComponentDAO();
componentDao.setPresentationId(id);
Calendar now = GregorianCalendar.getInstance();
componentDao.setCreated(now.getTime());
return componentDao;
}
@SuppressWarnings("unchecked")
public List<CourseGroupDAO> findCourseGroupByWords(final String[] words,
final Range range, final Date date, final boolean external) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Date startLastYear = getPreviousYearBeginning(LocalDate.now()).toDate();
Criteria criteria = session.createCriteria(CourseGroupDAO.class);
for(String word: words) {
criteria.add(Expression.ilike("title", word, MatchMode.ANYWHERE));
}
criteria.add(Expression.ne("visibility", "PR"));
if (external) {
criteria.add(Expression.ne("visibility", "RS"));
}
criteria.add(Expression.eq("hideGroup", false));
switch(range) {
case UPCOMING:
criteria = criteria.createCriteria("components", JoinFragment.LEFT_OUTER_JOIN).add(
Expression.or(Expression.gt("baseDate", date), Expression.and(Expression.isNull("baseDate"), Expression.isNotNull("startsText"))));
break;
case PREVIOUS:
criteria = criteria.createCriteria("components", JoinFragment.LEFT_OUTER_JOIN).add(
Expression.or(
Expression.and(Expression.le("baseDate", date), Expression.gt("baseDate", startLastYear)),
Expression.and(Expression.isNull("baseDate"), Expression.isNull("startsText"))
));
break;
}
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return criteria.list();
}
});
}
/**
*
*/
@SuppressWarnings("unchecked")
public List<CourseDepartmentDAO> findApproverDepartments(final String userId) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery(
"select * from course_department_approver " +
"left join course_department on course_department.code = course_department_approver.department " +
"where approver = :userId").addEntity(CourseDepartmentDAO.class);
query.setString("userId", userId);
return query.list();
}
});
}
/**
*
*/
@SuppressWarnings("unchecked")
public List<Object[]> findDepartmentApprovers(final String department) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery(
"select approver from course_department_approver " +
"where department = :deptId");
query.setString("deptId", department);
return query.list();
}
});
}
/**
*
*/
public CourseDepartmentDAO findDepartmentByCode(String code) {
return (CourseDepartmentDAO) getHibernateTemplate().get(CourseDepartmentDAO.class, code);
}
/**
*
*/
public void save(CourseDepartmentDAO departmentDao) {
getHibernateTemplate().save(departmentDao).toString();
}
/**
*
*/
public CourseSubunitDAO findSubunitByCode(String code) {
return (CourseSubunitDAO) getHibernateTemplate().get(CourseSubunitDAO.class, code);
}
/**
*
*/
public void save(CourseSubunitDAO subunitDao) {
getHibernateTemplate().save(subunitDao).toString();
}
/**
*
*/
public CourseOucsDepartmentDAO findOucsDeptByCode(String code) {
return (CourseOucsDepartmentDAO) getHibernateTemplate().get(CourseOucsDepartmentDAO.class, code);
}
/**
* select departmentCode from course_subunit left join course_oucs_department on t2Char = subunitCode where oucsCode = 'histfac'
*/
@SuppressWarnings("unchecked")
public CourseDepartmentDAO findDepartmentByPrimaryOrgUnit(final String primaryOrgUnit) {
List<Object> results = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery(
"select * from course_department " +
"left join course_subunit on course_subunit.departmentCode = course_department.code " +
"left join course_oucs_department on t2Char = subunitCode " +
"where oucsCode = :oucsDept").addEntity(CourseDepartmentDAO.class);
query.setString("oucsDept", primaryOrgUnit);
return query.list();
}
});
if (!results.isEmpty()) {
return (CourseDepartmentDAO)results.get(0);
}
return null;
}
/**
*
*/
public void save(CourseOucsDepartmentDAO oucsDao) {
getHibernateTemplate().save(oucsDao);
}
public void remove(CourseSignupDAO existingSignup) {
getHibernateTemplate().delete(existingSignup);
}
/**
* Used by tests to simulate another request being made.
*/
public void flushAndClear() {
getHibernateTemplate().flush();
getHibernateTemplate().clear();
}
/**
*
*/
@SuppressWarnings("unchecked")
public List<CourseComponentDAO> findCourseGroupsByCalendar(final boolean external, final String providerId) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
StringBuffer querySQL = new StringBuffer();
querySQL.append("select distinct * from course_component cc ");
querySQL.append("left join course_group_component cgc on cgc.courseComponentMuid = cc.muid ");
querySQL.append("left join course_group cg on cgc.courseGroupMuid = cg.muid ");
querySQL.append("where cc.starts > NOW() and cg.hideGroup = false ");
querySQL.append("and cg.visibility != 'PR' ");
if (external) {
querySQL.append("and cg.visibility != 'RS' ");
}
Query query = session.createSQLQuery(querySQL.toString()).addEntity(CourseComponentDAO.class);
return query.list();
}
});
}
/**
*
*/
@SuppressWarnings("unchecked")
public List<CourseComponentDAO> findCourseGroupsByNoDates(final boolean external, String providerId) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) {
StringBuffer querySQL = new StringBuffer();
querySQL.append("select distinct * from course_component cc ");
querySQL.append("left join course_group_component cgc on cgc.courseComponentMuid = cc.muid ");
querySQL.append("left join course_group cg on cgc.courseGroupMuid = cg.muid ");
querySQL.append("where cc.starts is NULL and ");
querySQL.append("(cc.baseDate > NOW() or (cc.baseDate is null and cc.startsText is not null)) and ");
querySQL.append("cg.hideGroup = false ");
querySQL.append("and cg.visibility != 'PR' ");
if (external) {
querySQL.append("and cg.visibility != 'RS' ");
}
Query query = session.createSQLQuery(querySQL.toString()).addEntity(CourseComponentDAO.class);
return query.list();
}
});
}
/**
*
*/
public int flagSelectedCourseGroups(final String source) {
return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) {
StringBuffer querySQL = new StringBuffer();
querySQL.append("update course_group ");
querySQL.append("set deleted = true ");
querySQL.append("where source = :source");
Query query = session.createSQLQuery(querySQL.toString()).setString("source", source);
return query.executeUpdate();
}
});
}
/**
*
*/
public int flagSelectedCourseComponents(final String source) {
return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) {
StringBuffer querySQL = new StringBuffer();
querySQL.append("update course_component ");
querySQL.append("set deleted = true ");
querySQL.append("where source = :source");
Query query = session.createSQLQuery(querySQL.toString()).setString("source", source);
return query.executeUpdate();
}
});
}
/**
* @{inhertDoc}
*/
public int flagSelectedDaisyCourseGroups(final String source, final Date now) {
return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) {
// This is done with a subselect so that it's not tied to MySQL.
// We need the inner select as MySQL doesn't let you select from the table you are updating.
String sql = "update course_group cg "+
"set cg.deleted = true "+
"where cg.source = :source and " +
"cg.muid in (select muid from ( " +
"select cg1.muid as muid from course_group cg1 " +
"left outer join course_signup as cs on cg1.muid = cs.courseGroupMuid "+
"left outer join course_group_component cgc on cg1.muid = cgc.courseGroupMuid " +
"left outer join course_component cc on cgc.courseComponentMuid = cc.muid " +
"where cc.baseDate > :now " +
"group by cg1.muid " +
"having count(cs.id) = 0 " +
") wrapper )";
Query query = session.createSQLQuery(sql)
.setString("source", source)
.setDate("now", now);
return query.executeUpdate();
}
});
}
/**
* @{inhertDoc}
*/
public int flagSelectedDaisyCourseComponents(final String source, final Date now) {
return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) {
// This is done with a subselect so that it's not tied to MySQL.
// We need the inner select as MySQL doesn't let you select from the table you are updating.
String sql = "update course_component cc "+
"set cc.deleted = true " +
"where cc.source = :source and " +
"cc.muid in (select muid from ( " +
"select cc1.muid as muid from course_component cc1 " +
"left outer join course_component_signup ccs on cc1.muid = ccs.courseComponentMuid "+
"left outer join course_signup cs on ccs.signup = cs.id " +
"where cc1.baseDate > :now " +
"group by cc1.muid " +
"having count(cs.id) = 0 " +
") wrapper )";
Query query = session.createSQLQuery(sql)
.setString("source", source)
.setDate("now", now);
return query.executeUpdate();
}
});
}
/**
* Hibernate handles the link between groups and components only one direction.
* We need to look after removing groups from the component
*/
@SuppressWarnings("unchecked")
public Collection<CourseGroupDAO> deleteSelectedCourseGroups(final String source) {
return (Collection<CourseGroupDAO>)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Criteria criteria = session.createCriteria(CourseGroupDAO.class);
criteria.add(Restrictions.eq("deleted", true));
criteria.add(Restrictions.eq("source", source));
List<CourseGroupDAO> groupDaos = criteria.list();
for (CourseGroupDAO groupDao : groupDaos) {
for (CourseComponentDAO component : groupDao.getComponents()) {
component.getGroups().remove(groupDao);
}
session.delete(groupDao);
}
return groupDaos;
}
});
}
@SuppressWarnings("unchecked")
public Collection<CourseComponentDAO> deleteSelectedCourseComponents(final String source) {
return (Collection<CourseComponentDAO>)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Criteria criteria = session.createCriteria(CourseComponentDAO.class);
criteria.add(Restrictions.eq("deleted", true));
criteria.add(Restrictions.eq("source", source));
List<CourseComponentDAO> componentDaos = criteria.list();
for (CourseComponentDAO componentDao : componentDaos) {
session.delete(componentDao);
}
return componentDaos;
}
});
}
public CourseCategoryDAO findCourseCategory(final String id) {
return (CourseCategoryDAO)getHibernateTemplate().execute(new HibernateCallback() {
public CourseCategoryDAO doInHibernate(Session session) {
return (CourseCategoryDAO)session.get(CourseCategoryDAO.class, id);
}
});
}
public void save(final CourseCategoryDAO category) {
/*
* Ok so the problem is that there maybe multiple importers running at the same time and they both
* want to insert the same category in different transactions. Because you can't do an atomic SELECT
* then INSERT in SQL you can end up with 2 transactions trying to do the insert. This is made more
* likely by hibernate batching up all the inserts/updates. As the import is run in one big transaction
* we don't want to get an exception partway through it.
*
* We can't alter hibernate to do this insert as using IGNORE means the number of rows affected doesn't
* match so hibernate throws an exception and we can't use INSERT ON DUPLICATE UPDATE as that then
* returns 2 (or 0) rows affected which again causes hibernate to throw an exception.
*
* We can't use a REPLACE as when the row exists it doesn't do an update it actually does a DELETE followed
* by an INSERT which causes all the foreign key constraints to break and the REPLACE command to fail.
*
* So we just do the INSERT manually in SQL and ignore the number of rows affected. As INSERT IGNORE
* isn't available on H2 we use a JOIN to do an insert if it doesn't already exist only then insert it.
* http://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/
*
* Categories should never be updated and they are marked as such in the hbm file.
*/
getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException, SQLException {
Query query = session.createSQLQuery(
"INSERT INTO course_category (categoryId, categoryName, categoryType) "+
"SELECT ?, ?, ? " +
"FROM (SELECT 1 AS i) mutex " +
" LEFT OUTER JOIN course_category ON course_category.categoryId = ? " +
"WHERE mutex.i = 1 AND course_category.categoryId IS NULL");
query.setString(0, category.getCategoryId());
query.setString(1, category.getCategoryName());
query.setString(2, category.getCategoryType());
query.setString(3, category.getCategoryId());
query.executeUpdate();
// This puts the value into the hibernate session.
findCourseCategory(category.getCategoryId());
return null;
}
});
}
public void setFlushMode(int i) {
getHibernateTemplate().setFlushMode(i);
}
}