USP_GROUPMEMBERADD

CREATE procedure dbo.USP_GROUPMEMBERADD
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @GROUPID uniqueidentifier,
  @MEMBERID uniqueidentifier = null,
  @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
)
as
  set nocount on;

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

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


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

  if @STARTDATE > @CURRENTDATE
  begin
    raiserror ('ERR_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 @GROUPTYPECODE tinyint;
  declare @GROUPSTARTDATE datetime;
  declare @EXISTINGID uniqueidentifier;
  declare @EXISTINGDATERANGEID uniqueidentifier;

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

  --Find the existing groupmember record and groupmemberdaterange for this constituent if they exist 

  --(constituent may be a previous member of this group)

  select
    @EXISTINGID = ID
  from
    dbo.GROUPMEMBER
  where
    GROUPID = @GROUPID and
    MEMBERID = @MEMBERID;

  select top 1
    @EXISTINGDATERANGEID = ID
  from
    dbo.GROUPMEMBERDATERANGE 
  where
    GROUPMEMBERID = @EXISTINGID
  order by 
    case
      when DATETO is null then 0
      else 1
    end, 
    DATETO desc

  if dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(@EXISTINGID) = 1
    raiserror('CK_GROUPMEMBER_ALREADYINGROUP', 13, 1);

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

  select
    @GROUPTYPECODE = GROUPTYPECODE,
    @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

  --If the group is a Household GROUPTYPECODE = 0 (built in type) then the member date range start date should be null

  --Otherwise, the member date range start date should be set to the group start date

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

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

  begin try
    if @EXISTINGID is null
      insert into dbo.GROUPMEMBER
      (
        ID,
        GROUPID,
        MEMBERID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        @ID,
        @GROUPID,
        @MEMBERID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );
    else
      set @ID = @EXISTINGID;

    if @EXISTINGDATERANGEID is null
      insert into dbo.GROUPMEMBERDATERANGE
      (
        ID,
        GROUPMEMBERID,
        DATEFROM,
        DATETO,
        COMMENTS,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        newid(),
        @ID,
        @STARTDATE,
        @ENDDATE,
        @COMMENTS,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );
    else
    begin
      --Extend the existing date range

      update dbo.GROUPMEMBERDATERANGE set
        DATEFROM = @STARTDATE,
        DATETO = @ENDDATE,
        COMMENTS = @COMMENTS,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @EXISTINGDATERANGEID;

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

      exec dbo.USP_GROUPMEMBERROLE_UPDATEDATES @ID, @CHANGEAGENTID;
    end

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

    if not exists(select 1 from dbo.GROUPMEMBER GM where GM.GROUPID = @GROUPID and GM.ISPRIMARY = 1 and GM.MEMBERID <> @MEMBERID)
      begin
        --we want to make sure that we're not setting an expired group member to be the primary

        if @ENDDATE is null
          update dbo.GROUPMEMBER set
            ISPRIMARY = 1,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE 
          where
            ID = @ID;
      end


    -- GROUPTYPECODE = 0 indicates household

    if @GROUPTYPECODE = 0 and @RECOGNIZEMEMBERFORHOUSEHOLD = 1
    begin
      -- Update an existing recognition default entry between the two constituents

      -- to have a matchfactor of 100 if it already exists.  Otherwise, create the entry.

      update dbo.REVENUERECOGNITIONDEFAULT set
        MATCHFACTOR = 100,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        SOURCECONSTITUENTID = @GROUPID and
        RECIPIENTCONSTITUENTID = @MEMBERID;

      if @@ROWCOUNT = 0
      begin
        insert into dbo.REVENUERECOGNITIONDEFAULT
        (
          SOURCECONSTITUENTID,
          RECIPIENTCONSTITUENTID,
          MATCHFACTOR,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )
        values
        (
          @GROUPID,
          @MEMBERID,
          100,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        )
      end
    end
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;