def update_db(added_stat: 'UserStat', update) -> None:
try:
with session_scope() as db:
user_stat = db.query(UserStatDB).filter(
UserStatDB.stats_monday == added_stat.stats_monday,
UserStatDB.cid == added_stat.cid,
UserStatDB.uid == added_stat.uid)
if user_stat.update(update) > 0:
return
logger.error(
f'[userstat.update] user {added_stat.uid}:{added_stat.cid} not found in DB'
)
# update возвращает 0 если объекта нет в бд
# тогда мы добавляем его в бд и еще раз пробуем обновить
# статистику добавляем даже по тем, кого нет в таблицах User|ChatUser
add_to_db(UserStatDB.copy(added_stat))
with session_scope() as db:
user_stat = db.query(UserStatDB).filter(
UserStatDB.stats_monday == added_stat.stats_monday,
UserStatDB.cid == added_stat.cid,
UserStatDB.uid == added_stat.uid)
user_stat.update(update)
except Exception as e:
logger.error(e)
raise Exception(
f"Can't update userstat {added_stat.uid}:{added_stat.cid} to DB"
)
def get_user_position(cls, user_id, cid, date):
position = -1
msg_count = 0
last_monday = get_current_monday(
) if date is None else get_date_monday(date)
try:
with session_scope() as db:
# noinspection PyUnresolvedReferences
q = db.query(UserStatDB, UserDB) \
.filter(UserStatDB.stats_monday == last_monday) \
.filter(UserStatDB.uid == UserDB.uid) \
.filter(UserStatDB.cid == cid) \
.filter(UserStatDB.all_messages_count > 0) \
.order_by(UserStatDB.all_messages_count.desc()) \
.all()
q = [(UserStat.copy(userstat), User.copy(user))
for userstat, user in q]
if q:
position = 0
for userstat, user in q:
position += 1
if userstat.uid == user_id:
msg_count = userstat.all_messages_count
break
except Exception as e:
logger.error(e)
return {'position': position, 'msg_count': msg_count}
def get_all(cls, cid: int, left=False) -> typing.List['ChatUser']:
try:
with session_scope() as db:
all_users = db.query(ChatUserDB) \
.filter(ChatUserDB.cid == cid) \
.filter(ChatUserDB.left == left) \
.all()
return [ChatUser.copy(chatuser) for chatuser in all_users]
except Exception as e:
logger.error(e)
raise Exception(f"Can't get all chatusers {cid}:{left} from DB")
def get(cls, uid: int) -> typing.Optional['User']:
try:
with session_scope() as db:
q = db.query(UserDB) \
.filter(UserDB.uid == uid) \
.limit(1) \
.all()
if q:
return User.copy(q[0])
return None
except Exception as e:
logger.error(e)
raise Exception(f"Can't get user {uid} from DB")
def get_by_username(cls, username: str) -> typing.Optional['User']:
username = username.lstrip('@')
try:
with session_scope() as db:
q = db.query(UserDB) \
.filter(UserDB.username == username) \
.limit(1) \
.all()
if q:
return User.copy(q[0])
return None
except Exception as e:
logger.error(e)
raise Exception(f"Can't get username {username} from DB")
def get_user_chats(cls, uid: int, cids: typing.Optional[typing.List[int]] = None) -> \
typing.List[int]:
config_cids = cids if cids else [int(c) for c in CONFIG.get('chats', [])]
try:
with session_scope() as db:
# noinspection PyUnresolvedReferences
user_in_chats = db.query(ChatUserDB) \
.filter(ChatUserDB.cid.in_(config_cids)) \
.filter(ChatUserDB.uid == uid) \
.filter(ChatUserDB.left == 0) \
.all()
return [chatuser.cid for chatuser in user_in_chats]
except Exception as e:
logger.error(e)
raise Exception(f"Can't get user chats {uid} from DB")
def get_random(cls, cid: int) -> typing.Optional['ChatUser']:
try:
with session_scope() as db:
q: typing.List[ChatUserDB] = db.query(ChatUserDB) \
.filter(ChatUserDB.cid == cid) \
.filter(ChatUserDB.left == 0) \
.order_by(func.rand()) \
.limit(1) \
.all()
if q:
return ChatUser.copy(q[0])
return None
except Exception as e:
logger.error(e)
raise Exception(f"Can't get random chatuser {cid} from DB")
def get(cls, uid: int, cid: int) -> typing.Optional['ChatUser']:
try:
with session_scope() as db:
q: typing.List[ChatUserDB] = db.query(ChatUserDB) \
.filter(ChatUserDB.uid == uid) \
.filter(ChatUserDB.cid == cid) \
.limit(1) \
.all()
if q:
chatuser = ChatUser.copy(q[0])
return chatuser
return None
except Exception as e:
logger.error(e)
raise Exception(f"Can't get chatuser {uid}:{cid} from DB")
def get_year_all_msg_count():
try:
with session_scope() as db:
q = db.query(func.sum(UserStatDB.all_messages_count)) \
.filter(UserStatDB.last_activity >= datetime(year, 1, 1)) \
.filter(UserStatDB.last_activity < datetime(year + 1, 1, 1)) \
.filter(UserStatDB.cid == cid) \
.all()[0][0]
if q is None:
return 0
except Exception as e:
logger.error(e)
return 0
count = int(q)
return count
def get_chat_stats(cls, cid, date=None):
last_monday = get_current_monday(
) if date is None else get_date_monday(date)
try:
with session_scope() as db:
# noinspection PyUnresolvedReferences
q = db.query(UserStatDB, UserDB) \
.filter(UserStatDB.stats_monday == last_monday) \
.filter(UserStatDB.uid == UserDB.uid) \
.filter(UserStatDB.cid == cid) \
.filter(UserStatDB.all_messages_count > 0) \
.order_by(UserStatDB.all_messages_count.desc()) \
.all()
return [(UserStat.copy(userstat), User.copy(user))
for userstat, user in q]
except Exception:
return []
def get_top_kroshka(cls, cid, date=None):
"""
:rtype: User
"""
last_monday = get_current_monday(
) if date is None else get_date_monday(date)
try:
with session_scope() as db:
# noinspection PyUnresolvedReferences
q = db.query(UserStatDB, UserDB) \
.filter(UserStatDB.stats_monday == last_monday) \
.filter(UserStatDB.uid == UserDB.uid) \
.filter(UserStatDB.cid == cid) \
.filter(or_(
UserStatDB.emoji_count > 0,
UserStatDB.stickers_count > 0,
UserStatDB.gifs_count > 0
)) \
.order_by(UserStatDB.emoji_count.desc()) \
.all()
q = [(UserStat.copy(userstat), User.copy(user))
for userstat, user in q]
if not q:
return None
except Exception as e:
logger.error(e)
return None
# получаем соотношение количества эмодзи к сообщениям
users_by_emoji = {}
for user_stat, user in q:
count = user_stat.all_messages_count
# учитываем только тек, кто написал от 30 сообщений
if count < 30 or user_stat.words_count < 500:
continue
users_by_emoji[user.uid] = user_stat.emoji_count / count
if len(users_by_emoji) > 0:
uid, _ = random.choice(sort_dict(users_by_emoji)[:10])
else:
_, user = random.choice(q)
uid = user.uid
user = User.get(uid)
return user
def update(uid, update, new_user):
try:
with session_scope() as db:
user = db.query(UserDB).filter(UserDB.uid == uid)
# если запись обновилась
if user.update(update) > 0:
return
logger.error(f'[user.update] User {uid} not found in DB')
# нет смысла кого попало добавлять в таблицу User
# # если не обновилась - значит такой записи нет в базе
# # тогда добавляем ее и пробуем обновить еще раз
# add_to_db(UserDB.copy(new_user))
# with session_scope() as db:
# user = db.query(UserDB).filter(UserDB.uid == uid)
# user.update(update)
except Exception as e:
logger.error(e)
raise Exception(f"Can't update user {uid} to DB")
def update(cls, uid: int, cid: int, update, new_user: '******'):
try:
with session_scope() as db:
user = db.query(ChatUserDB).filter(ChatUserDB.uid == uid).filter(
ChatUserDB.cid == cid)
# если запись обновилась
if user.update(update) > 0:
return
logger.error(f'[chatuser.update] user {uid}:{cid} not found in DB')
# в chatuser можно попасть только через вход/лив/написание собственных сообщений
# # если не обновилась - значит такой записи нет в базе
# # тогда добавляем ее и пробуем обновить еще раз
# add_to_db(cls.copy(new_user))
# with session_scope() as db:
# user = db.query(ChatUserDB).filter(ChatUserDB.uid == uid).filter(ChatUserDB.cid == cid)
# user.update(update)
except Exception as e:
logger.error(e)
raise Exception(f"Can't update chatuser {uid}:{cid} to DB")
def __get_all_msg_count(monday, cid):
# key = 'all_msg_{}_{}'.format(monday.strftime("%Y%m%d"), cid)
# cached = cache.get(key)
# if cached is not None:
# return int(cached)
try:
with session_scope() as db:
q = db.query(func.sum(UserStatDB.all_messages_count)) \
.filter(UserStatDB.stats_monday == monday) \
.filter(UserStatDB.cid == cid) \
.all()[0][0]
if q is None:
# cache.set(key, str(0), time=USER_CACHE_EXPIRE)
return 0
except Exception as e:
logger.error(e)
return 0
count = int(q)
# cache.set(key, str(count), time=USER_CACHE_EXPIRE)
return count
def get_joins(cid, days_ago) -> typing.List[int]:
try:
with session_scope() as db:
q = db.query(LeaveCollectorDB).from_statement(
sqlalchemy.text(f"""
SELECT
t1.*
FROM leave_logs t1
JOIN (SELECT
uid, MAX(date) date
FROM leave_logs
WHERE date >= {days_ago}
AND cid = {cid}
GROUP BY uid) t2
ON t1.uid = t2.uid
AND t1.date = t2.date
AND t1.leave_type in ('added', 'invite')
AND t1.cid = {cid};
""")).all()
return [x.uid for x in q]
except Exception as e:
logger.error(e)
return []
def get(cls, monday, uid, cid) -> typing.Optional['UserStat']:
cached = cache.get(cls.__get_cache_key(monday, uid, cid))
if cached:
return cached
logger.debug(f'get_lock {cid}:{uid}')
# лок, чтобы в редис попали точно такие же данные, как в бд
with cls.get_lock:
try:
with session_scope() as db:
q = db.query(UserStatDB) \
.filter(UserStatDB.stats_monday == monday,
UserStatDB.cid == cid,
UserStatDB.uid == uid) \
.limit(1) \
.all()
if q:
userstat = cls.copy(q[0])
cache.set(cls.__get_cache_key(monday, uid, cid),
userstat)
return userstat
except Exception as e:
logger.error(e)
return None
def get_chat(cls,
cid,
date=None,
fullstat=False,
salo=False,
tag_salo=False,
mat=False):
if cid > 0:
return {
'users_count': 0,
'top_chart': '',
'msg_count': 0,
'percent': 0
}
msg_count_percent = 100
top_chart = ''
uids = []
last_monday = get_current_monday(
) if date is None else get_date_monday(date)
all_msg_count = cls.__get_all_msg_count(last_monday, cid)
q = []
try:
with session_scope() as db:
# noinspection PyUnresolvedReferences
q = db.query(UserStatDB, UserDB) \
.filter(UserStatDB.stats_monday == last_monday) \
.filter(UserStatDB.uid == UserDB.uid) \
.filter(UserStatDB.cid == cid) \
.filter(UserStatDB.all_messages_count > 0) \
.order_by(UserStatDB.all_messages_count.desc()) \
.all()
q = [(UserStat.copy(userstat), User.copy(user))
for userstat, user in q]
except Exception as e:
logger.error(e)
if len(q) == 0:
return {
'users_count': 0,
'top_chart': '',
'msg_count': 0,
'percent': 0,
'uids': []
}
user_position = 0
asc_msg_count = 0
if salo:
q = q[::-1]
magic_percent = 100
q_all_length = len(q) # if type(q) is list else len(q.all())
if not salo and q_all_length > 25:
magic_percent = 146
for user_stat, user in q:
count = user_stat.all_messages_count
user_position += 1
asc_msg_count += count
raw_percent = count * magic_percent / all_msg_count
percent = cls.number_format(raw_percent, 2)
user_mat = '' if not mat else cls.__get_user_mat(user_stat)
top_chart += f"<b>{user_position}. {user.fullname}</b> — <b>{count}</b> ({percent}%){user_mat}\n"
uids.append(user.uid)
if not fullstat and user_position >= CONFIG['top_users_num']:
break
if salo and count > 15:
break
if not salo:
users_count = q_all_length
else:
users_count = user_position
all_users: typing.List[ChatUser] = []
try:
all_users = ChatUserDB.get_all(cid)
except Exception as e:
logger.error(e)
active_user_ids = [x.uid for _, x in q]
silent_lines = []
for chat_user in all_users:
if chat_user.uid not in active_user_ids:
user = User.get(chat_user.uid)
if user is not None:
username_if_salo = ' @{}'.format(
user.username) if tag_salo else ''
silent_lines.append("<b>{}</b>{}\n".format(
user.fullname, username_if_salo))
if len(silent_lines) > 0:
top_chart += "\nСовсем молчуны:\n"
top_chart += ''.join(silent_lines)
else:
top_chart += "\nСовсем молчунов нет 🙊\n"
if not fullstat or salo:
msg_count_percent = cls.number_format(
asc_msg_count * 100 / all_msg_count, 2)
all_msg_count = asc_msg_count
return {
'users_count': users_count,
'top_chart': top_chart,
'msg_count': all_msg_count,
'percent': msg_count_percent,
'uids': uids
}
def get_chat_year(cls, cid: int, year: int):
def get_year_all_msg_count():
try:
with session_scope() as db:
q = db.query(func.sum(UserStatDB.all_messages_count)) \
.filter(UserStatDB.last_activity >= datetime(year, 1, 1)) \
.filter(UserStatDB.last_activity < datetime(year + 1, 1, 1)) \
.filter(UserStatDB.cid == cid) \
.all()[0][0]
if q is None:
return 0
except Exception as e:
logger.error(e)
return 0
count = int(q)
return count
top_chart = ''
uids = []
all_msg_count = get_year_all_msg_count()
q = []
try:
with session_scope() as db:
# noinspection PyUnresolvedReferences
q = db.execute(f"""
SELECT `uid`, SUM(`all_messages_count`) AS `count`
FROM `user_stats`
WHERE `last_activity` >= '{year}-01-01'
AND `last_activity` < '{year + 1}-01-01'
AND `cid` = {cid}
AND `all_messages_count` > 0
GROUP BY `uid`
ORDER BY `count` DESC
""")
q = [(r[0], int(r[1])) for r in q]
except Exception as e:
logger.error(e)
if len(q) == 0:
return {
'users_count': 0,
'top_chart': '',
'msg_count': 0,
'percent': 0,
'uids': []
}
user_position = 0
asc_msg_count = 0
q_all_length = len(q) # if type(q) is list else len(q.all())
for uid, count in q:
user_position += 1
asc_msg_count += count
raw_percent = count * 100 / all_msg_count
percent = cls.number_format(raw_percent, 2)
user = User.get(uid)
name = user.fullname if user else uid
top_chart += f"{user_position}. {name} — {count} ({percent}%)\n"
uids.append(uid)
users_count = q_all_length
return {
'users_count': users_count,
'top_chart': top_chart,
'msg_count': all_msg_count,
'uids': uids
}
