USP_DATAFORMTEMPLATE_EDIT_BIOGRAPHICALDEMOGRAPHIC

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BIOGRAPHICALDEMOGRAPHIC 
(
      @ID uniqueidentifier,
      @CHANGEAGENTID uniqueidentifier = null,
      @FIRSTNAME nvarchar(100),
    @MIDDLENAME nvarchar(100),
    @LASTNAME nvarchar(100),
      @SUFFIXCODEID uniqueidentifier,
    @TITLECODEID uniqueidentifier,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    @MARITALSTATUSCODEID uniqueidentifier,
    @OCCUPATION nvarchar(100),
    @CNOTES nvarchar(1024),
    @HHMEMBER_FIRSTNAME nvarchar(100),
    @HHMEMBER_MIDDLENAME nvarchar(100),
    @HHMEMBER_LASTNAME nvarchar(100),
      @HHMEMBER_SUFFIXCODEID uniqueidentifier,
    @HHMEMBER_TITLECODEID uniqueidentifier,
    @HHMEMBER_BIRTHDATE dbo.UDT_FUZZYDATE,
    @HHMEMBER_MARITALSTATUSCODEID uniqueidentifier,
    @HHMEMBER_OCCUPATION nvarchar(100)
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()


  begin try

    declare @FULLNAME nvarchar(500);
    declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';

      if not exists(select * from dbo.WPBIOGRAPHICALDEMOGRAPHIC
                    where ID = @ID
                        and @FIRSTNAME = FIRSTNAME
                        and @MIDDLENAME = MIDDLENAME
                        and @LASTNAME = LASTNAME
                        and COALESCE(@SUFFIXCODEID,@EMPTYGUID) = COALESCE(SUFFIXCODEID,@EMPTYGUID)
                        and COALESCE(@TITLECODEID,@EMPTYGUID) = COALESCE(TITLECODEID,@EMPTYGUID)
              )
      begin
          set @FULLNAME = 
              case @TITLECODEID when null then '' else dbo.UFN_TITLECODE_GETDESCRIPTION(@TITLECODEID) + ' ' end
              +
              case @FIRSTNAME when '' then '' else @FIRSTNAME + ' ' end 
              +
              case @MIDDLENAME when '' then '' else @MIDDLENAME + ' ' end
              +
              case @LASTNAME when '' then '' else @LASTNAME end
              +
              case @SUFFIXCODEID when null then '' else ' ' + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(@SUFFIXCODEID) end
      end

    update dbo.WPBIOGRAPHICALDEMOGRAPHIC set
      FULLNAME = COALESCE(NULLIF(@FULLNAME,''),FULLNAME),
      FIRSTNAME = @FIRSTNAME,
          MIDDLENAME = @MIDDLENAME,
      LASTNAME = @LASTNAME,
      SUFFIXCODEID = @SUFFIXCODEID,
      TITLECODEID = @TITLECODEID,
      BIRTHDATE = @BIRTHDATE,
      MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
      OCCUPATION = @OCCUPATION,
      CNOTES = @CNOTES,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where ID = @ID;


      declare @HHMEMBERID uniqueidentifier;

      select top 1 @HHMEMBERID = ID 
      from dbo.WPBIOGRAPHICALHHMEMBER 
      where WPBIOGRAPHICALDEMOGRAPHICID = @ID 
      order by LASTNAME asc, FIRSTNAME asc, AGE desc;

      if @HHMEMBERID is not null
      begin

        if @HHMEMBER_LASTNAME = ''
        begin
          --Delete HHMember
          delete from dbo.WPBIOGRAPHICALHHMEMBER where ID = @HHMEMBERID;
        end

        else
        begin

          declare @HHMEMBERFULLNAME nvarchar(500);

            if not exists(select * from dbo.WPBIOGRAPHICALHHMEMBER
                          where ID = @HHMEMBERID
                              and @HHMEMBER_FIRSTNAME = FIRSTNAME
                              and @HHMEMBER_MIDDLENAME = MIDDLENAME
                              and @HHMEMBER_LASTNAME = LASTNAME
                              and COALESCE(@HHMEMBER_SUFFIXCODEID,@EMPTYGUID) = COALESCE(SUFFIXCODEID,@EMPTYGUID)
                              and COALESCE(@HHMEMBER_TITLECODEID,@EMPTYGUID) = COALESCE(TITLECODEID,@EMPTYGUID)
                    )
            begin
                set @HHMEMBERFULLNAME = 
                    case @HHMEMBER_TITLECODEID when null then '' else dbo.UFN_TITLECODE_GETDESCRIPTION(@HHMEMBER_TITLECODEID) + ' ' end
                    +
                    case @HHMEMBER_FIRSTNAME when '' then '' else @HHMEMBER_FIRSTNAME + ' ' end 
                    +
                    case @HHMEMBER_MIDDLENAME when '' then '' else @HHMEMBER_MIDDLENAME + ' ' end
                    +
                    case @HHMEMBER_LASTNAME when '' then '' else @HHMEMBER_LASTNAME end
                    +
                    case @HHMEMBER_SUFFIXCODEID when null then '' else ' ' + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(@HHMEMBER_SUFFIXCODEID) end
            end

          --Update existing HHMember
          update dbo.WPBIOGRAPHICALHHMEMBER set
            FULLNAME = COALESCE(NULLIF(@HHMEMBERFULLNAME,''),FULLNAME),
            FIRSTNAME = @HHMEMBER_FIRSTNAME,
            MIDDLENAME = @HHMEMBER_MIDDLENAME,
            LASTNAME = @HHMEMBER_LASTNAME,
            SUFFIXCODEID = @HHMEMBER_SUFFIXCODEID,
            TITLECODEID = @HHMEMBER_TITLECODEID,
            BIRTHDATE = @HHMEMBER_BIRTHDATE,
            MARITALSTATUSCODEID = @HHMEMBER_MARITALSTATUSCODEID,
            OCCUPATION = @HHMEMBER_OCCUPATION,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where ID = @HHMEMBERID;
        end

      end
      else
      begin

        if @HHMEMBER_LASTNAME <> ''
        begin
          --Create new HHMember record
          insert into dbo.WPBIOGRAPHICALHHMEMBER (ID,
                                                  WPBIOGRAPHICALDEMOGRAPHICID,
                                                  FULLNAME, 
                                                  FIRSTNAME, 
                                                  MIDDLENAME, 
                                                  LASTNAME, 
                                                  SUFFIXCODEID, 
                                                  TITLECODEID, 
                                                  BIRTHDATE, 
                                                  MARITALSTATUSCODEID, 
                                                  OCCUPATION,
                                                  ADDEDBYID,
                                                  CHANGEDBYID,
                                                  DATEADDED,
                                                  DATECHANGED)
          values
          (
            newid(),
            @ID,
              case @HHMEMBER_TITLECODEID when null then '' else dbo.UFN_TITLECODE_GETDESCRIPTION(@HHMEMBER_TITLECODEID) + ' ' end
              +
              case @HHMEMBER_FIRSTNAME when '' then '' else @HHMEMBER_FIRSTNAME + ' ' end 
              +
              case @HHMEMBER_MIDDLENAME when '' then '' else @HHMEMBER_MIDDLENAME + ' ' end
              +
              case @HHMEMBER_LASTNAME when '' then '' else @HHMEMBER_LASTNAME end
              +
              case @HHMEMBER_SUFFIXCODEID when null then '' else ' ' + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(@HHMEMBER_SUFFIXCODEID) end,
            @HHMEMBER_FIRSTNAME, 
            @HHMEMBER_MIDDLENAME, 
            @HHMEMBER_LASTNAME, 
            @HHMEMBER_SUFFIXCODEID, 
            @HHMEMBER_TITLECODEID, 
            @HHMEMBER_BIRTHDATE, 
            @HHMEMBER_MARITALSTATUSCODEID, 
            @HHMEMBER_OCCUPATION,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );

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

return 0;