def resolveAppeal(data):
try:
id = data.get('id')
appealResult = int(data.get('appealResult'))
appealFeedback = data.get('appealFeedback')
resolver = data.get('resolver')
resolveTime = data.get('resolveTime')
type = data.get('type')
sql = "update appeal set appealResult = '%s',appealFeedback = '%s',state=2, resolver='%s',resolveTime='%s' where videoID='%s'" % (
appealResult, appealFeedback, resolver, resolveTime, id)
cursor.execute(sql)
db.commit()
status = '不通过' if appealResult == 0 else '审核通过'
sql = "update video set videostatus='%s' where videoID = '%s'" % (
status, id)
cursor.execute(sql)
db.commit()
print(type)
if type == 0:
addHistory(id, 5, resolveTime, '申诉已完成', resolver, appealResult)
else:
updateHistory(id, 5, resolveTime, '申诉已完成', resolver, appealResult)
return Utils.responseGen(0, 'success', '')
except Exception as e:
return Utils.responseGen(1, 'fail', '')
def addNewAppeal(data, username):
template_appeal = 'insert into appeal(`videoid`,`appealTime`,`appealername`,`appealContent`,`state`) values(%s,%s,%s,%s,1)'
template_video = 'update video set appealed = 1 where videoid = %s'
template_history = "insert into history(`videoid`,`type`,`time`,`description`,`operator`,`state`) values (%s,4,%s,'申诉申请成功',%s,1)"
data_appeal = []
data_video = []
data_history = []
try:
for re in data:
selected = re.get('selected')
if selected:
id = re.get('id')
appeal = re.get('appeal')
time = Utils.time_format()
data_appeal.append((id, time, username, appeal))
data_video.append((id, ))
data_history.append((id, time, username))
cursor.executemany(template_appeal, data_appeal)
cursor.executemany(template_video, data_video)
cursor.executemany(template_history, data_history)
db.commit()
return Utils.responseGen(0, '申诉已提交', '')
except Exception as e:
db.rollback()
res = Utils.responseGen(1, '申诉失败', '')
return res
def registerUser(json_str):
try:
user = json.loads(json_str)
username = user.get('username')
password = user.get('password')
nickname = user.get('nickname')
sex = user.get('sex')
age = user.get('age')
birth = user.get('birth')
email = user.get('email')
phone = user.get('phone')
registerTime = datetime.datetime.strftime(datetime.datetime.now(),
'%Y-%m-%d %H:%M:%S')
sql = "insert into user(UserName,UserPassword,nickname,sex,birth,UserEmail,phone,age,registerTime) values ('%s','%s','%s','%s','%s','%s','%s','%d','%s');" % (
username, password, nickname, sex, birth, email, phone, age,
registerTime)
cursor.execute(sql)
db.commit()
re = Utils.responseGen(0, '注册成功', '')
return re
except Exception as e:
db.rollback()
re = Utils.responseGen(1, '注册失败', '')
return re
def deleteNotice(id):
try:
sql = "delete from notice where id='%s'" % (id)
cursor.execute(sql)
db.commit()
return Utils.responseGen(0, '删除成功', '')
except Exception as e:
db.rollback()
return Utils.responseGen(1, '删除失败', '')
def cancelAvatarChange(username):
try:
src = config.PIC_TMP + username + '.jpg'
# 删除avatar中的文件
if os.path.exists(src):
os.remove(src)
return Utils.responseGen(0, 'success', '')
except Exception as e:
return Utils.responseGen(1, 'fail', '')
def deleteUser(username):
try:
sql = "delete from user where username = '******'" % (username)
cursor.execute(sql)
db.commit()
re = Utils.responseGen(0, "删除成功", '')
return re
except Exception as e:
res = Utils.responseGen(1, '删除失败', '')
return res
def deleteMessage(id):
try:
sql = "delete from message where id = '%d'" % (id)
cursor.execute(sql)
db.commit()
re = Utils.responseGen(0, "删除成功", '')
return re
except Exception as e:
db.rollback()
res = Utils.responseGen(1, '删除失败', '')
return res
def resetPassword(username):
try:
password = '******'
sql = "update user set userpassword = '******' where username = '******'" % (
password, username)
cursor.execute(sql)
db.commit()
re = Utils.responseGen(0, "重置成功", '')
return re
except Exception as e:
db.rollback()
res = Utils.responseGen(1, '重置失败', '')
return res
def setMessageState(data):
try:
id = int(data.get('id'))
state = int(data.get('state'))
sql = "update message set readed = '%d' where id = '%d'" % (state, id)
cursor.execute(sql)
db.commit()
res = Utils.responseGen(0, "设置成功", '')
return res
except Exception as e:
db.rollback()
re = Utils.responseGen(1, "已读失败", '')
return re
def newNotice(data):
try:
content = data.get('content')
publisher = data.get('publisher')
title = data.get('title')
time = Utils.time_format()
sql = "insert into notice (title,time,publisher,content) values ('%s','%s','%s','%s')" % (
title, time, publisher, content)
cursor.execute(sql)
db.commit()
return Utils.responseGen(0, '发布成功', '')
except Exception as e:
db.rollback()
return Utils.responseGen(1, '发布失败', '')
def changePassword(data):
try:
password = data.get('password')
username = data.get('username')
sql = "update user set userpassword = '******' where username = '******'" % (
password, username)
cursor.execute(sql)
db.commit()
re = Utils.responseGen(0, "修改成功", '')
return re
except Exception as e:
db.rollback()
res = Utils.responseGen(1, '修改失败', '')
return res
def newFeedback(data):
try:
title = data.get('title')
content = data.get('content')
smtPerson = data.get('smtPerson')
smtTime = Utils.time_format()
sql = "insert into feedback(title,content,smtPerson,smtTime) values ('%s','%s','%s','%s')" % (
title, content, smtPerson, smtTime)
cursor.execute(sql)
db.commit()
return Utils.responseGen(0, 'success', '')
except Exception as e:
db.rollback()
return Utils.responseGen(1, '提交失败', '')
def replyFeedback(data):
try:
id = data.get('id')
applyTime = Utils.time_format()
applier = data.get('applier')
applyContent = data.get('applyContent')
sql = "update feedback set applyTime='%s',applier='%s',applyContent='%s',applied=1,readed=1 where id='%s'" % (
applyTime, applier, applyContent, id)
cursor.execute(sql)
db.commit()
Utils.sendMessage_fedbkReply(id)
return Utils.responseGen(0, 'success', '')
except Exception as e:
db.rollback()
return Utils.responseGen(1, '回复失败', '')
def updateNotice(data):
try:
content = data.get('content')
publisher = data.get('publisher')
title = data.get('title')
time = data.get('time')
id = data.get('id')
sql = "update notice set content='%s',publisher='%s',title='%s',time='%s' where id ='%s'" % (
content, publisher, title, time, id)
print(sql)
cursor.execute(sql)
db.commit()
return Utils.responseGen(0, '发布成功', '')
except Exception as e:
db.rollback()
return Utils.responseGen(1, '发布失败', '')
def getFailedVideoList(username):
new_db = newConnection()
new_cursor = new_db.cursor()
sql = "select * from video where videostatus = '不通过' and appealed=0 and videouploadername='%s'" % (
username)
new_cursor.execute(sql)
records = new_cursor.fetchall()
new_db.close()
list = []
key = 0
for re in records:
id = re[0]
video = getVideo(id)
copyinfo = getCopyInfo(id)
data = {
'key': key,
'tag': '不通过',
'name': re[1],
'time': str(re[9]),
'video': video,
'copyinfo': copyinfo
}
list.append(data)
key = key + 1
res = Utils.responseGen(0, 'success', list)
return res
def deleteFeature(data):
try:
authPassword = data.get('authPassword')
id = data.get('id')
if (authPassword != 'BigChuang2020'):
res = Utils.responseGen(1, '授权码错误,删除失败', '')
return res
sql = "delete from feature where feature id = '%s'" % (id)
cursor.execute(sql)
db.commit()
re = Utils.responseGen(0, "删除成功", '')
return re
except Exception as e:
db.rollback()
res = Utils.responseGen(1, '删除失败', '')
return res
def confirmAvatarChange(username):
try:
src = config.PIC_TMP + username + '.jpg'
dst = config.AVATAR_FOLDER + username + '.jpg'
# 删除avatar中的文件
if os.path.exists(dst):
os.remove(dst)
shutil.move(src, dst)
newdb = Connection.newConnection()
newcursor = newdb.cursor()
sql = "update user set avatar ='%s' where username = '******'" % (username,
username)
newcursor.execute(sql)
newdb.commit()
return Utils.responseGen(0, 'success', '')
except Exception as e:
print(str(e))
return Utils.responseGen(1, 'fail', '')
def avatarUpload(file, username):
try:
filepath = config.PIC_TMP + username + '.jpg'
# 先删除临时文件夹中已有的图片
if os.path.isfile(filepath):
os.remove(filepath)
file.save(filepath)
return Utils.responseGen(0, 'success', '')
except Exception as e:
return json.dumps({'code': -1, 'message': str(e)})
def setAuth(data):
try:
username = data.get('username')
authPassword = data.get('authPassword')
admined = data.get('auth')
if (authPassword != 'BigChuang2020'):
res = Utils.responseGen(1, '授权码错误,修改失败', '')
return res
print()
sql = "update user set admined = '%d' where username = '******'" % (
admined, username)
cursor.execute(sql)
db.commit()
re = Utils.responseGen(0, "修改成功", '')
return re
except Exception as e:
db.rollback()
res = Utils.responseGen(1, '修改失败', '')
return res
def getUnreadCount(username):
new_db = newConnection()
new_cursor = new_db.cursor()
sql = " select count(*) from message where mTo = '%s' and readed=0" % (
username)
new_cursor.execute(sql)
record = new_cursor.fetchone()
count = record[0]
new_db.close()
re = Utils.responseGen(0, "获取成功！", count)
return re
def fetchUser(keyword):
sql = "SELECT UserName FROM user WHERE username REGEXP '%s';" % (keyword)
cursor.execute(sql)
record = cursor.fetchall()
re = []
for un in record:
tmp = {'text': un[0], 'value': un[0]}
re.append(tmp)
res = Utils.responseGen(0, '查找成功', re)
return res
def updateUser(username, data):
try:
user = json.loads(data)
nickname = user.get('nickname')
sex = user.get('sex')
age = user.get('age')
birth = user.get('birth')
email = user.get('email')
phone = user.get('phone')
sign = user.get('sign')
sql = "update user set nickname = '%s', sex='%s' , age = '%d',birth = '%s',UserEmail='%s',phone='%s',sign='%s' where UserName = '******'" % (
nickname, sex, age, birth, email, phone, sign, username)
print(sql)
cursor.execute(sql)
db.commit()
res = Utils.responseGen(0, '更新成功！', '')
return res
except Exception as e:
db.rollback()
re = Utils.responseGen(1, '注册失败！', '')
return re
def sendMessage(msgData):
try:
msg = json.loads(msgData)
mFrom = msg.get('mFrom')
mTo = msg.get('mTo')
content = msg.get('content')
subject = msg.get('subject')
time = datetime.datetime.strftime(datetime.datetime.now(),
'%Y-%m-%d %H:%M:%S')
for to in mTo:
print(to)
sql = "INSERT INTO message (`mFrom`,`mTo`,`content`,`sendTime`,`readed`,`subject`) VALUES ('%s','%s','%s','%s',0,'%s');" % (
mFrom, to, content, time, subject)
cursor.execute(sql)
db.commit()
res = Utils.responseGen(0, '发送成功！', '')
return res
except Exception as e:
db.rollback()
re = Utils.responseGen(1, '发送失败！', '')
return re
def getAppealList(username, type, all=False):
new_db = newConnection()
new_cursor = new_db.cursor()
sql = ''
if all == True:
if type == 0:
# 全部
sql = "select * from appeal "
elif type == 1:
# 进行中
sql = "select * from appeal where state=1"
elif type == 2:
# 完成
sql = "select * from appeal where state=2"
else:
if type == 0:
# 全部
sql = "select * from appeal where appealerName='%s'" % (username)
elif type == 1:
# 进行中
sql = "select * from appeal where appealerName='%s' and state=1" % (
username)
elif type == 2:
# 完成
sql = "select * from appeal where appealerName='%s' and state=2" % (
username)
new_cursor.execute(sql)
record = new_cursor.fetchall()
new_db.close()
res = []
for re in record:
appeal = {
'appealID': re[0],
'videoID': re[1],
'appealTime': str(re[2]),
'appealerName': re[3],
'appealContent': re[4],
'state': re[5],
'appealResult': re[6],
'resolver': re[7],
'resolveTime': str(re[8]),
'appealFeedback': re[9]
}
video = getVideo(re[1])
r = {'key': re[0], 'appeal': appeal, 'video': video}
res.append(r)
return Utils.responseGen(0, 'success', res)
def getNoticeList():
sql = "select * from notice order by time desc"
cursor.execute(sql)
record = cursor.fetchall()
notices = []
for n in record:
notice = {
'id': n[0],
'key': n[0],
'content': n[1],
'time': str(n[2]),
'publisher': n[3],
'title': n[4]
}
notices.append(notice)
resbody = {'notices': notices}
re = Utils.responseGen(0, 'success', resbody)
return re
def getMessageList(username):
sql = "select * from message where mTo = '%s' order by readed , sendTime desc" % (
username)
cursor.execute(sql)
lists = cursor.fetchall()
re = []
for m in lists:
msg = {
'id': m[0],
'key': m[0],
'mFrom': m[1],
'mTo': m[2],
'content': m[3],
'time': datetime.datetime.strftime(m[4], '%Y-%m-%d %H:%M:%S'),
'readed': m[5],
'subject': m[6]
}
re.append(msg)
res = Utils.responseGen(0, "获取成功", re)
return res
def getFeedbackList():
sql = 'select * from feedback'
cursor.execute(sql)
records = cursor.fetchall()
data = []
for re in records:
feedback = {
'key': re[0],
'id': re[0],
'title': re[1],
'content': re[2],
'smtPerson': re[3],
'applied': re[4],
'applier': re[5],
'applyContent': re[6],
'smtTime': str(re[7]),
'applyTime': str(re[8]),
'readed': str(re[9])
}
data.append(feedback)
return Utils.responseGen(0, 'success', data)
def getUser(username):
sql = "select * from user where UserName = '******'" % (username)
cursor.execute(sql)
record = cursor.fetchone()
user = {
'username': record[0],
'password': record[1],
'avatar': record[11],
'email': record[2],
'age': record[3],
'sex': record[4],
'admined': record[6],
'nickname': record[7],
'birth': datetime.datetime.strftime(record[8], '%Y-%m-%d'),
'phone': record[9],
'sign': record[10],
'registerTime': str(record[12])
}
res = Utils.responseGen(0, 'success', user)
return res
def getAppealDetail(id):
video = getVideo(id)
copyinfo = getCopyInfo(id)
sql = "select * from appeal where videoid = '%s' " % (id)
cursor.execute(sql)
re = cursor.fetchone()
appeal = {
'appealID': re[0],
'videoID': re[1],
'appealTime': str(re[2]),
'appealerName': re[3],
'appealContent': re[4],
'state': re[5],
'appealResult': re[6],
'resolver': re[7],
'resolveTime': str(re[8]),
'appealFeedback': re[9]
}
data = {'appeal': appeal, 'video': video, 'copyinfo': copyinfo}
return Utils.responseGen(0, 'success', data)
def getAppealCount(username, all=False):
new_db = newConnection()
new_cursor = new_db.cursor()
sql1 = ''
sql2 = ''
sql3 = ''
if all:
# 全部
sql1 = "select count(*) from appeal "
# 进行中
sql2 = "select count(*) from appeal where state=1"
# 完成
sql3 = "select count(*) from appeal where state=2"
else:
# 全部
sql1 = "select count(*) from appeal where appealerName='%s'" % (
username)
# 进行中
sql2 = "select count(*) from appeal where appealerName='%s' and state=1" % (
username)
# 完成
sql3 = "select count(*) from appeal where appealerName='%s' and state=2" % (
username)
# 全部
new_cursor.execute(sql1)
c_all = new_cursor.fetchone()[0]
# 进行中
new_cursor.execute(sql2)
c_process = new_cursor.fetchone()[0]
# 已完成
new_cursor.execute(sql3)
c_finish = new_cursor.fetchone()[0]
new_db.close()
res = {'all': c_all, 'finish': c_finish, 'process': c_process}
return Utils.responseGen(0, 'success', res)