USP_KPIINSTANCE_CREATESTATUSCHANGEDALERT

create procedure dbo.USP_KPIINSTANCE_CREATESTATUSCHANGEDALERT
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @EMAILSUBJECT nvarchar(255) = null,
    @EMAILBODY nvarchar(max) = null
)
as
    set nocount on;

    begin try

        declare @EXISTINGID uniqueidentifier;
        select @EXISTINGID = STATUSCHANGEDALERTID 
            from dbo.KPIINSTANCE
            where ID = @ID;

        if @EXISTINGID is null
            begin
                declare @KPIINSTANCENAME nvarchar(255);
                select @KPIINSTANCENAME = NAME from dbo.KPIINSTANCE where ID = @ID; 

                declare @ALERTTYPEID uniqueidentifier;
                set @ALERTTYPEID = NewID();

                declare @CURRENTDATE date;
                set @CURRENTDATE = GetDate();

                declare @ALERTNAME nvarchar(100);
                set @ALERTNAME = left(@KPIINSTANCENAME + ' status changed alert', 100);

                -- use the 'KPI Instance Status History Data List'

                declare @DATALISTID uniqueidentifier;
                set @DATALISTID = '5D8127D7-C845-4B80-9030-38C0195B8E91'

                if @EMAILSUBJECT is null
                    set @EMAILSUBJECT = 'KPI status changed for <<NAME>>';

                if @EMAILBODY is null
                    set @EMAILBODY = 'The status of the KPI <<NAME>> has changed from <<PREVIOUSSTATUS>> to <<CURRENTSTATUS>>.' + CHAR(13) + CHAR(13) + 'The value has changed from <<PREVIOUSVALUE>> to  <<CURRENTVALUE>>.';

                -- if an alert with this name already exists (most likely added manually), then rename it to avoid a conflict

                select @EXISTINGID = ID from dbo.ALERTTYPE where NAME = @ALERTNAME;
                if @EXISTINGID is not null
                    update dbo.ALERTTYPE 
                        set NAME = left(NAME, 64) + cast(NewID() as nvarchar(36))
                        where ID = @EXISTINGID;

                -- insert a new alert type, defined as a single-instance system alert type

                insert into dbo.ALERTTYPE
                    (ID, NAME, TYPECODE, EMAILSUBJECT, EMAILBODY, DATALISTID, SECURITYCHECKFEATURETYPECODE, SECURITYCHECKFEATUREID, ISSINGLEINSTANCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values
                    (@ALERTTYPEID, @ALERTNAME, 2, @EMAILSUBJECT, @EMAILBODY, @DATALISTID, 3, @DATALISTID, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                -- create the default alert instance

                declare @ALERTINSTANCEID uniqueidentifier;
                set @ALERTINSTANCEID = NewID();

                insert into dbo.FEEDALERTINSTANCE
                    (ID, ALERTTYPEID, CONTEXTRECORDID, NAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED )
                    values
                    (@ALERTINSTANCEID, @ALERTTYPEID, @ID, 'Default', @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                -- update the KPI instance

                update dbo.KPIINSTANCE 
                    set STATUSCHANGEDALERTID = @ALERTTYPEID
                    where ID = @ID    
            end

        return 0;

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch