/* 20220804/RN - Make sure existing staff signatures is copied to all hotels */

DECLARE @UserIDNumber int
DECLARE @FK_UserIDNumber int
DECLARE @FK_SignatureID int
DECLARE @FK_HotelCustomerNumber int
DECLARE @HotelCustomerNumber int
DECLARE @Count int

SELECT @Count = COUNT(*)
FROM Customer (NOLOCK)
WHERE Customer.CustomerTypeNumber = 7

IF @Count = 1
BEGIN
  SELECT @FK_HotelCustomerNumber = CustomerNumber FROM Customer (NOLOCK) WHERE Customer.CustomerTypeNumber = 7
  UPDATE PersonnelSignatureXtab
  SET FK_HotelCustomerNumber = @FK_HotelCustomerNumber
END
ELSE
BEGIN
  DECLARE StaffIDCursor CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT UserIDNumber FROM Personel (NOLOCK)
    ORDER BY UserIDNumber ASC

  OPEN StaffIDCursor
  FETCH NEXT FROM StaffIDCursor INTO @UserIDNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    DECLARE PSigXtabCursor CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT FK_UserIDNumber, FK_SignatureID, ISNULL(FK_HotelCustomerNumber, -1) FROM PersonnelSignatureXtab (NOLOCK)
	  WHERE FK_UserIDNumber = @UserIDNumber

    OPEN PSigXtabCursor
    FETCH NEXT FROM PSigXtabCursor INTO @FK_UserIDNumber, @FK_SignatureID, @FK_HotelCustomerNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      DECLARE HotelsCursor CURSOR FORWARD_ONLY READ_ONLY FOR
	    SELECT CustomerNumber FROM Customer (NOLOCK)
	    WHERE CustomerTypeNumber = 7
	      AND ISNULL(MarkAsDeleted, 0) = 0
      OPEN HotelsCursor
	  FETCH NEXT FROM HotelsCursor INTO @HotelCustomerNumber
	  WHILE (@@FETCH_STATUS <> -1)
	  BEGIN
        SELECT @Count = COUNT(*) FROM PersonnelSignatureXtab (NOLOCK)
	    WHERE FK_UserIDNumber = @FK_UserIDNumber
	      AND FK_SignatureID = @FK_SignatureID
		  AND FK_HotelCustomerNumber = @HotelCustomerNumber

        IF @Count = 0
        BEGIN
	      INSERT INTO PersonnelSignatureXtab (FK_UserIDNumber, FK_SignatureID, FK_HotelCustomerNumber) VALUES (@FK_UserIDNumber, @FK_SignatureID, @HotelCustomerNumber)
	    END

        FETCH NEXT FROM HotelsCursor INTO @HotelCustomerNumber
	  END
	  CLOSE HotelsCursor
 	  DEALLOCATE HotelsCursor
      FETCH NEXT FROM PSigXtabCursor INTO @FK_UserIDNumber, @FK_SignatureID, @FK_HotelCustomerNumber
    END

    CLOSE PSigXtabCursor
    DEALLOCATE PSigXtabCursor

    FETCH NEXT FROM StaffIDCursor INTO @UserIDNumber
  END
  CLOSE StaffIDCursor
  DEALLOCATE StaffIDCursor
END
