def get_basicmember(self, key):
with dbapi2.connect(self.app.config['dsn']) as connection:
cursor = connection.cursor()
query = "SELECT NAME, SURNAME, USERNAME, GENDER, MEMBERTYPE,EMAIL, PASSWORD, CITY, INTERESTS,SCORE,YEAR, LASTLOGIN, REGTIME, ROLE FROM MEMBERS WHERE (MEMBERID = %s)"
cursor.execute(query, (key,))
name, surname, username, gender, membertype, email, password, city, interests, score, byear, lastlogin, regtime, role = cursor.fetchone()
return Basicmember(name, surname, username, gender, email, password, byear, city, interests, lastlogin, regtime, role)
def team_page(key):
if request.method == 'GET':
team = app.store.get_team(key)
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM MEMBERS WHERE teamid='%s';" % key)
members = [(key,
Basicmember(name, surname, username, gender, email,
password, byear, city, interests,
lastlogin, regtime, role))
for key, name, surname, username, gender, membertype,
email, password, city, interests, score, byear, teamid,
lastlogin, regtime, role in cursor]
connection.commit()
now = datetime.datetime.now()
return render_template('team.html',
team=team,
members=members,
current_time=now.ctime())
else:
title = request.form['title']
score = request.form['score']
year = request.form['year']
team_type = request.form.get('team_type')
location = request.form['location']
image = request.files['file']
if image:
image.save(
os.path.join(app.config['UPLOAD_FOLDER'] + '/image/teams/' +
str(key) + '.jpg'))
app.store.update_team(key, title, score, year, team_type, location)
return redirect(url_for('team_page', key=key))
def get_basicmembers(self):
with dbapi2.connect(self.app.config['dsn']) as connection:
cursor = connection.cursor()
query = "SELECT * FROM MEMBERS WHERE MEMBERTYPE=0 ORDER BY MEMBERID"
cursor.execute(query)
basicmembers = [(key, Basicmember(name, surname, username, gender, email, password, byear, city, interests, lastlogin, regtime, role))
for key, name, surname, username, gender, membertype, email, password, city, interests, score, byear, lastlogin, regtime, role,teamid in cursor]
return basicmembers
def search_basicmember(self, key):
with dbapi2.connect(self.app.config['dsn']) as connection:
cursor = connection.cursor()
query = "SELECT * FROM MEMBERS WHERE (NAME ILIKE %s OR USERNAME ILIKE %s) AND (MEMBERTYPE=0)"
key = '%'+key+'%'
cursor.execute(query, (key, key))
basicmembers = [(key, Basicmember(name, surname, username, gender, email, password, byear, city, interests, lastlogin, regtime, role))
for key, name, surname, username, gender, membertype, email, password, city, interests, score, byear, lastlogin, regtime, role,teamid in cursor]
return basicmembers
def race_page(key):
if request.method == 'GET':
race = app.store.get_race(key)
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute("SELECT memberid FROM RACE_RESULTS WHERE raceid='%s';"%key)
memberid = cursor.fetchone()[0]
connection.commit()
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM MEMBERS WHERE memberid='%s';"%memberid)
members = [(key, Basicmember(name, surname, username, gender, email, password, byear, city, interests, lastlogin, regtime, role))
for key, name, surname, username, gender, membertype, email, password, city, interests, score, byear,teamid, lastlogin, regtime, role in cursor]
connection.commit()
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute("SELECT DISTINCT RACEID FROM RACE_RESULTS ")
max_raceid = cursor.fetchall()
connection.commit()
for i in max_raceid:
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute("SELECT COUNT(ID) FROM RACE_RESULTS WHERE raceid='%s';"%i)
countid = cursor.fetchone()[0]
connection.commit()
dizi = [1]
dizi = randint(1,countid)
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute("SELECT id FROM RACE_RESULTS WHERE raceid='%s';"%i)
ids = cursor.fetchall()
connection.commit()
for id in ids:
ord = 1
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
query = "UPDATE RACE_RESULTS SET ord=%s WHERE (raceid=%s)"
cursor.execute(query, (ord, i))
connection.commit()
now = datetime.datetime.now()
return render_template('race.html', race=race, members=members,
current_time=now.ctime())
else:
title = request.form['title']
race_type = request.form.get('race_type')
time = request.form['time']
place = request.form['place']
app.store.update_race(key, title, race_type, time, place)
return redirect(url_for('race_page', key=key))
def basicmembers_page():
if request.method == 'GET':
basicmembers = app.store.get_basicmembers()
now = datetime.datetime.now()
return render_template('basicmembers.html',
basicmembers=basicmembers,
current_time=now.ctime())
elif 'basicmembers_to_delete' in request.form or 'search' in request.form:
if request.form['submit'] == 'Delete':
keys = request.form.getlist('basicmembers_to_delete')
for key in keys:
app.store.delete_basicmember(int(key))
return redirect(url_for('basicmembers_page'))
elif request.form['submit'] == 'search':
keyword = request.form['search']
basicmembers = app.store.search_basicmember(keyword)
now = datetime.datetime.now()
return render_template('basicmembers.html',
basicmembers=basicmembers,
current_time=now.ctime())
else:
name = request.form['name']
surname = request.form['surname']
username = request.form['username']
email = request.form['email']
password = request.form['password']
byear = request.form['byear']
city = request.form['city']
interests = request.form['interests']
gender = request.form.get('gender')
now = str((datetime.datetime.now()))
now = now[:-7]
if (app.store.check_admin(email, password)):
role = 'admin'
else:
role = 'user'
basicmember = Basicmember(name, surname, username, gender, email,
password, byear, city, interests, now, now,
role)
app.store.add_basicmember(basicmember)
return render_template('login.html')
def activity_page(key):
if request.method == 'GET':
activity = app.store.get_activity(key)
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute(
"SELECT memberid FROM ACTIVITY_MEMBERS WHERE activityid='%s';"
% key)
memberid = cursor.fetchone()
connection.commit()
with dbapi2.connect(app.config['dsn']) as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM MEMBERS WHERE memberid='%s';" %
memberid)
members = [(key,
Basicmember(name, surname, username, gender, email,
password, byear, city, interests,
lastlogin, regtime, role))
for key, name, surname, username, gender, membertype,
email, password, city, interests, score, byear, teamid,
lastlogin, regtime, role in cursor]
connection.commit()
now = datetime.datetime.now()
return render_template('activity.html',
activity=activity,
members=members,
current_time=now.ctime())
else:
title = request.form['title']
activity_type = request.form.get('activity_type')
time = request.form['time']
place = request.form['place']
activity_info = request.form['activity_info']
app.store.update_activity(key, title, activity_type, time, place,
activity_info)
return redirect(url_for('activity_page', key=key))