def get_thread(thread_id):
cursor = connections['mybb'].cursor()
cursor.execute(
"SELECT * FROM mybb_threads, mybb_posts WHERE mybb_threads.firstpost = mybb_posts.pid AND mybb_threads.tid=%s",
[thread_id])
thread = utils.dictfetchall(cursor)[0]
thread['edittime'] = datetime.fromtimestamp(thread['edittime'])
thread['url'] = "/forum/showthread.php?tid={}".format(thread['tid'])
return thread
def get_threads(thread_prefix=None,
forum_name=None,
orderby=None,
sort=None,
offsetby=None,
count=None):
""" Returns a list of threads from the indicated forumId """
params = []
order, limit = "", ""
tables = ["mybb_threads", "mybb_posts"]
conditions = [
"mybb_threads.firstpost = mybb_posts.pid", "mybb_threads.visible=1"
]
if forum_name is not None:
tables.append("mybb_forums")
conditions.append("mybb_threads.fid = mybb_forums.fid")
if isinstance(forum_name, basestring):
conditions.append("mybb_forums.name = %s")
params.append(forum_name)
else:
inClause = ", ".join(["%s"] * len(forum_name))
conditions.append("mybb_forums.name IN (" + inClause + ")")
params.extend(forum_name)
if thread_prefix is not None:
tables.append("mybb_threadprefixes")
conditions.extend([
"mybb_threads.prefix = mybb_threadprefixes.pid",
"mybb_threadprefixes.prefix = %s"
])
params.append(thread_prefix)
if orderby:
order = "ORDER BY {}".format(orderby)
if sort:
order += " {}".format(sort)
if count:
limit = "LIMIT %s"
params.append(count)
if offsetby:
limit += " OFFSET %s"
params.append(offsetby)
query_template = "SELECT * FROM {} WHERE {} {} {}"
query = query_template.format(', '.join(tables), ' AND '.join(conditions),
order, limit)
cursor = connections['mybb'].cursor()
cursor.execute(query, params)
threads = utils.dictfetchall(cursor)
for thread in threads:
thread['url'] = "/forum/showthread.php?tid={}".format(thread['tid'])
return threads
def sync_users():
cursor = connections['mybb'].cursor()
cursor.execute("SELECT uid, username, loginkey, email FROM mybb_users")
users = utils.dictfetchall(cursor)
for mybb_user in users:
try:
User.objects.get(email=mybb_user['email'])
print("Found {}".format(mybb_user['email']))
except User.DoesNotExist:
print("Creating {}".format(mybb_user['email']))
password = binascii.b2a_hex(os.urandom(15))
User.objects.create_user(mybb_user['username'], mybb_user['email'], password)
def get_events_in_range(calendar_name, start_range, end_range):
""" Returns an ordered list of events that fall within the specified time range.
TODO: Adjust times based on encoded timezone
"""
# Get all of the events that might fall in this time range from the mybb db
query = '''
SELECT mybb_events.*
FROM mybb_events, mybb_calendars
WHERE mybb_calendars.name = %s
AND mybb_events.cid = mybb_calendars.cid
AND (
(endtime = 0 AND starttime > UNIX_TIMESTAMP(%s))
OR endtime > UNIX_TIMESTAMP(%s)
)
'''
cursor = connections['mybb'].cursor()
cursor.execute(query, [calendar_name, start_range, start_range])
# Iterate over the event listings and expand any repeated or multiday events
events = list()
for result in utils.dictfetchall(cursor):
# myBB records start and end times in unix epoch time
start_time = datetime.fromtimestamp(result['starttime'])
end_time = datetime.fromtimestamp(result['endtime'])
# Figure out the event duration by subtracting the time of day. If duration
# is negative, assume duration of less than 24 hours so make math easy. Duration
# is ignored for non-repeating ranged events so this shouldn't be an issue.
seconds = (result['endtime'] % 86400) - (result['starttime'] % 86400)
if seconds < 0:
seconds += 86400
duration = timedelta(seconds=seconds)
# Load the repeat data and find all relevant event times
repeat_data = phpserialize.loads(
result['repeats']) if result['repeats'] else None
times = get_event_times(start_time, min(end_time, end_range), duration,
repeat_data)
# For every time block in the requested range associated with this event
# copy the results output and insert some useful start and end times
for (start, end) in filter(lambda (start, end): end >= start_range,
times):
event = result.copy()
event['start'] = start
event['end'] = end
event['url'] = "/forum/calendar.php?action=event&eid={}".format(
event['eid'])
event['is_today'] = datetime.today().date() == start.date()
events.append(event)
def sync_users():
cursor = connections['mybb'].cursor()
cursor.execute("SELECT uid, username, loginkey, email FROM mybb_users")
users = utils.dictfetchall(cursor)
for mybb_user in users:
try:
User.objects.get(email=mybb_user['email'])
print("Found {}".format(mybb_user['email']))
except User.DoesNotExist:
print("Creating {}".format(mybb_user['email']))
password = binascii.b2a_hex(os.urandom(15))
User.objects.create_user(mybb_user['username'], mybb_user['email'],
password)
def show_global(request):
start_date = datetime.utcnow()-timedelta(seconds=7*86400)
cursor = db.connection.cursor()
global_stats_query = """
select sum(race='zerg')/count(*) as zerg,
sum(race='protoss')/count(*) as protoss,
sum(race='terran')/count(*) as terran,
count(distinct matchid) as matches,
count(distinct clientid) as players
from match_result_players, match_results
where match_results.id=matchid
AND FROM_UNIXTIME(datetime) > %s
"""
cursor.execute(global_stats_query, [start_date])
global_stats = utils.dictfetchall(cursor)[0]
return render(request, 'ladder/global.html', dict(global_stats=global_stats))
def get_events_in_range(calendar_name, start_range, end_range):
""" Returns an ordered list of events that fall within the specified time range.
TODO: Adjust times based on encoded timezone
"""
# Get all of the events that might fall in this time range from the mybb db
query = """
SELECT mybb_events.*
FROM mybb_events, mybb_calendars
WHERE mybb_calendars.name = %s
AND mybb_events.cid = mybb_calendars.cid
AND (
(endtime = 0 AND starttime > UNIX_TIMESTAMP(%s))
OR endtime > UNIX_TIMESTAMP(%s)
)
"""
cursor = connections["mybb"].cursor()
cursor.execute(query, [calendar_name, start_range, start_range])
# Iterate over the event listings and expand any repeated or multiday events
events = list()
for result in utils.dictfetchall(cursor):
# myBB records start and end times in unix epoch time
start_time = datetime.fromtimestamp(result["starttime"])
end_time = datetime.fromtimestamp(result["endtime"])
# Figure out the event duration by subtracting the time of day. If duration
# is negative, assume duration of less than 24 hours so make math easy. Duration
# is ignored for non-repeating ranged events so this shouldn't be an issue.
seconds = (result["endtime"] % 86400) - (result["starttime"] % 86400)
if seconds < 0:
seconds += 86400
duration = timedelta(seconds=seconds)
# Load the repeat data and find all relevant event times
repeat_data = phpserialize.loads(result["repeats"])
times = get_event_times(start_time, min(end_time, end_range), duration, repeat_data)
# For every time block in the requested range associated with this event
# copy the results output and insert some useful start and end times
for (start, end) in filter(lambda (start, end): end >= start_range, times):
event = result.copy()
event["start"] = start
event["end"] = end
event["url"] = "/forum/calendar.php?action=event&eid={}".format(event["eid"])
event["is_today"] = datetime.today().date() == start.date()
events.append(event)
def get_threads(thread_prefix=None, forum_name=None, orderby=None, sort=None, offsetby=None, count=None):
""" Returns a list of threads from the indicated forumId """
params = []
order, limit = "", ""
tables = ["mybb_threads", "mybb_posts"]
conditions = ["mybb_threads.firstpost = mybb_posts.pid", "mybb_threads.visible=1"]
if forum_name is not None:
tables.append("mybb_forums")
conditions.append("mybb_threads.fid = mybb_forums.fid")
if isinstance(forum_name, basestring):
conditions.append("mybb_forums.name = %s")
params.append(forum_name)
else:
inClause = ", ".join(["%s"] * len(forum_name))
conditions.append("mybb_forums.name IN (" + inClause + ")")
params.extend(forum_name)
if thread_prefix is not None:
tables.append("mybb_threadprefixes")
conditions.extend(["mybb_threads.prefix = mybb_threadprefixes.pid", "mybb_threadprefixes.prefix = %s"])
params.append(thread_prefix)
if orderby:
order = "ORDER BY {}".format(orderby)
if sort:
order += " {}".format(sort)
if count:
limit = "LIMIT %s"
params.append(count)
if offsetby:
limit += " OFFSET %s"
params.append(offsetby)
query_template = "SELECT * FROM {} WHERE {} {} {}"
query = query_template.format(", ".join(tables), " AND ".join(conditions), order, limit)
cursor = connections["mybb"].cursor()
cursor.execute(query, params)
threads = utils.dictfetchall(cursor)
for thread in threads:
thread["url"] = "/forum/showthread.php?tid={}".format(thread["tid"])
return threads
def show_global(request):
start_date = datetime.utcnow() - timedelta(seconds=7 * 86400)
cursor = db.connection.cursor()
global_stats_query = """
select sum(race='zerg')/count(*) as zerg,
sum(race='protoss')/count(*) as protoss,
sum(race='terran')/count(*) as terran,
count(distinct matchid) as matches,
count(distinct clientid) as players
from match_result_players, match_results
where match_results.id=matchid
AND FROM_UNIXTIME(datetime) > %s
"""
cursor.execute(global_stats_query, [start_date])
global_stats = utils.dictfetchall(cursor)[0]
return render(request, 'ladder/global.html',
dict(global_stats=global_stats))
def show_region(request, region):
region_id = REGION_LOOKUP[region.upper()]
start_date = datetime.utcnow()-timedelta(seconds=7*86400)
cursor = db.connection.cursor()
region_stats_query = """
select sum(race='zerg')/count(*) as zerg,
sum(race='protoss')/count(*) as protoss,
sum(race='terran')/count(*) as terran,
count(distinct matchid) as matches,
count(distinct clientid) as players
from match_result_players, match_results
where match_results.id=matchid
AND region=%s
AND FROM_UNIXTIME(datetime) > %s
"""
cursor.execute(region_stats_query, [region_id, start_date])
region_stats = utils.dictfetchall(cursor)[0]
return render(request, 'ladder/region.html', dict(region_str=region.upper(), region=REGION_LOOKUP[region.upper()], region_stats=region_stats))
def show_region(request, region):
region_id = REGION_LOOKUP[region.upper()]
start_date = datetime.utcnow() - timedelta(seconds=7 * 86400)
cursor = db.connection.cursor()
region_stats_query = """
select sum(race='zerg')/count(*) as zerg,
sum(race='protoss')/count(*) as protoss,
sum(race='terran')/count(*) as terran,
count(distinct matchid) as matches,
count(distinct clientid) as players
from match_result_players, match_results
where match_results.id=matchid
AND region=%s
AND FROM_UNIXTIME(datetime) > %s
"""
cursor.execute(region_stats_query, [region_id, start_date])
region_stats = utils.dictfetchall(cursor)[0]
return render(
request, 'ladder/region.html',
dict(region_str=region.upper(),
region=REGION_LOOKUP[region.upper()],
region_stats=region_stats))
