CREATE PROCEDURE dbo.Revenue @AHotelNumberStr VARCHAR(100), @AStartDate CHAR(8), @AEndDate CHAR(8) AS
SET NOCOUNT ON
/*
  Created by Kent Johansen July 2016

  Changes:
    KJ 20200428: Ext: Added provision in MIS. Req #3788
    KJ 20181213: Bug: Fix for "Cannot resolve the collation conflict between "Danish_Norwegian_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation." on certain databases Case #CR154707
    KJ 20160919: Bug: Fix for "Wrong field names from availability"

*/

DECLARE @AInclAllotment BIT
DECLARE @LInclProvision BIT

SELECT @AInclAllotment = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
SET @AInclAllotment = ISNULL(@AInclAllotment, 1)

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
)

INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1,  @AHotelNumberStr, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

SELECT 
  CONVERT(DATETIME, #tmpAvailability.DateStamp, 112) AS Date, 
  #tmpAvailability.HotelNumber,
  CASE WHEN @AInclAllotment = 1 THEN
    SUM(ISNULL(#tmpAvailability.SoldRooms, 0))+SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0))
  ELSE
    SUM(ISNULL(#tmpAvailability.SoldRooms, 0))
  END AS SoldRooms,
  (
  SELECT SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.BookedPriceExPackageExVat)  
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  WHERE MISTableView.Datestamp COLLATE DATABASE_DEFAULT = #tmpAvailability.DateStamp COLLATE DATABASE_DEFAULT
  AND Customer.HotelNumber = #tmpAvailability.HotelNumber
  AND MISTableView.ValueType = 1
  AND (@LInclProvision = 1 OR ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  ) AS RoomRevenue,
  SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)) AS OutOfOrderBooms,
  (
  SELECT SUM(MISTableView.ActualPriceInclPackageExVat) + SUM(MISTableView.BookedPriceInclPackageExVat)  
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  WHERE MISTableView.Datestamp COLLATE DATABASE_DEFAULT = #tmpAvailability.DateStamp COLLATE DATABASE_DEFAULT
  AND Customer.HotelNumber = #tmpAvailability.HotelNumber
  AND (@LInclProvision = 1 OR ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  ) AS TotalRevenue,
  SUM(ISNULL(#tmpAvailability.Adults, 0)) + SUM(ISNULL(#tmpAvailability.Children1, 0)) + SUM(ISNULL(#tmpAvailability.Children2, 0)) AS Guests
FROM #tmpAvailability
GROUP BY #tmpAvailability.DateStamp, #tmpAvailability.HotelNumber
ORDER BY #tmpAvailability.HotelNumber, #tmpAvailability.DateStamp

DROP TABLE #tmpAvailability