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. Previous challenges have already established connectivity and record set retrieval from Vertica.
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 data is provided by “Reporters” or countries that send their customs information to the UN on a periodic basis. Both imports and exports (Trade Flows) are provided. The data is broken down by commodity, year, trade flow, and the trading partner with whom the trade is being conducted.
In this challenge, we’re going to implement the “Search Alerts” pages of our Detect IT application. We’re going to build the foundational audit process to identify two basic scenarios using the Trade Data compiled by the UN that originates from country submissions that might indicate that illegal trade is going on. (It should be noted that these flags only indicate possible wrongdoing. There will be false positives, but we’re hoping to provide customs officials, fisheries authorities and investigators and other interested parties direction for avenues for further analysis.)
1. The application should define/record an alert where import weight (netWeightKg) reported by the importer trading partner are 50% higher than the export weight reported by the exporting trading partner for the same commodity in the same year. Here is sample use case:
Imports to the US
select classification, year, tradeFlow, reporter, partner, commodity, netWeightKg, tradeValueUSD from Record where reporter = 842 and partner = 826 and tradeFlow = 1 and commodity = 30211
Exports from the UK
select classification, year, tradeFlow, reporter, partner, commodity, netWeightKg, tradeValueUSD from Record where reporter = 826 and partner = 842 and tradeFlow = 2 and commodity = 30211
The idea is to generate a table of data basically like so:
What we’re really looking for here are instances where the the imports reported are greater than the exports reported like for line 12 shown above. Note that the focus should be on comparing weight, rather than value. Value comparisons can also be useful but even where the weight match, the value are almost certain to differ as values on the export side are ‘free on board’ whereas those on the import side almost always include insurance and freight, and are accordingly higher. Now for Trout -- because this is often “farm-raised” this isn’t such a concern but for fish like Tuna or Swordfish this is more serious. Another issue we’ll need to deal with is missing data. Let’s not report issues for years where the data for one of the trading partners in missing. The system should add a record to the trading flow alert list (#1 below) where the import netWeightKg is 50% or more greater than the reported export weight during the same year and where the Commodity.source field = ‘Wild caught’. Obviously this assumes that the weight figures are available for both the reporting country and the partner. Please make the level (the 50% value) configurable.
2. We want to identify instances where changes in import or export volume for a particular commodity change by 200% or more. Please make the volume percentage change value configurable.
3. The final kind of alert is a condition where both there is both a import/export discrepancy AND a large change in import and export volume.
4. The search alert interface should have autocomplete available for Reporters, Partners, and Commodities.
5. The functionality on the Admin page of the Trading Data Prototype Application (http://54.218.19.28/Admin) should be duplicated onto the Admin screen of the Detect IT application. This page can be found here: https://github.com/topcoderinc/HP-LP-WWF-Detect-IT/tree/angular-dev/src/client/app/admin-profile.
5.a. This page provides connection information for Vertica and sets the global Netweight Level Percentage and Volume Level Percentage (changes in import or export weight). For the alerts, these levels will apply to all users. We’ll be storing the alerts in single table which any user can query using the “Search Alerts” web page.
5.b. The “Load from Vertica” button launches a process which deletes all the current Alerts from the system and reloads them according to the current parameters defined in the fields defined on the admin page. Note that the default values for Countries Codes should be all and in the case of Commodities should be all “Wild caught” commodities.
5.c. The application should provide a warning to the admin user (the only user types which can see the admin profile page) that they are about to delete the existing alerts in the system.
5.d. Please be considerate of your fellow competitors and don’t delete the current of alerts in the prototype app: http://54.218.19.28. It only takes about 15 minutes to regenerate them but it would be best simply to have the data available for all users for the duration of the challenge so that everyone can see the proposed end state for search alerts page and results display.
6. As shown in the prototype Trading Data Comparison application, our interface will include a set of filters and the first page of the alerts results by default. User will enter filter parameters to find the alert results that interest them and then users will be able click “See Details” to view a graphical representation of the trading activity for the particular trading partners and commodity over time. On the results display, we’re only going to show one commodity and trading partner relationship between 2 countries over the time period 2003-2015 for which we have data.
7. You can remove the "Save Query" button from the "Search Alerts" page. We're not going to support the saving of alert queries in the initial release.
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.jsFinal 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.