Examples with CRFVersionDAO org.akaza.openclinica.dao.submit.CRFVersionDAO used on opensource projects

Example 6 with CRFVersionDAO

use of org.akaza.openclinica.dao.submit.CRFVersionDAO in project OpenClinica by OpenClinica.

the class AuditLogStudyServlet method processRequest.

/*
     * (non-Javadoc) Assume that we get the user id automatically. We will jump
     * from the edit user page if the user is an admin, they can get to see the
     * users' log
     * 
     * @see org.akaza.openclinica.control.core.SecureController#processRequest()
     */
/*
     * (non-Javadoc) redo this servlet to run the audits per study subject for
     * the study; need to add a studyId param and then use the
     * StudySubjectDAO.findAllByStudyOrderByLabel() method to grab a lot of
     * study subject beans and then return them much like in
     * ViewStudySubjectAuditLogServet.process()
     * 
     * currentStudy instead of studyId?
     */
@Override
protected void processRequest() throws Exception {
    int studyId = currentStudy.getId();
    StudySubjectDAO subdao = new StudySubjectDAO(sm.getDataSource());
    SubjectDAO sdao = new SubjectDAO(sm.getDataSource());
    AuditDAO adao = new AuditDAO(sm.getDataSource());
    FormProcessor fp = new FormProcessor(request);
    StudyEventDAO sedao = new StudyEventDAO(sm.getDataSource());
    StudyEventDefinitionDAO seddao = new StudyEventDefinitionDAO(sm.getDataSource());
    EventDefinitionCRFDAO edcdao = new EventDefinitionCRFDAO(sm.getDataSource());
    EventCRFDAO ecdao = new EventCRFDAO(sm.getDataSource());
    StudyDAO studydao = new StudyDAO(sm.getDataSource());
    CRFDAO cdao = new CRFDAO(sm.getDataSource());
    CRFVersionDAO cvdao = new CRFVersionDAO(sm.getDataSource());
    HashMap eventCRFAuditsHashMap = new HashMap();
    HashMap eventsHashMap = new HashMap();
    HashMap studySubjectAuditsHashMap = new HashMap();
    HashMap subjectHashMap = new HashMap();
    ArrayList studySubjects = subdao.findAllByStudyOrderByLabel(currentStudy);
    logger.info("found " + studySubjects.size() + " study subjects");
    request.setAttribute("studySubjects", studySubjects);
    for (int ss = 0; ss < studySubjects.size(); ss++) {
        ArrayList studySubjectAudits = new ArrayList();
        ArrayList eventCRFAudits = new ArrayList();
        StudySubjectBean studySubject = (StudySubjectBean) studySubjects.get(ss);
        // request.setAttribute("studySub"+ss, studySubject);
        SubjectBean subject = (SubjectBean) sdao.findByPK(studySubject.getSubjectId());
        subjectHashMap.put(new Integer(studySubject.getId()), subject);
        // logger.info("just set a subject with a status of
        // "+subject.getStatus().getName());
        // request.setAttribute("subject"+ss, subject);
        StudyBean study = (StudyBean) studydao.findByPK(studySubject.getStudyId());
        request.setAttribute("study", study);
        // hmm, repetitive work?
        // Show both study subject and subject audit events together
        // Study
        studySubjectAudits.addAll(adao.findStudySubjectAuditEvents(studySubject.getId()));
        // subject
        // value
        // changed
        // Global
        studySubjectAudits.addAll(adao.findSubjectAuditEvents(subject.getId()));
        // subject
        // value
        // changed
        studySubjectAuditsHashMap.put(new Integer(studySubject.getId()), studySubjectAudits);
        // request.setAttribute("studySubjectAudits"+ss,
        // studySubjectAudits);
        // Get the list of events
        ArrayList events = sedao.findAllByStudySubject(studySubject);
        for (int i = 0; i < events.size(); i++) {
            // Link study event definitions
            StudyEventBean studyEvent = (StudyEventBean) events.get(i);
            studyEvent.setStudyEventDefinition((StudyEventDefinitionBean) seddao.findByPK(studyEvent.getStudyEventDefinitionId()));
            // Link event CRFs
            studyEvent.setEventCRFs(ecdao.findAllByStudyEvent(studyEvent));
        }
        // for (int i = 0; i < events.size(); i++) {
        // StudyEventBean studyEvent = (StudyEventBean) events.get(i);
        // ArrayList eventCRFs = studyEvent.getEventCRFs();
        // for (int j = 0; j < eventCRFs.size(); j++) {
        // //Link CRF and CRF Versions
        // EventCRFBean eventCRF = (EventCRFBean) eventCRFs.get(j);
        // eventCRF.setCrfVersion((CRFVersionBean)
        // cvdao.findByPK(eventCRF.getCRFVersionId()));
        // eventCRF.setCrf((CRFBean)
        // cdao.findByVersionId(eventCRF.getCRFVersionId()));
        // //Get the event crf audits
        // eventCRFAudits.addAll(adao.findEventCRFAuditEvents(eventCRF.getId()));
        // }
        // }
        eventsHashMap.put(new Integer(studySubject.getId()), events);
    // request.setAttribute("events"+ss, events);
    // eventCRFAuditsHashMap.put(new Integer(studySubject.getId()),
    // eventCRFAudits);
    // request.setAttribute("eventCRFAudits"+ss, eventCRFAudits);
    }
    // request.setAttribute("eventCRFAudits", eventCRFAuditsHashMap);
    request.setAttribute("events", eventsHashMap);
    request.setAttribute("studySubjectAudits", studySubjectAuditsHashMap);
    request.setAttribute("study", currentStudy);
    request.setAttribute("subjects", subjectHashMap);
    // FormProcessor fp = new FormProcessor(request);
    // 
    // AuditEventDAO aeDAO = new AuditEventDAO(sm.getDataSource());
    // ArrayList al = aeDAO.findAllByStudyId(currentStudy.getId());
    // 
    // EntityBeanTable table = fp.getEntityBeanTable();
    // ArrayList allRows = AuditEventStudyRow.generateRowsFromBeans(al);
    // String[] columns = { "Date and Time", "Action", "Entity/Operation",
    // "Record ID", "Changes and Additions","Other Info" };
    // table.setColumns(new ArrayList(Arrays.asList(columns)));
    // table.hideColumnLink(4);
    // table.hideColumnLink(1);
    // table.hideColumnLink(5);
    // table.setQuery("AuditLogUser?userLogId="+userId, new HashMap());
    // String[] columns =
    // {resword.getString("date_and_time"),resword.getString("action_message"),
    // resword.getString("entity_operation"),
    // resword.getString("updated_by"),resword.getString("subject_unique_ID"),resword.getString("changes_and_additions"),
    // //"Other Info",
    // resword.getString("actions")};
    // table.setColumns(new ArrayList(Arrays.asList(columns)));
    // table.setAscendingSort(false);
    // table.hideColumnLink(1);
    // table.hideColumnLink(5);
    // table.hideColumnLink(6);
    // //table.hideColumnLink(7);
    // table.setQuery("AuditLogStudy", new HashMap());
    // table.setRows(allRows);
    // table.computeDisplay();
    // 
    // 
    // request.setAttribute("table", table);
    logger.warn("*** found servlet, sending to page ***");
    String pattn = "";
    String pattern2 = "";
    pattn = ResourceBundleProvider.getFormatBundle().getString("date_format_string");
    pattern2 = ResourceBundleProvider.getFormatBundle().getString("date_time_format_string");
    request.setAttribute("dateFormatPattern", pattn);
    request.setAttribute("dateTimeFormatPattern", pattern2);
    forwardPage(Page.AUDIT_LOG_STUDY);
}

Example 7 with CRFVersionDAO

use of org.akaza.openclinica.dao.submit.CRFVersionDAO in project OpenClinica by OpenClinica.

the class RuleSetServiceTest method getRuleSetsByCrfStudyAndStudyEventDefinition.

// @pgawade 08-NOV-2010 Commented out the test
// "testFilterRuleSetsByStudyEventOrdinal" temporarily. Krikor will look
// into this.
// public void testFilterRuleSetsByStudyEventOrdinal() {
// List<RuleSetBean> ruleSets = getRuleSets();
// StudyEventDAO studyEventDao = new StudyEventDAO(getDataSource());
// StudyEventBean studyEventBean = (StudyEventBean)
// studyEventDao.findByPK(1);
// 
// RuleSetServiceInterface instance = (RuleSetServiceInterface)
// getContext().getBean("ruleSetService");
// List<RuleSetBean> ruleSets1 =
// instance.filterRuleSetsByStudyEventOrdinal(ruleSets, studyEventBean);
// 
// assertEquals("Expressions Size inside this RuleSet should be 1", 1,
// ruleSets1.get(0).getExpressions().size());
// assertEquals("Expression Value should be SE_ED2REPEA[1].F_CONC_V20.IG_CONC_CONCOMITANTMEDICATIONS.I_CONC_CON_MED_NAME",
// "SE_ED2REPEA[1].F_CONC_V20.IG_CONC_CONCOMITANTMEDICATIONS.I_CONC_CON_MED_NAME",
// ruleSets1.get(0).getExpressions().get(0).getValue());
// }
// 
// @pgawade 08-NOV-2010 Commented out the test
// "testFilterRuleSetsByStudyEventOrdinalWithALL" temporarily. Krikor will
// look into this.
// public void testFilterRuleSetsByStudyEventOrdinalWithALL() {
// List<RuleSetBean> ruleSets = getRuleSets();
// StudyEventDAO studyEventDao = new StudyEventDAO(getDataSource());
// StudyEventBean studyEventBean = (StudyEventBean)
// studyEventDao.findByPK(2);
// 
// RuleSetServiceInterface instance = (RuleSetServiceInterface)
// getContext().getBean("ruleSetService");
// ruleSets.get(0).getTarget().setValue("SE_ED2REPEA[ALL].F_CONC_V20.IG_CONC_CONCOMITANTMEDICATIONS.I_CONC_CON_MED_NAME");
// List<RuleSetBean> ruleSets2 =
// instance.filterRuleSetsByStudyEventOrdinal(ruleSets, studyEventBean);
// 
// assertEquals("Expressions Size inside this RuleSet should be 1", 1,
// ruleSets2.get(0).getExpressions().size());
// assertEquals("Expression Value should be SE_ED2REPEA[2].F_CONC_V20.IG_CONC_CONCOMITANTMEDICATIONS.I_CONC_CON_MED_NAME",
// "SE_ED2REPEA[2].F_CONC_V20.IG_CONC_CONCOMITANTMEDICATIONS.I_CONC_CON_MED_NAME",
// ruleSets2.get(0).getExpressions().get(0).getValue());
// }
private List<RuleSetBean> getRuleSetsByCrfStudyAndStudyEventDefinition() {
    StudyDAO studyDao = new StudyDAO(getDataSource());
    StudyBean study = (StudyBean) studyDao.findByPK(1);
    assertNotNull(study);
    StudyEventDefinitionDAO studyEventDefinitionDao = new StudyEventDefinitionDAO(getDataSource());
    StudyEventDefinitionBean studyEventDefinition = (StudyEventDefinitionBean) studyEventDefinitionDao.findByPK(2);
    assertNotNull(studyEventDefinition);
    CRFVersionDAO crfVersionDao = new CRFVersionDAO(getDataSource());
    CRFVersionBean crfVersion = (CRFVersionBean) crfVersionDao.findByPK(2);
    assertNotNull(crfVersion);
    RuleSetServiceInterface instance = (RuleSetServiceInterface) getContext().getBean("ruleSetService");
    List<RuleSetBean> ruleSets = instance.getRuleSetsByCrfStudyAndStudyEventDefinition(study, studyEventDefinition, crfVersion);
    return ruleSets;
}

Example 8 with CRFVersionDAO

use of org.akaza.openclinica.dao.submit.CRFVersionDAO in project OpenClinica by OpenClinica.

the class PrintAllEventCRFServlet method processRequest.

@Override
public void processRequest(HttpServletRequest request, HttpServletResponse response) throws Exception {
    FormProcessor fp = new FormProcessor(request);
    SessionManager sm = (SessionManager) request.getSession().getAttribute("sm");
    EventCRFBean ecb = (EventCRFBean) request.getAttribute(INPUT_EVENT_CRF);
    SectionBean sb = (SectionBean) request.getAttribute(SECTION_BEAN);
    ArrayList<SectionBean> allSectionBeans;
    // The PrintDataEntry servlet handles this parameter
    boolean isSubmitted = false;
    StudyEventDefinitionDAO sedao = new StudyEventDefinitionDAO(sm.getDataSource());
    EventDefinitionCRFDAO edao = new EventDefinitionCRFDAO(sm.getDataSource());
    EventDefinitionCRFDAO edcdao = new EventDefinitionCRFDAO(sm.getDataSource());
    StudyDAO studyDao = new StudyDAO(sm.getDataSource());
    StudyBean currentStudy = (StudyBean) request.getSession().getAttribute("study");
    ArrayList<StudyEventDefinitionBean> seds = new ArrayList<StudyEventDefinitionBean>();
    seds = sedao.findAllByStudy(currentStudy);
    // ArrayList eventDefinitionCRFs = (ArrayList) edao.findAllByStudy(site);
    CRFVersionDAO cvdao = new CRFVersionDAO(sm.getDataSource());
    CRFDAO cdao = new CRFDAO(sm.getDataSource());
    ArrayList<EventDefinitionCRFBean> edcs = new ArrayList();
    for (StudyEventDefinitionBean sed : seds) {
        int defId = sed.getId();
        edcs.addAll(edcdao.findAllByDefinition(currentStudy, defId));
    }
    Map eventDefinitionDefaultVersions = new LinkedHashMap();
    for (int i = 0; i < edcs.size(); i++) {
        EventDefinitionCRFBean edc = edcs.get(i);
        ArrayList versions = (ArrayList) cvdao.findAllByCRF(edc.getCrfId());
        edc.setVersions(versions);
        CRFBean crf = (CRFBean) cdao.findByPK(edc.getCrfId());
        // edc.setCrfLabel(crf.getLabel());
        edc.setCrfName(crf.getName());
        // to show/hide edit action on jsp page
        if (crf.getStatus().equals(Status.AVAILABLE)) {
            edc.setOwner(crf.getOwner());
        }
        CRFVersionBean defaultVersion = (CRFVersionBean) cvdao.findByPK(edc.getDefaultVersionId());
        StudyEventDefinitionBean studyEventDefinitionBean = (StudyEventDefinitionBean) sedao.findByPK(edc.getStudyEventDefinitionId());
        edc.setDefaultVersionName(defaultVersion.getName());
        if (defaultVersion.getStatus().isAvailable()) {
            List list = (ArrayList) eventDefinitionDefaultVersions.get(studyEventDefinitionBean);
            if (list == null)
                list = new ArrayList();
            list.add(defaultVersion);
            eventDefinitionDefaultVersions.put(studyEventDefinitionBean, list);
        }
    }
    // Whether IE6 or IE7 is involved
    String isIE = fp.getString("ie");
    if ("y".equalsIgnoreCase(isIE)) {
        request.setAttribute("isInternetExplorer", "true");
    }
    SectionDAO sdao = new SectionDAO(sm.getDataSource());
    CRFVersionDAO crfVersionDAO = new CRFVersionDAO(sm.getDataSource());
    CRFDAO crfDao = new CRFDAO(sm.getDataSource());
    Map sedCrfBeans = null;
    for (Iterator it = eventDefinitionDefaultVersions.keySet().iterator(); it.hasNext(); ) {
        if (sedCrfBeans == null)
            sedCrfBeans = new LinkedHashMap();
        StudyEventDefinitionBean sedBean = (StudyEventDefinitionBean) it.next();
        List crfVersions = (ArrayList) eventDefinitionDefaultVersions.get(sedBean);
        for (Iterator crfIt = crfVersions.iterator(); crfIt.hasNext(); ) {
            CRFVersionBean crfVersionBean = (CRFVersionBean) crfIt.next();
            allSectionBeans = new ArrayList<SectionBean>();
            ArrayList sectionBeans = new ArrayList();
            ItemGroupDAO itemGroupDao = new ItemGroupDAO(sm.getDataSource());
            // Find truely grouped tables, not groups with a name of 'Ungrouped'
            List<ItemGroupBean> itemGroupBeans = itemGroupDao.findOnlyGroupsByCRFVersionID(crfVersionBean.getId());
            CRFBean crfBean = crfDao.findByVersionId(crfVersionBean.getId());
            if (itemGroupBeans.size() > 0) {
                // get a DisplaySectionBean for each section of the CRF, sort
                // them, then
                // dispatch the request to a print JSP. The constructor for this
                // handler takes
                // a boolean value depending on whether data is involved or not
                // ('false' in terms of this
                // servlet; see PrintDataEntryServlet).
                DisplaySectionBeanHandler handler = new DisplaySectionBeanHandler(false, getDataSource(), getServletContext());
                handler.setCrfVersionId(crfVersionBean.getId());
                // handler.setEventCRFId(eventCRFId);
                List<DisplaySectionBean> displaySectionBeans = handler.getDisplaySectionBeans();
                request.setAttribute("listOfDisplaySectionBeans", displaySectionBeans);
                // Make available the CRF names and versions for
                // the web page's header
                CRFVersionBean crfverBean = (CRFVersionBean) crfVersionDAO.findByPK(crfVersionBean.getId());
                request.setAttribute("crfVersionBean", crfverBean);
                request.setAttribute("crfBean", crfBean);
                // Set an attribute signaling that data is not involved
                request.setAttribute("dataInvolved", "false");
                PrintCRFBean printCrfBean = new PrintCRFBean();
                printCrfBean.setDisplaySectionBeans(displaySectionBeans);
                printCrfBean.setCrfVersionBean(crfVersionBean);
                printCrfBean.setCrfBean(crfBean);
                printCrfBean.setEventCrfBean(ecb);
                printCrfBean.setGrouped(true);
                List list = (ArrayList) sedCrfBeans.get(sedBean);
                if (list == null)
                    list = new ArrayList();
                list.add(printCrfBean);
                sedCrfBeans.put(sedBean, list);
                continue;
            }
            ecb = new EventCRFBean();
            ecb.setCRFVersionId(crfVersionBean.getId());
            CRFVersionBean version = (CRFVersionBean) crfVersionDAO.findByPK(crfVersionBean.getId());
            ArrayList sects = (ArrayList) sdao.findByVersionId(version.getId());
            for (int i = 0; i < sects.size(); i++) {
                sb = (SectionBean) sects.get(i);
                // super.sb = sb;
                int sectId = sb.getId();
                if (sectId > 0) {
                    allSectionBeans.add((SectionBean) sdao.findByPK(sectId));
                }
            }
            request.setAttribute(INPUT_EVENT_CRF, ecb);
            request.setAttribute(SECTION_BEAN, sb);
            request.setAttribute(ALL_SECTION_BEANS, allSectionBeans);
            sectionBeans = super.getAllDisplayBeans(request);
            DisplaySectionBean dsb = super.getDisplayBean(false, false, request, isSubmitted);
            PrintCRFBean printCrfBean = new PrintCRFBean();
            printCrfBean.setAllSections(sectionBeans);
            printCrfBean.setDisplaySectionBean(dsb);
            printCrfBean.setEventCrfBean(ecb);
            printCrfBean.setCrfVersionBean(crfVersionBean);
            printCrfBean.setCrfBean(crfBean);
            printCrfBean.setGrouped(false);
            List list = (ArrayList) sedCrfBeans.get(sedBean);
            if (list == null)
                list = new ArrayList();
            list.add(printCrfBean);
            sedCrfBeans.put(sedBean, list);
        }
    }
    request.setAttribute("sedCrfBeans", sedCrfBeans);
    request.setAttribute("studyName", currentStudy.getName());
    forwardPage(Page.VIEW_ALL_DEFAULT_CRF_VERSIONS_PRINT, request, response);
}

Example 9 with CRFVersionDAO

use of org.akaza.openclinica.dao.submit.CRFVersionDAO in project OpenClinica by OpenClinica.

the class PrintCRFByIdServlet method processRequest.

/*
     * (non-Javadoc)
     * @see org.akaza.openclinica.control.managestudy.ViewSectionDataEntryServlet#processRequest()
     */
@Override
public void processRequest(HttpServletRequest request, HttpServletResponse response) throws Exception {
    StudyBean currentStudy = (StudyBean) request.getSession().getAttribute("study");
    StudyDAO studyDao = new StudyDAO(getDataSource());
    currentStudy = (StudyBean) studyDao.findByPK(1);
    CRFVersionDAO crfVersionDao = new CRFVersionDAO(getDataSource());
    if (request.getParameter("id") == null) {
        forwardPage(Page.LOGIN, request, response);
    }
    CRFVersionBean crfVersion = crfVersionDao.findByOid(request.getParameter("id"));
    request.setAttribute("study", currentStudy);
    if (crfVersion != null) {
        request.setAttribute("id", String.valueOf(crfVersion.getId()));
        super.processRequest(request, response);
    } else {
        forwardPage(Page.LOGIN, request, response);
    }
}

Example 10 with CRFVersionDAO

use of org.akaza.openclinica.dao.submit.CRFVersionDAO in project OpenClinica by OpenClinica.

the class ExportExcelStudySubjectAuditLogServlet method processRequest.

@Override
public void processRequest() throws Exception {
    StudySubjectDAO subdao = new StudySubjectDAO(sm.getDataSource());
    SubjectDAO sdao = new SubjectDAO(sm.getDataSource());
    AuditDAO adao = new AuditDAO(sm.getDataSource());
    StudyEventDAO sedao = new StudyEventDAO(sm.getDataSource());
    StudyEventDefinitionDAO seddao = new StudyEventDefinitionDAO(sm.getDataSource());
    EventDefinitionCRFDAO edcdao = new EventDefinitionCRFDAO(sm.getDataSource());
    EventCRFDAO ecdao = new EventCRFDAO(sm.getDataSource());
    StudyDAO studydao = new StudyDAO(sm.getDataSource());
    CRFDAO cdao = new CRFDAO(sm.getDataSource());
    CRFVersionDAO cvdao = new CRFVersionDAO(sm.getDataSource());
    StudySubjectBean studySubject = null;
    SubjectBean subject = null;
    ArrayList events = null;
    ArrayList studySubjectAudits = new ArrayList();
    ArrayList eventCRFAudits = new ArrayList();
    ArrayList studyEventAudits = new ArrayList();
    ArrayList allDeletedEventCRFs = new ArrayList();
    ArrayList allEventCRFs = new ArrayList();
    ArrayList allEventCRFItems = new ArrayList();
    String attachedFilePath = Utils.getAttachedFilePath(currentStudy);
    FormProcessor fp = new FormProcessor(request);
    int studySubId = fp.getInt("id", true);
    if (studySubId == 0) {
        addPageMessage(respage.getString("please_choose_a_subject_to_view"));
        forwardPage(Page.LIST_STUDY_SUBJECTS);
    } else {
        studySubject = (StudySubjectBean) subdao.findByPK(studySubId);
        StudyBean study = (StudyBean) studydao.findByPK(studySubject.getStudyId());
        // Check if this StudySubject would be accessed from the Current Study
        if (studySubject.getStudyId() != currentStudy.getId()) {
            if (currentStudy.getParentStudyId() > 0) {
                addPageMessage(respage.getString("no_have_correct_privilege_current_study") + " " + respage.getString("change_active_study_or_contact"));
                forwardPage(Page.MENU_SERVLET);
                return;
            } else {
                // The SubjectStudy is not belong to currentstudy and current study is not a site.
                Collection sites = studydao.findOlnySiteIdsByStudy(currentStudy);
                if (!sites.contains(study.getId())) {
                    addPageMessage(respage.getString("no_have_correct_privilege_current_study") + " " + respage.getString("change_active_study_or_contact"));
                    forwardPage(Page.MENU_SERVLET);
                    return;
                }
            }
        }
        subject = (SubjectBean) sdao.findByPK(studySubject.getSubjectId());
        /* Show both study subject and subject audit events together */
        // Study subject value changed
        Collection studySubjectAuditEvents = adao.findStudySubjectAuditEvents(studySubject.getId());
        // integer values.
        for (Iterator iterator = studySubjectAuditEvents.iterator(); iterator.hasNext(); ) {
            AuditBean auditBean = (AuditBean) iterator.next();
            if (auditBean.getAuditEventTypeId() == 3) {
                auditBean.setOldValue(Status.get(Integer.parseInt(auditBean.getOldValue())).getName());
                auditBean.setNewValue(Status.get(Integer.parseInt(auditBean.getNewValue())).getName());
            }
        }
        studySubjectAudits.addAll(studySubjectAuditEvents);
        // Global subject value changed
        studySubjectAudits.addAll(adao.findSubjectAuditEvents(subject.getId()));
        studySubjectAudits.addAll(adao.findStudySubjectGroupAssignmentAuditEvents(studySubject.getId()));
        // Get the list of events
        events = sedao.findAllByStudySubject(studySubject);
        for (int i = 0; i < events.size(); i++) {
            // Link study event definitions
            StudyEventBean studyEvent = (StudyEventBean) events.get(i);
            studyEvent.setStudyEventDefinition((StudyEventDefinitionBean) seddao.findByPK(studyEvent.getStudyEventDefinitionId()));
            // Link event CRFs
            studyEvent.setEventCRFs(ecdao.findAllByStudyEvent(studyEvent));
            // Find deleted Event CRFs
            List deletedEventCRFs = adao.findDeletedEventCRFsFromAuditEvent(studyEvent.getId());
            allDeletedEventCRFs.addAll(deletedEventCRFs);
            List eventCRFs = (List) adao.findAllEventCRFAuditEvents(studyEvent.getId());
            allEventCRFs.addAll(eventCRFs);
            List eventCRFItems = (List) adao.findAllEventCRFAuditEventsWithItemDataType(studyEvent.getId());
            allEventCRFItems.addAll(eventCRFItems);
            logger.info("deletedEventCRFs size[" + deletedEventCRFs.size() + "]");
            logger.info("allEventCRFItems size[" + allEventCRFItems.size() + "]");
        }
        for (int i = 0; i < events.size(); i++) {
            StudyEventBean studyEvent = (StudyEventBean) events.get(i);
            studyEventAudits.addAll(adao.findStudyEventAuditEvents(studyEvent.getId()));
            ArrayList eventCRFs = studyEvent.getEventCRFs();
            for (int j = 0; j < eventCRFs.size(); j++) {
                // Link CRF and CRF Versions
                EventCRFBean eventCRF = (EventCRFBean) eventCRFs.get(j);
                eventCRF.setCrfVersion((CRFVersionBean) cvdao.findByPK(eventCRF.getCRFVersionId()));
                eventCRF.setCrf(cdao.findByVersionId(eventCRF.getCRFVersionId()));
                // Get the event crf audits
                eventCRFAudits.addAll(adao.findEventCRFAuditEventsWithItemDataType(eventCRF.getId()));
                logger.info("eventCRFAudits size [" + eventCRFAudits.size() + "] eventCRF id [" + eventCRF.getId() + "]");
            }
        }
        ItemDataDAO itemDataDao = new ItemDataDAO(sm.getDataSource());
        for (Object o : eventCRFAudits) {
            AuditBean ab = (AuditBean) o;
            if (ab.getAuditTable().equalsIgnoreCase("item_data")) {
                ItemDataBean idBean = (ItemDataBean) itemDataDao.findByPK(ab.getEntityId());
                ab.setOrdinal(idBean.getOrdinal());
            }
        }
    }
    try {
        WritableFont headerFormat = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE2);
        WritableCellFormat cellFormat = new WritableCellFormat();
        cellFormat.setFont(headerFormat);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=export.xls");
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));
        WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream(), wbSettings);
        int row = 0;
        // Subject Information
        workbook.createSheet("Subject Information", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        // Subject Summary
        String[] excelRow = new String[] { "study_subject_ID", "secondary_subject_ID", "date_of_birth", "person_ID", "created_by", "status" };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        excelRow = new String[] { studySubject.getLabel(), studySubject.getSecondaryLabel(), dateFormat(subject.getDateOfBirth()), subject.getUniqueIdentifier(), studySubject.getOwner().getName(), studySubject.getStatus().getName() };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        row++;
        // Subject Audit Events
        excelRow = new String[] { "audit_event", "date_time_of_server", "user", "value_type", "old", "new" };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        for (int j = 0; j < studySubjectAudits.size(); j++) {
            AuditBean audit = (AuditBean) studySubjectAudits.get(j);
            excelRow = new String[] { audit.getAuditEventTypeName(), dateTimeFormat(audit.getAuditDate()), audit.getUserName(), audit.getEntityName(), audit.getOldValue(), audit.getNewValue() };
            for (int i = 0; i < excelRow.length; i++) {
                Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
        }
        row++;
        // Study Events
        excelRow = new String[] { "study_events", "location", "date", "occurrence_number" };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        for (int j = 0; j < events.size(); j++) {
            StudyEventBean event = (StudyEventBean) events.get(j);
            if (event.getStartTimeFlag()) {
                excelRow = new String[] { event.getStudyEventDefinition().getName(), event.getLocation(), dateTimeFormat(event.getDateStarted()), Integer.toString(event.getSampleOrdinal()) };
            } else {
                excelRow = new String[] { event.getStudyEventDefinition().getName(), event.getLocation(), dateFormat(event.getDateStarted()), Integer.toString(event.getSampleOrdinal()) };
            }
            for (int i = 0; i < excelRow.length; i++) {
                Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
        }
        autoSizeColumns(excelSheet);
        int sheet = 0;
        // Study Event Summary Looper
        for (int eventCount = 0; eventCount < events.size(); eventCount++) {
            row = 0;
            sheet++;
            StudyEventBean event = (StudyEventBean) events.get(eventCount);
            workbook.createSheet(event.getStudyEventDefinition().getName().replace("/", ".") + "_" + event.getSampleOrdinal(), sheet);
            excelSheet = workbook.getSheet(sheet);
            Label label = null;
            // Header
            label = new Label(0, row, ResourceBundleProvider.getResWord("name"), cellFormat);
            excelSheet.addCell(label);
            label = new Label(1, row, event.getStudyEventDefinition().getName(), cellFormat);
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, "Location");
            excelSheet.addCell(label);
            label = new Label(1, row, event.getLocation());
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, "Start Date");
            excelSheet.addCell(label);
            if (event.getStartTimeFlag()) {
                label = new Label(1, row, dateTimeFormat(event.getDateStarted()));
            } else {
                label = new Label(1, row, dateFormat(event.getDateStarted()));
            }
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, "Status");
            excelSheet.addCell(label);
            label = new Label(1, row, event.getSubjectEventStatus().getName());
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, ResourceBundleProvider.getResWord("occurrence_number"));
            excelSheet.addCell(label);
            label = new Label(1, row, Integer.toString(event.getSampleOrdinal()));
            excelSheet.addCell(label);
            row++;
            row++;
            // End Header
            // Audit for Deleted Event CRFs
            excelRow = new String[] { "name", "version", "deleted_by", "delete_date" };
            for (int i = 0; i < excelRow.length; i++) {
                label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
            for (int j = 0; j < allDeletedEventCRFs.size(); j++) {
                DeletedEventCRFBean deletedEventCRF = (DeletedEventCRFBean) allDeletedEventCRFs.get(j);
                if (deletedEventCRF.getStudyEventId() == event.getId()) {
                    excelRow = new String[] { deletedEventCRF.getCrfName(), deletedEventCRF.getCrfVersion(), deletedEventCRF.getDeletedBy(), dateFormat(deletedEventCRF.getDeletedDate()) };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                }
            }
            row++;
            row++;
            // Audit Events for Study Event
            excelRow = new String[] { "audit_event", "date_time_of_server", "user", "value_type", "old", "new" };
            for (int i = 0; i < excelRow.length; i++) {
                label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
            for (int j = 0; j < studyEventAudits.size(); j++) {
                AuditBean studyEvent = (AuditBean) studyEventAudits.get(j);
                if (studyEvent.getEntityId() == event.getId()) {
                    String getOld = studyEvent.getOldValue();
                    String oldValue = "";
                    if (getOld.equals("0"))
                        oldValue = "invalid";
                    else if (getOld.equals("1"))
                        oldValue = "scheduled";
                    else if (getOld.equals("2"))
                        oldValue = "not_scheduled";
                    else if (getOld.equals("3"))
                        oldValue = "data_entry_started";
                    else if (getOld.equals("4"))
                        oldValue = "completed";
                    else if (getOld.equals("5"))
                        oldValue = "stopped";
                    else if (getOld.equals("6"))
                        oldValue = "skipped";
                    else if (getOld.equals("7"))
                        oldValue = "locked";
                    else if (getOld.equals("8"))
                        oldValue = "signed";
                    else
                        oldValue = studyEvent.getOldValue();
                    String getNew = studyEvent.getNewValue();
                    String newValue = "";
                    if (getNew.equals("0"))
                        newValue = "invalid";
                    else if (getNew.equals("1"))
                        newValue = "scheduled";
                    else if (getNew.equals("2"))
                        newValue = "not_scheduled";
                    else if (getNew.equals("3"))
                        newValue = "data_entry_started";
                    else if (getNew.equals("4"))
                        newValue = "completed";
                    else if (getNew.equals("5"))
                        newValue = "removed";
                    else if (getNew.equals("6"))
                        newValue = "skipped";
                    else if (getNew.equals("7"))
                        newValue = "locked";
                    else if (getNew.equals("8"))
                        newValue = "signed";
                    else if (getNew.equals("9"))
                        newValue = "forzen";
                    else
                        newValue = studyEvent.getNewValue();
                    excelRow = new String[] { studyEvent.getAuditEventTypeName(), dateTimeFormat(studyEvent.getAuditDate()), studyEvent.getUserName(), studyEvent.getEntityName() + "(" + studyEvent.getOrdinal() + ")", oldValue, newValue };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                }
            }
            row++;
            row++;
            // Event CRFs Audit Events
            for (int j = 0; j < allEventCRFs.size(); j++) {
                AuditBean auditBean = (AuditBean) allEventCRFs.get(j);
                if (auditBean.getStudyEventId() == event.getId()) {
                    // Audit Events for Study Event
                    excelRow = new String[] { "name", "version", "date_interviewed", "interviewer_name", "owner" };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                    excelRow = new String[] { auditBean.getCrfName(), auditBean.getCrfVersionName(), dateFormat(auditBean.getDateInterviewed()), auditBean.getInterviewerName(), auditBean.getUserName() };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                    row++;
                    excelRow = new String[] { "audit_event", "date_time_of_server", "user", "value_type", "old", "new" };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                    row++;
                    for (int k = 0; k < allEventCRFItems.size(); k++) {
                        row--;
                        AuditBean eventCrfAudit = (AuditBean) allEventCRFItems.get(k);
                        if (eventCrfAudit.getStudyEventId() == event.getId() && eventCrfAudit.getEventCrfVersionId() == auditBean.getEventCrfVersionId()) {
                            String oldValue = "";
                            String newValue = "";
                            if (eventCrfAudit.getAuditEventTypeId() == 12 || eventCrfAudit.getEntityName().equals("Status")) {
                                String getOld = eventCrfAudit.getOldValue();
                                if (getOld.equals("0"))
                                    oldValue = "invalid";
                                else if (getOld.equals("1"))
                                    oldValue = "available";
                                else if (getOld.equals("2"))
                                    oldValue = "unavailable";
                                else if (getOld.equals("3"))
                                    oldValue = "private";
                                else if (getOld.equals("4"))
                                    oldValue = "pending";
                                else if (getOld.equals("5"))
                                    oldValue = "removed";
                                else if (getOld.equals("6"))
                                    oldValue = "locked";
                                else if (getOld.equals("7"))
                                    oldValue = "auto-removed";
                                else {
                                    oldValue = getOld;
                                }
                            } else if (eventCrfAudit.getAuditEventTypeId() == 32) {
                                String getOld = eventCrfAudit.getOldValue();
                                if (getOld.equals("0"))
                                    oldValue = "FALSE";
                                else if (getOld.equals("1"))
                                    oldValue = "TRUE";
                                else {
                                    oldValue = getOld;
                                }
                            } else {
                                oldValue = eventCrfAudit.getOldValue();
                            }
                            if (eventCrfAudit.getAuditEventTypeId() == 12 || eventCrfAudit.getEntityName().equals("Status")) {
                                String getNew = eventCrfAudit.getNewValue();
                                if (getNew.equals("0"))
                                    newValue = "invalid";
                                else if (getNew.equals("1"))
                                    newValue = "available";
                                else if (getNew.equals("2"))
                                    newValue = "unavailable";
                                else if (getNew.equals("3"))
                                    newValue = "private";
                                else if (getNew.equals("4"))
                                    newValue = "pending";
                                else if (getNew.equals("5"))
                                    newValue = "removed";
                                else if (getNew.equals("6"))
                                    newValue = "locked";
                                else if (getNew.equals("7"))
                                    newValue = "auto-removed";
                                else {
                                    newValue = getNew;
                                }
                            } else if (eventCrfAudit.getAuditEventTypeId() == 32) {
                                String getNew = eventCrfAudit.getNewValue();
                                if (getNew.equals("0"))
                                    newValue = "FALSE";
                                else if (getNew.equals("1"))
                                    newValue = "TRUE";
                                else {
                                    newValue = getNew;
                                }
                            } else {
                                newValue = eventCrfAudit.getNewValue();
                            }
                            String ordinal = "";
                            if (eventCrfAudit.getOrdinal() != 0) {
                                ordinal = "(" + eventCrfAudit.getOrdinal() + ")";
                            } else if (eventCrfAudit.getOrdinal() == 0 && eventCrfAudit.getItemDataRepeatKey() != 0) {
                                ordinal = "(" + eventCrfAudit.getItemDataRepeatKey() + ")";
                            }
                            excelRow = new String[] { eventCrfAudit.getAuditEventTypeName(), dateTimeFormat(eventCrfAudit.getAuditDate()), eventCrfAudit.getUserName(), eventCrfAudit.getEntityName() + ordinal, oldValue, newValue };
                            for (int i = 0; i < excelRow.length; i++) {
                                label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                                excelSheet.addCell(label);
                            }
                            row++;
                            row++;
                        }
                        row++;
                    }
                    row++;
                }
                autoSizeColumns(excelSheet);
            }
        }
        workbook.write();
        workbook.close();
        session.setAttribute("subject", null);
        session.setAttribute("study", null);
        session.setAttribute("studySub", null);
        session.setAttribute("studyEventAudits", null);
        session.setAttribute("studySubjectAudits", null);
        session.setAttribute("events", null);
        session.setAttribute("eventCRFAudits", null);
        session.setAttribute("allDeletedEventCRFs", null);
    } catch (Exception e) {
        throw e;
    } finally {
    // proposed move session attributes here
    }
}