CREATE PROCEDURE dbo.SetCoursePassedForCustomers AS
BEGIN
  --20170410/RN: Initial version

  SET NOCOUNT ON  

  DECLARE @SystemDate datetime
  DECLARE @ReferenceNumber int
  DECLARE @GuestNumber  int
  DECLARE @ProductNumber int
  DECLARE @CustomerNumber int
  DECLARE @HotelNumber int
  DECLARE @HotelCustomerNumber int
  DECLARE @InternalCourseProduction bit
  DECLARE @EventsNightAuditAlwaysSetPassed bit


  SELECT TOP 1 @SystemDate = SYSTEMDATE FROM System WITH (NOLOCK)
  DECLARE tmpCur CURSOR FOR
  SELECT Reservations.ReferenceNumber, Reservations.GuestNumber, RESERVATIONCONFERENCELINE.ProductNumber, RESERVATIONADDRESS.LINK
  FROM Reservations WITH (NOLOCK)
  INNER JOIN RESERVATIONCONFERENCELINE WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ReferenceNumber = Reservations.ReferenceNumber AND RESERVATIONCONFERENCELINE.GuestNo = 0
  INNER JOIN CONFERENCEROOMTYPES WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.BookedConferenceRoomTypeNumber = CONFERENCEROOMTYPES.ConferenceRoomTypeNumber AND CONFERENCEROOMTYPES.ResourceType = 16
  INNER JOIN ReservationReservationXtab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationReservationXtab.FK_ReferenceNumberSub
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.REFERENCENUMBER = RESERVATIONS.ReferenceNumber AND RESERVATIONADDRESSXTAB.GUESTNUMBER = RESERVATIONS.GuestNumber
    AND RESERVATIONADDRESSXTAB.GUESTNUMBER >= 1
    AND RESERVATIONADDRESSXTAB.AddressTypeNumber = 1
  INNER JOIN RESERVATIONADDRESS WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER AND ISNULL(RESERVATIONADDRESS.LINK, 0) > 0
  WHERE (1=1)
    AND Reservations.DepartureDate = @SystemDate
    AND Reservations.Status <= 6
  OPEN tmpCur
  FETCH NEXT FROM tmpCur INTO @ReferenceNumber, @GuestNumber, @ProductNumber, @CustomerNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SELECT @HotelNumber = dbo.GetReservationHotelNumber(@ReferenceNumber, @GuestNumber, -1)

    SELECT @HotelCustomerNumber = CustomerNumber
    FROM Customer WITH (NOLOCK) 
    WHERE HotelNumber = @HotelNumber
      AND LocalHotel=1 
      AND CustomerTypeNumber = 7
      AND ISNULL(MarkAsDeleted, 0) = 0

    SET @InternalCourseProduction = 0
	SET @EventsNightAuditAlwaysSetPassed = 0
    SELECT @InternalCourseProduction = dbo.GetHotelSetupKeyValue('InternalCourseProduction', @HotelCustomerNumber)
    SELECT @EventsNightAuditAlwaysSetPassed = dbo.GetHotelSetupKeyValue('EventsNightAuditAlwaysSetPassed', @HotelCustomerNumber)
    
	IF (@InternalCourseProduction = 1) AND (@EventsNightAuditAlwaysSetPassed = 1)
	BEGIN
      UPDATE CustomerProductPassed
      SET 
        IsProductPassed = 1,
	    IsProductPassedDate = @SystemDate
      WHERE FK_CustomerNumber = @CustomerNumber
        AND FK_ProductNumber = @ProductNumber

      IF @@ROWCOUNT = 0 -- Nothing was updated, so we need to insert
        INSERT INTO CustomerProductPassed (FK_CustomerNumber, FK_ProductNumber, IsProductPassed, IsProductPassedDate) VALUES (@CustomerNumber, @ProductNumber, 1, @SystemDate)
	END
    FETCH NEXT FROM tmpCur INTO @ReferenceNumber, @GuestNumber, @ProductNumber, @CustomerNumber
  END
  CLOSE tmpCur
  DEALLOCATE tmpCur
END
