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