CREATE PROCEDURE dbo.MiWellVer2 @AHotelCustomerNumber INT, @AFromDate DATETIME, @AToDate DATETIME AS
/*
  Created by Kent Johansen, December 2020
  Get reservation info for MiWell.
  New design with turnover per day

  Changes:
    KJ 20221018: work-a-round (Room_HallType is not corect in MIS).
    KJ 20221017: Bug: Fix for "Wrong roomtype shown". Case #CR224160
    KJ 20210225: CorrectionMoved and CorrectionDeleted= 0 removed. Ok that report include positive and negative amount if transactions is moved.
*/
SET NOCOUNT ON

DECLARE @HotelCountryNumber INT
DECLARE @DefaultCurrencyCode CHAR(3)

SELECT @DefaultCurrencyCode = CurrencyCode 
FROM Countries WITH (NOLOCK) 
WHERE Countrynumber = (Select CONVERT(INT,KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='HotelCurrency')

IF ISNULL(@DefaultCurrencyCode, '') = ''
BEGIN
  SELECT @HotelCountryNumber = CONVERT(INT,KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='CountryCode'
  IF ISNULL(@HotelCountryNumber, 0) > 0
    SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) WHERE CountryNumber=@HotelCountryNumber ORDER BY SortNumber
  IF ISNULL(@DefaultCurrencyCode, '') = ''
    SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) ORDER BY SortNumber    
END

SELECT 
GETUTCDATE() AS ReportDateUtc, CAST(MISTableView.ReferenceNumber AS VARCHAR) AS BookingCode, CAST(MISTableView.GuestNumber AS VARCHAR) AS BookingCode2,
MAX(Reservations.ReservationCreateDate) AS CreationDateTime,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 1
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS GuestNo,
(
  SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryNumber = ((SELECT TOP 1 CountryNumber FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN
   (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND AddressType = 1))
) AS GuestCountry,
(
  SELECT ISO_3166_A2 FROM Countries WITH (NOLOCK) WHERE CountryNumber = ((SELECT TOP 1 CountryNumber FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN
    (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND AddressType = 1))
) AS GuestISO2,
(
  SELECT TOP 1 ZipCode FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 1
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS GuestZipCode,
(
  SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 2
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS CompanyName,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 2
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS CompanyNo,
(
  SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 3
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgencyName,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 3
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgencyNo,
(
  SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 4
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgentName,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 4
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgentNo,
Segments.Text AS Segment,
PointOfSales.Text AS POS,
PurposeCodes.Text AS PurposeCode,
GuestTypes.Text AS GuestType,
/*
CASE WHEN Room_HallType = 0 THEN -- FROM GenerateTurnoverMIS... 0=Room, 1=Hall, 2=From Room Allotment
  (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypes.RoomTypeNumber = MISTableView.RoomTypeNumber)
WHEN Room_HallType = 1 THEN
  (SELECT Type FROM ConferenceRoomTypes WITH (NOLOCK) WHERE ConferenceRoomTypes.ConferenceRoomTypeNumber = MISTableView.RoomTypeNumber)
ELSE
  ''
END AS RoomType,
*/
-- work-a-round (Room_HallType is not corect in MIS).
(
  CASE 
    WHEN MISTableView.ValueType = 1 THEN (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypes.RoomTypeNumber = MISTableView.RoomTypeNumber)
    WHEN MISTableView.ValueType = 2 THEN (SELECT Type FROM ConferenceRoomTypes WITH (NOLOCK) WHERE ConferenceRoomTypes.ConferenceRoomTypeNumber = MISTableView.RoomTypeNumber)
    WHEN MISTableView.ValueType = 3 THEN (SELECT Type FROM ConferenceRoomTypes WITH (NOLOCK) WHERE ConferenceRoomTypes.ConferenceRoomTypeNumber = MISTableView.RoomTypeNumber)
    WHEN MISTableView.ValueType = 4 THEN ''
    WHEN MISTableView.ValueType = 5 THEN ''
    ELSE ''
  END
) AS RoomType,
(
  SELECT TOP 1 ArrivalDate
  FROM Reservations WITH (NOLOCK)
  WHERE Reservations.ReferenceNumber = MISTableView.ReferenceNumber 
  AND Reservations.GuestNumber = MISTableView.GuestNumber
  ORDER BY ArrivalDate ASC
) AS ArrivalDate,
(
  SELECT TOP 1 DepartureDate
  FROM Reservations WITH (NOLOCK) 
  WHERE Reservations.ReferenceNumber = MISTableView.ReferenceNumber 
  AND Reservations.GuestNumber = MISTableView.GuestNumber
  ORDER BY DepartureDate DESC
) AS DepartureDate,

Reservations.Status, 
CASE WHEN Reservations.Status = 7 THEN
  ISNULL(
    (
      SELECT TOP 1 Date 
      FROM ReservationFollowUpXTab rfx WITH (NOLOCK) 
      INNER JOIN FollowUp f WITH (NOLOCK) ON rfx.FollowUpNumber = f.FollowUpNumber 
      WHERE RFX.ReferenceNumber = Reservations.ReferenceNumber 
      AND RFX.GuestNumber = Reservations.GuestNumber 
      AND type = 5 
      AND SubType = 3 
      ORDER BY f.FollowUpNumber
    ),
    (
      SELECT TOP 1 Date 
      FROM ReservationFollowUpXTab rfx WITH (NOLOCK) 
      INNER JOIN FollowUp f WITH (NOLOCK) ON rfx.FollowUpNumber = f.FollowUpNumber 
      WHERE RFX.ReferenceNumber = Reservations.ReferenceNumber 
      AND RFX.GuestNumber = 0
      AND type = 5 
      AND SubType = 3 
      ORDER BY f.FollowUpNumber
    )
  )  
ELSE  
  NULL
END AS CancellationDateTime,
SUM(MISTableView.ActualNumberOfRooms) + SUM(MISTableView.BookedNumberOfRooms) AS NumberOfRooms,
SUM(MISTableView.BookedAdult) + SUM(MISTableView.BookedChildren1) + SUM(MISTableView.BookedChildren2) +
SUM(MISTableView.ActualAdult) + SUM(MISTableView.ActualChildren1) + SUM(MISTableView.ActualChildren2) AS PAX,
ProductWareLinkNumber AS LineNumber,
@DefaultCurrencyCode AS Currency,
Products.ProductName AS RateCode,
MISTableView.DateStamp,
MISTableView.ValueType,
(
  CASE 
    WHEN MISTableView.ValueType = 1 THEN 'Room'
    WHEN MISTableView.ValueType = 2 THEN 'Hall'
    WHEN MISTableView.ValueType = 3 THEN 'Daymeeting'
    WHEN MISTableView.ValueType = 4 THEN 'Restaurant'
    WHEN MISTableView.ValueType = 5 THEN 'Other'
    ELSE 'Unknown'
  END

) AS ValueTypeStr,  --mvtNotDefined, mvtRooms, mvtHalls, mvtDaymeetings, mvtRestaurant, mvtOther
SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat) AS AmountExVAT,
SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) AS AmountExPackExVAT
FROM MISTableView WITH (NOLOCK)
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTableView.ReferenceNumber
  AND Reservations.GuestNumber = MISTableView.GuestNumber
LEFT OUTER JOIN PurposeCodes WITH (NOLOCK) ON PurposeCodes.PurposeCodeNumber = Reservations.PurposeCode
LEFT OUTER JOIN GuestTypes WITH (NOLOCK) ON GuestTypes.TypeNumber = Reservations.GuestType
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = MISTableView.PointOfSaleNumber
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISTableView.SegmentNumber
LEFT OUTER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = MISTableView.ProductNumber
WHERE MISTableView.HotelCustomerNumber = @AHotelCustomerNumber
AND MISTableView.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISTableView.ReferenceNumber > 0
--AND MISTableView.CorrectionMoved = 0
--AND MISTableView.CorrectionDeleted = 0
GROUP BY DateStamp, ValueType, MISTableView.RoomtypeNumber, Room_HallType, MISTableView.ReferenceNumber, MISTableView.GuestNumber, ProductWareLinkNumber, 
  Reservations.ReferenceNumber, Reservations.GuestNumber,
  Segments.Text, PointOfSales.Text, PurposeCodes.Text, GuestTypes.Text, Reservations.Status, Products.ProductName
HAVING SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat) <> 0 OR
  SUM(MISTableView.ActualNumberOfRooms) + SUM(MISTableView.BookedNumberOfRooms) > 0
ORDER BY MISTableView.ReferenceNumber, MISTableView.GuestNumber, MISTableView.DateStamp, MISTableView.ValueType