EnviroInsite templates support the plotting of database functions in Boring Logs or Subsurface Profiles. Data from SQL scalar functions may be plotted as header/footer text in the EnviroInsite template. Functions with names starting with “ei_” will be available in the Functions tab in the right pane of the Template Design window.
Note: EnviroInsite always provides @facility_id and @sys_loc_code as parameters. These functions only support one project (TASK_CODE) per facility.
•This function reports the DT_GEO_BACKFILL.REMARK field for the specified location. Only one abandonment record is expected per location.
•The Advancement Method queries data from the DT_DRILL_ACTIVITY table where DRILL_EVENT starts with ‘INITIAL_’ and PURPOSE = ‘Advancement Method’. The function returns the text in DRILL_METHOD for each matching record.
•This function can support multiple drill events. If there are multiple records returned, the function separates those by commas and adds the START_DEPTH and DEPTH_UNIT to the end of each drill event in parentheses.
•The DEPTH_UNIT is returned from DT_LOCATION if DT_DRILL_ACTIVITY.DEPTH_UNIT is not populated.
•Hollow Stem Auger
•2.25” Hollow Stem Auger (0ft), 3.25” Hollow Stem Auger (12 ft)
•This function returns the PARAM_VALUE in the DT_LOCATION_PARAMETER table associated with PARAM_CODE = ‘Cave_In_Note’.
•This function queries the PARAM_VALUE in the DT_TASK_PARAMETER table where PARAM_CODE = ‘client_location’ value.
•If more than one record is found, the PARAM_VALUE is returned from the record with the most recent MEASUREMENT_DATE.
•This function includes arguments for @facility_id, @sys_loc_code, @topdepthrange, and @bottomdepthrange.
•This function queries the depth notes in DT_NOTE.NOTE_TEXT related to the queried location where DT_NOTE.NOTE_TYPE = ‘Depth Remarks’ and returns them, concatenated, in the order the notes were created.
•This function checks the PARAM_VALUEs for the following PARAM_CODEs in the DT_TASK_PARAMETER table:
•If PARAM_VALUE = ‘Y’, the function concatenates “Latitude:” with the VW_LOCATION.LATITUDE value and “Longitude” with the VW_LOCATION.LONGITUDE value. These values are rounded to 4 decimal places.
•If PARAM_VALUE = ‘N’, the function does not report the Lat/Long values.
•If PARAM_VALUE = ‘Y’, the function finds matching records for the following:
•DT_COORDINATE.COORD_TYPE_CODE = the PARAM_VALUE for DT_LOCATION_PARAMETER.PARAM_CODE = ‘ne_coord_type’
•DT_COORDINATE.IDENTIFIER = DT_FACILITY.IDENTIFIER (or ‘Primary’, if that field is blank).
•The function then concatenates “Northing:” with DT_COORDINATE.Y_COORD and “Easting” with DT_COORDINATE.X_COORD for those matching records.
•If PARAM_VALUE = ‘N’, the function does not report the Northing/Easting values.
•In addition to displaying Lat/Long and/or Northing/Easting, this function can display the location’s offset and station. If DT_LOCATION.STATION and/or DT_LOCATION.OFFSET are populated, then these values are concatenated with “Station:” or “Offset:” accordingly.
•Latitude: 12.3456° Longitude: -123.4567°
•Northing: 4380308.47714 Easting: 686202.15967 Station: B Offset: -0.5
•This function checks the PARAM_VALUE in the DT_LOCATION_PARAMETER table for PARAM_CODE = 'ei_disp_elev_accur'.
•If PARAM_VALUE = ‘Y’, then the function concatenates ‘Approximate Surface Elev:’ with the VW_LOCATION.SURF_ELEV and VW_LOCATION.ELEV_UNIT values, then ±, e.g.:
"Approximate Surface Elev: 1129 (FT.) ±"
•If PARAM_VALUE = ‘N’, then the function concatenates ‘Surface Elev:’ with the VW_LOCATION.SURF_ELEV and VW_LOCATION.ELEV_UNIT values, e.g.:
"Surface Elev: 1129.33 (FT.)"
•When a record where AT_FACILITY.ASSOCIATION = ‘office_designation’ exists, this function returns the ADDRESS_1, CITY, and STATE values from the RT_COMPANY table for the record where RT_COMPANY.COMPANY_CODE = AT_FACILITY.COMPANY_CODE.
•Any of these fields that are not populated will be omitted.
•The queried data are then formatted like "[address_1] [city], [state]", e.g., "12345 ABC ST. New York, NY".
•This function queries DT_FACILITY.ADDRESS_1, DT_FACILITY.CITY, and DT_FACILITY.STATE. The function inserts a ‘|’ (pipe) character in between DT_FACILITY.ADDRESS_1 and DT_FACILITY.CITY plus DT_FACILITY.STATE (i.e., 123 1st Ave | Seattle, WA).
•This function returns DT_FACILITY.CITY and DT_FACILITY.STATE values and formats it by adding a comma in between City and State (i.e., Seattle, WA).
•This function returns the following:
•If DT_WATER_TABLE.TYPE = ‘1st Observation’, then the associated DT_WATER_TABLE.REMARK is returned.
•If DT_WATER_TABLE.TYPE = ‘nowaterencountered’, then the function returns ‘No Water Encountered’.
•This function only supports one observation per DT_WATER_TABLE.TYPE = ‘1st Observation’. In cases where there is more than one record for the same observation type per location, then the function will return the record where the DT_WATER_TABLE.SEQUENCE is alphabetically last.
•This function queries DT_WATER_TABLE.REMARK where DT_WATER_TABLE.TYPE = ‘2nd Observation’.
•This function only supports one observation per DT_WATER_TABLE.TYPE = ‘2nd Observation’. In cases where there is more than one record for the same observation type per location, then the function will return the record where the DT_WATER_TABLE.SEQUENCE is alphabetically last.
•This function queries DT_WATER_TABLE.REMARK where DT_WATER_TABLE.TYPE = ‘3rd Observation’.
•This function only supports one observation per DT_WATER_TABLE.TYPE = ‘3rd Observation’. In cases where there is more than one record for the same observation type per location, then the function will return the record where the DT_WATER_TABLE.SEQUENCE is alphabetically last.
Copyright © 2023 EarthSoft, Inc. • Modified: 22 Aug 2023