X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=Commands%2FUsermgm.pm;h=101ea06910d949b30a974c67a24c4ec2e62bf5e4;hb=2cc4a8683c21bee13c36eb4c24954d7f57f4e967;hp=8a87f8e1fae85147459336c9e4bcf654420c6f29;hpb=5f7e87f30570220f44035831a51c881ee3ca34b7;p=NDIRC.git

diff --git a/Commands/Usermgm.pm b/Commands/Usermgm.pm
index 8a87f8e..101ea06 100644
--- a/Commands/Usermgm.pm
+++ b/Commands/Usermgm.pm
@@ -180,4 +180,115 @@ WHERE flag = $1
 	$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;