CREATE PROCEDURE dbo.DeleteOldFollowup(@DeletedFrom TINYINT) AS
SET NOCOUNT ON
/*
    Created by Kent Johansen 20070524

    Last Changed:
      20131121: Bug Fix for "key constraint error deleteing followup". Case 66742

    Parameters: @DeletedFrom is the same as TProgramPart in Types_Constants.pas

*/

DECLARE @RowsAffected INT
DECLARE @StartDate DATETIME
DECLARE @KeepMonthFollowup INT
DECLARE @DeleteBeforeDate SMALLDATETIME  -- The date we are deleting before now
DECLARE @SystemDate DATETIME
DECLARE @FollowupsDeletedBefore SMALLDATETIME -- The stored date that we before have deleted before. This can never be set to a value lower than it currently is!
DECLARE @tmpString CHAR(8)

SET @RowsAffected=0
SET @StartDate = GetDate()

-- Read Hotel settings
SELECT @KeepMonthFollowup=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='KeepMonthFollowups'
IF ISNULL (@KeepMonthFollowup, 0) = 0
  SET @KeepMonthFollowup = 60

SELECT @tmpString=ISNULL(KeyValue,0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='FollowupsDeletedBefore'
IF ISNULL(@tmpString, 0) = 0
  SET @FollowupsDeletedBefore = CONVERT(DATETIME, '19800101', 112) -- BEFORE: first picasso installation and before data in first converted installation
ELSE
  SET @FollowupsDeletedBefore = CONVERT(DATETIME, @tmpString, 112)

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

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

SELECT FollowupNumber 
INTO #FollowupNumbers
FROM Followup WITH (NOLOCK)
WHERE Type IN (3,4,8,13,17,18) /*3: futInternal 4: futPersonal 8: futOutOfOrder 13: FutCallSlip 17: futFAX 18: futSchedule*/
AND Date < @DeleteBeforeDate
AND ISNULL(CompletedBy,0) > 0

SELECT @RowsAffected = COUNT(*) FROM #FollowupNumbers

DELETE PersonalFollowupXtab WHERE FollowupNumber IN (SELECT FollowUpNumber FROM #FollowupNumbers)
DELETE ReservationFollowUpXTab WHERE FollowUpNumber IN (SELECT FollowUpNumber FROM #FollowupNumbers)
DELETE Followup WHERE FollowUpNumber IN (SELECT FollowUpNumber FROM #FollowupNumbers)

-- Write Delete date if it's greater than the old
IF @DeleteBeforeDate > @FollowupsDeletedBefore
BEGIN
  UPDATE dbo.HotelSetup SET KeyValue=CONVERT(CHAR(8), @DeleteBeforeDate, 112) WHERE SearchKey LIKE 'FollowupsDeletedBefore'
  IF @@rowcount = 0
    INSERT INTO dbo.HotelSetup (SearchKey, KeyValue) VALUES ('FollowupsDeletedBefore', CONVERT(CHAR(8), @DeleteBeforeDate, 112))
END

DROP TABLE #FollowupNumbers

SELECT @RowsAffected AS Deleted, @RowsAffected AS TotalRowsAffected, GetDate()-@StartDate AS TimeSpent