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; }