Get agent and guard information from planet scans

diff --git a/database/covop.sql b/database/covop.sql
new file mode 100644 (file)
index 0000000..67d9257
--- /dev/null
+++ b/database/covop.sql
@@ -0,0 +1,24 @@
+/*
+INSERT INTO planet_data_types (category,name) values('planet','Agents');
+INSERT INTO planet_data_types (category,name) values('planet','Security Guards');
+ */
+
+DROP VIEW planet_scans;
+
+CREATE OR REPLACE VIEW planet_scans AS
+    SELECT DISTINCT ON (s.planet) s.id, s.planet, s.tick, m.metal, c.crystal, e.eonium, mr.metal_roids, cr.crystal_roids, er.eonium_roids
+               ,h.hidden, fl.light, fm.medium, fh.heavy, a.agents, g.guards
+       FROM
+               (scans s JOIN (SELECT planet_data.scan AS id, planet_data.amount AS metal_roids FROM planet_data WHERE (planet_data.rid = 1)) mr USING (id))
+               JOIN (SELECT planet_data.scan AS id, planet_data.amount AS crystal_roids FROM planet_data WHERE (planet_data.rid = 2)) cr USING (id)
+               JOIN (SELECT planet_data.scan AS id, planet_data.amount AS eonium_roids FROM planet_data WHERE (planet_data.rid = 3)) er USING (id)
+               JOIN (SELECT planet_data.scan AS id, planet_data.amount AS metal FROM planet_data WHERE (planet_data.rid = 4)) m USING (id)
+               JOIN (SELECT planet_data.scan AS id, planet_data.amount AS crystal FROM planet_data WHERE (planet_data.rid = 5)) c USING (id)
+               JOIN (SELECT planet_data.scan AS id, planet_data.amount AS eonium FROM planet_data WHERE (planet_data.rid = 6)) e USING (id)
+               JOIN (SELECT planet_data.scan AS id, planet_data.amount AS hidden FROM planet_data WHERE (planet_data.rid = 25)) h USING (id)
+               LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS light FROM planet_data WHERE (planet_data.rid = 26)) fl USING (id)
+               LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS medium FROM planet_data WHERE (planet_data.rid = 27)) fm USING (id)
+               LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS heavy FROM planet_data WHERE (planet_data.rid = 28)) fh USING (id)
+               LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS agents FROM planet_data WHERE (planet_data.rid = 29)) a USING (id)
+               LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS guards FROM planet_data WHERE (planet_data.rid = 30)) g USING (id)
+       ORDER BY s.planet, s.tick DESC, s.id DESC;

diff --git a/scripts/scans.pl b/scripts/scans.pl
index 5b9e74eaf3a535ae718326416688aa18260f960c..bedd3df5cdf33e05d2b7108845680323d78d77dc 100755 (executable)
--- a/scripts/scans.pl
+++ b/scripts/scans.pl
@@ -114,15 +114,21 @@ while (my $scan = $newscans->fetchrow_hashref){
                        #TODO: something about planet being closed?
                }
                if ($type eq 'Planet'){
-                       $file =~ s/(\d),(\d)/$1$2/g;
                        while($file =~ m/"center">(Metal|Crystal|Eonium)\D+(\d+)\D+([\d,]+)/g){
-                               my ($roids,$res) = ($2,$3);
+                               my ($type,$roids,$res) = ($1,$2,$3);
                                $roids =~ s/,//g;
                                $addpdata->execute($planet,$tick,$scan->{id}
-                                       ,'roid',$1, $roids) or die $dbh->errstr;
+                                       ,'roid',$type, $roids) or die $dbh->errstr;
                                $res =~ s/,//g;
                                $addpdata->execute($planet,$tick,$scan->{id}
-                                       ,'resource',$1, $res) or die $dbh->errstr;
+                                       ,'resource',$type, $res) or die $dbh->errstr;
+                       }
+                       if($file =~ m{Security\ Guards .+? "center">(\d+)</td>
+                                       .+? "center">(\d+)</td>}sx){
+                               $addpdata->execute($planet,$tick,$scan->{id}
+                                       ,'planet','Agents', $1) or die $dbh->errstr;
+                               $addpdata->execute($planet,$tick,$scan->{id}
+                                       ,'planet','Security Guards', $2) or die $dbh->errstr;
                        }
                        if($file =~ m{<td class="center">([A-Z][a-z]+)</td><td class="center">([A-Z][a-z]+)</td><td class="center">([A-Z][a-z]+)</td>}){
                                $addpdata->execute($planet,$tick,$scan->{id}