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 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 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 getEmailFromToken(token):
t = (token,)
try:
(email,) = query_db("SELECT email FROM VERIFICATION WHERE token=?",t,one=True)
except TypeError:
return None
return email
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 getToken(email):
t = (email,)
(token,) = query_db('SELECT token FROM verification WHERE email=?', t, one=True)
return token
def tokenExists(token):
t = (token,)
(exists,) = query_db("SELECT COUNT(token) FROM VERIFICATION WHERE token=?", t, one=True)
return exists == 1
def getSalt(email):
t = (email,)
(salt,) = query_db('SELECT salt FROM USERS WHERE email=?', t, one=True)
return salt
def getCount(email):
t = (email, )
(count, ) = query_db('SELECT count FROM verification WHERE email=?',
t,
one=True)
return count
def sendChampNotifEmail(rotation):
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
#determine if the email is for the new player rotation or the standard rotation
if (rotation == ChampRotation.NewPlayers):
emails = [
email[0] for email in query_db(
"SELECT Distinct Notify.Email FROM Notify JOIN Champs ON Champs.Champ = Notify.Champ WHERE Champs.freeNew = 1"
)
]
else:
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:
if (rotation == ChampRotation.NewPlayers):
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.freeNew = 1 order by champs.champ""",
(email, ))
]
else:
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)