USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTCORRESPONDENCEPREVIEWCOMMUNITYEMAIL
The load procedure used by the view dataform template "Constituent Correspondence Preview Community Email View 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. |
| @EMAILNAME | nvarchar(255) | INOUT | Email name |
| @SENTDATE | datetime | INOUT | Date sent |
| @BOUNCED | bit | INOUT | Bounced |
| @BOUNCETEXT | nvarchar(1000) | INOUT | Bounce error |
| @EMAILADDRESS | nvarchar(255) | INOUT | Email address |
| @OPENED | bit | INOUT | Opened |
| @OPENEDDATE | datetime | INOUT | OPENEDDATE |
| @CLICKEDTHROUGH | bit | INOUT | CLICKEDTHROUGH |
| @DONATED | bit | INOUT | Donated |
| @EMAILSUBJECT | nvarchar(4000) | INOUT | Subject |
| @URL | UDT_WEBADDRESS | INOUT | Subject |
| @APPEALNAME | nvarchar(255) | INOUT | Appeal |
| @OPTEDOUT | bit | INOUT | Opted-out |
| @LINKPAGENAME1 | nvarchar(255) | INOUT | Links clicked |
| @LINKPAGENAME2 | nvarchar(255) | INOUT | LINKPAGENAME2 |
| @LINKPAGENAME3 | nvarchar(255) | INOUT | LINKPAGENAME3 |
| @LINKPAGENAME4 | nvarchar(255) | INOUT | LINKPAGENAME4 |
| @LINKPAGENAME5 | nvarchar(255) | INOUT | LINKPAGENAME5 |
| @LINKPAGEURL1 | UDT_WEBADDRESS | INOUT | Links clicked |
| @LINKPAGEURL2 | UDT_WEBADDRESS | INOUT | LINKPAGEURL2 |
| @LINKPAGEURL3 | UDT_WEBADDRESS | INOUT | LINKPAGEURL3 |
| @LINKPAGEURL4 | UDT_WEBADDRESS | INOUT | LINKPAGEURL4 |
| @LINKPAGEURL5 | UDT_WEBADDRESS | INOUT | LINKPAGEURL5 |
| @DONATIONAMOUNT | nvarchar(50) | INOUT | Donated |
| @SPAMCOMPLAINT | bit | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTCORRESPONDENCEPREVIEWCOMMUNITYEMAIL(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@EMAILNAME nvarchar(255) = null output,
@SENTDATE datetime = null output,
@BOUNCED bit = null output,
@BOUNCETEXT nvarchar(1000) = null output,
@EMAILADDRESS nvarchar(255) = null output,
@OPENED bit = null output,
@OPENEDDATE datetime = null output,
@CLICKEDTHROUGH bit = null output,
@DONATED bit = null output,
@EMAILSUBJECT nvarchar(4000) = null output,
@URL dbo.UDT_WEBADDRESS = null output,
@APPEALNAME nvarchar(255) = null output,
@OPTEDOUT bit = null output,
@LINKPAGENAME1 nvarchar(255) = null output,
@LINKPAGENAME2 nvarchar(255) = null output,
@LINKPAGENAME3 nvarchar(255) = null output,
@LINKPAGENAME4 nvarchar(255) = null output,
@LINKPAGENAME5 nvarchar(255) = null output,
@LINKPAGEURL1 dbo.UDT_WEBADDRESS = null output,
@LINKPAGEURL2 dbo.UDT_WEBADDRESS = null output,
@LINKPAGEURL3 dbo.UDT_WEBADDRESS = null output,
@LINKPAGEURL4 dbo.UDT_WEBADDRESS = null output,
@LINKPAGEURL5 dbo.UDT_WEBADDRESS = null output,
@DONATIONAMOUNT nvarchar(50) = null output,
@SPAMCOMPLAINT bit = null output
) as begin
set nocount on;
set @DATALOADED = 0;
set @DONATIONAMOUNT = 0;
declare @EMAILID int = 0;
declare @EMAILJOBRECIPIENTID int = 0;
select
@DATALOADED = 1,
@EMAILNAME = NC.[EMAILNAME],
@SENTDATE = NC.[SENTDATE],
@BOUNCED = NC.[BOUNCED],
@BOUNCETEXT = NC.[BOUNCETEXT],
@EMAILADDRESS = NC.[EMAILADDRESS],
@OPENED = NC.[OPENED],
@CLICKEDTHROUGH = NC.[CLICKEDTHROUGH],
@DONATED = NC.[DONATED],
@EMAILSUBJECT = NC.[EMAILSUBJECT],
@URL = NC.[URL],
@APPEALNAME = AP.[NAME],
@EMAILID = NC.[EMAILID],
@EMAILJOBRECIPIENTID = NC.[BBNCMAPID],
@SPAMCOMPLAINT = NC.[SPAMCOMPLAINT]
from
dbo.UFN_NETCOMMUNITYEMAILJOBRECIPIENT_EMAIL() NC
left join dbo.BBNCAPPEALIDMAP MAP on MAP.ID = NC.AppealID
left join dbo.APPEAL AP on AP.ID = MAP.APPEALID
left join dbo.EMAILJOB EJ on EJ.EmailID = NC.EMAILID
where
NC.[ID] = @ID
if @OPENED > 0
begin
select
@OPENEDDATE = ER.[OPENEDDATE],
@OPTEDOUT = ER.[GLOBALOPTEDOUT]
from
dbo.EMAILJOB_RECIPIENT ER
where ER.ID = @EMAILJOBRECIPIENTID and ER.EMAILID = @EMAILID
end
if @DONATED > 0
begin
select @DONATIONAMOUNT = sum(DT.GiftAmount)
from
dbo.DonationTransactions DT
where
DT.EmailID = @EMAILID and DT.ISDELETED = 0 and DT.STATUS = 1 and DT.EmailJobRecipientID = @EMAILJOBRECIPIENTID
end
if @CLICKEDTHROUGH > 0
begin
declare @tbl_emailpreview table(PID int, PageName nvarchar(255), URL nvarchar(1784), ClientSitesID int, ROW int identity(1,1));
insert into @tbl_emailpreview (PID, PageName, URL, ClientSitesID)
select
distinct top 5 [PID], p.PageName, el.[URL], s.ClientSitesID
from
dbo.[EmailLink] el
LEFT OUTER JOIN dbo.SitePages p on p.id = el.pid
LEFT OUTER JOIN dbo.Stats s on s.sourceid = el.emailid and s.sourcetypeid = 1 and s.EmailJobRecipientID = @EMAILJOBRECIPIENTID
WHERE el.EmailID = @EMAILID and (s.pageid = el.pid or s.URL = el.URL) order by PID desc
select @LINKPAGENAME1 = isnull(TBL.[PageName],TBL.URL),
@LINKPAGEURL1 = case when (TBL.URL is null or TBL.URL = '')
then C.VALUE + '?pid=' + cast(TBL.PID as nvarchar(100))
else TBL.URL
end
from @tbl_emailpreview TBL
inner join dbo.CMSSITESETTING C on C.ClientSitesID = TBL.ClientSitesID
where TBL.ROW = 1 and C.SETTING = 'PageBaseURL'
select @LINKPAGENAME2 = isnull(TBL.[PageName],TBL.URL),
@LINKPAGEURL2 = case when (TBL.URL is null or TBL.URL = '')
then C.VALUE + '?pid=' + cast(TBL.PID as nvarchar(100))
else TBL.URL end
from @tbl_emailpreview TBL
inner join dbo.CMSSITESETTING C on C.ClientSitesID = TBL.ClientSitesID
where TBL.ROW = 2 and C.SETTING = 'PageBaseURL'
select @LINKPAGENAME3 = isnull(TBL.[PageName],TBL.URL),
@LINKPAGEURL3 = case when (TBL.URL is null or TBL.URL = '')
then C.VALUE + '?pid=' + cast(TBL.PID as nvarchar(100))
else TBL.URL end
from @tbl_emailpreview TBL
inner join dbo.CMSSITESETTING C on C.ClientSitesID = TBL.ClientSitesID
where TBL.ROW = 3 and C.SETTING = 'PageBaseURL'
select @LINKPAGENAME4 = isnull(TBL.[PageName],TBL.URL),
@LINKPAGEURL4 = case when (TBL.URL is null or TBL.URL = '')
then C.VALUE + '?pid=' + cast(TBL.PID as nvarchar(100))
else TBL.URL end
from @tbl_emailpreview TBL
inner join dbo.CMSSITESETTING C on C.ClientSitesID = TBL.ClientSitesID
where TBL.ROW = 4 and C.SETTING = 'PageBaseURL'
select @LINKPAGENAME5 = isnull(TBL.[PageName],TBL.URL),
@LINKPAGEURL5 = case when (TBL.URL is null or TBL.URL = '')
then C.VALUE + '?pid=' + cast(TBL.PID as nvarchar(100))
else TBL.URL end
from @tbl_emailpreview TBL
inner join dbo.CMSSITESETTING C on C.ClientSitesID = TBL.ClientSitesID
where TBL.ROW = 5 and C.SETTING = 'PageBaseURL'
end
return 0;
end