USP_DATAFORMTEMPLATE_EDIT_REMOVESPOUSE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REMOVESPOUSE
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @REMOVEDATE date,
  @DISSOLVEREASONCODEID uniqueidentifier,
  @DISSOLVE bit,
  @RELATIONSHIPTYPECODEID uniqueidentifier,
  @RECIPROCALTYPECODEID uniqueidentifier
)
as
set nocount on;

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

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

if @REMOVEDATE > getdate()
  begin
    raiserror ('ERR_REMOVEDATE_CANNOT_BE_IN_FUTURE',13,1);
    return 1;
  end

declare @SPOUSEID uniqueidentifier;
set @SPOUSEID = null;

--Update spouse relationship
begin try
  update
    dbo.RELATIONSHIP
  set
    @SPOUSEID = RECIPROCALCONSTITUENTID,
    ISSPOUSE = 0,
    ENDDATE = @REMOVEDATE,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE,
    RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID,
    RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID
  from
    dbo.RELATIONSHIP
  where
    RELATIONSHIPCONSTITUENTID = @ID
    and ISSPOUSE = 1;

  exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID = @ID, @RECIPROCALCONSTITUENTID = @SPOUSEID,
          @ENDDATE = @REMOVEDATE, @CHANGEAGENTID = @CHANGEAGENTID;

  declare @HOUSEHOLDID uniqueidentifier;
  set @HOUSEHOLDID = null;

  --Get household
  select top(1)
    @HOUSEHOLDID = GM.GROUPID
  from 
    dbo.GROUPMEMBER as GM
    inner join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
  where 
    GM.MEMBERID = @ID
    and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1 
    and GD.GROUPTYPECODE = 0;

  if @DISSOLVE = 1
    begin
      set @DISSOLVE = 0;

      if not @HOUSEHOLDID is null
        begin
          set @DISSOLVE = 1;

          --Check that the household isn't already dissolved
          select
            @DISSOLVE = 0
          from
            dbo.DISSOLVEDGROUP
          where
            ID = @HOUSEHOLDID;

          --If the household is not dissolved check that the constituent 
          --and spouse are in the household and that the constituent or spouse is the primary member
          declare @CONSTITSPOUSEHOUSEHOLD tinyint;
          set @CONSTITSPOUSEHOUSEHOLD = 0;
          declare @CONSTITSPOUSEISPRIMARY tinyint;
          set @CONSTITSPOUSEISPRIMARY = 0;

          if @DISSOLVE = 1
            select
              @CONSTITSPOUSEHOUSEHOLD = count(GROUPMEMBER.ID),
              @CONSTITSPOUSEISPRIMARY = count(distinct ISPRIMARY)
            from
              dbo.GROUPMEMBER
              inner join 
              dbo.GROUPMEMBERDATERANGE
            on
              GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
            where
              GROUPID = @HOUSEHOLDID
              and GROUPMEMBERDATERANGE.DATETO is null
              and (MEMBERID = @ID
                or MEMBERID = @SPOUSEID);

            set @DISSOLVE = 0;
              if @CONSTITSPOUSEHOUSEHOLD = 2
                if @CONSTITSPOUSEISPRIMARY = 2 
                  set @DISSOLVE = 1;
        end

      if @DISSOLVE = 1
        begin
          insert into dbo.DISSOLVEDGROUP
          (
            ID, 
            DISSOLVEDDATE, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED, 
            DISSOLVEREASONCODEID
          )
          values
          (
            @HOUSEHOLDID, 
            @REMOVEDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE, 
            @DISSOLVEREASONCODEID
          );

          exec dbo.USP_GROUP_DISSOLVE @HOUSEHOLDID, @REMOVEDATE, @CHANGEAGENTID
        end
    end

  -- Update Household name
  update 
    dbo.[CONSTITUENT]
      set
        [KEYNAME] = left(dbo.UFN_NAMEFORMAT_FROMID(dbo.GROUPDATA.NAMEFORMATFUNCTIONID, PM.MEMBERID), 100),
        [DATECHANGED] = @CURRENTDATE,
        [CHANGEDBYID] = @CHANGEAGENTID
      from dbo.GROUPDATA
        inner join dbo.GROUPMEMBER PM on dbo.GROUPDATA.ID = PM.GROUPID and PM.ISPRIMARY <>0
      where 
        [GROUPDATA].ID = @HOUSEHOLDID and [CONSTITUENT].ID = @HOUSEHOLDID
        and dbo.GROUPDATA.NAMEFORMATFUNCTIONID is not null;                                    


  exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE 
    @ID, 
    @SPOUSEID, 
    @RELATIONSHIPTYPECODEID, 
    @RECIPROCALTYPECODEID, 
    @CHANGEAGENTID, 
    @CURRENTDATE

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

return 0;