USP_DATAFORMTEMPLATE_EDIT_COMMITTEE_2

The save procedure used by the edit dataform template "Committee Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(300)	IN	Description
@GIVESANONYMOUSLY	bit	IN	Gives anonymously
@PICTURE	varbinary	IN	Image
@PICTURETHUMBNAIL	varbinary	IN	Image thumbnail
@PICTURECHANGED	bit	IN	Picture changed
@WEBADDRESS	UDT_WEBADDRESS	IN	Website
@GROUPTYPEID	uniqueidentifier	IN	Group type
@STARTDATE	datetime	IN	Start date
@CANCOORDINATEEVENTS	bit	IN	Can coordinate events
@CANSOLICITREVENUE	bit	IN	Can solicit revenue and set fundraising goals
@CANSETCOMMITTEEGOALS	bit	IN	Can set committee goals
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_COMMITTEE_2
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @NAME nvarchar(100),
                        @DESCRIPTION nvarchar(300),
                        @GIVESANONYMOUSLY bit,
                        @PICTURE varbinary(max),
                        @PICTURETHUMBNAIL varbinary(max),
                        @PICTURECHANGED bit,
                        @WEBADDRESS dbo.UDT_WEBADDRESS,
                        @GROUPTYPEID uniqueidentifier,
                        @STARTDATE datetime,
                        @CANCOORDINATEEVENTS bit,
                        @CANSOLICITREVENUE bit,
                        @CANSETCOMMITTEEGOALS bit
                    ) as
                        set nocount on;

                        declare @CURRENTDATE datetime;

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

                        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

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

                        begin try
                            if @PICTURECHANGED = 1
                                update 
                                    dbo.[CONSTITUENT]
                                set
                                    [KEYNAME] = @NAME,
                                    [ISORGANIZATION] = 0,
                                    [GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,
                                    [PICTURE] = @PICTURE,
                                    [PICTURETHUMBNAIL] = @PICTURETHUMBNAIL,
                                    [WEBADDRESS] = @WEBADDRESS,
                                    [DATECHANGED] = @CURRENTDATE,
                                    [CHANGEDBYID] = @CHANGEAGENTID
                                where 
                                    [CONSTITUENT].ID = @ID;

                            else
                                update 
                                    dbo.[CONSTITUENT] 
                                set
                                    [KEYNAME] = @NAME,
                                    [ISORGANIZATION] = 0,
                                    [WEBADDRESS] = @WEBADDRESS,
                                    [GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,
                                    [DATECHANGED] = @CURRENTDATE,
                                    [CHANGEDBYID] = @CHANGEAGENTID
                                where 
                                    [CONSTITUENT].ID = @ID;

                            update
                                dbo.GROUPDATA
                            set
                                DESCRIPTION = @DESCRIPTION,
                                GROUPTYPEID = @GROUPTYPEID,
                                STARTDATE = @STARTDATE,
                                DATECHANGED = @CURRENTDATE,
                                CHANGEDBYID = @CHANGEAGENTID
                            where
                                ID = @ID;

                            -- Update member's start date to be equal to the group's start date if the member's start date was

                            -- on or before the previous group start date

                            update
                                dbo.GROUPMEMBERDATERANGE
                            set
                                DATEFROM = @STARTDATE,
                                DATETO = case when DATETO < @STARTDATE then @STARTDATE else DATETO end,
                                DATECHANGED = @CURRENTDATE,
                                CHANGEDBYID = @CHANGEAGENTID
                            where
                                GROUPMEMBERID in (select ID from dbo.GROUPMEMBER where GROUPID = @ID)
                                and ((DATEFROM is null and @PREVIOUSSTARTDATE is null)
                                        or (DATEFROM = @PREVIOUSSTARTDATE)
                                or (DATEFROM < @STARTDATE))

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

                            exec dbo.USP_GROUPMEMBERROLE_UPDATEDATESFORGROUP @ID, @CHANGEAGENTID;

                            update
                                dbo.[COMMITTEE]
                            set
                                [CANCOORDINATEEVENTS] = @CANCOORDINATEEVENTS,
                                [CANSOLICITREVENUE] = @CANSOLICITREVENUE,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CURRENTDATE,
                                [CANSETCOMMITTEEGOALS] = @CANSETCOMMITTEEGOALS
                            where
                                [ID] = @ID;

                            if @@ROWCOUNT = 0
                                insert into dbo.COMMITTEE(ID, CANCOORDINATEEVENTS, CANSOLICITREVENUE, CANSETCOMMITTEEGOALS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@ID, @CANCOORDINATEEVENTS, @CANSOLICITREVENUE, @CANSETCOMMITTEEGOALS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

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

                        return 0;