CREATE PROCEDURE sp_ClientPerson_Register

@SessionID int,

@DbID int,
@ID int,
@OnLine tinyint,

@Login TLoginName OUTPUT, 
@Pwd TLoginPwd,
@Type tinyint,

@FirstName TName, @MiddleName TName, @LastName TName, @BirthDate datetime, @Sex bit, 
@Country TCountry, @State TState, @City TCity, @Address TAddress, @Zip TZip, @Email	TEMail, @Phone TPhone,

@LogID int OUTPUT,
@Logged datetime OUTPUT,
@DataID int OUTPUT,

@LoginID int OUTPUT,
@ClientID int OUTPUT,
@RegID int OUTPUT,

@Name TName OUTPUT,
@NewLogin TLoginName OUTPUT, 
@OriginLogin TLoginName OUTPUT, 
@Started datetime OUTPUT,
@Status int OUTPUT

 AS

 /*
 -1 - Error
 0 - Ok
 1 - Empty Login
 2 - Empty password
 3 - Found but was renamed
 4 - Renamed and was added as newbie
 5 - Incorrect password
 6 - Was added as newbie
*/ 


DECLARE @Result int SET @Result = - 1 

DECLARE @N int, @OldLoginID int, @OldClientID int, @OldLogin TLoginName,  @OldPwd TLoginPwd, @ErrorMsg varchar( 255 ), @Code TCode
DECLARE @ClientType tinyint, @Updated datetime

SET @LoginID = NULL

SET @Logged = GETUTCDATE()

BEGIN TRANSACTION ClientPersonRegister

INSERT INTO ClientPersonData (FirstName, MiddleName, LastName, BirthDate, Sex, Country, State, City, Address, Zip, Email, Phone)
                                     VALUES (@FirstName, @MiddleName, @LastName, @BirthDate, @Sex, @Country, @State, @City, @Address, @Zip, @Email, @Phone)
IF @@ERROR <>  0  BEGIN ROLLBACK  TRANSACTION ClientPersonRegister RETURN - 1  END
SET @DataID = @@IDENTITY

INSERT INTO ClientPersonRegsLog (Logged, OnLine, Login, Pwd, Type, Status, DataID, SessionID, [$DbID], [$ID])
                                            VALUES (@Logged, @OnLine, @Login, @Pwd, @Type, @Result,  @DataID, @SessionID, @DbID, @ID)

IF @@ERROR <>  0  BEGIN ROLLBACK  TRANSACTION ClientPersonRegister RETURN - 1  END
SET @LogID = @@IDENTITY

COMMIT TRANSACTION ClientPersonRegister

BEGIN TRANSACTION ClientPersonRegister

 -- Check the loginname and the password of the login
 

SET @Login = ISNULL(@Login,'')
SET @Pwd = ISNULL(@Pwd,'')

IF ( @Login = '' ) OR (@Pwd = '' )
 BEGIN 
    IF @Login = '' BEGIN SET @Login =  '$' + convert(varchar( 8 ),@LogID) SET @ErrorMsg = 'Empty login'  SET @Result =  1  END
    IF @Pwd = '' BEGIN SET @Pwd =  '$' + convert(varchar( 8 ),@LogID) SET @ErrorMsg = 'Empty password'  SET @Result =  2  END
   IF @OnLine =  0 
   BEGIN
     INSERT INTO ClientPersonLogins (ClientID, Login, Pwd, Started, Status, Type)  VALUES ( 0 , @Login, @Pwd, @Logged,  0 ,  255 )
    SET @LoginID = @@IDENTITY
  END
  GOTO SaveHandler 
END

 /* Find a login with the name*/ 
SELECT @OldLoginID = LoginID, @OldPwd = Pwd, @OldClientID = ClientID  FROM ClientPersonLogins WHERE  (Login = @Login) AND  (Type = @Type)
SET @N = @@ROWCOUNT

IF @N >  0 
BEGIN

 UPDATE ClientPersonLogins SET Status =  0  WHERE (LoginID = @OldLoginID)  /* Wake up, login !*/ 
 IF @Pwd = @OldPwd
  BEGIN
    SET @Result =  0   /* This means the login has been found by the name and the password*/ 
    SET @LoginID = @OldLoginID
 --    SET @CurrentLogin = 1
 
    SET @ClientID = @OldClientID
    EXEC sp_ClientPerson_ModifyByData @OldClientID, @Name, @Code,  @DataID,  @RegID OUTPUT, @Updated OUTPUT
  END
    ELSE
  BEGIN
     /* Find a login with these name and password by the oldname and the password */ 
    SELECT @OldLoginID = LoginID, @OldPwd = Pwd, @OldClientID = ClientID FROM ClientPersonLogins WHERE  (OriginLogin = @Login)  AND (Pwd = @Pwd) AND (Type = @Type)
    SET @N = @@ROWCOUNT
     IF @N =  0 
      BEGIN  /* Name collision on @Login */ 
        IF @OnLine =  0 
         BEGIN  /* Renaming */ 
          SET @NewLogin = RTRIM(LTRIM(@Login)) + SUBSTRING(@LastName, 1 , 1 )
          IF EXISTS(SELECT LoginID FROM ClientPersonLogins WHERE (Login = @NewLogin) AND (Type = @Type))
          BEGIN
           SET @NewLogin = RTRIM(LTRIM(@NewLogin)) + SUBSTRING(@FirstName, 1 , 1 )
            IF EXISTS(SELECT LoginID FROM ClientPersonLogins WHERE (Login = @NewLogin) AND (Type = @Type))
            BEGIN
              SET @NewLogin = RTRIM(LTRIM(@NewLogin)) + RTRIM(LTRIM(STR(@LogID, 8 , 0 )))
            END
         END

        EXEC sp_ClientPerson_CreateByData @Name, @Code, @DataID, @ClientID OUTPUT, @ClientType OUTPUT, @RegID OUTPUT, @Updated  OUTPUT
        INSERT INTO ClientPersonLogins (ClientID, Login, Pwd, Type, OriginLogin, Started) VALUES (@ClientID, @NewLogin,@Pwd, @Type, @Login, @Logged)
        IF (@@ERROR <>  0 )  BEGIN  SET @ErrorMsg = 'Cannot insert renamed login' GOTO ErrHandler  END
        SET @LoginID = @@IDENTITY
 --       SET @CurrentLogin = 1
 
        SET @Result =  2   /* This means the login has been renamed and added as a newbie */ 
     END
      BEGIN
       SET @Result =  5 
       SET @ErrorMsg = 'Incorrect password' 
       GOTO SaveHandler  
     END

      END
        ELSE
      BEGIN
       UPDATE ClientPersonLogins SET Status =  0  WHERE (LoginID = @OldLoginID)  /* Wake up, login !*/ 
       SET @Result =  3   /* This means the player has been found by the old name  and the password*/ 
       SET @LoginID = @OldLoginID
 --       SET @CurrentLogin = 1
 
       SET @ClientID = @OldClientID
       EXEC sp_ClientPerson_ModifyByData @OldClientID, @Name, @Code, @DataID, @RegID OUTPUT, @Updated OUTPUT
      END 

  END
END
  ELSE
BEGIN  /* We have not found that login by these name and password at all and we'll add one as a newbie*/ 
    EXEC sp_ClientPerson_CreateByData @Name, @Code, @DataID, @ClientID OUTPUT, @ClientType OUTPUT, @RegID OUTPUT, @Updated  OUTPUT
    INSERT INTO ClientPersonLogins (ClientID, Login, Pwd, Type, Started) VALUES (@ClientID, @Login, @Pwd, @Type, @Logged)
    IF (@@ERROR <>  0 )  BEGIN  SET @ErrorMsg = 'Cannot insert new login' GOTO ErrHandler  END
    SET @LoginID = @@IDENTITY
 --        SET @CurrentLogin = 1
 
    SET @Result =  4   /* This means the login is a newbie*/ 
END

SaveHandler:

COMMIT TRANSACTION ClientPersonRegister

UPDATE ClientPersonRegsLog SET LoginID = @LoginID, Status = @Result, ErrorMsg = @ErrorMsg WHERE LogID = @LogID
RETURN @Result

ErrHandler:
ROLLBACK TRANSACTION ClientPersonRegister

SET @Result = - 1 
UPDATE ClientPersonRegsLog SET LoginID = @LoginID, Status = @Result, ErrorMsg = @ErrorMsg WHERE LogID = @LogID

EndHandler:
RETURN @Result
GO

set rs_register = Server.createobject( "ADODB.Recordset" )
	sql =  "DECLARE @Result int, @SessionID int, @DbID int, @ID int, @OnLine tinyint, @Login TLoginName, @Pwd TLoginPwd, @Type tinyint, @FirstName TName, @MiddleName TName, @LastName TName, @BirthDate datetime, @Sex bit, @Country TCountry, @State TState, @City TCity, @Address TAddress, @Zip TZip, @Email TEMail, @Phone TPhone, @LogID int, @Logged datetime, @DataID int, @LoginID int, @ClientID int, @RegID int, @Name TName, @NewLogin TLoginName, @OriginLogin TLoginName, @Started datetime, @Status int " &_
		   "SET @OnLine = 0  SET @DbID = NULL SET @ID = NULL SET @LoginID = NULL SET @SessionID =  0  SET @Login = '"& login &"' SET @Pwd = '"& password &"' SET @Type =  0  SET @FirstName = "& first_name & " SET @MiddleName = NULL SET @LastName = " & last_name & " SET @BirthDate= " & birth_date & " SET @Sex = " & sex & " SET @Country = " & country & " SET @State= " & state & " SET @City = " & city & " SET @Address = " & address & " SET @Zip = " & zip & " SET @Email = '"& email &"' SET @Phone = " & phone & " " &_
		   "EXEC @Result = sp_ClientPerson_Register @SessionID, @DbID, @ID, @OnLine, @Login, @Pwd, @Type, @FirstName, @MiddleName, @LastName, @BirthDate, @Sex, @Country, @State, @City, @Address, @Zip, @Email, @Phone, @LogID OUTPUT, @Logged OUTPUT, @DataID OUTPUT, @LoginID OUTPUT, @ClientID OUTPUT, @RegID OUTPUT, @Name OUTPUT, @NewLogin OUTPUT, @OriginLogin OUTPUT, @Started OUTPUT, @Status OUTPUT " &_
		   "select @Result as Result, @LoginID as LoginID, @ClientID as ClientID, @RegID as RegID" 
	rs_register.open sql, cn

<%= rs_register.fields( "Result" )%>