|
Senior Member
|
09-24-2023
, 23:18
Re: rank sqlite
|
#4
|
Quote:
Originally Posted by yagami
My mistake for not sending everything
PHP Code:
#include <amxmodx>
#include <hamsandwich>
#include <regex>
#include <sqlx>
#include <jctf>
#define SQLITE_DATABASE_NAME "clan_management"
#define TOP_PLAYERS_LENGTH 15
#define TOP_CLANS_LENGTH 10
enum _:PlayerEnum
{
bool:pIsAuthorized,
bool:pIsConnected,
bool:pIsAuthenticated,
bool:pIsWaitingLoginAttemptResponse,
bool:pRegisterPlayerIfNotFound,
bool:pShowRegisterOption,
pName[MAX_NAME_LENGTH],
pAuthid[MAX_AUTHID_LENGTH],
pIP[MAX_IP_LENGTH],
pLogin[128],
pPassword[128],
pJoinedClanAtDate[12],
pInviteCode[MAX_INVITE_CODE_LENGTH],
pTemporaryClanName[MAX_CLAN_NAME_LENGTH],
pTemporaryClanTag[MAX_CLAN_TAG_LENGTH],
pClanId,
pClanPrivilegies,
pID,
pXP,
pKills,
pDeaths,
pCaptures,
pDefenses,
}
public Player[MAX_PLAYERS + 1][PlayerEnum];
enum _:TopPlayersEnum
{
tpClanName[MAX_CLAN_NAME_LENGTH],
tpClanTag[MAX_CLAN_TAG_LENGTH],
tpName[MAX_NAME_LENGTH],
tpAuthid[MAX_AUTHID_LENGTH],
tpIP[MAX_IP_LENGTH],
tpXP,
tpKills,
tpDeaths,
tpCaptures,
tpDefenses,
tpPosition
}
enum _:TopClansEnum
{
tcName[MAX_CLAN_NAME_LENGTH],
tcTag[MAX_CLAN_TAG_LENGTH],
tcMembersCount,
tcTotalXP,
tcTotalKills,
tcTotalDeaths,
tcTotalCaptures,
tcTotalDefenses,
tcPosition
}
public jctf_flag(iEvent, id, iFlagTeam, bool:bAssist)
{
new top[TOP_PLAYERS_LENGTH][TopPlayersEnum]
switch(iEvent)
{
case FLAG_CAPTURED:
{
if(!bAssist)
{
top[id][pCaptures]++
}
log("OnTop15Rank(id: %d) total: %d", id, top[id][pCaptures]);
}
case FLAG_STOLEN:
{
if(!bAssist)
{
top[id][tpDefenses]++
}
log("OnTop15Rank(id: %d) total: %d", id, top[id][pCaptures]);
}
}
savePlayerData(id)
}
public OnTop15Command(id)
{
new top[TOP_PLAYERS_LENGTH][TopPlayersEnum];
new total = SQLQuery_GetTopPlayers(top, sizeof top);
for (new i = 0; i < total; i++)
{
/*
Variáveis disponíveis:
top[i][tpClanName]
top[i][tpClanTag]
top[i][tpName]
top[i][tpAuthid]
top[i][tpIP]
top[i][tpXP]
top[i][tpKills]
top[i][tpDeaths]
top[i][tpCaptures]
top[i][tpDefenses]
top[i][tpPosition]
*/
console_print(id, "%dº | %s | %d/%d", top[i][tpPosition], top[i][tpName], top[i][tpKills], top[i][tpDeaths]);
}
log("OnTop15Command(id: %d) total: %d", id, total);
}
public OnTopClansCommand(id)
{
new top[TOP_CLANS_LENGTH][TopClansEnum];
new total = SQLQuery_GetTopClans(top, sizeof top);
for (new i = 0; i < total; i++)
{
/*
Variáveis disponíveis:
top[i][tcName]
top[i][tcTag]
top[i][tcMembersCount]
top[i][tcTotalXP]
top[i][tcTotalKills]
top[i][tcTotalDeaths]
top[i][tcTotalCaptures]
top[i][tcTotalDefenses]
top[i][tcPosition]
*/
console_print(id, "%dº | TAG: %s | CLAN: %s | XP: %d | MEMBROS: %d", top[i][tcPosition], top[i][tcName], top[i][tcTag], top[i][tcTotalXP], top[i][tcMembersCount]);
}
log("OnTopClansCommand(id: %d) total: %d", id, total);
}
public client_putinserver(id)
{
Player[id][pIsConnected] = bool:is_user_connected(id);
loadPlayerData(id);
}
public client_authorized(id)
{
Player[id][pIsAuthorized] = true;
loadPlayerData(id);
}
public client_disconnected(id)
{
Player[id][pIsConnected] = false;
savePlayerData(id);
ArrayDestroy(Player[id][pNotifications]);
}
loadPlayerData(id)
{
if (!Player[id][pIsConnected] || !Player[id][pIsAuthorized])
{
log("loadPlayerData(id: %d) => Não conectado ou autorizado. connected: %d, auth: %d", id, Player[id][pIsConnected], Player[id][pIsAuthorized]);
return;
}
get_user_name(id, Player[id][pName], charsmax(Player[][pName]));
get_user_authid(id, Player[id][pAuthid], charsmax(Player[][pAuthid]));
get_user_ip(id, Player[id][pIP], charsmax(Player[][pIP]));
if (!validateCredentials(id))
{
if (Player[id][pIsWaitingLoginAttemptResponse])
{
client_print_color(id, print_team_red, "%s^3 Não foi possível carregar seus dados. Erro ao validar credenciais.", CHAT_TAG);
Player[id][pIsWaitingLoginAttemptResponse] = false;
}
log("loadPlayerData(id: %d) => Erro ao validar credenciais. Tipo: %d", id, cvarAuthenticationType);
return;
}
log("loadPlayerData(id: %d) => Consultando player", id);
SQLQuery_FetchPlayer(id);
}
savePlayerData(id)
{
if (!Player[id][pIsAuthenticated])
{
log("savePlayerData(id: %d) => Salvamento nao autorizado", id);
return;
}
SQLQuery_SavePlayer(id);
}
SQLQuery_SavePlayer(id)
{
formatex(Query, charsmax(Query),
"UPDATE players_stats SET \
xp = %d, \
kills = %d, \
deaths = %d, \
captures = %d, \
defenses = %d \
WHERE \
player_id = %d; \
UPDATE players SET \
last_seen = CURRENT_TIMESTAMP \
WHERE \
id = %d",
Player[id][pXP],
Player[id][pKills],
Player[id][pDeaths],
Player[id][pCaptures],
Player[id][pDefenses],
Player[id][pID],
Player[id][pID]
);
log("SQLQuery_SavePlayer(id: %d) => %s", id, Query);
SQL_ThreadQuery(DatabaseHandle, "@SQLCallback_Ignore", Query);
}
SQLQuery_GetTopPlayers(top[][TopPlayersEnum], length)
{
new Handle:query = SQL_PrepareQuery(DatabaseConnectionHandle,
"SELECT \
c.name, \
c.tag, \
p.name, \
p.authid, \
p.ip, \
s.xp, \
s.kills, \
s.deaths, \
s.captures, \
s.defenses \
FROM players p \
INNER JOIN players_stats s \
ON s.player_id = p.id \
LEFT JOIN clans_members cm \
ON cm.player_id = p.id AND cm.status = 1 \
LEFT JOIN clans c \
ON c.id = cm.clan_id AND c.status = 1 \
ORDER BY \
s.kills DESC, \
s.deaths ASC \
LIMIT %d",
length
);
new i = 0;
if (!SQL_Execute(query))
{
new error[128];
SQL_QueryError(query, error, charsmax(error));
log("SQLQuery_GetTopPlayers() erro na query: %s", error);
return 0;
}
while (SQL_MoreResults(query))
{
SQL_ReadResult(query, 0, top[i][tpClanName], charsmax(top[][tpClanName]));
SQL_ReadResult(query, 1, top[i][tpClanTag], charsmax(top[][tpClanTag]));
SQL_ReadResult(query, 2, top[i][tpName], charsmax(top[][tpName]));
SQL_ReadResult(query, 3, top[i][tpAuthid], charsmax(top[][tpAuthid]));
SQL_ReadResult(query, 4, top[i][tpIP], charsmax(top[][tpIP]));
top[i][tpXP] = SQL_ReadResult(query, 5);
top[i][tpKills] = SQL_ReadResult(query, 6);
top[i][tpDeaths] = SQL_ReadResult(query, 7);
top[i][tpCaptures] = SQL_ReadResult(query, 8);
top[i][tpDefenses] = SQL_ReadResult(query, 9);
top[i][tpPosition] = ++i;
SQL_NextRow(query);
}
return i;
}
SQLQuery_GetTopClans(top[][TopClansEnum], length)
{
new Handle:query = SQL_PrepareQuery(DatabaseConnectionHandle,
"SELECT \
c.name, \
c.tag, \
SUM(s.xp), \
SUM(s.kills), \
SUM(s.deaths), \
SUM(s.captures), \
SUM(s.defenses), \
COUNT(cm.player_id) \
FROM clans c \
INNER JOIN clans_members cm \
ON cm.clan_id = c.id AND cm.status = 1 \
INNER JOIN players_stats s \
ON s.player_id = cm.player_id \
GROUP BY \
c.id \
ORDER BY \
SUM(s.xp) DESC, \
SUM(s.kills) DESC, \
SUM(s.deaths) ASC \
LIMIT %d",
length
);
new i = 0;
if (!SQL_Execute(query))
{
new error[128];
SQL_QueryError(query, error, charsmax(error));
log("SQLQuery_GetTopClans() erro na query: %s", error);
return 0;
}
while (SQL_MoreResults(query))
{
SQL_ReadResult(query, 0, top[i][tcName], charsmax(top[][tcName]));
SQL_ReadResult(query, 1, top[i][tcTag], charsmax(top[][tcTag]));
top[i][tcTotalXP] = SQL_ReadResult(query, 2);
top[i][tcTotalKills] = SQL_ReadResult(query, 3);
top[i][tcTotalDeaths] = SQL_ReadResult(query, 4);
top[i][tcTotalCaptures] = SQL_ReadResult(query, 5);
top[i][tcTotalDefenses] = SQL_ReadResult(query, 6);
top[i][tcMembersCount] = SQL_ReadResult(query, 7);
top[i][tcPosition] = ++i;
SQL_NextRow(query);
}
return i;
}
|
I wanted to gank the best clans based on XP, and also which clan has the most kills, the most flags captured,
as well as the best playewr of each clan
Last edited by yagami; 09-24-2023 at 23:18.
|
|
Linear Mode
