Report Framework

Objective:

The objective of egov-report service is listed as below.

1. To provide a common framework for generating reports

2. To provide functionality to create new adhoc reports with minimal efforts

3. Avoid writing code again in case of new report requirements

4. Makes possible to create reports with only knowledge of SQL and JSONPath 

Requirements:

1. Prior Knowledge of Java/J2EE

2. Prior Knowledge of SpringBoot

3. Advanced Knowledge of PostgresSQL

4. Prior Knowledge of REST APIs and related concepts like path parameters, headers, JSON etc

5. JSONPath for filtering required data from json objects

Setup, Definitions & Functionality:

Setup:

1. Step 1: Write configuration as per your requirement. Structure of the config file is explained later in the same doc.

2. Step 2: Check-in the config file to a remote location preferably github, currently we check the files into this folder - https://github.com/egovernments/egov-services/tree/master/docs/rainmaker/{module-name}/reports for dev and QA and this folder - https://github.com/egovernments/punjab-rainmaker-customization/tree/master/configs/reports/configs for UAT.

3. Add module name and corresponding report path in same format as used by us in egov-services/docs/reportinfra/report/reportFileLocationsv1.txt at master · egovernments/egov-services and checkin that file to remote location.

4. Step 3: Provide the absolute path of the file mentioned in Point 3  to DevOps, to add it to the file-read path of report service. The file will be added to  environment manifest file for it to be read at start-up of the application.

5. Step 4: Run the report service app, use the module-name as path parameters in the URL of the requests for report service with required request body

Definitions:

1. Config file

   • A YAML (xyz.yml) file which contains configuration for report requirements. 

2. API

   • A REST endpoint to fetch data based on the configuration.

3. Inline-table

   • If we also want to show data from some external service with data coming from DB  in reports we use inline-tables. The data from external service is stored in inline-table and then used as any normal DB table to get data. This table is short lived and stays only for the time when query is being executed. It is never stored in DB. We provide JSON paths in ordered manner corresponding to each column in the table. These JSON paths will be used to extract required data from external service’s response. For configs please see ‘How to Use’ section. 

Functionality:

1. Provides an easy way to add reports on the fly just by adding configurations without any coding effort

2. Provides flexibility to customize result column names in the config

3. Provides flexibility to fetch data from DB and also from some other service returning required json objects when its not possible to get all required data from DB

4. Provides functionality to add filters as per requirements before actually fetching data for report

Feature List:

1. Get data directly from DB or from other service 

2. Supports adding of filters for filtering data

Impact:

1. Used by PGR for all of its reports.

2. Used by PT for all of its reports.

3. Used by TL for all of its reports.

Impacted By:

1. Changes in the version of PostgreSQL.

2. Changes in the format of response from external service if used

How to Use:

1. Configuration: As mentioned above, report service uses a config file per module to store all the configurations of reports pertaining to that module. Report service reads multiple such files at start-up to support reports of all the configured modules. The file contains the following keys:

   1.     reportName: name of report, to be used with module name to identify any    

    report config

1.     summary: summary of report

2.     version: version of report

3.     moduleName: name of module which the report belongs to

4.     externalService: To be used when some of report data needs to be fetched from external service through inline-tables. It contains following fields

   1. entity: JSON Path to filter json arrays(result to be turned into tables) from returned json 

   2. apiURL: api URL of the external service

   3. keyOrder: order of JSON object keys to form table columns from JSON  object arrays

   4. tableName: name to be given to represent this transformed data which will be used as table in the SQL query

5.     sourceColumns

    These represent the final data sent by service on GET_DATA api call. Order of 

    sourceColumns in the Config is same as that of columns in the result.  Each 

    sourceColumns  represent one column in the result. For each column data is   

    picked after executing final SQL query formed after appending groupby,  

    orderby, search params into base query

1. name: name of column to fetch data from query results, must be there in query results

2. label: custom column label

3. type: data type of column

4. source: module name

5. total: whether column total required on front end
   
   

6.     searchParams:

   1. name: name of search param. Must match variable used in search clause

   2. label: custom label for viewing on front end

   3. type: type of search params. If type is ‘singlevaluelist’ then  use pattern to populate searchparams possible values to select from by the user

Ex:-number,string,singlevaluelist etc

1. source: module name

2. isMandatory: If user must fill this searchparam before requesting report data

3. searchClause: SQL search clause for corresponding search params to filter results, to be appended in base query

Ex:- AND fnoc.tenantId IN ($ulb)

       Here $ulb will be replaced by user inputs

1. Pattern: This field will be used only when ‘type’ is set to ‘singlevaluelist’. It is external service URL combined with JSON Paths separated by ‘|’. The first JSON path is for codes and second for values. Values will be shown to the user in drop down. And codes corresponding to user selected value will be sent to the report service and will be used in searchClauses mentioned in last point.

Ex:-http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=tenant&masterName=tenants|$.MdmsRes.tenant.tenants.*.code|$.MdmsRes.tenant.tenants.*.name

1. Query: Main/base query clause for fetching report data from DB and custom tables formed after fetching data from external service 

2. Orderby: order by clause to be appended into base query

3. Groupby: group by clause to be appended into base query

4. additionalConfig: to provide additional custom configs which are not present above

Reference - 

https://raw.githubusercontent.com/egovernments/punjab-rainmaker-customization/master/configs/reports/configs/pt-reports.yml

1. API call: 

There are two API calls to report service ‘GET METADATA’ and ‘GET DATA’. 

GET METADATA:

This request to report service is made to get metadata for any report. The metadata contains information about search filters to be used in the report before actually sending request to get actual data. The user selected values are then used in GET_DATA request to filter data.

endpoint: /report/{moduleName}/metadata/_get

moduleName:- It is used to define the names of module which contains current report

Body: Body consists the following:

1. RequestInfo: Header details as used on the egov platform

2. tenantId: tenantId of ULB

3. reportName: name of the report to be used

Instance: 

URL: https://egov-micro-dev.egovernments.org/report/rainmaker-tl/metadata/_get

Body:

{ "tenantId": "pb.amritsar", "reportName": "TradeWiseCollectionReport", "RequestInfo": { "apiId": "emp", "ver": "1.0", "ts": 1558889531536, "action": "create", "did": "1", "key": "abcdkey", "msgId": "20170310130900", "requesterId": "", "authToken": "c94fad3f-8691-489d-8838-25b2bfc714fd" } }

GET DATA:

This request to report service is used to get data for the report. Inputs given by user for filters are sent in request body. These filters values are used while querying data from DB.

endpoint: report/{moduleName}/_get

moduleName: It is used to define the names of module which contains current repo

Body: Body consists the following:

1. RequestInfo: Header details as used on the egov platform

2. tenantId: tenantId of ULB

3. reportName: name of the report to be used

4. Array of searchparams corresponding to each of filled filters by the user. Each searchparam contains:-

   1. Name: name of filter

   2. Input: user selected value 

Instance: 

URL: https://egov-micro-dev.egovernments.org/report/rainmaker-tl/_get

Body:

{ "RequestInfo": { "apiId": "emp", "ver": "1.0", "ts": 1558888480562, "action": "create", "did": "1", "key": "abcdkey", "msgId": "20170310130900", "requesterId": "", "authToken": "c94fad3f-8691-489d-8838-25b2bfc714fd" }, "tenantId": "pb.amritsar", "reportName": "TradeLicenseRegistryReport", "searchParams": [ { "name": "fromDate", "input": 1556649000000 } ] }

Interaction Diagram: