From 2b9c7011667ceca69b18e83f5ec002d12635a17e Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Thu, 19 Sep 2013 08:10:17 +0200 Subject: [PATCH] schema updated and stat-day.php fixed --- sql/sql.d/054-continue-match.sql | 4 +- sql/sql.d/060-tournament.sql | 16 ++-- web/Obj/dbase_file.phh | 2 +- web/Obj/dbase_pgsql.phh | 8 +- web/briskin5/Obj/briskin5.phh | 4 +- web/briskin5/stat-day.php | 146 ++++++++++++++++++++----------- 6 files changed, 116 insertions(+), 64 deletions(-) diff --git a/sql/sql.d/054-continue-match.sql b/sql/sql.d/054-continue-match.sql index 3db1ff9..0ed40fb 100644 --- a/sql/sql.d/054-continue-match.sql +++ b/sql/sql.d/054-continue-match.sql @@ -1,8 +1,8 @@ DROP TABLE #PFX#bin5_table_orders; CREATE TABLE #PFX#bin5_table_orders ( mcode integer REFERENCES #PFX#bin5_matches (code) ON DELETE cascade ON UPDATE cascade, - ucode integer REFERENCES #PFX#users (code) ON DELETE cascade ON UPDATE cascade, - pos integer + pos integer, + ucode integer REFERENCES #PFX#users (code) ON DELETE cascade ON UPDATE cascade ); ALTER TABLE #PFX#bin5_matches DROP COLUMN mult_next; diff --git a/sql/sql.d/060-tournament.sql b/sql/sql.d/060-tournament.sql index d3f249a..b26a1b1 100644 --- a/sql/sql.d/060-tournament.sql +++ b/sql/sql.d/060-tournament.sql @@ -1,16 +1,20 @@ DROP TABLE #PFX#bin5_tournaments; CREATE TABLE #PFX#bin5_tournaments ( code SERIAL PRIMARY KEY, + active integer, name text ); -- add tournaments and field in the bin5_matches table -INSERT INTO #PFX#bin5_tournaments (code, name) VALUES (1, 'normal match'); -ALTER SEQUENCE #PFX#bin5_tournaments_code_seq RESTART WITH 2; +INSERT INTO #PFX#bin5_tournaments (code, active, name) VALUES (1, 1, 'normal match'); +INSERT INTO #PFX#bin5_tournaments (code, active, name) VALUES (2, 1, 'special match'); +ALTER SEQUENCE #PFX#bin5_tournaments_code_seq RESTART WITH 3; -ALTER TABLE #PFX#bin5_matches DROP COLUMN tourn; -ALTER TABLE #PFX#bin5_matches ADD COLUMN tourn integer DEFAULT 1; +ALTER TABLE #PFX#bin5_matches DROP COLUMN tcode; +ALTER TABLE #PFX#bin5_matches ADD COLUMN tcode integer DEFAULT 1; -ALTER TABLE #PFX#bin5_matches DROP CONSTRAINT #PFX#bin5_matches_tourn_fkey; -ALTER TABLE #PFX#bin5_matches ADD FOREIGN KEY (tourn) REFERENCES #PFX#bin5_tournaments(code) ON UPDATE cascade ON DELETE cascade; +ALTER TABLE #PFX#bin5_matches DROP CONSTRAINT #PFX#bin5_matches_tcode_fkey; +ALTER TABLE #PFX#bin5_matches ADD FOREIGN KEY (tcode) REFERENCES #PFX#bin5_tournaments(code) ON UPDATE cascade ON DELETE cascade; +ALTER TABLE #PFX#bin5_games DROP COLUMN act; +ALTER TABLE #PFX#bin5_games ADD COLUMN act integer; -- end reason of the game diff --git a/web/Obj/dbase_file.phh b/web/Obj/dbase_file.phh index 9bf634a..d917a81 100644 --- a/web/Obj/dbase_file.phh +++ b/web/Obj/dbase_file.phh @@ -178,7 +178,7 @@ class BriskDB { return ($ret); } - function bin5_points_save($date, $table, $tidx, $ucodes, $pts) + function bin5_points_save($date, $table, $tidx, $action, $ucodes, $pts) { return TRUE; } diff --git a/web/Obj/dbase_pgsql.phh b/web/Obj/dbase_pgsql.phh index f214999..91a0426 100644 --- a/web/Obj/dbase_pgsql.phh +++ b/web/Obj/dbase_pgsql.phh @@ -291,7 +291,7 @@ class BriskDB // ttok text UNIQUE, // tidx - function bin5_points_save($date, $table, $tidx, $ucodes, $pts) + function bin5_points_save($date, $table, $tidx, $action, $ucodes, $pts) { GLOBAL $G_dbpfx; $sql_ttok = escsql($table->table_token); @@ -343,9 +343,9 @@ class BriskDB /* * games management */ - $gam_sql = sprintf("INSERT INTO %sbin5_games (mcode, tstamp, asta_pnt, pnt, asta_win, friend, mazzo, mult) - VALUES (%d, to_timestamp(%d), %d, %d, %d, %d, %d, %d) RETURNING *;", - $G_dbpfx, $mtc_obj->code, $date, + $gam_sql = sprintf("INSERT INTO %sbin5_games (mcode, tstamp, act, asta_pnt, pnt, asta_win, friend, mazzo, mult) + VALUES (%d, to_timestamp(%d), %d, %d, %d, %d, %d, %d, %d) RETURNING *;", + $G_dbpfx, $mtc_obj->code, $date, $action, $table->old_asta_pnt, $table->old_pnt, $table->old_asta_win, $table->old_friend, diff --git a/web/briskin5/Obj/briskin5.phh b/web/briskin5/Obj/briskin5.phh index 9793262..3bb2cf1 100644 --- a/web/briskin5/Obj/briskin5.phh +++ b/web/briskin5/Obj/briskin5.phh @@ -609,7 +609,7 @@ class Bin5_table extends Table { require_once("../Obj/dbase_".$G_dbasetype.".phh"); if (($bdb = BriskDB::create()) != FALSE) { - $bdb->bin5_points_save($curtime, $this, $user->table_orig, $ucodes, $pts); + $bdb->bin5_points_save($curtime, $this, $user->table_orig, $action, $ucodes, $pts); unset($bdb); } else { @@ -1958,7 +1958,7 @@ function log_points($remote_addr, $curtime, $user, $where, $mesg) } } -function game_description($act, $form, $win = -1, $win_name = "?1?", $fri = -1, $fri_name "?2?", +function game_description($act, $form, $win = -1, $win_name = "?1?", $fri = -1, $fri_name = "?2?", $old_pnt = -1, $old_asta_pnt = -1) { GLOBAL $G_lang, $mlang_bin5_bin5; diff --git a/web/briskin5/stat-day.php b/web/briskin5/stat-day.php index 6d3ac74..92c1007 100644 --- a/web/briskin5/stat-day.php +++ b/web/briskin5/stat-day.php @@ -229,59 +229,97 @@ function main_file($curtime) function main_pgsql($from, $to) { - GLOBAL $G_dbpfx; + GLOBAL $G_lang, $G_dbpfx, $mlang_stat_day; - if (($fpexp = @fopen(LEGAL_PATH."/explain.log", 'w')) == FALSE) { - echo "Open explain failed
"; - exit; - } - fprintf($fpexp, "

Minuta delle partite dal (%s) al (%s)

", - $from, $to); + $ret = FALSE; + $fpexp = FALSE; - if (($bdb = BriskDB::create()) == FALSE) { - echo "database connection failed"; - exit; - } do { + if (($fpexp = @fopen(LEGAL_PATH."/explain.log", 'w')) == FALSE) { + log_crit("stat-day: open explain failed"); + break; + } + fprintf($fpexp, "

Minuta delle partite dal (%s) al (%s)

", + $from, $to); + + if (($bdb = BriskDB::create()) == FALSE) { + log_crit("stat-day: database connection failed"); + break; + } + if (pg_query($bdb->dbconn->db(), "BEGIN") == FALSE) { - log_crit("statadm: begin failed"); + log_crit("stat-day: begin failed"); break; } - - $tmt_sql = sprintf("select m.code as code from %sbin5_matches as m, %sbin5_games as g WHERE m.code = g.mcode AND g.tstamp >= '%s' AND g.tstamp < '%s' GROUP BY m.code;", $G_dbpfx, $G_dbpfx, $from, $to); - // if deletable old matches exists then ... - if (($tmt_pg = pg_query($bdb->dbconn->db(), $tmt_sql)) != FALSE) { + // retrieve list of active tournaments + $trn_sql = sprintf("SELECT * FROM %sbin5_tournaments WHERE active = 1;", $G_dbpfx); + if (($trn_pg = pg_query($bdb->dbconn->db(), $trn_sql)) == FALSE) { + log_crit("stat-day: select from tournaments failed"); + break; + } + + $trn_n = pg_numrows($trn_pg); + + for ($t = 0 ; $t < $trn_n ; $t++) { + $trn_obj = pg_fetch_object($trn_pg, $t); + + $tmt_sql = sprintf("SELECT m.code AS code FROM %sbin5_matches AS m, %sbin5_games AS g, %sbin5_tournaments as t WHERE t.code = m.tcode AND m.code = g.mcode AND g.tstamp >= '%s' AND g.tstamp < '%s' GROUP BY m.code;", + $G_dbpfx, $G_dbpfx, $G_dbpfx, $from, $to); + + // if deletable old matches exists then ... + if (($tmt_pg = pg_query($bdb->dbconn->db(), $tmt_sql)) == FALSE) { + log_crit("stat-day: select from matches failed"); + break; + } + // // store matches before clean them // $tmt_n = pg_numrows($tmt_pg); // get matches + if ($tmt_n == 0) + continue; + + if (!isset($mlang_stat_day[$trn_obj->name][$G_lang])) { + log_crit("stat-day: tournament name not found in array"); + break; + } + fprintf($fpexp, "

%s

", $mlang_stat_day[$trn_obj->name][$G_lang]); + for ($m = 0 ; $m < $tmt_n ; $m++) { fprintf($fpexp, "
"); $tmt_obj = pg_fetch_object($tmt_pg, $m); - + $usr_sql = sprintf(" -SELECT u.code AS code, u.login AS login, min(g.tstamp) AS first, max(g.tstamp) AS last, m.tidx AS tidx FROM %sbin5_matches AS m, %sbin5_games AS g, %sbin5_points AS p, %susers AS u WHERE m.code = g.mcode AND g.code = p.gcode AND u.code = p.ucode AND m.code = %d GROUP BY u.code, u.login, m.tidx;", $G_dbpfx, $G_dbpfx, $G_dbpfx, $G_dbpfx, $tmt_obj->code); - - if (($usr_pg = pg_query($bdb->dbconn->db(), $usr_sql)) == FALSE ) { +SELECT u.code AS code, u.login AS login, min(g.tstamp) AS first, max(g.tstamp) AS last, m.tidx AS tidx FROM %sbin5_matches AS m, %sbin5_games AS g, %sbin5_points AS p, %susers AS u, %sbin5_table_orders AS o WHERE m.code = g.mcode AND g.code = p.gcode AND u.code = p.ucode AND m.code = %d AND m.code = o.mcode AND u.code = o.ucode GROUP BY u.code, u.login, m.tidx, o.pos ORDER BY o.pos;", $G_dbpfx, $G_dbpfx, $G_dbpfx, $G_dbpfx, $G_dbpfx, $tmt_obj->code); + + if (($usr_pg = pg_query($bdb->dbconn->db(), $usr_sql)) == FALSE ) { break; } $usr_n = pg_numrows($usr_pg); - if ($usr_n != 5) { + if ($usr_n != BIN5_PLAYERS_N) { + break; + } + + $gam_sql = sprintf("SELECT g.* FROM %sbin5_games AS g, %sbin5_matches AS m WHERE g.mcode = m.code AND m.code = %d ORDER BY g.tstamp;", + $G_dbpfx, $G_dbpfx, $tmt_obj->code); + if (($gam_pg = pg_query($bdb->dbconn->db(), $gam_sql)) == FALSE ) { break; - } + } + + $usr_obj = array(); for ($u = 0 ; $u < $usr_n ; $u++) { - $usr_obj = pg_fetch_object($usr_pg, $u); + $usr_obj[$u] = pg_fetch_object($usr_pg, $u); if ($u == 0) { - fprintf($fpexp, "

Codice: %d (%s - %s), Tavolo: %s

\n", $tmt_obj->code, $usr_obj->first, $usr_obj->last, $usr_obj->tidx); + fprintf($fpexp, "

Codice: %d (%s - %s), Tavolo: %s

\n", $tmt_obj->code, $usr_obj[$u]->first, $usr_obj[$u]->last, $usr_obj[$u]->tidx); fprintf($fpexp, "\n"); - } - fprintf($fpexp, "", $usr_obj->login); - $pts_sql = sprintf(" -select p.pts as pts from %sbin5_matches as m, %sbin5_games as g, %sbin5_points as p, %susers as u WHERE m.code = g.mcode AND g.code = p.gcode AND u.code = p.ucode AND m.code = %d AND u.code = %d ORDER BY g.code", $G_dbpfx, $G_dbpfx, $G_dbpfx, $G_dbpfx, - $tmt_obj->code, $usr_obj->code); + } + fprintf($fpexp, "", $usr_obj[$u]->login); + $pts_sql = sprintf("SELECT p.pts as pts from %sbin5_matches as m, %sbin5_games as g, %sbin5_points as p, %susers as u WHERE m.code = g.mcode AND g.code = p.gcode AND u.code = p.ucode AND m.code = %d AND u.code = %d ORDER BY g.code", $G_dbpfx, $G_dbpfx, $G_dbpfx, $G_dbpfx, + $tmt_obj->code, $usr_obj[$u]->code); + // points of the match for each user if (($pts_pg[$u] = pg_query($bdb->dbconn->db(), $pts_sql)) == FALSE) { break; } @@ -294,28 +332,34 @@ select p.pts as pts from %sbin5_matches as m, %sbin5_games as g, %sbin5_points a } } } - if ($u != 5) { + if ($u != BIN5_PLAYERS_N) { break; - } - fprintf($fpexp, "\n"); + } + fprintf($fpexp, "\n"); // LISTA DELLE VARIE PARTITE for ($g = 0 ; $g < $num_games ; $g++) { + $gam_obj = pg_fetch_object($gam_pg, $g); fprintf($fpexp, ""); - for ($u = 0 ; $u < 5 ; $u++) { + for ($u = 0 ; $u < BIN5_PLAYERS_N ; $u++) { $pts_obj = pg_fetch_object($pts_pg[$u], $g); - fprintf($fpexp, "", $pts_obj->pts); + fprintf($fpexp, "", $pts_obj->pts); } + fprintf($fpexp, "", $usr_obj[$gam_obj->mazzo]->login, + xcape( game_description($gam_obj->act, 'plain', + $gam_obj->asta_win, $usr_obj[$gam_obj->asta_win]->login, + $gam_obj->friend, $usr_obj[$gam_obj->friend]->login, + $gam_obj->pnt, $gam_obj->asta_pnt) ) + ); fprintf($fpexp, "\n"); } // LISTA DEI TOTALI fprintf($fpexp, ""); - for ($u = 0 ; $u < 5 ; $u++) { - $usr_obj = pg_fetch_object($usr_pg, $u); + for ($u = 0 ; $u < BIN5_PLAYERS_N ; $u++) { $tot_sql = sprintf(" SELECT SUM(p.pts) AS pts FROM %sbin5_matches AS m, %sbin5_games AS g, %sbin5_points AS p, %susers AS u WHERE m.code = g.mcode AND g.code = p.gcode AND u.code = p.ucode AND m.code = %d AND u.code = %d", $G_dbpfx, $G_dbpfx, $G_dbpfx, $G_dbpfx, - $tmt_obj->code, $usr_obj->code); + $tmt_obj->code, $usr_obj[$u]->code); if (($tot_pg = pg_query($bdb->dbconn->db(), $tot_sql)) == FALSE ) { break; } @@ -327,37 +371,41 @@ SELECT SUM(p.pts) AS pts FROM %sbin5_matches AS m, %sbin5_games AS g, %sbin5_poi } if ($m < $tmt_n) break; - } // if (($tmt_pg = pg_query($bdb->dbco... - - fclose($fpexp); - return (TRUE); + } + if ($t < $trn_n) + break; + $ret = (TRUE); } while (0); - pg_query($bdb->dbconn->db(), "ROLLBACK"); - fclose($fpexp); + if ($ret == FALSE) { + pg_query($bdb->dbconn->db(), "ROLLBACK"); + } + if ($fpexp != FALSE) { + fclose($fpexp); + } - return (FALSE); + return ($ret); } // echo "QUIr\n"; // exit(123); function main() { - GLOBAL $G_dbasetype, $G_alarm_passwd, $pazz, $from, $to; - + GLOBAL $G_lang, $G_dbasetype, $G_alarm_passwd, $pazz, $from, $to; + if ($pazz != $G_alarm_passwd) { echo "Wrong password
"; mop_flush(); exit; } - + $fun_name = "main_${G_dbasetype}"; - + if ($ret = $fun_name($from, $to)) echo "Success.
\n"; else echo "Failed.
\n"; - + echo "Fine.\n"; mop_flush(); } -- 2.17.1
%s%s
mazzodescrizione
%d%d%s%s