WAME WebAPI - Implement Scope Module REST APIs

Challenge Objectives:

This challenge is the initial challenge of this project, and it will create the WebAPIs for the scope validation rules screens. 

Project Background

Migrating an existing Workload Modernization Engine  ( WAME),  from "Microsoft Access based DB application" to " .Net Core Web Application". WAME web application is design to handle multiple tenants, with each tenant has dedicated DB and Master DB to resolve(identify) tenant. This online application uses ANGULAR as UI and WEB API as middleware and backend DB as SQLServer 2014. Data import and export to WAME will be done using excel files.

To enable the migration, we need to develop the required "Web API(s)" for the decision engine in series of challenges.  The Web API(s) are act as a middleware for data access. Since Web API gets data from/to excel, middleware should be able to handle bulk data read/write with optimized performance. Approximate number of records in excel is about 100,000. File size may be about 1-3MB.

There are 4 major types of Web API(s)

1. Functional API(s):
All these APIs has functionality like:

• Upload (Import) Excel (WRITE data) 

  • After uploading file(s) from UI, API has to receive binary and create VM/DTO(list). Validate VM objects and create/update data in table.

• Export Excel (READ data).

  • Reads data from Table to DTO and create excel file to download.

2. Error File Web API:

• Provide an option to download the error file(s).

3. Static File/Data API(s):

• Provide an option  to download static file or read only data.

4 .Status API:

• For any Async process need an API to update status in UI.

Technology Stack

• ASP.NET Core (2.1) framework
• C# Development language
• ORM (EF) for SQL
• Other relevant dot net core packages (but not limited to) - Mapper, Logs, etc.
• SQL Server 2014 or above
• xUnit
• EPPlus to read or write Excel: https://github.com/JanKallman/EPPlus
• SwashBuckle to generate Swagger document:  https://github.com/domaindrivendev/Swashbuckle
• FluentValidation: https://github.com/JeremySkinner/FluentValidation
• AutoMapper: https://github.com/AutoMapper/AutoMapper

Challenge Requirements

Your submission should follow the structure of provided WameDEAPI project, which provides the sample application with Layers, Domain Models, Logging(log4net), Basic Excel read/write(EPPlus), DI frame work for services, Infrastructure components, and Mapper. 
We are using the opprotunityID in the header to identify the tenant. The app will use the opporitunity id to get the tenant configuration and then manage the data in the tenant database. The provided dbTenant.bak file is a backup of the tenant database.



And only the APIs described in the DE_SCOPE.docx document are in scope. You need to implement the following APIs. 

1. Export Validation Rule: GET /scope/exportValidationRules

• This api will select data from the DE_Scope_Validation db table, and export them as an Excel file to download. 

2. Import Scope Validation Rules. POST  /scope/importValidationRules - the request must be a multi-part request. 

• This api will sync data from the uploaded Excel file into the DE_Scope_Validation table
• You need to validate each record, and log the errors in the Log_ImportRulesErrors table. Please trucate the data in Log_ImportRulesErrors table first. 
• For each record, you may need to delete, insert or update the corresponding data in the DE_Scope_Validation table

3. Run the Scope Determination Rule Set: POST  /scope/runDeterminationRules

• It will first execute some sql statements to reset the state data in the database tables. 
• Then it will load the rules from the DE_Scope_Validation table. 
• For each rule, it will construct a dynamic SQL to select the records from one of the CTDB_Location_CMO, CTDB_SERVER_CMO, CTDB_App_CMO, CTDB_DB_CMO and CTDB_ADHOC_CMO tables (with necessary joins).  
• For each retrieved CTDB_*_CMO table record, it needs to set its InScopeCalc='N' and InScopeCalcReason = the-reason-from-rule and save back to the database, and then sync the InScopeCalc and InScopeCalcReason field values to the CTDB_reliability_p table. 

4. View Errors: GET /scope/{type}/errors - type can be importValidationRules, runValidationRules or importOverrides 

• importValidationRules errors - select the error records from Log_ImportRulesErrors table, and export them as an Excel file to download
• runValidationRules  - it seems there is no error table defined for this. 
• importOverrides - select error records from Log_OverrideImport table, and export them as an Excel file to download

5. Export Scope Review Form: GET /scope/exportReviewForm - with entity=xx parameter, there can be multiple (or none for all). 

• The entity can be: Location, Server, App, A2SM, DB, Adhoc, and their corresponding tables are CTDB_Location_CMO, CTDB_SERVER_CMO, CTDB_App_CMO, CTDB_A2SM_CMO, CTDB_DB_CMO and CTDB_ADHOC_CMO. 
• If an entity is provided in the request, the api should perform the query against the corresponding table, and create a worksheet in the Excel file to export. The query is already well documented in the DE_SCOPE.docx document. 
• If no entities are provided in the request, it's handled like all entities are provided. 

6. Import Scope Overrides: POST /scope/importOverrides - the request will contain an array of entities (or none for all).  It should be a multi-part request. 

• The file to be imported has the same format as the file exported from api-5 above. 
• The entity can be: Location, Server, App, A2SM, DB, Adhoc as above. You need to ensure the uploaded Excel document has the worksheets corresponding to the provided entities. And only the worksheets of the provided enitites need to be processed, all others will be ignored. If no entities are provided, all worksheets in the uploaded Excel document will be processed. 
• For each record in the worksheet, you should perform the operations as described in the DE_SCOPE.docx document. Note that the data should be updated in the corresponding CTDB_*_CMO table with updated InScopeOverride and InScopeOverrideReason field-values. And you should also sync the updated InScopeOverride and InScopeOverrideReason field-values in the CTDB_reliability_p table. 

 

xUnit tests are required. 

General Requirements:

• Data Modeling

  • This project follows DB first approach. Backup for Master DB and Tenant DB will be provided for Development.
  • Minor Changes of Creating Index, Primary Key has to include in the scope. Use Dotnet Core Entity Framework (EF6 or above) for DB objects read and write.

• Caching

  • Required Data to be Cached (in memory).

• Table Splitting

  • DTO/VM should have only required fields to reduce memory usage

• Shadow Property

  • Shadow properties has to be created because CMO or FMO tables has no Foreign key Relation.

• Auto mapper Property

  • To map Domain and VM/DTO objects.

• Naming conventions variable declaration

  • Use Pascal Casing for class names and method names.
  • Don't declare same variable names in multiple locations.
  • Avoid using Abbreviations as much as possible.
  • Use PascalCasing for abbreviations.
  • Organize namespaces with a clearly defined structure. ex: WAMEAPI.DE.SCOPE.

• Exception

  • Use log4net to catch/log exception to file. Already implemented in Api solution same can be reused.
  • Use properly try, catch blocks. Log all errors

• Clear Coding and Easy Read

  • Follow SOLID principles. Segregate Interfaces. Inject Requires services(DI).
  • No function/method should be more than 20-30 lines.

• Configuration

  • Declare enum, avoid explicitly specify type. Ex: Reliability (000,001,003,004,005).
  • Use config(json) and avoid hard coding of values. Framework provides reading of appconfig data from json file.
  • Develop Deployment  config for different environments (DEV, TEST, UAT).

• Memory

  • Check memory while running app. avoid memory leaks. Verify with Yourkit or any other tool for memory leaks.

• Threads

  •  All threads should join in main thread. should not left unattained before getting response..

• Performance

  • Web api has to complete task provide response within 30-40 sec to avoid time out error.

• Design Pattern

  • Recommended to use Factory/Abstract pattern with easy to reuse and enhance and clear code separation.
  • Detailed investigation required to design Factory/Abstract pattern. 

 

 

 

Final Submission Guidelines

- Source code and high level flow document
- Deployment Guide and Verification Guide
- Postman file including test cases
- Sample test data if necessary