USP_APPEALMAILINGSETUPLETTER_BUILDCANNEDSELECTION
Create or update the selection used to respect the canned constituent filter options for an appeal mailing letter.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @RECORDTYPEID | uniqueidentifier | IN | |
| @CONSTITUENTINCLUDECODE | tinyint | IN | |
| @CONSIDERREVENUEHISTORY | bit | IN | |
| @REVENUECRITERIACODE | tinyint | IN | |
| @LOWREVENUEAMOUNT | money | IN | |
| @HIGHREVENUEAMOUNT | money | IN |
Definition
Copy
create procedure dbo.USP_APPEALMAILINGSETUPLETTER_BUILDCANNEDSELECTION
(
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@RECORDTYPEID uniqueidentifier,
@CONSTITUENTINCLUDECODE tinyint,
@CONSIDERREVENUEHISTORY bit,
@REVENUECRITERIACODE tinyint,
@LOWREVENUEAMOUNT money,
@HIGHREVENUEAMOUNT money
)
as
set nocount on;
if @ID is null
set @ID = newID();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime = getDate();
declare @TOMORROWMIDNIGHT datetime = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(dd, 1, @CURRENTDATE));
declare @SELECTIONNAME nvarchar(max) = 'Appeal Mailing Setup Letter Selection (' + cast(@ID as nvarchar(36)) + ')';
declare @FUNCTIONNAME nvarchar(128) = 'UFN_APPEALMAILINGSETUPLETTERSELECTION_' + replace(cast(@ID as nvarchar(36)),'-','_');
declare @FUNCTIONSQL nvarchar(max);
declare @WHERECLAUSE nvarchar(max) = '';
begin try
-- Build the sql for the selection
set @FUNCTIONSQL =
'create function dbo.' + @FUNCTIONNAME + '() ' + char(13)
+ 'returns table ' + char(13)
+ 'as return ' + char(13)
if @CONSTITUENTINCLUDECODE > 1 or (@CONSIDERREVENUEHISTORY = 1 and @REVENUECRITERIACODE = 2)
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' with CTE_VALIDREVENUE as (' + char(13)
+ ' select * ' + char(13)
+ ' from dbo.UFN_REVENUELIFECYCLE_GETVALIDREVENUE() ' + char(13)
+ ' )'
if @CONSIDERREVENUEHISTORY = 1 and @REVENUECRITERIACODE <> 2
begin
if @CONSTITUENTINCLUDECODE > 1
set @FUNCTIONSQL = @FUNCTIONSQL
+ ', ' + char(13)
+ ' CTE_AGGREGATE as (' + char(13);
else
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' with CTE_AGGREGATE as (' + char(13);
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' select ' + char(13)
+ ' distinct VALIDREVENUE.CONSTITUENTID, ' + char(13);
set @FUNCTIONSQL = @FUNCTIONSQL +
case
when @REVENUECRITERIACODE = 0 then
' avg(VALIDREVENUE.AMOUNT) as AMOUNT ' + char(13)
when @REVENUECRITERIACODE = 1 then
' max(VALIDREVENUE.AMOUNT) as AMOUNT ' + char(13)
-- when @REVENUECRITERIACODE = 2 then
-- ' VALIDREVENUE.AMOUNT ' + char(13)
when @REVENUECRITERIACODE = 3 then
' sum(VALIDREVENUE.AMOUNT) as AMOUNT ' + char(13)
end;
if @CONSTITUENTINCLUDECODE > 1
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' from CTE_VALIDREVENUE VALIDREVENUE ' + char(13);
else
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' from dbo.UFN_REVENUELIFECYCLE_GETVALIDREVENUE() VALIDREVENUE ' + char(13)
/*
if @REVENUECRITERIACODE = 2
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' where VALIDREVENUE.DATE = (' + char(13)
+ ' select max(DATE) ' + char(13)
+ ' from CTE_VALIDREVENUE VALIDREVENUE2 ' + char(13)
+ ' where VALIDREVENUE2.CONSTITUENTID = VALIDREVENUE.CONSTITUENTID ' + char(13)
+ ' ) ' + char(13);
else
*/
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' group by VALIDREVENUE.CONSTITUENTID ' + char(13)
+ ' ) ' + char(13);
end
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' select ' + char(13)
+ ' distinct CONSTITUENT.ID ' + char(13)
+ ' from dbo.CONSTITUENT ' + char(13)
if @CONSIDERREVENUEHISTORY = 1
begin
if @REVENUECRITERIACODE = 2
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' left outer join CTE_VALIDREVENUE VALIDREVENUE ' + char(13)
+ ' on CONSTITUENT.ID = [VALIDREVENUE].CONSTITUENTID ' + char(13);
else
set @FUNCTIONSQL = @FUNCTIONSQL
+ ' left outer join CTE_AGGREGATE [AGGREGATE] ' + char(13)
+ ' on CONSTITUENT.ID = [AGGREGATE].CONSTITUENTID ' + char(13);
end
-- All donors
if @CONSTITUENTINCLUDECODE = 1
set @WHERECLAUSE = @WHERECLAUSE
+ 'where dbo.UFN_CONSTITUENT_ISDONOR(CONSTITUENT.ID) = 1 ' + char(13)
-- LYBUNTs
if @CONSTITUENTINCLUDECODE = 2
set @WHERECLAUSE = @WHERECLAUSE
+ 'where exists( ' + char(13)
+ ' select ID ' + char(13)
+ ' from CTE_VALIDREVENUE ' + char(13)
+ ' where CTE_VALIDREVENUE.CONSTITUENTID = CONSTITUENT.ID ' + char(13)
+ ' and CTE_VALIDREVENUE.[DATE] between ''' + cast(dateadd(yy, -2, @TOMORROWMIDNIGHT) as nvarchar(100)) + ''' and ''' + cast(dateadd(yy, -1, @TOMORROWMIDNIGHT) as nvarchar(100)) + '''' + char(13)
-- + ' and CTE_VALIDREVENUE.[DATE] between dateadd(yy, -2, @TOMORROWMIDNIGHT) and dateadd(yy, -1, @TOMORROWMIDNIGHT) ' + char(13)
+ ' )' + char(13)
+ ' and not exists(' + char(13)
+ ' select ID ' + char(13)
+ ' from CTE_VALIDREVENUE ' + char(13)
+ ' where CTE_VALIDREVENUE.CONSTITUENTID = CONSTITUENT.ID ' + char(13)
+ ' and CTE_VALIDREVENUE.[DATE] between ''' + cast(dateadd(yy, -1, @TOMORROWMIDNIGHT) as nvarchar(100)) + ''' and ''' + cast(@TOMORROWMIDNIGHT as nvarchar(100)) + '''' + char(13)
-- + ' and CTE_VALIDREVENUE.[DATE] between dateadd(yy, -1, @TOMORROWMIDNIGHT) and @TOMORROWMIDNIGHT ' + char(13)
+ ' )' + char(13);
-- SYBUNTS
if @CONSTITUENTINCLUDECODE = 3
set @WHERECLAUSE = @WHERECLAUSE
+ 'where exists( ' + char(13)
+ ' select ID ' + char(13)
+ ' from CTE_VALIDREVENUE ' + char(13)
+ ' where CTE_VALIDREVENUE.CONSTITUENTID = CONSTITUENT.ID ' + char(13)
+ ' and CTE_VALIDREVENUE.[DATE] < ''' + cast(dateadd(yy, -1, @TOMORROWMIDNIGHT) as nvarchar(100)) + '''' + char(13)
+ ' )' + char(13)
+ ' and not exists(' + char(13)
+ ' select ID ' + char(13)
+ ' from CTE_VALIDREVENUE ' + char(13)
+ ' where CTE_VALIDREVENUE.CONSTITUENTID = CONSTITUENT.ID ' + char(13)
+ ' and CTE_VALIDREVENUE.[DATE] between ''' + cast(dateadd(yy, -1, @TOMORROWMIDNIGHT) as nvarchar(100)) + ''' and ''' + cast(@TOMORROWMIDNIGHT as nvarchar(100)) + '''' + char(13)
+ ' )' + char(13);
-- Handle revenue history filters
if @CONSIDERREVENUEHISTORY = 1
begin
if len(@WHERECLAUSE) > 0
set @WHERECLAUSE = @WHERECLAUSE
+ ' and ' + char(13);
else
set @WHERECLAUSE = @WHERECLAUSE
+ 'where ' + char(13);
if @REVENUECRITERIACODE = 2
set @WHERECLAUSE = @WHERECLAUSE
+ 'VALIDREVENUE.DATE = (' + char(13)
+ ' select max(DATE) ' + char(13)
+ ' from CTE_VALIDREVENUE VALIDREVENUE2 ' + char(13)
+ ' where VALIDREVENUE2.CONSTITUENTID = VALIDREVENUE.CONSTITUENTID ' + char(13)
+ ' ) ' + char(13);
else
set @WHERECLAUSE = @WHERECLAUSE
+ '('
+ ' (coalesce([AGGREGATE].AMOUNT, 0) between ' + cast(coalesce(@LOWREVENUEAMOUNT, 0) as nvarchar(100)) + ' and ' + cast(coalesce(@HIGHREVENUEAMOUNT, 0) as nvarchar(100)) + ') ' + char(13)
+ ' or (' + cast(coalesce(@HIGHREVENUEAMOUNT, 0) as nvarchar(100)) + ' = 0 and coalesce([AGGREGATE].AMOUNT, 0) >= ' + cast(coalesce(@LOWREVENUEAMOUNT, 0) as nvarchar(100)) + ') ' + char(13)
-- + ' or (coalesce(@LOWREVENUEAMOUNT, 0) is null and coalesce([AGGREGATE].AMOUNT, 0) <= ' + cast(@HIGHREVENUEAMOUNT as nvarchar(100)) + ') ' + char(13)
+ ' ) ' + char(13);
end
set @FUNCTIONSQL = @FUNCTIONSQL + @WHERECLAUSE;
exec sp_sqlexec @FUNCTIONSQL;
-- exec sp_executesql @FUNCTIONSQL, N'@TOMORROWMIDNIGHT datetime', @TOMORROWMIDNIGHT = @TOMORROWMIDNIGHT;
exec dbo.USP_IDSETREGISTER_CREATEORUPDATE @ID output, @SELECTIONNAME, '', @FUNCTIONNAME, 1, @RECORDTYPEID, 0, 0, null, @CHANGEAGENTID, @CURRENTAPPUSERID, null;
-- Make sure the RECORDTYPEID gets set. The SP doesn't update it on an UPDATE.
update dbo.IDSETREGISTER set
RECORDTYPEID = @RECORDTYPEID,
ACTIVE = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;