CREATE PROCEDURE [dbo].[DeleteOldProducts](@DeletedFrom TINYINT) AS
SET NOCOUNT ON
/*
  Created by Jacob Pedersen November 2005

  Last Changed:
    KJ 20131014: ProductPrice Rewrite.
    20081127 JP: BUG: Fixed Lock Escalation
    20081123 JP: Minor ajustments
    20081120 AV: allow to delete productprices even though product is used in future. This means that we can not delete the whole product (because future roomlines use it) but just the past prices
    3.39
    20071002 JP: EXT: Cleanup empty rows
    3.38
    20070516 JP: BUG: Delete from ProductPrice table failed because date could not be converted
    20061006 JP: EXT: aded ProductPayTV table
    3.36
    20060508 JP: BUG: was not looking at products in reservations table, only roomline

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

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

*/

DECLARE
@ProductsDeleted INT,
@TotalRowsAffected INT,
@RowsAffected INT,
@KeepMonthProducts INT,
@ProductNumber INT,
@MarkAsDeleted BIT,
@PriceCount INT,
@PrimaryKey INT,
@MaximumDeletes INT,
@DeleteBeforeDate SMALLDATETIME,  -- The date we are deleting before now
@SystemDate DATETIME,
@ProductsDeletedBefore 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!
@rowcount INT,
@FirstArrivalDate DATETIME

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

/* Read Hotel settings */
SELECT @KeepMonthProducts=KeyValue FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey='KeepMonthProducts'
IF IsNull (@KeepMonthProducts, 0) = 0
  SET @KeepMonthProducts = 60

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

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

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

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

-- Prevent lock escalation when deleting from ProductPrices. Will take longer but increases concurrency. Actually should be possible to run on a live system now!
DECLARE @PreventLockEscalation INT
SELECT @PreventLockEscalation = ProductNumber FROM dbo.ProductPrice WITH (UPDLOCK, HOLDLOCK) WHERE 1=2

-- Cleanup empty rows
SET ROWCOUNT @MaximumDeletes -- DELETE FROM will stop after this many deletes
delete from productprice WITH (ROWLOCK) where 1=1
and adultprice1=0
and adultprice2=0
and adultprice3=0
and adultprice4=0
and adultprice5=0
and adultprice6=0
and adultprice7=0
and adultprice8=0
and adultprice9=0
and children1price1=0
and children1price2=0
and children1price3=0
and children1price4=0
and children1price5=0
and children1price6=0
and children1price7=0
and children1price8=0
and children1price9=0
and children2price1=0
and children2price2=0
and children2price3=0
and children2price4=0
and children2price5=0
and children2price6=0
and children2price7=0
and children2price8=0
and children2price9=0
and adultarrivaladdon=0
and adultstayaddon=0
and adultdepartureaddon=0
and adultarrivaladdon2=0
and adultstayaddon2=0
and adultdepartureaddon2=0
and adultarrivaladdon3=0
and adultstayaddon3=0
and adultdepartureaddon3=0
and adultarrivaladdon4=0
and adultstayaddon4=0
and adultdepartureaddon4=0
and adultarrivaladdon5=0
and adultstayaddon5=0
and adultdepartureaddon5=0
and adultarrivaladdon6=0
and adultstayaddon6=0
and adultdepartureaddon6=0
and adultarrivaladdon7=0
and adultstayaddon7=0
and adultdepartureaddon7=0
and adultarrivaladdon8=0
and adultstayaddon8=0
and adultdepartureaddon8=0
and adultarrivaladdon9=0
and adultstayaddon9=0
and adultdepartureaddon9=0

and children1arrivaladdon=0
and children1stayaddon=0
and children1departureaddon=0
and children1arrivaladdon2=0
and children1stayaddon2=0
and children1departureaddon2=0
and children1arrivaladdon3=0
and children1stayaddon3=0
and children1departureaddon3=0
and children1arrivaladdon4=0
and children1stayaddon4=0
and children1departureaddon4=0
and children1arrivaladdon5=0
and children1stayaddon5=0
and children1departureaddon5=0
and children1arrivaladdon6=0
and children1stayaddon6=0
and children1departureaddon6=0
and children1arrivaladdon7=0
and children1stayaddon7=0
and children1departureaddon7=0
and children1arrivaladdon8=0
and children1stayaddon8=0
and children1departureaddon8=0
and children1arrivaladdon9=0
and children1stayaddon9=0
and children1departureaddon9=0

and children2arrivaladdon=0
and children2stayaddon=0
and children2departureaddon=0
and children2arrivaladdon2=0
and children2stayaddon2=0
and children2departureaddon2=0
and children2arrivaladdon3=0
and children2stayaddon3=0
and children2departureaddon3=0
and children2arrivaladdon4=0
and children2stayaddon4=0
and children2departureaddon4=0
and children2arrivaladdon5=0
and children2stayaddon5=0
and children2departureaddon5=0
and children2arrivaladdon6=0
and children2stayaddon6=0
and children2departureaddon6=0
and children2arrivaladdon7=0
and children2stayaddon7=0
and children2departureaddon7=0
and children2arrivaladdon8=0
and children2stayaddon8=0
and children2departureaddon8=0
and children2arrivaladdon9=0
and children2stayaddon9=0
and children2departureaddon9=0

SET @rowcount = @@rowcount
SET @TotalRowsAffected=@TotalRowsAffected+@rowcount

-- Make sure we stay close to our goal of maximum deletes
IF @rowcount > @MaximumDeletes
	SET @MaximumDeletes = 0
ELSE
	SET @MaximumDeletes = @MaximumDeletes - @rowcount

-- Delete Cursor
DECLARE #ProductCur CURSOR LOCAL FAST_FORWARD FOR

	SELECT ProductNumber FROM dbo.Products WITH (NOLOCK)

OPEN #ProductCur

FETCH NEXT FROM #ProductCur
INTO @ProductNumber

WHILE (@@FETCH_STATUS <> -1) AND (@TotalRowsAffected < @MaximumDeletes)
BEGIN
  IF @@FETCH_STATUS <> -2
  BEGIN
    SET @RowsAffected=0

   SELECT @FirstArrivalDate = dbo.GetFirstArrivingDateOnProduct(@ProductNumber)

    SET ROWCOUNT @MaximumDeletes -- DELETE FROM will stop after this many deletes
    DELETE FROM dbo.ProductPrice WITH (ROWLOCK) 
    WHERE ProductNumber = @ProductNumber 
    AND DateStamp < CONVERT(CHAR(8), @DeleteBeforeDate, 112) -- DANGER! THIS IS NOT GOOD ENOUGH. MIGHT BE INHOUSE RESERVATIONS EVEN IN DELETE PERIOD!
	  AND CONVERT(DATETIME, DateStamp, 112) < @FirstArrivalDate -- NB! Needs an index on ProductNumber and DateStamp
    SET @rowcount = @@rowcount
    SET @RowsAffected=@RowsAffected+@rowcount
    SET ROWCOUNT 0
    BEGIN
      SELECT @PriceCount = COUNT(*) FROM dbo.ProductPrice WITH (NOLOCK) WHERE ProductNumber = @ProductNumber
      IF @@rowcount = 0
	SET @PriceCount = 0

      IF @PriceCount = 0 -- no prices left on a deleted product
      BEGIN
        SELECT @rowcount = COUNT(*) FROM dbo.Products WITH (NOLOCK) WHERE ProductNumber = @ProductNumber
        AND ProductNumber NOT IN (SELECT ProductNumber FROM ReservationRoomLine WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber2 FROM ReservationRoomLine WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber3 FROM ReservationRoomLine WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber4 FROM ReservationRoomLine WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber FROM Reservations WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber2 FROM Reservations WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber3 FROM Reservations WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber4 FROM Reservations WITH (NOLOCK))
        AND ProductNumber NOT IN (SELECT ProductNumber FROM   ReservationConferenceLine WITH (NOLOCK))

        IF (@@rowcount = 0) -- not in use any more. missing checks for CustomerFieldsPerHotel!
        BEGIN
	        DELETE FROM dbo.ProductMinimumAvailableRoomsXTab  WITH (ROWLOCK) WHERE ProductNumber = @ProductNumber
	        SET @RowsAffected=@RowsAffected+@@rowcount
	        DELETE FROM dbo.ProductPOSXtab  WITH (ROWLOCK) WHERE ProductNumber = @ProductNumber
	        SET @RowsAffected=@RowsAffected+@@rowcount
	        DELETE FROM dbo.ProductSegmentsXTab  WITH (ROWLOCK) WHERE ProductNumber = @ProductNumber
	        SET @RowsAffected=@RowsAffected+@@rowcount
	        DELETE FROM dbo.ProductPayTV  WITH (ROWLOCK) WHERE FK_Product = @ProductNumber
	        SET @RowsAffected=@RowsAffected+@@rowcount
          DELETE ProductPricePacket   WITH (ROWLOCK) WHERE FK_ProductNumber = @ProductNumber
	        SET @RowsAffected=@RowsAffected+@@rowcount

	        DELETE FROM dbo.Products WITH (ROWLOCK)  WHERE ProductNumber = @ProductNumber -- where MarkAsDeleted = True and no prices remain
	        SET @rowcount = @@rowcount
	        SET @RowsAffected=@RowsAffected+@rowcount
	        SET @ProductsDeleted=@ProductsDeleted+@rowcount
        END
        ELSE
        BEGIN
          SELECT @ProductNumber = IsNull(MIN(ObjectPrimaryKey), 0) - 1 FROM dbo.DeleteLog WITH (NOLOCK) WHERE Type = 3 --  3 = Products -- Actual Product was not deleted, we need to log the number of other rows deleted.
          IF @ProductNumber > -1
            SET @ProductNumber = -2
        END
      END
      ELSE
      BEGIN
      	SELECT @ProductNumber = IsNull(MIN(ObjectPrimaryKey), 0) - 1 FROM dbo.DeleteLog WITH (NOLOCK) WHERE Type = 3 --  3 = Products -- Actual Product was not deleted, we need to log the number of other rows deleted.
        IF @ProductNumber > -1
          SET @ProductNumber = -2
      END
    END
    -- Write to Log
    INSERT INTO dbo.DeleteLog WITH (ROWLOCK) (Type, ObjectPrimaryKey, DateStamp, DeletedBy, RowsAffected) VALUES (3, @ProductNumber, @SystemDate + CONVERT(DATETIME, CONVERT(CHAR, GETDATE(), 108), 108), @DeletedFrom, @RowsAffected) -- 3 = Products

    SET @TotalRowsAffected=@TotalRowsAffected+@RowsAffected
  END -- IF @@FETCH_STATUS <> -2

  FETCH NEXT FROM #ProductCur
  INTO @ProductNumber
END -- WHILE

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

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

CLOSE #ProductCur
DEALLOCATE #ProductCur