def get_cid(uid, place_id, lat, long, types):
d = Database()
#associate_event = database_getter.has_event_type(uid, lat, long, types)
if database_getter.is_school_event(uid, lat, long):
query = f'''SELECT `cid` FROM `cluster`
WHERE `uid` = "{uid}" AND `event_type` = "school"'''
d.cursor.execute(query)
return next(iter(d.cursor))[0]
type_list = []
for t in types:
type_list.append(f'(`dominant_types` LIKE "%{t}%")')
type_str = ' OR '.join(type_list)
query = f'''SELECT `cid` FROM `cluster`
WHERE `uid` = "{uid}" AND
(ABS( ABS({lat}-`center_lat`) - `ave_dist_to_center_lat`)
+ ABS( ABS({long}-`center_long`) - `ave_dist_to_center_long`))
< {theta.CLOSE_LAT_LONG} AND ({type_str})
'''
#print(query)
d.cursor.execute(query)
try:
return next(iter(d.cursor))[0]
except StopIteration:
query = f'''SELECT `cid`,`dominant_types` FROM `cluster`
WHERE `uid` = "{uid}" AND ({type_str})
'''
#print(query)
d.cursor.execute(query)
for cid, t in d.cursor:
t_list = t.split(",")
if len(set(t_list) & set(types)) >= 3:
return cid
return None
def get_location_with_event_as_list(uid, event):
d = Database()
query = '''SELECT `place_id` FROM `location index`
WHERE `uid` = "{}" AND `associate_event` = "{}"'''.format(uid, event)
d.cursor.execute(query)
result = [id[0] for id in d.cursor]
return result
def get_location_index_as_list(uid):
d = Database()
query = '''SELECT `place_id` FROM `location index`
WHERE `uid` = "{}"'''.format(uid)
d.cursor.execute(query)
result = [id[0] for id in d.cursor]
return result
def get_user_current_location(uid):
d = Database()
query = f'''SELECT `current_location_lat`,`current_location_long`
FROM `active data`
WHERE `uid` = "{uid}"'''
d.cursor.execute(query)
return next(iter(d.cursor))
def update_location_index(uid, place_id, address, location_name, lat, long, stayed_time):
d = Database()
query = '''SELECT COUNT(*) FROM `location index`
WHERE `uid` = "{}" AND `place_id` = "{}"'''.format(uid, place_id)
d.cursor.execute(query)
if int(next(iter(d.cursor))[0]) == 0:
types = ",".join(YelpAPI.get_categories_from_name_and_address(\
name=location_name,address=address))
if types != "":
types += "," + ",".join(FindPlaces.get_place_types(place_id))
else:
types += ",".join(FindPlaces.get_place_types(place_id))
event_type = has_event_type(uid,lat,long,types)
query = '''INSERT IGNORE INTO `location index`(
`uid`, `place_id`, `location_name`, `address`, `types`, `lat`, `long`,
`ave_staying_time`, `total_freq`, `associate_event`)
VALUES ("{}","{}","{}","{}","{}",{},{},{},{},"{}")'''.format(\
uid, place_id, location_name, address.replace('\n',' '), \
types, lat, long, stayed_time, 1, event_type)
d.cursor.execute(query)
d.db.commit()
else:
print("location already in index")
query = '''UPDATE `location index`
SET `ave_staying_time` = ({}+`total_freq`*`ave_staying_time`)/(`total_freq`+1), `total_freq`=`total_freq` + 1
WHERE `place_id` = "{}" AND `uid` = "{}"'''.format(stayed_time, place_id, uid)
d.cursor.execute(query)
d.db.commit()
def is_in_trip(uid):
d = Database()
query = f'''SELECT `in_trip` FROM `active data`
WHERE `uid` = "{uid}"'''
d.cursor.execute(query)
try:
return int(next(iter(d.cursor))[0]) != 0
except StopIteration:
return None
def get_all_clusters(uid):
d = Database()
query = f'''SELECT * FROM `cluster`
WHERE `uid` = "{uid}"'''
d.cursor.execute(query)
clusters = []
for c in d.cursor:
clusters.append(c)
return clusters
def get_user_trip_destination(uid):
d = Database()
query = f'''SELECT `destination` FROM `active data`
WHERE `uid` = "{uid}"'''
d.cursor.execute(query)
try:
return next(iter(d.cursor))[0]
except StopIteration:
return None
def is_past_location(place_id):
d = Database()
query = '''SELECT `location_name` FROM `location index`
WHERE `place_id` = "{}"'''.format(place_id)
d.cursor.execute(query)
try:
return str(next(iter(d.cursor))[0])
except StopIteration:
return None
def update_behavior_pattern(uid, start_time, event, col_str):
d = Database()
query = '''UPDATE `behavior pattern`
SET {}last_time="{}"
WHERE `uid` = "{}" AND `event`="{}"
'''.format(col_str, start_time, uid, event)
print(query)
d.cursor.execute(query)
d.db.commit()
def get_user_current_step(uid):
d = Database()
query = f'''SELECT `current_step` FROM `active data`
WHERE `uid` = "{uid}"'''
d.cursor.execute(query)
try:
return int(next(iter(d.cursor))[0])
except StopIteration:
return None
def get_location_info(uid, place_id):
d = Database()
query = '''SELECT `location_name`,`address`,`lat`,`long`,`types`,
`ave_staying_time`,`total_freq`,`associate_event`
FROM `location index`
WHERE `uid`="{}" AND `place_id` = "{}"'''.format(uid, place_id)
d.cursor.execute(query)
# location name 0 address 1 lat,long (2,3) types 4
#ave_staying_time 5, total_freq 6, associate_event 7
return next(iter(d.cursor))
def contains_visited_places(uid, id_list):
'''returns the number of id in id_list that has been visited before'''
d = Database()
result = []
for id in id_list:
query = '''SELECT COUNT(*) FROM `location index`
WHERE `place_id` = "{}"'''.format(id)
d.cursor.execute(query)
if int(next(iter(d.cursor))[0]) >= 1:
result.append(id)
return result
def add_to_db(r):
d = Database()
query = '''INSERT IGNORE INTO `recommendation`(
uid,type,start_time,duration,place_id,place_name,
place_address,place_types,steps)
VALUES ("{0}", {1}, "{2}", {3}, "{4}", "{5}", "{6}", "{7}", {8})
'''.format(r.uid, r.type, r.start_time, r.duration,\
r.place_id, r.place_name, r.place_address, \
",".join(r.place_types), r.steps)
d.cursor.execute(query)
d.db.commit()
def store_to_database(self, cid):
d = Database()
query = '''INSERT IGNORE INTO `cluster`(`uid`,`cid`,`members`,
`center_lat`,`center_long`,`ave_dist_to_center_lat`,`ave_dist_to_center_long`,
`dominant_types`,`ave_staying_time`,`frequency`,`event_type`)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
'''
val = (self.uid, cid, ",".join(self.members), \
self.center[0], self.center[1], self.ave_dist_to_center[0], self.ave_dist_to_center[1],
",".join(self.dominant_types), self.ave_staying_time, self.frequency, self.event_type)
d.cursor.execute(query, val)
d.db.commit()
def _get_bp_as_array(uid, event_type):
d = Database()
col_str = []
for hour in range(24):
col_str.append(f"`{hour}_{hour+1}_freq`")
query = '''SELECT {} FROM `behavior pattern`
WHERE `uid` = "{}" AND `event` = "{}"'''.format(",".join(col_str),\
uid,event_type)
d.cursor.execute(query)
array = []
for row in d.cursor:
for number in row:
array.append(number)
return array
def _filter_events_by_times(uid, events, current_time):
exclude = []
for e in events:
d = Database()
query = f'''SELECT `happened_times`,`last_time`,`normal_frequency`
FROM `behavior pattern` WHERE `uid` = "{uid}" AND `event` = "{e}"'''
d.cursor.execute(query)
happened_times, last_time, normal_frequency = next(iter(d.cursor))
if e == 'meal':
if int(happened_times) >= 3:
exclude.append(e)
elif e == 'coffee':
if int(happened_times) >= 1:
exclude.append(e)
elif ((current_time - last_time).total_seconds()/3600) \
< normal_frequency*0.5:
exclude.append(e)
return [e for e in events if e not in exclude]
def update_location_frequency(uid, start_time, end_time, place_id):
d = Database()
query = '''
INSERT INTO `location frequency`(uid,place_id{0})
VALUES ("{1}","{2}"{3})
ON DUPLICATE KEY UPDATE '''
col_str = val_str = update_str = ""
bp_col_str = ""
end_time_hour = end_time.hour + 1 if start_time.hour <= end_time.hour else end_time.hour+25
for h in range(start_time.hour, end_time_hour):
h = h % 24
col_str+=",{0}_{1}_freq, {0}_{1}_recent_time".format(h,h+1)
val_str+=",1,\"{}\"".format(start_time)
update_str+="{0}_{1}_freq = {0}_{1}_freq + 1, {0}_{1}_recent_time = \"{2}\",".format(h,h+1,start_time)
bp_col_str+="{0}_{1}_freq = {0}_{1}_freq + 1,".format(h,h+1)
update_str = update_str.rstrip(',')
query = query.format(col_str,uid,place_id,val_str) + update_str
#print(query)
d.cursor.execute(query)
d.db.commit()
event_type = database_getter.get_event_type(place_id)
if event_type != "none":
update_behavior_pattern(uid, start_time, event_type, bp_col_str)
def update_location_history(json_file,uid):
with open(json_file) as read_file:
data = json.load(read_file)
d = Database()
for tl in data['timelineObjects']:
if 'placeVisit' in tl:
try:
start_timestamp = round(int(tl['placeVisit']['duration']['startTimestampMs'])/1000)
end_timestamp = round(int(tl['placeVisit']['duration']['endTimestampMs'])/1000)
start_datetime = datetime.fromtimestamp(start_timestamp)
end_datetime = datetime.fromtimestamp(end_timestamp)
stayed_time = round((end_datetime - start_datetime).total_seconds()/60)
if 'childVisits' in tl['placeVisit']:
place = tl['placeVisit']['childVisits'][0]['location']
else:
place = tl['placeVisit']['location']
address = place['address']
location_name = place['name']
place_id = place['placeId']
lat = place['latitudeE7']/1e7
long = place['longitudeE7']/1e7
query = '''SELECT COUNT(*) FROM `location history`
WHERE `uid` = "{}" AND `start_time` = "{}"'''.format(uid,start_datetime)
d.cursor.execute(query)
if int(next(iter(d.cursor))[0]) == 0:
print("{} at {} is new info".format(location_name, start_datetime))
update_location_index(uid, place_id, address, location_name, lat, long, stayed_time)
update_location_frequency(uid, start_datetime, end_datetime, place_id)
query = '''
INSERT IGNORE INTO `location history`(uid, start_time, end_time, place_id,stayed_time)
VALUES (%s, %s, %s, %s, %s)'''
val = (uid, start_datetime, end_datetime, place_id,stayed_time)
d.cursor.execute(query,val)
d.db.commit()
except KeyError:
print("some bad location happened")
pass
def is_in_address_type(type):
d = Database()
query = '''SELECT COUNT(*) FROM `address type`
WHERE`type`="{}"'''.format(type)
d.cursor.execute(query)
return int(next(iter(d.cursor))[0]) > 0
def get_user_work_address(uid):
d = Database()
query = '''SELECT `work_address` FROM `user` WHERE `uid` = "{}"'''.format(
uid)
d.cursor.execute(query)
return str(next(iter(d.cursor))[0])
def get_staying_time(uid, cid):
d = Database()
query = f'''SELECT `ave_staying_time` FROM `cluster`
WHERE `uid` = "{uid}" AND `cid` = "{cid}"'''
d.cursor.execute(query)
return float(next(iter(d.cursor))[0])
def get_user_goal(uid):
d = Database()
d.cursor.execute(
'''SELECT `goal` FROM `user` WHERE `uid` = "{}" LIMIT 1'''.format(uid))
return int(next(iter(d.cursor))[0])
def get_event_type(place_id):
d = Database()
query = '''SELECT `associate_event` FROM `location index`
WHERE `place_id` = "{}"'''.format(place_id)
d.cursor.execute(query)
return str(next(iter(d.cursor))[0])
import sys
sys.path.append('./')
from DataConnecter import Database
d = Database()
#
# query = '''Create TABLE IF NOT EXISTS `behavior pattern` (
# `uid` VARCHAR(2),
# `place_id` VARCHAR(100),
# `happened_times` INT,
# `last_time` DATETIME,
# '''
# for hour in range(24):
# query+=" {}_{}_freq INT NOT NULL DEFAULT 0,\n".format(hour,hour+1)
#
# query+='''PRIMARY KEY(`uid`,`place_id`),
# FOREIGN KEY(`uid`) REFERENCES User(`uid`) ON DELETE CASCADE
# );
# '''
# print(query)
# d.cursor.execute(query)
# d.db.commit()
query = '''INSERT IGNORE INTO `behavior pattern`(uid,event)
VALUES("1","{}")'''
for event in ['gym', 'meal', 'work', 'home', 'school', 'coffee', 'grocery']:
d.cursor.execute(query.format(event))
d.db.commit()