[RELEASE] Script - Number of Players Online

1st
creat the LoginStatus in the Chars Table as follows

Code:

USE PS_GameData 
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'LoginStatus' AND object_id = OBJECT_ID('Chars')) 
BEGIN 
    ALTER TABLE Chars 
        ADD LoginStatus bit 
    PRINT 'Column successfully added!' 
END 
ELSE 
BEGIN 
    PRINT 'Column already exists!' 
END

2nd
update the Read Chars Stored procedure as follows

Code:

USE [PS_GameData]
GO
/****** Object:  StoredProcedure [dbo].[usp_Read_Chars_Detail2_R]    Script Date: 6/9/2013 12:12:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Proc [dbo].[usp_Read_Chars_Detail2_R] 

@CharID int

AS

SET NOCOUNT ON

DECLARE @UserUID int
DECLARE @Money bigint
DECLARE @GuildID int
DECLARE @GuildLevel tinyint

-- 2005-12-30 스탯,스킬 이벤트관련...
DECLARE @Event1 tinyint
DECLARE @Event2 tinyint
--

SET @Money = 0
SET @UserUID = (SELECT UserUID FROM Chars WHERE CharID = @CharID)
SET @GuildID = (SELECT GuildID FROM GuildChars WHERE CharID = @CharID AND Del = 0)

IF(@UserUID IS NOT NULL)
BEGIN
	SET @Money = (SELECT ISNULL([Money], 0) FROM UserStoredMoney WHERE UserUID = @UserUID)
END
ELSE
BEGIN
	SET @Money = 0
END

IF(@GuildID IS NOT NULL)
BEGIN
	SET @GuildLevel = (SELECT GuildLevel FROM GuildChars WHERE CharID = @CharID AND Del = 0)
END
ELSE
BEGIN
	SET @GuildID = 0
	SET @GuildLevel = 0
END

-- 2005-12-30 스탯,스킬 이벤트관련...
SELECT @Event1=Event1, @Event2=Event2 FROM CharEvents WHERE CharID=@CharID
--

SELECT UserUID, CharID, CharName, Slot, Family, Grow, Hair, Face, [Size], Job, Sex, [Level], 
StatPoint AS DistPoint, SkillPoint, [Str], Dex, Rec, [Int], Luc, Wis, HP, MP, SP, Map, Dir, [Exp], [Money], 
PosX, PosY, PosZ, Hg, Vg, Cg, Og, Ig, Del, K1, K2, K3, K4, @Money AS StoredMoney, @GuildID AS GuildID, @GuildLevel AS GuildLevel,

-- 전투공로관 보상레벨
KillLevel, DeadLevel,
--

-- 스탯,스킬 이벤트관련...
@Event1, @Event2,
--

DATEPART(yyyy, LeaveDate) AS LeaveDate_YYYY, 
DATEPART(mm, LeaveDate) AS LeaveDate_MM, 
DATEPART(dd, LeaveDate) AS LeaveDate_DD, 
DATEPART(hh, LeaveDate) AS LeaveDate_HH, 
DATEPART(mi, LeaveDate) AS LeaveDate_MI, 
DATEPART(ss, LeaveDate) AS LeaveDate_SS

--

FROM Chars WHERE CharID = @CharID

-- 접속시간 기록
UPDATE Chars SET JoinDate=GETDATE() WHERE CharID=@CharID
--

-- loged in Chars setup --
UPDATE Chars SET LoginStatus = 0 Where UserUID = @UserUID
UPDATE Chars SET LoginStatus = 1 WHERE CharID = @CharID and UserUID = @UserUID

SET NOCOUNT OFF

3rd
Update Leave Chars Procedure as follows

Code:

USE [PS_GameData]
GO
/****** Object:  StoredProcedure [dbo].[usp_Save_Char_LeaveDate_R]    Script Date: 6/9/2013 7:06:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Proc [dbo].[usp_Save_Char_LeaveDate_R] 

@CharID int

AS

SET NOCOUNT ON

-- 종료시간 기록
UPDATE Chars SET LeaveDate=GETDATE() WHERE CharID=@CharID
-- Loged In Chars setup --
UPDATE Chars SET LoginStatus = 0 WHERE CharID = @CharID

SET NOCOUNT OFF

4th not shure if its requierd but i did it anyways just for added if it failes on leave char stuff

Code:

USE [PS_userdata]
GO
/****** Object:  StoredProcedure [dbo].[usp_Try_GameLogout_R]    Script Date: 6/9/2013 7:18:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.usp_Try_GameLogout_R    Script Date: 2008-6-7 18:34:05 ******/
ALTER        Proc [dbo].[usp_Try_GameLogout_R]

/* 
Created by humanws, 2005-10-18
로그아웃 로그 저장
*/

@UserUID int,
@SessionID bigint,
@LogoutType smallint = 0,
@ErrType int = 0

AS

SET NOCOUNT ON

DECLARE @LogTime datetime
DECLARE @Sql nvarchar(4000)
DECLARE @yyyy varchar(4)
DECLARE @mm varchar(2)
DECLARE @dd varchar(2)
DECLARE @LogType bit	-- Login:0, Logout:1

SET @LogType = 1
SET @LogTime = GETDATE()
SET @yyyy = DATEPART(yyyy, @LogTime)
SET @mm = DATEPART(mm, @LogTime)
SET @dd = DATEPART(dd, @LogTime)

IF( LEN(@mm) = 1 )
BEGIN
	SET @mm = '0' + @mm
END

IF( LEN(@dd) = 1 )
BEGIN
	SET @dd = '0' + @dd
END

SET @Sql = N'
INSERT INTO PS_GameLog.dbo.UserLog
(SessionID, UserUID, LogType, LogTime, LogoutType, ErrType)
VALUES(@SessionID, @UserUID, @LogType, @LogTime, @LogoutType, @ErrType)'

EXEC sp_executesql @Sql, 
N'@SessionID bigint, @UserUID int, @LogType bit, @LogTime datetime, @LogoutType smallint, @ErrType int',
@SessionID, @UserUID, @LogType, @LogTime, @LogoutType, @ErrType

-- For Login status updates --
UPDATE PS_GameData.dbo.Chars SET LoginStatus = 0, LeaveDate = GETDATE() WHERE UserUID = @UserUID
SET NOCOUNT OFF

5th and final ( FOR ALL DC's caused by server shutdown and altF4 players ) creat this trigger witch uses the logtype and error codes i found to set the login status to 0 when players dc and alt F4 out of game also sets loginstatus to 0 if a player just reguler logs out with this trigger

Code:

USE [PS_GameLog]
GO
/****** Object:  Trigger [dbo].[Leave]    Script Date: 6/9/2013 7:22:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Leave]
ON [PS_GameLog].[dbo].[UserLog]
FOR INSERT
AS
declare @UserUID int

SET @UserUID= (SELECT UserUID FROM inserted where LogoutType in (1,3,4) and ErrType in (64,1236))

UPDATE PS_GameData.dbo.Chars SET LoginStatus=0 WHERE UserUID=@UserUID