Release Assembly - Benchmarking and Reporting Tool Rewrite Release 1 Assembly 1

Java
Register
Submit a solution
The challenge is finished.

Challenge Overview

Project Overview

The entire system is a tool suite that

  • Collects data as answers to questions (grouped into surveys),
  • Evaluates metrics for respondents based on submitted data/answers,
  • Categorizes respondents into peer groups based on submitted data or computed metric values,
  • Computes statistical data and  
  • Generates various reports (in MS Excel and PowerPoint).

The main component is a Visual Basic 6 application that controls the data processing and reporting part. It also provides direct data import from MS Excel files into DB2.

The scope of this project is to re-implement this data processing and reporting (DPR) component using Java based technologies.

This assembly will fix some bugs found during testing.

Competition Task Overview

This round of fixes focus on Import and compute respondent data from Excel (bypassing survey applications), this seems a long list but many of the issues are trivial:

  1. No status report was created when import failed when question did not exist in the database.
  2. Data source filter by name is not working. Shall allow for wild cards (see requirements): * and ?.
  3. Data source filter by Id displays: Invalid field value for field "dataSourceFilter.dataSourceId" when I enter 990? . Message refers to source code (too technical).  Are error messages externalized, so they can be easily changed ?
  4. Cancel filter button does not clear the value that produced the error (I input 990?, clicked Apply, then Cancel filter).
  5. Finish button gets enabled even if there is no file specified. Better if gets enable iif both the file was defined and datasource was selected.
  6. Error message reads 'The file contents were empty.' when clicking Finish and no file has been selected.  Are error messages externalized?
  7. Respondent data import is not transactional. Got a file that failed (bcs question was not in the database), but one SPI, SITE and COMPANY instance has been already created, see BRS 197: (197 - Priority: essential) If the import fails, then the entire import should be rolled back.
  8. BER_ANSWERS.VALUE is a varchar and shall match exactly the value in Excel; seems that there is a conversion to a decimal format (this issue is present in other places and reported below).  
  9. BER_ANSWERS.NUMERICVALUE is not computed – this one shall be the number (double) value obtained from converting the VALUE field.. These are the queries current system uses:
    • UPDATE BER_ANSWERS SET NUMERICVALUE = DOUBLE(VALUE) WHERE (NOT VALUE IS NULL) AND SPI_ID = ? AND QUESTION_ID IN (SELECT QUESTION_ID FROM QUESTIONS WHERE QUESTION_TYPE_ID IN (0, 1, 2))
    • UPDATE BER_ANSWERS SET NUMERICVALUE = CAST(DATE(VALUE) as BIGINT) WHERE NOT VALUE IS NULL AND SPI_ID = ? AND QUESTION_ID IN (SELECT QUESTION_ID FROM QUESTIONS WHERE QUESTION_TYPE_ID = 6)
    • We suggest to perform the updates once at the end for all imported SPIs (based on the data source). Also, we suggest BER_ANSWERS.TEMP_VALUE to be populated with the data source id.
  10. BER_ANSWER_OPTIONS is not populated correctly: BER_ANSWER_OPTIONS.Option_Id is populated with QUESTION_OPTIONS.VALUE and should be populated with QUESTION_OPTIONS.OPTION_ID. BER_ANSWER_OPTIONS.VALUE is not populated and shall be populated with QUESTION_OPTIONS.VALUE (which shall be the same as the <option_value> in the column header <question_id>-<option_value>).  BER_ANSWER_OPTIONS.TEMP_VALUE could be populated with the data source id.
  11. Incomplete handling of answers to multiple choice questions. Answers to multiple choice questions must be stored in both BER_ANSWERS and BER_ANSWER_OPTIONS tables based on the question type.
    • If type = 3 (checkbox) then for each option selected as answer there will be a record in the table BER_ANSWER_OPTIONS. There also must be a single record in the BER_ANSWERS table (to indicate that user has been answered the question). The BER_ANSWERS.VALUE shall be 3 or 'Checkbox',  BER_ANSWERS.NUMERICVALUE shall be left NULL.
    • If type = 4 (radio button) then the above rules apply pretty much the same, except that the record in BER_ANSWERS will have the VALUE = the value in  the Excel file and same as BER_ANSWER_OPTIONS.VALUE. BER_ANSWERS .NUMERICVALUE will be the same, only converted to a double (see the column types). Please note that there will be only one record in either tables, this is because if the question allows only one selected option as answer (being a radio button type of question).  ?
  12. If column G contains only a single process ID, import fails, but should not (should allow a single process).
  13. Validation is not complete – stops after first error. The content must be always validated completely in order to detect all the errors in the input file.
  14. Validation report does not provide links to offending cells. I have a text value as answer to a numeric question, and getting only this error message: Error when parse number in cell. Is it possible to configure these error messages (are they externalized)?
  15. Successful import does not produce status report (see transactions 77, 87, 88)?
  16. Valid file fails import (http://lexdcy1029.cloud.dst.ibm.com:9080/brt-web/statusReport?transactionId=83) with error message “Cannot get a numeric value from a text cell”. Again, there is no indication of which cell produces the error.
  17. Valid files failed validation and there is no error reported (http://lexdcy1029.cloud.dst.ibm.com:9080/brt-web/statusReport?transactionId=84, http://lexdcy1029.cloud.dst.ibm.com:9080/brt-web/statusReport?transactionId=85)
  18. If 'Entry Date' (column L) is missing, then file seems to fail validation. This is incorrect: current timestamp shall be used in tracked processes.
  19. Missing answers in the database (not all of the answers from the Excel file have been imported): transaction 87, Select * from DB2INST1.BER_ANSWERS where SPI_ID = 10000036.
  20. Import does not  handle answers to text and date type of questions (transaction 88)?
  21. Inconsistent messages: “Cannot get a numeric value from a text cell” and “Error when parse number in cell”   for same error: non-numeric value as answer to a numeric question.

Make sure the unit tests are still valid after this contest.

Technology Overview

The working environment requirement details for this application are outlined in the architecture. An overview of the environment requirements are listed below:

  • SUSE Linux Enterprise Server 10
  • WebSphere Application Server 8
  • DB2 9.5
  • Java 1.6
  • J2EE 1.6
  • EJB 3.1
  • JDBC 4.0
  • Spring 3.1
  • Struts 2.2.3
  • jQuery 1.7.2

Documentation Provided

Documents can be downloaded in contest forum.



Final Submission Guidelines

Submission Deliverables

A complete list of deliverables can be viewed in the TopCoder Assembly competition Tutorial at: http://apps.topcoder.com/wiki/display/tc/Assembly+Competition+Tutorial 

Final Submission

For each member, the final submission should be uploaded to the Online Review Tool.

Review style

Final Review

Community Review Board

Approval

User Sign-Off

Challenge links

ID: 30034282