USP_DATAFORMTEMPLATE_ADD_CONSOLIDATEDPROSPECTSEARCH

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSOLIDATEDPROSPECTSEARCH
(
    @ID uniqueidentifier output,
  @CHANGEAGENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
  @NAME nvarchar(100) = null,
  @SOURCECODE tinyint = 3,
  @SORTBYCODE tinyint = 1,
  @NAMEORID nvarchar(500) = '',
  @LOCATION nvarchar(500) = '',
  @LASTNAME nvarchar(50) = '',
  @FIRSTNAME nvarchar(50) = '',
  @MIDDLENAME nvarchar(50) = '',
  @NICKNAME nvarchar(50) = '',
  @ADDRESSBLOCK nvarchar(100) = '',
  @CITY nvarchar(100) = '',
  @STATE nvarchar(3) = '',
  @STATEID uniqueidentifier = null,
  @POSTCODE nvarchar(12) = '',
  @INCLUDEDECEASED bit = 1,
  @INCLUDEINACTIVE bit = 1,
  @SPOUSELASTNAME nvarchar(50) = '',
  @SPOUSEFIRSTNAME nvarchar(50) = '',
  @AGEMIN integer = null,
  @AGEMAX integer = null,
  @ESTIMATEDWEALTHMINID uniqueidentifier = null,
  @BUSINESS nvarchar(50) = '',
  @CLASSYEAR dbo.UDT_YEAR = null,
  @RECORDTYPECODE tinyint = 1,
  @EXACTMATCHONLY bit = 0,
  @OVERALLRATINGCODEID uniqueidentifier = null,
  @ISEXPORT bit = 0,
  @RESEARCHRESULTS xml = null,
  @MAXROWSCODE int = null,
  @EXPORTCACHEID nvarchar(100) = '',
  @ESTIMATEDWEALTHMAXID uniqueidentifier = null,
  @HASBUSINESS bit = null,
  @GIVINGMIN nvarchar(20) = null,
  @LARGESTGIFTMIN nvarchar(20) = null,
  @SECURITIESMIN nvarchar(20) = null,
  @REALESTATEMIN nvarchar(20) = null,
  @SPENDMIN nvarchar(20) = null,
  @INCOMERANGE nvarchar(30) = null,
  @GIVINGCATEGORIES xml = null,
  @GIVINGCATEGORYDISPLAY nvarchar(100) = null,
  @MOSAICS xml = null,
  @MOSAICDISPLAY nvarchar(100) = null
)
as
    set nocount on;

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

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

    begin try;

    --need to check with design on this
    --ensure name is not taken
    select @ID = ID from dbo.PROSPECTSEARCH 
    where OWNERID = @CURRENTAPPUSERID and NAME = @NAME;

    if @ID is null
    begin
      if @ID is null
          set @ID = newid()

      --We only allow top 10
      --Delete extra saves if we are adding a non-export search
      delete PROSPECTSEARCH
      from dbo.PROSPECTSEARCH
      where OWNERID = @CURRENTAPPUSERID 
        and ID not in (select ID from dbo.UFN_PROSPECTSEARCH_GETTOPN(9, @CURRENTAPPUSERID))
        and ISEXPORT = 0
        and @ISEXPORT = 0;

      --If we are adding an export search, delete any that already exist for this user
      delete from dbo.PROSPECTSEARCH 
      where OWNERID = @CURRENTAPPUSERID and
        ISEXPORT = 1 and
        @ISEXPORT = 1;

      insert into dbo.PROSPECTSEARCH
       (
        ID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED,
        NAME,
        OWNERID,
        SOURCECODE,
        SORTBYCODE,
        NAMEORID,
        LOCATION,
        LASTNAME,
        FIRSTNAME,
        MIDDLENAME,
        NICKNAME,
        ADDRESSBLOCK,
        CITY,
        STATE,
        STATEID,
        POSTCODE,
        INCLUDEDECEASED,
        INCLUDEINACTIVE,
        SPOUSELASTNAME,
        SPOUSEFIRSTNAME,
        AGEMIN,
        AGEMAX,
        ESTIMATEDWEALTHMINID,
        ESTIMATEDWEALTHMAXID,
        BUSINESS,
        HASBUSINESS,
        CLASSYEAR,
        RECORDTYPECODE,
        EXACTMATCHONLY,
        OVERALLRATINGCODEID,
        GIVINGMIN,
        LARGESTGIFTMIN,
        SECURITIESMIN,
        REALESTATEMIN,
        SPENDMIN,
        INCOMERANGE,
        GIVINGCATEGORYDISPLAY,
        MOSAICDISPLAY,
        ISEXPORT,
        RESEARCHRESULTS,
        MAXROWS,
        EXPORTCACHEID
       )
       values
       (
        @ID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        @NAME,
        @CURRENTAPPUSERID,
        @SOURCECODE,
        @SORTBYCODE,
        coalesce(@NAMEORID, ''),
        coalesce(@LOCATION, ''),
        coalesce(@LASTNAME, ''),
        coalesce(@FIRSTNAME, ''),
        coalesce(@MIDDLENAME, ''),
        coalesce(@NICKNAME, ''),
        coalesce(@ADDRESSBLOCK, ''),
        coalesce(@CITY, ''),
        coalesce(@STATE, ''),
        @STATEID,
        coalesce(@POSTCODE, ''),
        coalesce(@INCLUDEDECEASED, 0),
        coalesce(@INCLUDEINACTIVE, 0),
        coalesce(@SPOUSELASTNAME, ''),
        coalesce(@SPOUSEFIRSTNAME, ''),
        coalesce(@AGEMIN, 0),
        coalesce(@AGEMAX, 0),
        @ESTIMATEDWEALTHMINID,
        @ESTIMATEDWEALTHMAXID,
        coalesce(@BUSINESS, ''),
        @HASBUSINESS,
        coalesce(@CLASSYEAR, ''),
        @RECORDTYPECODE,
        @EXACTMATCHONLY,
        @OVERALLRATINGCODEID,
        coalesce(@GIVINGMIN, ''),
        coalesce(@LARGESTGIFTMIN, ''),
        coalesce(@SECURITIESMIN, ''),
        coalesce(@REALESTATEMIN, ''),
        coalesce(@SPENDMIN, ''),
        coalesce(@INCOMERANGE, ''),
        coalesce(@GIVINGCATEGORYDISPLAY, ''),
        coalesce(@MOSAICDISPLAY, ''),
        @ISEXPORT,
        @RESEARCHRESULTS,
        coalesce(@MAXROWSCODE, 100),
        coalesce(@EXPORTCACHEID, '')
       );
    end
    else
    begin
      update dbo.PROSPECTSEARCH
      set
              CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE,
        SOURCECODE = @SOURCECODE,
        SORTBYCODE = @SORTBYCODE,
        NAMEORID = coalesce(@NAMEORID, ''),
        LOCATION = coalesce(@LOCATION, ''),
        LASTNAME = coalesce(@LASTNAME, ''),
        FIRSTNAME = coalesce(@FIRSTNAME, ''),
        MIDDLENAME = coalesce(@MIDDLENAME, ''),
        NICKNAME = coalesce(@NICKNAME, ''),
        ADDRESSBLOCK = coalesce(@ADDRESSBLOCK, ''),
        CITY = coalesce(@CITY, ''),
        STATE = coalesce(@STATE, ''),
        STATEID = @STATEID,
        POSTCODE = coalesce(@POSTCODE, ''),
        INCLUDEDECEASED = coalesce(@INCLUDEDECEASED, 0),
        INCLUDEINACTIVE = coalesce(@INCLUDEINACTIVE, 0),
        SPOUSELASTNAME = coalesce(@SPOUSELASTNAME, ''),
        SPOUSEFIRSTNAME = coalesce(@SPOUSEFIRSTNAME, ''),
        AGEMIN = coalesce(@AGEMIN, 0),
        AGEMAX = coalesce(@AGEMAX, 0),
        ESTIMATEDWEALTHMINID = @ESTIMATEDWEALTHMINID,
        ESTIMATEDWEALTHMAXID = @ESTIMATEDWEALTHMAXID,
        BUSINESS = coalesce(@BUSINESS, ''),
        HASBUSINESS = @HASBUSINESS,
        CLASSYEAR = coalesce(@CLASSYEAR, ''),
        RECORDTYPECODE = @RECORDTYPECODE,
        EXACTMATCHONLY = @EXACTMATCHONLY,
        OVERALLRATINGCODEID = @OVERALLRATINGCODEID,
        GIVINGMIN = coalesce(@GIVINGMIN, ''),
        LARGESTGIFTMIN = coalesce(@LARGESTGIFTMIN, ''),
        SECURITIESMIN = coalesce(@SECURITIESMIN, ''),
        REALESTATEMIN = coalesce(@REALESTATEMIN, ''),
        SPENDMIN = coalesce(@SPENDMIN, ''),
        INCOMERANGE = coalesce(@INCOMERANGE, ''),
        GIVINGCATEGORYDISPLAY = coalesce(@GIVINGCATEGORYDISPLAY, ''),
        ISEXPORT = @ISEXPORT,
        RESEARCHRESULTS = @RESEARCHRESULTS,
        MAXROWS = coalesce(@MAXROWSCODE, 100),
        EXPORTCACHEID = coalesce(@EXPORTCACHEID, '')
      where ID = @ID;
    end

    exec dbo.USP_PROSPECTSEARCH_GET_CATEGORIES_UPDATEFROMXML @ID, @GIVINGCATEGORIES, @CHANGEAGENTID, @CURRENTDATE
    exec dbo.USP_PROSPECTSEARCH_GET_MOSAICS_UPDATEFROMXML @ID, @MOSAICS, @CHANGEAGENTID, @CURRENTDATE

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch


    return 0;