def getBingoFromCardbox (userid, cardbox=0):
"""
Returns a list of alphagrams that are due, length 7 or greater
"""
with lite.connect(getDBFile(userid)) as con:
cur = con.cursor()
cur.execute("select question from questions where cardbox is not null and length(question) >= 7 and difficulty in (-1,0,2) order by case cardbox when ? then -2 else difficulty end, cardbox, next_scheduled limit 1", (cardbox,))
result = cur.fetchone()
if result is not None:
return result[0]
cur.execute("select question from next_added where length(question) >= 7 order by timeStamp limit 1")
result = cur.fetchone()
if result is not None:
addWord(result[0], cur)
return result[0]
# need to get from study order
studyOrderIndex = getPrefs("studyOrderIndex", userid)
cur.execute("select question from questions where cardbox is not null and length(question) >= 7")
allCardboxBingos = [ x[0] for x in cur.fetchall() ]
with xs.getMysqlCon() as mysqlcon:
mysqlcon.execute("select alphagram from studyOrder where length(alphagram) >= 7 and studyOrderIndex > %s order by studyOrderIndex", studyOrderIndex)
for row in mysqlcon.fetchall():
if row[0] not in allCardboxBingos:
addWord(row[0], cur)
return row[0]
return None
def setPrefs2(prefName, userid, prefValue):
with xs.getMysqlCon() as con:
command = "update user_prefs set " + prefName + " = %s where userid = %s"
try:
con.execute(command, (prefValue, userid))
except:
return "Error updating user_prefs"
return "success"
def isAlphagramValid(alpha):
chCommand = "select count(*) from words where alphagram = %s"
with xs.getMysqlCon() as con:
if con is not None:
con.execute(chCommand, (alpha,))
return con.fetchone()[0] > 0
else:
return False
return False
def getDef (word) :
with xs.getMysqlCon() as con:
if con is None:
return " "
command = "select definition from words where word = %s"
con.execute(command, word)
try:
return con.fetchone()[0]
except:
return " "
def getAnagrams (alpha) :
'''
Takes in an alphagram and returns a list of words
'''
x = []
with xs.getMysqlCon() as con:
if con is not None:
con.execute("select word from words where alphagram = '{0}'".format(alpha))
for row in con.fetchall():
x.append(row[0])
return x
def getHooks (word) :
'''
Takes in a word
Returns a tuple (front hooks, word, back hooks)
'''
with xs.getMysqlCon() as con:
if con is not None:
command = "select front_hooks, word, back_hooks from words where word = %s"
con.execute(command, word)
return con.fetchone()
else:
return (None, None, None)
def getDots (word) :
'''
takes in a word, returns a list of two booleans
does the word lose the front / back letter and still make a word?
'''
with xs.getMysqlCon() as con:
if con is not None:
command = "select count(*) from words where word = %s"
con.execute(command, word[1:])
numFront = con.fetchone()[0]
con.execute(command, word[:-1])
numBack = con.fetchone()[0]
return [numFront > 0, numBack > 0]
def updateActive (userid, timestamp=None) :
if timestamp is None:
timestamp = int(time.time())
with xs.getMysqlCon() as con:
if con is None:
result["status"] = "Chat Database Connection Failed"
else:
try:
command = "update login set last_active = %s where userid = %s"
if (userid != 0): # 0 is the system user
con.execute(command, (timestamp, userid))
except mysql.Error, e:
result["status"] = "MySQL error %d %s " % (e.args[0], e.args[1])
def getAllPrefs(userid):
with xs.getMysqlCon() as con:
command = "select studyOrderIndex, closet, newWordsAtOnce, reschedHrs, showNumSolutions, cb0max, showHints, schedVersion from user_prefs where userid = %s"
con.execute(command, userid)
row = con.fetchone()
result = {}
result["studyOrderIndex"] = row[0]
result["closet"] = row[1]
result["newWordsAtOnce"] = row[2]
result["reschedHrs"] = row[3]
result["showNumSolutions"] = row[4]
result["cb0max"] = row[5]
result["showHints"] = row[6]
result["schedVersion"] = row[7]
return result
def appendChatToResult(line):
temp = line.split(',')
userid = temp[0]
timeStamp = temp[1]
message = ','.join(temp[2:]).strip()
expire = not message
with xs.getMysqlCon() as con:
if con is None:
pass
else:
con.execute("select photo, name from login where userid = %s", userid)
row = con.fetchone()
return {"chatDate": int(timeStamp), "photo": row[0], "name": row[1], "chatText": message, "chatUser": userid, "expire": expire}
def post(userid, message, chatTime=None, expire=False):
# chatTime is in milliseconds - epoch*1000
# message is unicode
result = {}
now = int(time.time())
if int(userid) > 10:
ua.updateActive(userid)
AUTOLOGOFF = .5 # in hours
logoffTime = now - (3600 * AUTOLOGOFF)
if chatTime is None:
chatTime = now * 1000
# this is a little kludgy
# bad data spooled to the chat file hoses everything
if userid is None:
pass
result["status"] = "Chat Missing Userid"
elif not expire and message is None:
result["status"] = "Chat Missing Message"
else:
try:
with xs.getMysqlCon(useUnicode=True) as con:
if expire:
command = "delete from chat where userid = %s and timeStamp = %s"
con.execute(command, (userid, chatTime))
msg = userid + u',' + unicode(chatTime) + u',' + u'\n'
result["msg"] = msg
else:
command = 'insert into chat (userid, timeStamp, message) values (%s, %s, %s)'
con.execute(command, (userid.encode('utf8'), chatTime,
message.encode('utf8')))
msg = userid + u',' + unicode(
chatTime) + u',' + message + u'\n'
result["msg"] = msg
command = "select userid from login where last_active > %s"
con.execute(command, [logoffTime])
for row in con.fetchall():
filename = os.path.join('chats', row[0] + '.chat')
with open(filename, 'a') as f:
f.write(msg.encode('utf8'))
result["status"] = "success"
except mysql.Error, e:
result["status"] = "MySQL error %d %s " % (e.args[0], e.args[1])
except:
def getFromStudyOrder (numNeeded, userid, cur):
result = [ ]
studyOrderIndex = getPrefs("studyOrderIndex", userid)
if numNeeded < 1:
return result
with xs.getMysqlCon() as mysqlcon:
if mysqlcon is None:
return result
cur.execute("select question from questions where next_scheduled is not null union all select question from next_added")
allQuestions = set([x[0] for x in cur.fetchall()])
while len(result) < numNeeded:
mysqlcon.execute("select alphagram, studyOrderIndex from studyOrder where studyOrderIndex between %s and %s order by studyOrderIndex", (studyOrderIndex, studyOrderIndex + 100))
allStudyOrder = [row for row in mysqlcon.fetchall() if row[0] not in allQuestions]
for row in allStudyOrder:
result.append(row[0])
studyOrderIndex = row[1]+1
if len(result) >= numNeeded:
break
if len(allStudyOrder) == 0:
studyOrderIndex = studyOrderIndex + 101
# allStudyOrder = mysqlcon.fetchall()
# studyOrder = list(set([x[0] for x in allStudyOrder]) - set(allQuestions))
# try:
# studyOrderIndex = min([x[1] for x in allStudyOrder if x[0] in studyOrder])
# except:
# studyOrderIndex = 0
# command = "select alphagram from studyOrder where studyOrderIndex = %s"
# while len(result) < numNeeded:
# mysqlcon.execute(command, studyOrderIndex)
# a = mysqlcon.fetchone()
# if a is None:
# return result
# if a[0] in allQuestions:
# pass
# else:
# result.append(a[0])
# studyOrderIndex = studyOrderIndex + 1
setPrefs(userid=userid, studyOrderIndex=studyOrderIndex)
return result
def setPrefs (userid,
prefName = None,
prefValue = None,
studyOrderIndex = None,
closet = None,
newWordsAtOnce = None,
reschedHrs = None,
showNumSolutions = None):
# TODO: check to be sure the inputs are correct data type, etc
with xs.getMysqlCon() as con:
try:
if con is None:
return "Error: DB Conn Failed Updating user_prefs"
if prefName is not None:
command = "update user_prefs set %s = %s where userid = %s"
command.execute(command, (prefName, prefValue, userid))
else:
command = "select studyOrderIndex, closet, newWordsAtOnce, reschedHrs, showNumSolutions from user_prefs where userid = %s"
con.execute(command, userid)
newPrefs = list(con.fetchone())
if studyOrderIndex is not None:
newPrefs[0] = studyOrderIndex
if closet is not None:
newPrefs[1] = closet
if newWordsAtOnce is not None:
newPrefs[2] = newWordsAtOnce
if reschedHrs is not None:
newPrefs[3] = reschedHrs
if showNumSolutions is not None:
newPrefs[4] = showNumSolutions
command = "update user_prefs set studyOrderIndex = %s, closet = %s, newWordsAtOnce = %s, reschedHrs = %s, showNumSolutions = %s where userid = %s"
con.execute(command, tuple(newPrefs) + (userid,))
return "Success updating user preferences"
except mysql.Error, e:
return "MySQL error updating user_prefs %d %s" % (e.args[0], e.args[1])
def getPrefs (prefName, userid):
with xs.getMysqlCon() as con:
command = "select " + prefName + " from user_prefs where userid = %s"
con.execute(command, userid)
return con.fetchone()[0]
import xerafinChat as xchat
import time
result = {"personal": 0, "daily": {"score": 0, "userid": 0}}
error = {"status": "success"}
try:
params = json.load(sys.stdin)
userid = params["userid"]
score = params["score"]
try:
gameover = params["gameOver"]
except:
gameover = False
with xs.getMysqlCon() as con:
con.execute("select score from invaders_personal where userid = %s", userid)
for row in con.fetchall():
result["personal"] = row[0]
con.execute("select score, userid from invaders_daily where dateStamp = curdate()")
for row in con.fetchall():
result["daily"]["score"] = row[0]
result["daily"]["userid"] = row[1]
if score > result["personal"]:
result["personal"] = score
con.execute("update invaders_personal set score = %s where userid = %s", (score, userid))
if score >= result["daily"]["score"]:
result["daily"] = {"score": score, "userid": userid}
con.execute("update invaders_daily set score = %s, userid = %s where dateStamp = curdate()", (score, userid))
if gameover and int(score) > 0:
command = "select name from login where userid = %s"
import xerafinSetup as xs
import time
import os, sys
import math
#print "Content-type: text/html\n\n"
#print "<html><head>"
#print "<title>Cron Test</title>"
#print "</head><body>"
now = int(time.time())
try:
with xs.getMysqlCon(True) as con:
con.execute("select count(*), sum(questionsAnswered) from leaderboard where dateStamp = curdate() - interval 1 day group by dateStamp")
row = con.fetchone()
users = row[0]
questions = row[1]
# Add daily summary to lb_summary
command = "insert into lb_summary (period, dateStamp, questionsAnswered, numUsers) values (%s, CURDATE() - interval 1 day, %s, %s)"
con.execute(command, ('DAY', questions, users))
try:
if time.strftime("%A") == "Monday":
command = "insert into lb_summary(period, dateStamp, questionsAnswered, numUsers) select 'WEEK', min(dateStamp), sum(questionsAnswered), count(distinct userid) from leaderboard where DATE_FORMAT(dateStamp, '%Y%u') = DATE_FORMAT(curdate() - interval 1 day, '%Y%u') group by DATE_FORMAT(dateStamp, '%Y%u')"
con.execute(command)
except:
pass
