$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,
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);
}
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/){
}
}
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;
}
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;
}
$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);
});
$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);
}
$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,
$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});
$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);
projects / ndwebbie.git / blobdiff