Automação_de_sistemas_e_processos.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "effebfc5",
   "metadata": {},
   "source": [
    "# Desafio\n",
    "\n",
    "Dado uma tabela que contém informações de uma empresa, deseja- se estimar o faturamento e a quantidade de produtos vendidos \n",
    "\n",
    "Meu objetivo: realizar isso de forma automatizada. Ou seja, o próprio computador irá realizar as operações, o download dos arquivos e os envios das contas para emails. \n",
    "\n",
    "*Entrar no sistema da empresa -> Baixar a base de dados -> Importar a base no Jupyter -> Realizar os cálculos -> Enviar emails*.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cdce1010",
   "metadata": {},
   "outputs": [],
   "source": [
    "#São funções capazes de automarizar o mouse, o teclado e a tela\n",
    "\n",
    "import pyautogui as pa\n",
    "import pyperclip as pe\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "01b27b3b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Baixar o arquivo de maneira automatizada\n",
    "\n",
    "pa.PAUSE = 0.01\n",
    "\n",
    "pa.hotkey(\"Ctrl\", \"t\")\n",
    "pe.copy(' https://drive.google.com/drive/folders/149xknr9JvrlEnhNWO49zPcw0PW5icxga?usp=sharing')\n",
    "pa.hotkey(\"Ctrl\", 'v')\n",
    "pa.press('enter')\n",
    "\n",
    "time.sleep(3) #Importante para o computador esperar os arquivos carregarem antes de dar prosseguimento no código\n",
    "#Caso não haja time.sleep(), pode ocorrer problemas. \n",
    "\n",
    "list_positions = [[619,455], [503,582], [1615,291], [1450,942]]\n",
    "\n",
    "for i in list_positions:\n",
    "    if i == [619,455]:\n",
    "        number_of_clicks = 2\n",
    "    else: \n",
    "        number_of_clicks = 1 \n",
    "    pa.click(x=i[0], y=i[1], clicks = number_of_clicks)\n",
    "    time.sleep(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c32aebc4",
   "metadata": {},
   "source": [
    "\n",
    "Podiamos resolver o comando acima com:\n",
    "\n",
    "pa.click(x=503, y=582, button = 'left')\n",
    "\n",
    "pa.click(x=791, y=949, clicks = 2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "403863f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#A biblioteca pandas nos auxilia na análise de dados\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "51e9ccc8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Código Venda</th>\n",
       "      <th>Data</th>\n",
       "      <th>ID Loja</th>\n",
       "      <th>Produto</th>\n",
       "      <th>Quantidade</th>\n",
       "      <th>Valor Unitário</th>\n",
       "      <th>Valor Final</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>65014</td>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>Shopping Morumbi</td>\n",
       "      <td>Sunga Listrado</td>\n",
       "      <td>5</td>\n",
       "      <td>114</td>\n",
       "      <td>570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65014</td>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>Shopping Morumbi</td>\n",
       "      <td>Casaco Listrado</td>\n",
       "      <td>1</td>\n",
       "      <td>269</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>65016</td>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>Iguatemi Campinas</td>\n",
       "      <td>Sapato Listrado</td>\n",
       "      <td>2</td>\n",
       "      <td>363</td>\n",
       "      <td>726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>65016</td>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>Iguatemi Campinas</td>\n",
       "      <td>Casaco</td>\n",
       "      <td>1</td>\n",
       "      <td>250</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65017</td>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>Shopping SP Market</td>\n",
       "      <td>Gorro Liso</td>\n",
       "      <td>3</td>\n",
       "      <td>92</td>\n",
       "      <td>276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7084</th>\n",
       "      <td>69996</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Center Shopping Uberlândia</td>\n",
       "      <td>Short Listrado</td>\n",
       "      <td>2</td>\n",
       "      <td>102</td>\n",
       "      <td>204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7085</th>\n",
       "      <td>69996</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Center Shopping Uberlândia</td>\n",
       "      <td>Mochila</td>\n",
       "      <td>4</td>\n",
       "      <td>270</td>\n",
       "      <td>1080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7086</th>\n",
       "      <td>69996</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Center Shopping Uberlândia</td>\n",
       "      <td>Pulseira Estampa</td>\n",
       "      <td>1</td>\n",
       "      <td>87</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7087</th>\n",
       "      <td>69997</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Ribeirão Shopping</td>\n",
       "      <td>Camisa Listrado</td>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7088</th>\n",
       "      <td>69997</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Ribeirão Shopping</td>\n",
       "      <td>Short Linho</td>\n",
       "      <td>2</td>\n",
       "      <td>133</td>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7089 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Código Venda       Data                     ID Loja           Produto  \\\n",
       "0            65014 2019-12-01            Shopping Morumbi    Sunga Listrado   \n",
       "1            65014 2019-12-01            Shopping Morumbi   Casaco Listrado   \n",
       "2            65016 2019-12-01           Iguatemi Campinas   Sapato Listrado   \n",
       "3            65016 2019-12-01           Iguatemi Campinas            Casaco   \n",
       "4            65017 2019-12-01          Shopping SP Market        Gorro Liso   \n",
       "...            ...        ...                         ...               ...   \n",
       "7084         69996 2019-12-26  Center Shopping Uberlândia    Short Listrado   \n",
       "7085         69996 2019-12-26  Center Shopping Uberlândia           Mochila   \n",
       "7086         69996 2019-12-26  Center Shopping Uberlândia  Pulseira Estampa   \n",
       "7087         69997 2019-12-26           Ribeirão Shopping   Camisa Listrado   \n",
       "7088         69997 2019-12-26           Ribeirão Shopping       Short Linho   \n",
       "\n",
       "      Quantidade  Valor Unitário  Valor Final  \n",
       "0              5             114          570  \n",
       "1              1             269          269  \n",
       "2              2             363          726  \n",
       "3              1             250          250  \n",
       "4              3              92          276  \n",
       "...          ...             ...          ...  \n",
       "7084           2             102          204  \n",
       "7085           4             270         1080  \n",
       "7086           1              87           87  \n",
       "7087           1             108          108  \n",
       "7088           2             133          266  \n",
       "\n",
       "[7089 rows x 7 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Importar tabela para o Jupyter\n",
    "frame = pd.read_excel(r'/Users/anaca/Downloads/Vendas - Dez.xlsx')\n",
    "display(frame)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f762a659",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7089 entries, 0 to 7088\n",
      "Data columns (total 7 columns):\n",
      " #   Column          Non-Null Count  Dtype         \n",
      "---  ------          --------------  -----         \n",
      " 0   Código Venda    7089 non-null   int64         \n",
      " 1   Data            7089 non-null   datetime64[ns]\n",
      " 2   ID Loja         7089 non-null   object        \n",
      " 3   Produto         7089 non-null   object        \n",
      " 4   Quantidade      7089 non-null   int64         \n",
      " 5   Valor Unitário  7089 non-null   int64         \n",
      " 6   Valor Final     7089 non-null   int64         \n",
      "dtypes: datetime64[ns](1), int64(4), object(2)\n",
      "memory usage: 387.8+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(frame.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "00cd7a99",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total de produtos vendidos: 7,089\n",
      "Faturamento: R$ 2,917,311.00\n"
     ]
    }
   ],
   "source": [
    "selled_products = frame['Quantidade'].count()\n",
    "revenues = frame['Valor Final'].sum()\n",
    "\n",
    "print(f'Total de produtos vendidos: {selled_products:,}')\n",
    "print(f'Faturamento: R$ {revenues:,.2f}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d3ffd463",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Envio de emails de forma automatizada \n",
    "\n",
    "link_email = 'https://mail.google.com/mail/u/0/?pli=1#inbox'\n",
    "email_endress = 'anacarolina.ovidio@estudante.ufjf.br'\n",
    "subject = 'Teste de envio autômato'\n",
    "\n",
    "pa.hotkey(\"Ctrl\", \"t\")\n",
    "pe.copy(link_email)\n",
    "pa.hotkey(\"Ctrl\", 'v')\n",
    "pa.press('enter')\n",
    "time.sleep(5)\n",
    "\n",
    "pa.click(x=185, y=297)\n",
    "time.sleep(3)\n",
    "pe.copy(email_endress)\n",
    "pa.hotkey(\"Ctrl\", 'v')\n",
    "time.sleep(1)\n",
    "pa.press('enter')\n",
    "pa.press('tab')\n",
    "time.sleep(1)\n",
    "pe.copy(subject)\n",
    "pa.hotkey(\"Ctrl\", 'v')\n",
    "time.sleep(1)\n",
    "\n",
    "\n",
    "text = f'''\n",
    "Total de produtos vendidos: {selled_products:,}\n",
    "Faturamento: R$ {revenues:,.2f}\n",
    "'''\n",
    "pa.press('tab')\n",
    "pe.copy(text)\n",
    "pa.hotkey(\"Ctrl\", 'v')\n",
    "time.sleep(3)\n",
    "\n",
    "pa.hotkey(\"Ctrl\", 'enter')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ffdd708",
   "metadata": {},
   "source": [
    "## Saber as coordenadas do mouse\n",
    "\n",
    "pa.position() -- O resultado será em coordenadas cartesianas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "68f41f8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Point(x=703, y=339)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "time.sleep(5)\n",
    "display(pa.position())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}