Challenge Overview
Project Overview
Detect IT is a web application that is being developed by the World Wildlife Fund that allows comparison of trade data to identify discrepancies in reporting of imports and exports between countries which may be a sign of illegal activity. For example: if Russia reports significantly less exports of king crab to South Korea than South Korea reports in imports of king crab from Russia, this suggests there may be some illegal trade occurring in king crab between the two countries.
This challenge is part of the Living Progress Build Challenge Blitz program.
Competition Task Overview
In this challenge, we'd like to build the real time search function, here are the details:
1. The searching is going to be done against a Vertica database which has been loaded with trade data from the United Nation's UNComtrade database. Here are the credentials for this database:
Host: 54.149.189.221
Port: 5433
Database Name: TradeData
User: user1
Password: pass1
This user account has read-only access to the data. It is the full production data set.
There is a lot of information in the "Record" table. Here are the relevant columns that users will be searching/querying when they initiate a query from the search page: https://github.com/topcoderinc/HP-LP-WWF-Detect-IT/blob/angular-dev/src/client/app/search-data/searchdata.html:
year
tradeFlow
reporter
partner
commodity
netweightKg
tradeValueUSD
The commodities are reported as codes. For example, the code 030247 is "Swordfish (Xiphias gladius)". These can be found in the Commodities table and we also designate there in the source column whether the commodities are "Wild-caught", "Farmed", "Primarily farmed", or "Hybrid".
The reporters and partners are countries. And the ids in the Record.reporter and Record.partner column are foreign keys to the Country table.
2. This challenge involves implementing the following 3 pages in the prototype: http://tc-ui-prototype.herokuapp.com/wwf-detect-it/search-page.html and http://tc-ui-prototype.herokuapp.com/wwf-detect-it/data-comparison-detail-page.html, and the http://tc-ui-prototype.herokuapp.com/wwf-detect-it/query-list-page.html you can also find them in the prototype repo and you'll need to implement these to make sure they work properly with the backend.
3. We need to update a few of the search parameters: Reporting Country should be changed to Importer(s) and Partner Country should be changed to "Exporter(s)". The Reporter Trade Flow parameter can be removed. These parameters should be updated in the filter section of the data-comparison-details-page.html as well.
4. On the search page there are some rules we need to impose to make the graphics readable and tables understandable. We are only going to allow more than one search parameter in one of the three dimensions of Importer, Exporter or Commodity, for example: you can have up to 5 Importers, 1 Exporter, and 1 Commodity OR 1 Importer and 5 Exporters and 1 Commodity OR 1 Importer and 1 Exporter and 5 Commodities. The number limits (5, 1 ,1 ) , (1, 5, 1) or (1, 1, 5) should be configurable. Users will pick out the Importers, Exporters and Commodities in the Search interface.
5. Please implement auto complete for the search parameters for Importer, Exporter and Commodity (like what we had here: http://54.218.19.28/).
6. The search results need to be rendered in a Line Chart using d3.js so we can visualize the results, we also need a table below the chart showing the results in table view. Please note we only need to implement Line Chart in this challenge, the other types of charts are out of scope.
7. We need to implement the Map view using d3.js, for now we only need to color code the countries depending on whether they are importers or exporters.
8. Below the map or line chart you should display a table with the following columns:
Year
Commodity Name
Importer (Country Name)
Importer netweightKg
Importer tradeValueUSD
Exporter (Country Name)
Exporter netweightKg
Exporter tradeValueUSD
Diff in netweightKg
Diff in tradeValueUSD
In order to generate a record in this form you'll need to combine the results of two different rows in the Record table in Vertica: the record of the importing country as reporter with the exporting country as partner AND the record of the exporter country as reporter with the importer country as partner for the same year and the same commodity. The system will need to calculate the difference quantities.
9. We need to impose the rules in the filters from both the search-page and the right side of data-comparison-detail-page, they need to affect results in the line chart, map view as well as the table view.
10. Even though we're allowing search parameters with multiple entries, let's show them individually in the chart. Hopefully, that will be easier to understand.
11. In the line chart, we should show years on the x axis and the y axis should show either netweightKg or tradeValueUSD depending on the selection of the user of the "Display Trade By" parameter. This filter is on the right side of the data comparison page as well.
12. On the search page when we save a query, it should save a name and be associated to the user and saved to a Query List so it can be rerun at a later time. Clicking the "Save Query" button on the search page should save the query to database and then direct the user to the Query List page where the list of saved queries is displayed. Each saved query has a panel with a slightly reduced size picture of the chart generated above. Clicking on the chart in the panel brings up one of the following options:
a. Remove a Saved Query - the trash icon - removes the current query panel from list and deletes the Saved Query from the database.
b. Edit a Saved Query - pencil and paper icon - takes the User to the Search Data page with the search parameters populated. This allows the user to adjust the query if required. If they leave the name the same and click "Save Query" again we should save any changes to the same query record. If the user changes the name, then this should be "Save As" functionality and we'll record a new save query record in the database.
c. Run a Saved Query - which is the eye/view icon - navigates the user to the Data Comparison results page with the saved search parameters just as if a user had clicked the "Run Query" button from the Search Data page.
13. On the query-list page there are a couple of changes that need to made:
a. Please replace the Created by: search parameter at the top of the page with "Importer". All the queries will be created by the current user so have a "Created By" parameter doesn't make sense.
b. Let's add the Commodity,Importer and Exporter parameters to the panels so user can see what the queries are all about. If there is more than one element in a parameter then you can just create a comma delimited list.
c. Let's remove the Created By and Last Update By elements from the panel field list. Those will always be the current user so they are meaningless. Also please remove the "Collaborators" field -- collaboration is being handled through the forums.
14. On the data-comparison-detail-page, the Save function should work exactly like the "Save Query" button on the search-data page. There is a popup in the current interface which asks for the Query Name. This need to be saved along with the current set of search parameters.
15. Do not implement the Sharing/Publishing, Printing, or Download functions on the Data Comparison page.
16. The Key Findings should aggregate the following info:
a. The aggregated total and average weight per year in KG of the Imports over the time period requested.
b. The aggregated total and average value per year in USD of the Imports over the time period requested.
c. The aggregated total and average weight per year in KG of the Exports over the time period requested.
���d. The aggregated total and average value per year in USD of the Exports over the time period requested.
e. The aggregated total of the difference for all the years and the average difference in weight per year between Imports and Exports over the time period requested.
f. The aggregated total of the difference for all the years in trade value and the average difference in trade value in USD per year between Imports and Exports over the time period requested.
Testing
For the backend services, you must provide unit tests and postman file to test the REST services.
For frontend, we've asked our prototype developers to create some basic unit tests for their AngularJS code. Please make sure that you update the front-end unit tests to ensure they are still functional. Any additional unit tests that you think would be helpful for future developers to understand your updates would be appreciated.
Technology Overview
Java 1.5
C# / .NET 4.5+
Visual Studio 2015
ASP.NET MVC Web API
SQL Server Express 2016
REST
Angular.js
CSS
Vertica
D3.js
Final Submission Guidelines
Submission Deliverables
1. Complete projects that cover all the mentioned requirements
2. For the backend, we need a word based deployment guide with details on how to configure and deploy the services to IIS, and details on how to test / verify the REST services.
3. For the frontend, please provide a README in markdown format with details on how to deploy and test the pages.
4. Please keep your frontend and backend code in separate and independent folders, because they will need to be merged into different repos.
Final Submission
- For frontend, please fork the following repo: https://github.com/topcoderinc/HP-LP-WWF-Detect-IT. We are working from the angular-dev branch (which is the default). This contains the existing front-end website code.
- For backend, please fork the following repo: https://github.com/topcoderinc/HP-LP-WWF-Detect-IT-Services. We are working from the master branch (which is the default). This contains the existing backend services code.
- You will need to request access to the repos by providing your GitHub ID in the GitHub Access thread in the forum.
- The winner will be asked to provide a pull request to each of the repos with his or her updates.
- You will still need to submit a submission.zip with your source code, build scripts, and configuration files via the challenge detail page on topcoder.com.
- Check README file to deploy this Angularjs app
- For each member, the final submission should be uploaded via the challenge detail page on topcoder.com.