CREATE PROCEDURE DBO.UISettingNewPersonel AS
SET NOCOUNT ON

/*
  Changes:
    KJ 20171212: Bug: Fix for "New user is not related to any hotel". Case #CR138040
    KJ 20110923: Ext: Added handle for new departments since setup user was created. 
    KJ 20080403: Bug Fix for "primary key violation"
    TL 20071024 Fixed violation of foreign key constraint
*/

DECLARE @CentralBookingType INT

SELECT @CentralBookingType = CONVERT(INTEGER, ISNULL(KeyValue, 0)) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE '%CentralBookingType%'

DECLARE @LastName VARCHAR(50)
DECLARE @HotelNumber INT
DECLARE @UserIdNumber INT
DECLARE @FK_DepartmentNumber INT
DECLARE @UserIdCode VARCHAR(20)
DECLARE @HotelCustomerNumber INT

DECLARE db_cur CURSOR FOR 
  SELECT LastName, HotelNumber, CustomerNumber FROM Customer WITH (NOLOCK) WHERE CustomerTypeNumber = 7 AND ISNULL(HotelNumber, -1) >= 0 AND MarkAsDeleted = 0 ORDER BY HotelNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @LastName, @HotelNumber, @HotelCustomerNumber
  
WHILE @@fetch_status = 0
BEGIN
  SET @LastName = REPLACE('Setup_' + @LastName, ' ', '')

  IF NOT EXISTS (SELECT UserIdNumber FROM Personel WITH (NOLOCK) WHERE LastName = @LastName) 
  BEGIN
    EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIdNumber OUTPUT

    SET @UserIdCode = CONVERT(VARCHAR(20), @LastName)

    INSERT INTO Personel (UserIdNumber, UserIdCode, FirstName, LastName, PasswordHash, UserTypeNumber, PasswordExpireAfter, EnableTimeRegistration, WorkDayStart, WorkDayEnd, ActivePerson, UserGroup) VALUES 
    (@UserIdNumber, @UserIdCode, @LastName, @LastName, dbo.GetPasswordHash('hotel'), 7, 0, 0, '00:00', '00:00', 1, 1) -- UserTypeNumber 7 means utHotel

	INSERT INTO PersonelHotelXTab VALUES (@UserIdNumber, @HotelCustomerNumber)

    DECLARE db_department CURSOR FOR
      SELECT PK_Department FROM Departments WITH (NOLOCK) WHERE HotelNumber = @HotelNumber AND ISNULL(MarkAsDeleted, 0) = 0
    OPEN db_department

    FETCH NEXT FROM db_department INTO @FK_DepartmentNumber

    WHILE @@fetch_status = 0
    BEGIN
      INSERT INTO PersonelDepartmentsXtab (UserIDNumber, FK_Department) VALUES (@UserIdNumber, @FK_DepartmentNumber)  
      FETCH NEXT FROM db_department INTO @FK_DepartmentNumber
    END

    DEALLOCATE db_department
  END
  ELSE
  BEGIN
    -- personel exists check if personeldepartmentsxtab exists for all departments
    SELECT @UserIdNumber = UserIdNumber FROM Personel WITH (NOLOCK) WHERE LastName = @LastName

    IF ISNULL(@UserIdNumber, 0) > -1
    BEGIN
      IF NOT EXISTS (SELECT * FROM PersonelHotelXTab WITH (NOLOCK) WHERE FK_UserIdNumber = @UserIdNumber AND FK_HotelCustomerNumber = @HotelCustomerNumber)
        INSERT INTO PersonelHotelXTab VALUES (@UserIdNumber, @HotelCustomerNumber)

      DECLARE db_department CURSOR FOR
        SELECT PK_Department FROM Departments WITH (NOLOCK) WHERE HotelNumber = @HotelNumber AND ISNULL(MarkAsDeleted, 0) = 0
      OPEN db_department

      FETCH NEXT FROM db_department INTO @FK_DepartmentNumber

      WHILE @@fetch_status = 0
      BEGIN
        IF NOT EXISTS (SELECT * FROM PersonelDepartmentsXtab WITH (NOLOCK) WHERE UserIDNumber = @UserIdNumber AND FK_Department = @FK_DepartmentNumber) 
          INSERT INTO PersonelDepartmentsXtab (UserIDNumber, FK_Department) VALUES (@UserIdNumber, @FK_DepartmentNumber)  
        
        FETCH NEXT FROM db_department INTO @FK_DepartmentNumber
      END

      DEALLOCATE db_department
    END
  END
  FETCH NEXT FROM db_cur INTO @LastName, @HotelNumber, @HotelCustomerNumber
END

DEALLOCATE db_cur