CREATE PROCEDURE dbo.OnlineGetProductPriceDiscount @AProductNumber INT, @ARoomTypeNumber INT, @AAdultPax INT, @AChild1Pax INT, @AChild2Pax INT, @AStartDateTime DATETIME, @AEndDateTime DATETIME, @ACustomerNumber INT = 0, @AHotelNumber INT = 0 AS
--Created AV 2007 1203
/*  Changes:
    SG 2017-08-30: When departuretime is past midnight, but before Arr24HoursStart, subtract 1 day in order to avoid price for two days.
    KJ 20131024: Added discount per hotel.
*/
DECLARE @IsDayProduct BIT
DECLARE @IsFixedPrice BIT
DECLARE @PricePerType BIT
DECLARE @DiscountRooms MONEY
DECLARE @CurrentDiscount MONEY
DECLARE @DiscountAll MONEY
DECLARE @DiscountHalls MONEY
DECLARE @DiscountMeeting MONEY
DECLARE @CalcTotalPrice REAL
DECLARE @CalcArrivalPrice REAL
DECLARE @DayProduct4 INT
DECLARE @OverNight4 INT
DECLARE @CustomerTypeNumber INT
DECLARE @LinkedCustomerTypeNumber INT
DECLARE @LinkedCustomerNumber INT

Declare @DayStartHour int
Declare @DepartureHour int
Declare @DateDiff int

--HotelSetup -> Basic -> "Arrangement 24 hours start at"
Select @DayStartHour = dbo.GetHotelSetupKeyValue('Arr24HoursStart', -1)
Set @DayStartHour = IsNull(@DayStartHour, 5)

Set @DepartureHour = DatePart(hour, @AEndDateTime);
Set @DateDiff = Datediff(day, @AStartDateTime, @AEndDateTime)

If @Datediff > 0 And @DepartureHour < @DayStartHour
Begin
  Set @AEndDateTime = DateAdd(day, -1, @AEndDateTime)
End


SELECT @DayProduct4 = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DayProduct4'
SELECT @OverNight4 = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OverNight4'

SET @CurrentDiscount = 0
SELECT @IsDayProduct = DayProduct, @IsFixedPrice = FixedPrice, @PricePerType = PricePerType FROM Products WITH (NOLOCK) WHERE ProductNumber = @AProductNumber
IF @PricePerType <> 1
  SET @ARoomTypeNumber = 0

EXEC DBO.ProductPrices @AProductNumber, @AStartDateTime, @AEndDateTime, @ARoomTypeNumber, 1, @AAdultPax, @AChild1Pax, @AChild2Pax, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT

IF @ACustomerNumber  > 0 AND @IsDayProduct = 1
BEGIN
  SELECT @CustomerTypeNumber = CustomerTypeNumber FROM Customer WITH (NOLOCK) WHERE CustomerNumber = @ACustomerNumber
  IF @CustomerTypeNumber = 1 AND @DayProduct4 = 2 -- If we are dealing with a Guest and if the hotelsetup setting says: Take the product from the Company
  BEGIN
    SELECT @LinkedCustomerNumber = ISNULL(CustomerNumber,0) FROM CustomerLinks WITH (NOLOCK) WHERE CustomerLinkNumber = @ACustomerNumber
    IF @LinkedCustomerNumber > 0
    BEGIN
      SELECT @LinkedCustomerTypeNumber = CustomerTypeNumber FROM Customer WITH (NOLOCK) WHERE CustomerNumber = @LinkedCustomerNumber
      IF @LinkedCustomerTypeNumber = 2 -- A Company
        SET @ACustomerNumber = @LinkedCustomerNumber
    END
  END

  SELECT @DiscountRooms = ISNULL(DiscountRooms, 0), 
  @DiscountAll = ISNULL(DiscountAll, 0), 
  @DiscountHalls =ISNULL( DiscountHalls, 0), 
  @DiscountMeeting = ISNULL(DiscountMetting, 0) 
  FROM CustomerFieldsPerHotel WITH (NOLOCK)
  WHERE CustomerNumber = @ACustomerNumber
  AND HotelNumber = @AHotelNumber
  SET @CurrentDisCount = @DiscountAll
  IF @IsDayProduct = 1 AND @CurrentDiscount = 0
  BEGIN
    IF @IsFixedPrice = 1 AND @DiscountHalls > 0
      SET @CurrentDiscount = @DisCountHalls
    ELSE IF @IsFixedPrice = 0 AND @DiscountMeeting > 0
      SET @CurrentDiscount = @DisCountMeeting 
  END
END

IF @CurrentDiscount > 0
BEGIN
  SET @CalcTotalPrice =  @CalcTotalPrice * (1 - (@CurrentDiscount / 100))
  SET @CalcArrivalPrice =  @CalcArrivalPrice * (1 - (@CurrentDiscount / 100))
END
SELECT @CalcArrivalPrice, @CalcTotalPrice
