X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=NDWeb%2FPages%2FCheck.pm;h=a0062ab3568b7a67327e171ecdf68d2cff7b67ea;hb=62e8f0d857b12761d85b8c97ffab2bc02d4b6777;hp=38553fe3fa5800d9c921bc989c1647c64f9faf48;hpb=a0ed1cdb42b8811b549f2b5028431f3c46a7e262;p=ndwebbie.git diff --git a/NDWeb/Pages/Check.pm b/NDWeb/Pages/Check.pm index 38553fe..a0062ab 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,gov, 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); @@ -99,7 +107,6 @@ sub render_body { } my @planets; my $planet_id = undef; - my $i = 0; while (my $planet = $query->fetchrow_hashref){ $planet_id = $planet->{id}; for my $type (qw/size score value xp/){ @@ -114,12 +121,10 @@ sub render_body { } } if ($self->isMember && ($self->isOfficer || $self->isBC)){ - if ($z && $planet->{alliance} eq 'NewDawn' && not ($self->isHC || $self->isOfficer)){ + if ($z && defined $planet->{alliance} && $planet->{alliance} eq 'NewDawn' && not ($self->isHC || $self->isOfficer)){ log_message $ND::UID,"BC browsing ND planet $planet->{coords} tick $self->{TICK}"; } } - $i++; - $planet->{ODD} = $i % 2; delete $planet->{id}; push @planets,$planet; } @@ -130,29 +135,27 @@ 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 + 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 $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 = ? ORDER BY num + }); my @missions; - $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; @@ -161,6 +164,34 @@ 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; + while (my $mission = $query->fetchrow_hashref){ + $mission->{CLASS} = $mission->{mission}; + my @ships; + $ships->execute($mission->{id}); + while (my $ship = $ships->fetchrow_hashref){ + 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); @@ -178,9 +209,7 @@ sub render_body { }); $query->execute($planet_id); my @scans; - $i = 0; while (my $scan = $query->fetchrow_hashref){ - $scan->{ODD} = $i++ % 2; push @scans,$scan; } $BODY->param(Scans => \@scans); @@ -198,6 +227,18 @@ sub render_body { } $BODY->param(OldCoords => \@coords); + $query = $DBH->prepare(q{SELECT DISTINCT ON(rid) tick,category,name,amount + FROM planet_data pd JOIN planet_data_types pdt ON pd.rid = pdt.id + WHERE pd.id = $1 ORDER BY rid,tick DESC + }); + $query->execute($planet_id); + my @pdata; + while (my $data = $query->fetchrow_hashref){ + $data->{amount} =~ s/(^[-+]?\d+?(?=(?>(?:\d{3})+)(?!\d))|\G\d{3}(?=\d))/$1,/g; #Add comma for ever 3 digits, i.e. 1000 => 1,000 + push @pdata,$data; + } + $BODY->param(PlanetData => \@pdata); + } $query = $DBH->prepare(q{SELECT x,y, size, size_gain, size_gain_day, @@ -216,7 +257,6 @@ sub render_body { $query->execute($x,$y) or $ND::ERROR .= p($DBH->errstr); my @galaxies; - $i = 0; while (my $galaxy = $query->fetchrow_hashref){ for my $type (qw/planets size score xp value/){ #$galaxy->{$type} = prettyValue($galaxy->{$type}); @@ -233,8 +273,6 @@ sub render_body { $galaxy->{$type} =~ s/(^[-+]?\d+?(?=(?>(?:\d{3})+)(?!\d))|\G\d{3}(?=\d))/$1,/g; #Add comma for ever 3 digits, i.e. 1000 => 1,000 } } - $i++; - $galaxy->{ODD} = $i % 2; push @galaxies,$galaxy; } $BODY->param(Galaxies => \@galaxies);