USP_GROUPMEMBERADDBULK

CREATE procedure dbo.USP_GROUPMEMBERADDBULK
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @GROUPID uniqueidentifier,
    @IDSETREGISTERID uniqueidentifier,
    @RECOGNIZEMEMBERFORHOUSEHOLD bit = 0, -- Only applies when adding a member to a household

    @STARTDATE date = null,
    @USENULLSTARTDATE bit = 0, -- If a null startdate is passed in, use it instead of the group start date

    @ENDDATE date = null,
    @COMMENTS nvarchar(300) = null,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
begin

    -- Developer note: This procedure is very closely related to USP_GROUPMEMBERADD.

    -- Any changes made to the logic should be made in both procedures if applicable.


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

    declare @ADDGROUPMEMBERSINBULKSYSTEMPRIVILEGEID uniqueidentifier = '4f5f223c-c0ed-4a2a-a0b7-32e293c3f3e2'

    declare @ISSYSADMIN bit = 0;

    -- skip security check if no app user is supplied

    if @CURRENTAPPUSERID is not null
    begin
        set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

        declare @GRANTORDENY int = 0;
        select
            @GRANTORDENY = SV.GRANTORDENY
        from
            dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE as SV
        where
            SV.APPUSERID = @CURRENTAPPUSERID
            and SV.SYSTEMPRIVILEGECATALOGID = @ADDGROUPMEMBERSINBULKSYSTEMPRIVILEGEID
        order by
            SV.GRANTORDENY asc

        if @ISSYSADMIN = 0 and @GRANTORDENY = 0
        begin
            raiserror ('BBERR_USER_NOT_AUTHORIZED', 13, 1);
        end
    end

    declare @SELECTIONISSTATIC bit = 0;
    select
        @SELECTIONISSTATIC = IDSETREGISTER.STATIC
    from
        dbo.IDSETREGISTER
    where
        IDSETREGISTER.ID = @IDSETREGISTERID

    if @SELECTIONISSTATIC = 0
    begin
        raiserror ('BBERR_SELECTION_NOT_STATIC', 13, 1);
        return 1;
    end

    declare @CURRENTDATE datetime = getdate();

    if @STARTDATE > @CURRENTDATE
    begin
        raiserror ('BBERR_STARTDATE_CANNOT_BE_IN_FUTURE', 13, 1);
        return 1;
    end

    if @ENDDATE > @CURRENTDATE
    begin
        raiserror ('BBERR_ENDDATE_CANNOT_BE_IN_FUTURE', 13, 1);
        return 1;
    end

    declare @MEMBERS table
    (
        CONSTITUENTID uniqueidentifier,
        ISPREVIOUSMEMBER bit,
        MEMBERID uniqueidentifier,
        MEMBERDATERANGEID uniqueidentifier
    )

    -- Add all constituents in the selection who are not current members, but may have been previous members

    insert into @MEMBERS
    select
        SELECTION.ID as CONSTITUENTID,
        case
            when GROUPMEMBER.ID is null then 0
            else 1
        end as ISPREVIOUSMEMBER,
        case
            -- If previous member, use existing ID

            -- If new member, make a new ID to use to create the record later

            when GROUPMEMBER.ID is null then newid()
            else GROUPMEMBER.ID
        end as MEMBERID,
        case
            -- If previous member, use existing date rangeID

            -- If new member, make a new date range ID to use to create the record later

            when GROUPMEMBER.ID is null then newid()
            else
                (
                    select top 1 GROUPMEMBERDATERANGE.ID
                    from dbo.GROUPMEMBERDATERANGE
                    where GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                    order by
                        case
                            when GROUPMEMBERDATERANGE.DATETO is null then 0
                            else 1
                        end,
                    GROUPMEMBERDATERANGE.DATETO desc
                )
        end as MEMBERDATERANGEID
    from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) as SELECTION
        left join dbo.GROUPMEMBER on SELECTION.ID = GROUPMEMBER.MEMBERID and GROUPMEMBER.GROUPID = @GROUPID
    where
        dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 0 and
        SELECTION.ID <> @GROUPID and
        (GROUPMEMBER.GROUPID is null or GROUPMEMBER.GROUPID = @GROUPID) and
        (
            -- no app user (skip security)

            @CURRENTAPPUSERID is null or
            -- sys admin (all records)

            @ISSYSADMIN = 1 or
            -- site security

            (
                exists(
                    select
                        1
                    from
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE as SECURITYVIEW
                        left join dbo.SITEPERMISSION
                            on SITEPERMISSION.APPUSERID = SECURITYVIEW.APPUSERID
                            and SITEPERMISSION.SYSTEMROLEID = SECURITYVIEW.SYSTEMROLEID
                    where
                        SECURITYVIEW.APPUSERID = @CURRENTAPPUSERID and
                        SECURITYVIEW.SYSTEMPRIVILEGECATALOGID = @ADDGROUPMEMBERSINBULKSYSTEMPRIVILEGEID and
                        SECURITYVIEW.GRANTORDENY = 1 and
                        (
                            SECURITYVIEW.SITESECURITYMODE = 0
                            or
                            (SITEPERMISSION.SITEID in (select SITEID from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(SELECTION.ID)) and (SECURITYVIEW.SITESECURITYMODE = 2 or SECURITYVIEW.SITESECURITYMODE = 3))
                            or
                            (SECURITYVIEW.SITESECURITYMODE = 1 and (select top 1 SITEID from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(SELECTION.ID)) is null)
                        )
                ) and
                -- constituent security

                (
                    -- all constituents

                    exists(
                        select
                            1
                        from
                            dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE as SECURITYVIEW
                        where
                            SECURITYVIEW.APPUSERID = @CURRENTAPPUSERID and
                            SECURITYVIEW.SYSTEMPRIVILEGECATALOGID = @ADDGROUPMEMBERSINBULKSYSTEMPRIVILEGEID and
                            SECURITYVIEW.GRANTORDENY = 1 and
                            SECURITYVIEW.RECORDSECURITYMODE = 0
                    ) or
                    -- specific and null constituent groups

                    SELECTION.ID in (
                        select
                            CSAA.CONSTITUENTID as ID
                        from
                            dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
                        where
                            CSAA.CONSTIT_SECURITY_ATTRIBUTEID in
                            (
                                select
                                    SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
                                from
                                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE as SECURITYVIEW
                                    inner join dbo.SYSTEMROLEAPPUSER
                                        on SYSTEMROLEAPPUSER.APPUSERID = SECURITYVIEW.APPUSERID
                                        and SYSTEMROLEAPPUSER.SYSTEMROLEID = SECURITYVIEW.SYSTEMROLEID
                                    inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY
                                        on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
                                where
                                    SECURITYVIEW.APPUSERID = @CURRENTAPPUSERID and
                                    SECURITYVIEW.SYSTEMPRIVILEGECATALOGID = @ADDGROUPMEMBERSINBULKSYSTEMPRIVILEGEID and
                                    SECURITYVIEW.GRANTORDENY = 1 and
                                    SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = 2
                            )
                        union all
                        select
                            ID
                        from
                            dbo.CONSTITUENT
                        where
                            exists(
                                select
                                    1
                                from
                                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SYSTEMPRIVILEGE as SECURITYVIEW
                                where
                                    SECURITYVIEW.APPUSERID = @CURRENTAPPUSERID and
                                    SECURITYVIEW.SYSTEMPRIVILEGECATALOGID = @ADDGROUPMEMBERSINBULKSYSTEMPRIVILEGEID and
                                    SECURITYVIEW.GRANTORDENY = 1 and
                                    SECURITYVIEW.RECORDSECURITYMODE = 1
                            ) and
                            ID not in (
                                select
                                    CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT.CONSTITUENTID
                                from
                                    dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT
                            )
                    )
                )
            )
        );

    select top 1 @ID = MEMBERS.MEMBERID
    from @MEMBERS MEMBERS
    order by MEMBERS.MEMBERID;

    declare @GROUPSTARTDATE datetime;
    select @GROUPSTARTDATE = STARTDATE
    from dbo.GROUPDATA
    where ID = @GROUPID;

    if @STARTDATE is not null and @GROUPSTARTDATE is not null
        if @STARTDATE < @GROUPSTARTDATE
        begin
            raiserror ('ERR_STARTDATE_CANNOT_BE_BEFORE_GROUPSTARTDATE', 13, 1);
            return 1;
        end

    -- The member date range start date should be set to the group start date

    if @STARTDATE is null and @USENULLSTARTDATE = 0
        set @STARTDATE = @GROUPSTARTDATE;

    if @COMMENTS is null
        set @COMMENTS = '';

    begin try
        -- Add group members who are not current or previous members

        insert into dbo.GROUPMEMBER
        (
            ID,
            GROUPID,
            MEMBERID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            NEWMEMBERS.MEMBERID,
            @GROUPID,
            NEWMEMBERS.CONSTITUENTID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from @MEMBERS NEWMEMBERS
        where NEWMEMBERS.ISPREVIOUSMEMBER = 0;

        -- Add date ranges for members who are not current or previous members

        insert into dbo.GROUPMEMBERDATERANGE
        (
            ID,
            GROUPMEMBERID,
            DATEFROM,
            DATETO,
            COMMENTS,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            NEWMEMBERS.MEMBERDATERANGEID,
            NEWMEMBERS.MEMBERID,
            @STARTDATE,
            @ENDDATE,
            @COMMENTS,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from @MEMBERS NEWMEMBERS
        where NEWMEMBERS.ISPREVIOUSMEMBER = 0;

        -- Update date ranges for previous members that are being reinstated

        update dbo.GROUPMEMBERDATERANGE set
            DATEFROM = @STARTDATE,
            DATETO = @ENDDATE,
            COMMENTS = @COMMENTS,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from @MEMBERS MEMBERS
        where
            MEMBERS.ISPREVIOUSMEMBER = 1 and
            MEMBERS.MEMBERDATERANGEID = GROUPMEMBERDATERANGE.ID;

        -- Update the role dates in case the member dates were changed

        update dbo.GROUPMEMBERROLE set
            STARTDATE =
                case
                    when GROUPMEMBERROLE.STARTDATE < GROUPMEMBERDATERANGE.DATEFROM then GROUPMEMBERDATERANGE.DATEFROM
                    when GROUPMEMBERROLE.STARTDATE > GROUPMEMBERDATERANGE.DATETO then GROUPMEMBERDATERANGE.DATETO
                    else GROUPMEMBERROLE.STARTDATE
                end,
            ENDDATE =
                case
                    when GROUPMEMBERROLE.ENDDATE > GROUPMEMBERDATERANGE.DATETO then GROUPMEMBERDATERANGE.DATETO
                    when GROUPMEMBERROLE.ENDDATE < GROUPMEMBERDATERANGE.DATEFROM then GROUPMEMBERDATERANGE.DATEFROM
                    else GROUPMEMBERROLE.ENDDATE
                end,
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from dbo.GROUPMEMBERROLE
            inner join dbo.GROUPMEMBER on GROUPMEMBER.ID = GROUPMEMBERROLE.GROUPMEMBERID
            inner join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
            inner join @MEMBERS MEMBERS on GROUPMEMBER.MEMBERID = MEMBERS.CONSTITUENTID
        where
            MEMBERS.ISPREVIOUSMEMBER = 1 and
            MEMBERS.MEMBERDATERANGEID = GROUPMEMBERDATERANGE.ID;

        -- If there is no existing primary member, make the first member the primary member

        if not exists(
            select 1
            from dbo.GROUPMEMBER
            where
                GROUPMEMBER.GROUPID = @GROUPID and
                GROUPMEMBER.ISPRIMARY = 1 and
                GROUPMEMBER.MEMBERID <> @ID
            )
        begin
            -- Do not set an expired group member to be the primary

            if @ENDDATE is null
                update dbo.GROUPMEMBER set
                    ISPRIMARY = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE 
                where
                    GROUPMEMBER.ID = @ID;
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch
end