UFN_SMARTQUERY_DONORANNIVERSARY
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ANNIVERSARYDATE | datetime | IN | |
| @REVENUESELECTIONID | uniqueidentifier | IN | |
| @CONSTITUENTSELECTIONID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @AMOUNTTYPE | tinyint | IN | |
| @AMOUNTONE | money | IN | |
| @AMOUNTTWO | money | IN | |
| @ONLYRETURNTOPDONORS | bit | IN | |
| @RETURNVALUE | int | IN | |
| @RETURNVALUEISPERCENT | bit | IN | |
| @ANNIVERSARYDATEISCURRENTDATE | bit | IN | |
| @REVENUEQUERYID | uniqueidentifier | IN | |
| @REVENUEQUERYTYPE | nvarchar(10) | IN | |
| @CONSTITUENTQUERYID | uniqueidentifier | IN | |
| @CONSTITUENTQUERYTYPE | nvarchar(10) | IN | |
| @CURRENCYID | uniqueidentifier | IN | |
| @CURRENCYCODE | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @MAXROWS | int | IN |
Definition
Copy
create function dbo.UFN_SMARTQUERY_DONORANNIVERSARY
(
@ANNIVERSARYDATE datetime,
@REVENUESELECTIONID uniqueidentifier = null,
@CONSTITUENTSELECTIONID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@AMOUNTTYPE tinyint = null,
@AMOUNTONE money = null,
@AMOUNTTWO money = null,
@ONLYRETURNTOPDONORS bit = null,
@RETURNVALUE int = null,
@RETURNVALUEISPERCENT bit = null,
@ANNIVERSARYDATEISCURRENTDATE bit = null,
@REVENUEQUERYID uniqueidentifier = null,
@REVENUEQUERYTYPE nvarchar(10) = null,
@CONSTITUENTQUERYID uniqueidentifier = null,
@CONSTITUENTQUERYTYPE nvarchar(10) = null,
@CURRENCYID uniqueidentifier = null,
@CURRENCYCODE tinyint = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@MAXROWS int = null
)
returns @RETURNTABLE table
(
ID uniqueidentifier,
NAME nvarchar(154),
ADDRESSBLOCK nvarchar(150),
CITY nvarchar(50),
STATE nvarchar(50),
POSTCODE nvarchar(12),
REVENUEDATE datetime,
AMOUNT money,
CURRENCYID uniqueidentifier
)
as
begin
declare @ISADMIN bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @CURRENCYID = case @CURRENCYCODE
when 0 then dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
end
declare @DAY int;
declare @MONTH int;
set @DAY = day(@ANNIVERSARYDATE);
set @MONTH = month(@ANNIVERSARYDATE);
select
@REVENUEQUERYID = dbo.UFN_IDSET_GETQUERYID(@REVENUESELECTIONID),
@REVENUEQUERYTYPE = dbo.UFN_IDSET_GETQUERYTYPE(@REVENUESELECTIONID),
@CONSTITUENTQUERYID = dbo.UFN_IDSET_GETQUERYID(@CONSTITUENTSELECTIONID),
@CONSTITUENTQUERYTYPE = dbo.UFN_IDSET_GETQUERYTYPE(@CONSTITUENTSELECTIONID);
-- Build list table of all revenue records that need to be included
declare @DATASET table
(
CONSTITUENTID uniqueidentifier,
REVENUEDATE datetime,
AMOUNT money
);
if @REVENUESELECTIONID is null and @CONSTITUENTSELECTIONID is null
begin
with REVENUE_CTE as
(
select
ID,
CONSTITUENTID,
DATE,
dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID, @CURRENCYID) AMOUNT
from
dbo.REVENUE
with (INDEX (IX_REVENUE_CONSTITUENTID_TRANSACTIONTYPECODE_DATE))
where
@ISADMIN = 1 or
(
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.ID) = 1 )
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
)
)
insert into @DATASET(CONSTITUENTID, REVENUEDATE, AMOUNT)
select
REVENUE_CTE.CONSTITUENTID,
REVENUE_CTE.DATE,
REVENUE_CTE.AMOUNT
from
REVENUE_CTE
--inner join REVENUE_CTE on REVENUE_CTE.ID = REVENUE.ID
where
day(REVENUE_CTE.DATE) = @DAY and
month(REVENUE_CTE.DATE) = @MONTH and
(@STARTDATE is null or REVENUE_CTE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) and
(@ENDDATE is null or REVENUE_CTE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) and
not exists(select 1 from REVENUE_CTE R where R.CONSTITUENTID = REVENUE_CTE.CONSTITUENTID and R.DATE > REVENUE_CTE.DATE)
end
else if @REVENUESELECTIONID is null
begin
with REVENUE_CTE as
(
select
ID,
CONSTITUENTID,
DATE,
dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID, @CURRENCYID) AMOUNT
from
dbo.REVENUE
with (INDEX (IX_REVENUE_CONSTITUENTID_TRANSACTIONTYPECODE_DATE))
where
@ISADMIN = 1 or
(
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.ID) = 1 )
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
)
)
insert into @DATASET(CONSTITUENTID, REVENUEDATE, AMOUNT)
select
REVENUE.CONSTITUENTID,
REVENUE.DATE,
REVENUE.AMOUNT
from
REVENUE_CTE as REVENUE
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID) SELECTION on REVENUE.CONSTITUENTID = SELECTION.ID and @CONSTITUENTSELECTIONID is not null
where
day(REVENUE.DATE) = @DAY and
month(REVENUE.DATE) = @MONTH and
(@STARTDATE is null or REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) and
(@ENDDATE is null or REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) and
not exists(select 1 from REVENUE_CTE R where R.CONSTITUENTID = REVENUE.CONSTITUENTID and R.DATE > REVENUE.DATE)
end
else if @CONSTITUENTSELECTIONID is null
begin
with REVENUE_CTE as
(
select
ID,
CONSTITUENTID,
DATE,
dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID, @CURRENCYID) AMOUNT
from
dbo.REVENUE
with (INDEX (IX_REVENUE_CONSTITUENTID_TRANSACTIONTYPECODE_DATE))
where
@ISADMIN = 1 or
(
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.ID) = 1 )
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
)
)
insert into @DATASET(CONSTITUENTID, REVENUEDATE, AMOUNT)
select
REVENUE.CONSTITUENTID,
REVENUE.DATE,
REVENUE.AMOUNT
from
REVENUE_CTE as REVENUE
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID) SELECTION on REVENUE.ID = SELECTION.ID and @REVENUESELECTIONID is not null
where
day(REVENUE.DATE) = @DAY and
month(REVENUE.DATE) = @MONTH and
(@STARTDATE is null or REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) and
(@ENDDATE is null or REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) and
not exists(select 1 from REVENUE_CTE R where R.CONSTITUENTID = REVENUE.CONSTITUENTID and R.DATE > REVENUE.DATE)
end
else
begin
with REVENUE_CTE as
(
select
ID,
CONSTITUENTID,
DATE,
dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID, @CURRENCYID) AMOUNT
from
dbo.REVENUE
with (INDEX (IX_REVENUE_CONSTITUENTID_TRANSACTIONTYPECODE_DATE))
where
@ISADMIN = 1 or
(
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.ID) = 1 )
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
and
(dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '6A51C0E5-B81C-4962-9BBA-03CD86AE5003', REVENUE.CONSTITUENTID) = 1)
)
)
insert into @DATASET(CONSTITUENTID, REVENUEDATE, AMOUNT)
select
REVENUE.CONSTITUENTID,
REVENUE.DATE,
REVENUE.AMOUNT
from
REVENUE_CTE as REVENUE
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID) REVENUESELECTION on REVENUE.ID = REVENUESELECTION.ID and @REVENUESELECTIONID is not null
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID) CONSTITUENTSELECTION on REVENUE.CONSTITUENTID = CONSTITUENTSELECTION.ID and @CONSTITUENTSELECTIONID is not null
where
day(REVENUE.DATE) = @DAY and
month(REVENUE.DATE) = @MONTH and
(@STARTDATE is null or REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) and
(@ENDDATE is null or REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) and
not exists(select 1 from REVENUE_CTE R where R.CONSTITUENTID = REVENUE.CONSTITUENTID and R.DATE > REVENUE.DATE)
end
-- Calculate number of rows returned
if @ONLYRETURNTOPDONORS = 1
begin
if @RETURNVALUEISPERCENT = 1
begin
select
@MAXROWS = ceiling(count(CONSTITUENTID) * (convert(decimal,@RETURNVALUE) / 100.00))
from
@DATASET;
end
else
set @MAXROWS = @RETURNVALUE;
end;
-- Insert filtered records into the return table
insert into @RETURNTABLE
select top(@MAXROWS) with ties
CONSTITUENT.ID,
CONSTITUENT.NAME,
ADDRESS.ADDRESSBLOCK,
ADDRESS.CITY,
dbo.UFN_STATE_GETABBREVIATION(ADDRESS.STATEID),
ADDRESS.POSTCODE,
DS.REVENUEDATE,
DS.AMOUNT,
@CURRENCYID
from
@DATASET DS
inner join
dbo.CONSTITUENT on CONSTITUENT.ID = DS.CONSTITUENTID
left join
dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
where
(DS.AMOUNT > @AMOUNTONE and @AMOUNTTYPE = 0) or
(DS.AMOUNT < @AMOUNTONE and @AMOUNTTYPE = 1) or
(DS.AMOUNT between @AMOUNTONE and @AMOUNTTWO and @AMOUNTTYPE = 2)
order by
(DS.AMOUNT) desc,CONSTITUENT.KEYNAME,CONSTITUENT.FIRSTNAME,CONSTITUENT.MIDDLENAME;
return;
end