用户注册及跟踪代码（一）

用户注册册及确认在线的ASP程序。
1. SQL的表及储存过程
---------------------------------------------
CREATE TABLE [dbo].[userbaseinfo] (
    [userid] [varchar] (50) NOT NULL ,
    [password] [varchar] (50) NOT NULL ,
    [validcodelogin] [char] (50) NOT NULL ,
    [userlevel] [char] (1) NULL ,
    [logintime] [char] (50) NULL
) ON [PRIMARY]
GO

alter table userbaseinfo
add
constraint PK_userbaseinfo_userid
    primary key (userid)
Go

CREATE TABLE [dbo].[userdetailinfo] (
    [userid] [varchar] (50) NOT NULL ,
    [password] [varchar] (30) NOT NULL ,
    [realname] [varchar] (10) NULL ,
    [sex] [char] (10) NULL ,
    [birthday] [datetime] NULL ,
    [idcode] [varchar] (50) NULL ,
    [address] [varchar] (300) NULL ,
    [email] [varchar] (50) NULL ,
    [telephone] [varchar] (50) NULL
) ON [PRIMARY]
GO

alter table userdetailinfo
add
constraint PK_userdetailinfo_userid
    primary key (userid)
Go

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE proc dbo.proc_GetRandom_internal
--取得校验码
    @minNum        integer,
    @maxNum        integer,
    @RandomNum    float output
as
set nocount on

declare @numRange integer
declare @ranSeed integer
declare @curTime  datetime

begin

  select @numRange=@maxNum-@minNum+1

  select @curTime=getdate()
  select @ranSeed=datediff(s,'2000-1-1',@curTime)
  select @ranSeed=@ranSeed+1
  select @RandomNum=rand()*@numRange+@minNum
  --print @RandomNum
  return
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE proc dbo.proc_GetValidCode_Internal
--取得校验码
    @CodeLength    integer,
    @ValidCode    varchar(10) output
as
set nocount on

declare @chrRnd char(1)
declare @chrRndNo integer

begin

  select @ValidCode=""

  while (@CodeLength>0)
  begin
    exec proc_GetRandom_internal 1,52,@chrRndNo output
    if @chrRndNo>26
      begin
       select @chrRndNo=@chrRndNo+6
      end
    select @chrRnd=char(@chrRndNo+64)
    select @ValidCode=@ValidCode+@chrRnd
    select @CodeLength=@CodeLength-1
  end
  print @validCode
  return
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE proc dbo.proc_UserInfoUpdate
--用户更新个人信息
    @ValidCodeLogin  varchar(10),
    @RealName    Varchar(10),
    @Sex        Varchar(10),
    @Birthday    datetime,
    @IDCode        Varchar(50),
    @Address    Varchar(300),
    @eMail        Varchar(50),
    @Telephone    Varchar(50)
as

set nocount on

declare @UserValidFlag int
declare @ValidCodeReg varchar(30)
declare @UserLevel varchar(1)
declare @UserID varchar(30)

begin
exec proc_isUserValidbyCode_internal @ValidCodeLogin,@UserValidFlag output
if @UserValidFlag<0
  begin
    --select @UserValidFlag as resultID
      -- -1 用户尚未登录
      -- -2 用户超时
    return @UserValidFlag
  end

select @UserID=UserID from UserBaseinfo where ValidCodeLogin=@ValidCodeLogin

Update UserDetailInfo
    set RealName=@RealName,
    Sex=@Sex,
    Birthday=@Birthday,
    IDCode=@IDCode,
    Address=@Address,
    eMail=@eMail,
    Telephone=@Telephone
  where
    UserID=@UserID;

if (@RealName="" or @Birthday="" or @Sex="" or @IDCode="" or @Address="" or @eMail="" or @Telephone="")
   begin
     --select -3 as resultID
     return -3  --信息尚未全部填写
   end

select 0 as resultID

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create proc dbo.proc_UserLogOut
--用户退出
    @ValidCodeLogin  varchar(10)
as

set nocount on

declare @UserValidFlag int
declare @UserLevel varchar(9)

begin
exec proc_isUserValidbyCode_internal @ValidCodeLogin,@UserValidFlag output
if (@UserValidFlag<0)
  begin
    --select @UserValidFlag as resultID
    return @UserValidFlag
      -- -1 用户尚未登录
      -- -2 用户超时
  end

Update UserBaseInfo
    set ValidCodeLogin='',
        LoginTime='1970-1-1'
  where
    ValidCodeLogin=@ValidCodeLogin

--select 0 as resultID
return 0
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE proc dbo.proc_UserRegBase
--用户基本资料注册
    @UserID        Varchar(30),
    @Password    Varchar(30)
as

set nocount on

declare @UserLevel varchar(9)
--declare @ValidCodeReg varchar(10)
declare @ValidCodeLogin  varchar(10)
declare @LoginTime  datetime
declare @userExist int
declare @PwdLength int

begin
select @UserLevel="0"
select @PwdLength=4
if (datalength(@Password)<@PwdLength)
   begin
     select -4 as returnID
     return -4        --密码长度不够
   end

--exec proc_GetValidCode_internal 10,@ValidCodeReg output    --取得用户注册校验码
exec proc_GetValidCode_internal 10,@ValidCodeLogin output    --取得用户登录校验码
exec proc_isUserExist_internal @UserID,@userExist output    --取得用户存在标志
select @LoginTime=getdate()
print @userExist
if @userExist=0
  begin
    select -1 as resultID
    return -1  --用户已存在
  end

--插入用户基本信息表
insert into UserBaseInfo
   (UserID,Password,UserLevel,ValidCodeLogin,LoginTime)
   Values(@UserID,@Password,@UserLevel,@ValidCodeLogin,@LoginTime)

--插入用户详细信息表
insert into UserDetailInfo
   (UserID,Password) Values(@UserID,@Password)

--取得用户注册校验码,登录校验码
select 0 as resultID
select ValidCodeLogin from UserBaseInfo where UserID=@UserID

return 0
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.proc_isUserExist_internal
--判断用户名是否存在
    @UserID        Varchar(30),
    @existFlag    int output
as

set nocount on
begin

if not EXISTS(select * from UserBaseInfo where UserID=@UserID)
   begin
    select @existFlag =-1
    return
   end
select @existFlag =0
return
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.proc_isUserValidbyCode_internal
--用户身份检验(根据登录校验码)
    @ValidCodeLogin  varchar(10),
    @validFlag    int output
as

set nocount on

declare @LoginTime  datetime
declare @curTime  datetime
declare @diffTime  datetime

begin
if not EXISTS(select * from UserBaseInfo where ValidCodeLogin=@ValidCodeLogin)
  begin
    select @validFlag=-1   --用户尚未登录
    return
  end

select @LoginTime = (select LoginTime from UserBaseInfo where ValidCodeLogin=@ValidCodeLogin)
select @curTime=getdate()
select @diffTime=datediff(hh,@LoginTime,@curTime)
if @diffTime>=10
  begin
    select @validFlag=-2    --用户超时
    return
  end

select @LoginTime=getdate()                --取得用户最后登录时间
update UserBaseInfo set LoginTime=@LoginTime where ValidCodeLogin=@ValidCodeLogin

select @validFlag=0
return
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO