List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetName
@Override public String getSheetName(int sheetIndex)
From source file:com.liteoc.control.admin.SpreadSheetTableClassic.java
License:LGPL
public NewCRFBean toNewCRF(javax.sql.DataSource ds, ResourceBundle resPageMsg) throws IOException, CRFReadingException { String dbName = SQLInitServlet.getDBName(); NewCRFBean ncrf = new NewCRFBean(ds, crfId); ncrf.setCrfId(crfId);// set crf id 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; int dataTypeId = 5;// default is ST(String) type HashMap itemCheck = ncrf.getItemNames(); HashMap GroupCheck = ncrf.getItemGroupNames(); HashMap openQueries = new LinkedHashMap(); HashMap backupItemQueries = new LinkedHashMap();// save all the item // queries if // deleting item happens ArrayList secNames = new ArrayList(); // check for dupes, also // 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++) { HSSFSheet sheet = wb.getSheetAt(j);// sheetIndex); String sheetName = wb.getSheetName(j); if (sheetName.equalsIgnoreCase("CRF") || sheetName.equalsIgnoreCase("Sections") || sheetName.equalsIgnoreCase("Items")) { validSheetNum++;// w w w. ja v a 2 s .co m } } 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")); } // check to see if questions are referencing a valid section name, tbh // 7/30 for (int j = 0; j < numSheets; j++) { HSSFSheet sheet = wb.getSheetAt(j);// sheetIndex); 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 = ""; ArrayList resNames = new ArrayList();// records all the // 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 + "')"; } 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); } //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.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")); } 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); // responseLabel = responseLabel.replaceAll("<[^>]*>", // ""); 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)) { // YW 1-29-2008 << only one option for "calculation" // type and "group-calculation" type // but do we really need this variable these two // types? // actually, responseTypeId=9 is not necessary for // 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) { // right now, func is not required; but if there is // 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)) { System.out.println("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()); // eg, s=2.3 => I=2, // 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.length() > 0 && finalRegexp.charAt(0) == '/') && 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)); // does the above no longer work??? // 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 = ""; 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 ? 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); 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()) {// owner // can // 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 + " 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 (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")) { // 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 + ")"; } else { sql = "INSERT INTO RESPONSE_SET (LABEL, OPTIONS_TEXT, OPTIONS_VALUES, " + "RESPONSE_TYPE_ID, VERSION_ID)" + " VALUES ('" + stripQuotes(responseLabel) + "', E'" + stripQuotes(resOptions) + "','" + stripQuotes(resValues) + "'," + "(SELECT RESPONSE_TYPE_ID From RESPONSE_TYPE Where NAME='" + stripQuotes(responseType.toLowerCase()) + "')," + versionIdString + ")"; } if (!resNames.contains(responseLabel)) { queries.add(sql); 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='" + stripQuotes(parentItem) + "' AND owner_id = " + ownerId + " )"; } else { parentItemString = "(SELECT ITEM_ID FROM ITEM WHERE NAME='" + stripQuotes(parentItem) + "' AND owner_id = " + ownerId + " ORDER BY 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.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)" + " 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) + ")"; logger.warn(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)" + " 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 + ")"; } 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); // 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 + "," // + "(SELECT MAX(ITEM_ID) FROM ITEM WHERE // NAME='" // + 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 OID DESC LIMIT 1),'" + "" + "', '" + "" + "', '" + "" + "', " + 1 + ", " + 1 + ", '', 1," + versionIdString + "," // + "(SELECT ITEM_ID FROM ITEM WHERE // NAME='" + itemName + "' ORDER BY OID DESC // 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.OID DESC LIMIT 1)," + k + ",0)"; } queries.add(sqlGroupLabel); } } 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 (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)"; } 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())"; } queries.add(sql); } // 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)) { // errors.add(resPageMsg.getString("the") + " " + // resPageMsg.getString("CRF_NAME_column") // + // resPageMsg.getString("was_blank_in_the_CRF_worksheet")); // htmlErrors.put(j + ",1,0", // 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 + ")"; } 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("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 = ""; 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 + "')"; } 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); 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 + ")"; // 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; }
From source file:com.liteoc.control.admin.SpreadSheetTableClassic.java
License:LGPL
public String toHTML(int sheetIndex) throws IOException { StringBuffer buf = new StringBuffer(); HSSFWorkbook wb = new HSSFWorkbook(fs); int numSheets = wb.getNumberOfSheets(); for (int j = 0; j < numSheets; j++) { HSSFSheet sheet = wb.getSheetAt(j);// sheetIndex); String sheetName = wb.getSheetName(j); buf.append(sheetName + "<br>"); buf.append("<table border=\"2\""); buf.append("caption=\"" + wb.getSheetName(sheetIndex) + "\"" + ">"); int numCols = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < numCols; i++) { buf.append("<tr>"); if (sheet.getRow(i) == null) { continue; }/*from w w w . j av a 2 s.c o m*/ int numCells = sheet.getRow(i).getLastCellNum(); for (int y = 0; y < numCells; y++) { HSSFCell cell = sheet.getRow(i).getCell((short) y); int cellType = 0; if (cell == null) { cellType = HSSFCell.CELL_TYPE_BLANK; } else { cellType = cell.getCellType(); } switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: buf.append("<td> </td>"); break; case HSSFCell.CELL_TYPE_NUMERIC: buf.append("<td>" + cell.getNumericCellValue() + "</td>"); break; case HSSFCell.CELL_TYPE_STRING: buf.append("<td>" + cell.getStringCellValue() + "</td>"); break; default: buf.append("<td></td>"); } } buf.append("</tr>"); } buf.append("</table>"); } // end of sheet count, added by tbh 5-31 return buf.toString(); }
From source file:com.liteoc.control.admin.SpreadSheetTableRepeating.java
License:LGPL
public SpreadSheetTableRepeating(FileInputStream parseStream, UserAccountBean ub, String versionName, Locale locale, int studyId) throws IOException { // super();/*from ww w . ja v a2 s. c o m*/ this.fs = new POIFSFileSystem(parseStream); this.ub = ub; this.versionName = versionName; this.locale = locale; this.studyId = studyId; HSSFWorkbook wb = new HSSFWorkbook(fs); int numSheets = wb.getNumberOfSheets(); for (int j = 0; j < numSheets; j++) { HSSFSheet sheet = wb.getSheetAt(j);// sheetIndex); String sheetName = wb.getSheetName(j); if (sheetName.equalsIgnoreCase("groups")) { isRepeating = true; } // *** now we've set it up so that we can switch back to classic, // tbh, 06/07 } // should be set in the super(), tbh 05/2007 }
From source file:com.liteoc.control.admin.SpreadSheetTableRepeating.java
License:LGPL
public NewCRFBean toNewCRF(javax.sql.DataSource ds, ResourceBundle resPageMsg) throws IOException, CRFReadingException { String dbName = SQLInitServlet.getDBName(); NewCRFBean ncrf = new NewCRFBean(ds, crfId); ncrf.setCrfId(crfId);// set crf id 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(); String pVersion = ""; String pVerDesc = ""; int parentId = 0; int dataTypeId = 5;// default is ST(String) type HashMap itemCheck = ncrf.getItemNames(); HashMap GroupCheck = ncrf.getItemGroupNames(); HashMap openQueries = new LinkedHashMap(); HashMap backupItemQueries = new LinkedHashMap();// save all the item // queries if // deleting item happens ArrayList secNames = new ArrayList(); // check for dupes, also 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); HashMap<String, String> allItems = new HashMap<String, String>(); Map<String, String[]> controlValues = new HashMap<String, String[]>(); int maxItemFormMetadataId = new ItemFormMetadataDAO(ds).findMaxId(); int validSheetNum = 0; for (int j = 0; j < numSheets; j++) { HSSFSheet sheet = wb.getSheetAt(j);// sheetIndex); String sheetName = wb.getSheetName(j); if (sheetName.equalsIgnoreCase("CRF") || sheetName.equalsIgnoreCase("Sections") || sheetName.equalsIgnoreCase("Items")) { validSheetNum++;// w w w. j ava2 s . co m } } 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(); // check to see if questions are referencing a valid section name, tbh // 7/30 for (int j = 0; j < numSheets; j++) { sheet = wb.getSheetAt(j);// sheetIndex); 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()); // great minds apparently think alike...tbh, commented out // 06/19/2007 // logger.info("LastRowNum()" + sheet.getLastRowNum()); String secName = ""; String page = ""; // YW << for holding "responseLabel_responseType" ArrayList resPairs = new ArrayList(); // YW >> ArrayList resNames = new ArrayList();// records all the // 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 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(); 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; } int cellIndex = 0; 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(resPageMsg.getString("the") + " " + 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."); 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")); } repeats.add(itemName); cell = sheet.getRow(k).getCell((short) 1); String descLabel = getValue(cell); descLabel = descLabel.replaceAll("<[^>]*>", ""); 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")); } // 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.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")); } 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("The SECTION_LABEL column is not a * valid section at row " + k + ", Items worksheet. " + * "Please check the Sections worksheet to see that * there is a valid LABEL for this SECTION_LABEL."); * htmlErrors.put(j + "," + k + ",5", "NOT A VALID * LABEL"); */ 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")); } // ******************************************* // 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 cell = sheet.getRow(k).getCell((short) 6); String groupLabel = getValue(cell); groupLabel = groupLabel.replaceAll("<[^>]*>", ""); if (itemName.length() > 0) { if (!StringUtil.isBlank(groupLabel)) { allItems.put(itemName, groupLabel); } else { allItems.put(itemName, "Ungrouped"); } } 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")); } 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")); } cell = sheet.getRow(k).getCell((short) 9); 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) 10); 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) 11); if (cell != null) { page = getValue(cell); } cell = sheet.getRow(k).getCell((short) 12); String questionNum = getValue(cell); 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") + "."); 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") + "."); htmlErrors.put(j + "," + k + ",13", resPageMsg.getString("INVALID_FIELD")); } else { responseTypeId = ResponseType.getByName(responseType.toLowerCase()).getId(); } 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") + itemNames.get(k) + resPageMsg.getString("change_radio")); } } } cell = sheet.getRow(k).getCell((short) 14); String responseLabel = getValue(cell); // responseLabel = responseLabel.replaceAll("<[^>]*>", // ""); 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") + "."); 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); // resOptions = resOptions.replaceAll("<[^>]*>", ""); // >> 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"; } 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") + "."); 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(","); 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") + "."); 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"; } if ("file".equalsIgnoreCase(responseType)) { resValues = "file"; } if (!resNames.contains(responseLabel) && StringUtil.isBlank(resValues) && responseTypeId != ResponseType.TEXT.getId() && responseTypeId != ResponseType.TEXTAREA.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") + "."); htmlErrors.put(j + "," + k + ",16", resPageMsg.getString("required_field")); } // YW 1-25-2008 << validate scoring expression if (responseTypeId == 8 || responseTypeId == 9) { // right now, func is not required; but if there is // 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 (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)) { // @pgawade 31-May-2011 Added the check to // compare the size of resValArray and // mapValArray before comparing the individual // 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") + "."); 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("<[^>]*>", ""); 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")); 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") + "."); 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") + "."); htmlErrors.put(j + "," + k + ",19", 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) { if (labelWithType.containsKey(responseLabel)) { // make sure same responseLabels have same // 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")); 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); // eg, s=2.3 => I=2, // 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") + "."); 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 cell = sheet.getRow(k).getCell((short) 18); String default_value = getValue(cell); default_value = default_value.replaceAll("<[^>]*>", ""); if ("date".equalsIgnoreCase(dataType) && !"".equals(default_value)) { // BWP>> try block needs to be added, because // cell.getDateCellValue() // can throw an exception. // All database values are stored in this format? en // Locale MM/dd/yyyy try { default_value = new SimpleDateFormat(ApplicationConstants.getDateFormatInItemData()) .format(cell.getDateCellValue()); } catch (Exception e) { default_value = getValue(cell); } } // YW 1-30-2008 if (default_value.length() > 0 && (responseTypeId == 8 || responseTypeId == 9)) { errors.add(resPageMsg.getString("default_value_not_allowed_for_calculation") + k + ", " + resPageMsg.getString("items_worksheet") + "."); htmlErrors.put(j + "," + k + ",18", resPageMsg.getString("INVALID_FIELD")); } // YW >> 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.info("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") + ". " + resPageMsg.getString("regular_expression_contained") + " '\\\\', " + resPageMsg.getString("it_should_only_contain_one") + "'\\'. "); htmlErrors.put(j + "," + k + "," + cellIndex, resPageMsg.getString("INVALID_FIELD")); } else { if ((finalRegexp.length() > 0 && finalRegexp.charAt(0) == '/') && 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") + ". " + 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") + ". " + 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") + ". "); 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") + ". "); 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") + ". " + 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); // String phi = ""; // logger.info("++ phi: "+getValue(cell)); if (StringUtil.isBlank(phi)) { phi = "0"; } else // throws NPE, so added the guard clause above, tbh // 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") + ". " + 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); // String required = ""; // 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") + ". " + 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) { errors.add(resPageMsg.getString("the") + " " + resPageMsg.getString("ITEM_DISPLAY_STATUS_column") + " " + resPageMsg.getString("was_invalid_at_row") + " " + k + ", " + resPageMsg.getString("items_worksheet") + ". " + 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 (repeats.contains(p0)) { 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") + ". " + 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") + ". " + 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") + ". " + 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") + ". " + 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 ? 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()) {// owner // can // 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 + // added by jxu 08-29-06 to fix // 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 (oldItem.getDataType() == oldItem.getDataType().DATE && ib.getDataType() == ib.getDataType().PDATE)//New Feature allow date to pdate even if the data is entered { 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) + "','" + stripQuotes(resValues) + "'," + "(SELECT RESPONSE_TYPE_ID From RESPONSE_TYPE Where NAME='" + stripQuotes(responseType.toLowerCase()) + "')," + versionIdString + ")"; } // YW << a response Label can not be used for more than // one response type if (!resPairs.contains(responseLabel.toString().toLowerCase() + "_" + responseType.toString().toLowerCase())) { // YW >> if (!resNames.contains(responseLabel)) { queries.add(sql); 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? // YW << else { 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 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.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.warn(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); 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 item.item_id = (select max(item_id) from item where name = '" + controlItemName + "')" + " and item.owner_id = " + ownerId + " and cifm.item_id = item.item_id and cifm.item_form_metadata_id > " + maxItemFormMetadataId + "), '" + 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 item.item_id = (select max(item_id) from item where name = '" + controlItemName + "')" + " and item.owner_id = " + ownerId + " and cifm.item_id = item.item_id and cifm.item_form_metadata_id > " + maxItemFormMetadataId + "), '" + controlItemName + "', '" + stripQuotes(optionValue) + "', '" + stripQuotes(message) + "'" + ")"; } queries.add(sql2_1); } else { logger.info("No insert into scd_item_metadata for item name = " + itemName + "with Simple_Conditional_Display = \"" + display + "\"."); } } // 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); // if (!StringUtil.isBlank(groupLabel)) { // //add the item and the group label together // //so that we can extract them // //later down the road, tbh // itemsToGrouplabels.put(itemName,groupLabel); // } if (!StringUtil.isBlank(groupLabel)) { ItemGroupBean itemGroup; ItemGroupMetadataBean igMeta; igMeta = new ItemGroupMetadataBean(); itemGroup = new ItemGroupBean(); try { logger.info("found " + groupLabel); itemGroup = (ItemGroupBean) itemGroups.get(groupLabel); logger.info("*** Found " + groupLabel + " and matched with " + itemGroup.getName()); // if(itemGroup != null){ igMeta = itemGroup.getMeta(); // } else { // itemGroup = new ItemGroupBean(); // } 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 + " ORDER BY OID DESC LIMIT 1),'" + igMeta.getHeader() + "', '" + igMeta.getSubheader() + "', '" + // above removed? igMeta.getLayout() + "', " + // above removed? igMeta.getRepeatNum() + ", " + igMeta.getRepeatMax() + ", '" + igMeta.getRepeatArray() + "', " + // above removed? igMeta.getRowStartNumber() + "," + versionIdString + "," // + "(SELECT ITEM_ID FROM ITEM // WHERE NAME='" // + itemName // + "' AND owner_id = " + // ub.getId() + " ORDER BY OID DESC // 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.OID DESC LIMIT 1)," + k + ", " + igMeta.isShowGroup() + ", " + igMeta.isRepeatingGroup() + ")"; } queries.add(sqlGroupLabel); } catch (NullPointerException e) { // Auto-generated catch block, added tbh 102007 e.printStackTrace(); errors.add(resPageMsg.getString("Error_found_at_row") + " \"" + (k + 1) + "\"" + resPageMsg.getString("items_worksheet") + ". " + 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 + " ORDER BY OID DESC LIMIT 1),'" + "" + "', '" + "" + "', '" + "" + "', " + 1 + ", " + 1 + ", '', 1," + versionIdString + "," + selectCorrectItemQueryPostgres + "," + k + ", false)"; } // >>>>>>> .r10888 queries.add(sqlGroupLabel); } } // end of very long for loop, tbh // ************************************** // 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 } else if (sheetName.equalsIgnoreCase("Groups")) { logger.info("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 (blankRowCount == 5) { logger.info("hit end of the row "); break; } if (sheet.getRow(gk) == null) { blankRowCount++; 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 = com.liteoc.core.form.StringUtil.escapeSingleQuote(groupHeader); if (groupHeader != null && groupHeader.length() > 255) { errors.add(resPageMsg.getString("group_header_length_error")); } cell = sheet.getRow(gk).getCell((short) ++cellNo); String groupRepeatNumber = getValue(cell); // to be switched to int, tbh // 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.info("found a non-numeric code in a numeric field: groupRepeatNumber"); } } cell = sheet.getRow(gk).getCell((short) ++cellNo); String groupRepeatMax = getValue(cell); // to be switched to int, tbh // 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)) { groupRepeatMax = "40";// problem, tbh } 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.info("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); // igMeta.setLayout(groupLayout); // igMeta.setRepeatArray(groupRepeatArray); igMeta.setShowGroup(isShowGroup); try { igMeta.setRepeatMax(new Integer(Integer.parseInt(groupRepeatMax))); } catch (NumberFormatException n2) { n2.printStackTrace(); 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))); } catch (NumberFormatException n3) { n3.printStackTrace(); 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.info("found "+itemName+" when we passed group // label "+groupLabel); // ItemGroupBean itemGroup = new ItemGroupBean(); // //logger.info("found "+groupLabel); // itemGroup= (ItemGroupBean)itemGroups.get(groupLabel); // logger.info("*** 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.info("read sections"); // multiple rows, six cells, last one is number // changed 06/14/2007: seven cells, last on is number, the // BORDER, tbh for (int k = 1; k < numRows; k++) { if (blankRowCount == 5) { // logger.info("hit end of the row "); // kludgey way to zero out the rows that can get // created in the // editing process; is there a better way? tbh // 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.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("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.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)) { // errors.add("The CRF_NAME column was blank in the CRF // worksheet."); // htmlErrors.put(j + ",1,0", "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")); } } // try { // CRFBean checkName = (CRFBean) cdao.findByPK(crfId); // if (!checkName.getName().equals(crfName)) { // logger.info("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(); /* * 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.info("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 = " "; 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.info("html table:" + buf.toString()); ncrf.setHtmlTable(buf.toString()); return ncrf; }
From source file:com.ms.commons.test.datareader.impl.ExcelReadUtil.java
License:Open Source License
private static List<MemoryTable> readSheets(HSSFWorkbook wb, boolean isVerticalStyle) { List<MemoryTable> tableList = new ArrayList<MemoryTable>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet sheet = wb.getSheetAt(i); String sheetName = wb.getSheetName(i); if (sheet.getPhysicalNumberOfRows() > 0) { if (isVerticalStyle) { tableList.add(readSheetWithVerticalStyle(sheetName, sheet)); } else { tableList.add(readSheet(sheetName, sheet)); }/*from w ww . j a va2 s . c o m*/ } } return tableList; }
From source file:com.selfsoft.business.service.impl.TbBusinessBalanceServiceImpl.java
public void printTbBusinessBalanceTemplate(OutputStream os, String tpl, Long id, String companyName) { // ??/* ww w .ja v a2s. co m*/ TbBusinessBalance tbBusinessBalance = this.findById(id); // ? TbFixEntrust tbFixEntrust = tbFixEntrustService.findById(tbBusinessBalance.getTbFixEntrust().getId()); // ? TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId()); // ? TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId()); // ?? TmCompany tmCompany = tmCompanyService.acquireUniqueTmCompany(); // ? List<TbFixEntrustContent> tbFixEntrustContentList = tbFixEntrustContentService .findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrust.getId()); List<TbFixEntrustContent> tbFixEntrustContentListPage = new ArrayList<TbFixEntrustContent>(); List<TbFixEntrustContent> tbFixEntrustContentListTemp = new ArrayList<TbFixEntrustContent>(); List<TbFixEntrustContent> tbFixEntrustContentListAdd = new ArrayList<TbFixEntrustContent>(); if (null != tbFixEntrustContentList && tbFixEntrustContentList.size() > 0) { for (int i = 0; i < tbFixEntrustContentList.size(); i++) { boolean flag = false; if (tbFixEntrustContentListTemp.size() == 0) { tbFixEntrustContentListTemp.add(tbFixEntrustContentList.get(i)); } else { if (tbFixEntrustContentListTemp.size() > 1) { for (TbFixEntrustContent _tbFixEntrustContent : tbFixEntrustContentListTemp) { if (_tbFixEntrustContent.getStationName() .equals(tbFixEntrustContentList.get(i).getStationName()) && _tbFixEntrustContent.getTbWorkingInfo().getId() .equals(tbFixEntrustContentList.get(i).getTbWorkingInfo().getId()) && _tbFixEntrustContent.getFreesymbol() .equals(tbFixEntrustContentList.get(i).getFreesymbol()) ) { flag = true; break; } } } } if (flag) { continue; } else { tbFixEntrustContentListTemp.add(tbFixEntrustContentList.get(i)); } TbFixEntrustContent temp = tbFixEntrustContentList.get(i); BigDecimal d = new BigDecimal(temp.getFixHourAll()); BigDecimal d2 = new BigDecimal(temp.getFixHour()); for (int j = i + 1; j < tbFixEntrustContentList.size(); j++) { if (temp.getStationName().equals(tbFixEntrustContentList.get(j).getStationName()) && temp.getTbWorkingInfo().getId() .equals(tbFixEntrustContentList.get(j).getTbWorkingInfo().getId()) && temp.getFreesymbol().equals(tbFixEntrustContentList.get(j).getFreesymbol())) { d = d.add(new BigDecimal(tbFixEntrustContentList.get(j).getFixHourAll())); d2 = d2.add(new BigDecimal(tbFixEntrustContentList.get(j).getFixHour())); } } temp.setFixHourAll(d.doubleValue()); temp.setFixHour(d2.doubleValue()); if (!temp.getFreesymbol().equals(1d) || !temp.getFixHourAll().equals(0d)) { tbFixEntrustContentListAdd.add(temp); } } } // ?? List<TbMaintianVo> maintianvos = tbMaintainPartContentService .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL); // ? List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL); // List<TbBusinessBalanceItem> tbBusinessBalanceItemList = tbBusinessBalanceItemService .findGroupTbBusinessBalanceItemListByTbBusinessBalanceId(tbBusinessBalance.getId()); // ??? if (null == maintianvos) { maintianvos = new ArrayList<TbMaintianVo>(); } if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) { for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) { TbMaintianVo tbMaintianVo = new TbMaintianVo(); tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId()); tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName()); tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode()); tbMaintianVo.setPartName(tmStockOutDetVo.getPartName()); tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName()); tbMaintianVo.setPrice(tmStockOutDetVo.getPrice()); tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity()); tbMaintianVo.setTotal(tmStockOutDetVo.getTotal()); tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree()); tbMaintianVo.setProjectType(tmStockOutDetVo.getProjectType()); tbMaintianVo.setZl(tmStockOutDetVo.getZl()); tbMaintianVo.setXmlx(tmStockOutDetVo.getXmlx()); maintianvos.add(tbMaintianVo); } } /** * add by ccr 2010-12-18 */ List<TbMaintianVo> maintianvosTemp = new ArrayList<TbMaintianVo>(); List<TbMaintianVo> maintianvosAdd = new ArrayList<TbMaintianVo>(); if (maintianvos.size() > 0) { for (int i = 0; i < maintianvos.size(); i++) { boolean flag = false; if (maintianvosTemp.size() == 0) { maintianvosTemp.add(maintianvos.get(i)); } else { if (maintianvosTemp.size() > 1) { int l = 0; for (TbMaintianVo _tbMaintianVo : maintianvosTemp) { if (_tbMaintianVo.getPartId().equals(maintianvos.get(i).getPartId()) && _tbMaintianVo.getIsFree().equals(maintianvos.get(i).getIsFree()) && _tbMaintianVo.getPrice().equals(maintianvos.get(i).getPrice())) { // maintianvosTemp.set(l, maintianvos.get(i)); flag = true; break; } l++; } } } if (flag) { continue; } else { maintianvosTemp.add(maintianvos.get(i)); } TbMaintianVo temp = maintianvos.get(i); BigDecimal d1 = new BigDecimal(temp.getPartQuantity()); BigDecimal d2 = new BigDecimal(temp.getTotal()); for (int j = i + 1; j < maintianvos.size(); j++) { if (temp.getPartId().equals(maintianvos.get(j).getPartId()) && temp.getIsFree().equals(maintianvos.get(j).getIsFree()) && temp.getPrice().equals(maintianvos.get(j).getPrice())) { temp.setPrice(maintianvos.get(j).getPrice()); d1 = d1.add(new BigDecimal(maintianvos.get(j).getPartQuantity())); d2 = d2.add(new BigDecimal(maintianvos.get(j).getTotal())); } } temp.setPartQuantity(d1.doubleValue()); temp.setTotal(d2.doubleValue()); if (!temp.getIsFree().equals(1L) || !temp.getPartQuantity().equals(0d)) { /* * temp.setPrice(new BigDecimal(temp.getTotal()).divide(new * BigDecimal(temp.getPartQuantity()),2, * BigDecimal.ROUND_HALF_UP).setScale(2, * BigDecimal.ROUND_HALF_UP).doubleValue()); */ maintianvosAdd.add(temp); } } } int fixSize = (tbFixEntrustContentListAdd == null ? 0 : tbFixEntrustContentListAdd.size()); int partSize = (maintianvosAdd == null ? 0 : maintianvosAdd.size()); try { HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl)); HSSFSheet sheet = workbook.getSheetAt(0); HSSFCellStyle style = workbook.createCellStyle(); style.setWrapText(true); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 9); style.setFont(font); HSSFCellStyle styleRight = workbook.createCellStyle(); styleRight.setWrapText(true); styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleRight.setFont(font); HSSFCellStyle styleBorderThinAll = workbook.createCellStyle(); styleBorderThinAll.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAll.setWrapText(true); styleBorderThinAll.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThinAll.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAll.setFont(font); HSSFCellStyle styleBorderThickLeft = workbook.createCellStyle(); styleBorderThickLeft.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeft.setWrapText(true); styleBorderThickLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeft.setFont(font); HSSFCellStyle styleBorderThickRight = workbook.createCellStyle(); styleBorderThickRight.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRight.setWrapText(true); styleBorderThickRight.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleBorderThickRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRight.setFont(font); HSSFCellStyle styleBorderThinAllCenter = workbook.createCellStyle(); styleBorderThinAllCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThinAllCenter.setWrapText(true); styleBorderThinAllCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThinAllCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThinAllCenter.setFont(font); HSSFCellStyle styleBorderThickLeftCenter = workbook.createCellStyle(); styleBorderThickLeftCenter.setBorderLeft(HSSFCellStyle.BORDER_THICK); styleBorderThickLeftCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickLeftCenter.setWrapText(true); styleBorderThickLeftCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickLeftCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickLeftCenter.setFont(font); HSSFCellStyle styleBorderThickRightCenter = workbook.createCellStyle(); styleBorderThickRightCenter.setBorderRight(HSSFCellStyle.BORDER_THICK); styleBorderThickRightCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorderThickRightCenter.setWrapText(true); styleBorderThickRightCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleBorderThickRightCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleBorderThickRightCenter.setFont(font); HSSFCellStyle styleCenter = workbook.createCellStyle(); styleCenter.setWrapText(true); styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCenter.setFont(font); HSSFRow row = null; HSSFCell cell = null; row = sheet.getRow(2); cell = row.getCell(4); // cell.setCellStyle(style); if ("dfbz".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("dfbzCode")); } else if ("xtl".equals(companyName)) { cell.setCellValue(Constants.getCompanyMap().get("xtlCode")); } row = sheet.getRow(2); cell = row.getCell(17); if ("xtl".equals(companyName)) { cell.setCellValue(/* tmCompany.getCompanyName() */"?"); } else { cell.setCellValue(tmCompany.getCompanyName()); } row = sheet.getRow(2); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyPhone() == null ? "" : tmCompany.getCompanyPhone()); row = sheet.getRow(4); cell = row.getCell(4); if ("xtl".equals(companyName)) { cell.setCellValue(""); } else { cell.setCellValue(tmCompany.getCompanyZipCode() == null ? "" : tmCompany.getCompanyZipCode()); } row = sheet.getRow(4); cell = row.getCell(17); cell.setCellValue(tmCompany.getCompanyAddress() == null ? "" : tmCompany.getCompanyAddress()); row = sheet.getRow(4); cell = row.getCell(33); cell.setCellValue(tmCompany.getCompanyFax() == null ? "" : tmCompany.getCompanyFax()); row = sheet.getRow(10); cell = row.getCell(2); // cell.setCellStyle(style); String entrustCode = tbFixEntrust.getEntrustCode(); String[] es = entrustCode.split("-"); String newCode = "RO" + es[0].substring(2, 6) + es[1]; cell.setCellValue(newCode); row = sheet.getRow(10); cell = row.getCell(24); // cell.setCellStyle(style); cell.setCellValue((tbFixEntrust.getTmUser().getUserRealName() == null || "".equals(tbFixEntrust.getTmUser().getUserRealName())) ? tbFixEntrust.getTmUser().getUserName() : tbFixEntrust.getTmUser().getUserRealName()); row = sheet.getRow(10); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( CommonMethod.parseDateToString(tbBusinessBalance.getBananceDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(8); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), "yyyy-MM-dd HH:mm")); row = sheet.getRow(12); cell = row.getCell(4); cell.setCellValue(tbCustomer.getCustomerName()); row = sheet.getRow(12); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getLicenseCode()); row = sheet.getRow(12); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName()); row = sheet.getRow(14); cell = row.getCell(4); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(18); cell = row.getCell(12); cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone()); row = sheet.getRow(14); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getChassisCode()); row = sheet.getRow(16); cell = row.getCell(4); cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress()); row = sheet.getRow(16); cell = row.getCell(24); cell.setCellValue(CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd")); row = sheet.getRow(16); cell = row.getCell(41); if ("xtl".equals(companyName)) { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } else { cell.setCellValue( tbFixEntrust.getEnterStationKilo() == null ? "" : new BigDecimal(tbFixEntrust.getEnterStationKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString() + " Km"); } row = sheet.getRow(18); cell = row.getCell(4); cell.setCellValue(tbCustomer.getContractPerson() == null ? "" : tbCustomer.getContractPerson()); row = sheet.getRow(18); cell = row.getCell(24); cell.setCellValue(tbCarInfo.getEngineCode() == null ? "" : tbCarInfo.getEngineCode()); row = sheet.getRow(18); cell = row.getCell(41); cell.setCellValue(tbCarInfo.getColor() == null ? "" : tbCarInfo.getColor()); /* * if (null != tbBusinessBalanceItemList && * tbBusinessBalanceItemList.size() > 0) { * * for (TbBusinessBalanceItem tbBusinessBalanceItem : * tbBusinessBalanceItemList) { * * if ("XLCLF".equals(tbBusinessBalanceItem .getBalanceItemCode())) * { * * row = sheet.getRow(77); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * if ("XLGSF".equals(tbBusinessBalanceItem .getBalanceItemCode())) * { * * row = sheet.getRow(75); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * if ("ZJE" .equals(tbBusinessBalanceItem.getBalanceItemCode())) { * * row = sheet.getRow(81); * * cell = row.getCell(24); * * cell.setCellValue(new BigDecimal(tbBusinessBalanceItem * .getBalanceItemTotal()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * continue; } * * } * * row = sheet.getRow(79); * * cell = row.getCell(24); * * cell.setCellValue("0.00"); * * } */ BigDecimal d_f_w = new BigDecimal("0.00"); BigDecimal d_f_p = new BigDecimal("0.00"); BigDecimal d_f_i = new BigDecimal("0.00"); BigDecimal d_f_c = new BigDecimal("0.00"); if (fixSize > 0) { for (int i = 0; i < fixSize; i++) { TbFixEntrustContent t = tbFixEntrustContentListAdd.get(i); if ("W".equals(t.getZl())) { d_f_w = d_f_w.add(new BigDecimal(t.getFixHourAll())); } else if ("P".equals(t.getZl())) { d_f_p = d_f_p.add(new BigDecimal(t.getFixHourAll())); } else if ("I".equals(t.getZl())) { d_f_i = d_f_i.add(new BigDecimal(t.getFixHourAll())); } else if ("C".equals(t.getZl())) { d_f_c = d_f_c.add(new BigDecimal(t.getFixHourAll())); } } } BigDecimal d_p_w = new BigDecimal("0.00"); BigDecimal d_p_p = new BigDecimal("0.00"); BigDecimal d_p_i = new BigDecimal("0.00"); BigDecimal d_p_c = new BigDecimal("0.00"); if (partSize > 0) { for (int i = 0; i < partSize; i++) { TbMaintianVo t = maintianvosAdd.get(i); if ("W".equals(t.getZl())) { d_p_w = d_p_w.add(new BigDecimal(t.getTotal())); } else if ("P".equals(t.getZl())) { d_p_p = d_p_p.add(new BigDecimal(t.getTotal())); } else if ("I".equals(t.getZl())) { d_p_i = d_p_i.add(new BigDecimal(t.getTotal())); } else if ("C".equals(t.getZl())) { d_p_c = d_p_c.add(new BigDecimal(t.getTotal())); } } } row = sheet.getRow(75); cell = row.getCell(6); cell.setCellValue(d_f_w.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(6); cell.setCellValue(d_p_w.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(6); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(6); cell.setCellValue( d_f_w.add(d_p_w).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(12); cell.setCellValue(d_f_p.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(12); cell.setCellValue(d_p_p.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(12); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(12); cell.setCellValue( d_f_p.add(d_p_p).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(18); cell.setCellValue(d_f_i.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(18); cell.setCellValue(d_p_i.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(18); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(18); cell.setCellValue( d_f_i.add(d_p_i).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(75); cell = row.getCell(24); cell.setCellValue(d_f_c.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(77); cell = row.getCell(24); cell.setCellValue(d_p_c.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(79); cell = row.getCell(24); cell.setCellValue("0.00"); row = sheet.getRow(81); cell = row.getCell(24); cell.setCellValue( d_f_c.add(d_p_c).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); Double wt = tbBusinessBalance.getWorkingHourTotalAll() == null ? 0d : tbBusinessBalance.getWorkingHourTotalAll(); Double wf = tbBusinessBalance.getWorkingHourFavourRate() == null ? 0d : tbBusinessBalance.getWorkingHourFavourRate(); Double pt = tbBusinessBalance.getFixPartTotalAll() == null ? 0d : tbBusinessBalance.getFixPartTotalAll(); Double pf = tbBusinessBalance.getFixPartFavourRate() == null ? 0d : tbBusinessBalance.getFixPartFavourRate(); Double st = tbBusinessBalance.getSolePartTotalAll() == null ? 0d : tbBusinessBalance.getSolePartTotalAll(); Double sf = tbBusinessBalance.getSolePartFavourRate() == null ? 0d : tbBusinessBalance.getSolePartFavourRate(); BigDecimal fixF = new BigDecimal(wt).divide(new BigDecimal(1 - wf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(wf)); BigDecimal partF = new BigDecimal(pt).divide(new BigDecimal(1 - pf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(pf)); BigDecimal soleF = new BigDecimal(st).divide(new BigDecimal(1 - sf), 2, BigDecimal.ROUND_HALF_UP) .multiply(new BigDecimal(sf)); row = sheet.getRow(83); cell = row.getCell(4); // cell.setCellStyle(styleCenter); cell.setCellValue(fixF.add(partF).add(soleF).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); row = sheet.getRow(83); cell = row.getCell(14); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbBusinessBalance.getPayedAmount()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); row = sheet.getRow(68); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(CommonMethod .parseDateToString(tbBusinessBalance.getTbFixEntrust().getRemindMaintainDate(), "yyyy-MM-dd")); row = sheet.getRow(70); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(tbBusinessBalance.getTbFixEntrust().getRemindMaintainKilo() == null ? "" : new BigDecimal(tbBusinessBalance.getTbFixEntrust().getRemindMaintainKilo()) .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP) + ""); row = sheet.getRow(77); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(tbBusinessBalance.getTbFixEntrust().getRemark()); ByteArrayOutputStream byteArrayOutImgLion = new ByteArrayOutputStream(); String pic = ""; if ("dfbz".equals(companyName)) { pic = "/lion_jsd.png"; } else if ("xtl".equals(companyName)) { pic = "/xtl_jsd.png"; } BufferedImage bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream(pic)); ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 41, 0, (short) 46, 7); patriarch.createPicture(anchorLion, workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); /* * int maxSize = 19; * * int maxFixSize = (fixSize > 19 ? 19 : fixSize); * * int maxPartSize = maxSize - maxFixSize > partSize ? partSize : * maxSize - maxFixSize; * * int partStartRow = (fixSize == 0 ? 21 : 21 + maxFixSize * 2 + 4); * * int partStartRowClone = ((fixSize - maxFixSize) == 0 ? 21 : 21 + * (fixSize - maxFixSize) * 2 + 4); * * int minBlank = 9; * * if ((fixSize + partSize) > 17) { * * HSSFSheet sheetClone = workbook.cloneSheet(0); * * HSSFRow rowClone = sheetClone.getRow(87); * * HSSFCell cellClone = rowClone.getCell(34); * * cellClone.setCellValue("2 2 "); * * HSSFPatriarch patriarchClone = sheetClone * .createDrawingPatriarch(); * * HSSFClientAnchor anchorLionClone = new HSSFClientAnchor(0, 0, * 1023, 200, (short) 41, 0, (short) 47, 8); * * patriarchClone.createPicture(anchorLionClone, workbook * .addPicture(byteArrayOutImgLion.toByteArray(), * HSSFWorkbook.PICTURE_TYPE_PNG)); * * if ((fixSize - maxFixSize) > 0) { * * * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, (short) * 49)); * * sheetClone.addMergedRegion(new Region(21, (short) 0, 21, (short) * 0)); * * * sheetClone.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); * * rowClone = sheetClone.getRow(21); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellValue(""); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(21); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(24); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(23); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * for (int i = 0; i < (fixSize - maxFixSize); i++) { * * TbFixEntrustContent tbFixEntrustContent = * tbFixEntrustContentListAdd .get(i + maxFixSize); * * rowClone = sheetClone.getRow(25 + i * 2); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(i + 1); * * cellClone = rowClone.getCell(2); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getStationCode()); * * cellClone = rowClone.getCell(10); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getStationName()); * * cellClone = rowClone.getCell(21); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent.getZl()); * * cellClone = rowClone.getCell(24); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbFixEntrustContent .getProjectType()); * * cellClone = rowClone.getCell(30); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getFixHour()).divide( new BigDecimal("1.00"), * 2, BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(34); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getWorkingHourPrice()) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(40); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbBusinessBalance * .getWorkingHourFavourRate()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(44); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal( * tbFixEntrustContent.getFixHourAll()).divide( new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } * * } * * if ((partSize - maxPartSize) > 0) { * * * sheetClone.addMergedRegion(new Region(partStartRowClone, (short) * 0, partStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new Region(partStartRowClone, (short) * 0, partStartRowClone, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * partStartRowClone, partStartRowClone, 0, 49)); * * rowClone = sheetClone.getRow(partStartRowClone); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellStyle(styleBorderThickLeft); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(partStartRowClone + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(partStartRowClone + 2 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(partStartRowClone + 2); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * for (int i = 0; i < (partSize - maxPartSize); i++) { * * TbMaintianVo tbMaintianVo = maintianvosAdd.get(i + maxPartSize); * * rowClone = sheetClone.getRow(partStartRowClone + 4 + i 2); * * if(null == rowClone) break; * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(i + 1); * * cellClone = rowClone.getCell(2); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getPartCode()); * * cellClone = rowClone.getCell(10); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getPartName()); * * cellClone = rowClone.getCell(21); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getZl()); * * cellClone = rowClone.getCell(24); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(tbMaintianVo.getProjectType()); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleRight); * * cellClone.setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(34); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbMaintianVo * .getPrice()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(40); * * // .setCellStyle(styleCenter); * * cellClone.setCellValue(new BigDecimal(tbBusinessBalance * .getFixPartFavourRate()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cellClone = rowClone.getCell(44); * * // cellClone.setCellStyle(styleCenter); * * cellClone .setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()) .multiply( new BigDecimal(tbMaintianVo * .getPrice())) .divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP) .toString()); } * * } * * int attachStartRowClone = 21; * * if ((fixSize - maxFixSize + partSize - maxPartSize) <= 17) { * * if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) == 0 * && (fixSize - maxFixSize) < 18) { * * attachStartRowClone = 21 + 2 + 2 + (fixSize - maxFixSize) * 2; * * } * * if ((partSize - maxPartSize) != 0 && (fixSize - maxFixSize) == 0 * && (partSize - maxPartSize) < 18) { * * attachStartRowClone = 21 + 2 + 2 + 2 + (partSize - maxPartSize) * * 2; } * * if ((fixSize - maxFixSize) != 0 && (partSize - maxPartSize) != 0 * && (fixSize - maxFixSize + partSize - maxPartSize < 18)) { * * attachStartRowClone = 21 + 2 + 2 + 2 + 2 + (fixSize - maxFixSize) * * 2 + (partSize - maxPartSize) * 2; * * } * * * sheetClone.addMergedRegion(new Region(attachStartRowClone, * (short) 0, attachStartRowClone, (short) 49)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone, * (short) 0, attachStartRowClone, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone, attachStartRowClone, 0, 49)); * * rowClone = sheetClone.getRow(attachStartRowClone); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(style); * * cellClone.setCellStyle(styleBorderThickLeft); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(attachStartRowClone + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeft); * * rowClone = sheetClone.getRow(attachStartRowClone + 2 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * rowClone = sheetClone.getRow(attachStartRowClone + 2); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("??"); * * if ("dfbz".equals(companyName)) { * * cellClone = rowClone.getCell(10); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("??"); * * cellClone = rowClone.getCell(21); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(24); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(30); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(34); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue("?"); * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * } else if ("xtl".equals(companyName)) { * * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 2, * (short) 2, attachStartRowClone + 2, (short) 39)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 2, * (short) 2, attachStartRowClone + 2, (short) 39)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone + 2, attachStartRowClone + 2, 2, 39)); * * cellClone = rowClone.getCell(2); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * } * * cellClone = rowClone.getCell(40); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * cellClone = rowClone.getCell(44); * * cellClone.setCellStyle(styleCenter); * * cellClone.setCellValue(""); * * rowClone = sheetClone.getRow(attachStartRowClone + 4); * * cellClone = rowClone.getCell(0); * * // cellClone.setCellStyle(styleCenter); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * cellClone.setCellValue("1"); * * rowClone = sheetClone.getRow(attachStartRowClone + 4 + 1); * * cellClone = rowClone.getCell(0); * * cellClone.setCellStyle(styleBorderThickLeftCenter); * * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 6, * (short) 0, 66, (short) 49)); * * sheetClone.addMergedRegion(new Region(attachStartRowClone + 6, * (short) 0, 66, (short) 49)); * * * sheetClone.addMergedRegion(new CellRangeAddress( * attachStartRowClone + 6, 66, 0, 49)); * * } * * } else { * * row = sheet.getRow(87); * * cell = row.getCell(34); * * cell.setCellValue(" 1 1 "); * * } * * if (fixSize > 0) { * * * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) 49)); * * sheet.addMergedRegion(new Region(21, (short) 0, 21, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); * * row = sheet.getRow(21); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellValue(""); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(22); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(24); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(23); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell.setCellStyle(styleBorderThickLeftCenter); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * for (int i = 0; i < maxFixSize; i++) { * * TbFixEntrustContent tbFixEntrustContent = * tbFixEntrustContentListAdd .get(i); * * row = sheet.getRow(25 + i * 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(i + 1); * * cell = row.getCell(2); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getStationCode()); * * cell = row.getCell(10); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getStationName()); * * cell = row.getCell(21); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getZl()); * * cell = row.getCell(24); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbFixEntrustContent.getProjectType()); * * cell = row.getCell(30); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getFixHour()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(34); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(40); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal((1 - tbBusinessBalance * .getWorkingHourFavourRate()) * 100) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(44); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbFixEntrustContent * .getFixHourAll()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); } * * } * * if ((fixSize <= maxSize - 1) && partSize > 0) { * * * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(partStartRow, (short) 0, * partStartRow, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(partStartRow, * partStartRow, 0, 49)); * * row = sheet.getRow(partStartRow); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellValue(""); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(partStartRow + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(partStartRow + 2 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * row = sheet.getRow(partStartRow + 2); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("??"); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * if (partSize > 0) { * * for (int i = 0; i < maxPartSize; i++) { * * TbMaintianVo tbMaintianVo = maintianvosAdd.get(i); * * row = sheet.getRow(partStartRow + 4 + i * 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(i + 1); * * cell = row.getCell(2); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getPartCode()); * * cell = row.getCell(10); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getPartName()); * * cell = row.getCell(21); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getZl()); * * cell = row.getCell(24); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(tbMaintianVo.getProjectType()); * * cell = row.getCell(30); * * cell.setCellStyle(styleRight); * * cell.setCellValue(new BigDecimal(tbMaintianVo * .getPartQuantity()).divide( new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(34); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbMaintianVo * .getPrice()).divide(new BigDecimal("1.00"), 2, * BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(40); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal((1 - tbBusinessBalance * .getFixPartFavourRate()) * 100).divide( new BigDecimal("1.00"), * 2, BigDecimal.ROUND_HALF_UP).toString()); * * cell = row.getCell(44); * * // cell.setCellStyle(styleCenter); * * cell.setCellValue(new BigDecimal(tbMaintianVo .getPartQuantity()) * .multiply( new BigDecimal(tbMaintianVo.getPrice())) .divide(new * BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } * * } * * } * * int attachStartRow = 21; * * if ((fixSize + partSize) <= 17) { * * if (fixSize != 0 && partSize == 0 && fixSize < 18) { * * attachStartRow = 19 + 2 + 2 + 2 + fixSize * 2; * * } * * if (partSize != 0 && fixSize == 0 && partSize < 18) { * * attachStartRow = 19 + 2 + 2 + 2 + partSize * 2; } * * if (fixSize != 0 && partSize != 0 && (fixSize + partSize < 18)) { * * attachStartRow = 19 + 2 + 2 + 2 + 2 + 2 + fixSize * 2 + partSize * * 2; * * } * * * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow, (short) 0, * attachStartRow, (short) 49)); * * * sheet.addMergedRegion(new CellRangeAddress(attachStartRow, * attachStartRow, 0, 49)); * * row = sheet.getRow(attachStartRow); * * cell = row.getCell(0); * * // cell.setCellStyle(style); * * cell.setCellStyle(styleBorderThickLeft); * * cell.setCellValue(""); * * row = sheet.getRow(attachStartRow + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeft); * * row = sheet.getRow(attachStartRow + 2 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * row = sheet.getRow(attachStartRow + 2); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("??"); * * if ("dfbz".equals(companyName)) { * * cell = row.getCell(10); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("??"); * * cell = row.getCell(21); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(24); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(30); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(34); * * cell.setCellStyle(styleCenter); * * cell.setCellValue("?"); * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * } else if ("xtl".equals(companyName)) { * * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * * sheet.addMergedRegion(new CellRangeAddress( attachStartRow + 2, * attachStartRow + 2, 2, 39)); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * } * * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * sheet.addMergedRegion(new Region(attachStartRow + 2, (short) 2, * attachStartRow + 2, (short) 39)); * * cell = row.getCell(2); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * * cell = row.getCell(40); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * cell = row.getCell(44); * * cell.setCellStyle(styleCenter); * * cell.setCellValue(""); * * row = sheet.getRow(attachStartRow + 4); * * cell = row.getCell(0); * * // cell.setCellStyle(styleCenter); * cell.setCellStyle(styleBorderThickLeftCenter); * * cell.setCellValue("1"); * * row = sheet.getRow(attachStartRow + 4 + 1); * * cell = row.getCell(0); * * cell.setCellStyle(styleBorderThickLeftCenter); * * if ("xtl".equals(companyName)) { * * * sheet.addMergedRegion(new Region(attachStartRow + 6, (short) 0, * 66, (short) 49)); * * sheet.addMergedRegion(new Region(attachStartRow + 6, (short) 0, * 66, (short) 49)); * * if (attachStartRow + 6 < 66) { * * sheet.addMergedRegion(new CellRangeAddress( attachStartRow + 6, * 66, 0, 49)); } * * } } */ int pageSize = 0; if ((fixSize > 0 && partSize == 0) || (partSize > 0 && fixSize == 0)) { if ((fixSize + partSize) <= 18) { pageSize = 1; } else { pageSize = (fixSize + partSize - 19) / 21 + 2; } for (int p = pageSize - 1; p >= 0; p--) { int maxSize = (p == pageSize - 1) ? (fixSize + partSize - p * 21) : 21; int startRow = 21; if (p == 0) { sheet = workbook.getSheetAt(0); for (int k = 1; k <= pageSize / 2; k++) { workbook.setSheetOrder(workbook.getSheetName(k), pageSize - k); } for (int k = 1; k < pageSize; k++) { workbook.setSheetName(k, "" + (k + 1) + ""); } workbook.setSheetName(0, "" + 1 + ""); } else { sheet = workbook.cloneSheet(0); //this.clearSheet(sheet, 21, 0, 66, 49); } if (fixSize > 0) { sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); row = sheet.getRow(21); cell = row.getCell(0); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(22); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(24); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(23); cell = row.getCell(0); cell.setCellValue("??"); cell.setCellStyle(styleBorderThickLeftCenter); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < maxSize; i++) { TbFixEntrustContent tbFixEntrustContent = tbFixEntrustContentListAdd.get(p * 21 + i); row = sheet.getRow(25 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * 21 + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getProjectType()); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessBalance.getWorkingHourFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } else if (partSize > 0) { sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 0, 49)); row = sheet.getRow(startRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(startRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(startRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(startRow + 2); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < maxSize; i++) { TbMaintianVo tbMaintianVo = maintianvosAdd.get(p * 21 + i); row = sheet.getRow(startRow + 4 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * 21 + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getProjectType()); cell = row.getCell(30); cell.setCellStyle(styleRight); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal((1 - tbBusinessBalance.getFixPartFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } row = sheet.getRow(87); cell = row.getCell(34); cell.setCellValue(" " + (p + 1) + " " + pageSize + " "); if (p == pageSize - 1) { int attachStartRow = 21 + 2 + 2 + (fixSize + partSize - (pageSize - 1) * 21) * 2; sheet.addMergedRegion(new CellRangeAddress(attachStartRow, attachStartRow, 0, 49)); row = sheet.getRow(attachStartRow); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(attachStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(attachStartRow + 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); if ("dfbz".equals(companyName)) { cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(""); } cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 4); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("1"); row = sheet.getRow(attachStartRow + 4 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); if ("xtl".equals(companyName)) { if (attachStartRow + 6 < 66) { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 66, 0, 49)); } } } } } if (fixSize > 0 && partSize > 0) { if ((fixSize + partSize) <= 16) { pageSize = 1; } else { pageSize = (fixSize + partSize - 17) / 19 + 2; } int fixPageSize = fixSize / pageSize; int partPageSize = partSize / pageSize; int fixLastPageSize = fixSize - (pageSize - 1) * fixPageSize; int partLastPageSize = partSize - (pageSize - 1) * partPageSize; if ((fixPageSize + partPageSize) < 19 && fixPageSize != 0) { //if(fixLastPageSize >= partSize){ if (fixPageSize >= partPageSize) { fixPageSize = (19 - partPageSize) > fixSize ? fixSize : (19 - partPageSize); } else { partPageSize = (19 - fixPageSize) > partSize ? partSize : (19 - fixPageSize); } } fixLastPageSize = fixSize - (pageSize - 1) * fixPageSize; partLastPageSize = (partSize - (pageSize - 1) * partPageSize) < 0 ? 0 : (partSize - (pageSize - 1) * partPageSize); for (int p = pageSize - 1; p >= 0; p--) { int partStartRow = 21 + 2 + 2 + ((p == pageSize - 1) ? fixLastPageSize : fixPageSize) * 2; if (p == 0) { sheet = workbook.getSheetAt(0); if (pageSize > 1) { for (int k = 1; k <= pageSize / 2; k++) { workbook.setSheetOrder(workbook.getSheetName(k), pageSize - k); } for (int k = 1; k < pageSize; k++) { workbook.setSheetName(k, "" + (k + 1) + ""); } } workbook.setSheetName(0, "" + 1 + ""); } else { sheet = workbook.cloneSheet(0); } if (fixSize > 0) { sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 49)); row = sheet.getRow(21); cell = row.getCell(0); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(22); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(24); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(23); cell = row.getCell(0); cell.setCellValue("??"); cell.setCellStyle(styleBorderThickLeftCenter); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < ((p == pageSize - 1) ? fixLastPageSize : fixPageSize); i++) { TbFixEntrustContent tbFixEntrustContent = tbFixEntrustContentListAdd .get(p * fixPageSize + i); row = sheet.getRow(25 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * fixPageSize + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getStationName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbFixEntrustContent.getProjectType()); cell = row.getCell(30); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHour()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getWorkingHourPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue( new BigDecimal((1 - tbBusinessBalance.getWorkingHourFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP) .toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbFixEntrustContent.getFixHourAll()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } if (partSize > 0) { sheet.addMergedRegion(new CellRangeAddress(partStartRow, partStartRow, 0, 49)); row = sheet.getRow(partStartRow); cell = row.getCell(0); // cell.setCellStyle(style); cell.setCellValue(""); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(partStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(partStartRow + 2); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); for (int i = 0; i < ((p == pageSize - 1) ? partLastPageSize : partPageSize); i++) { if (p * partPageSize + i > partSize - 1) { break; } TbMaintianVo tbMaintianVo = maintianvosAdd.get(p * partPageSize + i); row = sheet.getRow(partStartRow + 4 + i * 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellValue(p * partPageSize + i + 1); cell = row.getCell(2); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartCode()); cell = row.getCell(10); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getPartName()); cell = row.getCell(21); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getZl()); cell = row.getCell(24); // cell.setCellStyle(styleCenter); cell.setCellValue(tbMaintianVo.getProjectType()); cell = row.getCell(30); cell.setCellStyle(styleRight); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(34); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPrice()) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(40); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal((1 - tbBusinessBalance.getFixPartFavourRate()) * 100) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); cell = row.getCell(44); // cell.setCellStyle(styleCenter); cell.setCellValue(new BigDecimal(tbMaintianVo.getPartQuantity()) .multiply(new BigDecimal(tbMaintianVo.getPrice())) .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString()); } } row = sheet.getRow(87); cell = row.getCell(34); cell.setCellValue(" " + (p + 1) + " " + pageSize + " "); if (p == pageSize - 1) { int attachStartRow = 21 + 2 + 2 + 2 + 2 + (fixLastPageSize + partLastPageSize) * 2; sheet.addMergedRegion(new CellRangeAddress(attachStartRow, attachStartRow, 0, 49)); row = sheet.getRow(attachStartRow); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeft); row = sheet.getRow(attachStartRow + 2 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); row = sheet.getRow(attachStartRow + 2); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("??"); if ("dfbz".equals(companyName)) { cell = row.getCell(10); cell.setCellStyle(styleCenter); cell.setCellValue("??"); cell = row.getCell(21); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(24); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(30); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(34); cell.setCellStyle(styleCenter); cell.setCellValue("?"); cell = row.getCell(40); cell.setCellStyle(styleCenter); } else if ("xtl".equals(companyName)) { cell = row.getCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(""); } cell = row.getCell(40); cell.setCellStyle(styleCenter); cell.setCellValue(""); cell = row.getCell(44); cell.setCellStyle(styleCenter); cell.setCellValue(""); row = sheet.getRow(attachStartRow + 4); cell = row.getCell(0); // cell.setCellStyle(styleCenter); cell.setCellStyle(styleBorderThickLeftCenter); cell.setCellValue("1"); row = sheet.getRow(attachStartRow + 4 + 1); cell = row.getCell(0); cell.setCellStyle(styleBorderThickLeftCenter); if ("xtl".equals(companyName)) { if (attachStartRow + 6 < 66) { sheet.addMergedRegion(new CellRangeAddress(attachStartRow + 6, 66, 0, 49)); } } } } } workbook.write(os); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 8 41 }
From source file:com.servoy.extensions.plugins.excelxport.ImportSelectSheetPanel.java
License:Open Source License
public Runnable needsToRunFirst(final boolean forward) { return new Runnable() { public void run() { try { parent.blockGUI(Messages.getString("servoy.plugin.import.status.organizingData")); //$NON-NLS-1$ if (forward) { HSSFWorkbook wb = (HSSFWorkbook) state.getProperty("workbook"); //$NON-NLS-1$ SortedComboModel dcm = new SortedComboModel(StringComparator.INSTANCE); for (int i = 0; i < wb.getNumberOfSheets(); i++) { dcm.add(wb.getSheetName(i)); }//from www . j a v a 2 s . c om sheetSelect.setModel(dcm); HSSFSheet sheet = wb.getSheetAt(0);//take first default tableModel = new SheetTableModel(sheet); tableModel.setUseHeaderRow(headerRows.isSelected()); table.setModel(tableModel); } } finally { parent.releaseGUI(); } } }; }
From source file:com.web.mavenproject6.other.XLSParser.java
public static String parse(String name) { String result = ""; InputStream in = null;/* w w w . j a v a 2s . c o m*/ HSSFWorkbook wb = null; try { in = new FileInputStream(name); wb = new HSSFWorkbook(in); } catch (IOException e) { e.printStackTrace(); } System.out.println("!!!!sheet count:" + wb.getNumberOfSheets()); for (int i = 0; i < wb.getNumberOfSheets(); i++) { System.out.println("!!!!sheet[" + i + "]:" + wb.getSheetName(i)); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> it = sheet.iterator(); String buf = ""; while (it.hasNext()) { Row row = it.next(); //result += row.getRowNum() + ":"; switch (row.getRowNum() + 1) { case 13: result += readCell(row); result += "<br>"; break; case 14: result += readCell(row); result += "<br>"; break; case 15: result += readCell(row); break; case 16: result += readCell(row); result += "<br>"; break; case 18: result += readCell(row); break; case 19: result += readCell(row); break; case 20: result += readCell(row); break; case 21: result += readCell(row); break; case 22: result += readCell(row); result += "<br>"; break; case 25: result += readCell(row); break; case 26: result += readCell(row); result += "<br>"; break; default: result += readCell(row); result += "<br>"; break; } // if (row.getRowNum() + 1 != 34 || row.getRowNum() + 1 != 34) { // result += readCell(row); // } else { // result += "[" + readCell(row, 0, 7) + "][" + readCell(row, 7, 10); // } } } return result; }
From source file:com.web.mavenproject6.other.XLSParser.java
public static String parseStatisticDoc(String name) { String result = ""; InputStream in = null;//from w w w .ja v a2 s . c o m HSSFWorkbook wb = null; try { in = new FileInputStream(name); wb = new HSSFWorkbook(in); } catch (IOException e) { e.printStackTrace(); } System.out.println("!!!!sheet count:" + wb.getNumberOfSheets()); for (int i = 0; i < wb.getNumberOfSheets(); i++) { System.out.println("!!!!sheet[" + i + "]:" + wb.getSheetName(i)); } Sheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next(); Iterator<Cell> cells = row.iterator(); // row.getCell(0).getStringCellValue() switch (row.getRowNum()) { } result += "\n"; } return result; }
From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java
License:Open Source License
@Test public void testMetricsXlsReport() throws Exception { String rrdFilename = TEST_DIR + "queryCount_Counter" + RRD_FILE_EXTENSION; new RrdFileBuilder().rrdFileName(rrdFilename).build(); rrdFilename = TEST_DIR + "queryCount_Gauge" + RRD_FILE_EXTENSION; long endTime = new RrdFileBuilder().rrdFileName(rrdFilename).dsType(DsType.GAUGE).build(); List<String> metricNames = new ArrayList<String>(); metricNames.add("queryCount_Counter"); metricNames.add("queryCount_Gauge"); MetricsRetriever metricsRetriever = new RrdMetricsRetriever(); OutputStream os = metricsRetriever.createXlsReport(metricNames, TEST_DIR, START_TIME, endTime, null); InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray()); assertThat(xls, not(nullValue()));/*from w w w . j a va 2s . c o m*/ HSSFWorkbook wb = new HSSFWorkbook(xls); assertThat(wb.getNumberOfSheets(), equalTo(2)); HSSFSheet sheet = wb.getSheetAt(0); assertThat(sheet, not(nullValue())); verifyWorksheet(sheet, wb.getSheetName(0), 6, true); sheet = wb.getSheetAt(1); assertThat(sheet, not(nullValue())); verifyWorksheet(sheet, wb.getSheetName(1), 6, false); }