Java tutorial
/***************************************************************************** * Copyright 2012, 2013 University of Saskatchewan * * This file is part of the Curriculum Alignment Tool (CAT). * * CAT is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License as published by * the Free Software Foundation, either version 3 of the License, or *(at your option) any later version. * * CAT is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License * along with CAT. If not, see <http://www.gnu.org/licenses/>. * ****************************************************************************/ package ca.usask.gmcte.currimap.action; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.TreeMap; import org.apache.log4j.Logger; import org.hibernate.Session; import org.hibernate.criterion.Restrictions; import org.hibernate.transform.Transformers; import ca.usask.gmcte.currimap.model.Characteristic; import ca.usask.gmcte.currimap.model.CharacteristicType; import ca.usask.gmcte.currimap.model.ContributionOptionValue; import ca.usask.gmcte.currimap.model.Course; import ca.usask.gmcte.currimap.model.CourseClassification; import ca.usask.gmcte.currimap.model.CourseOffering; import ca.usask.gmcte.currimap.model.CourseOutcome; import ca.usask.gmcte.currimap.model.LinkCourseContributionProgramOutcome; import ca.usask.gmcte.currimap.model.LinkCourseOfferingContributionProgramOutcome; import ca.usask.gmcte.currimap.model.LinkCourseOutcomeProgramOutcome; import ca.usask.gmcte.currimap.model.LinkCourseProgram; import ca.usask.gmcte.currimap.model.LinkOrganizationCharacteristicType; import ca.usask.gmcte.currimap.model.LinkProgramProgramOutcome; import ca.usask.gmcte.currimap.model.LinkProgramProgramOutcomeCharacteristic; import ca.usask.gmcte.currimap.model.LinkProgramQuestion; import ca.usask.gmcte.currimap.model.MasteryOptionValue; import ca.usask.gmcte.currimap.model.Organization; import ca.usask.gmcte.currimap.model.Program; import ca.usask.gmcte.currimap.model.ProgramAdmin; import ca.usask.gmcte.currimap.model.ProgramOutcome; import ca.usask.gmcte.currimap.model.ProgramOutcomeGroup; import ca.usask.gmcte.currimap.model.QuestionResponse; import ca.usask.gmcte.currimap.model.Time; import ca.usask.gmcte.currimap.model.to.CourseAssessmentOption; import ca.usask.gmcte.currimap.model.to.CourseOfferingContribution; import ca.usask.gmcte.currimap.model.to.CourseTeachingMethodOption; import ca.usask.gmcte.currimap.model.to.Pair; import ca.usask.gmcte.currimap.model.to.ProgramOutcomeCourseContribution; import ca.usask.gmcte.util.HibernateUtil; public class ProgramManager { private static ProgramManager instance; private static Logger logger = Logger.getLogger(ProgramManager.class); public boolean save(String name, String description, String organizationId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { /** Creating Pojo */ Organization organization = (Organization) session.get(Organization.class, Integer.parseInt(organizationId)); Program o = new Program(); o.setOrganization(organization); o.setName(name); o.setDescription(description); session.save(o); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveProgramOutcomeGroupNameById(String value, int programOutcomeGroupId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { ProgramOutcomeGroup o = (ProgramOutcomeGroup) session.get(ProgramOutcomeGroup.class, programOutcomeGroupId); o.setName(value); session.merge(o); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveNewProgramOutcomeNameAndProgram(String value, int programId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { ProgramOutcomeGroup o = new ProgramOutcomeGroup(); o.setProgramSpecific(programId < 0 ? "N" : "Y"); o.setProgramId(programId); o.setName(value); session.save(o); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveProgramOutcomeNameById(String value, int programOutcomeId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { ProgramOutcome o = (ProgramOutcome) session.get(ProgramOutcome.class, programOutcomeId); o.setName(value); session.merge(o); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveNewProgramOutcomeNameAndGroup(String value, int programOutcomeGroupId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { ProgramOutcome o = new ProgramOutcome(); ProgramOutcomeGroup group = (ProgramOutcomeGroup) session.get(ProgramOutcomeGroup.class, programOutcomeGroupId); o.setGroup(group); o.setDescription(""); o.setName(value); session.save(o); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveProgramOutcomeDescriptionById(String value, int programOutcomeId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { ProgramOutcome o = (ProgramOutcome) session.get(ProgramOutcome.class, programOutcomeId); o.setDescription(value); session.merge(o); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveCourseOutcomeProgramOutcome(int outcomeId, int programOutcomeId, int courseOfferingId, int existingLinkId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { ProgramOutcome pOutcome = (ProgramOutcome) session.get(ProgramOutcome.class, programOutcomeId); CourseOffering courseOffering = (CourseOffering) session.get(CourseOffering.class, courseOfferingId); CourseOutcome outcome = (CourseOutcome) session.get(CourseOutcome.class, outcomeId); logger.debug(outcomeId + " " + (outcome == null)); LinkCourseOutcomeProgramOutcome o = null; if (existingLinkId > -1) // need to update or delete { o = (LinkCourseOutcomeProgramOutcome) session.get(LinkCourseOutcomeProgramOutcome.class, existingLinkId); if (outcomeId > -1) { o.setCourseOutcome(outcome); session.merge(o); } else session.delete(o); // contribution is invalid (or 0) Delete it } else { //need to create a new one o = new LinkCourseOutcomeProgramOutcome(); o.setCourseOffering(courseOffering); o.setCourseOutcome(outcome); o.setProgramOutcome(pOutcome); session.save(o); } session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); logger.error("Oops " + outcomeId + " " + programOutcomeId + " " + courseOfferingId, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean update(String id, String name, String description) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { Program o = (Program) session.get(Program.class, Integer.parseInt(id)); o.setName(name); o.setDescription(description); session.merge(o); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveLinkCourseProgram(int courseId, int programId, int classification, int time) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { /** Creating Pojo */ CourseClassification cl = (CourseClassification) session.get(CourseClassification.class, classification); Program p = (Program) session.get(Program.class, programId); Course c = (Course) session.get(Course.class, courseId); Time min = (Time) session.get(Time.class, time); LinkCourseProgram l = new LinkCourseProgram(); l.setCourse(c); l.setCourseClassification(cl); l.setTime(min); l.setProgram(p); session.save(l); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean updateLinkCourseProgram(int id, int classification, int time) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { LinkCourseProgram l = (LinkCourseProgram) session.get(LinkCourseProgram.class, id); CourseClassification cl = (CourseClassification) session.get(CourseClassification.class, classification); Time min = (Time) session.get(Time.class, time); l.setCourseClassification(cl); l.setTime(min); session.merge(l); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean removeProgramCourse(int courseLinkId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { LinkCourseProgram l = (LinkCourseProgram) session.get(LinkCourseProgram.class, courseLinkId); Program p = l.getProgram(); Course c = l.getCourse(); //any program contributions in the system linked to the course offering and program outcomes need to be deleted as well. @SuppressWarnings("unchecked") List<LinkCourseOfferingContributionProgramOutcome> offeringContributions = (List<LinkCourseOfferingContributionProgramOutcome>) session .createQuery("FROM LinkCourseOfferingContributionProgramOutcome lcoc " + "WHERE linkProgramOutcome IN (SELECT l FROM LinkProgramProgramOutcome l WHERE l.program.id=:programId) " + " AND lcoc.courseOffering.course.id = :courseId") .setParameter("programId", p.getId()).setParameter("courseId", c.getId()).list(); for (LinkCourseOfferingContributionProgramOutcome contr : offeringContributions) { session.delete(contr); } //any program contributions in the system linked to the course and program outcomes need to be deleted as well. @SuppressWarnings("unchecked") List<LinkCourseContributionProgramOutcome> courseContributions = (List<LinkCourseContributionProgramOutcome>) session .createQuery("FROM LinkCourseContributionProgramOutcome lcoc " + "WHERE linkProgramOutcome IN (SELECT l FROM LinkProgramProgramOutcome l WHERE l.program.id=:programId) " + " AND lcoc.course.id = :courseId") .setParameter("programId", p.getId()).setParameter("courseId", c.getId()).list(); for (LinkCourseContributionProgramOutcome contr : courseContributions) { session.delete(contr); } @SuppressWarnings("unchecked") List<LinkCourseOutcomeProgramOutcome> courseOutcomeLinks = (List<LinkCourseOutcomeProgramOutcome>) session .createQuery("FROM LinkCourseOutcomeProgramOutcome " + " WHERE courseOffering.course.id=:courseId " + " AND programOutcome IN (SELECT l.programOutcome FROM LinkProgramProgramOutcome l WHERE l.program.id=:programId)") .setParameter("courseId", c.getId()).setParameter("programId", p.getId()).list(); for (LinkCourseOutcomeProgramOutcome link : courseOutcomeLinks) { session.delete(link); } session.delete(l); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean removeLinkCourseOutcomeProgramOutcome(int id) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { LinkCourseOutcomeProgramOutcome l = (LinkCourseOutcomeProgramOutcome) session .get(LinkCourseOutcomeProgramOutcome.class, id); session.delete(l); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean removeProgram(int programId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { Program p = (Program) session.get(Program.class, programId); List<LinkProgramProgramOutcome> outcomeLinks = p.getLinkProgramOutcomes(); for (LinkProgramProgramOutcome link : outcomeLinks) { session.delete(link); } List<QuestionResponse> questionResponses = QuestionManager.instance() .getAllQuestionResponsesForProgram(p, session); for (QuestionResponse link : questionResponses) { session.delete(link); } List<LinkProgramQuestion> programQuestions = p.getLinkProgramQuestions(); for (LinkProgramQuestion link : programQuestions) { session.delete(link); } List<LinkCourseProgram> courseLinks = p.getLinkCoursePrograms(); for (LinkCourseProgram link : courseLinks) { session.delete(link); } @SuppressWarnings("unchecked") List<ProgramAdmin> adminLinks = session.createQuery("FROM ProgramAdmin WHERE program.id = :programId") .setParameter("programId", p.getId()).list(); for (ProgramAdmin link : adminLinks) { session.delete(link); } session.delete(p); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } @SuppressWarnings("unchecked") public boolean removeProgramOutcome(int outcomeLinkId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { LinkProgramProgramOutcome l = (LinkProgramProgramOutcome) session.get(LinkProgramProgramOutcome.class, outcomeLinkId); //delete associated characteristics List<LinkProgramProgramOutcomeCharacteristic> chars = session.createQuery( "FROM LinkProgramProgramOutcomeCharacteristic WHERE linkProgramProgramOutcome.id=:linkId") .setParameter("linkId", outcomeLinkId).list(); for (LinkProgramProgramOutcomeCharacteristic linkedChar : chars) { session.delete(linkedChar); } List<LinkCourseOfferingContributionProgramOutcome> offeringLinks = (List<LinkCourseOfferingContributionProgramOutcome>) session .createQuery( "FROM LinkCourseOfferingContributionProgramOutcome l where l.linkProgramOutcome.id=:linkId") .setParameter("linkId", outcomeLinkId).list(); if (offeringLinks != null) { for (LinkCourseOfferingContributionProgramOutcome li : offeringLinks) { session.delete(li); } } session.delete(l); session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public Program getProgramById(int id) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); Program o = null; try { o = (Program) session.get(Program.class, id); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return o; } @SuppressWarnings("unchecked") public List<String> getAllAvailableTerms(Program p) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<String> toReturn = null; try { toReturn = (List<String>) session.createQuery( "SELECT DISTINCT co.term FROM CourseOffering co WHERE co.course IN (SELECT lcp.course FROM LinkCourseProgram lcp WHERE lcp.program.id=:programId) ORDER BY co.term") .setParameter("programId", p.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<ContributionOptionValue> getContributionOptions() { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<ContributionOptionValue> toReturn = null; try { toReturn = (List<ContributionOptionValue>) session .createQuery("FROM ContributionOptionValue ORDER BY lower(displayIndex)").list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<MasteryOptionValue> getMasteryOptions() { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<MasteryOptionValue> toReturn = null; try { toReturn = (List<MasteryOptionValue>) session .createQuery("FROM MasteryOptionValue ORDER BY lower(displayIndex)").list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public ProgramOutcome getProgramOutcomeById(int id) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); ProgramOutcome o = null; try { o = (ProgramOutcome) session.get(ProgramOutcome.class, id); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return o; } public ProgramOutcomeGroup getProgramOutcomeGroupById(int id) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); ProgramOutcomeGroup o = null; try { o = (ProgramOutcomeGroup) session.get(ProgramOutcomeGroup.class, id); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return o; } @SuppressWarnings("unchecked") public List<LinkCourseProgram> getAllLinkCourseProgramForOrganization(Organization o) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkCourseProgram> toReturn = null; try { toReturn = (List<LinkCourseProgram>) session.createQuery("FROM LinkCourseProgram l " + "WHERE l.program.organization.id=:orgId " + " OR (l.program.organization.parentOrganization IS NOT NULL AND l.program.organization.parentOrganization.id=:orgId) ORDER BY lower(l.program.name)") .setParameter("orgId", o.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<Program> getAllPrograms() { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<Program> toReturn = null; try { toReturn = (List<Program>) session.createQuery("FROM Program ORDER BY lower(name)").list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<Program> getAllProgramsForOrganization(Organization o) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<Program> toReturn = null; try { toReturn = (List<Program>) session.createCriteria(Program.class).add(Restrictions.eq("organization", o)) .list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<CharacteristicType> getProgramCharacteristicTypes(Program p) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CharacteristicType> toReturn = null; try { toReturn = (List<CharacteristicType>) session.createQuery( "select ct from CharacteristicType ct, LinkProgramCharacteristicType lpct where lpct.program.id=:programId and lpct.characteristicType = ct order by lpct.displayIndex") .setParameter("programId", p.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<CharacteristicType> getCandidateCharacteristicTypes(List<Integer> alreadyUsed) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CharacteristicType> toReturn = null; try { StringBuilder queryString = new StringBuilder("select ct from CharacteristicType ct "); if (alreadyUsed.size() > 0) { queryString.append("where ct.id not in ("); queryString.append(inString(alreadyUsed)); queryString.append(") "); } queryString.append(" order by ct.name"); toReturn = (List<CharacteristicType>) session.createQuery(queryString.toString()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public boolean addCharacteristicToOrganization(int charId, int orgId) { boolean createSuccessful = false; Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { Organization d = (Organization) session.get(Organization.class, orgId); CharacteristicType cType = (CharacteristicType) session.get(CharacteristicType.class, charId); LinkOrganizationCharacteristicType link = new LinkOrganizationCharacteristicType(); int max = 0; try { max = (Integer) session.createQuery( "select max(displayIndex) from LinkOrganizationCharacteristicType l where l.organization.id = :orgId") .setParameter("orgId", d.getId()).uniqueResult(); } catch (Exception e) { logger.error("unable to determine max!", e); } link.setDisplayIndex(max + 1); link.setCharacteristicType(cType); link.setOrganization(d); //p.getLinkProgramCharacteristicTypes().add(link); session.persist(link); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } createSuccessful = true; return createSuccessful; } public boolean moveCharacteristicType(int id, int charTypeId, String direction) { //when moving up, find the one to be moved (while keeping track of the previous one) and swap display_index values //when moving down, find the one to be moved, swap displayIndex values of it and the next one //when deleting, reduce all links following one to be deleted by 1 boolean done = false; Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { @SuppressWarnings("unchecked") List<LinkOrganizationCharacteristicType> existing = (List<LinkOrganizationCharacteristicType>) session .createQuery( "select l from LinkProgramCharacteristicType l where l.program.id = :programId order by l.displayIndex") .setParameter("programId", id).list(); if (direction.equals("up")) { LinkOrganizationCharacteristicType prev = null; for (LinkOrganizationCharacteristicType link : existing) { if (link.getCharacteristicType().getId() == charTypeId && prev != null) { int swap = prev.getDisplayIndex(); prev.setDisplayIndex(link.getDisplayIndex()); link.setDisplayIndex(swap); session.merge(prev); session.merge(prev); done = true; break; } prev = link; } } else if (direction.equals("down")) { LinkOrganizationCharacteristicType prev = null; for (LinkOrganizationCharacteristicType link : existing) { if (prev != null) { int swap = prev.getDisplayIndex(); prev.setDisplayIndex(link.getDisplayIndex()); link.setDisplayIndex(swap); session.merge(prev); session.merge(link); done = true; break; } if (link.getCharacteristicType().getId() == charTypeId) { prev = link; } } } else if (direction.equals("delete")) { LinkOrganizationCharacteristicType toDelete = null; for (LinkOrganizationCharacteristicType link : existing) { if (toDelete != null) { link.setDisplayIndex(link.getDisplayIndex() - 1); session.merge(link); } if (link.getCharacteristicType().getId() == charTypeId) { toDelete = link; } } if (toDelete != null) { session.delete(toDelete); done = true; } } session.getTransaction().commit(); return done; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } @SuppressWarnings("unchecked") public List<CharacteristicType> setOrganizationCharacteristicTypes(Organization o) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CharacteristicType> toReturn = null; try { toReturn = (List<CharacteristicType>) session.createQuery( "select ct from CharacteristicType ct, LinkOrganizationCharacteristicType lpct where lpct.organization.id=:orgId and lpct.characteristicType = ct order by lpct.displayIndex") .setParameter("orgId", o.getId()).list(); o.setCharacteristicTypes(toReturn); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<Characteristic> getProgramCharacteristics(Program p, int level) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<Characteristic> toReturn = null; try { toReturn = (List<Characteristic>) session.createQuery( "select c from Characteristic c, CharacteristicType ct, LinkProgramCharacteristicType lpct where lpct.program.id=:programId and lpct.displayIndex=:index and lpct.characteristicType = ct and c.characteristicType = ct order by c.displayIndex") .setParameter("programId", p.getId()).setParameter("index", level).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<Characteristic> getCharacteristicsForType(CharacteristicType ct) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<Characteristic> toReturn = null; try { toReturn = (List<Characteristic>) session.createQuery( "select c from Characteristic c where c.characteristicType.id = :ctId order by c.displayIndex") .setParameter("ctId", ct.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<Program> getProgramOrderedByName() { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<Program> list = null; try { list = (List<Program>) session.createQuery("from Program order by name").list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return list; } @SuppressWarnings("unchecked") public List<LinkProgramProgramOutcome> getLinkProgramOutcomeForProgram(Program p) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkProgramProgramOutcome> toReturn = null; try { toReturn = (List<LinkProgramProgramOutcome>) session.createQuery( "from LinkProgramProgramOutcome l where l.program.id = :programId order by l.programOutcome.group.name, l.programOutcome.name") .setParameter("programId", p.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<ProgramOutcomeGroup> getProgramOutcomeGroupsProgram(Program p) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<ProgramOutcomeGroup> toReturn = null; try { toReturn = (List<ProgramOutcomeGroup>) session.createQuery( "SELECT distinct l.programOutcome.group from LinkProgramProgramOutcome l where l.program.id = :programId order by l.programOutcome.group.name") .setParameter("programId", p.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<ProgramOutcomeGroup> getProgramOutcomeGroupsOrganization(Organization o) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<ProgramOutcomeGroup> toReturn = null; try { toReturn = (List<ProgramOutcomeGroup>) session.createQuery( "SELECT distinct l.programOutcome.group from LinkProgramProgramOutcome l where l.program.organization.id = :orgId OR (l.program.organization.parentOrganization IS NOT NULL AND l.program.organization.parentOrganization= :orgId) order by l.programOutcome.group.name") .setParameter("orgId", o.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<ProgramOutcome> getProgramOutcomesForGroup(ProgramOutcomeGroup group) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<ProgramOutcome> toReturn = null; try { toReturn = (List<ProgramOutcome>) session .createQuery("from ProgramOutcome l where l.group.id=:groupId order by l.name") .setParameter("groupId", group.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkProgramProgramOutcome> getProgramOutcomeForGroup(ProgramOutcomeGroup group) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkProgramProgramOutcome> toReturn = null; try { toReturn = (List<LinkProgramProgramOutcome>) session.createQuery( "from LinkProgramProgramOutcome l where l.programOutcome.group.id = :groupId order by l.programOutcome.name") .setParameter("groupId", group.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkProgramProgramOutcome> getProgramOutcomeForGroupAndProgram(Program p, ProgramOutcomeGroup group) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkProgramProgramOutcome> toReturn = null; try { toReturn = (List<LinkProgramProgramOutcome>) session.createQuery( "from LinkProgramProgramOutcome l where l.program.id = :programId AND l.programOutcome.group.id = :groupId order by l.programOutcome.name") .setParameter("programId", p.getId()).setParameter("groupId", group.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkProgramProgramOutcome> getProgramOutcomesForProgram(Program p) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkProgramProgramOutcome> toReturn = null; try { toReturn = (List<LinkProgramProgramOutcome>) session.createQuery( "from LinkProgramProgramOutcome l where l.program.id = :programId order by l.programOutcome.group.name, l.programOutcome.name") .setParameter("programId", p.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseProgram> getLinkCourseProgramForProgram(Program p) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkCourseProgram> toReturn = null; try { toReturn = (List<LinkCourseProgram>) session.createQuery( "from LinkCourseProgram l where l.program.id = :programId order by l.time.displayIndex, l.course.subject, l.course.courseNumber") .setParameter("programId", p.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public TreeMap<String, Integer> getCourseOfferingsContributingToProgram(Program program) { return getCourseOfferingsContributingToProgram(program, new ArrayList<String>(0)); } @SuppressWarnings("unchecked") public TreeMap<String, Integer> getCourseOfferingsContributingToProgram(Program program, List<String> termList) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); TreeMap<String, Integer> toReturn = new TreeMap<String, Integer>(); List<CourseOffering> tempList = null; try { String query = "SELECT DISTINCT l.courseOffering FROM LinkCourseOfferingContributionProgramOutcome l where " + HibernateUtil.getListAsString("l.courseOffering.term", termList, true) + " l.linkProgramOutcome.program.id = :programId AND (l.contribution.calculationValue + l.mastery.calculationValue) > 0"; logger.debug(query); tempList = (List<CourseOffering>) session.createQuery(query).setParameter("programId", program.getId()) .list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); tempList = new ArrayList<CourseOffering>(); } for (CourseOffering offering : tempList) { if (!termList.contains(offering.getTerm())) { //don't want the course offering if it isn't in the term-list continue; } String course = offering.getCourse().getSubject() + " " + offering.getCourse().getCourseNumber(); Integer count = toReturn.get(course); if (count == null) count = 1; else count++; toReturn.put(course, new Integer(count)); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseOutcomeProgramOutcome> getCourseOutcomeLinksForProgramOutcome(CourseOffering offering, ProgramOutcome po) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkCourseOutcomeProgramOutcome> toReturn = null; logger.debug("OfferingId=" + offering.getId() + " programOutcomeId = " + po.getId()); try { toReturn = (List<LinkCourseOutcomeProgramOutcome>) session.createQuery( "from LinkCourseOutcomeProgramOutcome l where l.programOutcome.id = :programOutcomeId AND l.courseOffering.id=:courseOfferingId order by l.courseOutcome.name") .setParameter("programOutcomeId", po.getId()).setParameter("courseOfferingId", offering.getId()) .list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseOutcomeProgramOutcome> getCourseOutcomeLinks(CourseOffering offering, Session session) { return (List<LinkCourseOutcomeProgramOutcome>) session .createQuery("from LinkCourseOutcomeProgramOutcome l where l.courseOffering.id=:courseOfferingId") .setParameter("courseOfferingId", offering.getId()).list(); } @SuppressWarnings("unchecked") public List<LinkCourseOutcomeProgramOutcome> getProgramOutcomeLinksForCourseOutcome(int offeringId, int courseOutcomeId, Session session) { List<LinkCourseOutcomeProgramOutcome> toReturn = null; logger.debug("OfferingId=" + offeringId + " courseOutcomeId = " + courseOutcomeId); toReturn = (List<LinkCourseOutcomeProgramOutcome>) session.createQuery( "from LinkCourseOutcomeProgramOutcome l where l.courseOutcome.id = :courseOutcomeId AND l.courseOffering.id=:courseOfferingId order by l.courseOutcome.name") .setParameter("courseOutcomeId", courseOutcomeId).setParameter("courseOfferingId", offeringId) .list(); return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseOutcomeProgramOutcome> getCourseOutcomeLinksForProgramOutcome(CourseOffering offering, int programOutcomeId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkCourseOutcomeProgramOutcome> toReturn = null; logger.debug("OfferingId=" + offering.getId() + " programOutcomeId = " + programOutcomeId); try { toReturn = (List<LinkCourseOutcomeProgramOutcome>) session.createQuery( "select distinct l from LinkCourseOutcomeProgramOutcome l , LinkCourseOfferingOutcome l2 where l.programOutcome.id = :programOutcomeId AND l.courseOffering.id=:courseOfferingId AND l2.courseOffering = l.courseOffering AND (l.courseOutcome = l2.courseOutcome OR l.courseOutcome.name=:noMatchName) order by COALESCE(l2.displayIndex,'aaaaaaa')") .setParameter("programOutcomeId", programOutcomeId) .setParameter("noMatchName", OutcomeManager.noMatchName) .setParameter("courseOfferingId", offering.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public LinkCourseContributionProgramOutcome getCourseContributionLinksForProgramOutcome(Course course, LinkProgramProgramOutcome po) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); LinkCourseContributionProgramOutcome toReturn = null; try { toReturn = (LinkCourseContributionProgramOutcome) session.createQuery( "from LinkCourseContributionProgramOutcome l where l.linkProgramOutcome.id = :linkProgramOutcomeId AND l.course.id=:courseId") .setParameter("linkProgramOutcomeId", po.getId()).setParameter("courseId", course.getId()) .uniqueResult(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseOutcomeProgramOutcome> getCourseOutcomeLinksForProgramOutcome(List<String> courseIds, ProgramOutcome po) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkCourseOutcomeProgramOutcome> toReturn = null; try { toReturn = (List<LinkCourseOutcomeProgramOutcome>) session .createQuery("from LinkCourseOutcomeProgramOutcome l where " + HibernateUtil.getListAsString(" l.courseOffering.course.id ", courseIds, true, false) + " l.programOutcome.id = :programOutcomeId order by l.courseOutcome.name") .setParameter("programOutcomeId", po.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseOfferingContributionProgramOutcome> getCourseOfferingContributionLinksForProgramOutcome( List<String> courseIds, LinkProgramProgramOutcome po) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkCourseOfferingContributionProgramOutcome> toReturn = null; try { toReturn = (List<LinkCourseOfferingContributionProgramOutcome>) session .createQuery("FROM LinkCourseOfferingContributionProgramOutcome l WHERE " + HibernateUtil.getListAsString(" l.courseOffering.course.id ", courseIds, false, false) + " AND l.linkProgramOutcome.id = :linkProgramOutcomeId ORDER BY l.courseOffering.course.subject, l.courseOffering.course.courseNumber, l.courseOffering.term, l.courseOffering.sectionNumber,l.id") .setParameter("linkProgramOutcomeId", po.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseContributionProgramOutcome> getCourseContributionLinksForProgramOutcome( List<String> courseIds, LinkProgramProgramOutcome po) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<LinkCourseContributionProgramOutcome> toReturn = null; try { toReturn = (List<LinkCourseContributionProgramOutcome>) session .createQuery("FROM LinkCourseContributionProgramOutcome l WHERE " + HibernateUtil.getListAsString(" l.course.id ", courseIds, false, false) + " AND l.linkProgramOutcome.id = :linkProgramOutcomeId ORDER BY l.course.subject, l.course.courseNumber, l.id") .setParameter("linkProgramOutcomeId", po.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public LinkCourseOfferingContributionProgramOutcome getCourseOfferingContributionLinksForProgramOutcome( CourseOffering courseOffering, LinkProgramProgramOutcome po) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); LinkCourseOfferingContributionProgramOutcome toReturn = null; try { toReturn = (LinkCourseOfferingContributionProgramOutcome) session.createQuery( "from LinkCourseOfferingContributionProgramOutcome l where l.linkProgramOutcome.id = :linkProgramOutcomeId AND l.courseOffering.id=:courseOfferingId") .setParameter("linkProgramOutcomeId", po.getId()) .setParameter("courseOfferingId", courseOffering.getId()).uniqueResult(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<LinkCourseOfferingContributionProgramOutcome> getCourseOfferingContributionLinks( CourseOffering courseOffering, Session session) { return (List<LinkCourseOfferingContributionProgramOutcome>) session.createQuery( "from LinkCourseOfferingContributionProgramOutcome l where l.courseOffering.id=:courseOfferingId") .setParameter("courseOfferingId", courseOffering.getId()).list(); } public boolean saveCourseOfferingContributionLinksForProgramOutcome(int courseOfferingId, int linkProgramOutcomeId, int contributionId, int masteryId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { MasteryOptionValue mastery = (MasteryOptionValue) session.get(MasteryOptionValue.class, masteryId); ContributionOptionValue contribution = (ContributionOptionValue) session .get(ContributionOptionValue.class, contributionId); CourseOffering courseOffering = (CourseOffering) session.get(CourseOffering.class, courseOfferingId); LinkProgramProgramOutcome lpo = (LinkProgramProgramOutcome) session.get(LinkProgramProgramOutcome.class, linkProgramOutcomeId); LinkCourseOfferingContributionProgramOutcome o = getCourseOfferingContributionLinksForProgramOutcome( courseOffering, lpo, session); if (o == null) { o = new LinkCourseOfferingContributionProgramOutcome(); o.setCourseOffering(courseOffering); o.setLinkProgramOutcome(lpo); o.setContribution(contribution); o.setMastery(mastery); session.save(o); } else { o.setContribution(contribution); o.setMastery(mastery); session.merge(o); } session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public boolean saveCourseContributionLinksForProgramOutcome(int courseId, int linkProgramOutcomeId, int contributionId, int masteryId) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); try { ContributionOptionValue contribution = (ContributionOptionValue) session .get(ContributionOptionValue.class, contributionId); MasteryOptionValue mastery = (MasteryOptionValue) session.get(MasteryOptionValue.class, masteryId); Course course = (Course) session.get(Course.class, courseId); LinkProgramProgramOutcome lpo = (LinkProgramProgramOutcome) session.get(LinkProgramProgramOutcome.class, linkProgramOutcomeId); LinkCourseContributionProgramOutcome o = getCourseContributionLinksForProgramOutcome(course, lpo, session); if (o == null) { o = new LinkCourseContributionProgramOutcome(); o.setCourse(course); o.setLinkProgramOutcome(lpo); o.setContribution(contribution); o.setMastery(mastery); session.save(o); } else { o.setContribution(contribution); o.setMastery(mastery); session.merge(o); } session.getTransaction().commit(); return true; } catch (Exception e) { HibernateUtil.logException(logger, e); try { session.getTransaction().rollback(); } catch (Exception e2) { logger.error("Unable to roll back!", e2); } return false; } } public LinkCourseContributionProgramOutcome getCourseContributionLinksForProgramOutcome(Course course, LinkProgramProgramOutcome po, Session session) { LinkCourseContributionProgramOutcome toReturn = null; toReturn = (LinkCourseContributionProgramOutcome) session.createQuery( "from LinkCourseContributionProgramOutcome l where l.linkProgramOutcome.id = :linkProgramOutcomeId AND l.course.id=:courseId") .setParameter("linkProgramOutcomeId", po.getId()).setParameter("courseId", course.getId()) .uniqueResult(); return toReturn; } public LinkCourseOfferingContributionProgramOutcome getCourseOfferingContributionLinksForProgramOutcome( CourseOffering courseOffering, LinkProgramProgramOutcome po, Session session) { LinkCourseOfferingContributionProgramOutcome toReturn = null; toReturn = (LinkCourseOfferingContributionProgramOutcome) session.createQuery( "from LinkCourseOfferingContributionProgramOutcome l where l.linkProgramOutcome.id = :linkProgramOutcomeId AND l.courseOffering.id=:courseOfferingId") .setParameter("linkProgramOutcomeId", po.getId()) .setParameter("courseOfferingId", courseOffering.getId()).uniqueResult(); return toReturn; } @SuppressWarnings("unchecked") public List<ProgramOutcomeGroup> getProgramOutcomeGroupsForProgram(Program p) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<ProgramOutcomeGroup> toReturn = null; try { toReturn = (List<ProgramOutcomeGroup>) session.createQuery( "from ProgramOutcomeGroup l where (l.programSpecific='Y' AND l.programId=:programId) OR l.programSpecific='N' ORDER BY l.programSpecific DESC,l.name") .setParameter("programId", p.getId()).list(); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public List<CourseOfferingContribution> getContributionForProgramOutcome(ProgramOutcome programOutcome, Program program) { return getContributionForProgramOutcome(programOutcome, program, new ArrayList<String>(0)); } @SuppressWarnings("unchecked") public List<CourseOfferingContribution> getContributionForProgramOutcome(ProgramOutcome programOutcome, Program program, List<String> terms) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CourseOfferingContribution> toReturn = new ArrayList<CourseOfferingContribution>(); try { StringBuffer sql = new StringBuffer(); //sql.append(" SELECT co.course_id AS {p.courseId}, lppo.program_outcome_id AS {p.programOutcomeId}, sum(cov.calculation_value) AS {p.contributionSum},count( distinct course_offering_id) AS {p.contributionCount} "); // sql.append(" SELECT co.course_id, lppo.program_outcome_id, sum(cov.calculation_value),count( distinct course_offering_id)"); sql.append( " SELECT co.id AS courseOfferingId, cov.calculation_value AS contributionObject, mov.calculation_value as masteryObject"); sql.append(" FROM course_offering co"); sql.append(" ,link_course_program lcp"); sql.append(" ,link_program_program_outcome lppo"); sql.append(" ,link_course_offering_contribution_program_outcome lcopo"); sql.append(" ,contribution_option_value cov "); sql.append(" ,mastery_option_value mov "); sql.append(" WHERE "); sql.append(HibernateUtil.getListAsString("co.term", terms, true)); sql.append(" co.course_id = lcp.course_id"); sql.append(" AND co.id = lcopo.course_offering_id"); sql.append(" AND lcopo.link_program_program_outcome_id = lppo.id"); sql.append(" AND lppo.program_id = lcp.program_id"); sql.append(" AND lcopo.contribution_option_id = cov.id"); sql.append(" AND lcopo.mastery_option_id = mov.id"); sql.append(" AND lcp.program_id = :programId"); sql.append(" AND lppo.program_outcome_id = :programOutcomeId"); sql.append(" AND (cov.calculation_value + mov.calculation_value) > 0 "); logger.debug("getContributionForProgramOutcome Core contributions:" + programOutcome.getId() + " " + program.getId() + " " + sql.toString()); List<CourseOfferingContribution> fromOfferings = (List<CourseOfferingContribution>) session .createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(CourseOfferingContribution.class)) .setParameter("programId", program.getId()) .setParameter("programOutcomeId", programOutcome.getId()).list(); toReturn.addAll(fromOfferings); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<CourseOfferingContribution> getServiceCourseContributionForProgramOutomce( ProgramOutcome programOutcome, Program program) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CourseOfferingContribution> toReturn = new ArrayList<CourseOfferingContribution>(); try { StringBuffer sql = new StringBuffer(); sql.append( " SELECT lccpo.course_id AS courseOfferingId, cov.calculation_value AS contributionObject, mov.calculation_value AS masteryObject "); sql.append(" from link_course_contribution_program_outcome lccpo"); sql.append(" , link_program_program_outcome lppo "); sql.append(" , contribution_option_value cov "); sql.append(" , mastery_option_value mov "); sql.append(" WHERE lccpo.link_program_program_outcome_id = lppo.id "); sql.append(" AND lccpo.contribution_option_id = cov.id"); sql.append(" AND lccpo.mastery_option_id = mov.id"); sql.append(" AND lppo.program_id=:programId"); sql.append(" AND lppo.program_outcome_id = :programOutcomeId"); sql.append(" AND (cov.calculation_value + mov.calculation_value) > 0 "); logger.debug("getServiceCourseContributionForProgramOutomce " + sql.toString()); List<CourseOfferingContribution> fromCourses = (List<CourseOfferingContribution>) session .createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(CourseOfferingContribution.class)) .setParameter("programId", program.getId()) .setParameter("programOutcomeId", programOutcome.getId()).list(); toReturn.addAll(fromCourses); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<ProgramOutcomeCourseContribution> getProgramOutcomeCoreCourseContributionForProgram( Program program) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<ProgramOutcomeCourseContribution> toReturn = new ArrayList<ProgramOutcomeCourseContribution>(); try { StringBuffer sql = new StringBuffer(); //sql.append(" SELECT co.course_id AS {p.courseId}, lppo.program_outcome_id AS {p.programOutcomeId}, sum(cov.calculation_value) AS {p.contributionSum},count( distinct course_offering_id) AS {p.contributionCount} "); // sql.append(" SELECT co.course_id, lppo.program_outcome_id, sum(cov.calculation_value),count( distinct course_offering_id)"); sql.append( " SELECT co.course_id AS courseId, lppo.program_outcome_id AS programOutcomeId, sum(cov.calculation_value) AS contributionSumObject, sum(mov.calculation_value) AS masterySumObject "); sql.append(" FROM course_offering co"); sql.append(" ,link_course_program lcp"); sql.append(" ,link_program_program_outcome lppo"); sql.append(" ,link_course_offering_contribution_program_outcome lcopo"); sql.append(" ,contribution_option_value cov "); sql.append(" ,mastery_option_value mov "); sql.append(" WHERE co.course_id = lcp.course_id"); sql.append(" AND co.id = lcopo.course_offering_id"); sql.append(" AND lcopo.link_program_outcome_id = lppo.id"); sql.append(" AND lppo.program_id = lcp.program_id"); sql.append(" AND lcopo.contribution_option_id = cov.id"); sql.append(" AND lcopo.mastery_option_id = mov.id"); sql.append(" AND lcp.program_id = :programId"); sql.append(" GROUP BY co.course_id, lppo.program_outcome_id"); logger.debug("getProgramOutcomeCoreCourseContributionForProgram Core contributions:" + sql.toString()); List<ProgramOutcomeCourseContribution> fromOfferings = (List<ProgramOutcomeCourseContribution>) session .createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(ProgramOutcomeCourseContribution.class)) .setParameter("programId", program.getId()).list(); toReturn.addAll(fromOfferings); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } @SuppressWarnings("unchecked") public List<ProgramOutcomeCourseContribution> getProgramOutcomeServiceCourseContributionForProgram( Program program) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<ProgramOutcomeCourseContribution> toReturn = new ArrayList<ProgramOutcomeCourseContribution>(); try { StringBuffer sql = new StringBuffer(); sql.append( " SELECT lccpo.course_id AS courseId, lppo.program_outcome_id AS programOutcomeId, cov.calculation_value AS contributionSum, mov.calculation_value AS masterySum "); sql.append(" from link_course_contribution_program_outcome lccpo"); sql.append(" , link_program_program_outcome lppo "); sql.append(" , contribution_option_value cov "); sql.append(" , mastery_option_value mov "); sql.append(" WHERE lccpo.link_program_program_outcome_id = lppo.id "); sql.append(" AND lccpo.contribution_option_id = cov.id"); sql.append(" AND lccpo.mastery_option_id = mov.id"); sql.append(" AND lppo.program_id=:programId"); logger.debug("getProgramOutcomeServiceCourseContributionForProgram :" + sql.toString()); List<ProgramOutcomeCourseContribution> fromCourses = (List<ProgramOutcomeCourseContribution>) session .createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(ProgramOutcomeCourseContribution.class)) .setParameter("programId", program.getId()).list(); toReturn.addAll(fromCourses); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public List<CourseAssessmentOption> getProgramCourseAssessmentOptions(Program program) { return getProgramCourseAssessmentOptions(program, new ArrayList<String>(0)); } @SuppressWarnings("unchecked") public List<CourseAssessmentOption> getProgramCourseAssessmentOptions(Program program, List<String> terms) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CourseAssessmentOption> toReturn = new ArrayList<CourseAssessmentOption>(); try { StringBuffer sql = new StringBuffer(); sql.append( " select sum(lcoa.weight) as weight, ato.display_index as optionId, co.course_id as courseId "); sql.append(" from course_offering co "); sql.append(" ,link_course_offering_assessment lcoa "); sql.append(" ,link_course_program lcp "); sql.append(" ,assessment_time_option ato "); sql.append(" WHERE "); sql.append(HibernateUtil.getListAsString("co.term", terms, true)); sql.append(" lcp.program_id = :programId "); sql.append(" AND lcp.course_id = co.course_id "); sql.append(" AND co.id = lcoa.course_offering_id "); sql.append(" AND ato.id = lcoa.assessment_time_option_id "); sql.append("group by co.course_id, ato.display_index "); sql.append("order by co.course_id, ato.display_index "); //one record for each course that is part of the program. Each record contains courseid, sum of weights for each time-option and the number of course_offerings for each. List<CourseAssessmentOption> fromOfferings = (List<CourseAssessmentOption>) session .createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(CourseAssessmentOption.class)) .setParameter("programId", program.getId()).list(); toReturn.addAll(fromOfferings); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public HashMap<String, ArrayList<Double>> organizeAssessmentData(List<CourseAssessmentOption> dataList, int maxOptions, Map<String, Integer> offeringCounts) { //key = course-id //data is average for each course, for each assessment option id. Average is calculated by taking the sum / maximum of course Offerings for that course (the number of offerings that have assessment data) //value in location 0 is the # of course offerings for the course HashMap<String, ArrayList<Double>> r = new HashMap<String, ArrayList<Double>>(); for (CourseAssessmentOption option : dataList) { logger.debug(option.getCourseId() + " " + option.getOptionId() + " " + option.getWeight()); //retrieve set of values from HashMap, if not already in there, add it ArrayList<Double> data = r.get("" + option.getCourseId()); if (data == null) { data = new ArrayList<Double>(maxOptions + 1); for (int i = 0; i <= maxOptions; i++) data.add(new Double(0.0)); } //set the value in the corresponding location //by retrieving the value that already exists and adding the weight to it //and putting it back in the array at the saem location Double value = data.get(option.getOptionId()); value += option.getWeight(); data.set(option.getOptionId(), value); r.put("" + option.getCourseId(), data); } //calculate the averages by dividing the sum by the number of offerings. for (String key : r.keySet()) { ArrayList<Double> data = r.get(key); int offerings = offeringCounts.get(key) != null ? offeringCounts.get(key) : 1; for (int i = 1; i < data.size(); i++) { data.set(i, data.get(i) / offerings); } } return r; } public List<CourseTeachingMethodOption> getProgramCourseTeachingMethodOptions(Program program) { return getProgramCourseTeachingMethodOptions(program, new ArrayList<String>(0)); } @SuppressWarnings("unchecked") public List<CourseTeachingMethodOption> getProgramCourseTeachingMethodOptions(Program program, List<String> terms) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CourseTeachingMethodOption> toReturn = new ArrayList<CourseTeachingMethodOption>(); try { StringBuffer sql = new StringBuffer(); sql.append( " select sum(tmpo.comparative_value) as weight, tm.display_index as teachingMethodIndex, count(distinct lctm.course_offering_id) as offeringCount, co.course_id as courseId "); sql.append(" from course_offering co "); sql.append(" ,link_course_offering_teaching_method lctm "); sql.append(" ,link_course_program lcp "); sql.append(" ,teaching_method tm "); sql.append(" ,teaching_method_portion_option tmpo "); sql.append(" WHERE "); sql.append(HibernateUtil.getListAsString("co.term", terms, true)); sql.append(" lcp.program_id = :programId "); sql.append(" AND lcp.course_id = co.course_id "); sql.append(" AND co.id = lctm.course_offering_id "); sql.append(" AND lctm.teaching_method_id = tm.id "); sql.append(" AND lctm.teaching_method_portion_option_id = tmpo.id "); sql.append("group by co.course_id, tm.display_index "); sql.append("order by co.course_id, tm.display_index "); //one record for each course that is part of the program. Each record contains courseid, sum of weights for each time-option and the number of course_offerings for each. List<CourseTeachingMethodOption> fromOfferings = (List<CourseTeachingMethodOption>) session .createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(CourseTeachingMethodOption.class)) .setParameter("programId", program.getId()).list(); toReturn.addAll(fromOfferings); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public HashMap<String, ArrayList<Double>> organizeTeachingMethodData(List<CourseTeachingMethodOption> dataList, int maxOptions, Map<String, Integer> offeringCounts) { //key = course-id //data is average for each course, for each assessment option id. Average is calculated by taking the sum / maximum of course Offerings for that course (the number of offerings that have assessment data) //value in location 0 is the # of course offerings for the course HashMap<String, ArrayList<Double>> r = new HashMap<String, ArrayList<Double>>(); for (CourseTeachingMethodOption option : dataList) { logger.debug(option.getCourseId() + " " + option.getOfferingCount() + " " + option.getTeachingMethodIndexId() + " " + option.getWeight()); //retrieve set of values from HashMap, if not already in there, add it ArrayList<Double> data = r.get("" + option.getCourseId()); if (data == null) { data = new ArrayList<Double>(maxOptions + 1); for (int i = 0; i <= maxOptions; i++) data.add(new Double(0.0)); } //set the value in the corresponding location //by retrieving the value that already exists and adding the weight to it //and putting it back in the array at the saem location Double value = data.get(option.getTeachingMethodIndexId()); value += option.getWeight(); data.set(option.getTeachingMethodIndexId(), value); //put the value back in the hashmap r.put("" + option.getCourseId(), data); } //calculate the averages by dividing the sum by the number of offerings. for (String key : r.keySet()) { ArrayList<Double> data = r.get(key); int offerings = offeringCounts.get(key) != null ? offeringCounts.get(key) : 1; for (int i = 1; i < data.size(); i++) { data.set(i, data.get(i) / offerings); } } return r; } public List<CourseAssessmentOption> getProgramAssessmentGroups(Program program) { return getProgramAssessmentGroups(program, new ArrayList<String>(0)); } @SuppressWarnings("unchecked") public List<CourseAssessmentOption> getProgramAssessmentGroups(Program program, List<String> terms) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); List<CourseAssessmentOption> toReturn = new ArrayList<CourseAssessmentOption>(); try { StringBuffer sql = new StringBuffer(); sql.append( " select co.course_id as courseId,gr.display_index as optionId, sum(lcoa.weight) as weight "); sql.append(" from assessment_group gr "); sql.append(" , assessment a "); sql.append(" , link_course_offering_assessment lcoa "); sql.append(" , course_offering co "); sql.append(" , link_course_program lcp "); sql.append("where "); sql.append(HibernateUtil.getListAsString("co.term", terms, true)); sql.append("lcp.program_id=:programId "); sql.append(" and lcp.course_id = co.course_id "); sql.append(" and co.id = lcoa.course_offering_id "); sql.append(" and lcoa.assessment_id = a.id "); sql.append(" and a.assessment_group_id = gr.id "); sql.append(" group by co.course_id,gr.display_index "); //one record for each course that is part of the program. Each record contains courseid, sum of weights for each time-option and the number of course_offerings for each. List<CourseAssessmentOption> fromOfferings = (List<CourseAssessmentOption>) session .createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(CourseAssessmentOption.class)) .setParameter("programId", program.getId()).list(); toReturn.addAll(fromOfferings); session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public HashMap<String, ArrayList<Double>> organizeAssessmentGroupData(List<CourseAssessmentOption> dataList, int maxOptions, Map<String, Integer> offeringCounts) { //key = course-id //data is average for each course, for each assessment option id. Average is calculated by taking the sum / maximum of course Offerings for that course (the number of offerings that have assessment data) //value in location 0 is the # of course offerings for the course HashMap<String, ArrayList<Double>> r = new HashMap<String, ArrayList<Double>>(); for (CourseAssessmentOption option : dataList) { logger.debug(option.getCourseId() + " " + option.getOptionId() + " " + option.getWeight()); //retrieve set of values from HashMap, if not already in there, add it ArrayList<Double> data = r.get("" + option.getCourseId()); if (data == null) { data = new ArrayList<Double>(maxOptions + 1); for (int i = 0; i <= maxOptions; i++) data.add(new Double(0.0)); } //set the value in the corresponding location //by retrieving the value that already exists and adding the weight to it //and putting it back in the array at the saem location Double value = data.get(option.getOptionId()); value += option.getWeight(); data.set(option.getOptionId(), value); //put the value back in the hashmap r.put("" + option.getCourseId(), data); } //calculate the averages by dividing the sum by the number of offerings. for (String key : r.keySet()) { ArrayList<Double> data = r.get(key); int offerings = offeringCounts.get(key) != null ? offeringCounts.get(key) : 1; for (int i = 1; i < data.size(); i++) { data.set(i, data.get(i) / offerings); } } return r; } public Map<String, Integer> getCourseOfferingCounts(Program p) { return getCourseOfferingCounts(p, new ArrayList<String>(0)); } public Map<String, Integer> getCourseOfferingCounts(Program p, List<String> terms) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); Map<String, Integer> toReturn = new TreeMap<String, Integer>(); try { StringBuffer sql = new StringBuffer(); sql.append(" select co.course_id as a, count( distinct co.id) as b"); sql.append(" from course_offering co "); sql.append(" , link_course_program lcp "); sql.append("where "); sql.append(HibernateUtil.getListAsString("co.term", terms, true)); sql.append(" lcp.program_id = :programId "); sql.append(" and lcp.course_id = co.course_id "); sql.append(" and co.id in (select course_offering_id from link_course_offering_assessment) "); sql.append(" group by co.course_id; "); @SuppressWarnings("unchecked") List<Pair> counts = (List<Pair>) session.createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(Pair.class)).setParameter("programId", p.getId()) .list(); for (Pair pair : counts) { toReturn.put("" + pair.getA(), pair.getB()); } session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public ProgramManager() { } public Map<String, Integer> getProgramOutcomeContributions(Program p, List<String> terms) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); Map<String, Integer> toReturn = new TreeMap<String, Integer>(); try { StringBuffer sql = new StringBuffer(); sql.append(" SELECT a.outcome,a.course,sum(a.calc)/count(section) as value FROM "); sql.append(" ("); sql.append( " select cov.calculation_value as calc, po.id as outcome, co.course_id as course, co.id as section"); sql.append(" from link_course_offering_contribution_program_outcome lcocpo"); sql.append(" , link_program_program_outcome lppo"); sql.append(" , program_outcome po"); sql.append(" , contribution_option_value cov"); sql.append(" , course_offering co"); sql.append(" WHERE lppo.id = lcocpo.link_program_program_outcome_id"); sql.append(" AND po.id=lppo.program_outcome_id"); sql.append(" AND cov.id = lcocpo.contribution_option_id "); sql.append(" AND co.id = lcocpo.course_offering_id"); sql.append(" AND lppo.program_id="); sql.append(p.getId()); sql.append(" UNION "); sql.append( " select cov.calculation_value as calc, po.id as outcome, lccpo.course_id as course, -1 as section"); sql.append(" from link_course_contribution_program_outcome lccpo"); sql.append(" , link_program_program_outcome lppo"); sql.append(" , program_outcome po"); sql.append(" , contribution_option_value cov"); sql.append(" WHERE lppo.id = lccpo.link_program_program_outcome_id"); sql.append(" AND po.id=lppo.program_outcome_id"); sql.append(" AND cov.id = lccpo.contribution_option_id "); sql.append(" AND lppo.program_id="); sql.append(p.getId()); sql.append(" ) a"); sql.append(" group by a.outcome,a.course"); sql.append(" order by a.outcome,a.course"); @SuppressWarnings("unchecked") List<Pair> counts = (List<Pair>) session.createSQLQuery(sql.toString()) .setResultTransformer(Transformers.aliasToBean(Pair.class)).setParameter("programId", p.getId()) .list(); for (Pair pair : counts) { toReturn.put("" + pair.getA(), pair.getB()); } session.getTransaction().commit(); } catch (Exception e) { HibernateUtil.logException(logger, e); } return toReturn; } public static ProgramManager instance() { if (instance == null) { instance = new ProgramManager(); } return instance; } public String inString(List<Integer> in) { StringBuilder sb = new StringBuilder(); for (int i : in) { sb.append(","); sb.append(i); } return sb.substring(1); } }