spRollbackCopyBatch

CREATE procedure dbo.spRollbackCopyBatch
(
    @CopyBatchGUID uniqueidentifier,
    @CurrentUserID int
)
as
    set nocount on;

    BEGIN TRY

    BEGIN TRANSACTION

      --Let's whack the correlation between this clientsite and this copybatchguid


      DECLARE @clientSiteID int
      SET @clientSiteID = (SELECT top 1 ID from dbo.ClientSites WHERE ClientSites.CopyBatchGUID = @CopyBatchGUID )

      UPDATE ClientSites
      SET
      CopySourceID = NULL,
      CopyBatchGUID = NULL
      WHERE ClientSites.CopyBatchGUID = @CopyBatchGUID

      DELETE FROM dbo.CMSSITESETTING where CLIENTSITESID = @clientSiteID        

      declare @objectsToDelete table (guid uniqueidentifier)

      insert into @objectsToDelete
        select guid from SiteImages where CopyBatchGUID = @CopyBatchGUID
      insert into @objectsToDelete
        select guid from SiteStyleSheets where CopyBatchGUID = @CopyBatchGUID
      insert into @objectsToDelete
        select guid from SitePages where CopyBatchGUID = @CopyBatchGUID
      insert into @objectsToDelete
        select guid from SiteContent where CopyBatchGUID = @CopyBatchGUID        
      insert into @objectsToDelete
        select guid from EmailTemplate where CopyBatchGUID = @CopyBatchGUID

      delete OSE
      from dbo.CMSOBJETSECURITYEXCEPTION OSE
      inner join @objectsToDelete OTD
      on OSE.SECUREDOBJECTGUID = OTD.guid

      delete ESP
      from dbo.CMSEVERYONESECURITYPRIVS ESP
      inner join @objectsToDelete OTD
      on ESP.SECUREDOBJECTGUID = OTD.guid

            delete CTL
            from dbo.CMSTEMPLATELAYOUT CTL
            inner join SitePages SP
            on SP.ID = CTL.TEMPLATEID
      WHERE SP.CopyBatchGUID = @CopyBatchGUID

        delete S
        from dbo.Search S
        inner join dbo.SiteContent SC on SC.ID = S.SiteContentId
        where SC.CopyBatchGUID = @CopyBatchGUID

      DELETE FROM SiteImages WHERE CopyBatchGUID = @CopyBatchGUID
      DELETE FROM SiteLayouts WHERE CopyBatchGUID = @CopyBatchGUID
      DELETE FROM SiteStyleSheets WHERE CopyBatchGUID = @CopyBatchGUID
      DELETE FROM SiteFolders WHERE CopyBatchGUID = @CopyBatchGUID

      --Deleting the siteimages takes care of this

      --DELETE FROM SiteImageFolders WHERE CopyBatchGUID = @CopyBatchGUID

      DELETE FROM dbo.VanityURL where CopyBatchGUID = @CopyBatchGUID

      -- Need to delete page content first because it FKs to both sitecontent and sitepages


      declare @T_SITEPAGES TABLE
      (
        ID int
      )

      insert into @T_SITEPAGES
      select ID from dbo.SitePages where CopyBatchGUID = @CopyBatchGUID

      delete PC from dbo.PageContent PC
        inner join @T_SITEPAGES SP on PC.SitePagesID = SP.ID

      declare @T_SITECONTENT TABLE
      (
        ID int
      )

      insert into @T_SITECONTENT
      select ID from dbo.SiteContent where CopyBatchGUID = @CopyBatchGUID

      delete PC from dbo.PageContent PC
        inner join @T_SITECONTENT SC on PC.SiteContentID = SC.ID

      -- Now that the FK constraints won't fire, we can delete the sitecontent and sitepages


      DELETE FROM SiteContent WHERE CopyBatchGUID = @CopyBatchGUID
      DELETE FROM SitePages WHERE CopyBatchGUID = @CopyBatchGUID

      delete from dbo.[ECARDTEMPLATE] where COPYBATCHGUID = @CopyBatchGUID

      DELETE FROM EmailTemplate WHERE CopyBatchGUID = @CopyBatchGUID

      UPDATE dbo.SiteCopy
      SET TargetClientSitesID = null
      WHERE TargetClientSitesID = @clientSiteID

      DELETE FROM dbo.ClientSites WHERE ID = @clientSiteID


      UPDATE SiteCopy
      SET Status = 100, RollbackUserID = @CurrentUserID, RollbackDate = getutcdate()
      WHERE ID = @CopyBatchGUID

      UPDATE SiteCopy SET Status = 98, ErrorLog = ErrorLog +  'Rollback Complete.' + CHAR(13) WHERE ID = @CopyBatchGUID

    COMMIT TRANSACTION

    END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION
    UPDATE SiteCopy SET Status = 99, ErrorLog = ErrorLog + 'An error occurred while undoing the site copy: ' + ERROR_MESSAGE() + CHAR(13) WHERE ID = @CopyBatchGUID
  END CATCH