USP_DATALIST_APPEALBBISEMAIL
Displays Appeal BBIS Email Summary.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @APPEALID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @MAILDATEFROM | datetime | IN | From |
| @MAILDATETO | datetime | IN | To |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_APPEALBBISEMAIL (
@APPEALID uniqueidentifier,
@MAILDATEFROM datetime = null,
@MAILDATETO datetime = null
)
as
set nocount on;
declare @BBISEMAILS table (
EMAILID integer,
EMAILTEMPLATEID integer,
NAME nvarchar(510),
RECIPIENTS integer,
CLICKS integer,
OPENS integer,
SENDDATE datetime,
URL nvarchar(2047),
TOTALSENT integer);
insert into @BBISEMAILS
(EMAILID, EMAILTEMPLATEID, RECIPIENTS, CLICKS, OPENS, SENDDATE, NAME, URL, TOTALSENT)
select
BBNC.EMAILID,
BBNC.EMAILTEMPLATEID,
count(BBNC.ID),
sum(cast(BBNC.CLICKEDTHROUGH as integer)),
sum(cast(BBNC.OPENED as integer)),
BBNC.SENTDATE,
BBNC.EMAILNAME,
BBNC.URL,
(count(BBNC.ID) - (sum(cast(BBNC.BOUNCED as integer)) + sum(cast(BBNC.SPAMCOMPLAINT as integer))))
from
dbo.UFN_NETCOMMUNITYEMAILJOBRECIPIENT_EMAIL() AS BBNC
left outer join dbo.BBNCAPPEALIDMAP on BBNC.APPEALID = BBNCAPPEALIDMAP.ID
left outer join dbo.APPEAL on BBNCAPPEALIDMAP.APPEALID = APPEAL.ID
where
APPEAL.ID = @APPEALID
and
(
@MAILDATEFROM is null
or ( BBNC.SENTDATE is not null and BBNC.SENTDATE >= @MAILDATEFROM )
or ( BBNC.SENTDATE is null )
)
and
(
@MAILDATETO is null
or ( BBNC.SENTDATE is not null and BBNC.SENTDATE <= @MAILDATETO )
or ( BBNC.SENTDATE is null )
)
--and email.id <> 206
group by
BBNC.EMAILID,
BBNC.SENTDATE,
BBNC.EMAILNAME,
BBNC.EMAILTEMPLATEID,
BBNC.URL
declare @BASECURRENCYID uniqueidentifier
declare @TRANSACTIONCURRENCY uniqueidentifier
declare @DECIMALDIGITS tinyint
declare @ROUNDINGTYPECODE tinyint
select @BASECURRENCYID = basecurrencyid from APPEAL where id = @appealid
set @DECIMALDIGITS = 2
set @ROUNDINGTYPECODE = 0
--same as basecurrency
declare @ALLTRANSACTIONS table
(
EMAILID integer,
GIFTAMOUNT money
)
--==================================DONATION TRANSACTIONS===============================================
insert into @ALLTRANSACTIONS (EMAILID, GIFTAMOUNT)
select e.emailid, dbo.UFN_CURRENCY_ROUND(giftamount,2,0) from
@BBISEMAILS e
left join DONATIONTRANSACTIONS on DONATIONTRANSACTIONS.EmailID = e.emailid
where
dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType) = @BASECURRENCYID
AND DONATIONTRANSACTIONS.[Status] = 1 AND DONATIONTRANSACTIONS.IsDeleted = 0
insert into @ALLTRANSACTIONS (EMAILID, GIFTAMOUNT)
select
e.emailid,
case
when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(giftamount, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(giftamount, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
else 0
end [AMOUNTINCURRENCY]
from
@BBISEMAILS e
left join DONATIONTRANSACTIONS on DONATIONTRANSACTIONS.EmailID = e.emailid
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType)
and CURRENCYEXCHANGERATE.TOCURRENCYID = @BASECURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and dateadd(ms, 86399996, DateAdded) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
and dateadd(ms, 86399996, DateAdded) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
) LATESTORGANIZATIONEXCHANGERATE
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType)
and CURRENCYEXCHANGERATE.TOCURRENCYID =@BASECURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and dateadd(ms, 86399996, DateAdded) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
and dateadd(ms, 86399996, DateAdded) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
) LATESTINVERSEORGANIZATIONEXCHANGERATE
where
dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType) <> @BASECURRENCYID
AND DONATIONTRANSACTIONS.[Status] = 1 AND DONATIONTRANSACTIONS.IsDeleted = 0
--==================================EVENT TRANSACTIONS===============================================
insert into @ALLTRANSACTIONS (EMAILID, GIFTAMOUNT)
select e.emailid, dbo.UFN_CURRENCY_ROUND(giftamount,2,0) from
@BBISEMAILS e
left join EVENTTRANSACTIONS on EVENTTRANSACTIONS.EmailID = e.emailid
where
dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType) = @BASECURRENCYID
AND EVENTTRANSACTIONS.[Status] = 1 AND EVENTTRANSACTIONS.IsDeleted = 0
insert into @ALLTRANSACTIONS (EMAILID, GIFTAMOUNT)
select
e.emailid,
case
when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(giftamount, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(giftamount, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
else 0
end [AMOUNTINCURRENCY]
from
@BBISEMAILS e
left join EVENTTRANSACTIONS on EVENTTRANSACTIONS.EmailID = e.emailid
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType)
and CURRENCYEXCHANGERATE.TOCURRENCYID = @BASECURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and dateadd(ms, 86399996, DateAdded) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
and dateadd(ms, 86399996, DateAdded) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
) LATESTORGANIZATIONEXCHANGERATE
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType)
and CURRENCYEXCHANGERATE.TOCURRENCYID =@BASECURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and dateadd(ms, 86399996, DateAdded) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
and dateadd(ms, 86399996, DateAdded) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
) LATESTINVERSEORGANIZATIONEXCHANGERATE
where
dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType) <> @BASECURRENCYID
AND EVENTTRANSACTIONS.[Status] = 1 AND EVENTTRANSACTIONS.IsDeleted = 0
--==================================MEMBERSHIP TRANSACTIONS===============================================
insert into @ALLTRANSACTIONS (EMAILID, GIFTAMOUNT)
select e.emailid, dbo.UFN_CURRENCY_ROUND(giftamount,2,0) from
@BBISEMAILS e
left join MEMBERSHIPTRANSACTIONS on MEMBERSHIPTRANSACTIONS.EmailID = e.emailid
where
dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType) = @BASECURRENCYID
AND MEMBERSHIPTRANSACTIONs.[Status] = 1 AND MEMBERSHIPTRANSACTIONs.IsDeleted = 0
insert into @ALLTRANSACTIONS (EMAILID, GIFTAMOUNT)
select
e.emailid,
case
when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(giftamount, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(giftamount, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
else 0
end [AMOUNTINCURRENCY]
from
@BBISEMAILS e
left join MEMBERSHIPTRANSACTIONs on MEMBERSHIPTRANSACTIONs.EmailID = e.emailid
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType)
and CURRENCYEXCHANGERATE.TOCURRENCYID = @BASECURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and dateadd(ms, 86399996, DateAdded) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
and dateadd(ms, 86399996, DateAdded) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
) LATESTORGANIZATIONEXCHANGERATE
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType)
and CURRENCYEXCHANGERATE.TOCURRENCYID = @BASECURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and dateadd(ms, 86399996, DateAdded) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
and dateadd(ms, 86399996, DateAdded) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
) LATESTINVERSEORGANIZATIONEXCHANGERATE
where
dbo.UFN_GETCURRENCY_BYSHELBYCODE(GiftCurrencyType) <> @BASECURRENCYID
AND MEMBERSHIPTRANSACTIONs.[Status] = 1 AND MEMBERSHIPTRANSACTIONs.IsDeleted = 0
declare @ALLRESPONSES table(EMAILID integer, UNIQUERESPONSES integer, RESPONSES integer );
insert into @ALLRESPONSES
(EMAILID, UNIQUERESPONSES, RESPONSES)
select distinct E.EMAILID,
case
when count(distinct DT.EMAILJOBRECIPIENTID) is null then 0
else count(distinct DT.EMAILJOBRECIPIENTID)
end,
case
when count(DT.EMAILJOBRECIPIENTID) is null then 0
else count(DT.EMAILJOBRECIPIENTID)
end
from @bbisemails E
left join DONATIONTRANSACTIONS DT on E.EMAILID = DT.EMAILID
group by E.EMAILID
insert into @ALLRESPONSES
(EMAILID, UNIQUERESPONSES, RESPONSES)
select distinct E.EMAILID,
case
when count(distinct ET.EMAILJOBRECIPIENTID) is null then 0
else count(distinct ET.EMAILJOBRECIPIENTID)
end,
case
when count(ET.EMAILJOBRECIPIENTID) is null then 0
else count(ET.EMAILJOBRECIPIENTID)
end
from @bbisemails E
left join EVENTTRANSACTIONS ET on E.EMAILID = ET.EMAILID
group by E.EMAILID
insert into @ALLRESPONSES
(EMAILID, UNIQUERESPONSES, RESPONSES)
select distinct E.EMAILID,
case
when count(distinct MT.EMAILJOBRECIPIENTID) is null then 0
else count(distinct MT.EMAILJOBRECIPIENTID)
end,
case
when count(MT.EMAILJOBRECIPIENTID) is null then 0
else count(MT.EMAILJOBRECIPIENTID)
end
from @bbisemails E
left join MEMBERSHIPTRANSACTIONS MT on E.EMAILID = MT.EMAILID
group by E.EMAILID
declare @TMPRESPONSE table (EMAILID integer, RESPONSES integer, UNIQUERESPONSES integer)
insert into @TMPRESPONSE(EMAILID, RESPONSES,UNIQUERESPONSES)
select
EMAILID, SUM(responses), SUM(uniqueresponses)
from @ALLRESPONSES
group by EMAILID
select
e.SENDDATE,
e.NAME as EMAILNAME,
--dbo.UFN_BBNC_URL() + 'adminpage.aspx?edit=3&eid=' + cast(E.EMAILID as nvarchar) + '&cid=' + cast(E.EMAILID as nvarchar) + '&ecatid=' + cast(E.EMAILTEMPLATEID as nvarchar) + '&ctid=84' as MEDIAURL,
e.URL as MEDIAURL,
E.RECIPIENTS as EMAILSSENT,
r.UNIQUERESPONSES,
case when E.RECIPIENTS = 0 then '0%'
else replace(convert(varchar,convert(decimal(19,1),cast(r.UNIQUERESPONSES as numeric) / cast(e.RECIPIENTS as numeric) * 100)) + '%','.0','')
end as RESPONSERATE,
r.RESPONSES,
sum(giftamount) as TOTAL,
case
when ( r.RESPONSES ) = 0 then 0
else convert(decimal(19,2),convert(numeric, SUM(GIFTAMOUNT)) / convert(numeric, r.RESPONSES))
end as AVERAGE,
case
when E.RECIPIENTS = 0 or E.TOTALSENT = 0 then '0%'
else
replace(convert(varchar, convert(decimal(19,1), (cast(E.OPENS as numeric)/(cast(E.TOTALSENT as numeric)))* 100)) + '%','.0','')
end as OPENRATE,
e.CLICKS as CLICKTHROUGHS,
e.EMAILID,
@BASECURRENCYID
from
@BBISEMAILS e
left join @ALLTRANSACTIONS t on e.EMAILID = t.EMAILID
left join @TMPRESPONSE r on e.EMAILID = r.EMAILID
group by e.EMAILID, e.SENDDATE, e.NAME, e.EMAILTEMPLATEID, e.URL, e.RECIPIENTS, e.CLICKs,r.RESPONSES,r.UNIQUERESPONSES, e.OPENS, e.TOTALSENT