ūüďĎ 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>
3 Likes

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 Like

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 Likes

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


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