def chapter(url):
db = get_db()
# Whoops, target_user is basically the return of get_chapter. We can clean that up later
target_user = db.execute('SELECT username from user where url = ?',
(url, )).fetchone()
# Retrieve all the actions authored by this chapter
actions = db.execute('SELECT * from action where author_id = ?',
(target_user)).fetchall()
# Retrieve the chapter with get_chapter
chapter = get_chapter(url)
# Rave values for community building, policy change, and training and education
raw_cb = chapter['cb']
raw_pc = chapter['pc']
raw_te = chapter['te']
# We're going to use these percentages to fill up the progress bars with CSS
cb_percent = math.floor((raw_cb / 75) * 100)
pc_percent = math.floor((raw_pc / 75) * 100)
te_percent = math.floor((raw_te / 50) * 100)
# No need to fill a progress bar more than 100%
if cb_percent > 100:
cb_percent = 100
if pc_percent > 100:
pc_percent = 100
if te_percent > 100:
te_percent = 100
# A dictionary of those percentages to pass to the page template
points = {'cb': cb_percent, 'pc': pc_percent, 'te': te_percent}
return render_template('chapters/chapter.html',
chapter=chapter,
points=points,
actions=actions)
def userEdit(url):
db = get_db()
user = db.execute(
"SELECT username, email, password, permissions"
" FROM user where url = ?", (url, )
).fetchone()
# If we POST to the page, update the user
if request.method =='POST':
username = request.form['username']
email = request.form['email']
# In the template, we don't actually spit out the password
# This is ostensibly because of security reasons
# But the honest truth is I didn't want to figure out the hashing and rehashing logic at the moment
# Check if password was left blank. If it's blank, just use the same password. If not, hash it and insert.
if request.form['password'] != "":
password = generate_password_hash(request.form['password'])
else:
password = user['password']
# This could be a dropdown in the future, but for now I trust myself (mostly) not to make fatal typos
# You can tell it's the end of the week as I write this because of what I... just said
permissions = request.form['permissions']
print(user['username'])
db.execute(
'UPDATE user SET username = ?, email = ?, password = ?, permissions = ?'
' WHERE username = ?',
(username, email, password, permissions, user['username'])
)
db.commit()
# Redirect to the chapter page we just updated
return redirect(url_for('chapters.chapter', url=url))
return render_template('admin/user-edit.html', user=user)
def create():
# If we POST to the page, grabe the activity, points, and note.
if request.method == 'POST':
title = request.form['activity']
points = request.form['points']
note = request.form['note']
# This error handling stuff is from the tutorial, and I didn't do a great job implementing it elswhere
# I haven't taken the time to fully understand what's going on here, but I will eventually.
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
# Insert the action info into the database
db = get_db()
db.execute(
'INSERT INTO action (title, points, note, author_id)'
' Values (?, ?, ?, ?)', (title, points, note, g.user['id']))
# Update Chapter Building, Policy Change, or Training and Education Points on the Chapter profiles.
if title == 'policy':
db.execute('UPDATE user SET pc = pc + ? Where id = ?',
(int(points), g.user['id']))
elif title == 'community':
db.execute('UPDATE user SET cb = ? Where id = ?',
(int(points), g.user['id']))
elif title == 'education':
db.execute('UPDATE user SET te = ? Where id = ?',
(int(points), g.user['id']))
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/create.html')
def index():
db = get_db()
# Retrieve all the chapters, some of their data, and order by username alphabetically
chapters = db.execute('SELECT username, cb, pc, te, permissions, url'
' FROM user'
' ORDER BY username ASC').fetchall()
return render_template('chapters/index.html', chapters=chapters)
def register():
"""Register a new user.
Validates that the username is not already taken. Hashes the
password for security.
"""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif db.execute('SELECT id FROM user WHERE username = ?',
(username, )).fetchone() is not None:
error = 'User {0} is already registered.'.format(username)
if error is None:
# the name is available, store it in the database and go to
# the login page
db.execute('INSERT INTO user (username, password) VALUES (?, ?)',
(username, generate_password_hash(password)))
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html')
def spending():
db = get_db()
# Retrieve the full chapters list to send as dropdown
chapters = db.execute(
'SELECT username FROM user WHERE permissions LIKE "Chapter"'
)
# Retrieve the full list of spending items to represent as dropdown
items = db.execute(
'SELECT title FROM spending_list'
)
# Retrieve the list of chapters from the database
chapters = db.execute('SELECT username FROM user WHERE permissions LIKE "Chapter"')
# Init an empty list for the chapter listing
chapter_list = []
# Push the usernames into the chapter list for ease of use in search bar on page
for x in chapters.fetchall():
chapter_list.append((str(x['Username'])))
# If we POST to the page, update the database
if request.method == 'POST':
item = request.form['item']
cost = request.form['cost']
if g.user['permissions'] == 'Admin' or g.user['permissions'] == 'Staffer':
chapter = request.form['chapter']
# If the user is a chapter, they can only log points for themselves, so we default to their username
else:
chapter = g.user['username']
db = get_db()
# Get the chapter we're creating spending for
# May have to do some conditional logic in the chapter variable to account for students creating spending for themselves, like we do in the points logging
chapter_id = db.execute(
'SELECT id FROM user WHERE username LIKE ?',
(chapter,)
).fetchone()['id']
# Update the balance by subtracting the cost from the balance
db.execute(
'UPDATE user SET balance = balance - ? Where username = ?',
(int(cost), chapter,)
)
# Add this spending item to the spending table in db
db.execute(
'INSERT INTO spending (title, points, author_id)'
' Values (?, ?, ?)',
(item, int(cost), int(chapter_id),)
)
db.commit()
# Not sure where this should redirect to
return render_template('admin/spending.html', chapters=chapters, items=items, chapter_list=chapter_list)
def availableActivities():
db = get_db()
# Retrive the activities list
activities = db.execute(
'SELECT title, description, type, point_value, spanish_translation'
' FROM action_list').fetchall()
return render_template('blog/available-activities.html',
activities=activities)
def leaderboard():
db = get_db()
# Get chapter data for users with Chapter permissions, order by the sum of cb + pc + te
# Ordering by balance won't work well, since balance is more for spending and won't be clean
chapters = db.execute(
"SELECT username, cb, pc, te, balance, permissions, url"
" FROM user WHERE permissions LIKE 'Chapter'"
" ORDER BY cb + pc + te DESC").fetchall()
return render_template('blog/leaderboard.html', chapters=chapters)
def get_action(id):
# Returns an action based on the id
post = get_db().execute('SELECT * from action WHERE id = ?',
(id, )).fetchone()
if post is None:
abort(404, "Post id {0} doesn't exist.".format(id))
return post
def get_chapter(url):
# Return a specific chapter
chapter = get_db().execute(
'SELECT username, cb, pc, te, balance, url'
' FROM user WHERE url = ?', (url, )).fetchone()
if chapter is None:
abort(404, "Post id {0} doesn't exist.".format(id))
return chapter
def userList():
db = get_db()
# Retrieve all the users, get most of their fields, and order by username ascending
chapter_list = db.execute(
"SELECT username, cb, pc, te, balance, permissions, url"
" FROM user"
" ORDER BY username ASC"
).fetchall()
# Pass that chapter list in to the template.
return render_template('admin/user-list.html', chapter_list=chapter_list)
def load_logged_in_user():
"""If a user id is stored in the session, load the user object from
the database into ``g.user``."""
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def index():
db = get_db()
# Retrieve all the actions in the database
# Eventually, we'll only want to display them for this year (maybe)
actions = db.execute('SELECT title, author_id, points, note, id'
' FROM action'
' ORDER BY created DESC').fetchall()
# Retreive list of all users
chapters = db.execute('SELECT * from user').fetchall()
return render_template('blog/index.html',
actions=actions,
chapters=chapters)
def activity(id):
db = get_db()
# Get the title of the activity, searching by id
activity = db.execute('SELECT title FROM action_list WHERE id = ?',
(id, )).fetchone()
# Get the chapter names from each action where the title matches the given activity
chapters = db.execute(
'SELECT author_id, created FROM action WHERE title = ?',
(activity['title'], )).fetchall()
return render_template('blog/activity.html',
activity=activity,
chapters=chapters)
def delete(id):
# Get the action with the id supplied
target_action = get_action(id)
db = get_db()
# Find the author of this specific activity
author = db.execute('SELECT author_id from action WHERE id = ?',
(id, )).fetchone()['author_id']
# Get the point value of the action, to be subtracted later
action_value = db.execute('SELECT points from action where id = ?',
(id, )).fetchone()['points']
# Get the type of the action so we can subtract points from the correct place
type = db.execute('SELECT * from action_list WHERE title = ?',
(target_action['title'], )).fetchone()['type']
# Delete it
db.execute('DELETE FROM action WHERE id = ?', (id, ))
# Change the balance for the author
db.execute('UPDATE user SET balance = balance - ? where username = ?', (
action_value,
author,
))
#If type = policy change, update that field for the user
if type == "Policy Change":
#Update the policy change poins with differential.
db.execute('UPDATE user SET pc = pc - ? WHERE username = ?', (
action_value,
author,
))
db.commit()
#If type = community building, update that field for the user
elif type == "Community Building":
#Update the community building points with differential
db.execute('UPDATE user SET cb = cb - ? WHERE username = ?', (
action_value,
author,
))
db.commit()
#If type = training and education, update that field for the user
elif type == "Training and Education":
#Update the T&E points with differential
db.execute('UPDATE user SET te = te - ? WHERE username = ?', (
action_value,
author,
))
db.commit()
db.commit()
# Return to home page
return redirect(url_for('blog.index'))
def categories():
if request.method == 'POST':
# When someone POSTs to the page, grab the info from the form
title = request.form['title']
description = request.form['description']
type = request.form['type']
db = get_db()
# Add to the database
db.execute(
'INSERT INTO action_list (title, description, type)'
' Values (?, ?, ?)',
(title, description, type)
)
db.commit()
# Redirect to the available Activities page
return redirect(url_for('blog.availableActivities'))
return render_template('admin/categories.html')
def command(cmd=None):
db = get_db()
# If we hit the reset button, we're updating all the user stats to 0
# This doesn't update the balance, because we want to roll points over
# It also doesn't actually touch the actions in the database
# What we'll have to do, eventually, is only display a certain time window of actions
# But I actually want to keep all the actions in one database completely, forever
if cmd == RESET:
db.execute('UPDATE user SET cb = 0')
db.execute('UPDATE user SET pc = 0')
db.execute('UPDATE user SET te = 0')
db.commit()
response = "Reset point counts"
# Right now, this 'else' works because we only have two options. It might need to be elif or something else
else:
# I also followed a tutorial for this, copy/pasted some code from StackOverflow
# It works great, though, so I'm not complaining, and neither should you
# Important to note, you must create a "backup" folder in the root directory
# If there is not backup folder in the root directory, you'll get a bunch of errors
# In the future, I want to set a way to route this to say, a dropbox folder or something else
# For better storage. But for now we'll do it manually
backupdir = os.getcwd() + '/backups'
dbfile = os.getcwd() + '/instance/flaskr.sqlite'
# Create a timestamped database copy
if not os.path.isdir(backupdir):
raise Exception("Backup directory does not exist: {}".format(backupdir))
backup_file = os.path.join(backupdir, os.path.basename(dbfile) +
time.strftime("-%Y%m%d-%H%M%S") + '.sqlite')
connection = sqlite3.connect(dbfile)
cursor = connection.cursor()
# Lock database before making a backup
cursor.execute('begin immediate')
# Make new backup file
shutil.copyfile(dbfile, backup_file)
print ("\nCreating {}...".format(backup_file))
# Unlock database
connection.rollback()
response = "Backed up database"
return response, 200, {'Content-Type': 'text/plain'}
def users():
# When someone POSTs to the page, grab the info from the form
if request.method == 'POST':
username = request.form['username']
email = request.form['email']
password = request.form['password']
permissions = request.form['permissions']
# makeURL is defined at the top of this file right now
# It takes a username and gives us a nicer slug format for the url
url = makeURL(username)
db = get_db()
# Add to the database, with a hashed password and values at 0
db.execute(
'INSERT INTO user (username, password, email, permissions, cb, pc, te, balance, url)'
' Values (?, ?, ?, ?, 0, 0, 0, 0, ?)',
(username, generate_password_hash(password), email, permissions, url)
)
db.commit()
# Redirect to the new chapter page
return redirect(url_for('chapters.chapter', url=url))
return render_template('admin/users.html')
def login():
"""Log in a registered user by adding the user id to the session."""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
user = db.execute('SELECT * FROM user WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
# store the user id in a new session and return to the index
session.clear()
session['user_id'] = user['id']
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def update(id):
# get the action, from get_action
post = get_action(id)
# old point value of the action
old_points = post['points']
# Author of the action
author = post['author_id']
# Get title from selected action
title = post['title']
# Get type from action_list where title == title
db = get_db()
url = db.execute('SELECT * from user WHERE username = ?',
(author, )).fetchone()['url']
type = db.execute('SELECT * from action_list WHERE title = ?',
(title, )).fetchone()['type']
if request.method == 'POST':
points = request.form['points']
note = request.form['note']
error = None
#Calculate the differential between the new point count and the old points
points_delta = int(points) - int(old_points)
if error is not None:
flash(error)
else:
db = get_db()
# Update the action
db.execute(
'UPDATE action SET points = ?, note = ?'
' WHERE id = ?', (points, note, id))
# Set the balance to the delta of the new value
db.execute(
'UPDATE user SET balance = balance + ? WHERE username = ?', (
points_delta,
author,
))
db.commit()
#If type = policy change, update that field for the user
if type == "Policy Change":
#Update the policy change poins with differential.
db.execute('UPDATE user SET pc = pc + ? WHERE username = ?', (
points_delta,
author,
))
db.commit()
#If type = community building, update that field for the user
elif type == "Community Building":
#Update the community building points with differential
db.execute('UPDATE user SET cb = cb + ? WHERE username = ?', (
points_delta,
author,
))
db.commit()
#If type = training and education, update that field for the user
elif type == "Training and Education":
#Update the T&E points with differential
db.execute('UPDATE user SET te = te + ? WHERE username = ?', (
points_delta,
author,
))
db.commit()
return redirect(url_for('chapters.chapter', url=url))
return render_template('blog/update.html', post=post)
def activities():
db = get_db()
# Retrieve the list of Movement Building activities from database
mb_activities = db.execute('SELECT title FROM action_list WHERE type LIKE "Community Building"')
# Retrieve the list of Policy Change activities from database
pc_activities = db.execute('SELECT title FROM action_list WHERE type LIKE "Policy Change"')
# Retrive the list of Training and Education activities from database
te_activities = db.execute('SELECT title FROM action_list WHERE type LIKE "Training and Education"')
# Retrieve the list of chapters from the database
chapters = db.execute('SELECT username FROM user WHERE permissions LIKE "Chapter"')
# Init an empty list for the chapter listing
chapter_list = []
# Push the usernames into the chapter list for ease of use in search bar on page
for x in chapters.fetchall():
chapter_list.append((str(x['Username'])))
if request.method == 'POST':
# If we post to the page, add the activity to the action table
activity = request.form['activity']
points = request.form['points']
note = request.form['note']
# If the user is an Administrator or Staffer, they can use the select chapter form, so we pull from it
if g.user['permissions'] == 'Admin' or g.user['permissions'] == 'Staffer':
logged_chapter = request.form['chapter']
# If the user is a chapter, they can only log points for themselves, so we default to their username
else:
logged_chapter = g.user['username']
# This returns the type of activity, by searching by title
type = db.execute(
'SELECT type FROM action_list WHERE title LIKE ?', (activity,)
).fetchone()[0]
db.execute(
'INSERT INTO action (title, points, note, author_id)'
' Values (?, ?, ?, ?)',
(activity, points, note, logged_chapter)
)
# We also add to the balance of the logged chapter
db.execute(
'UPDATE user SET balance = balance + ? Where username = ?',
(int(points), logged_chapter)
)
# Update Chapter Building, Policy Change, or Training and Education
# This is based on type, which we get above.
if type == "Policy Change":
db.execute(
'UPDATE user SET pc = pc + ? Where username = ?',
(int(points), logged_chapter)
)
db.commit()
elif type == "Community Building":
db.execute(
'UPDATE user SET cb = cb + ? Where username = ?',
(int(points), logged_chapter)
)
db.commit()
elif type == "Training and Education":
db.execute(
'UPDATE user SET te = cb + ? Where username = ?',
(int(points), logged_chapter)
)
db.commit()
db.commit()
return render_template('admin/activities.html', mb_activities=mb_activities, pc_activities=pc_activities, te_activities=te_activities, chapters=chapters, chapter_list=chapter_list)
def stats():
db = get_db()
# Sums up how many community building points are in the database.
cb = db.execute(
'select sum(action.points)'
' FROM action'
' INNER JOIN action_list ON action.title=action_list.title'
' where action_list.type="Community Building"'
).fetchone()[0]
# Sums up how many policy change points are in the database.
pc = db.execute(
'select sum(action.points)'
' FROM action'
' INNER JOIN action_list ON action.title=action_list.title'
' where action_list.type="Policy Change"'
).fetchone()[0]
# Sums up how many training and education points are in the database.
te = db.execute(
'select sum(action.points)'
' FROM action'
' INNER JOIN action_list ON action.title=action_list.title'
' where action_list.type="Training and Education"'
).fetchone()[0]
# Sums up total points in the database
total = db.execute(
' select sum(action.points)'
' FROM action'
' INNER JOIN action_list ON action.title=action_list.title'
).fetchone()[0]
# Counts how many chapters have over 75 community building points
hqcb = db.execute('select count(*) from user where cb > 75').fetchone()[0]
# Counts how many chapters have over 75 policy change points
hqpc = db.execute('select count(*) from user where pc > 75').fetchone()[0]
# Counts how many chapters have over 50 training and education points
hqte = db.execute('select count(*) from user where te > 50').fetchone()[0]
# A rockstar, or high quality chapter has the following criteria
# Either 200+ total points (cb + pc + te)
# Or they've filled up 2 out of 3 of the buckets
# The rockstars query searches for those criteria in the line below
# We can change it if we change the high quality criteria, which happens
rockstars = db.execute('select count(*) from user where((cb + pc + te) >= 200 ) or (cb >= 75 and pc >= 75) or (cb >= 75 and te >= 50) or (pc >= 75 and te >= 50)').fetchone()[0]
# We want to know how many total chapters there are.
# We may supplement this with an "active" flag or something to denote DEF
total_chapters = db.execute('select count(*) from user').fetchone()[0]
# Calculates the percentage of high quality chapters total
hqpercent = math.floor((rockstars / total_chapters ) * 100)
# How many points have been spent
spending = db.execute('select sum(points) from spending').fetchone()[0]
# In the page template, we'll be listing out all the available activities
# So this query gives us that list
activities = db.execute('SELECT title, id FROM action_list')
# This gives us the count of how many of each activity have been done
raw_activity_count = db.execute('select title, count(*) from action group by title')
# Initialize an empty dictionary
activity_count = {}
# Create a dictionary with the key/value pair of "Activity" : Count
for row in raw_activity_count.fetchall():
activity_count[row[0]] = row[1]
# Create a dictionary with the overall points stats
points = {'cb': cb, 'pc': pc, 'te': te, 'total': total, 'spending':spending}
# Create a dictionary with the high quality stats
hq = {'cb': hqcb, 'pc': hqpc, 'te': hqte, 'hq':rockstars, 'percent':hqpercent}
# Render page, pass in the dictionaries for processing
return render_template('admin/stats.html', points=points, hq=hq, activities=activities, activity_count=activity_count)