class ChatRecords(Base, db.Model):
"""保存聊天记录 Model
"""
__tablename__ = "chat_records"
id_ = Column("id", db.Integer, db.Sequence("chat_records_id_seq"), primary_key=True)
self_id = Column(db.String(32))
ctx_id = Column(db.String(64))
msg = Column(db.String)
out = Column(db.Boolean, default=False)
@classmethod
async def add_msg(cls, event: Event, out: bool = False):
await ChatRecords.create(
self_id=str(event.self_id),
ctx_id=context_id(event),
msg=str(event.message),
out=out,
),
@classmethod
async def get_last_msg(cls, event: Event):
return (
await ChatRecords.query.where(cls.ctx_id == context_id(event))
.where(cls.self_id == str(event.self_id),)
.order_by(cls.id_.desc())
.gino.first()
)
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 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 SubContent(Base, db.Model):
"""所有订阅内容 Model
"""
__tablename__ = "sub_content"
link = db.Column(db.String(128), primary_key=True)
name = db.Column(db.String(128))
content = db.Column(db.LargeBinary)
@classmethod
async def add_or_update(cls, link, name, content) -> "SubContent":
sub = await cls.get(link)
if sub:
await sub.update(link=link, name=name, content=content).apply()
else:
sub = await cls.create(link=link, name=name, content=content)
return sub
@classmethod
async def check_update(cls):
logger.info("开始检查RSS更新")
all_subs = await SubContent.query.gino.all()
if all_subs:
await asyncio.wait([cls._check_one(sub) for sub in all_subs])
logger.info("结束检查RSS更新")
@classmethod
async def _check_one(cls, sub):
users = await SubUser.get_users(sub.link)
if not users:
return
logger.info("检查" + sub.name + "更新")
logger.info(sub.name + "的用户们：" + str(users))
event_list = [ctx_id2event(user.ctx_id) for user in users]
content = await get_rss_info(sub.link)
old_content = pickle.loads(sub.content)
diffs = diff(content, old_content)
logger.info(sub.name + "的更新" + str(diffs))
msgs = mk_msg_content(content, diffs)
await asyncio.gather(*[send_msgs(event, msgs) for event in event_list])
await SubContent.add_or_update(sub.link, sub.name,
pickle.dumps(content))
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 User(Base, db.Model):
"""用户表 Model
"""
__tablename__ = "user"
qq = Column(db.String(16), primary_key=True)
student_id = Column(db.String(32), unique=True)
password = Column(db.String(64), nullable=False)
cookies = Column(db.LargeBinary)
@classmethod
async def add(cls, *, qq: int, student_id: int, password: str, cookies):
user = User(
student_id=str(student_id), qq=str(qq), password=password, cookies=cookies,
)
await user.create()
return user
@classmethod
async def unbind(cls, qq: int):
user = await User.query.where(User.qq == str(qq)).gino.first()
await user.delete()
return True
@classmethod
async def check(cls, qq: int):
user = await cls.get(str(qq))
if user:
return user
_bot = get_bot()
await _bot.send(qq2event(qq), "未绑定，试试对我发送 `绑定`")
return False
@classmethod
async def get_cookies(cls, user: "******"):
from auth_swust import Login
from auth_swust import request as login_request
cookies = pickle.loads(user.cookies)
sess = login_request.Session(cookies)
res = await run_sync_func(
sess.get, API.jwc_index, allow_redirects=False, verify=False
)
# 302重定向了，session 失效，重新获取session
if res.status_code == 302 or res.status_code == 301:
u_ = Login(user.student_id, user.password)
is_log, _ = await run_sync_func(u_.try_login)
if is_log:
cookies = pickle.dumps(u_.get_cookies())
await user.update(cookies=cookies).apply()
logger.info(f"更新qq: {user.qq} 的 session")
return u_.get_cookies()
else:
return cookies
@classmethod
async def get_session(cls, user: "******"):
from auth_swust import request as login_request
key = f"cookies/{user.qq}"
cookies = await cache.get(key)
if not cookies:
cookies = await cls.get_cookies(user)
await cache.set(key, cookies, ttl=Config.CACHE_SESSION_TIMEOUT)
sess = login_request.Session(cookies)
return sess
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