USP_RELATIONSHIPS_MANAGELIFECHANGES

CREATE procedure dbo.USP_RELATIONSHIPS_MANAGELIFECHANGES
(
  @RELATIONSHIPID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier, 
  @SPOUSEID uniqueidentifier, 
  @CHANGEAGENTID uniqueidentifier
)
as
begin
  set nocount on;

  declare @UPDATEINDIVIDUALRELATIONSHIPS bit;
  select 
    @UPDATEINDIVIDUALRELATIONSHIPS = UPDATEINDIVIDUALRELATIONSHIPS
  from dbo.UFN_MARRIAGEOPTION_GETRULES();

  if @UPDATEINDIVIDUALRELATIONSHIPS = 1
  begin
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    declare @SPOUSERELATIONSHIPTYPECODEID uniqueidentifier;
    declare @CHILDID uniqueidentifier;
    declare @STARTDATE datetime;
    declare @ENDDATE datetime;

    declare @RECIPROCAL bit = 0;
    if @RELATIONSHIPID is null --need to get reciprocal relationship to correctly set marriage relationship

      select 
        @RECIPROCAL = 1,
        @RELATIONSHIPID = ID 
      from dbo.RELATIONSHIP 
      where ISSPOUSE = 1 and
        RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and RECIPROCALCONSTITUENTID = @SPOUSEID;

    declare @GENDERSPOUSE int = null;
    declare @GENDERCODEID uniqueidentifier = null;
    declare @GENDERDESCRIPTION varchar(100);
    select @GENDERCODEID = GENDERCODEID from dbo.CONSTITUENT where ID = @SPOUSEID

    if (@GENDERCODEID is null or @GENDERCODEID = '00000000-0000-0000-0000-000000000000')
      begin
        set @GENDERSPOUSE=0;
      end
      else
      begin
        select @GENDERDESCRIPTION = ltrim(rtrim(DEFAULTREASON)) from dbo.GENDERCODEDEFAULTMAPPING where GENDERCODEID=@GENDERCODEID;

        set @GENDERSPOUSE = case when @GENDERDESCRIPTION ='Unknown' then 0 
                            when @GENDERDESCRIPTION='Male' then 1
                            when @GENDERDESCRIPTION='Female' then 2
                          else 3
                      end;
      end

    --join to life changes configuration to get new typecode for parent and child relationship; exclude relationships created by the marriage process

    declare MARRIAGERELATIONSHIPCURSOR cursor local fast_forward for
      select 
        MARRIAGERELATIONSHIP.SPOUSERELATIONSHIPTYPECODEID, --new relationship on spouse side

        RELATIONSHIP.RECIPROCALCONSTITUENTID, --child

        RELATIONSHIP.STARTDATE,
        RELATIONSHIP.ENDDATE
      from dbo.RELATIONSHIP
      inner join dbo.MARRIAGERELATIONSHIPASSOCIATION 
        on MARRIAGERELATIONSHIPASSOCIATION.RELATIONSHIPTYPECODEID = RELATIONSHIP.RECIPROCAlTYPECODEID
      inner join dbo.MARRIAGERELATIONSHIP 
        on MARRIAGERELATIONSHIPASSOCIATION.MARRIAGERELATIONSHIPID = MARRIAGERELATIONSHIP.ID 
      where 
        (MARRIAGERELATIONSHIP.SPOUSEGENDERCODE = 3 or --any

         MARRIAGERELATIONSHIP.SPOUSEGENDERCODE = @GENDERSPOUSE) and
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
        (RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE >= @CURRENTDATE) and
        not exists (select ID from dbo.RELATIONSHIPBYMARRIAGE where RELATIONSHIPBYMARRIAGE.RELATIONSHIPID = RELATIONSHIP.ID) and
        not exists (select ID from dbo.DECEASEDCONSTITUENT where DECEASEDCONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID); 

    open MARRIAGERELATIONSHIPCURSOR;   

    fetch next from MARRIAGERELATIONSHIPCURSOR into @SPOUSERELATIONSHIPTYPECODEID, @CHILDID, @STARTDATE, @ENDDATE;

    set @CURRENTDATE = getdate();

    while (@@FETCH_STATUS = 0)
    begin                    
      --this returns a list of type codes; DESCRIPTION is set to 1 if there are commonly used relationship type combinations; we grab the first to match behavior in UI

      declare @RELATIONSHIPTYPERELATESTO table(VALUE uniqueidentifier, LABEL nvarchar(255), DESCRIPTION bit);
      delete @RELATIONSHIPTYPERELATESTO;

      insert into @RELATIONSHIPTYPERELATESTO 
        exec dbo.USP_SIMPLEDATALIST_RELATIONSHIPTYPERELATESTO 0, @CHILDID, @SPOUSERELATIONSHIPTYPECODEID, null, 0, null; 

      declare @RECIPROCALTYPECODEID uniqueidentifier = null;
      select 
        top 1 @RECIPROCALTYPECODEID = VALUE
      from @RELATIONSHIPTYPERELATESTO
      where DESCRIPTION = 1

      if not @RECIPROCALTYPECODEID is null --do nothing if there are no commonly used matches

      begin
        declare @SETID uniqueidentifier = newid();

        begin try
          insert into dbo.RELATIONSHIPSET
          (ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values
          (@SETID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          declare @RELATIONSHIPID_CHILD uniqueidentifier = newid();
          insert into dbo.RELATIONSHIP
          (
            ID,
            RELATIONSHIPCONSTITUENTID,
            RECIPROCALCONSTITUENTID,
            RELATIONSHIPTYPECODEID,
            RECIPROCALTYPECODEID,
            RELATIONSHIPSETID,
            STARTDATE,
            ENDDATE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          values
          (
            @RELATIONSHIPID_CHILD,
            @SPOUSEID,
            @CHILDID, 
            @SPOUSERELATIONSHIPTYPECODEID,
            @RECIPROCALTYPECODEID, 
            @SETID,
            @STARTDATE,
            @ENDDATE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );

          exec dbo.USP_RELATIONSHIPBYMARRIAGE_CREATE @RELATIONSHIPID, @RELATIONSHIPID_CHILD, @CHANGEAGENTID, @CURRENTDATE;
        end try
          begin catch
          --do nothing

          end catch
      end

      fetch next from MARRIAGERELATIONSHIPCURSOR into @SPOUSERELATIONSHIPTYPECODEID, @CHILDID, @STARTDATE, @ENDDATE;
    end

    --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

    close MARRIAGERELATIONSHIPCURSOR;        
    deallocate MARRIAGERELATIONSHIPCURSOR;

    --create additional relationships between "siblings"

    exec dbo.USP_RELATIONSHIPS_MANAGELIFECHANGES_ADDITIONAL @RELATIONSHIPID, @CONSTITUENTID, @SPOUSEID, @CHANGEAGENTID;

    if @RECIPROCAL = 0 --need to add the reciprocal relationships by marriage

      exec dbo.USP_RELATIONSHIPS_MANAGELIFECHANGES null, @SPOUSEID, @CONSTITUENTID, @CHANGEAGENTID;
  end
end