USP_CONSTITUENCYDATERANGE_REFRESHPLANNEDGIVERCONSTITUENCY
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CONSTITUENCYREFRESHPROCESSID | uniqueidentifier | IN | |
| @RECORDCOUNT | int | INOUT |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENCYDATERANGE_REFRESHPLANNEDGIVERCONSTITUENCY
(
@CONSTITUENTID uniqueidentifier = null,
@CONSTITUENCYREFRESHPROCESSID uniqueidentifier = null,
@RECORDCOUNT int = 0 output
)
as
begin
set nocount on;
set @RECORDCOUNT = 0;
declare @CURRENTDATE datetime = getdate();
declare @PLANNEDGIVERDEFINITIONID uniqueidentifier = 'CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634';
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 @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
begin
insert into dbo.CONSTITUENCYDATERANGE
(
ID,
CONSTITUENTID,
CONSTITUENCYDEFINITIONID,
DATEFROM,
REFRESHDATE
)
select
newid(),
PLANNEDGIFT.CONSTITUENTID,
@PLANNEDGIVERDEFINITIONID,
cast(min(PLANNEDGIFT.GIFTDATE) as date),
@CURRENTDATE
from
dbo.PLANNEDGIFT
left join dbo.CONSTITUENCYDATERANGE
on CONSTITUENCYDATERANGE.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
and CONSTITUENCYDATERANGE.ID is null
group by
PLANNEDGIFT.CONSTITUENTID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
update dbo.CONSTITUENCYDATERANGE
set
DATEFROM = PLANNEDGIFT.DATEFROM,
CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
from
dbo.CONSTITUENCYDATERANGE
inner join (
select
CONSTITUENTID,
cast(min(PLANNEDGIFT.GIFTDATE) as date) as DATEFROM
from
dbo.PLANNEDGIFT
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
group by
PLANNEDGIFT.CONSTITUENTID
) PLANNEDGIFT
on PLANNEDGIFT.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
where
CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join (
select distinct
CONSTITUENTID
from
dbo.PLANNEDGIFT
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
) PLANNEDGIFT
on PLANNEDGIFT.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
where
PLANNEDGIFT.CONSTITUENTID is null
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
else if @CONSTITUENCYREFRESHPROCESSID is null
begin
insert into dbo.CONSTITUENCYDATERANGE
(
ID,
CONSTITUENTID,
CONSTITUENCYDEFINITIONID,
DATEFROM,
REFRESHDATE
)
select
newid(),
PLANNEDGIFT.CONSTITUENTID,
@PLANNEDGIVERDEFINITIONID,
cast(min(PLANNEDGIFT.GIFTDATE) as date),
@CURRENTDATE
from
dbo.PLANNEDGIFT
left join dbo.CONSTITUENCYDATERANGE
on CONSTITUENCYDATERANGE.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
and CONSTITUENCYDATERANGE.ID is null
and PLANNEDGIFT.CONSTITUENTID = @CONSTITUENTID
group by
PLANNEDGIFT.CONSTITUENTID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
update dbo.CONSTITUENCYDATERANGE
set
DATEFROM = PLANNEDGIFT.DATEFROM,
CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
from
dbo.CONSTITUENCYDATERANGE
inner join (
select
CONSTITUENTID,
cast(min(PLANNEDGIFT.GIFTDATE) as date) as DATEFROM
from
dbo.PLANNEDGIFT
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
group by
PLANNEDGIFT.CONSTITUENTID
) PLANNEDGIFT
on PLANNEDGIFT.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
where
CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID
and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join (
select distinct
CONSTITUENTID
from
dbo.PLANNEDGIFT
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
) PLANNEDGIFT
on PLANNEDGIFT.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
where
PLANNEDGIFT.CONSTITUENTID is null
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID
and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
else
begin
insert into dbo.CONSTITUENCYDATERANGE
(
ID,
CONSTITUENTID,
CONSTITUENCYDEFINITIONID,
DATEFROM,
REFRESHDATE
)
select
newid(),
PLANNEDGIFT.CONSTITUENTID,
@PLANNEDGIVERDEFINITIONID,
cast(min(PLANNEDGIFT.GIFTDATE) as date),
@CURRENTDATE
from
dbo.PLANNEDGIFT
left join dbo.CONSTITUENCYDATERANGE
on CONSTITUENCYDATERANGE.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
and CONSTITUENCYDATERANGE.ID is null
group by
PLANNEDGIFT.CONSTITUENTID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
update dbo.CONSTITUENCYDATERANGE
set
DATEFROM = PLANNEDGIFT.DATEFROM,
CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
from
dbo.CONSTITUENCYDATERANGE
inner join (
select
CONSTITUENTID,
cast(min(PLANNEDGIFT.GIFTDATE) as date) as DATEFROM
from
dbo.PLANNEDGIFT
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
group by
PLANNEDGIFT.CONSTITUENTID
) PLANNEDGIFT
on PLANNEDGIFT.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
where
CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join (
select distinct
CONSTITUENTID
from
dbo.PLANNEDGIFT
where
(
PLANNEDGIFT.STATUSCODE = 2
or PLANNEDGIFT.STATUSCODE = 5
)
and exists (
select
ID
from
dbo.PLANNEDGIFTDESIGNATION
where
PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PLANNEDGIFT.ID
)
) PLANNEDGIFT
on PLANNEDGIFT.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
where
PLANNEDGIFT.CONSTITUENTID is null
and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PLANNEDGIVERDEFINITIONID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end