def save_event(user_id, args, event_type): params_map = type_to_params[event_type] table = type_to_tables[event_type] fields_string = " (user_id," values_string = '('+str(user_id) + ',' for param in args.keys(): val = args[param] if (param in time_params): time_object = datetime.datetime.strptime(val, incoming_time_format) val = time_object.strftime(server_time_format) if param in encrypt_params: pub_key = user_id val = encrypt(val) values_string = values_string + "\'"+val+"\'," fields_string = fields_string + params_map[param]+"," fields_string = fields_string[0:len(fields_string)-1] + ") " values_string = values_string[0:len(values_string)-1] + ")" query = "INSERT IGNORE INTO " + table + fields_string + "VALUES " + values_string if (FB.safe_execute(query)): save_event_summary(user_id, event_type, table, args) else: print "could not save event summary" return False
def is_user_admin(user_id): query = "SELECT `admin` FROM feel_user WHERE `id` = {0}".format(user_id) if(FB.safe_execute(query)): result = FB.cursor.fetchone() if result[0] == 1: return True else: return False
def save_event_rating(event_type, event_id, rating_dict): stress= rating_dict['stress'] recall = rating_dict['recall'] if stress == 'null': stress = 0 if recall == 'null': recall = 0 query = """UPDATE feel_event_summary SET stress_rating ={0}, recall_rating={1} WHERE event_id={2} AND event_type={3}""".format(int(stress), int(recall), event_id, event_type) return FB.safe_execute(query)
def get_basic_grid_data(user_id, page, limit, sidx, sord): query = "SELECT COUNT(*) FROM feel_event_summary WHERE user_id='{}'".format(user_id) if(FB.safe_execute(query)): count = FB.cursor.fetchone()[0] if count > 0: total_pages = math.ceil(count/limit) else: total_pages = 0 if page > total_pages: page = total_pages start = limit*page - limit if start < 0: start = 0 query = """SELECT event_id, user_id, event_type, event_time, memo FROM feel_event_summary WHERE user_id = {0} ORDER BY {1} {2} LIMIT {3}, {4}""".format(user_id, sidx, sord, int(start), int(limit)) if(FB.safe_execute(query)): result = FB.cursor.fetchall() if len(result) > 0: rows = [] id = 0 for row in result: event_id = int(row[0]) event_type = db2user_event_types[row[2]] event_time = row[3].strftime(iso8601_format) memo = row[4] row = { "id": id, "cell":[event_type, event_time, memo, event_id] } rows.append(row) id = id + 1 response = {"page":int(page), "records":limit,"total":total_pages,"events":rows} return response return None return None
def save_eda_data(user_id, start_time, end_time, sample_rate, eda, hand_side, temperature, acc_x, acc_y, acc_z): import pytz start_time_string = start_time.astimezone(pytz.utc).strftime(server_time_format) end_time_string = end_time.astimezone(pytz.utc).strftime(server_time_format) query = """INSERT IGNORE INTO feel_eda (`user_id`, `start_time`, `end_time`, `sampling_rate`, `eda`,`hand_side`, `temperature`, `acc_x`, `acc_y`, `acc_z`) VALUES ('{0}','{1}','{2}','{3}', '{4}','{5}','{6}','{7}','{8}', '{9}')""".format(user_id, start_time_string, end_time_string, sample_rate, eda, hand_side, temperature, acc_x, acc_y, acc_z) return FB.safe_execute(query)
def save_event_summary(user_id, event_type, table, args): fetch_id_query = "SELECT LAST_INSERT_ID()" if(FB.safe_execute(fetch_id_query)): result = FB.cursor.fetchone() event_id = result[0] fetch_query = "SELECT * FROM "+table+" WHERE id={0}".format(event_id) if(FB.safe_execute(fetch_query)): result = FB.cursor.fetchone() event_id = result[0] if event_type == 1: event_time = args['viewStart'] memo = args['from']+" - "+args['subject'] elif event_type == 3: event_time = args['startTime'] memo = args['phoneNumber'] elif event_type == 2: event_time = args['startTime'] memo = args['title'] summary_query = """INSERT IGNORE INTO feel_event_summary (event_id, user_id, event_type, event_time, memo) VALUES ({0},{1},{2},'{3}','{4}')""".format(event_id, user_id, event_type, event_time, memo) return FB.safe_execute(summary_query)
def get_eda_data_for_event(user_id, event_type, event_id, hand_side): event_type = user2db_event_types[event_type] table = type_to_tables[event_type] if event_type == 2 or event_type == 3: start = 'start_time' end = 'end_time' else: start = 'view_start' end = 'view_end' query = "SELECT `{0}`, `{1}` FROM `{2}` WHERE `id`={3}".format(start, end, table, event_id) if(safe_execute(query)): result = FB.cursor.fetchone() start_time = result[0] end_time = (result[1] +datetime.timedelta(minutes=10)) return get_eda_data(user_id, start_time, end_time, hand_side) return None
def get_eda_data(user_id, start_time, end_time, hand_side = 'RIGHT'): start = start_time.strftime(server_time_format) end = end_time.strftime(server_time_format) query = """SELECT `start_time`, `end_time`, `sampling_rate`, `{0}`, `{1}`,`{2}`,`{3}`,`{4}` FROM feel_eda WHERE `user_id` = {5} AND `hand_side`='{6}' AND start_time BETWEEN '{7}' AND '{8}' """.format('eda','temperature','acc_z','acc_y','acc_x', user_id, hand_side, start, end) if(FB.safe_execute(query)): result = FB.cursor.fetchall() eda_reads, temp_reads, acc_z_reads, acc_y_reads, acc_x_reads = [],[],[],[],[] response = {'eda':eda_reads, 'temp':temp_reads, 'acc_z':acc_z_reads, 'acc_y':acc_y_reads, 'acc_x':acc_x_reads} row_num = 0 row_end = start_time while(start_time < end_time): try: row = result[row_num] # results of reads are in row[3:7] row_data = {'eda':row[3],'temp':row[4],'acc_z':row[5],'acc_y':row[6], 'acc_x':row[7]} sampling_rate = row[2] row_start = row[0] if(start_time < row_start): fill_gap(response, row_start, start_time, sampling_rate) start_time = row_start continue row_end = row[1] if (row_end > end_time): fill_end(response, row_data, row_start, row_end, end_time, sampling_rate) break extend_response(response, row_data) start_time = row_end row_num = row_num + 1 except IndexError: # no more row data, fill the end gap from last row_end reading till end_time if not vars().has_key('sampling_rate'): # if there was no data at all, sampling rate is not defined. sampling_rate = 8 fill_gap(response, row_end, end_time, sampling_rate) break rev_response = revise_response(response, user_id) return rev_response