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');
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');
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');
--- /dev/null
+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)
+);
$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 {
use parent 'Catalyst::Controller';
use NDWeb::Include;
+use HTML::TagCloud;
=head1 NAME
}
}
+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
<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>
<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 %]
</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>
--- /dev/null
+[% 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>