Modificação da Planilha baixarda para ficar 1 para 1

app/controllers/operacoes_dashboard_controller.rb

@@ -74,13 +74,12 @@ class OperacoesDashboardController < ApplicationController
     end
 
     planilha = Analytics::PlanilhaEntregas.new(tabela)
-    linhas   = planilha.linhas
+    if planilha.linhas.empty?
-    if linhas.empty?
       return redirect_to(operacoes_planilha_path(operacao: tabela),
                          alert: "A operação #{planilha.label} não tem registros.")
     end
 
-    send_data Analytics::PlanilhaEntregasXlsx.new(linhas).gerar,
+    send_data Analytics::PlanilhaEntregasXlsx.new(planilha).gerar,
               filename: "Entregas #{planilha.label} #{Date.current.strftime('%m.%Y')}.xlsx",
               type: XLSX_MIME,
               disposition: 'attachment'

app/services/analytics/planilha_entregas.rb

@@ -1,11 +1,14 @@
 # app/services/analytics/planilha_entregas.rb
 #
-# Monta as linhas da "planilha Entregas" entregue ao cliente (modelo
+# Monta os dados da "planilha Entregas" entregue ao cliente (modelo
-# "Entregas SUDESTE MM.AAAA_FINAL.xlsx", aba ENTREGAS): as colunas A..V vêm da
+# "Entregas SUDESTE MM.AAAA_FINAL.xlsx"), com as 3 abas do modelo:
-# própria tabela da operação (gade_entregas_* — espelho da planilha original) e
+#
-# as colunas de resultado W..Z (STATUS/ENTREGA/DATA OCORRÊNCIA/OCORRÊNCIA) são
+#   RESUMO      — totais/performance e quadro por coordenadoria (EMAD e UBS),
-# preenchidas com o ÚLTIMO status de cada NF no rastreio — o preenchimento que
+#                 com os motivos de "Não Entregue" agregados do rastreio.
-# hoje é feito manualmente.
+#   ENTREGAS    — planilha do cliente (A..V da tabela gade_entregas_*) com as
+#                 colunas de resultado W..Z preenchidas pelo rastreio (o
+#                 preenchimento que hoje é feito manualmente).
+#   SimpliRoute — dump cru do rastreio (todas as visitas das NFs da operação).
 #
 # SEGURANÇA: tabela passa pela whitelist (Operacao.sanitizar) + quote_table_name;
 # colunas são whitelist fixa e as ausentes viram NULL (mesmo padrão do
@@ -39,9 +42,68 @@ module Analytics
       'nota_fiscal'              => 'NOTA FISCAL'
     }.freeze
 
-    # Cabeçalho completo A..Z (gade + resultado do rastreio).
+    # Cabeçalho completo A..Z da aba ENTREGAS (gade + resultado do rastreio).
     CABECALHO = (COLUNAS_GADE.values + ['STATUS', 'ENTREGA', 'DATA OCORRÊNCIA', 'OCORRÊNCIA']).freeze
 
+    # Aba SimpliRoute: cabeçalho exato do modelo -> colunas candidatas na tabela
+    # de rastreio (a primeira que existir é usada; nenhuma existente = vazia).
+    RASTREIO_COLUNAS = {
+      'Tracking ID'                        => %w[tracking_id],
+      'Referência ID'                      => %w[reference_id],
+      'Data prevista'                      => %w[planned_date],
+      'Motorista'                          => %w[driver],
+      'Co-pilotos'                         => %w[copilots co_pilotos copilotos],
+      'Veículo'                            => %w[vehicle],
+      'Título'                             => %w[title titulo],
+      'Endereço'                           => %w[address],
+      'ETA'                                => %w[eta],
+      'ETD'                                => %w[etd],
+      'Checkin'                            => %w[checkin],
+      'Checkout'                           => %w[checkout],
+      'Responsável'                        => %w[receiver responsavel responsible],
+      'Tempo de serviço estimado'          => %w[estimated_service_time tempo_de_servico_estimado duration],
+      'Tempo de serviço realme'            => %w[real_service_time tempo_de_servico_real],
+      'Antecipação'                        => %w[early antecipacao anticipation],
+      'Atraso'                             => %w[delay atraso],
+      'Latitude'                           => %w[latitude],
+      'Longitude'                          => %w[longitude],
+      'Checkout latitude'                  => %w[checkout_latitude],
+      'Checkout longitude'                 => %w[checkout_longitude],
+      'Load'                               => %w[load load_1 carga],
+      'Load 2'                             => %w[load_2],
+      'Load 3'                             => %w[load_3],
+      'Load 4'                             => %w[load_4],
+      'Estado'                             => %w[status],
+      'Comentários'                        => %w[checkout_comment comments comentarios comment],
+      'Observações'                        => %w[observation observacoes],
+      'Janela de horário Inicial 1'        => %w[window_start window_start_1 janela_inicial_1],
+      'Janela de horário Final 1'          => %w[window_end window_end_1 janela_final_1],
+      'Janela de horário Inicial 2'        => %w[window_start_2 janela_inicial_2],
+      'Janela de horário Final 2'          => %w[window_end_2 janela_final_2],
+      'Habilidades necessárias'            => %w[skills_required habilidades_necessarias],
+      'Habilidades adicionais'             => %w[skills_optional habilidades_adicionais],
+      'Notas'                              => %w[notes notas],
+      'Nome de contato'                    => %w[contact_name],
+      'Telefone de contato'                => %w[contact_phone telefone_de_contato],
+      'Correio eletrônico de contato'      => %w[contact_email correio_eletronico_de_contato],
+      'ID da rota'                         => %w[route_id id_da_rota],
+      'ID da conta'                        => %w[account_id],
+      'Nome da conta'                      => %w[account_name nome_da_conta],
+      'Nota Fiscal'                        => %w[nota_fiscal],
+      'Termo de Recebimento'               => %w[termo_de_recebimento],
+      'Foto da Fachada'                    => %w[foto_da_fachada],
+      'Relatório de visita'                => %w[relatorio_de_visita visit_report],
+      'Número de Série da Base (Aparelho)' => %w[numero_de_serie_da_base_aparelho num_serie_base numero_serie_base]
+    }.freeze
+
+    # Quadro do RESUMO — linhas fixas do modelo.
+    COORDENADORIAS = ['CRS Sudeste', 'CRS Sul', 'CRS Leste', 'CRS Norte', 'CRS Oeste', 'CRS Centro'].freeze
+    MOTIVOS        = ['Óbito', 'Responsável Ausente', 'Endereço não localizado', 'Recusa',
+                      'Paciente não reside no local', 'Outros'].freeze
+    GRUPOS         = %w[EMAD UBS].freeze
+
+    MESES_PT = %w[JANEIRO FEVEREIRO MARÇO ABRIL MAIO JUNHO JULHO AGOSTO SETEMBRO OUTUBRO NOVEMBRO DEZEMBRO].freeze
+
     attr_reader :tabela
 
     def initialize(tabela)
@@ -53,29 +115,88 @@ module Analytics
       Operacao.label(@tabela)
     end
 
+    # ── Aba ENTREGAS ─────────────────────────────────────────────
     # Array de hashes: chaves de COLUNAS_GADE + rastreio_status/checkout/observation.
     # ORDER BY g.ctid preserva a ordem física de importação — a mesma ordem da
     # planilha original do cliente (tabelas gade são read-only após o import).
     def linhas
+      @linhas ||= begin
+        gade = conn.quote_table_name(@tabela)
+        sql = <<~SQL
+          WITH ultimo AS (
+            SELECT *,
+                   ROW_NUMBER() OVER (PARTITION BY reference_id ORDER BY checkout DESC NULLS LAST) AS rn
+            FROM #{conn.quote_table_name(Entrega.table_name)}
+            WHERE reference_id IS NOT NULL
+          )
+          SELECT #{selects_gade},
+                 r.status      AS rastreio_status,
+                 r.checkout    AS rastreio_checkout,
+                 r.observation AS rastreio_observation
+          FROM #{gade} g
+          LEFT JOIN ultimo r ON r.rn = 1 AND r.reference_id::text = g.nota_fiscal
+          ORDER BY g.ctid
+        SQL
+        conn.select_all(sql).to_a
+      end
+    end
+
+    # ── Aba SimpliRoute ──────────────────────────────────────────
+    # Todas as visitas do rastreio para as NFs da operação (inclui repetidas,
+    # como no dump original do SimpliRoute).
+    def linhas_rastreio
       gade = conn.quote_table_name(@tabela)
       sql = <<~SQL
-        WITH ultimo AS (
+        SELECT r.*
-          SELECT *,
+        FROM #{conn.quote_table_name(Entrega.table_name)} r
-                 ROW_NUMBER() OVER (PARTITION BY reference_id ORDER BY checkout DESC NULLS LAST) AS rn
+        WHERE r.reference_id::text IN (SELECT nota_fiscal FROM #{gade} WHERE nota_fiscal IS NOT NULL)
-          FROM #{conn.quote_table_name(Entrega.table_name)}
+        ORDER BY r.reference_id, r.checkout NULLS LAST
-          WHERE reference_id IS NOT NULL
-        )
-        SELECT #{selects_gade},
-               r.status      AS rastreio_status,
-               r.checkout    AS rastreio_checkout,
-               r.observation AS rastreio_observation
-        FROM #{gade} g
-        LEFT JOIN ultimo r ON r.rn = 1 AND r.reference_id::text = g.nota_fiscal
-        ORDER BY g.ctid
       SQL
       conn.select_all(sql).to_a
     end
 
+    # { cabeçalho do modelo => coluna real do rastreio (ou nil) }.
+    def colunas_rastreio
+      existentes = conn.columns(Entrega.table_name).map(&:name)
+      RASTREIO_COLUNAS.transform_values { |cands| cands.find { |c| existentes.include?(c) } }
+    end
+
+    # ── Aba RESUMO ───────────────────────────────────────────────
+    # Agrega as linhas da ENTREGAS por grupo (EMAD/UBS) e coordenadoria:
+    # previsão/entregue/não entregue/pendente + motivos de "Não Entregue".
+    def resumo
+      grupos = GRUPOS.index_with { Hash.new { |h, k| h[k] = novo_bloco } }
+
+      linhas.each do |l|
+        bloco = grupos[grupo_de(l)][coordenadoria_de(l)]
+        bloco[:previsao] += 1
+        status = l['rastreio_status'].to_s
+        if status == 'completed'
+          bloco[:entregue] += 1
+        elsif Entrega::STATUS_FALHA.include?(status)
+          bloco[:nao_entregue] += 1
+          bloco[:motivos][motivo_de(l['rastreio_observation'])] += 1
+        else
+          bloco[:pendente] += 1
+        end
+      end
+
+      total_prev = linhas.size
+      total_real = grupos.values.sum { |coords| coords.values.sum { |b| b[:entregue] } }
+      {
+        mes:             "#{MESES_PT[Date.current.month - 1]}/#{Date.current.year}",
+        total_previsto:  total_prev,
+        total_realizado: total_real,
+        performance:     (total_prev.zero? ? nil : total_real.to_f / total_prev),
+        grupos:          grupos
+      }
+    end
+
+    # Bloco zerado do quadro RESUMO (também usado pelo gerador xlsx).
+    def novo_bloco
+      { previsao: 0, entregue: 0, nao_entregue: 0, pendente: 0, motivos: Hash.new(0) }
+    end
+
     private
 
     def conn
@@ -89,5 +210,33 @@ module Analytics
         existentes.include?(c) ? "g.#{c} AS #{c}" : "CAST(NULL AS text) AS #{c}"
       end.join(', ')
     end
+
+    # Grupo do quadro (EMAD/UBS): coluna "operacao" da linha; sem ela, deduz do
+    # nome/label da operação. Default UBS (grupo mais comum no modelo).
+    def grupo_de(linha)
+      texto = I18n.transliterate("#{linha['operacao']} #{@tabela} #{label}").upcase
+      texto.include?('EMAD') ? 'EMAD' : 'UBS'
+    end
+
+    # Casa a coordenadoria da linha com as fixas do modelo (sem acento/caixa);
+    # valores fora da lista entram como estão (viram linha extra no quadro).
+    def coordenadoria_de(linha)
+      bruto = linha['coordenadoria'].to_s.strip
+      return COORDENADORIAS.first if bruto.empty?   # sem coluna: agrega na 1ª (Sudeste)
+
+      chave = I18n.transliterate(bruto).upcase
+      COORDENADORIAS.find { |c| I18n.transliterate(c).upcase == chave } || bruto
+    end
+
+    # Classifica a observação do insucesso nos motivos fixos do RESUMO.
+    def motivo_de(observacao)
+      o = I18n.transliterate(observacao.to_s).downcase
+      return 'Óbito'                        if o.include?('obito')
+      return 'Responsável Ausente'          if o.include?('ausente')
+      return 'Endereço não localizado'      if o.include?('localizado') || o.include?('endereco')
+      return 'Recusa'                       if o.include?('recus')
+      return 'Paciente não reside no local' if o.include?('reside')
+      'Outros'
+    end
   end
 end

app/services/analytics/planilha_entregas_xlsx.rb

@@ -1,37 +1,108 @@
 # app/services/analytics/planilha_entregas_xlsx.rb
 #
-# Gera o binário .xlsx da planilha Entregas do cliente (aba ENTREGAS) a partir
+# Gera o binário .xlsx da planilha Entregas do cliente com as 3 abas do modelo
-# das linhas montadas por Analytics::PlanilhaEntregas — layout A..Z idêntico ao
+# "Entregas SUDESTE MM.AAAA_FINAL.xlsx": RESUMO, ENTREGAS e SimpliRoute.
-# modelo "Entregas SUDESTE MM.AAAA_FINAL.xlsx".
+# Os dados vêm de Analytics::PlanilhaEntregas. (Os gráficos embutidos do modelo
+# não são replicados — apenas os quadros de dados.)
 require 'caxlsx'
 
 module Analytics
   class PlanilhaEntregasXlsx
-    def initialize(linhas)
+    def initialize(planilha)
-      @linhas = linhas
+      @planilha = planilha
     end
 
     # String binária do .xlsx.
     def gerar
       pkg = Axlsx::Package.new
-      pkg.workbook.add_worksheet(name: 'ENTREGAS') do |sheet|
+      wb  = pkg.workbook
-        negrito = sheet.workbook.styles.add_style(b: true)
+      @negrito = wb.styles.add_style(b: true)
-        sheet.add_row(PlanilhaEntregas::CABECALHO, style: negrito)
+      @titulo  = wb.styles.add_style(b: true, sz: 12)
-        @linhas.each do |l|
+      @pct     = wb.styles.add_style(format_code: '0.0%')
-          valores, tipos = linha(l)
+
-          sheet.add_row(valores, types: tipos)
+      aba_resumo(wb)
-        end
+      aba_entregas(wb)
-        sheet.auto_filter = "A1:Z#{@linhas.size + 1}"
+      aba_simpliroute(wb)
-      end
+
       pkg.to_stream.read
     end
 
     private
 
+    # ── RESUMO ───────────────────────────────────────────────────
+    # Mesma disposição do modelo: mês, totais/performance e um quadro por grupo
+    # (EMAD e UBS) com as coordenadorias fixas + motivos de "Não Entregue".
+    # ("Cordenadoria" sem o primeiro "o" é verbatim do modelo.)
+    def aba_resumo(wb)
+      resumo = @planilha.resumo
+      wb.add_worksheet(name: 'RESUMO') do |s|
+        s.add_row []
+        s.add_row ['', resumo[:mes]], style: @titulo
+        s.add_row []
+        s.add_row ['', 'Total Previsto:',  resumo[:total_previsto]]
+        s.add_row ['', 'Total Realizado:', resumo[:total_realizado]]
+        s.add_row ['', 'Performance:',     resumo[:performance] || ''],
+                  style: [nil, nil, (resumo[:performance] ? @pct : nil)]
+
+        PlanilhaEntregas::GRUPOS.each { |grupo| quadro_grupo(s, grupo, resumo[:grupos][grupo]) }
+      end
+    end
+
+    def quadro_grupo(s, grupo, coords)
+      s.add_row []
+      s.add_row ['', grupo], style: @titulo
+      s.add_row ['', '', '', '', '', 'Informações', '', '', 'Motivos de "Não Entregue"'], style: @negrito
+      s.add_row ['', 'Cordenadoria', 'Previsão', 'Entregue', 'Não Entregue',
+                 'Status das Entregas', 'Conferência Documentos', '',
+                 *PlanilhaEntregas::MOTIVOS], style: @negrito
+
+      # Coordenadorias fixas do modelo + eventuais extras vindas dos dados.
+      nomes = PlanilhaEntregas::COORDENADORIAS + (coords.keys - PlanilhaEntregas::COORDENADORIAS)
+      total = @planilha.novo_bloco
+
+      nomes.each do |nome|
+        b = coords[nome] || @planilha.novo_bloco
+        total[:previsao]     += b[:previsao]
+        total[:entregue]     += b[:entregue]
+        total[:nao_entregue] += b[:nao_entregue]
+        total[:pendente]     += b[:pendente]
+        b[:motivos].each { |m, q| total[:motivos][m] += q }
+
+        s.add_row ['', nome, b[:previsao], b[:entregue], b[:nao_entregue],
+                   (b[:pendente].zero? ? 'Concluído' : 'Em andamento'), 'A iniciar', '',
+                   *PlanilhaEntregas::MOTIVOS.map { |m| b[:motivos][m] }]
+      end
+
+      s.add_row ['', 'Total', total[:previsao], total[:entregue], total[:nao_entregue], '', '', '',
+                 *PlanilhaEntregas::MOTIVOS.map { |m| total[:motivos][m] }], style: @negrito
+
+      if total[:previsao].positive?
+        s.add_row ['', 'Performance %', 1,
+                   total[:entregue].to_f / total[:previsao],
+                   total[:nao_entregue].to_f / total[:previsao]],
+                  style: [nil, @negrito, @pct, @pct, @pct]
+      else
+        s.add_row ['', 'Performance %', '', '', ''], style: [nil, @negrito]
+      end
+    end
+
+    # ── ENTREGAS ─────────────────────────────────────────────────
     # A..V direto da tabela gade (texto); W..Z do rastreio:
     # W STATUS (completed/failed...), X ENTREGA (Sim/Não), Y DATA OCORRÊNCIA
     # (data real do checkout), Z OCORRÊNCIA (motivo do insucesso).
-    def linha(l)
+    def aba_entregas(wb)
+      linhas = @planilha.linhas
+      wb.add_worksheet(name: 'ENTREGAS') do |sheet|
+        sheet.add_row(PlanilhaEntregas::CABECALHO, style: @negrito)
+        linhas.each do |l|
+          valores, tipos = linha_entrega(l)
+          sheet.add_row(valores, types: tipos)
+        end
+        sheet.auto_filter = "A1:Z#{linhas.size + 1}"
+      end
+    end
+
+    def linha_entrega(l)
       status_r = l['rastreio_status'].to_s
       entregue = if status_r == 'completed'
                    'Sim'
@@ -49,6 +120,26 @@ module Analytics
       [valores, tipos]
     end
 
+    # ── SimpliRoute ──────────────────────────────────────────────
+    # Dump cru do rastreio (todas as visitas das NFs da operação), com o
+    # cabeçalho exato do modelo; cabeçalho sem coluna correspondente sai vazio.
+    def aba_simpliroute(wb)
+      colunas = @planilha.colunas_rastreio
+      linhas  = @planilha.linhas_rastreio
+      wb.add_worksheet(name: 'SimpliRoute') do |sheet|
+        sheet.add_row(colunas.keys, style: @negrito)
+        linhas.each do |r|
+          sheet.add_row(colunas.values.map { |col| col ? texto(r[col]) : '' },
+                        types: Array.new(colunas.size, :string))
+        end
+        sheet.auto_filter = "A1:#{Axlsx.col_ref(colunas.size - 1)}#{linhas.size + 1}"
+      end
+    end
+
+    def texto(valor)
+      valor.respond_to?(:strftime) ? valor.strftime('%d/%m/%Y %H:%M') : valor.to_s
+    end
+
     def data_de(valor)
       return nil if valor.nil?
       return valor.to_date if valor.respond_to?(:to_date)