From 88534ccb5763a823eec9147f63c732588e275e6a Mon Sep 17 00:00:00 2001
From: Michael Andreen
Date: Wed, 26 Aug 2009 15:23:58 +0200
Subject: [PATCH] Support for planet tags
---
database/group_roles.sql | 8 +++++++
database/planettags.sql | 11 ++++++++++
lib/NDWeb/Controller/Intel.pm | 11 ++++++++++
lib/NDWeb/Controller/Stats.pm | 40 +++++++++++++++++++++++++++++++++++
root/lib/site/html.tt2 | 5 +++++
root/lib/site/leftbar.tt2 | 1 +
root/src/intel/planet.tt2 | 13 ++++++++++++
root/src/stats/tags.tt2 | 15 +++++++++++++
8 files changed, 104 insertions(+)
create mode 100644 database/planettags.sql
create mode 100644 root/src/stats/tags.tt2
diff --git a/database/group_roles.sql b/database/group_roles.sql
index e1c81b1..45118ae 100644
--- 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
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 bafea58..990b222 100644
--- 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 a1db245..e28c2ae 100644
--- 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 5ece0ac..098b05f 100644
--- a/root/lib/site/html.tt2
+++ b/root/lib/site/html.tt2
@@ -7,6 +7,11 @@
[%- FOR s IN site.stylesheets %]
+[%- END %]
+[%- IF css %]
+
[%- END %]
diff --git a/root/lib/site/leftbar.tt2 b/root/lib/site/leftbar.tt2
index dee6aab..0c0a37f 100644
--- a/root/lib/site/leftbar.tt2
+++ b/root/lib/site/leftbar.tt2
@@ -57,6 +57,7 @@
Add intel/scans
Launch confirmation
IRC request
+ Planet tags
[% ELSE %]
[% IF c.user.planet %]
diff --git a/root/src/intel/planet.tt2 b/root/src/intel/planet.tt2
index 4e5eb63..86fbff9 100644
--- a/root/src/intel/planet.tt2
+++ b/root/src/intel/planet.tt2
@@ -48,6 +48,19 @@
+[% IF tags.size > 0 %]
+
+
+ Tag | Added by | Last added |
+
+ [% FOR t IN tags %]
+
+ [% t.tag %] | [% t.nicks %] | [% t.time %] |
+
+ [% END %]
+
+[% END %]
+
[% IF channelusers.size > 0 %]
diff --git a/root/src/stats/tags.tt2 b/root/src/stats/tags.tt2
new file mode 100644
index 0000000..ff96fc2
--- /dev/null
+++ b/root/src/stats/tags.tt2
@@ -0,0 +1,15 @@
+[% META title = 'Planet tags' %]
+ Picked tags: [% tags.join(', ') %]
+[% cloud %]
+
+
+
+ Coords | Tags |
+
+[% FOR p IN planets %]
+
+ [% p.coords %] |
+ [% p.tags %] |
+
+[% END %]
+
--
2.39.2