From a4b209407c81ec7b71c9c7f8c4d57bb2f77256f4 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sat, 1 Nov 2008 18:10:55 +0100 Subject: [PATCH] Use new development scans --- database/clean-webbie.sql | 5 +-- database/develscan.sql | 37 ++++++++++++++++++++ lib/NDWeb/Controller/CovOp.pm | 4 +-- lib/NDWeb/Controller/Intel.pm | 6 ++-- lib/NDWeb/Controller/Raids.pm | 9 +++-- lib/NDWeb/Controller/Stats.pm | 14 ++++---- root/src/stats/planet.tt2 | 63 ++++++++++++++++------------------- scripts/scans.pl | 30 +++++++---------- 8 files changed, 96 insertions(+), 72 deletions(-) create mode 100644 database/develscan.sql diff --git a/database/clean-webbie.sql b/database/clean-webbie.sql index 2ba1118..9376126 100644 --- a/database/clean-webbie.sql +++ b/database/clean-webbie.sql @@ -7,10 +7,10 @@ TRUNCATE fleet_ships; 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; @@ -36,3 +36,4 @@ ALTER SEQUENCE raid_targets_id_seq RESTART 1; 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; diff --git a/database/develscan.sql b/database/develscan.sql new file mode 100644 index 0000000..38eeaee --- /dev/null +++ b/database/develscan.sql @@ -0,0 +1,37 @@ +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); diff --git a/lib/NDWeb/Controller/CovOp.pm b/lib/NDWeb/Controller/CovOp.pm index faf4296..ea67978 100644 --- a/lib/NDWeb/Controller/CovOp.pm +++ b/lib/NDWeb/Controller/CovOp.pm @@ -55,13 +55,13 @@ sub list : Private { , 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) diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm index 4cc3b7e..55f6643 100644 --- a/lib/NDWeb/Controller/Intel.pm +++ b/lib/NDWeb/Controller/Intel.pm @@ -289,13 +289,13 @@ sub naps : Local { ,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; diff --git a/lib/NDWeb/Controller/Raids.pm b/lib/NDWeb/Controller/Raids.pm index 98b3a39..410d42d 100644 --- a/lib/NDWeb/Controller/Raids.pm +++ b/lib/NDWeb/Controller/Raids.pm @@ -110,9 +110,9 @@ sub view : Local { , 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 @@ -121,8 +121,7 @@ sub view : Local { 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}); @@ -193,7 +192,7 @@ sub view : Local { $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; } diff --git a/lib/NDWeb/Controller/Stats.pm b/lib/NDWeb/Controller/Stats.pm index 1796605..63ad6d7 100644 --- a/lib/NDWeb/Controller/Stats.pm +++ b/lib/NDWeb/Controller/Stats.pm @@ -67,8 +67,8 @@ sub galaxy : Local { $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 }; } @@ -86,9 +86,9 @@ sub galaxy : Local { $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); @@ -181,10 +181,8 @@ sub planet : Local { 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 diff --git a/root/src/stats/planet.tt2 b/root/src/stats/planet.tt2 index fb07843..ea58187 100644 --- a/root/src/stats/planet.tt2 +++ b/root/src/stats/planet.tt2 @@ -34,57 +34,52 @@ Hidden:[% comma(planetscan.hidden) %] [% END %] -[% IF structurescan.tick %] - - +[% IF devscan.tick %] +
Tick:[% structurescan.tick %]
+ - - - + + + - + - + - - - + + + - - - + + + -
Tick:[% devscan.tick %]
Factories
LightMediumHeavy
[% structurescan.light_fac %][% structurescan.medium_fac %][% structurescan.heavy_fac %]
[% devscan.light_fac %][% devscan.medium_fac %][% devscan.heavy_fac %]
AmpsDistorters
[% structurescan.amps %]
[% devscan.amps %] [% structurescan.distorters %][% devscan.distorters %]
Refinaries
MetalCrystalEonium
[% structurescan.metal_ref %][% structurescan.crystal_ref %][% structurescan.eonium_ref %]
[% devscan.metal_ref %][% devscan.crystal_ref %][% devscan.eonium_ref %]
ReslabsFinanceSeccents
[% structurescan.reslabs %][% structurescan.fincents %][% structurescan.seccents %]
[% devscan.reslabs %][% devscan.fincents %][% devscan.seccents %]
-[% END %] -[% IF techscan.tick %] - - - - - + + + - - + + - - + + - - + + - - + + - - + + - - + +
Tick:[% techscan.tick %]
ResearchLevel
Travel Time[% techscan.travel %]
ResearchLevel
Travel Time[% devscan.travel %]
Infrastructure[% techscan.infra %]
Infrastructure[% devscan.infra %]
Hulls[% techscan.hulls %]
Hulls[% devscan.hulls %]
Waves[% techscan.waves %]
Waves[% devscan.waves %]
Core Extraction[% techscan.extraction %]
Core Extraction[% devscan.extraction %]
Covert Ops[% techscan.covert %]
Covert Ops[% devscan.covert %]
Mining[% techscan.mining %]
Mining[% devscan.mining %]
[% END %] diff --git a/scripts/scans.pl b/scripts/scans.pl index c6d8fca..818be59 100755 --- a/scripts/scans.pl +++ b/scripts/scans.pl @@ -26,7 +26,10 @@ use CGI; 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(); @@ -77,15 +80,12 @@ my $addplanetscan = $dbh->prepare(q{INSERT INTO planet_scans (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; @@ -184,21 +184,15 @@ while (my $scan = $newscans->fetchrow_hashref){ $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]| )+)(\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]| )+)(\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; -- 2.39.2