From: Michael Andreen Date: Wed, 26 Aug 2009 13:23:58 +0000 (+0200) Subject: Support for planet tags X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=88534ccb5763a823eec9147f63c732588e275e6a Support for planet tags --- 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 %] + + + + + [% FOR t IN tags %] + + + + [% END %] +
    TagAdded byLast added
    [% t.tag %][% t.nicks %][% t.time %]
    +[% 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 %] + +

    + + + +[% FOR p IN planets %] + + + + +[% END %] +
    CoordsTags
    [% p.coords %][% p.tags %]