def post(self):
sql = """
UPDATE ms_user_info
SET ui_name=%(name)s, ui_email=%(email)s,
ui_mobile=%(mobile)s, ui_department_id=%(department_id)s
WHERE ui_id=%(id)s;
""".strip()
# 验证用户密码
res = None
self.json_args['id'] = self.session.data['user_id']
try:
with self.db.cursor() as cursor:
cursor.execute('SELECT ui_passwd, ui_role FROM ms_user_info WHERE ui_id=%(id)s',
{"id": self.json_args['id']})
res = cursor.fetchone()
self.db.commit()
cursor.close()
self.json_args['password'] = hashlib.sha256(
(self.json_args['password'] + config.passwd_hash_key).encode('utf-8')).hexdigest()
if not (res and res[0] == self.json_args['password']):
return self.write(dict(errcode=RET.DATAERR, errmsg="账号或密码错误！"))
# 需要更改密码
if self.json_args.get('newPassword'):
sql = sql.replace('ui_name=%(name)s,', 'ui_name=%(name)s, ui_passwd=%(newPassword)s,')
self.json_args['newPassword'] = hashlib.sha256(
(self.json_args['newPassword'] + config.passwd_hash_key).encode('utf-8')).hexdigest()
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
DELETE FROM ms_student_case
WHERE sc_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="删除成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_department_info
SET di_name=%(name)s, di_dean=%(dean)s, di_address=%(address)s
WHERE di_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
INSERT INTO ms_department_info
(di_name, di_dean, di_address) VALUES
(%(name)s, %(dean)s, %(address)s);
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="添加成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
INSERT INTO ms_course_take
(ct_student_id, ct_section_id) VALUES
(%(student_id)s, %(section_id)s);
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="添加成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_student_case
SET sc_type=%(type)s, sc_level=%(level)s, sc_date=%(date)s, sc_description=%(description)s
WHERE sc_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
INSERT INTO ms_student_case
(sc_student_id, sc_type, sc_level, sc_date, sc_description) VALUES
(%(student_id)s, %(type)s, %(level)s, %(date)s, %(description)s);
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="添加成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
INSERT INTO ms_major_info
(mi_name, mi_degree, mi_study_time, mi_department_id) VALUES
(%(name)s, %(degree)s, %(study_time)s, %(department_id)s);
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="添加成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_user_info
SET ui_role=%(role)s
WHERE ui_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="授权成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_course_section
SET cs_course_id=%(course_id)s, cs_semester=%(semester)s, cs_year=%(year)s, cs_capacity=%(capacity)s,
cs_teacher_id=%(teacher_id)s, cs_btime=%(btime)s, cs_etime=%(etime)s
WHERE cs_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_user_info
SET ui_name=%(name)s, ui_email=%(email)s,
ui_mobile=%(mobile)s, ui_department_id=%(department_id)s
WHERE ui_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_major_info
SET mi_name=%(name)s, mi_degree=%(degree)s, mi_study_time=%(study_time)s,
mi_department_id=%(department_id)s
WHERE mi_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_course_info
SET ci_name=%(name)s, ci_property=%(property)s, ci_type=%(type)s, ci_period=%(period)s,
ci_credit=%(credit)s,ci_exam_type=%(exam_type)s, ci_department_id=%(department_id)s,
ci_description=%(description)s
WHERE ci_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
INSERT INTO ms_student_info
(si_id, si_name, si_birthday, si_sex, si_mobile, si_native_place,
si_enrollment_year, si_id_card, si_major_id) VALUES
(%(id)s, %(name)s, %(birthday)s, %(sex)s, %(mobile)s,
%(native_place)s, %(enrollment_year)s, %(id_card)s, %(major_id)s);
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="添加成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
UPDATE ms_student_info
SET si_name=%(name)s, si_birthday=%(birthday)s, si_sex=%(sex)s, si_mobile=%(mobile)s,
si_native_place=%(native_place)s, si_enrollment_year=%(enrollment_year)s,
si_id_card=%(id_card)s, si_major_id=%(major_id)s
WHERE si_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
INSERT INTO ms_course_info
(ci_name, ci_property, ci_type, ci_period, ci_credit,
ci_exam_type, ci_department_id, ci_description) VALUES
(%(name)s, %(property)s, %(type)s, %(period)s, %(credit)s,
%(exam_type)s, %(department_id)s, %(description)s);
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="添加成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
INSERT INTO ms_course_section
(cs_course_id, cs_semester, cs_year, cs_capacity,
cs_teacher_id, cs_btime, cs_etime) VALUES
(%(course_id)s, %(semester)s, %(year)s, %(capacity)s,
%(teacher_id)s, %(btime)s, %(etime)s);
""".strip()
try:
DAO.db_execute(self, sql, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="添加成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
SELECT ct_id, ct_student_id, si_name, ct_section_id, ci_name, ct_usual_grade, ct_mid_grade,
ct_final_grade, ct_grade, ct_GPA
FROM ms_course_take JOIN ms_student_info ON si_id=ct_student_id
JOIN ms_course_section ON cs_id=ct_section_id
JOIN ms_course_info ON ci_id=cs_course_id
WHERE CONCAT(ct_id, '') like %(id)s AND
ct_student_id like %(student_id)s AND
ct_section_id like %(section_id)s;
""".strip()
retKeys = [
'id', 'student_id', 'student_name', 'section_id', 'course_name',
'usual_grade', 'mid_grade', 'final_grade', 'grade', 'GPA'
]
self.json_args['id'] = '%{}%'.format(self.json_args['id'])
self.json_args['student_id'] = '%{}%'.format(
self.json_args['student_id'])
self.json_args['section_id'] = '%{}%'.format(
self.json_args['section_id'])
try:
res = DAO.db_query(self, sql, self.json_args, retKeys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
# 验证用户是否为该课授课教师或管理员
sql1 = """
SELECT *
FROM ms_course_section
WHERE cs_teacher_id=%(teacher_id)s AND
cs_id=(SELECT ct_section_id
FROM ms_course_take
WHERE ct_id=%(id)s);
""".strip()
try:
with self.db.cursor() as cursor:
cursor.execute(
sql1, {
'id': self.json_args.get('id'),
'teacher_id': self.session.data.get('user_id')
})
self.db.commit()
if not cursor.fetchone():
cursor.close()
return self.write(
dict(errcode=RET.ROLEERR, errmsg="用户非该课授课教师"))
cursor.close()
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
# 修改数据库
sql2 = """
UPDATE ms_course_take
SET ct_student_id=%(student_id)s, ct_section_id=%(section_id)s,
ct_usual_grade=%(usual_grade)s, ct_mid_grade=%(mid_grade)s,
ct_final_grade=%(final_grade)s, ct_grade=%(grade)s, ct_GPA=%(GPA)s
WHERE ct_id=%(id)s;
""".strip()
try:
DAO.db_execute(self, sql2, self.json_args)
return self.write(dict(errcode=RET.OK, errmsg="修改成功"))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
SELECT sc_id, sc_student_id, si_name, sc_type, sc_level, sc_date, sc_description
FROM ms_student_case JOIN ms_student_info ON sc_student_id=si_id
WHERE sc_student_id like %(student_id)s;
""".strip()
retKeys = ['id', 'student_id','student_name','type', 'level', 'date', 'description']
self.json_args['student_id'] = '%{}%'.format(self.json_args['student_id'])
try:
res = DAO.db_query(self, sql, self.json_args, retKeys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
SELECT ui_id, ui_name, ui_email, ui_mobile, ui_department_id, di_name, ui_permission, ui_role
FROM ms_user_info JOIN ms_department_info ON di_id=ui_department_id
WHERE ui_id=%(id)s
ORDER BY ui_id desc
LIMIT 1;
""".strip()
ret_keys = ['id', 'name', 'email', 'mobile', 'department_id', 'department_name', 'permission', 'role']
try:
res = DAO.db_query(self, sql, {'id': self.session.data['user_id']}, ret_keys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
SELECT di_id, di_name, di_dean, di_address
FROM ms_department_info
WHERE CONCAT(di_id, '') like %(id)s AND
di_name like %(name)s;
""".strip()
retKeys = ['id', 'name', 'dean', 'address']
self.json_args['id'] = '%{}%'.format(self.json_args['id'])
self.json_args['name'] = '%{}%'.format(self.json_args['name'])
try:
res = DAO.db_query(self, sql, self.json_args, retKeys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
SELECT ui_id, ui_name, ui_email, ui_mobile, ui_department_id, di_name, ui_permission, ui_role
FROM ms_user_info JOIN ms_department_info ON di_id=ui_department_id
WHERE ui_id like %(id)s AND
ui_name like %(name)s AND
di_name like %(department_name)s
ORDER BY ui_id desc;
""".strip()
ret_keys = ['id', 'name', 'email', 'mobile', 'department_id', 'department_name', 'permission', 'role']
self.json_args['id'] = '%{}%'.format(self.json_args['id'])
self.json_args['name'] = '%{}%'.format(self.json_args['name'])
self.json_args['department_name'] = '%{}%'.format(self.json_args['department_name'])
try:
res = DAO.db_query(self, sql, self.json_args, ret_keys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="参数错误"))
def post(self):
sql = """
SELECT ci_id, ci_name, ci_property, ci_type, ci_period, ci_credit,
ci_exam_type, ci_department_id, ci_description
FROM ms_course_info
WHERE CONCAT(ci_id, '') like %(id)s AND
ci_name like %(name)s;
""".strip()
retKeys = [
'id', 'name', 'property', 'type', 'period', 'credit', 'exam_type',
'department_id', 'description'
]
self.json_args['id'] = '%{}%'.format(self.json_args['id'])
self.json_args['name'] = '%{}%'.format(self.json_args['name'])
try:
res = DAO.db_query(self, sql, self.json_args, retKeys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def post(self):
sql = """
SELECT si_id, si_name, si_birthday, si_sex, si_mobile,
si_native_place, si_enrollment_year, si_id_card, si_major_id
FROM ms_student_info
WHERE si_id like %(id)s AND
si_name like %(name)s;
""".strip()
ret_keys = [
'id', 'name', 'birthday', 'sex', 'mobile', 'native_place',
'enrollment_year', 'id_card', 'major_id'
]
self.json_args['id'] = '%{}%'.format(self.json_args['id'])
self.json_args['name'] = '%{}%'.format(self.json_args['name'])
try:
res = DAO.db_query(self, sql, self.json_args, ret_keys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="参数错误"))
def post(self):
sql = """
SELECT cs_id, cs_course_id, ci_name, cs_semester, cs_year, cs_capacity,
cs_teacher_id, ui_name, cs_btime, cs_etime
FROM ms_course_section JOIN ms_course_info ON cs_course_id=ci_id
JOIN ms_user_info ON cs_teacher_id=ui_id
WHERE CONCAT(cs_id, '') like %(id)s AND
ci_name like %(name)s
ORDER BY cs_id;
""".strip()
retKeys = [
'id', 'course_id', 'course_name', 'semester', 'year', 'capacity',
'teacher_id', 'teacher_name', 'btime', 'etime'
]
self.json_args['id'] = '%{}%'.format(self.json_args['id'])
self.json_args['name'] = '%{}%'.format(self.json_args['name'])
try:
res = DAO.db_query(self, sql, self.json_args, retKeys)
return self.write(dict(errcode=RET.OK, errmsg="OK", data=res))
except Exception as e:
logging.error(e)
return self.write(dict(errcode=RET.PARAMERR, errmsg="出错"))
def __new__(cls, *args, **kwargs):
cls = super().__new__(cls, *args, **kwargs)
cls.dao = DAO(cls)
return cls
def setUp(self):
# 获取供应商和动作
self.idc = DAO(IDC).get_obj(flag='qcloud')
self.action = DAO(CloudAction).get_obj(flag='query_region')