Search in sources :

Example 31 with ItemGroupMetadataBean

use of org.akaza.openclinica.bean.submit.ItemGroupMetadataBean in project OpenClinica by OpenClinica.

the class DataEntryServlet method isEachRequiredFieldFillout.

protected boolean isEachRequiredFieldFillout(HttpServletRequest request) {
    HttpSession session = request.getSession();
    EventCRFBean ecb = (EventCRFBean) request.getAttribute(INPUT_EVENT_CRF);
    DiscrepancyNoteDAO dndao = new DiscrepancyNoteDAO(getDataSource());
    // need to update this method to accomodate dynamics, tbh
    ItemDataDAO iddao = new ItemDataDAO(getDataSource(), locale);
    ItemDAO idao = new ItemDAO(getDataSource());
    ItemFormMetadataDAO itemFormMetadataDao = new ItemFormMetadataDAO(getDataSource());
    // Below code will iterate all shown and hidden required fields/items in a crf version and verify if the data field is filled up with value or if not , then it is a hidden field with no show rule triggered for the item.        
    ArrayList<ItemFormMetadataBean> shownRequiredAllItemsInCrfVersion = itemFormMetadataDao.findAllItemsRequiredAndShownByCrfVersionId(ecb.getCRFVersionId());
    ArrayList<ItemFormMetadataBean> hiddenRequiredAllItemsInCrfVersion = itemFormMetadataDao.findAllItemsRequiredAndHiddenByCrfVersionId(ecb.getCRFVersionId());
    ItemGroupMetadataDAO<String, ArrayList> igdao = new ItemGroupMetadataDAO<String, ArrayList>(dataSource);
    ArrayList<ItemDataBean> itemdatas = null;
    for (ItemFormMetadataBean shownItemMeta : shownRequiredAllItemsInCrfVersion) {
        ItemGroupMetadataBean igBean = (ItemGroupMetadataBean) igdao.findByItemAndCrfVersion(shownItemMeta.getItemId(), ecb.getCRFVersionId());
        // verifies if the group that the item belongs to is not hidden.
        if (igBean != null && igBean.isShowGroup()) {
            itemdatas = iddao.findAllByEventCRFIdAndItemId(ecb.getId(), shownItemMeta.getItemId());
            if (itemdatas == null || itemdatas.size() == 0)
                return false;
            for (ItemDataBean itemdata : itemdatas) {
                System.out.println(itemdata.getItemId() + "  :  " + itemdata.getValue());
                if ((itemdata.getValue() == null || itemdata.getValue().equals("") || itemdata.getValue().trim().length() == 0) && dndao.findNumExistingNotesForItem(itemdata.getId()) < 1) {
                    return false;
                }
            }
        }
        ArrayList<DynamicsItemFormMetadataBean> dynamicsItemFormMetadataBeans = null;
        for (ItemFormMetadataBean hiddenItemMeta : hiddenRequiredAllItemsInCrfVersion) {
            itemdatas = iddao.findAllByEventCRFIdAndItemId(ecb.getId(), hiddenItemMeta.getItemId());
            dynamicsItemFormMetadataBeans = getItemMetadataService().getDynamicsItemFormMetadataDao().findByItemAndEventCrfShown(ecb, hiddenItemMeta.getItemId());
            if (itemdatas.size() == 0 && dynamicsItemFormMetadataBeans.size() > 0) {
                return false;
            }
            for (ItemDataBean itemdata : itemdatas) {
                if ((itemdata.getValue() == null || itemdata.getValue().equals("") || itemdata.getValue().trim().length() == 0) && dndao.findNumExistingNotesForItem(itemdata.getId()) < 1 && dynamicsItemFormMetadataBeans.size() > 0) {
                    return false;
                }
            }
        }
    }
    // had to change the query below to allow for hidden items here, tbh 04/2010
    ArrayList allFilled = iddao.findAllBlankRequiredByEventCRFId(ecb.getId(), ecb.getCRFVersionId());
    int numNotes = 0;
    if (!allFilled.isEmpty()) {
        LOGGER.trace("allFilled is not empty");
        FormDiscrepancyNotes fdn = (FormDiscrepancyNotes) session.getAttribute(AddNewSubjectServlet.FORM_DISCREPANCY_NOTES_NAME);
        HashMap idNotes = fdn.getIdNotes();
        for (int i = 0; i < allFilled.size(); i++) {
            ItemDataBean idb = (ItemDataBean) allFilled.get(i);
            int exsitingNotes = dndao.findNumExistingNotesForItem(idb.getId());
            if (exsitingNotes > 0) {
                LOGGER.trace("has existing note");
                numNotes++;
            } else if (idNotes.containsKey(idb.getId())) {
                LOGGER.trace("has note in session");
                numNotes++;
            }
        }
        LOGGER.trace("numNotes allFilled.size:" + numNotes + " " + allFilled.size());
        if (numNotes >= allFilled.size()) {
            LOGGER.trace("all required are filled out");
            return true;
        } else {
            LOGGER.debug("numNotes < allFilled.size() " + numNotes + ": " + allFilled.size());
            return false;
        }
    }
    return true;
}
Also used : DiscrepancyNoteDAO(org.akaza.openclinica.dao.managestudy.DiscrepancyNoteDAO) ItemDAO(org.akaza.openclinica.dao.submit.ItemDAO) FormDiscrepancyNotes(org.akaza.openclinica.control.form.FormDiscrepancyNotes) HashMap(java.util.HashMap) HttpSession(javax.servlet.http.HttpSession) ArrayList(java.util.ArrayList) ItemGroupMetadataBean(org.akaza.openclinica.bean.submit.ItemGroupMetadataBean) ItemDataDAO(org.akaza.openclinica.dao.submit.ItemDataDAO) ItemGroupMetadataDAO(org.akaza.openclinica.dao.submit.ItemGroupMetadataDAO) EventCRFBean(org.akaza.openclinica.bean.submit.EventCRFBean) ItemDataBean(org.akaza.openclinica.bean.submit.ItemDataBean) DynamicsItemFormMetadataBean(org.akaza.openclinica.domain.crfdata.DynamicsItemFormMetadataBean) ItemFormMetadataDAO(org.akaza.openclinica.dao.submit.ItemFormMetadataDAO) DynamicsItemFormMetadataBean(org.akaza.openclinica.domain.crfdata.DynamicsItemFormMetadataBean) ItemFormMetadataBean(org.akaza.openclinica.bean.submit.ItemFormMetadataBean)

Example 32 with ItemGroupMetadataBean

use of org.akaza.openclinica.bean.submit.ItemGroupMetadataBean in project OpenClinica by OpenClinica.

the class OpenRosaXmlGenerator method buildInstance.

// method
/**
     * @param model
     * @param crfVersion
     * @param crfSections
     * @return
     * @throws Exception
     */
private String buildInstance(Model model, FormLayoutBean formLayout, CRFVersionBean crfVersion, ArrayList<SectionBean> crfSections) throws Exception {
    DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
    DocumentBuilder build = docFactory.newDocumentBuilder();
    Document doc = build.newDocument();
    Element crfElement = doc.createElement(formLayout.getOid());
    crfElement.setAttribute("id", formLayout.getOid());
    doc.appendChild(crfElement);
    crfElement.setAttributeNS("http://www.w3.org/2000/xmlns/", "xmlns:jr", "http://openrosa.org/javarosa");
    for (SectionBean section : crfSections) {
        Element sectionSubTitle = doc.createElement("SECTION_" + section.getId() + ".SUBTITLE");
        Element sectionInstructions = doc.createElement("SECTION_" + section.getId() + ".INSTRUCTIONS");
        Element sectionElm = doc.createElement("SECTION_" + section.getLabel().replaceAll("\\W", "_"));
        crfElement.appendChild(sectionSubTitle);
        crfElement.appendChild(sectionInstructions);
        crfElement.appendChild(sectionElm);
    }
    ArrayList<ItemGroupBean> itemGroupBeans = getItemGroupBeansByFormLayout(formLayout);
    for (ItemGroupBean itemGroupBean : itemGroupBeans) {
        ItemGroupMetadataBean itemGroupMetadataBean = getItemGroupMetadataByGroup(itemGroupBean, crfVersion);
        String repeatGroupMin = itemGroupMetadataBean.getRepeatNum().toString();
        Boolean isrepeating = itemGroupMetadataBean.isRepeatingGroup();
        Element groupElement = doc.createElement(itemGroupBean.getOid());
        if (isrepeating) {
            groupElement.setTextContent(repeatGroupMin);
            groupElement.setAttribute("jr:template", "");
            Element hiddenOrdinalItem = doc.createElement("OC.REPEAT_ORDINAL");
            groupElement.appendChild(hiddenOrdinalItem);
        }
        crfElement.appendChild(groupElement);
        idao = new ItemDAO(dataSource);
        ArrayList<ItemBean> items = (ArrayList<ItemBean>) idao.findAllItemsByGroupIdOrdered(itemGroupBean.getId(), crfVersion.getId());
        for (ItemBean item : items) {
            ItemFormMetadataBean itemMetaData = getItemFormMetadata(item, crfVersion);
            if (itemMetaData.getHeader() != null && !itemMetaData.getHeader().equals("")) {
                Element header = doc.createElement(item.getOid() + ".HEADER");
                groupElement.appendChild(header);
            }
            if (itemMetaData.getHeader() != null && !itemMetaData.getSubHeader().equals("")) {
                Element subHeader = doc.createElement(item.getOid() + ".SUBHEADER");
                groupElement.appendChild(subHeader);
            }
            Element question = doc.createElement(item.getOid());
            groupElement.appendChild(question);
        }
    // end of item
    }
    // end of group
    // add meta
    Element meta = doc.createElement("meta");
    // add instanceId
    Element instanceId = doc.createElement("instanceID");
    meta.appendChild(instanceId);
    crfElement.appendChild(meta);
    TransformerFactory transformFactory = TransformerFactory.newInstance();
    Transformer transformer = transformFactory.newTransformer();
    transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
    transformer.setOutputProperty(OutputKeys.INDENT, "yes");
    StringWriter writer = new StringWriter();
    StreamResult result = new StreamResult(writer);
    DOMSource source = new DOMSource(doc);
    transformer.transform(source, result);
    return writer.toString();
}
Also used : ItemBean(org.akaza.openclinica.bean.submit.ItemBean) DOMSource(javax.xml.transform.dom.DOMSource) DocumentBuilderFactory(javax.xml.parsers.DocumentBuilderFactory) TransformerFactory(javax.xml.transform.TransformerFactory) Transformer(javax.xml.transform.Transformer) StreamResult(javax.xml.transform.stream.StreamResult) ItemDAO(org.akaza.openclinica.dao.submit.ItemDAO) Element(org.w3c.dom.Element) ArrayList(java.util.ArrayList) Document(org.w3c.dom.Document) ItemGroupMetadataBean(org.akaza.openclinica.bean.submit.ItemGroupMetadataBean) SectionBean(org.akaza.openclinica.bean.submit.SectionBean) StringWriter(java.io.StringWriter) DocumentBuilder(javax.xml.parsers.DocumentBuilder) ItemGroupBean(org.akaza.openclinica.bean.submit.ItemGroupBean) ItemFormMetadataBean(org.akaza.openclinica.bean.submit.ItemFormMetadataBean)

Example 33 with ItemGroupMetadataBean

use of org.akaza.openclinica.bean.submit.ItemGroupMetadataBean in project OpenClinica by OpenClinica.

the class OpenRosaXmlGenerator method getGroupInfo.

/**
     * @param itemGroupBean
     * @param crfVersion
     * @param section
     * @param factory
     * @param bindList
     * @return
     * @throws Exception
     */
private HashMap<String, Object> getGroupInfo(ItemGroupBean itemGroupBean, CRFVersionBean crfVersion, SectionBean section, WidgetFactory factory, ArrayList<Bind> bindList) throws Exception {
    boolean expressionEvaluate = true;
    igmdao = new ItemGroupMetadataDAO(dataSource);
    List<ItemGroupMetadataBean> itemGroupMetadata = igmdao.findMetaByGroupAndSection(itemGroupBean.getId(), crfVersion.getId(), section.getId());
    HashMap<String, Object> map = new HashMap<String, Object>();
    Group group = new Group();
    Repeat repeat = new Repeat();
    group.setUsercontrol(new ArrayList<UserControl>());
    repeat.setUsercontrol(new ArrayList<UserControl>());
    Label groupHeader = new Label();
    groupHeader.setLabel(itemGroupMetadata.get(0).getHeader());
    boolean isGroupRepeating = getItemGroupMetadata(itemGroupBean, crfVersion, section).isRepeatingGroup();
    String nodeset = "/" + crfVersion.getOid() + "/" + itemGroupBean.getOid();
    // repeat.setJrNoAddRemove("true()");
    repeat.setJrCount(nodeset);
    group.setRef(nodeset);
    repeat.setNodeset(nodeset);
    String groupExpression = null;
    ExpressionExpressionEvaluate eev = getSkipPattern(null, itemGroupBean);
    groupExpression = eev.getExpression();
    expressionEvaluate = eev.isExpressionEvaluate();
    if (groupExpression != null)
        groupExpression = getFullExpressionToParse(groupExpression, crfVersion, expressionEvaluate);
    setGroupWidget(section, crfVersion, groupExpression, bindList, factory, itemGroupBean, group, repeat, isGroupRepeating, groupHeader);
    map.put("group", group);
    map.put("repeat", repeat);
    map.put("isGroupRepeating", isGroupRepeating);
    return map;
}
Also used : Group(org.akaza.openclinica.web.pform.dto.Group) HashMap(java.util.HashMap) Label(org.akaza.openclinica.web.pform.dto.Label) Repeat(org.akaza.openclinica.web.pform.dto.Repeat) ItemGroupMetadataBean(org.akaza.openclinica.bean.submit.ItemGroupMetadataBean) ItemGroupMetadataDAO(org.akaza.openclinica.dao.submit.ItemGroupMetadataDAO) UserControl(org.akaza.openclinica.web.pform.dto.UserControl)

Example 34 with ItemGroupMetadataBean

use of org.akaza.openclinica.bean.submit.ItemGroupMetadataBean in project OpenClinica by OpenClinica.

the class SpreadSheetTableRepeating 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 groupItemMapQueries = new ArrayList();
    ArrayList errors = new ArrayList();
    // ArrayList repeats = new ArrayList();
    HashMap tableNames = new HashMap();
    HashMap items = new HashMap();
    SpreadSheetItemUtil item_from_row = null;
    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();
    ArrayList<String> itemGroupOids = new ArrayList<String>();
    ArrayList<String> itemOids = new ArrayList<String>();
    CRFDAO cdao = new CRFDAO(ds);
    CRFBean crf = (CRFBean) cdao.findByPK(crfId);
    ItemDAO idao = new ItemDAO(ds);
    CRFVersionDAO cvdao = new CRFVersionDAO(ds);
    ItemGroupDAO itemGroupDao = new ItemGroupDAO(ds);
    SheetValidationContainer sheetContainer = new SheetValidationContainer();
    HashMap<String, String> allItems = (HashMap<String, String>) sheetContainer.getAllItems();
    //HashMap<String, String> allItems = new HashMap<String, String>();
    Map<String, String[]> controlValues = new HashMap<String, String[]>();
    int maxItemFormMetadataId = new ItemFormMetadataDAO(ds).findMaxId();
    OnChangeSheetValidator instantValidator = new OnChangeSheetValidator(sheetContainer, resPageMsg);
    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.");
    }
    HSSFSheet sheet = wb.getSheetAt(4);
    HSSFCell insCell = sheet.getRow(1).getCell((short) 0);
    String versionNo = insCell.toString();
    // 7/30
    for (int j = 0; j < numSheets; j++) {
        // sheetIndex);
        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.debug("PhysicalNumberOfRows" +
            // sheet.getPhysicalNumberOfRows());
            // great minds apparently think alike...tbh, commented out
            // 06/19/2007
            // logger.debug("LastRowNum()" + sheet.getLastRowNum());
            String secName = "";
            String page = "";
            // YW << for holding "responseLabel_responseType"
            ArrayList resPairs = new ArrayList();
            // YW >>
            // 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;
            String itemName = null;
            String default_value = null;
            if (sheetName.equalsIgnoreCase("Items")) {
                logger.debug("read an item in sheet" + sheetName);
                Map labelWithOptions = new HashMap();
                Map labelWithValues = new HashMap();
                Map labelWithType = new HashMap<String, String>();
                logger.debug("row20 is: " + getValue(sheet.getRow(0).getCell((short) 20)));
                boolean hasWDColumn = "width_decimal".equalsIgnoreCase(getValue(sheet.getRow(0).getCell((short) 20))) ? true : false;
                //Adding itemnames for further use
                // HashMap itemNames = new HashMap();
                //htaycher : code should be competly refactored to use stucture to hold all data per row
                ArrayList<SpreadSheetItemUtil> row_items = new ArrayList<SpreadSheetItemUtil>();
                for (int k = 1; k < numRows; k++) {
                    if (sheet.getRow(k) == null) {
                        blankRowCount++;
                        if (blankRowCount == 5) {
                            break;
                        }
                        continue;
                    }
                    int cellIndex = 0;
                    HSSFCell cell = sheet.getRow(k).getCell((short) 0);
                    item_from_row = new SpreadSheetItemUtil();
                    row_items.add(item_from_row);
                    item_from_row.setItemName(getValue(cell));
                    item_from_row.verifyItemName(row_items, errors, htmlErrors, j, resPageMsg);
                    itemName = item_from_row.getItemName();
                    //}
                    cell = sheet.getRow(k).getCell((short) 1);
                    String descLabel = getValue(cell);
                    descLabel = descLabel.replaceAll("<[^>]*>", "");
                    item_from_row.setDescriptionLabel(descLabel);
                    if (StringUtil.isBlank(descLabel)) {
                        // errors.add("The DESCRIPTION_LABEL column was
                        // blank at row " + k + ", Items worksheet.");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DESCRIPTION_LABEL_column") + " " + resPageMsg.getString("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"));
                    }
                    item_from_row.setLeftItemText(leftItemText);
                    // 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 = "";
                        //htaycher max length=64
                        if (unit.length() > 64) {
                            errors.add(resPageMsg.getString("units_length_error"));
                            htmlErrors.put(j + "," + k + "," + CrfTemplateColumnNameEnum.UNITS.getCellNumber(), resPageMsg.getString("INVALID_FIELD"));
                        }
                        if (this.existingUnits.size() > 0) {
                        } else {
                            this.existingUnits = this.measurementUnitDao.findAllNames();
                            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)) {
                            this.logger.debug("unit=" + unit + " existed.");
                        } else {
                            String oid = "";
                            try {
                                oid = new MeasurementUnitOidGenerator().generateOid(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);
                            muSql = this.getMUInsertSql(oid, unit, ub.getId(), dbName);
                            queries.add(muSql);
                        }
                    }
                    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"));
                    }
                    //section label
                    cell = sheet.getRow(k).getCell((short) 5);
                    item_from_row.setSectionLabel(getValue(cell));
                    item_from_row.verifySectionLabel(row_items, errors, secNames, htmlErrors, j, resPageMsg);
                    secName = item_from_row.getSectionLabel();
                    // *******************************************
                    // group_label will go here, tbh in place 6
                    // have to advance all the rest by one at least (if
                    // there are
                    // no other columns) tbh, 5-14-2007
                    //group label
                    cell = sheet.getRow(k).getCell((short) 6);
                    item_from_row.setGroupLabel(getValue(cell));
                    //is it a reason for 13816
                    if (item_from_row.getItemName().length() > 0) {
                        if (!StringUtil.isBlank(item_from_row.getGroupLabel())) {
                            allItems.put(item_from_row.getItemName(), item_from_row.getGroupLabel());
                        } else {
                            allItems.put(item_from_row.getItemName(), "Ungrouped");
                        }
                    }
                    String groupLabel = item_from_row.getGroupLabel();
                    sheetContainer.getItemSectionNameMap().put(itemName, secName);
                    sheetContainer.collectRepGrpItemNameMap(itemName, item_from_row.getGroupLabel());
                    //header
                    cell = sheet.getRow(k).getCell((short) 7);
                    String header = getValue(cell);
                    if (header != null && header.length() > 2000) {
                        errors.add(resPageMsg.getString("item_header_length_error"));
                    }
                    //subheader
                    cell = sheet.getRow(k).getCell((short) 8);
                    String subHeader = getValue(cell);
                    if (subHeader != null && subHeader.length() > 240) {
                        errors.add(resPageMsg.getString("item_subheader_length_error"));
                        htmlErrors.put(j + "," + k + "," + CrfTemplateColumnNameEnum.SUBHEADER.getCellNumber(), resPageMsg.getString("INVALID_FIELD"));
                    }
                    //parentid
                    cell = sheet.getRow(k).getCell((short) 9);
                    String parentItem = getValue(cell);
                    item_from_row.setParentItem(parentItem);
                    item_from_row.verifyParentID(row_items, errors, htmlErrors, j, resPageMsg, itemGroups);
                    //for now , when(if ) code refactoring will be done, item will be written by SpreadSheetItemUtil
                    parentItem = item_from_row.getParentItem();
                    //column id
                    cell = sheet.getRow(k).getCell((short) 10);
                    int columnNum = 0;
                    String column = getValue(cell);
                    if (!StringUtil.isBlank(column)) {
                        try {
                            columnNum = Integer.parseInt(column);
                        } catch (NumberFormatException ne) {
                            columnNum = 0;
                        }
                    }
                    //page number
                    cell = sheet.getRow(k).getCell((short) 11);
                    if (cell != null) {
                        page = getValue(cell);
                    }
                    //question number
                    cell = sheet.getRow(k).getCell((short) 12);
                    String questionNum = getValue(cell);
                    //response type
                    cell = sheet.getRow(k).getCell((short) 13);
                    String responseType = getValue(cell);
                    int responseTypeId = 1;
                    if (StringUtil.isBlank(responseType)) {
                        // errors.add("The RESPONSE_TYPE column was blank at
                        // row " + k + ", items worksheet.");
                        // htmlErrors.put(j + "," + k + ",13", "REQUIRED
                        // FIELD");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_TYPE_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                        htmlErrors.put(j + "," + k + ",13", resPageMsg.getString("required_field"));
                    } else {
                        if (!ResponseType.findByName(responseType.toLowerCase())) {
                            // errors.add("The RESPONSE_TYPE column was
                            // invalid at row " + k
                            // + ", items worksheet.");
                            // htmlErrors.put(j + "," + k + ",13", "INVALID
                            // FIELD");
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_TYPE_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                            htmlErrors.put(j + "," + k + ",13", resPageMsg.getString("INVALID_FIELD"));
                        } else {
                            responseTypeId = ResponseType.getByName(responseType.toLowerCase()).getId();
                            item_from_row.setResponseTypeId(responseTypeId);
                        }
                        if (responseTypeId == 5) {
                            cell = sheet.getRow(k).getCell((short) 18);
                            String def = getValue(cell);
                            if (!StringUtil.isBlank(def)) {
                                errors.add(resPageMsg.getString("radio_with_default") + item_from_row.getItemName() + resPageMsg.getString("change_radio"));
                                htmlErrors.put(j + "," + k + "," + CrfTemplateColumnNameEnum.DEFAULT_VALUE.getCellNumber(), resPageMsg.getString("INVALID_FIELD"));
                            }
                        } else if (responseTypeId == ResponseType.INSTANT_CALCULATION.getId()) {
                            unit = "";
                        }
                    }
                    cell = sheet.getRow(k).getCell((short) 14);
                    String responseLabel = getValue(cell);
                    if (StringUtil.isBlank(responseLabel) && responseTypeId != ResponseType.TEXT.getId() && responseTypeId != ResponseType.TEXTAREA.getId()) {
                        // << tbh #4180
                        // errors.add("The RESPONSE_LABEL column was blank
                        // at row " + k + ", items worksheet.");
                        // htmlErrors.put(j + "," + k + ",14", "REQUIRED
                        // FIELD");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_LABEL_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                        htmlErrors.put(j + "," + k + ",14", 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 + ",14", resPageMsg.getString("should_be_file"));
                    }
                    cell = sheet.getRow(k).getCell((short) 15);
                    String resOptions = getValue(cell);
                    // >> tbh #4180, we cant have blanks since they will trip us up later in the process
                    if (responseTypeId == ResponseType.TEXT.getId()) {
                        responseLabel = "text";
                    } else if (responseTypeId == ResponseType.TEXTAREA.getId()) {
                        responseLabel = "textarea";
                    }
                    // << tbh
                    if (responseLabel.equalsIgnoreCase("text") || responseLabel.equalsIgnoreCase("textarea")) {
                        resOptions = "text";
                    } else if ("file".equalsIgnoreCase(responseType)) {
                        resOptions = "file";
                    }
                    int numberOfOptions = 0;
                    if (!resNames.contains(responseLabel) && StringUtil.isBlank(resOptions) && responseTypeId != ResponseType.TEXT.getId() && responseTypeId != ResponseType.TEXTAREA.getId()) {
                        // << tbh #4180
                        // errors.add("The RESPONSE_OPTIONS_TEXT column was
                        // blank at row " + k
                        // + ", Items worksheet.");
                        // htmlErrors.put(j + "," + k + ",15", "REQUIRED
                        // FIELD");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_OPTIONS_TEXT_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                        htmlErrors.put(j + "," + k + ",15", resPageMsg.getString("required_field"));
                    }
                    if (!resNames.contains(responseLabel) && !StringUtil.isBlank(resOptions)) {
                        if (responseTypeId == 8 || responseTypeId == 9) {
                            // YW 1-29-2008 << only one option for
                            // "calculation" type and "group-calculation"
                            // type
                            // but do we really need this variable these two
                            // types?
                            numberOfOptions = 1;
                        // YW >>
                        } else {
                            // String[] resArray = resOptions.split(",");
                            String text1 = resOptions.replaceAll("\\\\,", "##");
                            String[] resArray = text1.split(",");
                            numberOfOptions = resArray.length;
                        }
                    }
                    /**
                         * The application will show error on page if two
                         * identical RESPONSE_LABEL has different
                         * RESPONSE_OPTIONS_TEXT
                         */
                    String[] mapResArray = (String[]) labelWithOptions.get(responseLabel);
                    String text1 = resOptions.replaceAll("\\\\,", "##");
                    String[] resArray = text1.split(",");
                    item_from_row.setResponseOptions(resArray);
                    logger.debug(item_from_row.getItemName());
                    if (labelWithOptions.containsKey(responseLabel)) {
                        if (!StringUtil.isBlank(resOptions)) {
                            for (int i = 0; i < resArray.length; i++) {
                                if (!resArray[i].equals(mapResArray[i])) {
                                    errors.add(resPageMsg.getString("resp_label_with_different_resp_options") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                                    htmlErrors.put(j + "," + k + ",15", resPageMsg.getString("resp_label_with_different_resp_options_html_error"));
                                    break;
                                }
                            }
                        }
                    } else {
                        labelWithOptions.put(responseLabel, resArray);
                    }
                    cell = sheet.getRow(k).getCell((short) 16);
                    String resValues = getValue(cell);
                    if (responseLabel.equalsIgnoreCase("text") || responseLabel.equalsIgnoreCase("textarea")) {
                        resValues = "text";
                    } else if ("file".equalsIgnoreCase(responseType)) {
                        resValues = "file";
                    }
                    if (!resNames.contains(responseLabel) && StringUtil.isBlank(resValues) && responseTypeId != ResponseType.TEXT.getId() && responseTypeId != ResponseType.TEXTAREA.getId() && responseTypeId != ResponseType.INSTANT_CALCULATION.getId()) {
                        // << tbh #4180
                        // errors.add("The RESPONSE_VALUES column was blank
                        // at row " + k + ", Items worksheet.");
                        // htmlErrors.put(j + "," + k + ",16", "REQUIRED
                        // FIELD");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("RESPONSE_VALUES_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                        htmlErrors.put(j + ", " + k + ",16", resPageMsg.getString("required_field"));
                    }
                    // YW 1-25-2008 << validate scoring expression
                    if (responseTypeId == ResponseType.CALCULATION.getId() || responseTypeId == ResponseType.GROUP_CALCULATION.getId()) {
                        // 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 + ",16", 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 + ",16", resPageMsg.getString("INVALID_FIELD"));
                        }
                        if (exp.startsWith("getexternalvalue") || exp.startsWith("getExternalValue")) {
                        // do a different set of validations here, tbh
                        } else {
                            String group = groupLabel.length() > 0 ? groupLabel : "Ungrouped";
                            for (String v : variables) {
                                if (!allItems.containsKey(v)) {
                                    errors.add("Item '" + v + "' must be listed before the item '" + itemName + "' at row " + k + ", items worksheet. ");
                                    htmlErrors.put(j + "," + k + ",16", "INVALID FIELD");
                                } else {
                                    if (responseTypeId == 8 && !allItems.get(v).equalsIgnoreCase(group)) {
                                        errors.add("Item '" + v + "' and item '" + itemName + "' must have a same GROUP_LABEL at row " + k + ", items worksheet. ");
                                        htmlErrors.put(j + "," + k + ",16", "INVALID FIELD");
                                    } else if (responseTypeId == 9) {
                                        String g = allItems.get(v);
                                        if (!g.equalsIgnoreCase("ungrouped") && g.equalsIgnoreCase(group)) {
                                            errors.add("Item '" + v + "' and item '" + itemName + "' should not have a same GROUP_LABEL at row " + k + ", items worksheet. ");
                                            htmlErrors.put(j + "," + k + ",16", "INVALID FIELD");
                                        }
                                    }
                                }
                            }
                        }
                    } else if ("instant-calculation".equalsIgnoreCase(responseType)) {
                        OnChangeSheetValidationCell onchangecell = new OnChangeSheetValidationCell(OnChangeSheetValidationType.ALL, new SheetCell.Builder().rowName(itemName).colTitle("RESPONSE_VALUES_column").colValue(resValues).forWhich("instant_calculation").sheetNum(j).rowNum(k).colNum(16).build());
                        instantValidator.addValidationCells(onchangecell);
                    } else if (numberOfOptions > 0) {
                        // YW >>
                        String value1 = resValues.replaceAll("\\\\,", "##");
                        String[] resValArray = value1.split(",");
                        if (resValArray.length != numberOfOptions) {
                            /*
                                 * errors.add("There are an incomplete number of
                                 * option-value pairs in " + "RESPONSE_OPTIONS
                                 * and RESPONSE_VALUES at row " + k + ",
                                 * questions worksheet; perhaps you are missing
                                 * a comma? If there is a comma in any option
                                 * text/value itself, please use \\, instead.");
                                 * htmlErrors.put(j + "," + k + ",15", "NUMBER
                                 * OF OPTIONS DOES NOT MATCH"); htmlErrors.put(j +
                                 * "," + k + ",16", "NUMBER OF VALUES DOES NOT
                                 * MATCH");
                                 */
                            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 + ",15", resPageMsg.getString("number_option_not_match"));
                            htmlErrors.put(j + "," + k + ",16", resPageMsg.getString("number_value_not_match"));
                        }
                    }
                    /**
                         * The application will show error on page if two
                         * identical RESPONSE_LABEL has different REPONSE_VALUES
                         */
                    String[] mapValArray = (String[]) labelWithValues.get(responseLabel);
                    String value1 = resValues.replaceAll("\\\\,", "##");
                    String[] resValArray = value1.split(",");
                    if (labelWithValues.containsKey(responseLabel)) {
                        if (!StringUtil.isBlank(resValues)) {
                            // elements in them
                            if (null != resValArray && null != mapValArray && resValArray.length != mapValArray.length) {
                                errors.add(resPageMsg.getString("resp_label_with_different_resp_values") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ".");
                                htmlErrors.put(j + "," + k + ",16", resPageMsg.getString("resp_label_with_different_resp_values_html_error"));
                            } else {
                                for (int i = 0; i < resValArray.length; i++) {
                                    if (!resValArray[i].equals(mapValArray[i])) {
                                        errors.add(resPageMsg.getString("resp_label_with_different_resp_values") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                                        htmlErrors.put(j + "," + k + ",16", resPageMsg.getString("resp_label_with_different_resp_values_html_error"));
                                        break;
                                    }
                                }
                            }
                        }
                        controlValues.put(secName + "---" + itemName, mapValArray);
                    } else {
                        labelWithValues.put(responseLabel, resValArray);
                        controlValues.put(secName + "---" + itemName, resValArray);
                    }
                    /*
                         * Adding two columns here for the repeating rows,
                         * REsPONSE_LAYOUT and DEFAULT_VALUE TBH, 06/05/2007 YW
                         * 08-02-2007: move default_value down after data_type
                         */
                    // RESPONSE_LAYOUT
                    cell = sheet.getRow(k).getCell((short) 17);
                    // should be horizontal or vertical, tbh
                    // BWP: the application will assume a vertical layout if
                    // this value is not horizontal
                    // BWP 08-02-2007 <<
                    String responseLayout = getValue(cell);
                    responseLayout = responseLayout.replaceAll("<[^>]*>", "");
                    // BWP >>
                    cell = sheet.getRow(k).getCell((short) 19);
                    String dataType = getValue(cell);
                    dataType = dataType.replaceAll("<[^>]*>", "");
                    item_from_row.setDataType(dataType);
                    String dataTypeIdString = "1";
                    if (StringUtil.isBlank(dataType)) {
                        // errors.add("The DATA_TYPE column was blank at row
                        // " + k + ", items worksheet.");
                        // htmlErrors.put(j + "," + k + ",19", "REQUIRED
                        // FIELD");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DATA_TYPE_column") + " " + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                        htmlErrors.put(j + "," + k + ",19", resPageMsg.getString("required_field"));
                    } else {
                        if (!ItemDataType.findByName(dataType.toLowerCase())) {
                            // errors.add("The DATA_TYPE column was invalid
                            // at row " + k + ", Items worksheet.");
                            // htmlErrors.put(j + "," + k + ",19", "INVALID
                            // FIELD");
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("DATA_TYPE_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                            htmlErrors.put(j + "," + k + ",19", 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_with_dot"));
                                htmlErrors.put(j + "," + k + ",19", resPageMsg.getString("should_be_file"));
                            } else if ("instant-calculation".equalsIgnoreCase(responseType)) {
                                OnChangeSheetValidationCell onchangecell = new OnChangeSheetValidationCell(OnChangeSheetValidationType.NONE, SheetValidationType.SHOULD_BE_ST, new SheetCell.Builder().rowName(itemName).colTitle("DATA_TYPE_column").colValue(dataType).forWhich("instant_calculation").sheetNum(j).rowNum(k).colNum(19).build());
                                instantValidator.addValidationCells(onchangecell);
                            }
                            // 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) {
                        if (labelWithType.containsKey(responseLabel)) {
                            // datatype
                            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_with_dot"));
                                htmlErrors.put(j + "," + k + ",19", 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(st);
                                                // but 2.3 is not integer
                                                if (!I.toString().equals(st)) {
                                                    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_with_dot"));
                                        htmlErrors.put(j + "," + k + ",16", 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(st);
                                            } 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 + ",16", resPageMsg.getString("should_be_real"));
                                    }
                                }
                            }
                        }
                    }
                    // DEFAULT_VALUE
                    // can be anything, tbh
                    //
                    // YW 08-02-2007 << in database, default_value has been
                    // set type as varchar(255);
                    // outside database, it's going to be tied with item's
                    // DATA_TYPE
                    // here, default_value has been handled for dataType =
                    // date
                    //default value
                    cell = sheet.getRow(k).getCell((short) 18);
                    item_from_row.setDefaultValue(getValue(cell));
                    item_from_row.verifyDefaultValue(row_items, errors, htmlErrors, j, resPageMsg);
                    default_value = item_from_row.getDefaultValue();
                    cellIndex = 19;
                    String widthDecimal = "";
                    logger.debug("hasWidthDecimalColumn=" + hasWDColumn);
                    if (hasWDColumn) {
                        ++cellIndex;
                        cell = sheet.getRow(k).getCell((short) cellIndex);
                        widthDecimal = getValue(cell);
                        if (StringUtil.isBlank(widthDecimal)) {
                            widthDecimal = "";
                        } else {
                            if ("single-select".equalsIgnoreCase(responseType) || "multi-select".equalsIgnoreCase(responseType) || "radio".equalsIgnoreCase(responseType) || "checkbox".equalsIgnoreCase(responseType)) {
                                errors.add(resPageMsg.getString("error_message_for_width_decimal_at") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ":" + " " + resPageMsg.getString("width_decimal_unavailable_for_single_multi_checkbox_radio"));
                                htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_FIELD"));
                            } else {
                                StringBuffer message = new StringBuffer();
                                boolean isCalc = responseTypeId == 8 || responseTypeId == 9 ? true : false;
                                message = Validator.validateWidthDecimalSetting(widthDecimal, dataType, isCalc, this.locale);
                                if (message.length() > 0) {
                                    errors.add(resPageMsg.getString("error_message_for_width_decimal_at") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ":" + " " + message);
                                    htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_FIELD"));
                                }
                            }
                        }
                    }
                    ++cellIndex;
                    cell = sheet.getRow(k).getCell((short) cellIndex);
                    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();
                            // logger.debug("reg:" + finalRegexp);
                            if (finalRegexp.contains("\\\\")) {
                                // \\ in the regular expression it should
                                // not be allowed
                                // errors.add("The VALIDATION column has an
                                // invalid regular expression at row " + k
                                // + ", Items worksheet. Regular expression
                                // contained '\\\\', it should only contain
                                // one '\\'. ");
                                // htmlErrors.put(j + "," + k + ",21",
                                // "INVALID FIELD");
                                errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + " " + resPageMsg.getString("has_an_invalid_regular_expression_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("regular_expression_contained") + " '\\\\', " + resPageMsg.getString("it_should_only_contain_one") + "'\\'. ");
                                htmlErrors.put(j + "," + k + "," + cellIndex, 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("The VALIDATION column
                                        // has an invalid regular expression
                                        // at row " + k
                                        // + ", Items worksheet. Example:
                                        // regexp: /[0-9]*/ ");
                                        // htmlErrors.put(j + "," + k +
                                        // ",21", "INVALID FIELD");
                                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + resPageMsg.getString("has_an_invalid_regular_expression_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("Example") + " regexp: /[0-9]*/ ");
                                        htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_FIELD"));
                                    }
                                } else {
                                    // errors.add("The VALIDATION column has
                                    // an invalid regular expression at row
                                    // " + k
                                    // + ", Items worksheet. Example:
                                    // regexp: /[0-9]*/ ");
                                    // htmlErrors.put(j + "," + k + ",21",
                                    // "INVALID FIELD");
                                    errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + resPageMsg.getString("has_an_invalid_regular_expression_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + " " + resPageMsg.getString("Example") + " regexp: /[0-9]*/ ");
                                    htmlErrors.put(j + "," + k + "," + cellIndex, 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() + ", at row " +
                                // k
                                // + ", Items worksheet." );
                                // htmlErrors.put(j + "," + k + ",21",
                                // "INVALID FIELD");
                                errors.add(e.getMessage() + ", " + resPageMsg.getString("at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                                htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_FIELD"));
                            }
                        } else {
                            // errors.add("The VALIDATION column was invalid
                            // at row " + k
                            // + ", Items worksheet. ");
                            // htmlErrors.put(j + "," + k + ",21", "INVALID
                            // FIELD");
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot"));
                            htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_FIELD"));
                        }
                    }
                    ++cellIndex;
                    cell = sheet.getRow(k).getCell((short) cellIndex);
                    String regexpError = getValue(cell);
                    regexpError = regexpError.replaceAll("<[^>]*>", "");
                    if (!StringUtil.isBlank(regexp) && StringUtil.isBlank(regexpError)) {
                        // errors.add("The VALIDATION_ERROR_MESSAGE column
                        // was blank at row " + k
                        // + ", Items worksheet. It cannot be blank if
                        // VALIDATION is not blank.");
                        // htmlErrors.put(j + "," + k + ",22", "REQUIRED
                        // FIELD");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("VALIDATION_ERROR_MESSAGE_column") + resPageMsg.getString("was_blank_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + " " + resPageMsg.getString("cannot_be_blank_if_VALIDATION_not_blank"));
                        htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("required_field"));
                    }
                    if (regexpError != null && regexpError.length() > 255) {
                        errors.add(resPageMsg.getString("regexp_errror_length_error"));
                    }
                    ++cellIndex;
                    boolean phiBoolean = false;
                    cell = sheet.getRow(k).getCell((short) cellIndex);
                    String phi = getValue(cell);
                    // logger.debug("++ phi: "+getValue(cell));
                    if (StringUtil.isBlank(phi)) {
                        phi = "0";
                    } else // 06/07
                    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("The PHI column was invalid at row " +
                        // k
                        // + ", Items worksheet. PHI can only be either 0 or
                        // 1.");
                        // htmlErrors.put(j + "," + k + ",23", "INVALID
                        // VALUE");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("PHI_column") + resPageMsg.getString("was_invalid_at_row") + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("PHI_column") + " " + resPageMsg.getString("can_only_be_either_0_or_1"));
                        htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_VALUE"));
                    } else {
                        phiBoolean = "1".equals(phi) ? true : false;
                    }
                    ++cellIndex;
                    boolean isRequired = false;
                    cell = sheet.getRow(k).getCell((short) cellIndex);
                    String required = getValue(cell);
                    // added to stop NPEs, tbh 06/04/2007
                    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("The REQUIRED column was invalid at
                        // row " + k
                        // + ", Items worksheet. REQUIRED can only be either
                        // 0 or 1. ");
                        // htmlErrors.put(j + "," + k + ",24", "INVALID
                        // VALUE");
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("REQUIRED_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("REQUIRED_column") + resPageMsg.getString("can_only_be_either_0_or_1"));
                        htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_VALUE"));
                    } else {
                        isRequired = "1".equals(required) ? true : false;
                    }
                    // >> tbh 02/04/2010 adding this column for Dynamics
                    ++cellIndex;
                    boolean isShowItem = true;
                    // default is true
                    cell = sheet.getRow(k).getCell((short) cellIndex);
                    String showItem = getValue(cell);
                    if (!StringUtil.isBlank(showItem)) {
                        isShowItem = "0".equals(showItem) ? false : true;
                        isShowItem = "Hide".equalsIgnoreCase(showItem) ? false : true;
                    // supporting both, tbh 03/2010
                    }
                    ++cellIndex;
                    cell = sheet.getRow(k).getCell((short) cellIndex);
                    String display = getValue(cell);
                    String controlItemName = "", optionValue = "", message = "";
                    if (!StringUtil.isBlank(display)) {
                        if (isShowItem != false) {
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("ITEM_DISPLAY_STATUS_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("should_be_hide_for_scd"));
                            htmlErrors.put(j + "," + k + "," + (cellIndex - 1), resPageMsg.getString("INVALID_VALUE"));
                        }
                        String pvKey = secName + "---";
                        String d = display.replaceAll("\\\\,", "##");
                        String[] par = d.split(",");
                        //validate availability of item_label
                        if (par.length == 3) {
                            String p0 = par[0].trim();
                            String p1 = par[1].trim();
                            String p2 = par[2].trim();
                            if (p0.length() > 0 && p1.length() > 0 && p2.length() > 0) {
                                if (SpreadSheetItemUtil.isItemWithSameParameterExistsIncludingMyself(p0, row_items)) {
                                    controlItemName = p0;
                                    optionValue = p1;
                                    message = p2;
                                    pvKey += p0;
                                    if (controlValues.containsKey(pvKey)) {
                                        String[] pvs = controlValues.get(pvKey);
                                        boolean existing = false;
                                        for (String s : pvs) {
                                            if (s.trim().equals(p1)) {
                                                existing = true;
                                                break;
                                            }
                                        }
                                        if (!existing) {
                                            optionValue = "";
                                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SIMPLE_CONDITIONAL_DISPLAY_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("control_response_value_invalid") + " " + p1.replace("##", "\\\\,"));
                                            htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_VALUE"));
                                        }
                                    }
                                } else {
                                    errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SIMPLE_CONDITIONAL_DISPLAY_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("control_item_name_invalid") + " " + p0);
                                    htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_VALUE"));
                                }
                            } else {
                                errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SIMPLE_CONDITIONAL_DISPLAY_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("correct_pattern"));
                                htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_VALUE"));
                            }
                        } else {
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SIMPLE_CONDITIONAL_DISPLAY_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("correct_pattern"));
                            htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_VALUE"));
                        }
                    }
                    // 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 = "";
                    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 + "')";
                    } 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 + "')";
                    }
                    backupItemQueries.put(itemName, vlSql);
                    // 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 resOptions1 = resOptions.replaceAll("\\\\,", "\\,");
                    String resValues1 = resValues.replaceAll("\\\\,", "\\,");
                    rsb.setOptions(stripQuotes(resOptions1), stripQuotes(resValues1));
                    ItemFormMetadataBean ifmb = new ItemFormMetadataBean();
                    ifmb.setResponseSet(rsb);
                    ifmb.setShowItem(isShowItem);
                    ib.setItemMeta(ifmb);
                    items.put(itemName, ib);
                    int ownerId = ub.getId();
                    if (!itemCheck.containsKey(itemName)) {
                        // item not in
                        // the DB
                        openQueries.put(itemName, vlSql);
                    } 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;
                                } else {
                                    upSql = "UPDATE ITEM SET DESCRIPTION='" + stripQuotes(descLabel) + "'," + "UNITS='" + stripQuotes(unit) + "'," + "PHI_STATUS=" + phiBoolean + "," + "ITEM_DATA_TYPE_ID=" + dataTypeIdString + // the missing from clause bug
                                    " 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;
                                }
                                // end of if dbName
                                openQueries.put(itemName, upSql);
                            } else {
                                String upSql = "";
                                if (//New Feature allow date to pdate even if the data is entered
                                oldItem.getDataType() == oldItem.getDataType().DATE && ib.getDataType() == ib.getDataType().PDATE) {
                                    if (dbName.equals("oracle")) {
                                        upSql = "UPDATE ITEM SET DESCRIPTION='" + stripQuotes(descLabel) + "',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;
                                    } else {
                                        upSql = "UPDATE ITEM SET DESCRIPTION='" + stripQuotes(descLabel) + "',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;
                                    }
                                // end of if dbName
                                } else {
                                    if (dbName.equals("oracle")) {
                                        upSql = "UPDATE ITEM SET DESCRIPTION='" + stripQuotes(descLabel) + "'," + "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='" + stripQuotes(itemName) + "' AND item.owner_id = " + ownerId;
                                    } else {
                                        upSql = "UPDATE ITEM SET DESCRIPTION='" + stripQuotes(descLabel) + "'," + "PHI_STATUS=" + phiBoolean + " 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;
                                    }
                                // end of if dbName
                                }
                                openQueries.put(itemName, upSql);
                            }
                        } else {
                            ownerId = oldItem.getOwner().getId();
                        }
                    }
                    String sql = "";
                    if (dbName.equals("oracle")) {
                        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 + ")";
                    } 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 + ")";
                    }
                    // one response type
                    if (!resPairs.contains(responseLabel.toString().toLowerCase() + "_" + responseType.toString().toLowerCase())) {
                        // YW >>
                        if (!resNames.contains(responseLabel)) {
                            queries.add(sql);
                            resNames.add(responseLabel);
                        } else // 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?
                        // YW <<
                        {
                            errors.add("Error found at row \"" + (k + 1) + "\" in items worksheet. ResponseLabel \"" + responseLabel + "\" for ResponseType \"" + responseType + "\" has been used for another ResponseType.  ");
                            htmlErrors.put(j + "," + k + ",14", "INVALID FIELD");
                        }
                        resPairs.add(responseLabel.toString().toLowerCase() + "_" + responseType.toString().toLowerCase());
                    // YW >>
                    }
                    String parentItemString = "0";
                    if (!StringUtil.isBlank(parentItem)) {
                        if (dbName.equals("oracle")) {
                            parentItemString = "(SELECT MAX(ITEM_ID) FROM ITEM WHERE NAME='" + stripQuotes(parentItem) + "' AND owner_id = " + ownerId + " )";
                        } else {
                            parentItemString = "(SELECT ITEM_ID FROM ITEM WHERE NAME='" + stripQuotes(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_OID 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 = "";
                    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,DEFAULT_VALUE,RESPONSE_LAYOUT,WIDTH_DECIMAL, show_item)" + " 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) + ", '" + stripQuotes(default_value) + "','" + stripQuotes(responseLayout) + "','" + widthDecimal + "', " + (isShowItem ? 1 : 0) + ")";
                        logger.debug(sql2);
                    } 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,DEFAULT_VALUE,RESPONSE_LAYOUT,WIDTH_DECIMAL, show_item)" + " 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 + ", '" + stripQuotes(default_value) + "','" + stripQuotes(responseLayout) + "','" + widthDecimal + "'," + isShowItem + ")";
                    }
                    queries.add(sql2);
                    // 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 + ")";
                    }
                    queries.add(sql3);
                    String sql2_1 = "";
                    if (display.length() > 0) {
                        if (controlItemName.length() > 0 && optionValue.length() > 0 && message.length() > 0) {
                            //At this point, all errors for scd should be caught; and insert into item_form_metadata should be done
                            if (dbName.equals("oracle")) {
                                sql2_1 = "insert into scd_item_metadata (scd_item_form_metadata_id,control_item_form_metadata_id,control_item_name," + "option_value,message) values(" + "(select max(ifm.item_form_metadata_id) from item_form_metadata ifm where ifm.item_id=" + selectCorrectItemQueryOracle + "and ifm.show_item=0 )," + "(select cifm.item_form_metadata_id from item, item_form_metadata cifm" + " where cifm.crf_version_id = " + versionIdString + " and item.item_id = (select it.item_id from item it, versioning_map vm where it.name = '" + controlItemName + "'" + " and vm.crf_version_id = " + versionIdString + " and vm.item_id = it.item_id)" + " and cifm.item_id = item.item_id), " + "'" + controlItemName + "', '" + stripQuotes(optionValue) + "', '" + stripQuotes(message) + "'" + ")";
                            } else {
                                sql2_1 = "insert into scd_item_metadata (scd_item_form_metadata_id,control_item_form_metadata_id,control_item_name," + "option_value,message) values(" + "(select max(ifm.item_form_metadata_id) from item_form_metadata ifm where ifm.item_id=" + selectCorrectItemQueryPostgres + "and ifm.show_item=false )," + "(select cifm.item_form_metadata_id from item, item_form_metadata cifm" + " where cifm.crf_version_id = " + versionIdString + " and item.item_id = (select it.item_id from item it, versioning_map vm where it.name = '" + controlItemName + "'" + " and vm.crf_version_id = " + versionIdString + " and vm.item_id = it.item_id)" + " and cifm.item_id = item.item_id), " + "'" + controlItemName + "', '" + stripQuotes(optionValue) + "', '" + stripQuotes(message) + "'" + ")";
                            }
                            queries.add(sql2_1);
                        } else {
                            logger.debug("No insert into scd_item_metadata for item name = " + itemName + "with Simple_Conditional_Display = \"" + display + "\".");
                        }
                    }
                    // }
                    if (!StringUtil.isBlank(groupLabel)) {
                        ItemGroupBean itemGroup;
                        ItemGroupMetadataBean igMeta;
                        igMeta = new ItemGroupMetadataBean();
                        itemGroup = new ItemGroupBean();
                        try {
                            logger.debug("found " + groupLabel);
                            itemGroup = (ItemGroupBean) itemGroups.get(groupLabel);
                            logger.debug("*** Found " + groupLabel + " and matched with " + itemGroup.getName());
                            // if(itemGroup != null){
                            igMeta = itemGroup.getMeta();
                            if (igMeta == null) {
                                igMeta = new ItemGroupMetadataBean();
                            }
                            // above throws Nullpointer, need to change so
                            // that it does not, tbh 07-08-07
                            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, show_group, repeating_group) VALUES (" + "(SELECT MAX(ITEM_GROUP_ID) FROM ITEM_GROUP WHERE NAME='" + stripQuotes(itemGroup.getName()) + "' AND crf_id = " + crfId + " ),'" + stripQuotes(igMeta.getHeader()) + "', '" + stripQuotes(igMeta.getSubheader()) + "', '" + // above removed?
                                igMeta.getLayout() + "', " + // above removed?
                                igMeta.getRepeatNum() + ", " + igMeta.getRepeatMax() + ", '" + igMeta.getRepeatArray() + "', " + // above removed?
                                igMeta.getRowStartNumber() + "," + versionIdString + "," + "(SELECT MAX(ITEM.ITEM_ID) FROM ITEM,ITEM_FORM_METADATA,CRF_VERSION WHERE ITEM.NAME='" + stripQuotes(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 + ", " + (igMeta.isShowGroup() ? 1 : 0) + ", " + (igMeta.isRepeatingGroup() ? 1 : 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, show_group, repeating_group) VALUES (" + "(SELECT ITEM_GROUP_ID FROM ITEM_GROUP WHERE NAME='" + itemGroup.getName() + "' AND crf_id = " + crfId + " LIMIT 1),E'" + igMeta.getHeader() + "', E'" + igMeta.getSubheader() + "', '" + // above removed?
                                igMeta.getLayout() + "', " + // above removed?
                                igMeta.getRepeatNum() + ", " + igMeta.getRepeatMax() + ", '" + igMeta.getRepeatArray() + "', " + // above removed?
                                igMeta.getRowStartNumber() + "," + 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 + ", " + igMeta.isShowGroup() + ", " + igMeta.isRepeatingGroup() + ")";
                            }
                            queries.add(sqlGroupLabel);
                        } catch (NullPointerException e) {
                            // Auto-generated catch block, added tbh 102007
                            logger.error("Error  message", e);
                            errors.add(resPageMsg.getString("Error_found_at_row") + " \"" + (k + 1) + "\"" + resPageMsg.getString("items_worksheet_with_dot") + resPageMsg.getString("GROUP_LABEL") + "\"" + groupLabel + "\" " + resPageMsg.getString("does_not_exist_in_group_spreadsheet"));
                            htmlErrors.put(j + "," + k + ",6", resPageMsg.getString("GROUP_DOES_NOT_EXIST"));
                        }
                    } else {
                        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, repeating_group) VALUES (" + "(SELECT MAX(ITEM_GROUP_ID) FROM ITEM_GROUP WHERE NAME='Ungrouped' AND crf_id = " + crfId + " ),'" + "" + "', '" + "" + "', '" + "" + "', " + 1 + ", " + 1 + ", '', 1," + versionIdString + "," + selectCorrectItemQueryOracle + "," + 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, repeating_group) VALUES (" + "(SELECT ITEM_GROUP_ID FROM ITEM_GROUP WHERE NAME='Ungrouped' AND crf_id = " + crfId + "  LIMIT 1),'" + "" + "', '" + "" + "', '" + "" + "', " + 1 + ", " + 1 + ", '', 1," + versionIdString + "," + selectCorrectItemQueryPostgres + "," + k + ", false)";
                        }
                        // >>>>>>> .r10888
                        queries.add(sqlGroupLabel);
                    }
                }
                // **************************************
                // above this is where we will add the first sql query for
                // group names
                // will have to be put in a seperate list
                // and added at the end so that we insure
                // that all new item_names and group_names have been added,
                // tbh 5/14
                // **************************************
                // below is place where we will add the sheet name for
                // Groups
                // tbh, 5/14/2007
                // DONE -- add sql insert queries below
                // TODO review html error creation in table at end of file
                // TODO find out where to add the form group beans
                // TODO find out where to add the map beans
                // we need to make sure groups sql are executed first,
                // because item_group_id is
                // used when we insert item group meta data with item
                //validate that items of one group are not spread over several sections
                SpreadSheetItemUtil.verifySectionGroupPlacementForItems(row_items, errors, htmlErrors, j, resPageMsg, itemGroups);
                SpreadSheetItemUtil.verifyUniqueItemPlacementInGroups(row_items, errors, htmlErrors, j, resPageMsg, crfName, ds);
                instantValidator.validate();
                errors = (ArrayList<String>) instantValidator.getSheetErrors().addErrorsToSheet(errors);
                htmlErrors = (HashMap<String, String>) instantValidator.getSheetErrors().putHtmlErrorsToSheet(htmlErrors);
            } else if (sheetName.equalsIgnoreCase("Groups")) {
                logger.debug("read groups, ***comment added 5.14.07");
                ArrayList groupNames = new ArrayList();
                // create a group - item relationship with this table? hmm
                // they are in order: group_label, group_layout,
                // group_header,
                // group_sub_header, group_repeat_number, group_repeat_max,
                // group_repeat_array
                // so: seven rows
                // 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 + "')";
                } 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 + "')";
                }
                if (!GroupCheck.containsKey("Ungrouped")) {
                    queries.add(defaultSql);
                }
                for (int gk = 1; gk < numRows; gk++) {
                    if (sheet.getRow(gk) == null) {
                        blankRowCount++;
                        if (blankRowCount == 5) {
                            break;
                        }
                        continue;
                    }
                    HSSFCell cell = sheet.getRow(gk).getCell((short) 0);
                    String groupLabel = getValue(cell);
                    groupLabel = groupLabel.replaceAll("<[^>]*>", "");
                    if (StringUtil.isBlank(groupLabel)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("GROUP_LABEL_column") + resPageMsg.getString("was_blank_at_row") + " " + gk + ", " + resPageMsg.getString("Groups_worksheet") + ".");
                        htmlErrors.put(j + "," + gk + ",0", resPageMsg.getString("required_field"));
                    }
                    if (groupLabel != null && groupLabel.length() > 255) {
                        errors.add(resPageMsg.getString("group_label_length_error"));
                    }
                    // must these be unique? probably so, tbh
                    if (groupNames.contains(groupLabel)) {
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("GROUP_LABEL_column") + resPageMsg.getString("was_a_duplicate_of") + " " + groupLabel + resPageMsg.getString("at_row") + gk + ", " + resPageMsg.getString("Groups_worksheet") + ".");
                        htmlErrors.put(j + "," + gk + ",0", resPageMsg.getString("DUPLICATE_FIELD"));
                    } else {
                        groupNames.add(groupLabel);
                    }
                    // removed reference to 'groupLayout' here, tbh 102007
                    boolean isRepeatingGroup = true;
                    boolean newVersionCrf = false;
                    int cellNo = 0;
                    if (!(versionNo.equalsIgnoreCase("Version: 2.2") || versionNo.equalsIgnoreCase("Version: 2.5") || versionNo.equalsIgnoreCase("Version: 3.0"))) {
                        cellNo = 1;
                        cell = sheet.getRow(gk).getCell((short) cellNo);
                        try {
                            isRepeatingGroup = getValue(cell).equalsIgnoreCase("grid");
                            newVersionCrf = true;
                        } catch (Exception eee) {
                            errors.add(resPageMsg.getString("repeating_group_error"));
                        }
                    }
                    cell = sheet.getRow(gk).getCell((short) ++cellNo);
                    String groupHeader = getValue(cell);
                    // replace any apostrophes in groupHeader: issue 3277
                    groupHeader = org.akaza.openclinica.core.form.StringUtil.escapeSingleQuote(groupHeader);
                    if (groupHeader != null && groupHeader.length() > 255) {
                        errors.add(resPageMsg.getString("group_header_length_error"));
                    }
                    if (isRepeatingGroup) {
                        sheetContainer.getRepeatingGroupLabels().add(groupLabel);
                    }
                    cell = sheet.getRow(gk).getCell((short) ++cellNo);
                    String groupRepeatNumber = getValue(cell);
                    // adding clause to convert to int, tbh, 06/07
                    if (newVersionCrf && !isRepeatingGroup && !StringUtil.isBlank(groupRepeatNumber)) {
                        errors.add(resPageMsg.getString("repeat_number_none_repeating"));
                    } else if (!isRepeatingGroup && StringUtil.isBlank(groupRepeatNumber)) {
                        groupRepeatNumber = "1";
                    } else {
                        if (StringUtil.isBlank(groupRepeatNumber)) {
                            groupRepeatNumber = "1";
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            double dr = cell.getNumericCellValue();
                            if ((dr - (int) dr) * 1000 == 0) {
                                groupRepeatNumber = (int) dr + "";
                            }
                        } else {
                            logger.debug("found a non-numeric code in a numeric field: groupRepeatNumber");
                        }
                    }
                    cell = sheet.getRow(gk).getCell((short) ++cellNo);
                    String groupRepeatMax = getValue(cell);
                    // adding clause to convert to int, tbh 06/07
                    if (newVersionCrf && !isRepeatingGroup && !StringUtil.isBlank(groupRepeatMax)) {
                        errors.add(resPageMsg.getString("repeat_max_none_repeating"));
                    } else if (!isRepeatingGroup && StringUtil.isBlank(groupRepeatMax)) {
                        groupRepeatMax = "1";
                    } else {
                        if (StringUtil.isBlank(groupRepeatMax)) {
                            // problem, tbh
                            groupRepeatMax = "40";
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            double dr = cell.getNumericCellValue();
                            if ((dr - (int) dr) * 1000 == 0) {
                                groupRepeatMax = (int) dr + "";
                                // check for zero value
                                try {
                                    int repeatMaxInt = Integer.parseInt(groupRepeatMax);
                                    if (repeatMaxInt < 1) {
                                        groupRepeatMax = "40";
                                    }
                                } catch (NumberFormatException nfe) {
                                    groupRepeatMax = "40";
                                }
                            }
                        } else {
                            logger.debug("found a non-numeric code in a numeric field: groupRepeatMax");
                        }
                    }
                    // >> tbh 02/2010 adding show_hide for Dynamics
                    cell = sheet.getRow(gk).getCell((short) ++cellNo);
                    String showGroup = getValue(cell);
                    boolean isShowGroup = true;
                    if (!StringUtil.isBlank(showGroup)) {
                        try {
                            isShowGroup = "0".equals(showGroup) ? false : true;
                            isShowGroup = "Hide".equalsIgnoreCase(showGroup) ? false : true;
                        } catch (Exception eee) {
                            logger.debug("caught an exception with the boolean value for groups");
                        }
                    }
                    //                        if (!"1".equals(showGroup) && !"0".equals(showGroup)) {
                    //                            // throw an error here
                    //                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("SHOW_GROUP_column") + " "
                    //                                    + resPageMsg.getString("was_invalid_at_row") + " " + gk + ", " + resPageMsg.getString("Groups_worksheet") + ". "
                    //                                    + resPageMsg.getString("SHOW_GROUP_column") + resPageMsg.getString("can_only_be_either_0_or_1"));
                    //                            htmlErrors.put(j + "," + gk + "," + 4, resPageMsg.getString("INVALID_VALUE"));
                    //                        }
                    // cell = sheet.getRow(gk).getCell((short) 6);
                    // String groupRepeatArray = getValue(cell);
                    // below added 06/14/2007, tbh
                    /*
                         * BWP>>commented out after removal of borders column
                         * cell = sheet.getRow(gk).getCell((short) 7); String
                         * groupBorders = getValue(cell); Integer borders = 0;
                         * try { borders = Integer.valueOf(groupBorders); if
                         * (borders.intValue() <0) { errors.add("The BORDERS
                         * column must be a positive integer. " + groupBorders + "
                         * at row " + gk + ", Groups worksheet.");
                         * htmlErrors.put(j + "," + gk + ",7", "INVALID FIELD"); } }
                         * catch (NumberFormatException ne) { errors.add("The
                         * BORDERS column must be a positive integer. " +
                         * groupBorders + " at row " + gk + ", Groups
                         * worksheet."); htmlErrors.put(j + "," + gk + ",7",
                         * "INVALID FIELD"); } >>
                         */
                    // above added 06/14/2007, tbh
                    ItemGroupBean fgb = new ItemGroupBean();
                    fgb.setName(groupLabel);
                    fgb.setCrfId(crfId);
                    fgb.setStatus(Status.AVAILABLE);
                    ItemGroupMetadataBean igMeta = new ItemGroupMetadataBean();
                    igMeta.setHeader(groupHeader);
                    igMeta.setRepeatingGroup(isRepeatingGroup);
                    //htaycher: no borders anymnore //13817
                    igMeta.setBorders(0);
                    // igMeta.setLayout(groupLayout);
                    // igMeta.setRepeatArray(groupRepeatArray);
                    igMeta.setShowGroup(isShowGroup);
                    try {
                        igMeta.setRepeatMax(new Integer(Integer.parseInt(groupRepeatMax)));
                        if (igMeta.getRepeatMax() < 1) {
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("GROUP_REPEAT_MAX_column") + " " + resPageMsg.getString("must_be_a_positive_integer") + ". " + groupRepeatMax + " " + resPageMsg.getString("at_row") + " " + gk + ", " + resPageMsg.getString("Groups_worksheet") + ". ");
                            htmlErrors.put(j + "," + gk + ",3", resPageMsg.getString("INVALID_FIELD"));
                        }
                    } catch (NumberFormatException n2) {
                        logger.error("Error  message", n2);
                        if ("".equals(groupRepeatMax)) {
                            igMeta.setRepeatMax(40);
                        } else {
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("GROUP_REPEAT_MAX_column") + " " + resPageMsg.getString("must_be_a_positive_integer") + ". " + groupRepeatMax + " " + resPageMsg.getString("at_row") + " " + gk + ", " + resPageMsg.getString("Groups_worksheet") + ". ");
                            htmlErrors.put(j + "," + gk + ",3", resPageMsg.getString("INVALID_FIELD"));
                        }
                    }
                    try {
                        igMeta.setRepeatNum(new Integer(Integer.parseInt(groupRepeatNumber)));
                        if (igMeta.getRepeatNum() < 1) {
                            //mantiss 13917
                            errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("GROUP_REPEAT_NUM_column") + " " + resPageMsg.getString("must_be_a_positive_integer_or_blank") + ". " + groupRepeatNumber + " " + resPageMsg.getString("at_row") + " " + gk + ", " + resPageMsg.getString("Groups_worksheet") + ". ");
                            htmlErrors.put(j + "," + gk + ",2", resPageMsg.getString("INVALID_FIELD"));
                        }
                    } catch (NumberFormatException n3) {
                        logger.error(n3.getMessage());
                        errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("GROUP_REPEAT_NUM_column") + " " + resPageMsg.getString("must_be_a_positive_integer_or_blank") + ". " + groupRepeatNumber + " " + resPageMsg.getString("at_row") + " " + gk + ", " + resPageMsg.getString("Groups_worksheet") + ". ");
                        htmlErrors.put(j + "," + gk + ",2", resPageMsg.getString("INVALID_FIELD"));
                    }
                    // igMeta.setSubheader(groupSubheader);
                    fgb.setMeta(igMeta);
                    // now, we place the form group bean where we can
                    // generate the sql
                    // and find it again, tbh 5/14/2007
                    // Create oid for Item Group
                    String groupOid = itemGroupDao.getValidOid(fgb, crfName, fgb.getName(), itemGroupOids);
                    itemGroupOids.add(groupOid);
                    // changed to add metadata into item_group_metadata
                    // table-jxu
                    String gsql = "";
                    if (dbName.equals("oracle")) {
                        gsql = "INSERT INTO ITEM_GROUP ( " + "name, crf_id, status_id, date_created ,owner_id,oc_oid)" + "VALUES ('" + fgb.getName() + "', " + fgb.getCrfId() + "," + fgb.getStatus().getId() + "," + "sysdate," + ub.getId() + ",'" + groupOid + "')";
                    } else {
                        gsql = "INSERT INTO ITEM_GROUP ( " + "name, crf_id, status_id, date_created ,owner_id,oc_oid)" + "VALUES ('" + fgb.getName() + "', " + fgb.getCrfId() + "," + fgb.getStatus().getId() + "," + "now()," + ub.getId() + ",'" + groupOid + "')";
                    }
                    itemGroups.put(fgb.getName(), fgb);
                    if (!GroupCheck.containsKey(fgb.getName())) {
                        // item group not in the DB, then insert
                        // otherwise, will use the existing group because
                        // group name is unique
                        // and shared within CRF
                        queries.add(gsql);
                    }
                // if (!StringUtil.isBlank(groupLabel)) {
                // String itemName =
                // (String)itemsToGrouplabels.get(groupLabel);
                // logger.debug("found "+itemName+" when we passed group
                // label "+groupLabel);
                // ItemGroupBean itemGroup = new ItemGroupBean();
                // //logger.debug("found "+groupLabel);
                // itemGroup= (ItemGroupBean)itemGroups.get(groupLabel);
                // logger.debug("*** Found "+
                // groupLabel+
                // " and matched with "+
                // itemGroup.getName());
                // igMeta = itemGroup.getMeta();
                // //above throws Nullpointer, need to change so that it
                // does not, tbh 07-08-07
                // //moved down here from line 590, tbh
                //
                // String 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='"
                // + itemGroup.getName()
                // + "' AND crf_id = " + crfId + " ORDER BY OID DESC
                // LIMIT 1),'"+
                // igMeta.getHeader()+"', '" +
                // igMeta.getSubheader()+ "', '" +
                // igMeta.getLayout()+ "', " +
                // igMeta.getRepeatNum()+", " +
                // igMeta.getRepeatMax()+", '" +
                // igMeta.getRepeatArray()+"', " +
                // igMeta.getRowStartNumber()+ "," +
                // versionIdString + "," +
                // "(SELECT ITEM_ID FROM ITEM WHERE NAME='" + itemName +
                // "' AND owner_id = " + ub.getId() + " ORDER BY OID
                // DESC LIMIT 1)," +
                // igMeta.getOrdinal() + ",'" +
                // igMeta.getBorders()+
                // "')";
                // queries.add(sqlGroupLabel);
                //
                // }
                }
            } else if (sheetName.equalsIgnoreCase("Sections")) {
                logger.debug("read sections");
                // BORDER, tbh
                for (int k = 1; k < numRows; k++) {
                    if (blankRowCount == 5) {
                        // 06/2007
                        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("The SECTION_LABEL column was blank at
                        // row " + k + ", Sections worksheet.");
                        // htmlErrors.put(j + "," + k + ",0", "REQUIRED
                        // FIELD");
                        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 (secNames.contains(secLabel)) {
                        // errors.add("The SECTION_LABEL column was a
                        // duplicate of " + secLabel + " at row " + k
                        // + ", sections worksheet.");
                        // htmlErrors.put(j + "," + k + ",0", "DUPLICATE
                        // FIELD");
                        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.debug("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("The SECTION_TITLE column was blank at
                        // row " + k + ", Sections worksheet.");
                        // htmlErrors.put(j + "," + k + ",1", "REQUIRED
                        // FIELD");
                        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;
                        }
                    }
                    // below added 06/2007, tbh
                    cell = sheet.getRow(k).getCell((short) 6);
                    String strBorder = getValue(cell);
                    strBorder = strBorder.replaceAll("<[^>]*>", "");
                    Integer intBorder = new Integer(0);
                    try {
                        intBorder = new Integer(strBorder);
                    } catch (NumberFormatException npe) {
                    // let it pass here, tbh 06/18/2007
                    }
                    // change to sql 06/2007; change to section table in
                    // svn? tbh
                    String sql = "";
                    // BWP added borders column 4/24/2008
                    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, BORDERS) " + "VALUES (" + versionIdString + ",1,'" + secLabel + "','" + stripQuotes(title) + "', '" + stripQuotes(instructions) + "', '" + stripQuotes(subtitle) + "','" + pageNumber + "'," + k + "," + parentId + "," + ub.getId() + ",sysdate," + intBorder + ")";
                    } else {
                        sql = "INSERT INTO SECTION (CRF_VERSION_ID," + "STATUS_ID,LABEL, TITLE, INSTRUCTIONS, SUBTITLE, PAGE_NUMBER_LABEL," + "ORDINAL, PARENT_ID, OWNER_ID, DATE_CREATED,BORDERS) " + "VALUES (" + versionIdString + ",1,'" + secLabel + "','" + stripQuotes(title) + "', '" + stripQuotes(instructions) + "', '" + stripQuotes(subtitle) + "','" + pageNumber + "'," + k + "," + parentId + "," + ub.getId() + ",NOW()," + intBorder + ")";
                    }
                    queries.add(sql);
                }
            // end for loop
            } else if (sheetName.equalsIgnoreCase("CRF")) {
                logger.debug("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)) {
                    // htmlErrors.put(j + ",1,0", "REQUIRED FIELD");
                    throw new CRFReadingException("The CRF_NAME column was blank in the CRF worksheet.");
                }
                if (crfId > 0) {
                    CRFBean checkName = (CRFBean) cdao.findByPK(crfId);
                    if (!checkName.getName().equals(crfName)) {
                        throw new CRFReadingException(resPageMsg.getString("the") + " " + resPageMsg.getString("CRF_NAME_column") + " '" + crfName + "' " + resPageMsg.getString("did_not_match_crf_name") + " '" + checkName.getName() + "'.");
                    }
                }
                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"));
                    }
                }
                // try {
                // CRFBean checkName = (CRFBean) cdao.findByPK(crfId);
                // if (!checkName.getName().equals(crfName)) {
                // logger.debug("crf name is mismatch");
                // //errors.add("The CRF_NAME column did not match the
                // intended CRF version "
                // // + "you want to upload. Make sure this reads '" +
                // checkName.getName()
                // // + "' before you continue.");
                // //htmlErrors.put(j + ",1,0", "DID NOT MATCH CRF");
                // 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("The REVISION_NOTES column was blank in
                    // the CRF worksheet.");
                    // htmlErrors.put(j + ",1,3", "REQUIRED FIELD");
                    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();
                        CoreResources.setSchema(con);
                        /*
                             * 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 + ")";
                        } 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 + ")";
                        }
                        queries.add(createCRFSql);
                    } 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("Connection 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.debug("found a matching version name..." + version);
                    /*
                         * errors.add("The VERSION column is not unique. This
                         * can cause confusion in " + "selecting the correct
                         * CRF. Please make sure you change the " + "version
                         * name so that it can be uniquely identified by users
                         * in the system. " + "Otherwise, the previous same
                         * version will be deleted from database.");
                         * htmlErrors.put(j + ",1,2", "NOT UNIQUE");
                         */
                    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 = "";
                if (dbName.equals("oracle")) {
                    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 C WHERE C.NAME='" + crfName + "'),1,sysdate," + ub.getId() + ",'" + stripQuotes(revisionNotes) + "','" + 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 + "')";
                    }
                } 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 + "')";
                    } 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 + "')";
                    }
                }
                queries.add(sql);
                pVersion = version;
                pVerDesc = versionDesc;
            }
            versionIdString = "(SELECT CRF_VERSION_ID FROM CRF_VERSION WHERE NAME ='" + pVersion + "' AND CRF_ID=" + crfId + ")";
            // 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
    // queries.addAll(groupItemMapQueries);
    // added at the end so that items and groups already exist, tbh 5.15.07
    ncrf.setQueries(queries);
    ncrf.setItemQueries(openQueries);
    ncrf.setBackupItemQueries(backupItemQueries);
    ncrf.setItems(items);
    if (!errors.isEmpty()) {
        ncrf.setErrors(errors);
    }
    // logger.debug("html table:" + buf.toString());
    ncrf.setHtmlTable(buf.toString());
    return ncrf;
}
Also used : ItemBean(org.akaza.openclinica.bean.submit.ItemBean) HashMap(java.util.HashMap) LinkedHashMap(java.util.LinkedHashMap) ItemDAO(org.akaza.openclinica.dao.submit.ItemDAO) ArrayList(java.util.ArrayList) ItemGroupDAO(org.akaza.openclinica.dao.submit.ItemGroupDAO) ItemGroupMetadataBean(org.akaza.openclinica.bean.submit.ItemGroupMetadataBean) LinkedHashMap(java.util.LinkedHashMap) ResultSet(java.sql.ResultSet) CRFDAO(org.akaza.openclinica.dao.admin.CRFDAO) CRFVersionDAO(org.akaza.openclinica.dao.submit.CRFVersionDAO) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook) 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) ResponseSetBean(org.akaza.openclinica.bean.submit.ResponseSetBean) ItemGroupBean(org.akaza.openclinica.bean.submit.ItemGroupBean) Map(java.util.Map) HashMap(java.util.HashMap) LinkedHashMap(java.util.LinkedHashMap) OnChangeSheetValidator(org.akaza.openclinica.control.form.spreadsheet.OnChangeSheetValidator) SQLException(java.sql.SQLException) OnChangeSheetValidationCell(org.akaza.openclinica.control.form.spreadsheet.OnChangeSheetValidationCell) CRFReadingException(org.akaza.openclinica.exception.CRFReadingException) ItemFormMetadataDAO(org.akaza.openclinica.dao.submit.ItemFormMetadataDAO) PatternSyntaxException(java.util.regex.PatternSyntaxException) Pattern(java.util.regex.Pattern) Connection(java.sql.Connection) MeasurementUnitOidGenerator(org.akaza.openclinica.bean.oid.MeasurementUnitOidGenerator) PatternSyntaxException(java.util.regex.PatternSyntaxException) CRFReadingException(org.akaza.openclinica.exception.CRFReadingException) SQLException(java.sql.SQLException) IOException(java.io.IOException) SheetValidationContainer(org.akaza.openclinica.control.form.spreadsheet.SheetValidationContainer) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) CRFVersionBean(org.akaza.openclinica.bean.submit.CRFVersionBean) ItemFormMetadataBean(org.akaza.openclinica.bean.submit.ItemFormMetadataBean)

Example 35 with ItemGroupMetadataBean

use of org.akaza.openclinica.bean.submit.ItemGroupMetadataBean in project OpenClinica by OpenClinica.

the class ChangeCRFVersionController method confirmCRFVersionChange.

/*
     * Displays two set of columns for user to confirm his decision to switch to a new version of CRF
     * field name | OID | field value
     */
@RequestMapping(value = "/managestudy/confirmCRFVersionChange", method = RequestMethod.POST)
public // @RequestMapping("/managestudy/confirmCRFVersionChange")
ModelMap confirmCRFVersionChange(HttpServletRequest request, HttpServletResponse response, @RequestParam(value = "crfId", required = false) int crfId, @RequestParam(value = "crfName", required = false) String crfName, @RequestParam(value = "formLayoutId", required = false) int formLayoutId, @RequestParam(value = "formLayoutName", required = false) String formLayoutName, @RequestParam(value = "studySubjectLabel", required = false) String studySubjectLabel, @RequestParam(value = "studySubjectId", required = false) int studySubjectId, @RequestParam(value = "eventCRFId", required = false) int eventCRFId, @RequestParam(value = "eventDefinitionCRFId", required = false) int eventDefinitionCRFId, @RequestParam(value = "selectedVersionId", required = false) int selectedVersionId, @RequestParam(value = "selectedVersionName", required = false) String selectedVersionName, @RequestParam(value = "eventName", required = false) String eventName, @RequestParam(value = "eventCreateDate", required = false) String eventCreateDate, @RequestParam(value = "eventOrdinal", required = false) String eventOrdinal, @RequestParam("confirmCRFVersionSubmit") String as) {
    // to be removed for aquamarine
    if (!mayProceed(request)) {
        if (redirect(request, response, "/MainMenu?message=authentication_failed") == null)
            return null;
    }
    FormLayoutDAO formLayoutDao = new FormLayoutDAO(dataSource);
    selectedVersionName = (formLayoutDao.findByPK(selectedVersionId)).getName().trim();
    resetPanel(request);
    request.setAttribute("eventCRFId", eventCRFId);
    request.setAttribute("studySubjectLabel", studySubjectLabel);
    request.setAttribute("eventDefinitionCRFId", eventDefinitionCRFId);
    request.setAttribute("studySubjectId", studySubjectId);
    request.setAttribute("crfId", crfId);
    request.setAttribute("crfName", crfName);
    request.setAttribute("formLayoutId", formLayoutId);
    request.setAttribute("formLayoutName", formLayoutName.trim());
    request.setAttribute("selectedVersionId", selectedVersionId);
    if (selectedVersionName != null) {
        selectedVersionName = selectedVersionName.trim();
    }
    request.setAttribute("selectedVersionName", selectedVersionName);
    request.setAttribute("eventName", eventName);
    request.setAttribute("eventCreateDate", eventCreateDate);
    request.setAttribute("eventOrdinal", eventOrdinal);
    ModelMap gridMap = new ModelMap();
    ArrayList<String> pageMessages = initPageMessages(request);
    setupResource(request);
    if (selectedVersionId == -1) {
        // "Please select CRF
        String errorMessage = resword.getString("confirm_crf_version_em_select_version");
        // version";
        StringBuffer params = new StringBuffer();
        params.append("/pages/managestudy/chooseCRFVersion?crfId=" + crfId);
        params.append("&crfName=" + crfName);
        params.append("&formLayoutId=" + formLayoutId);
        params.append("&formLayoutName=" + formLayoutName);
        params.append("&selectedVersionName=" + selectedVersionName);
        params.append("&studySubjectLabel=" + studySubjectLabel);
        params.append("&studySubjectId=" + studySubjectId);
        params.append("&eventCRFId=" + eventCRFId);
        params.append("&eventDefinitionCRFId=" + eventDefinitionCRFId);
        params.append("&errorMessage=" + errorMessage);
        if (redirect(request, response, params.toString()) == null) {
            return null;
        }
    }
    request.getSession().removeAttribute("pageMessages");
    // get dATa for current crf version display
    // select name, ordinal, oc_oid, item_data_id, i.item_id, value from item_data id, item i
    // where id.item_id=i.item_id and event_crf_id = 171 order by i.item_id,ordinal;
    ArrayList<String[]> rows = new ArrayList<String[]>();
    int cur_counter = 0;
    int new_counter = 0;
    try {
        ItemDAO item_dao = new ItemDAO(dataSource);
        ItemDataBean d_bean = null;
        // get metadata to find repeat group or not
        ItemGroupMetadataDAO dao_item_form_mdata = new ItemGroupMetadataDAO(dataSource);
        HashMap<Integer, ItemGroupMetadataBean> hash_item_form_mdata = new HashMap<Integer, ItemGroupMetadataBean>();
        HashMap<Integer, ItemGroupMetadataBean> hash_new_item_form_mdata = new HashMap<Integer, ItemGroupMetadataBean>();
        List<ItemBean> cur_items = new ArrayList<>();
        List<ItemBean> new_items = new ArrayList<>();
        List<VersioningMap> origFormLayoutItems = versioningMapDao.findByFormLayoutId(formLayoutId);
        for (VersioningMap vm : origFormLayoutItems) {
            ItemGroupMetadataBean beans_item_form_mdata = (ItemGroupMetadataBean) dao_item_form_mdata.findByItemAndCrfVersion(vm.getVersionMapId().getItemId(), vm.getVersionMapId().getCrfVersionId());
            hash_item_form_mdata.put(new Integer(vm.getVersionMapId().getItemId()), beans_item_form_mdata);
            ItemBean itemBean = (ItemBean) item_dao.findByPK(vm.getVersionMapId().getItemId());
            cur_items.add(itemBean);
        }
        List<VersioningMap> newFormLayoutItems = versioningMapDao.findByFormLayoutId(selectedVersionId);
        for (VersioningMap vm : newFormLayoutItems) {
            ItemGroupMetadataBean bn_new_item_form_mdata = (ItemGroupMetadataBean) dao_item_form_mdata.findByItemAndCrfVersion(vm.getVersionMapId().getItemId(), vm.getVersionMapId().getCrfVersionId());
            hash_new_item_form_mdata.put(new Integer(vm.getVersionMapId().getItemId()), bn_new_item_form_mdata);
            ItemBean itemBean = (ItemBean) item_dao.findByPK(vm.getVersionMapId().getItemId());
            new_items.add(itemBean);
        }
        // get items description
        List<ItemBean> cur_items_with_data = item_dao.findAllWithItemDataByFormLayoutId(formLayoutId, eventCRFId);
        HashMap<String, ItemBean> hash_items_with_data = new HashMap<String, ItemBean>(cur_items_with_data.size());
        for (ItemBean item : cur_items_with_data) {
            hash_items_with_data.put(item.getOid(), item);
        }
        ItemBean temp = null;
        for (ItemBean item : cur_items) {
            temp = hash_items_with_data.get(item.getOid());
            if (temp != null) {
                item.setItemDataElements(temp.getItemDataElements());
            }
        }
        List<ItemBean> new_items_with_data = item_dao.findAllWithItemDataByFormLayoutId(selectedVersionId, eventCRFId);
        hash_items_with_data = new HashMap<String, ItemBean>(new_items_with_data.size());
        for (ItemBean item : new_items_with_data) {
            hash_items_with_data.put(item.getOid(), item);
        }
        for (ItemBean item : new_items) {
            temp = hash_items_with_data.get(item.getOid());
            if (temp != null) {
                item.setItemDataElements(temp.getItemDataElements());
            }
        }
        ItemBean cur_element = null;
        ItemBean new_element = null;
        ItemGroupMetadataBean bn_mdata = null;
        ItemGroupMetadataBean bn_new_mdata = null;
        while (cur_counter < cur_items.size() || new_counter < new_items.size()) {
            if (cur_counter < cur_items.size()) {
                cur_element = cur_items.get(cur_counter);
                bn_mdata = hash_item_form_mdata.get(new Integer(cur_element.getId()));
            }
            if (new_counter < new_items.size()) {
                new_element = new_items.get(new_counter);
                bn_new_mdata = hash_new_item_form_mdata.get(new Integer(new_element.getId()));
            }
            if (cur_counter < cur_items.size() && new_counter < new_items.size() && new_element.getId() == cur_element.getId()) {
                buildRecord(cur_element, new_element, bn_mdata, bn_new_mdata, rows);
                cur_counter++;
                new_counter++;
            } else if (cur_counter < cur_items.size() && (new_counter >= new_items.size() || new_element.getId() > cur_element.getId())) {
                buildRecord(cur_element, null, bn_mdata, null, rows);
                cur_counter++;
            } else if (new_counter < new_items.size() && (cur_counter >= cur_items.size() || new_element.getId() < cur_element.getId())) {
                buildRecord(null, new_element, null, bn_new_mdata, rows);
                new_counter++;
            }
        }
    } catch (Exception e) {
        logger.error(cur_counter + " " + new_counter);
        pageMessages.add(resword.getString("confirm_crf_version_em_dataextraction"));
    }
    request.setAttribute("pageMessages", pageMessages);
    gridMap.addAttribute("rows", rows);
    return gridMap;
}
Also used : ItemBean(org.akaza.openclinica.bean.submit.ItemBean) ItemDAO(org.akaza.openclinica.dao.submit.ItemDAO) HashMap(java.util.HashMap) ModelMap(org.springframework.ui.ModelMap) ArrayList(java.util.ArrayList) ItemGroupMetadataBean(org.akaza.openclinica.bean.submit.ItemGroupMetadataBean) VersioningMap(org.akaza.openclinica.domain.datamap.VersioningMap) HttpSessionRequiredException(org.springframework.web.HttpSessionRequiredException) ItemGroupMetadataDAO(org.akaza.openclinica.dao.submit.ItemGroupMetadataDAO) ItemDataBean(org.akaza.openclinica.bean.submit.ItemDataBean) FormLayoutDAO(org.akaza.openclinica.dao.submit.FormLayoutDAO) RequestMapping(org.springframework.web.bind.annotation.RequestMapping)

Aggregations

ItemGroupMetadataBean (org.akaza.openclinica.bean.submit.ItemGroupMetadataBean)36 ArrayList (java.util.ArrayList)20 ItemGroupBean (org.akaza.openclinica.bean.submit.ItemGroupBean)16 HashMap (java.util.HashMap)14 ItemBean (org.akaza.openclinica.bean.submit.ItemBean)13 ItemFormMetadataBean (org.akaza.openclinica.bean.submit.ItemFormMetadataBean)13 ItemGroupMetadataDAO (org.akaza.openclinica.dao.submit.ItemGroupMetadataDAO)12 DisplayItemGroupBean (org.akaza.openclinica.bean.submit.DisplayItemGroupBean)10 ItemDAO (org.akaza.openclinica.dao.submit.ItemDAO)10 DynamicsItemGroupMetadataBean (org.akaza.openclinica.domain.crfdata.DynamicsItemGroupMetadataBean)9 DisplayItemBean (org.akaza.openclinica.bean.submit.DisplayItemBean)8 EventCRFBean (org.akaza.openclinica.bean.submit.EventCRFBean)7 ItemDataBean (org.akaza.openclinica.bean.submit.ItemDataBean)7 SectionBean (org.akaza.openclinica.bean.submit.SectionBean)7 ItemFormMetadataDAO (org.akaza.openclinica.dao.submit.ItemFormMetadataDAO)7 ItemGroupDAO (org.akaza.openclinica.dao.submit.ItemGroupDAO)7 DynamicsItemFormMetadataBean (org.akaza.openclinica.domain.crfdata.DynamicsItemFormMetadataBean)4 List (java.util.List)3 Map (java.util.Map)3 DisplaySectionBean (org.akaza.openclinica.bean.submit.DisplaySectionBean)3