USP_BBNC_INTEGRATIONWEBTRAFFICADDUPDATE

            CREATE procedure dbo.USP_BBNC_INTEGRATIONWEBTRAFFICADDUPDATE
            (
                @WEBTRAFFICMAPID int,
                @BBNCUSERID int,
                @REQUESTDATE datetime,
                @PAGEID int,
                @PAGENAME nvarchar(50),
                @URL dbo.UDT_WEBADDRESS,
                @TEAMFUNDRAISERMAPID int,
                @TEAMFUNDRAISINGTEAMMAPID int,
                @EMAILJOBRECIPIENTMAPID int,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
                set nocount on;

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

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                begin try
                    if not (@WEBTRAFFICMAPID > 0)
                        raiserror('ERR_WEBTRAFFICMAPID_REQUIRED',16,1);

                    declare @NETCOMMUNITYCLIENTUSERID uniqueidentifier;
                    if @BBNCUSERID > 0
                        begin
                            select
                                @NETCOMMUNITYCLIENTUSERID = NETCOMMUNITYCLIENTUSER.ID
                            from
                                dbo.NETCOMMUNITYCLIENTUSER
                            where
                                BBNCUSERID = @BBNCUSERID;

                            if @NETCOMMUNITYCLIENTUSERID is null
                                begin
                                    set @NETCOMMUNITYCLIENTUSERID = newid();
                                    insert into dbo.NETCOMMUNITYCLIENTUSER
                                    (
                                        [ID],
                                        [BBNCUSERID],
                                        [LASTLOGINDATE],
                                        [ADDEDBYID],
                                        [CHANGEDBYID],
                                        [DATEADDED],
                                        [DATECHANGED]
                                    )
                                    values
                                    (
                                        @NETCOMMUNITYCLIENTUSERID,
                                        @BBNCUSERID,
                                        @REQUESTDATE,
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID,
                                        @CHANGEDATE,
                                        @CHANGEDATE
                                    );
                                end
                            else
                                update dbo.NETCOMMUNITYCLIENTUSER
                                set
                                    [LASTLOGINDATE] = @REQUESTDATE,
                                    [CHANGEDBYID] = @CHANGEAGENTID,
                                    [DATECHANGED] = @CHANGEDATE
                                where
                                    [ID] = @NETCOMMUNITYCLIENTUSERID
                                    and ([LASTLOGINDATE] IS NULL OR [LASTLOGINDATE] < @REQUESTDATE);
                        end

                    declare @TEAMFUNDRAISINGTEAMMEMBERID uniqueidentifier;
                    if @TEAMFUNDRAISERMAPID > 0
                        select
                            @TEAMFUNDRAISINGTEAMMEMBERID = TEAMFUNDRAISINGTEAMMEMBER.ID
                        from
                            dbo.TEAMFUNDRAISINGTEAMMEMBER
                            inner join dbo.BBNCTEAMFUNDRAISERIDMAP on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = BBNCTEAMFUNDRAISERIDMAP.TEAMFUNDRAISERID
                            left join dbo.BBNCTEAMFUNDRAISINGTEAMIDMAP on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = BBNCTEAMFUNDRAISINGTEAMIDMAP.TEAMFUNDRAISINGTEAMID
                        where
                            BBNCTEAMFUNDRAISERIDMAP.ID = @TEAMFUNDRAISERMAPID
                            and
                            (
                                BBNCTEAMFUNDRAISINGTEAMIDMAP.ID = @TEAMFUNDRAISINGTEAMMAPID
                                or
                                (
                                    @TEAMFUNDRAISINGTEAMMAPID = 0
                                    and
                                    BBNCTEAMFUNDRAISINGTEAMIDMAP.ID is null
                                )
                            );

                    if exists
                    (
                        select
                            [ID]
                        from
                            dbo.NETCOMMUNITYWEBTRAFFIC
                        where
                            @WEBTRAFFICMAPID = [BBNCMAPID]
                    )
                        update dbo.NETCOMMUNITYWEBTRAFFIC
                        set
                            [NETCOMMUNITYCLIENTUSERID] = @NETCOMMUNITYCLIENTUSERID,
                            [REQUESTDATE] = @REQUESTDATE,
                            [PAGEID] = coalesce(@PAGEID, 0),
                            [PAGENAME] = coalesce(@PAGENAME, ''),
                            [URL] = coalesce(@URL, ''),
                            [TEAMFUNDRAISINGTEAMMEMBERID] = @TEAMFUNDRAISINGTEAMMEMBERID,
                            [EMAILJOBRECIPIENTMAPID] = coalesce(@EMAILJOBRECIPIENTMAPID, 0),
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CHANGEDATE
                        where
                            [BBNCMAPID] = @WEBTRAFFICMAPID
                            and
                            (
                                coalesce([NETCOMMUNITYCLIENTUSERID], '00000000-0000-0000-0000-000000000000') <> coalesce(@NETCOMMUNITYCLIENTUSERID, '00000000-0000-0000-0000-000000000000')
                                or ([REQUESTDATE] <> @REQUESTDATE or ([REQUESTDATE] is null and @REQUESTDATE is not null) or ([REQUESTDATE] is not null and @REQUESTDATE is null))
                                or [PAGEID] = coalesce(@PAGEID, 0)
                                or [PAGENAME] = coalesce(@PAGENAME, '')
                                or [URL] <> coalesce(@URL, '')
                                or coalesce([TEAMFUNDRAISINGTEAMMEMBERID], '00000000-0000-0000-0000-000000000000') <> coalesce(@TEAMFUNDRAISINGTEAMMEMBERID, '00000000-0000-0000-0000-000000000000')
                                or [EMAILJOBRECIPIENTMAPID] <> coalesce(@EMAILJOBRECIPIENTMAPID, 0)
                            );
                    else
                        insert into dbo.NETCOMMUNITYWEBTRAFFIC
                        (
                            [BBNCMAPID],
                            [NETCOMMUNITYCLIENTUSERID],
                            [REQUESTDATE],
                            [PAGEID],
                            [PAGENAME],
                            [URL],
                            [TEAMFUNDRAISINGTEAMMEMBERID],
                            [EMAILJOBRECIPIENTMAPID],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @WEBTRAFFICMAPID,
                            @NETCOMMUNITYCLIENTUSERID,
                            @REQUESTDATE,
                            coalesce(@PAGEID, 0),
                            coalesce(@PAGENAME, ''),
                            coalesce(@URL, ''),
                            @TEAMFUNDRAISINGTEAMMEMBERID,
                            coalesce(@EMAILJOBRECIPIENTMAPID, 0),
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE
                        );
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;