222 lines
9.2 KiB
Python
222 lines
9.2 KiB
Python
from model.student_info_region_model import Student_info
|
|
from model.score_model import ScoreRecord
|
|
from model.class_model import Class_info
|
|
from model.employment_model import Employment
|
|
from sqlalchemy import func, text
|
|
|
|
GENDER_MAP = {0: '女', 1: '男'}
|
|
STATUS_MAP = {0: '在读', 1: '休学', 2: '退学', 3: '毕业', 4: '结业'}
|
|
EDUCATION_MAP = {1: '大专', 2: '本科', 3: '硕士研究生', 4: '博士研究生'}
|
|
|
|
|
|
class StatisticsDao:
|
|
@staticmethod
|
|
def get_students_by_age_range_dao(n: int, m: int, min_age: int, max_age: int, db):
|
|
try:
|
|
age = func.timestampdiff(text('YEAR'), Student_info.birthday, func.curdate())
|
|
q = db.query(Student_info). \
|
|
filter(Student_info.is_deleted == '0'). \
|
|
filter(age >= min_age). \
|
|
filter(age <= max_age)
|
|
total = q.count()
|
|
req = q.offset((n - 1) * m).limit(m).all()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
data = [
|
|
{
|
|
"student_id": s.student_id,
|
|
"student_name": s.student_name,
|
|
"gender": GENDER_MAP.get(s.gender, str(s.gender)),
|
|
"id_card": s.id_card,
|
|
"birthday": s.birthday,
|
|
"ethnicity": s.ethnicity,
|
|
"region_id": s.region_id,
|
|
"phone": s.phone,
|
|
"major": s.major,
|
|
"class_id": s.class_id,
|
|
"enrollment_date": s.enrollment_date,
|
|
"graduation_date": s.graduation_date,
|
|
"student_status": STATUS_MAP.get(s.student_status, str(s.student_status)),
|
|
"education_level": EDUCATION_MAP.get(s.education_level, str(s.education_level)),
|
|
}
|
|
for s in req
|
|
]
|
|
return data, total
|
|
|
|
@staticmethod
|
|
def get_students_by_class_id_dao(class_id: str, db):
|
|
try:
|
|
q = db.query(Student_info).filter(Student_info.class_id == class_id). \
|
|
filter(Student_info.is_deleted == '0')
|
|
total_count = q.count()
|
|
male_count = q.filter(Student_info.gender == 1).count()
|
|
female_count = q.filter(Student_info.gender == 0).count()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
return total_count, male_count, female_count
|
|
|
|
@staticmethod
|
|
def get_students_by_score_dao(n: int, m: int, score: float, db):
|
|
try:
|
|
q = (db.query(
|
|
Student_info.student_name,
|
|
Student_info.student_id,
|
|
func.min(ScoreRecord.score).label('score')
|
|
).join(ScoreRecord, Student_info.student_id == ScoreRecord.student_id)
|
|
.filter(Student_info.is_deleted == '0')
|
|
.filter(ScoreRecord.is_deleted == 0)
|
|
.group_by(Student_info.student_id, Student_info.student_name)
|
|
.having(func.min(ScoreRecord.score) > score))
|
|
total = q.count()
|
|
req = q.offset((n - 1) * m).limit(m).all()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
return [{"student_name": i.student_name,
|
|
'student_id': i.student_id,
|
|
"score": float(i.score) if i.score is not None else None} for i in req], total
|
|
|
|
@staticmethod
|
|
def get_student_by_no_pass_dao(n: int, m: int, fail_count: int, db):
|
|
try:
|
|
q = (db.query(
|
|
Student_info.student_id,
|
|
Student_info.student_name,
|
|
func.count(ScoreRecord.score_id).label('fail_count')
|
|
).join(ScoreRecord, Student_info.student_id == ScoreRecord.student_id)
|
|
.filter(Student_info.is_deleted == '0')
|
|
.filter(ScoreRecord.is_deleted == 0)
|
|
.filter(ScoreRecord.is_pass == 0)
|
|
.group_by(Student_info.student_id, Student_info.student_name)
|
|
.having(func.count(ScoreRecord.score_id) >= fail_count))
|
|
total = q.count()
|
|
req = q.offset((n - 1) * m).limit(m).all()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
return [{"student_id": i.student_id,
|
|
"student_name": i.student_name,
|
|
"fail_count": i.fail_count} for i in req], total
|
|
|
|
@staticmethod
|
|
def get_class_exam_avg_score_dao(n: int, m: int, db):
|
|
try:
|
|
q = (db.query(
|
|
ScoreRecord.course_id,
|
|
Student_info.class_id,
|
|
Class_info.class_name,
|
|
func.avg(func.ifnull(ScoreRecord.score, 0)).label('avg_score')
|
|
).join(Student_info, ScoreRecord.student_id == Student_info.student_id)
|
|
.join(Class_info, Student_info.class_id == Class_info.class_id)
|
|
.filter(Student_info.is_deleted == '0')
|
|
.filter(ScoreRecord.is_deleted == 0)
|
|
.filter(Class_info.is_deleted == False)
|
|
.group_by(ScoreRecord.course_id,
|
|
Student_info.class_id,
|
|
Class_info.class_name)
|
|
.order_by(func.avg(func.ifnull(ScoreRecord.score, 0)).desc()))
|
|
total = q.count()
|
|
req = q.offset((n - 1) * m).limit(m).all()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
return [{"course_id": i.course_id,
|
|
"class_id": i.class_id,
|
|
"class_name": i.class_name,
|
|
"avg_score": round(float(i.avg_score), 2)} for i in req], total
|
|
|
|
@staticmethod
|
|
def get_salary_top_dao(m: int, db):
|
|
try:
|
|
sq = (db.query(
|
|
Employment.student_id,
|
|
func.max(Employment.salary).label('max_salary')
|
|
).filter(Employment.is_deleted == 0)
|
|
.group_by(Employment.student_id)
|
|
.order_by(func.max(Employment.salary).desc())
|
|
.limit(m)
|
|
.subquery())
|
|
|
|
q = (db.query(
|
|
Student_info.student_name,
|
|
Class_info.class_name,
|
|
Employment.offer_date,
|
|
Employment.company_name,
|
|
Employment.salary
|
|
).join(Employment, Student_info.student_id == Employment.student_id)
|
|
.join(Class_info, Student_info.class_id == Class_info.class_id)
|
|
.join(sq,
|
|
(Employment.student_id == sq.c.student_id) &
|
|
(Employment.salary == sq.c.max_salary))
|
|
.filter(Student_info.is_deleted == '0')
|
|
.filter(Class_info.is_deleted == False)
|
|
.filter(Employment.is_deleted == 0))
|
|
|
|
req = q.all()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
return [{"student_name": i.student_name,
|
|
"class_name": i.class_name,
|
|
"offer_date": i.offer_date,
|
|
"company_name": i.company_name,
|
|
"salary": float(i.salary) if i.salary is not None else None} for i in req]
|
|
|
|
@staticmethod
|
|
def get_time_size_dao(n: int, m: int, db):
|
|
try:
|
|
q = (db.query(
|
|
Student_info.student_id,
|
|
Student_info.student_name,
|
|
Employment.offer_date,
|
|
Employment.resume_open_date,
|
|
func.datediff(Employment.offer_date, Employment.resume_open_date).label('time_size')
|
|
).join(Employment, Student_info.student_id == Employment.student_id)
|
|
.filter(Student_info.is_deleted == '0')
|
|
.filter(Employment.is_deleted == 0)
|
|
.filter(Employment.resume_open_date.isnot(None)))
|
|
|
|
total = q.count()
|
|
req = q.offset((n - 1) * m).limit(m).all()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
return [{"student_id": i.student_id,
|
|
"student_name": i.student_name,
|
|
"offer_date": i.offer_date,
|
|
"resume_open_date": i.resume_open_date,
|
|
"time_size": i.time_size} for i in req], total
|
|
|
|
@staticmethod
|
|
def get_class_avg_time_size_dao(n: int, m: int, db):
|
|
try:
|
|
q = (db.query(
|
|
Class_info.class_id,
|
|
Class_info.class_name,
|
|
func.avg(func.datediff(Employment.offer_date, Employment.resume_open_date)).label('avg_time_size')
|
|
).join(Student_info, Class_info.class_id == Student_info.class_id)
|
|
.join(Employment, Student_info.student_id == Employment.student_id)
|
|
.filter(Class_info.is_deleted == False)
|
|
.filter(Student_info.is_deleted == '0')
|
|
.filter(Employment.is_deleted == 0)
|
|
.filter(Employment.resume_open_date.isnot(None))
|
|
.group_by(Class_info.class_id, Class_info.class_name))
|
|
|
|
total = q.count()
|
|
req = q.offset((n - 1) * m).limit(m).all()
|
|
except Exception as e:
|
|
db.rollback()
|
|
raise e
|
|
else:
|
|
return [{"class_id": i.class_id,
|
|
"class_name": i.class_name,
|
|
"avg_time_size": round(float(i.avg_time_size), 2)} for i in req], total |