Procedures for Writing Custom Handlers

The information provided here describes the process of writing custom handlers to be used in EDP. After making the desired changes to the format file, it is required when using Professional EDP (with application-level security) or Enterprise EDP that the format either be encrypted by EarthSoft (i.e., *.xse) or be embedded as a resource in a strong-named .NET assembly (i.e., *.dll). See Securing EDD Formats for additional information.

Mappings with Integer IDs

A number of tables in the EQuIS database use Integer ID fields (FACILITY_ID, SAMPLE_ID, TEST_ID, etc.). When importing an EDD, EDP needs to know what values to use for these ID fields. This section explains how to define the XSD mapping such that EDP will properly populate the ID fields.

FACILITY_ID

The EQuIS data structure uses FACILITY_ID in almost all data tables (DT_*). Because this field is so prevalent, the EDP code has been written to automatically populate this field with the appropriate value (the FACILITY_ID of the currently selected facility). This being the case, the XSD does not need to contain any mapping elements for FACILITY_ID, it will automatically be handled behind the scenes. However, if a particular EDD (such as an EDD to import facilities) needs to override this functionality, then FACILITY_ID may be explicitly mapped in the XSD and it will function as any other mapping.

SAMPLE_ID, TEST_ID

When mapping EDDs into samples and test, the XSD mapping elements need to be set up to properly obtain and use the appropriate integer ID values. In order to simplify this process, EDP has been written to handle most of the logic regarding ID values. The XSD only needs to indicate how and where the ID values are to be used in the mapping. Below is an excerpt from an XSD that illustrates the use of ID values:

<edd:table mode="create" target="dt_sample">

<edd:field new_id="sample_id" target="sample_id" />

<edd:field source="Sample Code" target="sys_sample_code" />

... other field mappings omitted ...

</edd:table>

<edd:table mode="create" target="dt_field_sample">

<edd:field current_id="sample_id" target="sample_id" />

... other field mappings omitted ...

</edd:table>

This excerpt shows two important attributes, NEW_ID and CURRENT_ID. In many cases, a single EDD table will target both the DT_SAMPLE and DT_FIELD_SAMPLE. When executing the mappings (during the "Create" step), the DT_SAMPLE_MAPPING is executed first (because mappings are executed in the order they appear in the XSD). Each EDD row needs to get a new SAMPLE_ID value for the DT_SAMPLE row. However, when doing the second mapping (that targets DT_FIELD_SAMPLE), we do not want to get a new SAMPLE_ID value; we want to use the same SAMPLE_ID value that was used for the DT_SAMPLE row. The NEW_ID attribute creates (and returns) a new integer ID value of the specified type (i.e. "SAMPLE_ID"). The CURRENT_ID attribute returns the current integer ID value for the specified type (i.e. "SAMPLE_ID"), but does not create a new value.

These same NEW_ID and CURRENT_ID attributes are illustrated below as they would be used to map a Test/Result EDD format:

<edd:table mode="create" target="dt_test">

<edd:field new_id="test_id" target="test_id" />

<edd:field source="Sample Code" target="sys_sample_code" />

... other field mappings omitted ...

</edd:table>

<edd:table mode="create" target="dt_result">

<edd:field current_id="test_id" target="test_id" />

<edd:field source="Cas RN" target="cas_rn" />

... other field mappings omitted ...

</edd:table>

Conditional Mappings

When mapping EDD rows into EQuIS rows (during the "Create" step in EDP), there are some cases when mappings are conditional. For example, the EDD may contain a SAMP format that defines both field samples and lab samples. In order to create the DT_FIELD_SAMPLE records, the XSD must contain a mapping that targets DT_FIELD_SAMPLE. However, this mapping should not be executed for lab samples (because a lab sample should not have a DT_FIELD_SAMPLE record). A conditional mapping may be used to control whether or not a particular mapping is actually executed.

Here is an excerpt from an XSD:

<edd:table mode="create" target="dt_sample">

... field mappings omitted ...

</edd:table>

<edd:table mode="create" target="dt_field_sample" method="IsFieldSample">

... field mappings omitted ...

</edd:table>

As shown, the first mapping (that targets the DT_SAMPLE_TABLE) is executed for every row in the EDD. However, the second mapping (that targets DT_FIELD_SAMPLE) does have a conditional method (as indicated by the method attribute). So, before executing the mapping, EDP will first call the IsFieldSample method on the custom handler object. If that method returns "True," then the mapping is executed for that particular EDD row. If that method returns "False," then the mapping is skipped. Here is the example method (as defined in the custom handler):

Public Function IsFieldSample(ByVal eddRow As System.Data.DataRow) As Boolean

' implement logic to determine if this is a field sample '

Return eddRow.Item("sample_type").ToString.Equals("N")

End Function

A conditional mapping method must be a Public function that returns a Boolean value. The function may accept one or two parameters (as shown below). EDP will determine (at runtime) which of the following method signatures applies to the particular function:

Public Function SomeFunction(ByVal eddRow As System.Data.DataRow) As Boolean

' use this method signature if the logic requires only the EDD row (but not the target row) '

End Function

Public Function OtherFunction(ByVal eddRow As System.Data.DataRow, ByVal targetRow As System.Data.DataRow) As Boolean

' use this method signature if the logic requires both the EDD row AND the target row to evaluate the condition '

End Function

Custom Mapping Methods

One of the advantages of using a custom handler is to implement custom business rules that will be applied while mapping the EDD to the EQuIS database during the import process (i.e. the "Create" step in EDP). These methods may be used for any type of business rule, but one typical example is combining separate date and time fields from the EDD into a single DateTime field in EQuIS.

The first step in using a custom mapping method is to define the mapping in the EQuIS Format Definition (EFD) file (XSD) as shown:

<edd:table mode="parent" target="dt_sample">

<edd:field source="Sys_Sample_Code" target="Sys_Sample_Code" />

<edd:field method="GetSampleDate" target="sample_date" />

</edd:table>

The method attribute indicates that the value for the target field should come from a function call (to the custom handler object) instead of coming directly from a source field in the EDD. After defining the mapping using the method attribute, the next step is to define the function in the custom handler. The function must be a Public function and return any valid Object type (the return type must match the type of the target field in the database).

An example GetSampleDate function is shown here:

Public Function GetSampleDate(ByVal eddRow As System.Data.DataRow) As Date

' combine sample date and sample time '

Return Date.Parse(String.Format("{0} {1}", CType(eddRow.Item("sample_date"), Date).ToString("yyyy-MM-dd"), eddRow.Item("sample_time")))

End Function

When rows are being mapped, EDP will call this function and pass in the current row from the EDD. The function takes the values from the SAMPLE_DATE and SAMPLE_TIME columns, converts those values into a single Date value (that includes the time), and returns that Date value. That Date value is then inserted (according to the mapping) into the SAMPLE_DATE column of the new DT_SAMPLE row.

In some cases, the function may need to know specific information about the target row. In that case, the function could be defined as follows:

Public Function SomeFunction(ByVal eddRow As System.Data.DataRow, ByVal targetRow As System.Data.DataRow) As <some type>

' do something ... '

Return <some object>

End Function

If the function is defined with the second DataRow argument, then EDP will automatically pass that row as well. This is the DataRow object that represents the current target row that is being mapped.

Custom Cross-Checks

A custom cross-check is a check that involves values from different tables within the format. For example, suppose you have a Sample table and Test/Result table. A cross-check may be written that compares the ANALYSIS_DATE (from the Test/Result table) to the SAMPLE_DATE (from the Sample table). Since the fields being compared are in different tables, extra steps must be taken to effectively implement the check.

This section will refer to the Enovis custom handler to illustrate a custom cross-check. The Enovis format specification includes a custom check referred to as Check #16, which says:

ERR_E16: When RESULT_TYPE = "SC", or "RSTD" and SAMPLE_TYPE_CODE = "BD", "LCSD" or "MSD", (QC_DUP_SPIKE_RECOVERY) must be populated, else (QC_DUP_SPIKE_RECOVERY) must be null.

The SAMPLE_TYPE_CODE field is in the EnovisSAMP table, and the RESULT_TYPE_CODE field is in the EnovisTRS table, so we must implement a custom cross-check to achieve the desired functionality. Below are several excerpts of code from the Enovis custom handler that illustrate how to implement a custom cross-check.

' We need a reference to the data table for finding rows during custom cross-checks. This reference is obtained during AddDataHandlers() '

Private EnovisSAMP As System.Data.DataTable

' This relation is used for finding child rows (i.e. during custom cross-checks). This reference is obtain during AddDataHandlers() '

Private Smp_TrsQc As EarthSoft.Edp.EddRelation

' Several checks lookup the sample for a given test. Instead of looking it up over, and over, we will cache each lookup in this variable so we do not have to look it up again. '

Private sampleRow As System.Data.DataRow

Public Overrides Sub AddDataHandlers(ByRef Efd As EarthSoft.Edp.EddFormatDefinition)

' get the table reference (as defined in the *.xsd) '

Me.EnovisSAMP = Efd.Tables.Item("EnovisSAMP")

' add an event handler '

AddHandler Me.EnovisTRSQC.ColumnChanged, AddressOf Me.CheckEnovisTRSQC

. (code omitted)

' get the relation (as defined in the *.xsd) '

Me.Smp_TrsQc = CType(EnovisTRSQC, EddTable).ParentRelations.Item("FK_EnovisTRSQC_EnovisSAMP")

End Sub

' Call the check when any of the values associated with the check is changed '

Private Sub CheckEnovisTRSQC(ByVal sender As Object, ByVal e As System.Data.DataColumnChangeEventArgs)

Select Case e.Column.ColumnName.ToLower

Case "sys_sample_code"

ERR16(e)

Case "qc_dup_spike_recovery"

ERR16(e)

Case "result_type_code"

ERR16(e)

End Select

End Sub

Private Sub ERR16(ByVal e As System.Data.DataColumnChangeEventArgs)

' store sample_type_code and result_type_code in local variables '

Dim stc As String = String.Empty

Dim rtc As String = String.Empty

With e.Row

' Check if sys_sample_code, result_type_code are populated and parent table contains rows '

If Not .IsNull("sys_sample_code") AndAlso Not .IsNull("result_type_code") AndAlso Me.EnovisSAMP.Rows.Count > 0 Then

' do we need to lookup the sample row? '

If Me.sampleRow Is Nothing OrElse Not Me.sampleRow.Item("sys_sample_code").ToString.Equals(.Item("sys_sample_code")) Then

' use the relation to get the parent row for this sample '

Me.sampleRow = Me.Smp_TrsQc.GetParentRow(e.Row)

End If

' make sure sample row was found '

If Not Me.sampleRow Is Nothing AndAlso Not Me.sampleRow.IsNull("sample_type_code") Then

' Get the sample_type_code from the parent row '

stc = Me.sampleRow.Item("sample_type_code").ToUpper

' now get the result_type_code from the child/current row '

rtc = .Item("result_type_code").ToString.ToUpper

' now check for the specified conditions '

If ((stc = "BD" OrElse stc = "MSD" OrElse stc = "LCSD") AndAlso (rtc = "SC" OrElse rtc = "RSTD") AndAlso (.IsNull("qc_dup_spike_recovery"))) _

OrElse (Not ((stc = "BD" OrElse stc = "MSD" OrElse stc = "LCSD") AndAlso (rtc = "SC" OrElse rtc = "RSTD")) AndAlso (Not .IsNull("qc_dup_spike_recovery"))) Then

Me.AddError(e.Row, .Table.Columns.Item("qc_dup_spike_recovery"), EarthSoft.EDP.EddErrors.CustomError11)

Else

Me.RemoveError(e.Row, .Table.Columns.Item("qc_dup_spike_recovery"), EarthSoft.EDP.EddErrors.CustomError11)

End If

Else

' sample row was not found or sample_type_code is null '

Me.RemoveError(e.Row, .Table.Columns.Item("qc_dup_spike_recovery"), EarthSoft.EDP.EddErrors.CustomError11)

End If

Else

' sys_sample_code or result_type_code is null or parent table does not contain any rows '

Me.RemoveError(e.Row, .Table.Columns.Item("qc_dup_spike_recovery"), EarthSoft.EDP.EddErrors.CustomError11)

End If

End With

End Sub

'for most checks, if one cell is updated, the other needs to be explicitly updated

because the error will need to be added/removed to both columns '

Public Overrides Sub Grid_AfterCellUpdate(ByVal sender As Object, ByVal e As Object, ByVal edp As Object)

' make an explicit call to AfterCellUpdate to show/clear the error on the other cell '

Select Case e.Cell.Column.Key.ToLower

Case "sys_sample_code", "result_type_code"

If (e.Cell.Row.Band.Key.ToUpper = "ENOVISTRSQC") Then edp.AfterCellUpdate(sender, e.Cell.Row.Cells.Item("qc_dup_spike_recovery"))

End Select

End Sub

' Create the custom message '

Public Overloads Overrides Function ErrorMessage(ByVal err As EddErrors) As String

Select Case err

Case EddErrors.CustomError11

Return "qc_dup_spike_recovery cannot be null when sample_type_code = BD, LCSD or MSD Else qc_dup_spike_recovery must be null"

Case Else

Return MyBase.ErrorMessage(err)

End Select

End Function