def conv_view(conv_id=None):
if not g.user:
return show_message('You need to be logged in to see this page.')
conversation = database.query_db(
"select * from conversation where conversation_id=?", [conv_id],
one=True)
# posting a draft
if request.method == 'POST':
database.replace_message(request.form["text"], conv_id,
g.user["user_id"], conversation["user2_id"])
return redirect('/conv_view/' + conv_id)
messages = database.query_db(
"select * from message where conversation_id=? order by message_timestamp desc",
[conv_id])
hide_editor = False
if len(messages) > 0:
if time.time() > messages[0]['message_timestamp'] + datetime.timedelta(
days=7).total_seconds():
database.add_message(conv_id, messages[0]['receiver_id'],
messages[0]['sender_id'],
"Put a cool message here", time.time(), 0)
if messages[0]['sender_id'] != g.user[
'user_id']: #Hide draft post if use does not own it
messages.pop(0)
hide_editor = True
return render_template('conv_view.html',
conversation=conversation,
messages=messages,
hide_editor=hide_editor)
def report(test, report=None):
if not report:
grades = query_db('select grade, report from grades where test = ? \
and user = ?',
[test, session["username"]])
else:
grades = query_db('select grade, report from grades where test = ? \
and user = ? and report = ?',
[test, session["username"], report])
if not grades:
flash("You have not yet submitted any work for this handout")
return redirect(url_for('handout', test=test))
grades = grades[-1]
report_id = grades["report"]
report = query_db('select detail from reports where id = ?',
[report_id])
tests = json.loads(report[-1]["detail"])
return render_template('grades.html',
grade=grades["grade"],
tests=tests,
test=test)
def next_question(self, gamer):
question_idx = gamer.current_question_idx + 1
if question_idx < len(self.questions):
gamer.set_next_question(self.questions[question_idx], question_idx)
return self.questions[question_idx]
placeholders = ', '.join(['?'] * len(self.used_word_ids))
next_words = database.query_db("""select * from translates where word_id not in({}) order by random() limit 1""".format(placeholders), tuple(self.used_word_ids))
if len(next_words) == 0:
return None
next_word = next_words[0]
answers = database.query_db("""select * from translates where word_id not in(?) order by random() limit 4""", (next_word["word_id"],))
# right_answer_idx = random.randint(0, len(answers))
answers = [answer["en_word"] for answer in answers]
right_answer_idx = random.randint(0, 4)
answers.insert(right_answer_idx, next_word["en_word"])
question = Question(next_word["ru_word"], answers, right_answer_idx)
self.curr_word_id = next_word["word_id"]
self.used_word_ids.append(self.curr_word_id)
self.questions.append(question)
gamer.set_next_question(question, len(self.questions))
return question
def tokenIsAlive(token):
t = (token,)
(timeStamp,) = query_db('SELECT timestamp FROM VERIFICATION WHERE token=?', t, one=True)
t = (timeStamp,)
#result is 1 if time is less than 48 hrs. else result is 0
(isExpired,) = query_db("SELECT cast((strftime('%s','now','localtime')- strftime('%s',?)) AS real)/60/60 < 48.00",
t, one=True)
return isExpired == 1
def canResetPw(email):
db = get_db()
t=(email,)
(dt,) = query_db("SELECT timestamp FROM resetPw WHERE email=?", t, one=True)
t = (dt,)
(canSend,) = query_db("SELECT cast((strftime('%s','now','localtime')- strftime('%s',?)) AS real)/60/60 > 24.00",
t, one=True)
return canSend == 1
def graph_data(pdu_id):
count_ = database.query_db("SELECT count(*) FROM device_readings WHERE device_id = ?",pdu_id)[0][0]
if count_ > 50: offset = count_ - 50
else: offset = 0
rows = database.query_db("SELECT watts FROM device_readings WHERE device_id = ? LIMIT ? OFFSET ?;",[pdu_id,50,offset])
name="Watt Consumption for PDU "+pdu_id
data= {'name': name, 'data': [watt[0] for watt in rows]}
return json.dumps(data)
def all_tasks(statu=None):
if statu is None:
rv = query_db('''select task_id from tasks order by task_id desc''')
elif statu == CLOSED_STR:
rv = query_db('''select task_id from tasks where is_closed = ? order by task_id desc''',
[CLOSED])
else:
rv = query_db('''select task_id from tasks where closed_statu == ? order by task_id
desc''', [statu])
for task_id in rv:
yield get_task(task_id[0])
def save(self):
if self._find_dashboard():
query = "UPDATE dashboards SET name = ?, settings = ? WHERE id = ?"
else:
query = "INSERT INTO dashboards (name, settings, id) " +\
"VALUES (?, ?, ?)"
database.query_db(query, [self.dashboard["name"],
json.dumps(self.dashboard),
self._id])
return True
def verify_assertion():
jsonData = request.get_json()
AuthenticatorAttestationResponse = jsonData['AuthenticatorAttestationResponse']
clientDataJSON = AuthenticatorAttestationResponse['clientDataJSON']
clientDataJSON_padding = clientDataJSON.ljust((int)(math.ceil(len(clientDataJSON) / 4)) * 4, '=')
clientDataJSON = base64.b64decode(clientDataJSON_padding).decode('utf8')
clientDataJSONparsed = json.loads(clientDataJSON)
retrievedChallenge = clientDataJSONparsed['challenge']
try:
data = database.query_db("select * from PublicKeyCredentialCreationOptions where challenge=?",[retrievedChallenge])[0]
except:
return jsonify({"Error:","Could not find challenge"}),500
#DELETE from table
database.delete_db("delete from PublicKeyCredentialCreationOptions where challenge=?",[retrievedChallenge])
signature = AuthenticatorAttestationResponse['signature']
assertion_response = {'clientData':AuthenticatorAttestationResponse['clientDataJSON'],'authData':AuthenticatorAttestationResponse['authenticatorData'],'signature':signature,'userHandle':AuthenticatorAttestationResponse['userHandle']}
credential_id = AuthenticatorAttestationResponse.get('id')
user = database.query_db("select * from Users where credential_id=?",[credential_id])[0]
if len(user)==0:
return make_response(jsonify({'fail': 'User does not exist.'}), 401)
webauthn_user = webauthn.WebAuthnUser(
user[0], user[0], user[2], user[6],
user[4], user[3], user[5], user[7])
webauthn_assertion_response = webauthn.WebAuthnAssertionResponse(
webauthn_user,
assertion_response,
retrievedChallenge,
ORIGIN,
uv_required=False) # User Verification
sign_count = webauthn_assertion_response.verify()
try:
sign_count = webauthn_assertion_response.verify()
except Exception as e:
print(e)
return make_response(jsonify({'fail': 'Assertion failed'}),500)
# Update counter.
#Update database
update = database.insert_db("update Users SET sign_count=? where username=?",[sign_count,user[1]])
identityObj={'username':user[1],'id':user[0],'displayname':user[2]}
expires = datetime.timedelta(hours=2)
print(identityObj)
jwt = create_access_token(identity=identityObj,expires_delta=expires)
return jsonify({
'success':
'Successfully authenticated as {}'.format(user[1]),
'jwt':jwt,
'username': user[1]
})
def all_tasks(statu=None):
if statu is None:
rv = query_db('''select task_id from tasks order by task_id desc''')
elif statu == CLOSED_STR:
rv = query_db(
'''select task_id from tasks where is_closed = ? order by task_id desc''',
[CLOSED])
else:
rv = query_db(
'''select task_id from tasks where closed_statu == ? order by task_id
desc''', [statu])
for task_id in rv:
yield get_task(task_id[0])
def admin():
conf = GraderConfiguration()
modules = app.config["MODULES"]
if request.method == 'POST':
if "_registration" in request.form:
conf["registration"] = "open"
else:
conf["registration"] = "closed"
active_modules = []
for m in modules:
if m in request.form:
active_modules.append(m)
conf["active_modules"] = ",".join(active_modules)
flash("Active modules updated")
return redirect(url_for('admin'))
else:
registration_active = conf["registration"] == "open"
active_modules = get_active_modules()
module_active = dict([(m, m in active_modules) for m in modules])
all_grades_raw = query_db('select * from (select test, user, \
timestamp, grade, upload from grades \
order by grade ASC, timestamp DESC) \
as tmp group by test, user')
groups = []
emails = []
for u in query_db('select user, emails from users order by user'):
if u != app.config["ADMIN_USERNAME"]:
groups.append(u["user"])
emails.append(u["emails"])
all_grades = dict([(g, {}) for g in groups])
for g in all_grades_raw:
if g["user"] not in groups:
continue
all_grades[g["user"]][g["test"]] = dict(grade=g["grade"],
timestamp=g["timestamp"],
upload=g["upload"])
return render_template('admin.html',
all_grades=all_grades,
groups_emails=zip(groups, emails),
modules=modules,
module_active=module_active,
registration_active=registration_active,
user=session["username"])
def have_user_id(user_id):
rv = query_db('''select user_id from users''')
for ui in rv:
# print("in db:", ui[0])
if user_id == ui[0]:
return True
return False
def get_menus_of_restaurant(restaurant_id):
return db.query_db("""
SELECT menu.name, menu.description
FROM restaurant_menus
INNER JOIN menu on menu.id = restaurant_menus.menu_id
INNER JOIN restaurant on restauraunt.id = restaurant_menus.restaurant_id
""")
def insertResetAttempt(email):
db = get_db()
(dt,) = query_db("SELECT datetime('now','localtime')", one=True)
t = (email, dt)
db.execute("INSERT INTO resetPw (email, timestamp) VALUES (?,?)", t)
db.commit()
def refreshVerification(email):
db = get_db()
dt = query_db("SELECT datetime('now','localtime')",one=True)
t = (dt[0],email)
db.execute("UPDATE VERIFICATION SET timestamp=? WHERE email=(?)",t)
updateVerificationCount(email,1)
g.db.commit()
def addVerification( email, token):
db = get_db()
dt = query_db("SELECT datetime('now','localtime')",one=True)
t = (token,dt[0],email)
db.execute("INSERT INTO VERIFICATION values(?,?,'0',?)",t)
updateVerificationCount(email,1)
g.db.commit()
def emailExists(email):
t = (email,)
result = query_db('SELECT COUNT(email) FROM USERS WHERE email=(?)', t, one=True)
count = result[0]
if count > 0:
return True
return False
def verificationFromToday(email):
g.db = get_db()
t = (email,)
result = query_db("SELECT count(strftime('%Y-%m-%d',timestamp,'localtime')) from verification WHERE strftime(" + \
"'%Y-%m-%d',timestamp,'localtime')=strftime('%Y-%m-%d','now','localtime') and email=?",t,one=True)
count = result[0]
return count == 1
def conv_add_page():
if not g.user:
return show_message('You need to be logged in to see this page.')
if request.method == 'POST':
if not request.form['title']:
return render_template('conv_add.html',
error="Please name your conversation")
elif not request.form['recipient']:
return render_template(
'conv_add.html',
error="You need someone else in this conversation")
id = database.query_db("select user_id from user where username=?",
[request.form['recipient']],
one=True)
if not id:
return render_template('conv_add.html',
error="Recipient username does not exist")
else:
id = id[0]
database.add_conversation(g.user['user_id'], id, request.form['title'],
time.time())
return redirect(url_for("conv_list_page"))
return render_template('conv_add.html')
def update_menu_item(menu_item_id, n, c):
return db.query_db("""
UPDATE menu_item
SET name = %s, cost = %s
WHERE id = %s
""",
args=(n, c, menu_item_id))
def participate_closed_tasks(self):
rv = query_db(
'''select task_id from tasks where helper = ? and is_closed = ?
order by task_id desc''', [self.user_id, CLOSED])
for task_id in rv:
print(task_id[0])
yield get_task(task_id[0])
def webauthn_begin_assertion():
jsonData = request.get_json()
username = jsonData['username']
if not util.validate_username(username):
return make_response(jsonify({'fail': 'Invalid username.'}), 401)
user = database.query_db("select * from Users where username=?",[username])[0]
if len(user) == 0:
return make_response(jsonify({'fail': 'User does not exist.'}), 401)
if not user[4]:
return make_response(jsonify({'fail': 'Unknown credential ID.'}), 401)
challenge = util.generate_challenge(32)
# We strip the padding from the challenge stored in the session
# for the reasons outlined in the comment in webauthn_begin_activate.
toStoreChallenge = challenge.rstrip('=')
try:
insert = database.insert_db("insert into PublicKeyCredentialCreationOptions VALUES (?,?,?,?,?,?)",[None, None,user[0],None,username,toStoreChallenge])
except:
update = database.insert_db("update PublicKeyCredentialCreationOptions SET challenge=? where user_username=?",[toStoreChallenge,username])
webauthn_user = webauthn.WebAuthnUser(
user[0], user[1], user[2], user[6],
user[4], user[3], user[5], user[7])
webauthn_assertion_options = webauthn.WebAuthnAssertionOptions(
webauthn_user, challenge)
return jsonify(webauthn_assertion_options.assertion_dict)
def have_user_id(user_id):
rv = query_db('''select user_id from users''')
for ui in rv:
# print("in db:", ui[0])
if user_id == ui[0]:
return True
return False
def getTable():
"""Get all the information stored."""
table = {}
ts = db.query_db("select * from experience", one=False)
emotions = [
'anger', 'contempt', 'disgust', 'fear', 'happiness', 'neutral',
'sadness', 'surprise'
]
for t in ts:
id = t[0]
if id not in table:
table[id] = {
"count": 0,
"name": t[3],
"anger": 0,
"contempt": 0,
"disgust": 0,
"fear": 0,
"happiness": 0,
"neutral": 0,
"sadness": 0,
"surprise": 0
}
table[id]['count'] += 1
c = table[id]['count']
for i, emotion in enumerate(emotions):
table[id][emotion] = ((table[id][emotion] *
(c - 1)) / c) + (t[i + 4] / c)
return jsonify(table)
def get_items_on_menu(menu_id):
return db.query_db("""
SELECT menu_items.name, menu_items.cost
FROM items_on_menu
INNER JOIN menu on menu.id = items_on_menu.menu_id
INNER JOIN menu_item on menu_item.id = items_on_menu.menu_item_id
""")
def update_menu(menu_id, n, d):
return db.query_db("""
UPDATE menu
SET name = %s, description = %s
WHERE id = %s
""",
args=(n, d, menu_id))
def load_db(self, value=None, item='tag_id'):
if value is None:
value = self.tag_id
rv = query_db('select * from tags where %s = ?' % (item), [value],
one=True)
[self.tag_id, self.tagname, self.creater, self.init_time] = rv
def webauthn_end_activate():
jsonData = request.get_json()
AuthenticatorAttestationResponse = jsonData['AuthenticatorAttestationResponse']
clientDataJSON = AuthenticatorAttestationResponse['clientDataJSON']
clientDataJSON_padding = clientDataJSON.ljust((int)(math.ceil(len(clientDataJSON) / 4)) * 4, '=')
clientDataJSON = base64.b64decode(clientDataJSON_padding).decode('utf8')
clientDataJSONparsed = json.loads(clientDataJSON)
retrievedChallenge = clientDataJSONparsed['challenge']
try:
data = database.query_db("select * from PublicKeyCredentialCreationOptions where challenge=?",[retrievedChallenge])[0]
except:
return jsonify({"Error:","Could not find challenge"}),500
trusted_attestation_cert_required = True
self_attestation_permitted = True
none_attestation_permitted = True
registration_response = {'clientData':AuthenticatorAttestationResponse['clientDataJSON'],'attObj':AuthenticatorAttestationResponse['attestationObject']}
trust_anchor_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), TRUST_ANCHOR_DIR)
webauthn_registration_response = webauthn.WebAuthnRegistrationResponse(
RP_ID,
ORIGIN,
registration_response,
data[5],
trust_anchor_dir,
trusted_attestation_cert_required,
self_attestation_permitted,
none_attestation_permitted,
uv_required=False) # User Verification
try:
webauthn_credential = webauthn_registration_response.verify()
except (RuntimeError, TypeError, NameError):
print(RuntimeError)
return jsonify({'fail': 'Registration failed. Error: {}'.format(RuntimeError)})
credential_id=webauthn_credential.credential_id.decode("utf-8")
duplicatedId = database.query_db("select credential_id from Users where credential_id=?",[credential_id])
if len(duplicatedId)!=0:
return jsonify({"Error":"Error with register, try again"}),500
existing_user = database.query_db("select user_id from Users where username=?",[data[4]])
if len(existing_user)!=0:
return jsonify({"Error":"Error with register, try again"}),500
#Add user to database
database.insert_db("insert into Users VALUES (?,?,?,?,?,?,?,?)",[data[2],data[4],data[3],webauthn_credential.public_key,credential_id,webauthn_credential.sign_count,'http://localhost',data[1]])
#Remove from PublicKeyCredentialCreationOptions
database.delete_db("delete from PublicKeyCredentialCreationOptions where challenge=?",[retrievedChallenge])
return jsonify({'success': 'User successfully registered.'})
def getEmailFromToken(token):
t = (token,)
try:
(email,) = query_db("SELECT email FROM VERIFICATION WHERE token=?",t,one=True)
except TypeError:
return None
return email
def get_notifications(db, _school_id, _days=DEFAULT_DAYS):
if int(_school_id) < 0:
raise ValueError("school id must be at least 0!")
if _days < 0:
return ValueError("days must be at least 0!")
_today = date.today()
return database.query_db(db, sql_commands.SQL_NOTIFICATION_READ_STD, [_school_id, _today, _today + timedelta(days=_days)])
def get_schedule(db, _school_id, _days=DEFAULT_DAYS, _class=DEFAULT_CLASS):
if int(_school_id) < 0:
raise ValueError("school id must be at least 0!")
if _days < 0:
return ValueError("days must be at least 0!")
_today = date.today()
return database.query_db(db, sql_commands.SQL_SCHEDULE_READ_STD, [_school_id, _today, _today + timedelta(days=_days), _class])
def search_flight(airport):
query = '''SELECT f.origin, f.destination, f.dept_time, f.duration,
addtime(f.dept_time, f.duration) as arrival_time, plane.type as equipment
FROM flights.aircrafts plane
JOIN flights.airports a ON plane.location_id = a.id
JOIN flights.flight_plan f ON a.code = f.origin
WHERE a.code = %s;'''
return db.query_db(query, (airport,))
def conv_list_page():
if not g.user:
return show_message('You need to be logged in to see this page.')
items = database.query_db(
"select * from conversation where user1_id=? or user2_id=?",
[g.user['user_id'], g.user['user_id']])
return render_template('conv_list.html', conversations=items)
def load_db(self):
rv = query_db('''select * from events where event_id = ?''',
[self.event_id],
one=True)
[
self.event_id, self.user_id, self.task_id, self.act,
self.init_date, self.statu
] = rv
def load_db(self):
rv = query_db('''select * from notices where notice_id = ? ''',
[self.notice_id],
one=True)
[
self.notice_id, self.user_id, self.created_date, self.title,
self.info
] = rv
def recents(time_scale):
messages = []
t = int(time.time())-time_scale
rv = query_db('''select task_id from events where init_date > ?''', [t])
for task_id in list(set([id[0] for id in rv])):
if get_task(task_id).is_closed == OPEN:
messages.append(task_info(task_id))
return {"recents": messages}
def urgents(time_scale):
messages = []
t = int(time.time())+time_scale
rv = query_db('''select task_id from tasks where closed_statu = ? and end_date < ?
order by end_date''', [ALREADY, t])
for task_id in rv:
messages.append(task_info(task_id[0]))
return {"urgents": messages}
def plot():
depth_min = request.form['depth_min']
grad_min = request.form['grad_min']
data = query_db(depth_min, grad_min)
chart_json = plot_wells(data)
return render_template('plot.html', chart=chart_json)
def participate_tasks(self, statu):
''' get tasks user participate '''
rv = query_db(
'''select task_id from tasks where helper = ? and closed_statu = ?
and is_closed = ? order by task_id desc''',
[self.user_id, statu, OPEN])
for task_id in rv:
yield get_task(task_id[0])
def load_db(self, value=None, item='tag_id'):
if value is None:
value = self.tag_id
rv = query_db('select * from tags where %s = ?' % (item),
[value], one=True)
[self.tag_id, self.tagname, self.creater, self.init_time] = rv
def recents(time_scale):
messages = []
t = int(time.time()) - time_scale
rv = query_db('''select task_id from events where init_date > ?''', [t])
for task_id in list(set([id[0] for id in rv])):
if get_task(task_id).is_closed == OPEN:
messages.append(task_info(task_id))
return {"recents": messages}
def check_events(self, task):
print(task)
rv = query_db('''select event_id from events where user_id = ? and task_id = ?
and statu == ?''', [self.user_id, task.task_id, RAISED])
for eid in rv:
event = Event(eid[0])
event.check()
self.raised_events -= 1
self.update_db()
def create_db(self, user_id, task_id, act):
db = get_db()
init_date = int(time.time())
db.execute('''insert into events (user_id, task_id, act, init_date, statu)
values (?, ?, ?, ?, ?)''', [user_id, task_id, act, init_date, RAISED])
db.commit()
rv = query_db('''select event_id from events where user_id = ? and task_id = ?
and init_date = ?''', [user_id, task_id, init_date], one=True)
return rv[0]
def load_db(self, value, item='user_id'):
''' load user from users '''
rv = query_db('select * from users where %s = ?' % (item), [value],
one=True)
[
self.user_id, self.username, self.user_type, self.phone_no,
self.pw_hash, self.address, self.latest, self.raised_events,
self.raised_notices
] = rv
def create_db(self, user_id, title, info):
db = get_db()
created_time = int(time.time())
db.execute('''insert into notices (user_id, created_date, title, info) values
(?, ?, ?, ?)''', [user_id, created_time, title, info])
db.commit()
rv = query_db('''select notice_id from notices where user_id = ? and created_date = ?''',
[user_id, created_time], one=True)
return rv[0]
def urgents(time_scale):
messages = []
t = int(time.time()) + time_scale
rv = query_db(
'''select task_id from tasks where closed_statu = ? and end_date < ?
order by end_date''', [ALREADY, t])
for task_id in rv:
messages.append(task_info(task_id[0]))
return {"urgents": messages}
def getTableJson():
"""Return a json containing all the rows of the table (database)."""
table = {}
ts = db.query_db("select * from experience", one=False)
i = 0
for t in ts:
id = t[0]
table[i] = {"id": t[0], "person": t[2], "time": t[2], "name": t[3], "anger": t[4], "contempt": t[5], "disgust": t[6], "fear": t[7], "happiness": t[8], "neutral": t[9], "sadness": t[10], "surprise": t[11]}
i = i+1
return jsonify(table)
def before_request():
"""Make sure we are the connected to the database each request and look
up the current user to make sure they are connected
"""
g.db = database.connect_db()
g.user = None
if 'uid' in session:
g.user = database.query_db('select * from users where uid = ?',
[session['uid']], one=True)
g.whatapi = msgpackrpc.Client(msgpackrpc.Address("localhost", 18800), timeout=20)
def load_db(self):
''' load items from tasks '''
rv = query_db('''select * from tasks where task_id = ?''',
[self.task_id],
one=True)
[
_, self.poster_id, self.helper_id, self.create_date, self.title,
self.content, self.public_date, self.end_date, self.closed_statu,
self.is_closed, self.poster_score, self.helper_score, self.hits
] = rv
def checkIfUserExists(username):
exists = False
db = get_db()
userInfo = query_db('SELECT * FROM users WHERE username = ?', (username, ),
one=True)
if not userInfo is None:
exists = True
return exists
def populars():
messages = []
t = int(time.time())
rv = query_db('''select task_id from tasks where is_closed = ? ''', [OPEN])
tasks = [get_task(r[0]) for r in rv]
def rank_key(x):
return x.hit_score(t)
tasks.sort(key=rank_key, reverse=True)
for task in tasks:
messages.append(task_info(task.task_id))
return {'populars':messages}
def check_events(self, task):
print(task)
rv = query_db(
'''select event_id from events where user_id = ? and task_id = ?
and statu == ?''',
[self.user_id, task.task_id, RAISED])
for eid in rv:
event = Event(eid[0])
event.check()
self.raised_events -= 1
self.update_db()
def register():
if not request.is_json:
return gen_response(error="Body should contains JSON", code=400)
if "user_name" not in request.json:
return gen_response(error="Invalid request parameters", code=400)
user_id = database.query_db("""insert into users(user_name) values(?)""",
(request.json["user_name"], ),
ret_lastrowid=True)
return gen_response({"user_id": user_id})
def create_db(self, post_user):
''' create a new task and return task_id '''
db = get_db()
user_id = post_user.user_id
created_time = int(time.time())
db.execute('''insert into tasks (poster, create_date) values (?, ?)''',
[user_id, created_time])
db.commit()
rv = query_db('''select task_id from tasks where poster = ? and create_date= ?''',
[user_id, created_time], one=True)
return rv[0] if rv else None
def create_db(self, user_id, title, info):
db = get_db()
created_time = int(time.time())
db.execute(
'''insert into notices (user_id, created_date, title, info) values
(?, ?, ?, ?)''', [user_id, created_time, title, info])
db.commit()
rv = query_db(
'''select notice_id from notices where user_id = ? and created_date = ?''',
[user_id, created_time],
one=True)
return rv[0]
def sendChampNotifEmail(apiIds):
logging.basicConfig(filename='freeChampEvents.log',format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p',
level=logging.INFO)
subject = "Free Champion Notification"
g.db = get_db()
#reset free bool
g.db.execute("UPDATE CHAMPS SET free = 0")
#for each champ id in the api call, show that champ as free in the db
for champId in apiIds:
g.db.execute("UPDATE Champs SET free = 1 WHERE id = (?)", (champId,))
g.db.commit()
#get a list of users that have selected champs they want to be notified when they are free
emails = [email[0] for email in query_db("SELECT Distinct Notify.Email FROM Notify JOIN Champs ON Champs.Champ = Notify.Champ WHERE Champs.Free = 1")]
emailNum = len(emails)
logging.info("sending " + str(emailNum) + " notification email(s)")
print("updating free champ rotation. \n" + str(len(emails)) + " emails in this update")
for email in emails:
freeChampsSelectedByUser = [champ[0] for champ in query_db("""
SELECT champs.champ
FROM CHAMPS
JOIN notify ON champs.champ = notify.champ
where notify.email=(?) and champs.free = 1 order by champs.champ""", (email,))]
msg = "Hello from Free Champ! You wished to be notified when the below champs are free: \n"
for champ in freeChampsSelectedByUser:
msg += champ + '\n'
token = getToken(email)
#msg += "\n\n <a href=http://" + app.config['HOST'] + ":" + str(app.config['PORT']) + "/optOut>opt-out?token=" + token + "</a>"
msg += getOptOutMessage(token)
htmlMsg = """
<html>
<header></header>
<body>
<p>Hello from Free Champ! You wished to be notified when the below champs are free:<br/><br/>"""
for champ in freeChampsSelectedByUser:
htmlMsg += champ + "<br/>"
htmlMsg += getOptOutMessage(token, isHTML=True)
htmlMsg += "</body></html>"
sendEmail(email, subject, msg, htmlMsg)
def conv_view(conv_id=None):
if not g.user: return show_message('You need to be logged in to see this page.')
conversation = database.query_db("select * from conversation where conversation_id=?",
[conv_id], one=True)
# posting a draft
if request.method == 'POST':
database.replace_message(request.form["text"],conv_id,g.user["user_id"],conversation["user2_id"])
return redirect('/conv_view/'+conv_id)
messages = database.query_db("select * from message where conversation_id=? order by message_timestamp desc",
[conv_id])
hide_editor=False
if len(messages)>0:
if time.time() > messages[0]['message_timestamp'] + datetime.timedelta(days=7).total_seconds():
database.add_message(conv_id,messages[0]['receiver_id'],messages[0]['sender_id'],"Put a cool message here",time.time(),0)
if messages[0]['sender_id'] != g.user['user_id']:#Hide draft post if use does not own it
messages.pop(0)
hide_editor = True
return render_template('conv_view.html', conversation=conversation, messages=messages, hide_editor=hide_editor)
def index():
active = app.config["MODULES"]
grades_raw = query_db('select test,grade from grades where user = ?',
[session["username"]])
grades = []
for module in active:
best_grade = max([gr["grade"] for gr in grades_raw
if gr["test"] == module] + [0])
grades.append(dict(test=module, grade=best_grade))
return render_template('dashboard.html',
user=session["username"],
grades=grades)
def login():
"""Logs the user in."""
if g.user:
return redirect(url_for('home'))
if request.method == 'POST':
user = database.query_db('''select * from users where
email = ?''', [request.form['email'].lower()], one=True)
if user is None:
flash('Invalid email')
elif not check_password_hash(user['pw_hash'],
request.form['password']):
flash('Invalid password')
else:
session['uid'] = user['uid']
return redirect(url_for('home'))
return render_template('login.html')
def login():
error = None
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
result = query_db("select password from users where user == ?",
args=[username], one=True)
if result and check_password_hash(result["password"], password):
session['username'] = request.form['username']
flash('You were successfully logged in')
return redirect(url_for('index'))
else:
error = "Invalid Credentials"
return render_template('login.html', error=error)