def addTeam():
username = request.form.get('username')
name = request.form.get('name', '')
with getConn() as cursor:
# 更新用户团队信息
cursor.execute('insert ignore into TEAM (NAME) values ("%s")' %
(name, ))
cursor.execute('''
select LAST_INSERT_ID();
''')
teamId = cursor.fetchone()[0]
cursor.execute('''
update USER set TEAM_ID = "%d" where USERNAME = "******"
''' % (
int(teamId),
username,
))
res = {
'status': 1,
'message': 'success',
'data': {
'teamId': teamId,
'teamName': name
}
}
return jsonify(res)
def signUp():
# 获取name值，如果没有默认赋值'aa'
print(request.form)
username = request.form.get('username', '')
email = request.form.get('email', '')
password = request.form.get('password', '')
hashPassword = sha256_crypt.encrypt(password)
avatar = request.form.get('avatar', '')
with getConn() as cursor:
cursor.execute('select * from USER where EMAIL = "%s"' % (email, ))
if cursor.fetchone():
res = {"status": 0, "message": "email-existed-error"}
return jsonify(res)
cursor.execute('select * from USER where USERNAME = "******"' %
(username, ))
if cursor.fetchone():
res = {"status": 0, "message": "username-existed-error"}
return jsonify(res)
cursor.execute(
'insert into USER (USERNAME , EMAIL , PASSWORD , AVATAR ) values ("%s" ,"%s" ,"%s" ,"%s")'
% (
username,
email,
hashPassword,
avatar,
))
# 获取用户信息
res = getUser(email, password)
return jsonify(res)
def fund_edit(user_id=None):
"""
修改额度
"""
if request.method == 'POST':
"""
验证 edit_fund 字段的格式
验证 edit_fund 字段的格式
验证 edit_fund 字段的格式
"""
db = getConn()
cursor = db.cursor()
edit_fund = int(request.form['edit_fund'])
user_id = request.form['userid']
tag = request.form['tag']
reason = request.form['reason']
if request.form['option'] == '1': # 扣除额度
edit_fund = -edit_fund
user_sql = 'update user set fund=fund+%s where id="%s"' % (edit_fund,
user_id)
fundlog_sql = 'insert into fundlog(user_id, value, summary, detail) values ("%s", "%s", "%s", "%s")' % (
user_id, edit_fund, tag, reason)
try:
cursor.execute(user_sql)
cursor.execute(fundlog_sql)
db.commit()
except:
db.rollback()
err_msg = u'服务器内部错误，修改额度失败，请重试'
else:
err_msg = ''
db.close()
return jsonify({'err_msg': err_msg})
else:
db = getConn()
cursor = db.cursor()
sql = 'select id, account, username, fund from user where id="%s"' % user_id
try:
cursor.execute(sql)
except:
pass
else:
user = cursor.fetchone()
columns = ('id', 'account', 'username', 'fund')
user = dict(zip(columns, user))
db.close()
return render_template('admin/fund_edit.html', user=user)
def getTeam():
head = ('id', 'name', 'sum', 'created', 'updated')
data = []
with getConn() as cursor:
cursor.execute('select * from TEAM')
for item in cursor.fetchall():
data.append(dict(zip(head, item)))
res = {'status': 1, 'message': 'success', 'data': data}
return jsonify(res)
def getTeamTask(teamId):
head = ('id', 'name', 'deadline', 'finished', 'leader')
data = []
with getConn() as cursor:
cursor.execute('''
select ID,NAME,DEADLINE,FINISHED,LEADER
from TASK where TEAM_ID = "%d"
''' % (teamId, ))
for item in cursor.fetchall():
data.append(dict(zip(head, item)))
res = {'status': 1, 'message': 'success', 'data': data}
return jsonify(res)
def getMember(teamId):
head = ('id', 'username', 'email', 'avatar')
data = []
with getConn() as cursor:
cursor.execute('''
select ID,USERNAME,EMAIL,AVATAR
from USER where TEAM_ID = "%d"
''' % (teamId, ))
for item in cursor.fetchall():
data.append(dict(zip(head, item)))
res = {'status': 1, 'message': 'success', 'data': data}
return jsonify(res)
def updateState():
taskId = int(request.form.get('taskId'))
finished = int(request.form.get('finished'))
with getConn() as cursor:
# 更新用户团队信息
cursor.execute('''
update TASK set FINISHED = "%d" where ID = "%d"
''' % (
finished,
taskId,
))
res = {'status': 1, 'message': 'success'}
return jsonify(res)
def getSendTask(username, teamId):
head = ('id', 'name', 'deadline', 'finished', 'leader')
data = []
with getConn() as cursor:
cursor.execute('''
select ID,NAME,DEADLINE,FINISHED,LEADER
from TASK where LEADER = "%s" and TEAM_ID = "%d"
''' % (
username,
teamId,
))
for item in cursor.fetchall():
data.append(dict(zip(head, item)))
data = json.loads(json.dumps(data, default=datetime_handler))
res = {'status': 1, 'message': 'success', 'data': data}
return jsonify(res)
def user_add():
"""
添加成员
"""
if request.method == 'POST':
"""
对各个字段的验证
对各个字段的验证
对各个字段的验证
对各个字段的验证
"""
print(request.form)
account = request.form['account']
username = request.form['username']
password = request.form['password']
init_score = int(request.form['init_score'])
init_fund = int(request.form['init_fund'])
education = request.form['education']
grade = request.form['grade']
db = getConn()
cursor = db.cursor()
sql = 'select * from user where account="%s"' % account
try:
cursor.execute(sql)
except:
err_msg = u'服务器内部错误，添加成员失败，请重试'
else:
value = cursor.fetchone()
if value:
err_msg = u'账号已存在'
else:
sql = '''insert into user(account, username, password, education, score, fund, grade)
values ('%s', '%s', '%s', '%s', '%s', '%s', '%s');''' % (
account, username, password, education, init_score,
init_fund, grade)
try:
cursor.execute(sql)
db.commit()
except:
err_msg = u'服务器内部错误，添加成员失败，请重试'
db.rollback()
else:
err_msg = ''
db.close()
return jsonify({'err_msg': err_msg})
else:
return render_template('admin/user_add.html')
def getCalendarTask(teamId):
head = ('id', 'title', 'end', 'start')
color = ['#257e4a', '#ff9f89', '#3a87ad']
data = []
with getConn() as cursor:
cursor.execute('''
select ID,NAME,DEADLINE,CREATED
from TASK where TEAM_ID = "%d"
''' % (teamId, ))
for item in cursor.fetchall():
data.append(dict(zip(head, item)))
for item in data:
item['url'] = '/detail/' + str(item['id'])
item['color'] = color[item['id'] % 3]
data = json.loads(json.dumps(data, default=calendar_handler))
res = {'status': 1, 'message': 'success', 'data': data}
return jsonify(res)
def index():
"""
主页
"""
db = getConn()
cursor = db.cursor()
sql = 'select id, account, username, score, fund from user'
try:
cursor.execute(sql)
except:
pass
else:
users = []
columns = ('id', 'account', 'username', 'score', 'fund')
for item in cursor.fetchall():
users.append(dict(zip(columns, item)))
db.close()
return render_template('admin/index.html', users=users)
def user_delete():
"""
删除用户
"""
db = getConn()
cursor = db.cursor()
user_id = request.form['user_id']
sql = 'delete from user where id="%s"' % user_id
try:
cursor.execute(sql)
db.commit()
except:
err_msg = u'服务器内部错误，删除用户失败，请重试'
db.rollback()
else:
err_msg = ''
db.close()
return jsonify({'err_msg': err_msg})
def user_list():
"""
成员列表
"""
db = getConn()
cursor = db.cursor()
sql = 'select id, account, username, education, grade, telephone, qq from user'
try:
cursor.execute(sql)
except:
pass
else:
users = []
columns = ('id', 'account', 'username', 'education', 'grade',
'telephone', 'qq')
for item in cursor.fetchall():
users.append(dict(zip(columns, item)))
db.close()
return render_template('admin/user_list.html', users=users)
def getUser(email, password):
head = ('id', 'username', 'email', 'avatar', 'teamId', 'created',
'updated', 'teamName')
with getConn() as cursor:
cursor.execute('''
select PASSWORD from USER where EMAIL = "%s"
''' % (email, ))
hashed = cursor.fetchone()[0]
if sha256_crypt.verify(password, hashed):
cursor.execute('''
select USER.ID,USERNAME,EMAIL,AVATAR,TEAM_ID,USER.CREATED,USER.UPDATED,TEAM.NAME
from USER left join TEAM on USER.TEAM_ID = TEAM.ID
where EMAIL = "%s"
''' % (email, ))
user = cursor.fetchone()
userDict = dict(zip(head, user))
data = json.loads(
json.dumps(dict(userDict), default=datetime_handler))
res = {"status": 1, "message": "success", "data": data}
else:
res = {"status": 0, "message": "email-pwd-error"}
return res
def pwd_edit():
"""
修改密码
"""
"""
对输入的字段进行验证
对输入的字段进行验证
对输入的字段进行验证
对输入的字段进行验证
"""
if request.method == 'POST':
old_password = request.form['old_password']
new_password = request.form['new_password']
db = getConn()
cursor = db.cursor()
sql = 'select password from admin where id=%s' % (session['id'])
try:
cursor.execute(sql)
except:
err_msg = u'服务器内部错误，修改密码失败，请重试'
else:
password = cursor.fetchone()[0]
if password != old_password:
err_msg = u'旧密码错误'
else:
sql = 'update admin set password="******" where id="%s"' % (
new_password, session['id'])
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
err_msg = u'服务器内部错误，修改密码失败，请重试'
else:
err_msg = ''
db.close()
return jsonify({'err_msg': err_msg})
else:
return render_template('admin/pwd.html')
def getTask(taskId=0):
head = ('id', 'name', 'description', 'deadline', 'finished', 'leader',
'created', 'updated')
data = {}
with getConn() as cursor:
cursor.execute('''
select ID,NAME,DESCRIPTION,DEADLINE,FINISHED,LEADER,CREATED,UPDATED
from TASK where ID = "%d"
''' % (taskId, ))
data = dict(zip(head, cursor.fetchone()))
data = json.loads(json.dumps(data, default=datetime_handler))
# 处理负责人员
members = []
cursor.execute('''
select USERNAME from USER_TASK where TASK_ID = "%d"
''' % (taskId, ))
for item in cursor.fetchall():
members.append(item[0])
avatarMembers = []
avatarhead = ('username', 'avatar')
for item in members:
cursor.execute('''
select AVATAR from USER where USERNAME = "******"
''' % (item, ))
avatar = cursor.fetchone()[0]
avatarMembers.append(dict(zip(avatarhead, (item, avatar))))
data['member'] = avatarMembers
# 处理附件信息
cursor.execute('''
select ADDED_URL from TASK where ID = "%d"
''' % (taskId, ))
addedUrls = cursor.fetchone()[0].split(',')
for item in cursor.fetchall():
members.append(item[0])
data['addedUrl'] = addedUrls
res = {'status': 1, 'message': 'success', 'data': data}
return jsonify(res)
def addTask():
print(request.form)
teamId = int(request.form.get('teamId', ''))
username = request.form.get('username', '')
name = request.form.get('name', '')
description = request.form.get('description', '')
addedUrl = request.form.get('addedUrl', '')
deadline = request.form.get('deadline', '')
members = request.form.get('members', '')
with getConn() as cursor:
cursor.execute('''
insert into TASK
(NAME, DESCRIPTION,ADDED_URL,DEADLINE,LEADER,TEAM_ID )
values ("%s", "%s","%s","%s","%s","%d")
''' % (
name,
description,
addedUrl,
deadline,
username,
teamId,
))
cursor.execute('''
select LAST_INSERT_ID();
''')
taskId = cursor.fetchone()[0]
members = members.split(',')
for member in members:
cursor.execute('''
insert into USER_TASK
( USERNAME , TASK_ID )
values ("%s", "%d")
''' % (
member,
int(taskId),
))
res = {"status": 1, 'message': 'success'}
return jsonify(res)
def login():
"""
登录
"""
if request.method == 'POST':
account = request.form['account']
password = request.form['password']
# =================================
# 服务器端 account password 格式验证
#
#
# =================================
db = getConn()
cursor = db.cursor()
sql = 'select id, account, password, username, is_super, create_time from admin where account="%s"' % account
try:
cursor.execute(sql)
except:
err_msg = u'服务器内部错误'
else:
value = cursor.fetchone()
if not value:
err_msg = u'用户名不存在'
else:
columns = ('id', 'account', 'password', 'username', 'is_super',
'create_time')
user = dict(zip(columns, value))
if user['password'] == password:
err_msg = ''
for key in user:
session[key] = user[key]
else:
err_msg = u'密码错误'
return jsonify({'err_msg': err_msg})
db.close()
else:
return render_template('admin/login.html')
def updateTeam():
username = request.form.get('username')
if request.form.get('oldTeamId') != 'null':
oldTeamId = int(request.form.get('oldTeamId'))
newTeamId = int(request.form.get('newTeamId'))
with getConn() as cursor:
# 更新用户团队信息
cursor.execute('''
update USER set TEAM_ID = "%d" where USERNAME = "******"
''' % (
newTeamId,
username,
))
# 更新团队人数
if request.form.get('oldTeamId') != 'null':
cursor.execute('''
update TEAM set SUM = SUM - 1 where ID = "%d"
''' % (oldTeamId, ))
cursor.execute('''
update TEAM set SUM = SUM + 1 where ID = "%d"
''' % (newTeamId, ))
res = {'status': 1, 'message': 'success'}
return jsonify(res)
