def save_bot_repost_info(messageFromSender, forwardedMessage): ## Создание поста (время создания)
try:
cursor = conn.cursor()
if messageFromSender.from_user.first_name and messageFromSender.from_user.last_name:
name = messageFromSender.from_user.first_name + " " + messageFromSender.from_user.last_name
else:
name = "Имя не указано"
if forwardedMessage.sender_chat:
chatId = forwardedMessage.sender_chat.id
else:
chatId = forwardedMessage.chat.id
cursor.execute(
'INSERT INTO for_posts (user_id, username, created_at, text, message_id, chanel_id) VALUES (?, ?, ?, ?, ?, ?)',
(
messageFromSender.from_user.id,
name,
messageFromSender.date,
messageFromSender.text,
forwardedMessage.message_id,
chatId
)
)
conn.commit()
cursor.close()
except sqlite3.Error as error:
print("Ошибка сохранения: ", error)
finally:
print("Все прошло успешно")def save_engineer_reply_info(message): ##Ответ на пост (время ответа)
cursor = conn.cursor()
if message.from_user.first_name and message.from_user.last_name:
name = message.from_user.first_name + " " + message.from_user.last_name
else:
name = "Имя не указано"
if message.sender_chat:
chatId = message.sender_chat.id
else:
chatId = message.chat.id
Replymessage = cursor4.execute ('SELECT * FROM for_reply WHERE(reply_to_id) = (?)', (message.reply_to_message.forward_from_message_id,))
if Replymessage.fetchone() is None:
cursor.execute(
'INSERT INTO for_reply (user_id, username, created_at, text, reply_to_id, message_id, chanel_id) VALUES (?, ?, ?, ?, ?, ?,?)',
(
message.from_user.id,
name,
message.date,
message.text,
message.reply_to_message.forward_from_message_id,
message.message_id,
chatId
)
)
conn.commit()
cursor.close()
else:
print('есть в базе')
cursor.close()
returndef collect_analytics_by_user(message):
validationResult = check_user_date(message.text)
cursor5 = conn.cursor()
query = "SELECT strftime('%d-%m-%Y', datetime(for_posts.created_at, 'unixepoch', 'localtime')), for_reply.user_id, for_reply.created_at - for_posts.created_at as diff, for_reply.username FROM for_posts LEFT JOIN for_reply ON for_reply.reply_to_id = for_posts.message_id"
query = query + " WHERE for_reply.created_at BETWEEN ? AND ? GROUP BY for_posts.message_id ORDER BY for_posts.created_at DESC" ##for_posts.created_at , for_reply.created_at (БЫЛА) ##
##GROUP BY for_posts.message_id ORDER BY for_posts.created_at DESC" было
if validationResult == CheckDateResult.DATE:
cursor5.execute(query, dateToTimestamp(message.text, message.text))
elif validationResult == CheckDateResult.INTERVAL:
interval = message.text.split("-")
cursor5.execute(
query,
dateToTimestamp(interval[0], interval[1])
)
else:
analytics_ask_date(message)
return
result = cursor5.fetchall()
cursor5.close
print(result)
# open the file in the write mode
with open('report' + str(message.message_id) + ".csv", 'a+', encoding='UTF8', newline='') as f: ##Выгрузка общая по всем каналам
# create the csv writer
writer = csv.writer(f)
writer.writerow([
"Интервал",
"ФИО",
"Всего сообщений, шт",
"До \n30 сек",
"После \n30 секунд",
"SL%"
])
interval = message.text
messagesCount = len(result)
under30 = 0
top30 = 0
byGroup = {}
for row in result:
try:
byGroup[row[1]]["s"] = 0
except KeyError:
byGroup[row[1]] = {
"top30": 0,
"under30": 0,
"messagesCount": 0,
"s": 0
}
if row[2] == None:
messagesCount = messagesCount - 1
continue
if byGroup[row[1]]["messagesCount"] == None:
byGroup[row[1]]["messagesCount"] = 0
byGroup[row[1]]["messagesCount"] += 1
if row[2] > 30:
if byGroup[row[1]]["top30"] == None:
byGroup[row[1]]["top30"] = 0
byGroup[row[1]]["top30"] += 1
else:
if byGroup[row[1]]["under30"] == None:
byGroup[row[1]]["under30"] = 0
byGroup[row[1]]["under30"] += 1
for key in byGroup.keys():
if byGroup[key]["messagesCount"] == 0:
continue
top30 = byGroup[key]["top30"]
under30 = byGroup[key]["under30"]
messagesCount = byGroup[key]["messagesCount"]
percent = messagesCount / 100 #round((int(top30) / (int(messagesCount) / 100)), 2)
sl = round(under30 / percent, 2)
try:
managerName = managers[key]
except KeyError:
continue
writer.writerow([
interval,
managerName,
str(messagesCount),
str(under30),
str(top30),
str(sl)
])
f.close()
csv_to_execel('report' + str(message.message_id))
with open('report' + str(message.message_id) + ".xlsx", 'rb') as f: ##сохранение документа и его отправка
bot.send_document(message.chat.id, f)
f.close()def collect_analytics_by_group(message):
validationResult = check_user_date(message.text)
cursor5 = conn.cursor()
query = "SELECT strftime('%d-%m-%Y', datetime(for_posts.created_at, 'unixepoch', 'localtime')), for_posts.chanel_id, for_reply.created_at - for_posts.created_at as diff FROM for_posts LEFT JOIN for_reply ON for_reply.reply_to_id = for_posts.message_id"
query = query + " WHERE for_posts.created_at BETWEEN ? AND ? GROUP BY for_posts.message_id ORDER BY for_posts.created_at DESC"
if validationResult == CheckDateResult.DATE:
cursor5.execute(query, dateToTimestamp(message.text, message.text))
elif validationResult == CheckDateResult.INTERVAL:
interval = message.text.split("-")
cursor5.execute(
query,
dateToTimestamp(interval[0], interval[1])
)
else:
analytics_ask_date(message)
return
result = cursor5.fetchall()
cursor5.close
# open the file in the write mode
with open('report' + str(message.message_id) + ".csv", 'a+', encoding='UTF8', newline='') as f: ##Выгрузка общая по всем каналам
# create the csv writer
writer = csv.writer(f)
writer.writerow([
"Интервал",
"Группа",
"Всего сообщений, шт",
"До \n30 сек",
"После \n30 секунд", "SL%"])
interval = message.text
messagesCount = len(result)
under30 = 0
top30 = 0
byGroup = {}
for row in result:
try:
byGroup[row[1]]["s"] = 0
except KeyError:
byGroup[row[1]] = {
"top30": 0,
"under30": 0,
"messagesCount": 0,
"s": 0
}
if row[2] == None:
messagesCount = messagesCount - 1
continue
if byGroup[row[1]]["messagesCount"] == None:
byGroup[row[1]]["messagesCount"] = 0
byGroup[row[1]]["messagesCount"] += 1
if row[2] > 30:
if byGroup[row[1]]["top30"] == None:
byGroup[row[1]]["top30"] = 0
byGroup[row[1]]["top30"] += 1
else:
if byGroup[row[1]]["under30"] == None:
byGroup[row[1]]["under30"] = 0
byGroup[row[1]]["under30"] += 1
for key in byGroup.keys():
if byGroup[key]["messagesCount"] == 0:
continue
top30 = byGroup[key]["top30"]
under30 = byGroup[key]["under30"]
messagesCount = byGroup[key]["messagesCount"]
percent = messagesCount / 100 #round((int(top30) / (int(messagesCount) / 100)), 2)
sl = round(under30 / percent, 2)
writer.writerow([
interval,
channels[key],
str(messagesCount),
str(under30),
str(top30),
str(sl)
])
f.close()
csv_to_execel('report' + str(message.message_id))
with open('report' + str(message.message_id) + ".xlsx", 'rb') as f: ##сохранение документа и его отправка
bot.send_document(message.chat.id, f)
f.close()Warning (from warnings module):
File "C:\Users\xxx\AppData\Local\Programs\Python\Python310\lib\site-packages\telebot\util.py", line 87
task(*args, **kwargs)
RuntimeWarning: coroutine 'echo_photo' was never awaited