def GET(self, **kwargs):
		"""logs the user in
		Note that this GET request doesn't take json like the other requests
		"""
		row = kwargs

		if row.has_key('username') and row.has_key('password'):
			conn, cur = databeast.connect()

			#does user exist?
			cur.execute("SELECT COUNT(*) FROM users WHERE username=%s", [row['username']])
			count = cur.fetchall()[0][0]
			if count == 1:
				sql = "SELECT password FROM users WHERE username = %s"
				cur.execute(sql, [row['username']])
				hashed_pw = cur.fetchall()[0][0]
				if bcrypt.hashpw(row['password'].encode('utf-8'), hashed_pw.encode('utf-8')) == hashed_pw:
					output = [True, "User %s logged in." % (row['username'])]
					#open a training session
					session.openSession(row['username'])
				else:
					output = [False, "Invalid login credentials."]
			else:
				output = [False, "User %s does not exist." % row['username']]

			return output
		else:
			raise cherrypy.HTTPError(400)
 def tearDown(self):
     conn, cur = databeast.connect()
     cur.execute("DELETE FROM sessions WHERE username = %s", ['unit_test_user_7'])
     cur.execute("DELETE FROM sessions WHERE username = %s", [self.username])
     cur.execute("DELETE FROM users WHERE username = %s", [self.username])
     cur.execute("DELETE FROM sessions WHERE username = %s", ['unit_test_user_6'])
     conn.commit()
     conn.close()
def closeSession(username):
    """close a training session for a given user within the database
    """
    conn, cur = databeast.connect()

    sql = """UPDATE sessions SET end_time=%s, complete=FALSE
                WHERE username=%s AND end_time = NULL"""
    cur.execute(sql, [datetime.now(), username])
    conn.commit()
    conn.close()
    def PUT(self):
        """Mark a task as played and update the open session
        """
        row = cherrypy.request.json

        if row.has_key('task') and row.has_key('username'):
            task = row['task']
            username = row['username']
            #get the most recent open session
            conn, cur = databeast.connect()
            sql = """SELECT session, tasks_to_play, tasks_played
                    FROM sessions
                    WHERE username = %s AND end_time IS NULL
                    ORDER BY session DESC"""
            cur.execute(sql, [username])
            result = cur.fetchall()
            if result:
                session = result[0][0]
                tasks_to_play = result[0][1]
                tasks_played = result[0][2]

                #move the completed task to played list
                tasks_to_play.remove(task)

                if tasks_played:
                    tasks_played.append(task)
                else:
                    tasks_played = [task]

                #update DB
                sql = """UPDATE sessions
                        SET tasks_to_play = %s, tasks_played = %s
                        WHERE username = %s AND session = %s"""

                cur.execute(sql, [tasks_to_play, tasks_played, username, session])
                conn.commit()

                #close the session if we're out of tasks_played
                if len(tasks_to_play) == 0:
                    sql = """UPDATE sessions
                            SET complete = TRUE, end_time = %s
                            WHERE username = %s AND session = %s"""

                    cur.execute(sql, [datetime.now(), username, session])
                    conn.commit()

                return tasks_to_play

            else:
                raise cherrypy.HTTPError(400, "No open session for %s." % username)
        else:
            raise cherrypy.HTTPError(400, "Missing username and/or task")
 def GET(self, **kwargs):
     """Get the current open session given a username
     returns a list [day, week, task_list]
     """
     if kwargs.has_key('username'):
         #retrieve the open session (if exists from the db)
         conn, cur = databeast.connect()
         sql = """SELECT session, tasks_to_play FROM sessions
                 WHERE username = %s AND end_time IS NULL
                 ORDER BY start_time DESC"""
         cur.execute(sql, [kwargs['username']])
         result = cur.fetchall()
         if result:
             return result[0]
         else:
             return None
     else:
         raise cherrypy.HTTPError(400, "Missing username")
 def GET(self, **kwargs):
     """Get the problems for a given week and problem_set
     """
     if kwargs.has_key('week'):
         #retrieve the open session (if exists from the db)
         conn, cur = db.connect()
         query = "SELECT n1 FROM problems WHERE week='%s' AND problem_set='%s'"%(kwargs['week'], kwargs['problem_set'])
         cur.execute(query)
         op1 = cur.fetchall()
         query = "SELECT n2 FROM problems WHERE week='%s' AND problem_set='%s'"%(kwargs['week'], kwargs['problem_set'])
         cur.execute(query)
         op2 = cur.fetchall()
         query = "SELECT problem_id FROM problems WHERE week='%s' AND problem_set='%s'"%(kwargs['week'], kwargs['problem_set'])
         cur.execute(query)
         problem_id = cur.fetchall()
         if op1:
             return op1, op2, problem_id
         else:
             return None
     else:
         raise cherrypy.HTTPError(400, "Missing week")
def openSession(username):
    """
    open a training session for a given user within the database
    IMPORTANT - this function is only run during a login event
    returns db status message
    """
    conn, cur = databeast.connect()

    #first determine whether any open sessions exist
    sql = "SELECT count(*) FROM sessions WHERE username = %s AND end_time IS NULL"
    cur.execute(sql, [username])
    result = cur.fetchall()
    #TODO - check to see whether we are on the same training day - would want
    #to resume previous session in this case, and not close this one
    if result[0][0] >= 1:
        closeSession(username) #closes any active session for that user
    #now, determine the previous session number
    sql = "SELECT MAX(session) FROM sessions WHERE username = %s"
    cur.execute(sql, [username])
    result = cur.fetchall()
    if result[0][0]:
        session = result[0][0] + 1
    else:
        session = 1

    conn.close()

    #now, open the new session
    d = {}
    d['username'] = username
    d['tasks_to_play'] = tasks #TODO - counterbalancing
    d['training_day'] = 1 #TODO - increment based on previous training day
    d['week'] = 1
    d['session'] = session

    result = databeast.insert(d, "sessions")
    return result
    def test_task_delivery(self):
        """sessions should close after all tasks completed"""
        tasks = ["task1", "task2", "task3"]
        session.openSession('unit_test_user_7')

        #override the task list for the purpose of this test
        conn, cur = databeast.connect()
        sql = "UPDATE sessions SET tasks_to_play = %s WHERE username = %s"
        cur.execute(sql, [tasks, 'unit_test_user_7'])
        conn.commit()

        #mark the first task as done
        payload = {'username': '******', 'task':'task1'}
        r = requests.put(self.session_url, json=payload)
        json_data = json.loads(r.text)
        self.assertTrue(json_data == ["task2", "task3"])

        #mark the second task as done
        payload = {'username': '******', 'task':'task2'}
        r = requests.put(self.session_url, json=payload)
        json_data = json.loads(r.text)
        self.assertTrue(json_data == ["task3"])

        #mark the third (and final) task as done
        payload = {'username': '******', 'task':'task3'}
        r = requests.put(self.session_url, json=payload)
        json_data = json.loads(r.text)
        self.assertTrue(json_data == [])

        #check that session is closed
        sql = "SELECT complete FROM sessions WHERE username = %s"
        cur.execute(sql, ['unit_test_user_7'])
        result = cur.fetchall()
        conn.close()

        self.assertTrue(result[0][0] == True)