Mud Log Search UI Services & Integration

According to Wikipedia, “Mud logging is the creation of a detailed record (well log) of a borehole by examining the cuttings of rock brought to the surface by the circulating drilling medium (most commonly drilling mud).”  Our client has a significant set of mud logs and Topcoder has been developing technology to extract structured data from these records.  The documents are very interesting -- they are even oil-well shaped! 
 
In addition, to developing the raw OCR technology to process text on the Mud Log images, Topcoder is developing a user interface to allow geologists to search a set of Mud Logs for relevant information.  This is geological data so location is extremely important -- the core visualizations for this dashboard are maps.  In this case, maps annotated with the locations of the wells and the various Mud Log details.
 
In this challenge, you’ll be implementing and connecting a service layer that integrates our AngularJS web application to a SQL Server Mud Log database.  The application itself is only two basic screens:  a search interface and a filtered display.  You will be transforming our prototype into a working application and creating JSON service endpoints as requirements to hydrate our UI with real data.  Data access is read-only for this application.
 
Here is a brief overview of how the search and display fields map the fields in the Mud Log database.
 
Search
UWI:  [dbo].[IMAGE_OCR].[UWI]  -- Primary Key for accessing data across tables
States:   [dbo].[well_info].[PROVINCE_STATE_NAME]
Basin:   [dbo].[well_info].[BASIN_NAME]
Depth:  Max value of  [dbo].[IMAGE_OCR_DEPTH].[DEPTH] for each well
Vintage:   [dbo].[well_info].[SPUD_DATE]
Phrase Category:  [dbo].[IMAGE_OCR_PHRASE].[OCR_PHRASE_TYPE] -- there can be multiple phrases and phrase category in each well
Phrases:  [dbo].[IMAGE_OCR_PHRASE].[OCR_PHRASE]  -- there can be multiple phrases and phrase category in each well
Phrase Count:  [dbo].[IMAGE_OCR].[PHRASE_COUNT]
Phrase Score:   [dbo].[IMAGE_OCR].[SCORE]
 
Display
Longitude: [dbo].[uwi_lat_long].[LONGITUDE]
Latitude: [dbo].[uwi_lat_long].[LATITUDE]
UWI:  [dbo].[IMAGE_OCR].[UWI]
Well Name:  [dbo].[well_info].[WELL_NAME]
Operator:   [dbo].[well_info].[OPERATOR_NAME]
Drilled Since (change label to “Spud Date”):  [dbo].[well_info].[SPUD_DATE]

SQL Server connection parameters can be found in the Code Document forums.
 
Your completed implementation will involve updates to our front-end application.  The codebase can be found in the Code Document forums.
 
Other requirements:

1. Please implement the feature to process a text file of uploaded UWI numbers.  You assume there will be one UWI per line in the file.
2. The “Export to CSV” button should be replaced by an “Export to Excel” button.  Here are the requirements for the downloaded file:
   1. Worksheet 1 - will have 4 columns: Image Name, UWI, Phrase, Count.  For each image in the filtered results, we’ll count the phrases for each image.  (The pseudo code for the query is “Image Name, UWI, Phrase, Count from IMAGE_OCR_PHRASE group by Image Name, Phrase order by Count Desc). 
   2. Worksheet 2 - will display the raw phrase data from the IMAGE_OCR_PHRASE table for all the filtered rows.  The columns included in the output should be:  IMAGE_URL, IMAGE_NAME, OCR_PHRASE_TYPE, OCR_PHRASE, SCORE, X1, Y1, X2, Y2, ESTIMATED_DEPTH, PHASE_KEY, REF_KEY
3. The lasso functionality has not been implemented yet in the GUI.  This feature is out of scope for this challenge.
4. The actual mud logs for the View Mud Log link are loaded to AWS.  Let’s allow users to download those files locally when View Mud Log links are clicked.
5. The real data set is much larger that the current sample.  Please implement paging in your services and in the filtered results of the GUI.  Let’s also add County and Field to the current list of filters.  These are narrower filters and the values can be provided as dependant picklists based on state or basin.  We have this information for most wells in the well info table.   We may need to limit the maximum number of wells that can be displayed in the filtered results.
6. We’re going to make some adjustments to the filtered results table output.  The columns of this section should be:  Well Name, UWI, Field Name, Vintage, cut, flor, stn, odor, strmg, resd, bleeding, View Mud Log.  Under cut, flor, stn, odor, strmg, resd, bleeding columns you should list number of phrases for each file by the IMAGE_OCR_PHRASE.ALIAS field.  Basically what we’ve done here is combine all the versions of flor and stn into a single spelling.  In addition to this there should be an additional information that show the range of depth values for each ALIAS.   The “Top” value is the first depth value at which a phrase appears.  The “Base” value is the last depth value at which a phrase occurs.  The output should look something like the following:
   

 
Technology
AngularJS (8.2 currently implemented)
Java Springboot 2.1.+
Maven
SQL Server
REST

Final Submission Guidelines

- A single zip containing both the API code and the integrated Web app code (in separate folders)
- Detailed readme in markdown formart explaining how to configure, run and verify your submission
- Maven build script for the API