From: Michael Andreen <harv@ruin.nu>
Date: Fri, 8 Aug 2008 12:47:20 +0000 (+0200)
Subject: Get agent and guard information from planet scans
X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=635211f6d29f02fb994fff49684cf6c39b50b598

Get agent and guard information from planet scans
---

diff --git a/database/covop.sql b/database/covop.sql
new file mode 100644
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 5b9e74e..bedd3df 100755
--- 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}