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)