Envio Automatizado de OnePages por E-mail.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Envio Automatizado de OnePages por E-mail"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Entendendo a Situação"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Você trabalha em uma rede de lojas de roupas que possui 25 lojas espalhadas pelo Brasil.\n",
    "\n",
    "Diariamente, os Analistas de Dados calculam o **OnePage** de cada loja e enviam para o gerente da respectiva loja.\n",
    "\n",
    "O **OnePage** é um resumo gerencial que nos permite analisar o desempenho de uma loja com base nos seguintes indicadores:\n",
    "\n",
    "<table>\n",
    "    <thead>\n",
    "        <tr>\n",
    "            <th>\n",
    "                Indicador\n",
    "            </th>\n",
    "            <th>\n",
    "                Meta do Ano\n",
    "            </th>\n",
    "            <th>\n",
    "                Meta do Dia\n",
    "            <th>\n",
    "        </tr>\n",
    "    </thead>\n",
    "    <tbody>\n",
    "        <tr>\n",
    "            <td>\n",
    "                Faturamento\n",
    "            </td>\n",
    "            <td>\n",
    "                1.650.000\n",
    "            </td>\n",
    "            <td>\n",
    "                1.000\n",
    "            </td>\n",
    "        </tr>\n",
    "        <tr>\n",
    "            <td>\n",
    "                Diversidade de Produtos\n",
    "            </td>\n",
    "            <td>\n",
    "                120\n",
    "            </td>\n",
    "            <td>\n",
    "                4\n",
    "            </td>\n",
    "        </tr>\n",
    "        <tr>\n",
    "            <td>\n",
    "                Ticket Médio\n",
    "            </td>\n",
    "            <td>\n",
    "                500\n",
    "            </td>\n",
    "            <td>\n",
    "                500\n",
    "            </td>\n",
    "        </tr>\n",
    "    </tbody>\n",
    "    \n",
    "</table>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Desafio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nosso desafio envolve três passos:\n",
    "\n",
    "1. Calcular o OnePage de cada loja\n",
    "2. Enviar um e-mail para cada gerente com o OnePage de sua respectiva loja, anexando a planilha com os dados de venda da loja \n",
    "3. Enviar um e-mail para a diretoria com um ranking de faturamento das lojas, informando qual loja apresentou melhor desempenho e qual apresentou pior desempenho"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Solução Passo a Passo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 1 - Importar Bibliotecas e Arquivos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Importação das Bibliotecas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importa o módulo os\n",
    "import os\n",
    "\n",
    "# Importa o pandas com o apelido pd\n",
    "import pandas as pd\n",
    "\n",
    "# Importa a bibliioteca yagmail\n",
    "import yagmail\n",
    "\n",
    "# Importa a função para carregar as variáveis de ambiente\n",
    "from dotenv import load_dotenv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Carregamento das Variáveis de Ambiente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Caminho deste notebook\n",
    "caminho_nb = os.getcwd()\n",
    "\n",
    "# Carrega as variáveis de ambiente do arquivo .env (dotenv)\n",
    "load_dotenv(os.path.join(os.path.abspath(caminho_nb), '.env'))\n",
    "\n",
    "# E-mail do remetente\n",
    "remetente = os.getenv('EMAIL_REMETENTE')\n",
    "\n",
    "# Senha do remetente\n",
    "senha = os.getenv('SENHA_EMAIL_REMETENTE')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Definição das Metas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Metas de faturamento\n",
    "meta_faturamento_dia = 1_000\n",
    "meta_faturamento_ano = 1_650_000\n",
    "\n",
    "# Metas de diversidade de produtos\n",
    "meta_diversidade_produtos_dia = 4\n",
    "meta_diversidade_produtos_ano = 120\n",
    "\n",
    "# Metas de ticket médio\n",
    "meta_ticket_medio_dia = 500\n",
    "meta_ticket_medio_ano = 500"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Importação das Bases de Dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Caminho da pasta com as bases de dados\n",
    "pasta_bases_dados = os.path.join(caminho_nb, 'Bases de Dados')\n",
    "\n",
    "# Importa as bases de dados    \n",
    "df_vendas = pd.read_excel(os.path.join(pasta_bases_dados, 'Vendas.xlsx'))\n",
    "df_emails = pd.read_excel(os.path.join(pasta_bases_dados, 'Emails.xlsx'))\n",
    "df_lojas = pd.read_csv(os.path.join(pasta_bases_dados, 'Lojas.csv'), sep=';', encoding='latin1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exibição dos Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Estampa</td>\n",
       "      <td>1</td>\n",
       "      <td>358</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Camiseta</td>\n",
       "      <td>2</td>\n",
       "      <td>180</td>\n",
       "      <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Xadrez</td>\n",
       "      <td>1</td>\n",
       "      <td>368</td>\n",
       "      <td>368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>3</td>\n",
       "      <td>Relógio</td>\n",
       "      <td>3</td>\n",
       "      <td>200</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>3</td>\n",
       "      <td>Chinelo Liso</td>\n",
       "      <td>1</td>\n",
       "      <td>71</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Código Venda       Data  ID Loja         Produto  Quantidade  \\\n",
       "0             1 2019-01-01        1  Sapato Estampa           1   \n",
       "1             1 2019-01-01        1        Camiseta           2   \n",
       "2             1 2019-01-01        1   Sapato Xadrez           1   \n",
       "3             2 2019-01-02        3         Relógio           3   \n",
       "4             2 2019-01-02        3    Chinelo Liso           1   \n",
       "\n",
       "   Valor Unitário  Valor Final  \n",
       "0             358          358  \n",
       "1             180          360  \n",
       "2             368          368  \n",
       "3             200          600  \n",
       "4              71           71  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_vendas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Loja</th>\n",
       "      <th>Gerente</th>\n",
       "      <th>E-mail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "      <td>Helena</td>\n",
       "      <td>diegotorrescoder+helena@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shopping Midway Mall</td>\n",
       "      <td>Alice</td>\n",
       "      <td>diegotorrescoder+alice@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Norte Shopping</td>\n",
       "      <td>Laura</td>\n",
       "      <td>diegotorrescoder+laura@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shopping Iguatemi Fortaleza</td>\n",
       "      <td>Manuela</td>\n",
       "      <td>diegotorrescoder+manuela@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Shopping União de Osasco</td>\n",
       "      <td>Valentina</td>\n",
       "      <td>diegotorrescoder+valentina@gmail.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Loja    Gerente  \\\n",
       "0           Iguatemi Esplanada     Helena   \n",
       "1         Shopping Midway Mall      Alice   \n",
       "2               Norte Shopping      Laura   \n",
       "3  Shopping Iguatemi Fortaleza    Manuela   \n",
       "4     Shopping União de Osasco  Valentina   \n",
       "\n",
       "                                 E-mail  \n",
       "0     diegotorrescoder+helena@gmail.com  \n",
       "1      diegotorrescoder+alice@gmail.com  \n",
       "2      diegotorrescoder+laura@gmail.com  \n",
       "3    diegotorrescoder+manuela@gmail.com  \n",
       "4  diegotorrescoder+valentina@gmail.com  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_emails.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ID Loja</th>\n",
       "      <th>Loja</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Shopping Midway Mall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Norte Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Shopping Iguatemi Fortaleza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Shopping União de Osasco</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID Loja                         Loja\n",
       "0        1           Iguatemi Esplanada\n",
       "1        2         Shopping Midway Mall\n",
       "2        3               Norte Shopping\n",
       "3        4  Shopping Iguatemi Fortaleza\n",
       "4        5     Shopping União de Osasco"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lojas.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 2 - Criar uma Tabela para cada Loja e Definir o dia do Indicador"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Mesclando os Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "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",
       "      <th>Loja</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Estampa</td>\n",
       "      <td>1</td>\n",
       "      <td>358</td>\n",
       "      <td>358</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Camiseta</td>\n",
       "      <td>2</td>\n",
       "      <td>180</td>\n",
       "      <td>360</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Xadrez</td>\n",
       "      <td>1</td>\n",
       "      <td>368</td>\n",
       "      <td>368</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>Camisa Gola V Listrado</td>\n",
       "      <td>2</td>\n",
       "      <td>116</td>\n",
       "      <td>232</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34</td>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>Sapato Listrado</td>\n",
       "      <td>1</td>\n",
       "      <td>363</td>\n",
       "      <td>363</td>\n",
       "      <td>Iguatemi Esplanada</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Código Venda       Data  ID Loja                 Produto  Quantidade  \\\n",
       "0             1 2019-01-01        1          Sapato Estampa           1   \n",
       "1             1 2019-01-01        1                Camiseta           2   \n",
       "2             1 2019-01-01        1           Sapato Xadrez           1   \n",
       "3            21 2019-01-02        1  Camisa Gola V Listrado           2   \n",
       "4            34 2019-01-02        1         Sapato Listrado           1   \n",
       "\n",
       "   Valor Unitário  Valor Final                Loja  \n",
       "0             358          358  Iguatemi Esplanada  \n",
       "1             180          360  Iguatemi Esplanada  \n",
       "2             368          368  Iguatemi Esplanada  \n",
       "3             116          232  Iguatemi Esplanada  \n",
       "4             363          363  Iguatemi Esplanada  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Mescla as duas tabelas com base na coluna ID Loja\n",
    "df_vendas = df_vendas.merge(df_lojas, on='ID Loja')\n",
    "df_vendas.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Criando um Dicionário com os Registros de Vendas de cada Loja"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dicionário vazio para armazenar os dataframes relativos a cada loja\n",
    "lojas = dict()\n",
    "\n",
    "# Lista de lojas únicas\n",
    "lista_lojas = list(df_vendas['Loja'].unique())\n",
    "\n",
    "# Percorre a lista de lojas\n",
    "for loja in lista_lojas:\n",
    "    # Cria uma nova entrada no dicionário\n",
    "    # Cada chave é o nome de uma loja\n",
    "    # Cada valor é o dataframe com os dados de vendas de uma loja, incluindo todas as colunas\n",
    "    # Lógica do filtro: \n",
    "    # \"Pegue todas as linhas da tabela de vendas em que as células da coluna 'Loja' contenham o nome da loja desta iteração\"\n",
    "    lojas[loja] = df_vendas.loc[df_vendas['Loja'] == loja, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Obtendo a Data mais Recente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Obtém a maior data (data mais recente) da coluna de Data da tabela de vendas\n",
    "data_indicador = df_vendas['Data'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 3 - Salvar as Planilhas na Pasta de Backup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nesta etapa, vamos criar uma pasta para cada loja e salvar uma planilha com os seus respectivos registros de vendas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Muda de diretório\n",
    "os.chdir('Backup Arquivos Lojas')\n",
    "\n",
    "# Percorre o dicionário de lojas\n",
    "for loja in lojas:\n",
    "    # Verifica se não existe uma pasta com o nome da loja desta iteração\n",
    "    if not os.path.exists(loja):\n",
    "        # Cria uma pasta com o nome da loja\n",
    "        os.mkdir(loja)\n",
    "        \n",
    "    # Nome do arquivo\n",
    "    # Exemplo: 11-30-Iguatemi Esplanada.xlsx\n",
    "    nome_arquivo = '{}-{}-{}.xlsx'.format(data_indicador.month, data_indicador.day, loja)\n",
    "    \n",
    "    # Pasta de destino do arquivo\n",
    "    destino_arquivo = os.path.join(os.getcwd(), loja, nome_arquivo)\n",
    "    \n",
    "    # Exporta o dataframe para a pasta de destino\n",
    "    lojas[loja].to_excel(destino_arquivo)\n",
    "    \n",
    "# Volta para a pasta deste notebook\n",
    "os.chdir('..')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 4 - Calcular os Indicadores e Enviar os E-mails"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta função é usada para enviar os e-mails:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def enviar_email(remetente, senha, destinatario, assunto, mensagem, anexo):\n",
    "    try:\n",
    "        # Inicializa uma conexão com um servidor SMTP, criando uma instância de yagmail.SMTP\n",
    "        yag = yagmail.SMTP(user=remetente, password=senha)\n",
    "\n",
    "        # Envia o e-mail com os detalhes de nosso e-mail\n",
    "        yag.send(to=destinatario, subject=assunto, contents=mensagem, attachments=anexo)\n",
    "\n",
    "        # Exibe uma mensagem de que o e-mail foi enviado\n",
    "        print('E-mail enviado com sucesso.')\n",
    "    except:\n",
    "        print('Erro: o e-mail não foi enviado.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta função é usada para escrever a mensagem do e-mail:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def escrever_mensagem(nome_gerente, data_indicador, meta_faturamento_dia, faturamento_dia, meta_diversidade_produtos_dia, diversidade_produtos_dia, meta_ticket_medio_dia, ticket_medio_dia, faturamento_ano, meta_faturamento_ano, diversidade_produtos_ano, meta_diversidade_produtos_ano, ticket_medio_ano, meta_ticket_medio_ano):\n",
    "    return f'''\n",
    "    <p>Bom-dia, {nome_gerente}.</p>\n",
    "    <p>\n",
    "        O resultado de ontem <strong>({data_indicador.day}/{data_indicador.month})</strong> \n",
    "        da loja <strong>{loja}</strong> está descrito nas tabelas a seguir:\n",
    "    </p>\n",
    "    <table>\n",
    "        <thead>\n",
    "            <tr>\n",
    "                <th>Indicador</th>\n",
    "                <th>Meta do Dia</th>\n",
    "                <th>Resultado</th>\n",
    "                <th>Cenário</th>\n",
    "            </tr>\n",
    "        </thead>\n",
    "        <tbody>\n",
    "            <tr>\n",
    "                <td>Faturamento</td>\n",
    "                <td>{meta_faturamento_dia}</td>\n",
    "                <td>{faturamento_dia}</td>\n",
    "                <td>{'▲' if faturamento_dia >= meta_faturamento_dia else '▼'}</td>\n",
    "            </tr>\n",
    "            <tr>\n",
    "                <td>Diversidade de Produtos</td>\n",
    "                <td>{meta_diversidade_produtos_dia}</td>\n",
    "                <td>{diversidade_produtos_dia}</td>\n",
    "                <td>{'▲' if diversidade_produtos_dia >= meta_diversidade_produtos_dia else '▼'}</td>\n",
    "            </tr>\n",
    "            <tr>\n",
    "                <td>Ticket Médio</td>\n",
    "                <td>{meta_ticket_medio_dia}</td>\n",
    "                <td>{ticket_medio_dia}</td>\n",
    "                <td>{'▲' if ticket_medio_dia >= meta_ticket_medio_dia else '▼'}</td>\n",
    "            </tr>\n",
    "        </tbody>\n",
    "    </table>\n",
    "\n",
    "    </br>\n",
    "\n",
    "    <table>\n",
    "        <thead>\n",
    "            <tr>\n",
    "                <th>Indicador</th>\n",
    "                <th>Meta do Ano</th>\n",
    "                <th>Resultado</th>\n",
    "                <th>Cenário</th>\n",
    "            </tr>\n",
    "        </thead>\n",
    "        <tbody>\n",
    "            <tr>\n",
    "                <td>Faturamento</td>\n",
    "                <td>{meta_faturamento_ano}</td>\n",
    "                <td>{faturamento_ano}</td>\n",
    "                <td>{'▲' if faturamento_ano >= meta_faturamento_ano else '▼'}</td>\n",
    "            </tr>\n",
    "            <tr>\n",
    "                <td>Diversidade de Produtos</td>\n",
    "                <td>{meta_diversidade_produtos_ano}</td>\n",
    "                <td>{diversidade_produtos_ano}</td>\n",
    "                <td>{'▲' if diversidade_produtos_ano >= meta_diversidade_produtos_ano else '▼'}</td>\n",
    "            </tr>\n",
    "            <tr>\n",
    "                <td>Ticket Médio</td>\n",
    "                <td>{meta_ticket_medio_ano}</td>\n",
    "                <td>{ticket_medio_ano}</td>\n",
    "                <td>{'▲' if ticket_medio_ano >= meta_ticket_medio_ano else '▼'}</td>\n",
    "            </tr>\n",
    "        </tbody>\n",
    "    </table>\n",
    "    <p>Segue em anexo a planilha com todos os dados para mais detalhes.</p>\n",
    "    <p>Caso tenha alguma dúvida, saiba que estou a disposição.</p>\n",
    "    <p>Atenciosamente, Diego Moura Torres</p>\n",
    "    '''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estas funções são usadas para calcular os indicadores:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calcular_faturamento(tabela):\n",
    "    '''Calcula o faturamento com os registros da tabela passada como argumento'''\n",
    "    return tabela['Valor Final'].sum()\n",
    "\n",
    "\n",
    "def calcular_diversidade_produtos(tabela):\n",
    "    '''Calcula a diversidade de produtos com os registros da tabela passada como argumento'''\n",
    "    return len(tabela['Produto'].unique())\n",
    "\n",
    "\n",
    "def calcular_ticket_medio(tabela):\n",
    "    '''Calcula o ticket médio com base nos valores de venda'''\n",
    "    # Agrupa os registros de venda pelo Código da Venda, somando os valores de venda\n",
    "    df_vendas_agrupadas = tabela[['Código Venda', 'Valor Final']].groupby('Código Venda').sum()\n",
    "    \n",
    "    # Retorna a média dos valores de todas as vendas (ticket médio das vendas)\n",
    "    return df_vendas_agrupadas['Valor Final'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "Erro: o e-mail não foi enviado.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n",
      "E-mail enviado com sucesso.\n"
     ]
    }
   ],
   "source": [
    "# Percorre as entradas do dicionário de lojas\n",
    "for loja in lojas:\n",
    "    # Armazena o dataframe com os dados de vendas da loja desta iteração\n",
    "    df_vendas_loja = lojas[loja]\n",
    "    \n",
    "    # Armazena o dataframe com os dados de venda da data mais recente\n",
    "    df_vendas_loja_ultimo_dia = df_vendas_loja.loc[df_vendas_loja['Data'] == data_indicador, :]\n",
    "\n",
    "    # Calcula o faturamento acumulado da loja desta iteração\n",
    "    faturamento_ano = calcular_faturamento(df_vendas_loja)\n",
    "    \n",
    "    # Calcula o faturamento do dia da loja desta iteração\n",
    "    faturamento_dia = calcular_faturamento(df_vendas_loja_ultimo_dia)\n",
    "\n",
    "    # Calcula a diversidade de produtos para o ano\n",
    "    diversidade_produtos_ano = calcular_diversidade_produtos(df_vendas_loja)\n",
    "    \n",
    "    # Calcula a diversidade de produtos para o dia\n",
    "    diversidade_produtos_dia = calcular_diversidade_produtos(df_vendas_loja_ultimo_dia)\n",
    "\n",
    "    # Calcula o ticket médio para o ano\n",
    "    ticket_medio_ano = calcular_ticket_medio(df_vendas_loja)\n",
    "    \n",
    "    # Calcula o ticket médio para o dia\n",
    "    ticket_medio_dia = calcular_ticket_medio(df_vendas_loja_ultimo_dia)\n",
    "    \n",
    "    # Nome do gerente para o qual o e-mail será enviado\n",
    "    nome_gerente = df_emails.loc[df_emails['Loja'] == loja, 'Gerente'].values[0]\n",
    "\n",
    "    # E-mail do gerente\n",
    "    destinatario = df_emails.loc[df_emails['Loja'] == loja, 'E-mail'].values[0]\n",
    "\n",
    "    # Assunto do e-mail\n",
    "    assunto = 'OnePage Dia {}/{} - Loja {}'.format(data_indicador.day, data_indicador.month, loja)\n",
    "\n",
    "    # Mensagem do e-mail\n",
    "    mensagem = escrever_mensagem(\n",
    "        nome_gerente, \n",
    "        data_indicador, \n",
    "        meta_faturamento_dia, \n",
    "        faturamento_dia, \n",
    "        meta_diversidade_produtos_dia, \n",
    "        diversidade_produtos_dia, \n",
    "        meta_ticket_medio_dia, \n",
    "        ticket_medio_dia, \n",
    "        faturamento_ano, \n",
    "        meta_faturamento_ano, \n",
    "        diversidade_produtos_ano, \n",
    "        meta_diversidade_produtos_ano, \n",
    "        ticket_medio_ano, \n",
    "        meta_ticket_medio_ano\n",
    "    )\n",
    "\n",
    "    # Caminho do anexo do e-mail\n",
    "    anexo = os.path.join(\n",
    "        caminho_nb, \n",
    "        'Backup Arquivos Lojas', \n",
    "        loja,\n",
    "        f'{data_indicador.month}-{data_indicador.day}-{loja}.xlsx'\n",
    "    )\n",
    "\n",
    "    enviar_email(remetente, senha, destinatario, assunto, mensagem, anexo)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 5 - Criar ranking para Diretoria"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tabela com os valores de faturamento total de cada loja\n",
    "df_faturamento_ano = df_vendas[['Loja', 'Valor Final']].groupby('Loja').sum()\n",
    "\n",
    "# Ordena os valores de faturamento em ordem decrescente\n",
    "df_faturamento_ano.sort_values(by='Valor Final', ascending=False)\n",
    "\n",
    "# Muda de diretório\n",
    "os.chdir('Backup Arquivos Lojas')\n",
    "\n",
    "# Nome do arquivo a ser exportado para a pasta de backups\n",
    "nome_arquivo = '{}-{}-Ranking de Faturamento do Ano.xlsx'.format(data_indicador.month, data_indicador.day)\n",
    "\n",
    "# Exporta o dataframe\n",
    "df_faturamento_ano.to_excel(nome_arquivo)\n",
    "\n",
    "# Tabela com os dados de vendas do último dia (data mais recente)\n",
    "df_vendas_lojas_ultimo_dia = df_vendas.loc[df_vendas['Data'] == data_indicador, :]\n",
    "\n",
    "# Tabela com os valores de faturamento do dia de cada loja\n",
    "df_faturamento_dia = df_vendas_lojas_ultimo_dia[['Loja', 'Valor Final']].groupby('Loja').sum()\n",
    "\n",
    "# Ordena os valores de faturamento em ordem decrescente\n",
    "df_faturamento_dia.sort_values(by='Valor Final', ascending=False)\n",
    "\n",
    "# Nome do arquivo a ser exportado para a pasta de backups\n",
    "nome_arquivo = '{}-{}-Ranking de Faturamento do Dia.xlsx'.format(data_indicador.month, data_indicador.day)\n",
    "\n",
    "# Exporta o dataframe\n",
    "df_faturamento_dia.to_excel(nome_arquivo)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 6 - Enviar E-mail para Diretoria"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "E-mail enviado com sucesso.\n"
     ]
    }
   ],
   "source": [
    "# E-mail da diretoria\n",
    "destinatario = df_emails.loc[df_emails['Loja'] == 'Diretoria', 'E-mail'].values[0]\n",
    "\n",
    "# Assunto do e-mail\n",
    "assunto = 'Ranking de Lojas Dia {}/{}'.format(data_indicador.day, data_indicador.month)\n",
    "\n",
    "# Mensagem do e-mail\n",
    "mensagem = f'''Prezados, desejo a todos um ótimo dia.\n",
    "\n",
    "Resumidamente, hoje temos o seguinte cenário:\n",
    "Análise do Dia\n",
    "<table>\n",
    "    <thead>\n",
    "        <tr>\n",
    "            <th>Melhor Loja</th>\n",
    "            <th>Faturamento</th>\n",
    "        </tr>\n",
    "    </thead>\n",
    "    <tbody>\n",
    "        <tr>\n",
    "            <td>{df_faturamento_dia.index[0]}</td>\n",
    "            <td>R${df_faturamento_dia.iloc[0, 0]}</td>\n",
    "        </tr>\n",
    "    </tbody>\n",
    "</table>\n",
    "<table>\n",
    "    <thead>\n",
    "        <tr>\n",
    "            <th>Pior Loja</th>\n",
    "            <th>Faturamento</th>\n",
    "        </tr>\n",
    "    </thead>\n",
    "    <tbody>\n",
    "        <tr>\n",
    "            <td>{df_faturamento_dia.index[-1]}</td>\n",
    "            <td>R${df_faturamento_dia.iloc[-1, 0]}</td>\n",
    "        </tr>\n",
    "    </tbody>\n",
    "</table>\n",
    "Análise do Ano\n",
    "<table>\n",
    "    <thead>\n",
    "        <tr>\n",
    "            <th>Melhor Loja</th>\n",
    "            <th>Faturamento</th>\n",
    "        </tr>\n",
    "    </thead>\n",
    "    <tbody>\n",
    "        <tr>\n",
    "            <td>{df_faturamento_ano.index[0]}</td>\n",
    "            <td>R${df_faturamento_ano.iloc[0, 0]}</td>\n",
    "        </tr>\n",
    "    </tbody>\n",
    "</table>\n",
    "<table>\n",
    "    <thead>\n",
    "        <tr>\n",
    "            <th>Pior Loja</th>\n",
    "            <th>Faturamento</th>\n",
    "        </tr>\n",
    "    </thead>\n",
    "    <tbody>\n",
    "        <tr>\n",
    "            <td>{df_faturamento_ano.index[-1]}</td>\n",
    "            <td>R${df_faturamento_ano.iloc[-1, 0]}</td>\n",
    "        </tr>\n",
    "    </tbody>\n",
    "</table>\n",
    "\n",
    "Seguem em anexo as planilhas com os rankings de faturamento do dia e do ano para cada uma das lojas.\n",
    "\n",
    "Caso tenham alguma dúvida, estarei a disposição.\n",
    "\n",
    "Atenciosamente,\n",
    "Diego Moura Torres\n",
    "'''\n",
    "\n",
    "# Caminho do anexo do ranking do ano\n",
    "anexo_ranking_ano = os.path.join(\n",
    "    caminho_nb, \n",
    "    'Backup Arquivos Lojas', \n",
    "    f'{data_indicador.month}-{data_indicador.day}-Ranking de Faturamento do Ano.xlsx'\n",
    ")\n",
    "\n",
    "anexo_ranking_dia = os.path.join(\n",
    "    caminho_nb, \n",
    "    'Backup Arquivos Lojas', \n",
    "    f'{data_indicador.month}-{data_indicador.day}-Ranking de Faturamento do Dia.xlsx'\n",
    ")\n",
    "    \n",
    "# Envia o e-mail para a diretoria\n",
    "enviar_email(remetente, senha, destinatario, assunto, mensagem, anexo=[anexo_ranking_ano, anexo_ranking_dia])"
   ]
  }
 ],
 "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}