DAO frontend v2

[storeman]

I want to contribute to this helpful and beautiful project, so I started experimenting with a new frontend. I noticed there was a lot of organic grow going on in the routes.py and the routing was all done through POST-requests, which is not very user friendly to navigate using back and forth buttons.

I've spend some hours setting up an alternative UI (v2) which looks fresh (using bootstrap), has a clean routes.py by simplifying some parts and rebuilding them with the knowledge we have now. I've rebuild first part now awaiting feedback from the community.

The working branch: https://github.com/storeman/dao/tree/feature/frontend-v2
To visit the new frontend, go to your DAO url and add /v2

I've added:

• NPM package.json to manage javascript/css libraries:
  • Bootstrap
  • Bootstrap Icons
  • HTMX for smooth reloading on the run-page

Changes:

• Use GET requests for getting info
• Use POST when modifying/executing
• Split graph and log views, but allow navigation between them
• Streamlined routes.py
• Improved run-status showing/loading
• All on the /v2 path (original routes.py untouched)

I wanted to check here if this is something the community is open to these changes so I can continue investing time in this project.

Here some screenshots:

[corneel27]

Look perfect.
I will do some tests.
Perhaps you will make e PR?

[storeman]

Thanks!

I will first try to create the missing pages (reports, solar, savings, config). Now I get the hang of python, I think I can finish it this week. When completed, I will create a PR. Is that how you want to work? I don´t want unfinished work to pollute any workflow.

[corneel27]

OK, I will wait your finished work, but what I have seen looks a very good improvement.
When you have questions: you can ask them here or in a dm on tweakers.

[corneel27]

I have done some tests.

There is a problem in the run-menu:
When I run one of the items all seems OK.
But when i run a second items it only shows the log of the first item and after a while (when finished) I get the message: done.
For example: I did the baseload calculation and after that optimize calc with debug:

[storeman]

Check! It had something to do with the same timestamps on different logs. Added a fix, plus a button to cancel a running process. Also pushed

[corneel27]

When I try to generate a report V2 I get an empty screen:

Do I make a mistake?

[storeman]

Fixed the DB-migration in check_db.py in the PR.

[corneel27]

When I return to the "legacy" UI I cannot switch from the "Home" menu to another menu-item:

Reports "tomorrow" give an empty result.

When I check forecast: it works, but tomorrow is always with forecast

[storeman]

Die is interessant, kennelijk genereert Flask een url naar /home en deze zou in de huidige versie hetzelfde gedrag opleveren. Ik pas de /home route aan naar /. Inmiddels gepusht.

[storeman]

Forecast-issue ook aangepast. Komt ook in de PR.

[corneel27]

Forecast -issue is gedeeltelijk opgelost.
Wanneer forecastniet van toepassing is kan dan de check-box "hidden" worden gemaakt
Bijvoorbeeld: "Yesterday" heeft geen forecast:

Uitlijning is nog niet aangepast of moet ik een "opwaardering" of build doen?

[corneel27]

Na een nieuwe "npm build", werkt de uitlijning wel goed!

[storeman]

Yes, build is dan nodig inderdaad!

Forecast issue heb ik aangepast: deze heeft een eigen dropdown-item

[corneel27]

Smaken kunnen verschillen maar ik vind getal-kolommen rechts uitgelijnd mooier, grotere getallen springen er dan ook beter uit:

[storeman]

Je hebt gelijk. Inmiddels aangepast.

[storeman]

Can you check if the migration has been applied? The variable table should have the 'aggregate' column with defaults.

…

-------- Original Message --------

On Monday, 07/13/26 at 16:12 Cees van Beek ***@***.***> wrote: When I try to generate a report V2 I get an empty screen: [image.png (view on web)](https://github.com/user-attachments/assets/8435298b-a373-4896-a0e8-66bd6905b1e1) Do I make a mistake? — Reply to this email directly, [view it on GitHub](#720?email_source=notifications&email_token=AAAT45YIHCFFTX3AT4WTATL5ETU3FA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCNZWGIZTIMRWUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVRTG633UMVZF6Y3MNFRWW#discussioncomment-17623426), or [unsubscribe](https://github.com/notifications/unsubscribe-auth/AAAT456DCAXKHW4NBOHXWGT5ETU3FAVCNFSNUABIKJSXA33TNF2G64TZHM2TSNBQGQZDCMJUHNCGS43DOVZXG2LPNY5TCMBTG4YTENJSUF3AE). Triage notifications, keep track of coding agent tasks and review pull requests on the go with GitHub Mobile for [iOS](https://github.com/notifications/mobile/ios/AAAT453XIHY5VVGGMWLY22L5ETU3FA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCNZWGIZTIMRWUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVJTG633UMVZF62LPOM) and [Android](https://github.com/notifications/mobile/android/AAAT4572HCN5CHV56ZM3PMT5ETU3FA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCNZWGIZTIMRWUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVZTG633UMVZF6YLOMRZG62LE). Download it today! You are receiving this because you authored the thread.Message ID: ***@***.***>

[corneel27]

Oh, I totally forgot that. You have added this in check_db.py. I must run this for the db upgrade.
Thank you!

[corneel27]

Helaas nog steeds een lege tabel/geen grafiek in Reports V2, ook niet na npm build.

[storeman]

Gaan we dat uitzoeken. Stappenplan:

• Migratie gelukt?
• Werkt dit endpoint: /v2/api/data/?start=2026-07-01T00%3A00&end=2026-07-02T00%3A00&aggregate=hour
• Kun je op de report-v2 pagina de browser console openen (F12) en kijken of je foutmeldingen ziet?

[corneel27]

Ja migratie van db (kolom aggregate erbij) is gelukt.

http://192.168.178.64:5000//v2/api/data/?start=2026-07-01T00%3A00&end=2026-07-02T00%3A00&aggregate=hour
Geeft een internal server error.
In dashboard log deze foutmelding:

2026-07-14 13:14:38,763 fout app Thread-35 (process_request_thread) : Exception on /v2/api/data/ [GET]
Traceback (most recent call last):
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/cursors.py", line 157, in execute
    result = self._query(query)
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
    ~~~~~~~~~~^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 575, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 826, in _read_query_result
    result.read()
    ~~~~~~~~~~~^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 1203, in read
    first_packet = self.connection._read_packet()
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 782, in _read_packet
    packet.raise_for_error()
    ~~~~~~~~~~~~~~~~~~~~~~^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
    ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('2026-07-01 00:00:00') AS serie_start, datetime('2026-07-02 00:00:00') AS se...' at line 2")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pandas/io/sql.py", line 1679, in execute
    return execute_function(sql, *args)
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
    return meth(
        self,
        distilled_parameters,
        execution_options or NO_OPTIONS,
    )
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/sql/elements.py", line 527, in _execute_on_connection
    return connection._execute_clauseelement(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self, distilled_params, execution_options
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/base.py", line 1641, in _execute_clauseelement
    ret = self._execute_context(
        dialect,
    ...<8 lines>...
        cache_hit=cache_hit,
    )
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ~~~~~~~~~~~~~~~~~~~~~~~~~^
        dialect, context, statement, parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        e, str_statement, effective_parameters, cursor, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/base.py", line 2363, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/cursors.py", line 157, in execute
    result = self._query(query)
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
    ~~~~~~~~~~^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 575, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 826, in _read_query_result
    result.read()
    ~~~~~~~~~~~^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 1203, in read
    first_packet = self.connection._read_packet()
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/connections.py", line 782, in _read_packet
    packet.raise_for_error()
    ~~~~~~~~~~~~~~~~~~~~~~^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
    ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('2026-07-01 00:00:00') AS serie_start, datetime('2026-07-02 00:00:00') AS se...' at line 2")
[SQL: WITH RECURSIVE params AS 
(SELECT datetime(%(serie_start)s) AS serie_start, datetime(%(serie_end)s) AS serie_end, %(step)s AS step), 
series(dt_start, ts_start, dt_end, ts_end) AS 
(SELECT params.serie_start AS dt_start, unixepoch(params.serie_start) AS ts_start, datetime(params.serie_start, params.step) AS dt_end, unixepoch(datetime(params.serie_start, params.step)) AS ts_end 
FROM params UNION ALL SELECT series.dt_end AS dt_start, series.ts_end AS ts_start, datetime(series.dt_end, params.step) AS dt_end, unixepoch(datetime(series.dt_end, params.step)) AS ts_end 
FROM series, params 
WHERE datetime(series.dt_end, params.step) <= params.serie_end), 
selected_vars AS 
(SELECT %(param_1)s AS code UNION ALL SELECT %(param_2)s AS code UNION ALL SELECT %(param_3)s AS code UNION ALL SELECT %(param_4)s AS code UNION ALL SELECT %(param_5)s AS code UNION ALL SELECT %(param_6)s AS code UNION ALL SELECT %(param_7)s AS code UNION ALL SELECT %(param_8)s AS code UNION ALL SELECT %(param_9)s AS code UNION ALL SELECT %(param_10)s AS code), 
vals AS 
(SELECT series.ts_start AS ts, variabel.code AS code, CASE WHEN (variabel.aggregate = %(aggregate_1)s) THEN sum(`values`.value) ELSE avg(`values`.value) END AS val 
FROM series INNER JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN `values` ON variabel.id = `values`.variabel AND `values`.time >= series.ts_start AND `values`.time < series.ts_end GROUP BY variabel.code, series.dt_start), 
forecasts AS 
(SELECT series.ts_start AS ts, variabel.code AS code, CASE WHEN (variabel.aggregate = %(aggregate_2)s) THEN sum(prognoses.value) ELSE avg(prognoses.value) END AS val 
FROM series INNER JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN prognoses ON variabel.id = prognoses.variabel AND prognoses.time >= series.ts_start AND prognoses.time < series.ts_end GROUP BY variabel.code, series.dt_start)
 SELECT series.dt_start AS ts, variabel.code, variabel.dim, vals.val AS v, forecasts.val AS f 
FROM series INNER JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN vals ON vals.ts = series.ts_start AND vals.code = variabel.code LEFT OUTER JOIN forecasts ON forecasts.ts = series.ts_start AND forecasts.code = variabel.code]
[parameters: {'serie_start': datetime.datetime(2026, 7, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Amsterdam')), 'serie_end': datetime.datetime(2026, 7, 2, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Amsterdam')), 'step': '+1 hour', 'param_1': 'da', 'param_2': 'cons', 'param_3': 'prod', 'param_4': 'pv_dc', 'param_5': 'pv_ac', 'param_6': 'profit', 'param_7': 'cost', 'param_8': 'bat_in', 'param_9': 'bat_out', 'param_10': 'soc', 'aggregate_1': 'sum', 'aggregate_2': 'sum'}]
(Background on this error at: https://sqlalche.me/e/20/f405)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/flask/app.py", line 1511, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/flask/app.py", line 919, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/flask/app.py", line 917, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/flask/app.py", line 902, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/home/cees/day-ahead-6/dao/webserver/app/v2/api/routes.py", line 24, in data
    data = data_report.get_data(
        start=datetime.fromisoformat(start).replace(tzinfo=ZoneInfo(timezone_raw)),
        end=datetime.fromisoformat(end).replace(tzinfo=ZoneInfo(timezone_raw)),
        aggregate=aggregate
    )
  File "/home/cees/day-ahead-6/dao/prog/da_report.py", line 3640, in get_data
    df = pd.read_sql_query(query, connection)
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pandas/io/sql.py", line 497, in read_sql_query
    return pandas_sql.read_query(
           ~~~~~~~~~~~~~~~~~~~~~^
        sql,
        ^^^^
    ...<6 lines>...
        dtype_backend=dtype_backend,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pandas/io/sql.py", line 1856, in read_query
    result = self.execute(sql, params)
  File "/home/cees/day-ahead-6/venv3.13/lib/python3.14/site-packages/pandas/io/sql.py", line 1681, in execute
    raise DatabaseError(f"Execution failed on sql '{sql}': {exc}") from exc
pandas.errors.DatabaseError: Execution failed on sql 'WITH RECURSIVE params AS 
(SELECT datetime(:serie_start) AS serie_start, datetime(:serie_end) AS serie_end, :step AS step), 
series(dt_start, ts_start, dt_end, ts_end) AS 
(SELECT params.serie_start AS dt_start, unixepoch(params.serie_start) AS ts_start, datetime(params.serie_start, params.step) AS dt_end, unixepoch(datetime(params.serie_start, params.step)) AS ts_end 
FROM params UNION ALL SELECT series.dt_end AS dt_start, series.ts_end AS ts_start, datetime(series.dt_end, params.step) AS dt_end, unixepoch(datetime(series.dt_end, params.step)) AS ts_end 
FROM series, params 
WHERE datetime(series.dt_end, params.step) <= params.serie_end), 
selected_vars AS 
(SELECT :param_1 AS code UNION ALL SELECT :param_2 AS code UNION ALL SELECT :param_3 AS code UNION ALL SELECT :param_4 AS code UNION ALL SELECT :param_5 AS code UNION ALL SELECT :param_6 AS code UNION ALL SELECT :param_7 AS code UNION ALL SELECT :param_8 AS code UNION ALL SELECT :param_9 AS code UNION ALL SELECT :param_10 AS code), 
vals AS 
(SELECT series.ts_start AS ts, variabel.code AS code, CASE WHEN (variabel.aggregate = :aggregate_1) THEN sum(values.value) ELSE avg(values.value) END AS val 
FROM series JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN values ON variabel.id = values.variabel AND values.time >= series.ts_start AND values.time < series.ts_end GROUP BY variabel.code, series.dt_start), 
forecasts AS 
(SELECT series.ts_start AS ts, variabel.code AS code, CASE WHEN (variabel.aggregate = :aggregate_2) THEN sum(prognoses.value) ELSE avg(prognoses.value) END AS val 
FROM series JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN prognoses ON variabel.id = prognoses.variabel AND prognoses.time >= series.ts_start AND prognoses.time < series.ts_end GROUP BY variabel.code, series.dt_start)
 SELECT series.dt_start AS ts, variabel.code, variabel.dim, vals.val AS v, forecasts.val AS f 
FROM series JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN vals ON vals.ts = series.ts_start AND vals.code = variabel.code LEFT OUTER JOIN forecasts ON forecasts.ts = series.ts_start AND forecasts.code = variabel.code': (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('2026-07-01 00:00:00') AS serie_start, datetime('2026-07-02 00:00:00') AS se...' at line 2")
[SQL: WITH RECURSIVE params AS 
(SELECT datetime(%(serie_start)s) AS serie_start, datetime(%(serie_end)s) AS serie_end, %(step)s AS step), 
series(dt_start, ts_start, dt_end, ts_end) AS 
(SELECT params.serie_start AS dt_start, unixepoch(params.serie_start) AS ts_start, datetime(params.serie_start, params.step) AS dt_end, unixepoch(datetime(params.serie_start, params.step)) AS ts_end 
FROM params UNION ALL SELECT series.dt_end AS dt_start, series.ts_end AS ts_start, datetime(series.dt_end, params.step) AS dt_end, unixepoch(datetime(series.dt_end, params.step)) AS ts_end 
FROM series, params 
WHERE datetime(series.dt_end, params.step) <= params.serie_end), 
selected_vars AS 
(SELECT %(param_1)s AS code UNION ALL SELECT %(param_2)s AS code UNION ALL SELECT %(param_3)s AS code UNION ALL SELECT %(param_4)s AS code UNION ALL SELECT %(param_5)s AS code UNION ALL SELECT %(param_6)s AS code UNION ALL SELECT %(param_7)s AS code UNION ALL SELECT %(param_8)s AS code UNION ALL SELECT %(param_9)s AS code UNION ALL SELECT %(param_10)s AS code), 
vals AS 
(SELECT series.ts_start AS ts, variabel.code AS code, CASE WHEN (variabel.aggregate = %(aggregate_1)s) THEN sum(`values`.value) ELSE avg(`values`.value) END AS val 
FROM series INNER JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN `values` ON variabel.id = `values`.variabel AND `values`.time >= series.ts_start AND `values`.time < series.ts_end GROUP BY variabel.code, series.dt_start), 
forecasts AS 
(SELECT series.ts_start AS ts, variabel.code AS code, CASE WHEN (variabel.aggregate = %(aggregate_2)s) THEN sum(prognoses.value) ELSE avg(prognoses.value) END AS val 
FROM series INNER JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN prognoses ON variabel.id = prognoses.variabel AND prognoses.time >= series.ts_start AND prognoses.time < series.ts_end GROUP BY variabel.code, series.dt_start)
 SELECT series.dt_start AS ts, variabel.code, variabel.dim, vals.val AS v, forecasts.val AS f 
FROM series INNER JOIN variabel ON variabel.code IN (SELECT selected_vars.code 
FROM selected_vars) LEFT OUTER JOIN vals ON vals.ts = series.ts_start AND vals.code = variabel.code LEFT OUTER JOIN forecasts ON forecasts.ts = series.ts_start AND forecasts.code = variabel.code]
[parameters: {'serie_start': datetime.datetime(2026, 7, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Amsterdam')), 'serie_end': datetime.datetime(2026, 7, 2, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Amsterdam')), 'step': '+1 hour', 'param_1': 'da', 'param_2': 'cons', 'param_3': 'prod', 'param_4': 'pv_dc', 'param_5': 'pv_ac', 'param_6': 'profit', 'param_7': 'cost', 'param_8': 'bat_in', 'param_9': 'bat_out', 'param_10': 'soc', 'aggregate_1': 'sum', 'aggregate_2': 'sum'}]
(Background on this error at: https://sqlalche.me/e/20/f405)
2026-07-14 13:14:38,769 info werkzeug Thread-35 (process_request_thread) : 192.168.178.64 - - [14/Jul/2026 13:14:38] "�[35m�[1mGET /v2/api/data/?start=2026-07-01T00:00&end=2026-07-02T00:00&aggregate=hour HTTP/1.1�[0m" 500 -

Dezelfde foutmelding in dashboard.log krijg ik als ik dit opvraag:
http://192.168.178.64:5000/v2/reports-v2 en een leeg scherm krijg.

Dit is het scherm met de developers-console:

Voor de volledigheid: ik werk met maraiadb.

[storeman]

Thanks, dat zou heel goed kunnen. Ik moet eerst even een omgeving opzetten waarop ik kan testen. Is daar al iets voor beschikbaar? Ik kon het nog niet echt vinden in de source.

[storeman]

Goed, dat is een flinke puzzel om universeel te maken. Ik had me niet voldoende gerealiseerd dat er dergelijk grote verschillen zijn in het werken met datum/tijd. Ik snap dat je die logica naar pandas hebt getrokken.

Ik blijf nog even puzzelen.

[corneel27]

Nee er is nog geen goede test-suite.
Staat wel op mijn wensen-lijst om te maken.

Tot nu toe converteer ik de twee mariadb-databases: ha (alleen statistics en meta_statistics) en dao naar sqlite op mijn laptop daarna converteer ik die weer naar postgresql en dan doe ik handmatig een riedel tests op alle db's.
Conversie gaat met een script (je moet eerst met pip mysql2sqlite installeren).
Script wordt gerund vanuit dao\prog:

mysql2sqlite -f ../data/day_ahead.db -d day_ahead -h 192.168.178.36 -P 3306 -u day_ahead -p -V --use-buffered-cursors
mysql2sqlite -f ../data/home-assistant_v2.db -d homeassistant -h 192.168.178.36 -P 3306 -u homeassistant -p -t statistics_meta statistics -V --use-buffered-cursors
cd ../data
pgloader --with "prefetch rows = 1000" sqlite://day_ahead.db pgsql://postgres:max1950ml@localhost/day_ahead
pgloader --with "prefetch rows = 1000" sqlite://home-assistant_v2.db pgsql://postgres:max1950ml@localhost/homeassistant
cd ../prog

Kom je hiermee iets verder?

[corneel27]

Je moet de python versie hebben: pip install mysql-to-sqlite3

[storeman]

Check, ik heb nu een chart op een mysql backend en postgres backend.

[corneel27]

Ik heb vanmorgen de nieuwe updates in je PR gemerged.
Opnieuw npm build gerund.
Ik krijg (met mariadb/mysql backend) deze uitvoer.
Dit staat in de adres-balk: http://192.168.178.64:5000/v2/reports-v2

[corneel27]

Wat ik me nog bedacht:
als we het rekenwerk aan mijn kant gebruiken, ik maak daar een soort van api-interface van die jij aanroept en jij zet de data in tabellen en grafieken met jouw tools.
Zou dat kunnen werken?

[storeman]

Dat verklaart een hoop! Ik ga me even oriënteren in de huidige logica.

[storeman]

Ik heb een zelfde soort query kunnen maken voor de HA database en die data wordt nu gecombineerd. Een paar vraagjes;

1. Klopt het dat de SoC niet uit de HA-database wordt gelezen, dus dat alleen de prognoses beschikbaar zijn?
2. Ik zie sommige forecast-waardes behoorlijk fors afwijken. Dit zijn dan historische forecast waardes. Heb jij daar een verklaring voor?

4. Duurt het laden van de data/chart bij jou nog zo lang? Bij mij duurden de oude charts/tables voor balans tamelijk lang. Ik heb net de HA-query geoptimaliseerd. Wellicht dat dat ook nog nodig is voor de DA-query, maar dan moet er wel eerst een probleem zijn.
5. Nieuwe PR gemaakt om te testen. De "presets" werken nog niet, maar de kolommen zijn wel beter te selecteren. Ik heb nog niet alle datasets geconfigureerd voor de chart, dus het kan zijn dat je wel data in de tabel ziet, maar nog niet in de chart. Als je wil draaien is een npm install / npm ci nodig én een npm run build ivm een nieuwe package

[corneel27]

Ik heb je laatste PR gemerged en mijn lokale branche geupdated met jouw aanvullingen.
Met mysql duurt het nog steeds 30 sec.
Ik heb mijn databases geconverteerd naar sqlite,maar een nieuwe start geeft na 3 minuten nog geen grafiek/tabel.
Ook geen foutmelding in dahboard.log.
Heb ik dan zo'n trage hardware (Intel I7, 2019, ssd)?

Misschien heb ik iets niet goed geinstalleerd?
Hoe krijg ik dit gedebugged?

[storeman]

Kun je laten weten hoeveel records je ongeveer in je db hebt (values en prognoses)? Die hardware lijkt me in ieder geval geen probleem.
Ter referentie bij mij: 160k values en 650k prognoses, sqlite ~22MB

Eventueel kan ik je eens mijn sqlite db sturen om te kijken of dat helpt. (of jij stuurt de jouwe naar mij), dan kan ik het debuggen.

Bij mij heb ik op dit endpoint: /v2/api/data/?start=2026-07-01&end=2026-07-10&aggregate=hour

Productie/docker (N150 cpu, traag): 1300ms
Lokaal/dev (i9-13900 laptop cpu): 1200ms

[corneel27]

mysql/mariadb:
prognoses 740.602 records 99,3 MB
values 323.512 records 44.0 MB
sqlite totaal: 66,61 MB

Als ik dit invul in de adres-balk:
http://192.168.178.64:5000/v2/api/data/?start=2026-07-01&end=2026-07-10&aggregate=hour
Krijg ik:
{"error":"aggregate"}
Misschien zit daar de fout, doe ik iets verkeerd of ben ik iets vergeten?