CREATE PROCEDURE DBO.MISDetailed @AHotelCustomerNumber INT, @AFromDate DATETIME, @AToDate DATETIME, @AIncludeCompanyInfo BIT = 0 AS
/*
  Created by Kent Johansen, November 2023
  Changes:
    KJ 20240617: Ext: Added TransactionText
    KJ 20240425: Added ConfirmDateStamp + ReservationCreatedBy
    KJ 20240109: Added ware/waregroups info
    KJ 20231215: Added guesttype and companyinfo
*/

SELECT Personel.UserIDCode, MISTableViewDeleted.ReservationStatus, MISTableViewDeleted.HotelCustomerNumber, Customer.LastName AS HotelName, MISTableViewDeleted.ReferenceNumber, MISTableViewDeleted.FolioNumber,
Reservations.ArrivalDate, Reservations.DepartureDate, MISTableViewDeleted.DateStamp,
MISTableViewDeleted.AccountNumber, MISTableViewDeleted.ValueType, SUM(MISTableViewDeleted.BookedAdult) + SUM(MISTableViewDeleted.ActualAdult) AS Adult,
SUM(MISTableViewDeleted.BookedNumberOfRooms) + SUM(MISTableViewDeleted.ActualNumberOfRooms) AS NumberOfRooms, 
SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.BookedOnlyPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualOnlyPackageExVat) AS AmountExVAT,
SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.BookedOnlyPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) + SUM(MISTableViewDeleted.ActualOnlyPackage) AS AmountInclVAT,
ProjectCodes.Name AS ProjectCodeName, Departments.Name AS DepartmentName, Reservations.ReservationCreateDate, PointOfSales.Text AS POSName, Segments.Text AS SegmentName, Products.ProductName AS RatePlan,
RoomTypes.Type AS RoomType, ConferenceRoomTypes.Type AS ConferenceRoomType, MISTableViewDeleted.CancellationDate, GuestTypes.Text AS GuestTypeName,
MISTableViewDeleted.WareGroupNumber, WareGroups.Text AS WareGroupName, MISTableViewDeleted.WareNumber, Ware.WareName, ReservationsExt.ConfirmDateStamp,
(SELECT TOP 1 (SELECT UserIdCode FROM Personel WITH (NOLOCK) WHERE UserIdNumber = Followup.Createdby) FROM Followup WITH (NOLOCK) WHERE SubType = 1 AND UserCreated = 0 AND FollowupNumber IN (
   SELECT FollowupNumber FROM ReservationFollowupXTab WITH (NOLOCK) WHERE ReservationFollowupXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationFollowupXTab.GuestNumber = Reservations.GuestNumber)) AS ReservationCreatedBy,
MISTableViewDeleted.Text AS TransactionText
INTO #tmp
FROM MISTableViewDeleted WITH (NOLOCK)
LEFT OUTER JOIN Personel WITH (NOLOCK) ON Personel.UserIDNumber = MISTableViewDeleted.StaffID
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableViewDeleted.HotelCustomerNumber
LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTableViewDeleted.ReferenceNumber
  AND Reservations.GuestNumber = 0
LEFT OUTER JOIN ProjectCodes WITH (NOLOCK) ON ProjectCodes.PK_ProjectCode = MISTableViewDeleted.ProjectCode
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = MISTableViewDeleted.PointOfSaleNumber
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISTableViewDeleted.SegmentNumber
LEFT OUTER JOIN GuestTypes WITH (NOLOCK) ON GuestTypes.TypeNumber = Reservations.GUestType
LEFT OUTER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = MISTableViewDeleted.ProductNumber
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = MISTableViewDeleted.RoomTypeNumber
LEFT OUTER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = MISTableViewDeleted.RoomTypeNumber
LEFT OUTER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = MISTableViewDeleted.WareGroupNumber
LEFT OUTER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = MISTableViewDeleted.WareNumber
INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = MISTableViewDeleted.DepartmentNumber
LEFT OUTER JOIN ReservationsExt WITH (NOLOCK) ON ReservationsExt.FK_ReferenceNumber = MISTableViewDeleted.ReferenceNumber
  AND ReservationsExt.FK_GuestNumber = MISTableViewDeleted.GuestNumber
WHERE HotelCustomerNumber = @AHotelCustomerNumber
AND DateStamp BETWEEN @AFromDate AND @AToDate
GROUP BY Personel.UserIDCode, MISTableViewDeleted.ReservationStatus, MISTableViewDeleted.HotelCustomerNumber, Customer.LastName, MISTableViewDeleted.ReferenceNumber, MISTableViewDeleted.FolioNumber,
Reservations.ArrivalDate, Reservations.DepartureDate, MISTableViewDeleted.DateStamp,
MISTableViewDeleted.AccountNumber, MISTableViewDeleted.ValueType, ProjectCodes.Name, Departments.Name, Reservations.ReservationCreateDate, PointOfSales.Text, Segments.Text, Products.ProductName,
RoomTypes.Type, ConferenceRoomTypes.Type, MISTableViewDeleted.CancellationDate, GuestTypes.Text, MISTableViewDeleted.WareGroupNumber, WareGroups.Text, MISTableViewDeleted.WareNumber, Ware.WareName,
ReservationsExt.ConfirmDateStamp, Reservations.ReferenceNumber, Reservations.GuestNumber, MISTableViewDeleted.Text

IF @AIncludeCompanyInfo = 1
  SELECT #tmp.*, ReservationAddress.LastName AS CompanyName
  FROM #tmp
  LEFT OUTER JOIN ReservationAddressXTAB WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = #tmp.ReferenceNumber
    AND ReservationAddressXTab.GuestNumber = 0
    AND ReservationAddressXTab.AddressTypeNumber = 2
  LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
ELSE
  SELECT *
  FROM #tmp

DROP TABLE #tmp