def get_chats(category=None):
if category:
return sql.select(where="chats",
condition=dict(category=category),
multiple=True)
else:
return sql.select(where="chats", multiple=True)
def get_count_for_category(category):
count_users = sql.select(where="chats",
what="SUM(count_users)",
condition={"category": category})
count_chats = sql.select(where="chats",
what="COUNT(*)",
condition={"category": category})
return count_chats, count_users
def load_answers(category):
return sql.select(where="answers q, chats c",
what="q.chat_id, q.poll_id, q.answer, q.message_id",
condition={
"q.chat_id": "=c.chat_id",
"c.category": category
})
def load_questions(category):
return sql.select(where="questions q, chats c",
what="q.chat_id, q.poll_id, q.question, q.message_id",
condition={
"q.chat_id": "=c.chat_id",
"c.category": category
})
def questions_to_send(category):
return sql.select(where="winner_questions w, chats c",
what="w.id, c.chat_id, w.question",
condition={
"w.category": category,
"c.chat_id": "w.chat_id"
})
def add_chat(chat_id) -> bool:
exists = sql.select(where="chats", condition={"chat_id": chat_id})
if not exists:
logging.info(chat_id)
sql.insert(table="chats", chat_id=chat_id)
return False
else:
return True
def get_winner_question_id(chat_id):
return sql.select(what="s.message_id",
where="winner_questions w, chats, sent_messages s",
condition={
"chats.category": "=w.category",
"chats.chat_id": chat_id,
"s.chat_id": chat_id,
"w.id": "=s.id_question"
})
def add_winner_question(chat_id, category, question, message_id, poll_id):
if sql.select(where="winner_questions", condition={"category": category}):
sql.update(table="winner_questions",
chat_id=chat_id,
message_id=message_id,
question=question,
poll_id=poll_id,
condition=dict(category=category))
else:
sql.insert(table="winner_questions",
chat_id=chat_id,
message_id=message_id,
question=question,
poll_id=poll_id,
category=category)
def get_winner_answers(category):
answers = sql.select(where="winner_answers",
what="answer",
order="vote_count DESC",
limit=3,
multiple=True,
condition={"category": category})
question = get_winner_question(category)
if question and answers:
emo = ["☀️", "🌤️", "⛅️"]
text = f"☁️ {question}\n\n"
text += "\n".join(
[f"{emo[num]} {answer}" for num, (answer, ) in enumerate(answers)])
text += "\n\n#А"
return text
def add_category(chat_id, category, language):
if sql.select(where="chats", condition={"chat_id": chat_id}):
sql.update(table="chats",
category=category,
language=language,
condition=dict(chat_id=chat_id))
def get_category(chat_id):
return sql.select(what="category",
where="chats",
condition=dict(chat_id=chat_id))
def get_categories(language=None):
return sql.select(what="DISTINCT category",
where="chats",
condition=dict(language=language) if language else None,
multiple=True)
def get_sent(category):
return sql.select(where="sent_messages s, chats c",
condition={
"s.chat_id": "c.chat_id",
"c.category": category
})
def get_winner_question(category: str):
return sql.select(where="winner_questions",
what="question",
condition=dict(category=category))