USP_BATCHREVENUE_DELETE

CREATE procedure dbo.USP_BATCHREVENUE_DELETE
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
begin
  --check deletion rules, if any


  declare @SOURCEREVENUEID uniqueidentifier;
  declare @CURRENTDATE datetime = getdate();

  declare @contextCache varbinary(128);
  set @contextCache = CONTEXT_INFO();

  if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

  delete from dbo.BATCHREVENUESPLIT where BATCHREVENUEID = @ID;

  exec dbo.USP_REVENUEBATCH_CLEARSCHEDULEISPENDINGFLAG @REVENUEBATCHID = @ID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;

  declare @PLEDGEIDS as table (ID uniqueidentifier);
  insert into @PLEDGEIDS select BATCHREVENUEAPPLICATIONPLEDGEID from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID
  delete from dbo.BATCHREVENUEAPPLICATIONPLEDGE where ID in (select ID from @PLEDGEIDS);

  declare @MEMBERSHIPIDS as table (ID uniqueidentifier);
  insert into @MEMBERSHIPIDS select BATCHREVENUEAPPLICATIONMEMBERSHIPID from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID
  delete from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP where ID in (select ID from @MEMBERSHIPIDS);

  -- remove any revenue batch registrant guests of the this registrant

  -- Delete any registrant registration maps for this registrant and their guests

  -- Deleting from REGISTRANTREGISTRATIONMAP cascade deletes REGISTRANTPREFERENCEMAP

  delete from
    dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
  where
    BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID in
    (
      select
        BATCHREVENUEREGISTRANT.ID
      from
        dbo.BATCHREVENUEREGISTRANT
      inner join
        dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID = BATCHREVENUEREGISTRANT.ID
      where
        BATCHREVENUEAPPLICATION.BATCHREVENUEID = @ID
    )
    or 
    BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID in
    (
      select
        GUEST.ID
      from
        dbo.BATCHREVENUEREGISTRANT GUEST
      inner join
        dbo.BATCHREVENUEREGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
      inner join
        dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID = HOST.ID
      where
        BATCHREVENUEAPPLICATION.BATCHREVENUEID = @ID
    );

  --Remove the registrant's guests

  delete from
    dbo.BATCHREVENUEREGISTRANT
  where
    BATCHREVENUEREGISTRANT.ID in
    (
      select
        GUEST.ID
      from 
        dbo.BATCHREVENUEREGISTRANT GUEST
      inner join
        dbo.BATCHREVENUEREGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
      inner join
        dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID = HOST.ID
      where
        BATCHREVENUEAPPLICATION.BATCHREVENUEID = @ID
    );

  declare @BATCHREVENUEREGISTRANTS table (ID uniqueidentifier);
  insert into @BATCHREVENUEREGISTRANTS(ID)
  (
    select
      BATCHREVENUEREGISTRANT.ID
    from
      dbo.BATCHREVENUEREGISTRANT
    inner join
      dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID = BATCHREVENUEREGISTRANT.ID
    where
      BATCHREVENUEAPPLICATION.BATCHREVENUEID = @ID
  );

  --BATCHREVENUEAPPLICATION cannot support multiple cascade paths so set the batch revenue registrant IDs to null before deleting

  update dbo.BATCHREVENUEAPPLICATION set
    BATCHREVENUEREGISTRANTID = null,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where
    BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID is not null
    and
    BATCHREVENUEAPPLICATION.BATCHREVENUEID = @ID;

  --Remove the registrant

  delete from
    dbo.BATCHREVENUEREGISTRANT
  where
    BATCHREVENUEREGISTRANT.ID in (select ID from @BATCHREVENUEREGISTRANTS)
    and BATCHREVENUEREGISTRANT.ID not in (select BATCHREVENUEREGISTRANTID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEREGISTRANTID is not null);

  delete from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID;
  delete from dbo.BATCHREVENUERECOGNITION where BATCHREVENUEID = @ID;

  --remove revenue group members of groups you are about to delete

  delete from
    dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
  where
    MEMBERID in 
    (select BRC.ID from dbo.BATCHREVENUECONSTITUENT BRC
      inner join dbo.BATCHREVENUE BR on BR.CONSTITUENTID = BRC.ID
      where BR.ID = @ID);

  --remove revenue constituent if the constituent was created in this batch

  delete
    from dbo.BATCHREVENUECONSTITUENT
  where
    ID in
    (select BRC.ID from dbo.BATCHREVENUECONSTITUENT BRC
      inner join dbo.BATCHREVENUE BR on BR.CONSTITUENTID = BRC.ID
      where BR.ID = @ID);

  delete from dbo.BATCHREVENUECONSTITUENTACCOUNT
  where BATCHREVENUECONSTITUENTACCOUNT.ID in
    (
      select CONSTITUENTACCOUNTID
      from dbo.BATCHREVENUE
      where BATCHREVENUE.ID = @ID
    );

  --SEPAMANDATEACTIVITY stores a BATCHROWID column that can store either ERB rows or membership dues batch rows. This is a guid column because we cannot have two FK's due to 

  -- multiple cascade paths

  update dbo.SEPAMANDATEACTIVITY set
    BATCHROWID = null
  where
    SEPAMANDATEACTIVITY.BATCHROWID = @ID;

  --remove BBIS transaction info if batch row was created by BBIS donation transaction.

  declare @BBNCTRANID int = 0;
  select @BBNCTRANID = (select top 1 BBNCTRANID
  from dbo.BATCHREVENUEBBNCINFO
  where ID in
    (select BRB.ID from dbo.BATCHREVENUEBBNCINFO BRB
     inner join dbo.BATCHREVENUE BR on BR.ID = BRB.BATCHREVENUEID
     where BR.ID = @ID));

  delete
  from dbo.BATCHREVENUEBBNCINFO
  where ID in
    (select BRB.ID from dbo.BATCHREVENUEBBNCINFO BRB
     inner join dbo.BATCHREVENUE BR on BR.ID = BRB.BATCHREVENUEID
     where BR.ID = @ID);

  if @BBNCTRANID > 0
    exec [dbo].[spTransactions_DeleteDonation] @BBNCTRANID

      --remove the constituent update batch row for constituent edits

  exec USP_BATCHCONSTITUENTUPDATE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

  exec USP_BATCHREVENUE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  return 0;
end