From: Michael Andreen Date: Wed, 31 Dec 2008 11:04:14 +0000 (+0100) Subject: New fleet infrastructure X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=7d478421939145912a4155865ad6b521e1bff08f New fleet infrastructure --- diff --git a/database/fleets.sql b/database/fleets.sql new file mode 100644 index 0000000..549c2e9 --- /dev/null +++ b/database/fleets.sql @@ -0,0 +1,77 @@ +CREATE TABLE intel ( + id SERIAL PRIMARY KEY, + uid INTEGER NOT NULL REFERENCES users(uid), + sender INTEGER NOT NULL REFERENCES planets(id), + target INTEGER NOT NULL REFERENCES planets(id), + mission TEXT NOT NULL, + name TEXT NOT NULL, + amount INTEGER, + tick INTEGER NOT NULL, + eta INTEGER NOT NULL, + back INTEGER, + ingal BOOLEAN NOT NULL +); + +INSERT INTO intel (id,uid,sender,target,mission,name,amount,tick,eta,back,ingal) + (SELECT id,uid,sender,target,mission,name,amount,tick,eta,back,ingal FROM fleets + WHERE target IS NOT NULL); + +ALTER TABLE fleets RENAME COLUMN id TO fid; +ALTER TABLE fleet_ships RENAME COLUMN id TO fid; +ALTER TABLE fleet_scans RENAME COLUMN id TO fid; +ALTER TABLE fleet_scans RENAME COLUMN scan TO id; + +CREATE TABLE launch_confirmations ( + fid INTEGER PRIMARY KEY REFERENCES fleets(fid), + uid INTEGER NOT NULL REFERENCES users(uid), + target INTEGER NOT NULL REFERENCES planets(id), + landing_tick INTEGER NOT NULL, + eta INTEGER NOT NULL, + back INTEGER NOT NULL +); + +INSERT INTO launch_confirmations (fid,uid,target,eta,back,landing_tick) ( + SELECT fid,uid,target,eta,back,tick AS landing_tick FROM fleets + WHERE fid IN (select fid FROM fleet_ships) + AND uid <> -1 AND back IS NOT NULL AND target IS NOT NULL +); + +CREATE TABLE full_fleets ( + fid INTEGER PRIMARY KEY REFERENCES fleets(fid), + uid INTEGER NOT NULL REFERENCES users(uid) +); + +INSERT INTO full_fleets (fid,uid) ( + SELECT fid,uid FROM fleets WHERE fid IN (select fid FROM fleet_ships) + AND uid <> -1 AND mission = 'Full fleet' AND name = 'Main' +); + +ALTER TABLE fleets DROP COLUMN target; +ALTER TABLE fleets DROP COLUMN eta; +ALTER TABLE fleets DROP COLUMN back; +ALTER TABLE fleets DROP COLUMN ingal; +ALTER TABLE fleets DROP COLUMN uid; + +ALTER TABLE fleets RENAME COLUMN sender TO planet; + +CREATE TABLE intel_scans ( + id INTEGER REFERENCES scans(id), + intel INTEGER REFERENCES intel(id), + PRIMARY KEY (id,intel) +); + +INSERT INTO intel_scans (id,intel) ( + SELECT id,fid FROM fleet_scans WHERE id IN ( + SELECT id from scans where type in ('News','Jumpgate') + ) +); + +DELETE FROM fleet_scans WHERE id IN ( + SELECT id from scans where type in ('News','Jumpgate') +); + +DELETE FROM fleets WHERE fid NOT IN ( + SELECT fid FROM fleet_scans + UNION SELECT fid FROM full_fleets + UNION SELECT fid FROM launch_confirmations +); diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm index d3a44e3..2afedad 100644 --- a/lib/NDWeb/Controller/Calls.pm +++ b/lib/NDWeb/Controller/Calls.pm @@ -68,15 +68,15 @@ sub list : Local { ,u.defense_points, u.attack_points, c.landing_tick ,dc.username AS dc, (c.landing_tick - tick()) AS curreta ,p2.race, i.amount, i.eta, i.shiptype, p2.alliance - ,coords(p2.x,p2.y,p2.z), COUNT(DISTINCT f.id) AS fleets + ,coords(p2.x,p2.y,p2.z), COUNT(DISTINCT f.fid) AS fleets FROM calls c JOIN users u ON c.member = u.uid JOIN current_planet_stats p ON u.planet = p.id LEFT OUTER JOIN incomings i ON i.call = c.id LEFT OUTER JOIN current_planet_stats p2 ON i.sender = p2.id LEFT OUTER JOIN users dc ON c.dc = dc.uid - LEFT OUTER JOIN fleets f ON f.target = u.planet - AND f.tick = c.landing_tick AND f.back = f.tick + f.eta - 1 + LEFT OUTER JOIN launch_confirmations f ON f.target = u.planet + AND f.landing_tick = c.landing_tick AND f.back = f.landing_tick + f.eta - 1 WHERE $where GROUP BY c.id, p.x,p.y,p.z,p.id, p.size, p.score, p.value, c.landing_tick ,u.defense_points,u.attack_points,dc.username @@ -113,18 +113,38 @@ sub edit : Local { my $outgoings = $dbh->prepare(q{ - SELECT i.id,i.mission, i.name, i.tick,eta - , i.amount, coords(x,y,z) AS coords, t.id AS planet - 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) - WHERE i.sender = $1 - AND (i.tick > $2 - 14 OR i.mission = 'Full fleet') - ORDER BY i.tick,x,y,z +( + SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta + ,amount, NULL AS coords, planet, NULL AS back, NULL AS recalled + FROM fleets f + WHERE planet = $1 AND tick <= $2 AND ( + mission = 'Full fleet' + OR fid IN (SELECT fid FROM fleet_scans) + ) AND ( + mission = 'Full fleet' + OR tick >= $2 - 12 + ) + ORDER BY mission,name,tick DESC +) UNION ( + SELECT fid,mission,name,landing_tick AS tick, eta, amount + , coords(x,y,z), t.id AS planet, back + , (back <> landing_tick + eta - 1) AS recalled + FROM fleets f + JOIN launch_confirmations USING (fid) + LEFT OUTER JOIN current_planet_stats t ON target = t.id + WHERE f.planet = $1 AND back >= $2 AND landing_tick - eta - 12 < $2 +) UNION ( + SELECT DISTINCT ON (tick,x,y,z,mission,name,amount) + NULL as fid, i.mission, i.name, i.tick,eta + , i.amount, coords(x,y,z), t.id AS planet, back, NULL AS recalled + FROM intel i + LEFT OUTER JOIN current_planet_stats t ON i.target = t.id + WHERE uid = -1 AND i.sender = $1 AND i.tick > $2 - 14 AND i.tick < $2 + 14 + ORDER BY i.tick,x,y,z,mission,name,amount,back +) }); my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships - WHERE id = ? ORDER BY num + WHERE fid = ? ORDER BY num }); $outgoings->execute($call->{planet},$call->{landing_tick}); my @fleets; @@ -133,12 +153,13 @@ sub edit : Local { $fleet->{back} == $call->{landing_tick}){ $fleet->{fleetcatch} = 1; } - $ships->execute($fleet->{id}); - if ($ships->rows != 0){ + if ($fleet->{fid}){ + $ships->execute($fleet->{fid}); my @ships; while (my $ship = $ships->fetchrow_hashref){ push @ships,$ship; } + push @ships, {ship => 'No', amount => 'ships'} if @ships == 0; $fleet->{ships} = \@ships; } push @fleets, $fleet; @@ -146,21 +167,20 @@ sub edit : Local { $c->stash(fleets => \@fleets); my $defenders = $dbh->prepare(q{ - SELECT DISTINCT ON (i.tick,x,y,z,s.id,i.name,i.amount) i.id,i.mission, i.name, i.tick,eta - , i.amount, coords(x,y,z) AS coords, s.id AS planet - 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.target = ? - AND i.tick = ? AND i.mission = 'Defend' - ORDER BY i.tick,x,y,z +SELECT DISTINCT ON (x,y,z,s.id,name,amount) fid,mission, name, eta + , amount, coords(x,y,z) AS coords, landing_tick AS tick, f.planet + ,back, (back <> landing_tick + eta - 1) AS recalled +FROM launch_confirmations lc + JOIN fleets f USING (fid) + LEFT OUTER JOIN current_planet_stats s ON f.planet = s.id +WHERE target = ? AND landing_tick = ? AND mission = 'Defend' +ORDER BY x,y,z }); $defenders->execute($call->{planet},$call->{landing_tick}); my @defenders; while (my $fleet = $defenders->fetchrow_hashref){ - $ships->execute($fleet->{id}); + $ships->execute($fleet->{fid}); if ($ships->rows != 0){ my @ships; while (my $ship = $ships->fetchrow_hashref){ @@ -186,8 +206,8 @@ sub edit : Local { $outgoings->execute($attacker->{planet},$call->{landing_tick}); my @missions; while (my $mission = $outgoings->fetchrow_hashref){ - $ships->execute($mission->{id}); - if ($ships->rows != 0){ + if ($mission->{fid}){ + $ships->execute($mission->{fid}); my @ships; while (my $ship = $ships->fetchrow_hashref){ push @ships,$ship; @@ -212,12 +232,12 @@ sub defleeches : Local { my $query = $dbh->prepare(q{SELECT username,defense_points,count(id) AS calls , SUM(fleets) AS fleets, SUM(recalled) AS recalled FROM (SELECT username,defense_points,c.id,count(f.target) AS fleets - , count(NULLIF(f.tick + f.eta -1 = f.back,TRUE)) AS recalled + , count(NULLIF(f.landing_tick + f.eta -1 = f.back,TRUE)) AS recalled FROM users u JOIN calls c ON c.member = u.uid - LEFT OUTER JOIN fleets f ON u.planet = f.target - AND c.landing_tick = f.tick - WHERE (f.mission = 'Defend' AND f.uid > 0 AND f.back IS NOT NULL AND NOT ingal) - OR f.target IS NULL + LEFT OUTER JOIN ( + SELECT * FROM launch_confirmations JOIN fleets USING (fid) + ) f ON u.planet = f.target AND c.landing_tick = f.landing_tick + WHERE f.mission = 'Defend' GROUP BY username,defense_points,c.id ) d GROUP BY username,defense_points ORDER BY fleets DESC, defense_points diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm index fd84472..94bbfa1 100644 --- a/lib/NDWeb/Controller/Intel.pm +++ b/lib/NDWeb/Controller/Intel.pm @@ -211,7 +211,7 @@ sub members : Local { FROM users u JOIN groupmembers gm USING (uid) LEFT OUTER JOIN (SELECT DISTINCT ON (planet) planet,tick from scans where type = 'News' ORDER BY planet,tick DESC) n USING (planet) - LEFT OUTER JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM fleets WHERE amount IS NULL) i ON i.sender = u.planet + LEFT OUTER JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM intel WHERE amount IS NULL) i ON i.sender = u.planet LEFT OUTER JOIN current_planet_stats t ON i.target = t.id LEFT OUTER JOIN (SELECT rt.id,planet,tick FROM raids r JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.planet = i.target @@ -231,7 +231,7 @@ sub member : Local { my $query = $dbh->prepare(q{ SELECT coords(t.x,t.y,t.z), i.eta, i.tick, rt.id AS ndtarget, rc.launched, inc.landing_tick FROM users u - LEFT OUTER JOIN (SELECT DISTINCT eta,tick,sender,target,mission,name FROM fleets WHERE amount IS NULL) i ON i.sender = u.planet + LEFT OUTER JOIN (SELECT DISTINCT eta,tick,sender,target,mission,name FROM intel WHERE amount IS NULL) i ON i.sender = u.planet LEFT OUTER JOIN current_planet_stats t ON i.target = t.id LEFT OUTER JOIN (SELECT rt.id,planet,tick FROM raids r JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.planet = i.target @@ -268,7 +268,7 @@ sub member : Local { $query = $dbh->prepare(q{ SELECT coords(t.x,t.y,t.z),t.alliance_id, t.alliance, i.eta, i.tick, i.ingal FROM users u - JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM fleets WHERE amount IS NULL) i ON i.sender = u.planet + JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM intel WHERE amount IS NULL) i ON i.sender = u.planet LEFT OUTER JOIN current_planet_stats t ON i.target = t.id WHERE u.uid = $1 AND (i.mission = 'Defend' OR i.mission = 'AllyDef') ORDER BY (i.tick - i.eta) diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm index 6dcc1f6..164a0d9 100644 --- a/lib/NDWeb/Controller/Members.pm +++ b/lib/NDWeb/Controller/Members.pm @@ -82,25 +82,35 @@ sub index : Path : Args(0) { $calls->execute($c->user->id); $c->stash(calls => $calls->fetchall_arrayref({}) ); - my $query = $dbh->prepare(q{SELECT f.id, coords(x,y,z), target, mission - , f.amount, tick, back -FROM fleets f -LEFT OUTER JOIN current_planet_stats p ON f.target = p.id -WHERE NOT ingal AND f.uid = ? AND f.sender = ? AND - (back >= ? OR (tick >= tick() - 24 AND name = 'Main')) -GROUP BY f.id, x,y,z, mission, tick,back,f.amount,f.target -ORDER BY x,y,z,mission,tick + my $query = $dbh->prepare(q{ +( + SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta + ,amount, NULL AS coords, planet AS target, NULL AS back + FROM fleets f + JOIN full_fleets USING (fid) + WHERE uid = $1 AND planet = $2 AND tick >= tick() - 24 + AND name = 'Main' AND mission = 'Full fleet' + ORDER BY mission,name,tick DESC +) UNION ( + SELECT fid,mission,name,landing_tick AS tick, eta, amount + , coords(x,y,z), target, back + FROM fleets f + JOIN launch_confirmations USING (fid) + LEFT OUTER JOIN current_planet_stats t ON target = t.id + WHERE uid = $1 AND f.planet = $2 AND back >= tick() + AND landing_tick - eta - 12 < tick() +) }); my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships - WHERe id = ? ORDER BY num + WHERE fid = ? ORDER BY num }); - $query->execute($c->user->id,$c->user->planet,$c->stash->{TICK}); + $query->execute($c->user->id,$c->user->planet); my @fleets; while (my $fleet = $query->fetchrow_hashref){ my @ships; - $ships->execute($fleet->{id}); + $ships->execute($fleet->{fid}); while (my $ship = $ships->fetchrow_hashref){ push @ships,$ship; } @@ -185,16 +195,19 @@ sub postfleetupdate : Local { $dbh->begin_work; eval{ my $insert = $dbh->prepare(q{INSERT INTO fleets - (uid,sender,name,mission,tick,amount) - VALUES (?,?,'Main','Full fleet',tick(),?) RETURNING id}); - my ($id) = $dbh->selectrow_array($insert,undef,$c->user->id + (planet,name,mission,tick,amount) + VALUES (?,'Main','Full fleet',tick(),?) RETURNING fid}); + my ($id) = $dbh->selectrow_array($insert,undef ,$c->user->planet,$amount); $insert = $dbh->prepare(q{INSERT INTO fleet_ships - (id,ship,amount) VALUES (?,?,?)}); + (fid,ship,amount) VALUES (?,?,?)}); for my $s (@ships){ unshift @{$s},$id; $insert->execute(@{$s}); } + $insert = $dbh->prepare(q{INSERT INTO full_fleets + (fid,uid) VALUES (?,?)}); + $insert->execute($id,$c->user->id); $dbh->commit; }; if ($@){ @@ -221,9 +234,9 @@ sub postfleetsupdates : Local { }); $dbh->begin_work; if ($c->req->param('cmd') eq 'Recall Fleets'){ - my $updatefleets = $dbh->prepare(q{UPDATE fleets - SET back = tick() + (tick() - (tick - eta)) - WHERE uid = ? AND id = ? AND back > tick()+eta + my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations + SET back = tick() + (tick() - (landing_tick - eta)) + WHERE uid = ? AND fid = ? AND back > tick()+eta }); for my $param ($c->req->param()){ @@ -233,8 +246,8 @@ sub postfleetsupdates : Local { } } }elsif ($c->req->param('cmd') eq 'Change Fleets'){ - my $updatefleets = $dbh->prepare(q{UPDATE fleets - SET back = ? WHERE uid = ? AND id = ?}); + my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations + SET back = ? WHERE uid = ? AND fid = ?}); for my $param ($c->req->param()){ if ($param =~ /^change:(\d+)$/){ @@ -370,7 +383,7 @@ sub insertintel : Private { unless ($tick =~ /^(\d+)$/){ $tick = $c->stash->{game}->{tick}; } - my $addintel = $dbh->prepare(q{INSERT INTO fleets + my $addintel = $dbh->prepare(q{INSERT INTO intel (name,mission,tick,target,sender,eta,amount,ingal,back,uid) VALUES($1,$2,$3,planetid($4,$5,$6,$10),planetid($7,$8,$9,$10) ,$11,$12,$13,$14,$15) @@ -399,6 +412,7 @@ sub insertintel : Private { sub launchConfirmation : Local { my ( $self, $c ) = @_; + $c->stash(error => $c->flash->{error}); $c->stash(missions => $c->flash->{missions}); } @@ -430,11 +444,14 @@ sub postconfirmation : Local { WHERE uid = ? AND target = ? AND wave = ? }); my $addfleet = $dbh->prepare(q{INSERT INTO fleets - (uid,name,mission,sender,target,tick,eta,back,amount) - VALUES ($1,$2,$3,(SELECT planet FROM users WHERE uid = $1),$4,$5,$6,$7,$8) - RETURNING id + (name,mission,planet,tick,amount) + VALUES ($2,$3,(SELECT planet FROM users WHERE uid = $1),tick(),$4) + RETURNING fid + }); + my $addconfirmation = $dbh->prepare(q{INSERT INTO launch_confirmations + (fid,uid,target,landing_tick,eta,back) VALUES ($1,$2,$3,$4,$5,$6) }); - my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (id,ship,amount) + my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount) VALUES (?,?,?) }); my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES( @@ -455,17 +472,14 @@ sub postconfirmation : Local { my $name = $1; my $tick = $c->stash->{TICK}+$9; $tick += $8 if defined $8; + $tick = $13 if defined $13; my $eta = $9; + $eta += $14 if defined $14; my $mission = $10; my $x = $5; my $y = $6; my $z = $7; my $back = $tick + $eta - 1; - if ($13){ - $tick = $13; - }elsif ($14){ - $back += $14; - } $mission{tick} = $tick; $mission{mission} = $mission; $mission{target} = "$x:$y:$z"; @@ -495,8 +509,9 @@ sub postconfirmation : Local { warn "No ships in: $ships"; next; } - my $fleet = $dbh->selectrow_array($addfleet,undef,$c->user->id,$name,$mission - ,$planet_id,$tick,$eta,$back,$amount); + my $fleet = $dbh->selectrow_array($addfleet,undef,$c->user->id,$name + ,$mission,$amount); + $addconfirmation->execute($fleet,$c->user->id,$planet_id,$tick,$eta,$back); $mission{fleet} = $fleet; for my $ship (@ships){ $addships->execute($fleet,$ship->{ship},$ship->{amount}); @@ -527,7 +542,11 @@ sub postconfirmation : Local { }; if ($@){ $dbh->rollback; - die $@; + if ($@ =~ m/insert or update on table "fleet_ships" violates foreign key constraint "fleet_ships_ship_fkey"\s+DETAIL:\s+Key \(ship\)=\(([^)]+)\)/){ + $c->flash( error => "'$1' is NOT a valid ship"); + }else{ + $c->flash( error => $@); + } } $c->res->redirect($c->uri_for('launchConfirmation')); diff --git a/lib/NDWeb/Controller/Raids.pm b/lib/NDWeb/Controller/Raids.pm index 217ce37..c6c1461 100644 --- a/lib/NDWeb/Controller/Raids.pm +++ b/lib/NDWeb/Controller/Raids.pm @@ -137,23 +137,22 @@ sub view : Local { #next if ($target->{score}/$planet->{score} < 0.4) && ($target->{value}/$planet->{value} < 0.4); } - my $unitscans = $dbh->prepare(q{ - SELECT DISTINCT ON (name) i.id,i.name, i.tick, i.amount - FROM fleets i - WHERE i.uid = -1 - AND i.sender = ? - AND i.mission = 'Full fleet' - GROUP BY i.id,i.tick,i.name,i.amount - ORDER BY name,i.tick DESC + my $unitscans = $dbh->prepare(q{ +SELECT DISTINCT ON (name) fid, name, tick, amount +FROM fleets +WHERE planet = ? + AND mission = 'Full fleet' +GROUP BY fid,tick,name,amount +ORDER BY name,tick DESC }); $unitscans->execute($target->{planet}); my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships - WHERE id = ? ORDER BY num + WHERE fid = ? ORDER BY num }); my @missions; while (my $mission = $unitscans->fetchrow_hashref){ my @ships; - $ships->execute($mission->{id}); + $ships->execute($mission->{fid}); while (my $ship = $ships->fetchrow_hashref){ push @ships,$ship; } diff --git a/lib/NDWeb/Controller/Root.pm b/lib/NDWeb/Controller/Root.pm index 09ace65..6099db5 100644 --- a/lib/NDWeb/Controller/Root.pm +++ b/lib/NDWeb/Controller/Root.pm @@ -188,8 +188,9 @@ sub end : ActionClass('RenderView') { if ($c->user_exists){ my $fleetupdate = 0; if ($c->check_user_roles(qw/member_menu/)){ - $fleetupdate = $dbh->selectrow_array(q{SELECT tick FROM fleets WHERE sender = ? - AND mission = 'Full fleet' AND tick > tick() - 24 + $fleetupdate = $dbh->selectrow_array(q{ +SELECT tick FROM fleets WHERE planet = ? AND tick > tick() - 24 +AND mission = 'Full fleet' AND name IN ('Main','Advanced Unit'); },undef,$c->user->planet); $fleetupdate = 0 unless defined $fleetupdate; } diff --git a/lib/NDWeb/Controller/Stats.pm b/lib/NDWeb/Controller/Stats.pm index 36cc1e5..c7fa4a8 100644 --- a/lib/NDWeb/Controller/Stats.pm +++ b/lib/NDWeb/Controller/Stats.pm @@ -112,34 +112,41 @@ sub planet : Local { if ($c->check_user_roles(qw/stats_missions/)){ my $query = $dbh->prepare(q{ - SELECT DISTINCT ON (i.tick,x,y,z,t.id,i.name,i.amount) i.id,i.mission, i.name, i.tick, i.eta AS eta - , i.amount, coords(x,y,z) AS coords, t.id AS planet - FROM (( - SELECT * FROM fleets - WHERE sender = $1 AND tick > tick() - 14 - ) UNION ( - SELECT * FROM fleets WHERE sender = $1 AND mission = 'Full fleet' - ORDER BY tick DESC LIMIT 5 - ) - ) i - LEFT OUTER JOIN (planets - NATURAL JOIN planet_stats) t ON i.target = t.id - AND t.tick = ( SELECT MAX(tick) FROM planet_stats) - WHERE i.uid = -1 - ORDER BY i.tick,x,y,z,t.id,i.name,i.amount,i.eta - }); +( + SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta + ,amount, NULL AS coords, planet, NULL AS back + FROM fleets f + WHERE planet = $1 AND tick <= tick() AND ( + mission = 'Full fleet' + OR fid IN (SELECT fid FROM fleet_scans) + ) AND ( + mission = 'Full fleet' + OR tick >= tick() - 12 + ) + ORDER BY mission,name,tick DESC +) UNION ( + SELECT DISTINCT ON (tick,x,y,z,mission,name,amount) + NULL as fid, i.mission, i.name, i.tick,eta + , i.amount, coords(x,y,z), t.id AS planet, back + FROM intel i + LEFT OUTER JOIN current_planet_stats t ON i.target = t.id + WHERE uid = -1 AND i.sender = $1 AND i.tick > tick() - 14 AND i.tick < tick() + 14 + ORDER BY i.tick,x,y,z,mission,name,amount,back +) + }); $query->execute($id); my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships - WHERE id = ? ORDER BY num + WHERE fid = ? ORDER BY num }); my @missions; while (my $mission = $query->fetchrow_hashref){ my @ships; - $ships->execute($mission->{id}); - if ($ships->rows != 0){ + if ($mission->{fid}){ + $ships->execute($mission->{fid}); while (my $ship = $ships->fetchrow_hashref){ push @ships,$ship; } + push @ships, {ship => 'No', amount => 'ships'} if @ships == 0; $mission->{ships} = \@ships; } push @missions,$mission; @@ -149,7 +156,7 @@ sub planet : Local { $query = $dbh->prepare(q{ SELECT DISTINCT ON (i.tick,x,y,z,s.id,i.name,i.amount) i.id,i.mission, i.name, i.tick,eta , i.amount, coords(x,y,z) AS coords, s.id AS planet - FROM fleets i + FROM intel i LEFT OUTER JOIN (planets NATURAL JOIN planet_stats) s ON i.sender = s.id AND s.tick = ( SELECT MAX(tick) FROM planet_stats) diff --git a/lib/NDWeb/Include.pm b/lib/NDWeb/Include.pm index b5acfbb..5c8ba9c 100644 --- a/lib/NDWeb/Include.pm +++ b/lib/NDWeb/Include.pm @@ -64,7 +64,7 @@ sub intelquery { my ($columns,$where) = @_; return qq{ SELECT $columns, i.mission, i.tick AS landingtick,MIN(i.eta) AS eta, i.amount, i.ingal, u.username -FROM (fleets i NATURAL JOIN users u) +FROM (intel i NATURAL JOIN users u) JOIN current_planet_stats t ON i.target = t.id JOIN current_planet_stats o ON i.sender = o.id WHERE $where diff --git a/root/lib/inc/missionlist.tt2 b/root/lib/inc/missionlist.tt2 index 870205e..f72445d 100644 --- a/root/lib/inc/missionlist.tt2 +++ b/root/lib/inc/missionlist.tt2 @@ -1,7 +1,7 @@ - + [% FOR m IN missions %] - + + [% IF m.fleetcatch %] [% END %] + [% IF m.recalled %] + + [% END %] [% IF m.ships %] - +
CoordsNameMissionTickETAAmount
CoordsNameMissionTickETAAmountBack
[% IF m.ships %] ⇓ @@ -16,12 +16,16 @@ [% m.tick %] [% m.eta %] [% m.amount %][% m.back %]FLEETCATCH!!Recalled