Converted .getships and .shipshome

[NDIRC.git] / Commands / Usermgm.pm

diff --git a/Commands/Usermgm.pm b/Commands/Usermgm.pm
index be87acb886493a059a1b2c33284f957e6d917b10..101ea06910d949b30a974c67a24c4ec2e62bf5e4 100644 (file)
--- a/Commands/Usermgm.pm
+++ b/Commands/Usermgm.pm
@@ -162,4 +162,133 @@ GROUP BY username,pnick,hostmask LIMIT 5
        }
 }
 
+sub flag
+       : Help(syntax: .flag flag | Lists all users with the given flag.)
+       : ACL(irc_flag)
+{
+       my ($self,$c,$msg) = @_;
+       my ($flag) = $msg =~ /^(\w)$/ or die 'ARGS';
+
+       my $f = $c->model->prepare(q{
+SELECT TRIM(', ' FROM concat(username||', ')),count(username)
+FROM (SELECT uid, username FROM users ORDER BY username) u
+       JOIN groupmembers gm USING (uid)
+       JOIN groups g USING (gid)
+WHERE flag = $1
+               });
+       my ($users,$count) = $c->model->selectrow_array($f,undef,$flag);
+       $c->reply("<b>$count</b> Users with flag <b>$flag</b>: $users");
+}
+
+sub laston
+       : Help(syntax: .laston flag [days] | lists users and the number of days since they were last seen (irc|forum|claim). If days is specified it will only list users with at least that amount of idle time. Days can also specify forum and claim with irc|forum|claim syntax)
+       : ACL(irc_laston)
+{
+       my ($self,$c,$msg) = @_;
+       my ($flag,$min,$forum,$claim) = $msg =~ /^(\w)(?: (\d+)(?:\|(\d+)\|(\d+))?)?$/
+               or die 'ARGS';
+       $min //= 0;
+
+       my $f = $c->model->prepare(q{
+SELECT username, COALESCE(last::text,'?') AS last
+       ,COALESCE(lastforum::text,'?') AS lastforum
+       ,COALESCE(lastclaim::text,'?') AS lastclaim
+FROM (SELECT username
+               ,date_part('day',now() - laston)::int AS last
+               ,date_part('day',now() -
+                       (SELECT max(time) FROM forum_thread_visits WHERE uid = u.uid)) AS lastforum
+               ,date_part('day',now() -
+                       (SELECT max(timestamp) FROM raid_claims WHERE uid = u.uid)) AS lastclaim
+       FROM users u
+               NATURAL JOIN groupmembers
+               NATURAL JOIN groups
+       WHERE flag = $1
+       ) a
+WHERE COALESCE(last >= $2,TRUE) AND COALESCE(lastforum >= $3,TRUE)
+       AND COALESCE(lastclaim >= $4,TRUE)
+ORDER BY last DESC, lastforum DESC, lastclaim DESC
+               });
+       $f->execute($flag,$min,$forum,$claim);
+
+       my $text;
+       my $i = 0;
+       while (my $user = $f->fetchrow_hashref){
+               $text .= "$user->{username}($user->{last}|$user->{lastforum}|$user->{lastclaim}) ";
+               $i++;
+       }
+       $c->reply("<b>$i</b> Users(days) with flag <b>$flag</b>: $text");
+}
+
+
+sub lastseen
+       : Help(syntax: .lastseen username | Shows the number of days since the user(s) last visited irc, forum and raids)
+       : ACL(irc_lastseen)
+{
+       my ($self,$c,$msg) = @_;
+       my ($username) = $msg =~ /^(\S+)$/ or die 'ARGS';
+
+       my $f = $c->model->prepare(q{
+SELECT username, COALESCE(date_part('day',now() - laston)::text,'?') AS last
+       ,COALESCE(date_part('day',now() - (SELECT max(time)
+               FROM forum_thread_visits WHERE uid = u.uid))::text,'?') AS lastforum
+       ,COALESCE(date_part('day',now() - (SELECT max(timestamp)
+               FROM raid_claims WHERE uid = u.uid))::text,'?') AS lastclaim
+FROM users u
+WHERE username ILIKE $1 ORDER BY lower(username)
+               });
+       $f->execute($username);
+
+       my $text;
+       my $i = 0;
+       while (my $user = $f->fetchrow_hashref){
+               $text .= "$user->{username}($user->{last}|$user->{lastforum}|$user->{lastclaim}) ";
+               $i++;
+       }
+       $c->reply("<b>$i</b> Users(days): $text");
+}
+
+sub getships
+       : Help(Usage: .getships ship | % can be used as wildcard, e.g. beet%, shipshome shows the number of ships currently home)
+       : Alias(shipshome)
+       : ACL(irc_getships)
+{
+       my ($self,$c,$msg) = @_;
+       my ($ship) = $msg =~ /^(\S+)$/ or die 'ARGS';
+       my $dbh = $c->model;
+
+       my $f = $dbh->prepare(q{
+SELECT username,SUM(fs.amount) AS amount
+FROM users u
+       JOIN (SELECT DISTINCT ON (planet) planet,fid FROM fleets
+               WHERE mission = 'Full fleet' AND name <> 'Unit'
+               ORDER BY planet,tick DESC,fid DESC
+       ) f  USING (planet)
+       JOIN fleet_ships fs USING (fid)
+WHERE ship ILIKE $1 AND uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
+GROUP BY username ORDER BY amount DESC
+               });
+       if ($self->name eq 'shipshome'){
+               $f = $dbh->prepare(q{
+SELECT username,SUM(amount) AS amount
+FROM available_ships
+WHERE ship ILIKE ? AND uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
+GROUP BY username ORDER BY amount DESC
+               });
+       }
+       $f->execute($ship);
+       my $text;
+       my $i = 0;
+       my $total = 0;
+       while (my $user = $f->fetchrow_hashref){
+               $text .= "$user->{username}: $user->{amount} ";
+               $i++;
+               $total += $user->{amount};
+       }
+       if ($text){
+               $c->reply("<b>$i</b> Users with <b>$total $ship</b>: $text");
+       }else{
+               $c->reply("Couldn't find any user with <b>$ship</b>");
+       }
+}
+
 1;