CREATE PROCEDURE DBO.DeleteOldAttachments AS
/* Created february 2011 by Villy Damsbo */
DECLARE
@ReservationAttachmentXTabDeleted INT,
@CustomerAttachmentXTabDeleted INT,
@ProductCertificateDeleted INT,
@AttachmentsDeleted INT,
@KeepAttachmentsMonths INT,
@AttachmentsDeletedBefore SMALLDATETIME, 
@StartDate DATETIME, 
@SystemDate DATETIME,
@DeleteBeforeDate SMALLDATETIME,  
@tmpString CHAR(8)

SET @StartDate = GetDate()
 
SELECT @KeepAttachmentsMonths = ISNULL(
 (SELECT KeyValue
  FROM HotelSetup WITH (NOLOCK) 
  WHERE SearchKey = 'KeepAttachmentsMonths'), 24) 

SELECT @tmpString = ISNULL(KeyValue, 0) 
FROM HotelSetup WITH (NOLOCK) 
WHERE SearchKey = 'AttachmentsDeletedBefore' 

IF ISNULL(@tmpString, 0) = 0
  SET @AttachmentsDeletedBefore = CONVERT(DATETIME, '19800101', 112) 
ELSE
  SET @AttachmentsDeletedBefore = CONVERT(DATETIME, @tmpString, 112)

SELECT @SystemDate = CONVERT(DATETIME, SystemDate, 112) 
FROM System WITH (NOLOCK) 
WHERE Number = 1

SET @DeleteBeforeDate = DATEADD(MONTH, - @KeepAttachmentsMonths, @SystemDate)

DELETE FROM CustomerAttachmentXTab 
WHERE AttachmentNumber IN (SELECT AttachmentNumber
                           FROM Attachment WITH (NOLOCK) 
                           WHERE DateStamp < @DeleteBeforeDate)
SET @CustomerAttachmentXTabDeleted = @@ROWCOUNT 

DELETE FROM ReservationAttachmentXTab 
WHERE AttachmentNumber IN (SELECT AttachmentNumber
                           FROM Attachment WITH (NOLOCK) 
                           WHERE DateStamp < @DeleteBeforeDate)
SET @ReservationAttachmentXTabDeleted = @@ROWCOUNT 

UPDATE ProductCertificate 
SET FK_CertificateAttachmentID = NULL 
WHERE FK_CertificateAttachmentID IN (SELECT AttachmentNumber
                           FROM Attachment WITH (NOLOCK) 
                           WHERE DateStamp < @DeleteBeforeDate)
SET @ProductCertificateDeleted = @@ROWCOUNT 

DELETE FROM Attachment  
WHERE 
  AttachmentNumber NOT IN (SELECT FK_CertificateAttachmentID 
                           FROM ProductCertificate WITH (NOLOCK)) AND 
  AttachmentNumber NOT IN (SELECT AttachmentNumber
                           FROM CustomerAttachmentXTab WITH (NOLOCK)) AND
  AttachmentNumber NOT IN (SELECT AttachmentNumber
                           FROM ReservationAttachmentXTab WITH (NOLOCK)) 
SET @AttachmentsDeleted = @@ROWCOUNT 

UPDATE HotelSetup 
SET KeyValue = CONVERT(CHAR(8), @DeleteBeforeDate, 112) 
WHERE SearchKey = 'AttachmentsDeletedBefore' 
  
IF @@ROWCOUNT = 0
  INSERT INTO HotelSetup (SearchKey, KeyValue) 
    VALUES ('AttachmentsDeletedBefore', CONVERT(CHAR(8), @DeleteBeforeDate, 112))

SELECT
  @AttachmentsDeleted AS Deleted, 
  @AttachmentsDeleted AS AttachmentsDeleted,
  @CustomerAttachmentXTabDeleted AS CustomerAttachmentXTabDeleted,
  @ReservationAttachmentXTabDeleted AS ReservationAttachmentXTabDeleted,
  @ProductCertificateDeleted AS ProductCertificateDeleted,
  @CustomerAttachmentXTabDeleted + @ReservationAttachmentXTabDeleted + @ProductCertificateDeleted + @AttachmentsDeleted AS TotalRowsAffected, 
  GetDate() - @StartDate AS TimeSpent