def stats_reg():
user_id = check_auth()
if not user_id in config.admin_ids:
content = '<div>' + lang.lang['admin_not_enough_rights'] + '</div><div><a href="' + config.base_url + '/auth/vk_start">' + lang.lang['admin_login'] + '</a></div></div>\n'
return render_template('template.html', title=lang.lang['admin_title'], content=content)
conn, cur = db.mysql_init()
fields = ['id', 'first_name', 'last_name', 'grade', 'school', 'email', 'reg_date']
line_chart = pygal.Bar()
line_chart.title = 'Registration stats'
min_h = 10**10
max_h = -10**10
regs = {6: [], 7: []}
cur.execute('select grade, reg_date from users where id > 22')
for u in cur.fetchall():
g, d = int(u[0]), u[1] // 3600
regs[g].append(d)
min_h = min(min_h, d)
max_h = max(max_h, d)
regsc = {6: [0 for x in range(max_h - min_h + 1)], 7: [0 for x in range(max_h - min_h + 1)]}
for g in [6, 7]:
for h in regs[g]:
regsc[g][h - min_h] += 1
line_chart.x_labels = [str((h + 3) % 24) for h in range(min_h, max_h + 1)]
for g in [6, 7]:
line_chart.add(str(g), regsc[g])
db.mysql_close(conn, cur)
return line_chart.render()
def reload_settings(check=False):
"""载入设置，修改LOGIC_CONF"""
mysql_db, mysql_cursor = db.mysql_connect(
MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASS, MYSQL_NAME,
)
msgs = []
error_msgs = []
excepted = False
def get_new_conf(table, sql):
"""从表里取得新的配置"""
new_conf = {}
try:
rows = db.mysql_read_cursor(
mysql_cursor,
sql.format(table=table)
)
success_row_count = 0
for row in rows:
try:
new_conf[row[0]] = util.unicode2utf8(loads(row[1]))
success_row_count += 1
except:
error_msgs.append(
'%s: <font color="red">!ERROR!</font>'
' type:<font color="red"><b>%s</b></font>, '
'name:<font color="red">%s</font><br/>' % (table, row[0], row[2])
)
excepted = True
msgs.append(
'%s: %d rows <font color="green">OK.</font><br/>' % (table, success_row_count)
)
except:
error_msgs.append(
'%s: <font color="red">Exception:%s</font><br/>' % (table, format_exc())
)
excepted = True
return new_conf
# 设置
new_logic_conf = get_new_conf(
MYSQL_SETTINGS_TABLE,
"SELECT type1, `value`, `name` FROM {table} WHERE `type` in ('', 'server') ORDER BY id"
)
if not check:
LOGIC_CONF.update(new_logic_conf)
global RELOAD_SETTINGS_MSGS
RELOAD_SETTINGS_MSGS = "\n".join(("\n".join(error_msgs), "\n".join(msgs)))
RELOAD_SETTINGS_MSGS = \
'<!doctype html><html lang="zh-CN"><head><meta charset="UTF-8"></head><body>' + \
RELOAD_SETTINGS_MSGS + \
'</body></html>'
db.mysql_close(mysql_db, mysql_cursor)
return excepted
def do_reg(data):
conn, cur = db.mysql_init()
fields = ['last_name', 'first_name', 'patronymic', 'gender', 'grade', 'city', 'school', 'grade2', 'email', 'comment', 'state', 'reg_date']
data['state'] = 0
data['reg_date'] = time.time()
cur.execute('insert into users(' + ', '.join(fields) + ') values (' + ', '.join(['%s'] * len(fields)) + ')', [data[f] for f in fields])
cur.execute ('select last_insert_id()')
user_id = cur.fetchall()[0][0]
conn.commit()
db.mysql_close(conn, cur)
return user_id
def do_set_color():
user_id = check_auth(request)
if not user_id in config.allowed_users:
return redirect(config.base_url)
conn, cur = db.mysql_init()
try:
problem_id = int(request.args.get('problem', '0'))
color = request.args.get('color', '')
except:
return redirect(config.base_url)
cur.execute('update problems set color=%s where id=%s', [color, problem_id])
conn.commit()
db.mysql_close(conn, cur)
return redirect(config.base_url + '/problem' + str(problem_id))
def do_drop():
user_id = check_auth(request)
if not user_id in config.allowed_users:
return redirect(config.base_url)
conn, cur = db.mysql_init()
try:
event_id = int(request.args.get('event', '0'))
balloon_id = int(request.args.get('balloon', '0'))
except:
return redirect(config.base_url)
cur.execute('update balloons set state=1 where id=%s', [balloon_id])
conn.commit()
db.mysql_close(conn, cur)
return redirect(config.base_url + '/event' + str(event_id))
def admin_schools():
user_id = check_auth()
if not user_id in config.admin_ids:
content = '<div>' + lang.lang['admin_not_enough_rights'] + '</div><div><a href="' + config.base_url + '/auth/vk_start">' + lang.lang['admin_login'] + '</a></div></div>\n'
return render_template('template.html', title=lang.lang['admin_title'], content=content)
conn, cur = db.mysql_init()
cur.execute('select school, count(*) as cnt from users where id>22 group by school order by cnt')
users = []
for r in cur.fetchall():
u = {'school': r[0], 'count': int(r[1])}
users.append(u)
content = ''
content += '<table>\n' + ''.join(['<tr><td>' + u['school'] + '</td><td>' + str(u['count']) + '</td></tr>\n' for u in users]) + '</table>\n'
db.mysql_close(conn, cur)
return render_template('template.html', title=lang.lang['admin_title'], content=content)
def admin_do_edit():
user_id = check_auth()
if not user_id in config.admin_ids:
return lang.lang['admin_not_enough_rights']
try:
user_id = int(request.args.get('id', '0'))
field = request.args.get('f', '')
value = request.args.get('v', '')
except:
return redirect(config.base_url)
if not field in ['first_name', 'last_name', 'grade', 'grade2', 'school', 'school2', 'work'] \
+ ['r0', 'r1', 'r2', 'r3', 'r4', 'r5', 'r6']:
return 'error1'
conn, cur = db.mysql_init()
cur.execute('update users set ' + field + '=%s, res=r0+r1+r2+r3+r4+r5+r6 where id=%s', [value, str(user_id)])
conn.commit()
db.mysql_close(conn, cur)
return str(user_id) + '.' + field + '=' + value
def index():
conn, cur = db.mysql_init()
user_id = check_auth(request)
content = ''
events = []
cur.execute('select id, name, state from events')
for row in cur.fetchall():
events.append(row)
if len(events) == 0:
content = lang.lang['index_no_events']
for e in events:
content += '<div><a href="event' + str(e[0]) + '">' + cgi.escape(e[1]) + '</a></div>'
if user_id == None:
content += '<div>' + lang.lang['index_not_authorised'] + ' <a href="/ball/auth">' + lang.lang['index_log_in'] + '</a></div>'
else:
content += '<div><b>' + str(user_id) + '</b></div>'
db.mysql_close(conn, cur)
return render_template('template.html', title=lang.lang['index_title'], content=content)
def problem(problem_id):
user_id = check_auth(request)
if not user_id in config.allowed_users:
return redirect(config.base_url)
conn, cur = db.mysql_init()
problem_id = int(problem_id)
content = ''
colors = ['#f9ff0f', '#000000', '#f6ab23', '#cc0000', '#03C03C', '#e1379e', '#9e37e1', '#2FACAC', '#0047AB', '#FFFFF']
problems = []
cur.execute('select id, letter, color, name from problems where id=%s', [problem_id])
for row in cur.fetchall():
p = { 'id': row[0], 'letter': row[1], 'color': row[2], 'name': row[3] }
problems.append(p)
problems_html = '<h2>' + problems[0]['letter'] + ': ' + problems[0]['name'] + '</h2>\n'
content += problems_html
colors_html = ''
colors_html += '<div><span style="color:' + problems[0]['color'] + '">' + lang.lang['problem_cur_color'] + ' <b>' + problems[0]['color'] + '</b>' + '</span></div>'
for c in colors:
colors_html += '<div><a href="/ball/do_set_color?problem=' + str(problem_id) + '&color=' + urllib.parse.quote(c) + '"><span style="color:' + c + '">' + lang.lang['problem_set_color'] + ' <b>' + c + '</b>' + '</span></a></div>'
content += colors_html
db.mysql_close(conn, cur)
return render_template('template.html', title=problems[0]['letter'], content=content)
def admin():
user_id = check_auth()
if not user_id in config.admin_ids:
content = '<div>' + lang.lang['admin_not_enough_rights'] + '</div><div><a href="' + config.base_url + '/auth/vk_start">' + lang.lang['admin_login'] + '</a></div></div>\n'
return render_template('template.html', title=lang.lang['admin_title'], content=content)
try:
mode = request.args.get('mode', '')
except:
mode = ''
try:
order = request.args.get('order', 'id')
except:
order = 'id'
conn, cur = db.mysql_init()
fields = ['id', 'first_name', 'last_name', 'grade', 'grade2', 'school', 'school2', 'reg_date', 'work', 'r0', 'r1', 'r2', 'r3', 'r4', 'r5', 'r6', 'res']
cur.execute('select ' + ', '.join(fields) + ' from users where id > 22 order by ' + order)
content = ''
users = []
for u in cur.fetchall():
i = 0
user = {}
for f in fields:
user[f] = u[i]
i += 1
user['reg_date'] = time.strftime('%b %d %H:%M:%S', time.localtime(int(user['reg_date'])))
users.append(user)
show_fields = fields[::]
if mode != 'add':
show_fields.append('title')
names = {6: {}, 7: {}}
for u in users:
name = u['first_name'] + ' ' + u['last_name']
grade = int(u['grade'])
user_hash = get_user_hash(u['id'])
link_title = config.base_url + '/static/title' + str(user_hash) + '.pdf'
u['link_title'] = '<a href="' + cgi.escape(link_title) + '">титул</a>'
text = ''
if mode == 'edit':
for f in fields:
ustr = ''
if f in ['id', 'reg_date']:
ustr += cgi.escape(str(u[f]))
else:
style = ''
if f in ['grade', 'grade2']:
style = 'style="width: 30px;"'
elif f in ['r0', 'r1', 'r2', 'r3', 'r4', 'r5', 'r6', 'res']:
style = 'style="width: 20px;"'
ustr += '<input type="text" ' + style \
+ 'id="u_' + str(u['id']) + '_' + f + '"' \
+ 'value="' + cgi.escape(str(u[f])) + '"' \
+ 'onkeydown="pressed(' + str(u['id']) + ", '" + f + "');" + '" />'
text += '<td>' + ustr + '</td>'
text += '<td>' + u['link_title'] + '</td>'
elif mode == 'add':
for f in fields:
ustr = ''
if not f in ['work']:
ustr += cgi.escape(str(u[f]))
else:
ustr += '<input type="text" ' \
+ 'id="u_' + str(u['id']) + '_' + f + '"' \
+ 'value="' + cgi.escape(str(u[f])) + '"' \
+ 'onkeydown="pressed(' + str(u['id']) + ", '" + f + "');" + '" />'
text += '<td>' + ustr + '</td>'
elif mode == 'res':
for f in fields:
ustr = ''
if not f in ['r0', 'r1', 'r2', 'r3', 'r4', 'r5', 'r6', 'res']:
ustr += cgi.escape(str(u[f]))
else:
style = ''
if f in ['r0', 'r1', 'r2', 'r3', 'r4', 'r5', 'r6', 'res']:
style = 'style="width: 20px;"'
ustr += '<input type="text" ' + style \
+ 'id="u_' + str(u['id']) + '_' + f + '"' \
+ 'value="' + cgi.escape(str(u[f])) + '"' \
+ 'onkeydown="pressed(' + str(u['id']) + ", '" + f + "');" + '" />'
text += '<td>' + ustr + '</td>'
else:
text += ''.join(['<td>' + cgi.escape(str(u[f])) + '</td>' for f in fields])
text += '<td>' + u['link_title'] + '</td>'
u['text'] = '<tr>' + text + '</tr>\n'
if not name in names[grade]:
names[grade][name] = True
content += '<table>\n' + '<tr>' + ''.join(['<th>' + f + '</th>' for f in show_fields]) + '</th>'
content += ''.join([u['text'] for u in users])
content += '</table>\n'
for g in [6, 7]:
content += '<div>' + str(len(names[g])) + ' различных имен-фамилий в ' + str(g) + ' классе</div>'
content += '<div>' + str(len(names[6]) + len(names[7])) + ' различных имен-фамилий всего</div>'
db.mysql_close(conn, cur)
return render_template('template.html', title=lang.lang['admin_title'], content=content)
tex.write('\\vskip 1cm\n')
tex.write('титульный лист участника\n\n')
tex.write('\\vskip 1cm\n')
tex.write('{\huge \\bf ' + texify(first_name) + ' ' + texify(last_name) + '}\n\n')
tex.write('{\large (' + texify(school) + ', ' + texify(grade2) + ' класс)}\n\n')
tex.write('\\vskip 2cm\n')
tex.write('{\\Large Код бланка} \n')
b = '\\begin{tabular}{|c|}\n' + ' \\hline\n' + ' {\\Huge \\phantom{X} } \\\\\n' + ' \\hline\n' + '\\end{tabular}\n'
tex.write(b + b + b + b + ' --- ' + b + b + '\n\n')
tex.write('(просим переписать с титульного листа работы)\n\n')
tex.write('\\vskip 7cm\n\n')
tex.write('Просим распечатать этот титульный лист и принести его с собой на\n')
tex.write('Математический праздник в \\\\\n')
tex.write(str(grade) + ' классе в Физико-математическом лицее 30.\n\n')
tex.write('\\vskip 1cm\n')
tex.write('{\\it Начало Праздника в 10 часов утра. На решение задач дается 2 часа.\n')
tex.write('Завершается программа Праздника около 18 часов.\n\n')
tex.write('Результаты после закрытия~--- на сайте} \\texttt{spbtc.ru/matrpazdnik}\n\n')
tex.write('\\vskip 1cm\n')
tex.write('(регистрация ' + str(user_hash) + ')\\\\\n')
tex.write('\\end{center}\n')
tex.write('\\end{document}\n')
tex.close()
call(['pdflatex', file_name + '.tex'])
call(['mv', '-f', file_name + '.pdf', 'static/'])
call(['rm', '-f', file_name + '.aux', file_name + '.log', file_name + '.tex'])
db.mysql_close(conn, cur)
def event(event_id):
user_id = check_auth(request)
if not user_id in config.allowed_users:
return redirect(config.base_url)
conn, cur = db.mysql_init()
event_id = int(event_id)
content = ''
events = []
cur.execute('select id, name, state from events where id=%s', [event_id])
for row in cur.fetchall():
e = row
event = { 'name': e[1], 'state': e[2] }
problems = []
problems_map = {}
cur.execute('select id, letter, color from problems where event_id=%s', [event_id])
for row in cur.fetchall():
p = { 'id': row[0], 'letter': row[1], 'color': row[2] }
problems.append(p)
problems_map[p['id']] = len(problems) - 1
for p in problems:
cur.execute('select count(*) from balloons where event_id=%s and problem_id=%s', [event_id, p['id']])
cnt = 0
for row in cur.fetchall():
cnt = int(row[0])
p['cnt'] = cnt
problems_html = '<h2>' + lang.lang['event_header_problems'] + '</h2>\n'
problems_html += '<table style="width: 100%;"><tr>'
for p in problems:
text = ' '
if p['color'] == '':
text = '?'
problems_html += '<td style="height: 50px; width: 25px; text-align: center; background-color: ' + p['color'] + '; border-style: solid; border-width: 1px;">' + text + '</td>'
problems_html += '<td style="height: 50px; width: 50px; text-align: left; font-weight: bold;"><a href="/ball/problem' + str(p['id']) + '">' + p['letter'] + '</a>' + '(' + str(p['cnt']) + ')' + '</td>'
problems_html += '</tr></table>\n'
content += problems_html
teams = []
teams_map = {}
cur.execute('select id, name, long_name from teams where event_id=%s', [event_id])
for row in cur.fetchall():
t = { 'id': row[0], 'name': row[1], 'long_name': row[2] }
teams.append(t)
teams_map[t['id']] = len(teams) - 1
first_to_solve = {}
for p in problems:
cur.execute('select id from balloons where event_id=%s and problem_id=%s order by id limit 1', [event_id, p['id']])
for row in cur.fetchall():
first_to_solve[p['id']] = row[0]
first_solved = {}
for t in teams:
cur.execute('select id from balloons where event_id=%s and team_id=%s order by id limit 1', [event_id, t['id']])
for row in cur.fetchall():
first_solved[t['id']] = row[0]
def get_balloons_html(header, get_state_str):
balloons = []
for row in cur.fetchall():
b = { 'id': row[0], 'problem_id': row[1], 'team_id': row[2], 'volunteer_id': row[3], 'state': int(row[4]) }
balloons.append(b)
if len(balloons) == 0:
return ''
balloons_html = '<h2>' + header + '</h2>\n'
balloons_html += '<table style="width: 100%;">\n'
for b in balloons:
p = problems[problems_map[b['problem_id']]]
t = teams[teams_map[b['team_id']]]
state_str = get_state_str(event_id, b)
balloons_html += '<tr style="padding: 10px;">'
balloons_text = ' '
if not p['color']:
balloons_text = '?'
balloons_html += '<td style="background-color: ' + p['color'] + '; width: 20px; border-style: solid; border-width: 1px;">' + balloons_text + '</td>'
x = ''
if first_to_solve[b['problem_id']] == b['id']:
x = '<b>' + lang.lang['event_queue_first_to_solve'] + '</b>'
balloons_html += '<td>' + x + lang.lang['event_queue_problem'] + ' <b>' + p['letter'] + '</b></td>'
x = ''
if b['team_id'] in first_solved and first_solved[b['team_id']] == b['id']:
x = '<b>' + lang.lang['event_queue_first_solved'] + '</b>'
balloons_html += '<td>' + x + lang.lang['event_queue_team'] + ' <b>' + t['name'] + '</b>: ' + cgi.escape(t['long_name']) + '</td>'
balloons_html += '<td>' + state_str + '</td>'
balloons_html += '<tr>\n'
balloons_html += '</table>\n'
return balloons_html
fields = ', '.join(['id', 'problem_id', 'team_id', 'volunteer_id', 'state'])
cur.execute('select ' + fields + ' from balloons where event_id=%s and state>=100 and state<200 and volunteer_id=%s order by state, id desc', [event_id, user_id])
content += get_balloons_html(lang.lang['event_header_your_queue'], get_state_str_current)
cur.execute('select ' + fields + ' from balloons where event_id=%s and state<100 order by state, id desc', [event_id])
content += get_balloons_html(lang.lang['event_header_offer'], get_state_str_queue)
cur.execute('select ' + fields + ' from balloons where event_id=%s and state>=100 order by state, id desc', [event_id])
content += get_balloons_html(lang.lang['event_header_queue'], get_state_str_queue)
db.mysql_close(conn, cur)
return render_template('template.html', title = event['name'], content = content)