Topcoder Direct - Update Competition Costs Report To Use Aggregated Table in Redshift

For this challenge, we'd like to implement another query function for Competition Cost Report.

1. Create a new aggregation table in redshift similar like table definition informix as below

create table competition_costs
(
    payment_date    datetime year to fraction(3),
    contest_id    int,
    client    varchar,
    billing_project_name    varchar,
    direct_project_name    varchar,
    launch_date    datetime year to fraction(3),
    completion_date    datetime year to fraction(3),
    category    varchar,
    contest_name    varchar,
    contest_status    varchar,
    actual_total_member_costs    decimal,
    payment_type_desc    varchar,
    line_item_category    char,
    reference_id    varchar,
    line_item_amount    decimal,
    client_id    int,
    billing_project_id    int,
    direct_project_id    int,
    project_category_id    int,
    payment_detail_id    int,
    is_studio    int,
    payment_id    int,
    payment_desc    varchar,
    invoice_amount    decimal,
    processed    boolean,
    process_date    datetime year to fraction(3),
    invoice_number    varchar,
    invoice_id    decimal,
    invoice_record_id    decimal
)

this is going to load exported data from informix.
2. Add a similar method as https://github.com/appirio-tech/direct-app/blob/dev/src/java/main/com/topcoder/direct/services/view/util/DataProvider.java#L5358-L5583

but the data will be retrieved from redshift with the passed parameters

you can refer http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-in-code.html about how to connect to redshift.

3. Update https://github.com/appirio-tech/direct-app/blob/dev/src/java/main/com/topcoder/direct/services/view/action/report/DashboardBillingCostReportAction.java#L306-310

To call the new method.

4. Create some test data and verify the Competition Cost report is working properly.

5.  The redshift connection required configuration should be tokenized in order to support different environment. 

Redshift

For this challenge, you are expected to use redshift, there are free plan if you are new to AWS.

If you have any problem with using redshift, please request in forum, so I can help resolve it.
 

Final Submission Guidelines

1. Code changes.
2. Test Data
3. Setup and Verification Steps
4. Winner will be responsible to create PR for merge.