Add defprio to main and top members

diff --git a/database/defprio.sql b/database/defprio.sql
new file mode 100644 (file)
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 2b50ebe7f6449c9f2f6d0a47a99c5fd7b11e3557..354bdbeefedfc939ef656ebf2685583e7ded2f4d 100644 (file)
--- 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 6d9c50bf0f29f64a8f492666ae50c42047481c29..2120afe12ade5fba015d015d90275abdd930cf3a 100644 (file)
--- a/root/src/members/index.tt2
+++ b/root/src/members/index.tt2
@@ -76,6 +76,7 @@
 <fieldset class="forum-post"> <legend>Account details</legend>
        <ul>
        <li> ND rank (by score): [% u.rank %] </li>
+       <li> Defprio: [% u.defprio %] </li>
        <li> Defense points: [% u.defense_points %] </li>
        <li> Attack points: [% u.attack_points %] </li>
        <li> Scan points: [% u.scan_points %] </li>

diff --git a/root/src/members/points.tt2 b/root/src/members/points.tt2
index a90a8e38468f75deddc4d0afc762baec6a069e4f..6b11530021ace30d7fa631d5ca92b22c69231a28 100644 (file)
--- a/root/src/members/points.tt2
+++ b/root/src/members/points.tt2
@@ -3,6 +3,7 @@
        <tr>
        <th>User</th>
        <th><a href="[% c.uri_for('points','total_points') %]">Total</a></th>
+       <th><a href="[% c.uri_for('points','defprio') %]">Defprio</a></th>
        <th><a href="[% c.uri_for('points','defense_points') %]">Defenses</a></th>
        <th><a href="[% c.uri_for('points','attack_points') %]">Attacks</a></th>
        <th><a href="[% c.uri_for('points','raid_points') %]">Raid</a></th>
@@ -13,6 +14,7 @@
        <tr align="right" class="[% loop.count % 2 == 0 ? 'even' : 'odd' %]">
        <td align="left">[% m.username %]</td>
        <td>[% m.total_points %]</td>
+       <td>[% m.defprio %]</td>
        <td>[% m.defense_points %]</td>
        <td>[% m.attack_points %]</td>
        <td>[% m.raid_points %]</td>