USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPDETAIL

The load procedure used by the view dataform template "Sponsorship Detail View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@SPONSORSHIPID	uniqueidentifier	INOUT	SPONSORSHIPID
@SPONSORSHIPOPPORTUNITYID	uniqueidentifier	INOUT	Sponsorship
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	Name
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@EMAILADDRESS	nvarchar(100)	INOUT	EMAILADDRESS
@PICTURE	varbinary	INOUT	Image
@PICTURETHUMBNAIL	varbinary	INOUT	Image thumbnail
@SPONSORSINCE	datetime	INOUT	Sponsor since
@LASTSPONSORSHIP	nvarchar(100)	INOUT	Last sponsorship
@SPONSORSHIPCOUNT	int	INOUT	Active sponsorships
@SPONSORSHIPTOTALAMOUNT	money	INOUT	Sponsorship amount to date
@OPPORTUNITYNAME	nvarchar(100)	INOUT	OPPORTUNITYNAME

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPDETAIL
(
  @ID uniqueidentifier,
  @SPONSORSHIPID uniqueidentifier = null output,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null output,
  @CONSTITUENTID uniqueidentifier = null output,
    @DATALOADED bit = 0 output,

  --SPONSOR

  @NAME nvarchar(100) = null output,
    @ADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @EMAILADDRESS nvarchar(100) = null output,
  @PICTURE varbinary(max) = null output,
    @PICTURETHUMBNAIL varbinary(max) = null output,
  @SPONSORSINCE datetime = null output,
  @LASTSPONSORSHIP nvarchar(100) = null output,
  @SPONSORSHIPCOUNT int = null output,
  @SPONSORSHIPTOTALAMOUNT money=null output,
  @OPPORTUNITYNAME nvarchar(100) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

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

  declare @countChild int;

  declare @CURRENTDATEEARLIESTTIME date;
  set @CURRENTDATEEARLIESTTIME = @CURRENTDATE;

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.

  -------------------------------

  -- Sponsor information

  -------------------------------

 -- Get consituentid

  select 
     @CONSTITUENTID = CONSTITUENTID
  from 
     dbo.SPONSORSHIP
  where ID = @ID
    select @DATALOADED = 1,
             @NAME = name,
         @PICTURE = PICTURE,
         @PICTURETHUMBNAIL = PICTURETHUMBNAIL
      from dbo.CONSTITUENT
     where ID = @CONSTITUENTID 

  --EMAIL

    select @EMAILADDRESS = EMAILADDRESS
      from dbo.EMAILADDRESS
     where EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID
       and EMAILADDRESS.ISPRIMARY = 1;

    --PHONE

    select @PHONENUMBER = PHONE.NUMBER
      from dbo.PHONE
     where PHONE.CONSTITUENTID = @CONSTITUENTID
     and PHONE.ISPRIMARY = 1;

    --ADDRESS

    select @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID)
      from dbo.ADDRESS
     where ADDRESS.CONSTITUENTID = @CONSTITUENTID
         and ADDRESS.ISPRIMARY = 1;

  --SPONSOR SINCE

  select @SPONSORSINCE = min(DATEFROM)
    from dbo.SPONSORDATERANGE
   where CONSTITUENTID = @CONSTITUENTID

  --LAST SPONSORSHIP

  select @LASTSPONSORSHIP = case when nullif(DATETO, CURRENT_TIMESTAMP) >= CURRENT_TIMESTAMP then 'Current' else DATETO end
    from dbo.SPONSORDATERANGE
   where CONSTITUENTID = @CONSTITUENTID
         and DATEFROM <= @CURRENTDATEEARLIESTTIME
         and (DATETO is null or DATETO >= @CURRENTDATEEARLIESTTIME)

  --SPONSORSHIP COUNT

  select @SPONSORSHIPCOUNT = count(*)
    from dbo.SPONSORSHIP
   where CONSTITUENTID = @CONSTITUENTID
     and STATUSCODE = 1

  select @SPONSORSHIPOPPORTUNITYID= SPONSORSHIPOPPORTUNITYID
  from dbo.SPONSORSHIP where ID = @ID

  --SPONSORSHIP ID


  set @SPONSORSHIPID = @ID

  --TOTAL AMOUNT OF ALL SPONSORSHIPS -- STILL NEEDS DESIGN

  --SPONSORSHIP

  select
     @OPPORTUNITYNAME = dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(@SPONSORSHIPOPPORTUNITYID)


    return 0;