USP_BATCHREVENUE_DELETEBATCH

CREATE PROCEDURE dbo.USP_BATCHREVENUE_DELETEBATCH
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
AS
BEGIN
  set nocount on;

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

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

  begin try
    declare @lockName nvarchar(36);
    set @lockName = upper(cast(@ID as nvarchar(36)));

    declare @result int;

    exec @result = sp_getapplock @Resource=@lockName, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=0;

    if @result = 0
    begin
      if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

      set CONTEXT_INFO @CHANGEAGENTID;

      if @CURRENTAPPUSERID is not null
      begin
        declare @GRANTED bit
        select @GRANTED=dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER(@CURRENTAPPUSERID, @ID)
        if @GRANTED = 0
          raiserror('You do not have permission to delete this batch.', 13, 1);
      end;

      -- Cannot delete if there is an uncommitted batch that originated from this one,

      -- as these records may be associated with that batch. These records will be deleted

      -- when that batch is committed or deleted.

      -- Example: an exception batch

      if not exists (select ID from dbo.BATCH where ORIGINATINGBATCHID = @ID and STATUSCODE = 0)
      begin
        --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.BATCHID = @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.BATCHID = @ID
          );

        delete from dbo.BATCHREVENUEFINANCIALINSTITUTION
        where BATCHREVENUEFINANCIALINSTITUTION.ID in
          (
            select BATCHREVENUECONSTITUENTACCOUNT.FINANCIALINSTITUTIONID
            from dbo.BATCHREVENUECONSTITUENTACCOUNT
            where BATCHREVENUECONSTITUENTACCOUNT.BATCHID = @ID
          );

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

      --if status is committed then don't update the ISPENDING

      if(select STATUSCODE from dbo.BATCH where ID = @ID) <> 1
        begin
          update dbo.REVENUESCHEDULE
          set
            REVENUESCHEDULE.ISPENDING = 0,
            REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
            REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
          from dbo.REVENUESCHEDULE
            inner join dbo.BATCHREVENUEAPPLICATION as [STREAMS] on [STREAMS].REVENUEID = REVENUESCHEDULE.ID and [STREAMS].WASGENERATED = 1
          where [STREAMS].BATCHREVENUEID in (select ID from dbo.BATCHREVENUE where BATCHID = @ID);

          update dbo.REVENUESCHEDULE
          set
            REVENUESCHEDULE.ISPENDING = 0,
            REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
            REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
          from dbo.REVENUESCHEDULE
            inner join dbo.BATCHREVENUE on BATCHREVENUE.PAYINGPENDINGREVENUEID = REVENUESCHEDULE.ID
          where BATCHREVENUE.BATCHID = @ID;
        end

      delete from dbo.BATCHREVENUESPLIT where BATCHREVENUEID in (select ID from dbo.BATCHREVENUE where BATCHID = @ID);

      delete from dbo.BATCHREVENUEAPPLICATIONPLEDGE where ID in
        (select BATCHREVENUEAPPLICATIONPLEDGEID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID in
        (select ID from dbo.BATCHREVENUE where BATCHID = @ID));
      delete from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP where ID in
        (select BATCHREVENUEAPPLICATIONMEMBERSHIPID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID in
        (select ID from dbo.BATCHREVENUE where BATCHID = @ID));

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

      -- Delete any registrant registration maps for the registrants and their guests

      -- Deleting from REGISTRANTREGISTRATIONMAP cascade deletes REGISTRANTPREFERENCEMAP

      delete from dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
      where
        BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID in
          (
            select BATCHREVENUEREGISTRANT.ID
            from dbo.BATCHREVENUEREGISTRANT
            where BATCHREVENUEREGISTRANT.BATCHID = @ID
          )
        or
        BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID in
          (
            select GUEST.ID
            from dbo.BATCHREVENUEREGISTRANT GUEST
              inner join dbo.BATCHREVENUEREGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
            where HOST.BATCHID = @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
          where HOST.BATCHID = @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
      from dbo.BATCHREVENUEAPPLICATION
        inner join dbo.BATCHREVENUE on BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
      where
        BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID is not null and
        BATCHREVENUE.BATCHID = @ID;

      --Remove the registrant

      delete from dbo.BATCHREVENUEREGISTRANT
      where BATCHREVENUEREGISTRANT.BATCHID = @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
      from dbo.SEPAMANDATEACTIVITY
        inner join dbo.BATCHREVENUE on BATCHREVENUE.ID = SEPAMANDATEACTIVITY.BATCHROWID
      where BATCHREVENUE.BATCHID = @ID;

      delete from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID in (select ID from dbo.BATCHREVENUE where BATCHID = @ID);
      delete from dbo.BATCHREVENUERECOGNITION where BATCHREVENUEID in (select ID from dbo.BATCHREVENUE where BATCHID = @ID);

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

      declare @BATCHSTATUS int;
      select @BATCHSTATUS = dbo.UFN_BATCH_GETSTATUSCODE (@ID);

      --only transactions from open batches need to be downloaded again

      if @BATCHSTATUS = 0 
      begin
        declare @BBNCTRANSACTIONIDS xml = (select * from dbo.UFN_BBNC_DONATION_GETROWBBNCIDS(@ID) for xml raw('INT'),type,root('TRANSACTIONIDS'),BINARY BASE64);
        exec dbo.USP_BBNC_TRANSACTION_DELETEBATCH @BBNCTRANSACTIONIDS;
      end

      delete from dbo.BATCHREVENUEBBNCINFO where BATCHREVENUEID in (select ID from dbo.BATCHREVENUE where BATCHID = @ID);

      --remove as well the constituent update batch entries created used by ERB existing constituent edits;

      delete from dbo.BATCHCONSTITUENTUPDATE where BATCHID = @ID;

      --remove row from base table

      delete from dbo.BATCHREVENUE where BATCHID = @ID;

      -- remove any CC/EFT events tied to this batch

      delete from dbo.RECURRINGGIFTINSTALLMENTEVENT where BATCHID = @ID;

      exec sp_releaseapplock @Resource=@lockName, @LockOwner='Session';

      if not @contextCache is null
        set CONTEXT_INFO @contextCache; 
    end;
    else
      raiserror('This batch is in use and cannot be deleted.', 13, 1);
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
END