USP_REPORT_SPONSORSHIPMAILINGSCOMMUNICATIONS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONSEGMENTID | uniqueidentifier | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN |
Definition
Copy
create procedure dbo.[USP_REPORT_SPONSORSHIPMAILINGSCOMMUNICATIONS]
(
@SEGMENTATIONSEGMENTID uniqueidentifier,
@REPORTUSERID nvarchar(128) = null,
@ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;
declare @ISTESTSEGMENT bit;
declare @SEGMENTATIONID uniqueidentifier;
declare @TEMPLATENAME nvarchar(255);
declare @SEGMENTNAME nvarchar(255);
declare @PACKAGENAME nvarchar(255);
declare @CHANNEL nvarchar(255);
declare @DATATABLE nvarchar(128);
declare @SQL nvarchar(max);
declare @ISBBEC bit;
declare @DONORIDSQL nvarchar(128);
begin try
set @ISTESTSEGMENT = (case when exists(select * from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID) then 1 else 0 end);
if @ISTESTSEGMENT = 1
--Grab package and other info from the mailing "test segment"...
select
@SEGMENTATIONID = [MKTSEGMENTATION].[ID],
@TEMPLATENAME = (case when charindex('(', reverse([MKTSEGMENTATION].[NAME])) > 0 then substring([MKTSEGMENTATION].[NAME], 1, len([MKTSEGMENTATION].[NAME]) - charindex('(', reverse([MKTSEGMENTATION].[NAME])) - 1) else [MKTSEGMENTATION].[NAME] end),
@SEGMENTNAME = dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]),
@PACKAGENAME = [MKTPACKAGE].[NAME],
@CHANNEL = [MKTPACKAGE].[CHANNEL],
@ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID])
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
else
begin
--Grab package and other info from the mailing "segment"...
select
@SEGMENTATIONID = [MKTSEGMENTATION].[ID],
@TEMPLATENAME = (case when charindex('(', reverse([MKTSEGMENTATION].[NAME])) > 0 then substring([MKTSEGMENTATION].[NAME], 1, len([MKTSEGMENTATION].[NAME]) - charindex('(', reverse([MKTSEGMENTATION].[NAME])) - 1) else [MKTSEGMENTATION].[NAME] end),
@SEGMENTNAME = (case when [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1 then 'Exclude: ' else '' end) + [MKTSEGMENT].[NAME],
@PACKAGENAME = isnull([MKTPACKAGE].[NAME], ''),
@CHANNEL = isnull([MKTPACKAGE].[CHANNEL], ''),
@ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID])
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
--If the ID passed in is not a mailing segment or mailing test segment, then assume it is the base segment ID...
if @SEGMENTATIONID is null
select
@SEGMENTATIONID = null,
@TEMPLATENAME = '',
@SEGMENTNAME = [NAME],
@PACKAGENAME = '',
@CHANNEL = '',
@ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([QUERYVIEWCATALOGID])
from dbo.[MKTSEGMENT]
where [ID] = @SEGMENTATIONSEGMENTID;
end
if @SEGMENTATIONID is not null
begin
set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
--Make sure table exists...
if not exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CREATEDATATABLE] @SEGMENTATIONID;
end
set @SQL = 'declare @CURRENTAPPUSERID uniqueidentifier = dbo.[UFN_APPUSER_GETREPORTAPPUSERID](@REPORTUSERID, @ALTREPORTUSERID);' + char(13) +
'declare @ISADMIN bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);' + char(13) +
'declare @APPUSER_IN_NONRACROLE bit = dbo.[UFN_SECURITY_APPUSER_IN_NONRACROLE](@CURRENTAPPUSERID);' + char(13) +
'declare @APPUSER_IN_NOSECGROUPROLE bit = dbo.[UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE](@CURRENTAPPUSERID);' + char(13) +
'declare @APPUSER_IN_NONSITEROLE bit = dbo.[UFN_SECURITY_APPUSER_IN_NONSITEROLE](@CURRENTAPPUSERID);' + char(13) +
'declare @APPUSER_IN_NOSITEROLE bit = dbo.[UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE](@CURRENTAPPUSERID);' + char(13) +
char(13) +
'select distinct' + char(13) +
' (case when [CONSTITUENT].[ID] is null then ''<Deleted>'' else dbo.[UFN_CONSTITUENT_BUILDNAME]([CONSTITUENT].[ID]) end) as [SPONSOR],' + char(13) +
' (select dbo.[UFN_BUILDFULLADDRESS]([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [ADDRESS].[STATEID], [ADDRESS].[POSTCODE], [ADDRESS].[COUNTRYID]) from dbo.[ADDRESS] where [ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [ADDRESS].[ISPRIMARY] = 1) as [SPONSORADDRESS],' + char(13) +
' (case when dbo.[UFN_SPONSORSHIPAFFILIATE_GETNAME]([CONSTITUENT].[ID]) is not null then' + char(13) +
' (case when [CONSTITUENT].[ID] is null then ''<Deleted>'' else dbo.[UFN_CONSTITUENT_BUILDNAME]([CONSTITUENT].[ID]) end)' + char(13) +
' else' + char(13) +
' dbo.[UFN_CONSTITUENT_BUILDNAME]([FINANCIALTRANSACTION].[CONSTITUENTID])' + char(13) +
' end) as [DONOR], ' + char(13) +
' (case when dbo.[UFN_SPONSORSHIPAFFILIATE_GETNAME]([CONSTITUENT].[ID]) is not null then' + char(13) +
' (select dbo.[UFN_BUILDFULLADDRESS]([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [ADDRESS].[STATEID], [ADDRESS].[POSTCODE], [ADDRESS].[COUNTRYID]) from dbo.[ADDRESS] where [ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [ADDRESS].[ISPRIMARY] = 1)' + char(13) +
' else' + char(13) +
' (select dbo.[UFN_BUILDFULLADDRESS]([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [ADDRESS].[STATEID], [ADDRESS].[POSTCODE], [ADDRESS].[COUNTRYID]) from dbo.[ADDRESS] where [ADDRESS].[CONSTITUENTID] = [FINANCIALTRANSACTION].[CONSTITUENTID] and [ADDRESS].[ISPRIMARY] = 1)' + char(13) +
' end) as [DONORADDRESS],' + char(13) +
' dbo.[UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION]([SPONSORSHIPOPPORTUNITY].[ID]) as [SPONSORSHIPOPPORTUNITY],' + char(13) +
' [SPONSORSHIP].[STARTDATE] as [DATE],' + char(13) +
' [SPONSORSHIP].[STATUS],' + char(13) +
' [SPONSORSHIPPROGRAM].[NAME] as [PROGRAM],' + char(13) +
' [LASTTRANSACTION].[ACTION] as [TRANSACTIONTYPE],' + char(13) +
' [SPONSORSHIPREASON].[REASON] as [REASON],' + char(13) +
' @TEMPLATENAME as [TEMPLATENAME],' + char(13) +
' @SEGMENTNAME as [SEGMENTNAME],' + char(13) +
' @PACKAGENAME as [PACKAGENAME],' + char(13) +
' @CHANNEL as [CHANNEL],' + char(13) +
' (case when [CONSTITUENT].[ID] is not null' + char(13) +
' and' + char(13) +
' (@ISADMIN = 1' + char(13) +
' or' + char(13) +
' (' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONRACROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSECGROUPROLE) = 1' + char(13) +
' )' + char(13) +
' and' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONSITEROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSITEROLE) = 1' + char(13) +
' )' + char(13) +
' )' + char(13) +
' )' + char(13) +
' then' + char(13) +
' N''http://www.blackbaud.com/SPONSORCONSTITUENTID?SPONSORCONSTITUENTID='' + convert(nvarchar(36), [CONSTITUENT].[ID])' + char(13) +
' else' + char(13) +
' ''''' + char(13) +
' end) as [SPONSORCONSTITUENTID],' + char(13) +
' (case when (@ISADMIN = 1' + char(13) +
' or' + char(13) +
' (' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONRACROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSECGROUPROLE) = 1' + char(13) +
' )' + char(13) +
' and' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONSITEROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSITEROLE) = 1' + char(13) +
' )' + char(13) +
' )' + char(13) +
' )' + char(13) +
' then' + char(13) +
' N''http://www.blackbaud.com/DONORCONSTITUENTID?DONORCONSTITUENTID='' + convert(nvarchar(36), [FINANCIALTRANSACTION].[CONSTITUENTID])' + char(13) +
' else' + char(13) +
' ''''' + char(13) +
' end) as [DONORCONSTITUENTID],' + char(13) +
' (case when (@ISADMIN = 1' + char(13) +
' or' + char(13) +
' (' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONRACROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSECGROUPROLE) = 1' + char(13) +
' )' + char(13) +
' and' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONSITEROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSITEROLE) = 1' + char(13) +
' )' + char(13) +
' )' + char(13) +
' )' + char(13) +
' then' + char(13) +
' N''http://www.blackbaud.com/SPONSORSHIPOPPORTUNITYID?SPONSORSHIPOPPORTUNITYID='' + convert(nvarchar(36), [SPONSORSHIPOPPORTUNITY].[ID])' + char(13) +
' else' + char(13) +
' ''''' + char(13) +
' end) as [SPONSORSHIPOPPORTUNITYID],' + char(13) +
' (case when (@ISADMIN = 1' + char(13) +
' or' + char(13) +
' (' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONRACROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSECGROUPROLE) = 1' + char(13) +
' )' + char(13) +
' and' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONSITEROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSITEROLE) = 1' + char(13) +
' )' + char(13) +
' )' + char(13) +
' )' + char(13) +
' then' + char(13) +
' N''http://www.blackbaud.com/SPONSORSHIPPROGRAMID?SPONSORSHIPPROGRAMID='' + convert(nvarchar(36), [SPONSORSHIPPROGRAM].[ID])' + char(13) +
' else' + char(13) +
' ''''' + char(13) +
' end) as [SPONSORSHIPPROGRAMID],' + char(13) +
' (case when (@ISADMIN = 1' + char(13) +
' or' + char(13) +
' (' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONRACROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSECGROUPROLE) = 1' + char(13) +
' )' + char(13) +
' and' + char(13) +
' (' + char(13) +
' @APPUSER_IN_NONSITEROLE = 1' + char(13) +
' or' + char(13) +
' dbo.[UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE](@CURRENTAPPUSERID, [CONSTITUENT].[ID], @APPUSER_IN_NOSITEROLE) = 1' + char(13) +
' )' + char(13) +
' )' + char(13) +
' )' + char(13) +
' then' + char(13) +
' N''http://www.blackbaud.com/SPONSORSHIPID?SPONSORSHIPID='' + convert(nvarchar(36), [SPONSORSHIP].[ID])' + char(13) +
' else' + char(13) +
' ''''' + char(13) +
' end) as [SPONSORSHIPID]' + char(13);
if @SEGMENTATIONID is null
begin
--Join to the base segment view because this report is running outside of a mailing...
set @SQL += 'from dbo.[' + dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME](@SEGMENTATIONSEGMENTID) + '] as [DONORS]' + char(13) +
'inner join dbo.[SPONSORSHIP] on [SPONSORSHIP].[ID] = [DONORS].[ID]' + char(13) +
'inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [SPONSORSHIP].[CONSTITUENTID]' + char(13);
end
else
begin
--Join to the mailing data table...
set @SQL += 'from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13);
--For BBEC only, check to see if we have any merged constituents in this mailing. If so, then we want to show the original constituent.
if @ISBBEC = 1 and exists(select * from dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] where [SEGMENTATIONID] = @SEGMENTATIONID)
begin
set @SQL += 'left join dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] on [MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS].[FINDERNUMBER] = [DONORS].[FINDERNUMBER]' + char(13);
set @DONORIDSQL = 'isnull([MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS].[ORIGINALCONSTITUENTID], [DONORS].[DONORID])';
end
else
set @DONORIDSQL = '[DONORS].[DONORID]';
set @SQL += 'inner join dbo.[SPONSORSHIP] on [SPONSORSHIP].[ID] = [DONORS].[SPONSORSHIPID]' + char(13) +
'left join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = ' + @DONORIDSQL + char(13);
end
set @SQL += 'inner join dbo.[SPONSORSHIPPROGRAM] on [SPONSORSHIP].[SPONSORSHIPPROGRAMID] = [SPONSORSHIPPROGRAM].[ID]' + char(13) +
'inner join dbo.[SPONSORSHIPCOMMITMENT] on [SPONSORSHIPCOMMITMENT].[ID] = [SPONSORSHIP].[SPONSORSHIPCOMMITMENTID]' + char(13) +
'inner join dbo.[SPONSORSHIPOPPORTUNITY] on [SPONSORSHIPOPPORTUNITY].[ID] = [SPONSORSHIP].[SPONSORSHIPOPPORTUNITYID]' + char(13) +
'inner join dbo.[SPONSORSHIPTRANSACTION] as [LASTTRANSACTION] on [LASTTRANSACTION].[SPONSORSHIPCOMMITMENTID] = [SPONSORSHIP].[SPONSORSHIPCOMMITMENTID] and [LASTTRANSACTION].[TRANSACTIONSEQUENCE] = (select max([TRANSACTIONSEQUENCE]) from dbo.[SPONSORSHIPTRANSACTION] as [MAXSEQUENCE] where [MAXSEQUENCE].[SPONSORSHIPCOMMITMENTID] = [SPONSORSHIP].[SPONSORSHIPCOMMITMENTID] and [SPONSORSHIP].[ID] in ([MAXSEQUENCE].[CONTEXTSPONSORSHIPID], [MAXSEQUENCE].[TARGETSPONSORSHIPID], [MAXSEQUENCE].[DECLINEDSPONSORSHIPID]))' + char(13) +
'left join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [SPONSORSHIP].[REVENUESPLITID]' + char(13) +
'left join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] and [FINANCIALTRANSACTION].[DELETEDON] is null' + char(13) +
'left join dbo.[SPONSORSHIPREASON] on [SPONSORSHIPREASON].[ID] = [LASTTRANSACTION].[SPONSORSHIPREASONID]' + char(13);
if @SEGMENTATIONID is not null
begin
if @ISTESTSEGMENT = 0
set @SQL += 'where [DONORS].[SEGMENTID] = @SEGMENTATIONSEGMENTID' + char(13) +
'and [DONORS].[TESTSEGMENTID] is null' + char(13);
else
set @SQL += 'where [DONORS].[TESTSEGMENTID] = @SEGMENTATIONSEGMENTID' + char(13);
end
exec sp_executesql @SQL, N'@TEMPLATENAME nvarchar(255), @SEGMENTNAME nvarchar(255), @PACKAGENAME nvarchar(255), @CHANNEL nvarchar(255), @ALTREPORTUSERID nvarchar(128), @REPORTUSERID nvarchar(128), @SEGMENTATIONSEGMENTID uniqueidentifier',
@TEMPLATENAME = @TEMPLATENAME,
@SEGMENTNAME = @SEGMENTNAME,
@PACKAGENAME = @PACKAGENAME,
@CHANNEL = @CHANNEL,
@REPORTUSERID = @REPORTUSERID,
@ALTREPORTUSERID = @ALTREPORTUSERID,
@SEGMENTATIONSEGMENTID = @SEGMENTATIONSEGMENTID;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;