Como enviar um relatório mensal do consumo energético

Introdução

Neste tutorial vou mostrar a minha implementação para enviar um email todos os meses com os dados referentes ao mês passado do consumo energético. Indica também o custo da fatura e faz as contas para mostrar o custo de cada equipamento nesse mês.
É uma forma de nos mantermos informados.

Requerimento :point_down:
Este tutorial usa um tópico meu, que habilita guardarmos os dados na base de dados durante muito tempo, ele guarda um valor por dia, neste caso guardamos o valor total do dia por aparelho e o total, algo como isto:

data total forno frigorifico
15-04-2020 20.4 2.4 1
16-04-2020 18.2 0 0.89
17-04-2020 21 0.1 0.57
18-04-2020 19.8 0 0.9

Código

Sensor para a recolha dos dados da base de dados

Os dados são guardados nos atributos, porque pelos meus testes o state tem um limite menor de caractéres do que o attributes.

No db_url alterem o user, password e table para o vosso caso

No SELECT, pelo menos 3 dados são precisos (Day, month e year), o resto são as vossas colunas na tabela. Notem que eu tenho uma coluna chamada total_eur_increment, essa coluna é onde tenho o valor da fatura, na verdade esse campo só é preciso na última linha, serve para fazer os cálculos dos gastos em €
Alterem também o energy_kwh pelo nome da vossa tabela

RECOMENDAÇÃO: Aconselho a excluirem este sensor do recorder para não ocupar espaço na base de dados do Home Assistant, na verdade só vão precisar do valor atual, isto é totalmente opcional.

ATENÇÃO: Se a vossa query SQL tiver muitos campos, pode acontecer ao SQL não passar a informação toda devido ao max_allowed_packet (não tenho a certeza se é mesmo isto, mas acho que sim), neste caso, têm duas opções, ou diminuem os campos ou então têm de alterar a variável na base de dados.

sensor:
  - platform: sql
    db_url: mysql://user:password@core-mariadb/table
    queries:
      - name: energy_last_month_report
        query: >
          SELECT
            CONCAT
            (
              '[',
              GROUP_CONCAT(json_content SEPARATOR ','),
              ']'
            ) AS json,
            'info in attributes' AS 'attributes'
          FROM
          (
            SELECT JSON_OBJECT(
              "day", DAY(`date`),
              "month", MONTH(`date`),
              "year", YEAR(`date`),
              "total", ROUND(IFNULL(`total`, 0), 2),
              "total_eur_increment", ROUND(IFNULL(`total_eur_increment`, 0), 2),
              "placa", ROUND(IFNULL(`placa`, 0), 2),
              "oven", ROUND(IFNULL(`oven`, 0), 2),
              "microwave", ROUND(IFNULL(`microwave`, 0), 2),
              "fridge", ROUND(IFNULL(`fridge`, 0), 2),
              "washing_machine", ROUND(IFNULL(`washing_machine`, 0), 2),
              "drying_machine", ROUND(IFNULL(`drying_machine`, 0), 2),
              "dishwasher", ROUND(IFNULL(`dishwasher`, 0), 2),
              "hot_water_tank", ROUND(IFNULL(`hot_water_tank`, 0), 2),
              "ender3", ROUND(IFNULL(`ender3`, 0), 2),
              "pc_ricardo", ROUND(IFNULL(`pc_ricardo`, 0), 2)
            ) AS json_content
            FROM `energy_kwh`
            WHERE YEAR(`date`) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
            AND MONTH(`date`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
            ORDER BY DAY(`date`)
          ) AS json;
        column: "attributes"

Configuração do notify para envio de email

Neste passo, usem as configurações que sejam corretas para o vosso email.

notify:
  - platform: smtp
    name: email
    sender: noreply@cpha.pt
    sender_name: Home Assistant
    recipient: ricreis394@cpha.pt
    server: smtp.cpha.pt
    port: 587
    username: noreply@cpha.pt
    password: teste123

Sensor que altera todos os meses

Este sensor vai servir de trigger na automação, podem utilizar outro caso tenham.

sensor:
  - platform: time_date
    display_options:
      - "date"
  - platform: template
    sensors:
      month:
        entity_id: sensor.date
        value_template: >
          {{ ["Janeiro", "Fevereiro", "Março", "Abril", "Maio", "Junho", "Julho", "Agosto", "Setembro", "Outubro", "Novembro", "Dezembro"][now().month-1] }}

Automação

A automação vai atualizar os dados do sensor SQL (alterem para o nome do vosso sensor), para que na passagem de mês ele altere os valores para os corretos e envia imediatamente os dados por email.
Na parte do html têm de alterar pelos vossos nomes das colunas.
Resumidamente o html está composto da seguinte forma:

  • Preenche o cabeçalho da tabela com os nomes, está dentro da tag <thead>
  • Dentro da <tbody> ele percorre todos os dias e mostra os valores
  • No <tfoot> tem duas linhas que é o Total em kWh e o Total em €

Ou seja, nestes três sítios têm que alterar os nomes. Neste caso pode ter células a mais ou a menos dependendo do vosse setup, adaptem!

automation:
  - alias: energy_last_month_report_email
    initial_state: true
    trigger:
      - platform: state
        entity_id: sensor.month
    action:
      - service: homeassistant.update_entity
        data:
          entity_id: sensor.energy_last_month_report
      - service: notify.email
        data_template:
          title: Relatório Mensal - Consumo energético
          message: " "
          data:
            html: >
              {% set json = (states.sensor.energy_last_month_report.attributes.json|from_json) %}
              {% set month_number = json[0]["month"] | int %}
              {% set year = json[0]["year"] | int %}
              {% set month = ["Janeiro", "Fevereiro", "Março", "Abril", "Maio", "Junho", "Julho", "Agosto", "Setembro", "Outubro", "Novembro", "Dezembro"][month_number-1] %}
              
              {% set total_eur = json[-1]["total_eur_increment"] %}
              
              {% macro get_total(attribute) %}
                {{ json | sum(attribute=attribute) | round(2) }}
              {% endmacro %}
              
              {% macro cell(value) %}
                {% if value == 0.00 %}
                  {% set value = value | int %}
                {% endif %}
                <td>{{ value }}</td>
              {% endmacro %}
              
              {% macro get_total_eur(attribute) %}
                {{ ((float(get_total(attribute)) * float(total_eur)) / float(get_total("total"))) | round(2) }} €
              {% endmacro %}
              
              <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
              <html xmlns="http://www.w3.org/1999/xhtml">
                <head>
                  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
                  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
                  <style type="text/css">
                    body {
                      font-family: Arial, Helvetica, sans-serif;
                    }
                    
                    table {
                      border-collapse: collapse;
                      border-radius: 3px;
                      overflow: hidden;
                      background: #fff;
                    }
                    table th, table td {
                      padding: 0.4em;
                      text-align: center;
                    }
                    table thead tr {
                      background: #03a9f4;
                      color: #fff;
                    }
                    table tbody tr.odd {
                      background-color: #fff;
                      color: #222;
                    }
                    table tbody tr.even {
                      background-color: #e9e9e9;
                      color: #222;
                    }
                    table tfoot tr {
                      background: #919191;
                      color: #fff;
                    }
                  </style>
                </head>
                <body>
                  <h1>Home Assistant</h1>
                  <h2>Consumo energético</h2>
                  <p>Relatório mensal de {{month}} de {{year}}</p>
                  <p>Total: {{ get_total("total") }} kWh</p>
                  <p>Valor aproximado da fatura: <b>{{ total_eur }} €</b></p>
                  <table>
                    <thead>
                      <tr>
                        <th>Dia</th>
                        <th>Total</th>
                        <th>Placa</th>
                        <th>Micro-ondas</th>
                        <th>Forno</th>
                        <th>Frigorífico</th>
                        <th>Máquina de lavar roupa</th>
                        <th>Máquina de secar roupa</th>
                        <th>Máquina de lavar loiça</th>
                        <th>Ender3</th>
                        <th>PC Ricardo</th>
                      </tr>
                    </thead>
                    <tbody>
                      {% for day in json %}
                      <tr class="{{loop.cycle('odd', 'even')}}">
                        {{ cell(day["day"]) }}
                        {{ cell(day["total"]) }}
                        {{ cell(day["placa"]) }}
                        {{ cell(day["microwave"]) }}
                        {{ cell(day["oven"]) }}
                        {{ cell(day["fridge"]) }}
                        {{ cell(day["washing_machine"]) }}
                        {{ cell(day["drying_machine"]) }}
                        {{ cell(day["dishwasher"]) }}
                        {{ cell(day["ender3"]) }}
                        {{ cell(day["pc_ricardo"]) }}
                      </tr>
                      {% endfor %}
                    </tbody>
                    <tfoot>
                      <tr>
                        <td><b>Total (kWh)</b></td>
                        <td>{{ get_total("total") }}</td>
                        <td>{{ get_total("placa") }}</td>
                        <td>{{ get_total("microwave") }}</td>
                        <td>{{ get_total("oven") }}</td>
                        <td>{{ get_total("fridge") }}</td>
                        <td>{{ get_total("washing_machine") }}</td>
                        <td>{{ get_total("drying_machine") }}</td>
                        <td>{{ get_total("dishwasher") }}</td>
                        <td>{{ get_total("ender3") }}</td>
                        <td>{{ get_total("pc_ricardo") }}</td>
                      </tr
                      <tr>
                        <td><b>Total (€)</b></td>
                        <td>{{ total_eur }} €</td>
                        <td>{{ get_total_eur("placa") }}</td>
                        <td>{{ get_total_eur("microwave") }}</td>
                        <td>{{ get_total_eur("oven") }}</td>
                        <td>{{ get_total_eur("fridge") }}</td>
                        <td>{{ get_total_eur("washing_machine") }}</td>
                        <td>{{ get_total_eur("drying_machine") }}</td>
                        <td>{{ get_total_eur("dishwasher") }}</td>
                        <td>{{ get_total_eur("ender3") }}</td>
                        <td>{{ get_total_eur("pc_ricardo") }}</td>
                      </tr>
                    </tfoot>
                  </table>
                  <p>Relatório gerado automaticamente por computador.</p>
                  <p>Criado por Ricardo Reis</p>
                </body>
              </html>
5 Curtiram

Bom dia,

Andava com ideias de implementar isto, e portanto salvou-me uma data de tempo. Obrigado por partilhar. Agora só tenho de corrigir as medidas do analisador que construí :). Deixo aqui duas sugestões:
1ª - Gerarem o ficheiros nos dias em que as companhias sugerem o envio da leitura. Para depois comunicarem a leitura.
2ª - Pedirem ás vossas companhias a definição de uma estrutura de ficheiro de forma a que o email gerado possa ser enviado e processado pela companhia.

Mais uma vez obrigado pela partilha.

Bom dia,

Quanto ao 1º ponto, isso é possível, basta apenas alterar o código SQL para condizer com o intervalo de data que pretende, basicamente no WHERE tem de arranjar forma de só selecionar esse intervalo. Não fiz testes, não sei a solução mas passa por aí.
Quanto ao 2º, não sei se a companhia colabora nesse sentido. Mas se sim, é bom. Mas também temos de ter a certeza que os valores gerados estão corretos e não há falhas.

Eu por acaso já questionei a Endesa e eles não possuem esse serviço. Contudo penso que ser forem bastantes pessoas a solicitar o mesmo poderá ser implementado. Aliás não penso que seja difícil. Quanto aos valores gerados serem corretos, aqui as variáveis que influenciarão será o analisador e o HA. Quando ao analisador basta comparar a leitura com o contador. Foi assim que verifiquei que o meu analisador necessita de ser “afinado”. Quando ao HA, penso que não haverá problemas a não ser com erros na base de dados ou problemas no cartão.
No meu caso ainda não tenho dados comparativos porque depois de ter verificado que existia diferença entre o contador da companhia e o meu analisador ignorei os valores. Consigo no entanto verificar que as medições e os registos estão a ser efetuados. Tenho o bi-horário e no HA tenho configurado os períodos de pico e vazio e verifico a evolução ao dia e ao mês, assim como o custo ao dia por período e ao mês. Não está 100% mas dá para ter uma ideia. Além de “afinar” o analisador falta-me criar automações para criar alertas de consumo excessivo, por exemplo quando comparado com dias homólogos.

Sinceramente não acho que fossem implementar tal coisa, concordo que não é dificil mas só vejo esse esforço ser possível se fosse uma empresa, mesmo que a comunidade das casas inteligentes queira isso, não somos assim tantos para os fazer mudar de ideias.
Sobre as leituras, se os valores não estiverem 100% certos mas aproximados não seria um problema, bastava apenas de vez em quando ir calibrando consoante os valores reais do contador.

Eu uma das coisas que pretendo fazer é mesmo automatizar o envio de leituras, sendo que nos contadores mais recentes até já faz isso. O que não é o meu caso.
Contudo, as emrpesas teem portais não é muito complicado ter uma tool que submete via o portal deles, outras aceitação isso por sms também que ainda torna mais simples.

1 Curtiu

Mesmo para particulares existe o portal web para dar a contagem, programaticamente não sou a pessoa mais correta para dizer se é possível automatizar sem trabalho do lado de lá

@ricreis394 com empresas referia-me aos fornecedores edp, galp e endesa. Não é dficil fazer código para ir submeter no portal usando as credenciais de um utilizador e simular a interação como um utilizador real. Existem várias ferramentas criadas para fazer testes de UX que permitem automatizar esse tipo de fluxos ou mesmo através de scripts.

Como saber se o relatório vai ser enviado ?

coloquei isto no meu HA , mas ele este fim de mês não enviou qq relatório.

Existe maneira de testar ?

Obrigado.

A automação foi criada, então podes verificar o atributo dela chamado last_triggered que diz a última vez que foi accionada.
Podes, se quiseres para testar, accionar manualmente a automação para que ela envie o email. Basta abrires a automação e carregas em Despoletar

Boa tarde Ricardo,
Sim o trigger foi feito , mas a automação não envia o email .
Terei de configurar mais alguma coisa para o email ?

notify:
  - platform: smtp
    name: email
    sender: xxxx@gmail.com
    sender_name: My Home Assistant
    recipient: xxxxx@gmail.com
    server: smtp.gmail.com
    port: 587
#    encryption: starttls
    username: xxxx@gmail.com
    password: xxxxx

Também já fui ao myaccount da google e dei permissões .

Permitir aplicativos menos seguros: ATIVADA

Obrigado.

Essas configurações dependem de quem opera o serviço, no meu caso não uso gmail por isso podem não coincidir com essas configurações.

Mas… nunca tinhas testado o envio de email?

Resolvido .
A resposta está aqui neste video de como configurar o GMAIL para permitir o envio de email apartir de “APPS”

Obrigado

2 Curtiram

Copyright © 2017-2021. Todos os direitos reservados
CPHA.pt - info@cpha.pt


FAQ | Termos de Serviço/Regras | Política de Privacidade