USP_CONSTITUENCYDATERANGE_REFRESHLOYALDONORCONSTITUENCY
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CONSTITUENCYREFRESHPROCESSID | uniqueidentifier | IN | |
| @RECORDCOUNT | int | INOUT |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENCYDATERANGE_REFRESHLOYALDONORCONSTITUENCY
(
@CONSTITUENTID uniqueidentifier = null,
@CONSTITUENCYREFRESHPROCESSID uniqueidentifier = null,
@RECORDCOUNT int = 0 output
)
as
begin
set nocount on;
set @RECORDCOUNT = 0;
declare @CURRENTDATE datetime = getdate();
declare @LOYALDONORCONSTITUENCYDEFINITIONID uniqueidentifier = 'E5A0EA42-65BA-4B25-AFE2-9B709F99E72B';
declare @LOYALDONORCONSTITUENCYDEFPERIOD integer;
declare @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE smallint;
declare @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION bit;
declare @PERIODSFROMCURRENTDATE datetime;
select
@LOYALDONORCONSTITUENCYDEFPERIOD = LOYALDONORCONSTITUENCYDEFPERIOD,
@LOYALDONORCONSTITUENCYDEFPERIODUNITCODE = LOYALDONORCONSTITUENCYDEFPERIODUNITCODE,
@MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION
from
dbo.CONSTITUENCYCRITERIASPOUSE
if @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE = 1
begin
set @PERIODSFROMCURRENTDATE = dateadd(mm, -@LOYALDONORCONSTITUENCYDEFPERIOD, @CURRENTDATE);
end
else
begin
set @PERIODSFROMCURRENTDATE = dateadd(yy, -@LOYALDONORCONSTITUENCYDEFPERIOD, @CURRENTDATE);
end
begin try
if @CONSTITUENTID is not null and @CONSTITUENCYREFRESHPROCESSID is not null
begin
raiserror('ERR_REFRESH_CONSTITUENTANDSELECTION : A constituency refresh cannot be run with both a specific constituent and a selection.', 13, 1);
end
if object_id('tempdb..#LOYALDONORCONSTITUENTS') is not null
begin
drop table #LOYALDONORCONSTITUENTS;
end
create table #LOYALDONORCONSTITUENTS
(
CONSTITUENTID uniqueidentifier
);
if @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
begin
if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
begin
insert into #LOYALDONORCONSTITUENTS
(
CONSTITUENTID
)
select CONSTITUENTID
from (
select distinct
REVENUEANDRECOGNITION.CONSTITUENTID,
REVENUEANDRECOGNITION.PERIOD
from (
select distinct
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end as PERIOD
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock)
on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
where
REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUE.DELETEDON is null and
FTLT.DELETEDON is null and
FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
union all
select distinct
REVENUERECOGNITION.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT with (nolock)
on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.REVENUERECOGNITION with (nolock)
on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
where
REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUESPLIT.DELETEDON is null and
REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID and
REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
REVENUERECOGNITION.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
) as REVENUEANDRECOGNITION
group by
REVENUEANDRECOGNITION.CONSTITUENTID,
REVENUEANDRECOGNITION.PERIOD
) as SUBSQL
group by
SUBSQL.CONSTITUENTID
having count(*) >= @LOYALDONORCONSTITUENCYDEFPERIOD
end
else
begin
insert into #LOYALDONORCONSTITUENTS
(
CONSTITUENTID
)
select CONSTITUENTID
from (
select distinct
CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end as PERIOD
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock)
on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
where
REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUE.DELETEDON is null and
FTLT.DELETEDON is null and
FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
) as SUBSQL
group by
CONSTITUENTID
having count(*) >= @LOYALDONORCONSTITUENCYDEFPERIOD
end
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join #LOYALDONORCONSTITUENTS as LOYALDONORCONSTITUENTS
on LOYALDONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
where
LOYALDONORCONSTITUENTS.CONSTITUENTID is null
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @LOYALDONORCONSTITUENCYDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
else if @CONSTITUENCYREFRESHPROCESSID is null
begin
if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
begin
insert into #LOYALDONORCONSTITUENTS
(
CONSTITUENTID
)
select CONSTITUENTID
from (
select distinct
REVENUEANDRECOGNITION.CONSTITUENTID,
REVENUEANDRECOGNITION.PERIOD
from (
select distinct
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end as PERIOD
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock)
on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
where
REVENUE.CONSTITUENTID = @CONSTITUENTID and
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUE.DELETEDON is null and
FTLT.DELETEDON is null and
FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
union all
select distinct
REVENUERECOGNITION.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT with (nolock)
on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.REVENUERECOGNITION with (nolock)
on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
where
REVENUE.CONSTITUENTID = @CONSTITUENTID and
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUESPLIT.DELETEDON is null and
REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID and
REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
REVENUERECOGNITION.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
) as REVENUEANDRECOGNITION
group by
REVENUEANDRECOGNITION.CONSTITUENTID,
REVENUEANDRECOGNITION.PERIOD
) as SUBSQL
group by
SUBSQL.CONSTITUENTID
having count(*) >= @LOYALDONORCONSTITUENCYDEFPERIOD
end
else
begin
insert into #LOYALDONORCONSTITUENTS
(
CONSTITUENTID
)
select CONSTITUENTID
from (
select distinct
CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end as PERIOD
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock)
on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
where
REVENUE.CONSTITUENTID = @CONSTITUENTID and
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUE.DELETEDON is null and
FTLT.DELETEDON is null and
FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
) as SUBSQL
group by
CONSTITUENTID
having count(*) >= @LOYALDONORCONSTITUENCYDEFPERIOD
end
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join #LOYALDONORCONSTITUENTS as LOYALDONORCONSTITUENTS
on LOYALDONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
where
CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
and LOYALDONORCONSTITUENTS.CONSTITUENTID is null
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @LOYALDONORCONSTITUENCYDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
else
begin
if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
begin
insert into #LOYALDONORCONSTITUENTS
(
CONSTITUENTID
)
select CONSTITUENTID
from (
select distinct
REVENUEANDRECOGNITION.CONSTITUENTID,
REVENUEANDRECOGNITION.PERIOD
from (
select distinct
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end as PERIOD
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock)
on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = REVENUE.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
where
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUE.DELETEDON is null and
FTLT.DELETEDON is null and
FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
union all
select distinct
REVENUERECOGNITION.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT with (nolock)
on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.REVENUERECOGNITION with (nolock)
on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = REVENUE.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
where
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUESPLIT.DELETEDON is null and
REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID and
REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
REVENUERECOGNITION.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
) as REVENUEANDRECOGNITION
group by
REVENUEANDRECOGNITION.CONSTITUENTID,
REVENUEANDRECOGNITION.PERIOD
) as SUBSQL
group by
SUBSQL.CONSTITUENTID
having count(*) >= @LOYALDONORCONSTITUENCYDEFPERIOD
end
else
begin
insert into #LOYALDONORCONSTITUENTS
(
CONSTITUENTID
)
select CONSTITUENTID
from (
select distinct
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end as PERIOD
from
dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT with (nolock)
on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLT with (nolock)
on FTLT.FINANCIALTRANSACTIONID = REVENUE.ID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = REVENUE.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
where
REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors
cast(REVENUE.DATE as datetime) >= @PERIODSFROMCURRENTDATE and
REVENUE.DELETEDON is null and
FTLT.DELETEDON is null and
FTLT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
group by
REVENUE.CONSTITUENTID,
case @LOYALDONORCONSTITUENCYDEFPERIODUNITCODE
when 1 then
datediff(mm, cast(REVENUE.DATE as datetime), @CURRENTDATE)
else
datediff(yy, cast(REVENUE.DATE as datetime), @CURRENTDATE)
end
) as SUBSQL
group by
CONSTITUENTID
having count(*) >= @LOYALDONORCONSTITUENCYDEFPERIOD
end
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join #LOYALDONORCONSTITUENTS as LOYALDONORCONSTITUENTS
on LOYALDONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
where
LOYALDONORCONSTITUENTS.CONSTITUENTID is null
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @LOYALDONORCONSTITUENCYDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
insert into dbo.CONSTITUENCYDATERANGE
(
ID,
CONSTITUENTID,
CONSTITUENCYDEFINITIONID,
REFRESHDATE
)
select
newid(),
LOYALDONORCONSTITUENTS.CONSTITUENTID,
@LOYALDONORCONSTITUENCYDEFINITIONID,
@CURRENTDATE
from
#LOYALDONORCONSTITUENTS as LOYALDONORCONSTITUENTS
left join dbo.CONSTITUENCYDATERANGE
on CONSTITUENCYDATERANGE.CONSTITUENTID = LOYALDONORCONSTITUENTS.CONSTITUENTID
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @LOYALDONORCONSTITUENCYDEFINITIONID
where
CONSTITUENCYDATERANGE.ID is null;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
update dbo.CONSTITUENCYDATERANGE
set
CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
from
dbo.CONSTITUENCYDATERANGE
inner join #LOYALDONORCONSTITUENTS as LOYALDONORCONSTITUENTS
on LOYALDONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
where
CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @LOYALDONORCONSTITUENCYDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
drop table #LOYALDONORCONSTITUENTS;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end