Hi,
I’m working on adding a custom filter “company_number”, a field that was added to “account.invoice”, all this in Journal Cash report
class Journal(metaclass=PoolMeta):
__name__ = 'account.journal'
@classmethod
def get_debit_credit_balance(cls, journals, names):
pool = Pool()
MoveLine = pool.get('account.move.line')
Move = pool.get('account.move')
Account = pool.get('account.account')
AccountType = pool.get('account.account.type')
Company = pool.get('company.company')
Invoice = pool.get('account.invoice')
invoice = Invoice.__table__()
context = Transaction().context
cursor = Transaction().connection.cursor()
company_number = context.get('company_number')
result = {}
ids = [j.id for j in journals]
for name in ['debit', 'credit', 'balance']:
result[name] = dict.fromkeys(ids, 0)
company_id = Transaction().context.get('company')
if not company_id:
return result
company = Company(company_id)
line = MoveLine.__table__()
move = Move.__table__()
account = Account.__table__()
account_type = AccountType.__table__()
where = (
(move.date >= context.get('start_date')) &
(move.date <= context.get('end_date')) &
~Coalesce(account_type.receivable, False) &
~Coalesce(account_type.payable, False) &
(move.company == company.id)
)
if company_number:
where &= (
(invoice.move != Null) &
(move.origin != Null) &
(invoice.company_number == company_number)
)
for sub_journals in grouped_slice(journals):
sub_journals = list(sub_journals)
red_sql = reduce_ids(move.journal, [j.id for j in sub_journals])
origin_value = 'account.invoice,' + str(invoice.id)
query = (line
.join(move, 'LEFT', condition=line.move == move.id)
.join(invoice, 'LEFT',
condition=move.origin == origin_value)
.join(account, 'LEFT', condition=line.account == account.id)
.join(account_type, 'LEFT', condition=account.type == account_type.id)
.select(move.journal, Sum(line.debit), Sum(line.credit),
where=where & red_sql,
group_by=move.journal)
)
sql, params = query
print(sql)
print(params)
cursor.execute(*query)
for journal_id, debit, credit in cursor:
# SQLite uses float for SUM
if not isinstance(debit, Decimal):
debit = Decimal(str(debit))
if not isinstance(credit, Decimal):
credit = Decimal(str(credit))
result['debit'][journal_id] = company.currency.round(debit)
result['credit'][journal_id] = company.currency.round(credit)
result['balance'][journal_id] = company.currency.round(
debit - credit)
return result
With this query it works
SELECT
b.journal,
SUM(a.debit),
SUM(a.credit)
FROM
account_move_line a
LEFT JOIN
account_move b ON a.move = b.id
LEFT JOIN
account_invoice c ON
(b.origin = 'account.invoice,' || c.id::TEXT)
LEFT JOIN
account_account d ON a.account = d.id
LEFT JOIN
account_account_type e ON d.type = e.id
WHERE
b.date >= '2025-05-14'
AND b.date <= '2025-05-14'
AND NOT COALESCE(e.receivable, false)
AND NOT COALESCE(e.payable, false)
AND b.company = 1
AND b.journal IN (1,2,3,4,5,6)
AND (c.company_number = '2')
GROUP BY
b.journal;
But when I execute the code the paramenter to origin is passed like
'account.invoice,“id” ’ not like ‘account.invoice,1000’
How to fix it?
Thanks in advance!