USP_DATAFORMTEMPLATE_EDITLOAD_PARTICIPANTCOMMUNICATIONTEMPLATES

The load procedure used by the edit dataform template "Participant Communication Templates Edit Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@NAME	nvarchar(184)	INOUT	Name
@DESCRIPTION	nvarchar(500)	INOUT	Description
@SUBJECT	nvarchar(500)	INOUT	Subject
@USERID	int	INOUT	User ID
@CLIENTSITEID	int	INOUT	Client Site ID
@ISAVAILABLETOINDIVIDUAL	bit	INOUT	Individuals
@ISAVAILABLETOHOUSEHOLD	bit	INOUT	Households
@ISAVAILABLETOTEAMMEMBER	bit	INOUT	Team members
@ISAVAILABLETOTEAMLEADER	bit	INOUT	Team leaders
@ISAVAILABLETOCOMPANYLEADER	bit	INOUT	Company leaders
@ISAVAILABLETOFUNDRAISINGCOACH	bit	INOUT	Fundraising coaches
@ISAVAILABLETOSPONSOR	bit	INOUT	Sponsors
@INDIVIDUALSENABLED	bit	INOUT	Individuals may fundraise
@HOUSEHOLDSENABLED	bit	INOUT	Households may fundraise
@TEAMSENABLED	bit	INOUT	Team may fundraise
@COMPANIESENABLED	bit	INOUT	Companies may fundraise
@FUNDRAISINGCOACHENABLED	bit	INOUT	Fundraising coach is enabled
@SPONSORENABLED	bit	INOUT	Sponsor is enabled
@CONTENTHTML	nvarchar(max)	INOUT	Content
@ISACTIVE	bit	INOUT	Active
@CONFIRMATIONTYPECODE	tinyint	INOUT	Email type code
@FROMADDRESS	nvarchar(500)	INOUT	From address
@FROMNAME	nvarchar(500)	INOUT	From name
@REPLYADDRESS	nvarchar(500)	INOUT	Reply address
@RECIPIENTDONOR	bit	INOUT	Donors
@RECIPIENTPROSPECT	bit	INOUT	Prospects
@RECIPIENTINDIVIDUAL	bit	INOUT	Individuals
@RECIPIENTHOUSEHOLDMEMBER	bit	INOUT	Household members
@RECIPIENTHOUSEHOLDLEADER	bit	INOUT	Household leaders
@RECIPIENTTEAMMEMBER	bit	INOUT	Team members
@RECIPIENTTEAMLEADER	bit	INOUT	Team leaders
@RECIPIENTCOMPANYLEADER	bit	INOUT	Company leaders
@RECIPIENTSTATUSPREVIOUS	bit	INOUT	Previous
@RECIPIENTSTATUSCURRENT	bit	INOUT	Current
@PREVIOUSYEAREVENTID	uniqueidentifier	INOUT	Previous year eventid
@AVAILABLEASLETTER	bit	INOUT	Available as letter
@ISAVAILABLETOHOUSEHOLDMEMBER	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PARTICIPANTCOMMUNICATIONTEMPLATES(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
    @NAME nvarchar(184) = null output,
    @DESCRIPTION nvarchar(500) = null output,
    @SUBJECT nvarchar(500) = null output,
  @USERID int = null output,
  @CLIENTSITEID int = null output,
  @ISAVAILABLETOINDIVIDUAL bit = null output,
    @ISAVAILABLETOHOUSEHOLD bit = null output,
    @ISAVAILABLETOTEAMMEMBER bit = null output,
    @ISAVAILABLETOTEAMLEADER bit = null output,
    @ISAVAILABLETOCOMPANYLEADER bit = null output,
    @ISAVAILABLETOFUNDRAISINGCOACH bit = null output,
  @ISAVAILABLETOSPONSOR bit = null output,
  @INDIVIDUALSENABLED bit = null output,
  @HOUSEHOLDSENABLED bit = null output,
  @TEAMSENABLED bit = null output,
  @COMPANIESENABLED bit = null output,  
  @FUNDRAISINGCOACHENABLED bit = null output,
  @SPONSORENABLED bit = null output,
  @CONTENTHTML nvarchar(max) = null output,
  @ISACTIVE bit = null output,
  @CONFIRMATIONTYPECODE tinyint = null output,
  @FROMADDRESS nvarchar(500) = null output,
  @FROMNAME nvarchar(500) = null output,
  @REPLYADDRESS nvarchar(500) = null output,

  @RECIPIENTDONOR bit = null output,
  @RECIPIENTPROSPECT bit = null output,
  @RECIPIENTINDIVIDUAL bit = null output,
  @RECIPIENTHOUSEHOLDMEMBER bit = null output,
  @RECIPIENTHOUSEHOLDLEADER bit = null output,
  @RECIPIENTTEAMMEMBER bit = null output,
  @RECIPIENTTEAMLEADER bit = null output,
  @RECIPIENTCOMPANYLEADER bit = null output,
  @RECIPIENTSTATUSPREVIOUS bit = null output,
  @RECIPIENTSTATUSCURRENT bit = null output,
  @PREVIOUSYEAREVENTID uniqueidentifier= null output,
  @AVAILABLEASLETTER bit = null output,
  @ISAVAILABLETOHOUSEHOLDMEMBER bit = null output
)
as

    set nocount on;
  declare @EVENTID uniqueidentifier
    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 0

  -- 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.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
    select
        @DATALOADED = 1
        ,@TSLONG = EET.TSLONG
    ,@EVENTID = EET.EVENTID
        ,@NAME = ET.NAME
        ,@DESCRIPTION = ET.DESCRIPTION
        ,@SUBJECT = ET.SUBJECT        
    ,@USERID = ET.OwnerID
    ,@CLIENTSITEID = ET.ClientSitesID
    ,@ISAVAILABLETOINDIVIDUAL = ISAVAILABLETOINDIVIDUAL
    ,@ISAVAILABLETOHOUSEHOLD = ISAVAILABLETOHOUSEHOLD
    ,@ISAVAILABLETOTEAMMEMBER = ISAVAILABLETOTEAMMEMBER
    ,@ISAVAILABLETOTEAMLEADER = ISAVAILABLETOTEAMLEADER
    ,@ISAVAILABLETOCOMPANYLEADER = ISAVAILABLETOCOMPANYLEADER
    ,@ISAVAILABLETOFUNDRAISINGCOACH = ISAVAILABLETOFUNDRAISINGCOACH
    ,@ISAVAILABLETOSPONSOR = ISAVAILABLETOSPONSOR
    ,@CONTENTHTML = ET.ContentHTML
    ,@ISACTIVE = EET.ISACTIVE
    ,@CONFIRMATIONTYPECODE = EET.CONFIRMATIONTYPECODE
    ,@FROMADDRESS = ET.FromAddress
    ,@FROMNAME = ET.FromDisplayName
    ,@REPLYADDRESS = ET.ReplyAddress

    ,@RECIPIENTDONOR = EET.RECIPIENTDONOR
    ,@RECIPIENTPROSPECT = EET.RECIPIENTPROSPECT
    ,@RECIPIENTINDIVIDUAL = EET.RECIPIENTINDIVIDUAL
    ,@RECIPIENTHOUSEHOLDMEMBER = EET.RECIPIENTHOUSEHOLDMEMBER
    ,@RECIPIENTHOUSEHOLDLEADER = EET.RECIPIENTHOUSEHOLDLEADER
    ,@RECIPIENTTEAMMEMBER = EET.RECIPIENTTEAMMEMBER
    ,@RECIPIENTTEAMLEADER = EET.RECIPIENTTEAMLEADER
    ,@RECIPIENTCOMPANYLEADER = EET.RECIPIENTCOMPANYLEADER
    ,@RECIPIENTSTATUSPREVIOUS = EET.RECIPIENTSTATUSPREVIOUS
    ,@RECIPIENTSTATUSCURRENT = EET.RECIPIENTSTATUSCURRENT
    ,@AVAILABLEASLETTER = EET.AVAILABLEASLETTER
    ,@ISAVAILABLETOHOUSEHOLDMEMBER = ISAVAILABLETOHOUSEHOLDMEMBER
    from dbo.EVENTEMAILTEMPLATE EET 
  join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
    where EET.ID = @ID;

  if exists(select 1 from EVENTSPONSORSHIPTYPE where EVENTID = @EVENTID)
        set @SPONSORENABLED = 1
    else
        set @SPONSORENABLED = 0  

  select 
  @INDIVIDUALSENABLED = INDIVIDUALSFUNDRAISE,
    @HOUSEHOLDSENABLED = HOUSEHOLDSFUNDRAISE,
    @TEAMSENABLED = TEAMSFUNDRAISE,
    @COMPANIESENABLED = COMPANIESFUNDRAISE,    
    @FUNDRAISINGCOACHENABLED = CASE WHEN INDIVIDUALSFUNDRAISE = 1 OR HOUSEHOLDSFUNDRAISE = 1 OR TEAMSFUNDRAISE = 1 OR COMPANIESFUNDRAISE = 1 THEN 1 ELSE 0 END ,
    @PREVIOUSYEAREVENTID= PRIORYEAREVENTID    
  --from EVENTTEAMFAFCONFIG 
  --WHERE EVENTID = @EVENTID
    FROM dbo.EVENTEXTENSION E(NOLOCK) INNER JOIN 
      dbo.EVENTTEAMFAFCONFIG EC (NOLOCK)
      ON E.EVENTID= EC.EVENTID AND E.EVENTID=  @EVENTID

    return 0;