- my $query = $dbh->prepare(qq{
- SELECT a.id,a.name,a.relationship,s.members,s.score,s.size
- ,r.resources,r.hidden,r.planets
- ,(resources/planets)::bigint AS resplanet
- ,(hidden/planets)::bigint AS hidplanet
- ,(score + (resources / 300) + (hidden / 100))::bigint AS nscore
- ,(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
- });
+ my $query = $dbh->prepare(q{
+SELECT aid AS id,alliance AS name,relationship,members,score,size
+ ,resources,hidden,planets
+ ,(resources/planets)::bigint AS resplanet
+ ,(hidden/planets)::bigint AS hidplanet
+ , nscore, nscore2, nscore3
+FROM alliance_resources
+ORDER BY } . $order
+ );