[Release] Database Total Online Time Mod

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 )

PHP Code:

---

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 


---

Approved it is

great release

Quote:

---

Originally Posted by 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 )

PHP Code:

---

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 ?

Quote:

---

Originally Posted by .:[CZ]Memphis:.

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

---

Add another Column in Character table

Same as the one on MEMB_STAT

Code:

---

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 :p tell me if it works

EDIT: just tested it. works fine

I have this problem!
http://img106.imageshack.us/img106/8132/problemz.jpg

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

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

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

or u mean a php page?

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

http://www.gameznetwork.com.ar/Gamez...TopOnline.html

Quote:

---

Originally Posted by 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

http://www.gameznetwork.com.ar/Gamez...TopOnline.html

---

wat the original script in hours?

Quote:

---

<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

looks good. but query is a bit craptastic :p

could easily be

PHP Code:

---

$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>'; 


---

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

Quote:

---

Originally Posted by crlfrancis

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

:p
so everytime u need the character table... just use MULINK.MuOnline.dbo.Character :):lol: