def span(cls): return ( cls.select(fn.MIN(cls.id)).scalar(), cls.select(fn.MAX(cls.id)).scalar(), )
def max(cls): """Get the maximum integer id used in this table. Guaranteed to return an integer (i.e. never ``None``).""" return cls.select(fn.MAX(cls.id)).scalar() or 0
def get_stats(cls): query = (Pokemon .select(Pokemon.pokemon_id, fn.COUNT(Pokemon.pokemon_id).alias('count'), fn.MAX(Pokemon.disappear_time).alias('lastseen')) .group_by(Pokemon.pokemon_id) .order_by(-SQL('count')) .dicts()) pokemons = list(query) known_pokemon = set( p['pokemon_id'] for p in query ) unknown_pokemon = set(range(1,151)).difference(known_pokemon) pokemons.extend( { 'pokemon_id': i, 'count': 0, 'lastseen': None } for i in unknown_pokemon) for p in pokemons: p['pokemon_name'] = get_pokemon_name(p['pokemon_id']) return pokemons
def get(self): start = request.args.get('start') or 0 length = request.args.get('length') or -1 radarrid = request.args.get('radarrid') upgradable_movies = [] upgradable_movies_not_perfect = [] if settings.general.getboolean('upgrade_subs'): days_to_upgrade_subs = settings.general.days_to_upgrade_subs minimum_timestamp = ( (datetime.datetime.now() - timedelta(days=int(days_to_upgrade_subs))) - datetime.datetime(1970, 1, 1)).total_seconds() if settings.general.getboolean('upgrade_manual'): query_actions = [1, 2, 3, 6] else: query_actions = [1, 3] upgradable_movies_conditions = [ (TableHistoryMovie.action.in_(query_actions)), (TableHistoryMovie.timestamp > minimum_timestamp), (TableHistoryMovie.score.is_null(False)) ] upgradable_movies_conditions += get_exclusion_clause('movie') upgradable_movies = TableHistoryMovie.select(TableHistoryMovie.video_path, fn.MAX(TableHistoryMovie.timestamp).alias('timestamp'), TableHistoryMovie.score, TableMovies.tags, TableMovies.monitored)\ .join(TableMovies, on=(TableHistoryMovie.radarrId == TableMovies.radarrId))\ .where(reduce(operator.and_, upgradable_movies_conditions))\ .group_by(TableHistoryMovie.video_path)\ .dicts() upgradable_movies = list(upgradable_movies) for upgradable_movie in upgradable_movies: if upgradable_movie['timestamp'] > minimum_timestamp: try: int(upgradable_movie['score']) except ValueError: pass else: if int(upgradable_movie['score']) < 120: upgradable_movies_not_perfect.append( upgradable_movie) query_conditions = [(TableMovies.title.is_null(False))] if radarrid: query_conditions.append((TableMovies.radarrId == radarrid)) query_condition = reduce(operator.and_, query_conditions) movie_history = TableHistoryMovie.select(TableHistoryMovie.id, TableHistoryMovie.action, TableMovies.title, TableHistoryMovie.timestamp, TableHistoryMovie.description, TableHistoryMovie.radarrId, TableMovies.monitored, TableHistoryMovie.video_path.alias('path'), TableHistoryMovie.language, TableMovies.tags, TableHistoryMovie.score, TableHistoryMovie.subs_id, TableHistoryMovie.provider, TableHistoryMovie.subtitles_path)\ .join(TableMovies, on=(TableHistoryMovie.radarrId == TableMovies.radarrId))\ .where(query_condition)\ .order_by(TableHistoryMovie.timestamp.desc())\ .limit(length)\ .offset(start)\ .dicts() movie_history = list(movie_history) blacklist_db = TableBlacklistMovie.select( TableBlacklistMovie.provider, TableBlacklistMovie.subs_id).dicts() blacklist_db = list(blacklist_db) for item in movie_history: # Mark movies as upgradable or not item.update({"upgradable": False}) if { "video_path": str(item['path']), "timestamp": float(item['timestamp']), "score": str(item['score']), "tags": str(item['tags']), "monitored": str(item['monitored']) } in upgradable_movies_not_perfect: # noqa: E129 if os.path.isfile( path_mappings.path_replace_movie( item['subtitles_path'])): item.update({"upgradable": True}) del item['path'] postprocessMovie(item) if item['score']: item['score'] = str(round( (int(item['score']) * 100 / 120), 2)) + "%" # Make timestamp pretty if item['timestamp']: item["raw_timestamp"] = int(item['timestamp']) item["parsed_timestamp"] = datetime.datetime.fromtimestamp( int(item['timestamp'])).strftime('%x %X') item['timestamp'] = pretty.date(item["raw_timestamp"]) # Check if subtitles is blacklisted item.update({"blacklisted": False}) if item['action'] not in [0, 4, 5]: for blacklisted_item in blacklist_db: if blacklisted_item['provider'] == item[ 'provider'] and blacklisted_item[ 'subs_id'] == item['subs_id']: # noqa: E125 item.update({"blacklisted": True}) break count = TableHistoryMovie.select()\ .join(TableMovies, on=(TableHistoryMovie.radarrId == TableMovies.radarrId))\ .where(TableMovies.title.is_null(False))\ .count() return jsonify(data=movie_history, total=count)
def process_comments(appcfg): # Get newest comments with two week overlap print(' PROCESSING NEWEST PUSHSHIFT.IO COMMENTS FOR', appcfg.subreddit) try: newest_utc = int( Comment.select(fn.MAX(Comment.created_utc)).scalar().timestamp()) except (TypeError, AttributeError): newest_utc = None if newest_utc is not None: oldestdate = newest_utc # - 1209600 # two weeks overlap, in seconds else: oldestdate = appcfg.oldestdate try: comment_id_set = get_push_comments(appcfg, appcfg.newestdate, oldestdate) except (ConnectionError, SSLError, ChunkedEncodingError): comment_id_set = None print(" Connection Error for Pushshift API. Quitting...") # quit() return comment_id_set # Get oldest comments in case progress was interrupted, with two week overlap try: oldest_utc = int( Comment.select(fn.MIN(Comment.created_utc)).scalar().timestamp()) except (TypeError, AttributeError): oldest_utc = None if oldest_utc is not None: newestdate = oldest_utc # + 1209600 # two weeks overlap, in seconds else: newestdate = appcfg.newestdate print(' PROCESSING OLDEST PUSHSHIFT.IO COMMENTS FOR', appcfg.subreddit) try: old_comment_id_set = get_push_comments(appcfg, newestdate, appcfg.oldestdate) except (ConnectionError, SSLError, ChunkedEncodingError): old_comment_id_set = None print(" Connection Error for Pushshift API. Quitting...") # quit() return old_comment_id_set comment_id_set |= old_comment_id_set filedate = arrow.now().timestamp basedir = "/rpa" if os.environ.get('DOCKER', '0') == '1' else '.' coutput_file_path = "{basedir}/{subreddit}_comments_{timestamp}.txt".format( basedir=basedir, subreddit=appcfg.subreddit, timestamp=filedate) # with open(coutput_file_path, 'w', encoding='UTF-8') as comment_file: # comment_file.writelines(comment_id_set) print(" Total comments submitted to", appcfg.subreddit, "in set:", len(comment_id_set)) deleted = Author.get_or_none(name='[deleted]') if deleted is not None: cupdatet = Comment.update(deleted=True).where( (Comment.author == deleted.id) & (Comment.deleted.is_null() or Comment.deleted == 0)).execute() print( ' Updated deleted field in comments. Set deleted = True for', cupdatet, 'records.') cupdatef = Comment.update( deleted=False).where((Comment.author != deleted.id) & (Comment.deleted.is_null())).execute() print( ' Updated deleted field in comments. Set deleted = False for', cupdatef, 'records.')
def nextqueue_position(self): lastPos = Group.select(fn.MAX(Group.queue_position)).scalar() if lastPos is None: return 0 else: return lastPos + 1
def with_last_voted(cls): """Queryset annotated with .last_voted""" from app.models import Vote return cls.select().annotate(Vote, fn.MAX(Vote.time).alias('last_voted'))
def update_acm_rank(contest_id, user_id, problem_id, sub_create_time, result): with db.connection_context(): record = acm_contest_rank.get_or_none( acm_contest_rank.contest_id == contest_id, acm_contest_rank.user_id == user_id ) if record is None: with db.connection_context(): max_id = ( acm_contest_rank.select( fn.MAX(acm_contest_rank.id).alias("max")) .get() .max ) # print(max_id) # assert False record = acm_contest_rank( # id=(max_id + 1 if max_id else 1), user_id=user_id, contest_id=contest_id, submission_number=0, accepted_number=0, total_time=0, submission_info={}, ) #print(record.submission_info.get(problem_id, {})) #print(record.submission_info.get(problem_id, {}).get("is_ac", False)) if record.submission_info.get(str(problem_id), {}).get("is_ac", False): # logging.debug('dsalkjf') print('adsfsad') return record.submission_number += 1 record.submission_info[str(problem_id)] = record.submission_info.get( str(problem_id), {}) if result == 0: # print(0) print('---------------------------------') print(sub_create_time) time = contest.select(contest.create_time).where( contest.id == contest_id).get().create_time print(time) print(sub_create_time - time) time = (sub_create_time - time).seconds + \ (sub_create_time - time).days * 24 * 60 * 60 print(time) record.total_time += time record.accepted_number += 1 record.submission_info[str(problem_id)] = record.submission_info.get( str(problem_id), 0) record.submission_info[str(problem_id)]["is_ac"] = True record.submission_info[str(problem_id)]["ac_time"] = time with db.connection_context(): # print('cp') cp = ( contest_problem.select() .where( contest_problem.contest_id == contest_id, contest_problem.problem_id == problem_id, ) .get() ) # print(cp.first_ac_id) if cp.first_ac_id is None: # print('-------------------------none') record.submission_info[str(problem_id)]["is_first_ac"] = True cp.first_ac_id = user_id cp.save() else: record.total_time += 1200 record.submission_info[str(problem_id)] = record.submission_info.get( str(problem_id), 0) record.submission_info[str(problem_id)]["is_ac"] = False #print(record.submission_info[str(problem_id)].get("error_number", 0)) record.submission_info[str(problem_id)]["error_number"] = ( record.submission_info[str(problem_id)].get("error_number", 0) + 1 ) with db.connection_context(): # print('save') return record.save()
def get_bounty_max_block_number(): return BountyEvent.select(fn.MAX(BountyEvent.block_number)).scalar()
def with_last_mined(cls): """Queryset annotated with .last_mined""" from app.models import Block return cls.select().annotate(Block, fn.MAX(Block.time).alias('last_mined'))
def genesis(): return db.Genesis.select(fn.MAX(db.Genesis.genesis)).get().max
def last_msg(): last_msg = db.Telegram.select(fn.MAX(db.Telegram.update_id)).get().max return last_msg if last_msg is not None else settings.TELEGRAM_OFFSET
def open_positions_report(): markets = [ lp.market for lp in LongPosition.select(LongPosition.market).distinct() ] results = [] result_str = "Open Positions:\n" total_net = Decimal('0.0') total_spent = Decimal('0.0') for market in markets: current_price = Candle.select().where( Candle.market == market).order_by( Candle.timestamp.desc()).limit(1)[0].close (num_positions, quantity, spent, min, avg, max, min_sell_price) = LongPosition.select( fn.COUNT(LongPosition.id), fn.SUM(LongPosition.buy_quantity), fn.SUM(LongPosition.buy_quantity * LongPosition.purchase_price), fn.MIN(LongPosition.purchase_price), fn.AVG(LongPosition.purchase_price), fn.MAX(LongPosition.purchase_price), fn.MIN(LongPosition.sell_price)).where( LongPosition.market == market, LongPosition.sell_timestamp.is_null(True)).scalar( as_tuple=True) if not num_positions: continue quantity = Decimal(quantity).quantize(Decimal('0.00000001')) spent = Decimal(spent) current_value = quantity * current_price profit = (current_value - spent).quantize(Decimal('0.00000001')) total_net += profit total_spent += spent current_profit_percentage = (current_value / spent * Decimal('100.0')).quantize( Decimal('0.01')) results.append({ "market": market, "num_positions": num_positions, "min_position": min.quantize(Decimal('0.00000001')), "avg_position": avg.quantize(Decimal('0.00000001')), "max_position": max.quantize(Decimal('0.00000001')), "min_sell_price": min_sell_price.quantize(Decimal('0.00000001')), "min_profit_percentage": (min_sell_price / min * Decimal('100.00')).quantize( Decimal('0.01')), "profit": profit, "current_profit_percentage": current_profit_percentage, "quantity": quantity.normalize() }) if total_spent > Decimal('0.0'): total_percentage = (total_net / total_spent * Decimal('100.0')).quantize(Decimal('0.01')) else: total_percentage = Decimal('0.0') for result in sorted(results, key=lambda i: i['profit'], reverse=True): result_str += f"{'{:>8}'.format(result['market'])}: {result['min_position']:0.8f} | {result['min_sell_price']:0.8f} ({'{:>6}'.format(str(result['min_profit_percentage']))}%) | {'{:>2}'.format(str(result['num_positions']))} | {'{:>6}'.format(str(result['current_profit_percentage']))}%\n" result_str += f"{'-' * 53}\n" result_str += f" total: {'{:>11}'.format(str(total_net))} | {'{:>6}'.format(str(total_percentage))}%\n" return result_str
def info(self, event, user: User = None): if not user: user = event.author else: if not isinstance(user, DiscoUser): try: user = self.state.guilds[event.guild.id].members[user].user except KeyError: try: user = self.state.users[user] except KeyError: try: user = self.bot.client.api.users_get(user) except APIException: return event.msg.reply( ':eyes: User not found').after(3).delete() self.client.api.channels_typing(event.channel.id) content = [] content.append('**\u276F User Information**') content.append('Profile: <@{}>'.format(user.id)) created_dt = to_datetime(user.id) content.append('Created: <t:{0}:R> (<t:{0}:f>)'.format( int(created_dt.replace(tzinfo=pytz.UTC).timestamp()))) member = event.guild.get_member(user.id) if event.guild else None if user.public_flags: badges = '' user_badges = list(UserFlags(user.public_flags)) for badge in user_badges: badges += '<{}> '.format(BADGE_EMOJI[badge]) content.append('Badges: {}'.format(badges)) if member: content.append('\n**\u276F Member Information**') if member.nick: content.append('Nickname: {}'.format(member.nick)) content.append('Joined: <t:{0}:R> (<t:{0}:f>)'.format( int(member.joined_at.replace(tzinfo=pytz.UTC).timestamp()))) content.append('Messages: {}'.format( int( Message.select(fn.Count( Message.id)).where((Message.author_id == user.id) & (Message.guild_id == event.guild. id)).tuples()[0][0]))) if member.roles: content.append('Roles: {}'.format(', '.join( ('<@&{}>'.format(r) for r in member.roles)))) # Execute a bunch of queries newest_msg = Message.select(fn.MAX(Message.id)).where( (Message.author_id == user.id) & (Message.guild_id == event.guild.id)).tuples()[0][0] infractions = Infraction.select(Infraction.id).where( (Infraction.user_id == user.id) & (Infraction.guild_id == event.guild.id)).tuples() if newest_msg: content.append('\n **\u276F Activity**') content.append('Last Message: <t:{0}:R> (<t:{0}:f>)'.format( int((to_datetime(newest_msg).replace( tzinfo=pytz.UTC)).timestamp()))) # content.append('First Message: {} ({})'.format( # humanize.naturaltime(datetime.utcnow() - to_datetime(oldest_msg)), # to_datetime(oldest_msg).strftime("%b %d %Y %H:%M:%S"), # )) if len(infractions) > 0: content.append('\n**\u276F Infractions**') total = len(infractions) content.append('Total Infractions: **{:,}**'.format(total)) embed = MessageEmbed() try: avatar = User.with_id(user.id).get_avatar_url() except APIException: avatar = user.get_avatar_url( ) # This fails if the user has never been seen by speedboat. embed.set_author(name='{} ({})'.format( str(user), user.id, ), icon_url=avatar) embed.set_thumbnail(url=avatar) embed.description = '\n'.join(content) embed.color = get_dominant_colors_user(user, avatar) event.msg.reply('', embed=embed)
def get_last_datetime(): '''Finds the maximum datetime in the orders database''' lastDatetime = Tabs.select(fn.MAX(Tabs.timestamp)).scalar(convert=True) return lastDatetime
def user_summary(): # noqa: D103 form = DateRangeForm(request.args) sort = request.args.get("sort") if sort: try: sort = int(sort) except: sort = None desc = request.args.get("desc") if desc: try: desc = int(desc) except: desc = None if not (form.from_date.data and form.to_date.data): date_range = User.select( fn.MIN(User.created_at).alias('from_date'), fn.MAX(User.created_at).alias('to_date')).first() return redirect( url_for( "user_summary", from_date=date_range.from_date.date().isoformat(), to_date=date_range.to_date.date().isoformat(), sort=sort, desc=desc)) user_counts = (User.select( User.organisation.alias("org_id"), fn.COUNT(fn.DISTINCT(User.id)).alias("user_count")).where( User.created_at.between(form.from_date.data, form.to_date.data)).join( UserOrg, JOIN.LEFT_OUTER, on=(UserOrg.org_id == User.id)).group_by( User.organisation)).alias("user_counts") linked_counts = (OrcidToken.select( OrcidToken.org.alias("org_id"), fn.COUNT(fn.DISTINCT(OrcidToken.user)).alias("linked_user_count")).where( OrcidToken.created_at.between(form.from_date.data, form.to_date.data)).group_by( OrcidToken.org).alias("linked_counts")) query = (Organisation.select( Organisation.name, fn.COALESCE(user_counts.c.user_count, 0).alias("user_count"), fn.COALESCE(linked_counts.c.linked_user_count, 0).alias("linked_user_count")).join( user_counts, on=(Organisation.id == user_counts.c.org_id)).join( linked_counts, JOIN.LEFT_OUTER, on=(Organisation.id == linked_counts.c.org_id))) if sort == 1: order_fields = [SQL("user_count"), SQL("linked_user_count"), ] else: order_fields = [Organisation.name, ] if desc: order_fields = [f.desc() for f in order_fields] query = query.order_by(*order_fields) total_user_count = sum(r.user_count for r in query if r.user_count) total_linked_user_count = sum(r.linked_user_count for r in query if r.linked_user_count) headers = [(h, url_for( "user_summary", from_date=form.from_date.data, to_date=form.to_date.data, sort=i, desc=1 if sort == i and not desc else 0)) for i, h in enumerate(["Name", "Linked User Count / User Count (%)"])] return render_template( "user_summary.html", form=form, query=query, total_user_count=total_user_count, total_linked_user_count=total_linked_user_count, sort=sort, desc=desc, headers=headers)
def process_submissions(appcfg): # Get newest submissions with two week overlap print(' PROCESSING NEWEST PUSHSHIFT.IO SUBMISSIONS FOR', appcfg.subreddit) try: newest_utc = int( Submission.select(fn.MAX( Submission.created_utc)).scalar().timestamp()) except (TypeError, AttributeError): newest_utc = None if newest_utc is not None: oldestdate = newest_utc # - 1209600 # two weeks overlap, in seconds else: oldestdate = appcfg.oldestdate try: post_id_set = get_push_submissions(appcfg, appcfg.newestdate, oldestdate) except (ConnectionError, SSLError, ChunkedEncodingError): post_id_set = None print(" Connection Error for Pushshift API. Quitting...") # quit() return post_id_set # Get oldest submissions in case progress was interrupted, with four week overlap try: oldest_utc = int( Submission.select(fn.MIN( Submission.created_utc)).scalar().timestamp()) except (TypeError, AttributeError): oldest_utc = None if oldest_utc is not None: newestdate = oldest_utc # + 2400000 # four week overlap, in seconds else: newestdate = appcfg.newestdate print(' PROCESSING OLDEST PUSHSHIFT.IO SUBMISSIONS FOR', appcfg.subreddit) try: old_post_id_set = get_push_submissions(appcfg, newestdate, appcfg.oldestdate) except (ConnectionError, SSLError, ChunkedEncodingError): old_post_id_set = None print(" Connection Error for Pushshift API. Quitting...") # quit() return old_post_id_set post_id_set |= old_post_id_set filedate = arrow.now().timestamp output_file_path = "{subreddit}_{timestamp}.csv".format( subreddit=appcfg.subreddit, timestamp=filedate) # with open(output_file_path, 'w', encoding='UTF-8') as post_file: # post_file.writelines(post_id_set) print(" Total posts submitted to", appcfg.subreddit, "in set:", len(post_id_set)) deleted = Author.get_or_none(name='[deleted]') if deleted is not None: supdatet = Submission.update( deleted=True).where((Submission.author == deleted.id) & (Submission.deleted.is_null() or Submission.deleted == 0)).execute() print( ' Updated deleted field in submissions. Set deleted = True for ', supdatet, ' records.') supdatef = Submission.update( deleted=False).where((Submission.author != deleted.id) & (Submission.deleted.is_null())).execute() print( ' Updated deleted field in submissions. Set deleted = False for ', supdatef, ' records.')
def write(self, path, buf, offset, fh): print('Write:') print(' offset: ' + str(offset)) print(' bytes: ' + str(len(buf))) if path not in self.tempfiles: self.tempfiles[path] = { 'file': tempfile.SpooledTemporaryFile(max_size=50 * (1024**2)) } self.tempfiles[path]['lastwrite'] = datetime.datetime.now() self.tempfiles[path]['file'].seek(offset) print(self.tempfiles[path]['file'].write(buf)) return print('Write:') print(' offset: ' + str(offset)) print(' bytes: ' + str(len(buf))) file_id = self._get_path_ids(path)[-1] f = File.get(id=file_id) nof_chunks = int(len(buf) / self._tgchunk_size) + ( 1 if len(buf) != self._tgchunk_size else 0) first_chunk_start_position = offset - (offset % self._tgchunk_size) first_chunk_no = int(first_chunk_start_position / self._tgchunk_size) chunks_streams = [] for x in range(first_chunk_no, first_chunk_no + nof_chunks): tgd, created = TelegramDocument.get_or_create(file_id=f.id, file_no=x) if created: chunks_streams.append(None) else: chunks_streams.append(self._get_file(tgd.telegram_id)) for x in range(first_chunk_no, first_chunk_no + nof_chunks): chunk_stream = chunks_streams.pop(0) chunk_buf = tempfile.SpooledTemporaryFile(max_size=1024**3) chunk_buf.seek(0) if chunk_stream is not None: chunk_buf.write(chunk_stream.read()) if x is 0: start_chunk = offset % self._tgchunk_size last_chunk = self._tgchunk_size start_buf = start_chunk + (first_chunk_no * self._tgchunk_size) last_buf = (first_chunk_no + 1) * self._tgchunk_size elif x is (first_chunk_no + nof_chunks - 1): start_chunk = 0 last_chunk = self._tgchunk_size start_buf = x * self._tgchunk_size last_buf = ((x + 1) * self._tgchunk_size) - 1 else: start_chunk = 0 last_chunk = (offset + len(buf)) % self._tgchunk_size start_buf = x * self._tgchunk_size last_buf = start_buf + last_chunk chunk_buf.seek(start_chunk) print(' bytes to write (' + str(start_buf) + ':' + str(last_buf) + '): ' + str(len(buf[start_buf:last_buf]))) bytes_written = chunk_buf.write(buf[start_buf:last_buf]) print(' bytes written: ' + str(bytes_written)) # chunk_buf[start_chunk:last_chunk] = buf[start_buf:last_buf] tg_doc_id = self._upload_file(chunk_buf) tgd.telegram_id = tg_doc_id tgd.save() last_tgd = TelegramDocument.select( fn.MAX(TelegramDocument.file_no), TelegramDocument.id, TelegramDocument.telegram_id, TelegramDocument.file_no).where( TelegramDocument.file_id == f.id).first() file_size = last_tgd.file_no * self._tgchunk_size file_size += int( self._get_file(last_tgd.telegram_id).headers['Content-length']) print('Last file size: ' + self._get_file(last_tgd.telegram_id).headers['Content-length']) f.updated_at = datetime.datetime.now() f.size = file_size f.save() print('\n') return f.size
def get_highest_number(cls, course: Course): return (cls.select(fn.MAX(cls.number)).where( cls.course == course).group_by(cls.course).scalar())
def getProcessedFile(): file = Mad_ProcessedFile.select(Mad_ProcessedFile.name).where(Mad_ProcessedFile.id == Mad_ProcessedFile.select(fn.MAX(Mad_ProcessedFile.id)).scalar()).dicts() # pylint: disable=E1120 # noqa try: return file[0]['name'] except IndexError: return minstr()
def info(self, event, user=None): if not user: user = event.author else: if not isinstance(user, DiscoUser): try: user = self.state.guilds[event.guild.id].members[user].user except KeyError: try: user = self.state.users[user] except KeyError: try: user = self.bot.client.api.users_get(user) except APIException: return event.msg.reply( 'User not found :eyes:').after(3).delete() self.client.api.channels_typing(event.channel.id) content = [] content.append('**\u276F User Information**') content.append('Profile: <@{}>'.format(user.id)) created_dt = to_datetime(user.id) content.append('Created: {} ({})'.format( humanize.naturaltime(datetime.utcnow() - created_dt), created_dt.strftime("%b %d %Y %H:%M:%S"))) member = event.guild.get_member(user.id) if event.guild else None if user.presence: #I couldn't get this to work w/o it lol emoji, status = get_status_emoji(user.presence) content.append('Status: <{}> {}'.format(emoji, status)) if user.presence.game and user.presence.game.name: if user.presence.game.type == ActivityTypes.DEFAULT: content.append('{}'.format(user.presence.game.name)) if user.presence.game.type == ActivityTypes.CUSTOM: content.append('Custom Status: {}'.format( user.presence.game.state)) if user.presence.game.type == ActivityTypes.LISTENING: content.append('Listening to {} on Spotify'.format( user.presence.game.details) ) #In the embed, details is the songname. if user.presence.game.type == ActivityTypes.STREAMING: content.append('Streaming: [{}]({})'.format( user.presence.game.name, user.presence.game.url)) if user.public_flags: badges = '' user_badges = list(UserFlags(user.public_flags)) for badge in user_badges: badges += '<{}> '.format(BADGE_EMOJI[badge]) content.append('Badges: {}'.format(badges)) if member: content.append('\n**\u276F Member Information**') if member.nick: content.append('Nickname: {}'.format(member.nick)) content.append('Joined: {} ago ({})'.format( humanize.naturaldelta(datetime.utcnow() - member.joined_at), member.joined_at.strftime("%b %d %Y %H:%M:%S"), )) if member.roles: content.append('Roles: {}'.format(', '.join( ('<@&{}>'.format(member.guild.roles.get(r).id) for r in member.roles)))) # Execute a bunch of queries newest_msg = Message.select(fn.MAX(Message.id)).where( (Message.author_id == user.id) & (Message.guild_id == event.guild.id)).tuples()[0][0] oldest_msg = Message.select(fn.MIN(Message.id)).where( (Message.author_id == user.id) & (Message.guild_id == event.guild.id)).tuples()[0][0] #Slow Query voice = GuildVoiceSession.select( fn.COUNT(GuildVoiceSession.user_id), fn.SUM(GuildVoiceSession.ended_at - GuildVoiceSession.started_at) ).where((GuildVoiceSession.user_id == user.id) & (~(GuildVoiceSession.ended_at >> None)) & (GuildVoiceSession.guild_id == event.guild.id)).tuples()[0] infractions = Infraction.select(Infraction.id).where( (Infraction.user_id == user.id) & (Infraction.guild_id == event.guild.id)).tuples() if newest_msg and oldest_msg: content.append('\n **\u276F Activity**') content.append('Last Message: {} ({})'.format( humanize.naturaltime(datetime.utcnow() - to_datetime(newest_msg)), to_datetime(newest_msg).strftime("%b %d %Y %H:%M:%S"), )) content.append('First Message: {} ({})'.format( humanize.naturaltime(datetime.utcnow() - to_datetime(oldest_msg)), to_datetime(oldest_msg).strftime("%b %d %Y %H:%M:%S"), )) if len(infractions) > 0: content.append('\n**\u276F Infractions**') total = len(infractions) content.append('Total Infractions: **{:,}**'.format(total)) if voice[0]: content.append('\n**\u276F Voice**') content.append('Sessions: `{:,}`'.format(voice[0])) content.append('Time: `{}`'.format( str(humanize.naturaldelta(voice[1])).title())) embed = MessageEmbed() try: avatar = User.with_id(user.id).get_avatar_url() except: avatar = user.get_avatar_url( ) # This fails if the user has never been seen by speedboat. embed.set_author(name='{}#{} ({})'.format( user.username, user.discriminator, user.id, ), icon_url=avatar) embed.set_thumbnail(url=avatar) embed.description = '\n'.join(content) embed.color = get_dominant_colors_user(user, avatar) event.msg.reply('', embed=embed)
def _parse_table_to_DB(table_contents) -> None: """Обработка скачанного расписания в базу данных Parameters ---------- table_contents: bytes содержимое в виде набора байт скачанной таблицы """ book = open_workbook(file_contents=table_contents) sheet = book.sheet_by_index(0) num_cols = sheet.ncols # Check if several columns in the beginning are duplicating start_offset = 0 first_col = sheet.col_slice(start_offset, 0, 4) for col in range(1, num_cols): curr_col = sheet.col_slice(col, 0, 4) if all(first_col[i].value != curr_col[i].value for i in range(len(first_col))): start_offset = col - 1 break group_count = -1 day_count = -1 print_status_in = num_cols // 15 existing_records = [{ "day_id": None, "subject_id": None }, { "day_id": None, "subject_id": None }] group_max_count = Weeks.select(fn.MAX(Weeks.days_of_group_id)).scalar() if group_max_count is not None and group_max_count > 0: group_count = group_max_count day_max_count = Days.select(fn.MAX( Days.subject_schedules_of_day_id)).scalar() if day_max_count is not None and day_max_count > 0: day_count = day_max_count for col_index in range(num_cols): if col_index % print_status_in == 0: print(Fore.YELLOW + f"Прогресс: {int((col_index / num_cols) * 100)}%" + Style.RESET_ALL) group_cell = str(sheet.cell(1, col_index).value) if search(r'.{4}-\d{2}-\d{2}', group_cell): group_cell = findall(r'.{4}-\d{2}-\d{2}', group_cell)[0] else: continue for day in range(6): for lesson in range(6): for evenness in range(2): lesson_number = int( sheet.cell(3 + lesson * 2 + day * 12, start_offset + 1).value) if not Parser._lesson_start_end_table_filled: lesson_start_time = str( sheet.cell(3 + lesson * 2 + day * 12, start_offset + 2).value) lesson_end_time = str( sheet.cell(3 + lesson * 2 + day * 12, start_offset + 3).value) lesson_start_time = time( *map(int, lesson_start_time.split('-'))) lesson_end_time = time( *map(int, lesson_end_time.split('-'))) Parser._fill_lesson_start_end_table( lesson_number, lesson_start_time, lesson_end_time) subject = Parser._get_cell_info( 3 + evenness + lesson * 2 + day * 12, col_index, sheet).replace("\n", " ") lesson_type = Parser._get_cell_info( 3 + evenness + lesson * 2 + day * 12, col_index + 1, sheet) lecturer = Parser._get_cell_info( 3 + evenness + lesson * 2 + day * 12, col_index + 2, sheet).replace(",", ".") classroom = Parser._get_cell_info( 3 + evenness + lesson * 2 + day * 12, col_index + 3, sheet) url = Parser._get_cell_info( 3 + evenness + lesson * 2 + day * 12, col_index + 4, sheet) day_of_week_id = (group_count + 1) \ if lesson == 0 and day == 0 else (group_count - int(not bool(evenness))) schedule_of_subject_id = (day_count + 1) \ if lesson == 0 else (day_count - int(not bool(evenness))) try: day_id = Weeks.get((Weeks.group == group_cell) & ( Weeks.even == bool(evenness))).days_of_group_id existing_records[evenness]["day_id"] = day_id except DoesNotExist: existing_records[evenness]["day_id"] = None try: subject_id = Days.get( (Days.day_of_week_id == existing_records[evenness]["day_id"]) & (Days.day_of_week == Parser._week_days[day]) ).subject_schedules_of_day_id existing_records[evenness][ "subject_id"] = subject_id except DoesNotExist: existing_records[evenness]["subject_id"] = None if len([ i for i in Subjects.select().where( (Subjects.schedule_of_subject_id == existing_records[evenness]["subject_id"]) & (Subjects.lesson_number == lesson_number) ).execute() ]) == 0: # print("Создаём " + # ', '.join([str(lesson_number), subject, lesson_type, lecturer, classroom, url]) # + f" в subject_id {str(schedule_of_subject_id)}") Subjects.create( schedule_of_subject_id=schedule_of_subject_id, lesson_number=lesson_number, subject=subject, lesson_type=lesson_type, teacher=lecturer, class_number=classroom, link=url) else: s = Subjects.get( (Subjects.schedule_of_subject_id == existing_records[evenness]["subject_id"]) & (Subjects.lesson_number == lesson_number)) if s.subject != subject or s.lesson_type != lesson_type or s.teacher != lecturer or \ s.class_number != classroom or s.link != url: # print(f"Обновляем " + # ', '.join( # [str(lesson_number), subject, lesson_type, lecturer, classroom, url]) # + " в subject_id {str(existing_records[evenness]['subject_id'])}") s.subject = subject s.lesson_type = lesson_type s.teacher = lecturer s.class_number = classroom s.link = url s.save() if len([ i for i in Days.select().where( (Days.day_of_week_id == existing_records[evenness]["day_id"]) & (Days.day_of_week == Parser._week_days[day])).execute() ]) == 0: if len([ i for i in Days.select().where( (Days.day_of_week_id == day_of_week_id) & (Days.day_of_week == Parser._week_days[day])).execute() ]) == 0: Days.create( day_of_week_id=day_of_week_id, day_of_week=Parser._week_days[day], subject_schedules_of_day_id=day_count + 1) day_count += 1 if len([ i for i in Weeks.select().where( (Weeks.group == group_cell) & (Weeks.even == bool(evenness))).execute() ]) == 0: Weeks.create(group=group_cell, even=bool(evenness), days_of_group_id=group_count + 1) group_count += 1
def _execute(self) -> Tuple[List[PopularItemsOutput]]: """ Execute new payment transaction :return: relevant output information """ # Get order table joined with customer table order_customer_query = (Order.select( Order.id.alias("order_id"), Order.district_id.alias("district_id"), Order.warehouse_id.alias("warehouse_id"), Order.entry_date.alias("entry_date"), Customer.middle_name.alias("middle_name"), Customer.first_name.alias("first_name"), Customer.last_name.alias("last_name"), ).join( Customer, on=((Order.warehouse_id == Customer.warehouse_id) & (Order.district_id == Customer.district_id) & (Order.customer_id == Customer.id)), ).where((Order.warehouse_id == self.warehouse_id) & (Order.district_id == self.district_id)).order_by( Order.entry_date.desc()).limit( self.orders_to_examine).cte("order_customer_query")) # Get order lines with maximum quantity, joined with item table OrderLineInner: OrderLine = OrderLine.alias() order_line_sum_qty_query = (OrderLineInner.select( OrderLineInner.warehouse_id.alias("warehouse_id"), OrderLineInner.district_id.alias("district_id"), OrderLineInner.order_id.alias("order_id"), fn.SUM(OrderLineInner.quantity).alias("sum_qty"), ).where((OrderLineInner.warehouse_id == self.warehouse_id) & (OrderLineInner.district_id == self.district_id)).group_by( OrderLineInner.warehouse_id, OrderLineInner.district_id, OrderLineInner.order_id, OrderLineInner.item_id, ).cte("order_line_sum_qty_query")) order_line_max_qty_query = (order_line_sum_qty_query.select( order_line_sum_qty_query.c.order_id, fn.MAX(order_line_sum_qty_query.c.sum_qty), ).group_by( order_line_sum_qty_query.c.warehouse_id, order_line_sum_qty_query.c.district_id, order_line_sum_qty_query.c.order_id, ).with_cte(order_line_sum_qty_query)) customer_name_field = Case( None, (( order_customer_query.c.middle_name.is_null(), fn.CONCAT( order_customer_query.c.first_name, " ", order_customer_query.c.last_name, ), ), ), fn.CONCAT( order_customer_query.c.first_name, order_customer_query.c.middle_name, order_customer_query.c.last_name, ), ).alias("customer_name") popular_items_query = (OrderLine.select( order_customer_query.c.order_id, order_customer_query.c.entry_date, customer_name_field, fn.SUM(OrderLine.quantity).alias("quantity"), Item.id.alias("item_id"), Item.name.alias("item_name"), ).join( order_customer_query, on=((OrderLine.warehouse_id == order_customer_query.c.warehouse_id) & (OrderLine.district_id == order_customer_query.c.district_id) & (OrderLine.order_id == order_customer_query.c.order_id)), ).join(Item, on=(OrderLine.item_id == Item.id)).group_by( order_customer_query.c.order_id, order_customer_query.c.entry_date, customer_name_field, Item.id, Item.name, ).having( DBTuple(order_customer_query.c.order_id, fn.SUM( OrderLine.quantity)).in_(order_line_max_qty_query)).order_by( order_customer_query.c.order_id.desc(), fn.SUM(OrderLine.quantity).desc(), ).with_cte(order_customer_query)) # Process query output return ([result for result in popular_items_query.dicts()], )