Hmmm, devo estar a fazer algo mal:
automation:
- alias: "Bi-horário EDP-semanal"
initial_state: true
trigger:
- platform: homeassistant
event: start
action:
- service: shell_command.python_install_pymysql_dependency
- alias: "Registar o consumo diário bi-horário"
initial_state: true
trigger:
- platform: state
entity_id:
- sensor.daily_energy_shelly_peak_offpeak_offpeak
- sensor.daily_energy_shelly_peak_offpeak_peak
action:
- service: shell_command.daily_insert_mysql
data_template:
table: consumobi_energia
value: >
{{ trigger.to_state.state }}
column: >
{% if trigger.entity_id == "sensor.daily_energy_shelly_peak_offpeak_offpeak" %}
consumobi_energia_diario_offpeak
{% endif %}
{% if trigger.entity_id == "sensor.daily_energy_shelly_peak_offpeak_peak" %}
consumobi_energia_diario_peak
{% endif %}
sensor:
- platform: sql
db_url: mysql://XXX:XXX.@core-mariadb/custom_data
queries:
- name: consumobi_electricidade_offpeak
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(consumobi_energia_diario_offpeak SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`consumobi_energia_diario_offpeak` >= 10, 'red', IF(`consumobi_energia_diario_offpeak` >= 6, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(consumobi_energia_diario_offpeak), 2) AS total,
FORMAT(round(((sum(consumo_energia_diario_offpeak) * 0.0923)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM consumobi_energia
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value_offpeak"
- name: consumobi_electricidade_peak
query: >-
SELECT
CONCAT(
'[',
GROUP_CONCAT(DAY(date) SEPARATOR ','),
']'
) AS labels,
CONCAT(
'[',
GROUP_CONCAT(consumobi_energia_diario_peak SEPARATOR ','),
']'
) AS data,
CONCAT(
'["',
GROUP_CONCAT(IF(`consumobi_energia_diario_peak` >= 10, 'red', IF(`consumobi_energia_diario_peak` >= 6, 'orange', 'green')) SEPARATOR '","'),
'"]'
) AS colors,
ROUND(SUM(consumobi_energia_diario_peak), 2) AS total,
FORMAT(round(((sum(consumo_energia_diario_peak) * 0.1833)*1.23), 2), 2) AS 'total_eur',
'info in attributes' AS value
FROM consumobi_energia
WHERE MONTH(date) = MONTH(CURRENT_DATE);
column: "value_peak"
Como ainda só há registo do “offpeak”, dadas as horas, no HeidiSQL, experimentei o chartjs:
chart: bar
custom_options:
showLegend: false
data:
datasets:
- backgroundColor: '${states["sensor.consumobi_electricidade_offpeak"].attributes.colors}'
borderWidth: 1
data: '${states["sensor.consumobi_electricidade_offpeak"].attributes.data}'
label: Eletricidade
labels: '${states["sensor.consumobi_electricidade_offpeak"].attributes.labels}'
entity_row: false
options:
scales:
yAxes:
- ticks:
beginAtZero: true
title:
display: true
text: >
${"Consumo energético Mês Corrente -> " +
states["sensor.consumobi_electricidade_offpeak"].attributes.total_eur + "€"}
type: 'custom:chartjs-card'
Mas o gráfico aparece, no eixo X, desconfigurado, e sem valores apresentados.
No HeidiSQL, o offpeak está a 0,65.
