def get_user_to_club_membership(userid):
c, conn = connection()
c.execute('SELECT * FROM club_membership WHERE user_id=%s', escape_string(str(userid)))
membership = c.fetchall()
c.close()
conn.close()
return membership
def get_event_membership(eventid):
"""Function takes eventid and returns list with users"""
c, conn = connection()
c.execute('SELECT * FROM event_membership WHERE event_id=%s', escape_string(str(eventid)))
eventid = c.fetchall()
c.close()
conn.close()
return eventid
def give_event_ownership(userid, eventid):
'''Function takes userid and eventid and set user as owner of that event'''
c, conn = connection()
c.execute('UPDATE event SET owner_id=%s WHERE idevent=%s',
(escape_string(str(userid)), escape_string(str(eventid))))
conn.commit()
c.close()
conn.close()
def del_user_from_event(userid, eventid):
"""Functions takes user's id and event's id and removes user from that event"""
c, conn = connection()
c.execute('DELETE FROM event_membership WHERE user_id=%s and event_id=%s',
(escape_string(str(userid)), escape_string(str(userid))))
conn.commit()
c.close()
conn.close()
def del_user_from_club(userid, clubid):
"""Functions takes user's id and club's id and removes user from that club"""
c, conn = connection()
c.execute('DELETE FROM club_membership WHERE user_id=%s and club_id=%s',
(escape_string(str(userid)), escape_string(str(userid))))
conn.commit()
c.close()
conn.close()
def give_club_ownership(userid, clubid):
'''Function takes userid and clubid and set user as owner of that club'''
c, conn = connection()
c.execute('UPDATE club SET owner_id=%s WHERE idclub=%s',
(escape_string(str(userid)), escape_string(str(clubid))))
conn.commit()
c.close()
conn.close()
def get_organization_by_name(organizationname):
"""Function takes organization name and returns dict with organization's data"""
c, conn = connection()
c.execute("SELECT * FROM organization WHERE name=%s", escape_string(organizationname))
organization_data = c.fetchone()
c.close()
conn.close()
return organization_data
def change_event_info(eventid, new_info):
'''Function takes eventid and new info and changes specified event info'''
c, conn = connection()
c.execute('UPDATE event SET info=%s WHERE idevent=%s',
(escape_string(new_info), escape_string(str(str(eventid)))))
conn.commit()
c.close()
conn.close()
def change_user_password(userid, new_password):
'''Function takes userid and new password and changes specified user password'''
c, conn = connection()
c.execute('UPDATE user SET password=%s WHERE iduser=%s',
(escape_string(new_password), escape_string(str(str(userid)))))
conn.commit()
c.close()
conn.close()
def give_organization_ownership(userid, organizationid):
'''Function takes userid and organizationid and set user as owner of that organization'''
c, conn = connection()
c.execute('UPDATE organization SET owner_id=%s WHERE idorganization=%s',
(escape_string(str(userid)), escape_string(str(organizationid))))
conn.commit()
c.close()
conn.close()
def give_admin(userid):
'''Function takes userid and gives him all priviliges'''
c, conn = connection()
c.execute('UPDATE user SET admin=1 WHERE iduser=%s',
(escape_string(str(userid))))
conn.commit()
c.close()
conn.close()
def confirm_email(mail):
'''Function confirms user's mail'''
c, conn = connection()
c.execute('UPDATE user SET email_confirm=1 WHERE email=%s',
escape_string(str(mail)))
conn.commit()
c.close()
conn.close()
def get_event(eventname):
"""Functions takes event name and returns event data"""
c, conn = connection()
c.execute("SELECT * FROM event WHERE name=%s", escape_string(str(eventname)))
event_data = c.fetchone()
c.close()
conn.close()
return event_data
def get_club(clubname):
"""Function takes club name and returns club data"""
c, conn = connection()
c.execute("SELECT * FROM club WHERE name=%s", escape_string(str(clubname)))
club_data = c.fetchone()
c.close()
conn.close()
return club_data
def get_organization_by_id(organizationid):
"""Function takes organization id and returns dict with organization's data"""
c, conn = connection()
c.execute("SELECT * FROM organization WHERE idorganization=%s", escape_string(str(organizationid)))
organization_data = c.fetchone()
c.close()
conn.close()
return organization_data
def change_event_date(eventid, new_date):
'''Function takes eventid and new date and changes specified event date'''
c, conn = connection()
c.execute('UPDATE event SET date=%s WHERE idevent=%s',
(escape_string(new_date), escape_string(str(str(eventid)))))
conn.commit()
c.close()
conn.close()
def change_organization_contact(organizationid, new_contact):
'''Function takes organizationid and new contact and changes specified organization contact'''
c, conn = connection()
c.execute(
'UPDATE organization SET contact=%s WHERE idorganization=%s',
(escape_string(new_contact), escape_string(str(str(organizationid)))))
conn.commit()
c.close()
conn.close()
def get_further_events(userid):
""" """
c, conn = connection()
today = datetime.today()
c.execute('SELECT idevent FROM event WHERE date>=%s', (today))
event_data = c.fetchall()
c.close()
conn.close()
return event_data
def change_mail(userid, new_mail):
'''Function takes userid and new mail and changes specified user mail'''
c, conn = connection()
c.execute('UPDATE user SET email=%s WHERE iduser=%s',
(escape_string(new_mail), escape_string(str(userid))))
c.execute('UPDATE user SET email_confirm=0 WHERE iduser=%s',
escape_string(str(userid)))
conn.commit()
c.close()
conn.close()
def create_event_membership(userid, eventid):
"""Function takes user id, event id and club which is owner of the event and assigns user to event"""
c, conn = connection()
c.execute('INSERT INTO event_membership (user_id, event_id) VALUES'
'(%s, %s)'
, (escape_string(str(userid)), escape_string(str(eventid)))
)
conn.commit()
c.close()
conn.close()
def create_club_membership(userid, clubid):
"""Function takes user id, club id and assigns user to that club"""
c, conn = connection()
c.execute('INSERT INTO club_membership (user_id, club_id) VALUES'
'(%s, %s)'
, (escape_string(str(userid)), escape_string(str(clubid)))
)
conn.commit()
c.close()
conn.close()
def get_club_by_organization(organizationid):
c, conn = connection()
c.execute("SELECT name FROM club WHERE organization_id=%s", escape_string(str(organizationid)))
club_data = c.fetchall()
c.close()
conn.close()
club_name_list = []
for name in club_data:
club_name_list.append(name['name'])
return club_name_list
def del_user(userid):
"""Function takes user's id and deletes it from database
tip: check dependencies before deleting
"""
c, conn = connection()
c.execute('DELETE FROM user WHERE iduser=%s', (escape_string(str(userid))))
conn.commit()
c.close()
conn.close()
def del_club(clubid):
"""Function takes club's id and deletes it from database
tip: check dependencies before deleting
"""
c, conn = connection()
c.execute('DELETE FROM club WHERE idclub=%s', (escape_string(str(clubid))))
conn.commit()
c.close()
conn.close()
def get_club_by_user(userid):
"""Functions takes user id and returns list with id's of clubs which user belong to"""
c, conn = connection()
c.execute("SELECT * FROM club_membership WHERE user_id=%s", escape_string(str(userid)))
club_data = c.fetchall()
c.close()
conn.close()
club_id_list = []
for clid in club_data:
club_id_list.append(clid['club_id'])
return club_id_list
def get_events_by_user(userid):
"""Functions takes user id and returns list with id's of events which user take part in"""
c, conn = connection()
c.execute("SELECT * FROM event_membership WHERE user_id=%s", escape_string(str(userid)))
event_data = c.fetchall()
c.close()
conn.close()
event_id_list = []
for evid in event_data:
event_id_list.append(evid['event_id'])
return event_id_list
def get_club_membership(clubid):
"""Function takes clubid and returns list of id's of users which belong to that club"""
c, conn = connection()
c.execute('SELECT * FROM club_membership WHERE club_id=%s', escape_string(str(clubid)))
users = c.fetchall()
c.close()
conn.close()
users_list = []
for user in users:
users_list.append(user["user_id"])
return users_list
def get_all_organizations():
'''Function returns all existing organizations'''
c, conn = connection()
c.execute("SELECT name FROM organization")
organization_data = c.fetchall()
c.close()
conn.close()
organization_list = []
for name in organization_data:
organization_list.append(name["name"])
return organization_list
def del_event(eventid):
"""Function takes event's id and deletes it from database
tip: check dependencies before deleting
"""
c, conn = connection()
c.execute('DELETE FROM event WHERE idevent=%s',
(escape_string(str(eventid))))
conn.commit()
c.close()
conn.close()
def get_events_by_club(clubid):
"""Functions takes club id and returns list with names of events which belong to that club"""
c, conn = connection()
c.execute("SELECT name FROM event WHERE club_id=%s", escape_string(str(clubid)))
event_data = c.fetchall()
c.close()
conn.close()
event_name_list = []
for name in event_data:
event_name_list.append(name['name'])
return event_name_list
