USP_DATAFORMTEMPLATE_VIEW_CONSOLIDATEDWEALTHPAGEDATA

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSOLIDATEDWEALTHPAGEDATA

    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(400) = null output,
  @ISORGANIZATION bit = null output,
  @ISGROUP bit = null output,
  @ISHOUSEHOLD bit = null output,
  @LASTRESEARCHDETAILSUPDATE datetime = null output,
    @LASTRESEARCHEDBY nvarchar(100) = null output,
    @LASTWEALTHUPDATE datetime = null output,
    @DISABLEDWEALTHUPDATES bit = null output,
    @WEALTHPOINTREGISTERED bit = null output,
    @WEALTHPOINTACCOUNTTYPECODE integer = null output,
    @CANVIEWBIOGRAPHICAL bit = null output,
    @CANVIEWCAMPAIGN bit = null output,
    @CANVIEWFOUNDATION bit = null output,
    @CANVIEWGIFT bit = null output,
    @CANVIEWINCOME bit = null output,
    @CANVIEWAFFLUENCEINDICATORS bit = null output,
    @CANVIEWNONPROFIT bit = null output,
    @CANVIEWOTHERASSETS bit = null output,
    @CANVIEWBUSINESS bit = null output,
    @CANVIEWREALESTATE bit = null output,
    @CANVIEWSECURITIES bit = null output,
    @CANVIEWGIVINGTOMYORG bit = null output,
  @USER_GRANTED_CONSTITPERSONALINFO_EDIT bit = null output,
    @HASGIVINGSUMMARY bit = null output,
    @N_GIFTS integer = null output,
  @VIEWAS_ADVOCATECONSTITUENCYTEXT nvarchar(100) = null output,
  @VIEWAS_BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
  @VIEWAS_COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
  @VIEWAS_STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
  @VIEWAS_FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
  @VIEWAS_PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
  @VIEWAS_VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
  @ISADVOCATE bit = null output
 as

    set nocount on;
    set @DATALOADED = 0;    

    declare @SYSADMIN bit;    
    select @SYSADMIN = ISSYSADMIN from dbo.APPUSER where APPUSER.ID = @CURRENTAPPUSERID;

  select 
    @DATALOADED = 1,
    @NAME = NAME,
        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
        @ISGROUP = CONSTITUENT.ISGROUP
  from dbo.CONSTITUENT
  where ID = @ID;

  select 
    @LASTRESEARCHDETAILSUPDATE = PROSPECT.LASTUPDATED,
    @LASTRESEARCHEDBY = APPUSER.DISPLAYNAME
  from dbo.PROSPECT
    inner join  dbo.APPUSER on PROSPECT.LASTRESEARCHEDBYID = APPUSER.ID
  where PROSPECT.ID = @ID;

    select 
    @LASTWEALTHUPDATE = WEALTH.WEALTHPOINTDATE
    from
        dbo.WEALTH
    where
        WEALTH.ID = @ID;

    select 
        @DISABLEDWEALTHUPDATES = count(*)
    from
        dbo.DISABLEDWEALTHUPDATES
    where
        DISABLEDWEALTHUPDATES.ID = @ID

    --Get WealthPoint Account Information
    select top (1)
        @WEALTHPOINTREGISTERED =      REGISTERED,
        @WEALTHPOINTACCOUNTTYPECODE = ACCOUNTTYPECODE
    from
        dbo.WEALTHPOINTCONFIGURATION

  if (1=@sysadmin) begin
        set @CANVIEWBIOGRAPHICAL = 1;
        set @CANVIEWCAMPAIGN = 1;
        set @CANVIEWFOUNDATION = 1;
        set @CANVIEWGIFT = 1;
        set @CANVIEWINCOME = 1;
        set @CANVIEWAFFLUENCEINDICATORS = 1;
        set @CANVIEWNONPROFIT = 1;
        set @CANVIEWOTHERASSETS = 1;
        set @CANVIEWBUSINESS = 1;
        set @CANVIEWREALESTATE = 1;
        set @CANVIEWSECURITIES = 1;
    set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 1;
        set @CANVIEWGIVINGTOMYORG = 1;
    end
    else begin
        set @CANVIEWBIOGRAPHICAL =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'45F165B4-025E-49B3-AFCC-FD9B741406DB') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'ca164256-542b-48fe-b165-d53d0dd75ac4');
        set @CANVIEWCAMPAIGN =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'7685BF76-60A9-4C8F-8DCE-C143505F3F9D') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5a0edb04-1ce6-49fb-b965-5cb91746c37e');
        set @CANVIEWFOUNDATION =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'F4C7D336-DDEF-474A-89E5-291EE2DB2F52') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'f9b1f154-844d-4e11-92f5-4c8f5fa0a1cf');
        set @CANVIEWGIFT =                    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4B3922A5-2C7B-4FFB-9F64-54F024C40E13') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '608322a5-0699-42c6-9cc9-07b3ae685f5e');
        set @CANVIEWINCOME =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4CD067F6-DED7-458C-B592-11AAC1186FD9') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '0e21b37a-ec41-4883-8b4d-f9738688d441');
        set @CANVIEWAFFLUENCEINDICATORS =    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8BFA650D-4FC4-4454-94F2-2A38332C0684') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '791f6e7d-8aa8-4078-a4e1-0392cd0b73f2');
        set @CANVIEWNONPROFIT =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'9CA1A80A-3697-4D42-B7A9-CC0CB5B562A9') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '40f3df01-4ba4-4228-9a3b-18e4bd98fbe3');
        set @CANVIEWOTHERASSETS =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'DC27FA3B-7EFE-484C-99CA-FC8C84D7EE0A') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'b70edfe9-af03-486a-bc01-352a219cbf01');
        set @CANVIEWBUSINESS =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'2B80B727-9E55-433E-9B37-E061645B1263') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'd6d56c8f-3f0d-4f5a-b194-e5c5d1399a48');
        set @CANVIEWREALESTATE =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'C5659434-1493-4c84-BCB5-2D2DA2932CA0') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '0a054f34-9246-49a8-87f0-2cf789597080');
        set @CANVIEWSECURITIES =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8623389D-F469-4a34-B3D5-00C642257CDC') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '90758f12-463a-421b-8433-1883eb1c0aa5');
        set @CANVIEWGIVINGTOMYORG =     dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'523eee21-4ee1-471b-9f2e-6e67a7d4670c');

    set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 
        case 
          when @ISORGANIZATION = 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'a9954902-ea62-48ae-8e6f-4e2ed3c3f4f9')
          when @ISHOUSEHOLD = 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'2f419cfd-9e70-406c-8277-ba2c4b7bdfba')
          when @ISGROUP = 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'292ee330-a63c-4cc5-98c2-a1168a1b7150')
          else dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'788AB947-26ED-40C4-865E-8FE29577E593')
        end;
    end

    set @HASGIVINGSUMMARY = 0;
    select 
        @HASGIVINGSUMMARY = 1,
        @N_GIFTS = TOTALGIFTSGIVEN
    from 
        dbo.RE7INTEGRATIONGIVINGSUMMARY
    where
        RE7INTEGRATIONGIVINGSUMMARY.ID = @ID

  select
    @VIEWAS_BOARDMEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF'),
    @VIEWAS_STAFFCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467'),
    @VIEWAS_COMMITTEECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799'),
    @VIEWAS_FUNDRAISERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923'),
    @VIEWAS_PROSPECTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8'),
    @VIEWAS_VOLUNTEERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D'),
    @VIEWAS_ADVOCATECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C6899')

      Set @ISADVOCATE = dbo.UFN_CONSTITUENT_ISADVOCATE(@ID)

    return 0;