def get(self):
"""Get mail blast."""
db = connect_to_cloudsql()
cursor = db.cursor()
cursor.execute('SELECT username, password, email, fname, \
lname, dob, timezone, email_verified FROM '
+ ENV_DB + '.Users')
rows = cursor.fetchall()
for row in rows:
user = User(*row)
rec = recommender.Recommend(user)
events = rec.get_events()
interests = set()
for e in events:
interests.add(e[-3])
for (event_index, e) in enumerate(events):
events[event_index] = helper_strip_date(e)
formatted_event_email(user.email, list(interests), events)
# event_string = ''
# for eid, ename, desc, start_date, end_date, num_cap, num_attending, lname, add, tag, lat, lon in events:
# if (desc is None):
# desc = ''
# event_string += "{}, {} to {}, {}/{} filled\n{}\n\n".format(ename, start_date, end_date, num_attending, num_cap, desc)
# print(event_string)
# body = 'Hey {},\n\nHere are some upcoming events we think you might be interested in:\n\n\n{}'.format(user.fname, event_string)
# print(user.email)
# send_events_email(user.email, body)
return ({}, 200)
def get_events(self):
"""Gets all events based on username.
:return: List of data.
"""
database = main.connect_to_cloudsql()
cursor = database.cursor()
query = """
SELECT DISTINCT E.eid, E1.ename, E1.description,
E.category, E1.start_date, E1.end_date, E1.num_cap,
E1.num_attending, L.lname, L.address_1, E.tag, L.lat, L.lon
FROM {}.EventTags AS E, {}.UserTags AS U, {}.Events as E1, {}.Locations as L
WHERE U.username='******' AND
E.tag = U.tag AND
E1.eid = E.eid AND
E1.lid = L.lid AND
E1.start_date >= {}
ORDER by E1.start_date
""".format(ENV_DB, ENV_DB, ENV_DB, ENV_DB, self.user.username,
str(datetime.date.today()))
cursor.execute(query)
data = cursor.fetchall()
database.close()
return [i for i in data]
def test_connect_to_cloudsql(self):
db = main.connect_to_cloudsql()
cursor = db.cursor()
cursor.execute("SELECT DATABASE();")
data = cursor.fetchone()
db.close()
assert data == ('Dev', )
def get_events(self):
"""Gets events for common tags.
:return: List of events with common tags for each group.
"""
database = main.connect_to_cloudsql()
cursor = database.cursor()
result = []
for tag in self.interests:
query = """
SELECT DISTINCT E.eid, E1.ename, E1.description,
E.category, E1.start_date, E1.end_date, E1.num_cap,
E1.num_attending, L.lname, L.address_1, E.tag, L.lat, L.lon
FROM {}.EventTags AS E, {}.UserTags AS U, {}.Events as E1, {}.Locations as L
WHERE E.tag = '{}' AND
E1.eid = E.eid AND
E1.lid = L.lid AND
E1.start_date > {}
ORDER by E1.start_date
""".format(ENV_DB, ENV_DB, ENV_DB, ENV_DB, tag,
str(datetime.date.today()))
cursor.execute(query)
data = cursor.fetchall()
result.extend([i for i in data])
database.close()
return result
def teardown_new_user():
database = main.connect_to_cloudsql()
cursor = database.cursor()
query = "DELETE FROM Users WHERE username = '******'"
cursor.execute(query)
database.commit()
database.close()
def get_user_interests_with_categories(self):
"""Gets user interests from categories.
:return: list of user interests.
"""
database = main.connect_to_cloudsql()
cursor = database.cursor()
cursor.execute("SELECT tag, category FROM " + ENV_DB +
".UserTags WHERE username='******'")
data = cursor.fetchall()
database.close()
return list((i[0], i[1]) for i in data)
def get_interests_each_member(self, username):
"""Gets interests of each individual member of the group.
:param username: String username.
:return: Set of interests for each member.
"""
database = main.connect_to_cloudsql()
cursor = database.cursor()
cursor.execute("SELECT tag FROM " + ENV_DB +
".UserTags WHERE username='******'")
data = cursor.fetchall()
database.close()
return set([i[0] for i in data])
def get_members(self):
"""Gets members of the group.
:return: List of members.
"""
database = main.connect_to_cloudsql()
cursor = database.cursor()
query = ("SELECT username from " + ENV_DB +
".Groups WHERE gid='{}'").format(self.g_id)
cursor.execute(query)
data = cursor.fetchall()
database.close()
return list(i[0] for i in data)
def get_user_interests(self):
"""Gets user interests from tags.
:return: None.
"""
database = main.connect_to_cloudsql()
cursor = database.cursor()
cursor.execute("SELECT tag FROM " + ENV_DB +
".UserTags WHERE username='******'")
data = cursor.fetchall()
database.close()
self.most_interested = sorted([i[0] for i in data])
return self.most_interested
def test_register_user_function(self):
try:
my_user = User(VALID_UNUSED_UNAME, VALID_UNUSED_PSSWD)
main.register_user(my_user)
database = main.connect_to_cloudsql()
cursor = database.cursor()
query = "SELECT username, password FROM Users WHERE username = '******' AND password = '******'"
cursor.execute(query)
data = cursor.fetchone()
assert data[0] == VALID_UNUSED_UNAME
assert data[1] == VALID_UNUSED_PSSWD
teardown_new_user()
except:
# safely assume connect_to_cloudsql works, since
# otherwise the insert would have failed
teardown_new_user()
raise