async def get_item_list(key_words=None, type="sell"):
session = DBSession()
results = []
if key_words:
for word in key_words:
like_str = ""
for c in word:
like_str += f'%{c}'
like_str += '%'
results.extend(
session.query(SellItem).filter(
SellItem.type == type, SellItem.is_onsell == True,
SellItem.item_name.like(like_str)).order_by(
SellItem.add_time).all())
else:
results.extend(
session.query(SellItem).filter(
SellItem.type == type,
SellItem.is_onsell == True,
).order_by(SellItem.add_time).all())
session.close()
return sorted(list(set([i for i in results])),
key=lambda x: x.add_time,
reverse=True)
async def get_homework_info(group_id, date: date, subjects=None):
like_str = ""
session = DBSession()
results = []
if subjects:
for subject in subjects:
for c in subject:
like_str += f'%{c}'
like_str += '%'
results.extend(
session.query(Homework).filter(
Homework.group_id == group_id, Homework.added_date <= date,
Homework.end_date >= date,
Homework.subject_name.like(like_str)).order_by(
Homework.end_date).all())
else:
results.extend(
session.query(Homework).filter(
Homework.group_id == group_id,
Homework.added_date <= date,
Homework.end_date >= date,
).order_by(Homework.end_date).all())
session.close()
return sorted(list(set([i for i in results])), key=lambda x: x.end_date)
async def invite_by(qq_id, invite_by):
"""
更新用户的邀请者
:param qq_id:
:param invite_by:
:return:
"""
session = DBSession()
user_invite = session.query(User_invite).filter(
User_invite.qq_id == qq_id).first()
if not user_invite:
session.close()
await init_user_invite(qq_id=qq_id,
nickname=await get_qq_user_info(qq_id))
invite_by(qq_id, invite_by)
else:
if not user_invite.invite_by:
user_invite.invite_by = invite_by
else:
raise RuntimeError
session.merge(user_invite)
session.commit()
# 判断是否邀请到足够人数
invite_user_num = session.query(User_invite).filter(
User_invite.invite_by == invite_by).count()
if invite_user_num == 3:
await invite.get_sell_card(invite_by)
session.close()
return True
async def add_item(item_name,
item_info,
seller_id,
type='sell',
from_group_id=None,
add_time=None,
**kw):
if add_time is None:
add_time = datetime.now()
item = SellItem(item_name=item_name,
item_info=item_info,
seller_id=seller_id,
from_group_id=from_group_id,
add_time=add_time,
type=type,
is_onsell=True)
session = DBSession()
session.add(item)
session.commit()
item = session.query(SellItem).filter(
SellItem.seller_id == seller_id,
SellItem.item_name == item_name,
SellItem.item_info == item_info,
).first()
session.close()
return item
async def add_QQUser(qq_id,
nickname,
group_id=None,
group_card=None,
sex=None,
group_role=None,
**kw):
session = DBSession()
qqUser = session.query(QQUser).filter(QQUser.qq_id == qq_id).first()
if qqUser:
qqUser.nickname = nickname
if (group_id):
qqUser.group_id = group_id
qqUser.group_role = group_role
qqUser.sex = sex
session.merge(qqUser)
session.commit()
session.close()
return 1
else:
qqUser = QQUser(qq_id=qq_id,
group_id=group_id,
nickname=nickname,
group_card=group_card,
sex=sex,
group_role=group_role,
join_time=datetime.now())
session.add(qqUser)
session.commit()
session.close()
return 0
async def del_item(id):
session = DBSession()
item = session.query(SellItem).filter(SellItem.id == id).first()
item.is_onsell = not item.is_onsell
session.merge(item)
session.commit()
session.close()
async def add_group_info(group_id,
group_name,
last_weather_notify=None,
is_morningcall_on=False,
is_curriculumschedule_on=True,
is_weather_notify_on=False,
is_homework_daily_on=False):
session = DBSession()
group = session.query(GroupInfo).filter(
GroupInfo.group_id == group_id).first()
if group:
group.group_id = group_id
group.group_name = group_name
session.merge(group)
session.commit()
session.close()
return 1
else:
group = GroupInfo(group_id=group_id,
group_name=group_name,
last_weather_notify=last_weather_notify,
is_morningcall_on=is_morningcall_on,
is_curriculumschedule_on=is_curriculumschedule_on,
is_weather_notify_on=is_weather_notify_on,
is_homework_daily_on=is_homework_daily_on)
session.add(group)
session.commit()
session.close()
return 0
async def add_homework_info(group_id,
subject_name,
content,
end_date,
assign_for=None,
added_date=None,
add_by=None):
if added_date is None:
added_date = date.today()
homework = Homework(group_id=group_id,
subject_name=subject_name,
content=content,
end_date=end_date,
assign_for=assign_for,
added_date=added_date,
add_by=add_by)
session = DBSession()
session.add(homework)
session.commit()
homework = session.query(Homework).filter(
Homework.group_id == group_id, Homework.subject_name == subject_name,
Homework.content == content, Homework.assign_for == assign_for,
Homework.added_date == added_date, Homework.add_by == add_by).first()
session.close()
return homework
async def join_event(user_id, nickname, sex):
session = DBSession()
dbuser = session.query(CPuser).filter(CPuser.qq_id == user_id).first()
if dbuser:
session.close()
return False
cpuser = CPuser(qq_id=user_id,
user_name=nickname,
user_sex=sex,
is_paired=False)
session.add(cpuser)
session.commit()
session.close()
session = DBSession()
cpuser = session.query(CPuser).filter(CPuser.qq_id == user_id).first()
session.close()
return cpuser
async def get_my_item(seller_id):
session = DBSession()
item = session.query(SellItem).filter(
SellItem.seller_id == seller_id,
SellItem.is_onsell == True,
).all()
session.close()
return item
async def get_all_group_info():
try:
session = DBSession()
group = session.query(GroupInfo).all()
session.close()
except Exception as e:
raise RuntimeError('更新notify时间时失败：' + str(e))
return group
async def update_last_weather_notify(group_id, last_weather_notify):
session = DBSession()
try:
group = session.query(GroupInfo).filter(
GroupInfo.group_id == group_id).first()
group.last_weather_notify = last_weather_notify
session.merge(group)
session.commit()
except Exception:
session.rollback()
session.close()
async def update_item(id, item_name=None, item_info=None):
session = DBSession()
item = session.query(SellItem).filter(SellItem.id == id).first()
if item_name:
item.item_name = item_name
if item_info:
item.item_info = item_info
item.add_time = datetime.now()
session.merge(item)
session.commit()
session.close()
async def find_the_other(user_sex):
if user_sex == 1:
findsex = 2
else:
findsex = 1
session = DBSession()
theOther = session.query(CPuser).filter(
CPuser.user_sex == findsex,
CPuser.is_paired == 0,
).first()
session.close()
return theOther
def getClassInfo(week, weekday, group_id, classnums) -> list:
session = DBSession()
curriculums = []
rs = session.query(Curriculum).filter(
Curriculum.group_id == group_id, Curriculum.weekday == weekday + 1,
Curriculum.begin_week <= week, Curriculum.end_week >= week,
or_(*[Curriculum.class_num == i for i in classnums])).order_by(
Curriculum.class_num).order_by(Curriculum.group_name).all()
#session.rollback()
session.close()
if rs is not None:
for r in rs:
curriculums.append(r)
# 为未定义课程时间生成时间
# 获取当前周
localtime = datetime.now()
# 为未定义课程时间生成时间
summer_time = [
time(8, 30),
time(10, 25),
time(14, 30),
time(16, 25),
time(19, 30),
]
winter_time = [
time(8, 30),
time(10, 25),
time(14, 00),
time(15, 55),
time(19, 00),
]
for curriculum in curriculums:
curriculum.group_name = [curriculum.group_name]
# if not curriculum.start_time:
if 10 > localtime.month >= 5:
curriculum.start_time = summer_time[int(curriculum.class_num) - 1]
else:
curriculum.start_time = winter_time[int(curriculum.class_num) - 1]
merge_curriculums = []
for i in range(len(curriculums) - 1):
if curriculums[i].class_name == curriculums[i + 1].class_name and \
curriculums[i].class_num == curriculums[i + 1].class_num:
curriculums[i + 1].group_name.extend(curriculums[i].group_name)
else:
merge_curriculums.append(curriculums[i])
if curriculums:
merge_curriculums.append(curriculums[len(curriculums) - 1])
return merge_curriculums
async def init_user_invite(qq_id, nickname):
session = DBSession()
user_invite = session.query(User_invite).filter(
User_invite.qq_id == qq_id).first()
if user_invite:
session.close()
return
else:
user_invite = User_invite(qq_id=qq_id,
nickname=nickname,
join_time=datetime.now())
session.add(user_invite)
session.commit()
session.close()
async def get_do_you_know():
session = DBSession()
dyn = session.query(DoYouKnow).order_by(func.rand()).first()
dyn.times += 1
try:
session.merge(dyn)
except Exception:
session.rollback()
session.close()
result = {}
result['text'] = dyn.text
result['info'] = dyn.times
return result
async def get_user_invite(qq_id):
"""
通过 qq 号，找到用户表项
:param qq_id:
:return:
"""
session = DBSession()
user_invite = session.query(User_invite).filter(
User_invite.qq_id == qq_id).first()
if user_invite:
session.close()
return user_invite
else:
raise RuntimeError
async def get_item(id):
session = DBSession()
item = session.query(SellItem).filter(SellItem.id == id).first()
session.close()
return item
async def get_homework_info_by_id(id):
session = DBSession()
homework = session.query(Homework).filter(Homework.id == id).first()
session.close()
return homework
async def del_homework(id):
session = DBSession()
session.query(Homework).filter(Homework.id == id).delete()
session.commit()
session.close()
async def get_group_info(group_id) -> GroupInfo:
session = DBSession()
group_info = session.query(GroupInfo).filter(
GroupInfo.group_id == group_id).first()
session.close()
return group_info
async def getRecentClassInfo(recent_time: datetime,
group_id,
timeLimit=None,
from_schedule=False):
# 获取当前周
localtime = datetime.now()
week = get_session_week(localtime)
# 数据库
session = DBSession()
curriculums = session.query(Curriculum). \
filter(
Curriculum.group_id == group_id,
Curriculum.weekday == localtime.weekday() + 1,
Curriculum.begin_week <= week,
Curriculum.end_week >= week,
).order_by(Curriculum.class_num, Curriculum.group_name).all()
#session.rollback()
session.close()
# 为未定义课程时间生成时间
summer_time = [
time(8, 30),
time(10, 25),
time(14, 30),
time(16, 25),
time(19, 30),
]
winter_time = [
time(8, 30),
time(10, 25),
time(14, 00),
time(15, 55),
time(19, 00),
]
for curriculum in curriculums:
# if not curriculum.start_time:
if 10 > localtime.month >= 5:
curriculum.start_time = summer_time[int(curriculum.class_num) - 1]
else:
curriculum.start_time = winter_time[int(curriculum.class_num) - 1]
curriculums_cpy = copy.deepcopy(curriculums)
if from_schedule:
# 筛选
result = []
for curriculum in curriculums:
if curriculum.start_time.strftime("%H%M%S") >= localtime.strftime(
"%H%M%S"):
if timeLimit:
if curriculum.start_time.strftime("%H%M%S") <= \
(localtime + timedelta(minutes=timeLimit)).strftime("%H%M%S"):
result.append(curriculum)
else:
result.append(curriculum)
return result
# 筛选
result = []
for curriculum in curriculums_cpy:
curriculum.group_name = [curriculum.group_name]
if curriculum.start_time.strftime("%H%M%S") >= localtime.strftime(
"%H%M%S"):
if timeLimit:
if curriculum.start_time.strftime("%H%M%S") <= \
(localtime + timedelta(minutes=timeLimit)).strftime("%H%M%S"):
result.append(curriculum)
else:
if not result:
result.append(curriculum)
elif curriculum.class_num == result[0].class_num:
result.append(curriculum)
merge_curriculums = []
for i in range(len(result) - 1):
if result[i].class_name == result[i + 1].class_name and \
result[i].class_num == result[i + 1].class_num:
result[i + 1].group_name.extend(result[i].group_name)
else:
merge_curriculums.append(result[i])
if result:
merge_curriculums.append(result[len(result) - 1])
return merge_curriculums
async def get_user(qq_id):
session = DBSession()
qqUser = session.query(QQUser).filter(QQUser.qq_id == qq_id).first()
session.close()
return qqUser