USP_EMAIL_DELETE_NULL_TRANSACTIONS

CREATE procedure dbo.USP_EMAIL_DELETE_NULL_TRANSACTIONS
(
  @EMAILID int
 )
as
begin
set nocount on;

-- The EmailJobRecipientID FK constraint was removed on donation transactions table in 4.0 SP1.

-- Keep the id when available from the NETCOMMUNITYEMAILJOBRECIPIENT table to maintain the constituent donation history


-- donation transactions

update dt set dt.EmailJobRecipientID = null
from DonationTransactions dt
inner join (
select dt.DonationTransactionsID ID from DonationTransactions dt
inner join EmailJob_Recipient ejr on ejr.ID = dt.EmailJobRecipientID
inner join Email e on e.ID = ejr.EmailID
left join NETCOMMUNITYEMAILJOBRECIPIENT ncejr on ncejr.BBNCMAPID = ejr.id
where e.id = @EMAILID
and ncejr.BBNCMAPID is null
) fk
on fk.ID = dt.DonationTransactionsID;

-- event transactions

update et set et.EmailJobRecipientID = null
from EventTransactions et
inner join (
select et.EventTransactionsId ID from EventTransactions et
inner join EmailJob_Recipient ejr on ejr.ID = et.EmailJobRecipientID
inner join Email e on e.ID = ejr.EmailID
where e.ID = @EMAILID
) fk
on fk.ID = et.EventTransactionsid;

-- membership transactions

update mt set mt.EmailJobRecipientID = null
from MembershipTransactions mt
inner join (
select mt.ID from MembershipTransactions mt
inner join EmailJob_Recipient ejr on ejr.ID = mt.EmailJobRecipientID
inner join Email e on e.ID = ejr.EmailID
where e.ID = @EMAILID
) fk
on fk.ID = mt.ID;

--profile update transactions

update pt set pt.EmailJobRecipientID = null
from ProfileUpdateTransactions pt
inner join (
select pt.ProfileUpdateTransactionsID ID from ProfileUpdateTransactions pt
inner join EmailJob_Recipient ejr on ejr.ID = pt.EmailJobRecipientID
inner join Email e on e.ID = ejr.EmailID
where e.ID = @EMAILID
) fk
on fk.ID = pt.ProfileUpdateTransactionsID;

--signup transactions

update st set st.EmailJobRecipientID = null
from SignupTransactions st
inner join (
select SignupTransactionsID ID from SignupTransactions st
inner join EmailJob_Recipient ejr on ejr.ID = st.EmailJobRecipientID
inner join Email e on e.ID = ejr.EmailID
where e.ID = @EMAILID
) fk
on fk.ID = st.SignupTransactionsID;

-- volunteer transactions

update vt set vt.EmailJobRecipientID = null
from VolunteerTransactions vt
inner join (
select vt.ID from VolunteerTransactions vt
inner join EmailJob_Recipient ejr on ejr.ID = vt.EmailJobRecipientID
inner join Email e on e.ID = ejr.EmailID
where e.ID = @EMAILID
) fk
on fk.ID = vt.ID;



-- Cleanup pending EMAILJOB_RECIPIENT_DONATED Info

--Reference from spREINT_GetEmailRecipientsInfo procedure, delete the record after updating the email recipients data in BBCRM

--As we are already deleting the email data, and this will never sync to BBCRM, cleaning up the transaction info


update EJRD set EJRD.EmailJobRecipientID = null, EMAILID = null
from dbo.EMAILJOB_RECIPIENT_DONATED EJRD where EMAILID = @EMAILID



end