New mail report optimized for Discord

diff --git a/database/r100.sql b/database/r100.sql
index debbc44804e276ab6d7146474efb35f2893c02bb..feebfdf157e39964efe2e4d975fa00f8b4444983 100644 (file)
--- a/database/r100.sql
+++ b/database/r100.sql
@@ -36,5 +36,6 @@ ALTER TABLE ship_stats ALTER COLUMN cloaked DROP DEFAULT, ALTER COLUMN baseeta D
 DROP TYPE race;
 DROP TYPE govs;
 
+ALTER TABLE incomings DROP CONSTRAINT incomings_call_key, ADD CONSTRAINT incomings_unique_key UNIQUE (call, pid, fleet, eta);
 
 \i functions/hostile_alliances.sql

diff --git a/scripts/ndreport.pl b/scripts/ndreport.pl
index 8f2cd3b7aaeb541a07266a2605ff19246c1d61d0..b93eebc63f7ea9a3fd717f644b77e429db29a038 100755 (executable)
--- a/scripts/ndreport.pl
+++ b/scripts/ndreport.pl
@@ -26,6 +26,7 @@ use local::lib;
 use DBI;
 use DBD::Pg qw(:pg_types);
 use CGI qw/:standard/;
+use Data::Dumper;
 
 use Email::Simple;
 use Encode;
@@ -48,29 +49,97 @@ my $email = Email::Simple->new($text);
 
 my $body =  decode('ISO-8859-1', $email->body);
 
+unless ($body =~ /Report of events in tick (\d+)/) {
+       exit 0;
+}
+
+my $tick = $1;
+
 my $c = $dbh->prepare(q{
-SELECT coords(x,y,z) FROM current_planet_stats WHERE pid = (SELECT pid FROM users WHERE username = $1
-       AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M'))
+SELECT coords(x,y,z), uid, pid
+FROM users u
+       LEFT JOIN current_planet_stats p USING (pid)
+WHERE username = $1
+       AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
 });
+my ($coords, $uid, $pid) = $dbh->selectrow_array($c, undef, $user);
 
-my $a = $dbh->prepare(q{
-SELECT race, $1 - tick() FROM current_planet_stats WHERE x = $2 AND y = $3 AND z = $4
+$coords //= '(no coords entered)';
+
+my $attacker = $dbh->prepare(q{
+SELECT pid, race FROM current_planet_stats WHERE x = $1 AND y = $2 AND z = $3
 });
 
 my $report = $dbh->prepare(q{INSERT INTO irc_requests (channel,uid,message) VALUES('def',$1,$2)});
 
+
+my $message = "Tick **$tick**: *$user* $coords has incs\n";
+
+my $add_inc = $dbh->prepare(q{INSERT INTO incomings (call,pid,eta,amount,fleet) VALUES($1,$2,$3,$4,$5)});
+
+my $find_call = $dbh->prepare(q{
+SELECT call, count(*) AS count
+FROM calls
+       JOIN incomings i USING (call)
+WHERE uid = $1 AND landing_tick = $2
+GROUP BY call
+});
+my $add_call = $dbh->prepare(q{
+INSERT INTO calls
+       (uid,landing_tick,info)
+       VALUES($1,$2,'')
+RETURNING call
+});
+
+my $fleetcatch = $dbh->prepare(q{
+SELECT count(*) FROM launch_confirmations WHERE uid = $1 AND back = $2
+});
+
+
+my %incs;
 while($body =~ /jumpgate from (.+?), located at (\d+):(\d+):(\d+).+?our system in tick (\d+) and appears to have (\d+)/sg){
-       my ($fleet, $x,$y,$z,$tick, $amount) = ($1,$2,$3,$4,$5,$6);
+       my ($fleet, $x,$y,$z,$landing_tick, $amount) = ($1,$2,$3,$4,$5,$6);
 
-       my ($coords) = $dbh->selectrow_array($c, undef, $user);
+       my $eta = $landing_tick - $tick;
 
-       $coords //= '(no coords entered)';
+       my ($pid, $race) = $dbh->selectrow_array($attacker,undef, $x,$y,$z);
 
-       my ($race,$eta) = $dbh->selectrow_array($a,undef, $tick,$x,$y,$z);
+       push @{$incs{$landing_tick}}, {x => $x, y => $y, z => $z, eta => $eta, amount => $amount, race => $race, fleet => $fleet, pid => $pid};
+}
 
-       $report->execute(-5,"$user has incs: $coords $x:$y:$z $fleet $race $amount $eta");
+for my $landing_tick (sort keys %incs) {
+       $message .= "\n> Landing tick: *$landing_tick*";
+       my $call;
+       if ($uid) {
+               my ($returning) = $dbh->selectrow_array($fleetcatch, undef, $uid, $landing_tick);
+               if ($returning > 0) {
+                       $message .= "\n**THIS IS A POSSIBLE FLEETCATCH!**"
+               }
+               if (my $c = $dbh->selectrow_hashref($find_call, undef, $uid, $landing_tick)) {
+                       $call = $c->{call};
+                       $message .= "\n__Previously an addtional **$c->{count}** fleets landing on this tick__"
+               }
+               else
+               {
+                       ($call) = $dbh->selectrow_array($add_call, undef, $uid, $landing_tick);
+               }
+       }
+       $message .= "\n```";
+       for my $i (@{$incs{$landing_tick}}) {
+               if ($uid)
+               {
+                       $add_inc->execute($call, $i->{pid}, $i->{eta}, $i->{amount}, $i->{fleet});
+               }
+               $message .= "\n$i->{x}:$i->{y}:$i->{z} $i->{race} $i->{eta} $i->{amount} $i->{fleet} "
+       }
+       $message .= "```";
 }
 
+
+#print $message;
+
+$report->execute(-5,$message);
+
 $dbh->disconnect;
 
 system 'killall','-USR1', 'ndbot.pl';