USP_DATAFORMTEMPLATE_VIEW_FUNDRAISERPROFILE

The load procedure used by the view dataform template "Fundraiser Summary Profile View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ISFUNDRAISERPROFILE	bit	INOUT	ISFUNDRAISERPROFILE
@ADDRESS	nvarchar(300)	INOUT	Address
@PHONENUMBER	nvarchar(100)	INOUT	Phone
@PHONETYPE	nvarchar(100)	INOUT	Phone type
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email
@WEBADDRESS	UDT_WEBADDRESS	INOUT	Web
@PICTURE	varbinary	INOUT	Picture
@ISINACTIVE	bit	INOUT	Status
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	Deceased date
@OPPORTUNITYCOUNT	int	INOUT	Total qualified opportunity count
@OPPORTUNITYAMOUNT	money	INOUT	Total qualified opportunity amount
@ASKCOUNT	int	INOUT	Ask count
@ASKAMOUNT	money	INOUT	Pending ask amount
@ISSELF	bit	INOUT	ISSELF
@SOLICITCODECOUNT	int	INOUT	SOLICITCODECOUNT
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@POSITIONTITLE	nvarchar(100)	INOUT	Position title
@SITE	nvarchar(100)	INOUT	Position site
@STARTDATE	datetime	INOUT	Start date
@HOUSEHOLDTEXT	nvarchar(154)	INOUT	Household
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@REQUESTEDAMOUNT	money	INOUT	Requested amount
@CURRENCYID	uniqueidentifier	INOUT	Currency ID
@ISDECEASED	bit	INOUT	ISDECEASED
@CONSTITUENTINACTIVITYREASON	nvarchar(63)	INOUT	Inactive reason
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@RELATEDCONSTITUENT	nvarchar(154)	INOUT	RELATEDCONSTITUENT
@RELATEDCONSTITUENTID	uniqueidentifier	INOUT	RELATEDCONSTITUENTID
@ISSPOUSEDECEASED	bit	INOUT	ISSPOUSEDECEASED
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@ISGROUP	bit	INOUT	ISGROUP
@QUALIFIEDOPPORTUNITYCOUNT	int	INOUT	Qualified opportunity count
@QUALIFIEDOPPORTUNITYAMOUNT	money	INOUT	Qualified opportunity amount
@SOCIALMEDIAACCOUNTS	xml	INOUT
@UNQUALIFIEDOPPORTUNITYCOUNT	int	INOUT
@UNQUALIFIEDOPPORTUNITYAMOUNT	money	INOUT
@RESPONSEPENDINGOPPORTUNITYCOUNT	int	INOUT
@RESPONSEPENDINGOPPORTUNITYAMOUNT	money	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FUNDRAISERPROFILE(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ISFUNDRAISERPROFILE bit = null output,
  @ADDRESS nvarchar(300) = null output,
  @PHONENUMBER nvarchar(100) = null output,
  @PHONETYPE nvarchar(100) = null output,
  @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
  @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
  @PICTURE varbinary(max) = null output,
  @ISINACTIVE bit = null output,
  @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
  @OPPORTUNITYCOUNT int = null output,
  @OPPORTUNITYAMOUNT money = null output,
  @ASKCOUNT int = null output,
  @ASKAMOUNT money = null output,
  @ISSELF bit = null output,
  @SOLICITCODECOUNT int = null output,
  @GIVESANONYMOUSLY bit = null output,
  @POSITIONTITLE nvarchar(100) = null output,
  @SITE nvarchar(100) = null output,
  @STARTDATE datetime = null output,
  @HOUSEHOLDTEXT nvarchar(154) = null output,
  @HOUSEHOLDID uniqueidentifier = null output,
  @REQUESTEDAMOUNT money = null output,
  @CURRENCYID uniqueidentifier = null output,
  @ISDECEASED bit = null output,
  @CONSTITUENTINACTIVITYREASON nvarchar(63) = null output,
  @DONOTMAIL bit = null output,
  @DONOTEMAIL bit = null output,
  @DONOTPHONE bit = null output,
  @PHONEISCONFIDENTIAL bit = null output,
  @ADDRESSISCONFIDENTIAL bit = null output,
  @ADDRESSID uniqueidentifier = null output,
  @PHONENUMBERID uniqueidentifier = null output,
  @EMAILADDRESSID uniqueidentifier = null output,
  @RELATEDCONSTITUENT nvarchar(154) = null output,
  @RELATEDCONSTITUENTID uniqueidentifier = null output,
  @ISSPOUSEDECEASED bit = null output,
  @ISORGANIZATION bit = null output,
  @ISGROUP bit = null output,
  @QUALIFIEDOPPORTUNITYCOUNT int = null output,
  @QUALIFIEDOPPORTUNITYAMOUNT money = null output,
  @SOCIALMEDIAACCOUNTS xml = null output,
  @UNQUALIFIEDOPPORTUNITYCOUNT int = null output,  
  @UNQUALIFIEDOPPORTUNITYAMOUNT money = null output,
  @RESPONSEPENDINGOPPORTUNITYCOUNT int = null output,  
  @RESPONSEPENDINGOPPORTUNITYAMOUNT money = null output
) as begin

  set nocount on;

  -- TSH  09/29/09  Bug 57968  Opportunities are for enterprise and advanced prospect management and basic prospect management.
  declare @CALCULATEPLANDETAIL bit;
  set @CALCULATEPLANDETAIL = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') | dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0a07aa22-ed88-42b7-8c55-d80df4f4cea2') | dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('ab25cd89-7288-4605-b0ea-48961960ec06');

  set @DATALOADED = 0;

  set @ISSELF = 0;
  --MAB 2/18/09 Fix for work item 21382
  --select @ISSELF=1 from dbo.APPUSER where ID=@CURRENTAPPUSERID and CONSTITUENTID=@ID;

  select
    @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(APPUSER.ID)
  from
    dbo.APPUSER
  where
    APPUSER.CONSTITUENTID = @ID;

  if @CURRENCYID is null
    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();  

  select
    @DATALOADED = 1,
    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
    @ISGROUP = CONSTITUENT.ISGROUP,
    @ISFUNDRAISERPROFILE = 1,
    @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
    @DONOTMAIL = ADDRESS.DONOTMAIL,
    @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
    @ADDRESSID = ADDRESS.ID,        
    @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
    @PHONETYPE = dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PHONE.PHONETYPECODEID),
    @DONOTPHONE = PHONE.DONOTCALL,
    @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
    @PHONENUMBERID = PHONE.ID,
    @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
    @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
    @EMAILADDRESSID = EMAILADDRESS.ID,
    @WEBADDRESS = CONSTITUENT.WEBADDRESS,
    @PICTURE = case @ISSELF when 1 then null else CONSTITUENT.PICTURETHUMBNAIL end,
    @ISINACTIVE = CONSTITUENT.ISINACTIVE,
    @DECEASEDDATE = DECEASEDCONSTITUENT.DECEASEDDATE,
    @ISDECEASED = case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end,
    @OPPORTUNITYCOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          count(*)
        from
          dbo.OPPORTUNITY O 
        where
          O.STATUSCODE in (1,2 )
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
        )
      else
        0
      end
    ),
    @OPPORTUNITYAMOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
        from
           dbo.OPPORTUNITY O 
        where
          O.STATUSCODE in (1, 2)
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
      )
      else
        0
      end
    ),
    @ASKCOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          count(*)
        from
          dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 2 
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
        )
      else
        0
      end
    ),
    @ASKAMOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
        from
           dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 2
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
        )
      else
        0
      end
    ),
    @GIVESANONYMOUSLY = CONSTITUENT.GIVESANONYMOUSLY,
    @REQUESTEDAMOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          coalesce(sum(coalesce(dbo.UFN_FUNDINGREQUEST_GETAMOUNTREQUESTEDINCURRENCY(FUNDINGREQUEST.ID, @CURRENCYID),0)),0)
        from dbo.FUNDINGREQUEST
        where PRIMARYMANAGERID = CONSTITUENT.ID
          or SECONDARYMANAGERID = CONSTITUENT.ID
          or CONSTITUENT.ID in (
            select SPONSORID
            from FUNDINGREQUESTSPONSOR
            where FUNDINGREQUESTID = FUNDINGREQUEST.ID
          )
        )
      else
        0
      end
    ),
    @RELATEDCONSTITUENT = case when CONSTITUENT.ISORGANIZATION = 1 then NF_PARENTORG.NAME else NF_SPOUSE.NAME end,
    @RELATEDCONSTITUENTID = case when CONSTITUENT.ISORGANIZATION = 1 then ORGANIZATIONDATA.PARENTCORPID else RELATIONSHIP.RECIPROCALCONSTITUENTID end,
    @ISSPOUSEDECEASED = case when SPOUSEDECEASEDCONSTITUENT.ID is not null then 1 else 0 end,
    @QUALIFIEDOPPORTUNITYCOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          count(*)
        from
          dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 1 
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
        )
      else
        0
      end
    ),
    @QUALIFIEDOPPORTUNITYAMOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
        from
           dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 1
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
      )
      else
        0
      end
      ),
      @UNQUALIFIEDOPPORTUNITYCOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          count(*)
        from
          dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 0 
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
        )
      else
        0
      end
    ),
    @UNQUALIFIEDOPPORTUNITYAMOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
        from
           dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 0
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
      )
      else
        0
      end
      ),
      @RESPONSEPENDINGOPPORTUNITYCOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          count(*)
        from
          dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 2 
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
        )
      else
        0
      end
    ),
    @RESPONSEPENDINGOPPORTUNITYAMOUNT = (
      case @CALCULATEPLANDETAIL when 1 then (
        select
          coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
        from
           dbo.OPPORTUNITY O 
        where
          O.STATUSCODE = 2
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
          and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
      )
      else
        0
      end
    )
  from dbo.CONSTITUENT
    left outer join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
    left outer join dbo.ADDRESS on ADDRESS.CONSTITUENTID=CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
    left outer join dbo.PHONE on PHONE.CONSTITUENTID=CONSTITUENT.ID and PHONE.ISPRIMARY = 1
    left outer join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID=CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
    left outer join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISSPOUSE = 1
    left outer join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = CONSTITUENT.ID
    left join dbo.DECEASEDCONSTITUENT SPOUSEDECEASEDCONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSEDECEASEDCONSTITUENT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF_SPOUSE
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(ORGANIZATIONDATA.PARENTCORPID) NF_PARENTORG
  where
    CONSTITUENT.ID = @ID

  declare @TODAY date = getdate();

  select
    @STARTDATE = ORGANIZATIONPOSITIONHOLDER.DATEFROM,
    @POSITIONTITLE = ORGANIZATIONPOSITION.NAME,
    @SITE = SITE.NAME
  from
    dbo.ORGANIZATIONPOSITIONHOLDER
  inner join
    dbo.ORGANIZATIONPOSITION on ORGANIZATIONPOSITION.ID = ORGANIZATIONPOSITIONHOLDER.POSITIONID
  left join
    dbo.SITE on SITE.ID = ORGANIZATIONPOSITION.SITEID
  where
    ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = @ID and
    (@TODAY between ORGANIZATIONPOSITIONHOLDER.DATEFROM and coalesce(ORGANIZATIONPOSITIONHOLDER.DATETO, @TODAY));

  if @ISINACTIVE = 1
    select @CONSTITUENTINACTIVITYREASON = dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
    from dbo.CONSTITUENTINACTIVEDETAIL
    where ID = @ID

  --Solicit Codes
  select @SOLICITCODECOUNT=count(ID)
  from CONSTITUENTSOLICITCODE
  where CONSTITUENTID=@ID

  --SOCIAL MEDIA ACCOUNTS
  select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@ID);

  -- HOUSEHOLD
  -- constituents are only allowed to be part of one household, only individuals can be in households
  if @ISORGANIZATION = 0 and @ISGROUP = 0
      select top(1)
        @HOUSEHOLDID = CG.ID,
        @HOUSEHOLDTEXT = CG.NAME
      from dbo.GROUPMEMBER as GM
      join dbo.CONSTITUENT as CG on GM.GROUPID = CG.ID
    join dbo.GROUPDATA as GD on GD.ID = CG.ID
      where GM.MEMBERID = @ID
      and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
    and GD.GROUPTYPECODE = 0;

  return 0
end