Topcoder - Create Tool For Aggregating Cost Report Data into Single Table

Background

Currently, the billing costs report (https://www.topcoder.com/direct/dashboardBillingCostReport.action) in Topcoder Direct is very slow to execute.  The main reason is that there are several complex queries to execute which takes times.

We'd like to improve this by aggregating the related cost report data into a single table, so we can revise the billing costs report logic in Topcoder Direct 

Scope

For this challenge, we'd like to you create a tool to export cost report data from a list of queries.

General Requirements
1. Understand the queries (attached in forum) and define a new table that contains every information used (query parameters in WHERE statement) and returned (data in the SELECT statement), so we can easily refine the logic in Topcoder direct to use the same query parameters to do against the new table. but it will be simple and fast. the queries are grouped into two commands with/without inovice number, see following. If needed, you can define two tables for each group

INSERT INTO 'informix'.command(command_id,command_desc,command_group_id) VALUES (32369, 'dashboard_billing_cost_invoice_report_aggregation', 13343);
INSERT INTO 'informix'.command(command_id,command_desc,command_group_id) VALUES (32379, 'dashboard_billing_cost_invoice_report_invoice_number_aggregation', 13343);

INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32866, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32867, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32868, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32869, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32870, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32871, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32876, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32877, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32882, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32947, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32948, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32369, 32949, 0);

INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32872, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32873, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32874, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32875, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32878, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32879, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32880, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32881, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32883, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32950, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32951, 0);
INSERT INTO 'informix'.command_query_xref(command_id,query_id,sort_order) VALUES (32379, 32958, 0);

2. Update the queries by removing the query parameters and the user permission check, this make it avaiable for following execution, please make it work for the following, even it return no data.
2.1 By removing the query parameters, we can get all the cost report data export, then we can query the new table by using these query parameters instead.
2.2 For removing the user permission check, what I mean is to remove the following logic in all queries.
(
             exists (
                     select resource_id from user_permission_grant upg
                     where upg.user_id=DECODE(@uid@,0,upg.user_id,@uid@) and upg.permission_type_id IN (0,1,2,3) and upg.resource_id = p.tc_direct_project_id
                     )

          OR
             exists (
                     SELECT
                            tdp2.project_id as tc_direct_project_id
                     FROM tc_direct_project tdp2
                     INNER JOIN group_associated_direct_projects gadp ON tdp2.project_id = gadp.tc_direct_project_id
                     INNER JOIN customer_group sg ON sg.group_id = gadp.group_id
                     INNER JOIN group_member gm ON gm.group_id = sg.group_id
                     WHERE gm.user_id = DECODE(@uid@, 0, gm.user_id, @uid@)
                     AND   gm.active = 1
                     AND   sg.archived = 0
                     AND   tdp2.project_id = p.tc_direct_project_id
                     )
          OR
              exists (
                      SELECT
                             tdp2.project_id as tc_direct_project_id
                      FROM tc_direct_project tdp2
                      INNER JOIN corporate_oltp:direct_project_account dpa ON tdp2.project_id = dpa.project_id
                      INNER JOIN group_associated_billing_accounts gaba ON gaba.billing_account_id = dpa.billing_account_id
                      INNER JOIN customer_group sg ON sg.group_id = gaba.group_id
                      INNER JOIN group_member gm ON gm.group_id = sg.group_id
                      WHERE gm.user_id = DECODE(@uid@, 0, gm.user_id, @uid@)
                      AND   gm.active = 1
                      AND   sg.archived = 0
                      AND tdp2.project_id = p.tc_direct_project_id
                      )
           OR
           exists (
                   SELECT sg.group_id
                   FROM customer_group sg
                   INNER JOIN group_member gm ON gm.group_id = sg.group_id
                   WHERE gm.user_id = DECODE(@uid@, 0, gm.user_id, @uid@)
                   AND   gm.active = 1
                   AND   sg.archived = 0
                   AND   sg.auto_grant = 1
                   AND   sg.client_id = ttc.client_id
                   )          
          )
3. Create A General Export Tool which
3.1 Execute the queries in files, and save the data in CSV files separately.
3.2 The initial queries to execute will be the updated queries above, but the informix database is lacking of these data, so for testing,  you just need to demonstration by using queries that can query and export data from multiple database, like tcs_catalog, common_oltp etc.
3.3 Previously, we have a informix export tool which exports one table, you can use that as your codebase or create completely new source code.
3.4 the tool should be ready configuration from file, the configuration should contains informix database configuration, queries to execute, the export directory, the csv separator etc, making the tool easy to use.
3.5 the tool should be built as  a single executable jar file, and it can ready the configuration file in a parameter.

About Informix Database
To run the docker informix instance, I would suggest to use

`docker run -it -p 2020:2021 appiriodevops/informix:1.2`

Winner Responsibility
we will carry out the export against our dev database after completion of this challenge to see if it works properly as we expect, if not, winner will be responsible to troubleshoot and make changes.
 

Final Submission Guidelines

1. Source Code for the export tool
2. schema to create the new tables
3. Updated queries related to cost report
4. README.md file about how to use the tool
5. test data and test queries to demonstrate the tool can be used properly.