USP_DESIGNATION_DELETE

CREATE procedure dbo.USP_DESIGNATION_DELETE (
  @ID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier
  )
as
begin
  set nocount on;

  --check deletion rules

  declare @LEVEL1ID uniqueidentifier;
  declare @LEVEL2ID uniqueidentifier;
  declare @LEVEL3ID uniqueidentifier;
  declare @LEVEL4ID uniqueidentifier;
  declare @LEVEL5ID uniqueidentifier;
  declare @NUMREVENUERECS int;
  declare @NUMCAMPAIGNRECS int;
  declare @NUMLOCATIONRECS int;
  declare @NUMRECOGNITIONPROGRAMRECS int;
  declare @NUMOPPORTUNITYRECS int;
  declare @NUMPLANNEDGIFTS int;
  declare @NUMPLANNEDGIFTADDITIONS int;

  select @LEVEL1ID = DESIGNATIONLEVEL1ID
    ,@LEVEL2ID = DESIGNATIONLEVEL2ID
    ,@LEVEL3ID = DESIGNATIONLEVEL3ID
    ,@LEVEL4ID = DESIGNATIONLEVEL4ID
    ,@LEVEL5ID = DESIGNATIONLEVEL5ID
  from dbo.DESIGNATION
  where ID = @ID

  if exists (
      select top 1 1
      from dbo.DESIGNATION D
      inner join dbo.REVENUESPLIT_EXT R on D.ID = R.DESIGNATIONID
      where (
          (D.DESIGNATIONLEVEL1ID = @LEVEL1ID)
          and (
            D.DESIGNATIONLEVEL2ID = @LEVEL2ID
            or @LEVEL2ID is null
            )
          and (
            D.DESIGNATIONLEVEL3ID = @LEVEL3ID
            or @LEVEL3ID is null
            )
          and (
            D.DESIGNATIONLEVEL4ID = @LEVEL4ID
            or @LEVEL4ID is null
            )
          and (
            D.DESIGNATIONLEVEL5ID = @LEVEL5ID
            or @LEVEL5ID is null
            )
          )
      )
  begin
    raiserror (
        'BBERR_HASASSOCIATEDREVENUE : This designation has associated revenue and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end

  select @NUMCAMPAIGNRECS = count(DESIGNATIONCAMPAIGN.ID)
  from dbo.DESIGNATIONCAMPAIGN
  left join dbo.DESIGNATION D on DESIGNATIONCAMPAIGN.DESIGNATIONID = D.ID
  where (D.ID = @ID)
    or
    --search child designations as well

    (
      (D.DESIGNATIONLEVEL1ID = @LEVEL1ID)
      and (
        D.DESIGNATIONLEVEL2ID = @LEVEL2ID
        or @LEVEL2ID is null
        )
      and (
        D.DESIGNATIONLEVEL3ID = @LEVEL3ID
        or @LEVEL3ID is null
        )
      and (
        D.DESIGNATIONLEVEL4ID = @LEVEL4ID
        or @LEVEL4ID is null
        )
      and (
        D.DESIGNATIONLEVEL5ID = @LEVEL5ID
        or @LEVEL5ID is null
        )
      );

  if @NUMCAMPAIGNRECS = 1
  begin
    raiserror (
        'BBERR_HASASSOCIATEDCAMPAIGN : This designation has an associated campaign and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end
  else
    if @NUMCAMPAIGNRECS > 1
    begin
      raiserror (
          'BBERR_HASMULTIPLEASSOCIATEDCAMPAIGNS : This designation has associated campaigns and cannot be deleted. The designation can be marked as inactive.'
          ,13
          ,1
          );

      return 0;
    end

  select @NUMLOCATIONRECS = count(SPONSORSHIPLOCATION.ID)
  from dbo.SPONSORSHIPLOCATION
  where SPONSORSHIPLOCATION.DESIGNATIONID = @ID

  if @NUMLOCATIONRECS = 1
  begin
    raiserror (
        'BBERR_HASASSOCIATEDSPONSORSHIPLOCATION : This designation has associated an sponsorship location and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end
  else
    if @NUMLOCATIONRECS > 1
    begin
      raiserror (
          'BBERR_HASMULTIPLEASSOCIATEDSPONSORSHIPLOCATIONS : This designation has associated sponsorship locations and cannot be deleted. The designation can be marked as inactive.'
          ,13
          ,1
          );

      return 0;
    end

  declare @ISMAPPEDINBBIS bit;

  set @ISMAPPEDINBBIS = 0;

  select @ISMAPPEDINBBIS = 1
  from dbo.BBNCDESIGNATIONIDMAP
  inner join dbo.DonationDesignations on DonationDesignations.BackOfficeID = BBNCDESIGNATIONIDMAP.ID
  where BBNCDESIGNATIONIDMAP.DESIGNATIONID = @ID;

  if @ISMAPPEDINBBIS = 1
  begin
    raiserror (
        'BBERR_USEDBYBBISPART : This designation is being used by a Blackbaud Internet Solutions part and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end

  select @NUMRECOGNITIONPROGRAMRECS = count(RECOGNITIONPROGRAMDESIGNATION.ID)
  from dbo.RECOGNITIONPROGRAMDESIGNATION
  left join dbo.DESIGNATION D on RECOGNITIONPROGRAMDESIGNATION.DESIGNATIONID = D.ID
  where (D.ID = @ID)
    or
    --search child designations as well

    (
      (D.DESIGNATIONLEVEL1ID = @LEVEL1ID)
      and (
        D.DESIGNATIONLEVEL2ID = @LEVEL2ID
        or @LEVEL2ID is null
        )
      and (
        D.DESIGNATIONLEVEL3ID = @LEVEL3ID
        or @LEVEL3ID is null
        )
      and (
        D.DESIGNATIONLEVEL4ID = @LEVEL4ID
        or @LEVEL4ID is null
        )
      and (
        D.DESIGNATIONLEVEL5ID = @LEVEL5ID
        or @LEVEL5ID is null
        )
      );

  if @NUMRECOGNITIONPROGRAMRECS = 1
  begin
    raiserror (
        'BBERR_HASASSOCIATEDRECOGNITIONPROGRAM : This designation has an associated recognition program and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end
  else
    if @NUMRECOGNITIONPROGRAMRECS > 1
    begin
      raiserror (
          'BBERR_HASMULTIPLEASSOCIATEDRECOGNITIONPROGRAMS : This designation has associated recognition programs and cannot be deleted. The designation can be marked as inactive.'
          ,13
          ,1
          );

      return 0;
    end

  select @NUMOPPORTUNITYRECS = count(OPPORTUNITYDESIGNATION.ID)
  from dbo.OPPORTUNITYDESIGNATION
  left join dbo.DESIGNATION D on OPPORTUNITYDESIGNATION.DESIGNATIONID = D.ID
  where (D.ID = @ID)
    or
    --search child designations as well

    (
      (D.DESIGNATIONLEVEL1ID = @LEVEL1ID)
      and (
        D.DESIGNATIONLEVEL2ID = @LEVEL2ID
        or @LEVEL2ID is null
        )
      and (
        D.DESIGNATIONLEVEL3ID = @LEVEL3ID
        or @LEVEL3ID is null
        )
      and (
        D.DESIGNATIONLEVEL4ID = @LEVEL4ID
        or @LEVEL4ID is null
        )
      and (
        D.DESIGNATIONLEVEL5ID = @LEVEL5ID
        or @LEVEL5ID is null
        )
      );

  if @NUMOPPORTUNITYRECS = 1
  begin
    raiserror (
        'BBERR_HASASSOCIATEDOPPORTUNITY : This designation has an associated opportunity and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end
  else
    if @NUMOPPORTUNITYRECS > 1
    begin
      raiserror (
          'BBERR_HASMULTIPLEASSOCIATEDOPPORTUNITIES : This designation has associated opportunities and cannot be deleted. The designation can be marked as inactive.'
          ,13
          ,1
          );

      return 0;
    end

  select @NUMPLANNEDGIFTS = count(PLANNEDGIFTDESIGNATION.ID)
  from dbo.PLANNEDGIFTDESIGNATION
  left join dbo.DESIGNATION D on PLANNEDGIFTDESIGNATION.DESIGNATIONID = D.ID
  where (D.ID = @ID)
    or
    --search child designations as well

    (
      (D.DESIGNATIONLEVEL1ID = @LEVEL1ID)
      and (
        D.DESIGNATIONLEVEL2ID = @LEVEL2ID
        or @LEVEL2ID is null
        )
      and (
        D.DESIGNATIONLEVEL3ID = @LEVEL3ID
        or @LEVEL3ID is null
        )
      and (
        D.DESIGNATIONLEVEL4ID = @LEVEL4ID
        or @LEVEL4ID is null
        )
      and (
        D.DESIGNATIONLEVEL5ID = @LEVEL5ID
        or @LEVEL5ID is null
        )
      );

  if @NUMPLANNEDGIFTS = 1
  begin
    raiserror (
        'BBERR_HASASSOCIATEDPLANNEDGIFT : This designation has an associated planned gift and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end
  else
    if @NUMPLANNEDGIFTS > 1
    begin
      raiserror (
          'BBERR_HASMULTIPLEASSOCIATEDPLANNEDGIFTS : This designation has associated planned gifts and cannot be deleted. The designation can be marked as inactive.'
          ,13
          ,1
          );

      return 0;
    end

  select @NUMPLANNEDGIFTADDITIONS = count(PLANNEDGIFTADDITIONDESIGNATION.ID)
  from dbo.PLANNEDGIFTADDITIONDESIGNATION
  left join dbo.DESIGNATION D on PLANNEDGIFTADDITIONDESIGNATION.DESIGNATIONID = D.ID
  where (D.ID = @ID)
    or
    --search child designations as well

    (
      (D.DESIGNATIONLEVEL1ID = @LEVEL1ID)
      and (
        D.DESIGNATIONLEVEL2ID = @LEVEL2ID
        or @LEVEL2ID is null
        )
      and (
        D.DESIGNATIONLEVEL3ID = @LEVEL3ID
        or @LEVEL3ID is null
        )
      and (
        D.DESIGNATIONLEVEL4ID = @LEVEL4ID
        or @LEVEL4ID is null
        )
      and (
        D.DESIGNATIONLEVEL5ID = @LEVEL5ID
        or @LEVEL5ID is null
        )
      );

  if @NUMPLANNEDGIFTADDITIONS = 1
  begin
    raiserror (
        'BBERR_HASASSOCIATEDPLANNEDGIFTADDITION : This designation has an associated planned gift addition and cannot be deleted. The designation can be marked as inactive.'
        ,13
        ,1
        );

    return 0;
  end
  else
    if @NUMPLANNEDGIFTADDITIONS > 1
    begin
      raiserror (
          'BBERR_HASMULTIPLEASSOCIATEDPLANNEDGIFTADDITIONS : This designation has associated planned gift additions and cannot be deleted. The designation can be marked as inactive.'
          ,13
          ,1
          );

      return 0;
    end

  begin try
    declare @contextCache varbinary(128);

    --cache current context information

    set @contextCache = CONTEXT_INFO();
    --set CONTEXT_INFO to @CHANGEAGENTID

    set CONTEXT_INFO @CHANGEAGENTID;

    delete
    from dbo.DESIGNATIONGOAL
    from dbo.DESIGNATIONGOAL
    inner join dbo.DESIGNATION on DESIGNATIONGOAL.DESIGNATIONID = DESIGNATION.ID
    where
      --This designation...

      (DESIGNATION.ID = @ID)
      or
      --...and its children

      (
        (DESIGNATION.DESIGNATIONLEVEL1ID = @LEVEL1ID)
        and (
          DESIGNATION.DESIGNATIONLEVEL2ID = @LEVEL2ID
          or @LEVEL2ID is null
          )
        and (
          DESIGNATION.DESIGNATIONLEVEL3ID = @LEVEL3ID
          or @LEVEL3ID is null
          )
        and (
          DESIGNATION.DESIGNATIONLEVEL4ID = @LEVEL4ID
          or @LEVEL4ID is null
          )
        and (
          DESIGNATION.DESIGNATIONLEVEL5ID = @LEVEL5ID
          or @LEVEL5ID is null
          )
        );

    delete
    from dbo.DESIGNATION
    where
      --This designation...

      (DESIGNATION.ID = @ID)
      or
      --...and its children

      (
        (DESIGNATION.DESIGNATIONLEVEL1ID = @LEVEL1ID)
        and (
          DESIGNATION.DESIGNATIONLEVEL2ID = @LEVEL2ID
          or @LEVEL2ID is null
          )
        and (
          DESIGNATION.DESIGNATIONLEVEL3ID = @LEVEL3ID
          or @LEVEL3ID is null
          )
        and (
          DESIGNATION.DESIGNATIONLEVEL4ID = @LEVEL4ID
          or @LEVEL4ID is null
          )
        and (
          DESIGNATION.DESIGNATIONLEVEL5ID = @LEVEL5ID
          or @LEVEL5ID is null
          )
        );

    --reset CONTEXT_INFO to previous value

    if not @contextCache is null
      set CONTEXT_INFO @contextCache;
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;

    return 1;
  end catch

  return 0;
end