Automação de Envio de E-mail com Dados de Vendas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ed8c5dc6",
   "metadata": {},
   "source": [
    "# Automação de Sistemas e Processos"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7804ce39",
   "metadata": {},
   "source": [
    "## Passo a Passo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa139c65",
   "metadata": {},
   "source": [
    "Este é o passo a passo envolvido para a solução deste desafio:\n",
    "\n",
    "1. Abrir uma aba em branco no navegador\n",
    "2. Digitar a URL do arquivo armazenado no Google Drive\n",
    "3. Pressionar 'enter'\n",
    "4. Clicar na pasta 'Exportar'\n",
    "5. Clicar no menu de três pontos\n",
    "6. Clicar na opção 'Fazer Download'\n",
    "7. Importar a planilha intitulada 'Vendas - Dez.xlsx' com o pandas\n",
    "8. Obter os indicadores\n",
    "9. Abrir uma nova aba em branco no navegador para acessar o Gmail\n",
    "10. Digitar o endereço do Gmail na barra de endereços\n",
    "11. Pressionar 'enter'\n",
    "12. Clicar no botão 'Escrever'\n",
    "13. Preencher os campos da caixa de e-mail\n",
    "14. Enviar o e-mail"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e05f818",
   "metadata": {},
   "source": [
    "## Observações"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b5ed2e5",
   "metadata": {},
   "source": [
    "Esta automação foi usada para o cenário em que o usuário estava logado em uma conta do Google e em que a opção para downloads estava configurada para usar a pasta Downloads como destino para os arquivos baixados."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e200cb6f",
   "metadata": {},
   "source": [
    "## Baixando o Arquivo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a873ca4",
   "metadata": {},
   "source": [
    "Nesta etapa vamos abrir uma nova aba no navegador e baixar a planilha para a pasta de Downloads."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b0d50276-09a6-4dde-ab83-8006f1688eb2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pyautogui\n",
    "import time\n",
    "\n",
    "# Define uma pausa de 1.5s entre os comandos do pyautogui (failsafe)\n",
    "pyautogui.PAUSE = 1.5\n",
    "\n",
    "# Abre uma aba em branco no navegador\n",
    "pyautogui.hotkey('ctrl', 't')\n",
    "\n",
    "# Escreve na barra de endereços o link para acessar a planilha\n",
    "pyautogui.write(r'https://drive.google.com/drive/folders/149xknr9JvrlEnhNWO49zPcw0PW5icxga?usp=sharing')\n",
    "\n",
    "# Dá enter na barra de endereços\n",
    "pyautogui.press('enter')\n",
    "\n",
    "# Aguarda 5s\n",
    "time.sleep(5)\n",
    "\n",
    "# Obtém as coordenadas do centro da figura\n",
    "x, y = pyautogui.locateCenterOnScreen('botao-exportar.png')\n",
    "\n",
    "# Dá um duplo clique no centro da imagem\n",
    "pyautogui.click(x, y, clicks=2)\n",
    "\n",
    "# Aguarda 2s\n",
    "time.sleep(2)\n",
    "\n",
    "# Clica no arquivo (planilha)\n",
    "pyautogui.click(x=472, y=296)\n",
    "\n",
    "# Clica no menu de três pontos\n",
    "pyautogui.click(x=768, y=201)\n",
    "\n",
    "# Clica na opção de download\n",
    "pyautogui.click(x=850, y=571)\n",
    "\n",
    "# Aguarda 5s\n",
    "time.sleep(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "530fe6e1",
   "metadata": {},
   "source": [
    "## Análise de Dados"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5292055",
   "metadata": {},
   "source": [
    "Nesta etapa vamos usar o **pandas** para analisar a base de dados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3168ce2d",
   "metadata": {
    "tags": []
   },
   "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": [
    "import pandas as pd\n",
    "\n",
    "# Cria um dataframe a partir da planilha de vendas\n",
    "df_vendas = pd.read_excel('/home/diego/Downloads/Vendas - Dez.xlsx')\n",
    "\n",
    "# Exibe o dataframe\n",
    "display(df_vendas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fc7720a5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Faturamento: R$2.917.311,00\n",
      "Unidades vendidas: 15.227\n"
     ]
    }
   ],
   "source": [
    "# Obtém o faturamento total\n",
    "faturamento = df_vendas['Valor Final'].sum()\n",
    "\n",
    "# Texto para faturamento\n",
    "faturamento = 'R${:_.2f}'.format(faturamento).replace('.', ',').replace('_', '.')\n",
    "\n",
    "# Obtém o total de produtos vendidos\n",
    "unidades_vendidas = df_vendas['Quantidade'].sum()\n",
    "\n",
    "# Texto para unidades vendidas\n",
    "unidades_vendidas = '{:_}'.format(unidades_vendidas).replace('.', ',').replace('_', '.')\n",
    "\n",
    "# Exibe os indicadores\n",
    "print(f'Faturamento: {faturamento}')\n",
    "print(f'Unidades vendidas: {unidades_vendidas}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f206d42e",
   "metadata": {},
   "source": [
    "## Envio de E-mail"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df592b9b",
   "metadata": {},
   "source": [
    "Para enviar um e-mail, basta criar mais uma aba para abrir o Gmail, clicar em escrever e-mail e preencher os campos da caixa de e-mail."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "63869b2a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Abre uma aba em branco no navegador\n",
    "pyautogui.hotkey('ctrl', 't')\n",
    "\n",
    "# Digita o link na barra de endereços\n",
    "pyautogui.write(r'https://mail.google.com/mail/u/0/?tab=rm&ogbl#inbox')\n",
    "\n",
    "# Dá enter\n",
    "pyautogui.press('enter')\n",
    "\n",
    "# Aguarda 5s\n",
    "time.sleep(5)\n",
    "\n",
    "# Clica no botão para escrever e-mail\n",
    "pyautogui.click(x=148, y=200)\n",
    "\n",
    "# Escreve o endereço de e-mail no respectivo campo\n",
    "pyautogui.write('diegotorrescoder+diretoria@gmail.com')\n",
    "\n",
    "# Dá um tab para pular para o próximo e-mail\n",
    "pyautogui.press('tab')\n",
    "\n",
    "# Dá um tab para pular para o campo de assunto\n",
    "pyautogui.press('tab')\n",
    "\n",
    "# Escreve o assunto do e-mail\n",
    "pyautogui.write('Relatorio de Vendas')\n",
    "\n",
    "# Dá um tab para pular o corpo do e-mail\n",
    "pyautogui.press('tab')\n",
    "\n",
    "# Mensagem a ser enviada\n",
    "mensagem = f'''Prezado Diretor,\n",
    "Estou lhe enviando os resultados da analise dos dados de vendas.\n",
    "\n",
    "Faturamento: {faturamento}\n",
    "Unidades vendidas: {unidades_vendidas}\n",
    "\n",
    "Atenciosamente,\n",
    "Diego Moura Torres'''\n",
    "\n",
    "# Digita a mensagem no corpo do e-mail\n",
    "pyautogui.write(mensagem)\n",
    "\n",
    "# Envia o e-mail\n",
    "pyautogui.hotkey('ctrl', 'enter')"
   ]
  }
 ],
 "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.10.9"
  },
  "vscode": {
   "interpreter": {
    "hash": "e71f299a5bf3d7dc40e73e139723bea0031d6369712625678d30401e652cd2f9"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}