[Release] Database Total Online Time Mod

[john_d]

What this does?
- add a new column in your MEMB_STAT table for total online time for the user EVER!

Does can it count in HOUR, DATE, Minutes?
- yes

Do i need this?
- no idea... it's ur life

What of things i can do with this?
- well i don't really know but i use as referral system on my test server. referral Total Online TIme = Mu CASH ^^

STEPS TO INSTALL

1. NEED TO ADD A NEW COLUMN in MEMB_STAT TABLE
-----
COLUMN NAME: TotalTime
TYPE: int
NOT NULL
DEFAULT 0
-----

2. Change your WZ_DISCONNECT_MEMB ( stored procedure )

CREATE PROCEDURE WZ_DISCONNECT_MEMB
@memb___id varchar(10)
 AS
Begin    
set nocount on
    Declare  @find_id varchar(10)    
    Declare @ConnectStat tinyint
    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(), TotalTime = TotalTime+(DATEDIFF(mi,ConnectTM,getdate()))
         where memb___id = @memb___id
            -- TIMEONLINE MOD by john_d
    end
end

GO

 

[Dios]

Approved it is

 

[DarkMaster.]

great release

 

[.:[CZ]Memphis:.]

What this does?
- add a new column in your MEMB_STAT table for total online time for the user EVER!

Does can it count in HOUR, DATE, Minutes?
- yes

Do i need this?
- no idea... it's ur life

What of things i can do with this?
- well i don't really know but i use as referral system on my test server. referral Total Online TIme = Mu CASH ^^

STEPS TO INSTALL

1. NEED TO ADD A NEW COLUMN in MEMB_STAT TABLE
-----
COLUMN NAME: TotalTime
TYPE: int
NOT NULL
DEFAULT 0
-----

2. Change your WZ_DISCONNECT_MEMB ( stored procedure )

CREATE PROCEDURE WZ_DISCONNECT_MEMB
@memb___id varchar(10)
 AS
Begin    
set nocount on
    Declare  @find_id varchar(10)    
    Declare @ConnectStat tinyint
    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(), TotalTime = TotalTime+(DATEDIFF(mi,ConnectTM,getdate()))
         where memb___id = @memb___id
            -- TIMEONLINE MOD by john_d
    end
end

GO

but it's just for logged account, not character ?

 

[john_d]

but it's just for logged account, not character ?

Add another Column in Character table

Same as the one on MEMB_STAT

CREATE PROCEDURE WZ_DISCONNECT_MEMB
@memb___id varchar(10)
 AS
Begin    
set nocount on
    Declare  @find_id varchar(10)    
   Declare  @con_tm DATETIME
    Declare @ConnectStat tinyint
    Set @ConnectStat = 0     
    Set @find_id = 'NOT'
    select @find_id = S.memb___id, @con_tm = S.ConnectTM 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(), TotalTime = TotalTime+(DATEDIFF(mi,ConnectTM,getdate()))
         where memb___id = @memb___id
            -- TIMEONLINE MOD by john_d
         update character set TotalTime = TotalTime+(DATEDIFF(mi,@con_tm,getdate())) 
         from character as c INNER join AccountCharacter as ac ON 
         c.Name = ac.GameIDC where c.accountid = @memb___id
    end
end

GO

pst dont have time to test it tell me if it works

EDIT: just tested it. works fine

 

[santiz]

I have this problem!

 

[crlfrancis]

Can you write a script to display Top 10 most players online?
You can use this for top online contest

 

[Stinger 7100]

Or you could just leave it and out of the list the top 10 most online. Would be the same?

 

[john_d]

select top 10 * from character where ctl_code = 0 order by TotalTime desc

or u mean a php page?

 

[crlfrancis]

never mind i found one on muweb.org and it works fine with totaltime, i like how it shows the mins instead of hours

You must be registered to see links

 

[john_d]

never mind i found one on muweb.org and it works fine with totaltime, i like how it shows the mins instead of hours

You must be registered to see links

wat the original script in hours?

 

[crlfrancis]

<div align="center">
<div align="center">
<table width="50%" border="0" cellspacing="1" cellpadding="1">
<tr>
<td class="normal_text_white">Players</td>
<td class="normal_text_white">Online Time</td>
<td class="normal_text_white">Status</td>
</tr>
<?php

/**
* @DjSoulz
* @copyright 2008
*/

if( !isset($_GET['op']))
{
require('../includes/denied.php');
denied('Time');
}
require('config.php');

//We get the user
$online = $db->Execute('SELECT DISTINCT
TOP 25
AccountCharacter.ID,
AccountCharacter.GameIDC,
MEMB_STAT.ServerName,
MEMB_STAT.IP,
MEMB_STAT.ConnectTM,
MEMB_STAT.DisConnectTM,
MEMB_STAT.OnlineHours
from
MEMB_STAT
join
AccountCharacter
on
Memb_stat.memb___id = AccountCharacter.ID
ORDER BY
MEMB_STAT.OnlineHours DESC');
while($online_time = $online->fetchrow())
{
//Maybe the statut too
$statut = $db->Execute("Select ConnectStat FROM MEMB_STAT WHERE memb___id='".$online_time[0]."'");
$statut_ok = $statut->fetchrow();
if($statut_ok[0] == 1){$online_stat = '<span style="color:green"><b>Online</b></span>';}
elseif($statut_ok[0] == 0){$online_stat = '<span style="color:red"><b>Offline</b></span>';}
?>
<tr>
<td class="normal_text_white"><a href="index.php?op=character&character=<?=$online_time[1];?>"><?=$online_time[1];?></a></td>
<td class="normal_text_white"><?=$online_time[6];?></td>
<td class="normal_text_white"><?=$online_stat?></td>
</tr>
<?
}
?>
</table>
</div>

there you go
i just changed OnlineHours to TotalOnline to show in mins

 

[john_d]

looks good. but query is a bit craptastic

could easily be

$online = $db->Execute('select top 25 c.Name, c.Class, c.cLevel, ms.ConnectStat,c.TotalTime from Character as C left outer join MEMB_STAT as ms ON c.Accountid = ms.memb___id order by c.TotalTime desc');
echo '<table cellpadding="2" cellspacing="2">';
foreach ($online as $on => $r) {
 echo '<tr><td>'.$r['NAME'].'</td><td>'.$r['TOTALTIME'].' Minutes</td></tr>';
}
echo '</table>';

 

[crlfrancis]

i use me_muonline database for the accounts so that wouldn't work for me

 

[john_d]

i use me_muonline database for the accounts so that wouldn't work for me

Easily fixed. in 2 steps

1. Create a Link servers. let say we call it "MULINK"
Link server info
Provider: OLE DB PROVIDER for ODBC DRIVERS
Product name: MULINK
Datasource: MuOnline;MuOnlineJoinDB;Ranking;
Driver: DRIVER={SQL Server};SERVER=127.0.0.1;UID=;PWD=;

to very if it is working.

Query this:
1. Use Me_MuOnline;
2. Select * FROM MULINK.MuOnline.dbo.Character


so everytime u need the character table... just use MULINK.MuOnline.dbo.Character

 

[crlfrancis]

wish i knew how to do this

 

[Shagoon]

Very nice tweak. Thanks John.

 

[Maxim.T]

Thanks John , very usefull release .

 

[[hidden]]

^^ very useful.

 

[SkyLinE27]

Useful release,Thanks