Challenge Overview
Project Overview
We are building a web based application that allows Construction Project Managers to track tasks and the number of hours spent on each task, their costs, materials and equipment.
Currently, they are using a legacy Powerbuilder application along with emails and spreadsheets to track their progress. With this project, we are seeking to modernize this by building a HTML5 based application.
In this challenge you will create the necessary code to allow the user to import Excel spreadsheets into the application and "wire" that functionality to the Upload Data buttons. There are 3 areas for importing and three different type of Excel spreadsheets (one for each area). Example spreadsheets are provided.
Details
The imports will occur during the Schedule Creation area of the application ("Step 2" through "Step 4". For each step (steps 2 - 4), the user will click the Upload Data button and will receive a prompt to choose an Excel file from their computer. The contents of this file will then be used to populate the input fields on the screen. This is intended as an alternative method to manually editing the fields (which is already functional). All data on the screen should be erased and replaced with the imported data.
Tasks, Hierarchy, Hours (Step 2)
Use the data in the example "Step_2_Import.xlsm" file to populate the data on this screen.
Description of Step 2 Excel Import Worksheet
-
The first four rows of the spreadsheet can be ignored for the purpose of this import, the relevant data will start on row 5
-
Column A contains the task "Level" information (see notes below) and will map to the "Default WBS" column on the webpage. It will also determine the indent level for the task
-
Column B will map to the "Task Description" column on the webpage
-
Column C will map to the "Baseline Hours" column on the webpage
-
If there is no value in this column for a task, this means that field will be auto-calculated on the webpage (code to do this is already there)
-
-
Column D can be ignored for the purpose of this import
-
Column E onwards contains the hours spent in each week, this will map to the "Base line Weekly Hours" area of the webpage
-
Please note that this area is paginated and the import will need to assign values for all weeks present in the import spreadsheet
-
Column E represents "WK 1", column F represents "WK 2", and so forth
-
This area of the spreadsheet contains a variable number of "weeks" it may contain anywhere from 1 to 100 weeks of data, please account for this in your solution
-
Notes on Task Levels
Each task can can contain subtasks, there are 6 possible levels of subtasks. All "level 1" tasks have a WBS number of "X", all "level 2" tasks have a WBS number of "X.X", "level 3" tasks have a WBS number of "X.X.X" and so forth. If a tasks has a subtask of its own, it's "Hour's" fields are automatically calculated to be the sum of it's child tasks. You can see how this works in the included codebase (manual editing of these tasks is functional). Please ask for any clarification on this if needed.
Materials (Step 3)
Use the data in the example "Step_3_Import.xlsm" file to populate the data on this screen.
Description of Step 3 Excel Import Worksheet
-
The first row of the spreadsheet can be ignored for the purpose of this import, the relevant data will start on row 2
-
Columns A - C can be ignored for the purpose of this import
-
Column D will map to the "Material Description" column on the webpage
-
Column E will map to the "Orig. Total QTY" column on the webpage
-
Column F will map to the "Unit" column on the webpage (note this is a selection box, assume the entry in the spreadsheet will be one of the available choices - but allow for future choices to be added to the application)
Step 3 Additional Notes
There are two types of material entries - "headers" and actual materials. All actual material entries are indented beneath their corresponding headers. The "Orig. Total QTY" field for headers is automatically calculated by the UI as the sum of all tasks under it. In the import spreadsheet, you can determine which rows are "header rows" because they have no values in Columns E and F. After the import, this should be reflected correctly on the page - all "header" items should not be indented and all "material" items should be indented under their corresponding header. Please ask for an needed clarifications on this in the forums.
Equipment (Step 4)
Use the data in the example "Step_4_Import.xlsm" file to populate the data on this screen.
Description of Step 4 Excel Import Worksheet
-
The first two rows of the spreadsheet can be ignored for the purpose of this import, the relevant data will start on row 3
-
Column A will map to the "Tag #" column on the webpage
-
Column B will map to the "Equipment Description" column on the webpage
-
Column C will map to the "Quantity" column on the webpage
-
Column D will map to the "Promise Date" column on the webpage
Resources Provided
-
Example Excel workbooks for import, three workbooks - one for each step
-
The codebase for the application
Environment Setup
The recommended environment for this challenge is Windows 7 or above with .NET 4.5.2. Windows is needed becase the code base makes use of edge.js to wrap some .NET database calls. Several other configurations will work as well, but if you don't already have an environment, this will probably be the fastest setup. It is recommended that you also setup the test database for full functionality. This can be done by installing SQL Server 2012 Express (or 2008) and restoring the databse backup from test_files/db.zip located in the code base. Please see docs/Deployment_Guide.doc for further details and ask any questions on this in the forums.
General Notes
-
Third party libraries are permitted as long as they are free for enterprise use (specifically an Excel parsing library is recommended)
-
For all three import spreadsheets, the number of data rows in the spreadsheet is variable, the example spreadsheet contains sample data, however in production the imported spreadsheet may be much longer - please make sure your solution accounts for this variability
-
In each of the three areas, once the data is imported - the user should then have the ability to edit the data (e.g. the application should return to the state it was just prior to the import, but with the newly acquired data)
Final Submission Guidelines
Submission Deliverables
-
Fully functional import functionality for all three "steps"
-
All example workbooks must import successfully
-
Import capability must be "wired" correctly to the Upload Data buttons in each step
Code Aesthetics
-
Code must be clean (no non-applicable comments, unused variables etc), tidy (consistent indents)
- Clear and purposeful naming for variables, classes etc.