X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=NDWeb%2FPages%2FCheck.pm;h=5df1265199408f700d3f2e1531f51567331fdf56;hb=14e523fcb4dc6eb3ce780e0780959187c2610a6f;hp=38553fe3fa5800d9c921bc989c1647c64f9faf48;hpb=a0ed1cdb42b8811b549f2b5028431f3c46a7e262;p=ndwebbie.git

diff --git a/NDWeb/Pages/Check.pm b/NDWeb/Pages/Check.pm
index 38553fe..5df1265 100644
--- a/NDWeb/Pages/Check.pm
+++ b/NDWeb/Pages/Check.pm
@@ -78,7 +78,7 @@ sub render_body {
 		$extra_columns = ", planet_status,hit_us, alliance,relationship";
 	}
 
-	my $query = $DBH->prepare(qq{Select id,coords(x,y,z), ((ruler || ' OF ') || p.planet) as planet,race,
+	my $query = $DBH->prepare(qq{Select p.id,coords(x,y,z), ((ruler || ' OF ') || p.planet) as planet,race,
 		size, size_gain, size_gain_day,
 		score,score_gain,score_gain_day,
 		value,value_gain,value_gain_day,
@@ -87,7 +87,15 @@ sub render_body {
 		scorerank,scorerank_gain,scorerank_gain_day,
 		valuerank,valuerank_gain,valuerank_gain_day,
 		xprank,xprank_gain,xprank_gain_day,
-		p.value - p.size*200 - coalesce(c.metal+c.crystal+c.eonium,0)/150 - coalesce(c.structures,(SELECT avg(structures) FROM covop_targets)::int)*1500 AS fleetvalue,(c.metal+c.crystal+c.eonium)/100 AS resvalue  $extra_columns from current_planet_stats_full p LEFT OUTER JOIN covop_targets c ON p.id = c.planet where x = ? AND y = ? $where order by x,y,z asc});
+		p.value - p.size*200 - 
+			COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150 - 
+			COALESCE(ss.total ,(SELECT COALESCE(avg(total),0) FROM structure_scans)::int)*1500 AS fleetvalue
+		,(metal+crystal+eonium)/100 AS resvalue  $extra_columns 
+		FROM current_planet_stats_full p 
+			LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet
+			LEFT OUTER JOIN structure_scans ss ON p.id = ss.planet
+		WHERE x = ? AND y = ? $where ORDER BY x,y,z ASC
+	});
 
 	if (defined $z){
 		$query->execute($x,$y,$z);
@@ -130,19 +138,19 @@ sub render_body {
 
 		my $query = $DBH->prepare(q{ 
 			SELECT i.id,i.mission, i.name, i.tick AS landingtick,MIN(eta) AS eta
-				, i.amount, coords(t.x,t.y,t.z) AS target
+				, i.amount, coords(x,y,z) AS target
 			FROM fleets i
 			LEFT OUTER JOIN (planets
 				NATURAL JOIN planet_stats) t ON i.target = t.id
-					AND t.tick = ( SELECT max(tick) FROM planet_stats)
+					AND t.tick = ( SELECT MAX(tick) FROM planet_stats)
 			WHERE  i.uid = -1
 				AND i.sender = ?
 				AND (i.tick > tick() - 14 OR i.mission = 'Full Fleet')
-			GROUP BY i.id,t.x,t.y,t.z,i.mission,i.tick,i.name,i.amount,i.ingal,i.uid
-			ORDER BY i.tick,t.x,t.y,t.z
+			GROUP BY i.id,x,y,z,i.mission,i.tick,i.name,i.amount,i.ingal,i.uid
+			ORDER BY i.tick,x,y,z
 		});
 		$query->execute($planet_id);
-		my $ships = $DBH->prepare(q{SELECT ship,amount FROM fleet_ships where id = ?});
+		my $ships = $DBH->prepare(q{SELECT ship,amount FROM fleet_ships WHERE id = ?});
 		my @missions;
 		$i = 0;
 		while (my $mission = $query->fetchrow_hashref){
@@ -161,6 +169,38 @@ sub render_body {
 		}
 		$BODY->param(Missions => \@missions);
 
+		$query = $DBH->prepare(q{ 
+			SELECT i.id,i.mission, i.name, i.tick AS landingtick,MIN(eta) AS eta
+				, i.amount, coords(x,y,z) AS sender
+			FROM fleets i
+			LEFT OUTER JOIN (planets
+				NATURAL JOIN planet_stats) s ON i.sender = s.id
+					AND s.tick = ( SELECT MAX(tick) FROM planet_stats)
+			WHERE  i.uid = -1
+				AND i.target = ?
+				AND (i.tick > tick() - 14 OR i.mission = 'Full Fleet')
+			GROUP BY i.id,x,y,z,i.mission,i.tick,i.name,i.amount,i.ingal,i.uid
+			ORDER BY i.tick,x,y,z
+		});
+		$query->execute($planet_id);
+		my @incomings;
+		$i = 0;
+		while (my $mission = $query->fetchrow_hashref){
+			$mission->{ODD} = $i++ % 2;
+			$mission->{CLASS} = $mission->{mission};
+			my @ships;
+			$ships->execute($mission->{id});
+			my $j = 0;
+			while (my $ship = $ships->fetchrow_hashref){
+				$ship->{ODD} = $j++ % 2;
+				push @ships,$ship;
+			}
+			push @ships, {ship => 'No', amount => 'ships'} if @ships == 0;
+			$mission->{ships} = \@ships;
+			push @incomings,$mission;
+		}
+		$BODY->param(Incomings => \@incomings);
+
 		$query = $DBH->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats 
 			WHERE id = ? AND tick > tick() - 24});
 		$query->execute($planet_id);