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
CodePudding user response:
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
CodePudding user response:
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:
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...
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.