def postFortuneDelete(self, fortuneGuid):
appliedFortunes = db.query('SELECT * FROM applied_fortune WHERE fortuneGuid = %s', fortuneGuid)
if len(appliedFortunes) is not 0:
return 'fortune can not be deleted'
db.query('DELETE FROM fortune WHERE guid = %s', fortuneGuid)
return 'Fortune deleted'
def student_signin(service, access_token=None, access_token_secret=None, **kwarg):
if service == 'twitter':
response = twitter.get(
'https://api.twitter.com/1/account/verify_credentials.json',
access_token=access_token,
access_token_secret=access_token_secret
)
data = response.content
handle = data['screen_name']
serviceId = data['id_str']
name = data['name']
email = ''
else:
serviceId = kwarg['values']['facebook_id']
handle = None
access_token = kwarg['values']['access_token']
name = kwarg['values']['name']
email = kwarg['values']['email']
# Determine if this user already exists in our database
rows = db.query('SELECT * FROM student_client WHERE service = %s AND serviceId = %s', (service, serviceId))
if len(rows) is 0:
# New student, create account
guid = str(uuid())
db.query(
'INSERT INTO student_client (guid, name, email, service, handle, serviceId, oauthToken, oauthSecret) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
(guid, name, email, service, handle, serviceId, access_token, access_token_secret)
)
def postPsychicState(self, psychicGuid, values):
db.query(
'UPDATE psychic SET active = %s WHERE guid = %s',
(values['isActive'], psychicGuid)
)
return 'psychic updated'
def postPsychicProfile(self, psychicGuid, values):
db.query(
'UPDATE psychic SET givenName = %s, fullName = %s, nickName = %s, surName = %s WHERE guid = %s',
(values['givenName'], values['fullName'], values['nickName'], values['surName'], psychicGuid)
)
return 'psychic updated'
def postFortuneStatus(self, fortuneGuid, values):
# Validate that the required fields are present
if 'action' not in values or (values['action'] != 'activate' and values['action'] != 'deactivate'):
return 'Missing or invalid action'
# Update the value on the fortune
newValue = 1 if values['action'] == 'activate' else 0
db.query('UPDATE fortune SET active = %s WHERE guid = %s', (newValue, fortuneGuid))
return 'Fortune status updated'
def postFortuneNetwork(self, fortuneGuid, values):
if 'network' not in values:
return 'Missing network'
if values['network'] != 'twitter' and values['network'] != 'facebook':
return 'Invalid network'
db.query('UPDATE fortune SET service=%s WHERE guid = %s', (values['network'], fortuneGuid))
return 'Network Updated'
def post(self, code):
values = request.json
# Validate that the required fields are present
if 'rating' not in values:
return 'Missing rating'
# Update the value on the fortune
db.query('UPDATE applied_fortune SET liked = %s WHERE shortId = %s', (values['rating'], code))
return 'Applied Fortune updated'
def postFortuneMessage(self, fortuneGuid, values):
# Validate that the required fields are present
if 'message' not in values:
return 'Missing message'
appliedFortunes = db.query('SELECT * FROM applied_fortune WHERE fortuneGuid = %s', fortuneGuid)
if len(appliedFortunes) is not 0:
return 'fortune can not be updated'
# Update the value on the fortune
db.query('UPDATE fortune SET message = %s WHERE guid = %s', (values['message'], fortuneGuid))
return 'Fortune message updated'
def studentRatingPage(shortcode):
rows = db.query(
'SELECT fortune.message, fortune.service, psychic.fullName, applied_fortune.shortId, applied_fortune.studentGuid FROM applied_fortune JOIN fortune ON fortune.guid = applied_fortune.fortuneGuid JOIN psychic ON fortune.psychicGuid = psychic.guid WHERE shortId = %s',
shortcode
)
student = db.query('SELECT email FROM student_client JOIN applied_fortune ON applied_fortune.studentGuid = student_client.guid WHERE applied_fortune.shortId = %s', shortcode)
if len(rows) is 1:
return render_template('rating.html', fortune=rows[0], email=student[0]['email'])
else:
abort(404)
def getFortuneStats(fortuneGuid):
dataToReturn = {}
data = db.query(
'SELECT name, appliedTime, student_client.service, liked, fortune.active AS \'fortuneActive\', fortune.createdTime AS \'fortuneCreatedTime\' FROM applied_fortune JOIN student_client on student_client.guid = applied_fortune.studentGuid JOIN fortune on applied_fortune.fortuneGuid = fortune.guid WHERE fortuneGuid = %s ORDER BY appliedTime',
fortuneGuid
)
dataToReturn['numberOfApplications'] = len(data)
rating = {
'liked': 0,
'disliked': 0,
'unrated': 0
}
applicationTimes = []
for row in data:
if row['liked'] == 1:
rating['liked'] += 1
elif row['liked'] == -1:
rating['disliked'] += 1
else:
rating['unrated'] += 1
applicationTimes.append(row['appliedTime'])
dataToReturn['rating'] = rating
dataToReturn['status'] = data[0]['fortuneActive']
dataToReturn['timeBeforeApplied'] = (applicationTimes[0] - data[0]['fortuneCreatedTime']).total_seconds()*1000
dataToReturn['timeSinceMostRecentApplication'] = (datetime.datetime.now() - applicationTimes[len(applicationTimes) - 1]).total_seconds()*1000
if len(applicationTimes) > 1:
timeBetweenApplications = []
lastApplication = None
for time in applicationTimes:
if lastApplication is None:
lastApplication = time
else:
timeBetweenApplications.append((time - lastApplication).total_seconds())
dataToReturn['averageTimeBetweenApplications'] = sum(timeBetweenApplications) / len(timeBetweenApplications)
else:
dataToReturn['averageTimeBetweenApplications'] = 0
numStudentsQuery = db.query('SELECT COUNT(*) AS count FROM student_client', ())
numberOfStudents = numStudentsQuery[0]['count']
dataToReturn['percentageAppliedTo'] = dataToReturn['numberOfApplications'] / (numberOfStudents * 1.0)
return dataToReturn
def validate_token(request):
token = request.args.get('token')
if token is None:
raise InvalidToken
rows = db.query('SELECT psychicGuid, createdTime FROM access_token WHERE guid = %s AND expired = 0', token)
if len(rows) is 0:
raise InvalidToken
now = datetime.datetime.now()
if rows[0]['createdTime'] + datetime.timedelta(hours=10) > now:
return rows[0]['psychicGuid']
else:
db.query('UPDATE access_token SET expired = 1 WHERE guid = %s', token)
raise InvalidToken
def getFortuneStatus(self, fortuneGuid):
rows = db.query('SELECT active from fortune WHERE guid = %s', fortuneGuid)
if len(rows) == 1:
return json.dumps(rows[0])
else:
raise InvalidFortune
def get(self, code):
rows = db.query('SELECT liked FROM applied_fortune WHERE shortId = %s', code)
if len(rows) == 1:
return json.dumps(rows[0])
else:
return 'Invalid code'
def getPsychic(self, psychicGuid):
rows = db.query(
'SELECT givenName, surName, fullName, nickName, email, active, twitterUsername FROM psychic WHERE guid = %s',
psychicGuid
)
return json.dumps(rows[0])
def dashboard(psychicGuid):
rows = db.query('SELECT * FROM psychic WHERE guid = %s', psychicGuid)
if len(rows) is 1:
for i in rows[0]:
if rows[0][i] is None:
rows[0][i] = ''
return render_template('dashboard.html', psychic=rows[0])
else:
return 'Does not exist'
def validate_token_fortune_ownership(request, fortuneGuid):
token = request.args.get('token')
if token is None:
raise InvalidToken
rows = db.query(
'SELECT message FROM fortune LEFT JOIN access_token ON access_token.psychicGuid = fortune.psychicGuid WHERE fortune.guid = %s AND access_token.guid = %s',
(fortuneGuid, token)
)
return False if len(rows) is 0 else True
def postFortune():
# Always validate the token first
psychicGuid = validate_token(request)
values = request.json
print(values)
# Validate that all the required fields are present
if 'message' not in values or values['message'] is None:
return 'Missing message'
if 'service' not in values or values['service'] is None:
return 'Missing service'
guid = str(uuid())
db.query(
'INSERT INTO fortune (guid, message, psychicGuid, service) VALUES (%s, %s, %s, %s)',
(guid, values['message'], psychicGuid, values['service'])
)
return 'Fortune created'
def getFortune(self, fortuneGuid):
data = db.query('SELECT * FROM fortune WHERE guid = %s', fortuneGuid)
# Dates must be serialized before returning
if len(data) == 1:
row = data[0]
row['createdTime'] = row['createdTime'].strftime('%Y-%m-%d %H:%M:%S')
if row['expires'] is not None:
row['expires'] = row['expires'].strftime('%Y-%m-%d %H:%M:%S')
return json.dumps(row)
else:
raise InvalidFortune
def p_signin_twitter(access_token, access_token_secret):
# Get some general information about this user
response = twitter.get(
'https://api.twitter.com/1/account/verify_credentials.json',
access_token=access_token,
access_token_secret=access_token_secret
)
data = response.content
name = data['name']
twitter_id = data['id_str']
twitter_username = data['screen_name']
guid = ''
# Determine if this user already exists in our database
rows = db.query('SELECT * FROM psychic WHERE twitterId = %s', twitter_id)
if len(rows) is 0:
# New user, create account
guid = str(uuid())
db.query(
'INSERT INTO psychic (guid, fullName, twitterUsername, twitterId, twitterAccessToken, twitterAccessTokenSecret) VALUES (%s, %s, %s, %s, %s, %s)',
(guid, name, twitter_username, twitter_id, access_token, access_token_secret)
)
else:
# Existing user, set the guid
guid = rows[0]['guid']
# Set info so we know who is signed in
session['access_token'] = access_token
session['access_token_secret'] = access_token_secret
session['psychic_guid'] = guid
# Issue the user a token
token = str(uuid())
db.query('INSERT INTO access_token (guid, psychicGuid) VALUES (%s, %s)', (token, guid))
return token, guid
def getPsychicAllFortunes(self, psychicGuid):
# Select the rating at the same time as the fortunes.
rows = db.query(
'SELECT fortune.guid, fortune.message, fortune.createdTime, fortune.psychicGuid, fortune.active, fortune.service, fortune.expires, SUM(applied_fortune.liked) as rating FROM fortune left join applied_fortune ON applied_fortune.fortuneGuid = fortune.guid WHERE psychicGuid = %s GROUP BY fortune.guid ORDER BY createdTime DESC;',
psychicGuid
)
# Dates must be serialized before returning
for row in rows:
row['createdTime'] = row['createdTime'].strftime('%Y-%m-%d %H:%M:%S')
if row['rating'] is not None:
row['rating'] = int(row['rating'])
if row['expires'] is not None:
row['expires'] = row['expires'].strftime('%Y-%m-%d %H:%M:%S')
return json.dumps(rows)
def siteStates_psychicStats(psychicGuid):
rows = db.query(
'SELECT fortune.guid, appliedTime, active FROM fortune LEFT JOIN applied_fortune ON fortune.guid = applied_fortune.fortuneGuid WHERE fortune.psychicGuid = %s GROUP BY fortune.guid',
psychicGuid
)
numberApplied = 0
numberNotApplied = 0
isActive = False
for row in rows:
if row['appliedTime'] is not None:
numberApplied += 1
else:
numberNotApplied += 1
if row['active']:
isActive = True
return json.dumps({"numberApplied": numberApplied, "numberNotApplied": numberNotApplied, "isActive": isActive})
def getPsychicRating(self, psychicGuid):
rows = db.query(
'SELECT liked as likes FROM applied_fortune JOIN fortune ON fortune.guid = applied_fortune.fortuneGuid WHERE fortune.psychicGuid = %s',
psychicGuid
)
# Calculate rating
ratings = {
'likes': 0,
'dislikes': 0,
'unrated': 0
}
for row in rows:
if row['likes'] == 1:
ratings['likes'] += 1
elif row['likes'] == -1:
ratings['dislikes'] += 1
else:
ratings['unrated'] += 1
return json.dumps(ratings)
def studentUnsubscribe(guid):
db.query('UPDATE student_client SET active = 0 WHERE guid = %s', guid)
return render_template('home.html')
def getFortuneAppliedTo(self, fortuneGuid):
data = db.query(
'SELECT studentGuid, serviceHandle, liked FROM applied_fortune JOIN student_client on student_client.guid = applied_fortune.studentGuid WHERE fortuneGuid = %s',
fortuneGuid
)
return json.dumps(data)
def siteStats_psychicList():
# TODO: Should retrieve the score as well
rows = db.query('SELECT surName, twitterUsername, guid FROM psychic')
return json.dumps(rows)