Search in sources :

Example 1 with NewCRFBean

use of org.akaza.openclinica.bean.admin.NewCRFBean in project OpenClinica by OpenClinica.

the class CreateCRFVersionServlet method processRequest.

@SuppressWarnings("unchecked")
@Override
public void processRequest() throws Exception {
    resetPanel();
    panel.setStudyInfoShown(true);
    CRFDAO cdao = new CRFDAO(sm.getDataSource());
    CRFVersionDAO vdao = new CRFVersionDAO(sm.getDataSource());
    EventDefinitionCRFDAO edao = new EventDefinitionCRFDAO(sm.getDataSource());
    FormProcessor fp = new FormProcessor(request);
    // checks which module the requests are from
    String module = fp.getString(MODULE);
    // keep the module in the session
    session.setAttribute(MODULE, module);
    request.setAttribute("xformEnabled", CoreResources.getField("xform.enabled"));
    String action = request.getParameter("action");
    CRFVersionBean version = (CRFVersionBean) session.getAttribute("version");
    if (StringUtil.isBlank(action)) {
        logger.debug("action is blank");
        request.setAttribute("version", version);
        forwardPage(Page.CREATE_CRF_VERSION);
    } else if ("confirm".equalsIgnoreCase(action)) {
        String dir = SQLInitServlet.getField("filePath");
        if (!new File(dir).exists()) {
            logger.debug("The filePath in datainfo.properties is invalid " + dir);
            addPageMessage(resword.getString("the_filepath_you_defined"));
            forwardPage(Page.CREATE_CRF_VERSION);
            // BWP 01/13/2009 >>
            return;
        // >>
        }
        // All the uploaded files will be saved in filePath/crf/original/
        String theDir = dir + "crf" + File.separator + "original" + File.separator;
        if (!new File(theDir).isDirectory()) {
            new File(theDir).mkdirs();
            logger.debug("Made the directory " + theDir);
        }
        // MultipartRequest multi = new MultipartRequest(request, theDir, 50 * 1024 * 1024);
        String tempFile = "";
        try {
            tempFile = uploadFile(theDir, version);
        } catch (CRFReadingException crfException) {
            Validator.addError(errors, "excel_file", crfException.getMessage());
            String msg = crfException.getMessage();
            request.setAttribute("formMessages", errors);
            forwardPage(Page.CREATE_CRF_VERSION);
            return;
        } catch (Exception e) {
            // 
            logger.warn("*** Found exception during file upload***");
            e.printStackTrace();
        }
        session.setAttribute("tempFileName", tempFile);
        // provided and/or not xls format
        if (errors.isEmpty()) {
            String s = ((NewCRFBean) session.getAttribute("nib")).getVersionName();
            if (s.length() > 255) {
                Validator.addError(errors, "excel_file", resword.getString("the_version_CRF_version_more_than_255"));
            } else if (s.length() <= 0) {
                Validator.addError(errors, "excel_file", resword.getString("the_VERSION_column_was_blank"));
            }
            version.setName(s);
            if (version.getCrfId() == 0) {
                version.setCrfId(fp.getInt("crfId"));
            }
            session.setAttribute("version", version);
        }
        if (!errors.isEmpty()) {
            logger.debug("has validation errors ");
            request.setAttribute("formMessages", errors);
            forwardPage(Page.CREATE_CRF_VERSION);
        } else {
            CRFBean crf = (CRFBean) cdao.findByPK(version.getCrfId());
            ArrayList versions = (ArrayList) vdao.findAllByCRF(crf.getId());
            for (int i = 0; i < versions.size(); i++) {
                CRFVersionBean version1 = (CRFVersionBean) versions.get(i);
                if (version.getName().equals(version1.getName())) {
                    // version already exists
                    logger.debug("Version already exists; owner or not:" + ub.getId() + "," + version1.getOwnerId());
                    if (ub.getId() != version1.getOwnerId()) {
                        // not owner
                        addPageMessage(respage.getString("CRF_version_try_upload_exists_database") + version1.getOwner().getName() + respage.getString("please_contact_owner_to_delete"));
                        forwardPage(Page.CREATE_CRF_VERSION);
                        return;
                    } else {
                        // owner,
                        ArrayList definitions = edao.findByDefaultVersion(version1.getId());
                        if (!definitions.isEmpty()) {
                            // used in
                            // definition
                            request.setAttribute("definitions", definitions);
                            forwardPage(Page.REMOVE_CRF_VERSION_DEF);
                            return;
                        } else {
                            // not used in definition
                            int previousVersionId = version1.getId();
                            version.setId(previousVersionId);
                            session.setAttribute("version", version);
                            session.setAttribute("previousVersionId", new Integer(previousVersionId));
                            forwardPage(Page.REMOVE_CRF_VERSION_CONFIRM);
                            return;
                        }
                    }
                }
            }
            // didn't find same version in the DB,let user upload the excel
            // file
            logger.debug("didn't find same version in the DB,let user upload the excel file.");
            // List excelErr =
            // ((ArrayList)request.getAttribute("excelErrors"));
            List excelErr = (ArrayList) session.getAttribute("excelErrors");
            logger.debug("excelErr.isEmpty()=" + excelErr.isEmpty());
            if (excelErr != null && excelErr.isEmpty()) {
                addPageMessage(resword.getString("congratulations_your_spreadsheet_no_errors"));
                forwardPage(Page.VIEW_SECTION_DATA_ENTRY_PREVIEW);
            } else {
                logger.debug("OpenClinicaException thrown, forwarding to CREATE_CRF_VERSION_CONFIRM.");
                forwardPage(Page.CREATE_CRF_VERSION_CONFIRM);
            }
            return;
        }
    } else if ("confirmsql".equalsIgnoreCase(action)) {
        NewCRFBean nib = (NewCRFBean) session.getAttribute("nib");
        if (nib != null && nib.getItemQueries() != null) {
            request.setAttribute("openQueries", nib.getItemQueries());
        } else {
            request.setAttribute("openQueries", new HashMap());
        }
        boolean canDelete = false;
        // check whether need to delete previous version
        Boolean deletePreviousVersion = (Boolean) session.getAttribute("deletePreviousVersion");
        Integer previousVersionId = (Integer) session.getAttribute("previousVersionId");
        if (deletePreviousVersion != null && deletePreviousVersion.equals(Boolean.TRUE) && previousVersionId != null && previousVersionId.intValue() > 0) {
            logger.debug("Need to delete previous version");
            // whether we can delete
            canDelete = canDeleteVersion(previousVersionId.intValue());
            if (!canDelete) {
                logger.debug("but cannot delete previous version");
                if (session.getAttribute("itemsHaveData") == null && session.getAttribute("eventsForVersion") == null) {
                    addPageMessage(respage.getString("you_are_not_owner_some_items_cannot_delete"));
                }
                if (session.getAttribute("itemsHaveData") == null) {
                    session.setAttribute("itemsHaveData", new ArrayList());
                }
                if (session.getAttribute("eventsForVersion") == null) {
                    session.setAttribute("eventsForVersion", new ArrayList());
                }
                forwardPage(Page.CREATE_CRF_VERSION_NODELETE);
                return;
            }
            ArrayList<ItemBean> nonSharedItems = (ArrayList<ItemBean>) vdao.findNotSharedItemsByVersion(previousVersionId.intValue());
            // htaycher: here is the trick we need to put in nib1.setItemQueries()
            // update statements for shared items and insert for nonShared that were just deleted 5927
            HashMap item_table_statements = new HashMap();
            ArrayList<String> temp = new ArrayList<String>(nonSharedItems.size());
            for (ItemBean item : nonSharedItems) {
                temp.add(item.getName());
                item_table_statements.put(item.getName(), nib.getBackupItemQueries().get(item.getName()));
            }
            for (String item_name : (Set<String>) nib.getItemQueries().keySet()) {
                // check if item shared
                if (!temp.contains(item_name)) {
                    item_table_statements.put(item_name, nib.getItemQueries().get(item_name));
                }
            }
            // statements to run
            if (!nonSharedItems.isEmpty()) {
                request.setAttribute("openQueries", item_table_statements);
            }
            // htaycher: put all statements in
            nib.setItemQueries(item_table_statements);
            session.setAttribute("nib", nib);
        }
        // submit
        logger.debug("commit sql");
        NewCRFBean nib1 = (NewCRFBean) session.getAttribute("nib");
        if (nib1 != null) {
            try {
                if (canDelete) {
                    nib1.deleteInsertToDB();
                } else {
                    nib1.insertToDB();
                }
                request.setAttribute("queries", nib1.getQueries());
                // YW << for add a link to "View CRF Version Data Entry".
                // For this purpose, CRFVersion id is needed.
                // So the latest CRFVersion Id of A CRF Id is it.
                CRFVersionDAO cvdao = new CRFVersionDAO(sm.getDataSource());
                ArrayList crfvbeans = new ArrayList();
                logger.debug("CRF-ID [" + version.getCrfId() + "]");
                int crfVersionId = 0;
                String versionOID = null;
                if (version.getCrfId() != 0) {
                    crfvbeans = cvdao.findAllByCRFId(version.getCrfId());
                    CRFVersionBean cvbean = (CRFVersionBean) crfvbeans.get(crfvbeans.size() - 1);
                    crfVersionId = cvbean.getId();
                    versionOID = cvbean.getOid();
                    for (Iterator iter = crfvbeans.iterator(); iter.hasNext(); ) {
                        cvbean = (CRFVersionBean) iter.next();
                        if (crfVersionId < cvbean.getId()) {
                            crfVersionId = cvbean.getId();
                        }
                    }
                }
                // Not needed; crfVersionId will be autoboxed in Java 5
                // this was added for the old CVS java compiler
                Integer cfvID = new Integer(crfVersionId);
                if (cfvID == 0) {
                    cfvID = cvdao.findCRFVersionId(nib1.getCrfId(), nib1.getVersionName());
                }
                CRFVersionBean finalVersion = (CRFVersionBean) cvdao.findByPK(cfvID);
                version.setCrfId(nib1.getCrfId());
                version.setOid(finalVersion.getOid());
                CRFBean crfBean = (CRFBean) cdao.findByPK(version.getCrfId());
                crfBean.setUpdatedDate(version.getCreatedDate());
                crfBean.setUpdater(ub);
                cdao.update(crfBean);
                // workaround to get a correct file name below, tbh 06/2008
                request.setAttribute("crfVersionId", cfvID);
                // YW >>
                // return those properties to initial values
                session.removeAttribute("version");
                session.removeAttribute("eventsForVersion");
                session.removeAttribute("itemsHaveData");
                session.removeAttribute("nib");
                session.removeAttribute("deletePreviousVersion");
                session.removeAttribute("previousVersionId");
                // save new version spreadsheet
                String tempFile = (String) session.getAttribute("tempFileName");
                if (tempFile != null) {
                    logger.debug("*** ^^^ *** saving new version spreadsheet" + tempFile);
                    try {
                        String dir = SQLInitServlet.getField("filePath");
                        File f = new File(dir + "crf" + File.separator + "original" + File.separator + tempFile);
                        // check to see whether crf/new/ folder exists
                        // inside, if not,
                        // creates
                        // the crf/new/ folder
                        String finalDir = dir + "crf" + File.separator + "new" + File.separator;
                        if (!new File(finalDir).isDirectory()) {
                            logger.debug("need to create folder for excel files" + finalDir);
                            new File(finalDir).mkdirs();
                        }
                        // String newFile = version.getCrfId() +
                        // version.getName() + ".xls";
                        String newFile = version.getCrfId() + version.getOid() + ".xls";
                        logger.debug("*** ^^^ *** new file: " + newFile);
                        File nf = new File(finalDir + newFile);
                        logger.debug("copying old file " + f.getName() + " to new file " + nf.getName());
                        copy(f, nf);
                    // ?
                    } catch (IOException ie) {
                        logger.debug("==============");
                        addPageMessage(respage.getString("CRF_version_spreadsheet_could_not_saved_contact"));
                    }
                }
                session.removeAttribute("tempFileName");
                session.removeAttribute(MODULE);
                session.removeAttribute("excelErrors");
                session.removeAttribute("htmlTab");
                forwardPage(Page.CREATE_CRF_VERSION_DONE);
            } catch (OpenClinicaException pe) {
                logger.debug("--------------");
                session.setAttribute("excelErrors", nib1.getErrors());
                // request.setAttribute("excelErrors", nib1.getErrors());
                forwardPage(Page.CREATE_CRF_VERSION_ERROR);
            }
        } else {
            forwardPage(Page.CREATE_CRF_VERSION);
        }
    } else if ("delete".equalsIgnoreCase(action)) {
        logger.debug("user wants to delete previous version");
        List excelErr = (ArrayList) session.getAttribute("excelErrors");
        logger.debug("for overwrite CRF version, excelErr.isEmpty()=" + excelErr.isEmpty());
        if (excelErr != null && excelErr.isEmpty()) {
            addPageMessage(resword.getString("congratulations_your_spreadsheet_no_errors"));
            // should be moved to excelErr != null block
            session.setAttribute("deletePreviousVersion", Boolean.TRUE);
            forwardPage(Page.VIEW_SECTION_DATA_ENTRY_PREVIEW);
        } else {
            // should be moved to excelErr != null
            session.setAttribute("deletePreviousVersion", Boolean.FALSE);
            // block
            logger.debug("OpenClinicaException thrown, forwarding to CREATE_CRF_VERSION_CONFIRM.");
            forwardPage(Page.CREATE_CRF_VERSION_CONFIRM);
        }
    }
}
Also used : ItemBean(org.akaza.openclinica.bean.submit.ItemBean) Set(java.util.Set) HashMap(java.util.HashMap) ArrayList(java.util.ArrayList) CRFReadingException(org.akaza.openclinica.exception.CRFReadingException) OpenClinicaException(org.akaza.openclinica.exception.OpenClinicaException) Iterator(java.util.Iterator) ArrayList(java.util.ArrayList) List(java.util.List) EventDefinitionCRFDAO(org.akaza.openclinica.dao.managestudy.EventDefinitionCRFDAO) EventCRFDAO(org.akaza.openclinica.dao.submit.EventCRFDAO) CRFDAO(org.akaza.openclinica.dao.admin.CRFDAO) CRFVersionDAO(org.akaza.openclinica.dao.submit.CRFVersionDAO) FormProcessor(org.akaza.openclinica.control.form.FormProcessor) EventDefinitionCRFDAO(org.akaza.openclinica.dao.managestudy.EventDefinitionCRFDAO) IOException(java.io.IOException) OpenClinicaException(org.akaza.openclinica.exception.OpenClinicaException) InsufficientPermissionException(org.akaza.openclinica.web.InsufficientPermissionException) CRFReadingException(org.akaza.openclinica.exception.CRFReadingException) IOException(java.io.IOException) NewCRFBean(org.akaza.openclinica.bean.admin.NewCRFBean) CRFBean(org.akaza.openclinica.bean.admin.CRFBean) NewCRFBean(org.akaza.openclinica.bean.admin.NewCRFBean) CRFVersionBean(org.akaza.openclinica.bean.submit.CRFVersionBean) File(java.io.File)

Example 2 with NewCRFBean

use of org.akaza.openclinica.bean.admin.NewCRFBean in project OpenClinica by OpenClinica.

the class DeleteCRFVersionServlet method processRequest.

@Override
public void processRequest() throws Exception {
    FormProcessor fp = new FormProcessor(request);
    int versionId = fp.getInt(VERSION_ID, true);
    String action = request.getParameter("action");
    if (versionId == 0) {
        addPageMessage(respage.getString("please_choose_a_CRF_version_to_delete"));
        forwardPage(Page.CRF_LIST_SERVLET);
    } else {
        CRFVersionDAO cvdao = new CRFVersionDAO(sm.getDataSource());
        CRFDAO cdao = new CRFDAO(sm.getDataSource());
        EventDefinitionCRFDAO edcdao = new EventDefinitionCRFDAO(sm.getDataSource());
        StudyEventDefinitionDAO sedDao = new StudyEventDefinitionDAO(sm.getDataSource());
        StudyEventDAO seDao = new StudyEventDAO(sm.getDataSource());
        ItemDataDAO iddao = new ItemDataDAO(sm.getDataSource());
        EventCRFDAO ecdao = new EventCRFDAO(sm.getDataSource());
        StudySubjectDAO ssdao = new StudySubjectDAO(sm.getDataSource());
        CRFVersionBean version = (CRFVersionBean) cvdao.findByPK(versionId);
        // find definitions using this version
        ArrayList definitions = edcdao.findByDefaultVersion(version.getId());
        for (Object edcBean : definitions) {
            StudyEventDefinitionBean sedBean = (StudyEventDefinitionBean) sedDao.findByPK(((EventDefinitionCRFBean) edcBean).getStudyEventDefinitionId());
            ((EventDefinitionCRFBean) edcBean).setEventName(sedBean.getName());
        }
        // find event crfs using this version
        ArrayList<ItemDataBean> idBeans = iddao.findByCRFVersion(version);
        ArrayList<EventCRFBean> eCRFs = ecdao.findAllByCRF(version.getCrfId());
        for (EventCRFBean eCRF : eCRFs) {
            StudySubjectBean ssBean = (StudySubjectBean) ssdao.findByPK(eCRF.getStudySubjectId());
            eCRF.setStudySubject(ssBean);
            StudyEventBean seBean = (StudyEventBean) seDao.findByPK(eCRF.getStudyEventId());
            StudyEventDefinitionBean sedBean = (StudyEventDefinitionBean) sedDao.findByPK(seBean.getStudyEventDefinitionId());
            seBean.setStudyEventDefinition(sedBean);
            eCRF.setStudyEvent(seBean);
        }
        ArrayList eventCRFs = ecdao.findAllByCRFVersion(versionId);
        boolean canDelete = true;
        if (!definitions.isEmpty()) {
            // used in definition
            canDelete = false;
            request.setAttribute("definitions", definitions);
            addPageMessage(respage.getString("this_CRF_version") + " " + version.getName() + respage.getString("has_associated_study_events_definitions_cannot_delete"));
        } else if (!idBeans.isEmpty()) {
            canDelete = false;
            request.setAttribute("eventCRFs", eCRFs);
            request.setAttribute("itemDataForVersion", idBeans);
            addPageMessage(respage.getString("this_CRF_version") + " " + version.getName() + respage.getString("has_associated_item_data_cannot_delete"));
        } else if (!eventCRFs.isEmpty()) {
            canDelete = false;
            request.setAttribute("eventsForVersion", eventCRFs);
            addPageMessage(respage.getString("this_CRF_version") + " " + version.getName() + respage.getString("has_associated_study_events_cannot_delete"));
        }
        if ("confirm".equalsIgnoreCase(action)) {
            request.setAttribute(VERSION_TO_DELETE, version);
            forwardPage(Page.DELETE_CRF_VERSION);
        } else {
            // submit
            if (canDelete) {
                ArrayList items = cvdao.findNotSharedItemsByVersion(versionId);
                NewCRFBean nib = new NewCRFBean(sm.getDataSource(), version.getCrfId());
                nib.setDeleteQueries(cvdao.generateDeleteQueries(versionId, items));
                nib.deleteFromDB();
                addPageMessage(respage.getString("the_CRF_version_has_been_deleted_succesfully"));
            } else {
                addPageMessage(respage.getString("the_CRF_version_cannot_be_deleted"));
            }
            forwardPage(Page.CRF_LIST_SERVLET);
        }
    }
}
Also used : EventDefinitionCRFDAO(org.akaza.openclinica.dao.managestudy.EventDefinitionCRFDAO) EventCRFDAO(org.akaza.openclinica.dao.submit.EventCRFDAO) CRFDAO(org.akaza.openclinica.dao.admin.CRFDAO) CRFVersionDAO(org.akaza.openclinica.dao.submit.CRFVersionDAO) FormProcessor(org.akaza.openclinica.control.form.FormProcessor) ArrayList(java.util.ArrayList) StudyEventDefinitionBean(org.akaza.openclinica.bean.managestudy.StudyEventDefinitionBean) EventDefinitionCRFDAO(org.akaza.openclinica.dao.managestudy.EventDefinitionCRFDAO) StudyEventBean(org.akaza.openclinica.bean.managestudy.StudyEventBean) StudySubjectDAO(org.akaza.openclinica.dao.managestudy.StudySubjectDAO) ItemDataDAO(org.akaza.openclinica.dao.submit.ItemDataDAO) EventCRFBean(org.akaza.openclinica.bean.submit.EventCRFBean) NewCRFBean(org.akaza.openclinica.bean.admin.NewCRFBean) StudyEventDefinitionDAO(org.akaza.openclinica.dao.managestudy.StudyEventDefinitionDAO) StudySubjectBean(org.akaza.openclinica.bean.managestudy.StudySubjectBean) ItemDataBean(org.akaza.openclinica.bean.submit.ItemDataBean) StudyEventDAO(org.akaza.openclinica.dao.managestudy.StudyEventDAO) CRFVersionBean(org.akaza.openclinica.bean.submit.CRFVersionBean) EventDefinitionCRFBean(org.akaza.openclinica.bean.managestudy.EventDefinitionCRFBean) EventCRFDAO(org.akaza.openclinica.dao.submit.EventCRFDAO)

Example 3 with NewCRFBean

use of org.akaza.openclinica.bean.admin.NewCRFBean in project OpenClinica by OpenClinica.

the class CreateCRFVersionServlet method canDeleteVersion.

/**
 * Checks whether the version can be deleted
 *
 * @param previousVersionId
 * @return
 */
private boolean canDeleteVersion(int previousVersionId) {
    CRFVersionDAO cdao = new CRFVersionDAO(sm.getDataSource());
    ArrayList items = null;
    ArrayList itemsHaveData = new ArrayList();
    // boolean isItemUsedByOtherVersion =
    // cdao.isItemUsedByOtherVersion(previousVersionId);
    // if (isItemUsedByOtherVersion) {
    // ArrayList itemsUsedByOtherVersion = (ArrayList)
    // cdao.findItemUsedByOtherVersion(previousVersionId);
    // session.setAttribute("itemsUsedByOtherVersion",itemsUsedByOtherVersion);
    // return false;
    EventCRFDAO ecdao = new EventCRFDAO(sm.getDataSource());
    ArrayList events = ecdao.findAllByCRFVersion(previousVersionId);
    if (!events.isEmpty()) {
        session.setAttribute("eventsForVersion", events);
        return false;
    }
    items = cdao.findNotSharedItemsByVersion(previousVersionId);
    for (int i = 0; i < items.size(); i++) {
        ItemBean item = (ItemBean) items.get(i);
        if (ub.getId() != item.getOwner().getId()) {
            logger.debug("not owner" + item.getOwner().getId() + "<>" + ub.getId());
            return false;
        }
        if (cdao.hasItemData(item.getId())) {
            itemsHaveData.add(item);
            logger.debug("item has data");
            session.setAttribute("itemsHaveData", itemsHaveData);
            return false;
        }
    }
    // user is the owner and item not have data,
    // delete previous version with non-shared items
    NewCRFBean nib = (NewCRFBean) session.getAttribute("nib");
    nib.setDeleteQueries(cdao.generateDeleteQueries(previousVersionId, items));
    session.setAttribute("nib", nib);
    return true;
}
Also used : ItemBean(org.akaza.openclinica.bean.submit.ItemBean) NewCRFBean(org.akaza.openclinica.bean.admin.NewCRFBean) CRFVersionDAO(org.akaza.openclinica.dao.submit.CRFVersionDAO) ArrayList(java.util.ArrayList) EventCRFDAO(org.akaza.openclinica.dao.submit.EventCRFDAO)

Example 4 with NewCRFBean

use of org.akaza.openclinica.bean.admin.NewCRFBean in project OpenClinica by OpenClinica.

the class CreateCRFVersionServlet method uploadFile.

/**
 * Uploads the excel version file
 *
 * @param version
 * @throws Exception
 */
public String uploadFile(String theDir, CRFVersionBean version) throws Exception {
    List<File> theFiles = uploadHelper.returnFiles(request, context, theDir);
    // Enumeration files = multi.getFileNames();
    errors.remove("excel_file");
    String tempFile = null;
    for (File f : theFiles) {
        // File f = multi.getFile(name);
        if (f == null || f.getName() == null) {
            logger.debug("file is empty.");
            Validator.addError(errors, "excel_file", resword.getString("you_have_to_provide_spreadsheet"));
            session.setAttribute("version", version);
            return tempFile;
        } else if (f.getName().indexOf(".xls") < 0 && f.getName().indexOf(".XLS") < 0) {
            logger.debug("file name:" + f.getName());
            Validator.addError(errors, "excel_file", respage.getString("file_you_uploaded_not_seem_excel_spreadsheet"));
            session.setAttribute("version", version);
            return tempFile;
        } else {
            logger.debug("file name:" + f.getName());
            tempFile = f.getName();
            // create the inputstream here, so that it can be enclosed in a
            // try/finally block and closed :: BWP, 06/08/2007
            FileInputStream inStream = null;
            FileInputStream inStreamClassic = null;
            SpreadSheetTableRepeating htab = null;
            SpreadSheetTableClassic sstc = null;
            // create newCRFBean here
            NewCRFBean nib = null;
            try {
                inStream = new FileInputStream(theDir + tempFile);
                // *** now change the code here to generate sstable, tbh
                // 06/07
                htab = new SpreadSheetTableRepeating(inStream, ub, // FileInputStream(theDir + tempFile), ub,
                version.getName(), locale, currentStudy.getId());
                htab.setMeasurementUnitDao((MeasurementUnitDao) SpringServletAccess.getApplicationContext(context).getBean("measurementUnitDao"));
                if (!htab.isRepeating()) {
                    inStreamClassic = new FileInputStream(theDir + tempFile);
                    sstc = new SpreadSheetTableClassic(inStreamClassic, ub, version.getName(), locale, currentStudy.getId());
                    sstc.setMeasurementUnitDao((MeasurementUnitDao) SpringServletAccess.getApplicationContext(context).getBean("measurementUnitDao"));
                }
                if (htab.isRepeating()) {
                    htab.setCrfId(version.getCrfId());
                    // not the best place for this but for now...
                    session.setAttribute("new_table", "y");
                } else {
                    sstc.setCrfId(version.getCrfId());
                }
                if (htab.isRepeating()) {
                    nib = htab.toNewCRF(sm.getDataSource(), respage);
                } else {
                    nib = sstc.toNewCRF(sm.getDataSource(), respage);
                }
                // bwp; 2/28/07; updated 6/11/07;
                // This object is created to pull preview information out of
                // the
                // spreadsheet
                HSSFWorkbook workbook = null;
                FileInputStream inputStream = null;
                try {
                    inputStream = new FileInputStream(theDir + tempFile);
                    workbook = new HSSFWorkbook(inputStream);
                    // Store the Sections, Items, Groups, and CRF name and
                    // version information
                    // so they can be displayed in a preview. The Map
                    // consists of the
                    // names "sections," "items," "groups," and "crf_info"
                    // as keys, each of which point
                    // to a Map containing data on those CRF sections.
                    // Check if it's the old template
                    Preview preview;
                    if (htab.isRepeating()) {
                        // the preview uses date formatting with default
                        // values in date fields: yyyy-MM-dd
                        preview = new SpreadsheetPreviewNw();
                    } else {
                        preview = new SpreadsheetPreview();
                    }
                    session.setAttribute("preview_crf", preview.createCrfMetaObject(workbook));
                } catch (Exception exc) {
                    // opening the stream could
                    // throw FileNotFoundException
                    exc.printStackTrace();
                    String message = resword.getString("the_application_encountered_a_problem_uploading_CRF");
                    logger.debug(message + ": " + exc.getMessage());
                    this.addPageMessage(message);
                } finally {
                    if (inputStream != null) {
                        try {
                            inputStream.close();
                        } catch (IOException io) {
                        // ignore this close()-related exception
                        }
                    }
                }
                ArrayList ibs = isItemSame(nib.getItems(), version);
                if (!ibs.isEmpty()) {
                    ArrayList warnings = new ArrayList();
                    warnings.add(resexception.getString("you_may_not_modify_items"));
                    for (int i = 0; i < ibs.size(); i++) {
                        ItemBean ib = (ItemBean) ibs.get(i);
                        if (ib.getOwner().getId() == ub.getId()) {
                            warnings.add(resword.getString("the_item") + " '" + ib.getName() + "' " + resexception.getString("in_your_spreadsheet_already_exists") + ib.getDescription() + "), DATA_TYPE(" + ib.getDataType().getName() + "), UNITS(" + ib.getUnits() + "), " + resword.getString("and_or") + " PHI_STATUS(" + ib.isPhiStatus() + "). UNITS " + resword.getString("and") + " DATA_TYPE(PDATE to DATE) " + resexception.getString("will_not_be_changed_if") + " PHI, DESCRIPTION, DATA_TYPE from PDATE to DATE " + resexception.getString("will_be_changed_if_you_continue"));
                        } else {
                            warnings.add(resword.getString("the_item") + " '" + ib.getName() + "' " + resexception.getString("in_your_spreadsheet_already_exists") + ib.getDescription() + "), DATA_TYPE(" + ib.getDataType().getName() + "), UNITS(" + ib.getUnits() + "), " + resword.getString("and_or") + " PHI_STATUS(" + ib.isPhiStatus() + "). " + resexception.getString("these_field_cannot_be_modified_because_not_owner"));
                        }
                        request.setAttribute("warnings", warnings);
                    }
                }
                ItemBean ib = isResponseValid(nib.getItems(), version);
                if (ib != null) {
                    nib.getErrors().add(resword.getString("the_item") + ": " + ib.getName() + " " + resexception.getString("in_your_spreadsheet_already_exits_in_DB"));
                }
            } catch (IOException io) {
                logger.warn("Opening up the Excel file caused an error. the error message is: " + io.getMessage());
            } finally {
                if (inStream != null) {
                    try {
                        inStream.close();
                    } catch (IOException ioe) {
                    }
                }
                if (inStreamClassic != null) {
                    try {
                        inStreamClassic.close();
                    } catch (IOException ioe) {
                    }
                }
            }
            // request.setAttribute("excelErrors", .getErrors());
            session.setAttribute("excelErrors", nib.getErrors());
            session.setAttribute("htmlTable", nib.getHtmlTable());
            session.setAttribute("nib", nib);
        }
    }
    return tempFile;
}
Also used : ItemBean(org.akaza.openclinica.bean.submit.ItemBean) MeasurementUnitDao(org.akaza.openclinica.dao.hibernate.MeasurementUnitDao) ArrayList(java.util.ArrayList) IOException(java.io.IOException) FileInputStream(java.io.FileInputStream) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook) OpenClinicaException(org.akaza.openclinica.exception.OpenClinicaException) InsufficientPermissionException(org.akaza.openclinica.web.InsufficientPermissionException) CRFReadingException(org.akaza.openclinica.exception.CRFReadingException) IOException(java.io.IOException) NewCRFBean(org.akaza.openclinica.bean.admin.NewCRFBean) File(java.io.File)

Example 5 with NewCRFBean

use of org.akaza.openclinica.bean.admin.NewCRFBean in project OpenClinica by OpenClinica.

the class SpreadSheetTableClassic method toNewCRF.

public NewCRFBean toNewCRF(javax.sql.DataSource ds, ResourceBundle resPageMsg) throws IOException, CRFReadingException {
    String dbName = SQLInitServlet.getDBName();
    NewCRFBean ncrf = new NewCRFBean(ds, crfId);
    // set crf id
    ncrf.setCrfId(crfId);
    StringBuffer buf = new StringBuffer();
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numSheets = wb.getNumberOfSheets();
    ArrayList queries = new ArrayList();
    ArrayList errors = new ArrayList();
    ArrayList repeats = new ArrayList();
    HashMap tableNames = new HashMap();
    HashMap items = new HashMap();
    String pVersion = "";
    String pVerDesc = "";
    int parentId = 0;
    // default is ST(String) type
    int dataTypeId = 5;
    HashMap itemCheck = ncrf.getItemNames();
    HashMap GroupCheck = ncrf.getItemGroupNames();
    HashMap openQueries = new LinkedHashMap();
    // save all the item
    HashMap backupItemQueries = new LinkedHashMap();
    // queries if
    // deleting item happens
    // check for dupes, also
    ArrayList secNames = new ArrayList();
    // YW 1-30-2008
    HashMap<String, String> allItems = new HashMap<String, String>();
    ArrayList<String> itemGroupOids = new ArrayList<String>();
    ArrayList<String> itemOids = new ArrayList<String>();
    CRFDAO cdao = new CRFDAO(ds);
    CRFBean crf = (CRFBean) cdao.findByPK(crfId);
    ItemDataDAO iddao = new ItemDataDAO(ds);
    ItemDAO idao = new ItemDAO(ds);
    CRFVersionDAO cvdao = new CRFVersionDAO(ds);
    ItemGroupDAO itemGroupDao = new ItemGroupDAO(ds);
    int validSheetNum = 0;
    for (int j = 0; j < numSheets; j++) {
        // sheetIndex);
        HSSFSheet sheet = wb.getSheetAt(j);
        String sheetName = wb.getSheetName(j);
        if (sheetName.equalsIgnoreCase("CRF") || sheetName.equalsIgnoreCase("Sections") || sheetName.equalsIgnoreCase("Items")) {
            validSheetNum++;
        }
    }
    if (validSheetNum != 3) {
        // errors.add("The excel spreadsheet doesn't have required valid
        // worksheets. Please check whether it contains" +
        // " sheets of CRF, Sections and Items.");
        errors.add(resPageMsg.getString("excel_not_have_valid_worksheet"));
    }
    // 7/30
    for (int j = 0; j < numSheets; j++) {
        // sheetIndex);
        HSSFSheet sheet = wb.getSheetAt(j);
        String sheetName = wb.getSheetName(j);
        if (sheetName.equalsIgnoreCase("Instructions")) {
        // totally ignore instructions
        } else {
            /*
                 * current strategem: build out the queries by hand and revisit
                 * this as part of the data loading module. We begin to check
                 * for errors here and look for blank cells where there should
                 * be data, tbh, 7/28
                 */
            int numRows = sheet.getPhysicalNumberOfRows();
            int lastNumRow = sheet.getLastRowNum();
            // logger.info("PhysicalNumberOfRows" +
            // sheet.getPhysicalNumberOfRows());
            logger.info("PhysicalNumberOfRows" + sheet.getPhysicalNumberOfRows());
            // logger.info("LastRowNum()" + sheet.getLastRowNum());
            String secName = "";
            String page = "";
            // records all the
            ArrayList resNames = new ArrayList();
            // response_labels
            HashMap htmlErrors = new HashMap();
            // the above two need to persist across mult. queries,
            // and they should be created FIRST anyway, since instrument is
            // first
            // also need to add to VERSIONING_MAP, tbh, 6-6-3
            // try to count how many blank rows, if 5 concective blank rows
            // found, stop reading
            int blankRowCount = 0;
            if (sheetName.equalsIgnoreCase("Items")) {
                logger.info("read an item in sheet" + sheetName);
                Map labelWithType = new HashMap<String, String>();
                // let's insert the default group first
                ItemGroupBean defaultGroup = new ItemGroupBean();
                defaultGroup.setName("Ungrouped");
                defaultGroup.setCrfId(crfId);
                defaultGroup.setStatus(Status.AVAILABLE);
                // Create oid for Item Group
                String defaultGroupOid = itemGroupDao.getValidOid(defaultGroup, crfName, defaultGroup.getName(), itemGroupOids);
                itemGroupOids.add(defaultGroupOid);
                String defaultSql = "";
                if (dbName.equals("oracle")) {
                    /*defaultSql =
                            "INSERT INTO ITEM_GROUP ( " + "name, crf_id, status_id, date_created ,owner_id,oc_oid)" + "VALUES ('" + defaultGroup.getName()
                                + "', " + defaultGroup.getCrfId() + "," + defaultGroup.getStatus().getId() + "," + "sysdate," + ub.getId() + ",'"
                                + defaultGroupOid + "')";*/
                    defaultSql = "INSERT INTO ITEM_GROUP ( name, crf_id, status_id, date_created ,owner_id,oc_oid)" + " VALUES (?, ?, ?,sysdate, ?, ?)";
                } else {
                    /* defaultSql =
                            "INSERT INTO ITEM_GROUP ( " + "name, crf_id, status_id, date_created ,owner_id,oc_oid)" + "VALUES ('" + defaultGroup.getName()
                                + "', " + defaultGroup.getCrfId() + "," + defaultGroup.getStatus().getId() + "," + "now()," + ub.getId() + ",'"
                                + defaultGroupOid + "')";*/
                    defaultSql = "INSERT INTO ITEM_GROUP ( name, crf_id, status_id, date_created ,owner_id,oc_oid)" + " VALUES (?, ?, ?,now(), ?, ?)";
                }
                if (!GroupCheck.containsKey("Ungrouped")) {
                    ArrayList<SqlParameter> sqlParameters = new ArrayList<>();
                    sqlParameters.add(new SqlParameter(defaultGroup.getName()));
                    sqlParameters.add(new SqlParameter(defaultGroup.getCrfId().toString(), JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(defaultGroup.getStatus().getId() + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(ub.getId() + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(defaultGroupOid + "", JDBCType.INTEGER));
                    QueryObject qo = new QueryObject();
                    qo.setSql(defaultSql);
                    qo.setSqlParameters(sqlParameters);
                    queries.add(qo);
                }
                // Adding itemnames for further use
                HashMap itemNames = new HashMap();
                for (int k = 1; k < numRows; k++) {
                    HSSFCell cell = sheet.getRow(k).getCell((short) 0);
                    String itemName = getValue(cell);
                    itemName = itemName.replaceAll("<[^>]*>", "");
                    itemNames.put(k, itemName);
                }
                for (int k = 1; k < numRows; k++) {
                    // logger.info("hit row "+k);
                    if (blankRowCount == 5) {
                        logger.info("hit end of the row ");
                        break;
                    }
                    if (sheet.getRow(k) == null) {
                        blankRowCount++;
                        continue;
                    }
                    HSSFCell cell = sheet.getRow(k).getCell((short) 0);
                    String itemName = getValue(cell);
                    itemName = itemName.replaceAll("<[^>]*>", "");
                    // regexp to make sure it is all word characters, '\w+' in regexp terms
                    if (!Utils.isMatchingRegexp(itemName, "\\w+")) {
                        // different item error to go here
                        errors.add(resPageMsg.getString("item_name_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("you_can_only_use_letters_or_numbers"));
                        htmlErrors.put(j + "," + k + ",0", resPageMsg.getString("INVALID_FIELD"));
                    }
                    if (StringUtil.isBlank(itemName)) {
                        // errors.add("The ITEM_NAME column was blank at row
                        // " + k + ", Items worksheet.");
                        // htmlErrors.put(j + "," + k + ",0", "REQUIRED
                        // FIELD");
                        errors.add(resPageMsg.getString("item_name_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". ");
                        htmlErrors.put(j + "," + k + ",0", resPageMsg.getString("required_field"));
                    }
                    if (itemName != null && itemName.length() > 255) {
                        errors.add(resPageMsg.getString("item_name_length_error"));
                    }
                    if (repeats.contains(itemName)) {
                        // errors.add("A duplicate ITEM_NAME of " + itemName
                        // + " was detected at row " + k
                        // + ", Items worksheet.");
                        // htmlErrors.put(j + "," + k + ",0", "DUPLICATE
                        // FIELD");
                        errors.add(resPageMsg.getString("duplicate") + " " + resPageMsg.getString("item_name_column") + " " + itemName + " " + resPageMsg.getString("was_detected_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",0", resPageMsg.getString("required_field"));
                    } else if (itemName.length() > 0) {
                        allItems.put(itemName, "Ungrouped");
                    }
                    repeats.add(itemName);
                    cell = sheet.getRow(k).getCell((short) 1);
                    String descLabel = getValue(cell);
                    descLabel = descLabel.replaceAll("<[^>]*>", "");
                    if (StringUtil.isBlank(descLabel)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DESCRIPTION_LABEL_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + "," + resPageMsg.getString("items_worksheet") + ".");
                        // errors.add("The DESCRIPTION_LABEL column was
                        // blank at row " + k + "," +
                        // resPageMsg.getString("items_worksheet") +".");
                        htmlErrors.put(j + "," + k + ",1", resPageMsg.getString("required_field"));
                    }
                    if (descLabel != null && descLabel.length() > 4000) {
                        errors.add(resPageMsg.getString("item_desc_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 2);
                    String leftItemText = getValue(cell);
                    if (leftItemText != null && leftItemText.length() > 4000) {
                        errors.add(resPageMsg.getString("left_item_length_error"));
                    }
                    // Commented out to resolve issue-2413
                    // if (StringUtil.isBlank(leftItemText)) {
                    // errors.add(resPageMsg.getString("the") + " " +
                    // resPageMsg.getString("LEFT_ITEM_TEXT_column") + " "
                    // + resPageMsg.getString("was_blank_at_row") + k + ","
                    // + resPageMsg.getString("items_worksheet") + ".");
                    // htmlErrors.put(j + "," + k + ",2",
                    // resPageMsg.getString("required_field"));
                    // }
                    cell = sheet.getRow(k).getCell((short) 3);
                    String unit = getValue(cell).trim();
                    if (unit != null && unit.length() > 0) {
                        String muSql = "";
                        if (this.existingUnits.size() > 0) {
                        } else {
                            this.existingUnits = this.measurementUnitDao.findAllNamesInUpperCase();
                            if (this.existingUnits == null) {
                                this.existingUnits = new TreeSet<String>();
                            }
                        }
                        if (this.existingOIDs.size() > 0) {
                        } else {
                            this.existingOIDs = this.measurementUnitDao.findAllOIDs();
                            if (this.existingOIDs == null) {
                                this.existingOIDs = new TreeSet<String>();
                            }
                        }
                        if (this.existingUnits.contains(unit.toUpperCase())) {
                            this.logger.error("unit=" + unit + " existed.");
                        } else {
                            String oid = "";
                            try {
                                oid = new MeasurementUnitOidGenerator().generateOidNoValidation(unit);
                            } catch (Exception e) {
                                throw new RuntimeException("CANNOT GENERATE OID");
                            }
                            if (this.existingOIDs.contains(oid)) {
                                if (oid.length() > 40) {
                                    oid = oid.substring(0, 35);
                                }
                                oid = new MeasurementUnitOidGenerator().randomizeOid(oid);
                            }
                            this.existingOIDs.add(oid);
                            this.existingUnits.add(unit.toUpperCase());
                            muSql = this.getMUInsertSqlParameters();
                            ArrayList<SqlParameter> sqlParameters = new ArrayList<>();
                            sqlParameters.add(new SqlParameter(oid));
                            sqlParameters.add(new SqlParameter(unit));
                            QueryObject qo = new QueryObject();
                            qo.setSql(muSql);
                            qo.setSqlParameters(sqlParameters);
                            queries.add(qo);
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 4);
                    String rightItemText = getValue(cell);
                    if (rightItemText != null && rightItemText.length() > 2000) {
                        errors.add(resPageMsg.getString("right_item_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 5);
                    if (cell != null) {
                        secName = getValue(cell);
                        secName = secName.replaceAll("<[^>]*>", "");
                    }
                    if (secName != null && secName.length() > 2000) {
                        errors.add(resPageMsg.getString("section_label_length_error"));
                    }
                    if (!secNames.contains(secName)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SECTION_LABEL_column") + " " + resPageMsg.getString("not_valid_section_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("check_to_see_that_there_is_valid_LABEL"));
                        htmlErrors.put(j + "," + k + ",5", resPageMsg.getString("NOT_A_VALID_LABEL"));
                    }
                    cell = sheet.getRow(k).getCell((short) 6);
                    String header = getValue(cell);
                    if (header != null && header.length() > 2000) {
                        errors.add(resPageMsg.getString("item_header_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 7);
                    String subHeader = getValue(cell);
                    if (subHeader != null && subHeader.length() > 240) {
                        errors.add(resPageMsg.getString("item_subheader_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 8);
                    String parentItem = getValue(cell);
                    parentItem = parentItem.replaceAll("<[^>]*>", "");
                    // Checking for a valid paren item name
                    if (!StringUtil.isBlank(parentItem)) {
                        if (!itemNames.containsValue(parentItem)) {
                            errors.add("the Parent item specified on row " + k + " does not exist in the CRF template. Please update the value. ");
                        }
                    }
                    // BWP>>Prevent parent names that equal the Item names
                    if (itemName != null && itemName.equalsIgnoreCase(parentItem)) {
                        parentItem = "";
                    }
                    cell = sheet.getRow(k).getCell((short) 9);
                    int columnNum = 0;
                    String column = getValue(cell);
                    if (!StringUtil.isBlank(column)) {
                        try {
                            columnNum = Integer.parseInt(column);
                        } catch (NumberFormatException ne) {
                            columnNum = 0;
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 10);
                    if (cell != null) {
                        page = getValue(cell);
                    }
                    cell = sheet.getRow(k).getCell((short) 11);
                    String questionNum = getValue(cell);
                    cell = sheet.getRow(k).getCell((short) 12);
                    String responseType = getValue(cell);
                    int responseTypeId = 1;
                    if (StringUtil.isBlank(responseType)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_TYPE_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",12", resPageMsg.getString("required_field"));
                    } else {
                        if (!ResponseType.findByName(responseType.toLowerCase())) {
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_TYPE_column") + " " + resPageMsg.getString("was_invalid_at_row") + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                            htmlErrors.put(j + "," + k + ",12", resPageMsg.getString("INVALID_FIELD"));
                        } else {
                            responseTypeId = ResponseType.getByName(responseType.toLowerCase()).getId();
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 13);
                    String responseLabel = getValue(cell);
                    // "");
                    if (StringUtil.isBlank(responseLabel) && responseTypeId != ResponseType.TEXT.getId() && responseTypeId != ResponseType.TEXTAREA.getId()) {
                        // << tbh #4180
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_LABEL_column") + " " + resPageMsg.getString("was_blank_at_row") + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",13", resPageMsg.getString("required_field"));
                    } else if ("file".equalsIgnoreCase(responseType) && !"file".equalsIgnoreCase(responseLabel)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_LABEL_column") + " " + resPageMsg.getString("should_be_file") + resPageMsg.getString("at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",13", resPageMsg.getString("should_be_file"));
                    }
                    cell = sheet.getRow(k).getCell((short) 14);
                    String resOptions = getValue(cell);
                    // resOptions = resOptions.replaceAll("<[^>]*>", "");
                    if (responseLabel.equalsIgnoreCase("text") || responseLabel.equalsIgnoreCase("textarea")) {
                        resOptions = "text";
                    }
                    if ("file".equalsIgnoreCase(responseType)) {
                        resOptions = "file";
                    }
                    // YW 2-5-2008 << set default resOptions for calculation
                    // and group-calculation type
                    // if(responseTypeId==8 || responseTypeId==9) {
                    // resOptions = resOptions.length()>0 ? resOptions :
                    // "can not calculate";
                    // }
                    // YW >>
                    int numberOfOptions = 0;
                    if (!resNames.contains(responseLabel) && StringUtil.isBlank(resOptions) && responseTypeId != ResponseType.TEXT.getId() && responseTypeId != ResponseType.TEXTAREA.getId()) {
                        // << tbh #4180
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_OPTIONS_TEXT_column") + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",14", resPageMsg.getString("required_field"));
                    }
                    if (!resNames.contains(responseLabel) && !StringUtil.isBlank(resOptions)) {
                        // old template
                        if (responseTypeId == 8 || responseTypeId == 9) {
                            numberOfOptions = 1;
                        // YW >>
                        } else {
                            // String[] resArray = resOptions.split(",");
                            String text1 = resOptions.replaceAll("\\\\,", "##");
                            String[] resArray = text1.split(",");
                            numberOfOptions = resArray.length;
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 15);
                    String resValues = getValue(cell);
                    String value1 = resValues.replaceAll("\\\\,", "##");
                    String[] resValArray = value1.split(",");
                    if (responseLabel.equalsIgnoreCase("text") || responseLabel.equalsIgnoreCase("textarea")) {
                        resValues = "text";
                    }
                    if ("file".equalsIgnoreCase(responseType)) {
                        resValues = "file";
                    }
                    if (!resNames.contains(responseLabel) && StringUtil.isBlank(resValues) && responseTypeId != ResponseType.TEXT.getId() && responseTypeId != ResponseType.TEXTAREA.getId()) {
                        // << tbh, #4180, add textarea too?
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_VALUES_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",15", resPageMsg.getString("required_field"));
                    }
                    // YW 1-25-2008 << validate scoring expression
                    if (responseTypeId == 8 || responseTypeId == 9) {
                        // func, it must be correctly spelled
                        if (resValues.contains(":")) {
                            String[] s = resValues.split(":");
                            if (!"func".equalsIgnoreCase(s[0].trim())) {
                                errors.add(resPageMsg.getString("expression_not_start_with_func_at") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". ");
                                htmlErrors.put(j + "," + k + ",15", resPageMsg.getString("INVALID_FIELD"));
                            }
                        }
                        String exp = resValues;
                        // make both \\, and , works for functions
                        exp = exp.replace("\\\\,", "##");
                        exp = exp.replace("##", ",");
                        exp = exp.replace(",", "\\\\,");
                        resValues = exp;
                        if (exp.startsWith("func:")) {
                            exp = exp.substring(5).trim();
                        }
                        exp = exp.replace("\\\\,", "##");
                        StringBuffer err = new StringBuffer();
                        ArrayList<String> variables = new ArrayList<String>();
                        ScoreValidator scoreValidator = new ScoreValidator(locale);
                        if (!scoreValidator.isValidExpression(exp, err, variables)) {
                            errors.add(resPageMsg.getString("expression_invalid_at") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ": " + err);
                            htmlErrors.put(j + "," + k + ",15", resPageMsg.getString("INVALID_FIELD"));
                        }
                        String group = "Ungrouped";
                        for (String v : variables) {
                            if (!allItems.containsKey(v)) {
                                errors.add(resPageMsg.getString("item") + v + resPageMsg.getString("must_listed_before_item") + itemName + resPageMsg.getString("item_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". ");
                                htmlErrors.put(j + "," + k + ",15", resPageMsg.getString("INVALID_FIELD"));
                            }
                        }
                    } else if (numberOfOptions > 0) {
                        // YW >>
                        if (resValArray.length != numberOfOptions) {
                            errors.add(resPageMsg.getString("incomplete_option_value_pair") + " " + resPageMsg.getString("RESPONSE_OPTIONS_column") + " " + resPageMsg.getString("and") + " " + resPageMsg.getString("RESPONSE_VALUES_column") + " " + resPageMsg.getString("at_row") + k + " " + resPageMsg.getString("items_worksheet") + "; " + resPageMsg.getString("perhaps_missing_comma"));
                            htmlErrors.put(j + ", " + k + ", 14", resPageMsg.getString("number_option_not_match"));
                            htmlErrors.put(j + ", " + k + ", 15", resPageMsg.getString("number_value_not_match"));
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 16);
                    String dataType = getValue(cell);
                    dataType = dataType.replaceAll("<[^>]*>", "");
                    String dataTypeIdString = "1";
                    if (StringUtil.isBlank(dataType)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DATA_TYPE_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet"));
                        htmlErrors.put(j + ", " + k + ", 16", resPageMsg.getString("required_field"));
                    } else {
                        if (!ItemDataType.findByName(dataType.toLowerCase())) {
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DATA_TYPE_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                            htmlErrors.put(j + ", " + k + ", 16", resPageMsg.getString("INVALID_FIELD"));
                        } else {
                            if ("file".equalsIgnoreCase(responseType) && !"FILE".equalsIgnoreCase(dataType)) {
                                errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DATA_TYPE_column") + " " + resPageMsg.getString("should_be_file") + " " + resPageMsg.getString("at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                                htmlErrors.put(j + ", " + k + ", 16", resPageMsg.getString("should_be_file"));
                            }
                            // dataTypeId =
                            // (ItemDataType.getByName(dataType)).getId();
                            dataTypeIdString = "(SELECT ITEM_DATA_TYPE_ID From ITEM_DATA_TYPE Where CODE='" + dataType.toUpperCase() + "')";
                        }
                    }
                    if (responseTypeId == 3 || responseTypeId == 5 || responseTypeId == 6 || responseTypeId == 7) {
                        // make sure same responseLabels have same datatype
                        if (labelWithType.containsKey(responseLabel)) {
                            logger.debug("in label=" + responseLabel);
                            if (!dataType.equalsIgnoreCase(labelWithType.get(responseLabel).toString())) {
                                errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DATA_TYPE_column") + " " + resPageMsg.getString("does_not_match_the_item_data_type_with_the_same_response_label") + " " + k + ", " + resPageMsg.getString("items_worksheet"));
                                htmlErrors.put(j + "," + k + ",16", resPageMsg.getString("INVALID_FIELD"));
                            }
                        } else {
                            labelWithType.put(responseLabel, dataType);
                            // make sure response values matching datatype
                            if (resValArray.length > 0) {
                                boolean wrongType = false;
                                if ("int".equalsIgnoreCase(dataType)) {
                                    for (String s : resValArray) {
                                        String st = s != null && s.length() > 0 ? s.trim() : "";
                                        if (st.length() > 0) {
                                            try {
                                                Integer I = Integer.parseInt(s.trim());
                                                // but 2.3 is not integer
                                                if (!I.toString().equals(s.trim())) {
                                                    wrongType = true;
                                                }
                                            } catch (Exception e) {
                                                wrongType = true;
                                            }
                                        }
                                    }
                                    if (wrongType) {
                                        wrongType = false;
                                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_VALUES_column") + " " + resPageMsg.getString("should_be_integer") + " " + resPageMsg.getString("at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                                        htmlErrors.put(j + ", " + k + ", 15", resPageMsg.getString("should_be_integer"));
                                    }
                                } else if ("real".equalsIgnoreCase(dataType)) {
                                    for (String s : resValArray) {
                                        String st = s != null && s.length() > 0 ? s.trim() : "";
                                        if (st.length() > 0) {
                                            try {
                                                Double I = Double.parseDouble(s.trim());
                                            } catch (Exception e) {
                                                wrongType = true;
                                            }
                                        }
                                    }
                                    if (wrongType) {
                                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_VALUES_column") + " " + resPageMsg.getString("should_be_real") + " " + resPageMsg.getString("at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                                        htmlErrors.put(j + ", " + k + ", 15", resPageMsg.getString("should_be_real"));
                                    }
                                }
                            }
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 17);
                    String regexp = getValue(cell);
                    String regexp1 = "";
                    if (!StringUtil.isBlank(regexp)) {
                        // parse the string and get reg exp eg. regexp:
                        // /[0-9]*/
                        regexp1 = regexp.trim();
                        if (regexp1.startsWith("regexp:")) {
                            String finalRegexp = regexp1.substring(7).trim();
                            if (finalRegexp.contains("\\\\")) {
                                // \\ in the regular expression it should
                                // not be allowed
                                errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + " " + resPageMsg.getString("has_an_invalid_regular_expression_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("regular_expression_contained") + " '\\\\', " + resPageMsg.getString("it_should_only_contain_one") + "'\\'. ");
                                htmlErrors.put(j + "," + k + ",17", resPageMsg.getString("INVALID_FIELD"));
                            } else {
                                if (finalRegexp.startsWith("/") && finalRegexp.endsWith("/")) {
                                    finalRegexp = finalRegexp.substring(1, finalRegexp.length() - 1);
                                    try {
                                        Pattern p = Pattern.compile(finalRegexp);
                                        // YW 11-21-2007 << add another \ if
                                        // there is \ in regexp
                                        char[] chars = regexp1.toCharArray();
                                        regexp1 = "";
                                        for (char c : chars) {
                                            if (c == '\\' && !dbName.equals("oracle")) {
                                                regexp1 += c + "\\";
                                            } else {
                                                regexp1 += c;
                                            }
                                        }
                                    // YW >>
                                    } catch (PatternSyntaxException pse) {
                                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + resPageMsg.getString("has_an_invalid_regular_expression_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("Example:") + " regexp: /[0-9]*/ ");
                                        htmlErrors.put(j + "," + k + ",17", resPageMsg.getString("INVALID_FIELD"));
                                    }
                                } else {
                                    // errors.add("The VALIDATION column has
                                    // an invalid regular expression at row
                                    // " + k
                                    // + ", Items worksheet. Example:
                                    // regexp: /[0-9]*/ ");
                                    errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + " " + resPageMsg.getString("has_an_invalid_regular_expression_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("Example") + " regexp: /[0-9]*/ ");
                                    htmlErrors.put(j + "," + k + ",17", resPageMsg.getString("INVALID_FIELD"));
                                }
                            }
                        } else if (regexp1.startsWith("func:")) {
                            boolean isProperFunction = false;
                            try {
                                Validator.processCRFValidationFunction(regexp1);
                                isProperFunction = true;
                            } catch (Exception e) {
                                errors.add(e.getMessage() + ", " + resPageMsg.getString("at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". ");
                                htmlErrors.put(j + "," + k + ",17", resPageMsg.getString("INVALID_FIELD"));
                            }
                        } else {
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". ");
                            htmlErrors.put(j + "," + k + ",17", resPageMsg.getString("INVALID_FIELD"));
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 18);
                    String regexpError = getValue(cell);
                    regexpError = regexpError.replaceAll("<[^>]*>", "");
                    if (!StringUtil.isBlank(regexp) && StringUtil.isBlank(regexpError)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_ERROR_MESSAGE_column") + resPageMsg.getString("was_blank_at_row") + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("cannot_be_blank_if_VALIDATION_not_blank"));
                        htmlErrors.put(j + "," + k + ",18", resPageMsg.getString("required_field"));
                    }
                    if (regexpError != null && regexpError.length() > 255) {
                        errors.add(resPageMsg.getString("regexp_errror_length_error"));
                    }
                    boolean phiBoolean = false;
                    cell = sheet.getRow(k).getCell((short) 19);
                    // String phi = getValue(cell);
                    String phi = "";
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        double dphi = cell.getNumericCellValue();
                        if ((dphi - (int) dphi) * 1000 == 0) {
                            phi = (int) dphi + "";
                        }
                    }
                    if (!"0".equals(phi) && !"1".equals(phi)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("PHI_column") + resPageMsg.getString("was_invalid_at_row") + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("PHI_column") + " " + resPageMsg.getString("can_only_be_either_0_or_1"));
                        htmlErrors.put(j + "," + k + ",19", resPageMsg.getString("INVALID_VALUE"));
                    } else {
                        phiBoolean = "1".equals(phi) ? true : false;
                    }
                    boolean isRequired = false;
                    cell = sheet.getRow(k).getCell((short) 20);
                    String required = getValue(cell);
                    logger.info(getValue(cell));
                    // String required = "";
                    if (StringUtil.isBlank(required)) {
                        required = "0";
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        double dr = cell.getNumericCellValue();
                        if ((dr - (int) dr) * 1000 == 0) {
                            required = (int) dr + "";
                        }
                    }
                    if (!"0".equals(required) && !"1".equals(required)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("REQUIRED_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". " + resPageMsg.getString("REQUIRED_column") + resPageMsg.getString("can_only_be_either_0_or_1"));
                        htmlErrors.put(j + "," + k + ",20", resPageMsg.getString("INVALID_VALUE"));
                    } else {
                        isRequired = "1".equals(required) ? true : false;
                    }
                    // Create oid for Item Bean
                    String itemOid = idao.getValidOid(new ItemBean(), crfName, itemName, itemOids);
                    itemOids.add(itemOid);
                    // better spot for checking item might be right here,
                    // tbh 7-25
                    String vlSql = "";
                    ArrayList<SqlParameter> sqlParameters = new ArrayList<>();
                    QueryObject qo = new QueryObject();
                    if (dbName.equals("oracle")) {
                        /* vlSql =
                                "INSERT INTO ITEM (NAME,DESCRIPTION,UNITS,PHI_STATUS,"
                                    + "ITEM_DATA_TYPE_ID, ITEM_REFERENCE_TYPE_ID,STATUS_ID,OWNER_ID,DATE_CREATED,OC_OID) " + "VALUES ('"
                                    + stripQuotes(itemName) + "','" + stripQuotes(descLabel) + "','" + stripQuotes(unit) + "'," + (phiBoolean == true ? 1 : 0)
                                    + "," + dataTypeIdString + ",1,1," + ub.getId() + ", sysdate" + ",'" + itemOid + "')";*/
                        vlSql = "INSERT INTO ITEM (NAME,DESCRIPTION,UNITS,PHI_STATUS," + "ITEM_DATA_TYPE_ID, ITEM_REFERENCE_TYPE_ID,STATUS_ID,OWNER_ID,DATE_CREATED,OC_OID) " + "VALUES (?,?,?," + (phiBoolean == true ? 1 : 0) + "," + dataTypeIdString + ",1,1," + ub.getId() + ", sysdate" + ",?)";
                    } else {
                        /*vlSql =
                                "INSERT INTO ITEM (NAME,DESCRIPTION,UNITS,PHI_STATUS,"
                                    + "ITEM_DATA_TYPE_ID, ITEM_REFERENCE_TYPE_ID,STATUS_ID,OWNER_ID,DATE_CREATED,OC_OID) " + "VALUES ('"
                                    + stripQuotes(itemName) + "','" + stripQuotes(descLabel) + "','" + stripQuotes(unit) + "'," + phiBoolean + ","
                                    + dataTypeIdString + ",1,1," + ub.getId() + ", NOW()" + ",'" + itemOid + "')";*/
                        vlSql = "INSERT INTO ITEM (NAME,DESCRIPTION,UNITS,PHI_STATUS," + "ITEM_DATA_TYPE_ID, ITEM_REFERENCE_TYPE_ID,STATUS_ID,OWNER_ID,DATE_CREATED,OC_OID) " + "VALUES (?, ?, ?," + phiBoolean + "," + dataTypeIdString + ",1,1," + ub.getId() + ", NOW()" + ",?)";
                    }
                    // backupItemQueries.put(itemName, vlSql);
                    sqlParameters = new ArrayList<>();
                    sqlParameters.add(new SqlParameter(itemName));
                    sqlParameters.add(new SqlParameter(descLabel));
                    sqlParameters.add(new SqlParameter(unit));
                    sqlParameters.add(new SqlParameter(itemOid));
                    qo = new QueryObject();
                    qo.setSql(vlSql);
                    qo.setSqlParameters(sqlParameters);
                    backupItemQueries.put(itemName, qo);
                    // to compare items from DB later, if two items have the
                    // same name,
                    // but different units or phiStatus, they are different
                    ItemBean ib = new ItemBean();
                    ib.setName(itemName);
                    ib.setUnits(unit);
                    ib.setPhiStatus(phiBoolean);
                    ib.setDescription(descLabel);
                    ib.setDataType(ItemDataType.getByName(dataType.toLowerCase()));
                    // put metadata into item
                    ResponseSetBean rsb = new ResponseSetBean();
                    // notice that still "\\," in options - jxu-08-31-06
                    String updatedResOptions = resOptions.replaceAll("\\\\,", "\\,");
                    String updatedResValues = resValues.replaceAll("\\\\,", "\\,");
                    // following rsb used in isResponseValid in CreateCRFVersionServlet for comparing response
                    // options text and values between form versions. Please keep as is. - Z 19-Jun-2020
                    rsb.setOptions(stripQuotes(updatedResOptions), stripQuotes(updatedResValues));
                    ItemFormMetadataBean ifmb = new ItemFormMetadataBean();
                    ifmb.setResponseSet(rsb);
                    ib.setItemMeta(ifmb);
                    items.put(itemName, ib);
                    int ownerId = ub.getId();
                    if (!itemCheck.containsKey(itemName)) {
                        // item not in
                        // the DB
                        // openQueries.put(itemName, vlSql);
                        openQueries.put(itemName, qo);
                    } else {
                        // item in the DB
                        ItemBean oldItem = (ItemBean) idao.findByNameAndCRFId(itemName, crfId);
                        if (oldItem.getOwnerId() == ub.getId()) {
                            // update
                            if (!cvdao.hasItemData(oldItem.getId())) {
                                // no
                                // item
                                // data
                                String upSql = "";
                                if (dbName.equals("oracle")) {
                                    /* upSql =
                                            "UPDATE ITEM SET DESCRIPTION='" + stripQuotes(descLabel) + "'," + "UNITS='" + stripQuotes(unit) + "',"
                                                + "PHI_STATUS=" + (phiBoolean ? 1 : 0) + "," + "ITEM_DATA_TYPE_ID=" + dataTypeIdString
                                                + " WHERE exists (SELECT versioning_map.item_id from versioning_map, crf_version where"
                                                + " versioning_map.crf_version_id = crf_version.crf_version_id" + " AND crf_version.crf_id= " + crfId
                                                + " AND item.item_id = versioning_map.item_id)" + " AND item.name='" + stripQuotes(itemName)
                                                + "' AND item.owner_id = " + ownerId;*/
                                    upSql = "UPDATE ITEM SET DESCRIPTION= ?,UNITS= ?, " + "PHI_STATUS=" + (phiBoolean ? 1 : 0) + "," + "ITEM_DATA_TYPE_ID=" + dataTypeIdString + " WHERE exists (SELECT versioning_map.item_id from versioning_map, crf_version where" + " versioning_map.crf_version_id = crf_version.crf_version_id AND crf_version.crf_id= " + crfId + " AND item.item_id = versioning_map.item_id) AND item.name= ? " + "' AND item.owner_id = ?";
                                } else {
                                    /* upSql =
                                            "UPDATE ITEM SET DESCRIPTION='" + stripQuotes(descLabel) + "'," + "UNITS='" + stripQuotes(unit) + "',"
                                                + "PHI_STATUS=" + phiBoolean + "," + "ITEM_DATA_TYPE_ID=" + dataTypeIdString
                                                + " FROM versioning_map, crf_version" + " WHERE item.name='" + stripQuotes(itemName) + "' AND item.owner_id = "
                                                + ownerId + " AND item.item_id = versioning_map.item_id AND"
                                                + " versioning_map.crf_version_id = crf_version.crf_version_id" + " AND crf_version.crf_id = " + crfId;*/
                                    upSql = "UPDATE ITEM SET DESCRIPTION= ?, UNITS= ?," + "PHI_STATUS=" + phiBoolean + "," + "ITEM_DATA_TYPE_ID=" + dataTypeIdString + " FROM versioning_map, crf_version" + " WHERE item.name= ? AND item.owner_id =? " + " AND item.item_id = versioning_map.item_id AND" + " versioning_map.crf_version_id = crf_version.crf_version_id" + " AND crf_version.crf_id = " + crfId;
                                }
                                // end of if dbname
                                // openQueries.put(itemName, upSql);
                                sqlParameters = new ArrayList<>();
                                sqlParameters.add(new SqlParameter(descLabel));
                                sqlParameters.add(new SqlParameter(unit));
                                sqlParameters.add(new SqlParameter(itemName));
                                sqlParameters.add(new SqlParameter(ownerId + "", JDBCType.INTEGER));
                                qo = new QueryObject();
                                qo.setSql(upSql);
                                qo.setSqlParameters(sqlParameters);
                                openQueries.put(itemName, qo);
                            } else {
                                String upSql = "";
                                if (dbName.equals("oracle")) {
                                    upSql = "UPDATE ITEM SET DESCRIPTION= ?," + "PHI_STATUS=" + (phiBoolean ? 1 : 0) + " WHERE exists (SELECT versioning_map.item_id from versioning_map, crf_version where" + " versioning_map.crf_version_id = crf_version.crf_version_id" + " AND crf_version.crf_id= " + crfId + " AND item.item_id = versioning_map.item_id)" + " AND item.name= ? " + " AND item.owner_id = ?";
                                } else {
                                    upSql = "UPDATE ITEM SET DESCRIPTION= ?," + "PHI_STATUS=" + phiBoolean + " FROM versioning_map, crf_version" + " WHERE item.name= ? AND item.owner_id = ? " + ownerId + " AND item.item_id = versioning_map.item_id AND " + " versioning_map.crf_version_id = crf_version.crf_version_id" + " AND crf_version.crf_id = " + crfId;
                                }
                                // end of if dbName
                                // openQueries.put(itemName, upSql);
                                sqlParameters = new ArrayList<>();
                                sqlParameters.add(new SqlParameter(descLabel));
                                sqlParameters.add(new SqlParameter(itemName));
                                sqlParameters.add(new SqlParameter(ownerId + "", JDBCType.INTEGER));
                                qo = new QueryObject();
                                qo.setSql(upSql);
                                qo.setSqlParameters(sqlParameters);
                                openQueries.put(itemName, qo);
                            }
                        } else {
                            ownerId = oldItem.getOwner().getId();
                        }
                    }
                    String sql = "";
                    sqlParameters = new ArrayList<>();
                    if (dbName.equals("oracle")) {
                        // resOptions = resOptions.replaceAll("\\\\,",
                        // "\\,");
                        /* sql =
                                "INSERT INTO RESPONSE_SET (LABEL, OPTIONS_TEXT, OPTIONS_VALUES, " + "RESPONSE_TYPE_ID, VERSION_ID)" + " VALUES ('"
                                    + stripQuotes(responseLabel) + "', '" + stripQuotes(resOptions.replaceAll("\\\\,", "\\,")) + "','"
                                    + stripQuotes(resValues.replace("\\\\", "\\")) + "'," + "(SELECT RESPONSE_TYPE_ID From RESPONSE_TYPE Where NAME='"
                                    + stripQuotes(responseType.toLowerCase()) + "')," + versionIdString + ")";*/
                        sql = "INSERT INTO RESPONSE_SET (LABEL, OPTIONS_TEXT, OPTIONS_VALUES, RESPONSE_TYPE_ID, VERSION_ID)" + " VALUES (?, ?, ?,(SELECT RESPONSE_TYPE_ID From RESPONSE_TYPE Where NAME=?)," + versionIdString + ")";
                        sqlParameters.add(new SqlParameter(responseLabel));
                        sqlParameters.add(new SqlParameter(resOptions.replaceAll("\\\\,", "\\,")));
                        sqlParameters.add(new SqlParameter(resValues.replace("\\\\", "\\")));
                        sqlParameters.add(new SqlParameter(responseType.toLowerCase()));
                        // in versionIdString there is one parameter:crfId
                        sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    } else {
                        /* sql =
                                "INSERT INTO RESPONSE_SET (LABEL, OPTIONS_TEXT, OPTIONS_VALUES, " + "RESPONSE_TYPE_ID, VERSION_ID)" + " VALUES ('"
                                    + stripQuotes(responseLabel) + "', E'" + stripQuotes(resOptions) + "', E'" + stripQuotes(resValues) + "',"
                                    + "(SELECT RESPONSE_TYPE_ID From RESPONSE_TYPE Where NAME='" + stripQuotes(responseType.toLowerCase()) + "'),"
                                    + versionIdString + ")";*/
                        sql = "INSERT INTO RESPONSE_SET (LABEL, OPTIONS_TEXT, OPTIONS_VALUES, RESPONSE_TYPE_ID, VERSION_ID)" + " VALUES (?, ?, ?,(SELECT RESPONSE_TYPE_ID From RESPONSE_TYPE Where NAME=?)," + versionIdString + ")";
                        sqlParameters.add(new SqlParameter(responseLabel));
                        sqlParameters.add(new SqlParameter(updatedResOptions));
                        sqlParameters.add(new SqlParameter(updatedResValues));
                        sqlParameters.add(new SqlParameter(responseType.toLowerCase()));
                        // in versionIdString there is one parameter:crfId
                        sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    }
                    if (!resNames.contains(responseLabel)) {
                        qo = new QueryObject();
                        qo.setSql(sql);
                        qo.setSqlParameters(sqlParameters);
                        queries.add(qo);
                        resNames.add(responseLabel);
                    // this will have to change since we have some data
                    // in the actual
                    // spreadsheet
                    // change it to caching response set names in a
                    // collection?
                    // or just delete the offending cells from the
                    // spreadsheet?
                    }
                    String parentItemString = "0";
                    if (!StringUtil.isBlank(parentItem)) {
                        if (dbName.equals("oracle")) {
                            parentItemString = "(SELECT MAX(ITEM_ID) FROM ITEM WHERE NAME='" + parentItem + "' AND owner_id = " + ownerId + " )";
                        } else {
                            parentItemString = "(SELECT ITEM_ID FROM ITEM WHERE NAME='" + parentItem + "' AND owner_id = " + ownerId + " ORDER BY OC_OID DESC LIMIT 1)";
                        }
                    }
                    String selectCorrectItemQueryPostgres = " (SELECT I.ITEM_ID FROM ITEM I LEFT OUTER JOIN ITEM_FORM_METADATA IFM ON I.ITEM_Id = IFM.ITEM_ID LEFT OUTER JOIN CRF_VERSION CV ON IFM.CRF_VERSION_ID = CV.CRF_VERSION_ID  WHERE " + " ( I.NAME='" + itemName + "'" + " AND I.owner_id = " + ownerId + " AND CV.CRF_VERSION_ID is null )" + " OR " + " ( I.NAME='" + itemName + "'" + " AND I.owner_id = " + ownerId + " AND CV.CRF_VERSION_ID is not null AND CV.CRF_ID =" + crfId + " ) " + " ORDER BY I.OC_ID DESC LIMIT 1) ";
                    String selectCorrectItemQueryOracle = " (SELECT MAX(I.ITEM_ID) FROM ITEM I LEFT OUTER JOIN ITEM_FORM_METADATA IFM ON I.ITEM_Id = IFM.ITEM_ID LEFT OUTER JOIN CRF_VERSION CV ON IFM.CRF_VERSION_ID = CV.CRF_VERSION_ID  WHERE " + " ( I.NAME='" + itemName + "'" + " AND I.owner_id = " + ownerId + " AND CV.CRF_VERSION_ID is null )" + " OR " + " ( I.NAME='" + itemName + "'" + " AND I.owner_id = " + ownerId + " AND CV.CRF_VERSION_ID is not null AND CV.CRF_ID =" + crfId + " )) ";
                    String sql2 = "";
                    sqlParameters = new ArrayList<>();
                    if (dbName.equals("oracle")) {
                        /*  sql2 =
                                "INSERT INTO ITEM_FORM_METADATA (CRF_VERSION_ID, RESPONSE_SET_ID," + "ITEM_ID,SUBHEADER,header,LEFT_ITEM_TEXT,"
                                    + "RIGHT_ITEM_TEXT,PARENT_ID,SECTION_ID,ORDINAL,PARENT_LABEL,COLUMN_NUMBER,PAGE_NUMBER_LABEL,question_number_label,"
                                    + "REGEXP,REGEXP_ERROR_MSG,REQUIRED)" + " VALUES ("
                                    + versionIdString
                                    + ",(SELECT RESPONSE_SET_ID FROM RESPONSE_SET WHERE LABEL='"
                                    + stripQuotes(responseLabel)
                                    + "'"
                                    + " AND VERSION_ID="
                                    + versionIdString
                                    + "),"
                                    + selectCorrectItemQueryOracle
                                    + ",'"
                                    + stripQuotes(subHeader)
                                    + "','"
                                    + stripQuotes(header)
                                    + "','"
                                    + stripQuotes(leftItemText)
                                    + "','"
                                    + stripQuotes(rightItemText)
                                    + "',"
                                    + parentItemString
                                    + ", (SELECT SECTION_ID FROM SECTION WHERE LABEL='"
                                    + secName
                                    + "' AND "
                                    + "CRF_VERSION_ID IN "
                                    + versionIdString
                                    + "), "
                                    + k
                                    + ",'"
                                    + parentItem
                                    + "',"
                                    + columnNum
                                    + ",'"
                                    + stripQuotes(page)
                                    + "','"
                                    + stripQuotes(questionNum)
                                    + "','"
                                    + stripQuotes(regexp1) + "','" + stripQuotes(regexpError) + "', " + (isRequired ? 1 : 0) + ")";
*/
                        sql2 = "INSERT INTO ITEM_FORM_METADATA (CRF_VERSION_ID, RESPONSE_SET_ID," + "ITEM_ID,SUBHEADER,header,LEFT_ITEM_TEXT," + "RIGHT_ITEM_TEXT,PARENT_ID,SECTION_ID,ORDINAL,PARENT_LABEL,COLUMN_NUMBER,PAGE_NUMBER_LABEL,question_number_label," + "REGEXP,REGEXP_ERROR_MSG,REQUIRED)" + " VALUES (" + versionIdString + // responseLabel
                        ",(SELECT RESPONSE_SET_ID FROM RESPONSE_SET WHERE LABEL=?" + " AND VERSION_ID=" + versionIdString + ")," + selectCorrectItemQueryPostgres + // subHeader, header, leftItemText, rightItemText
                        ",?, ?, ?, ?, " + parentItemString + ", (SELECT SECTION_ID FROM SECTION WHERE LABEL='" + secName + "' AND " + "CRF_VERSION_ID IN " + versionIdString + "), " + k + ",'" + parentItem + "'," + columnNum + // page, questionNum, regexp1, regexpError
                        ",?,?,?,?, " + (isRequired ? 1 : 0) + ")";
                    } else {
                        /* sql2 =
                                "INSERT INTO ITEM_FORM_METADATA (CRF_VERSION_ID, RESPONSE_SET_ID," + "ITEM_ID,SUBHEADER,HEADER,LEFT_ITEM_TEXT,"
                                    + "RIGHT_ITEM_TEXT,PARENT_ID,SECTION_ID,ORDINAL,PARENT_LABEL,COLUMN_NUMBER,PAGE_NUMBER_LABEL,question_number_label,"
                                    + "REGEXP,REGEXP_ERROR_MSG,REQUIRED)" + " VALUES ("
                                    + versionIdString
                                    + ",(SELECT RESPONSE_SET_ID FROM RESPONSE_SET WHERE LABEL='"
                                    + stripQuotes(responseLabel)
                                    + "'"
                                    + " AND VERSION_ID="
                                    + versionIdString
                                    + "),"
                                    + selectCorrectItemQueryPostgres
                                    + ",'"
                                    + stripQuotes(subHeader)
                                    + "','"
                                    + stripQuotes(header)
                                    + "','"
                                    + stripQuotes(leftItemText)
                                    + "','"
                                    + stripQuotes(rightItemText)
                                    + "',"
                                    + parentItemString
                                    + ", (SELECT SECTION_ID FROM SECTION WHERE LABEL='"
                                    + secName
                                    + "' AND "
                                    + "CRF_VERSION_ID IN "
                                    + versionIdString
                                    + "), "
                                    + k
                                    + ",'"
                                    + parentItem
                                    + "',"
                                    + columnNum
                                    + ",'"
                                    + stripQuotes(page)
                                    + "','"
                                    + stripQuotes(questionNum)
                                    + "','"
                                    + stripQuotes(regexp1) + "','" + stripQuotes(regexpError) + "', " + isRequired + ")";*/
                        sql2 = "INSERT INTO ITEM_FORM_METADATA (CRF_VERSION_ID, RESPONSE_SET_ID," + "ITEM_ID,SUBHEADER,HEADER,LEFT_ITEM_TEXT," + "RIGHT_ITEM_TEXT,PARENT_ID,SECTION_ID,ORDINAL,PARENT_LABEL,COLUMN_NUMBER,PAGE_NUMBER_LABEL,question_number_label," + "REGEXP,REGEXP_ERROR_MSG,REQUIRED)" + " VALUES (" + versionIdString + // responseLabel
                        ",(SELECT RESPONSE_SET_ID FROM RESPONSE_SET WHERE LABEL=?" + " AND VERSION_ID=" + versionIdString + ")," + selectCorrectItemQueryPostgres + // subHeader, header, leftItemText, rightItemText
                        ",?, ?, ?, ?, " + parentItemString + ", (SELECT SECTION_ID FROM SECTION WHERE LABEL='" + secName + "' AND " + "CRF_VERSION_ID IN " + versionIdString + "), " + k + ",'" + parentItem + "'," + columnNum + // page, questionNum, regexp1, regexpError
                        ",?,?,?,?, " + isRequired + ")";
                    }
                    // in versionIdString there one parameter:crfId
                    sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(responseLabel));
                    sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(subHeader));
                    sqlParameters.add(new SqlParameter(header));
                    sqlParameters.add(new SqlParameter(leftItemText));
                    sqlParameters.add(new SqlParameter(rightItemText));
                    // in versionIdString there one parameter:crfId
                    sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(page));
                    sqlParameters.add(new SqlParameter(questionNum));
                    sqlParameters.add(new SqlParameter(regexp1));
                    sqlParameters.add(new SqlParameter(regexpError));
                    qo = new QueryObject();
                    qo.setSql(sql2);
                    qo.setSqlParameters(sqlParameters);
                    queries.add(qo);
                    // link version with items now
                    String sql3 = "";
                    if (dbName.equals("oracle")) {
                        sql3 = "INSERT INTO VERSIONING_MAP (CRF_VERSION_ID, ITEM_ID) VALUES ( " + versionIdString + "," + selectCorrectItemQueryOracle + ")";
                    } else {
                        sql3 = "INSERT INTO VERSIONING_MAP (CRF_VERSION_ID, ITEM_ID) VALUES ( " + versionIdString + "," + selectCorrectItemQueryPostgres + ")";
                    }
                    sqlParameters = new ArrayList<>();
                    // in versionIdString there one parameter:crfId
                    sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    qo = new QueryObject();
                    qo.setSql(sql3);
                    qo.setSqlParameters(sqlParameters);
                    queries.add(qo);
                    // this item doesn't have group, so put it into
                    // 'Ungrouped' group
                    String sqlGroupLabel = "";
                    if (dbName.equals("oracle")) {
                        sqlGroupLabel = "INSERT INTO ITEM_GROUP_METADATA (" + "item_group_id,header," + "subheader, layout, repeat_number, repeat_max," + " repeat_array,row_start_number, crf_version_id," + "item_id , ordinal, borders) VALUES (" + "(SELECT MAX(ITEM_GROUP_ID) FROM ITEM_GROUP WHERE NAME='Ungrouped' AND crf_id = " + crfId + " ),'" + "" + "', '" + "" + "', '" + "" + "', " + 1 + ", " + 1 + ", '', 1," + versionIdString + "," + // + itemName + "' ),"
                        "(SELECT MAX(ITEM.ITEM_ID) FROM ITEM,ITEM_FORM_METADATA,CRF_VERSION WHERE ITEM.NAME='" + itemName + "' " + "AND ITEM.ITEM_ID = ITEM_FORM_METADATA.ITEM_ID and ITEM_FORM_METADATA.CRF_VERSION_ID=CRF_VERSION.CRF_VERSION_ID " + "AND CRF_VERSION.CRF_ID= " + crfId + " )," + k + ",0)";
                    } else {
                        sqlGroupLabel = "INSERT INTO ITEM_GROUP_METADATA (" + "item_group_id,HEADER," + "subheader, layout, repeat_number, repeat_max," + " repeat_array,row_start_number, crf_version_id," + "item_id , ordinal, borders) VALUES (" + "(SELECT ITEM_GROUP_ID FROM ITEM_GROUP WHERE NAME='Ungrouped' AND crf_id = " + crfId + " ORDER BY oc_oid DESC LIMIT 1),'" + "" + "', '" + "" + "', '" + "" + "', " + 1 + ", " + 1 + ", '', 1," + versionIdString + "," + // LIMIT 1),"
                        "(SELECT ITEM.ITEM_ID FROM ITEM,ITEM_FORM_METADATA,CRF_VERSION WHERE ITEM.NAME='" + itemName + "' " + "AND ITEM.ITEM_ID = ITEM_FORM_METADATA.ITEM_ID and ITEM_FORM_METADATA.CRF_VERSION_ID=CRF_VERSION.CRF_VERSION_ID " + "AND CRF_VERSION.CRF_ID= " + crfId + " ORDER BY ITEM.OC_OID DESC LIMIT 1)," + k + ",0)";
                    }
                    sqlParameters = new ArrayList<>();
                    // in versionIdString there one parameter:crfId
                    sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    qo = new QueryObject();
                    qo.setSql(sqlGroupLabel);
                    qo.setSqlParameters(sqlParameters);
                    queries.add(qo);
                }
            } else if (sheetName.equalsIgnoreCase("Sections")) {
                logger.info("read sections");
                // multiple rows, six cells, last one is number
                for (int k = 1; k < numRows; k++) {
                    if (blankRowCount == 5) {
                        logger.info("hit end of the row ");
                        break;
                    }
                    if (sheet.getRow(k) == null) {
                        blankRowCount++;
                        continue;
                    }
                    HSSFCell cell = sheet.getRow(k).getCell((short) 0);
                    String secLabel = getValue(cell);
                    secLabel = secLabel.replaceAll("<[^>]*>", "");
                    if (StringUtil.isBlank(secLabel)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SECTION_LABEL_column") + " " + resPageMsg.getString("was_blank_at_row") + k + " " + ", " + resPageMsg.getString("sections_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",0", resPageMsg.getString("required_field"));
                    }
                    if (secLabel != null && secLabel.length() > 2000) {
                        errors.add(resPageMsg.getString("section_label_length_error"));
                    }
                    if (secLabel.contains("'")) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SECTION_LABEL_column") + " value " + secLabel + " " + resPageMsg.getString("has_invalid_character") + "\" ' \"" + ". " + resPageMsg.getString("remove_invalid_character"));
                    }
                    if (secNames.contains(secLabel)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SECTION_LABEL_column") + resPageMsg.getString("was_a_duplicate_of") + secLabel + " " + resPageMsg.getString("at_row") + " " + k + ", " + resPageMsg.getString("sections_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",0", resPageMsg.getString("DUPLICATE_FIELD"));
                    }
                    // logger.info("section name:" + secLabel + "row num:"
                    // +k);
                    secNames.add(secLabel);
                    cell = sheet.getRow(k).getCell((short) 1);
                    String title = getValue(cell);
                    title = title.replaceAll("<[^>]*>", "");
                    if (StringUtil.isBlank(title)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SECTION_TITLE_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("sections_worksheet") + ".");
                        htmlErrors.put(j + "," + k + ",1", resPageMsg.getString("required_field"));
                    }
                    if (title != null && title.length() > 2000) {
                        errors.add(resPageMsg.getString("section_title_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 2);
                    String subtitle = getValue(cell);
                    if (subtitle != null && subtitle.length() > 2000) {
                        errors.add(resPageMsg.getString("section_subtitle_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 3);
                    String instructions = getValue(cell);
                    if (instructions != null && instructions.length() > 2000) {
                        errors.add(resPageMsg.getString("section_instruction_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 4);
                    String pageNumber = getValue(cell);
                    if (pageNumber != null && pageNumber.length() > 5) {
                        errors.add(resPageMsg.getString("section_page_number_length_error"));
                    }
                    cell = sheet.getRow(k).getCell((short) 5);
                    String parentSection = getValue(cell);
                    parentSection = parentSection.replaceAll("<[^>]*>", "");
                    if (!StringUtil.isBlank(parentSection)) {
                        try {
                            parentId = Integer.parseInt(parentSection);
                        } catch (NumberFormatException ne) {
                            parentId = 0;
                        }
                    }
                    String sql = "";
                    if (dbName.equals("oracle")) {
                        /*  sql =
                                "INSERT INTO SECTION (CRF_VERSION_ID," + "STATUS_ID,LABEL, TITLE, INSTRUCTIONS, SUBTITLE, PAGE_NUMBER_LABEL,"
                                    + "ORDINAL, PARENT_ID, OWNER_ID, DATE_CREATED) " + "VALUES (" + versionIdString + ",1,'" + secLabel + "','"
                                    + stripQuotes(title) + "', '" + stripQuotes(instructions) + "', '" + stripQuotes(subtitle) + "','" + pageNumber + "'," + k
                                    + "," + parentId + "," + ub.getId() + ",sysdate)";*/
                        sql = "INSERT INTO SECTION (CRF_VERSION_ID," + "STATUS_ID,LABEL, TITLE, INSTRUCTIONS, SUBTITLE, PAGE_NUMBER_LABEL," + "ORDINAL, PARENT_ID, OWNER_ID, DATE_CREATED) " + "VALUES (" + versionIdString + ", 1, ? ,?, ?, ?, ?, ?, ?, ?,sysdate)";
                    } else {
                        /* sql =
                                "INSERT INTO SECTION (CRF_VERSION_ID," + "STATUS_ID,LABEL, TITLE, INSTRUCTIONS, SUBTITLE, PAGE_NUMBER_LABEL,"
                                    + "ORDINAL, PARENT_ID, OWNER_ID, DATE_CREATED) " + "VALUES (" + versionIdString + ",1,'" + secLabel + "','"
                                    + stripQuotes(title) + "', '" + stripQuotes(instructions) + "', '" + stripQuotes(subtitle) + "','" + pageNumber + "'," + k
                                    + "," + parentId + "," + ub.getId() + ",NOW())";*/
                        sql = "INSERT INTO SECTION (CRF_VERSION_ID," + "STATUS_ID,LABEL, TITLE, INSTRUCTIONS, SUBTITLE, PAGE_NUMBER_LABEL," + "ORDINAL, PARENT_ID, OWNER_ID, DATE_CREATED) " + "VALUES (" + versionIdString + ", 1, ? ,?, ?, ?, ?, ?, ?, ?,NOW())";
                    }
                    ArrayList<SqlParameter> sqlParameters = new ArrayList<>();
                    // in versionIdString there one parameter:crfId
                    sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(secLabel));
                    sqlParameters.add(new SqlParameter(title));
                    sqlParameters.add(new SqlParameter(instructions));
                    sqlParameters.add(new SqlParameter(subtitle));
                    sqlParameters.add(new SqlParameter(pageNumber));
                    sqlParameters.add(new SqlParameter(k + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(parentId + "", JDBCType.INTEGER));
                    sqlParameters.add(new SqlParameter(ub.getId() + "", JDBCType.INTEGER));
                    QueryObject qo = new QueryObject();
                    qo.setSql(sql);
                    qo.setSqlParameters(sqlParameters);
                    queries.add(qo);
                }
            // end for loop
            } else if (sheetName.equalsIgnoreCase("CRF")) {
                logger.info("read crf");
                // one row, four cells, all strings
                if (sheet == null || sheet.getRow(1) == null || sheet.getRow(1).getCell((short) 0) == null) {
                    throw new CRFReadingException("Blank row found in sheet CRF.");
                }
                HSSFCell cell = sheet.getRow(1).getCell((short) 0);
                crfName = getValue(cell);
                crfName = crfName.replaceAll("<[^>]*>", "");
                if (StringUtil.isBlank(crfName)) {
                    // resPageMsg.getString("required_field"));
                    throw new CRFReadingException("The CRF_NAME column was blank in the CRF worksheet.");
                }
                if (crfName.length() > 255) {
                    errors.add(resPageMsg.getString("crf_name_length_error"));
                }
                CRFBean existingCRFWithSameName = (CRFBean) cdao.findByName(crfName);
                if (this.getCrfId() == 0) {
                    if (existingCRFWithSameName.getName() != null && existingCRFWithSameName.getName().equals(crfName)) {
                        errors.add(resPageMsg.getString("crf_name_already_used"));
                    }
                }
                // TODO Why the following codes are commented out? -jxu
                // try {
                // CRFBean checkName = (CRFBean) cdao.findByPK(crfId);
                // if (!checkName.getName().equals(crfName)) {
                // logger.info("crf name is mismatch");
                // errors.add(resPageMsg.getString("the") + " " +
                // resPageMsg.getString("CRF_NAME_column") +
                // resPageMsg.getString("did_not_match_crf_version") + " '"
                // + checkName.getName()
                // + "' " + resPageMsg.getString("before_you_continue"));
                // htmlErrors.put(j + ",1,0",
                // resPageMsg.getString("DID_NOT_MATCH_CRF"));
                // }
                // } catch (Exception pe) {
                // logger.warn("Exception happened when check CRF name" +
                // pe.getMessage());
                // }
                cell = sheet.getRow(1).getCell((short) 1);
                String version = getValue(cell);
                version = version.replaceAll("<[^>]*>", "");
                ncrf.setVersionName(version);
                if (version != null && version.length() > 255) {
                    errors.add(resPageMsg.getString("version_length_error"));
                }
                // YW, 08-22-2007, since versionName is now obtained from
                // spreadsheet,
                // blank check has been moved to
                // CreateCRFVersionServlet.java
                // and mismatch check is not necessary
                // if (StringUtil.isBlank(version)) {
                // errors.add("The VERSION column was blank in the CRF
                // worksheet.");
                // htmlErrors.put(j + ",1,1", "REQUIRED FIELD");
                // }else if (!version.equals(versionName)) {
                // errors.add("The VERSION column did not match the intended
                // version name "
                // + "you want to upload. Make sure this reads '" +
                // versionName
                // + "' before you continue.");
                // htmlErrors.put(j + ",1,1", "DID NOT MATCH VERSION");
                // }
                cell = sheet.getRow(1).getCell((short) 2);
                String versionDesc = getValue(cell);
                versionDesc = versionDesc.replaceAll("<[^>]*>", "");
                if (versionDesc != null && versionDesc.length() > 4000) {
                    errors.add(resPageMsg.getString("version_description_length_error"));
                }
                cell = sheet.getRow(1).getCell((short) 3);
                String revisionNotes = getValue(cell);
                revisionNotes = revisionNotes.replaceAll("<[^>]*>", "");
                if (revisionNotes != null && revisionNotes.length() > 255) {
                    errors.add(resPageMsg.getString("revision_notes_length_error"));
                }
                if (StringUtil.isBlank(revisionNotes)) {
                    errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("REVISION_NOTES_column") + " " + resPageMsg.getString("was_blank_in_the_CRF_worksheet"));
                    htmlErrors.put(j + ",1,3", resPageMsg.getString("required_field"));
                }
                // Generating query string for the new CRF
                Connection con = null;
                String crfOid = null;
                if (crfId == 0) {
                    crfOid = cdao.getValidOid(new CRFBean(), crfName);
                    int nextCRFId;
                    try {
                        con = ds.getConnection();
                        /*
                             * We are selecting the crf id which will be used to
                             * save the new CRF. Selecting the crf id in advance
                             * will not cause any problem in a multi threaded
                             * environment because the nextVal() method always
                             * returns unique values. So there is no chance of
                             * processing two CRF simultaneously with same crf
                             * id.
                             */
                        ResultSet nextIdRs;
                        if (dbName.equals("oracle")) {
                            nextIdRs = con.createStatement().executeQuery("select crf_id_seq.nextval from dual");
                        } else {
                            nextIdRs = con.createStatement().executeQuery("select nextval('crf_crf_id_seq')");
                        }
                        nextIdRs.next();
                        nextCRFId = nextIdRs.getInt(1);
                        crfId = nextCRFId;
                        ncrf.setCrfId(crfId);
                        String createCRFSql = "";
                        if (dbName.equals("oracle")) {
                            /*createCRFSql =
                                    "INSERT INTO CRF (CRF_ID, STATUS_ID, NAME, DESCRIPTION, OWNER_ID, DATE_CREATED, OC_OID, SOURCE_STUDY_ID) VALUES (" + crfId
                                        + ", 1,'" + stripQuotes(crfName) + "','" + stripQuotes(versionDesc) + "'," + ub.getId() + ",sysdate" + ",'" + crfOid
                                        + "'," + studyId + ")";*/
                            createCRFSql = "INSERT INTO CRF (CRF_ID, STATUS_ID, NAME, DESCRIPTION, OWNER_ID, DATE_CREATED, OC_OID, SOURCE_STUDY_ID) VALUES " + "(?, 1, ?, ?, ?, sysdate,? ,?)";
                        } else {
                            /* createCRFSql =
                                    "INSERT INTO CRF (CRF_ID, STATUS_ID, NAME, DESCRIPTION, OWNER_ID, DATE_CREATED, OC_OID, SOURCE_STUDY_ID) VALUES (" + crfId
                                        + ", 1,'" + stripQuotes(crfName) + "','" + stripQuotes(versionDesc) + "'," + ub.getId() + ",NOW()" + ",'" + crfOid
                                        + "'," + studyId + ")";*/
                            createCRFSql = "INSERT INTO CRF (CRF_ID, STATUS_ID, NAME, DESCRIPTION, OWNER_ID, DATE_CREATED, OC_OID, SOURCE_STUDY_ID) VALUES " + "(?, 1, ?, ?, ?, NOW(),? ,?)";
                        }
                        ArrayList<SqlParameter> sqlParameters = new ArrayList<>();
                        sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(crfName));
                        sqlParameters.add(new SqlParameter(versionDesc));
                        sqlParameters.add(new SqlParameter(ub.getId() + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(crfOid));
                        sqlParameters.add(new SqlParameter(studyId + "", JDBCType.INTEGER));
                        QueryObject qo = new QueryObject();
                        qo.setSql(createCRFSql);
                        qo.setSqlParameters(sqlParameters);
                        queries.add(qo);
                    } catch (SQLException e) {
                        logger.warn("Exception encountered with query select nextval('crf_crf_id_seq'), Message-" + e.getMessage());
                    } finally {
                        if (con != null) {
                            try {
                                con.close();
                            } catch (SQLException e) {
                                logger.warn("Connectin can't be closed");
                            }
                        }
                    }
                }
                // check for instrument existence here??? tbh 7/28
                // engaging in new validation, tbh, 6-4-04
                // modify nib.getinstversions to look for version name and
                // description
                // need to stop uploads of same name-description pairs
                HashMap checkCRFVersions = ncrf.getCrfVersions();
                // this now returns a hash map of key:version_name
                // ->value:version_description
                boolean overwrite = false;
                if (checkCRFVersions.containsKey(version)) {
                    logger.info("found a matching version name..." + version);
                    errors.add(resPageMsg.getString("version_not_unique_cause_confusion"));
                    htmlErrors.put(j + ",1,2", resPageMsg.getString("NOT_UNIQUE"));
                }
                // Create oid for Crf Version
                String oid;
                if (crfOid != null) {
                    oid = cvdao.getValidOid(new CRFVersionBean(), crfOid, version);
                } else {
                    CRFBean crfBean = (CRFBean) cdao.findByName(crfName);
                    oid = cvdao.getValidOid(new CRFVersionBean(), crfBean.getOid(), version);
                }
                String sql = "";
                ArrayList<SqlParameter> sqlParameters = new ArrayList<>();
                if (dbName.equals("oracle")) {
                    logger.warn("TEST 2");
                    if (crfId == 0) {
                        /*sql =
                                "INSERT INTO CRF_VERSION (NAME, DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) "
                                    + "VALUES ('" + stripQuotes(version) + "','" + stripQuotes(versionDesc) + "'," + "(SELECT CRF_ID FROM CRF WHERE NAME='"
                                    + crfName + "'),1,sysdate," + ub.getId() + ",'" + stripQuotes(revisionNotes) + "','" + oid + "')";*/
                        sql = "INSERT INTO CRF_VERSION (NAME,DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) " + "VALUES (?, ?, (SELECT CRF_ID FROM CRF WHERE NAME=?), 1, sysdate, ?, ?, ?)";
                        sqlParameters.add(new SqlParameter(version + ""));
                        sqlParameters.add(new SqlParameter(versionDesc));
                        sqlParameters.add(new SqlParameter(crfName));
                        sqlParameters.add(new SqlParameter(ub.getId() + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(revisionNotes));
                        sqlParameters.add(new SqlParameter(oid + ""));
                    } else {
                        /* sql =
                                "INSERT INTO CRF_VERSION (NAME,DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) " + "VALUES ('"
                                    + version + "','" + stripQuotes(versionDesc) + "'," + crfId + ",1,sysdate," + ub.getId() + ",'"
                                    + stripQuotes(revisionNotes) + "','" + oid + "')";*/
                        sql = "INSERT INTO CRF_VERSION (NAME,DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) " + "VALUES (?, ?, ?, 1, sysdate, ?, ?, ?)";
                        sqlParameters.add(new SqlParameter(version + ""));
                        sqlParameters.add(new SqlParameter(versionDesc));
                        sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(ub.getId() + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(revisionNotes));
                        sqlParameters.add(new SqlParameter(oid + ""));
                    }
                } else {
                    if (crfId == 0) {
                        /*sql =
                                "INSERT INTO CRF_VERSION (NAME, DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) "
                                    + "VALUES ('" + stripQuotes(version) + "','" + stripQuotes(versionDesc) + "'," + "(SELECT CRF_ID FROM CRF WHERE NAME='"
                                    + crfName + "'),1,NOW()," + ub.getId() + ",'" + stripQuotes(revisionNotes) + "','" + oid + "')";*/
                        sql = "INSERT INTO CRF_VERSION (NAME,DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) " + " VALUES (?, ?, (SELECT CRF_ID FROM CRF WHERE NAME=?), 1, NOW(), ?, ?, ?)";
                        sqlParameters.add(new SqlParameter(version + ""));
                        sqlParameters.add(new SqlParameter(versionDesc));
                        sqlParameters.add(new SqlParameter(crfName));
                        sqlParameters.add(new SqlParameter(ub.getId() + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(revisionNotes));
                        sqlParameters.add(new SqlParameter(oid + ""));
                    } else {
                        /* sql =
                                "INSERT INTO CRF_VERSION (NAME,DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) " + "VALUES ('"
                                    + version + "','" + stripQuotes(versionDesc) + "'," + crfId + ",1,NOW()," + ub.getId() + ",'" + stripQuotes(revisionNotes)
                                    + "','" + oid + "')";*/
                        sql = "INSERT INTO CRF_VERSION (NAME,DESCRIPTION, CRF_ID, STATUS_ID,DATE_CREATED," + "OWNER_ID,REVISION_NOTES,OC_OID) " + " VALUES (?, ?, ?, 1, NOW(), ?, ?, ?)";
                        sqlParameters.add(new SqlParameter(version + ""));
                        sqlParameters.add(new SqlParameter(versionDesc));
                        sqlParameters.add(new SqlParameter(crfId + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(ub.getId() + "", JDBCType.INTEGER));
                        sqlParameters.add(new SqlParameter(revisionNotes));
                        sqlParameters.add(new SqlParameter(oid + ""));
                    }
                }
                QueryObject qo = new QueryObject();
                qo.setSql(sql);
                qo.setSqlParameters(sqlParameters);
                queries.add(qo);
                for (int i = 0; i < queries.size(); i++) {
                    String s = (String) queries.get(i);
                    logger.info("====================" + s);
                }
                pVersion = version;
                pVerDesc = versionDesc;
            }
            /*versionIdString = "(SELECT CRF_VERSION_ID FROM CRF_VERSION WHERE NAME ='" + pVersion + "' AND CRF_ID=" + crfId + ")";*/
            versionIdString = "(SELECT CRF_VERSION_ID FROM CRF_VERSION WHERE NAME ='" + pVersion + "' AND CRF_ID=?)";
            // move html creation to here, include error creation as well,
            // tbh 7/28
            buf.append(sheetName + "<br>");
            buf.append("<div class=\"box_T\"><div class=\"box_L\"><div class=\"box_R\"><div class=\"box_B\"><div class=\"box_TL\"><div class=\"box_TR\"><div class=\"box_BL\"><div class=\"box_BR\">");
            buf.append("<div class=\"textbox_center\">");
            buf.append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\"");
            buf.append("caption=\"" + wb.getSheetName(j) + "\"" + ">");
            for (int i = 0; i < numRows; i++) {
                buf.append("<tr>");
                if (sheet.getRow(i) == null) {
                    continue;
                }
                int numCells = sheet.getRow(i).getLastCellNum();
                for (int y = 0; y < numCells; y++) {
                    HSSFCell cell = sheet.getRow(i).getCell((short) y);
                    int cellType = 0;
                    String error = "&nbsp;";
                    String errorKey = j + "," + i + "," + y;
                    if (htmlErrors.containsKey(errorKey)) {
                        error = "<span class=\"alert\">" + htmlErrors.get(errorKey) + "</span>";
                    }
                    if (cell == null) {
                        cellType = HSSFCell.CELL_TYPE_BLANK;
                    } else {
                        cellType = cell.getCellType();
                    }
                    switch(cellType) {
                        case HSSFCell.CELL_TYPE_BLANK:
                            buf.append("<td class=\"table_cell\">" + error + "</td>");
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            buf.append("<td class=\"table_cell\">" + cell.getNumericCellValue() + " " + error + "</td>");
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            buf.append("<td class=\"table_cell\">" + cell.getStringCellValue() + " " + error + "</td>");
                            break;
                        default:
                            buf.append("<td class=\"table_cell\">" + error + "</td>");
                    }
                }
                buf.append("</tr>");
            }
            buf.append("</table>");
            buf.append("<br></div>");
            buf.append("</div></div></div></div></div></div></div></div>");
            buf.append("</div><br>");
        }
    // end of the else sheet loop
    }
    // end of the for loop for sheets
    ncrf.setQueries(queries);
    ncrf.setItemQueries(openQueries);
    ncrf.setBackupItemQueries(backupItemQueries);
    ncrf.setItems(items);
    if (!errors.isEmpty()) {
        ncrf.setErrors(errors);
    }
    // logger.info("html table:" + buf.toString());
    ncrf.setHtmlTable(buf.toString());
    return ncrf;
}
Also used : ItemBean(org.akaza.openclinica.bean.submit.ItemBean) ItemDAO(org.akaza.openclinica.dao.submit.ItemDAO) SQLException(java.sql.SQLException) ItemGroupDAO(org.akaza.openclinica.dao.submit.ItemGroupDAO) CRFReadingException(org.akaza.openclinica.exception.CRFReadingException) ItemDataDAO(org.akaza.openclinica.dao.submit.ItemDataDAO) ResultSet(java.sql.ResultSet) PatternSyntaxException(java.util.regex.PatternSyntaxException) Pattern(java.util.regex.Pattern) CRFDAO(org.akaza.openclinica.dao.admin.CRFDAO) SqlParameter(org.akaza.openclinica.bean.admin.SqlParameter) CRFVersionDAO(org.akaza.openclinica.dao.submit.CRFVersionDAO) Connection(java.sql.Connection) MeasurementUnitOidGenerator(org.akaza.openclinica.bean.oid.MeasurementUnitOidGenerator) QueryObject(org.akaza.openclinica.bean.admin.QueryObject) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook) SQLException(java.sql.SQLException) PatternSyntaxException(java.util.regex.PatternSyntaxException) CRFReadingException(org.akaza.openclinica.exception.CRFReadingException) IOException(java.io.IOException) NewCRFBean(org.akaza.openclinica.bean.admin.NewCRFBean) CRFBean(org.akaza.openclinica.bean.admin.CRFBean) NewCRFBean(org.akaza.openclinica.bean.admin.NewCRFBean) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) ScoreValidator(org.akaza.openclinica.logic.score.ScoreValidator) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) CRFVersionBean(org.akaza.openclinica.bean.submit.CRFVersionBean) ResponseSetBean(org.akaza.openclinica.bean.submit.ResponseSetBean) ItemGroupBean(org.akaza.openclinica.bean.submit.ItemGroupBean) ItemFormMetadataBean(org.akaza.openclinica.bean.submit.ItemFormMetadataBean)

Aggregations

NewCRFBean (org.akaza.openclinica.bean.admin.NewCRFBean)6 ArrayList (java.util.ArrayList)5 CRFVersionDAO (org.akaza.openclinica.dao.submit.CRFVersionDAO)5 IOException (java.io.IOException)4 ItemBean (org.akaza.openclinica.bean.submit.ItemBean)4 CRFDAO (org.akaza.openclinica.dao.admin.CRFDAO)4 CRFReadingException (org.akaza.openclinica.exception.CRFReadingException)4 CRFBean (org.akaza.openclinica.bean.admin.CRFBean)3 CRFVersionBean (org.akaza.openclinica.bean.submit.CRFVersionBean)3 EventCRFDAO (org.akaza.openclinica.dao.submit.EventCRFDAO)3 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)3 File (java.io.File)2 Connection (java.sql.Connection)2 ResultSet (java.sql.ResultSet)2 SQLException (java.sql.SQLException)2 HashMap (java.util.HashMap)2 Pattern (java.util.regex.Pattern)2 PatternSyntaxException (java.util.regex.PatternSyntaxException)2 QueryObject (org.akaza.openclinica.bean.admin.QueryObject)2 SqlParameter (org.akaza.openclinica.bean.admin.SqlParameter)2