IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.ReservationRoomLineDobbelBookingPrevention') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER dbo.ReservationRoomLineDobbelBookingPrevention 
GO
CREATE TRIGGER [ReservationRoomLineDobbelBookingPrevention] ON dbo.RESERVATIONROOMLINE FOR INSERT, UPDATE AS
/*
    Created by Jacob Pedersen 20070801

    Description: Raises an error if another roomline conflicts in the room in the period

    Changes:
    20110413 BA: BUG: CR023237/AutoProlongStay NightAudit Plugin calls FixReservationDates Stord Procedure
                      which fails if you have a room that is both OOO and OOS.
    20110131 BA: BUG: CR019218/When hsDoubleBookingEnabled = True then skip this Trigger!
    20090227 BA: BUG: CR006959/UpdateNoShow.plg can not append to the nameliste if a conflicting cancelled or noshow reservation exists.
    20071004 JP: BUG: OutForService does not generate double bookings like OutOfOrder does
    20071001 JP: BUG: do not care about outofhouse status (only if we do not have cashier enabled which almost all have - enough for proper test)
    20070928 JP: EXT: added more logging
    20070820 JP: BUG: Do not check in the past 
*/

IF UPDATE(RoomNumber) OR UPDATE(ArrivalDate) OR UPDATE(ArrivalTime) OR UPDATE(DepartureDate) OR UPDATE(DepartureTime) -- no need to do anything if roomnumber or period has not been affected 
BEGIN
  DECLARE
  @LineNumber INT,
  @RoomNumber INT,
  @Arrival DATETIME,
  @Departure DATETIME,
  @ExistingStatus INT,
  @DoubleBookingEnabled INT, -- CR0192018
  @ExistingReservationNumber INT, -- 20110413/CR023237
  @UseDefaultBookTimeForRooms BIT

  -- CR0192018
  SELECT @DoubleBookingEnabled = KeyValue FROM HotelSetup WHERE SEARCHKEY = 'EnableDoubleBooking'
  IF ISNULL(@DoubleBookingEnabled, 0) = 1 
    RETURN

  SELECT @LineNumber = LineNumber, @RoomNumber = RoomNumber,  @ExistingStatus = StatusRL, @Arrival = CONVERT(DATETIME, ArrivalDate, 112)+CONVERT(DATETIME, ArrivalTime, 108), @Departure = CONVERT(DATETIME, DepartureDate, 112)+CONVERT(DATETIME, DepartureTime, 108) FROM Inserted

  

  -- BA/090227: CR006959/UpdateNoShow.plg cannot append Grid lines to the namelist if there exists
  --            a cancelled or Noshow reservation that overlap an other reservation. 
  SELECT @ExistingReservationNumber = R.ReferenceNumber
  FROM ReservationRoomLineXTAB RLX (NOLOCK)
  INNER JOIN Reservations R (NOLOCK)
    ON RLX.ReferenceNumber = R.ReferenceNumber AND RLX.GuestNumber = R.GuestNumber AND RLX.LineNumber = @LineNumber

  IF @ExistingStatus < 7 /*Seven and above is Cancelled and NoShow*/ AND
     @ExistingReservationNumber > 0 -- 20110413/CR023237: Do not conflict with other OOO's and OOS's
  BEGIN
    DECLARE @SystemDateTime DATETIME
    SELECT @SystemDateTime = CONVERT(DATETIME, SystemDate, 112) + convert(datetime, convert(varchar, getdate(), 108)) FROM dbo.System(NOLOCK) WHERE Number=1
    IF @Arrival < @SystemDateTime
     SET @Arrival = @SystemDateTime

    IF @RoomNumber > 0 -- we can only have a double booking if we have a room
    BEGIN
      DECLARE
      @ConflictLineNumber INT,
      @ConflictArrival DATETIME,
      @ConflictDeparture DATETIME,
	  @DefaultArrivalTime VARCHAR(5),
	  @DefaultDepartureTime VARCHAR(5)

	  SELECT  @DefaultArrivalTime = ISNULL(KeyValue, '0') FROM HotelSETUP WHERE SearchKey = 'TimeOfArrivalForRes'
	  SELECT @DefaultDepartureTime = ISNULL(KeyValue, '0') FROM HotelSETUP WHERE SearchKey = 'TimeOfDepartureForRes'

	  SELECT @UseDefaultBookTimeForRooms = 1 FROM reservationroomline rl
	  LEFT OUTER JOIN ProductExtended pe ON rl.ProductNumber = pe.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended pe2 ON rl.ProductNumber2 = pe2.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended pe3 ON rl.ProductNumber3 = pe3.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended pe4 ON rl.ProductNumber4 = pe4.FK_ProductNumber
	  WHERE rl.LINENUMBER = @LineNumber AND (ISNULL(pe.UseDefaultBookTimeForRooms,0) = 1 OR ISNULL(pe2.UseDefaultBookTimeForRooms,0) = 1 OR  ISNULL(pe3.UseDefaultBookTimeForRooms,0) = 1 OR ISNULL(pe4.UseDefaultBookTimeForRooms,0) = 1)

      SELECT @ConflictLineNumber = RL.LineNumber --, @RoomNumber = RoomNumber  -- ??? roomnumber is the same, no need to reselect
      , @ConflictArrival = CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108)
      , @ConflictDeparture = CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108) 
      FROM dbo.ReservationRoomLine RL (NOLOCK) 
	  LEFT OUTER JOIN ProductExtended PE ON RL.ProductNumber = PE.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended PE2 ON RL.ProductNumber2 = PE2.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended PE3 ON RL.ProductNumber3 = PE3.FK_ProductNumber
	  LEFT OUTER JOIN ProductExtended PE4 ON RL.ProductNumber4 = PE4.FK_ProductNumber
	  INNER JOIN dbo.ReservationRoomLineXTab RLX (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RLX.ReferenceNumber <> -2 -- -2 is outforservice
      AND RoomNumber = @RoomNumber 
      AND RL.LineNumber <> @LineNumber -- Cannot conflict with myself
	  AND CASE WHEN ISNULL(PE.UseDefaultBookTimeForRooms, 0) = 1 OR
				    ISNULL(PE2.UseDefaultBookTimeForRooms, 0) = 1 OR
				    ISNULL(PE3.UseDefaultBookTimeForRooms, 0) = 1 OR
				    ISNULL(PE4.UseDefaultBookTimeForRooms, 0) = 1  
			   THEN CONVERT(DATETIME,ArrivalDate,112)+CONVERT(DATETIME,@DefaultArrivalTime  ,108) 
		       ELSE CONVERT(DATETIME,ArrivalDate,112)+CONVERT(DATETIME,ArrivalTime,108) 
	      END<= CASE WHEN ISNULL(@UseDefaultBookTimeForRooms, 0) = 1 
	                 THEN DATEADD(dd, 0, DATEDIFF(dd, 0, @Departure)) + CONVERT(DATETIME, @DefaultDepartureTime  ,108) 
			         ELSE @Departure  
		        END 
      AND CASE WHEN ISNULL(PE.UseDefaultBookTimeForRooms, 0) = 1 
		       THEN CONVERT(DATETIME,DepartureDate,112)+CONVERT(DATETIME, @DefaultDepartureTime  ,108) 
		       ELSE CONVERT(DATETIME,DepartureDate,112)+CONVERT(DATETIME, DepartureTime,108) 
	       END>= CASE WHEN ISNULL(@UseDefaultBookTimeForRooms, 0) = 1 
			         THEN DATEADD(dd, 0, DATEDIFF(dd, 0, @Arrival)) + CONVERT(DATETIME, @DefaultArrivalTime  ,108) 
			         ELSE  @Arrival  
		         END
	  AND ((ArrivalDate = DepartureDate AND DepartureTime > ArrivalTime) OR (ArrivalDate < DepartureDate))
	  AND StatusRL < 6
--      INNER JOIN dbo.Reservations R (NOLOCK) ON RLX.ReferenceNumber=R.ReferenceNumber AND RLX.GuestNumber=R.GuestNumber AND ((Status<=5) OR ((Status = 6) 
--   AND (CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108) > @SystemDateTime)))
      INNER JOIN dbo.Reservations R (NOLOCK) ON RLX.ReferenceNumber=R.ReferenceNumber AND RLX.GuestNumber=R.GuestNumber AND (R.Status<6)

      IF @@rowcount > 0
      BEGIN
        DECLARE @ErrorStr VARCHAR(255)
        SET @ErrorStr = 'Double booking! Line=' + CONVERT(VARCHAR, @LineNumber) + ' Room=' + CONVERT(VARCHAR, @RoomNumber) + ' Arr.="' + CONVERT(VARCHAR, @Arrival) + '" Dep.="' + CONVERT(VARCHAR, @Departure) + '" Conflict with Line=' + CONVERT(VARCHAR, @ConflictLineNumber) + ' Arr.="' + CONVERT(VARCHAR, @ConflictArrival) + '" Dep.="' + CONVERT(VARCHAR, @ConflictDeparture) + '"'
        RAISERROR (@ErrorStr, 16, 1)
      END
    END
  END
END