I am looking to add a custom view for dashboard addition on my system.
My purpose is to have a quick overview with latest account.statement entries (per journal) with amount. It answers the query about “what is my current amount of money in bank accounts”.
The SQL query I would use (in pure sql) would be:
SELECT last.journal, last.date, end_balance
FROM (SELECT journal, MAX(date) AS date FROM account_statement GROUP BY journal) AS last,
account_statement
WHERE last.journal = account_statement.journal
AND last.date = account_statement.date;
Note that I am not using state information as I am populating automatically Statement from bank. So I know that draft statement are right too.
Now, I am looking to do it in the right way. Do you have advices for that ?
For now I am considering:
extending account.statement.journal to provide last_date and last_amount information (with Function fields)
or having a new Model (but which type ? ModelSQL seems wrong as there is nothing to store) to query the database
Next steps for having a View and Action Window are more simple.
It looks like your primary key is the statement journal so it seems a good choice.
This is always an option to define a new ModelSQL to expose a SQL query to the ORM. The main reasons to do that are:
The query result can not be linked to a primary Model (no Function field)
The access rights must be different than the original Model of the table
It is much more performant than a SQL query
So I think the Function field is the best solution. And I’m also thinking that it could land in standard (and replace the code of Statement.on_change_journal).
I agree, lats amount is always used as starting balance of the next journal.
And for the next date I always search for the last statement date to be able to know from which date I need to import the bank statements.
I think this is also interseting for bussines owners, as most of the time they will like to know which is the balance of its bank accounts (which are represented by statements). So I think this also makes sense for statment.
a question just to be sure to understand your meaning: you would like to move the current code of Statement.on_change_journal (keep the search(order, limit=1) approch) , or you would like to rewrite the method as Function field inside Journal but using postgresql Window Functions ?
I think it is better to have function field on journal (using Window Functions as it will require to be a class method getter). And update the Statement.on_change_journal to use the new field instead of doing a search.