SET NOCOUNT ON
-- 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 @DepartmentStr VARCHAR(50)
--DECLARE @DepartmentNumber VARCHAR(1)
DECLARE @FK_DepartmentNumber INT
DECLARE @UserIdCode VARCHAR(20)

IF @CentralBookingType = 7
BEGIN
  DECLARE db_cur CURSOR FOR 
    SELECT LastName, HotelNumber 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
  
  WHILE @@fetch_status = 0
  BEGIN
    SET @LastName = REPLACE('Setup_' + @LastName, ' ', '')

    IF NOT EXISTS (SELECT UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserIdCode = @LastName) 
    BEGIN
      --SET @DepartmentStr = ''

      EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIdNumber OUTPUT

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

      INSERT INTO Personel (UserIdNumber, UserIdCode, FirstName, LastName, Passwd, /*DepartmentNumbers,*/ UserTypeNumber, PasswordExpireAfter, EnableTimeRegistration, WorkDayStart, WorkDayEnd, ActivePerson, UserGroup) VALUES 
      (@UserIdNumber, @UserIdCode, @LastName, @LastName, 'hotel', /*@DepartmentStr,*/ 7, 0, 0, '00:00', '00:00', 1, 1) -- UserTypeNumber 7 means utHotel

      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
--        SET @DepartmentStr = @DepartmentStr + @DepartmentNumber
        INSERT INTO PersonelDepartmentsXtab (UserIDNumber, FK_Department) VALUES (@UserIdNumber, @FK_DepartmentNumber)  
        FETCH NEXT FROM db_department INTO @FK_DepartmentNumber
      END

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

  DEALLOCATE db_cur
END
ELSE
BEGIN
  SELECT @LastName = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'HotelName' 
  IF @LastName <> ''
  BEGIN
    SET @LastName = REPLACE('Setup_' + @LastName, ' ', '')

    IF NOT EXISTS (SELECT UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserIdCode = @LastName) 
    BEGIN
--      SET @DepartmentStr = ''

      EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIdNumber OUTPUT

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

      INSERT INTO Personel (UserIdNumber, UserIdCode, FirstName, LastName, Passwd, /*DepartmentNumbers,*/ UserTypeNumber, PasswordExpireAfter, EnableTimeRegistration, WorkDayStart, WorkDayEnd, ActivePerson, UserGroup) VALUES 
      (@UserIdNumber, @UserIdCode, @LastName, @LastName, 'hotel', /*@DepartmentStr,*/ 7, 0, 0, '00:00', '00:00', 1, 1) -- UserTypeNumber 7 means utHotel

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

      FETCH NEXT FROM db_department INTO @FK_DepartmentNumber

      WHILE @@fetch_status = 0
      BEGIN
--        SET @DepartmentStr = @DepartmentStr + @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
END

UPDATE Personel SET UserGroup = 3 WHERE ISNULL(UserGroup,0) IN (0,4)
GO