USP_DATAFORMTEMPLATE_EDIT_SALESORDERALERTCONFIGURATION_BYAPPUSER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESORDERALERTCONFIGURATION_BYAPPUSER (
    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @ALERTONDONATION bit,
    @ALERTONDAILYADMISSION bit,
    @ALERTONSCHEDULEDPROGRAM bit,
    @ALERTONSPECIALEVENT bit,
    @ALERTONMEMBERSHIP bit
)
as
    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        if 
            @ALERTONDONATION = 1 or
            @ALERTONDAILYADMISSION = 1 or
            @ALERTONSCHEDULEDPROGRAM = 1 or
            @ALERTONSPECIALEVENT = 1 or
            @ALERTONMEMBERSHIP = 1 or
            len(@EMAILADDRESS) > 0
        begin
            declare @SALESORDERALERTEMAILADDRESSID uniqueidentifier = null
            select @SALESORDERALERTEMAILADDRESSID = SALESORDERALERTEMAILADDRESS.ID
            from dbo.SALESORDERALERTEMAILADDRESS
            where SALESORDERALERTEMAILADDRESS.APPUSERID = @CURRENTAPPUSERID

            if @SALESORDERALERTEMAILADDRESSID is null
            begin
                set @SALESORDERALERTEMAILADDRESSID = newid()
                insert dbo.SALESORDERALERTEMAILADDRESS (
                    [ID],
                    [APPUSERID],
                    [EMAILADDRESS],
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values (
                    @SALESORDERALERTEMAILADDRESSID,
                    @CURRENTAPPUSERID,
                    @EMAILADDRESS,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                );
            end
            else
            begin
                update SALESORDERALERTEMAILADDRESS
                set 
                    [EMAILADDRESS] = @EMAILADDRESS,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @SALESORDERALERTEMAILADDRESSID
            end

            --Find the configuration settings this appuser is associated with
            declare @SALESORDERALERTCONFIGURATIONID uniqueidentifier = null
            select @SALESORDERALERTCONFIGURATIONID = SALESORDERALERTCONFIGURATION.ID
            from dbo.SALESORDERALERTEMAILSUBSCRIPTION
            inner join dbo.SALESORDERALERTCONFIGURATION on
                SALESORDERALERTEMAILSUBSCRIPTION.SALESORDERALERTCONFIGURATIONID = SALESORDERALERTCONFIGURATION.ID
            where SALESORDERALERTEMAILSUBSCRIPTION.SALESORDERALERTEMAILADDRESSID = @SALESORDERALERTEMAILADDRESSID

            if @SALESORDERALERTCONFIGURATIONID is null
            begin
                set @SALESORDERALERTCONFIGURATIONID = newid()

                insert into dbo.SALESORDERALERTCONFIGURATION (
                    ID,
                    ALERTONDONATION,
                    ALERTONDAILYADMISSION,
                    ALERTONSCHEDULEDPROGRAM,
                    ALERTONSPECIALEVENT,
                    ALERTONMEMBERSHIP,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values (
                    @SALESORDERALERTCONFIGURATIONID,
                    @ALERTONDONATION,
                    @ALERTONDAILYADMISSION,
                    @ALERTONSCHEDULEDPROGRAM,
                    @ALERTONSPECIALEVENT,
                    @ALERTONMEMBERSHIP,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                )

                insert into dbo.SALESORDERALERTEMAILSUBSCRIPTION (
                    ID,
                    SALESORDERALERTCONFIGURATIONID,
                    SALESORDERALERTEMAILADDRESSID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED

                )
                values (
                    newid(),
                    @SALESORDERALERTCONFIGURATIONID,
                    @SALESORDERALERTEMAILADDRESSID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                )
            end
            else
            begin
                -- Future: If there comes a time when sales order alert configurations can be used by multiple users,
                -- it'd probably be a good idea to create a new 'configuration' record if more than one user is pointing to this one
                -- I doubt we'd want to allow a user to change the configuration settings for everyone else associated with this one
                -- Right now: that's not an issue, so let's just update!

                update dbo.SALESORDERALERTCONFIGURATION 
                set
                    ALERTONDONATION = @ALERTONDONATION,
                    ALERTONSCHEDULEDPROGRAM = @ALERTONSCHEDULEDPROGRAM,
                    ALERTONDAILYADMISSION = @ALERTONDAILYADMISSION,
                    ALERTONMEMBERSHIP = @ALERTONMEMBERSHIP,
                    ALERTONSPECIALEVENT = @ALERTONSPECIALEVENT,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @SALESORDERALERTCONFIGURATIONID
            end
        end
        else --User had no options checked, let's remove their settings
        begin
            /* cache current context information */
            declare @contextCache varbinary(128);
            set @contextCache = CONTEXT_INFO();

            /* set CONTEXT_INFO to @CHANGEAGENTID */
            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID

            -- Again, if an appuser can have more than one email or setting configuration at some point,
            -- we'll probably want to change this
            delete dbo.SALESORDERALERTEMAILADDRESS
            where APPUSERID = @CURRENTAPPUSERID

            /* reset CONTEXT_INFO to previous value */
            if not @contextCache is null
                set CONTEXT_INFO @contextCache
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;