•Standard Schema Database Functions
•Geotech Schema Database Functions
•Custom Functions in EnviroInsite
Standard Schema Database Functions
EQuIS EnviroInsite calls the following standard database functions.
•EQUIS.UNIT_CONVERSION
•EQUIS.TO_NUMBER
•EQUIS.MEASURE_DATUM
Geotech Schema Database Functions
EnviroInsite templates support the plotting of database functions in Boring Logs or Subsurface Profiles. EQuIS Geotech includes EnviroInsite database functions installed with the Geotech Schema (schema.geotech.xme) that work with EQuIS Geotech EnviroInsite templates to plot soil boring logs, Cone Penetration Test (CPT logs), Dilatometer Test (DMT) logs, well construction logs, and subsurface profiles. See the following EQuIS Geotech articles for details:
•Downhole data functions
•Header/footer data functions (scalar functions)
•Header/footer graphic functions
Custom Functions in EnviroInsite
EnviroInsite templates support the plotting of database functions in Boring Logs or Subsurface Profiles. Functions with names starting with “ei_” will be available in the Functions tab in the right pane of the Template Design window.
General Requirements for all EnviroInsite Custom Functions
•Custom functions must adhere to the EQuIS Database Modification Compliance Requirements.
•Custom functions must be added to custom Schemas, rather than the standard ‘dbo’ or ‘equis’ schemas.
•Unless instructed by EarthSoft, never overwrite or change a standard database function.
For more information, see Add Custom Database Objects.
Data from SQL scalar functions may be plotted as header/footer text if configured with the following requirements:
•The function meets the general requirements for all EnviroInsite custom functions.
•The function name begins with "ei_".
•The inputs to the function must be the FACILITY_ID followed by the SYS_LOC_CODE.
•Optional inputs for TopDepthRange (real) and BottomDepthRange (real) can also be included to specify a depth range to output specific text on each page of the boring log (see example below).
•The output of the function should return a formatted text string.
The code below is a simplified example of a compatible custom database function created in SQL Server Management Studio.
CREATE FUNCTION cust_schema.ei_depth_notes_custom ( @facility_id INT , @sys_loc_code VARCHAR(20) , @topdepthrange REAL , @bottomdepthrange REAL ) RETURNS VARCHAR(255) BEGIN
DECLARE @result VARCHAR(255);
SELECT @result = STRING_AGG(CONVERT(NVARCHAR(MAX), n.note_text), CHAR(10)) FROM dt_note AS n INNER JOIN dt_location l ON l.euid = n.db_record_id AND n.note_type = 'DEPTH_REMARK' WHERE l.facility_id = @facility_id AND l.sys_loc_code = @sys_loc_code AND (n.value_1 IS NULL OR (n.value_1 BETWEEN @topdepthrange AND @bottomdepthrange))
RETURN @result;
END; GO
GRANT EXECUTE ON cust_schema.ei_depth_notes_custom TO es_public; GO |
Downhole Function Documentation
Data from SQL table-valued functions may be plotted as boring log columns if configured with the following requirements:
•The function meets the general requirements for all EnviroInsite custom functions.
•The function name must begin with "ei_".
•The inputs to the function must be the FACILITY_ID followed by the SYS_LOC_CODE.
•The output of the function must return columns named start_depth, end_depth, depth_unit, column_header, and column_value.
The column_value contains the data to be plotted. If column_header is populated, it will replace the Title Text of the column (if enabled). The start_depth and end_depth columns represent the depth intervals for each column_value. The depth_unit is used for English/metric conversion if applied by the template.
The code below is a simplified example of a compatible custom database function. The function returns blow counts for a location, separated by dashes.
CREATE FUNCTION cust_schema.ei_interval_blowcounts_custom ( @facility_id INT ,@sys_loc_code VARCHAR(20) ) RETURNS @results TABLE
BEGIN ( start_depth FLOAT ,end_depth FLOAT ,depth_unit VARCHAR(10) ,column_header VARCHAR(100) ,column_value VARCHAR(100) ) AS BEGIN
INSERT INTO @results
SELECT ROUND(start_depth, 1) , ROUND(end_depth, 1) , depth_unit , 'Blow Counts' , CONCAT( main_blows_1 ,CASE WHEN LEN(main_blows_2) > 0 THEN CONCAT('-',main_blows_2) ELSE '' END , CASE WHEN LEN(main_blows_3) > 0 THEN CONCAT('-',main_blows_3) ELSE '' END , CASE WHEN LEN(main_blows_4) > 0 THEN CONCAT('-',main_blows_4) ELSE '' END ) n FROM dt_geo_standard_penetration WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code
RETURN END; GO
GRANT EXECUTE ON cust_schema.ei_interval_blowcounts_custom TO es_public; GO |
Formatting Text in Downhole Function Data
Parts of the text returned from the function data may be displayed as bold or underlined. This can be accomplished using the following syntax (HTML tags):
Bold – <B>Some text</B>
Underline – <UL>Some text</UL>
The following is an example of bold and underlined text in a boring log using the above syntax.
Downhole Graphic Documentation
To properly configure a downhole function that returns a graphic, the function must follow these requirements:
•The function meets the general requirements for all EnviroInsite custom functions.
•The function name must begin with "ei_"
•The inputs to the function must be the FACILITY_ID followed by the SYS_LOC_CODE.
•The output of the function must return columns named start_depth, end_depth, depth_unit, column_header, and column_value.
•An optional output column named column_color may also be returned. This is an integer value representing the RBG color to be used as the background color for the log interval.
•The column_value of type varbinary(max) is the graphic that will be displayed on the log and should point to a Binary Large Object (BLOB)-storing field such as DT_FILE.CONTENT.
•The graphic file type must be in the .DXF format.
The code below is a simplified example of a custom database function. In this example, the function returns a graphic associated with the DT_SAMPLE.SAMPLE_CLASS at the specified depth. This function searches the DT_FILE table for the PLACE_TYPE ‘sample_class_graphic’ and a PLACE_CODE that matches the SAMPLE_CLASS, then returns the CONTENT field as the column_value.
CREATE FUNCTION cust_schema.ei_sampleclass_graphic ( @facility_id INT , @sys_loc_code VARCHAR(20) ) RETURNS @results TABLE ( start_depth FLOAT ,end_depth FLOAT ,depth_unit VARCHAR(10) ,column_header VARCHAR(100) ,column_value VARBINARY(max) ) AS BEGIN
INSERT INTO @results SELECT s.start_depth,s.end_depth,s.depth_unit,'Sample Class' column_header,f.content FROM dbo.dt_sample s LEFT JOIN dbo.dt_file f ON f.place_type = 'sample_class_graphic' AND f.place_code = s.sample_class WHERE s.facility_id=@facility_id AND s.sys_loc_code=@sys_loc_code AND s.parent_sample_code IS NULL
RETURN END; GO
GRANT EXECUTE ON cust_schema.ei_sampleclass_graphic TO es_public; GO |