[Script request] Unique visits

[Bonecrusher]

Looking for a script which will calculate number of unique visits on my servers. Create a separate file with unique hwids, nicks and ips. Would be nice to access it via command while in game.

Unique visits today
yesterday
this week
this month

[ExHunter]

On SC2 or SC3?

If SC3 you gotta wait if some things get fixed.

[Bonecrusher]

SC2 preferably.

[SyavX]

Code: (Pascal) [Select]

function CheckDatabaseUpdate(Database: Integer; Query: PChar): Integer;
var
    Check: Integer;
begin
    // DEBUG MESSAGE
    //WriteLn('>'+Query);
       
    Check:= DB_Update(Database, Query);
   
    if Check = 0 then
        WriteLn('Error: '+DB_Error());
   
    Result:= Check;
end;


procedure InsertRecord(ip, hwid, name: String);
var
    db: String;
    created: Boolean;
begin
    db:= 'visits.db';
   
    if Not FileExists(db) then
    begin
        created:= WriteFile(db,'');
        if created then
            WriteLn('Database file "'+db+'" has been created...')
        else
            WriteLn('Cannot create database file "'+db+'"');
    end;
   
    if (DB_Open(0, db, '', '', DB_Plugin_SQLite) <> 0) then
    begin
        if created then
            CheckDatabaseUpdate(0, 'CREATE TABLE IF NOT EXISTS visits(timestamp, ip, hwid, name);');
       
        //escaping " (double quotes)
        ip  := StrReplace(ip,   #34, #34#34);
        hwid:= StrReplace(hwid, #34, #34#34);
        name:= StrReplace(name, #34, #34#34);
       
        CheckDatabaseUpdate(0, 'INSERT INTO visits(timestamp, ip, hwid, name) VALUES(datetime("now", "localtime"),"'+ip+'","'+hwid+'","'+name+'");');
       
        DB_Close(0);
    end
    else
        WriteLn('Database "'+db+'" cannot be opened!');
end;


procedure UniqueVisits(action, period: String);
var
    db, query, modifier: String;
    export_text, export_path: String;
    visits: Integer;
begin
    db:= 'visits.db';
   
    if DB_Open(0, db, '', '', DB_Plugin_SQLite) <> 0 then
    begin
        period:= LowerCase(Trim(period));
        action:= LowerCase(Trim(action));
       
        // http://www.sqlite.org/lang_datefunc.html
        if ((period = 'day') or (period = 'month') or (period = 'year')) then
            modifier:= '"start of '+period+'"'
        else if (period = 'week') then
            modifier:= '"weekday 0", "-7 days"'
        else
        begin
            period:='day';
            modifier:= '"start of '+period+'"';
        end;
       
        // SHOW TO CONSOLE
        if (action = 'show') then
        begin
            query:= 'SELECT COUNT(hwid) FROM ( SELECT DISTINCT ip, hwid, name FROM visits WHERE timestamp BETWEEN datetime("now", "localtime", '+modifier+') AND datetime("now", "localtime") );';
           
            visits:= 0;
            if DB_Query(0, query) <> 0 then
            begin
                while DB_NextRow(0) <> 0 do
                begin
                    visits:= DB_GetLong(0, 0);
                    break;
                end;
               
                DB_FinishQuery(0);
            end;
           
            WriteLn(IntToStr(visits)+' unique visits last '+period);
        end
        // SAVE TO FILE
        else if (action = 'save') then
        begin
            query:= 'SELECT DISTINCT ip, hwid, name FROM visits WHERE timestamp BETWEEN datetime("now", "localtime", '+modifier+') AND datetime("now", "localtime");';
           
            if DB_Query(0, query) <> 0 then
            begin
                export_text:= '';
                while DB_NextRow(0) <> 0 do
                    export_text:= export_text + DB_GetString(0, 0) +#9+ DB_GetString(0, 1) +#9+ DB_GetString(0, 2) + #13#10;
               
                DB_FinishQuery(0);
               
                export_path:= 'visits_'+period+'.txt';
                if (WriteFile(export_path, export_text)) then
                    WriteLn('Data has been saved to '+export_path)
                else
                    WriteLn('Error occured while writing to '+export_path+' file!');
            end
            else
            begin
                WriteLn('An error has occured!');
                WriteLn('Query: '+query);
                WriteLn('Description: '+DB_Error());
            end;
        end;
       
        DB_Close(0);
    end
    else
        WriteLn('Database "'+db+'" cannot be opened!');
end;


procedure OnJoinGame(ID, Team: Byte);
begin
    InsertRecord(GetPlayerStat(ID, 'IP'), GetPlayerStat(ID, 'HWID'), GetPlayerStat(ID, 'Name'));
end;


procedure OnAdminMessage(IP, Msg: String);
begin
    Msg:= LowerCase(Trim(Msg));
   
    if (Msg = '/visits') then
        UniqueVisits('show', 'day')
    else if (GetPiece(Msg, ' ', 0) = '/visits') then
        if ((GetPiece(Msg, ' ', 1) = 'show') or (GetPiece(Msg, ' ', 1) = 'save')) then
            UniqueVisits(GetPiece(Msg, ' ', 1), GetPiece(Msg, ' ', 2))
        // DEBUG COMMAND
        else if (GetPiece(Msg, ' ', 1) = 'add') then
            InsertRecord(IP, 'HWIDEXAMPLE', 'Remote "Dummy" Admin'+IntToStr(Random(1, 4)));
   
    {   Command format: /visits [show|save [day|week|month|year]]
        /visits
        /visits show
        /visits show month
        /visits save week
        etc.
    }
end;
Requirements: libdb

Command format: /visits [show|save [day|week|month|year]]
        /visits
        /visits show
        /visits show month
        /visits save week


Notes: Use /visits add command to add test data.

[Bonecrusher]

Thanks a lot for the effort but I dont think I can run soldatserver without safemode.

[ExHunter]

so, this script is only quickly tested with myself alone. so it MAY contain bugs, but I don't think so.

features:
- checks for hwid (tested)
- every monday script will reset weekly counter (untested)
- every 1st of the month script will reset monthly counter (untested)
- '/visits' command and every 5 minutes a message (tested)

I don't know how the script will behave with alot of HWIDs.. so keep me up to date maybe.

and don't rename/edit any file names (especially the .sdb files).

~ExHunter

[Bonecrusher]

Thanks a lot, currently running on 5 servers.