From f8e878fd27cf0abeeb3935a5728549acc5f624c7 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sat, 25 Jul 2009 17:49:14 +0200 Subject: [PATCH] Add defprio to main and top members --- database/defprio.sql | 13 +++++++++++++ lib/NDWeb/Controller/Members.pm | 32 ++++++++++++++++---------------- root/src/members/index.tt2 | 1 + root/src/members/points.tt2 | 2 ++ 4 files changed, 32 insertions(+), 16 deletions(-) create mode 100644 database/defprio.sql diff --git a/database/defprio.sql b/database/defprio.sql new file mode 100644 index 0000000..56186ec --- /dev/null +++ b/database/defprio.sql @@ -0,0 +1,13 @@ +CREATE OR REPLACE VIEW users_defprio AS +SELECT u.*, (0.2 * (u.attack_points / GREATEST(a.attack, 1::numeric)) + + 0.4 * (u.defense_points / GREATEST(a.defense, 1::numeric)) + + 0.2 * (p.size::numeric / a.size) + 0.05 * (p.score::numeric / a.score) + + 0.15 * (p.value::numeric / a.value))::numeric(3,2) AS defprio +FROM users u + JOIN current_planet_stats p ON u.planet = p.id + , ( + SELECT avg(u.attack_points) AS attack, avg(u.defense_points) AS defense, avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value + FROM users u + JOIN current_planet_stats p ON p.id = u.planet + WHERE u.uid IN ( SELECT groupmembers.uid FROM groupmembers WHERE groupmembers.gid = 2) + ) a; diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm index 2b50ebe..354bdbe 100644 --- a/lib/NDWeb/Controller/Members.pm +++ b/lib/NDWeb/Controller/Members.pm @@ -2,6 +2,7 @@ package NDWeb::Controller::Members; use strict; use warnings; +use feature ":5.10"; use parent 'Catalyst::Controller'; use NDWeb::Include; @@ -32,8 +33,8 @@ sub index : Path : Args(0) { $c->stash(u => $dbh->selectrow_hashref(q{SELECT planet,defense_points ,attack_points,scan_points,humor_points , (attack_points+defense_points+scan_points/20)::NUMERIC(5,1) as total_points - , sms,rank,hostmask,call_if_needed,sms_note - FROM users WHERE uid = ? + , sms,rank,hostmask,call_if_needed,sms_note,defprio + FROM users_defprio WHERE uid = ? },undef,$c->user->id) ); @@ -261,7 +262,8 @@ sub points : Local { my ( $self, $c, $order ) = @_; my $dbh = $c->model; - if ($order && $order =~ /^((?:defense|attack|total|humor|scan|raid)_points)$/){ + if ($order ~~ /^((?:defense|attack|total|humor|scan|raid)_points)$/ + || $order ~~ /^(defprio)$/){ $order = "$1 DESC"; }else{ $order = 'total_points DESC'; @@ -270,20 +272,18 @@ sub points : Local { my $limit = 'LIMIT 10'; $limit = '' if $c->check_user_roles(qw/members_points_nolimit/); - my $query = $dbh->prepare(qq{SELECT username,defense_points,attack_points - ,scan_points,humor_points - ,(attack_points+defense_points+scan_points/20)::NUMERIC(4,0) as total_points - , count(NULLIF(rc.launched,FALSE)) AS raid_points - FROM users u LEFT OUTER JOIN raid_claims rc USING (uid) - WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2) - GROUP BY username,defense_points,attack_points,scan_points,humor_points,rank - ORDER BY $order $limit}); + my $query = $dbh->prepare(q{ +SELECT username,defense_points,attack_points + ,scan_points,humor_points,defprio + ,(attack_points+defense_points+scan_points/20)::NUMERIC(4,0) as total_points + , count(NULLIF(rc.launched,FALSE)) AS raid_points +FROM users_defprio u LEFT OUTER JOIN raid_claims rc USING (uid) +WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2) +GROUP BY username,defense_points,attack_points,scan_points,humor_points,defprio +ORDER BY } . "$order $limit" + ); $query->execute; - my @members; - while (my $member = $query->fetchrow_hashref){ - push @members,$member; - } - $c->stash(members => \@members); + $c->stash(members => $query->fetchall_arrayref({})); } sub addintel : Local { diff --git a/root/src/members/index.tt2 b/root/src/members/index.tt2 index 6d9c50b..2120afe 100644 --- a/root/src/members/index.tt2 +++ b/root/src/members/index.tt2 @@ -76,6 +76,7 @@
Account details