USP_DATALIST_WEALTHINFORMATIONDASHBOARDAFFLUENCEINDICATOR

                CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDAFFLUENCEINDICATOR
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ISVISIBLE bit = 1,
                    @CONFIDENCE int = 0,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    declare @ISADMIN bit;
                    declare @APPUSER_IN_NONRACROLE bit;
                    declare @APPUSER_IN_NOSECGROUPROLE bit;

                    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                    if @ISVISIBLE = 1
                    begin

                        with RECORDCONFIDENCE as(
                            select     
                                WP.ID,
                                case
                                    when WP.CONFIRMED = 1 then '5'
                                    when WP.REJECTED =  1 then '0'
                                    else
                                         isnull(CR.CONFIDENCE,(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                                end as CONFIDENCE

                            from
                            dbo.WPAFFLUENCEINDICATOR WP
                            left outer join
                                dbo.MATCHCODE MC on WP.MC = MC.MATCHCODE
                            left outer join 
                                dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID    
                            left outer join
                                dbo.WEALTHSOURCE WS on CR.WEALTHSOURCEID = WS.ID 
                            where
                             WS.SOURCE like WP.SOURCE or WS.SOURCE is null
                        )

                        select top 1000
                            SOURCE,
                            FULLNAME,
                            MULTIMILLN,
                            RPASSETS,
                            WEALTHIND,
                            WP.ID,
                            PHONE,
                            GENDER,
                            CNOTES,
                            ADDRESSTYPE,
                            LINE1,
                            CITY,
                            STATE,
                            ZIP,
                            ZIP4,
                            COUNTY,
                            TITLE,
                            COMPANY,
                            WEBSITE,
                            SALES,
                            EMPLOYMENT,
                            NAICS,
                            AGE,
                            DONOR,
                            MARITAL,
                            CHILDRENINDICATOR,
                            INCOME,
                            CONTRIBUTOR,
                            POLITICALAFFILIATION,
                            RELIGIOUSAFFILIATION,
                            ETHNIC
                        from
                            dbo.WPAFFLUENCEINDICATOR WP
                        left outer join
                            RECORDCONFIDENCE RC on RC.ID = WP.ID
                        where
                            WEALTHID = @CONSTITUENTID and REJECTED = 0 and
                            (((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
                            ((@CONFIDENCE = 99)                     and WP.CONFIRMED = 1))
                            and (@ISADMIN = 1 or 
                                @APPUSER_IN_NONRACROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, WEALTHID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                    end
                    else
                    begin

                        with RECORDCONFIDENCE as(
                            select     
                                WP.ID,
                                case
                                    when WP.CONFIRMED = 1 then '5'
                                    when WP.REJECTED =  1 then '0'
                                    else
                                         isnull(CR.CONFIDENCE,(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                                end as CONFIDENCE

                            from
                            dbo.WPAFFLUENCEINDICATOR WP
                            left outer join
                                dbo.MATCHCODE MC on WP.MC = MC.MATCHCODE
                            left outer join 
                                dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID    
                            left outer join
                                dbo.WEALTHSOURCE WS on CR.WEALTHSOURCEID = WS.ID 
                            where
                             WS.SOURCE like WP.SOURCE or WS.SOURCE is null
                        )

                        select top 1
                            SOURCE,
                            FULLNAME,
                            MULTIMILLN,
                            RPASSETS,
                            WEALTHIND
                        from
                            dbo.WPAFFLUENCEINDICATOR WP
                        left outer join
                            RECORDCONFIDENCE RC on RC.ID = WP.ID
                        where
                            WEALTHID = @CONSTITUENTID and REJECTED = 0 and
                            (((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
                            ((@CONFIDENCE = 99)                     and WP.CONFIRMED = 1))
                    end