From 07070d61b51e6521b1a93ecedeb2e694467d9c60 Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Sun, 13 Feb 2011 17:32:55 +0100 Subject: [PATCH] management of placements via postgresql --- TODO.txt | 7 +- web/briskin5/Obj/placing.phh | 141 ++++++++++++++++++++++++++++++++--- web/briskin5/statadm.php | 134 ++++++++++++++++++++++++++++++--- 3 files changed, 262 insertions(+), 20 deletions(-) diff --git a/TODO.txt b/TODO.txt index 3ccdf6f..3922384 100644 --- a/TODO.txt +++ b/TODO.txt @@ -10,8 +10,11 @@ DONE - travasatore file to pg DONE - travaso punteggi DONE - memorizzazione nuovi i punteggi nel db - - gestione classifiche in SQL - + DONE - gestione classifiche in SQL + DONE - produrle + DONE - mostrarle + + STEP 1.5 - sistema di inserimento nuovi utenti (riversatore da form di inserimento) STEP 2 diff --git a/web/briskin5/Obj/placing.phh b/web/briskin5/Obj/placing.phh index f068899..31b97a3 100644 --- a/web/briskin5/Obj/placing.phh +++ b/web/briskin5/Obj/placing.phh @@ -78,7 +78,7 @@ class Ptsgam { else return ($this->pts / $this->gam); } -} +} // class Ptsgam { function ptsgam_cmp($a, $b) { @@ -95,6 +95,16 @@ function ptsgam_cmp($a, $b) return (($norma < $normb) ? 1 : -1); } +/* types of placing based on delta time */ +define(TY_DTIME_TRI, 0); +define(TY_DTIME_MON, 1); +define(TY_DTIME_WEE, 2); + +/* subtypes of placing based on number of played games */ +define(SUBTY_FREQ_LO, 0); +define(SUBTY_FREQ_HI, 1); + + function placings_show(&$user) { $mtime = placing_time(); @@ -102,23 +112,23 @@ function placings_show(&$user) $ret = sprintf("

CLASSIFICHE

(aggiornate alle ore %s del %s)", $tm[0], $tm[1]); $tmwee = placing_date($mtime - WEE_LIMIT + (3600)); - $ret .= sprintf("", $tmwee[1], $tm[1], WEE_MAX_GAMES, placing_show($user, "wee_hi") ); - $ret .= sprintf("\n", $tmwee[1], $tm[1], WEE_MAX_GAMES, WEE_MIN_GAMES, placing_show($user, "wee_lo") ); + $ret .= sprintf("", $tmwee[1], $tm[1], WEE_MAX_GAMES, placing_show($user, TY_DTIME_WEE, SUBTY_FREQ_HI) ); + $ret .= sprintf("\n", $tmwee[1], $tm[1], WEE_MAX_GAMES, WEE_MIN_GAMES, placing_show($user, TY_DTIME_WEE, SUBTY_FREQ_LO) ); $tmmon = placing_date($mtime - MON_LIMIT + (3600)); - $ret .= sprintf("", $tmmon[1], $tm[1], MON_MAX_GAMES, placing_show($user, "mon_hi") ); - $ret .= sprintf("\n", $tmmon[1], $tm[1], MON_MAX_GAMES, MON_MIN_GAMES, placing_show($user, "mon_lo") ); + $ret .= sprintf("", $tmmon[1], $tm[1], MON_MAX_GAMES, placing_show($user, TY_DTIME_MON, SUBTY_FREQ_HI) ); + $ret .= sprintf("\n", $tmmon[1], $tm[1], MON_MAX_GAMES, MON_MIN_GAMES, placing_show($user, TY_DTIME_MON, SUBTY_FREQ_LO) ); $tmtri = placing_date($mtime - TRI_LIMIT + (3600)); - $ret .= sprintf("", $tmtri[1], $tm[1], TRI_MAX_GAMES, placing_show($user, "tri_hi")); - $ret .= sprintf("", $tmtri[1], $tm[1], TRI_MAX_GAMES, TRI_MIN_GAMES, placing_show($user, "tri_lo")); + $ret .= sprintf("", $tmtri[1], $tm[1], TRI_MAX_GAMES, placing_show($user, TY_DTIME_TRI, SUBTY_FREQ_HI)); + $ret .= sprintf("", $tmtri[1], $tm[1], TRI_MAX_GAMES, TRI_MIN_GAMES, placing_show($user, TY_DTIME_TRI, SUBTY_FREQ_LO)); $ret .= sprintf("

Settimanale
dal %s al %s
(non meno di %d partite)

%s

Settimanale
dal %s al %s
(meno di %d partite, più di %d)

%s

Settimanale
dal %s al %s
(non meno di %d partite)

%s

Settimanale
dal %s al %s
(meno di %d partite, più di %d)

%s

Mensile
dal %s al %s
(non meno di %d partite)

%s

Mensile
dal %s al %s
(meno di %d partite, più di %d)

%s

Mensile
dal %s al %s
(non meno di %d partite)

%s

Mensile
dal %s al %s
(meno di %d partite, più di %d)

%s

Trimestrale
dal %s al %s
(non meno di %d partite)

%s

Trimestrale
dal %s al %s
(meno di %d partite, più di %d)

%s

Trimestrale
dal %s al %s
(non meno di %d partite)

%s

Trimestrale
dal %s al %s
(meno di %d partite, più di %d)

%s
"); return ($ret); } -function placing_time() +function placing_time_file() { if (($fp = @fopen(LEGAL_PATH."/class_wee_lo.log", 'r')) == FALSE) { return (FALSE); @@ -129,15 +139,65 @@ function placing_time() return ( $st['mtime'] ); } +function placing_time_pgsql() +{ + GLOBAL $G_dbpfx; + $bdb = new BriskDB(); + + $mti_sql = sprintf("SELECT CAST(EXTRACT(EPOCH FROM mtime) AS INTEGER) as mtime + FROM %sbin5_places_mtime WHERE code = 0;", $G_dbpfx); + + if (($mti_pg = pg_query($bdb->dbconn->db(), $mti_sql)) == FALSE || pg_numrows($mti_pg) == 0) { + // no point found, abort + log_crit("placing: get placing mtime failed [$mti_sql]"); + return (FALSE); + } + + $mti_pg = pg_fetch_object($mti_pg, 0); + + return ($mti_pg->mtime); +} + +function placing_time() +{ + GLOBAL $G_dbasetype; + + $fun_name = "placing_time_${G_dbasetype}"; + + return ($fun_name()); +} + function placing_date($mtime) { return array( date('G:i', $mtime), date('j/n/y', $mtime) ); } -function placing_show(&$user, $suff) +function placing_show_file(&$user, $ty, $subty) { $tail = FALSE; + + $suff = ""; + switch($ty) { + case TY_DTIME_TRI: + $suff = "tri_"; + break; + case TY_DTIME_MON: + $suff = "mon_"; + break; + case TY_DTIME_WEE: + $suff = "wee_"; + break; + } + + switch($subty) { + case TY_FREQ_LO: + $suff .= "lo"; + break; + case TY_FREQ_HI: + $suff .= "hi"; + break; + } if (($fp = @fopen(LEGAL_PATH."/class_".$suff.".log", 'r')) == FALSE) { return (FALSE); @@ -184,3 +244,66 @@ function placing_show(&$user, $suff) return ($ret); } +// +// + +function placing_show_pgsql(&$user, $ty, $subty) +{ + GLOBAL $G_dbpfx; + + $bdb = new BriskDB(); + + if ($user != FALSE) { + $pla_sql = sprintf("SELECT * from %sbin5_places where type = %d AND (rank <= %d OR ucode = '%s');", + $G_dbpfx, ($ty * 2) + $subty, TOP_NUM, escsql($user->code)); + } + else { + $pla_sql = sprintf("SELECT * from %sbin5_places where type = %d AND rank <= %d;", + $G_dbpfx, ($ty * 2) + $subty, TOP_NUM); + } + + if (($pla_pg = pg_query($bdb->dbconn->db(), $pla_sql)) == FALSE || pg_numrows($pla_pg) == 0) { + // no point found, abort + log_crit("placing: get placing list failed [$pla_sql]"); + return (""); + } + + // MLANG + $ret = sprintf(""); + + for ($i = 0 ; $i < pg_numrows($pla_pg) ; $i++) { + $pla_obj = pg_fetch_object($pla_pg,$i); + + $ein = ""; + $eou = ""; + if ($user != FALSE) { + if ($user->code == $pla_obj->ucode) { + $ein = ""; + $eou = ""; + } + } + + /* when the user is far from the top-ten we place a ... separator before it */ + if ($pla_obj->rank > TOP_NUM) { + $ret .= sprintf(""); + } + $ret .= sprintf("", $pla_obj->rank, + $ein, xcape($pla_obj->login), $eou, + $ein, $pla_obj->score, $eou, + $ein, $pla_obj->pts, $pla_obj->games, $eou); + } + $ret .= "
Pos.UtenteScore(Punti/Partite)
. . . . . . . . . .
%d%s%s%s%s%12.3f%s%s(%d/%d)%s
"; + + return ($ret); +} + +function placing_show(&$user, $ty, $subty) +{ + GLOBAL $G_dbasetype; + + $fun_name = "placing_show_${G_dbasetype}"; + + return ($fun_name($user, $ty, $subty)); +} + +?> \ No newline at end of file diff --git a/web/briskin5/statadm.php b/web/briskin5/statadm.php index 96b79cd..3c3e954 100644 --- a/web/briskin5/statadm.php +++ b/web/briskin5/statadm.php @@ -39,21 +39,16 @@ ini_set("max_execution_time", "240"); require_once("../Obj/brisk.phh"); require_once("../Obj/auth.phh"); +require_once("../Obj/dbase_${G_dbasetype}.phh"); require_once("Obj/briskin5.phh"); require_once("Obj/placing.phh"); -function main() +function main_file($curtime) { - GLOBAL $pazz, $G_alarm_passwd; $tri = array(); $mon = array(); $wee = array(); - echo "inizio
"; - flush(); - if ($pazz != $G_alarm_passwd) - exit; - if (($fp = @fopen(LEGAL_PATH."/points.log", 'r')) == FALSE) { echo "Open data file error"; exit; @@ -78,7 +73,6 @@ function main() } // recalculate all the placings - $curtime = time(); // 1246428948|492e4e9e856b0|N|tre|172.22.1.90|STAT:BRISKIN5:FINISH_GAME|4a4afd4983039|6|3|tre|1|due|2|uno|-1| while (!feof($fp)) { $p = 0; @@ -214,8 +208,130 @@ function main() fclose($fphi); fclose($fplo); +} + +function main_pgsql($curtime) +{ + GLOBAL $G_dbpfx; + + $bdb = new BriskDB(); + + $limi = array( TRI_LIMIT, MON_LIMIT, WEE_LIMIT ); + $ming = array( TRI_MIN_GAMES, MON_MIN_GAMES, WEE_MIN_GAMES ); + $maxg = array( TRI_MAX_GAMES, MON_MAX_GAMES, WEE_MAX_GAMES ); + + do { + if (pg_query($bdb->dbconn->db(), "BEGIN") == FALSE) { + log_crit("statadm: begin failed"); + break; + } + + // Truncate table (postgresql extension, in other SQL you must user unqualified DELETE + $tru_sql = sprintf("TRUNCATE %sbin5_places;", $G_dbpfx); + if (pg_query($bdb->dbconn->db(), $tru_sql) == FALSE) { + log_crit("statadm: truncate failed"); + break; + } + + for ($dtime = 0 ; $dtime < count($limi) ; $dtime++) { + $old_score = array( 1000000000, 1000000000); + $old_gam = array( -1, -1); + $rank = array( 0, 0); + + $pla_sql = sprintf("SELECT (float4(sum(p.pts)) * 100.0 ) / float4(count(p.pts)) as score, sum(p.pts) as points, count(p.pts) as games, u.code as ucode, u.login as login + FROM %sbin5_points as p, %sbin5_games as g, %sbin5_matches as m, %susers as u + WHERE p.ucode = u.code AND p.gcode = g.code AND g.mcode = m.code AND + g.tstamp > to_timestamp(%d) + GROUP BY u.code, u.login + ORDER BY (float4(sum(p.pts)) * 100.0 ) / float4(count(p.pts)) DESC, + count(p.pts) DESC", + $G_dbpfx, $G_dbpfx, $G_dbpfx, $G_dbpfx, $curtime - $limi[$dtime], $ming[$dtime]); + + // log_crit("statadm: INFO: [$pla_sql]"); + + if (($pla_pg = pg_query($bdb->dbconn->db(), $pla_sql)) == FALSE || pg_numrows($pla_pg) == 0) { + // no point found, abort + log_crit("statadm: main placement select failed [$pla_sql]"); + break; + } + + for ($i = 0 ; $i < pg_numrows($pla_pg) ; $i++) { + $pla_obj = pg_fetch_object($pla_pg,$i); + if ($pla_obj->games < $ming[$dtime]) + continue; - echo "FINITO\n"; + if ($pla_obj->games < $maxg[$dtime]) + $subty = 0; + else + $subty = 1; + + $ty = ($dtime * 2) + $subty; + + if ($pla_obj->games != $old_gam[$subty] || $pla_obj->score != $old_score[$subty]) { + $rank[$subty]++; + } + $new_sql = sprintf("INSERT INTO %sbin5_places (type, rank, ucode, login, pts, games, score) + VALUES (%d, %d, %d, '%s', %d, %d, %f);", + $G_dbpfx, $ty, $rank[$subty], $pla_obj->ucode, escsql($pla_obj->login), + $pla_obj->points, $pla_obj->games, $pla_obj->score); + if ( ! (($new_pg = pg_query($bdb->dbconn->db(), $new_sql)) != FALSE && + pg_affected_rows($new_pg) == 1) ) { + log_crit("statadm: new place insert failed: ".print_r($pla_obj, TRUE)); + break; + } + + $old_gam[$subty] = $pla_obj->games; + $old_score[$subty] = $pla_obj->score; + } // for ($i = 0 ; $i < pg_numrows($pla_pg) ; $i++) { + if ($i < pg_numrows($pla_pg)) { + break; + } + } // for ($dtime = 0 ; $dtime < count($limi) ; $dtime++) { + if ($dtime < count($limi)) { + break; + } + + $mti_sql = sprintf("UPDATE %sbin5_places_mtime SET mtime = (to_timestamp(%d)) WHERE code = 0;", + $G_dbpfx, $curtime); + if ( ! (($mti_pg = pg_query($bdb->dbconn->db(), $mti_sql)) != FALSE && + pg_affected_rows($mti_pg) == 1) ) { + log_crit("statadm: new mtime insert failed."); + break; + } + + if (pg_query($bdb->dbconn->db(), "COMMIT") == FALSE) { + break; + } + return (TRUE); + } while (0); + + pg_query($bdb->dbconn->db(), "ROLLBACK"); + + return (FALSE); +} + +function main() +{ + GLOBAL $G_dbasetype, $G_alarm_passwd, $pazz; + + echo "Inizio.
"; + flush(); + if ($pazz != $G_alarm_passwd) { + echo "Wrong password
"; + flush(); + exit; + } + + $fun_name = "main_${G_dbasetype}"; + + $curtime = time(); + if ($ret = $fun_name($curtime)) + echo "Success.
\n"; + else + echo "Failed.
\n"; + + echo "Fine.\n"; + flush(); } main(); -- 2.17.1