4.2 Report_system.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "11e90da7-9a49-42cc-9d5b-ae5530603d6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "137059 133540 107670\n"
     ]
    }
   ],
   "source": [
    "import telegram\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import io\n",
    "import pandas as pd\n",
    "import pandahouse\n",
    "from read_db.CH import Getch\n",
    "import os\n",
    "\n",
    "sns.set()\n",
    "\n",
    "\n",
    "# Создадим вспомогательную функцию\n",
    "def sign(x):\n",
    "    \"\"\" Данная функция возвращает число со знаком \"\"\"\n",
    "    if x > 0:\n",
    "        return '+' + str(x) + str('%')\n",
    "    else:\n",
    "        return str(x) + str('%')\n",
    "\n",
    "\n",
    "def report(chat=None):\n",
    "    \"\"\" Данная функция создает запрос в БД и присылает отчет по базовым метрикам в telegram\"\"\"\n",
    "    chat_id = chat or 2093549033\n",
    "    # bot = telegram.Bot(token=os.environ.get(\"REPORT_BOT_TOKEN\"))  # шировка токена по пути Settings -> CI/CD -> Variables\n",
    "    bot = telegram.Bot(token='**************************************************')\n",
    "\n",
    "    \n",
    "    query = '''\n",
    "    SELECT toStartOfDay(toDateTime(day)) AS day_,\n",
    "       sum(like) AS like,\n",
    "       sum(view) AS view,\n",
    "       sum(message) AS message\n",
    "    FROM\n",
    "      (select *\n",
    "       from\n",
    "         (select toDate(time) as day,\n",
    "                 user_id,\n",
    "                 countIf(user_id, action='like') as like,\n",
    "                 countIf(user_id, action='view') as view\n",
    "          from {db}.feed_actions\n",
    "          group by day, user_id) t1\n",
    "          \n",
    "       FULL join\n",
    "       \n",
    "         (select toDate(time) as day,\n",
    "                 user_id,\n",
    "                 count(reciever_id) as message\n",
    "          from simulator_20220120.message_actions\n",
    "          group by day, user_id) t2 \n",
    "          \n",
    "        using day, user_id\n",
    "        order by day, user_id) AS virtual_table\n",
    "    WHERE day_ <= yesterday() and day_ > yesterday() - 8\n",
    "    GROUP BY toStartOfDay(toDateTime(day))\n",
    "    ORDER BY day_ DESC;\n",
    "    '''\n",
    "    #  FROM simulator_20220120.feed_actions\n",
    "        \n",
    "    \n",
    "    data = Getch(query).df\n",
    "    day = data.day_[0]\n",
    "    view = data.view[0]\n",
    "    view_to_day = round((data.view[0] / data.view[1] - 1) * 100, 2)\n",
    "    view_to_week = round((data.view[0] / data.view[7] - 1) * 100, 2)\n",
    "    \n",
    "    like = data.like[0]\n",
    "    like_to_day = round((data.like[0] / data.like[1] - 1) * 100, 2)\n",
    "    like_to_week = round((data.like[0] / data.like[7] - 1) * 100, 2)\n",
    "    \n",
    "    message = data.message[0]\n",
    "    message_to_day = round((data.message[0] / data.message[1] - 1) * 100, 2)\n",
    "    message_to_week = round((data.message[0] / data.message[7] - 1) * 100, 2)\n",
    "    \n",
    "\n",
    "    \n",
    "    # Динамика количества лайков, постов, сообщений за 1 день/неделю\n",
    "    msg = ('Отчет за ' + str(day).split(' ')[0] + ':' + str('\\n') +\n",
    "           'Просмотры: ' + str('\\n') + \n",
    "           str(view) + str(' / ') + sign(view_to_day) + str(' / ') + sign(view_to_week) + str('\\n') + \n",
    "           \n",
    "           'Лайки: '  + str('\\n') +\n",
    "           str(like) + str(' / ') + sign(view_to_day) + str(' / ') + sign(like_to_week) + str('\\n') + \n",
    "           \n",
    "           'Сообщения: ' + str('\\n') +\n",
    "           str(message) + str(' / ') + sign(message_to_day) + str(' / ') + sign(message_to_week) + str('\\n') + \n",
    "           '--------------------------------------------------------------' + str('\\n') +\n",
    "           'кол-во/динамика за день/неделю')\n",
    "\n",
    "    bot.sendMessage(chat_id=chat_id, text= msg)\n",
    "    \n",
    " \n",
    "\n",
    "    # Отправка графиков по базовым метрикам\n",
    "    y = data.iloc[:7, [2,1,3]]\n",
    "    x = data.iloc[:7, 0]\n",
    "    plt.title('Динамика количества лайков, постов, сообщений')\n",
    "    plt.xticks(rotation=15)\n",
    "    plt.plot(x,y)\n",
    "    plt.legend(['views', 'likes', 'messages'], )\n",
    "\n",
    "    plot_object = io.BytesIO()\n",
    "    plt.savefig(plot_object)\n",
    "    plot_object.name = 'plot.png'\n",
    "    plot_object.seek(0)\n",
    "    bot.sendPhoto(chat_id = chat_id, photo = plot_object)\n",
    "    plt.close()\n",
    "\n",
    "    \n",
    "\n",
    "    query2 = '''\n",
    "    SELECT post_id AS post_id,\n",
    "       countIf(action = 'view') AS \"view\",\n",
    "       countIf(action = 'like') AS \"like\",\n",
    "       countIf(action = 'like') / countIf(action = 'view') AS \"CTR\",\n",
    "       count(DISTINCT user_id) AS \"courage\"\n",
    "    FROM {db}.feed_actions\n",
    "    WHERE toDate(time) = yesterday()\n",
    "    GROUP BY post_id\n",
    "    ORDER BY \"view\" DESC\n",
    "    LIMIT 10;\n",
    "    '''\n",
    "    #  FROM simulator_20220120.feed_actions\n",
    "\n",
    "    \n",
    "    data2 = Getch(query2).df\n",
    "    file_object = io.StringIO()\n",
    "    data2.to_csv(file_object, index=False)\n",
    "    name_file = str(day).split(' ')[0] + ' TOP_10_posts' + '.csv'\n",
    "    file_object.name = name_file\n",
    "    file_object.seek(0)\n",
    "    bot.sendDocument(chat_id=chat_id, document=file_object)\n",
    "\n",
    "\n",
    "try:\n",
    "    report()\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}