--- /dev/null
+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
+);
,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
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;
$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;
$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){
$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;
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
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
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
$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)
$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;
}
$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 ($@){
});
$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()){
}
}
}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+)$/){
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)
sub launchConfirmation : Local {
my ( $self, $c ) = @_;
+ $c->stash(error => $c->flash->{error});
$c->stash(missions => $c->flash->{missions});
}
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(
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";
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});
};
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'));
#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;
}
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;
}
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;
$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)
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
<table>
- <tr><th></th><th>Coords</th><th>Name</th><th>Mission</th><th>Tick</th><th>ETA</th><th>Amount</th></tr>
+ <tr><th></th><th>Coords</th><th>Name</th><th>Mission</th><th>Tick</th><th>ETA</th><th>Amount</th><th>Back</th></tr>
[% FOR m IN missions %]
- <tr align="center" class="[% loop.count % 2 == 0 ? 'even' : 'odd' %]" onclick="$('#fleet[% m.id %]').toggle()">
+ <tr align="center" class="[% loop.count % 2 == 0 ? 'even' : 'odd' %]" onclick="$('#fleet[% m.fid %]').toggle()">
<td>
[% IF m.ships %]
<b>⇓</b>
<td>[% m.tick %]</td>
<td>[% m.eta %]</td>
<td>[% m.amount %]</td>
+ <td>[% m.back %]</td>
[% IF m.fleetcatch %]
<td class="Hostile">FLEETCATCH!!</td>
[% END %]
+ [% IF m.recalled %]
+ <td>Recalled</td>
+ [% END %]
</tr>
[% IF m.ships %]
- <tr id="fleet[% m.id %]" class="hidden">
+ <tr id="fleet[% m.fid %]" class="hidden">
<td>
<td colspan="5"><table>
[% FOR s IN m.ships %]
<tr><td></td><th>Target</th><th>Mission</th><th>Amount</th><th>Landing Tick</th><th>Back home</th><th>change?</th></tr>
[% FOR f IN fleets %]
<tr class="[% loop.count % 2 == 0 ? 'even' : 'odd' %]">
- <td><input type="button" onclick="$('#fleet[% f.id %]').toggle()" value="⇓" title="Show fleets"></td>
+ <td><input type="button" onclick="$('#fleet[% f.fid %]').toggle()" value="⇓" title="Show fleets"></td>
<td>[% IF f.target %]<a href="[% c.uri_for('/stats/planet',f.target) %]">[% f.coords %]</a>[% END %]</td>
<td>[% f.mission %]</td>
<td>[% f.amount %]</td><td>[% f.tick %]</td>
- <td>[% IF f.back %]<input style="width:5em" type="text" name="back:[% f.id %]" value="[% f.back %]">[% END %]</td>
- <td>[% IF f.back %]<input type="checkbox" name="change:[% f.id %]">[% END %]</td>
+ <td>[% IF f.back %]<input style="width:5em" type="text" name="back:[% f.fid %]" value="[% f.back %]">[% END %]</td>
+ <td>[% IF f.back %]<input type="checkbox" name="change:[% f.fid %]">[% END %]</td>
</tr>
- <tr id="fleet[% f.id %]" class="hidden">
+ <tr id="fleet[% f.fid %]" class="hidden">
<td>
<td colspan="5"><table>
[% FOR s IN f.ships %]
my $findplanet = $dbh->prepare(q{SELECT planetid(?,?,?,?)});
my $findoldplanet = $dbh->prepare(q{SELECT id FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND tick <= $4 ORDER BY tick DESC LIMIT 1});
my $findcoords = $dbh->prepare(q{SELECT * FROM planetcoords(?,?)});
-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 $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,planet,amount) VALUES(?,?,?,?) RETURNING fid});
+my $fleetscan = $dbh->prepare(q{INSERT INTO fleet_scans (fid,id) VALUES(?,?)});
+my $addintel = $dbh->prepare(q{INSERT INTO intel (name,mission,sender,target,tick,eta,back,amount,ingal,uid)
+ VALUES(?,?,?,?,?,?,?,?,?,-1) RETURNING id});
+my $intelscan = $dbh->prepare(q{INSERT INTO fleet_scans (intel,id) VALUES(?,?)});
my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (id,ship,amount) VALUES(?,?,?)});
my $addplanetscan = $dbh->prepare(q{INSERT INTO planet_scans
(id,tick,planet,metal_roids,metal,crystal_roids,crystal,eonium_roids,eonium
while($file =~ m{class="left">Fleet:\s(.*?)</td><td\sclass="right">
Mission:\s(\w+)</td></tr>(.*?)Total\sShips:\s(\d+)}sxg){
- my $id = addfleet($1,$2,$3,$scan->{planet},undef
- ,$scan->{tick},undef,undef,$4);
+ my $id = addfleet($1,$2,$3,$scan->{planet},$scan->{tick},$4);
$fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
}
}
+sub parse_unit {
+ my ($scan,$file) = @_;
+
+ my $id = addfleet($scan->{type},'Full fleet',$file,$scan->{planet},$scan->{tick});
+ $fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
+}
+
+sub parse_jumpgate {
+ my ($scan,$file) = @_;
+
+ while ($file =~ m{(\d+):(\d+):(\d+)\D+(Attack|Defend|Return)</td><td class="left">([^<]*)\D+(\d+)\D+(\d+)}g){
+ my ($sender) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3,$scan->{tick});
+ ($sender) = $dbh->selectrow_array($findoldplanet,undef,$1,$2,$3,$scan->{tick})
+ if ((not defined $sender) && $4 eq 'Return');
+ my $id = addintel($5,$4,$sender,$scan->{planet},$scan->{tick}+$6,$6
+ ,undef,$7, $scan->{x} == $1 && $scan->{y} == $2);
+ $intelscan->execute($id,$scan->{id});
+ }
+
+}
+
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
my %parsers = (
Planet => \&parse_planet,
Incoming => \&parse_incoming,
+ Unit => \&parse_unit,
+ 'Advanced Unit' => \&parse_unit,
+ Jumpgate => \&parse_jumpgate,
);
my $z = $4;
my $tick = $5;
$scan->{tick} = $5;
+ $scan->{type} = $1;
+ $scan->{x} = $x;
+ $scan->{y} = $y;
if($dbh->selectrow_array(q{SELECT * FROM scans WHERE scan_id = ? AND tick = ? AND id <> ?},undef,$scan->{scan_id},$tick,$scan->{id})){
$dbh->pg_rollback_to('scans') or die "rollback didn't work";
}
if (exists $parsers{$type}){
$parsers{$type}->($scan,$file);
- }elsif ($type eq 'Jumpgate'){
- #print "$file\n";
- while ($file =~ m{(\d+):(\d+):(\d+)\D+(Attack|Defend|Return)</td><td class="left">([^<]*)\D+(\d+)\D+(\d+)}g){
-
- my ($sender) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3,$tick) or die $dbh->errstr;
- ($sender) = $dbh->selectrow_array($findoldplanet,undef,$1,$2,$3,$tick) if ((not defined $sender) && $4 eq 'Return');
- my $id = addfleet($5,$4,undef,$sender,$planet,$tick+$6,$6
- ,undef,$7, $x == $1 && $y == $2);
- $fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
- }
}elsif ($type eq 'News'){
while( $file =~ m{top">((?:\w| )+)\D+(\d+)</td><td class="left" valign="top">(.+?)</td></tr>}g){
my $news = $1;
my ($target) = $dbh->selectrow_array($findplanet,undef
,$2,$3,$4,$t) or die $dbh->errstr;
die "No target: $2:$3:$4" unless defined $target;
- my $id = addfleet($1,$mission,undef,$planet,$target,$6
+ my $id = addintel($1,$mission,$planet,$target,$6
,$eta,$back,undef, ($x == $2 && $y == $3));
- $fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
+ $intelscan->execute($id,$scan->{id});
}elsif($news eq 'Incoming' && $text =~ m/We have detected an open jumpgate from (.*?), located at (\d+):(\d+):(\d+). The fleet will approach our system in tick (\d+) and appears to have roughly (\d+) ships/g){
my $eta = $5 - $t;
my $mission = '';
my ($target) = $dbh->selectrow_array($findplanet,undef
,$2,$3,$4,$t) or die $dbh->errstr;
die "No target: $2:$3:$4" unless defined $target;
- my $id = addfleet($1,$mission,undef,$target,$planet,$5
+ my $id = addintel($1,$mission,$target,$planet,$5
,$eta,$back,$6, ($x == $2 && $y == $3));
- $fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
+ $intelscan->execute($id,$scan->{id});
}
}
} elsif($type eq 'Development'){
}
push @values,$total;
$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;
} elsif($type eq 'Landing'){
} else {
print "Something wrong with scan $scan->{id} type $type at tick $tick http://game.planetarion.com/showscan.pl?scan_id=$scan->{scan_id}\n";
system 'killall','-USR1', 'irssi' if $parsedscans;
sub addfleet {
- my ($name,$mission,$ships,$sender,$target,$tick,$eta,$back,$amount,$ingal) = @_;
-
- die "no sender" unless defined $sender;
-
- $ingal = 0 unless $ingal;
- $back = $tick + 4 if $ingal && $eta <= 4;
-
- if ($mission eq 'Return'){
- ($sender,$target) = ($target,$sender);
- $back = $tick + $eta if $eta;
- }
+ my ($name,$mission,$ships,$sender,$tick,$amount) = @_;
my @ships;
my $total = 0;
$total += $2;
push @ships, [$1,$2];
}
- $amount = $total if (!defined $amount) && defined $ships;
+ $amount = $total if (!defined $amount);
my $id = $dbh->selectrow_array($addfleet,undef,$name,$mission,$sender
- ,$target,$tick, $eta, $back, $amount,$ingal) or die $dbh->errstr;
+ ,$tick, $amount);
for my $s (@ships){
unshift @{$s},$id;
$addships->execute(@{$s}) or die $dbh->errstr;
}
return $id;
-};
+}
+
+sub addintel {
+ my ($name,$mission,$sender,$target,$tick,$eta,$back,$amount,$ingal) = @_;
+
+ die "no sender" unless defined $sender;
+
+ $ingal = 0 unless $ingal;
+ $back = $tick + 4 if $ingal && $eta <= 4;
+
+ if ($mission eq 'Return'){
+ ($sender,$target) = ($target,$sender);
+ $back = $tick + $eta if $eta;
+ }
+
+ my $id = $dbh->selectrow_array($addintel,undef,$name,$mission,$sender
+ ,$target,$tick, $eta, $back, $amount,$ingal);
+ return $id;
+}
projects / ndwebbie.git / commitdiff