/*
* OpenClinica is distributed under the
* GNU Lesser General Public License (GNU LGPL).
* For details see: http://www.openclinica.org/license
* copyright 2003-2005 Akaza Research
*/
package org.akaza.openclinica.dao.submit;
import java.sql.Connection;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.SortedSet;
import java.util.TreeSet;
import javax.sql.DataSource;
import org.akaza.openclinica.bean.core.EntityBean;
import org.akaza.openclinica.bean.core.Status;
import org.akaza.openclinica.bean.managestudy.StudyEventBean;
import org.akaza.openclinica.bean.managestudy.StudySubjectBean;
import org.akaza.openclinica.bean.submit.CRFVersionBean;
import org.akaza.openclinica.bean.submit.EventCRFBean;
import org.akaza.openclinica.bean.submit.ItemBean;
import org.akaza.openclinica.dao.EventCRFSDVFilter;
import org.akaza.openclinica.dao.EventCRFSDVSort;
import org.akaza.openclinica.dao.core.AuditableEntityDAO;
import org.akaza.openclinica.dao.core.CoreResources;
import org.akaza.openclinica.dao.core.DAODigester;
import org.akaza.openclinica.dao.core.SQLFactory;
import org.akaza.openclinica.dao.core.TypeNames;
/**
* <P>
* EventCRFDAO.java, data access object for an instance of an event being filled out on a subject. Was originally
* individual_instrument table in OpenClinica v.1.
*
* @author thickerson
*
* TODO test create and update first thing
*/
public class EventCRFDAO<K extends String, V extends ArrayList> extends AuditableEntityDAO {
// private DAODigester digester;
private void setQueryNames() {
this.findByPKAndStudyName = "findByPKAndStudy";
this.getCurrentPKName = "getCurrentPK";
}
public EventCRFDAO(DataSource ds) {
super(ds);
setQueryNames();
}
public EventCRFDAO(DataSource ds, DAODigester digester) {
super(ds);
this.digester = digester;
setQueryNames();
}
// This constructor sets up the Locale for JUnit tests; see the locale
// member variable in EntityDAO, and its initializeI18nStrings() method
public EventCRFDAO(DataSource ds, DAODigester digester, Locale locale) {
this(ds, digester);
this.locale = locale;
}
@Override
protected void setDigesterName() {
digesterName = SQLFactory.getInstance().DAO_EVENTCRF;
}
@Override
public void setTypesExpected() {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
this.setTypeExpected(2, TypeNames.INT);
this.setTypeExpected(3, TypeNames.INT);
this.setTypeExpected(4, TypeNames.DATE);
this.setTypeExpected(5, TypeNames.STRING);
this.setTypeExpected(6, TypeNames.INT);
this.setTypeExpected(7, TypeNames.INT);
this.setTypeExpected(8, TypeNames.STRING);// annotations
this.setTypeExpected(9, TypeNames.TIMESTAMP);// completed
this.setTypeExpected(10, TypeNames.INT);// validator id
this.setTypeExpected(11, TypeNames.DATE);// date validate
this.setTypeExpected(12, TypeNames.TIMESTAMP);// date val. completed
this.setTypeExpected(13, TypeNames.STRING);
this.setTypeExpected(14, TypeNames.STRING);
this.setTypeExpected(15, TypeNames.INT);// owner id
this.setTypeExpected(16, TypeNames.DATE);
this.setTypeExpected(17, TypeNames.INT);// subject id
this.setTypeExpected(18, TypeNames.DATE);// date updated
this.setTypeExpected(19, TypeNames.INT);// updater
this.setTypeExpected(20, TypeNames.BOOL);// electronic_signature_status
this.setTypeExpected(21, TypeNames.BOOL);// sdv_status
this.setTypeExpected(22, TypeNames.INT);// old_status
this.setTypeExpected(23, TypeNames.INT); // sdv_update_id
// if ("oracle".equalsIgnoreCase(CoreResources.getDBName())) {
// this.setTypeExpected(24, TypeNames.INT); // r
// }
}
public EntityBean update(EntityBean eb) {
EventCRFBean ecb = (EventCRFBean) eb;
ecb.setActive(false);
HashMap variables = new HashMap();
HashMap nullVars = new HashMap();
variables.put(new Integer(1), new Integer(ecb.getStudyEventId()));
variables.put(new Integer(2), new Integer(ecb.getCRFVersionId()));
if (ecb.getDateInterviewed() == null) {
nullVars.put(new Integer(3), new Integer(Types.DATE));
variables.put(new Integer(3), null);
} else {
variables.put(new Integer(3), ecb.getDateInterviewed());
}
variables.put(new Integer(4), ecb.getInterviewerName());
variables.put(new Integer(5), new Integer(ecb.getCompletionStatusId()));
variables.put(new Integer(6), new Integer(ecb.getStatus().getId()));
variables.put(new Integer(7), ecb.getAnnotations());
if (ecb.getDateCompleted() == null) {
nullVars.put(new Integer(8), new Integer(Types.TIMESTAMP));
variables.put(new Integer(8), null);
} else {
variables.put(new Integer(8), new java.sql.Timestamp(ecb.getDateCompleted().getTime()));
}
// variables.put(new Integer(8),ecb.getDateCompleted());
variables.put(new Integer(9), new Integer(ecb.getValidatorId()));
if (ecb.getDateValidate() == null) {
nullVars.put(new Integer(10), new Integer(Types.DATE));
variables.put(new Integer(10), null);
} else {
variables.put(new Integer(10), ecb.getDateValidate());
}
// variables.put(new Integer(10),ecb.getDateValidate());
if (ecb.getDateValidateCompleted() == null) {
nullVars.put(new Integer(11), new Integer(Types.TIMESTAMP));
variables.put(new Integer(11), null);
} else {
variables.put(new Integer(11), new Timestamp(ecb.getDateValidateCompleted().getTime()));
}
// variables.put(new Integer(11),ecb.getDateValidateCompleted());
variables.put(new Integer(12), ecb.getValidatorAnnotations());
variables.put(new Integer(13), ecb.getValidateString());
variables.put(new Integer(14), new Integer(ecb.getStudySubjectId()));
variables.put(new Integer(15), new Integer(ecb.getUpdaterId()));
variables.put(new Integer(16), new Boolean(ecb.isElectronicSignatureStatus()));
variables.put(new Integer(17), new Boolean(ecb.isSdvStatus()));
if (ecb.getOldStatus() != null && ecb.getOldStatus().getId() > 0) {
variables.put(new Integer(18), new Integer(ecb.getOldStatus().getId()));
} else {
variables.put(new Integer(18), new Integer(0));
}
// @pgawade 22-May-2011 added the sdv updater id variable
variables.put(new Integer(19), ecb.getSdvUpdateId());
// variables.put(new Integer(19), new Integer(ecb.getId()));
variables.put(new Integer(20), new Integer(ecb.getId()));
this.execute(digester.getQuery("update"), variables, nullVars);
if (isQuerySuccessful()) {
ecb.setActive(true);
}
return ecb;
}
public void markComplete(EventCRFBean ecb, boolean ide) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(ecb.getId()));
if (ide) {
execute(digester.getQuery("markCompleteIDE"), variables);
} else {
execute(digester.getQuery("markCompleteDDE"), variables);
}
}
public EntityBean create(EntityBean eb) {
EventCRFBean ecb = (EventCRFBean) eb;
HashMap variables = new HashMap();
HashMap nullVars = new HashMap();
variables.put(new Integer(1), new Integer(ecb.getStudyEventId()));
variables.put(new Integer(2), new Integer(ecb.getCRFVersionId()));
Date interviewed = ecb.getDateInterviewed();
if (interviewed != null) {
variables.put(new Integer(3), ecb.getDateInterviewed());
} else {
variables.put(new Integer(3), null);
nullVars.put(new Integer(3), new Integer(Types.DATE));
}
logger.debug("created: ecb.getInterviewerName()" + ecb.getInterviewerName());
variables.put(new Integer(4), ecb.getInterviewerName());
variables.put(new Integer(5), new Integer(ecb.getCompletionStatusId()));
variables.put(new Integer(6), new Integer(ecb.getStatus().getId()));
variables.put(new Integer(7), ecb.getAnnotations());
variables.put(new Integer(8), new Integer(ecb.getOwnerId()));
variables.put(new Integer(9), new Integer(ecb.getStudySubjectId()));
variables.put(new Integer(10), ecb.getValidateString());
variables.put(new Integer(11), ecb.getValidatorAnnotations());
executeWithPK(digester.getQuery("create"), variables, nullVars);
if (isQuerySuccessful()) {
ecb.setId(getLatestPK());
}
return ecb;
}
public Object getEntityFromHashMap(HashMap hm) {
EventCRFBean eb = new EventCRFBean();
this.setEntityAuditInformation(eb, hm);
eb.setId(((Integer) hm.get("event_crf_id")).intValue());
eb.setStudyEventId(((Integer) hm.get("study_event_id")).intValue());
eb.setCRFVersionId(((Integer) hm.get("crf_version_id")).intValue());
eb.setDateInterviewed((Date) hm.get("date_interviewed"));
eb.setInterviewerName((String) hm.get("interviewer_name"));
eb.setCompletionStatusId(((Integer) hm.get("completion_status_id")).intValue());
eb.setAnnotations((String) hm.get("annotations"));
eb.setDateCompleted((Date) hm.get("date_completed"));
eb.setValidatorId(((Integer) hm.get("validator_id")).intValue());
eb.setDateValidate((Date) hm.get("date_validate"));
eb.setDateValidateCompleted((Date) hm.get("date_validate_completed"));
eb.setValidatorAnnotations((String) hm.get("validator_annotations"));
eb.setValidateString((String) hm.get("validate_string"));
eb.setStudySubjectId(((Integer) hm.get("study_subject_id")).intValue());
eb.setSdvStatus((Boolean) hm.get("sdv_status"));
eb.setSdvUpdateId((Integer) hm.get("sdv_update_id"));
Integer oldStatusId = (Integer) hm.get("old_status_id");
eb.setOldStatus(Status.get(oldStatusId));
// eb.setStatus(Status.get((Integer) hm.get("status_id"))
return eb;
}
public Collection findAll() {
this.setTypesExpected();
ArrayList alist = this.select(digester.getQuery("findAll"));
ArrayList al = new ArrayList();
Iterator it = alist.iterator();
while (it.hasNext()) {
EventCRFBean eb = (EventCRFBean) this.getEntityFromHashMap((HashMap) it.next());
al.add(eb);
}
return al;
}
public Collection findAll(String strOrderByColumn, boolean blnAscendingSort, String strSearchPhrase) {
ArrayList al = new ArrayList();
return al;
}
public EntityBean findByPK(int ID) {
EventCRFBean eb = new EventCRFBean();
this.setTypesExpected();
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(ID));
String sql = digester.getQuery("findByPK");
ArrayList alist = this.select(sql, variables);
Iterator it = alist.iterator();
if (it.hasNext()) {
eb = (EventCRFBean) this.getEntityFromHashMap((HashMap) it.next());
}
return eb;
}
public Collection findAllByPermission(Object objCurrentUser, int intActionType, String strOrderByColumn, boolean blnAscendingSort, String strSearchPhrase) {
ArrayList al = new ArrayList();
return al;
}
public Collection findAllByPermission(Object objCurrentUser, int intActionType) {
ArrayList al = new ArrayList();
return al;
}
public ArrayList findAllByStudyEvent(StudyEventBean studyEvent) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(studyEvent.getId()));
return executeFindAllQuery("findAllByStudyEvent", variables);
}
public ArrayList findAllByStudyEventAndStatus(StudyEventBean studyEvent, Status status) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(studyEvent.getId()));
variables.put(new Integer(2), new Integer(status.getId()));
return executeFindAllQuery("findAllByStudyEventAndStatus", variables);
}
public ArrayList<EventCRFBean> findAllByStudySubject(int studySubjectId) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(studySubjectId));
return executeFindAllQuery("findAllByStudySubject", variables);
}
public ArrayList findAllByStudyEventAndCrfOrCrfVersionOid(StudyEventBean studyEvent, String crfVersionOrCrfOID) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(studyEvent.getId()));
variables.put(new Integer(2), crfVersionOrCrfOID);
variables.put(new Integer(3), crfVersionOrCrfOID);
return executeFindAllQuery("findAllByStudyEventAndCrfOrCrfVersionOid", variables);
}
public ArrayList<EventCRFBean> findAllByStudyEventInParticipantForm(StudyEventBean studyEvent,int sed_Id,int studyId) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(studyEvent.getId()));
variables.put(new Integer(2), new Integer(sed_Id));
variables.put(new Integer(3), new Integer(studyId));
return executeFindAllQuery("findAllByStudyEventInParticipantForm", variables);
}
public ArrayList findAllByCRF(int crfId) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(crfId));
return executeFindAllQuery("findAllByCRF", variables);
}
public ArrayList findAllByCRFVersion(int versionId) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(versionId));
return executeFindAllQuery("findAllByCRFVersion", variables);
}
public ArrayList findAllStudySubjectByCRFVersion(int versionId) {
this.setTypesExpected();
// ss.label, sed.name as sed_name, s.name as study_name
this.setTypeExpected(24, TypeNames.STRING);
this.setTypeExpected(25, TypeNames.STRING);
this.setTypeExpected(26, TypeNames.STRING);
if ("oracle".equalsIgnoreCase(CoreResources.getDBName())) {
this.setTypeExpected(25, TypeNames.STRING); // r
this.setTypeExpected(26, TypeNames.STRING); // r
this.setTypeExpected(27, TypeNames.STRING); // r
}
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(versionId));
ArrayList alist = this.select(digester.getQuery("findAllStudySubjectByCRFVersion"), variables);
ArrayList al = new ArrayList();
Iterator it = alist.iterator();
while (it.hasNext()) {
HashMap hm = (HashMap) it.next();
EventCRFBean eb = (EventCRFBean) this.getEntityFromHashMap(hm);
eb.setStudySubjectName((String) hm.get("label"));
eb.setEventName((String) hm.get("sed_name"));
eb.setStudyName((String) hm.get("study_name"));
al.add(eb);
}
return al;
}
public ArrayList findUndeletedWithStudySubjectsByCRFVersion(int versionId) {
this.setTypesExpected();
// ss.label, sed.name as sed_name, s.name as study_name, ss.sample_ordinal as repeat_number
// this.setTypeExpected(23, TypeNames.STRING);
this.setTypeExpected(24, TypeNames.STRING);
this.setTypeExpected(25, TypeNames.STRING);
this.setTypeExpected(26, TypeNames.STRING);
this.setTypeExpected(27, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(versionId));
ArrayList alist = this.select(digester.getQuery("findUndeletedWithStudySubjectsByCRFVersion"), variables);
ArrayList al = new ArrayList();
Iterator it = alist.iterator();
while (it.hasNext()) {
HashMap hm = (HashMap) it.next();
EventCRFBean eb = (EventCRFBean) this.getEntityFromHashMap(hm);
eb.setStudySubjectName((String) hm.get("label"));
eb.setEventName((String) hm.get("sed_name"));
eb.setStudyName((String) hm.get("study_name"));
eb.setEventOrdinal((Integer) hm.get("repeat_number"));
al.add(eb);
}
return al;
}
public ArrayList findByEventSubjectVersion(StudyEventBean studyEvent, StudySubjectBean studySubject, CRFVersionBean crfVersion) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(studyEvent.getId()));
variables.put(new Integer(2), new Integer(crfVersion.getId()));
variables.put(new Integer(3), new Integer(studySubject.getId()));
return executeFindAllQuery("findByEventSubjectVersion", variables);
}
// TODO: to get rid of warning refactor executeFindAllQuery method in
// superclass
public EventCRFBean findByEventCrfVersion(StudyEventBean studyEvent, CRFVersionBean crfVersion) {
EventCRFBean eventCrfBean = null;
HashMap<Integer, Integer> variables = new HashMap<Integer, Integer>();
variables.put(new Integer(1), new Integer(studyEvent.getId()));
variables.put(new Integer(2), new Integer(crfVersion.getId()));
ArrayList<EventCRFBean> eventCrfs = executeFindAllQuery("findByEventCrfVersion", variables);
if (!eventCrfs.isEmpty() && eventCrfs.size() == 1) {
eventCrfBean = eventCrfs.get(0);
}
return eventCrfBean;
}
public ArrayList<EventCRFBean> findByCrfVersion(CRFVersionBean crfVersion) {
HashMap<Integer, Integer> variables = new HashMap<Integer, Integer>();
variables.put(new Integer(1), new Integer(crfVersion.getId()));
ArrayList<EventCRFBean> eventCrfs = executeFindAllQuery("findByCrfVersion", variables);
return eventCrfs;
}
public void delete(int eventCRFId) {
HashMap variables = new HashMap();
variables.put(new Integer(1), new Integer(eventCRFId));
this.execute(digester.getQuery("delete"), variables);
return;
}
public void setSDVStatus(boolean sdvStatus, int userId, int eventCRFId) {
HashMap variables = new HashMap();
variables.put(new Integer(1), sdvStatus);
variables.put(new Integer(2), userId);
variables.put(new Integer(3), eventCRFId);
this.execute(digester.getQuery("setSDVStatus"), variables);
}
public Integer countEventCRFsByStudy(int studyId, int parentStudyId) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
String sql = digester.getQuery("countEventCRFsByStudy");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByStudyIdentifier(String identifier) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, identifier);
String sql = digester.getQuery("countEventCRFsByStudyIdentifier");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByStudySubject(int studySubjectId, int studyId, int parentStudyId) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studySubjectId);
variables.put(2, studyId);
variables.put(3, parentStudyId);
String sql = digester.getQuery("countEventCRFsByStudySubject");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByStudyIdentifier(int studyId, int parentStudyId, String studyIdentifier) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
variables.put(3, studyIdentifier);
String sql = digester.getQuery("countEventCRFsByStudyIdentifier");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByByStudySubjectCompleteOrLockedAndNotSDVd(int studySubjectId) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studySubjectId);
String sql = digester.getQuery("countEventCRFsByByStudySubjectCompleteOrLockedAndNotSDVd");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public ArrayList getEventCRFsByStudySubjectCompleteOrLocked(int studySubjectId) {
HashMap variables = new HashMap();
variables.put(1, studySubjectId);
return executeFindAllQuery("getEventCRFsByStudySubjectCompleteOrLocked", variables);
}
public ArrayList getEventCRFsByStudySubjectLimit(int studySubjectId, int studyId, int parentStudyId, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, studySubjectId);
variables.put(2, studyId);
variables.put(3, parentStudyId);
variables.put(4, limit);
variables.put(5, offset);
return executeFindAllQuery("getEventCRFsByStudySubjectLimit", variables);
}
public ArrayList getEventCRFsByStudySubject(int studySubjectId, int studyId, int parentStudyId) {
HashMap variables = new HashMap();
variables.put(1, studySubjectId);
variables.put(2, studyId);
variables.put(3, parentStudyId);
return executeFindAllQuery("getEventCRFsByStudySubject", variables);
}
public ArrayList getGroupByStudySubject(int studySubjectId, int studyId, int parentStudyId) {
HashMap variables = new HashMap();
variables.put(1, studySubjectId);
variables.put(2, studyId);
variables.put(3, parentStudyId);
return executeFindAllQuery("getGroupByStudySubject", variables);
}
public ArrayList getEventCRFsByStudyIdentifier(int studyId, int parentStudyId, String studyIdentifier, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
variables.put(3, studyIdentifier);
variables.put(4, limit);
variables.put(5, offset);
return executeFindAllQuery("getEventCRFsByStudyIdentifier", variables);
}
public Integer getCountWithFilter(int studyId, int parentStudyId, EventCRFSDVFilter filter) {
setTypesExpected();
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
String sql = digester.getQuery("getCountWithFilter");
sql += filter.execute("");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
Integer count = (Integer) ((HashMap) it.next()).get("count");
return count;
} else {
return null;
}
}
public ArrayList<EventCRFBean> getWithFilterAndSort(int studyId, int parentStudyId, EventCRFSDVFilter filter, EventCRFSDVSort sort, int rowStart, int rowEnd) {
ArrayList<EventCRFBean> eventCRFs = new ArrayList<EventCRFBean>();
setTypesExpected();
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
String sql = digester.getQuery("getWithFilterAndSort");
sql = sql + filter.execute("");
// sql = sql + sort.execute("");
sql = sql + " order By ec.date_created ASC "; // major hack
if ("oracle".equalsIgnoreCase(CoreResources.getDBName())) {
// sql += " ) where rownum <= " + rowEnd + " and rownum >" + rowStart + " ";
sql += " )x)where r between " + (rowStart + 1) + " and " + rowEnd;
} else {
sql = sql + " LIMIT " + (rowEnd - rowStart) + " OFFSET " + rowStart;
}
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
while (it.hasNext()) {
EventCRFBean eventCRF = (EventCRFBean) this.getEntityFromHashMap((HashMap) it.next());
eventCRFs.add(eventCRF);
}
return eventCRFs;
}
public ArrayList getEventCRFsByStudy(int studyId, int parentStudyId, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
variables.put(3, limit);
variables.put(4, offset);
return executeFindAllQuery("getEventCRFsByStudy", variables);
}
public ArrayList getEventCRFsByStudySubjectLabelLimit(String label, int studyId, int parentStudyId, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, '%' + label + '%');
variables.put(2, studyId);
variables.put(3, parentStudyId);
variables.put(4, limit);
variables.put(5, offset);
return executeFindAllQuery("getEventCRFsByStudySubjectLabelLimit", variables);
}
public ArrayList getEventCRFsByEventNameLimit(String eventName, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, eventName);
variables.put(2, limit);
variables.put(3, offset);
return executeFindAllQuery("getEventCRFsByEventNameLimit", variables);
}
public ArrayList getEventCRFsByEventDateLimit(int studyId, String eventDate, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, eventDate);
variables.put(3, limit);
variables.put(4, offset);
return executeFindAllQuery("getEventCRFsByEventDateLimit", variables);
}
public ArrayList getEventCRFsByStudySDV(int studyId, boolean sdvStatus, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, sdvStatus);
variables.put(3, limit);
variables.put(4, offset);
return executeFindAllQuery("getEventCRFsByStudySDV", variables);
}
public ArrayList getEventCRFsByCRFStatus(int studyId, int subjectEventStatusId, int limit, int offset) {
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, subjectEventStatusId);
variables.put(3, limit);
variables.put(4, offset);
return executeFindAllQuery("getEventCRFsByCRFStatus", variables);
}
public ArrayList getEventCRFsBySDVRequirement(int studyId, int parentStudyId, int limit, int offset, Integer... sdvCode) {
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
this.setTypesExpected();
String sql = digester.getQuery("getEventCRFsBySDVRequirement");
sql += " AND ( ";
for (int i = 0; i < sdvCode.length; i++) {
sql += i != 0 ? " OR " : "";
sql += " source_data_verification_code = " + sdvCode[i];
}
sql += " ) )) limit " + limit + " offset " + offset;
ArrayList alist = this.select(sql, variables);
ArrayList al = new ArrayList();
Iterator it = alist.iterator();
while (it.hasNext()) {
EventCRFBean eb = (EventCRFBean) this.getEntityFromHashMap((HashMap) it.next());
al.add(eb);
}
return al;
}
public Integer countEventCRFsByStudySubjectLabel(String label, int studyId, int parentStudyId) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, label);
variables.put(2, studyId);
variables.put(3, parentStudyId);
String sql = digester.getQuery("countEventCRFsByStudySubjectLabel");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByStudySDV(int studyId, boolean sdvStatus) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, sdvStatus);
String sql = digester.getQuery("countEventCRFsByStudySDV");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByCRFStatus(int studyId, int statusId) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, statusId);
String sql = digester.getQuery("countEventCRFsByCRFStatus");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByEventName(String eventName) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, eventName);
String sql = digester.getQuery("countEventCRFsByEventName");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsBySDVRequirement(int studyId, int parentStudyId, Integer... sdvCode) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, parentStudyId);
String sql = digester.getQuery("countEventCRFsBySDVRequirement");
sql += " AND ( ";
for (int i = 0; i < sdvCode.length; i++) {
sql += i != 0 ? " OR " : "";
sql += " source_data_verification_code = " + sdvCode[i];
}
sql += "))) ";
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByEventNameSubjectLabel(String eventName, String subjectLabel) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, eventName);
variables.put(2, subjectLabel);
String sql = digester.getQuery("countEventCRFsByEventNameSubjectLabel");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Integer countEventCRFsByEventDate(int studyId, String eventDate) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, studyId);
variables.put(2, eventDate);
String sql = digester.getQuery("countEventCRFsByEventDate");
ArrayList rows = this.select(sql, variables);
Iterator it = rows.iterator();
if (it.hasNext()) {
return (Integer) ((HashMap) it.next()).get("count");
} else {
return 0;
}
}
public Map<Integer, SortedSet<EventCRFBean>> buildEventCrfListByStudyEvent(Integer studySubjectId) {
this.setTypesExpected(); // <== Must be called first
Map<Integer, SortedSet<EventCRFBean>> result = new HashMap<Integer, SortedSet<EventCRFBean>>();
HashMap<Integer, Object> param = new HashMap<Integer, Object>();
int i = 1;
param.put(i++, studySubjectId);
List selectResult = select(digester.getQuery("buildEventCrfListByStudyEvent"), param);
Iterator it = selectResult.iterator();
while (it.hasNext()) {
EventCRFBean bean = (EventCRFBean) this.getEntityFromHashMap((HashMap) it.next());
Integer studyEventId = bean.getStudyEventId();
if (!result.containsKey(studyEventId)) {
result.put(studyEventId, new TreeSet<EventCRFBean>(new Comparator<EventCRFBean>() {
public int compare(EventCRFBean o1, EventCRFBean o2) {
Integer id1 = o1.getId();
Integer id2 = o2.getId();
return id1.compareTo(id2);
}
}));
}
result.get(studyEventId).add(bean);
}
return result;
}
public Set<Integer> buildNonEmptyEventCrfIds(Integer studySubjectId) {
Set<Integer> result = new HashSet<Integer>();
HashMap<Integer, Object> param = new HashMap<Integer, Object>();
int i = 1;
param.put(i++, studySubjectId);
List selectResult = select(digester.getQuery("buildNonEmptyEventCrfIds"), param);
Iterator it = selectResult.iterator();
while (it.hasNext()) {
HashMap hm = (HashMap) it.next();
result.add((Integer) hm.get("event_crf_id"));
}
return result;
}
public void updateCRFVersionID(int event_crf_id, int crf_version_id, int user_id) {
Connection con = null;
updateCRFVersionID(event_crf_id, crf_version_id, user_id, con);
}
/* this function allows to run transactional updates for an action */
public void updateCRFVersionID(int event_crf_id, int crf_version_id, int user_id, Connection con) {
this.unsetTypeExpected();
this.setTypeExpected(1, TypeNames.INT);
this.setTypeExpected(2, TypeNames.INT);
this.setTypeExpected(3, TypeNames.INT);
this.setTypeExpected(4, TypeNames.BOOL);
this.setTypeExpected(3, TypeNames.INT);
HashMap variables = new HashMap();
variables.put(1, crf_version_id);
variables.put(2, user_id);
variables.put(3, user_id);
variables.put(4, false);
variables.put(5, event_crf_id);
String sql = digester.getQuery("updateCRFVersionID");
// this is the way to make the change transactional
if (con == null) {
this.execute(sql, variables);
} else {
this.execute(sql, variables, con);
}
}
}