From f5639def9af1ddcef6c9d17d6c6ab534aeb6adda Mon Sep 17 00:00:00 2001
From: Michael Andreen <harv@ruin.nu>
Date: Thu, 19 Mar 2009 19:30:06 +0100
Subject: [PATCH] Use top60 members to calculate estimated score

---
 database/endtick.sql              |  3 +++
 lib/NDWeb/Controller/Alliances.pm | 28 +++++++++++++++++++++-------
 root/src/alliances/resources.tt2  | 24 ++++++++++++------------
 3 files changed, 36 insertions(+), 19 deletions(-)
 create mode 100644 database/endtick.sql

diff --git a/database/endtick.sql b/database/endtick.sql
new file mode 100644
index 0000000..9fcfe52
--- /dev/null
+++ b/database/endtick.sql
@@ -0,0 +1,3 @@
+CREATE FUNCTION endtick() RETURNS integer
+AS $$SELECT value::integer FROM misc WHERE id = 'ENDTICK'$$
+LANGUAGE sql STABLE;
diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm
index e95d80f..833143a 100644
--- a/lib/NDWeb/Controller/Alliances.pm
+++ b/lib/NDWeb/Controller/Alliances.pm
@@ -216,14 +216,27 @@ 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*(1150-tick())*250)/100 + score + (resources/planets*scoremem)/300
-			+ (hidden/planets*scoremem)/100)::bigint AS nscore3
-		,(s.size::int8*(1150-tick())*250)/100 AS scoregain3
+		,(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
@@ -240,6 +253,7 @@ sub resources : Local {
 		push @alliances,$alliance;
 	}
 	$c->stash(alliances => \@alliances);
+	$c->stash( comma => \&comma_value)
 }
 
 
diff --git a/root/src/alliances/resources.tt2 b/root/src/alliances/resources.tt2
index 71031ab..20c642d 100644
--- a/root/src/alliances/resources.tt2
+++ b/root/src/alliances/resources.tt2
@@ -9,7 +9,7 @@
 		<th><a href="[% c.uri_for('resources','resplanet') %]">Resources / Planet</a></th>
 		<th><a href="[% c.uri_for('resources','hidplanet') %]">Hidden Resources / Planet</a></th>
 		<th><a href="[% c.uri_for('resources','nscore') %]">Res score (gain)</a>[1]</th>
-		<th><a href="[% c.uri_for('resources','nscore2') %]">Avg res score (gain)</a>[2]</th>
+		<th><a href="[% c.uri_for('resources','nscore2') %]">Top res score (gain)</a>[2]</th>
 		<th><a href="[% c.uri_for('resources','nscore3') %]">Estimated score</a>[3]</th>
 	</tr>
 [% FOR a IN alliances %]
@@ -17,18 +17,18 @@
 		<td><a href="[% c.uri_for("edit",a.id) %]">[% a.name %]</a></td>
 		<td class="[% a.relationship %]">[% a.relationship %]</td>
 		<td>[% a.members %] ([% a.planets %])</td>
-		<td>[% a.score %]</td>
-		<td>[% a.size %]</td>
-		<td>[% a.resources %]</td>
-		<td>[% a.hidden %]</td>
-		<td>[% a.resplanet %]</td>
-		<td>[% a.hidplanet %]</td>
-		<td>[% a.nscore %] ([% a.scoregain %])</td>
-		<td>[% a.nscore2 %] ([% a.nscore2 - a.score %])</td>
-		<td>[% a.nscore3 %] ([% a.scoregain3 %])</td>
+		<td>[% comma(a.score) %]</td>
+		<td>[% comma(a.size) %]</td>
+		<td>[% comma(a.resources) %]</td>
+		<td>[% comma(a.hidden) %]</td>
+		<td>[% comma(a.resplanet) %]</td>
+		<td>[% comma(a.hidplanet) %]</td>
+		<td>[% comma(a.nscore) %] ([% a.nscore - a.score %])</td>
+		<td>[% comma(a.nscore2) %] ([% a.nscore2 - a.score %])</td>
+		<td>[% comma(a.nscore3) %] ([% a.nscore3 - a.score %])</td>
 	</tr>
 [% END %]
 </table>
 <p>[1] = New score if resources are spent (gain is just the extra value compared to unspent resources) + hidden production getting released</p>
-<p>[2] = Same as [1] but calculated with avg resources and max 60 members</p>
-<p>[3] = Same as [2] but also includes resources if roids are kept to end of round (gain is just from the roids)</p>
+<p>[2] = Score for the top60 members, after res being spent and production getting released.</p>
+<p>[3] = Same as [2] but also includes resources if roids are kept to end of round</p>
-- 
2.39.2