USP_SITEPAGES_BULKPUTINTRASHBIN

CREATE procedure dbo.USP_SITEPAGES_BULKPUTINTRASHBIN
(
@XML xml,
@NUMBEROFSUCCESSFUL int output,
@NUMBEROFTEMPLATESINUSE int output,
@NUMBEROFSPECIALPAGES int output,
@NUMBEROFFUNDRAISERPAGES int output,
@NUMBEROFCHAPTERPAGES int output,
@NUMBEROTHERREFERENCES int output
)
with execute as owner
AS    
BEGIN
    BEGIN TRAN

    set nocount on

    create table #SITEPAGEIDS ([SitePageID] int)
    insert into #SITEPAGEIDS select IDS.ID.value('.', 'int') from @XML.nodes('/SitePages/id') IDS(ID)

    ------------------------------------------------------------------

    ---Check for templates that are in use by pages.  These cannot be deleted.

    declare @TemplatesInUse table ([SitePageID] int)
    insert into @TemplatesInUse
    select distinct SPI.SitePageID 
    from #SITEPAGEIDS SPI
    inner join dbo.sitePages SP on SP.templatepageid = SPI.SitePageID

    delete #SITEPAGEIDS
    from #SITEPAGEIDS SPI
    inner join @TemplatesInUse T on T.SitePageID = SPI.SitePageID

    set @NUMBEROFTEMPLATESINUSE = (select count(1) from @TemplatesInUse);

    ------------------------------------------------------------------

    ---Check for pages configured as special pages.  These cannot be deleted.

    declare @SpecialPages table ([SitePageID] int)    
    insert into @SpecialPages
    select distinct SP.SitePageID
    from dbo.CMSSITESETTING CSS
    inner join #SITEPAGEIDS SP on CAST(CSS.VALUE as int)=SP.SitePageID
    where ENUMID in(2,3,4,5,53)

    delete #SITEPAGEIDS
    from #SITEPAGEIDS SPI
    inner join @SpecialPages T on T.SitePageID = SPI.SitePageID

    set @NUMBEROFSPECIALPAGES = (select count(1) from @SpecialPages);

    ------------------------------------------------------------------

    ---Check for pages configured as chapter pages.  These cannot be deleted.

    declare @ChapterPages table ([SitePageID] int)    
    select distinct SP.SitePageID
    from dbo.chapterpages 
    inner join #SITEPAGEIDS SP on chapterpages.sitepageid=SP.SitePageID

    delete #SITEPAGEIDS
    from #SITEPAGEIDS SPI
    inner join @ChapterPages T on T.SitePageID = SPI.SitePageID

    set @NUMBEROFCHAPTERPAGES = (select count(1) from @ChapterPages);

    ------------------------------------------------------------------

    ---Check for pages configured as fundraiser pages.  These cannot be deleted.

    declare @FundraiserPages table ([SitePageID] int)    
    select distinct SP.SitePageID
    from dbo.fundraiserpages 
    inner join #SITEPAGEIDS SP on fundraiserpages.sitepageid=SP.SitePageID

    delete #SITEPAGEIDS
    from #SITEPAGEIDS SPI
    inner join @FundraiserPages T on T.SitePageID = SPI.SitePageID    

    set @NUMBEROFFUNDRAISERPAGES = (select count(1) from @FundraiserPages);


  -----------------------------------------------------------------------

    --Clear any BBSP template references so they will not cause a FK issue below    


  delete BBET from 
        dbo.BBPAYENVIRONMENTTEMPLATE BBET
        inner join dbo.CRMBBPAYTEMPLATE CBPT on BBET.TEMPLATEID = CBPT.ID
        inner join #SITEPAGEIDS SPI on SPI.SitePageID =  CBPT.SITEPAGEID

    delete dbo.CRMBBPAYTEMPLATE  from dbo.CRMBBPAYTEMPLATE CBPT
    inner join #SITEPAGEIDS SPI on SPI.SitePageID =  CBPT.SITEPAGEID

    ------------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------

    --------We will now check foreign key constraints to make sure we aren't going

    --------to move a page to trash bin when it is referenced by a part (possibly custom)


    declare @NumberBeforeReferenceCheck int;declare @NumberAfterReferenceCheck int

    set @NumberBeforeReferenceCheck = (select count(1) from #SITEPAGEIDS);

    declare @PARENTOBJECTID int;declare @PARENTCOLUMNID int;declare @REFERENCEDOBJECTID int;declare @REFERENCEDCOLUMNID int;

    declare FKCURSOR cursor local fast_forward for
        select sys.foreign_key_columns.parent_object_id, sys.foreign_key_columns.parent_column_id, sys.foreign_key_columns.referenced_object_id, sys.foreign_key_columns.referenced_column_id
        from sys.foreign_key_columns 
        inner join sys.columns on sys.foreign_key_columns.referenced_object_id = sys.columns.object_id and sys.foreign_key_columns.referenced_column_id = sys.columns.column_id
        inner join sys.foreign_keys on foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id
        where sys.foreign_key_columns.referenced_object_id = object_id('dbo.sitepages')
        and sys.foreign_keys.delete_referential_action <> 1
        and sys.foreign_keys.object_id <> object_id('FK_VanityURL_PageID')
    -- SHL BBIS User Story 530366; Because we want the page's template settings to exist while it is in the trash bin (in case it's restored)

    -- we make sure that this foreign key constraint is ignored similar to how vanity url key constrains are ignored

    and sys.foreign_keys.object_id <> object_id('FK_CMSSITEPAGEBBPAYTEMPLATEVERSION_SITEPAGEID')

    open FKCURSOR
    fetch next from FKCURSOR into @PARENTOBJECTID, @PARENTCOLUMNID, @REFERENCEDOBJECTID, @REFERENCEDCOLUMNID;

    while @@fetch_status = 0
    begin
        declare @SELECTSQL nvarchar(4000);
        set @SELECTSQL = 'delete #SITEPAGEIDS from dbo.#SITEPAGEIDS PTD inner join dbo.SITEPAGES SP on SP.ID = PTD.SitePageID inner join [' + OBJECT_NAME(@PARENTOBJECTID) + '] P on P.[' + col_name(@PARENTOBJECTID, @PARENTCOLUMNID) + '] = SP.[' + col_name(@REFERENCEDOBJECTID, @REFERENCEDCOLUMNID) + '] '
        exec sp_executesql @SELECTSQL

        fetch next from FKCURSOR into @PARENTOBJECTID, @PARENTCOLUMNID, @REFERENCEDOBJECTID, @REFERENCEDCOLUMNID;
    end

    close FKCURSOR
    deallocate FKCURSOR

    set @NumberAfterReferenceCheck = (select count(1) from #SITEPAGEIDS);

    set @NUMBEROTHERREFERENCES = @NumberBeforeReferenceCheck - @NumberAfterReferenceCheck

    ------------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------



    update dbo.SitePages SET
    Deleted=1,
    InTrashBin=1
    from dbo.SitePages SP
    inner join #SITEPAGEIDS SPI on SPI.SitePageID = SP.ID    

    set @NUMBEROFSUCCESSFUL = (select count(1) from #SITEPAGEIDS);

    drop table #SITEPAGEIDS

    COMMIT TRAN    
END