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) AS
SET NOCOUNT ON

/*
  Created by Kent Johansen July 2011
  Changes:
    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,
(CASE WHEN Reservations.GuestNumber = 0 THEN 1 ELSE 0 END) AS 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,
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
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
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
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)))

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,
(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
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
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)))

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 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
  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
  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)))

  SELECT * FROM #tmp
  UNION
  SELECT * FROM #tmp1

  DROP TABLE #tmp1
END
ELSE
  SELECT * FROM #tmp

DROP TABLE #tmp