- 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
- ,((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 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
+ );