动态更新时间的问题

动态更新时间的问题

-- -----------------------------------------------------------------
-- File : ../Database/StoredProcedure/GSP_User_LogoutUser.sql
-- Desc : Logout out user StoredProcedure
-- Auth : tiantao
-- Date : 2010-05-12 10:09
-- Last : 
-- Copyright (c) 2009 JingDian,Corp. All rights reserved.
-- -----------------------------------------------------------------
USE [WebGame_User]
GO

 

 

-- -----------------------------------------------------------------
-- StoredProcedure：GSP_User_LogoutUser     登出用户  
-- -----------------------------------------------------------------
IF EXISTS (SELECT 1
          FROM sysobjects
          WHERE  id = object_id('dbo.GSP_User_LogoutUser')
          AND TYPE = 'P')
   DROP PROCEDURE dbo.GSP_User_LogoutUser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GSP_User_LogoutUser]
 @dwAccountID    INT,     -- 账号ID
 @dwNowTime     INT,     -- 登出时间
 @dwRoleId     INT,     -- 登出的角色
 @dwSvrId        INT,     -- 登出的服务器ID
 @dwCurTime     INT      -- 当前账号离线时间[防沉迷]

AS
 DECLARE     @T_IsCheck    TINYINT;    -- 是否通过防沉迷验证
 DECLARE     @T_LogOnTime   INT;     -- 登录时间
 DECLARE     @T_LogOutTime   INT;
 DECLARE     @T_OnlineTime   INT;     -- 在线累积时间
 DECLARE     @T_IdentityCard   VARCHAR(18);
 DECLARE     @T_TimeDiff    INT;     -- 时间差
 
 -- 日志相关
 DECLARE     @T_RoleID    INT;
 DECLARE     @T_Year     INT;
 DECLARE     @T_Month    INT;
 DECLARE     @T_Day     INT;
 DECLARE     @T_Date     DATETIME; 
 DECLARE     @T_CurOnTime   NVARCHAR(256);
 --DECLARE     @T_CurOnTime   DATETIME;
 DECLARE     @T_TableName   NVARCHAR(256);
 DECLARE     @T_UpdateSql   NVARCHAR(2048); 
 DECLARE     @T_QuerySql    NVARCHAR(2048); 
 DECLARE     @T_Param_Def   NVARCHAR(1024);

BEGIN
 SET NOCOUNT ON;
 
 -- --------------防沉迷时间操作-----------------------------------------------
 -- 1.查找该角色对应的账号，身份证号
  SELECT @T_IdentityCard = UA_IdentityCard, 
      @T_IsCheck = UA_IsCheck,
      @T_LogOutTime = UA_LogOutTime, 
      @T_OnlineTime = UA_OnlineTime, 
      @T_LogOnTime = UA_LogOnTime FROM User_AccountInfo 
   WHERE UA_AccountID = @dwAccountID;
  
 IF(@T_IsCheck = 0)
 BEGIN
  -- 获取在线累积时间    
  SET @T_TimeDiff = @dwCurTime - @T_LogOnTime;
   
  IF(@T_TimeDiff >= 5*3600)
  BEGIN
   UPDATE User_AccountInfo SET UA_FCMTime = @dwCurTime,UA_OnlineTime = 0 WHERE UA_IdentityCard = @T_IdentityCard;
   IF(@@ERROR<>0)
   BEGIN
    ROLLBACK TRANSACTION;
    SELECT -1 AS Result
    RETURN -1;
   END
  END
  ELSE
  BEGIN  
   UPDATE User_AccountInfo SET UA_OnlineTime = @T_TimeDiff + @T_OnlineTime, UA_LogOutTime = @dwCurTime 
    WHERE UA_AccountID = @dwAccountID;
   IF(@@ERROR<>0)
   BEGIN
    SELECT -2 AS Result;
    RETURN -2;
   END
  END
 END
 -- -----------------------------------------------------------------------------

 -- 更新登陆:Role_BasicInfo  角色基本信息
 UPDATE Role_BasicInfo SET RB_LogonTime = 0,
         RB_LogonIP = NULL,
         RB_LogoutTime = @dwNowTime,
         RB_SvrID = 0
  WHERE RB_AccountID = @dwAccountID;
 IF(@@ERROR<>0)
 BEGIN
  SELECT -2 AS Result;
  RETURN -2;
 END
 
 -- -----------------------------------------------------------------------------
 -- 插入 Log_LogonInfo    登录日志表
 SET @T_Year = YEAR(GETDATE());
 SET @T_Month = MONTH(GETDATE());
 SET @T_Day = DAY(GETDATE());

 -- table name
 SET @T_TableName = 'Log_LogonInfo_' + RTRIM(CONVERT(varchar(32), @T_Year))
      + RTRIM(CONVERT(varchar(32), @T_Month))
      + RTRIM(CONVERT(varchar(32), @T_Day));  
      
 -- update logout game time data
 SET @T_UpdateSql = N'UPDATE [WebGame_Log].[dbo].[' + @T_TableName
      + N'] SET LLI_LogOutTime = ' + N'GETDATE()' 
      + N' WHERE LLI_RoleID = ' + N'@T_RoleID'
      + N' AND LLI_LogOnTime = '+ N'(SELECT MAX(LLI_LogOnTime) FROM [WebGame_Log].[dbo].['
      + @T_TableName + N'] WHERE LLI_RoleID = ' + N'@T_RoleID' + N')';
    
 SET @T_Param_Def = N'@T_RoleID INT';
 SET @T_RoleID = @dwRoleID
       
 EXEC sp_executesql @T_UpdateSql, @T_Param_Def, @T_RoleID;
 IF(@@ERROR <> 0)
 BEGIN    
  SELECT -6 AS Result
  -- ROLLBACK TRANSACTION;
  RETURN -6;
 END 
 -- -----------------------------------------------------------------------------------

 RETURN 0;

END
GO