Скрипт статистики, Verlihub

----------------------------------------------
-- userstats.lua [1.1] for VerliHub (lua 5) --
-- By S_talker (stalker@uch.net) 05.02.2008 --
----------------------------------------------

DebugFlag = 1
CheckTime = 60*5
CheckTimeDaily = 60*60*24
DeleteOldTime = 60*60*24*30
norm = 10000000000000

--SQL table name
SqlTable =  "bot_userstats"
SqlTableDaily =  "bot_userstats_daily"
SqlTableOverall =  "bot_userstats_overall"

function Main()
    local res, err
    sql = "CREATE TABLE IF NOT EXISTS "..SqlTable.." (id bigint NOT NULL auto_increment, nick varchar(30) NOT NULL, currenttime datetime NOT NULL default 0, description varchar(255) NOT NULL default '', slots tinyint NOT NULL, onlinetime int NOT NULL default 0, avgshare bigint NOT NULL default 0, maxshare bigint NOT NULL default 0, lastshare bigint NOT NULL default 0, rating bigint NOT NULL default 0, PRIMARY KEY (id))"
    res, err = SQ(sql)
    sql = "CREATE TABLE IF NOT EXISTS "..SqlTableDaily.." (id bigint NOT NULL auto_increment, nick varchar(30) NOT NULL, currenttime datetime NOT NULL default 0, description varchar(255) NOT NULL default '', slots tinyint NOT NULL, onlineusers int NOT NULL, share bigint NOT NULL default 0, PRIMARY KEY (id))"
    res, err = SQ(sql)
    sql = "CREATE TABLE IF NOT EXISTS "..SqlTableOverall.." (id bigint NOT NULL auto_increment, nick varchar(30) NOT NULL, description varchar(255) NOT NULL default '', slots tinyint NOT NULL, onlinetime int NOT NULL default 0, avgshare bigint NOT NULL default 0, maxshare bigint NOT NULL default 0, lastshare bigint NOT NULL default 0, rating bigint NOT NULL default 0, PRIMARY KEY (id))"
    res, err = SQ(sql)
end

--variables for timer
TimeUnit = 0

function VH_OnTimer()
    TimeUnit = TimeUnit + 1
    if TimeUnit >= CheckTime then
        TimeUnit = 0

        HubSecurity = GetHubSecurity()
    OpChat = GetOpChat()
    
        _, users = VH:GetNickList()
    _, _, users = string.find(users, "%$[Nn]ick[Ll]ist%s(.*)")
    UserCount = 0
    for nick in string.gfind(users, "([^%$%|]+)%$%$") do
        if nick ~= HubSecurity and nick ~= OpChat then
        UserCount = UserCount + 1
        end
    end
    for nick in string.gfind(users, "([^%$%|]+)%$%$") do
        if nick ~= HubSecurity and nick ~= OpChat then
        _, MyInfo = VH:GetMyINFO(nick)
            _, _, Tag = string.find(MyInfo, "^%$MyINFO %$ALL [^ ]+ [^$]*(%b<>)")
            if Tag == nil then
            Slots = 0
            _, _, Description, Share = string.find(MyInfo, "%$MyINFO %$ALL [^ ]+ ([^$]*)%$.*%$([%d]+)%$")
        else
            _, _, Description, Slots, Share = string.find(MyInfo, "%$MyINFO %$ALL [^ ]+ ([^$]*)%<[^$]*,S:([%d]+).*%>%$.*%$([%d]+)%$")
        end;
        Description = string.gsub(Description, "%'", "''")
        SQ("INSERT INTO "..SqlTableDaily.." (nick, currenttime, description, slots, share, onlineusers) VALUES ('"..nick.."', NOW(), '"..Description.."', "..Slots..", "..Share..", "..UserCount..")")
        end
    end

    _, rows = SQ("SELECT * FROM "..SqlTableDaily.." WHERE currenttime < DATE_SUB(NOW(),INTERVAL "..CheckTimeDaily.." SECOND)")
    if rows > 0 then
    nicks = ""
    res, rows = SQ("SELECT DISTINCT nick FROM "..SqlTableDaily)
    for i=0, rows-1 do
        res, nick = SF(i)
        nicks = nicks.." "..nick
    end
    for nick in string.gfind(nicks, "([^ ]+)") do
        local res, rows
        res, rows = SQ("SELECT description, slots, share FROM "..SqlTableDaily.." WHERE nick = '"..nick.."' ORDER BY currenttime DESC LIMIT 1")
        res, Description, Slots, LastShare = SF(0)
        res, rows = SQ("SELECT MAX(share) FROM "..SqlTableDaily.." WHERE nick = '"..nick.."'")
        res, MaxShare = SF(0)
        res, rows = SQ("SELECT COUNT(*) FROM "..SqlTableDaily.." WHERE nick = '"..nick.."'")
        res, OnlineTime = SF(0)
        OnlineTime = OnlineTime * CheckTime
        AvgShare = 0
        Rating = 0
        res, rows = SQ("SELECT share, slots, onlineusers FROM "..SqlTableDaily.." WHERE nick = '"..nick.."'")
        for j=0, rows-1 do
        res, Share, Slots, OnlineUsers = SF(j)
        AvgShare = AvgShare + Share
        k = 1 - 1 / (Slots + 1)
        Rating = Rating + Share * OnlineUsers * k
        end
        if rows > 0 then
        AvgShare = AvgShare / rows
        end
        Description = string.gsub(Description, "%'", "''")
        SQ("INSERT INTO "..SqlTable.." (nick, currenttime, description, slots, onlinetime, avgshare, maxshare, lastshare, rating) VALUES ('"..nick.."', NOW(), '"..Description.."', "..Slots..", "..OnlineTime..", "..AvgShare..", "..MaxShare..", "..LastShare..", "..Rating..")")
    end

    SQ("TRUNCATE TABLE "..SqlTableDaily)
    SQ("TRUNCATE TABLE "..SqlTableOverall)
    SQ("DELETE FROM "..SqlTable.." WHERE currenttime < DATE_SUB(NOW(),INTERVAL "..DeleteOldTime.." SECOND)")

    nicks = ""
    res, rows = SQ("SELECT DISTINCT nick FROM "..SqlTable)
    for i=0, rows-1 do
        res, nick = SF(i)
        nicks = nicks.." "..nick
    end
    for nick in string.gfind(nicks, "([^ ]+)") do
        local res, rows
        res, rows = SQ("SELECT description, slots, lastshare FROM "..SqlTable.." WHERE nick = '"..nick.."' ORDER BY currenttime DESC LIMIT 1")
        res, Description, Slots, LastShare = SF(0)
        res, rows = SQ("SELECT MAX(maxshare) FROM "..SqlTable.." WHERE nick = '"..nick.."'")
        res, MaxShare = SF(0)
        res, rows = SQ("SELECT SUM(onlinetime) FROM "..SqlTable.." WHERE nick = '"..nick.."'")
        res, OnlineTime = SF(0)
        res, rows = SQ("SELECT AVG(avgshare), SUM(rating) FROM "..SqlTable.." WHERE nick = '"..nick.."'")
        res, AvgShare, Rating = SF(0)
        Description = string.gsub(Description, "%'", "''")
        SQ("INSERT INTO "..SqlTableOverall.." (nick, description, slots, onlinetime, avgshare, maxshare, lastshare, rating) VALUES ('"..nick.."', '"..Description.."', "..Slots..", "..OnlineTime..", "..AvgShare..", "..MaxShare..", "..LastShare..", "..Rating..")")
    end

    end
    
    end
end

function VH_OnUserCommand(nick, data)
    if string.find(data,"%+toplist") then
    res, _, a, b = string.find(data,"%+toplist ([%d]+)-([%d]+)")
    if not res then
        a = 1
        b = 30
    end
    ShowTopList(nick, a, b)
    return 0
    elseif string.find(data,"%+rating") then
    res, _, infonick = string.find(data,"%+rating (.+)")
    if not res then
        infonick = nick
    end
    ShowRating(nick, infonick)
    return 0
    end
    return 1
end

function ShowTopList(nick, a, b)
    HubSecurity = GetHubSecurity()
    res, rows = SQ("SELECT nick, rating/"..norm.." FROM "..SqlTableOverall.." ORDER BY rating DESC LIMIT "..(a-1)..", "..(b-a+1))
    if res then
    VH:SendDataToUser("<"..HubSecurity.."> Рейтинг пользователей", nick)
    for i=0, rows-1 do
        res, infonick, rating = SF(i)
        rating = string.format("%d", rating or 0)
        VH:SendDataToUser("<"..HubSecurity.."> "..(a+i)..".\t"..infonick.." -- "..rating, nick)
    end
    else
    VH:SendDataToUser("<"..HubSecurity.."> Ошибка при выполнении запроса ", nick)
    end
end

function ShowRating(nick, infonick)
    HubSecurity = GetHubSecurity()
    Rated = SQ("SELECT nick, description, slots, onlinetime, avgshare, maxshare, lastshare, rating/"..norm.." FROM "..SqlTableOverall.." WHERE nick='"..infonick.."'")
    if Rated then
    _, infonick, Description, Slots, OnlineTime, AvgShare, MaxShare, LastShare, Rating = SF(0)
    Rating = string.format("%d", Rating or 0)
    end
    Regged = SQ("SELECT class, reg_date, reg_op, login_last, login_cnt, login_ip, email, note_op, note_usr FROM reglist WHERE nick='"..infonick.."'")
    if Regged then
        _, Class, RegDate, RegOp, LoginLast, LoginCnt, LoginIP, EMail, NoteOp, NoteUsr = SF(0)
    else
        Class = 0
    end
    if Rated then
    res, rows = SQ("SELECT nick FROM "..SqlTableOverall.." ORDER BY rating DESC")
    Place = 1
    Found = false
    for i=0, rows-1 do
        res, usernick = SF(i)
        if not Found then
        if infonick == usernick then
            Found = true
        else
            Place = Place + 1
        end
        end
    end
    FirstLogin = 0
    if SQ("SELECT TO_DAYS(NOW()) - TO_DAYS(currenttime) FROM "..SqlTable.." WHERE nick = '"..infonick.."' ORDER BY currenttime ASC") then
        _, Days = SF(0)
    end
    VH:SendDataToUser("<"..HubSecurity.."> -----------------------------------------------------------------------", nick)
    VH:SendDataToUser("<"..HubSecurity.."> Информация о пользователе "..infonick, nick)
    VH:SendDataToUser("<"..HubSecurity.."> -----------------------------------------------------------------------", nick)
    VH:SendDataToUser("<"..HubSecurity.."> Рейтинг: "..Rating.." ("..Place.." место)", nick)
    VH:SendDataToUser("<"..HubSecurity.."> Класс: "..Class, nick)
    if Description and Description ~= "" then
        VH:SendDataToUser("<"..HubSecurity.."> Описание: "..Description, nick)
    end
    if Slots and Slots ~= "0" then
        VH:SendDataToUser("<"..HubSecurity.."> Слотов: "..Slots, nick)
    end
    VH:SendDataToUser("<"..HubSecurity.."> Средний размер шары: "..string.format("%.2f", AvgShare and AvgShare/1024/1024/1024 or 0).." ГБ", nick)
    VH:SendDataToUser("<"..HubSecurity.."> Максимальный размер шары: "..string.format("%.2f", MaxShare and MaxShare/1024/1024/1024 or 0).." ГБ", nick)
    VH:SendDataToUser("<"..HubSecurity.."> Последний размер шары: "..string.format("%.2f", LastShare and LastShare/1024/1024/1024 or 0).." ГБ", nick)
    VH:SendDataToUser("<"..HubSecurity.."> Среднесуточное время в онлайне: "..FormatTime(OnlineTime and OnlineTime/(Days+1) or 0), nick)
    else
    VH:SendDataToUser("<"..HubSecurity.."> Пользователя "..infonick.." нет в рейтинговой таблице", nick)
    end
end

-- Get time string from seconds (HH:MM)
function FormatTime(Seconds)
    Minutes = Seconds/60
    Hours = math.floor(Minutes/60)
    Minutes = math.mod(math.floor(Minutes),60)
    return Hours..":"..string.format("%02d", Minutes or 0)
end

-- Get the Class of specified User
function GetClass(nick)
       res, class = VH:GetUserClass(nick)
       return class
end

-- Message to OpChat
function MsgToOpChat(Data)
       return VH:SendPMToUser(Data, Bot, GetOpChat())
end

-- Get HubSecurity Name
function GetHubSecurity()
       res, val = VH:GetConfig("config", "hub_security")
       return val
end

-- Get OpChat Name
function GetOpChat()
       res, val = VH:GetConfig("config", "opchat_name")
       return val
end

-- Debugging purposes
function Debug(Str)
    if DebugFlag then
    VH:SendDataToUser(Str, "Admin")
    end
    return Str
end

function SQ(sql)
    return VH:SQLQuery(sql);
end;

function SF(row)
    return VH:SQLFetch(row);
end;