NOTE: See ****UPDATED VERSION**** in comments below

TitleName/Agency Point of ContactVersion of EdgeFrontier

Adds a more detailed Ani/Ali remark to an event upon creation and when additional Ani/Ali data is associated with the event. The comment is added using the IFCADEventAddComments command. The Ani/Ali data is queried directly from the CAD database via a view that we added to our instance called vw_AniAliDataForRemark. 

If you want to customize what the ani/ali remark says or formatting you would change the view. You could also use this as an example for adding a comment to an event based off of data from a database (or other source). We were advised to put the views in a schema specific to our organization so that when database updates are made our objects would not be deleted/modified.

To set up you will have to enter the database connection information.

The format of the remark currently looks something like this:

Here is the view... I couldn't add multiple files.

CREATE VIEW [ss911].[vw_AniAliDataForRemark]  
(
     [num_1]
    ,[eid]
    ,[eventCallID]
    ,[aniAliString]
)
AS
    WITH cte AS(
    --Some of the data when returned contains carriage returns (CHAR(13)) and/or line feeds (CHAR(10)) from the source. Replace these characters with a space to ensure
    --AniAli data is not broken up when added to remarks.
    SELECT ae.[num_1], ae.[eid], cech.[event_call_id],
        REPLACE(REPLACE(
            'LOCATION:'    + ISNULL(aniali.[estnum],'') + ' ' + ISNULL(aniali.[efeanme],'') + coalesce(', ' + aniali.loc_id, '') + ', ' + ISNULL(aniali.[ccity],'') +
            ' \PHONE:'    + ISNULL(aniali.[clrnum],'')  +
            ' \COMP:'    + COALESCE(aniali.clname, cech.clname, '') +
            ' \SRC:'    + ISNULL(aniali.[service],'') +
            ' \PNUM:'    + ISNULL(aniali.[pilot_nnx],'')   +
            ' \ESN_TEXT:'    + ISNULL(aniali.[esn_text],'') + ISNULL(' ' + [y_lat],'') + ISNULL(' ' + [x_long],'') +
            ' \ESN:'    + ISNULL(aniali.[esn], '')
        ,CHAR(13), ' '), CHAR(10), ' ') as aniAliString
    FROM [dbo].[an_al] as aniali
    INNER JOIN [dbo].[common_event_call_history] AS cech ON cech.call_id = aniali.call_num
    INNER JOIN [dbo].[agency_event] AS ae ON ae.eid = cech.eid
    )
    --Do not show rows that have already been listed as a remark for this eid.
    SELECT * FROM cte
    WHERE aniAliString NOT IN (SELECT comm FROM evcom WHERE eid = cte.eid)
GO