def getcatenationrank(request):
serial = request.GET.get('serial')
tlist = []
listcatenationrank = []
for cate in ('cate_one_number', 'cate_two_number', 'cate_three_number', 'cate_four_number', 'cate_five_number'):
sql = f"""
SELECT DISTINCT
student_extra.{cate}
FROM
student_extra ,
student
WHERE
student_extra.student_id = student.student_id
AND
student.campus_id NOT IN ('9a0b75a5-cb17-4f92-965b-816a93b606cd','8c8c79d0-ce80-421d-abe8-31dc446b3371')
ORDER BY
student_extra.{cate} DESC
"""
cur = get_sql_done(sql)
seplist = []
for i in cur:
seplist.append(i[0])
tlist.append(seplist)
out = get5sumbase(request)
for item in out:
listcatenationrank = item
nationranklist = get_nation_rank()
relist = [tlist[0].index(listcatenationrank[0]) + 1, tlist[1].index(listcatenationrank[1]) + 1,
tlist[2].index(listcatenationrank[2]) + 1, tlist[3].index(listcatenationrank[3]) + 1,
tlist[4].index(listcatenationrank[4]) + 1, nationranklist.index(listcatenationrank[5]) + 1]
return relist
def getcampusbasicinfo(quest) -> List:
campus_id = quest.GET.get("campus_id")
sql = f"""SELECT
student.student_id,
(student_extra.cate_one_number+
student_extra.cate_two_number+
student_extra.cate_three_number+
student_extra.cate_four_number+
student_extra.cate_five_number) AS amount,
student_extra.cate_one_number,
student_extra.cate_two_number,
student_extra.cate_three_number,
student_extra.cate_four_number,
student_extra.cate_five_number
FROM
student ,
student_extra
WHERE
student.student_id = student_extra.student_id AND
student.campus_id = '{campus_id}'
ORDER BY amount DESC"""
cur = get_sql_done(sql)
li = []
for i in cur:
li.append(list(i))
return li
def getteacherclazz(quest) -> List:
serial = quest.GET.get("serial")
sql = f"""
SELECT DISTINCT
stu_class_current.class_id,
stu_class.`name`,
SUBSTR(stu_class.`name` FROM 4 FOR 7) num
FROM
stu_class_current ,
student ,
stu_class
WHERE
student.student_id = stu_class_current.student_id AND
student.campus_id = LCASE((SELECT
employee.campus_id
FROM
employee
WHERE
employee.serial = '{serial}')) AND
stu_class_current.class_id = stu_class.class_id
ORDER BY num"""
cur = get_sql_done(sql)
tlist = []
for i in cur:
tlist.append(list(i)[:2])
print(type(tlist))
return tlist
def getcardname() -> List:
sql1 = """SELECT
0,
card.card_name,
card.id
FROM
card
"""
cur = get_sql_done(sql1)
lim = []
for i in cur:
lim.append(list(i))
return lim
def get5sumbase(request) -> List:
serial = request.GET.get('serial')
sql = f"""
SELECT
student_extra.cate_one_number,
student_extra.cate_two_number,
student_extra.cate_three_number,
student_extra.cate_four_number,
student_extra.cate_five_number,
( student_extra.cate_one_number + student_extra.cate_two_number + student_extra.cate_three_number + student_extra.cate_four_number + student_extra.cate_five_number ) AS card_sum
FROM
student_extra,
student
WHERE
student_extra.student_id = student.student_id
AND student.serial = '{serial}'
"""
cur = get_sql_done(sql)
return cur
def get_nation_rank() -> List:
sql0 = f'''SELECT b.*,( @ro := @ro + 1 ) AS rank FROM ( SELECT DISTINCT ( student_extra.cate_one_number + student_extra.cate_two_number + student_extra.cate_three_number + student_extra.cate_four_number + student_extra.cate_five_number ) AS sum
FROM
student_extra,
student
WHERE
student.student_id = student_extra.student_id
AND student.campus_id NOT IN ( '9a0b75a5-cb17-4f92-965b-816a93b606cd','8c8c79d0-ce80-421d-abe8-31dc446b3371' )
AND student.`name` NOT LIKE '试听%'
ORDER BY
sum DESC
) b,
( SELECT @ro := 0 ) c'''
out = get_sql_done(sql0)
list = []
for i in out:
list.append(i[0])
# 反回一个从到小的卡牌数据列表，排名可用 index() 方法获得
return list
def getclassrank(request):
serial = request.GET.get('serial')
allrank = []
for item in (
'a.cate_one_number', 'a.cate_two_number', 'a.cate_three_number', 'a.cate_four_number', 'a.cate_five_number',
'(a.cate_one_number+a.cate_two_number+a.cate_three_number+a.cate_four_number+ a.cate_five_number)'):
sql = f"""SELECT
{item}
FROM
student_extra a
WHERE
a.student_id IN (SELECT
stu_class_current.student_id
FROM
stu_class_current
WHERE
stu_class_current.class_id = (SELECT
stu_class_current.class_id
FROM
student ,
stu_class_current
WHERE
student.serial = '{serial}' AND
student.student_id = stu_class_current.student_id))
"""
cur = get_sql_done(sql)
seprank = []
for cr in cur:
seprank.append(cr[0])
seprank.sort(reverse=True)
allrank.append(seprank)
bcur = get5sumbase(request)
blist = []
for isb in bcur:
blist = list(isb)
print(isb)
# 最终结果是一个数组
print(blist)
print(allrank)
relist = [allrank[0].index(blist[0]) + 1, allrank[1].index(blist[1]) + 1, allrank[2].index(blist[2]) + 1,
allrank[3].index(blist[3]) + 1, allrank[4].index(blist[4]) + 1, allrank[5].index(blist[5]) + 1]
return relist
def getteacherbasicinfo(request):
serial = request.GET.get("serial")
sql = f"""
SELECT
employee.`name`,
employee.serial,
SUBSTRING_INDEX( campus.campus_name, "（",1 ) campus_name,
SUBSTR( campus.campus_name FROM - 6 FOR 5 ) campus_serial
FROM
employee,
campus
WHERE
employee.campus_id = campus.campus_id
AND employee.serial = '{serial}'
"""
cur = get_sql_done(sql)
for item in cur:
li = item
return list(li)
def getcampusnsn() -> List:
sql = """
SELECT
campus.campus_id,
SUBSTRING_INDEX(campus.campus_name, '（', 1)
campus,
SUBSTRING(campus.campus_name
FROM - 6
FOR
5 ) num
FROM
campus
WHERE
campus.campus_id
NOT
IN('8c8c79d0-ce80-421d-abe8-31dc446b3371', '9a0b75a5-cb17-4f92-965b-816a93b606cd')"""
cur = get_sql_done(sql)
li = []
for i in cur:
li.append(list(i))
return li
def getcampusrank(request):
serial = request.GET.get('serial')
allrank = []
for item in (
"b.cate_one_number", "b.cate_two_number", "b.cate_three_number", "b.cate_four_number", "cate_five_number",
"(b.cate_one_number+b.cate_two_number+b.cate_three_number+b.cate_four_number+b.cate_five_number)"):
sql = f"""SELECT distinct {item} FROM
(SELECT
student.student_id as student_id,
student.`name`,
student.serial,
student.campus_id
FROM
student
WHERE
student.campus_id NOT IN ( '9a0b75a5-cb17-4f92-965b-816a93b606cd', '8c8c79d0-ce80-421d-abe8-31dc446b3371' ) AND
student.`name` NOT LIKE '试听%') oo
left join student_extra b
on oo.student_id=b.student_id WHERE oo.campus_id=(SELECT
student.campus_id
FROM
student
WHERE
student.serial = '{serial}')"""
curout = get_sql_done(sql)
singlecnt = []
for i in curout:
if i[0] != None:
# print(i[0])
singlecnt.append(i[0])
singlecnt.sort(reverse=True)
allrank.append(singlecnt)
stusore = get5sumbase(request)
li = []
for ite in stusore:
li = list(ite)
relist = [allrank[0].index(li[0]) + 1, allrank[1].index(li[1]) + 1, allrank[2].index(li[2]) + 1,
allrank[3].index(li[3]) + 1, allrank[4].index(li[4]) + 1, allrank[5].index(li[5]) + 1, ]
return relist
def getteachercourse(quest) -> List:
serial = quest.GET.get("serial")
sql = f"""
SELECT DISTINCT
course_extra.course_id,
course_extra.course_name
FROM
student_card ,
course_extra
WHERE
student_card.course_id = course_extra.course_id AND
student_card.employee_id = ( SELECT employee.employee_id FROM employee WHERE employee.serial = ( '{serial}' ) )"""
li = []
cur = get_sql_done(sql)
for i in cur:
li.append(list(i))
cname = []
for ite in li:
if ite[1] not in cname:
cname.append(ite[1])
return cname
def get_student_basic_info(request) -> List:
serial = request.GET.get('serial')
sql = f"""SELECT
student.`name`,
student.serial,
stu_class.`name` AS class,
b.class_cnt,
campus.campus_name
FROM
student ,
stu_class_current ,
stu_class ,
campus,
(SELECT
Count(stu_class_current.class_id) class_cnt
FROM
stu_class_current
WHERE
stu_class_current.class_id = (SELECT
stu_class.class_id
FROM
student ,
stu_class ,
stu_class_current
WHERE
student.serial = '{serial}' AND
student.student_id = stu_class_current.student_id AND
stu_class_current.class_id = stu_class.class_id)) b
WHERE
student.student_id = stu_class_current.student_id AND
stu_class_current.class_id = stu_class.class_id AND
stu_class.campus_id = campus.campus_id AND
student.serial = '{serial}'"""
cur = get_sql_done(sql)
li = []
for item in cur:
li = item
return li
def getteachercard(quest):
serial = quest.GET.get("serial")
sql = f"""
SELECT
Count(student_card.card_num) AA,
card.card_name,
card_id
FROM
student_card ,
card
WHERE
student_card.employee_id = (SELECT employee.employee_id FROM employee WHERE employee.serial = '{serial}') AND
student_card.card_id = card.id
GROUP BY
student_card.card_id
ORDER BY card_id DESC
"""
cur = get_sql_done(sql)
# 用于生成固定格式且固定长度的列表
lii = getcardname()
for item in cur:
lii[item[2] - 1] = list(item)
return lii