postgresql Odoo 14,尝试在资金模块中计算银行余额时发出

xzv2uavs  于 2022-11-23  发布在  PostgreSQL
关注(0)|答案(2)|浏览(157)

我得到了工作,以修复错误时,计算,但我仍然不知道如何修复它,因为我仍然是新手
Odoo服务器错误
回溯(最近的呼叫排在最后):文件“/home/equipAccounting/equipment/odoo/addons/base/models/ir_http.py”,第237行,在分派结果=请求.分派()文件“/home/equipAccounting/equipment/odoo/http.py“,第683行,在分派结果=自身._call_function中(**self.params)文件“/home/equipAccounting/equip/odoo/http.py”,第359行,在调用函数中返回已检查的调用(self.db,args,*kwargs)文件“/home/equipAccounting/equip/odoo/service/model.py“,第94行,在 Package 器中返回f(dbname,args,*kwargs)文件“/home/equipAccounting/equip/odoo/http.py“,第347行,在checked_call result = self.endpoint中(*a,**kw)文件“/home/equipAccounting/equiple/odoo/http.py“,第912行,在调用中返回自身。方法(*args,**kw)文件“/home/equipAccounting/equiple/odoo/http.py“,第531行,在响应中返回响应= f(*args,**kw)文件“/home/equipAccounting/equiple/addons/basic/web/controllers/main.py“,第1393行,在调用按钮中,操作=自身。调用kw(模型,方法,参数,kwargs)文件“/home/equipAccounting/equiple/addons/basic/web/controllers/main.py”,第1381行,在调用kw中,返回调用kw(请求.env[模型],方法,参数,kwargs)文件“/home/equipAccounting/equiple/odoo/api.py“,第396行,在调用kw中,结果=调用kw_multi(方法,模型,参数,kwargs)文件“/home/equipAccounting/equipodoo/api.py”,第383行,在_call_kw_multi结果=方法中(recs,args,*kwargs)文件“/home/equipAccounting/equipaddons/core/财政部预测/模型/财政部银行预测.py”,第290行,在执行(主查询)文件“/usr/local/lib/python3.8/dist-packages/decorator.py“中,第232行,在函数返回调用程序中文件“/home/equipAccounting/equipAccounting/odoo/sql_db.py”,第101行,在检查返回f(self,args,*kwargs)文件“/home/equipAccounting/equipAccounting/odoo/sql_db.py”,第298行,在执行res = self._obj.execute(query,params)时出现异常
上述异常是以下异常的直接原因:
回溯(最近的呼叫排在最后):文件“/home/equipAccounting/equiple/odoo/http.py“,第639行,在异常处理中返回super(JsonRequest,self)。异常处理(exception)文件“/home/equipAccounting/equiple/odoo/http.py”,第315行,在异常处理中引发异常。第9行“)”处或其附近出现语法错误:
WHERE绝对日记帐标识IN()
代码如下:

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

先谢了

3b6akqbq

3b6akqbq1#

该错误与Odoo无关。
psycopg2.errors.SyntaxError:第9行“)”处或其附近出现语法错误:WHERE绝对日记帐标识IN()
很明显,查询本身存在语法错误。您使用了IN操作符,但后面没有值列表。
您的fc_journal_list参数在您的呼叫中没有值。您应该在建立查询之前拦截空白清单。
此外,您的代码中至少存在两个大的安全风险:
1.永远不要在查询中使用字符串格式,您的问题下面的注解已经指向variables in SQL queries,这是使SQL注入变得容易的常见错误...
1.不要让这些有安全风险的方法(这里都是返回查询的方法)公开给odoo外部API,只要在方法名的开头加上一个_就可以了。

e4yzc0pl

e4yzc0pl2#

Odoo有一个非常强大的ORM API来做psql查询。你有什么好的理由用sql来代替吗?
您需要的函数是:Read,用于选择您使用的字段; searchfiltered,用于筛选结果。
我建议阅读下面的教程。https://www.odoo.com/documentation/14.0/developer/reference/addons/orm.html#search-read
我还看了一下odoo源代码里面的好例子,我认为stock模块是查看一些例子的好地方。https://github.com/odoo/odoo/blob/14.0/addons/stock/models/stock_move.py
要在不删除查询的情况下修复错误,在调用get_bank_fc_query的函数中,你必须首先检查空列表。在python中这很容易,因为所有空列表都等于False,所以这样做:

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
....

相关问题