CREATE PROCEDURE [dbo].[ReportSalesStatPro] @AFromDate CHAR(8), @AToDate CHAR(8), @AHotelNumberCSV VARCHAR(50), @AReservationTypeCSV VARCHAR(25), 
  @AReservationStatusCSV VARCHAR(25), @ACustomerNumberCSV VARCHAR(2000), @APerArrivalDate BIT, @VATFactor MONEY, @AArr24HoursStarts VARCHAR(5), 
  @ACampaignCodeCSV VARCHAR(2000), @ACountryCSV VARCHAR(2000), @AZipCodeFrom VARCHAR(10), @AZipCodeTo VARCHAR(10), @AShowExtra BIT,
  @ASegmentNumberCSV VARCHAR(2000), @APOSNumberCSV VARCHAR(2000), @ACategoryNumberCSV VARCHAR(2000), @ARoomTypeCSV VARCHAR(1000) AS
SET NOCOUNT ON

/*
  Created by Kent Johansen July 2011
  Changes:
    KJ 20231129: Ext: Added Roomtype
    KJ 20231016: Bug: Fix for "Number of Pax not correct".
    KJ 20231009: Bug: Fix for "Number of rooms not correct". Case #CR236388
    KJ 20230907: Ext: Added category filter. Req# 4203
    KJ 20230425: Turnover reservation report. Req #4166
    KJ 20201028: Added segment and POS. Req #3881
    KJ 20200709: Use Reservations.ReservationCreateDate
    KJ 20200312: Possible to exclude extras.
    KJ 20200309: Opt: Oprimixed sql.
    KJ 20191008: Ext: Reservations should be unique reservations.
    KJ 20191008: Bug: Fix for "Room nights calculated wrong".
    KJ 20190927: Ext: Show Room nights
    KJ 20141215: Bug: Fix for "Extra not calculated correctly". Case #CR080208
    KJ 20130723: Bug; Fix for "Extra not calculated correctly". Case #CR061544
    KJ 20130219: Bug: Fix for "Only include for selected period". Case #CR053107
    KJ 20121120: Bug: Fix for "Extra not calculated correctly". Case #CR050181
    KJ 20120807: Ext: Added Country and zipcode search
    KJ 20120227: Ext: Added Owner Reservations. Req #1169
    KJ 20120118: Bug: Fix for "All reservations does not show". Case #CR038314
    KJ 20110707: Ext: Added Compaigncode

*/

SELECT DISTINCT ReservationMain.ReferenceNumber, 0 AS ReservationConfLineNumber,

CASE WHEN @APerArrivalDate = 1 THEN 
  CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.ArrivalDate ELSE Reservations.ArrivalDate END
ELSE
  CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112)
END AS DateStamp,
(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.TotalPrice ELSE Reservations.TotalPrice END)*@VATFactor AS TurnoverRoom,
ReservationRoomLine.NumberOfRooms,
(
  CASE WHEN Reservations.GuestNumber = 0 THEN 
    DATEDIFF(d, CONVERT(DATETIME, ReservationRoomLine.ArrivalDate, 112), CONVERT(DATETIME, ReservationRoomLine.DepartureDate, 112)) * ReservationRoomLine.NumberOfRooms
  ELSE 
    DATEDIFF(d, CONVERT(DATETIME, Reservations.ArrivalDate, 112), CONVERT(DATETIME, Reservations.DepartureDate, 112))
  END
) AS NumberOfNights,
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Adults*ReservationRoomLine.NumberOfRooms ELSE Reservations.Adults END AS Adults,
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Children1*ReservationRoomLine.NumberOfRooms ELSE Reservations.Children1 END AS Ch1,
CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Children2*ReservationRoomLine.NumberOfRooms ELSE Reservations.Children2 END AS Ch2,
0 AS TurnoverDayMeeting,
0 AS DaymeetingNumberOfPax,
0 AS TurnoverHall,
0 AS TurnoverTable,
0 AS TurnoverExtra,
Reservations.GuestNumber
, ra.Link, Rooms.CustomerNumber, ReservationRoomLine.LineNumber,
Segments.Number AS SegmentNumber, Segments.Text AS SegmentName, Segments.SortNumber AS SegmentSort,
PointOfSales.Number AS POSNumber, PointOfSales.Text AS POSName, PointOfSales.SortNumber AS POSSort,
Reservations.Status AS ReservationStatus
INTO #tmp
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
  AND (@ARoomTypeCSV = '' OR ReservationRoomLine.BookedTypeNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomTypeCSV)))
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  AND (@AHotelNumberCSV = '' OR ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV)))
  AND ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationTypeCSV))
INNER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON Reservations.ReferenceNumber = rax.ReferenceNumber 
  AND Reservations.GuestNumber = rax.GuestNumber
LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber 
LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber 
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = Reservations.MarketSegment
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = Reservations.PointOfSale
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ra.Link
LEFT OUTER JOIN CategoryGroupXTabCustomer WITH (NOLOCK) ON CategoryGroupXTabCustomer.CustomerNumber = Customer.CustomerNumber
LEFT OUTER JOIN CategoryXTabCustomer WITH (NOLOCK) ON CategoryXTabCustomer.CustomerNumber = Customer.CustomerNumber
WHERE
(@ACampaignCodeCSV = '' OR Reservations.FK_CampaignCode IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACampaignCodeCSV)))
AND ra.AddressType = 1
AND (@ACountryCSV = '' OR ra.CountryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACountryCSV)))
AND (@AZipCodeFrom = '' OR ra.ZipCode >= @AZipCodeFrom)
AND (@AZipCodeTo = '' OR ra.ZipCode <= @AZipCodeTo)
AND 
(
  (@APerArrivalDate = 1 AND
    (
      (
        Reservations.GuestNumber = 0 AND ReservationRoomLine.DepartureDate >= @AFromDate AND ReservationRoomLine.ArrivalDate <= @AToDate
      ) 
      OR
      (
        Reservations.GuestNumber > 0 AND Reservations.DepartureDate >= @AFromDate AND Reservations.ArrivalDate <= @AToDate
      ) 
    )
  )
  OR (@APerArrivalDate = 0 AND CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112) <= @AToDate AND CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112) >= @AFromDate)
)
AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV))
AND Reservations.Referencenumber > 0
AND (@ASegmentNumberCSV = '' OR Reservations.MarketSegment IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR Reservations.PointOfSale IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@APOSNumberCSV)))
AND (@ACategoryNumberCSV = '' OR CategoryXTabCustomer.CategoryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACategoryNumberCSV)))

UNION

SELECT DISTINCT ReservationMain.ReferenceNumber, ReservationConferenceLine.ReservationConfLineNumber,

CASE WHEN @APerArrivalDate = 1 THEN 
  ReservationConferenceLine.ArrivalDate
ELSE
  CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112)
END AS DateStamp,
0 AS TurnoverRoom,
0 AS NumberOfRooms,
0 AS NumberOfNights,
0 AS Adults,
0 AS Ch1,
0 AS Ch2,
(CASE WHEN ISNULL(ReservationConferenceLine.TableBooking, 0) = 0 AND ISNULL(Products.FixedPrice, 0) = 0 THEN ReservationConferenceLine.TotalPrice ELSE 0 END*@VATFactor) AS TurnoverDayMeeting,
(CASE WHEN ISNULL(ReservationConferenceLine.TableBooking, 0) = 0 AND ISNULL(Products.FixedPrice, 0) = 0 THEN ReservationConferenceLine.Adults + ReservationConferenceLine.Children1 + ReservationConferenceLine.Children2 ELSE 0 END) AS DaymeetingNumberOfPax,
(CASE WHEN ISNULL(ReservationConferenceLine.TableBooking, 0) = 0 AND ISNULL(Products.FixedPrice, 0) = 1 THEN ReservationConferenceLine.TotalPrice ELSE 0 END*@VATFactor) AS TurnoverHall,
(CASE WHEN ISNULL(ReservationConferenceLine.TableBooking, 0) = 1 THEN ReservationConferenceLine.TotalPrice ELSE 0 END*@VATFactor) AS TurnoverTable,
0 AS TurnoverExtra,
Reservations.GuestNumber
, NULL, NULL, ReservationConferenceLine.ReservationConfLineNumber,
Segments.Number AS SegmentNumber, Segments.Text AS SegmentName, Segments.SortNumber AS SegmentSort,
PointOfSales.Number AS POSNumber, PointOfSales.Text AS POSName, PointOfSales.SortNumber AS POSSort,
Reservations.Status AS ReservationStatus
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber
  AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber
LEFT OUTER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = ReservationConferenceLine.ProductNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  AND (@AHotelNumberCSV = '' OR ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV)))
  AND ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationTypeCSV))
INNER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON Reservations.ReferenceNumber = rax.ReferenceNumber 
  AND Reservations.GuestNumber = rax.GuestNumber
LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber 
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = Reservations.MarketSegment
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = Reservations.PointOfSale
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ra.Link
LEFT OUTER JOIN CategoryGroupXTabCustomer WITH (NOLOCK) ON CategoryGroupXTabCustomer.CustomerNumber = Customer.CustomerNumber
LEFT OUTER JOIN CategoryXTabCustomer WITH (NOLOCK) ON CategoryXTabCustomer.CustomerNumber = Customer.CustomerNumber
WHERE
(@ACampaignCodeCSV = '' OR Reservations.FK_CampaignCode IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACampaignCodeCSV)))
AND ra.AddressType = 1
AND (@ACountryCSV = '' OR ra.CountryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACountryCSV)))
AND (@AZipCodeFrom = '' OR ra.ZipCode >= @AZipCodeFrom)
AND (@AZipCodeTo = '' OR ra.ZipCode <= @AZipCodeTo)
AND 
(
  (@APerArrivalDate = 1 AND 
  (
    ((ReservationConferenceLine.ArrivalDate<=@AToDate AND ReservationConferenceLine.DepartureDate>=@AFromDate)
    OR  (ReservationConferenceLine.ArrivalDate=@AFromDate AND ReservationConferenceLine.ArrivalTime>=@AArr24HoursStarts)
    OR  (ReservationConferenceLine.DepartureDate=@AToDate AND ReservationConferenceLine.DepartureTime<@AArr24HoursStarts))
  )
  OR (@APerArrivalDate = 0 AND CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112) <= @AToDate AND CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112) >= @AFromDate)
  )
)
AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV))
AND Reservations.Referencenumber > 0
AND (@ASegmentNumberCSV = '' OR Reservations.MarketSegment IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR Reservations.PointOfSale IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@APOSNumberCSV)))
AND (@ACategoryNumberCSV = '' OR CategoryXTabCustomer.CategoryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACategoryNumberCSV)))

IF @AShowExtra = 1
BEGIN
  SELECT DISTINCT ReservationMain.ReferenceNumber, 0 AS ReservationConfLineNumber, 
  CASE WHEN @APerArrivalDate = 1 THEN 
    ArrangementLines.Date
  ELSE
    CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112)
  END AS DateStamp,
  0 AS TurnoverRoom, 
  0 AS NumberOfRooms,
  0 AS NumberOfNights,
  0 AS Adults,
  0 AS Ch1,
  0 AS Ch2,
  0 AS TurnoverDayMeeting,
  0 AS DaymeetingNumberOfPax,
  0 AS TurnoverHall,
  0 AS TurnoverTable,
  CASE WHEN @APerArrivalDate = 1 THEN 
    ((SELECT DBO.GetArrangementValue(Reservations.ReferenceNumber, Reservations.GuestNumber, ArrangementLines.Date, ArrangementLines.Date))*@VATFactor) 
  ELSE  
    ((SELECT DBO.GetArrangementValue(Reservations.ReferenceNumber, Reservations.GuestNumber, @AFromDate, @AToDate))*@VATFactor) 
  END
    AS TurnoverExtra,
  Reservations.GuestNumber
  , NULL AS Link, NULL AS CustomerNumber, 0 AS LineNumber,
  Segments.Number AS SegmentNumber, Segments.Text AS SegmentName, Segments.SortNumber AS SegmentSort,
  PointOfSales.Number AS POSNumber, PointOfSales.Text AS POSName, PointOfSales.SortNumber AS POSSort,
  Reservations.Status AS ReservationStatus
  INTO #tmp1
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber 
    AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXTab.ArrangementLineNumber
    AND ISNULL(ArrangementLines.PacketItemNumber, 0) = 0
  INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    AND (@AHotelNumberCSV = '' OR ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV)))
    AND ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationTypeCSV))
  INNER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON Reservations.ReferenceNumber = rax.ReferenceNumber 
    AND Reservations.GuestNumber = rax.GuestNumber
  LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber 
  LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = Reservations.MarketSegment
  LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = Reservations.PointOfSale
  LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ra.Link
  LEFT OUTER JOIN CategoryGroupXTabCustomer WITH (NOLOCK) ON CategoryGroupXTabCustomer.CustomerNumber = Customer.CustomerNumber
  LEFT OUTER JOIN CategoryXTabCustomer WITH (NOLOCK) ON CategoryXTabCustomer.CustomerNumber = Customer.CustomerNumber
  WHERE
  (@ACampaignCodeCSV = '' OR Reservations.FK_CampaignCode IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACampaignCodeCSV)))
  AND ra.AddressType = 1
  AND (@ACountryCSV = '' OR ra.CountryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACountryCSV)))
  AND (@AZipCodeFrom = '' OR ra.ZipCode >= @AZipCodeFrom)
  AND (@AZipCodeTo = '' OR ra.ZipCode <= @AZipCodeTo)
  AND 
  (
    (@APerArrivalDate = 1 AND ArrangementLines.Date <= @AToDate AND ArrangementLines.Date >= @AFromDate) 
    OR (@APerArrivalDate = 0 AND CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112) <= @AToDate AND CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112) >= @AFromDate)
  )
  AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV))
  AND Reservations.Referencenumber > 0
  AND (@ASegmentNumberCSV = '' OR Reservations.MarketSegment IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ASegmentNumberCSV)))
  AND (@APOSNumberCSV = '' OR Reservations.PointOfSale IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@APOSNumberCSV)))
  AND (@ACategoryNumberCSV = '' OR CategoryXTabCustomer.CategoryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACategoryNumberCSV)))

  SELECT #tmp.*, 
  CASE WHEN ISNULL(ReservationAddress.FirstName, '') <> '' THEN ReservationAddress.LastName + ', ' + ReservationAddress.FirstName ELSE ReservationAddress.LastName END AS FullName
  FROM #tmp
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = #tmp.ReferenceNumber
    AND ReservationAddressXTab.GuestNumber = #tmp.GuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
    AND ReservationAddress.AddressType = 1

  UNION

  SELECT #tmp1.*,
  CASE WHEN ISNULL(ReservationAddress.FirstName, '') <> '' THEN ReservationAddress.LastName + ', ' + ReservationAddress.FirstName ELSE ReservationAddress.LastName END AS FullName
  FROM #tmp1
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = #tmp1.ReferenceNumber
    AND ReservationAddressXTab.GuestNumber = #tmp1.GuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
    AND ReservationAddress.AddressType = 1

  DROP TABLE #tmp1
END
ELSE
  SELECT #tmp.*,
  CASE WHEN ISNULL(ReservationAddress.FirstName, '') <> '' THEN ReservationAddress.LastName + ', ' + ReservationAddress.FirstName ELSE ReservationAddress.LastName END AS FullName
  FROM #tmp
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = #tmp.ReferenceNumber
    AND ReservationAddressXTab.GuestNumber = #tmp.GuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
    AND ReservationAddress.AddressType = 1

DROP TABLE #tmp