Codice PHP:
class QueryChallenge
{
const PLAYERS_SFIDE = "SELECT distinct(P.id) as id, P.cognome as cognome, P.nome as nome " .
"FROM players as P, archivio_sfide as A " .
"WHERE (P.id = A.id_loser OR P.id = A.id_winner) " .
"AND DATE_FORMAT(A.data, '%Y') = ? " .
"AND DATE_FORMAT(A.data, '%m') <= ?";
const PLAYERS_SFIDE_MESE = "SELECT distinct(P.id) as id, P.cognome as cognome, P.nome as nome " .
"FROM players as P, archivio_sfide as A " .
"WHERE (P.id = A.id_loser OR P.id = A.id_winner) " .
"AND DATE_FORMAT(A.data, '%Y') = ? " .
"AND DATE_FORMAT(A.data, '%m') = ?";
static public function puntiBaseGiocate($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS GIOCATE FROM archivio_sfide WHERE (id_winner = ? OR id_loser =?) AND DATE_FORMAT(data, '%Y') = ? AND DATE_FORMAT(data, '%m') $op ?";
return $query;
}
static public function puntiBaseSupPrimFascia($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_1F FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND ( RLOS.fascia = 1 OR (( (RLOS.raank < RWIN.raank AND RLOS.raank !=0) OR (RWIN.raank = 0 AND RLOS.raank >0)) AND RLOS.fascia <= RWIN.fascia))";
return $query;
}
static public function puntiBaseInf($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_INF FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND (RWIN.fascia < RLOS.fascia OR ( (RWIN.raank < RLOS.raank and RWIN.raank != 0 OR ( RLOS.raank = 0 and RWIN.raank != 0)) AND RWIN.fascia <= RLOS.fascia)) AND RLOS.fascia != 1";
return $query;
}
static public function puntiBonusSupPrimFascia2_0($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_1F FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND (
RLOS.fascia = 1 OR
RLOS.fascia < RWIN.fascia OR
(RLOS.fascia <= RWIN.fascia AND RLOS.raank < RWIN.raank AND RLOS.raank >0) OR
(RWIN.raank = 0 AND RLOS.raank >0 and RLOS.fascia < RWIN.fascia))
AND A.htoh = '2-0'";
return $query;
}
static public function puntiBonusInf2a0($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_INF FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND (RWIN.fascia < RLOS.fascia OR (RWIN.raank < RLOS.raank and RWIN.raank > 0 AND RWIN.fascia <= RLOS.fascia))
AND ((RLOS.fascia - RWIN.fascia) < 2) AND A.htoh = '2-0'";
return $query;
}
static public function puntiCircuito1a01F($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_1F FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND RLOS.fascia = 1 AND (A.htoh = '1-0' or A.htoh = '1-1')";
return $query;
}
static public function puntiCircuitoPrimaVinta5to8oLiv2A($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_2F FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND (RLOS.raank IN (5,6,7,8) OR (RLOS.fit IN ('4.3') AND RLOS.raank > 4))
AND (A.htoh = '1-0' or A.htoh = '1-1')";
return $query;
}
static public function puntiBonusSupPrimFascia($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_1F FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND (
RLOS.fascia = 1 OR
RLOS.fascia < RWIN.fascia OR
(RLOS.fascia <= RWIN.fascia AND RLOS.raank < RWIN.raank AND RLOS.raank >0) OR
(RWIN.raank = 0 AND RLOS.raank >0 and RLOS.fascia < RWIN.fascia))
AND (A.htoh = '2-0' or A.htoh = '2-1')";
return $query;
}
static public function puntiCircuitoPrimaVinta9to16oLiv2B($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_3F FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND RWIN.data_ins = RLOS.data_ins AND ((RLOS.raank > 8 and RLOS.raank < 17) OR (RLOS.fit in ('4.4','4.5') AND RLOS.raank > 8) ) AND (A.htoh = '1-0' or A.htoh = '1-1')";
return $query;
}
static public function getCircuitoOver161a0($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT COUNT(*) AS W_VS_OV16 FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND (RLOS.raank > 16 or RLOS.raank = 0) AND RLOS.fascia >=3 AND (A.htoh = '1-0' or A.htoh = '1-1') AND ((RLOS.fascia - RWIN.fascia) < 2)";
return $query;
}
static public function puntiCircuitoBonusChiusa($periodo){
$op = "<=";
if($periodo == "m")
$op = "=";
$query = "SELECT RWIN.raank as RANK_WIN, RLOS.raank as RANK_LOS, RWIN.fit as FIT_WIN, RLOS.fit as FIT_LOS,
RWIN.punti_pr + RWIN.punti_dmc + RWIN.punti_aut + RWIN.punti_sfi + RWIN.punti_mas as PNT_WIN,
RLOS.punti_pr + RLOS.punti_dmc + RLOS.punti_aut + RLOS.punti_sfi + RLOS.punti_mas as PNT_LOS
FROM archivio_sfide as A, ranking as RWIN, ranking as RLOS
WHERE A.id_winner = ? AND A.id_winner = RWIN.player_id AND A.id_loser = RLOS.player_id
AND DATE_FORMAT(A.data, '%Y') = ?
AND DATE_FORMAT(A.data, '%m') $op ?
AND RWIN.data_ins = RLOS.data_ins
AND RWIN.data_ins = (select max(data_ins) FROM ranking WHERE data_ins <= A.data)
AND ((RLOS.raank >0 && RLOS.raank <= 16) OR RLOS.fit IN ('4.1','4.2','4.3','4.4','4.5','4.6')) AND (A.htoh = '2-0' OR A.htoh = '2-1')";;
return $query;
}
}
Ecco una definizione semplificata delle tabelle coinvolte: