TRUNCATE incomings;
TRUNCATE raid_claims;
/*TRUNCATE dumps;*/
+TRUNCATE scan_requests;
TRUNCATE fleet_scans;
TRUNCATE planet_scans;
-TRUNCATE structure_scans;
-TRUNCATE tech_scans;
+TRUNCATE development_scans;
TRUNCATE irc_requests;
UPDATE users SET scan_points = 0, defense_points = 0, attack_points = 0, humor_points = 0, rank = NULL, planet = NULL;
DELETE FROM scans;
ALTER SEQUENCE raids_id_seq RESTART 1;
ALTER SEQUENCE scans_id_seq RESTART 1;
ALTER SEQUENCE irc_requests_id_seq RESTART 1;
+ALTER SEQUENCE scan_requests_id_seq RESTART 1;
--- /dev/null
+DROP VIEW current_structure_scans;
+DROP VIEW current_tech_scans;
+DROP TABLE structure_scans;
+DROP TABLE tech_scans;
+
+CREATE TABLE development_scans (
+ id integer PRIMARY KEY REFERENCES scans(id),
+ planet integer NOT NULL REFERENCES planets(id),
+ tick integer NOT NULL,
+ light_fac integer NOT NULL,
+ medium_fac integer NOT NULL,
+ heavy_fac integer NOT NULL,
+ amps integer NOT NULL,
+ distorters integer NOT NULL,
+ metal_ref integer NOT NULL,
+ crystal_ref integer NOT NULL,
+ eonium_ref integer NOT NULL,
+ reslabs integer NOT NULL,
+ fincents integer NOT NULL,
+ seccents integer NOT NULL,
+ total integer NOT NULL,
+
+ travel integer NOT NULL,
+ infra integer NOT NULL,
+ hulls integer NOT NULL,
+ waves integer NOT NULL,
+ extraction integer NOT NULL,
+ covert integer NOT NULL,
+ mining integer NOT NULL
+);
+
+CREATE OR REPLACE VIEW current_development_scans AS
+SELECT DISTINCT ON (planet) ds.*
+FROM development_scans ds
+ORDER BY planet, tick DESC, id DESC;
+
+CREATE INDEX development_scans_planet_index ON development_scans(planet,tick);
, distorters,gov
, MaxResHack,co.tick AS lastcovop
FROM (SELECT p.id,coords(x,y,z),size, metal,crystal,eonium,guards
- ,seccents,NULLIF(ss.total::integer,0) AS structures,distorters
+ ,seccents,NULLIF(ds.total::integer,0) AS structures,distorters
,max_bank_hack(metal,crystal,eonium,p.value
,(SELECT value FROM current_planet_stats WHERE id = ?)) AS MaxResHack
, planet_status, relationship,gov
FROM current_planet_stats p
LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_structure_scans ss ON p.id = ss.planet
+ LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
) AS foo
LEFT OUTER JOIN (SELECT id,max(tick) AS tick FROM covop_attacks GROUP BY id) co USING (id)
WHERE (metal IS NOT NULL OR seccents IS NOT NULL)
,ruler, p.planet,race, size, score, value
, xp, sizerank, scorerank, valuerank, xprank, 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
+ - COALESCE(ds.total ,(SELECT COALESCE(avg(total),0)
+ FROM current_development_scans)::int)*1500 AS fleetvalue
,(metal+crystal+eonium)/100 AS resvalue, planet_status,hit_us
, alliance,relationship,nick
FROM current_planet_stats p
LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_structure_scans ss ON p.id = ss.planet
+ LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
WHERE planet_status IN ('Friendly','NAP') order by x,y,z asc
});
$query->execute;
, p.x,p.y,p.z, race
, p.value - p.size*200 -
COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150 -
- COALESCE(ss.total ,(SELECT
+ COALESCE(ds.total ,(SELECT
COALESCE(avg(total),0) FROM
- structure_scans)::int)*1500 AS fleetvalue
+ current_development_scans)::int)*1500 AS fleetvalue
,(metal+crystal+eonium)/100 AS resvalue, comment
, hidden, light, medium, heavy, metal, crystal, eonium
,metal_roids, crystal_roids, eonium_roids
FROM current_planet_stats p
JOIN raid_targets r ON p.id = r.planet
LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_structure_scans ss ON p.id = ss.planet
- LEFT OUTER JOIN current_tech_scans ts ON p.id = ts.planet
+ LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
WHERE r.raid = $1
AND NOT COALESCE(p.x = $2 AND p.y = $3,False)
ORDER BY size});
$target->{resvalue} = floor($target->{resvalue}/$num)*$num;
}
$target->{comment} = parseMarkup($target->{comment}) if ($target->{comment});
- $target->{hidden} = int($target->{hidden} / 100);
+ $target->{hidden} = int($target->{hidden} / 100) if $target->{hidden};
push @targets,$target;
}
$extra_columns .= q{
,gov, 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
+ COALESCE(ds.total ,(SELECT COALESCE(avg(total),0)
+ FROM current_development_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
+ LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
WHERE x = ? AND y = ? AND COALESCE(z = ?,TRUE)
- ORDER BY x,y,z,p.id,ps.tick DESC, ps.id DESC, ss.tick DESC, ss.id DESC
+ ORDER BY x,y,z,p.id,ps.tick DESC, ps.id DESC, ds.tick DESC, ds.id DESC
});
$query->execute($x,$y,$z);
if ($c->check_user_roles(qw/stats_planetdata/)){
$c->stash(planetscan => $dbh->selectrow_hashref(q{SELECT *
FROM current_planet_scans WHERE planet = $1},undef,$id));
- $c->stash(structurescan => $dbh->selectrow_hashref(q{SELECT *
- FROM current_structure_scans WHERE planet = $1},undef,$id));
- $c->stash(techscan => $dbh->selectrow_hashref(q{SELECT *
- FROM current_tech_scans WHERE planet = $1},undef,$id));
+ $c->stash(devscan => $dbh->selectrow_hashref(q{SELECT *
+ FROM current_development_scans WHERE planet = $1},undef,$id));
}
my $query = $dbh->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats
<tr><th>Hidden:</th><td colspan="2" align="right">[% comma(planetscan.hidden) %]</td></tr>
</table>
[% END %]
-[% IF structurescan.tick %]
-<table [% IF structurescan.tick < TICK - 24 %] class="old"[% END %]>
- <tr><th>Tick:</th><td>[% structurescan.tick %]</td></tr>
+[% IF devscan.tick %]
+<table [% IF devscan.tick < TICK - 24 %] class="old"[% END %]>
+ <tr><th>Tick:</th><td>[% devscan.tick %]</td></tr>
<tr><th colspan="3">Factories</th></tr>
<tr><th>Light</th><th>Medium</th><th>Heavy</th></tr>
- <tr align="right"><td>[% structurescan.light_fac %]</td>
- <td>[% structurescan.medium_fac %]</td>
- <td>[% structurescan.heavy_fac %]</td>
+ <tr align="right"><td>[% devscan.light_fac %]</td>
+ <td>[% devscan.medium_fac %]</td>
+ <td>[% devscan.heavy_fac %]</td>
</tr>
<tr><th>Amps</th><th></th><th>Distorters</th></tr>
- <tr align="right"><td>[% structurescan.amps %]</td>
+ <tr align="right"><td>[% devscan.amps %]</td>
<td></td>
- <td>[% structurescan.distorters %]</td>
+ <td>[% devscan.distorters %]</td>
</tr>
<tr><th colspan="3">Refinaries</th></tr>
<tr><th>Metal</th><th>Crystal</th><th>Eonium</th></tr>
- <tr align="right"><td>[% structurescan.metal_ref %]</td>
- <td>[% structurescan.crystal_ref %]</td>
- <td>[% structurescan.eonium_ref %]</td>
+ <tr align="right"><td>[% devscan.metal_ref %]</td>
+ <td>[% devscan.crystal_ref %]</td>
+ <td>[% devscan.eonium_ref %]</td>
</tr>
<tr><th>Reslabs</th><th>Finance</th><th>Seccents</th></tr>
- <tr align="right"><td>[% structurescan.reslabs %]</td>
- <td>[% structurescan.fincents %]</td>
- <td>[% structurescan.seccents %]</td>
+ <tr align="right"><td>[% devscan.reslabs %]</td>
+ <td>[% devscan.fincents %]</td>
+ <td>[% devscan.seccents %]</td>
</tr>
-</table>
-[% END %]
-[% IF techscan.tick %]
-<table [% IF techscan.tick < TICK - 24 %] class="old"[% END %]>
- <tr><th>Tick:</th><td>[% techscan.tick %]</td></tr>
- <tr><th>Research</th><th>Level</th></tr>
- <tr><td>Travel Time</td>
- <td align="right">[% techscan.travel %]</td>
+ <tr><th colspan="2">Research</th><th>Level</th></tr>
+ <tr><td colspan="2">Travel Time</td>
+ <td align="right">[% devscan.travel %]</td>
</tr>
- <tr><td>Infrastructure</td>
- <td align="right">[% techscan.infra %]</td>
+ <tr><td colspan="2">Infrastructure</td>
+ <td align="right">[% devscan.infra %]</td>
</tr>
- <tr><td>Hulls</td>
- <td align="right">[% techscan.hulls %]</td>
+ <tr><td colspan="2">Hulls</td>
+ <td align="right">[% devscan.hulls %]</td>
</tr>
- <tr><td>Waves</td>
- <td align="right">[% techscan.waves %]</td>
+ <tr><td colspan="2">Waves</td>
+ <td align="right">[% devscan.waves %]</td>
</tr>
- <tr><td>Core Extraction</td>
- <td align="right">[% techscan.extraction %]</td>
+ <tr><td colspan="2">Core Extraction</td>
+ <td align="right">[% devscan.extraction %]</td>
</tr>
- <tr><td>Covert Ops</td>
- <td align="right">[% techscan.covert %]</td>
+ <tr><td colspan="2">Covert Ops</td>
+ <td align="right">[% devscan.covert %]</td>
</tr>
- <tr><td>Mining</td>
- <td align="right">[% techscan.mining %]</td>
+ <tr><td colspan="2">Mining</td>
+ <td align="right">[% devscan.mining %]</td>
</tr>
</table>
[% END %]
use DBI;
use DBD::Pg qw(:pg_types);
use LWP::Simple;
-use lib qw{/var/www/ndawn/lib/};
+
+use FindBin;
+use lib "$FindBin::Bin/../lib";
+
use ND::DB;
our $dbh = ND::DB::DB();
(id,tick,planet,metal_roids,metal,crystal_roids,crystal,eonium_roids,eonium
,agents,guards,light,medium,heavy,hidden)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
-my $addstrucscan = $dbh->prepare(q{INSERT INTO structure_scans
+my $adddevscan = $dbh->prepare(q{INSERT INTO development_scans
(id,tick,planet,light_fac,medium_fac,heavy_fac,amps,distorters
- ,metal_ref,crystal_ref,eonium_ref,reslabs,fincents,seccents,total)
- VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
-
-my $addtechscan = $dbh->prepare(q{INSERT INTO tech_scans
- (id,tick,planet,travel,infra,hulls,waves,extraction,covert,mining)
- VALUES(?,?,?,?,?,?,?,?,?,?)});
-
+ ,metal_ref,crystal_ref,eonium_ref,reslabs,fincents,seccents
+ ,travel,infra,hulls,waves,extraction,covert,mining,total)
+ VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
+ });
$dbh->begin_work or die 'No transaction';
$newscans->execute or die $dbh->errstr;
$fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
}
}
- } elsif($type eq 'Surface Analysis'){
+ } elsif($type eq 'Development'){
my @values = ($scan->{id},$tick,$planet);
my $total = 0;
while($file =~ m{((?:[a-zA-Z]| )+)</t[dh]><td(?: class="right")?>(\d+)}sg){
push @values,$2;
- $total += $2;
+ $total += $2 if $#values <= 13;
}
push @values,$total;
- $addstrucscan->execute(@values);
- } elsif($type eq 'Technology Analysis'){
- my @values = ($scan->{id},$tick,$planet);
- while($file =~ m{((?:[a-zA-Z]| )+)</t[dh]><td(?: class="right")?>(\d+)}sg){
- push @values,$2;
- }
- $addtechscan->execute(@values);
+ $adddevscan->execute(@values);
} elsif($type eq 'Unit' || $type eq 'Advanced Unit'){
my $id = addfleet($type,'Full fleet',$file,$planet,undef,$tick,undef,undef,undef);
$fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
projects / ndwebbie.git / commitdiff