vou contribuir um pouco aqui com um conhecimento que tenho e que uso no meu servidor!
Primeiro crie as colunas no memb_stat
Código:
USE [MUOnline]
GO
ALTER TABLE MEMB_STAT
ADD [ConnectStartTime] [varchar](10) NOT NULL DEFAULT (getdate())
GO
ALTER TABLE MEMB_STAT
ADD [ConnectEndTime] [varchar](10) NOT NULL DEFAULT (getdate())
GO
ALTER TABLE MEMB_STAT
ADD [TimeON] [int] NOT NULL DEFAULT 0
GO
Depois Alterar a Procedure de Connect
Código:
DROP PROCEDURE [dbo].[WZ_CONNECT_MEMB]
GO
CREATE PROCEDURE [dbo].[WZ_CONNECT_MEMB]
@memb___id VARCHAR(10),
@ServerName VARCHAR(20),
@IP VARCHAR(15)
AS
BEGIN
SET NOCOUNT ON
DECLARE @find_id VARCHAR(10);
DECLARE @ConnectStat TINYINT;
SET @find_id = 'NOT';
SET @ConnectStat = 1;
SELECT @find_id = S.memb___id FROM MEMB_STAT S INNER JOIN MEMB_INFO I ON S.memb___id = I.memb___id WHERE I.memb___id = @memb___id
IF(@find_id = 'NOT')
BEGIN
INSERT INTO MEMB_STAT (memb___id,ConnectStat,ServerName,IP,ConnectTM) VALUES(@memb___id, @ConnectStat, @ServerName, @IP, getdate());
--=============================================================--
-- ATUALIZANDO HORÁRIO DE LOGIN NA MEMB_STAT APÓS INSERT DE DADOS --
--=============================================================--
UPDATE MEMB_STAT SET ConnectStartTime = DATEDIFF(s, '19700101', GETDATE()) WHERE memb___id = @memb___id;
END
ELSE
UPDATE MEMB_STAT SET ConnectStat = @ConnectStat, ServerName = @ServerName,IP = @IP, ConnectTM = getdate() WHERE memb___id = @memb___id
--=========================================--
-- ATUALIZANDO HORÁRIO DE LOGIN NA MEMB_STAT --
--=========================================--
UPDATE MEMB_STAT SET ConnectStartTime = DATEDIFF(s, '19700101', GETDATE()) WHERE memb___id = @memb___id;
END
e por fim Alterar a Procedure de Desconnect
Código:
DROP PROCEDURE [DBO].[WZ_DISCONNECT_MEMB]
GO
CREATE PROCEDURE [DBO].[WZ_DISCONNECT_MEMB]
@memb___id VARCHAR(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @FIND_ID VARCHAR(10);
DECLARE @CONNECTSTAT TINYINT;
DECLARE @LOGINTIME INT;
DECLARE @LOGOUTTIME INT;
DECLARE @TIMEON INT;
SET @CONNECTSTAT = 0
SET @FIND_ID = 'NOT'
SELECT @FIND_ID = S.memb___id FROM MEMB_STAT S INNER JOIN MEMB_INFO I ON S.memb___id = I.memb___id WHERE I.memb___id = @memb___id;
IF( @FIND_ID <> 'NOT' )
BEGIN
UPDATE MEMB_STAT SET CONNECTSTAT = @CONNECTSTAT, DISCONNECTTM = GETDATE() WHERE memb___id = @memb___id;
--======================================================--
-- ATUALIZANDO HORÁRIO DE LOGGOUT NA MEMB_STAT --
--======================================================--
UPDATE MEMB_STAT SET ConnectEndTime = DATEDIFF(s, '19700101', GETDATE()) WHERE memb___id = @memb___id;
--==============================================================--
-- SELECIONANDO HORÁRIO DE LOGIN E DE LOGGOUT NA MEMB_STAT --
--==============================================================--
SET @LOGINTIME = (SELECT DATEDIFF(s, GETDATE(), DATEADD(s, CAST(MEMB_STAT.ConnectStartTime as int), '19700101')) FROM MEMB_STAT WHERE memb___id = @memb___id);
SET @LOGOUTTIME = (SELECT DATEDIFF(s, GETDATE(), DATEADD(s, CAST(MEMB_STAT.ConnectEndTime as int), '19700101')) FROM MEMB_STAT WHERE memb___id = @memb___id);
--==============================--
-- EXECUTANDO OPERAÇÃO --
--==============================--
SET @TIMEON = @LOGOUTTIME - @LOGINTIME;
--==============================================--
-- ATUALIZANDO TEMPO ONLINE NA MEMB_STAT --
--==============================================--
UPDATE MEMB_STAT SET TimeON = TimeON + @TIMEON WHERE memb___id = @memb___id;
END
END
com isso salva o tempo online em segundos mas pode alterar!
Para listar o tempo online no php :
Código PHP:
<?php
$findSTAT = mssql_query("SELECT * FROM MEMB_STAT WHERE memb___id='".$userName."'");
$fetchSTAT = mssql_fetch_object($findSTAT);
$seconds = $fetchSTAT->TimeON;
$hours = floor($seconds / 3600);
$seconds -= $hours * 3600;
$minutes = floor($seconds / 60);
$seconds -= $minutes * 60;
$timeON = (''.$hours.'h'.$minutes.'min'.$seconds.'sec');
echo $timeON;
?>
Para colocar o premio e so alterar as proceduro disconnect do jeito que quiser
boa sorte amigo!