automacaoprocesso.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 1 - Importar Arquivos e Bibliotecas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import bibliotecas\n",
    "import pandas as pd\n",
    "import win32com.client as win32\n",
    "import pathlib\n",
    "\n",
    "\n",
    "#import base de dados\n",
    "emails = pd.read_excel(r'database\\Emails.xlsx')\n",
    "vendas = pd.read_excel(r'database\\Vendas.xlsx')\n",
    "lojas = pd.read_csv(r'database\\Lojas.csv', encoding='latin1', sep=';')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 2 - Definir Criar uma Tabela para cada Loja e Definir o dia do Indicador"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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",
       "      <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",
       "    <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100994</th>\n",
       "      <td>69846</td>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>8</td>\n",
       "      <td>Short Estampa</td>\n",
       "      <td>2</td>\n",
       "      <td>96</td>\n",
       "      <td>192</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100995</th>\n",
       "      <td>69846</td>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>8</td>\n",
       "      <td>Tênis Estampa</td>\n",
       "      <td>5</td>\n",
       "      <td>256</td>\n",
       "      <td>1280</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100996</th>\n",
       "      <td>69850</td>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>8</td>\n",
       "      <td>Calça Estampa</td>\n",
       "      <td>4</td>\n",
       "      <td>177</td>\n",
       "      <td>708</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100997</th>\n",
       "      <td>69972</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>8</td>\n",
       "      <td>Terno Liso</td>\n",
       "      <td>3</td>\n",
       "      <td>720</td>\n",
       "      <td>2160</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100998</th>\n",
       "      <td>69972</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>8</td>\n",
       "      <td>Sapato Estampa</td>\n",
       "      <td>5</td>\n",
       "      <td>358</td>\n",
       "      <td>1790</td>\n",
       "      <td>Salvador Shopping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100999 rows × 8 columns</p>\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",
       "100994         69846 2019-12-25        8           Short Estampa           2   \n",
       "100995         69846 2019-12-25        8           Tênis Estampa           5   \n",
       "100996         69850 2019-12-25        8           Calça Estampa           4   \n",
       "100997         69972 2019-12-26        8              Terno Liso           3   \n",
       "100998         69972 2019-12-26        8          Sapato Estampa           5   \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  \n",
       "...                ...          ...                 ...  \n",
       "100994              96          192   Salvador Shopping  \n",
       "100995             256         1280   Salvador Shopping  \n",
       "100996             177          708   Salvador Shopping  \n",
       "100997             720         2160   Salvador Shopping  \n",
       "100998             358         1790   Salvador Shopping  \n",
       "\n",
       "[100999 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#incluir nome da loja em vendas\n",
    "vendas = vendas.merge(lojas, on='ID Loja')\n",
    "display(vendas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "dicionario_lojas = {}\n",
    "for loja in lojas['Loja']:\n",
    "    dicionario_lojas[loja] = vendas.loc[vendas['Loja']==loja, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-26 00:00:00\n"
     ]
    }
   ],
   "source": [
    "dia_indicador = vendas['Data'].max()\n",
    "print(dia_indicador)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 3 - Salvar a planilha na pasta de backup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "caminho_backup = pathlib.Path(r'arquivos_lojas')\n",
    "arquivos_backup = caminho_backup.iterdir()\n",
    "\n",
    "lista_nomes_backup = [arquivo.name for arquivo in arquivos_backup]\n",
    "\n",
    "for loja in dicionario_lojas:\n",
    "    if loja not in lista_nomes_backup:\n",
    "        nova_pasta = caminho_backup / loja\n",
    "        nova_pasta.mkdir()\n",
    "        \n",
    "    #salvar dentro da pasta    \n",
    "    nome_arquivo = f'{dia_indicador.month}_{dia_indicador.day}_{loja}.xlsx'\n",
    "    local_arquivo = caminho_backup / loja / nome_arquivo\n",
    "    dicionario_lojas[loja].to_excel(local_arquivo)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 4 - Definição de metas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "meta_fatu_dia = 1000\n",
    "meta_fatu_ano = 1650000\n",
    "meta_qtdeprodutos_dia = 4\n",
    "meta_qtdeprodutos_ano = 120\n",
    "meta_ticketmedio_dia = 500\n",
    "meta_ticketmedio_ano = 500"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 5 - Automatizar todas as lojas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "for loja in dicionario_lojas:\n",
    "\n",
    "    vendas_loja = dicionario_lojas[loja]\n",
    "\n",
    "    vendas_loja_dia = vendas_loja.loc[vendas_loja['Data']==dia_indicador , :]\n",
    "\n",
    "    #faturamento\n",
    "    fatu_ano = vendas_loja['Valor Final'].sum()\n",
    "\n",
    "    fatu_dia = vendas_loja_dia['Valor Final'].sum()\n",
    "\n",
    "    #diversidade de produtos\n",
    "    qtde_produtos_ano = len(vendas_loja['Produto'].unique())\n",
    "    qtde_produtos_dia = len(vendas_loja_dia['Produto'].unique())\n",
    "\n",
    "    #ticket médio\n",
    "    valor_venda = vendas_loja.groupby('Código Venda').sum()\n",
    "    tick_ano = valor_venda['Valor Final'].mean()\n",
    "\n",
    "    valor_venda_dia = vendas_loja_dia.groupby('Código Venda').sum()\n",
    "    tick_dia = valor_venda_dia['Valor Final'].mean()\n",
    "    \n",
    "    #Enviar o e-mail\n",
    "    outlook = win32.Dispatch('outlook.application')\n",
    "    nome = emails.loc[emails['Loja']==loja, 'Gerente'].values[0]\n",
    "    mail = outlook.CreateItem(0)\n",
    "    mail.To = emails.loc[emails['Loja']==loja, 'E-mail'].values[0]\n",
    "    mail.Subject = f'OnePage Dia {dia_indicador.day}/{dia_indicador.month} - Loja {loja}'\n",
    "    \n",
    "    if fatu_dia >= meta_fatu_dia:\n",
    "        cor_fat_dia = 'green'\n",
    "    else:\n",
    "        cor_fat_dia = 'red'\n",
    "    if fatu_ano >= meta_fatu_ano:\n",
    "        cor_fat_ano = 'green'\n",
    "    else:\n",
    "        cor_fat_ano = 'red'\n",
    "\n",
    "\n",
    "    if qtde_produtos_dia >= meta_qtdeprodutos_dia:\n",
    "        cor_qtde_dia = 'green'\n",
    "    else:\n",
    "        cor_qtde_dia = 'red'\n",
    "    if qtde_produtos_ano >= meta_qtdeprodutos_ano:\n",
    "        cor_qtde_ano = 'green'\n",
    "    else:\n",
    "        cor_qtde_ano = 'red'\n",
    "\n",
    "\n",
    "    if tick_dia >= meta_ticketmedio_dia:\n",
    "        cor_tick_dia = 'green'\n",
    "    else:\n",
    "        cor_tick_dia = 'red'\n",
    "    if tick_ano >= meta_ticketmedio_ano:\n",
    "        cor_tick_ano = 'green'\n",
    "    else:\n",
    "        cor_tick_ano = 'red'\n",
    "    \n",
    "\n",
    "    mail.HTMLBody = f'''\n",
    "    <p>Bom dia, {nome}.</p>\n",
    "\n",
    "    <p>O resultado de ontem <strong>({dia_indicador.day}/{dia_indicador.month})</strong> da <strong>Loja {loja}</strong> foi:</p>\n",
    "\n",
    "    <table>\n",
    "      <tr>\n",
    "        <th>Indicador</th>\n",
    "        <th>Valor Dia</th>\n",
    "        <th>Meta Dia</th>\n",
    "        <th>Cenário Dia</th>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Faturamento</td>\n",
    "        <td style=\"text-align: center\">R${fatu_dia:,.2f}</td>\n",
    "        <td style=\"text-align: center\">R${meta_fatu_dia:,.2f}</td>\n",
    "        <td style=\"text-align: center\"><font color=\"{cor_fat_dia}\">◙</font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Diversidade de Produto</td>\n",
    "        <td style=\"text-align: center\">{qtde_produtos_dia}</td>\n",
    "        <td style=\"text-align: center\">{meta_qtdeprodutos_dia}</td>\n",
    "        <td style=\"text-align: center\"><font color=\"{cor_qtde_dia}\">◙</font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Ticket Médio</td>\n",
    "        <td style=\"text-align: center\">R${tick_dia:,.2f}</td>\n",
    "        <td style=\"text-align: center\">R${meta_ticketmedio_dia:,.2f}</td>\n",
    "        <td style=\"text-align: center\"><font color=\"{cor_tick_dia}\">◙</font></td>\n",
    "      </tr>\n",
    "    </table>\n",
    "    <br>\n",
    "    <table>\n",
    "      <tr>\n",
    "        <th>Indicador</th>\n",
    "        <th>Valor Ano</th>\n",
    "        <th>Meta Ano</th>\n",
    "        <th>Cenário Ano</th>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Faturamento</td>\n",
    "        <td style=\"text-align: center\">R${fatu_ano:,.2f}</td>\n",
    "        <td style=\"text-align: center\">R${meta_fatu_ano:,.2f}</td>\n",
    "        <td style=\"text-align: center\"><font color=\"{cor_fat_ano}\">◙</font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Diversidade de Produto</td>\n",
    "        <td style=\"text-align: center\">{qtde_produtos_ano}</td>\n",
    "        <td style=\"text-align: center\">{meta_qtdeprodutos_ano}</td>\n",
    "        <td style=\"text-align: center\"><font color=\"{cor_qtde_ano}\">◙</font></td>\n",
    "      </tr>\n",
    "      <tr>\n",
    "        <td>Ticket Médio</td>\n",
    "        <td style=\"text-align: center\">R${tick_ano:,.2f}</td>\n",
    "        <td style=\"text-align: center\">R${meta_ticketmedio_ano:,.2f}</td>\n",
    "        <td style=\"text-align: center\"><font color=\"{cor_tick_ano}\">◙</font></td>\n",
    "      </tr>\n",
    "    </table>\n",
    "\n",
    "    <p>Segue em anexo a planilha com todos os dados para ánalise.</p>\n",
    "    <p>Qualquer dúvida estou à disposição.</p>\n",
    "    <p>Att., Giannini</p>\n",
    "    '''\n",
    "\n",
    "    # Anexos (pode colocar quantos quiser):\n",
    "    attachment  = pathlib.Path.cwd() / caminho_backup / loja / f'{dia_indicador.month}_{dia_indicador.day}_{loja}.xlsx'\n",
    "    mail.Attachments.Add(str(attachment))\n",
    "    mail.Send()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 6 - Criar ranking para diretoria"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Valor Final</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Loja</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Iguatemi Campinas</th>\n",
       "      <td>1762419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Vila Velha</th>\n",
       "      <td>1731167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bourbon Shopping SP</th>\n",
       "      <td>1726110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rio Mar Recife</th>\n",
       "      <td>1722766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping SP Market</th>\n",
       "      <td>1721763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Palladium Shopping Curitiba</th>\n",
       "      <td>1721120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Norte Shopping</th>\n",
       "      <td>1711968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ribeirão Shopping</th>\n",
       "      <td>1707122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iguatemi Esplanada</th>\n",
       "      <td>1699681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rio Mar Shopping Fortaleza</th>\n",
       "      <td>1698430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Center Leste Aricanduva</th>\n",
       "      <td>1682870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Novo Shopping Ribeirão Preto</th>\n",
       "      <td>1678225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Iguatemi Fortaleza</th>\n",
       "      <td>1674824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center Shopping Uberlândia</th>\n",
       "      <td>1668921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping União de Osasco</th>\n",
       "      <td>1663770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Barra</th>\n",
       "      <td>1653103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passei das Águas Shopping</th>\n",
       "      <td>1649014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Eldorado</th>\n",
       "      <td>1648111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Salvador Shopping</th>\n",
       "      <td>1646739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Center Interlagos</th>\n",
       "      <td>1640486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Ibirapuera</th>\n",
       "      <td>1635060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Parque Dom Pedro Shopping</th>\n",
       "      <td>1631630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Recife</th>\n",
       "      <td>1624879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Midway Mall</th>\n",
       "      <td>1590441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Morumbi</th>\n",
       "      <td>1586444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Valor Final\n",
       "Loja                                         \n",
       "Iguatemi Campinas                     1762419\n",
       "Shopping Vila Velha                   1731167\n",
       "Bourbon Shopping SP                   1726110\n",
       "Rio Mar Recife                        1722766\n",
       "Shopping SP Market                    1721763\n",
       "Palladium Shopping Curitiba           1721120\n",
       "Norte Shopping                        1711968\n",
       "Ribeirão Shopping                     1707122\n",
       "Iguatemi Esplanada                    1699681\n",
       "Rio Mar Shopping Fortaleza            1698430\n",
       "Shopping Center Leste Aricanduva      1682870\n",
       "Novo Shopping Ribeirão Preto          1678225\n",
       "Shopping Iguatemi Fortaleza           1674824\n",
       "Center Shopping Uberlândia            1668921\n",
       "Shopping União de Osasco              1663770\n",
       "Shopping Barra                        1653103\n",
       "Passei das Águas Shopping             1649014\n",
       "Shopping Eldorado                     1648111\n",
       "Salvador Shopping                     1646739\n",
       "Shopping Center Interlagos            1640486\n",
       "Shopping Ibirapuera                   1635060\n",
       "Parque Dom Pedro Shopping             1631630\n",
       "Shopping Recife                       1624879\n",
       "Shopping Midway Mall                  1590441\n",
       "Shopping Morumbi                      1586444"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "faturamento_lojas = vendas.groupby('Loja')[['Loja','Valor Final' ]].sum()\n",
    "faturamento_lojas_ano = faturamento_lojas.sort_values(by='Valor Final', ascending=False)\n",
    "display(faturamento_lojas_ano)\n",
    "   \n",
    "nome_arquivo = '{}_{}_Ranking Anual.xlsx'.format(dia_indicador.month, dia_indicador.day)\n",
    "faturamento_lojas_ano.to_excel(r'arquivos_lojas\\{}'.format(nome_arquivo))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Valor Final</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Loja</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Salvador Shopping</th>\n",
       "      <td>3950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Novo Shopping Ribeirão Preto</th>\n",
       "      <td>3400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center Shopping Uberlândia</th>\n",
       "      <td>2651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Eldorado</th>\n",
       "      <td>2391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Center Interlagos</th>\n",
       "      <td>1582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Recife</th>\n",
       "      <td>1366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Norte Shopping</th>\n",
       "      <td>1259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping União de Osasco</th>\n",
       "      <td>1207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Vila Velha</th>\n",
       "      <td>937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bourbon Shopping SP</th>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ribeirão Shopping</th>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Center Leste Aricanduva</th>\n",
       "      <td>485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Palladium Shopping Curitiba</th>\n",
       "      <td>381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rio Mar Shopping Fortaleza</th>\n",
       "      <td>368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Midway Mall</th>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shopping Ibirapuera</th>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Valor Final\n",
       "Loja                                         \n",
       "Salvador Shopping                        3950\n",
       "Novo Shopping Ribeirão Preto             3400\n",
       "Center Shopping Uberlândia               2651\n",
       "Shopping Eldorado                        2391\n",
       "Shopping Center Interlagos               1582\n",
       "Shopping Recife                          1366\n",
       "Norte Shopping                           1259\n",
       "Shopping União de Osasco                 1207\n",
       "Shopping Vila Velha                       937\n",
       "Bourbon Shopping SP                       676\n",
       "Ribeirão Shopping                         650\n",
       "Shopping Center Leste Aricanduva          485\n",
       "Palladium Shopping Curitiba               381\n",
       "Rio Mar Shopping Fortaleza                368\n",
       "Shopping Midway Mall                      170\n",
       "Shopping Ibirapuera                       118"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "vendas_dia = vendas.loc[vendas['Data']==dia_indicador, :]\n",
    "faturamento_lojas_dia = vendas_dia.groupby('Loja')[['Loja', 'Valor Final']].sum()\n",
    "faturamento_lojas_dia = faturamento_lojas_dia.sort_values(by='Valor Final', ascending=False)\n",
    "display(faturamento_lojas_dia)\n",
    "\n",
    "nome_arquivo = '{}_{}_Ranking Dia.xlsx'.format(dia_indicador.month, dia_indicador.day)\n",
    "faturamento_lojas_dia.to_excel(r'arquivos_lojas\\{}'.format(nome_arquivo))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Passo 7 - Enviar e-mail para diretoria"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Email da Diretoria enviado\n"
     ]
    }
   ],
   "source": [
    "outlook = win32.Dispatch('outlook.application')\n",
    "\n",
    "mail = outlook.CreateItem(0)\n",
    "mail.To = emails.loc[emails['Loja']=='Diretoria', 'E-mail'].values[0]\n",
    "mail.Subject = f'Ranking Dia {dia_indicador.day}/{dia_indicador.month}'\n",
    "mail.Body = f'''\n",
    "Prezados, bom dia.\n",
    "\n",
    "Melhor loja do Dia em faturamento: Loja {faturamento_lojas_dia.index[0]} com Faturamento R${faturamento_lojas_dia.iloc[0, 0]:,.2f}\n",
    "Pior loja do Dia em faturamento: Loja {faturamento_lojas_dia.index[-1]} com Faturamento R${faturamento_lojas_dia.iloc[-1, 0]:,.2f}\n",
    "\n",
    "Melhor loja do Ano em faturamento: Loja {faturamento_lojas_ano.index[0]} com Faturamento R${faturamento_lojas_ano.iloc[0, 0]:,.2f}\n",
    "Pior loja do Ano em faturamento: Loja {faturamento_lojas_ano.index[0]} com Faturamento R${faturamento_lojas_ano.iloc[-1, 0]:,.2f}\n",
    "\n",
    "Segue em anexo os rankings do ano e do dia de todas as lojas.\n",
    "\n",
    "Qualquer dúvida estou à disposição.\n",
    "\n",
    "Att., Giannini.\n",
    "'''\n",
    "\n",
    "attachment  = pathlib.Path.cwd() / caminho_backup / f'{dia_indicador.month}_{dia_indicador.day}_Ranking Anual.xlsx'\n",
    "mail.Attachments.Add(str(attachment))\n",
    "attachment  = pathlib.Path.cwd() / caminho_backup / f'{dia_indicador.month}_{dia_indicador.day}_Ranking Dia.xlsx'\n",
    "mail.Attachments.Add(str(attachment))\n",
    "\n",
    "mail.Send()\n",
    "print('Email da Diretoria enviado')"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}