CREATE PROCEDURE DBO.OwnerReportReservationsByTransactionsSimple @AStartDateTime DateTime, @AEndDateTime DateTime, @AReservationStatusStr VARCHAR(25), @ARoomNumberStr VARCHAR(2500), @AHotelNumber INT = -1, @AUserIdNumber INT = 0, @AAddToFolio BIT = 0 AS
SET NOCOUNT ON

/*

  Created by Kent Johansen Februar 2008
  Returns: Reservations/transactions for selected period with Owner part.

  TODO:

  Changes:
    KJ 20111018: Ext: Added Owner discount Req #1008.
    KJ 20090401: Use spec when guest > 0

*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

IF @ShowPrint = 1
  PRINT 'OwnerReportReservationsByTransactionsSimple'

DECLARE @HotelSetupOwnerPercent INT
SELECT @HotelSetupOwnerPercent = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'LoginSplit'

DECLARE @ReferenceNumber INT
DECLARE @GuestName VARCHAR(100)
DECLARE @FirstName VARCHAR(100)
DECLARE @LastName VARCHAR(100)
DECLARE @GuestNumber INT
DECLARE @HotelNumber INT
DECLARE @RoomNumber INT
DECLARE @ArrivalDate DATETIME 
DECLARE @DepartureDate DATETIME  
DECLARE @Adults INT
DECLARE @Children1 INT
DECLARE @Children2 INT
DECLARE @TotalPrice MONEY
DECLARE @SpecArrivalDate DATETIME 
DECLARE @SpecDepartureDate DATETIME
DECLARE @SpecAdults INT
DECLARE @SpecChildren1 INT
DECLARE @SpecChildren2 INT
DECLARE @SpecTotalPrice MONEY
DECLARE @osPercent INTEGER
DECLARE @Status INT
DECLARE @TotalWithVat MONEY

DECLARE @CleaningPrice MONEY
DECLARE @ResultPackagePrice MONEY
DECLARE @NettoPrice MONEY

DECLARE @Pax INT

DECLARE @OwnerDiscountRate SMALLMONEY
DECLARE @DiscoutTxt VARCHAR(20)

IF ISNULL(@ARoomNumberStr, '') = ''
  SET @ARoomNumberStr = ''

CREATE TABLE #Reservations (
  Referencenumber INT, 
  GuestName VARCHAR(100),
  GuestNumber INT, 
  HotelNumber INT NULL,
  ReservationArrival DATETIME, 
  ReservationDeparture DATETIME, 
  Pax INT NULL,
  Adults INT NULL, 
  Children1 INT NULL,
  Children2 INT NULL, 
  TotalPrice MONEY NULL,
  CleaningPrice MONEY NULL, 
  PackagePrice MONEY NULL,
  OwnerPercent INT NULL,
  NettoPrice MONEY NULL,
  RoomNumber INT NULL,
  ReservationStatus INT NULL
)
	
DECLARE db_cur CURSOR FOR
  SELECT DISTINCT 
    Folios.ReferenceNumber, 
    Reservations.GuestNumber,
--    ReservationMain.HotelNumber,
    Rooms.HotelNumber,
    Rooms.Number,
    ReservationRoomLine.ArrivalDate, 
    Reservations.ArrivalDate,
    ReservationRoomLine.DepartureDate, 
    Reservations.DepartureDate,
    ReservationRoomLine.Adults, 
    Reservations.Adults,
    ReservationRoomLine.Children1, 
    Reservations.Children1,
    ReservationRoomLine.Children2, 
    Reservations.Children2,
    ReservationRoomLine.TotalPrice, 
    Reservations.TotalPrice,
    Rooms.OwnerSettlementPercent,
    Reservations.Status,
    SUM(Transactions.PriceOverwrite * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END))
  FROM Transactions WITH (NOLOCK)
  INNER JOIN Ware WITH (NOLOCK) ON Transactions.WareNumber = Ware.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber 
    AND WareGroups.Payment in (2,3,7,8)
  INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  INNER JOIN Rooms WITH (NOLOCK) ON Convert(Int,Transactions.Note) = Rooms.Number 
    AND ISNULL(OwnerSettlementType, 0) > 0  
    AND ((@ARoomNumberStr = '') OR (Rooms.Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberStr)))) 
    AND ((@AHotelNumber = -1) OR (Rooms.HotelNumber = @AHotelNumber))
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = Rooms.CustomerNumber
  INNER JOIN Folios WITH (NOLOCK) ON Transactions.Folionumber = Folios.FolioNumber 
  RIGHT OUTER JOIN Reservations WITH (NOLOCK) on Reservations.ReferenceNumber = Folios.ReferenceNumber 
    AND Reservations.ReferenceNumber > 0
    AND Reservations.GuestNumber = Transactions.GuestNumber  
    AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusStr)) 
  LEFT OUTER JOIN ReservationRoomLineXTab WITH (NOLOCK) on Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber 
    AND Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber
  LEFT OUTER JOIN RESERVATIONROOMLINE WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
  WHERE Transactions.MovedDeleted = 0
  AND Transactions.ShowOnBillFlag = 1 
  AND Transactions.Payment = 0
  AND Transactions.OriginalDate >= CONVERT(VARCHAR(8), @AStartDateTime, 112)  
  AND Transactions.OriginalDate <= CONVERT(VARCHAR(8), @AEndDateTime, 112)
  GROUP BY 
    Folios.ReferenceNumber, 
    Reservations.GuestNumber,
--    ReservationMain.HotelNumber,
    Rooms.HotelNumber,
    Rooms.Number,
    ReservationRoomLine.ArrivalDate, 
    Reservations.ArrivalDate,
    ReservationRoomLine.DepartureDate, 
    Reservations.DepartureDate,
    ReservationRoomLine.Adults, 
    Reservations.Adults,
    ReservationRoomLine.Children1, 
    Reservations.Children1,
    ReservationRoomLine.Children2, 
    Reservations.Children2,
    ReservationRoomLine.TotalPrice, 
    Reservations.TotalPrice,
    Rooms.OwnerSettlementPercent,
    Reservations.Status
  HAVING  SUM(Transactions.PriceOverwrite * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) > 0
  ORDER BY Rooms.Number
OPEN db_cur
	
FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestNumber, @HotelNumber, @RoomNumber, @ArrivalDate, @SpecArrivalDate, @DepartureDate, @SpecDepartureDate, 
  @Adults, @SpecAdults, @Children1, @SpecChildren1, @Children2, @SpecChildren2, @TotalPrice, @SpecTotalPrice, @osPercent, @Status, @TotalWithVat
	
SET @CleaningPrice = 0
SET @ResultPackagePrice = 0
SET @NettoPrice = 0

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @ShowPrint = 1
    PRINT '--------------------------'
	
  IF @GuestNumber > 0
  BEGIN
    IF @ShowPrint = 1
      PRINT 'Use SPEC'
	
    SET @ArrivalDate = @SpecArrivalDate
    SET @DepartureDate = @SpecDepartureDate
    SET @Adults = @SpecAdults
    SET @Children1 = @SpecChildren1
    SET @Children2 = @SpecChildren2
    SET @TotalPrice = @SpecTotalPrice
  END
	
  SET @Adults = ISNULL(@Adults, -1)
  SET @Children1 = ISNULL(@Children1, -1)
  SET @Children2 = ISNULL(@Children2, -1)
  SET @HotelNumber = ISNULL(@HotelNumber, -1)

  IF @ShowPrint = 1
  BEGIN
    PRINT '@TotalWithVat'
    PRINT CONVERT(VARCHAR(10), @TotalWithVat)
  END

  SET @CleaningPrice = 0
  SET @ResultPackagePrice = @TotalPrice - @TotalWithVat

  IF ISNULL(@ResultPackagePrice, 0) = 0
    SET @ResultPackagePrice = 0
  IF ISNULL(@TotalPrice, 0) = 0
    SET @TotalPrice = 0

  IF ISNULL(@CleaningPrice, 0) = 0
    SET @CleaningPrice = 0
  IF ISNULL(@osPercent, 0) = 0
    SET @osPercent = @HotelSetupOwnerPercent

  SET @TotalPrice = @TotalWithVat
  SET @NettoPrice = (@TotalPrice * @osPercent / 100)

  SELECT TOP 1 @OwnerDiscountRate = raOwnerDisc.OwnerDiscountRate 
  FROM ReservationAddressXTab raxOwnerDisc WITH (NOLOCK)
  INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber = @ReferenceNumber
    AND R.GuestNumber = @GuestNumber
  INNER JOIN ReservationAddress raOwnerDisc WITH (NOLOCK) ON raxOwnerDisc.ReferenceNumber = R.ReferenceNumber
    AND raxOwnerDisc.GuestNumber = R.GuestNumber
    AND raOwnerDisc.AddressNumber = raxOwnerDisc.AddressNumber
    AND raOwnerDisc.AddressType = R.DiscountCustomerType

    SET @DiscoutTxt = ''
    IF @NettoPrice > 0 AND @OwnerDiscountRate > 0
    BEGIN
      SET @DiscoutTxt = CAST(@OwnerDiscountRate AS VARCHAR(2)) + '% Rabat'
      SET @NettoPrice = @NettoPrice - ((@NettoPrice * @OwnerDiscountRate) / 100)
    END

/*
  IF @DiscoutTxt <> ''
    SET @OwnerWareText = @OwnerWareText + ' ' + @DiscoutTxt       
*/    
   -- get the name and address
  SELECT @FirstName = ra.FirstName, @LastName = ra.LastName 
  FROM ReservationAddressXTab rax WITH (NOLOCK)
  INNER JOIN ReservationAddress ra WITH (NOLOCK) ON rax.AddressNumber = ra.AddressNumber AND rax.ReferenceNumber = @ReferenceNumber AND rax.GuestNumber = @GuestNumber AND AddressType = 1

  IF @FirstName = ''
    SET @GuestName = @LastName
  ELSE
    SET @GuestName = @FirstName + ' ' + @LastName
   
  INSERT INTO #Reservations (Referencenumber, GuestName, GuestNumber, HotelNumber, ReservationArrival, ReservationDeparture, Pax, Adults, Children1, Children2, TotalPrice, CleaningPrice, PackagePrice, OwnerPercent, NettoPrice, RoomNumber, ReservationStatus)
  VALUES (@ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @ArrivalDate, @DepartureDate, @Pax, @Adults, @Children1, @Children2, @TotalPrice, @CleaningPrice, @ResultPackagePrice, @osPercent, @NettoPrice, @RoomNumber, @Status)

  FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestNumber, @HotelNumber, @RoomNumber, @ArrivalDate, @SpecArrivalDate, @DepartureDate, @SpecDepartureDate, 
    @Adults, @SpecAdults, @Children1, @SpecChildren1, @Children2, @SpecChildren2, @TotalPrice, @SpecTotalPrice, @osPercent, @Status, @TotalWithVat
END

CLOSE db_cur
DEALLOCATE db_cur

SELECT * FROM #Reservations WITH (NOLOCK)
ORDER BY RoomNumber, ReservationArrival

DROP TABLE #Reservations