public static function points(FrankizDateTime $begin, FrankizDateTime $end)
{
$res = XDB::query('SELECT uid,
SUM( _vote1*5 + _vote2*2 + _vote3 - _vote4*13 + _vote5*4.2 +
_vote6*6.9 + _vote7*3.14 + _vote8*3 + _vote9*7 + _vote10*7.1) as total,
SUM(_vote1) as nb1,
SUM(_vote2) as nb2,
SUM(_vote3) as nb3,
SUM(_vote4) as nb4,
SUM(_vote5) as nb5,
SUM(_vote6) as nb6,
SUM(_vote7) as nb7,
SUM(_vote8) as nb8,
SUM(_vote9) as nb9,
SUM(_vote10) as nb10
FROM (
SELECT uid,
if(rule = 1, count(*), 0) as _vote1,
if(rule = 2, count(*), 0) as _vote2,
if(rule = 3, count(*), 0) as _vote3,
if(rule = 4, count(*), 0) as _vote4,
if(rule = 5, count(*), 0) as _vote5,
if(rule = 6, count(*), 0) as _vote6,
if(rule = 7, count(*), 0) as _vote7,
if(rule = 8, count(*), 0) as _vote8,
if(rule = 9, count(*), 0) as _vote9,
if(rule = 10, count(*), 0) as _vote10
FROM qdj_votes AS qv
INNER JOIN qdj AS q
ON qv.qdj = q.id
WHERE qv.rule >0
AND q.date BETWEEN {?} AND {?}
AND uid NOT IN (
SELECT uid
FROM groups AS g
JOIN castes AS c ON g.gid = c.group
JOIN castes_users AS cu ON cu.cid = c.cid
WHERE g.name = "qdj" AND c.rights = "admin"
)
GROUP BY rule, uid
) AS aux
GROUP BY uid
ORDER BY total DESC', $begin->toDb(), $end->toDb())->fetchAllAssoc();
$users = new collection('User');
foreach ($res as $key => $e) {
$res[$key]['average'] = ($e['nb1'] + $e['nb2'] + $e['nb3'] + $e['nb4'] + $e['nb5'] + $e['nb6'] + $e['nb7'] + $e['nb8'] + $e['nb9'] + $e['nb10']) / 10;
$res[$key]['user'] = $users->addget($e['uid']);
unset($res[$key]['uid']);
$res[$key]['deviation'] = round(sqrt((pow($e['nb1'], 2) + pow($e['nb2'], 2) + pow($e['nb3'], 2) + pow($e['nb4'], 2) + pow($e['nb5'], 2) + pow($e['nb6'], 2) + pow($e['nb7'], 2) + pow($e['nb8'], 2) + pow($e['nb9'], 2) + pow($e['nb10'], 2)) / 10 - pow($res[$key]['average'], 2)), 2);
}
$users->select(UserSelect::base());
return $res;
}
