class PhysicalOrCommonScore(Base, db.Model):
"""课程成绩 Model
"""
_cn_list = ["学期", "课程", "课程号", "学分", "正考", "补考", "绩点"]
_en_list = [
"term",
"course_name",
"course_id",
"credit",
"score",
"make_up_score",
"gpa",
]
__tablename__ = "score_physic_or_common"
id_ = Column("id",
db.Integer,
db.Sequence("physic_or_common_score_id_seq"),
primary_key=True)
student_id = Column(
db.String(32),
db.ForeignKey("user.student_id",
onupdate="CASCADE",
ondelete="SET NULL"),
primary_key=True,
)
course_id = Column(db.String(16), primary_key=True)
term = Column(db.String(64), primary_key=True) # 学期
course_name = Column(db.String(64))
credit = Column(db.String(16))
score = Column(db.String(16)) # 可能考试成绩是 `通过`
make_up_score = Column(db.String(16)) # 补考成绩
gpa = Column(db.String(16))
cata = Column(db.String(16))
@classmethod
async def add_or_update_one(cls, student_id, cata, series):
kwargs = dict(student_id=student_id, cata=cata)
for cn_key, en_key in zip(cls._cn_list, cls._en_list):
kwargs[en_key] = series[cn_key]
sco = (await cls.query.where(cls.student_id == student_id
).where(cls.course_id == series["课程号"]
).where(cls.term == series["学期"]
).gino.first())
if sco:
await sco.update(**kwargs).apply()
else:
await cls.create(**kwargs)
@classmethod
async def add_or_update(cls, student_id, score_dict, cata):
table = score_dict[cata]
for _, series in table.iterrows():
await cls.add_or_update_one(student_id, cata, series)
class CETScore(Base, db.Model):
"""计划课程成绩 Model
"""
_cn_list = ["准考证号", "考试场次", "语言级别", "总分", "听力", "阅读", "写作", "综合"]
_en_list = [
"exam_id",
"exam_name",
"level",
"total",
"listen",
"read",
"write",
"common",
]
__tablename__ = "score_cet"
id_ = Column("id",
db.Integer,
db.Sequence("cet_score_id_seq"),
primary_key=True)
student_id = Column(
db.String(32),
db.ForeignKey("user.student_id",
onupdate="CASCADE",
ondelete="SET NULL"),
primary_key=True,
)
exam_id = Column(db.String(32), primary_key=True)
exam_name = Column(db.String(64))
level = Column(db.String(16))
total = Column(db.String(16))
listen = Column(db.String(16))
read = Column(db.String(16))
write = Column(db.String(16))
common = Column(db.String(16))
@classmethod
async def add_or_update_one(cls, student_id, series):
kwargs = dict(student_id=student_id)
for cn_key, en_key in zip(cls._cn_list, cls._en_list):
kwargs[en_key] = series[cn_key]
sco = (await cls.query.where(cls.student_id == student_id
).where(cls.exam_id == series["准考证号"]
).gino.first())
if sco:
await sco.update(**kwargs).apply()
else:
await cls.create(**kwargs)
@classmethod
async def add_or_update(cls, student_id, table):
for _, series in table.iterrows():
await cls.add_or_update_one(student_id, series)
class CourseStudent(Base, db.Model):
"""学生选课表 Model
"""
__tablename__ = "course_student"
student_id = Column(
db.String(32),
db.ForeignKey("user.student_id",
onupdate="CASCADE",
ondelete="SET NULL"),
primary_key=True,
)
course_json = Column(JSONB, nullable=False, server_default="{}")
def __str__(self):
return f"<CourseStudent id:{self.student_id}>"
@classmethod
async def add_or_update(cls, student_id, course_json) -> "CourseStudent":
c_stu = await cls.get(student_id)
if c_stu:
await c_stu.update(course_json=course_json).apply()
else:
c_stu = await cls.create(student_id=student_id,
course_json=course_json)
return c_stu
@classmethod
async def get_course(cls, qq: int) -> Union["CourseStudent", str]:
if not await User.check(qq):
return "NOT_BIND"
_bot = get_bot()
query = cls.join(User).select()
course_student = await query.where(User.qq == str(qq)).gino.first()
if course_student is None:
await add_job(cls.update_course, args=[qq])
await _bot.send(qq2event(qq), "正在抓取课表，抓取过后我会直接发给你！")
return "WAIT"
return course_student
@classmethod
async def update_course(cls, qq: int):
user: User = await User.get(str(qq))
if not user:
return
_bot = get_bot()
sess = await User.get_session(user)
res = await run_sync_func(get_course_api, sess)
if res:
c_stu = await cls.add_or_update(student_id=user.student_id,
course_json=json.dumps(res))
await call_command(_bot, qq2event(qq), "cs")
return c_stu
class SubUser(Base, db.Model):
"""用户订阅 Model
"""
__tablename__ = "sub_user"
ctx_id = db.Column(db.String(64), primary_key=True)
link = db.Column(
db.String(128),
db.ForeignKey("sub_content.link",
onupdate="CASCADE",
ondelete="SET NULL"),
primary_key=True,
)
only_title = db.Column(db.Boolean, default=True)
def __repr__(self):
return f"<SubUser {self.ctx_id} {self.link}>"
@classmethod
async def add_sub(cls, event: Event, url: str, only_title=False):
# TODO: UTF8
try:
d = await get_rss_info(url)
except Exception as e:
logger.exception(e)
await send(get_bot(), event, "获取订阅信息失败，但已添加到订阅中，我们会稍后重试。")
d = {"channel": {}, "entries": []}
if not d:
return None
info = d["channel"]
title = info.get("title", url)
sub = await SubContent.add_or_update(
link=url,
name=title,
content=pickle.dumps(d),
)
await SubUser.create(ctx_id=context_id(event, mode="group"),
link=sub.link,
only_title=only_title)
return title
@classmethod
async def get_sub(cls, event: Event, url: str):
ctx_id = context_id(event, mode="group")
loader = SubUser.load(sub_content=SubContent)
sub = (await
cls.outerjoin(SubContent).select().where(cls.link == url).where(
cls.ctx_id == ctx_id).gino.load(loader).first())
return sub
@classmethod
async def get_user_subs(cls, event: Event) -> List["SubUser"]:
ctx_id = context_id(event, mode="group")
loader = SubUser.load(sub_content=SubContent)
sub = (await cls.outerjoin(SubContent).select().where(
cls.ctx_id == ctx_id).gino.load(loader).all())
return sub
@classmethod
async def remove_sub(cls, event: Event, url: str):
ctx_id = context_id(event, mode="group")
sub = (await
cls.query.where(cls.link == url).where(cls.ctx_id == ctx_id
).gino.first())
await sub.delete()
return True
@classmethod
async def get_users(cls, url: str):
sub = await cls.query.where(cls.link == url).gino.all()
return sub
class PlanScore(Base, db.Model):
"""计划课程成绩 Model
"""
_cn_list = ["课程", "课程号", "学分", "课程性质", "正考", "补考", "绩点"]
_en_list = [
"course_name",
"course_id",
"credit",
"property_",
"score",
"make_up_score",
"gpa",
]
__tablename__ = "score_plan"
id_ = Column("id",
db.Integer,
db.Sequence("plan_score_id_seq"),
primary_key=True)
student_id = Column(
db.String(32),
db.ForeignKey("user.student_id",
onupdate="CASCADE",
ondelete="SET NULL"),
primary_key=True,
)
course_id = Column(db.String(16), primary_key=True)
term = Column(db.String(64), primary_key=True) # 学期
course_name = Column(db.String(64))
property_ = Column("property", db.String(64)) # 必修 选修 限选
credit = Column(db.String(16))
score = Column(db.String(16)) # 可能考试成绩是 `通过`
make_up_score = Column(db.String(16)) # 补考成绩
gpa = Column(db.String(16))
season = Column(db.String(16)) # 春季 秋季 term中表现为春季2 秋季1
@classmethod
async def add_or_update_one(cls, student_id, term, season, series):
kwargs = dict(student_id=student_id, term=term, season=season)
for cn_key, en_key in zip(cls._cn_list, cls._en_list):
kwargs[en_key] = series[cn_key]
sco = (await
cls.query.where(cls.student_id == student_id
).where(cls.course_id == series["课程号"]
).where(cls.term == term).gino.first())
if sco:
await sco.update(**kwargs).apply()
else:
await cls.create(**kwargs)
@classmethod
async def add_or_update(cls, student_id, plan):
terms = pd.unique(plan["term"])
for term in terms:
_term = plan[plan["term"] == term]
seasons = pd.unique(_term["season"])
for season in seasons:
data = _term[_term["season"] == season]
for _, series in data.iterrows():
await cls.add_or_update_one(student_id, term, season,
series)
@classmethod
def to_df(cls, plan_scores: List["PlanScore"]):
dct = defaultdict(list)
for item in plan_scores:
for en, cn in zip(PlanScore._en_list, PlanScore._cn_list):
dct[cn].append(getattr(item, en, None))
dct["term"].append(getattr(item, "term", None))
dct["season"].append(getattr(item, "season", None))
df = pd.DataFrame(data=dct)
return df
@classmethod
async def load_score(cls, event: Event) -> pd.DataFrame:
user = await User.check(event.user_id)
if not user:
return
scores = await cls.query.where(cls.student_id == user.student_id
).gino.all()
if not scores:
return
df = cls.to_df(scores)
return df