Need to pick channel before sending

[ndwebbie.git] / lib / NDWeb / Controller / Alliances.pm

diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm
index 983ccea3b30bd95d5284fcae4aa13eeb9fbc955e..c2d40fffb4f81518fde43e6c01c236c826d66fe3 100644 (file)
--- a/lib/NDWeb/Controller/Alliances.pm
+++ b/lib/NDWeb/Controller/Alliances.pm
@@ -62,7 +62,6 @@ sub list : Local {
                } . $order);
        $query->execute;
        $c->stash(alliances => $query->fetchall_arrayref({}) );
-       $c->stash(comma => \&comma_value);
 }
 
 sub edit : Local {
@@ -84,7 +83,7 @@ sub edit : Local {
        $order .= ' DESC' if $order eq 'hit_us';
 
        my $members = $dbh->prepare(q{
-               SELECT coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
+               SELECT id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
                        ,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
                FROM current_planet_stats p
                WHERE p.alliance_id = ?
@@ -216,17 +215,33 @@ sub resources : Local {
                ,r.resources,r.hidden,r.planets
                ,(resources/planets)::bigint AS resplanet
                ,(hidden/planets)::bigint AS hidplanet
-               ,((resources / 300) + (hidden / 100))::bigint AS scoregain
                ,(score + (resources / 300) + (hidden / 100))::bigint AS nscore
-               ,((resources/planets*scoremem)/300 + (hidden/planets*scoremem)/100)::bigint AS scoregain2
-               ,(score + (resources/planets*scoremem)/300
-                       + (hidden/planets*scoremem)/100)::bigint AS nscore2
-               ,((s.size::int8*(1400-tick())*250)/100 + score + (resources/planets*scoremem)/300
-                       + (hidden/planets*scoremem)/100)::bigint AS nscore3
-               ,(s.size::int8*(1400-tick())*250)/100 AS scoregain3
-               FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources, sum(hidden) AS hidden, count(*) AS planets 
-               FROM planets p join planet_scans c ON p.id = c.planet GROUP by alliance_id) r 
-                       NATURAL JOIN alliances a 
+               ,(SELECT sum(score)::bigint FROM (
+SELECT score + (metal+crystal+eonium)/300 + hidden/100 AS score
+FROM current_planet_stats p
+       JOIN current_planet_scans ps ON p.id = ps.planet
+WHERE alliance_id = r.id
+ORDER BY score DESC
+LIMIT 60) a
+               ) AS nscore2
+               ,(SELECT sum(score)::bigint FROM (
+SELECT score + (metal+crystal+eonium)/300 + hidden/100 + (endtick()-tick())*(
+               250*size + COALESCE(metal_ref + crystal_ref + eonium_ref,7)* 1000
+               + CASE extraction WHEN 0 THEN 3000 WHEN 1 THEN 11500 ELSE COALESCE(extraction,3)*3000*3 END
+       )*(1.35+0.005*COALESCE(fincents,20))/100 AS score
+FROM current_planet_stats p
+       JOIN current_planet_scans ps ON p.id = ps.planet
+       LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
+WHERE alliance_id = r.id
+ORDER BY score DESC
+LIMIT 60) a
+               ) AS nscore3
+               FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources
+                               , sum(hidden) AS hidden, count(*) AS planets
+                               FROM planets p join current_planet_scans c ON p.id = c.planet
+                               GROUP by alliance_id
+                       ) r
+                       NATURAL JOIN alliances a
                        LEFT OUTER JOIN (SELECT *,LEAST(members,60) AS scoremem FROM alliance_stats
                                WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON a.id = s.id
                ORDER BY $order