USP_DATAFORMTEMPLATE_VIEW_BIOGRAPHICALDEMOGRAPHICNEEDSUPDATEFORM

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BIOGRAPHICALDEMOGRAPHICNEEDSUPDATEFORM
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @NEEDSUPDATEFORM bit = null output,

  @CONSTITUENTID uniqueidentifier = null output,
  @MOSAIC nvarchar(200) = null output,
  @HHINCOMECODEID uniqueidentifier = null output,
  @DISCRETIONARYSPENDING money = null output,
  @ISCONFIRMED bit = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;
  set @NEEDSUPDATEFORM = 1;

  declare @LASTNAME_NEW nvarchar(200);
  declare @LASTNAME_VALUE nvarchar(200);

  declare @FIRSTNAME_NEW nvarchar(200);
  declare @FIRSTNAME_VALUE nvarchar(200);

  declare @MIDDLENAME_NEW nvarchar(200); 
  declare @MIDDLENAME_VALUE nvarchar(200);

  declare @TITLECODEID_NEW uniqueidentifier;
  declare @TITLECODEID_VALUE uniqueidentifier;
  declare @TITLE2CODEID_VALUE uniqueidentifier;

  declare @SUFFIXCODEID_NEW uniqueidentifier;
  declare @SUFFIXCODEID_VALUE uniqueidentifier;
  declare @SUFFIX2CODEID_VALUE uniqueidentifier;

  declare @BIRTHDATE_NEW dbo.UDT_FUZZYDATE;
  declare @BIRTHDATE_VALUE dbo.UDT_FUZZYDATE;

  declare @MARITALSTATUSCODEID_NEW uniqueidentifier;
  declare @MARITALSTATUSCODEID_VALUE uniqueidentifier;

  declare @DISCRETIONARYSPENDING_NEW money;
  declare @DISCRETIONARYSPENDING_VALUE money;

  declare @HHINCOMECODEID_NEW uniqueidentifier;
  declare @HHINCOME_NEW nvarchar(200);
  declare @HHINCOME_VALUE nvarchar(200);

  declare @MOSAIC_NEW nvarchar(200);
  declare @MOSAIC_VALUE nvarchar(200);

  declare @HASSPOUSE bit;

  declare @ISSPOUSEMATCHCODE bit;

    set @HASSPOUSE = 0

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

  select
    @CONSTITUENTID = C.ID,

    @LASTNAME_NEW = D.LASTNAME,
    @FIRSTNAME_NEW = left(D.FIRSTNAME, 50),
    @MIDDLENAME_NEW = left(D.MIDDLENAME, 50),
    @SUFFIXCODEID_NEW = D.SUFFIXCODEID,
    @TITLECODEID_NEW = D.TITLECODEID,
    @BIRTHDATE_NEW = D.BIRTHDATE,
    @MARITALSTATUSCODEID_NEW = D.MARITALSTATUSCODEID,
    @DISCRETIONARYSPENDING_NEW = D.DISCRETIONARY_SPEND,
    @HHINCOMECODEID_NEW = D.HOUSEHOLD_INCOMECODEID,    
    @MOSAIC_NEW = D.HOUSEHOLD_MOSAIC,
    @ISCONFIRMED = D.CONFIRMED,
    @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,
    @SUFFIX2CODEID_VALUE = C.SUFFIX2CODEID,
    @TITLECODEID_VALUE = C.TITLECODEID,
    @TITLE2CODEID_VALUE = C.TITLE2CODEID,
    @BIRTHDATE_VALUE = C.BIRTHDATE,
    @MARITALSTATUSCODEID_VALUE = C.MARITALSTATUSCODEID

  from
    WPBIOGRAPHICALDEMOGRAPHIC as D
  left join
    CONSTITUENT C on C.ID = D.WEALTHID
  where @ID = D.ID;

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

  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'

  if 
  (
    @ISSPOUSEMATCHCODE = 0
    and 
    (
      (@LASTNAME_NEW <> @LASTNAME_VALUE and @LASTNAME_VALUE <> '' and @LASTNAME_NEW <> '')
      or (@FIRSTNAME_NEW <> @FIRSTNAME_VALUE and @FIRSTNAME_VALUE <> '' and @FIRSTNAME_NEW <> '')
      or (@MIDDLENAME_NEW <> @MIDDLENAME_VALUE and @MIDDLENAME_VALUE <> '' and @MIDDLENAME_NEW <> '')
      or (@TITLECODEID_NEW <> @TITLECODEID_VALUE and (@TITLE2CODEID_VALUE is null or @TITLECODEID_NEW <> @TITLE2CODEID_VALUE) and @TITLECODEID_VALUE is not null and @TITLECODEID_NEW is not null)
      or (@SUFFIXCODEID_NEW <> @SUFFIXCODEID_VALUE and (@SUFFIX2CODEID_VALUE is null or @SUFFIXCODEID_NEW <> @SUFFIX2CODEID_VALUE) and @SUFFIXCODEID_VALUE is not null and @SUFFIXCODEID_NEW is not null)
      or (@BIRTHDATE_NEW <> @BIRTHDATE_VALUE and @BIRTHDATE_VALUE <> '00000000' and @BIRTHDATE_NEW <> '00000000')
      or (@MARITALSTATUSCODEID_NEW <> @MARITALSTATUSCODEID_VALUE and @MARITALSTATUSCODEID_VALUE is not null and @MARITALSTATUSCODEID_NEW is not null)
      or (@HASSPOUSE = 0 and exists(Select 1 from WPBIOGRAPHICALHHMEMBER where WPBIOGRAPHICALDEMOGRAPHICID = @ID))
    )
  )
  or (@DISCRETIONARYSPENDING_NEW <> @DISCRETIONARYSPENDING_VALUE and @DISCRETIONARYSPENDING_VALUE <> '' and @DISCRETIONARYSPENDING_NEW <> '')
  or (@HHINCOME_NEW <> @HHINCOME_VALUE and @HHINCOME_VALUE <> '' and @HHINCOME_NEW <> '')
  or (@MOSAIC_NEW <> @MOSAIC_VALUE and @MOSAIC_VALUE <> '' and @MOSAIC_NEW <> '')

  begin
      --we want to show dataform
      set @DATALOADED = 1;
    set @NEEDSUPDATEFORM = 1;
  end else begin
      --do not want to show dataform
      set @DATALOADED = 1;
    set @NEEDSUPDATEFORM = 0;

    set @MOSAIC = case when @MOSAIC_VALUE <> '' then '' else @MOSAIC_NEW end;
    set @HHINCOMECODEID = case when  @HHINCOME_VALUE <> '' then null else @HHINCOMECODEID_NEW end;
    set @DISCRETIONARYSPENDING = case when @DISCRETIONARYSPENDING_VALUE <> '' then '' else @DISCRETIONARYSPENDING_NEW end;
  end
    return 0;