Question 113798 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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
....
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search