def mybb_auth(request):
cookie = request.COOKIES.get("mybbuser", None)
if cookie is None:
return None
try:
(uid, loginkey) = cookie.split("_", 1)
except KeyError:
return None
except ValueError:
return None
conn = utils.connect("emforum")
c = conn.cursor()
c.execute(
"""SELECT username, usergroup, additionalgroups
FROM mybb_users
WHERE uid=%s AND loginkey=%s
LIMIT 1
""",
(uid, loginkey),
)
if c.rowcount < 1:
return None
(username, usergroup, additionalgroups) = c.fetchone()
groups = [int(usergroup)]
if additionalgroups != "":
groups.extend([int(x) for x in additionalgroups.split(",")])
c.execute("SELECT title FROM mybb_usergroups " "WHERE gid IN (%s)" % ", ".join(["%s"] * len(groups)), groups)
return (uid, username, [name for (name,) in c.fetchall()])
def json_opnotify(request):
if ((not request.user.is_authenticated() or
request.user.profile.characterid is None)):
return HttpResponse(json.dumps([]), mimetype="text/json")
fid_list = []
if 'Ally' in request.user.profile.mybb_groups:
fid_list.append(FID_ALLY)
if 'Lutinari Syndicate' in request.user.profile.mybb_groups:
fid_list.append(FID_LUTI)
if 'Gradient' in request.user.profile.mybb_groups:
fid_list.append(FID_GRD)
if 'Electus Matari' in request.user.profile.mybb_groups:
fid_list.append(FID_EM)
fid_list.append(FID_ALLY)
if len(fid_list) == 0:
return HttpResponse(json.dumps([]), mimetype="text/json")
now = datetime.datetime.utcnow()
today = "%s.%02i.%02i" % (now.year - 1898, now.month, now.day)
conn = connect('emforum')
c = conn.cursor()
c.execute("SELECT tid, username, subject "
"FROM mybb_threads "
"WHERE fid in (%s) "
" AND NOT sticky "
" AND subject REGEXP '^[0-9]+\\.[0-9]{2}\\.[0-9]{2}' "
" AND subject >= %%s"
% (", ".join(str(fid) for fid in fid_list),),
(today,))
result = []
for tid, username, subject in c.fetchall():
m = OP_SUBJECT_RX.match(subject)
if m is None:
continue
year, month, day = (int(x) for x in m.group(1).split("."))
hour, minute = (int(x) for x in m.group(2).split(":"))
try:
dt = datetime.datetime(year + 1898, month, day, hour, minute)
except ValueError:
continue
timestamp = time.mktime(dt.timetuple())
location = m.group(3)
text = m.group(4)
result.append({'url': 'http://www.electusmatari.com/forums/showthread.php?tid=%s' % (tid,),
'tid': tid,
'username': username,
'time': int(timestamp),
'location': location,
'text': text})
result.sort(key=lambda obj: obj['time'])
return HttpResponse(json.dumps(result), mimetype="text/json")
def setavatar(request):
if request.user.profile.characterid is None:
messages.add_message(request, messages.ERROR,
"Avatar update failed, you are not "
"authenticated.")
return HttpResponseRedirect('/auth/')
db = utils.connect('emforum')
c = db.cursor()
userauth.mybb_setavatar(c, request.user.profile.mybb_uid,
("https://image.eveonline.com/Character/%s_64.jpg"
% (request.user.profile.characterid,)),
64, 64)
db.commit()
messages.add_message(request, messages.INFO,
"Avatar successfully updated.")
return HttpResponseRedirect('/auth/')
def kb_lastseen(corpname):
db = utils.connect('emkillboard')
c = db.cursor()
c.execute("""
SELECT p.plt_name AS name,
CAST(GREATEST(COALESCE(MAX(invd.ind_timestamp), MAKEDATE(1970, 1)),
COALESCE(MAX(k.kll_timestamp), MAKEDATE(1970, 1)))
AS DATETIME) AS lastseen
FROM kb3_pilots p
INNER JOIN kb3_corps c ON p.plt_crp_id = c.crp_id
LEFT JOIN kb3_inv_detail invd ON p.plt_id = invd.ind_plt_id
LEFT JOIN kb3_kills k ON p.plt_id = k.kll_victim_id
WHERE c.crp_name = %s
GROUP BY p.plt_name
""",
(corpname,))
return dict(c.fetchall())
def add_diplo_status(request, status):
if request.user.is_anonymous():
return
if ('Diplomats' not in request.user.profile.mybb_groups and
'Council' not in request.user.profile.mybb_groups):
return
conn = connect('emforum')
c = conn.cursor()
c.execute("""
SELECT t.prefix, COUNT(*)
FROM mybb_threads AS t
LEFT JOIN mybb_posts AS p
ON t.firstpost = p.pid
LEFT JOIN mybb_users AS eu
ON (CASE p.edituid WHEN 0 THEN p.uid ELSE p.edituid END) = eu.uid
WHERE t.fid = %s
AND (t.prefix IN (%s, %s)
OR (t.prefix = %s
AND eu.uid = %s))
GROUP BY t.prefix
""", (DIPLO_FORUM,
PREFIX_NEED_ATTENTION,
PREFIX_SET_STANDING,
PREFIX_IN_ACTION,
request.user.profile.mybb_uid
))
prefix_counts = dict(c.fetchall())
numopen = prefix_counts.get(PREFIX_NEED_ATTENTION, 0)
numassigned = prefix_counts.get(PREFIX_IN_ACTION, 0)
numset = prefix_counts.get(PREFIX_SET_STANDING, 0)
if 'Diplomats' in request.user.profile.mybb_groups:
if numopen > 0 or numassigned > 0:
status.append({'text': '%i open diplomatic file%s' %
(numopen, "s" if numopen != 1 else ""),
'url': 'http://www.electusmatari.com/standings/check/'})
status.append({'text': '%i assigned to you' %
(numassigned,),
'url': 'http://www.electusmatari.com/standings/check/'})
if 'Council' in request.user.profile.mybb_groups:
if numset > 0:
status.append({'text': '%i standing%s to be set' %
(numset, "s" if numset != 1 else ""),
'url': 'http://www.electusmatari.com/standings/check/'})
def get_forum_details():
db = utils.connect('emforum')
c = db.cursor()
c.execute("SELECT LOWER(subject), tid, prefix FROM mybb_threads "
"WHERE fid IN (129, 130, 131)")
personnel_threads = dict((subject, (tid, prefix))
for (subject, tid, prefix) in c.fetchall())
c.execute("SELECT LOWER(u.username), COUNT(*) "
"FROM mybb_threads t "
" INNER JOIN mybb_users u ON t.uid = u.uid "
"WHERE t.fid = 123 "
"GROUP BY LOWER(u.username)")
intro_threads = dict(c.fetchall())
c.execute("SELECT LOWER(username) "
"FROM mybb_users "
"WHERE CONCAT(',', usergroup, ',', additionalgroups, ',') "
"LIKE '%,60,%'")
grd_users = set(name for (name,) in c.fetchall())
return personnel_threads, intro_threads, grd_users
def view_stats(request):
conn = utils.connect('emmisc')
c = conn.cursor()
c.execute("SELECT table_schema, "
" SUM(table_rows) AS rows, "
" SUM(data_length + index_length) AS size "
"FROM information_schema.TABLES "
"GROUP BY table_schema "
"ORDER BY size DESC")
mysql_databases = c.fetchall()
c.execute("SELECT table_name, "
" table_rows AS rows, "
" data_length + index_length AS size "
"FROM information_schema.TABLES "
"ORDER BY size DESC")
mysql_tables = c.fetchall()
c = connection.cursor()
c.execute("SELECT 'eve', "
" (SELECT SUM(n_live_tup) FROM pg_stat_all_tables), "
" pg_database_size('eve')")
postgresql_databases = c.fetchall()
c.execute("""
SELECT CASE WHEN nspname = 'public'
THEN relname
ELSE nspname || '.' || relname
END AS "relation",
C.reltuples::numeric AS rows,
pg_total_relation_size(C.oid) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
""")
postgresql_tables = c.fetchall()
return direct_to_template(request, 'emadmin/stats.html',
extra_context={'tab': 'stats',
'mysql_databases': mysql_databases,
'mysql_tables': mysql_tables,
'postgresql_databases': postgresql_databases,
'postgresql_tables': postgresql_tables})
def view_groups(request):
db = utils.connect('emforum')
c = db.cursor()
c.execute("SELECT gid, title FROM mybb_usergroups")
gid2name = dict(c.fetchall())
c.execute("SELECT uid, username, lastactive, usergroup, additionalgroups "
"FROM mybb_users")
group_users = {}
now = datetime.datetime.utcnow()
for (mybb_uid, mybb_username, lastactive,
usergroup, additionalgroups) in c.fetchall():
try:
user = User.objects.get(profile__mybb_uid=mybb_uid)
except User.DoesNotExist:
user = None
lastactive = datetime.datetime.utcfromtimestamp(lastactive)
delta = now - lastactive
gids = [usergroup] + [int(gid) for gid in additionalgroups.split(",")
if gid != '']
for gid in gids:
group = gid2name.get(gid, "<Group %i>" % gid)
if group in IGNORED_GROUPS:
continue
group_users.setdefault(group, [])
group_users[group].append({'name': mybb_username,
'user': user,
'lastactive': lastactive,
'days': delta.days,
'cssclass': ('veryold'
if delta.days > OLD_DAYS
else '')})
for users in group_users.values():
users.sort(lambda a, b: cmp(a['name'].lower(), b['name'].lower()))
groups = [(name, len(users), users)
for (name, users) in group_users.items()]
groups.sort(lambda a, b: cmp(a[0].lower(), b[0].lower()))
return direct_to_template(request, 'emadmin/groups.html',
extra_context={'tab': 'groups',
'group_list': groups})
def authenticate_users(user=None):
"""
Authenticate all users.
Go through MyBB users. If they do not have a profile, drop groups.
If they have a profile, if it's not active, drop groups.
Go through groups.
"""
start = datetime.datetime.utcnow()
db = utils.connect('emforum')
# dict of name -> (titles, freeformtitle)
grddetails = get_gradient_details()
# set of entityID
allies = get_allies()
if user is None:
users = get_mybbusers(db)
else:
users = get_mybbusers(db, user.profile.mybb_uid)
api = apiroot()
totalcount = 0
activecount = 0
for mybbuser in users:
if mybbuser.has_group('Banned'):
continue
totalcount += 1
oldcorp = None
if mybbuser.profile:
oldcorp = mybbuser.profile.corp
reason = update_single_user(api, mybbuser, grddetails, allies)
if len(mybbuser.toadd) == 0:
active = False
else:
active = True
activecount += 1
if mybbuser.profile:
if mybbuser.profile.usertitle:
usertitle = '<b>%s</b><br />' % mybbuser.profile.usertitle
elif mybbuser.has_group('Council'):
usertitle = '<b>Council Member</b><br />'
elif mybbuser.has_group('Diplomats'):
usertitle = '<b>Alliance Diplomat</b><br />'
else:
usertitle = ''
if mybbuser.profile.alliance:
usertitle += '%s<br />%s' % (mybbuser.profile.corp,
mybbuser.profile.alliance)
elif mybbuser.profile.corp:
usertitle += mybbuser.profile.corp
mybbuser.usertitle = usertitle
mybbuser.save()
if mybbuser.profile:
mybbuser.profile.active = active
mybbuser.profile.save()
newcorp = mybbuser.profile.corp
change_description = mybbuser.change_description()
if change_description is not None:
message = "User %s %s: %s" % (mybbuser.username,
change_description,
reason)
if not active:
message += ", marking inactive"
log.info("%s" % (message,))
if mybbuser.profile:
for corp in set([oldcorp, newcorp]):
if corp is not None:
AuthLog.objects.create(corp=corp, message=message)
else:
AuthLog.objects.create(corp=None, message=message)
db.commit()
end = datetime.datetime.utcnow()
if user is None:
log.info("Authenticated %i active users of %i in %s" %
(activecount, totalcount, str(end - start)))
def view_activity(request):
# Corp restriction
if request.user.is_staff:
if "corpname" in request.GET:
corp = request.GET["corpname"]
else:
corp = "Electus Matari"
else:
corp = request.user.profile.corp
# Date restriction
if "todate" in request.GET and request.GET["todate"] != '':
try:
todate = datetime.datetime.strptime(request.GET["todate"],
"%Y-%m-%d")
except ValueError:
todate = datetime.datetime.utcnow()
messages.add_message(request, messages.ERROR,
"To date %s is malformed, should be "
"YYYY-MM-DD." % request.GET["todate"])
else:
todate = datetime.datetime.utcnow()
if "fromdate" in request.GET and request.GET["fromdate"] != '':
try:
fromdate = datetime.datetime.strptime(request.GET["fromdate"],
"%Y-%m-%d")
except ValueError:
fromdate = todate - datetime.timedelta(days=28)
messages.add_message(request, messages.ERROR,
"From date %s is malformed, should be "
"YYYY-MM-DD." % request.GET["fromdate"])
else:
fromdate = todate - datetime.timedelta(days=28)
todate = todate.replace(hour=0, minute=0, second=0, microsecond=0)
fromdate = fromdate.replace(hour=0, minute=0, second=0, microsecond=0)
# Actual data
db = utils.connect("emkillboard")
c = db.cursor()
c.execute("SELECT HOUR(k.kll_timestamp), p.plt_name "
"FROM kb3_kills k "
" INNER JOIN kb3_inv_detail inv ON k.kll_id = inv.ind_kll_id "
" INNER JOIN kb3_alliances a ON inv.ind_all_id = a.all_id "
" INNER JOIN kb3_corps c ON inv.ind_crp_id = c.crp_id "
" INNER JOIN kb3_pilots p ON inv.ind_plt_id = p.plt_id "
"WHERE k.kll_timestamp >= %s "
" AND k.kll_timestamp < %s "
" AND (a.all_name = %s OR c.crp_name = %s)",
(fromdate, todate, corp, corp))
kbcount = {}
for hour, name in c:
kbcount.setdefault(hour, set())
kbcount[hour].add(name)
killboarddata = [(hour, len(pilots)) for (hour, pilots) in kbcount.items()]
killboarddata.sort()
# forumdata
wanted_uids = [x.mybb_uid for x in
Profile.objects.filter(Q(active=True) &
(Q(corp=corp) |
Q(alliance=corp)))]
if len(wanted_uids) == 0:
forumdata = []
else:
db = utils.connect("emforum")
c = db.cursor()
c.execute("SELECT hour, COUNT(*) "
"FROM (SELECT DISTINCT HOUR(access) AS hour, uid "
" FROM user_log "
" WHERE access >= %%s "
" AND access < %%s "
" AND uid IN (%s) "
" ) AS sq "
"GROUP BY hour" % ", ".join(["%s"] * len(wanted_uids)),
[fromdate, todate] + wanted_uids)
# Displace by 0.5 to put it next to the killboard bars
forumdata = [(hour + 0.5, count) for (hour, count) in c.fetchall()]
return direct_to_template(request, 'corpadmin/activity.html',
extra_context={'tab': 'activity',
'corpname': corp,
'is_staff': request.user.is_staff,
'fromdate': fromdate.strftime("%Y-%m-%d"),
'todate': todate.strftime("%Y-%m-%d"),
'killboarddata': json.dumps(killboarddata),
'forumdata': json.dumps(forumdata)})
def mybb_lastseen():
db = utils.connect('emforum')
c = db.cursor()
c.execute("SELECT uid, lastactive FROM mybb_users")
return dict(c.fetchall())
def main_handle_apikey(request):
profile = request.user.profile
keyid = request.POST.get('keyID', None)
vcode = request.POST.get('vCode', None)
selected_charid = request.POST.get('characterID', None)
dorename = request.POST.get('dorename', False)
api = apiroot()
if vcode is not None and not 'emforum' in vcode:
messages.add_message(request, messages.ERROR,
"Please change your Verification Code so "
"it contains the string 'emforum'")
return HttpResponseRedirect('/auth/')
try:
chars = api.account.Characters(keyID=keyid, vCode=vcode)
chars = [(row.name, row.characterID) for row in chars.characters]
except Exception as e:
messages.add_message(request, messages.ERROR,
"Error during API call: %s" % str(e))
return HttpResponseRedirect('/auth/')
if len(chars) == 1:
charname, charid = chars[0]
else:
charname = None
charid = None
for thischarname, thischarid in chars:
if (str(thischarid) == selected_charid or
thischarname.lower() == profile.mybb_username.lower()):
charname = thischarname
charid = thischarid
break
if charname is None:
return direct_to_template(
request, 'emauth/main.html',
extra_context={'state': 'select_character',
'character_list': sorted(chars),
'keyID': keyid,
'vCode': vcode,
'tab': 'auth'})
if charname != profile.mybb_username:
if not dorename:
return direct_to_template(
request, 'emauth/main.html',
extra_context={'state': 'ask_rename',
'keyID': keyid,
'vCode': vcode,
'characterID': charid,
'forum_name': profile.mybb_username,
'char_name': charname,
'tab': 'auth'})
else:
db = utils.connect('emforum')
c = db.cursor()
if not userauth.mybb_setusername(c, profile.mybb_uid, charname):
db.rollback()
messages.add_message(request, messages.ERROR,
"That username already exists. Please "
"contact a forum administrator to "
"resolve this conflict.")
return HttpResponseRedirect('/auth/')
db.commit()
messages.add_message(request, messages.INFO,
"Forum username changed to %s" % charname)
# Forum username is correct now
# Set the profile info
charinfo = api.eve.CharacterInfo(characterID=charid)
profile.name = charinfo.characterName
profile.characterid = charinfo.characterID
profile.corp = charinfo.corporation
profile.corpid = charinfo.corporationID
profile.alliance = getattr(charinfo, 'alliance', None)
profile.allianceid = getattr(charinfo, 'allianceID', None)
profile.active = True
profile.save()
try:
userauth.authenticate_users(profile.user)
messages.add_message(request, messages.INFO,
"Authentication successful.")
except userauth.AuthenticationError as e:
messages.add_message(request, messages.ERROR,
str(e))
return HttpResponseRedirect('/auth/')
