Odoo 14, issue when trying to computing bank balance in treasury module

Question

i got work to fix error when computing but i still dont have idea how to fix it because i'm still newbie

Odoo Server Error

Traceback (most recent call last): File "/home/equipAccounting/equip/odoo/addons/base/models/ir_http.py", line 237, in _dispatch result = request.dispatch() File "/home/equipAccounting/equip/odoo/http.py", line 683, in dispatch result = self._call_function(**self.params) File "/home/equipAccounting/equip/odoo/http.py", line 359, in _call_function return checked_call(self.db, args, *kwargs) File "/home/equipAccounting/equip/odoo/service/model.py", line 94, in wrapper return f(dbname, args, *kwargs) File "/home/equipAccounting/equip/odoo/http.py", line 347, in checked_call result = self.endpoint(*a, **kw) File "/home/equipAccounting/equip/odoo/http.py", line 912, in call return self.method(*args, **kw) File "/home/equipAccounting/equip/odoo/http.py", line 531, in response_wrap response = f(*args, **kw) File "/home/equipAccounting/equip/addons/basic/web/controllers/main.py", line 1393, in call_button action = self._call_kw(model, method, args, kwargs) File "/home/equipAccounting/equip/addons/basic/web/controllers/main.py", line 1381, in _call_kw return call_kw(request.env[model], method, args, kwargs) File "/home/equipAccounting/equip/odoo/api.py", line 396, in call_kw result = _call_kw_multi(method, model, args, kwargs) File "/home/equipAccounting/equip/odoo/api.py", line 383, in _call_kw_multi result = method(recs, args, *kwargs) File "/home/equipAccounting/equip/addons/core/treasury_forecast/models/treasury_bank_forecast.py", line 290, in compute_bank_balances self.env.cr.execute(main_query) File "/usr/local/lib/python3.8/dist-packages/decorator.py", line 232, in fun return caller(func, (extras + args), *kw) File "/home/equipAccounting/equip/odoo/sql_db.py", line 101, in check return f(self, args, *kwargs) File "/home/equipAccounting/equip/odoo/sql_db.py", line 298, in execute res = self._obj.execute(query, params) Exception

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

Traceback (most recent call last): File "/home/equipAccounting/equip/odoo/http.py", line 639, in _handle_exception return super(JsonRequest, self)._handle_exception(exception) File "/home/equipAccounting/equip/odoo/http.py", line 315, in _handle_exception raise exception.with_traceback(None) from new_cause psycopg2.errors.SyntaxError: syntax error at or near ")" LINE 9:
WHERE abs.journal_id IN ()

and here is the code :

def get_bank_fc_query(self, fc_journal_list, date_start, date_end,company_domain):
    query = """                
            UNION
            SELECT CAST('FBK' AS text) AS type, absl.id AS ID, am.date,                    absl.payment_ref as name, am.company_id, absl.amount_main_currency                    as amount, absl.cf_forecast, abs.journal_id, NULL as kind                FROM account_bank_statement_line absl                
            LEFT JOIN account_move am ON (absl.move_id = am.id)                
            LEFT JOIN account_bank_statement abs ON (absl.statement_id = abs.id)
            WHERE abs.journal_id IN {}                    
            AND am.date BETWEEN '{}' AND '{}'                    
            AND am.company_id in {}            """
            .format(str(fc_journal_list), date_start, date_end,company_domain)
            return query

def get_acc_move_query(self, date_start, date_end, company_domain):
    query = """            
            UNION
            SELECT CAST('FPL' AS text) AS type, aml.id AS ID,aml.treasury_date AS date, am.name AS name, aml.company_id,                aml.amount_residual AS amount, NULL AS cf_forecast,                
            NULL AS journal_id, am.move_type as kind            
            FROM account_move_line aml            
            LEFT JOIN account_move am ON (aml.move_id = am.id)            
            WHERE am.state NOT IN ('draft')                
            AND aml.treasury_planning AND aml.amount_residual != 0                
            AND aml.treasury_date BETWEEN '{}' AND '{}'                
            AND aml.company_id in {}        """
            .format(date_start, date_end, company_domain)
            return query

Thanks in advance

Answer 1

Odoo has a very powerful ORM API to do the psql queries. Is there a good reason you use sql instead?

The functions you need are, Read for selecting the fields you use, search and filtered for filtering the results.

I suggest reading the following tutorial. https://www.odoo.com/documentation/14.0/developer/reference/addons/orm.html#search-read

also look at good examples inside the odoo source, I think the stock module is a good place to see some examples. https://github.com/odoo/odoo/blob/14.0/addons/stock/models/stock_move.py

To fix the error without removing the query, In the function calling get_bank_fc_query you have to check for empty lists first. In python that is very easy, becouse everything that is empty equals False, so do this:

if not fc_journal_list:
    raise exceptions.UserError('fc_journal_list cannot be empty')

query = self.get_bank_fc_query(fc_journal_list, date_start, date_end,company_domain
....

Answer 2

The error has nothing to do with Odoo.

psycopg2.errors.SyntaxError: syntax error at or near ")" LINE 9: WHERE abs.journal_id IN ()

It's cleary a syntax error in the query itself. You're using the IN operator without having a value list afterwards.

Your fc_journal_list parameter doesn't have values on your call. You should catch an empty list before creating the query.

And then there are atleast 2 big security risks in your code:

1. never ever use string formatting for querys, the comment under your question already points to variables in SQL queries that's the common mistake to make SQL injections an easy thing...

2. don't make such security risky methods (here both query returning methods) public to the odoo external API. Just add a _ at the beginning of the method names and you're fine on that part.