IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Knuthenborg_DiscountSetup]') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.Knuthenborg_DiscountSetup
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Knuthenborg_DiscountPlugin]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Knuthenborg_DiscountPlugin]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Knuthenborg_DiscountSetup]') AND type in (N'P', N'PC'))
DROP PROCEDURE Knuthenborg_DiscountSetup
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Knuthenborg_DiscountPlugin]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Knuthenborg_DiscountPlugin]
GO
-- ***************************************
-- Setup
-- ***************************************
CREATE PROCEDURE dbo.Knuthenborg_DiscountSetup(@MaxDays INT OUTPUT, @Date1 datetime OUTPUT, @Date2 datetime OUTPUT)
AS
BEGIN
  SET @MaxDays = 14 -- Maximum number of days from arrival to purchase of ware with discount
  DECLARE @Year varchar(4) = CAST(YEAR(GETDATE()) AS varchar)
  SET @Date1 = @Year + '-03-29' --mm-dd'
  SET @Date2 = @Year + '-05-09' --mm-dd'

  SELECT 
    26, -- Sæsonkort voksen 2025
    58, -- Adgang til Knuthenborg voksen    
    55  -- Dagsbillet voksen
  UNION ALL SELECT
    27, -- Sæsonkort barn 3-11 år 2025
    59, -- Adgang til Knuthenborg barn 3-11 år     
    165 -- Dagsbillet barn 3-11 år
END
GO
IF EXISTS(SELECT * FROM HotelSetup WHERE SearchKey='DiscountPlugin' AND KeyValue='Knuthenborg_DiscountPlugin') 
  AND NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Knuthenborg_DiscountUsageCount]') AND type in (N'U'))
BEGIN
  CREATE TABLE [dbo].[Knuthenborg_DiscountUsageCount](
	  [ReferenceNumber] [int] NOT NULL,
	  [Guestnumber] [smallint] NOT NULL,
	  [Warenumber] [int] NOT NULL,
	  [UsedCount] [int] NOT NULL,
   CONSTRAINT [PK_KnuthenborgDiscountUsageCount] PRIMARY KEY CLUSTERED 
  (
	  [ReferenceNumber] ASC,
	  [Guestnumber] ASC,
	  [Warenumber] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[Knuthenborg_DiscountUsageCount]  WITH CHECK ADD  CONSTRAINT [FK_KnuthenborgDiscountUsageCount_RESERVATIONS] FOREIGN KEY([ReferenceNumber], [Guestnumber])
  REFERENCES [dbo].[RESERVATIONS] ([ReferenceNumber], [GuestNumber])
  ON UPDATE CASCADE
  ON DELETE CASCADE

  ALTER TABLE [dbo].[Knuthenborg_DiscountUsageCount] CHECK CONSTRAINT [FK_KnuthenborgDiscountUsageCount_RESERVATIONS]

  ALTER TABLE [dbo].[Knuthenborg_DiscountUsageCount]  WITH CHECK ADD  CONSTRAINT [FK_KnuthenborgDiscountUsageCount_Ware] FOREIGN KEY([Warenumber])
  REFERENCES [dbo].[Ware] ([WareNumber])

  ALTER TABLE [dbo].[Knuthenborg_DiscountUsageCount] CHECK CONSTRAINT [FK_KnuthenborgDiscountUsageCount_Ware]
END
GO
CREATE PROCEDURE dbo.Knuthenborg_DiscountPlugin
(
  @AFunction INT,
  @ACampaignCode varchar(MAX),
  @AHotelNumber INT,
  @AWareNumbersCSV varchar(MAX) = NULL,
  @ABookingDate datetime = NULL,
  @ADeliveryDate datetime = NULL,
  @ADiscountSourceID INT = NULL,
  @AUsedCount INT = NULL
)
AS
BEGIN

  DECLARE @MaxDays INT -- Maximum number of days from arrival to purchase of ware with discount
  DECLARE @Date1 datetime 
  DECLARE @Date2 datetime 
  DECLARE @Map TABLE(WareNumber INT NOT NULL,PacketWareNumber INT NOT NULL,DiscountWareNumber INT NOT NULL)

  INSERT INTO @Map
  EXEC dbo.Knuthenborg_DiscountSetup @MaxDays OUTPUT, @Date1 OUTPUT, @Date2 OUTPUT

  DECLARE @Result INT = 0
  DECLARE @ReferenceNumber INT
  DECLARE @GuestNumber INT
  DECLARE @WarePriceRuleType INT = 1
  DECLARE @PacketWares TABLE (PacketWareNumber INT NOT NULL,Pieces INT NOT NULL)
  DECLARE @ArrivalDate date
  DECLARE @Index INT = CHARINDEX('-', @ACampaignCode)
  DECLARE @TmpStr varchar(MAX)

  IF ISNULL(@Index,0) <= 0
  BEGIN
    SET @ReferenceNumber = TRY_CAST(@ACampaignCode AS INT)
    SET @GuestNumber = 0
  END
  ELSE
  BEGIN
    SET @ReferenceNumber = TRY_CAST(SUBSTRING(@ACampaignCode, 1, @Index-1) AS INT)
    SET @TmpStr = SUBSTRING(@ACampaignCode, @Index+1, LEN(@ACampaignCode))
    IF @TmpStr = ''
      SET @GuestNumber = NULL
    ELSE
      SET @GuestNumber = TRY_CAST(@TmpStr AS INT)
  END
  IF ISNULL(@ReferenceNumber,0) <= 0 OR ISNULL(@GuestNumber,-1) < 0
    SET @Result = -1 --Invalid campaign code

  IF @AFunction=1  -- GET DISCOUNT VALUES AND MAX COUNTS
  BEGIN
    IF @Result = 0
    BEGIN
      SELECT @ArrivalDate=CONVERT(date, R.ArrivalDate, 112)
      FROM RESERVATIONS R WITH (NOLOCK)
      INNER JOIN RESERVATIONMAIN RM WITH (NOLOCK) ON RM.REFERENCENUMBER=R.ReferenceNumber AND RM.HOTELNUMBER=@AHotelNumber
      WHERE R.ReferenceNumber=@ReferenceNumber
      AND R.GuestNumber=@GuestNumber

      IF @ArrivalDate IS NULL
        SET @Result = -1 --Invalid campaign code
      ELSE
      IF DATEADD(day, @MaxDays, @ArrivalDate) < GETDATE()
        SET @Result = 4 -- Campaign code is no longer active
    END

    IF @Result = 0
    BEGIN
      INSERT INTO @PacketWares
      SELECT M.PacketWareNumber,SUM(AL.Pieces) Pieces
      FROM ArrangementReservationsXtab AX WITH (NOLOCK) 
      INNER JOIN ArrangementLines AL WITH (NOLOCK) ON AL.ArrangementLineNumber=AX.ArrangementLineNumber AND AL.Date=@ArrivalDate
      INNER JOIN @Map M ON M.PacketWareNumber=AL.WareNumber
      WHERE AX.ReferenceNumber=@ReferenceNumber
      AND AX.GuestNumber=@GuestNumber
      GROUP BY M.PacketWareNumber

      IF @@ROWCOUNT = 0
        SET @Result = 2 -- Campaign code not valid for this webshop
    END

    SELECT -2 CampaignID, @Result Result

    IF @Result = 0
    BEGIN
      SELECT 0 DiscountType, 0 Value, 0 IsFixedAmount
      WHERE 1=2

      IF (@ArrivalDate < @Date1) OR (@ArrivalDate > @Date2)
        SELECT M.WareNumber, CAST(MAX(RP.Price) AS float) DiscountValue, CAST(1 AS BIT) IsFixedAmount, MIN(PW.PacketWareNumber) DiscountSourceID
        FROM @PacketWares PW
        INNER JOIN @Map M ON M.PacketWareNumber=PW.PacketWareNumber
        INNER JOIN SplitNumberList(@AWareNumbersCSV) L ON L.value=M.WareNumber
        CROSS APPLY (
          SELECT WareLinkGroupRecurringPatternXTab.FKRecurringPatternId RecurringPatternId, TRY_CAST(R.RuleValue AS decimal) Price
          FROM WareLinkGroupRecurringPatternXTab WITH (NOLOCK)
	        INNER JOIN WareLinkGroup WLG WITH (NOLOCK) ON WLG.Id = WareLinkGroupRecurringPatternXTab.FKWareLinkGroupId
	        INNER JOIN Ware W WITH (NOLOCK) ON W.WareLinkNumber=WLG.Id AND ISNULL(W.markasdeleted, 0) = 0 AND W.WareNumber = M.DiscountWareNumber
          INNER JOIN RecurringRule R WITH (NOLOCK) ON R.FK_RecurringPatternId=WareLinkGroupRecurringPatternXTab.FKRecurringPatternId AND R.RuleType =@WarePriceRuleType
	        UNION
	        SELECT X.FK_RecurringPatternId RecurringPatternId, TRY_CAST(R.RuleValue AS decimal) Price
	        FROM RecurringPatternWareXTab X WITH (NOLOCK)
	        INNER JOIN Ware W WITH (NOLOCK) ON W.WareNumber=X.FK_WareNumber AND ISNULL(W.markasdeleted, 0) = 0 AND W.WareNumber = M.DiscountWareNumber
          INNER JOIN RecurringRule R WITH (NOLOCK) ON R.FK_RecurringPatternId=X.FK_RecurringPatternId AND R.RuleType =@WarePriceRuleType
        ) RP
        INNER JOIN RecurringPattern P WITH (NOLOCK) ON P.Id=RP.RecurringPatternId AND P.StartRecurringDateTime <= @ArrivalDate AND (CAST(P.EndRecurringDateTime as int) <=0 OR  P.EndRecurringDateTime > @ArrivalDate)
        INNER JOIN RecurringPatternSpecified S WITH (NOLOCK) ON S.FK_RecurringPatternId=P.Id AND ISNULL(S.Deleted,0)=0 AND CAST(S.StartDateTime AS date) <= @ArrivalDate AND (CAST(S.EndDateTime as int) <=0 OR CAST(S.EndDateTime AS date) >= @ArrivalDate)
        WHERE RP.Price IS NOT NULL
        GROUP BY M.WareNumber
      ELSE
        SELECT M.WareNumber, CAST(100 AS float) DiscountValue, CAST(0 AS BIT) IsFixedAmount, PW.PacketWareNumber DiscountSourceID
        FROM @PacketWares PW
        INNER JOIN @Map M ON M.PacketWareNumber=PW.PacketWareNumber
        INNER JOIN SplitNumberList(@AWareNumbersCSV) L ON L.value=M.WareNumber

      SELECT PW.PacketWareNumber DiscountSourceID, PW.Pieces MaxCount, ISNULL(U.UsedCount, 0) UsedCount
      FROM @PacketWares PW
      LEFT OUTER JOIN [dbo].[Knuthenborg_DiscountUsageCount] U WITH (NOLOCK) ON U.ReferenceNumber=@ReferenceNumber AND U.Guestnumber=@GuestNumber AND U.Warenumber=PW.PacketWareNumber

    END
  END -- Function = 1

  IF @AFunction=2  -- GET USED COUNT
  BEGIN
      SELECT U.Warenumber, U.UsedCount
      FROM [dbo].[Knuthenborg_DiscountUsageCount] U WITH (NOLOCK) 
      WHERE U.ReferenceNumber=@ReferenceNumber AND U.Guestnumber=@GuestNumber
  END -- Function = 2

  IF @AFunction=3 -- SET USED COUNT
  BEGIN
    MERGE [dbo].[Knuthenborg_DiscountUsageCount] T
    USING (SELECT @ReferenceNumber ReferenceNumber, @GuestNumber GuestNumber, @ADiscountSourceID WareNumber, @AUsedCount UsedCount) S
    ON (T.ReferenceNumber=@ReferenceNumber AND T.GuestNumber=@GuestNumber AND T.WareNumber=S.WareNumber)
    WHEN NOT MATCHED BY Target THEN
      INSERT (ReferenceNumber, GuestNumber, WareNumber, UsedCount) VALUES (S.ReferenceNumber, S.GuestNumber, S.WareNumber, S.UsedCount)
    WHEN MATCHED THEN
      UPDATE SET UsedCount = T.UsedCount+S.UsedCount
    ;
  END -- Function = 3
END
GO
IF NOT EXISTS(SELECT * FROM HotelSetup WHERE SearchKey='DiscountPlugin' AND KeyValue='Knuthenborg_DiscountPlugin')
BEGIN
  DROP PROCEDURE dbo.Knuthenborg_DiscountSetup
  DROP PROCEDURE dbo.Knuthenborg_DiscountPlugin
END
/*
MERGE HotelSetup T
USING (SELECT 'DiscountPlugin' SearchKey, 'Knuthenborg_DiscountPlugin' KeyValue) S
ON (T.SearchKey = S.SearchKey)
WHEN NOT MATCHED BY TARGET THEN
  INSERT (SearchKey, KeyValue) VALUES (S.SearchKey, S.KeyValue)
WHEN MATCHED THEN
  UPDATE SET KeyValue = S.KeyValue
;
*/
/*
EXEC Knuthenborg_DiscountPlugin 1, '1382-0', 0, '26,27'

EXEC Knuthenborg_DiscountPlugin 2, '1382-0', 0

--EXEC Knuthenborg_DiscountPlugin 3, '1382-0', 0, NULL, 59, 1
--EXEC Knuthenborg_DiscountPlugin 2, '1382-0', 0
--DELETE FROM [dbo].[Knuthenborg_DiscountUsageCount]
*/
