USP_DATAFORMTEMPLATE_EDIT_BIOGRAPHICALDEMOGRAPHICUPDATE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BIOGRAPHICALDEMOGRAPHICUPDATE 
(
    @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,

    @CONSTITUENTID uniqueidentifier,
    @FULLNAME nvarchar(200),
    @CONSTITUENTTYPE integer,
    @GENDERCODE integer,

    @LASTNAME_NEW nvarchar(200),
    @LASTNAME_VALUE nvarchar(200),
    @LASTNAME_UPD bit,

    @FIRSTNAME_NEW nvarchar(200),
    @FIRSTNAME_VALUE nvarchar(200),
    @FIRSTNAME_UPD bit,

    @MIDDLENAME_NEW nvarchar(200),
    @MIDDLENAME_VALUE nvarchar(200),
    @MIDDLENAME_UPD bit,

    @SUFFIXCODEID_NEW uniqueidentifier,
    @SUFFIXCODEID_VALUE uniqueidentifier,
    @SUFFIX_NEW nvarchar(200),
    @SUFFIX_VALUE nvarchar(200),
    @SUFFIX_UPD bit,

    @TITLECODEID_NEW uniqueidentifier,
    @TITLECODEID_VALUE uniqueidentifier,
    @TITLE_NEW nvarchar(200),
    @TITLE_VALUE nvarchar(200),
    @TITLE_UPD bit,

    @BIRTHDATE_NEW dbo.UDT_FUZZYDATE,
    @BIRTHDATE_VALUE dbo.UDT_FUZZYDATE,
    @BIRTHDATE_UPD bit,

    @MARITALSTATUSCODEID_NEW uniqueidentifier,
    @MARITALSTATUSCODEID_VALUE uniqueidentifier,
    @MARITALSTATUS_NEW nvarchar(200),
    @MARITALSTATUS_VALUE nvarchar(200),
    @MARITALSTATUS_UPD bit,

    @MOSAIC_NEW nvarchar(200),
    @MOSAIC_VALUE nvarchar(200),
    @MOSAIC_UPD bit,

    @HHINCOMECODEID_NEW uniqueidentifier,
    @HHINCOME_NEW nvarchar(200),
    @HHINCOME_VALUE nvarchar(200),
    @HHINCOME_UPD bit,

    @DISCRETIONARYSPENDING_NEW money,
    @DISCRETIONARYSPENDING_VALUE money,
    @DISCRETIONARYSPENDING_UPD bit,

    @HASSPOUSE bit,
    @HHMEMBERS xml,
    @SPOUSEID uniqueidentifier,
    @SPOUSE_LASTNAME nvarchar(200),
    @SPOUSE_FIRSTNAME nvarchar(200),
    @SPOUSE_MIDDLENAME nvarchar(200),
    @SPOUSE_TITLECODEID uniqueidentifier,
    @SPOUSE_TITLE2CODEID uniqueidentifier,
    @SPOUSE_SUFFIXCODEID uniqueidentifier,
    @SPOUSE_NICKNAME nvarchar(200),
    @SPOUSE_MAIDENNAME nvarchar(200),
    @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,
    @SPOUSE_GENDERCODE integer,
    @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
    @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier,
    @SPOUSERELATIONSHIPTYPECODE uniqueidentifier,
    @SPOUSE_MARITALSTATUSCODEID uniqueidentifier,
    @ISSPOUSEMATCHCODE bit
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        -- handle updating the data

      --This is the workaround we use on USP_TACONSTITUENT_ADD for spouse belongs to a house error msg 
      if exists(select 1 from GROUPMEMBER
                        left join GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                        left join GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                    where
                        @SPOUSEID = MEMBERID
                        and GROUPDATA.GROUPTYPECODE = 0
                        and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE) 
            )
      begin  
        declare @ERRMSG nvarchar(400);
        declare @SPOUSENAME nvarchar(100);
        declare @SPOUSEHOUSEHOLDNAME nvarchar(100);

        select @SPOUSENAME = NAME
        from CONSTITUENT
        where @SPOUSEID = CONSTITUENT.ID

        select @SPOUSEHOUSEHOLDNAME = CONSTITUENT.NAME 
        from GROUPMEMBER
              left join GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
              left join CONSTITUENT on CONSTITUENT.ID = GROUPMEMBER.GROUPID
        where
          @SPOUSEID = MEMBERID
          and GROUPDATA.GROUPTYPECODE = 0

        select @ERRMSG = @SPOUSENAME + ' is already a member of the "' + @SPOUSEHOUSEHOLDNAME + '" household. If this is inaccurate, please visit ' + @SPOUSENAME + '''s record to correct this information.'

        raiserror(@ERRMSG, 13, 1);
      end

    if @LASTNAME_UPD = 1
      set @LASTNAME_VALUE = @LASTNAME_NEW

    if @FIRSTNAME_UPD = 1
      set @FIRSTNAME_VALUE = @FIRSTNAME_NEW

    if @MIDDLENAME_UPD = 1
      set @MIDDLENAME_VALUE = @MIDDLENAME_NEW

    if @SUFFIX_UPD = 1
      set @SUFFIXCODEID_VALUE = @SUFFIXCODEID_NEW

    if @TITLE_UPD = 1
      set @TITLECODEID_VALUE = @TITLECODEID_NEW

    if @BIRTHDATE_UPD = 1
      set @BIRTHDATE_VALUE = @BIRTHDATE_NEW

    if @MARITALSTATUS_UPD = 1
      set @MARITALSTATUSCODEID_VALUE = @MARITALSTATUSCODEID_NEW

    --Update fields as they have been selected on the form
    update
      dbo.CONSTITUENT
    set
      KEYNAME = @LASTNAME_VALUE,
      FIRSTNAME = @FIRSTNAME_VALUE,
      MIDDLENAME = @MIDDLENAME_VALUE,
      SUFFIXCODEID = @SUFFIXCODEID_VALUE,
      TITLECODEID = @TITLECODEID_VALUE,
      BIRTHDATE = @BIRTHDATE_VALUE,
      MARITALSTATUSCODEID = @MARITALSTATUSCODEID_VALUE
    where
      ID = @CONSTITUENTID

    --Update spouse as it has been selected on the form
    if @HASSPOUSE = 0 and ISNULL(@SPOUSE_LASTNAME, '') <> ''
    begin

      if @SPOUSEID is null
      begin

        declare @SPOUSE_FIRSTNAME_SHORT nvarchar(50),
                @SPOUSE_MIDDLENAME_SHORT nvarchar(50);

        select @SPOUSE_FIRSTNAME_SHORT = left(@SPOUSE_FIRSTNAME, 50),
              @SPOUSE_MIDDLENAME_SHORT = left(@SPOUSE_MIDDLENAME, 50);

        exec USP_DATAFORMTEMPLATE_INDIVIDUALRECORDSIMPLIFIED_ADD 
                @ID = @SPOUSEID output, 
                @CURRENTAPPUSERID=@CURRENTAPPUSERID, 
                @CHANGEAGENTID=@CHANGEAGENTID, 
                @LASTNAME=@SPOUSE_LASTNAME,
                @FIRSTNAME= @SPOUSE_FIRSTNAME_SHORT,
                @MIDDLENAME= @SPOUSE_MIDDLENAME_SHORT,
                @TITLECODEID=@SPOUSE_TITLECODEID,
                @SUFFIXCODEID=@SPOUSE_SUFFIXCODEID,
                @GENDERCODE=@SPOUSE_GENDERCODE,
                @BIRTHDATE=@SPOUSE_BIRTHDATE;

        update CONSTITUENT
        set MARITALSTATUSCODEID = @SPOUSE_MARITALSTATUSCODEID
        where ID = @SPOUSEID;
      end

      declare @UPDATERELATIONSHIPID uniqueidentifier = null;
      select @UPDATERELATIONSHIPID = ID 
      from RELATIONSHIP
      where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
          and RECIPROCALCONSTITUENTID = @SPOUSEID
          and RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID
          and RECIPROCALTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID

      exec USP_DATAFORMTEMPLATE_ADD_CONSITUENTSPOUSE_2
              @CURRENTAPPUSERID = @CURRENTAPPUSERID,
              @CHANGEAGENTID = @CHANGEAGENTID,
              @CONSTITUENTID = @CONSTITUENTID,
              @RECIPROCALCONSTITUENTID = @SPOUSEID,
              @RECIPROCALTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID,
              @RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID,
              @UPDATERELATIONSHIPID = @UPDATERELATIONSHIPID;

    end

    --still needs to update model scores  

    return 1
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;