USP_SITECONTENT_BULKPUTINTRASHBIN

CREATE procedure dbo.USP_SITECONTENT_BULKPUTINTRASHBIN
(
    @XML xml,
    @NUMBEROFSUCCESSFUL int = 0 output,
    @NUMBERINCONTENTTEST int = 0 output,
    @NUMBEROFDOCPARTSUSEDBYFORMS int = 0 output,
    @NUMBERINTARGETEDCONTENT int = 0 output,
    @NUMBEROFFUNDRAIERS int = 0 output,
    @NUMBERPERSONALPAGEWITHOWNERS int = 0 output,
    @PARTSSTILLONPAGES xml = null output
)
AS    
begin
    set nocount on

    begin tran

        declare @SITECONTENTIDS table ([ID] int)

        insert into @SITECONTENTIDS select IDS.ID.value('.', 'int') from @XML.nodes('/SiteContent/id') IDS(ID)

        -----------Check Content Tests

        declare @PARTSINCONTENTTEST table ([SITECONTENTID] int)    
        insert into @PARTSINCONTENTTEST
        select distinct SC.[ID]
        from dbo.CONTENTCOMPARISONS CC
        inner join dbo.CONTENTCOMPARISONPART CCP on CC.CCPID = CCP.ID
        inner join @SITECONTENTIDS SC on SC.[ID]= CC.CID
        where CCP.STARTEDDATE is not null and CCP.ENDEDDATE is null

        set @NUMBERINCONTENTTEST = (select count(1) from @PARTSINCONTENTTEST)

        delete @SITECONTENTIDS
        from @SITECONTENTIDS STI
        inner join @PARTSINCONTENTTEST P on P.SITECONTENTID= STI.[ID]

        -----------Check Targeted Content

        declare @PARTSINTARGETEDCONTENT table ([SITECONTENTID] int)

        insert into @PARTSINTARGETEDCONTENT
        select distinct SCI.[ID]
        from dbo.TARGETEDCONTENTCHECKS TCC
        inner join dbo.TARGETEDCONTENTPART TCP on TCP.[ID] = TCC.[TARGETEDPARTID]
        inner join dbo.SITECONTENT SC on SC.[ID] = TCP.[SITECONTENTID]
        inner join @SITECONTENTIDS SCI on SCI.[ID] = TCC.[CONTENTID]
        where SC.[DELETED] = 0 or SC.[INTRASHBIN] = 1 --Only care about TC parts that are not deleted at all, or are in Trash bin 


        set @NUMBERINTARGETEDCONTENT = (select count(1) from @PARTSINTARGETEDCONTENT)  

        delete @SITECONTENTIDS
        from @SITECONTENTIDS STI
        inner join @PARTSINTARGETEDCONTENT P on P.SITECONTENTID= STI.[ID]

        -----------Check parts that still exist on pages.


        declare @PARTSTHATEXISTONPAGES table ([SITECONTENTID] int)

        insert into @PARTSTHATEXISTONPAGES
          select SCI.ID
            from @SITECONTENTIDS SCI
              inner join dbo.pagecontent PC on SCI.ID = PC.SiteContentID

        delete @SITECONTENTIDS
          from @SITECONTENTIDS SCI
         inner join @PARTSTHATEXISTONPAGES P on P.SITECONTENTID = SCI.ID

         set @PARTSSTILLONPAGES = 
          (select SC.Title from @PARTSTHATEXISTONPAGES P
           inner join dbo.SITECONTENT SC on P.SITECONTENTID = SC.ID
          FOR XML RAW ('Part'), ROOT)

        -----------Fundraisers

        declare @FUNDRAISERS table ([SITECONTENTID] int)

        insert into @FUNDRAISERS        
        select SC.ID
        from @SITECONTENTIDS SC
        inner join dbo.FUNDRAISERS F on F.SITECONTENTID = SC.ID
        where F.BACKOFFICEID is not null and [IsCarePageManager]  = 0

        set @NUMBEROFFUNDRAIERS = (select count(1) from @FUNDRAISERS)  

        delete @SITECONTENTIDS
        from @SITECONTENTIDS STI
        inner join @FUNDRAISERS P on P.SITECONTENTID= STI.[ID]  

        -----------Check Personal Pages

        declare @PARTSWITHPPOWNERS table ([SITECONTENTID] int)

        insert into @PARTSWITHPPOWNERS        
        select SC.ID
        from @SITECONTENTIDS SC
        inner join dbo.FundRaisers FR on FR.SITECONTENTID = SC.ID
        inner join dbo.FundraiserSolicitors FS on FS.FundraiserID = FR.ID
        where FR.[IsCarePageManager] <> 0
        union
        select SC.ID
        from @SITECONTENTIDS SC
        inner join dbo.FundRaisers FR on FR.SITECONTENTID = SC.ID
        inner join dbo.FundraiserTeams FS on FS.FundraiserID = FR.ID
        where FR.[IsCarePageManager] <> 0

        set @NUMBERPERSONALPAGEWITHOWNERS = (select count(1) from @PARTSWITHPPOWNERS)  

        delete @SITECONTENTIDS
        from @SITECONTENTIDS STI
        inner join @PARTSWITHPPOWNERS P on P.SITECONTENTID= STI.[ID]  


        --get all of the document parts that are used by forms

        declare @DocPartsUsedByForms table ([SiteContentID] int)

        insert into @DocPartsUsedByForms
        SELECT distinct convert(int, convert(varchar(20), Properties.query('string(FileUploadProperties[1]/@DPID)')))
        FROM dbo.CustomFormElement cfe
        WHERE cfe.TypeID = 4
        AND cfe.SubTypeID = 16 

        declare @NUMTODELETEWITHDOCPARTS int = (select count(1) from @SiteContentIDS)

        --prevent document parts that are used by forms from being moved

        delete @SiteContentIDS
        from @SiteContentIDS STI
        inner join @DocPartsUsedByForms P on P.SiteContentID= STI.[ID]

        --set the number parts that were prevented from moving

        set @NUMBEROFDOCPARTSUSEDBYFORMS = @NUMTODELETEWITHDOCPARTS - (select count(1) from @SiteContentIDS)

        UPDATE SiteContent SET Deleted=1, InTrashBin=1 from dbo.SiteContent inner join @SiteContentIDS SC on SiteContent.ID=SC.ID

        set @NUMBEROFSUCCESSFUL = (select count(1) from @SiteContentIDS);

    commit tran    
end