USP_PARTIALINFORMATIONSEARCHCRITERIA_ADDORUPDATE

This procedure is used to add or update Prospect Quick Search criteria.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@SEARCHDATE	datetime	IN
@KEYNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@NICKNAME	nvarchar(50)	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@UNITNUMBER	nvarchar(25)	IN
@CITY	nvarchar(50)	IN
@STATEID	uniqueidentifier	IN
@POSTCODE	nvarchar(50)	IN
@COMPANYNAME	nvarchar(100)	IN
@PHONENUMBER	nvarchar(100)	IN
@REALESTATESEARCH	bit	IN
@REALESTATEADDRESSSEARCHTYPECODE	tinyint	IN
@REALESTATESEARCHPROPERTYADDRESS	bit	IN
@REALESTATESEARCHMAILINGADDRESS	bit	IN
@REALESTATESEARCHBUYERADDRESS	bit	IN
@REALESTATESEARCHASSESSEEADDRESS	bit	IN
@BUSINESSOWNERSHIPSEARCH	bit	IN
@DATASOURCESEARCHTYPECODE	tinyint	IN
Definition

Copy


            CREATE procedure dbo.USP_PARTIALINFORMATIONSEARCHCRITERIA_ADDORUPDATE (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SEARCHDATE datetime = null,
                @KEYNAME nvarchar(100) = '',
                @FIRSTNAME nvarchar(50) = '',
                @NICKNAME nvarchar(50) = '',
                @ADDRESSBLOCK nvarchar(150) = '',
                @UNITNUMBER nvarchar(25) = '',
                @CITY nvarchar(50) = '',
                @STATEID uniqueidentifier = null,
                @POSTCODE nvarchar(50) = '',
                @COMPANYNAME nvarchar(100) = '',
                @PHONENUMBER nvarchar(100) = '',
                @REALESTATESEARCH bit = 0,
                @REALESTATEADDRESSSEARCHTYPECODE tinyint = 0,
                @REALESTATESEARCHPROPERTYADDRESS bit = 0,
                @REALESTATESEARCHMAILINGADDRESS bit = 0,
                @REALESTATESEARCHBUYERADDRESS bit = 0,
                @REALESTATESEARCHASSESSEEADDRESS bit = 0,
                @BUSINESSOWNERSHIPSEARCH bit = 0,
                @DATASOURCESEARCHTYPECODE tinyint = 0
            ) as begin
                set nocount on;

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

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

                --make sure we do not have any null strings

                set @KEYNAME = coalesce(@KEYNAME, '');
                set @FIRSTNAME = coalesce(@FIRSTNAME, '');
                set @NICKNAME = coalesce(@NICKNAME, '');
                set @ADDRESSBLOCK = coalesce(@ADDRESSBLOCK, '');
                set @UNITNUMBER = coalesce(@UNITNUMBER, '');
                set @CITY = coalesce(@CITY, '');
                set @POSTCODE = coalesce(@POSTCODE, '');
                set @COMPANYNAME = coalesce(@COMPANYNAME, '');
                set @PHONENUMBER = coalesce(@PHONENUMBER, '');
                set @REALESTATESEARCH = coalesce(@REALESTATESEARCH, 0);
                set @REALESTATEADDRESSSEARCHTYPECODE = coalesce(@REALESTATEADDRESSSEARCHTYPECODE, 0);
                set @REALESTATESEARCHPROPERTYADDRESS = coalesce(@REALESTATESEARCHPROPERTYADDRESS, 0);
                set @REALESTATESEARCHMAILINGADDRESS = coalesce(@REALESTATESEARCHMAILINGADDRESS, 0);
                set @REALESTATESEARCHBUYERADDRESS = coalesce(@REALESTATESEARCHBUYERADDRESS, 0);
                set @REALESTATESEARCHASSESSEEADDRESS = coalesce(@REALESTATESEARCHASSESSEEADDRESS, 0);
                set @BUSINESSOWNERSHIPSEARCH = coalesce(@BUSINESSOWNERSHIPSEARCH, 0);
                set @DATASOURCESEARCHTYPECODE = coalesce(@DATASOURCESEARCHTYPECODE, 0);

                --check as to whether the search criteria already exists for this application user

                declare @EXISTINGID uniqueidentifier;
                select
                    @EXISTINGID = PISC.[ID]
                from
                    dbo.[PARTIALINFORMATIONSEARCHCRITERIA] PISC
                where
                    PISC.[APPUSERID] = @CURRENTAPPUSERID and
                    PISC.[KEYNAME] = @KEYNAME and
                    PISC.[FIRSTNAME] = @FIRSTNAME and
                    PISC.[NICKNAME] = @NICKNAME and
                    PISC.[ADDRESSBLOCK] = @ADDRESSBLOCK and
                    PISC.[UNITNUMBER] = @UNITNUMBER and
                    PISC.[CITY] = @CITY and
                    (PISC.[STATEID] = @STATEID or (PISC.[STATEID] is null and @STATEID is null)) and
                    PISC.[POSTCODE] = @POSTCODE and
                    PISC.[COMPANYNAME] = @COMPANYNAME and
                    PISC.[PHONENUMBER] = @PHONENUMBER and
                    PISC.[REALESTATESEARCH] = @REALESTATESEARCH and
                    PISC.[REALESTATEADDRESSSEARCHTYPECODE] = @REALESTATEADDRESSSEARCHTYPECODE and
                    PISC.[REALESTATESEARCHPROPERTYADDRESS] = @REALESTATESEARCHPROPERTYADDRESS and
                    PISC.[REALESTATESEARCHMAILINGADDRESS] = @REALESTATESEARCHMAILINGADDRESS and
                    PISC.[REALESTATESEARCHBUYERADDRESS] = @REALESTATESEARCHBUYERADDRESS and
                    PISC.[REALESTATESEARCHASSESSEEADDRESS] = @REALESTATESEARCHASSESSEEADDRESS and
                    PISC.[BUSINESSOWNERSHIPSEARCH] = @BUSINESSOWNERSHIPSEARCH and
                    PISC.[DATASOURCESEARCHTYPECODE] = @DATASOURCESEARCHTYPECODE;

                begin try
                    if @EXISTINGID is not null begin
                        set @ID = @EXISTINGID;

                        update
                            dbo.[PARTIALINFORMATIONSEARCHCRITERIA]
                        set
                            [SEARCHDATE] = @SEARCHDATE,
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CURRENTDATE
                        where
                            [ID] = @ID;
                    end
                    else begin
                        if @ID is null
                            set @ID = newid();

                        insert into dbo.[PARTIALINFORMATIONSEARCHCRITERIA] (
                            [ID],
                            [APPUSERID],
                            [SEARCHDATE],
                            [KEYNAME],
                            [FIRSTNAME],
                            [NICKNAME],
                            [ADDRESSBLOCK],
                            [UNITNUMBER],
                            [CITY],
                            [STATEID],
                            [POSTCODE],
                            [COMPANYNAME],
                            [PHONENUMBER],
                            [REALESTATESEARCH],
                            [REALESTATEADDRESSSEARCHTYPECODE],
                            [REALESTATESEARCHPROPERTYADDRESS],
                            [REALESTATESEARCHMAILINGADDRESS],
                            [REALESTATESEARCHBUYERADDRESS],
                            [REALESTATESEARCHASSESSEEADDRESS],
                            [BUSINESSOWNERSHIPSEARCH],
                            [DATASOURCESEARCHTYPECODE],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values (
                            @ID,
                            @CURRENTAPPUSERID,
                            @SEARCHDATE,
                            @KEYNAME,
                            @FIRSTNAME,
                            @NICKNAME,
                            @ADDRESSBLOCK,
                            @UNITNUMBER,
                            @CITY,
                            @STATEID,
                            @POSTCODE,
                            @COMPANYNAME,
                            @PHONENUMBER,
                            @REALESTATESEARCH,
                            @REALESTATEADDRESSSEARCHTYPECODE,
                            @REALESTATESEARCHPROPERTYADDRESS,
                            @REALESTATESEARCHMAILINGADDRESS,
                            @REALESTATESEARCHBUYERADDRESS,
                            @REALESTATESEARCHASSESSEEADDRESS,
                            @BUSINESSOWNERSHIPSEARCH,
                            @DATASOURCESEARCHTYPECODE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );
                    end

                    --remove old searches

                    declare @SAVEDSEARCHESPERAPPUSER integer;

                    select top 1
                        @SAVEDSEARCHESPERAPPUSER = PISC.[SAVEDSEARCHESPERAPPUSER]
                    from
                        dbo.[PARTIALINFORMATIONSEARCHCONFIGURATION] PISC;

                    --we want a default value of 100 if no row exists

                    set @SAVEDSEARCHESPERAPPUSER = coalesce(@SAVEDSEARCHESPERAPPUSER, 100);

                    delete
                        dbo.[PARTIALINFORMATIONSEARCHCRITERIA]
                    from
                        dbo.[PARTIALINFORMATIONSEARCHCRITERIA]
                    where
                        [APPUSERID] = @CURRENTAPPUSERID and
                        [ID] not in (
                            select top (@SAVEDSEARCHESPERAPPUSER)
                                PISC.[ID]
                            from
                                dbo.[PARTIALINFORMATIONSEARCHCRITERIA] PISC
                            where
                                PISC.[APPUSERID] = @CURRENTAPPUSERID
                            order by
                                PISC.[SEARCHDATE] desc
                        )
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;
            end