
REGION = 'us-west-2'
BUCKET = 'p100-analytics'
CREDENTIALS = 'db-credentials-2015-02-17'


import logging
import MySQLdb
#from MySQLdb import ProgrammingError, OperationalError, InterfaceError
from psycopg2 import ProgrammingError, OperationalError, InterfaceError, InternalError
import psycopg2 as mdb
import pandas
from p100.range import Range
#import MySQLdb as mdb
import numpy as np
import datetime, re
from csv import reader

from p100.range import Range
from p100.errors import MyError
from p100.utils.botoops import BotoOps

l_logger = logging.getLogger("p100.database")

FIRST_BLOOD_DRAW=datetime.datetime(2014, 6, 24)
SECOND_BLOOD_DRAW=datetime.datetime(2014, 9, 30)
import boto.kms
import boto
from boto.s3.key import Key
import json

class Database(object):

    def __init__(self, host=None, user=None, passwd=None, db=None,
                    port=None, credentials=CREDENTIALS, bucket=BUCKET, region=REGION):
        if user is None and credentials is not None:
            cred = BotoOps().get_credentials( credentials, bucket, region)
            self.host = cred['host']
            self.user = cred['user']
            self.passwd = cred['password']
            self.port = cred['port']
            self.db_name = cred['database']
        else:
            self.host, self.user, self.passwd, self.db_name, self.port = host,user,passwd,db,port
        self._db = None
        l_logger.debug( "DataBase %s,%s,%s" % (self.host, self.user, self.db_name ))

    def __del__(self):
        # Close the database
        self.db.close()

    def Get(self, table, key, value, field='*', orderby=None):
        # Get the cursor
        cursor = self.db.cursor()

        if (orderby is None):
            cursor.execute("SELECT " + ','.join(field) + " FROM " + table + " WHERE " + key + " = (%s)", (value,))
        else:
             cursor.execute("SELECT " + ','.join(field) + " FROM " + table + " WHERE " + key + " = (%s) ORDER BY " + orderby, (value,))
        return cursor

    def GetDataFrame(self, q_string, var_tup=None):
        """
        Given a select query return a pandas dataframe corresponding to that query.
        Columns are the columns from the query, you must set the index on the dataframe
        manually, i.e. >>df = df.set_index("index_column") 

        q_string is a select query
        var_tup is a tuple containing values to be passed to the query

        example:
            >> Database_obj = Database()
            >> q = "SELECT * from table where table_id = %s"
            >> v = ( 1, )
            >> df = Database_obj.GetDataFrame( q, v)
        """
        def map_to_dict( results, field_names):
            res_dict = {}
            for fn in field_names:
                res_dict[fn] = []
            for res in results:
                for fn, f in zip(field_names, res):
                    res_dict[fn].append(f)
            return res_dict
        def map_to_df( results, field_names):
            return pandas.DataFrame.from_dict(map_to_dict( results, field_names ))
        cursor = self.GetCursor()
        l_logger.debug("Query: %s, %r" % (q_string,var_tup))
        cursor.execute(q_string,var_tup)
        results = cursor.fetchall()
        field_names = [i[0] for i in cursor.description]
        if len(results) == 0:
            return None
        else:
            return map_to_df( results, field_names )

    def GetCursor(self):
        return self.db.cursor();

    def Commit(self):
        self.db.commit();

    def Close(self):
        """
        Closes the database connection
        """
        l_logger.debug("Closing database connection")
        self.db.close()

    def _GetNewConnection(self):
        """
        Creates a new database connection
        """
        l_logger.debug("Creating a db connection")
        return mdb.connect(host=self.host,
                user=self.user, 
                password=self.passwd, 
                database=self.db_name,
                port=self.port
                )

    @property
    def db( self ):
        """
        Returns a functioning mysqldb connection, if we're lucky
        """
        #TODO: backoff
        if self._db is None:
            self._db = self._GetNewConnection()
        try:
            self._db.isolation_level
        except (OperationalError, InterfaceError, InternalError):
            l_logger.exception("Looks like the db is not responding. Trying to recover.")
            try:
                self._db.close()
            except ProgrammingError:
                l_logger.info("Database is closed, attempting to recover")
            self._db = self._GetNewConnection()
        return self._db

    def GetCompliance(self, username):

        # Get the cursor
        cursor = self.db.cursor();

        cursor.execute("SELECT * FROM compliance WHERE username = (%s)", (username,))
        result = None
        columns = [d[0] for d in cursor.description]

        # Concatenate all the tuples together
        for row in cursor:
            if (result is None):
                result = row
            else:
                result = zip(result, row)

        if (result is None):
            return {}

        if (len(result)==0):
            return {}

        # Now convert each value into a numpy array
        final = {}
        for key, values in zip(columns, result):
            if (key == "USERNAME"):
                continue;
            try:
                final[key] = int(values)
            except:
                final[key] = values
        return final

    def GetData(self, username):

        # Get the cursor
        cursor = self.db.cursor();

        cursor.execute("SELECT * FROM data5 WHERE username = (%s) ORDER BY ROUND", (username,))
        result = None
        columns = [d[0] for d in cursor.description]

        # Concatenate all the tuples together
        result = []
        for row in cursor:
            result.append(row)

        if (len(result)>0):
            result = zip(*result)

        if (result is None):
            return {}

        if (len(result)==0):
            return {}

        # Now convert each value into a numpy array
        final = {}
        for key, values in zip(columns, result):
            if (key == "USERNAME"):
                continue;
            if (key == "ROUND"):
                final[key] = np.array(values, dtype=np.object)
                continue;

            try:
                final[key] = np.array(values, dtype=np.float)
            except:
                final[key] = np.array(values, dtype=np.object)
        return final

    def GetAll(self, table):

         # Get the cursor
        cursor = self.db.cursor()

        cursor.execute("SELECT * FROM " + table)
        return cursor

    def Command(self, command):

        cursor = self.db.cursor()
        cursor.execute(command);
        return cursor;

    def GetHeaders(self, table):

        # Get the cursor
        cursor = self.db.cursor();

        cursor.execute("SELECT * FROM " + table + " LIMIT 1");
        columns = [d[0] for d in cursor.description];
        return columns;

    def GetInternalIDMapping(self):

        cursor = self.db.cursor()
        cursor.execute("SELECT MEASUREMENT,INTERNAL_ID FROM ranges");
        mapping = {};
        for (measurement, id) in cursor:
            mapping[id] = measurement
        return mapping

    def GetMeasurementByRound(self, measurement, username, round):

        cursor = self.db.cursor()
        cursor.execute("SELECT " + measurement + " FROM data5 WHERE USERNAME = (%s) and ROUND = (%s)", (username,round))
        results = [];
        for row in cursor:
            results.append(row)

        # Return nothing if it is not filled in
        if (len(results)==0):
            return None

        # Error if there are multiple results
        if (len(results)>1):
            raise MyError('Multiple results back from database for %s %s %s'%(measurement, username, str(round)))

        # Return the actual value
        return results[0]

    def GetMeasurementByDate(self, measurement, username, date):

        cursor = self.db.cursor()
        cursor.execute("SELECT " + measurement + " FROM data5 WHERE USERNAME = (%s) and DATE = (%s)", (username,date))
        results = [];
        for row in cursor:
            results.append(row)

        # Return nothing if it is not filled in
        if (len(results)==0):
            return None

        # Error if there are multiple results
        if (len(results)>1):
            raise MyError('Multiple results back from database for %s %s %s'%(measurement, username, str(date)))

        # Return the actual value
        return results[0]

    def LoadRanges(self):

        # Dictionary to store the results
        ranges = {};

        # Retrieve all the ranges from the database
        results = self.Command("SELECT * FROM ranges")

        for (measurement, unit, male_low, male_med, male_high, female_low, female_med, female_high, hmdb, internal_id) in results:
            range = Range(measurement, unit, male_low, male_med, male_high, female_low, female_med, female_high, hmdb, internal_id)
            if (not range.measurement in ranges):
                ranges[range.measurement] = range
            else:
                raise MyError("Warning: range for measurement %s already exists" % range.measurement)
        print "Loaded %d reference ranges" % len(ranges)
        return ranges;

    def Clean(self, value):

        temp = value.strip('<').strip('>').strip(' ').strip('.').strip("NR");

        # If there is nothing left, return NULL
        if (len(temp)==0):
            return None # Return null value

        # This is to handle the Quest NOT APPLICABLE issue
        if ("OT" in temp.upper()):
            return None

        # Below detectable limit. Return 0
        if ("DL" in temp.upper()):
            temp = '0'
        # Set A/B and binary entries to integers
        elif (temp.upper() == 'A'):
            print temp
            temp = '0'
        elif (temp.upper() == 'B'):
            print temp
            temp = '1'
        elif (temp.upper() == "NO"):
            print temp
            temp = '0'
        elif (temp.upper() == "YES"):
            print temp
            temp = '1'

        return temp;

    def UpdateData(self, username, round, db_date, date_ordered, data, mapping):

        # For this we check each value individually. If data is missing at a position, we fill it in.
        # Otherwise, we only update it if date_ordered is more recent than db_date
        update = False
        if (date_ordered > db_date):
            update = True

        # Go through each data point in my dictionary
        for key in data.keys():

            # Get the new value
            new_value = self.Clean(data[key]);

            # Do not insert a null value
            if (new_value is None):
                continue;

            # Skip the key if we don't have a place for it
            if (not key in mapping):
                continue;

            # Get the value from this row for the old date
            original_value = self.GetMeasurementByDate(mapping[key], username, db_date)

            # If it returns None, this is an error. This function is for updating, not inserting
            if (original_value is None):
                raise MyError('Attempted insertion in UpdateData with username %s and round %d'%(username, round));

            # Otherwise, get the value
            original_value = original_value[0];

            # If this is None or update = True, we can set the value
            if (original_value is None) or (update):

                command = "UPDATE data5 SET %s = '%s' WHERE USERNAME = '%s' AND DATE = '%s'"%(mapping[key], new_value, username, db_date)
                self.Command(command);

            """
            elif (original_value != new_value):

                # Take this away
                command = "UPDATE data SET %s = '%s' WHERE USERNAME = '%s' AND DATE = '%s'"%(mapping[key], new_value, username, db_date)
                print command
                self.Command(command);
            """

        # Finally, update the date so we don't repeat ourselves
        # TODO: I'm not sure how this will affect Quest additions, so leave it off for now.
        if (update):
            command = "UPDATE data5 SET %s = '%s' WHERE USERNAME = '%s' AND DATE = '%s'"%('DATE', date_ordered, username, db_date)
            #print command
            #self.Command(command);

    def InsertData(self, username, round, date_ordered, data, mapping):

        # Build the insertion statement
        command = "INSERT INTO data5 (USERNAME,ROUND,DATE";
        for key in data.keys():

            if (not key in mapping):
                continue;

            command += ','+mapping[key];

        command += ") VALUES (%s, %s, %s";

        # Build tuple for parameterization
        tdata = (username, round, date_ordered.strftime('%Y-%m-%d %H:%M:%S'))

        for key in data.keys():

            if (not key in mapping):
                continue;

            command += ', %s';
            tdata += (self.Clean(data[key]),);

        command += ")";

        # Get the cursor
        cursor = self.db.cursor();
        cursor.execute(command, tdata)

    def LoadQuest(self, filename):

        # Create regular expression for the measurement
        id_reg = re.compile(r"\([0-9]{8}\)");
        #username_reg = re.compile(r"[0-9]{7}(?!\S)");
        username_reg = re.compile(r"[0-9]{7}");

        # Get the headers from the data table
        mapping = self.GetInternalIDMapping()

        with open(filename, 'rU') as f:
            for tokens in reader(f):

                if (len(tokens)<2):
                    continue;

                id = id_reg.findall(tokens[1]);
                if (len(id)==0):
                    #print "Warning: No ID in Quest measurement"
                    continue
                if (len(id)>1):
                    #print "Warning: Multiple IDs in Quest measurement"
                    continue

                # Get the actual id
                id = id[0].strip('(').strip(')')

                # Must split the value int two
                temp = tokens[2].split(' ');
                value = temp[0].strip();

                # Search fields 3 and 4 for usernames
                temp1 = username_reg.findall(tokens[3])

                usernames = set();
                for e in temp1:
                    usernames.add(e);
                if (len(usernames)==0):
                    print "Error, no username for Quest measurement"
                    print tokens;
                    print temp1, temp2
                    continue;
                if (len(usernames)>1):
                    print "Error multiple usernames for Quest measurement"
                    print temp1, temp2
                    continue;

                # Actually get the username
                username = usernames.pop();
                submitted_date = tokens[9];

                # Convert submitted date to datetime
                date_ordered = datetime.datetime.strptime(submitted_date, "%m/%d/%Y");
                if (date_ordered <= FIRST_BLOOD_DRAW):
                    round = 1
                elif (date_ordered <= SECOND_BLOOD_DRAW):
                    round = 2
                else:
                    round = 3

                # Get the value for this date and round
                result = self.GetMeasurementByRound('DATE', username, round);
                if (not result is None):

                    # There is data, skip insertion
                    #print "Found round %d for username %s"%(round, username);

                    # Get the associated date
                    db_date = result[0];
                    if (db_date is None):
                        raise MyError('No date for username %s and round %d'%(username, round));

                    # Update data one at a time (yes inefficient, so shoot me)
                    self.UpdateData(username, round, db_date, date_ordered, {id: value}, mapping)

                # There was no row found, so insert a new row!
                else:

                    # Insert new row
                    print "Cannot insert rows from Quest data [%s %s]"%(username, date_ordered)
                    #raise MyError('Cannot insert data from Quest - add Genova first!');


    def LoadGenova(self, filename):

        titles = None
        headers = None

        # Get the headers from the data table
        mapping = self.GetInternalIDMapping()

        with open(filename, 'rU') as f:
            for line in f:

                # Load the headers, removing the final 9 columns
                if (titles is None):
                    titles = line.strip().split('\t')[:-9]

                # Next load the identifiers, removing the final 9 columns
                elif (headers is None):
                    headers = line.strip().split('\t')[:-9]

                # Finally, load the data, removing the final 9 columns
                else:
                    tokens = line.strip().split('\t')[:-9]

                    # Convert into dictionary
                    data = dict(zip([x.strip().upper() for x in headers], [x.strip().upper() for x in tokens]))

                    # Get username from this row
                    username = data["LAST NAME"]

                    print tokens
                    print username

                    # Get date from this row
                    date_ordered = datetime.datetime.strptime(data['DATE ORDERED'], "%m/%d/%y");
                    if (date_ordered <= FIRST_BLOOD_DRAW):
                        round = 1
                    elif (date_ordered <= SECOND_BLOOD_DRAW):
                        round = 2
                    else:
                        round = 3

                    # Get the value for this date and round
                    result = self.GetMeasurementByRound('DATE', username, round);
                    if (not result is None):

                        # There is data, skip insertion
                        print "Found round %d for username %s"%(round, username);

                        # Get the associated date
                        db_date = result[0];
                        if (db_date is None):
                            raise MyError('No date for username %s and round %d'%(username, round));

                        # Update data
                        self.UpdateData(username, round, db_date, date_ordered, data, mapping)

                    # There was no row found, so insert a new row!
                    # TODO: we could check to see if the date already exists as well
                    else:

                        # Insert new row
                        print "Inserting for username %s date %s"%(username, date_ordered)
                        self.InsertData(username, round, date_ordered, data, mapping)

    def CreateDataTable(self):

        # Get the ranges, which will determine what we load in
        ranges = self.LoadRanges();

        command = ""
        command += "CREATE TABLE data5 (ENTRY INT PRIMARY KEY AUTO_INCREMENT, USERNAME VARCHAR(16) NOT NULL, DATE DATETIME, ROUND INT NOT NULL"
        count = 0

        for key in sorted(ranges.keys()):
                command += ", " + key.upper().replace('-','_').replace('/','_').replace(' ', '_').replace('.','_') + " FLOAT"
        command += ")"

        # First create columns
        self.Command(command)






e9d2a3174182fe8106a4e66f741eb305a81eef14

REGION = 'us-west-2'
BUCKET = 'p100-analytics'
CREDENTIALS = 'db-credentials-2015-02-17'


import logging
import MySQLdb
#from MySQLdb import ProgrammingError, OperationalError, InterfaceError
from psycopg2 import ProgrammingError, OperationalError, InterfaceError, InternalError
import psycopg2 as mdb
import pandas
from p100.range import Range
#import MySQLdb as mdb
import numpy as np
import datetime, re
from csv import reader

from p100.range import Range
from p100.errors import MyError
from p100.utils.botoops import BotoOps

l_logger = logging.getLogger("p100.database")

FIRST_BLOOD_DRAW=datetime.datetime(2014, 6, 24)
SECOND_BLOOD_DRAW=datetime.datetime(2014, 9, 30)
import boto.kms
import boto
from boto.s3.key import Key
import json

class Database(object):

    def __init__(self, host=None, user=None, passwd=None, db=None,
                    port=None, credentials=CREDENTIALS, bucket=BUCKET, region=REGION):
        if user is None and credentials is not None:
            cred = BotoOps().get_credentials( credentials, bucket, region)
            self.host = cred['host']
            self.user = cred['user']
            self.passwd = cred['password']
            self.port = cred['port']
            self.db_name = cred['database']
        else:
            self.host, self.user, self.passwd, self.db_name, self.port = host,user,passwd,db,port
        self._db = None
        l_logger.debug( "DataBase %s,%s,%s" % (self.host, self.user, self.db_name ))

    def __del__(self):
        # Close the database
        self.db.close()

    def Get(self, table, key, value, field='*', orderby=None):
        # Get the cursor
        cursor = self.db.cursor()

        if (orderby is None):
            cursor.execute("SELECT " + ','.join(field) + " FROM " + table + " WHERE " + key + " = (%s)", (value,))
        else:
             cursor.execute("SELECT " + ','.join(field) + " FROM " + table + " WHERE " + key + " = (%s) ORDER BY " + orderby, (value,))
        return cursor

    def GetDataFrame(self, q_string, var_tup=None):
        """
        Given a select query return a pandas dataframe corresponding to that query.
        Columns are the columns from the query, you must set the index on the dataframe
        manually, i.e. >>df = df.set_index("index_column") 

        q_string is a select query
        var_tup is a tuple containing values to be passed to the query

        example:
            >> Database_obj = Database()
            >> q = "SELECT * from table where table_id = %s"
            >> v = ( 1, )
            >> df = Database_obj.GetDataFrame( q, v)
        """
        def map_to_dict( results, field_names):
            res_dict = {}
            for fn in field_names:
                res_dict[fn] = []
            for res in results:
                for fn, f in zip(field_names, res):
                    res_dict[fn].append(f)
            return res_dict
        def map_to_df( results, field_names):
            return pandas.DataFrame.from_dict(map_to_dict( results, field_names ))
        cursor = self.GetCursor()
        l_logger.debug("Query: %s, %r" % (q_string,var_tup))
        cursor.execute(q_string,var_tup)
        results = cursor.fetchall()
        field_names = [i[0] for i in cursor.description]
        if len(results) == 0:
            return None
        else:
            return map_to_df( results, field_names )

    def GetCursor(self):
        return self.db.cursor();

    def Commit(self):
        self.db.commit();

    def Close(self):
        """
        Closes the database connection
        """
        l_logger.debug("Closing database connection")
        self.db.close()

    def _GetNewConnection(self):
        """
        Creates a new database connection
        """
        l_logger.debug("Creating a db connection")
        return mdb.connect(host=self.host,
                user=self.user, 
                password=self.passwd, 
                database=self.db_name,
                port=self.port
                )

    @property
    def db( self ):
        """
        Returns a functioning mysqldb connection, if we're lucky
        """
        #TODO: backoff
        if self._db is None:
            self._db = self._GetNewConnection()
        try:
            self._db.isolation_level
        except (OperationalError, InterfaceError, InternalError):
            l_logger.exception("Looks like the db is not responding. Trying to recover.")
            try:
                self._db.close()
            except ProgrammingError:
                l_logger.info("Database is closed, attempting to recover")
            self._db = self._GetNewConnection()
        return self._db

    def GetCompliance(self, username):

        # Get the cursor
        cursor = self.db.cursor();

        cursor.execute("SELECT * FROM compliance WHERE username = (%s)", (username,))
        result = None
        columns = [d[0] for d in cursor.description]

        # Concatenate all the tuples together
        for row in cursor:
            if (result is None):
                result = row
            else:
                result = zip(result, row)

        if (result is None):
            return {}

        if (len(result)==0):
            return {}

        # Now convert each value into a numpy array
        final = {}
        for key, values in zip(columns, result):
            if (key == "USERNAME"):
                continue;
            try:
                final[key] = int(values)
            except:
                final[key] = values
        return final

    def GetData(self, username):

        # Get the cursor
        cursor = self.db.cursor();

        cursor.execute("SELECT * FROM data5 WHERE username = (%s) ORDER BY ROUND", (username,))
        result = None
        columns = [d[0] for d in cursor.description]

        # Concatenate all the tuples together
        result = []
        for row in cursor:
            result.append(row)

        if (len(result)>0):
            result = zip(*result)

        if (result is None):
            return {}

        if (len(result)==0):
            return {}

        # Now convert each value into a numpy array
        final = {}
        for key, values in zip(columns, result):
            if (key == "USERNAME"):
                continue;
            if (key == "ROUND"):
                final[key] = np.array(values, dtype=np.object)
                continue;

            try:
                final[key] = np.array(values, dtype=np.float)
            except:
                final[key] = np.array(values, dtype=np.object)
        return final

    def GetAll(self, table):

         # Get the cursor
        cursor = self.db.cursor()

        cursor.execute("SELECT * FROM " + table)
        return cursor

    def Command(self, command):

        cursor = self.db.cursor()
        cursor.execute(command);
        return cursor;

    def GetHeaders(self, table):

        # Get the cursor
        cursor = self.db.cursor();

        cursor.execute("SELECT * FROM " + table + " LIMIT 1");
        columns = [d[0] for d in cursor.description];
        return columns;

    def GetInternalIDMapping(self):

        cursor = self.db.cursor()
        cursor.execute("SELECT MEASUREMENT,INTERNAL_ID FROM ranges");
        mapping = {};
        for (measurement, id) in cursor:
            mapping[id] = measurement
        return mapping

    def GetMeasurementByRound(self, measurement, username, round):

        cursor = self.db.cursor()
        cursor.execute("SELECT " + measurement + " FROM data5 WHERE USERNAME = (%s) and ROUND = (%s)", (username,round))
        results = [];
        for row in cursor:
            results.append(row)

        # Return nothing if it is not filled in
        if (len(results)==0):
            return None

        # Error if there are multiple results
        if (len(results)>1):
            raise MyError('Multiple results back from database for %s %s %s'%(measurement, username, str(round)))

        # Return the actual value
        return results[0]

    def GetMeasurementByDate(self, measurement, username, date):

        cursor = self.db.cursor()
        cursor.execute("SELECT " + measurement + " FROM data5 WHERE USERNAME = (%s) and DATE = (%s)", (username,date))
        results = [];
        for row in cursor:
            results.append(row)

        # Return nothing if it is not filled in
        if (len(results)==0):
            return None

        # Error if there are multiple results
        if (len(results)>1):
            raise MyError('Multiple results back from database for %s %s %s'%(measurement, username, str(date)))

        # Return the actual value
        return results[0]

    def LoadRanges(self):

        # Dictionary to store the results
        ranges = {};

        # Retrieve all the ranges from the database
        results = self.Command("SELECT * FROM ranges")

        for (measurement, unit, male_low, male_med, male_high, female_low, female_med, female_high, hmdb, internal_id) in results:
            range = Range(measurement, unit, male_low, male_med, male_high, female_low, female_med, female_high, hmdb, internal_id)
            if (not range.measurement in ranges):
                ranges[range.measurement] = range
            else:
                raise MyError("Warning: range for measurement %s already exists" % range.measurement)
        print "Loaded %d reference ranges" % len(ranges)
        return ranges;

    def Clean(self, value):

        temp = value.strip('<').strip('>').strip(' ').strip('.').strip("NR");

        # If there is nothing left, return NULL
        if (len(temp)==0):
            return None # Return null value

        # This is to handle the Quest NOT APPLICABLE issue
        if ("OT" in temp.upper()):
            return None

        # Below detectable limit. Return 0
        if ("DL" in temp.upper()):
            temp = '0'
        # Set A/B and binary entries to integers
        elif (temp.upper() == 'A'):
            print temp
            temp = '0'
        elif (temp.upper() == 'B'):
            print temp
            temp = '1'
        elif (temp.upper() == "NO"):
            print temp
            temp = '0'
        elif (temp.upper() == "YES"):
            print temp
            temp = '1'

        return temp;

    def UpdateData(self, username, round, db_date, date_ordered, data, mapping):

        # For this we check each value individually. If data is missing at a position, we fill it in.
        # Otherwise, we only update it if date_ordered is more recent than db_date
        update = False
        if (date_ordered > db_date):
            update = True

        # Go through each data point in my dictionary
        for key in data.keys():

            # Get the new value
            new_value = self.Clean(data[key]);

            # Do not insert a null value
            if (new_value is None):
                continue;

            # Skip the key if we don't have a place for it
            if (not key in mapping):
                continue;

            # Get the value from this row for the old date
            original_value = self.GetMeasurementByDate(mapping[key], username, db_date)

            # If it returns None, this is an error. This function is for updating, not inserting
            if (original_value is None):
                raise MyError('Attempted insertion in UpdateData with username %s and round %d'%(username, round));

            # Otherwise, get the value
            original_value = original_value[0];

            # If this is None or update = True, we can set the value
            if (original_value is None) or (update):

                command = "UPDATE data5 SET %s = '%s' WHERE USERNAME = '%s' AND DATE = '%s'"%(mapping[key], new_value, username, db_date)
                self.Command(command);

            """
            elif (original_value != new_value):

                # Take this away
                command = "UPDATE data SET %s = '%s' WHERE USERNAME = '%s' AND DATE = '%s'"%(mapping[key], new_value, username, db_date)
                print command
                self.Command(command);
            """

        # Finally, update the date so we don't repeat ourselves
        # TODO: I'm not sure how this will affect Quest additions, so leave it off for now.
        if (update):
            command = "UPDATE data5 SET %s = '%s' WHERE USERNAME = '%s' AND DATE = '%s'"%('DATE', date_ordered, username, db_date)
            #print command
            #self.Command(command);

    def InsertData(self, username, round, date_ordered, data, mapping):

        # Build the insertion statement
        command = "INSERT INTO data5 (USERNAME,ROUND,DATE";
        for key in data.keys():

            if (not key in mapping):
                continue;

            command += ','+mapping[key];

        command += ") VALUES (%s, %s, %s";

        # Build tuple for parameterization
        tdata = (username, round, date_ordered.strftime('%Y-%m-%d %H:%M:%S'))

        for key in data.keys():

            if (not key in mapping):
                continue;

            command += ', %s';
            tdata += (self.Clean(data[key]),);

        command += ")";

        # Get the cursor
        cursor = self.db.cursor();
        cursor.execute(command, tdata)

    def LoadQuest(self, filename):

        # Create regular expression for the measurement
        id_reg = re.compile(r"\([0-9]{8}\)");
        #username_reg = re.compile(r"[0-9]{7}(?!\S)");
        username_reg = re.compile(r"[0-9]{7}");

        # Get the headers from the data table
        mapping = self.GetInternalIDMapping()

        with open(filename, 'rU') as f:
            for tokens in reader(f):

                if (len(tokens)<2):
                    continue;

                id = id_reg.findall(tokens[1]);
                if (len(id)==0):
                    #print "Warning: No ID in Quest measurement"
                    continue
                if (len(id)>1):
                    #print "Warning: Multiple IDs in Quest measurement"
                    continue

                # Get the actual id
                id = id[0].strip('(').strip(')')

                # Must split the value int two
                temp = tokens[2].split(' ');
                value = temp[0].strip();

                # Search fields 3 and 4 for usernames
                temp1 = username_reg.findall(tokens[3])

                usernames = set();
                for e in temp1:
                    usernames.add(e);
                if (len(usernames)==0):
                    print "Error, no username for Quest measurement"
                    print tokens;
                    print temp1, temp2
                    continue;
                if (len(usernames)>1):
                    print "Error multiple usernames for Quest measurement"
                    print temp1, temp2
                    continue;

                # Actually get the username
                username = usernames.pop();
                submitted_date = tokens[9];

                # Convert submitted date to datetime
                date_ordered = datetime.datetime.strptime(submitted_date, "%m/%d/%Y");
                if (date_ordered <= FIRST_BLOOD_DRAW):
                    round = 1
                elif (date_ordered <= SECOND_BLOOD_DRAW):
                    round = 2
                else:
                    round = 3

                # Get the value for this date and round
                result = self.GetMeasurementByRound('DATE', username, round);
                if (not result is None):

                    # There is data, skip insertion
                    #print "Found round %d for username %s"%(round, username);

                    # Get the associated date
                    db_date = result[0];
                    if (db_date is None):
                        raise MyError('No date for username %s and round %d'%(username, round));

                    # Update data one at a time (yes inefficient, so shoot me)
                    self.UpdateData(username, round, db_date, date_ordered, {id: value}, mapping)

                # There was no row found, so insert a new row!
                else:

                    # Insert new row
                    print "Cannot insert rows from Quest data [%s %s]"%(username, date_ordered)
                    #raise MyError('Cannot insert data from Quest - add Genova first!');


    def LoadGenova(self, filename):

        titles = None
        headers = None

        # Get the headers from the data table
        mapping = self.GetInternalIDMapping()

        with open(filename, 'rU') as f:
            for line in f:

                # Load the headers, removing the final 9 columns
                if (titles is None):
                    titles = line.strip().split('\t')[:-9]

                # Next load the identifiers, removing the final 9 columns
                elif (headers is None):
                    headers = line.strip().split('\t')[:-9]

                # Finally, load the data, removing the final 9 columns
                else:
                    tokens = line.strip().split('\t')[:-9]

                    # Convert into dictionary
                    data = dict(zip([x.strip().upper() for x in headers], [x.strip().upper() for x in tokens]))

                    # Get username from this row
                    username = data["LAST NAME"]

                    print tokens
                    print username

                    # Get date from this row
                    date_ordered = datetime.datetime.strptime(data['DATE ORDERED'], "%m/%d/%y");
                    if (date_ordered <= FIRST_BLOOD_DRAW):
                        round = 1
                    elif (date_ordered <= SECOND_BLOOD_DRAW):
                        round = 2
                    else:
                        round = 3

                    # Get the value for this date and round
                    result = self.GetMeasurementByRound('DATE', username, round);
                    if (not result is None):

                        # There is data, skip insertion
                        print "Found round %d for username %s"%(round, username);

                        # Get the associated date
                        db_date = result[0];
                        if (db_date is None):
                            raise MyError('No date for username %s and round %d'%(username, round));

                        # Update data
                        self.UpdateData(username, round, db_date, date_ordered, data, mapping)

                    # There was no row found, so insert a new row!
                    # TODO: we could check to see if the date already exists as well
                    else:

                        # Insert new row
                        print "Inserting for username %s date %s"%(username, date_ordered)
                        self.InsertData(username, round, date_ordered, data, mapping)

    def CreateDataTable(self):

        # Get the ranges, which will determine what we load in
        ranges = self.LoadRanges();

        command = ""
        command += "CREATE TABLE data5 (ENTRY INT PRIMARY KEY AUTO_INCREMENT, USERNAME VARCHAR(16) NOT NULL, DATE DATETIME, ROUND INT NOT NULL"
        count = 0

        for key in sorted(ranges.keys()):
                command += ", " + key.upper().replace('-','_').replace('/','_').replace(' ', '_').replace('.','_') + " FLOAT"
        command += ")"

        # First create columns
        self.Command(command)





