CREATE PROCEDURE dbo.DeleteOldAllotments(@DeletedFrom TINYINT) AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen 20070214

    Last Changed:
      vd20150323 CustomerAllotments implemented differently
      2007-05-16 BUG: JP: Fix for missing check for allotment in CustomerFieldsPerHotel

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

    Notes:	- The actual Allotment is not deleted until there are no more Rows and MarkAsDeleted=1. 
		- Rows are not deleted for Allotments which are in use on reservations. Not until these reservations are deleted, the Rows are too.
		- Log is updated everytime something is deleted from the Allotment. Since same primary key cannot exist twice, deletes within a Allotment will have negative Allotment numbers which does NOT refer to the actual Allotment

*/

DECLARE
@AllotmentsDeleted INT,
@TotalRowsAffected INT,
@RowsAffected INT,
@KeepMonthAllotments INT,
@AllotmentNumber INT,
@MarkAsDeleted SMALLDATETIME,
@RowCount INT,
@PrimaryKey INT,
@MaximumDeletes INT,
@DeleteBeforeDate SMALLDATETIME,  -- The date we are deleting before now
@SystemDate DATETIME,
@AllotmentsDeletedBefore SMALLDATETIME, -- The stored date that we before have deleted before. This can never be set to a value lower than it currently is!
@tmpString CHAR(8),
@StartDate DATETIME -- MUST BE DATETIME. CANNOT BE SMALLDATETIME!

SET @AllotmentsDeleted = 0
SET @TotalRowsAffected = 0
SET @StartDate = GetDate()

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

SELECT @MaximumDeletes=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='MaximumDeletesAllotments'
IF IsNull (@MaximumDeletes, 0) = 0
  SET @MaximumDeletes = 30000

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

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

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

SET @TotalRowsAffected=0

SET @RowsAffected=0
-- DELETE LINES
DELETE FROM AllotmentLines WHERE CONVERT(DATETIME, DateStamp, 112) < @DeleteBeforeDate
SET @RowsAffected=@RowsAffected+@@rowcount

DELETE FROM Allotments 
WHERE MarkAsDeleted > 0 
  AND NOT AllotmentNumber IN (SELECT AllotmentNumber FROM AllotmentLines WITH (NOLOCK)) 
  AND NOT AllotmentNumber IN (SELECT AllotmentNumber FROM ReservationMain WITH (NOLOCK))
  AND NOT AllotmentNumber IN (SELECT AllotmentNumber FROM CustomerAllotmentXTAB WITH (NOLOCK))

SET @AllotmentsDeleted=@@rowcount
SET @RowsAffected=@RowsAffected+@AllotmentsDeleted
SET @TotalRowsAffected=@TotalRowsAffected+@RowsAffected

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

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

