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/')