CREATE PROCEDURE dbo.HotelCreated AS
/* 
  Created January 2017 by Villy Damsbo Structure taken from ..\DBUpdate\Post\AddMissingPersonelDepartmentRecords.sql 
  Changes:
  KJ 20220413: Added AutoService. Hotel relation missing.

*/

DECLARE @UserIdNumber INTEGER
DECLARE @HotelCustomerNumber INTEGER

DECLARE db_cur_per CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
  SELECT UserIdNumber
  FROM Personel WITH (NOLOCK)
  WHERE Personel.UserTypeNumber IN (2, 3, 4, 5, 6, 8, 9, 10) 
  -- ~ UserType IN (Techotel, AutoDay, utAutoNight, utPicassoOnline, utPierre, utChannelBooking, utPicassoOnlineOther, utAutoService)
OPEN db_cur_per

FETCH NEXT FROM db_cur_per INTO @UserIdNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE db_cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT CustomerNumber
    FROM Customer (NOLOCK)
    WHERE CustomerTypeNumber = 7 AND Customer.HotelNumber >= 0
  OPEN db_cur
  FETCH NEXT FROM db_cur INTO @HotelCustomerNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF NOT EXISTS
    (
      SELECT * 
      FROM PersonelHotelXTab WITH (NOLOCK) 
      WHERE FK_HotelCustomerNumber = @HotelCustomerNumber AND FK_UserIdNumber = @UserIdNumber
    )
    BEGIN
	  -- Print('Value for hotel ' + CONVERT(VARCHAR, @HotelCustomerNumber) + ' inserted for user ' + CONVERT(VARCHAR, @UserIdNumber))
      INSERT INTO PersonelHotelXTab (FK_UserIdNumber, FK_HotelCustomerNumber) VALUES (@UserIdNumber, @HotelCustomerNumber)
    END
	FETCH NEXT FROM db_cur INTO @HotelCustomerNumber
  END

  CLOSE db_cur
  DEALLOCATE db_cur

  FETCH NEXT FROM db_cur_per INTO @UserIdNumber
END

CLOSE db_cur_per
DEALLOCATE db_cur_per