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 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_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_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 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 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 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 make_cp(user1, user2): session = DBSession() user1.is_paired = True user1.paired_to = user2.id user2.is_paired = True user2.paired_to = user1.id session.merge(user1) session.merge(user2) 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_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 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 del_homework(id): session = DBSession() session.query(Homework).filter(Homework.id == id).delete() session.commit() session.close()
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 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_item(id): session = DBSession() item = session.query(SellItem).filter(SellItem.id == id).first() session.close() return item
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 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 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 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 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 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 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 get_user(qq_id): session = DBSession() qqUser = session.query(QQUser).filter(QQUser.qq_id == qq_id).first() session.close() return qqUser