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