$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,
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 $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){
}
$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);
INSERT INTO planet_data_types (category,name) VALUES('struc','Security Centre');
CREATE TABLE planet_data (
- id SERIAL PRIMARY KEY,
- uid INTEGER NOT NULL REFERENCES users(uid),
- planet INTEGER NOT NULL REFERENCES planets(id),
+ id INTEGER NOT NULL REFERENCES planets(id),
+ scan INTEGER NOT NULL REFERENCES scans(id),
tick INTEGER NOT NULL,
rid INTEGER NOT NULL REFERENCES planet_data_types(id),
- amount INTEGER NOT NULL
-) WITHOUT OIDS;
-
-CREATE TABLE data_scans (
- id INTEGER PRIMARY KEY REFERENCES planet_data(id),
- scan INTEGER NOT NULL REFERENCES scans(id)
+ amount INTEGER NOT NULL,
+ PRIMARY KEY(rid,scan)
) WITHOUT OIDS;
DROP TABLE intel;
CREATE INDEX fleets_sender_index ON fleets (sender);
CREATE INDEX fleets_mission_index ON fleets (mission);
CREATE INDEX fleets_ingal_index ON fleets (ingal);
+
+DROP TABLE covop_targets ;
+
+CREATE OR REPLACE VIEW planet_scans AS
+SELECT DISTINCT ON (planet) id,planet,tick,metal,crystal,eonium,metal_roids,crystal_roids,eonium_roids
+FROM scans s
+ JOIN (SELECT scan AS id,amount AS metal_roids FROM planet_data
+ WHERE rid = 1) AS mr USING (id)
+ JOIN (SELECT scan AS id,amount AS crystal_roids FROM planet_data
+ WHERE rid = 2) AS cr USING (id)
+ JOIN (SELECT scan AS id,amount AS eonium_roids FROM planet_data
+ WHERE rid = 3) AS er USING (id)
+ JOIN (SELECT scan AS id,amount AS metal FROM planet_data
+ WHERE rid = 4) AS m USING (id)
+ JOIN (SELECT scan AS id,amount AS crystal FROM planet_data
+ WHERE rid = 5) AS c USING (id)
+ JOIN (SELECT scan AS id,amount AS eonium FROM planet_data
+ WHERE rid = 6) AS e USING (id)
+ORDER BY planet,tick DESC,id DESC;
+
+CREATE OR REPLACE VIEW structure_scans AS
+SELECT DISTINCT ON (planet) id,planet,tick, total,distorters,seccents
+FROM scans s
+ JOIN (SELECT scan AS id, SUM(amount) AS total FROM planet_data
+ WHERE rid >= 14 AND rid <= 24 GROUP BY scan) AS t USING (id)
+ JOIN (SELECT scan AS id,amount AS distorters FROM planet_data
+ WHERE rid = 18) AS d USING (id)
+ JOIN (SELECT scan AS id,amount AS seccents FROM planet_data
+ WHERE rid = 24) AS sc USING (id)
+ORDER BY planet,tick DESC, id DESC
my $scangroups = $dbh->prepare(q{SELECT id,scan_id,tick,uid FROM scans WHERE groupscan AND NOT parsed});
my $oldscan = $dbh->prepare(q{SELECT scan_id FROM scans WHERE scan_id = ? AND tick >= tick() - 168});
-my $addScan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid) VALUES (?,?,COALESCE(?,'-1'))});
+my $addScan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid) VALUES (?,?,?)});
my $parsedscan = $dbh->prepare(q{UPDATE scans SET tick = ?, type = ?, planet = ?, parsed = TRUE WHERE id = ?});
my $addpoints = $dbh->prepare(q{UPDATE users SET scan_points = scan_points + ? WHERE uid = ? });
my $delscan = $dbh->prepare(q{DELETE FROM scans WHERE id = ?});
my $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,sender,target,tick,eta,back,amount,ingal,uid) VALUES(?,?,?,?,?,?,?,?,?,-1) RETURNING id});
my $fleetscan = $dbh->prepare(q{INSERT INTO fleet_scans (id,scan) VALUES(?,?)});
my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (id,ship,amount) VALUES(?,?,?)});
-my $addpdata = $dbh->prepare(q{INSERT INTO planet_data (planet,tick,rid,amount,uid) VALUES(?,?,(SELECT id FROM planet_data_types WHERE category = ? AND name = ?), ?,-1) RETURNING id});
-my $pdatascan = $dbh->prepare(q{INSERT INTO data_scans (id,scan) VALUES(?,?)});
+my $addpdata = $dbh->prepare(q{INSERT INTO planet_data (id,tick,scan,rid,amount) VALUES(?,?,?,(SELECT id FROM planet_data_types WHERE category = ? AND name = ?), ?)});
$newscans->execute or die $dbh->errstr;
while (my $scan = $newscans->fetchrow_hashref){
if ($type eq 'Planet'){
$file =~ s/(\d),(\d)/$1$2/g;
while($file =~ m/(Metal|Crystal|Eonium)\D+(\d+)\D+(\d+)/g){
- my $id = $dbh->selectrow_array($addpdata,undef,$planet,$tick
+ $addpdata->execute($planet,$tick,$scan->{id}
,'roid',$1, $2) or die $dbh->errstr;
- $pdatascan->execute($id,$scan->{id});
- $id = $dbh->selectrow_array($addpdata,undef,$planet,$tick
+ $addpdata->execute($planet,$tick,$scan->{id}
,'resource',$1, $3) or die $dbh->errstr;
- $pdatascan->execute($id,$scan->{id});
}
}elsif ($type eq 'Jumpgate'){
while ($file =~ m/(\d+):(\d+):(\d+)\D+"left"\>(Attack|Defend|Return)<\/td><td>([^<]*)<\/td><td>(\d+)\D+(\d+)/g){
} elsif($type eq 'Surface Analysis' || $type eq 'Technology Analysis'){
my $cat = ($type eq 'Surface Analysis' ? 'struc' : 'tech');
while($file =~ m{((?:[a-zA-Z]| )+)</t[dh]><td(?: class="right")?>(\d+)}sg){
- my $id = $dbh->selectrow_array($addpdata,undef,$planet,$tick
+ $addpdata->execute($planet,$tick,$scan->{id}
,$cat,$1, $2) or die $dbh->errstr;
- $pdatascan->execute($id,$scan->{id});
}
} elsif($type eq 'Unit' || $type eq 'Advanced Unit'){
</div>
<div class="leftinfo">
<table>
-<tr><th>Tick</th><th>Coords</th></tr>
-<TMPL_LOOP OldCoords>
-<tr>
- <td><TMPL_VAR NAME=Tick></td><td><TMPL_VAR NAME=X>:<TMPL_VAR NAME=Y>:<TMPL_VAR NAME=Z></td>
-</tr>
-</TMPL_LOOP>
<tr><th>Tick</th><th>Scan</th></tr>
<TMPL_LOOP Scans>
<tr class="<TMPL_IF ODD>odd<TMPL_ELSE>even</TMPL_IF>">
<td><TMPL_VAR NAME=Tick></td>
- <td><a href="http://game.planetarion.com/showscan.pl?scan=<TMPL_VAR NAME=Scan_id>"><TMPL_VAR NAME=Type></a></td>
+ <td><a href="http://game.planetarion.com/showscan.pl?scan_id=<TMPL_VAR NAME=Scan_id>"><TMPL_VAR NAME=Type></a></td>
+</tr>
+</TMPL_LOOP>
+<tr><th>Tick</th><th>Coords</th></tr>
+<TMPL_LOOP OldCoords>
+<tr>
+ <td><TMPL_VAR NAME=Tick></td><td><TMPL_VAR NAME=X>:<TMPL_VAR NAME=Y>:<TMPL_VAR NAME=Z></td>
</tr>
</TMPL_LOOP>
</table>
</tr>
</TMPL_LOOP>
</table>
+<table>
+ <tr><th>Sender</th><th>Name</th><th>Mission</th><th>Landing tick</th><th>ETA</th><th>Amount</th></tr>
+ <TMPL_LOOP Incomings>
+ <tr align="center" class="<TMPL_IF ODD>odd<TMPL_ELSE>even</TMPL_IF>" onclick="toggleVisibility('fleet<TMPL_VAR NAME=Id>')">
+ <td><a href="/check?coords=<TMPL_VAR NAME=Sender>"><TMPL_VAR NAME=Sender></a></td>
+ <td><TMPL_VAR NAME=Name></td>
+ <td class="<TMPL_VAR NAME=Class>"><TMPL_VAR NAME=Mission></td><td><TMPL_VAR NAME=LandingTick></td>
+ <td><TMPL_VAR NAME=ETA></td><td><TMPL_VAR NAME=Amount></td>
+ </tr>
+ <tr id="fleet<TMPL_VAR NAME=Id>" style="z-index:1; display:none;">
+ <td/>
+ <td colspan="5"><table>
+ <TMPL_LOOP Ships>
+ <tr class="<TMPL_IF ODD>odd<TMPL_ELSE>even</TMPL_IF>">
+ <td><TMPL_VAR NAME=Ship></td><td><TMPL_VAR NAME=Amount></td>
+ </tr>
+ </TMPL_LOOP>
+ </table></td>
+ </tr>
+ </TMPL_LOOP>
+</table>
</TMPL_IF>
<div class="leftinfo">
<img class="graph" src="/graph/stats/<TMPL_VAR NAME=Coords>" alt="stats" height="300" width="500"/>
projects / ndwebbie.git / commitdiff