def add_event(event: EventModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "INSERT INTO event " \
"(title, tags, eventdate, description, image, location, expiretime, city) " \
"VALUES " \
"(%s, %s, %s, %s, %s, %s, %s, %s)"
val = (event.title, event.tags, event.event_date, event.description,
event.image, event.location, event.expire_date, event.address)
try:
cursor.execute(sql, val)
connector.commit()
handled = True
except mysql.connector.errors.IntegrityError as err:
print(err.msg)
handled = True
return Errors.DUPLICATE.name
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
cursor.execute("SELECT @@identity")
for x in cursor:
event_id = x[0]
return event_id
def edit_event(event: EventModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "UPDATE event " \
"SET title = %s, tags = %s, eventdate = %s, description = %s, " \
"image = %s, location = %s, expiretime = %s, city = %s" \
"WHERE eventid = %s"
val = (event.title, event.tags, event.event_date, event.description,
event.image, event.location, event.expire_date, event.address,
event.eid)
try:
cursor.execute(sql, val)
connector.commit()
if cursor.rowcount == 0:
handled = True
return Errors.MISSING.name
else:
handled = True
return Errors.SUCCESS.name
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def retrieve_event(event_id: str):
handled = False
connector = SqlController().sql_connector
cursor = connector.cursor()
sql = "SELECT * " \
"FROM event " \
"WHERE eventid = %s"
val = [int(event_id)]
try:
cursor.execute(sql, val)
event_info = cursor.fetchone()
if not event_info:
handled = True
return Errors.MISSING.name
else:
handled = True
return_event = decode_string_event(str(event_info))
return_event.hosts = get_host(return_event.eid)
return_event.attendees = get_join(return_event.eid)
return return_event
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def remove_user(user: UserModel, event: EventModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
sql = "DELETE FROM joins WHERE joinid = %s and eventid = %s"
val = (user.uid, event.eid)
try:
cursor.execute(sql, val)
connector.commit()
return cursor.rowcount
finally:
connector.rollback()
return Errors.FAILURE.name
def expire():
connector = SqlController().sql_connector
cursor = connector.cursor()
today = datetime.date.today()
cur_year = str(today.year)
cur_month = str(today.month).zfill(2)
cur_day = str(today.day).zfill(2)
expire_date = cur_year + '-' + cur_month + '-' + cur_day
sql = "DELETE FROM event WHERE expiretime < %s"
val = [expire_date]
try:
cursor.execute(sql, val)
connector.commit()
return cursor.rowcount
finally:
connector.rollback()
return Errors.FAILURE.name
def get_host(event_id: str):
connector = SqlController().sql_connector
cursor = connector.cursor()
got = False
sql = 'SELECT HostID '\
'FROM Host ' \
'WHERE EventID = %s'
val = [event_id]
try:
cursor.execute(sql, val)
host = cursor.fetchone()
got = True
return str(host[0])
finally:
if not got:
return None
def remove_user(data):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "DELETE FROM JoinTable WHERE JoinID = %s and EventID = %s"
val = (data['uid'], data['eid'])
try:
cursor.execute(sql, val)
connector.commit()
handled = True
if cursor.rowcount == 0:
return Errors.MISSING.name
else:
return data['uid'], data['eid']
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def remove_user(user_id: str, event: EventModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "DELETE FROM JoinTable WHERE JoinID = %s and EventID = %s"
val = (user_id, event.eid)
try:
cursor.execute(sql, val)
connector.commit()
handled = True
if cursor.rowcount == 0:
return Errors.MISSING.name
else:
return user_id, event.eid
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def get_events(data):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
time = data['time']
clauses = []
time_clause = "expiretime > \'" + datetime.datetime.today().strftime(
'%Y-%m-%d') + "\'"
if time:
time_clause = "(" + time_clause + " AND DATEDIFF(expiretime, \'" + \
datetime.datetime.today().strftime('%Y-%m-%d') + "\') < " + str(time) + ")"
clauses.append(time_clause)
state = data['state']
if state:
state_clause = 'location = \'' + state + "\'"
clauses.append(state_clause)
tag = data['tag']
if tag:
tag_clause = "tags = \'" + tag + "\'"
clauses.append(tag_clause)
keyword = data['keyword']
if keyword:
keyword_clause = "(description LIKE \'%" + keyword + "%\'" + " OR " + "title LIKE \'%" + keyword + "%\')"
clauses.append(keyword_clause)
query = ''
for clause in clauses:
query = query + clause + ' AND '
query = query[:-5]
sql = 'SELECT eventid, title, location, expiretime, image ' \
'FROM event ' \
'WHERE ' + query
try:
cursor.execute(sql)
result_list = cursor.fetchall()
handled = True
return Errors.SUCCESS.name, result_list
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name, None
def get_join(event_id: str):
connector = SqlController().sql_connector
cursor = connector.cursor()
got = False
result = []
sql = 'SELECT JoinID '\
'FROM JoinTable ' \
'WHERE EventID = %s'
val = [event_id]
try:
cursor.execute(sql, val)
join = cursor.fetchall()
for attendee in join:
result.append(str(attendee[0]))
got = True
return result
finally:
if not got:
return []
def join_event(data):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "INSERT INTO JoinTable (JoinID, EventID)" \
"VALUES (%s, %s)"
val = (data['uid'], data['eid'])
try:
cursor.execute(sql, val)
connector.commit()
handled = True
return 'OK'
except mysql.connector.errors.IntegrityError as err:
print(err.msg)
handled = True
return Errors.DUPLICATE.name
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def get_hosted_event(user: UserModel):
events = []
connector = SqlController().sql_connector
cursor = connector.cursor()
sql = "SELECT eventid " \
"FROM host "\
"WHERE hostid = %s"
val = [user.uid]
try:
cursor.execute(sql, val)
event_list = cursor.fetchone()
if not event_list:
return events
for result in event_list:
events.append(result[0])
return events
except mysql.connector.errors as err:
print(err.msg)
return Errors.FAILURE.name
finally:
connector.rollback()
return Errors.FAILURE.name
def host_event(uid, event: EventModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "INSERT INTO Host (hostid, eventid)" \
"VALUES(%s, %s)"
val = (uid, event.eid)
try:
cursor.execute(sql, val)
connector.commit()
handled = True
return event.eid, uid
except mysql.connector.errors.IntegrityError as err:
if not handled:
print(err.msg)
handled = True
return Errors.DUPLICATE.name
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def join_event(user: UserModel, event: EventModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "INSERT INTO JoinTable (JoinID, EventID)" \
"VALUES (%s, %s)"
val = (user.uid, event.eid)
try:
cursor.execute(sql, val)
connector.commit()
event.attendees.append(user.uid)
user.join_events.append(event.eid)
handled = True
return user.uid, event.eid
except mysql.connector.errors.IntegrityError as err:
print(err.msg)
handled = True
return Errors.DUPLICATE.name
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def host_event(user: UserModel, event: EventModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "INSERT INTO host (hostid, eventid)" \
"VALUES(%s, %s)"
val = (user.uid, event.eid)
try:
cursor.execute(sql, val)
connector.commit()
event.hosts.append(user.uid)
user.host_events.append(event.eid)
return user.uid, event.eid
except mysql.connector.errors.IntegrityError as err:
print(err.msg)
handled = True
finally:
connector.rollback()
if not handled:
return Errors.FAILURE.name
else:
return Errors.DUPLICATE.name