Support for planet tags

diff --git a/database/group_roles.sql b/database/group_roles.sql
index e1c81b1f6e07f973066e129e5165ff1bac7debc5..45118aea811d7ff8dc567793c965914de3c50a8b 100644 (file)
--- a/database/group_roles.sql
+++ b/database/group_roles.sql
@@ -40,6 +40,7 @@ INSERT INTO roles VALUES('intel_naps');
 INSERT INTO roles VALUES('textexport_alliance');
 INSERT INTO roles VALUES('stats_find_nick');
 INSERT INTO roles VALUES('members_defenders');
+INSERT INTO roles VALUES('stats_tags_all');
 
 INSERT INTO group_roles (gid,role) VALUES(2,'member_menu');
 INSERT INTO group_roles (gid,role) VALUES(2,'attack_menu');
@@ -55,13 +56,16 @@ INSERT INTO group_roles (gid,role) VALUES(6,'calls_list');
 INSERT INTO group_roles (gid,role) VALUES(6,'calls_edit');
 INSERT INTO group_roles (gid,role) VALUES(6,'calls_leeches');
 INSERT INTO group_roles (gid,role) VALUES(6,'members_defenders');
+INSERT INTO group_roles (gid,role) VALUES('D','stats_tags_all');
 
 INSERT INTO group_roles (gid,role) VALUES(4,'bc_menu');
 INSERT INTO group_roles (gid,role) VALUES(4,'raids_info');
 INSERT INTO group_roles (gid,role) VALUES(4,'raids_edit');
+INSERT INTO group_roles (gid,role) VALUES('B','stats_tags_all');
 
 INSERT INTO group_roles (gid,role) VALUES(5,'intel_menu');
 INSERT INTO group_roles (gid,role) VALUES(5,'intel');
+INSERT INTO group_roles (gid,role) VALUES('I','stats_tags_all');
 
 INSERT INTO group_roles (gid,role) VALUES(8,'no_fleet_update');
 
@@ -110,3 +114,7 @@ INSERT INTO group_roles (gid,role) VALUES(3,'intel_member');
 INSERT INTO group_roles (gid,role) VALUES(3,'intel_naps');
 INSERT INTO group_roles (gid,role) VALUES(3,'textexport_alliance');
 INSERT INTO group_roles (gid,role) VALUES(3,'members_defenders');
+
+INSERT INTO group_roles (gid,role) VALUES('R','stats_tags_all');
+
+INSERT INTO group_roles (gid,role) VALUES('s','stats_tags_all');

diff --git a/database/planettags.sql b/database/planettags.sql
new file mode 100644 (file)
index 0000000..1011fe0
--- /dev/null
+++ b/database/planettags.sql
@@ -0,0 +1,11 @@
+CREATE TABLE available_planet_tags (
+       tag CITEXT PRIMARY KEY
+);
+
+CREATE TABLE planet_tags (
+       pid INTEGER NOT NULL REFERENCES planets (pid)
+       ,tag CITEXT NOT NULL REFERENCES available_planet_tags (tag)
+       ,uid INTEGER REFERENCES users (uid)
+       ,time TIMESTAMPTZ NOT NULL DEFAULT NOW()
+       ,PRIMARY KEY (pid,uid,tag)
+);

diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm
index bafea58ea8be45ad64da1f5450990e5b3193ad52..990b2225e9f07fef9b1afc0ff028b1150f5f955a 100644 (file)
--- a/lib/NDWeb/Controller/Intel.pm
+++ b/lib/NDWeb/Controller/Intel.pm
@@ -103,6 +103,17 @@ ORDER BY tick DESC, mission
        $query->execute($id,$ticks);
        $c->stash(outgoing => $query->fetchall_arrayref({}) );
 
+       $query = $dbh->prepare(q{
+SELECT tag, array_to_string(array_agg(username),', ') AS nicks, to_char(max(time),'YYYY-MM-DD HH24:MI') AS time
+FROM planet_tags pt
+       JOIN users u USING (uid)
+WHERE pt.pid = $1
+GROUP BY tag
+ORDER BY time DESC
+               });
+       $query->execute($id);
+       $c->stash(tags => $query->fetchall_arrayref({}) );
+
 }
 
 sub channels : Local {

diff --git a/lib/NDWeb/Controller/Stats.pm b/lib/NDWeb/Controller/Stats.pm
index a1db2459db9a6e55f21341a8f805dcfe177c00f4..e28c2aeaecffa84d80f140ac5a8a9617f353b54d 100644 (file)
--- a/lib/NDWeb/Controller/Stats.pm
+++ b/lib/NDWeb/Controller/Stats.pm
@@ -5,6 +5,7 @@ use warnings;
 use parent 'Catalyst::Controller';
 
 use NDWeb::Include;
+use HTML::TagCloud;
 
 =head1 NAME
 
@@ -259,6 +260,45 @@ sub find : Local {
        }
 }
 
+sub tags : Local {
+       my ( $self, $c ) = @_;
+       my $dbh = $c->model;
+
+       my @tags = $c->req->param('tags');
+       $c->stash(tags => \@tags);
+       my $query = $dbh->prepare(q{
+SELECT tag, count(*)
+FROM planet_tags pt
+WHERE tag <> ALL($1)
+       AND ($3 OR uid = $2)
+GROUP BY tag
+               });
+       $query->execute(\@tags, $c->user->id, $c->check_user_roles('stats_tags_all') // 0);
+       my $cloud = HTML::TagCloud->new;
+       while (my $tag = $query->fetchrow_hashref){
+               my @t = @tags;
+               push @t, $tag->{tag};
+               my %param = (tags => \@t);
+               $cloud->add($tag->{tag}, $c->uri_for('tags',\%param), $tag->{count});
+       }
+       $c->stash(cloud => $cloud->html);
+       $c->stash(css => $cloud->css);
+
+       my $query = $dbh->prepare(q{
+WITH p AS (SELECT pid, coords(x,y,z) FROM current_planet_stats
+), t AS (SELECT pid,tag,bool_or(uid = $2) AS own,max(time) AS time
+       FROM planet_tags
+       WHERE ($3 OR uid = $2)
+       GROUP BY pid,tag
+       ORDER BY time DESC
+), tags AS (SELECT pid, array_agg(tag) AS tags
+       FROM t GROUP BY pid
+)
+SELECT p.*k,array_to_string(tags,', ') AS tags FROM p JOIN tags USING (pid) WHERE tags @> $1;
+               });
+       $query->execute(\@tags, $c->user->id, $c->check_user_roles('stats_tags_all') // 0);
+       $c->stash(planets => $query->fetchall_arrayref({}));
+}
 
 =head1 AUTHOR
 

diff --git a/root/lib/site/html.tt2 b/root/lib/site/html.tt2
index 5ece0acdd96bdb58840f79cb8c44c69401318819..098b05f8c97a3dfec3863d850aac7e2a8f279244 100644 (file)
--- a/root/lib/site/html.tt2
+++ b/root/lib/site/html.tt2
@@ -7,6 +7,11 @@
        <link rel="stylesheet" type="text/css" href="/static/css/[% user.css or "black" %].css">
 [%- FOR s IN site.stylesheets %]
        <link rel="stylesheet" type="text/css" href="/static/[% s %]">
+[%- END %]
+[%- IF css %]
+<style type="text/css">
+[% css %]
+</style>
 [%- END %]
        <link rel="icon" type="image/ico" href="/static/favicon.ico">
        <script type="text/javascript" src="/static/js/raid.js"></script>

diff --git a/root/lib/site/leftbar.tt2 b/root/lib/site/leftbar.tt2
index dee6aabaa111fe54b9b8a7209dba65e4ff68e445..0c0a37ffbdfc2316eedb592fe3b49e10d11f4693 100644 (file)
--- a/root/lib/site/leftbar.tt2
+++ b/root/lib/site/leftbar.tt2
@@ -57,6 +57,7 @@
        <li><a href="/members/addintel">Add intel/scans</a></li>
        <li><a href="/members/launchConfirmation">Launch confirmation</a></li>
        <li><a href="/members/ircrequest">IRC request</a></li>
+       <li><a href="/stats/tags">Planet tags</a></li>
 </ul>
        [% ELSE %]
                [% IF c.user.planet %]

diff --git a/root/src/intel/planet.tt2 b/root/src/intel/planet.tt2
index 4e5eb631185398fbd9ceb3e990edbc69e80d5340..86fbff97005f876802a87d58d8b59110ef394af6 100644 (file)
--- a/root/src/intel/planet.tt2
+++ b/root/src/intel/planet.tt2
@@ -48,6 +48,19 @@
        </p>
 </form>
 
+[% IF tags.size > 0 %]
+<table class="stats">
+       <tr>
+       <th>Tag</th><th>Added by</th><th>Last added</th>
+       </tr>
+       [% FOR t IN tags %]
+       <tr>
+               <td>[% t.tag %]</td><td>[% t.nicks %]</td><td>[% t.time %]</td>
+       </tr>
+       [% END %]
+</table>
+[% END %]
+
 [% IF channelusers.size > 0 %]
 <table class="stats">
        <tr>

diff --git a/root/src/stats/tags.tt2 b/root/src/stats/tags.tt2
new file mode 100644 (file)
index 0000000..ff96fc2
--- /dev/null
+++ b/root/src/stats/tags.tt2
@@ -0,0 +1,15 @@
+[% META title = 'Planet tags' %]
+<p> Picked tags: [% tags.join(', ') %]
+[% cloud %]
+
+<table class="stats">
+       <tr>
+               <th>Coords</th><th>Tags</th>
+       </tr>
+[% FOR p IN planets %]
+       <tr class="[% loop.count % 2 == 0 ? 'even' : 'odd' %]">
+       <td><a href="[% c.uri_for('planet',p.pid) %]">[% p.coords %]</a></td>
+       <td>[% p.tags %]</td>
+       </tr>
+[% END %]
+</table>