CREATE PROCEDURE dbo.PersonnelSalesReport @AFromDate CHAR(8), @AToDate CHAR(8), @AHotelNumberCSV VARCHAR(50), @AReservationTypeCSV VARCHAR(25), 
  @AReservationStatusCSV VARCHAR(25), @AUserIdNumberCSV VARCHAR(2000), @APerArrivalDate BIT, @VATFactor MONEY, @ACampaignCodeCSV VARCHAR(2000),
  @AAddressTypeCSV VARCHAR(20), @AMinRooms INT, @AMaxRooms INT, @ACustomerNumberCSV VARCHAR(2000) AS
SET NOCOUNT ON
/*
  Created by Kent Johansen December 2009
  Changes:
    KJ 20200907: Reservations.ReservationCreateDate
    KJ 20130219: Bug: Fix for "Only include for selected period". Case #CR053107
    KJ 20120320: Bug: Fix for "Extra calculated wrong". Case #CR041188
    KJ 20120228: Ext: Added Owner Reservations. Req #1169
    KJ 20110406: Ext: CampaignCode deactivated for now. Case# CR022184+CR022147.
    KJ 20110325: Ext: Staff sales report - Added customer and some search params. Req# 867.
    KJ 20110310: Ext: Added CampaignCode. Req# 853
    KJ 20110226: Ext: Added VATFactor. Req# 826
    KJ 20100322: Bug: Fix for "Not all guest is added to the result"
    KJ 20100122: Bug: Fix for "Extra not shown in no room on reservation"
    KJ 20090112: Total rewrite of query
*/

SELECT DISTINCT ReservationMain.StaffId AS UserIdNumber, Personel.UserIdCode, ReservationMain.ReferenceNumber, 0, 

CASE WHEN Reservations.GuestNumber = 0 THEN
  CASE WHEN ISNULL(ReservationRoomLine.Day2, 0) = 0 THEN ReservationRoomLine.ProductNumber ELSE -1 END
ELSE 
  CASE WHEN ISNULL(Reservations.Day2, 0) = 0 THEN Reservations.ProductNumber ELSE -1 END
END AS ProductNumber,

CASE WHEN @APerArrivalDate = 1 THEN 
  CASE WHEN Reservations.GuestNumber = 0 THEN DATEPART(m, ReservationRoomLine.ArrivalDate) ELSE DATEPART(m, Reservations.ArrivalDate) END
ELSE
  DATEPART(m, Reservations.ReservationCreateDate) 
END AS MonthIdx,
Reservations.PurposeCode, Reservations.PointOfSale, Reservations.MarketSegment, Reservations.GuestType, ReservationMain.ReservationType,
Reservations.Status,
(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.TotalPrice ELSE Reservations.TotalPrice END)*@VATFactor AS TurnoverRoom, 
0 AS TurnoverHall,
0 AS TurnoverDayMeeting,
0 AS TurnoverTable,
0 AS TurnoverExtra,
Reservations.GuestNumber,
( 
  SELECT TOP 1 ra.Link FROM ReservationAddress ra WITH (NOLOCK)
  LEFT OUTER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  WHERE rax.ReferenceNumber = Reservations.ReferenceNumber 
  AND rax.GuestNumber = Reservations.GuestNumber
  AND ((@AAddressTypeCSV = '') OR (ra.AddressType IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AAddressTypeCSV))))
) AS CustomerNumber,
( 
  SELECT TOP 1 ra.ContactPersonCustomerNumber FROM ReservationAddress ra WITH (NOLOCK)
  LEFT OUTER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  WHERE rax.ReferenceNumber = Reservations.ReferenceNumber 
  AND rax.GuestNumber = Reservations.GuestNumber
  AND ((@AAddressTypeCSV = '') OR (ra.AddressType IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AAddressTypeCSV))))
) AS ContactCustomerNumber
, ra.Link, Rooms.CustomerNumber, ReservationRoomLine.LineNumber
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))
  AND (@AUserIdNumberCSV = '' OR ReservationMain.StaffId IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AUserIdNumberCSV)))
LEFT OUTER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = ReservationMain.StaffId
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 
WHERE (1=1) 
AND ra.AddressType = 1
AND (@ACampaignCodeCSV = '' OR Reservations.FK_CampaignCode IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACampaignCodeCSV)))
AND 
(
  (@APerArrivalDate = 1 AND
    (
      (
        Reservations.GuestNumber = 0 
        AND 
        (
          (
            (ReservationRoomLine.DepartureDate>=@AFromDate AND ReservationRoomLine.ArrivalDate <= @AToDate) 
            OR (ReservationRoomLine.DepartureDate = ReservationRoomLine.ArrivalDate AND ReservationRoomLine.DepartureDate>=@AFromDate AND ReservationRoomLine.ArrivalDate <= @AToDate)
          )
        )
      ) 
      OR
      (
        Reservations.GuestNumber > 0 
        AND 
        (
          (
            (Reservations.DepartureDate>=@AFromDate AND Reservations.ArrivalDate <= @AToDate) 
            OR (Reservations.DepartureDate = Reservations.ArrivalDate AND Reservations.DepartureDate>=@AFromDate AND Reservations.ArrivalDate <= @AToDate)
          )
        )
      ) 
    )
  )
  OR (@APerArrivalDate = 0 AND Reservations.ReservationCreateDate <= @AToDate AND Reservations.ReservationCreateDate >= @AFromDate)
)

AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV))
AND Reservations.Referencenumber > 0
AND dbo.GetTotalNumberOfRooms(Reservations.ReferenceNumber) BETWEEN @AMinRooms AND @AMaxRooms

UNION

SELECT DISTINCT ReservationMain.StaffId AS UserIdNumber, Personel.UserIdCode, ReservationMain.ReferenceNumber, ReservationConferenceLine.ReservationConfLineNumber,

ReservationConferenceLine.ProductNumber AS ProductNumber,

CASE WHEN @APerArrivalDate = 1 THEN 
  DATEPART(m, ReservationConferenceLine.ArrivalDate) 
ELSE
  DATEPART(m, Reservations.ReservationCreateDate) 
END AS MonthIdx,
Reservations.PurposeCode, Reservations.PointOfSale, Reservations.MarketSegment, Reservations.GuestType, ReservationMain.ReservationType,
Reservations.Status, 

0 AS TurnoverRoom, 
(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) = 0 AND ISNULL(Products.FixedPrice, 0) = 0 THEN ReservationConferenceLine.TotalPrice ELSE 0 END*@VATFactor) AS TurnoverDayMeeting,
(CASE WHEN ISNULL(ReservationConferenceLine.TableBooking, 0) = 1 THEN ReservationConferenceLine.TotalPrice ELSE 0 END*@VATFactor) AS TurnoverTable,
0 AS TurnoverExtra,
Reservations.GuestNumber,
( 
  SELECT TOP 1 ra.Link FROM ReservationAddress ra WITH (NOLOCK)
  LEFT OUTER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  WHERE rax.ReferenceNumber = Reservations.ReferenceNumber 
  AND rax.GuestNumber = Reservations.GuestNumber
  AND ((@AAddressTypeCSV = '') OR (ra.AddressType IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AAddressTypeCSV))))
) AS CustomerNumber,
( 
  SELECT TOP 1 ra.ContactPersonCustomerNumber FROM ReservationAddress ra WITH (NOLOCK)
  LEFT OUTER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  WHERE rax.ReferenceNumber = Reservations.ReferenceNumber 
  AND rax.GuestNumber = Reservations.GuestNumber
  AND ((@AAddressTypeCSV = '') OR (ra.AddressType IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AAddressTypeCSV))))
) AS ContactCustomerNumber
, NULL, NULL, ReservationConferenceLine.ReservationConfLineNumber
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))
  AND (@AUserIdNumberCSV = '' OR ReservationMain.StaffId IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AUserIdNumberCSV)))
LEFT OUTER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = ReservationMain.StaffId
WHERE (1=1) 
AND (@ACampaignCodeCSV = '' OR Reservations.FK_CampaignCode IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACampaignCodeCSV)))
AND 
(
  (@APerArrivalDate = 1 AND 
  (
  ReservationConferenceLine.ReservationConfLineNumber > 0 AND (ReservationConferenceLine.DepartureDate>=@AFromDate AND ReservationConferenceLine.ArrivalDate <= @AToDate) 
  OR (ReservationConferenceLine.DepartureDate = ReservationConferenceLine.ArrivalDate AND ReservationConferenceLine.DepartureDate>=@AFromDate AND ReservationConferenceLine.ArrivalDate <= @AToDate)
  )
  OR (@APerArrivalDate = 0 AND Reservations.ReservationCreateDate <= @AToDate AND Reservations.ReservationCreateDate >= @AFromDate)
  )
)

AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV))
AND Reservations.Referencenumber > 0
AND dbo.GetTotalNumberOfRooms(Reservations.ReferenceNumber) BETWEEN @AMinRooms AND @AMaxRooms

UNION

SELECT DISTINCT ReservationMain.StaffId AS UserIdNumber, Personel.UserIdCode, ReservationMain.ReferenceNumber, 0, 

-1 AS ProductNumber,

CASE WHEN @APerArrivalDate = 1 THEN 
  DATEPART(m, Reservations.ArrivalDate)
ELSE
  DATEPART(m, Reservations.ReservationCreateDate) 
END AS MonthIdx,
Reservations.PurposeCode, Reservations.PointOfSale, Reservations.MarketSegment, Reservations.GuestType, ReservationMain.ReservationType,
Reservations.Status, 

0 AS TurnoverRoom, 
0 AS TurnoverHall,
0 AS TurnoverDayMeeting,
0 AS TurnoverTable,
((SELECT DBO.GetArrangementValue(Reservations.ReferenceNumber, Reservations.GuestNumber, @AFromDate, @AToDate))*@VATFactor) AS TurnoverExtra,
Reservations.GuestNumber,
( 
  SELECT TOP 1 ra.Link FROM ReservationAddress ra WITH (NOLOCK)
  LEFT OUTER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  WHERE rax.ReferenceNumber = Reservations.ReferenceNumber 
  AND rax.GuestNumber = Reservations.GuestNumber
  AND ((@AAddressTypeCSV = '') OR (ra.AddressType IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AAddressTypeCSV))))
) AS CustomerNumber,
( 
  SELECT TOP 1 ra.ContactPersonCustomerNumber FROM ReservationAddress ra WITH (NOLOCK)
  LEFT OUTER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
  WHERE rax.ReferenceNumber = Reservations.ReferenceNumber 
  AND rax.GuestNumber = Reservations.GuestNumber
  AND ((@AAddressTypeCSV = '') OR (ra.AddressType IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AAddressTypeCSV))))
) AS ContactCustomerNumber
, NULL, NULL, NULL
FROM Reservations WITH (NOLOCK)
INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber 
INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXTab.ArrangementLineNumber
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))
  AND (@AUserIdNumberCSV = '' OR ReservationMain.StaffId IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AUserIdNumberCSV)))
LEFT OUTER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = ReservationMain.StaffId
WHERE (1=1)
AND (@ACampaignCodeCSV = '' OR Reservations.FK_CampaignCode IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACampaignCodeCSV)))
AND 
(
  (@APerArrivalDate = 1 AND ArrangementLines.Date <= @AToDate AND ArrangementLines.Date >= @AFromDate) 
  OR (@APerArrivalDate = 0 AND Reservations.ReservationCreateDate <= @AToDate AND Reservations.ReservationCreateDate >= @AFromDate)
)

AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV))
AND Reservations.Referencenumber > 0
AND dbo.GetTotalNumberOfRooms(Reservations.ReferenceNumber) BETWEEN @AMinRooms AND @AMaxRooms