USP_GROUPADD

Adds a group. As of Aug 2009, is called from Group.Add and Committee.Add.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(300)	IN
@GROUPTYPEID	uniqueidentifier	IN
@GIVESANONYMOUSLY	bit	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@COUNTRYID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@STATEID	uniqueidentifier	IN
@POSTCODE	nvarchar(12)	IN
@PHONETYPECODEID	uniqueidentifier	IN
@NUMBER	nvarchar(100)	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@MEMBERS	xml	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@PRIMARYCONTACTID	uniqueidentifier	IN
@NEWMEMBERSEARCHID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@CHANGEAGENTID	uniqueidentifier	IN
@DONOTMAIL	bit	IN
@DONOTMAILREASONCODEID	uniqueidentifier	IN
@SKIP_ADDING_SECURITYGROUPS	bit	IN
@OMITFROMVALIDATION	bit	IN
@CART	nvarchar(10)	IN
@DPC	nvarchar(8)	IN
@LOT	nvarchar(5)	IN
@COUNTYCODEID	uniqueidentifier	IN
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@LASTVALIDATIONATTEMPTDATE	datetime	IN
@VALIDATIONMESSAGE	nvarchar(200)	IN
@CERTIFICATIONDATA	int	IN
@SKIP_ADDING_SITES	bit	IN
@DONOTEMAIL	bit	IN
@DONOTCALL	bit	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@ISCONFIDENTIAL	bit	IN
@DONOTCALLREASONCODEID	uniqueidentifier	IN
@PHONE_ISCONFIDENTIAL	bit	IN
@ADDTYPE	tinyint	IN
@IDSETREGISTERID	uniqueidentifier	IN
@DONOTEMAILREASONCODEID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.USP_GROUPADD
(
    @ID uniqueidentifier output,
    @CURRENTAPPUSERID uniqueidentifier,
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(300) = '',
    @GROUPTYPEID uniqueidentifier,
    @GIVESANONYMOUSLY bit = 0,
    @ADDRESSTYPECODEID uniqueidentifier = null,
    @COUNTRYID uniqueidentifier = null,
    @ADDRESSBLOCK nvarchar(150) = '',
    @CITY nvarchar(50) = '',
    @STATEID uniqueidentifier = null,
    @POSTCODE nvarchar(12) = '',
    @PHONETYPECODEID uniqueidentifier = null,
    @NUMBER nvarchar(100) = '',
    @EMAILADDRESSTYPECODEID uniqueidentifier = null,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
    @MEMBERS xml = null,
    @WEBADDRESS dbo.UDT_WEBADDRESS = '',
    @PRIMARYCONTACTID uniqueidentifier = null,
    @NEWMEMBERSEARCHID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @DONOTMAIL bit = 0,
    @DONOTMAILREASONCODEID uniqueidentifier = null,
    @SKIP_ADDING_SECURITYGROUPS bit=0,
    @OMITFROMVALIDATION bit = 0,
    @CART nvarchar(10) = '',
    @DPC nvarchar(8) = '',
    @LOT nvarchar(5) = '',
    @COUNTYCODEID uniqueidentifier = null,
    @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
    @LASTVALIDATIONATTEMPTDATE datetime = null,
    @VALIDATIONMESSAGE nvarchar(200) = '',
    @CERTIFICATIONDATA integer = 0,
    @SKIP_ADDING_SITES bit=0,
    @DONOTEMAIL bit = 0,
    @DONOTCALL bit = 0,
    @INFOSOURCECODEID uniqueidentifier = null,
    @ISCONFIDENTIAL bit = 0,
    @DONOTCALLREASONCODEID uniqueidentifier = null,
    @PHONE_ISCONFIDENTIAL bit = 0,
    @ADDTYPE tinyint = 0,
    @IDSETREGISTERID uniqueidentifier = null,
    @DONOTEMAILREASONCODEID uniqueidentifier = null
)
as
    set nocount on;

    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;
    declare @APPUSER_IN_NONSITEROLE bit;
    declare @APPUSER_IN_NOSITEROLE bit;

    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    if @STARTDATE is not null
        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)

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

    if @ID is null
        set @ID = newid();                            

    if @DONOTMAIL = 0
        set @DONOTMAILREASONCODEID = null

    if @DONOTCALL = 0
        set @DONOTCALLREASONCODEID = null

    if @DONOTEMAIL = 0
        set @DONOTEMAILREASONCODEID = null

    begin try
        insert into dbo.CONSTITUENT
        (
            ID,
            KEYNAME, 
            GIVESANONYMOUSLY, 
            ISGROUP, 
            WEBADDRESS,
            ISCONSTITUENT,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        values
        (
            @ID, 
            @NAME, 
            @GIVESANONYMOUSLY, 
            1, 
            @WEBADDRESS,
            1,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        )

        insert into dbo.GROUPDATA
        (
            ID, 
            GROUPTYPECODE, 
            GROUPTYPEID, 
            STARTDATE, 
            DESCRIPTION, 
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        values
        (
            @ID, 
            1, -- Custom group

            @GROUPTYPEID, 
            @STARTDATE, 
            @DESCRIPTION, 
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        )

                if
                (
                        (@STATEID is not null)
                        or (coalesce(@ADDRESSBLOCK,'') <> '') 
                        or (coalesce(@CITY,'') <> '') 
                        or (coalesce(@POSTCODE,'') <> '') 
                        or (@ADDRESSTYPECODEID is not null) 
                        or (@COUNTRYID is not null)
                        or (@DONOTMAIL = 1)
                        or (@DONOTMAILREASONCODEID is not null)
                )
                begin
                declare @ADDRESSID uniqueidentifier;
                set @ADDRESSID = newID();

                insert into dbo.ADDRESS
                (
                    ID,
                    CONSTITUENTID, 
                    ADDRESSTYPECODEID, 
                    ISPRIMARY, 
                    COUNTRYID, 
                    STATEID, 
                    ADDRESSBLOCK, 
                    CITY, 
                    POSTCODE, 
                    CART,
                    DPC,
                    LOT,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
                    DONOTMAIL, 
                    DONOTMAILREASONCODEID,
                ISCONFIDENTIAL
                )
                values
                (
                    @ADDRESSID,
                    @ID, 
                    @ADDRESSTYPECODEID, 
                    1, 
                    @COUNTRYID, 
                    @STATEID, 
                    @ADDRESSBLOCK, 
                    @CITY, 
                    @POSTCODE, 
                    @CART,
                    @DPC,
                    @LOT,                        
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
                    @DONOTMAIL,
                    @DONOTMAILREASONCODEID,
                    @ISCONFIDENTIAL
                )

                insert into dbo.ADDRESSVALIDATIONUPDATE
                (
                    [ID],
                    [COUNTYCODEID],
                    [CONGRESSIONALDISTRICTCODEID],
                    [LASTVALIDATIONATTEMPTDATE],
                    [VALIDATIONMESSAGE],
                    [CERTIFICATIONDATA],
                    [OMITFROMVALIDATION],
                    [INFOSOURCECODEID],
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED]
                )
                values
                (
                    @ADDRESSID,
                    @COUNTYCODEID,
                    @CONGRESSIONALDISTRICTCODEID,
                    @CURRENTDATE,
                    @VALIDATIONMESSAGE,
                    @CERTIFICATIONDATA,
                    @OMITFROMVALIDATION,
                    @INFOSOURCECODEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                );
                end

                declare @PHONE_COUNTRYID uniqueidentifier = @COUNTRYID
                if @COUNTRYID is null
                    exec @PHONE_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

        if coalesce(@NUMBER, '') <> ''
        begin
            insert into dbo.PHONE
            (
                CONSTITUENTID, 
                PHONETYPECODEID, 
                NUMBER, 
                COUNTRYID,
                ISPRIMARY, 
                DONOTCALL,
                INFOSOURCECODEID,
                DONOTCALLREASONCODEID,
                ISCONFIDENTIAL,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            values
            (
                @ID, 
                @PHONETYPECODEID, 
                @NUMBER, 
                @PHONE_COUNTRYID,
                1, 
                @DONOTCALL,
                @INFOSOURCECODEID,
                @DONOTCALLREASONCODEID,
                @PHONE_ISCONFIDENTIAL,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            )

            if @PRIMARYCONTACTID is not null
                exec dbo.USP_GROUP_COPYPHONE @ID, @PRIMARYCONTACTID, @NUMBER, @CHANGEAGENTID;
        end

        if coalesce(@EMAILADDRESS, '') <> ''
        begin
            insert into dbo.EMAILADDRESS
            (
                CONSTITUENTID, 
                EMAILADDRESSTYPECODEID, 
                EMAILADDRESS, 
                ISPRIMARY, 
                DONOTEMAIL,
                INFOSOURCECODEID,
                DONOTEMAILREASONCODEID,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            values
            (
                @ID, 
                @EMAILADDRESSTYPECODEID, 
                @EMAILADDRESS, 
                1, 
                @DONOTEMAIL,
                @INFOSOURCECODEID,
                @DONOTEMAILREASONCODEID,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            )
        end

        if @ADDTYPE = 0 -- Add single member

        begin
            declare @MEMBERSTOINSERT table
            (
                GROUPMEMBERID uniqueidentifier,
                MEMBERID uniqueidentifier,
                ISPRIMARY bit
            )

            if (@ISADMIN = 0 and
                    (
                        (@APPUSER_IN_NONRACROLE = 0 
                        and exists( 
                            select T.c.value('(MEMBERID)[1]','uniqueidentifier')
                            from @MEMBERS.nodes('/MEMBERS/ITEM') T(c)
                            where
                                T.c.value('(MEMBERID)[1]','uniqueidentifier') is not null
                                and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(
                                @CURRENTAPPUSERID, T.c.value('(MEMBERID)[1]','uniqueidentifier'), @APPUSER_IN_NOSECGROUPROLE) = 0))
                        or
                        (@APPUSER_IN_NONSITEROLE = 0 
                        and exists( 
                            select T.c.value('(MEMBERID)[1]','uniqueidentifier')
                            from @MEMBERS.nodes('/MEMBERS/ITEM') T(c)
                            where
                                T.c.value('(MEMBERID)[1]','uniqueidentifier') is not null
                                and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(
                                @CURRENTAPPUSERID, T.c.value('(MEMBERID)[1]','uniqueidentifier'), @APPUSER_IN_NOSITEROLE) = 0))
                    )
            )                                
            begin
                raiserror ('ERR_MEMBER_NOACCESS',13,1);
                return 0;
            end

            insert into @MEMBERSTOINSERT
            (
                GROUPMEMBERID,
                MEMBERID,
                ISPRIMARY
            )
            select
                newid(),
                T.c.value('(MEMBERID)[1]','uniqueidentifier'),
                T.c.value('(ISPRIMARY)[1]','bit')
            from @MEMBERS.nodes('/MEMBERS/ITEM') T(c)
            where T.c.value('(MEMBERID)[1]','uniqueidentifier') is not null

            if (exists(select top 1 MEMBERID from @MEMBERSTOINSERT group by MEMBERID having count(*) > 1))
            begin
                raiserror('ERR_MEMBERSUNIQUE', 13, 1);
                return 1;
            end

            insert into dbo.GROUPMEMBER
            (
                ID, 
                GROUPID, 
                MEMBERID, 
                ISPRIMARY,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                GROUPMEMBERID,
                @ID,
                MEMBERID,
                0,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @MEMBERSTOINSERT

            insert into dbo.GROUPMEMBERDATERANGE
            (
                GROUPMEMBERID, 
                DATEFROM, 
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                GROUPMEMBERID,
                @STARTDATE,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @MEMBERSTOINSERT            

            declare @PRIMARYMEMBERID uniqueidentifier
            select @PRIMARYMEMBERID=GROUPMEMBERID
            from @MEMBERSTOINSERT
            where ISPRIMARY=1

            if @PRIMARYMEMBERID is not null
            begin
                update dbo.GROUPMEMBER
                set
                    ISPRIMARY = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    ID = @PRIMARYMEMBERID
            end
        end
        else
        begin
                        exec dbo.USP_GROUPMEMBERADDBULK @ID, @CHANGEAGENTID, @ID, @IDSETREGISTERID, default, @STARTDATE, 1, default, default, @CURRENTAPPUSERID;
        end

        /*Start security groups*/
        if coalesce(@SKIP_ADDING_SECURITYGROUPS,0) = 0
        begin
            exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
                    @APPUSERID =@CURRENTAPPUSERID,
                    @DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
                    @CONSTITUENTID  =@ID,
                    @DATEADDEDTOUSE =@CURRENTDATE,
                    @CHANGEAGENTID = @CHANGEAGENTID;
        end
        /*end security groups*/

        /*Start sites*/
        if coalesce(@SKIP_ADDING_SITES,0) = 0
        begin
            exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
                    @APPUSERID =@CURRENTAPPUSERID,
                    @DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
                    @CONSTITUENTID  =@ID,
                    @DATEADDEDTOUSE =@CURRENTDATE,
                    @CHANGEAGENTID = @CHANGEAGENTID;
        end
        /*end sites*/

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

    return 0