If not exists (select * from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is Nulls or comments != 'web login user'))
Begin --Has no login record
Insert into kodak.tOnlineClient (UserGuid, RoleName, IISUrl, MachineIP, SessionID, IsOnline, LoginTime)
Values ('e540b9bc-3c19-469e-b3ee-362a228911a2', 'Administrator', 'http://localhost:4302/WebService/GCRISService.asmx', '150.245.176.149', 'ozff5zav4i5t1myspgj0mr55', 1, GETDATE())
Select 0
End
Else
Begin --Has login record
If exists (Select 1 from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and IsOnline = 0 and (comments is null or comments != 'web login user'))
Begin --(UserGuid's Online status is Off)
If 'False' = 'True'
Begin -- This is a session has ever logined but time out now
IF exists (Select 1 from didaj.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and '150.245.176.149' <> MachineIP and (comments is null or comments != 'web login user'))
Begin -- ClientSeesion ID has been hijackered by other session
--Return Value = 2 implicate Some one else hase ever been logined and logouted since this session time out
Select 2
End
Else
Begin -- The ClientSessionID is of himself
Update kodak.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2 ' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
else
Begin --Login try for the first time
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
Else
Begin --(UserGuid's Online status is On)
if exists (Select 1 from dbo.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and MachineIP = '150.245.176.149' and (comments is null or comments != 'web login user'))
Begin --At Same Machine, imlicated that is the Same User(Maybe exit the client side abnormally or relogin when fisnishing lockscreen status)
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
Else --At different Machine
Select 1 -- Some one Else with the Same UserID Is Online
End
End
Begin --Has no login record
Insert into kodak.tOnlineClient (UserGuid, RoleName, IISUrl, MachineIP, SessionID, IsOnline, LoginTime)
Values ('e540b9bc-3c19-469e-b3ee-362a228911a2', 'Administrator', 'http://localhost:4302/WebService/GCRISService.asmx', '150.245.176.149', 'ozff5zav4i5t1myspgj0mr55', 1, GETDATE())
Select 0
End
Else
Begin --Has login record
If exists (Select 1 from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and IsOnline = 0 and (comments is null or comments != 'web login user'))
Begin --(UserGuid's Online status is Off)
If 'False' = 'True'
Begin -- This is a session has ever logined but time out now
IF exists (Select 1 from didaj.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and '150.245.176.149' <> MachineIP and (comments is null or comments != 'web login user'))
Begin -- ClientSeesion ID has been hijackered by other session
--Return Value = 2 implicate Some one else hase ever been logined and logouted since this session time out
Select 2
End
Else
Begin -- The ClientSessionID is of himself
Update kodak.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2 ' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
else
Begin --Login try for the first time
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
End
Else
Begin --(UserGuid's Online status is On)
if exists (Select 1 from dbo.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and MachineIP = '150.245.176.149' and (comments is null or comments != 'web login user'))
Begin --At Same Machine, imlicated that is the Same User(Maybe exit the client side abnormally or relogin when fisnishing lockscreen status)
Update dbo.tOnlineClient
set LoginTime = getdate(), IsOnline = 1, MachineIP = '150.245.176.149', SessionID = 'ozff5zav4i5t1myspgj0mr55'
where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is null or comments != 'web login user')
Select 0 --Can Login
End
Else --At different Machine
Select 1 -- Some one Else with the Same UserID Is Online
End
End
可以参考下面的方法修改
declare
v_i integer;
v_j integer;
begin
select count(*) into v_i
from kodak.tOnlineClient where UserGuid = 'e540b9bc-3c19-469e-b3ee-362a228911a2' and (comments is Nulls or comments != 'web login user');
if v_i=0 then
Insert into kodak.tOnlineClient (UserGuid, RoleName, IISUrl, MachineIP, SessionID, IsOnline, LoginTime)
Values ('e540b9bc-3c19-469e-b3ee-362a228911a2', 'Administrator', 'http://localhost:4302/WebService/GCRISService.asmx', '150.245.176.149', 'ozff5zav4i5t1myspgj0mr55', 1, sysdate);
else
...
end if;
end;