X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FStats.pm;h=c7fa4a8653bc09e1a1b52bc5ce913a1fa4b9404b;hb=fcd1ee7b280d01a082b3a868a56145f970251f5d;hp=25798595b155bffa8ccf6730ffde551c0b61c772;hpb=5d49b3579d2ad39f6ae9bc2dba37ebbf0334e0da;p=ndwebbie.git diff --git a/lib/NDWeb/Controller/Stats.pm b/lib/NDWeb/Controller/Stats.pm index 2579859..c7fa4a8 100644 --- a/lib/NDWeb/Controller/Stats.pm +++ b/lib/NDWeb/Controller/Stats.pm @@ -55,7 +55,10 @@ sub galaxy : Local { }); $query->execute($x,$y); - $c->stash(g => $query->fetchrow_hashref ); + my $g = $query->fetchrow_hashref; + $c->detach('/default') unless $g; + $c->stash(g => $g); + my $extra_columns = ''; if ($c->check_user_roles(qw/stats_intel/)){ @@ -67,13 +70,14 @@ 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 }; } - $query = $dbh->prepare(qq{SELECT p.id,coords(x,y,z), ruler, p.planet,race, + $query = $dbh->prepare(qq{SELECT DISTINCT ON (x,y,z,p.id) + p.id,coords(x,y,z), ruler, p.planet,race, size, size_gain, size_gain_day, score,score_gain,score_gain_day, value,value_gain,value_gain_day, @@ -83,10 +87,11 @@ sub galaxy : Local { valuerank,valuerank_gain,valuerank_gain_day, xprank,xprank_gain,xprank_gain_day $extra_columns - FROM current_planet_stats_full p + 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 = ? AND COALESCE(z = ?,TRUE) ORDER BY x,y,z ASC + 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, ds.tick DESC, ds.id DESC }); $query->execute($x,$y,$z); @@ -100,39 +105,48 @@ sub planet : Local { my $p = $dbh->selectrow_hashref(q{SELECT id,x,y,z FROM current_planet_stats WHERE id = $1},undef,$id); + $c->detach('/default') unless $p; + $c->forward('galaxy',[$p->{x},$p->{y},$p->{z}]); $c->stash(p => $p); 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; @@ -142,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) @@ -177,12 +191,10 @@ sub planet : Local { } if ($c->check_user_roles(qw/stats_planetdata/)){ - my $query = $dbh->prepare(q{SELECT DISTINCT ON(rid) tick,category,name,amount - FROM planet_data pd JOIN planet_data_types pdt ON pd.rid = pdt.id - WHERE pd.id = $1 ORDER BY rid,tick DESC - }); - $query->execute($id); - $c->stash(planetdata => $query->fetchall_arrayref({}) ); + $c->stash(planetscan => $dbh->selectrow_hashref(q{SELECT * + FROM current_planet_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 @@ -210,6 +222,7 @@ sub find : Local { my $dbh = $c->model; local $_ = $find || $c->req->param('coords'); + $c->stash(searchterm => $_); if (/(\d+)(?: |:)(\d+)(?: |:)(\d+)(?:(?: |:)(\d+))?/){ my $planet = $dbh->selectrow_array(q{SELECT planetid($1,$2,$3,$4)} @@ -217,8 +230,19 @@ sub find : Local { $c->res->redirect($c->uri_for('planet',$planet)); }elsif (/(\d+)(?: |:)(\d+)/){ $c->res->redirect($c->uri_for('galaxy',$1,$2)); + }elsif($c->check_user_roles(qw/stats_find_nick/)) { + my $query = $dbh->prepare(q{SELECT id,coords(x,y,z),nick + FROM current_planet_stats p + WHERE nick ilike $1 + }); + $query->execute($_); + my $planets = $query->fetchall_arrayref({}); + if (@{$planets} == 1){ + $c->res->redirect($c->uri_for('planet',$planets->[0]->{id})); + }else{ + $c->stash(planets => $planets); + } } - }