X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=database%2Ffunctions%2Fdef_leeches.sql;fp=database%2Ffunctions%2Fdef_leeches.sql;h=74a4c46a06204887fb21a566b7604e8501a4a743;hb=76887e53c27118fe04ae8fe75dce610a398b5853;hp=0000000000000000000000000000000000000000;hpb=73119e806350719e2ce899bfbaa2ba19f0dc87d1;p=ndwebbie.git

diff --git a/database/functions/def_leeches.sql b/database/functions/def_leeches.sql
new file mode 100644
index 0000000..74a4c46
--- /dev/null
+++ b/database/functions/def_leeches.sql
@@ -0,0 +1,34 @@
+CREATE OR REPLACE VIEW def_leeches AS
+WITH f AS (
+	SELECT uid,fid,lc.pid,f.pid AS fpid,landing_tick,eta,back, SUM(fs.amount*(s.metal + s.crystal + s.eonium)/100.0) AS value
+	FROM launch_confirmations lc
+		JOIN fleets f USING (fid)
+		JOIN fleet_ships fs USING (fid)
+		JOIN ship_stats s ON fs.ship = s.name
+	WHERE mission = 'Defend'
+	GROUP BY uid,fid,lc.pid,f.pid,landing_tick,eta,back
+), f2 AS (
+	SELECT f.uid
+		,SUM(f.value / COALESCE(p.value, (SELECT value FROM planet_stats WHERE pid = f.fpid AND tick = landing_tick - eta ORDER BY tick DESC LIMIT 1))) AS sent_value
+	FROM calls c
+		JOIN users u USING (uid)
+		JOIN f USING (pid,landing_tick)
+		LEFT JOIN (SELECT pid AS fpid,value,tick AS landing_tick FROM planet_stats) AS p USING (fpid, landing_tick)
+	GROUP BY f.uid
+)
+SELECT uid,username,defense_points,count(call) AS calls
+	, SUM(fleets) AS fleets, SUM(recalled) AS recalled
+	,count(NULLIF(fleets,0)) AS defended_calls
+	,SUM(value)::NUMERIC(4,2) AS value
+	,sent_value::NUMERIC(4,2)
+FROM (SELECT u.uid,username,defense_points,call,count(f.back) AS fleets
+		, count(NULLIF(f.landing_tick + f.eta -1 = f.back,TRUE)) AS recalled
+		,SUM(f.value / COALESCE(p.value, (SELECT value FROM planet_stats WHERE pid = f.pid AND tick = landing_tick - eta ORDER BY tick DESC LIMIT 1))) AS value
+	FROM users u
+		JOIN calls c USING (uid)
+		LEFT JOIN f USING (pid,landing_tick)
+		LEFT JOIN (SELECT pid,value,tick AS landing_tick FROM planet_stats) AS p USING (pid, landing_tick)
+	GROUP BY u.uid,username,defense_points,call
+) d
+	LEFT JOIN f2 USING (uid)
+GROUP BY uid,username,defense_points, sent_value