-
Notifications
You must be signed in to change notification settings - Fork 2
/
notes.txt
206 lines (193 loc) · 14.2 KB
/
notes.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
Recent images uploaded:
find -type f -printf '%T+\t%p\n' | sort -n
Rewrite Rules:
RewriteEngine on
RewriteRule ^/allfactions$ /compendium.php
RewriteRule ^/faction/([a-zA-Z0-9]+)$ /faction.php?fa=$1
RewriteRule ^/fa/([a-zA-Z0-9]+)$ /faction.php?fa=$1
RewriteRule ^/faction/([a-zA-z0-9]+)/killteam/([a-zA-Z0-9]+)$ /killteam.php?fa=$1&kt=$2
RewriteRule ^/fa/([a-zA-Z0-9]+)/kt/([a-zA-Z0-9]+)$ /killteam.php?fa=$1&kt=$2
RewriteRule ^/roster/([\-a-zA-Z0-9]+)$ /roster.php?rid=$1
RewriteRule ^/r/([\-a-zA-Z0-9]+)$ /roster.php?rid=$1
RewriteRule ^/user/([a-zA-Z0-9]+)$ /rosters.php?uid=$1
RewriteRule ^/u/([a-zA-Z0-9]+)$ /rosters.php?uid=$1
RewriteRule ^/u$ /rosters.php
RewriteRule ^/rostergallery/([\-a-zA-Z0-9]+)$ /rostergallery.php?rid=$1
RewriteRule ^/r/([\-a-zA-Z0-9]+)/g$ /rostergallery.php?rid=$1
RewriteRule ^/dashboard/([a-zA-Z0-9]*)$ /dashboard.php?rid=$1
RewriteRule ^/dashboard$ /dashboard.php
SQL Queries:
SELECT COUNT(DISTINCT userid, userip) AS UserCount30Minute, COUNT(*) AS EventCount30Minute FROM Event WHERE userip NOT IN ('73.165.66.83', '68.80.166.102') AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -30 minute);
#SELECT useragent, COUNT(*) AS EventCount FROM Event WHERE userid NOT IN ('73.165.66.83', '68.80.166.102') AND useragent != '' AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -7 day) GROUP BY useragent ORDER BY COUNT(*) DESC;
#SELECT COUNT(*) AS EventCount, SUM(CASE sessiontype WHEN 'pwa' THEN 1 ELSE 0 END) AS PWAEventCount, SUM(CASE sessiontype WHEN 'browser' THEN 1 ELSE 0 END) AS BrowserEventCount FROM Event WHERE userip NOT IN ('73.165.66.83', '68.80.166.102') AND sessiontype <> '';
SELECT * FROM Event WHERE userip NOT IN ('73.165.66.83', '68.80.166.102') ORDER BY eventid DESC LIMIT 1000;
#SELECT * FROM Event WHERE eventtype = 'session' AND action = 'signup' AND userip NOT IN ('73.165.66.83', '68.80.166.102') ORDER BY eventid DESC LIMIT 100;
SELECT CAST(datestamp AS Date) AS Date, SUM(CASE WHEN action = 'signup' THEN 1 ELSE 0 END) AS SignupCount, COUNT(DISTINCT userip) AS UserCount, SUM(CASE WHEN eventtype = 'page' THEN 1 ELSE 0 END) AS PageViews FROM Event WHERE userip NOT IN ('73.165.66.83', '68.80.166.102') GROUP BY CAST(datestamp AS Date) ORDER BY 1 DESC;
SELECT CAST(datestamp AS Date) AS Date, COUNT(*) AS TotalEventCount, SUM(CASE sessiontype WHEN 'pwa' THEN 1 ELSE 0 END) AS PWAEventCount, SUM(CASE sessiontype WHEN 'pwa' THEN 0 ELSE 1 END) AS BrowserEventCount FROM Event WHERE userip NOT IN ('73.165.66.83', '68.80.166.102') AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -10 day) AND userid != '[anon]' GROUP BY CAST(datestamp AS Date) ORDER BY 1 DESC;
SELECT referrer, COUNT(*) AS ReferrerCount7Days FROM Event WHERE referrer != '' AND referrer NOT LIKE 'https://ktdash.app%' AND userip NOT IN ('73.165.66.83', '68.80.166.102') AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -7 day) GROUP BY referrer ORDER BY COUNT(*) DESC LIMIT 100;
SELECT * FROM Event WHERE action IN ('portrait', 'opportrait') AND userip NOT IN ('73.165.66.83', '68.80.166.102') AND label = 'custom' ORDER BY eventid DESC LIMIT 100;
#SELECT * FROM Event WHERE action = 'gallery' AND userip NOT IN ('73.165.66.83', '68.80.166.102') ORDER BY eventid DESC LIMIT 100;
#SELECT * FROM Event WHERE action IN ('import', 'clone') AND userip NOT IN ('73.165.66.83', '68.80.166.102') ORDER BY eventid DESC;
#SELECT * FROM Event WHERE action= 'print' AND userip NOT IN ('73.165.66.83', '68.80.166.102') ORDER BY eventid DESC;
#SELECT * FROM Event WHERE eventtype = 'settings' AND userip NOT IN ('73.165.66.83', '68.80.166.102') ORDER BY eventid DESC LIMIT 100;
#SELECT * FROM Event WHERE action = 'gettext' ORDER BY eventid DESC;
#SELECT * FROM Event WHERE action = 'help' ORDER BY eventid DESC;SE
SELECT 'Users' AS CountType, COUNT(*) AS Count FROM User WHERE userid NOT IN ('prebuilt', 'vince') UNION
SELECT 'Rosters', COUNT(*) AS RosterCount FROM Roster WHERE userid NOT IN ('prebuilt', 'vince') AND rostername != 'Sample Team: Intercessors' UNION
SELECT 'SpotlightRosters', COUNT(*) AS SpotlightCount FROM Roster WHERE userid NOT IN ('prebuilt', 'vince') AND spotlight = 1 UNION
SELECT 'RostersWithNotes', COUNT(*) AS RosterCount FROM Roster WHERE userid NOT IN ('prebuilt', 'vince') AND notes != '' AND rostername != 'Sample Team: Intercessors' UNION
SELECT 'RosterOps', COUNT(*) AS RosterOpCount FROM RosterOperative WHERE userid NOT IN ('prebuilt', 'vince') UNION
SELECT 'RosterOpsWithNotes', COUNT(*) AS RosterOpCount FROM RosterOperative WHERE userid NOT IN ('prebuilt', 'vince') AND notes != '';
#SELECT * FROM Event WHERE action = 'print' ORDER BY eventid DESC;
#SELECT useragent, COUNT(*) AS EventCount FROM Event WHERE userip NOT IN ('73.165.66.83', '68.80.166.102') GROUP BY useragent ORDER BY 2 DESC;
SELECT ' 3 Days' AS Period, COUNT(DISTINCT userid) AS DistinctActiveUsers FROM Event WHERE userid != '[anon]' AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -3 day) UNION
SELECT ' 7 Days' AS Period, COUNT(DISTINCT userid) AS DistinctActiveUsers FROM Event WHERE userid != '[anon]' AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -7 day) UNION
SELECT '14 Days' AS Period, COUNT(DISTINCT userid) AS DistinctActiveUsers FROM Event WHERE userid != '[anon]' AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -14 day) UNION
SELECT '21 Days' AS Period, COUNT(DISTINCT userid) AS DistinctActiveUsers FROM Event WHERE userid != '[anon]' AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -21 day) UNION
SELECT '28 Days' AS Period, COUNT(DISTINCT userid) AS DistinctActiveUsers FROM Event WHERE userid != '[anon]' AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -28 day) UNION
SELECT '90 Days' AS Period, COUNT(DISTINCT userid) AS DistinctActiveUsers FROM Event WHERE userid != '[anon]' AND datestamp > DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -90 day);
# Killteams that don't have spotlighted or prebuilt roster
SELECT *
FROM Killteam K LEFT JOIN Roster R
ON R.factionid = K.factionid AND R.killteamid = K.killteamid AND (R.spotlight = 1 OR R.userid = 'prebuilt')
WHERE R.rosterid IS NULL
# Action/Event Log
ALTER VIEW EventLogView
AS
SELECT
E.datestamp,
CASE CONCAT(E.eventtype, '|', E.action)
WHEN 'page|view' THEN
CONCAT(IFNULL(CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a>'), '[Anon]'), ' viewed page <a href="', E.url, '">', E.url, '</a>')
WHEN 'roster|addop' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> added ', O.opname, ' "', RO.opname, '" to <a href="/killteam.php?fa=', K.factionid, '&kt=', K.killteamid, '">', K.killteamname, '</a> roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>')
WHEN 'roster|create' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> created new <a href="/killteam.php?fa=', K.factionid, '&kt=', K.killteamid, '">', K.killteamname, '</a> roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>')
WHEN 'roster|importv1' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> imported a v1 roster')
WHEN 'roster|gettext' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> viewed roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>', '''s text description')
WHEN 'roster|cloneop' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> cloned a new operative into roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>')
WHEN 'dashboard|TP' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a>''s ', ' roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>', CASE WHEN var2 = 1 THEN ' moved to the next TP' ELSE ' went back to the previous TP' END)
WHEN 'session|signup' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> signed up')
WHEN 'dashboard|W' THEN
CONCAT(RO.opname, ' of ','<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a>''s ', ' roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>', ' ', CASE WHEN var2 = 1 THEN 'gained' ELSE 'lost' END, ' 1 Wound')
WHEN 'dashboard|CP' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a>''s ', ' roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>', ' ', CASE WHEN var2 = 1 THEN 'gained' ELSE 'used' END, ' 1 CP')
WHEN 'dashboard|VP' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a>''s ', ' roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>', ' ', CASE WHEN var2 = 1 THEN 'gained' ELSE 'lost' END, ' 1 VP')
WHEN 'dashboard|RP' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a>''s ', ' roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>', ' ', CASE WHEN var2 = 1 THEN 'gained' ELSE 'lost' END, ' 1 RP')
WHEN 'roster|print' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> printed their "', CASE label WHEN 'roster' THEN R.rostername ELSE RO.opname END, CASE label WHEN 'roster' THEN '" roster' ELSE '" operative' END)
WHEN 'roster|opportrait' THEN
CONCAT('<a href="/rosters.php?uid=', U.userid, '">', U.username, '</a> added a new ', CASE WHEN label = 'custom' THEN 'custom' ELSE 'default' END, ' portrait for "', RO.opname, '" of ', ' roster <a href="/roster.php?rid=', R.rosterid, '">', R.rostername, '</a>')
ELSE
''
END
AS ActionLog,
E.userid, E.eventtype, E.action, E.label, E.var1, E.var2, E.var3,
U.username, R.rostername, RO.opname,
O.opname AS optype
FROM
Event E
LEFT JOIN User U
ON U.userid = E.userid
LEFT JOIN Roster R
ON R.rosterid = E.var1
LEFT JOIN Killteam K
ON K.factionid = R.factionid
AND K.killteamid = R.killteamid
LEFT JOIN RosterOperative RO
ON RO.rosterid = E.var1
AND RO.rosteropid = E.var2
LEFT JOIN Operative O
ON O.factionid = RO.factionid
AND O.killteamid = RO.killteamid
AND O.fireteamid = RO.fireteamid
AND O.opid = RO.opid
WHERE
E.eventtype NOT IN ('compendium', 'pwa', 'opname')
AND CONCAT(E.eventtype, '|', E.action) NOT IN ('dashboard|selectroster', 'dashboard|reset', 'settings|view', 'roster|edit', 'settings|set', 'session|login', 'roster|view', 'roster|editop', 'roster|delop', 'dashboard|init', 'rosters|view', 'roster|killteamcomp', 'roster|delete', 'roster|gallery')
ORDER BY
E.eventid DESC;
SITEMAP GENERATOR - Last updated 2023-06-28
/* All Factions */
SELECT CONCAT('https://ktdash.app/fa/', factionid) FROM Faction UNION
/* All Killteams */
SELECT CONCAT('https://ktdash.app/fa/', factionid, '/kt/', killteamid) FROM Killteam UNION
/* Top users */
SELECT CONCAT('http://ktdash.app/u/', username) FROM User WHERE userid IN ('vince', 'tim', 'prebuilt') UNION
/* Top user rosters */
SELECT CONCAT('http://ktdash.app/r/', rosterid) FROM Roster WHERE userid IN ('vince', 'tim', 'prebuilt') UNION
/* Top user roster galleries */
SELECT CONCAT('http://ktdash.app/r/', rosterid, '/g') FROM Roster WHERE userid IN ('vince', 'tim', 'prebuilt') UNION
/* Users with spotlighted rosters */
SELECT DISTINCT CONCAT('http://ktdash.app/u/', username) FROM Roster R INNER JOIN User U ON U.userid = R.userid WHERE R.userid NOT IN ('vince', 'tim', 'prebuilt') AND R.spotlight = 1 UNION
/* Spotlighted rosters */
SELECT DISTINCT CONCAT('http://ktdash.app/r/', R.rosterid) FROM Roster R WHERE R.userid NOT IN ('vince', 'tim', 'prebuilt') AND R.spotlight = 1 UNION
/* Spotlighted roster galleries */
SELECT DISTINCT CONCAT('http://ktdash.app/r/', R.rosterid, '/g') FROM Roster R WHERE R.userid NOT IN ('vince', 'tim', 'prebuilt') AND R.spotlight = 1;
# Missing Narrative Info (Battle Honours, Rare Equipment, TacOps)
SELECT
KT.factionid, KT.killteamid, KT.killteamname,
SUM(CASE WHEN EQ.eqcategory = 'Battle Honour' THEN 1 ELSE 0 END) AS BattleHonours,
SUM(CASE WHEN EQ.eqcategory = 'Rare Equipment' THEN 1 ELSE 0 END) AS RareEquipments,
IFNULL(TAC.TacOpCount, 0) AS TacOps
FROM
Killteam KT
INNER JOIN Equipment EQ
ON EQ.factionid = KT.factionid
AND EQ.killteamid = KT.killteamid
LEFT JOIN
(
SELECT archetype, COUNT(*) AS TacOpCount
FROM TacOp
GROUP BY archetype
) TAC
#ON TAC.tacopid LIKE CONCAT(KT.factionid, '-', KT.killteamid, '-%')
ON TAC.archetype = KT.killteamname
WHERE CONCAT(KT.factionid, '-', KT.killteamid) NOT IN ('TAU-HC', 'TAU-CM', 'AEL-COM', 'AEL-CW', 'CHAOS-DG', 'CHAOS-CSM', 'IMP-IG', 'CHAOS-DAEM', 'IMP-ECC', 'IMP-GK', 'NEC-TW', 'IMP-FW', 'AEL-TRP', 'CHAOS-TS', 'IMP-SM', 'IMP-TOE', 'ORK-GSK', 'TYR-BC', 'TYR-HF')
GROUP BY
KT.factionid, KT.killteamid, KT.killteamname,
TAC.tacopcount
HAVING
SUM(CASE WHEN EQ.eqcategory = 'Battle Honour' THEN 1 ELSE 0 END) < 1
OR SUM(CASE WHEN EQ.eqcategory = 'Rare Equipment' THEN 1 ELSE 0 END) < 1
OR TAC.tacopcount < 1 OR TAC.tacopcount IS NULL
ORDER BY
1, 2;
#USER STATS
#MOST VIEWED ROSTERS
SELECT R.userid, U.username, rosterid, CONCAT('https://ktdash.app/r/', rosterid) AS Link, viewcount, importcount
FROM Roster R
INNER JOIN User U ON U.userid = R.userid
WHERE R.userid NOT IN ('prebuilt')
ORDER BY viewcount DESC
LIMIT 100;
#MOST VIEWED USERS
SELECT R.userid, U.username, CONCAT('https://ktdash.app/u/', U.username) AS Link, SUM(viewcount) AS totalviews
FROM Roster R
INNER JOIN User U ON U.userid = R.userid
WHERE R.userid NOT IN ('prebuilt')
GROUP BY R.userid, U.username, CONCAT('https://ktdash.app/u/', U.username)
ORDER BY SUM(viewcount) DESC
LIMIT 100;
#MOST IMPORTED ROSTERS
SELECT R.userid, U.username, rosterid, CONCAT('https://ktdash.app/r/', rosterid) AS Link, viewcount, importcount
FROM Roster R
INNER JOIN User U ON U.userid = R.userid
WHERE R.userid NOT IN ('prebuilt')
ORDER BY importcount DESC
LIMIT 100;
#MOST IMPORTED USERS
SELECT R.userid, U.username, CONCAT('https://ktdash.app/u/', U.username) AS Link, SUM(importcount) AS totalimports
FROM Roster R
INNER JOIN User U ON U.userid = R.userid
WHERE R.userid NOT IN ('prebuilt')
GROUP BY R.userid, U.username, CONCAT('https://ktdash.app/u/', U.username)
ORDER BY SUM(importcount) DESC
LIMIT 100;