CREATE PROCEDURE dbo.PMIPmsHistory @AHotelNumber INT, @AStartDate CHAR(8), @AEndDate CHAR(8), @AType TINYINT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen January 2013
  Returns the Pms info for PMI/d2o interface
  DataType: 1=Market/Segment, 2=Room type, 3=Rate code/Product, 4=BookingChannel/xx, 5=Booking Source/yy
  SubType: 
    PmsHistory: 1=Total, 2=Noshow, 3=Departure, 4=Arrival
    PmsOTB: 1=TotalDifinite, 2=DepartureDifinite, 3=ArrivalDifinite, 4=TotalTentative, 5=DepartureTentative, 6=ArrivalTentative
  Changes:
    KJ 20200428: Ext: Added provision in MIS. Req #3788
    KJ 20160919: Bug: Fix for "Wrong field names from availability"
    KJ 20150317: Bug: Fit changes made in GenerateAvailability
    KJ 20130430: Bug: Fix for "To many rooms when namelist hasn't been created" Case #CR055249
    KJ 20130226: Ext: Added @AType for support of PmsOTB
*/
DECLARE @LInclProvision BIT

SELECT @LInclProvision = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='IncludeProvisionInMIS'
SET @LInclProvision = ISNULL(@LInclProvision, 0)

CREATE TABLE #tmpAvailability
(
  DateStamp CHAR(8) NOT NULL,
  RoomTypeNumber INT NOT NULL,
  SegmentNumber INT NOT NULL,
  PointOfSaleNumber INT NOT NULL,
  HotelNumber INT NOT NULL,
  SoldRoomType INT NOT NULL,
  ProductNumber INT NOT NULL,
  ReservationGroup INT NOT NULL,
  SoldRooms SMALLINT NULL,
  Adults SMALLINT NULL,
  Children1 SMALLINT NULL,
  Children2 SMALLINT NULL,
  NumberOfConfirmed SMALLINT NULL,
  NumberOfProvisionals SMALLINT NULL,
  NumberOfGuaranteed SMALLINT NULL,
  NumberOfTentative SMALLINT NULL,
  NumberOfWaitinglists SMALLINT NULL,
  NumberOfDayrooms SMALLINT NULL,
  FreeRoomsAllotment SMALLINT NULL,
  TotalRoomsAllotment SMALLINT NULL,
  PaxAllotment SMALLINT NULL,
  NumberOfRoomsSoldToday SMALLINT NULL,
  NumberOfOutOfOrder SMALLINT NULL,
  NumberOfOutForService SMALLINT NULL,
  NumberOfOccupied SMALLINT NULL,
  NumberOfOccupiedGroup SMALLINT NULL,
  SoldPrice MONEY NULL,
  RequestorID INT NULL,
  NumberOfRoomsSoldCourse smallint NULL,
  NumberOfRoomsSoldAttendee smallint NULL,
  NumberOfArrival SMALLINT NULL,
  NumberOfInhouse SMALLINT NULL,
  NumberOfStay SMALLINT NULL,
  NumberOfDeparture SMALLINT NULL,
  ArrivalPax SMALLINT NULL,
  InhousePax SMALLINT NULL,
  StayPax SMALLINT NULL,
  DeparturePax SMALLINT NULL,
  NumberOfWaitingArrival SMALLINT NULL,
  NumberOfTentativeArrival SMALLINT NULL,
  WaitingArrivalPax SMALLINT NULL, 
  TentativeArrivalPax SMALLINT NULL
)

DELETE FROM #tmpAvailability

CREATE TABLE #tmp 
(
  DataType INT,
  DataTypeName VARCHAR(20),
  SubType INT,
  SubTypeName VARCHAR(20),
  Date CHAR(8),
  DataTypeValue INT,
  DataTypeValueName VARCHAR(100),
  Pieces INT,
  Adults INT,
  Children INT,
  Revenue MONEY)

DECLARE @DateStamp CHAR(8)
DECLARE @ReservationStatus INT
DECLARE @SegmentNumber INT
DECLARE @SegmentName VARCHAR(30)
DECLARE @RoomTypeNumber INT
DECLARE @RoomTypeName VARCHAR(10)
DECLARE @ProductNumber INT
DECLARE @ProductName VARCHAR(50)
DECLARE @ArrivalDate CHAR(8)
DECLARE @DepartureDate CHAR(8)
DECLARE @Rooms INT
DECLARE @Adults INT
DECLARE @Children INT
DECLARE @Revenue MONEY

DECLARE @SubType INT
DECLARE @HotelCustomerNumber INT

DECLARE @DoSkip BIT
DECLARE @tmpSubType INT
DECLARE @HotelNumberSTR VARCHAR(100)

SET @HotelNumberSTR = CAST(@AHotelNumber AS VARCHAR(100))
IF @HotelNumberSTR = '-1'
	SET @HotelNumberSTR = ''

SELECT @HotelCustomerNumber = CustomerNumber
FROM Customer WITH (NOLOCK)
WHERE HotelNumber = @AHotelNumber
AND CustomerTypeNumber = 7
AND ISNULL(MarkAsDeleted, 0) = 0

DECLARE db_cur CURSOR FOR
  SELECT MISTableView.DateStamp, MISTableView.ReservationStatus, MISTableView.SegmentNumber, Segments.Text,
  MISTableView.RoomTypeNumber, RoomTypes.Type, ISNULL(MISTableView.ProductNumber,0), ISNULL(Products.ProductName, 'UNKNOWN'),
  Reservations.ArrivalDate, Reservations.DepartureDate,
  SUM(MISTableView.ActualNumberOfRooms) + SUM(MISTableView.BookedNumberOfRooms),
  SUM(MISTableView.ActualAdult) + SUM(MISTableView.BookedAdult),
  SUM(MISTableView.ActualChildren1) + SUM(MISTableView.BookedChildren1) + SUM(MISTableView.ActualChildren2) + SUM(MISTableView.BookedChildren2),
  SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.BookedPriceExPackageExVat)
  FROM MISTableView WITH (NOLOCK)
  LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISTableView.SegmentNumber
  LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = MISTableView.RoomTypeNumber
  LEFT OUTER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = MISTableView.ProductNumber
  INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTableView.ReferenceNumber
    AND Reservations.GuestNumber = MISTableView.GuestNumber
  WHERE MISTableView.Datestamp >= @AStartDate
  AND MISTableView.DateStamp <= @AEndDate
  AND MISTableView.ValueType = 1 --AND MISTableView.MISData IN (1)
  AND MISTableView.ReservationStatus <> 7 -- Cancelled
  AND MISTableView.HotelCustomerNumber = @HotelCustomerNumber
  AND (@LInclProvision = 1 OR ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  GROUP BY MISTableView.DateStamp, MISTableView.ReservationStatus, MISTableView.SegmentNumber, Segments.Text,
    MISTableView.RoomTypeNumber, RoomTypes.Type, MISTableView.ProductNumber, Products.ProductName,
    Reservations.GuestNumber, Reservations.ArrivalDate, Reservations.DepartureDate--, ReservationRoomLine.ArrivalDate, ReservationRoomLine.DepartureDate
OPEN db_cur

FETCH NEXT FROM db_cur INTO @DateStamp, @ReservationStatus, @SegmentNumber, @SegmentName, @RoomTypeNumber, @RoomTypeName, @ProductNumber,
  @ProductName, @ArrivalDate, @DepartureDate, @Rooms, @Adults, @Children, @Revenue
  
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @DoSkip = 0
  
  IF @AType = 1
  BEGIN
    IF @ReservationStatus = 8 -- Noshow
      SET @SubType = 2
    ELSE IF @DepartureDate = @DateStamp
      SET @SubType = 3
    ELSE IF @ArrivalDate = @DateStamp
      SET @SubType = 4
    ELSE 
      SET @SubType = -1
  END
  ELSE
  BEGIN
    IF @ReservationStatus = 8 -- noshow
      SET @DoSkip = 1
    ELSE IF @ReservationStatus = 4 -- Tentative
    BEGIN
      IF @DepartureDate = @DateStamp
        SET @SubType = 5
      ELSE IF @ArrivalDate = @DateStamp
        SET @SubType = 6
      ELSE 
        SET @SubType = 4
    END
    ELSE
    BEGIN
      IF @DepartureDate = @DateStamp
        SET @SubType = 2
      ELSE IF @ArrivalDate = @DateStamp
        SET @SubType = 3
      ELSE 
        SET @SubType = 1
    END
  END
  
  IF @DoSkip = 0
  BEGIN
    -- INITIALIZE START
    -- DataType = 1 Market/Segment
    EXEC PMIPmsUpdateData 1, 1, @DateStamp, @SegmentNumber, @SegmentName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 1, 2, @DateStamp, @SegmentNumber, @SegmentName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 1, 3, @DateStamp, @SegmentNumber, @SegmentName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 1, 4, @DateStamp, @SegmentNumber, @SegmentName, 0, 0, 0, 0, @AType
    IF @AType = 2
    BEGIN
      EXEC PMIPmsUpdateData 1, 5, @DateStamp, @SegmentNumber, @SegmentName, 0, 0, 0, 0, @AType
      EXEC PMIPmsUpdateData 1, 6, @DateStamp, @SegmentNumber, @SegmentName, 0, 0, 0, 0, @AType
    END

    -- DataType = 2 Room type
    EXEC PMIPmsUpdateData 2, 1, @DateStamp, @RoomTypeNumber, @RoomTypeName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 2, 2, @DateStamp, @RoomTypeNumber, @RoomTypeName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 2, 3, @DateStamp, @RoomTypeNumber, @RoomTypeName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 2, 4, @DateStamp, @RoomTypeNumber, @RoomTypeName, 0, 0, 0, 0, @AType
    IF @AType = 2
    BEGIN
      EXEC PMIPmsUpdateData 2, 5, @DateStamp, @RoomTypeNumber, @RoomTypeName, 0, 0, 0, 0, @AType
      EXEC PMIPmsUpdateData 2, 6, @DateStamp, @RoomTypeNumber, @RoomTypeName, 0, 0, 0, 0, @AType
    END

    -- DataType = 3 Rate code/Product
    EXEC PMIPmsUpdateData 3, 1, @DateStamp, @ProductNumber, @ProductName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 3, 2, @DateStamp, @ProductNumber, @ProductName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 3, 3, @DateStamp, @ProductNumber, @ProductName, 0, 0, 0, 0, @AType
    EXEC PMIPmsUpdateData 3, 4, @DateStamp, @ProductNumber, @ProductName, 0, 0, 0, 0, @AType
    IF @AType = 2
    BEGIN
      EXEC PMIPmsUpdateData 3, 5, @DateStamp, @ProductNumber, @ProductName, 0, 0, 0, 0, @AType
      EXEC PMIPmsUpdateData 3, 6, @DateStamp, @ProductNumber, @ProductName, 0, 0, 0, 0, @AType
    END
    -- INITIALIZE END

    
    IF @AType = 1
      SET @tmpSubType = 1
    ELSE
    BEGIN
      IF @SubType <= 3
        SET @tmpSubType = 1
      ELSE
        SET @tmpSubType = 4
    END

    EXEC PMIPmsUpdateData 1, @tmpSubType, @DateStamp, @SegmentNumber, @SegmentName, @Rooms, @Adults, @Children, @Revenue, @AType
    EXEC PMIPmsUpdateData 2, @tmpSubType, @DateStamp, @RoomTypeNumber, @RoomTypeName, @Rooms, @Adults, @Children, @Revenue, @AType
    EXEC PMIPmsUpdateData 3, @tmpSubType, @DateStamp, @ProductNumber, @ProductName, @Rooms, @Adults, @Children, @Revenue, @AType

    IF (@AType = 1 AND @SubType > -1) OR (@AType = 2 AND @SubType <> 1 AND @SubType <> 4)
    BEGIN
      EXEC PMIPmsUpdateData 1, @SubType, @DateStamp, @SegmentNumber, @SegmentName, @Rooms, @Adults, @Children, @Revenue, @AType
      EXEC PMIPmsUpdateData 2, @SubType, @DateStamp, @RoomTypeNumber, @RoomTypeName, @Rooms, @Adults, @Children, @Revenue, @AType
      EXEC PMIPmsUpdateData 3, @SubType, @DateStamp, @ProductNumber, @ProductName, @Rooms, @Adults, @Children, @Revenue, @AType
    END
  END
  
/* Skal måske aktiveres senere når det bliver afgjort hvad BookingChannel og BookingSource er i Picasso.
  -- DataType = 4 BookingChannel/xx
  EXEC PMIPmsUpdateData 4, 1, @DateStamp, @xxNumber, @xxName, @Rooms, @Adults, @Children, @Revenue
  IF @SubType > -1
    EXEC PMIPmsUpdateData 4, @SubType, @DateStamp, @xxNumber, @xxName, @Rooms, @Adults, @Children, @Revenue

  -- DataType = 5 Booking Source/yy
  EXEC PMIPmsUpdateData 5, 1, @DateStamp, @yyNumber, @yyName, @Rooms, @Adults, @Children, @Revenue
  IF @SubType > -1
    EXEC PMIPmsUpdateData 5, @SubType, @DateStamp, @yyNumber, @yyName, @Rooms, @Adults, @Children, @Revenue
*/

  FETCH NEXT FROM db_cur INTO @DateStamp, @ReservationStatus, @SegmentNumber, @SegmentName, @RoomTypeNumber, @RoomTypeName, @ProductNumber, 
    @ProductName, @ArrivalDate, @DepartureDate, @Rooms, @Adults, @Children, @Revenue
END

CLOSE db_cur
DEALLOCATE db_cur

INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1,  @HotelNumberSTR, '', 1, 0, '', @AIncludeOccupiedInOFS = 1
  
SELECT #tmp.DataType, #tmp.DataTypeName, #tmp.SubType, #tmp.SubTypeName, #tmp.Date, #tmp.DataTypeValue, #tmp.DataTypeValueName, 
#tmp.Pieces,
#tmp.Adults,
#tmp.Children,
#tmp.Revenue,
( SELECT SUM(#tmpAvailability.NumberOfOutOfOrder) FROM #tmpAvailability WHERE #tmpAvailability.DateStamp = #tmp.Date
  AND #tmpAvailability.RoomTypeNumber = #tmp.DataTypeValue) AS OOO
FROM #tmp
ORDER BY Date, DataType, DataTypeValue, SubType

DROP TABLE #tmp
DROP TABLE #tmpAvailability