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)