Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190531.sql =================================================================== diff -u --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190531.sql (revision 0) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190531.sql (revision e08a9e538cf4c89f50fd15cdabfa0319076c2692) @@ -0,0 +1,43 @@ +-- Turn off autocommit, so nothing is committed if there is an error +SET AUTOCOMMIT = 0; +SET FOREIGN_KEY_CHECKS=0; +----------------------Put all sql statements below here------------------------- + +-- LDEV-4828 Add tables for QB collections +CREATE TABLE lams_qb_collection (`uid` BIGINT AUTO_INCREMENT, + `name` VARCHAR(255), + `user_id` BIGINT, + `personal` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (uid), + INDEX (personal), + CONSTRAINT FK_lams_qb_collection_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE + ); + +CREATE TABLE lams_qb_collection_question (`collection_uid` BIGINT NOT NULL, + `qb_question_uid` BIGINT NOT NULL, + CONSTRAINT FK_lams_qb_collection_question_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_collection_question_2 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) + ON DELETE CASCADE ON UPDATE CASCADE + ); + + +CREATE TABLE lams_qb_collection_organisation (`collection_uid` BIGINT NOT NULL, + `organisation_id` BIGINT NOT NULL, + CONSTRAINT FK_lams_qb_collection_share_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_collection_share_2 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) + ON DELETE CASCADE ON UPDATE CASCADE + ); + +INSERT INTO lams_qb_collection VALUES (1, 'Public questions', NULL, false); + +INSERT INTO lams_qb_collection_question + SELECT 1, uid FROM lams_qb_question; + +----------------------Put all sql statements above here------------------------- + +-- If there were no errors, commit and restore autocommit to on +COMMIT; +SET AUTOCOMMIT = 1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java =================================================================== diff -u -rffd9d97b9e967e027f30a62f888a7f5670b35ba0 -re08a9e538cf4c89f50fd15cdabfa0319076c2692 --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision ffd9d97b9e967e027f30a62f888a7f5670b35ba0) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision e08a9e538cf4c89f50fd15cdabfa0319076c2692) @@ -42,4 +42,9 @@ int getCountQbQuestions(Integer questionType, String searchString); + List getCollectionQuestions(long collectionUid, Integer offset, Integer limit); + + void addCollectionQuestion(long collectionUid, long qbQuestionUid); + + void removeCollectionQuestion(long collectionUid, long qbQuestionUid); } \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java =================================================================== diff -u -rffd9d97b9e967e027f30a62f888a7f5670b35ba0 -re08a9e538cf4c89f50fd15cdabfa0319076c2692 --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision ffd9d97b9e967e027f30a62f888a7f5670b35ba0) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision e08a9e538cf4c89f50fd15cdabfa0319076c2692) @@ -46,6 +46,13 @@ + "BurningQuestionLike AS bl ON bl.burningQuestion = b WHERE b.scratchieItem.qbQuestion.uid = :qbQuestionUid " + "GROUP BY b.question ORDER BY COUNT(bl.uid) DESC"; + private static final String FIND_COLLECTION_QUESTIONS = "SELECT q.* FROM lams_qb_collection_question AS c " + + "JOIN lams_qb_question ON c.qb_question_uid = q.uid WHERE c.collection_uid = :collectionUid"; + + private static final String ADD_COLLECTION_QUESTION = "INSERT INTO lams_qb_collection_question VALUES (:collectionUid, :qbQuestionUid)"; + + private static final String REMOVE_COLLECTION_QUESTION = "DELETE FROM lams_qb_collection_question WHERE collection_uid = :collectionUid AND qb_question_uid = :qbQuestionUid"; + @Override public QbQuestion getQbQuestionByUid(Long qbQuestionUid) { return (QbQuestion) this.find(QbQuestion.class, qbQuestionUid); @@ -212,4 +219,29 @@ } return map; } + + @Override + public List getCollectionQuestions(long collectionUid, Integer offset, Integer limit) { + Query query = getSession().createNativeQuery(FIND_COLLECTION_QUESTIONS, QbQuestion.class); + query.setParameter("collectionUid", collectionUid); + if (offset != null) { + query.setFirstResult(offset); + } + if (limit != null) { + query.setMaxResults(limit); + } + return query.list(); + } + + @Override + public void addCollectionQuestion(long collectionUid, long qbQuestionUid) { + getSession().createNativeQuery(ADD_COLLECTION_QUESTION).setParameter("collectionUid", collectionUid) + .setParameter("qbQuestionUid", qbQuestionUid).executeUpdate(); + } + + @Override + public void removeCollectionQuestion(long collectionUid, long qbQuestionUid) { + getSession().createNativeQuery(REMOVE_COLLECTION_QUESTION).setParameter("collectionUid", collectionUid) + .setParameter("qbQuestionUid", qbQuestionUid).executeUpdate(); + } } \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/model/QbCollection.java =================================================================== diff -u --- lams_common/src/java/org/lamsfoundation/lams/qb/model/QbCollection.java (revision 0) +++ lams_common/src/java/org/lamsfoundation/lams/qb/model/QbCollection.java (revision e08a9e538cf4c89f50fd15cdabfa0319076c2692) @@ -0,0 +1,99 @@ +package org.lamsfoundation.lams.qb.model; + +import java.io.Serializable; +import java.util.ArrayList; +import java.util.List; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.GeneratedValue; +import javax.persistence.GenerationType; +import javax.persistence.Id; +import javax.persistence.JoinColumn; +import javax.persistence.JoinTable; +import javax.persistence.ManyToMany; +import javax.persistence.OrderBy; +import javax.persistence.Table; + +import org.apache.commons.lang.builder.EqualsBuilder; +import org.apache.commons.lang.builder.HashCodeBuilder; +import org.lamsfoundation.lams.usermanagement.Organisation; + +/** + * A collection of Question Bank questions bound to an user. + * + * @author Marcin Cieslak + */ +@Entity +@Table(name = "lams_qb_collection") +public class QbCollection implements Serializable { + private static final long serialVersionUID = 8481910298794355970L; + + @Id + @Column + @GeneratedValue(strategy = GenerationType.IDENTITY) + private Long uid; + + @Column + private String name; + + // if it is true, then it is a private, one user only collection + @Column + private boolean personal = false; + + // if it is null, then this is a public collection + @Column(name = "user_id") + private Integer userId; + + @ManyToMany + @JoinTable(name = "lams_qb_collection_organisation", joinColumns = @JoinColumn(name = "collection_uid"), inverseJoinColumns = @JoinColumn(name = "organisation_id")) + @OrderBy("name") + private List organisations = new ArrayList<>(); + + public Long getUid() { + return uid; + } + + public String getName() { + return name; + } + + public void setName(String name) { + this.name = name; + } + + public boolean isPersonal() { + return personal; + } + + public void setPersonal(boolean personal) { + this.personal = personal; + } + + public Integer getUserId() { + return userId; + } + + public void setUserId(Integer userId) { + this.userId = userId; + } + + public List getOrganisations() { + return organisations; + } + + public void setOrganisations(List organisations) { + this.organisations = organisations; + } + + @Override + public boolean equals(Object o) { + QbCollection other = (QbCollection) o; + return new EqualsBuilder().append(this.uid, other.uid).isEquals(); + } + + @Override + public int hashCode() { + return new HashCodeBuilder().append(this.uid).toHashCode(); + } +} \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/service/IQbService.java =================================================================== diff -u -rca24a6088935075fff20ede75c29def3f8351316 -re08a9e538cf4c89f50fd15cdabfa0319076c2692 --- lams_common/src/java/org/lamsfoundation/lams/qb/service/IQbService.java (.../IQbService.java) (revision ca24a6088935075fff20ede75c29def3f8351316) +++ lams_common/src/java/org/lamsfoundation/lams/qb/service/IQbService.java (.../IQbService.java) (revision e08a9e538cf4c89f50fd15cdabfa0319076c2692) @@ -5,7 +5,9 @@ import org.lamsfoundation.lams.qb.dto.QbStatsActivityDTO; import org.lamsfoundation.lams.qb.dto.QbStatsDTO; +import org.lamsfoundation.lams.qb.model.QbCollection; import org.lamsfoundation.lams.qb.model.QbQuestion; +import org.lamsfoundation.lams.usermanagement.Organisation; public interface IQbService { @@ -50,4 +52,22 @@ String searchString); int getCountQbQuestions(Integer questionType, String searchString); -} + + QbCollection getPublicCollection(); + + QbCollection getUserPrivateCollection(int userId); + + List getUserCollections(int userId); + + QbCollection addCollection(int userId, String name); + + void removeCollection(long collectionUid); + + Organisation shareCollection(QbCollection collection, int organisationId); + + void unshareCollection(QbCollection collection, int organisationId); + + void addQuestionToCollection(long collectionUid, long qbQuestionUid); + + void removeQuestionFromCollection(long collectionUid, long qbQuestionUid); +} \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java =================================================================== diff -u -rca24a6088935075fff20ede75c29def3f8351316 -re08a9e538cf4c89f50fd15cdabfa0319076c2692 --- lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision ca24a6088935075fff20ede75c29def3f8351316) +++ lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision e08a9e538cf4c89f50fd15cdabfa0319076c2692) @@ -4,6 +4,7 @@ import java.util.Collections; import java.util.HashMap; import java.util.HashSet; +import java.util.Iterator; import java.util.LinkedList; import java.util.List; import java.util.Map; @@ -20,9 +21,11 @@ import org.lamsfoundation.lams.qb.dao.IQbDAO; import org.lamsfoundation.lams.qb.dto.QbStatsActivityDTO; import org.lamsfoundation.lams.qb.dto.QbStatsDTO; +import org.lamsfoundation.lams.qb.model.QbCollection; import org.lamsfoundation.lams.qb.model.QbOption; import org.lamsfoundation.lams.qb.model.QbQuestion; import org.lamsfoundation.lams.tool.service.ILamsCoreToolService; +import org.lamsfoundation.lams.usermanagement.Organisation; import org.lamsfoundation.lams.util.Configuration; import org.lamsfoundation.lams.util.ConfigurationKeys; import org.lamsfoundation.lams.util.WebUtil; @@ -234,6 +237,80 @@ return activityDTO; } + @Override + public QbCollection getPublicCollection() { + return (QbCollection) qbDAO.find(QbCollection.class, 1L); + } + + @Override + @SuppressWarnings("unchecked") + public QbCollection getUserPrivateCollection(int userId) { + Map map = new HashMap<>(); + map.put("userId", userId); + map.put("personal", true); + List result = qbDAO.findByProperties(QbCollection.class, map); + if (!result.isEmpty()) { + return result.get(0); + } + + QbCollection collection = new QbCollection(); + collection.setName("Private questions"); + collection.setUserId(userId); + collection.setPersonal(true); + qbDAO.insert(collection); + return collection; + } + + @Override + @SuppressWarnings("unchecked") + public List getUserCollections(int userId) { + return qbDAO.findByProperty(QbCollection.class, "user.userId", userId); + } + + @Override + public QbCollection addCollection(int userId, String name) { + QbCollection collection = new QbCollection(); + collection.setName(name); + collection.setUserId(userId); + qbDAO.insert(collection); + return collection; + } + + @Override + public void removeCollection(long collectionUid) { + qbDAO.deleteById(QbCollection.class, collectionUid); + } + + @Override + public Organisation shareCollection(QbCollection collection, int organisationId) { + Organisation organisation = (Organisation) qbDAO.find(Organisation.class, organisationId); + collection.getOrganisations().add(organisation); + qbDAO.update(collection); + return organisation; + } + + @Override + public void unshareCollection(QbCollection collection, int organisationId) { + Iterator orgIterator = collection.getOrganisations().iterator(); + while (orgIterator.hasNext()) { + if (orgIterator.next().getOrganisationId().equals(organisationId)) { + orgIterator.remove(); + break; + } + } + qbDAO.update(collection); + } + + @Override + public void addQuestionToCollection(long collectionUid, long qbQuestionUid) { + qbDAO.addCollectionQuestion(collectionUid, qbQuestionUid); + } + + @Override + public void removeQuestionFromCollection(long collectionUid, long qbQuestionUid) { + qbDAO.removeCollectionQuestion(collectionUid, qbQuestionUid); + } + public void setQbDAO(IQbDAO qbDAO) { this.qbDAO = qbDAO; }