SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--存储过程.SYS_Login
ALTER PROCEDURE [SYS_Login]
  @UserCode     varchar(32)--用户帐号
, @PassWord     varchar(128)--密码
, @IPAddress    varchar(128)--客户端IP地址
, @ComputerName varchar(128)--客户端机器名称
, @Result       int out	--返回消息ID
, @Text         varchar(256) out--返回消息文本
, @State        int = 1--状态 0:验证其他用户 1:验证当前登录用户
, @WorkPass     int = 0--验证业务密码
--, @LoginState   int = 0--保存登录状态
AS
--用户登录验证
--8056 修改于 2010.7.6
--8056 修改于 2011.01.19
/*
	@Result 返回值
		0: 验证失败
		1: 验证成功
		2: 提示有效期
		3: 已到有效期
	@State = 0 时， 不写入验证日志
*/

--验证帐号和密码
DECLARE 
  @ID int, 
  @HostId int,
  @HostEnabled int,
  @EmployeeId int,
  @ExpiryDate datetime, 
  @Authorized int, 
  @Date datetime, 
  @TipDate datetime, 
  @LastDate datetime,
  @SystemState int,
  @BCE41 int,
  @LastHostName varchar(128),
  @LastLoginState int,
  @_Pass varchar(128),
  @_WorkPass varchar(128),
  @LoginState   int = 0  --保存登录状态
	
SELECT @SystemState=dbo.GetSystemState()

IF @SystemState>0
BEGIN
	--1：正在维护  2：正在升级
	SELECT @Text=CASE @SystemState WHEN 1 THEN '系统正在维护，请稍候登录。' WHEN 2 THEN '系统正在升级，请稍候登录。' END
	RAISERROR(@Text, 16, 1) with nowait	
	RETURN
END

SET @Date = getdate()
SET @TipDate='9999-12-25'
SET @LastDate = '9999-12-31'	

IF @Date>@LastDate
BEGIN
	RAISERROR('授权已到期，本系统停止运行。如果您需要继续使用，请联系经销商获取继续使用许可权。如果您院有软件款未支付，请先支付款项。', 16, 1) with nowait
	RETURN
END

IF @Date>@TipDate
BEGIN
	set @Text='授权将到期，还有 '+cast(DATEDIFF (day, @Date, @LastDate) as varchar)+' 天使用期限，请尽快联系经销商获取继续使用许可权。'
END

select @HostId = ID, @HostEnabled = ISNULL(Enabled, 0) from SYS_Hosts where Name=@ComputerName

IF @HostId is null
BEGIN
  --判断客户端是否存在，不存在，则增加	
  EXEC Core_NewID 'SYS_Hosts', 'ID', @HostId out							
  INSERT INTO SYS_Hosts (ID, Code, Name, IP, xType, MacAddress, Position, Enabled, Description)	
  VALUES(@HostId, @HostId, @ComputerName, @IPAddress, 0, '', '',	1,'')
END

if @HostEnabled = 0
BEGIN
	SET @Result = 0
	RAISERROR('本客户端电脑被管理员禁止登录。', 16, 1) with nowait
	RETURN
END

SELECT 
  @id = id, 
  @EmployeeId = EmployeeId,  
  @ExpiryDate = ExpiryDate, 
  @Authorized = Authorized,
  @LastHostName = LoginHost,
  @_Pass = [Password],
  @_WorkPass = WorkPass,
  @LastLoginState = isnull(LoginState, 0)
FROM
	Sys_Users 
WHERE
	Code=@UserCode


IF @id IS NULL
BEGIN
	SET @Result = 0
	RAISERROR('帐号[%s]不存在。', 16, 1, @UserCode) with nowait
	RETURN
END

IF (@WorkPass = 1)
BEGIN
  IF @_WorkPass<>@PassWord  
  BEGIN
	  SET @Result = 0
	  RAISERROR('帐号[%s]业务密码不正确。', 16, 1, @UserCode) with nowait
	  RETURN
  END
END
ELSE BEGIN
  IF @_Pass<>@PassWord  
  BEGIN
	  SET @Result = 0
	  RAISERROR('帐号[%s]登录密码不正确。', 16, 1, @UserCode) with nowait
	  RETURN
  END
END

IF @Authorized=1
BEGIN
	SET @Result = 0
	RAISERROR('帐号已禁用.请与管理员联系.', 16, 1) with nowait				
	RETURN
END

IF @Date>@ExpiryDate
BEGIN
	SET @Result = 0
	RAISERROR('帐号已超出有效期.请与管理员联系.', 16, 1) with nowait		
	RETURN
END

IF @State=1 --判断用户是否已经登录
BEGIN
  SELECT @LoginState=ISNULL(VALUE, 0) FROM SYS_Parameters WHERE ProductID=1 AND ProgramID=1 AND ParamNo=35

  IF (@LoginState=1) and (@LastLoginState & 1 = 1) and (@ComputerName<>@LastHostName)
  BEGIN

    /*
    DECLARE @DBName varchar(255)
    Select @DBName=Name+'_'+@UserCode From Master.dbo.SysDataBases Where DbId=(Select Dbid From Master.dbo.SysProcesses Where Spid = @@spid)
    if object_id('tempdb.dbo.##'+@DBName) is null
    begin
      exec ('create table ##'+@DBName+'(userid varchar(6))')
    end
    */
	  SET @Result = 0
	  RAISERROR('帐号已经从客户端[%s]登录, 如有疑问请与管理员联系.', 16, 1, @LastHostName) with nowait		
	  RETURN    
  END
END

--获取员工在职状态
SELECT @BCE41=BCE41 FROM BCE1 WHERE BCE01=@EmployeeId

IF @BCE41 IS NULL
BEGIN
	SET @Result = 0
	RAISERROR('帐号不是有效的员工.请与管理员联系.', 16, 1) with nowait				
	RETURN
END

--检测用户是否在职
IF @BCE41 in(2,3)
BEGIN
  SET @Result = 0
  RAISERROR('帐号对应的员工已经离职或退休.', 16, 1) with nowait						
  RETURN
END

IF @HostEnabled = 2
BEGIN
  --判断黑名单
  if EXISTS(SELECT * FROM Sys_AccessHosts WHERE UserID=@ID AND Status=0)
  BEGIN
    SET @Result = 0
    RAISERROR('此帐号不能从当前客户端登录系统.', 16, 1) with nowait						
    RETURN    
  END
  --判断白名单
  IF NOT EXISTS(SELECT * FROM Sys_AccessHosts WHERE UserID=@ID AND Status=1)
  BEGIN
    SET @Result = 0
    RAISERROR('此帐号不能从当前客户端登录系统.', 16, 1) with nowait						
    RETURN    
  END
END

SET @Result = 1

--if dbo.IS_X64()=0 and isnull(@Text,'')=''
--begin
--	set @Text='当前数据库为32位系统，建议使用64位系统'
--end

declare @icheck varchar(100)=''
if isnull(@Text,'')=''
 set @icheck=dbo.CheckBatchMove()
if @icheck>'' 
begin
	set @Text=@icheck
end


IF @State=1
BEGIN
  update SYS_Users set LoginState=0 where LoginHost = @ComputerName and id <> @id
  update SYS_Users set LoginHost = @ComputerName, LoginState = LoginState | 1 where id = @id
END

--获取医生所在的诊室
declare @bce01 int,@bas02 varchar(30)
select @bas02=Room From SYS_HOSTS where Name=@ComputerName
if isnull(@bas02,'')>'' 
begin
		delete BJT1 where BAS02=@bas02 
		insert into BJT1(BAS02,BCE01)
		select @bas02,@EmployeeId
end