USP_DATAFORMTEMPLATE_EDITLOAD_BIOGRAPHICALDEMOGRAPHICUPDATE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_BIOGRAPHICALDEMOGRAPHICUPDATE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,

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

    @LASTNAME_NEW nvarchar(200) = null output,
    @LASTNAME_VALUE nvarchar(200) = null output,
    @LASTNAME_UPD bit = null output,

    @FIRSTNAME_NEW nvarchar(200) = null output,
    @FIRSTNAME_VALUE nvarchar(200) = null output,
    @FIRSTNAME_UPD bit = null output,

    @MIDDLENAME_NEW nvarchar(200) = null output, 
    @MIDDLENAME_VALUE nvarchar(200) = null output,
    @MIDDLENAME_UPD bit = null output,

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

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

    @BIRTHDATE_NEW dbo.UDT_FUZZYDATE = null output,
    @BIRTHDATE_VALUE dbo.UDT_FUZZYDATE = null output,
    @BIRTHDATE_UPD bit = null output,

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

    @DISCRETIONARYSPENDING_NEW money = null output,
    @DISCRETIONARYSPENDING_VALUE money = null output,
    @DISCRETIONARYSPENDING_UPD bit = null output,

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

    @MOSAIC_NEW nvarchar(200) = null output,
    @MOSAIC_VALUE nvarchar(200) = null output,
    @MOSAIC_UPD bit = null output,

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

    @ISSPOUSEMATCHCODE bit = null output

)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 0
  set @HASSPOUSE = 0
  set @CONSTITUENTTYPE = 0

    declare @CHANGEAGENTID uniqueidentifier = null;
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.

  select
      @DATALOADED = 1,
    @CONSTITUENTID = C.ID,
    @FULLNAME = C.NAME,
    @GENDERCODE = C.GENDERCODE,

    @LASTNAME_NEW = D.LASTNAME,
    @FIRSTNAME_NEW = left(D.FIRSTNAME, 50),
    @MIDDLENAME_NEW = left(D.MIDDLENAME, 50),
    @SUFFIXCODEID_NEW = D.SUFFIXCODEID,
    @SUFFIX_NEW = case when (C.SUFFIX2CODEID is null or C.SUFFIX2CODEID <> D.SUFFIXCODEID) then D.SUFFIX else '' end,
    @TITLECODEID_NEW = D.TITLECODEID,
    @TITLE_NEW = case when (C.TITLE2CODEID is null or C.TITLE2CODEID <> D.TITLECODEID) then D.TITLE else '' end,
    @BIRTHDATE_NEW = D.BIRTHDATE,
    @MARITALSTATUSCODEID_NEW = D.MARITALSTATUSCODEID,
    @MARITALSTATUS_NEW = D.MARITALSTATUS,
    @DISCRETIONARYSPENDING_NEW = D.DISCRETIONARY_SPEND,
    @HHINCOMECODEID_NEW = D.HOUSEHOLD_INCOMECODEID,    
    @MOSAIC_NEW = D.HOUSEHOLD_MOSAIC,
    @ISSPOUSEMATCHCODE = case when D.MC like 'S%' then 1 else 0 end,

    @LASTNAME_VALUE = C.KEYNAME,
    @FIRSTNAME_VALUE = C.FIRSTNAME,
    @MIDDLENAME_VALUE = C.MIDDLENAME,
    @SUFFIXCODEID_VALUE = C.SUFFIXCODEID,
    @SUFFIX_VALUE = S.DESCRIPTION,
    @TITLECODEID_VALUE = C.TITLECODEID,
    @TITLE_VALUE = T.DESCRIPTION,
    @BIRTHDATE_VALUE = C.BIRTHDATE,
    @MARITALSTATUSCODEID_VALUE = C.MARITALSTATUSCODEID,
    @MARITALSTATUS_VALUE = M.DESCRIPTION,

    @HHMEMBERS = dbo.UFN_WPBIOGRAPHICAL_GETHHMEMBERS2_TOITEMLISTXML(@ID)

  from
    WPBIOGRAPHICALDEMOGRAPHIC as D
  left join
    CONSTITUENT C on C.ID = D.WEALTHID
  left join 
    SUFFIXCODE S on S.ID = C.SUFFIXCODEID
  left join 
    TITLECODE T on T.ID = C.TITLECODEID
  left join 
    MARITALSTATUSCODE M on M.ID = C.MARITALSTATUSCODEID
  where @ID = D.ID;

  if exists(select ID from RELATIONSHIP where @CONSTITUENTID = RELATIONSHIPCONSTITUENTID and ISSPOUSE = 1)
    set @HASSPOUSE = 1

  exec USP_RELATIONSHIPTYPECODE_GETORCREATE @SPOUSERELATIONSHIPTYPECODE output, 'Spouse', @CHANGEAGENTID, 'true','false','false', 'false', 'true', 'false', 'false', 'false'

  select 
    @HHINCOME_NEW = DESCRIPTION 
  from 
    HOUSEHOLDINCOMEESTIMATECODE 
  where 
    @HHINCOMECODEID_NEW = ID 

  DECLARE @DEMOGRAPHICMODELS TABLE ( 
      CATEGORY nvarchar(200), 
      STRINGVALUE nvarchar(200),
      CURRENCYVALUE money 
  )

  insert into @DEMOGRAPHICMODELS (
      CATEGORY, 
      STRINGVALUE, 
      CURRENCYVALUE
  )
  select 
      CATEGORY,
      STRINGVALUE,
      CURRENCYVALUE
  from 
      dbo.UFN_ATTRIBUTE_GETATTRIBUTELIST('Model Scores and Ratings', @CONSTITUENTID, @CHANGEAGENTID)

  select
      @DISCRETIONARYSPENDING_VALUE = CURRENCYVALUE
  from @DEMOGRAPHICMODELS
  where CATEGORY = 'Discretionary spending estimate'

  select
      @HHINCOME_VALUE = STRINGVALUE
  from @DEMOGRAPHICMODELS
  where CATEGORY = 'Household income estimate'

  select
      @MOSAIC_VALUE = STRINGVALUE
  from @DEMOGRAPHICMODELS
  where CATEGORY = 'Household Mosaic'


    return 0;