mirror of
https://github.com/NaN-tic/trytond-account_reports.git
synced 2023-12-14 05:43:08 +01:00
181 lines
6.8 KiB
Python
181 lines
6.8 KiB
Python
# This file is part of account_reports for tryton. The COPYRIGHT file
|
|
# at the top level of this repository contains the full copyright notices and
|
|
# license terms.
|
|
from decimal import Decimal
|
|
from sql.aggregate import Sum
|
|
from sql.conditionals import Coalesce
|
|
from sql.operators import In
|
|
|
|
from trytond.pool import Pool, PoolMeta
|
|
from trytond.tools import reduce_ids
|
|
from trytond.transaction import Transaction
|
|
|
|
__all__ = ['FiscalYear', 'Account', 'Party']
|
|
|
|
|
|
class FiscalYear(metaclass=PoolMeta):
|
|
__name__ = 'account.fiscalyear'
|
|
|
|
def get_periods(self, start_period, end_period):
|
|
pool = Pool()
|
|
Period = pool.get('account.period')
|
|
domain = [('fiscalyear', '=', self)]
|
|
if start_period:
|
|
domain += [('start_date', '>=', start_period.start_date)]
|
|
domain += [('end_date', '>=', start_period.end_date)]
|
|
if end_period:
|
|
domain += [('start_date', '<=', end_period.start_date)]
|
|
domain += [('end_date', '<=', end_period.end_date)]
|
|
|
|
periods = Period.search(domain)
|
|
return periods
|
|
|
|
|
|
class AccountTemplate(metaclass=PoolMeta):
|
|
__name__ = 'account.account.template'
|
|
|
|
@classmethod
|
|
def __setup__(cls):
|
|
super(AccountTemplate, cls).__setup__()
|
|
# hide because used in odt report
|
|
cls.general_ledger_balance.states['invisible'] = True
|
|
|
|
|
|
class Account(metaclass=PoolMeta):
|
|
__name__ = 'account.account'
|
|
|
|
@classmethod
|
|
def __setup__(cls):
|
|
super(Account, cls).__setup__()
|
|
# hide because used in odt report
|
|
cls.general_ledger_balance.states['invisible'] = True
|
|
|
|
@classmethod
|
|
def read_account_vals(cls, accounts, with_moves=False,
|
|
exclude_party_moves=False):
|
|
pool = Pool()
|
|
Account = pool.get('account.account')
|
|
Move = pool.get('account.move')
|
|
MoveLine = pool.get('account.move.line')
|
|
line = MoveLine.__table__()
|
|
move = Move.__table__()
|
|
table_a = Account.__table__()
|
|
table_c = Account.__table__()
|
|
|
|
in_max = 3000
|
|
values = {}
|
|
transaction = Transaction()
|
|
cursor = transaction.connection.cursor()
|
|
move_join = 'INNER' if with_moves else 'LEFT'
|
|
if not accounts:
|
|
accounts = Account.search([
|
|
('company', '=', transaction.context.get('company')),
|
|
])
|
|
account_ids = [a.id for a in accounts]
|
|
group_by = (table_a.id,)
|
|
columns = (group_by + (Sum(Coalesce(line.debit, 0)).as_('debit'),
|
|
Sum(Coalesce(line.credit, 0)).as_('credit'),
|
|
(Sum(Coalesce(line.debit, 0)) -
|
|
Sum(Coalesce(line.credit, 0))).as_('balance')))
|
|
for i in range(0, len(account_ids), in_max):
|
|
sub_ids = account_ids[i:i + in_max]
|
|
red_sql = reduce_ids(table_a.id, sub_ids)
|
|
where = red_sql
|
|
periods = transaction.context.get('periods', False)
|
|
if periods:
|
|
periods.append(0)
|
|
where = (where & In(Coalesce(move.period, 0), periods))
|
|
date = transaction.context.get('date')
|
|
if date:
|
|
where &= (move.date <= date)
|
|
if exclude_party_moves:
|
|
# This "where" not use account kind (before a change use it)
|
|
# because there are some companies that the accounts kind and
|
|
# party_required use in a different way that "standard".
|
|
# For example if you check the prty_required an account with
|
|
# the kind equal to 'other'
|
|
where = (where & (line.party == None))
|
|
|
|
cursor.execute(*table_a.join(table_c,
|
|
condition=(table_c.left >= table_a.left)
|
|
& (table_c.right <= table_a.right)
|
|
).join(line, move_join,
|
|
condition=line.account == table_c.id
|
|
).join(move, move_join,
|
|
condition=move.id == line.move
|
|
).select(*columns, where=where, group_by=group_by))
|
|
|
|
for account, debit, credit, balance in cursor.fetchall():
|
|
# SQLite uses float for SUM
|
|
if not isinstance(credit, Decimal):
|
|
credit = Decimal(str(credit))
|
|
if not isinstance(debit, Decimal):
|
|
debit = Decimal(str(debit))
|
|
if not isinstance(balance, Decimal):
|
|
balance = Decimal(str(balance))
|
|
values[account] = {
|
|
'credit': credit,
|
|
'debit': debit,
|
|
'balance': balance,
|
|
}
|
|
return values
|
|
|
|
|
|
class Party(metaclass=PoolMeta):
|
|
__name__ = 'party.party'
|
|
|
|
@classmethod
|
|
def get_account_values_by_party(cls, parties, accounts, company):
|
|
'''
|
|
Function to compute credit,debit and balance for party ids.
|
|
'''
|
|
res = {}
|
|
pool = Pool()
|
|
Move = pool.get('account.move')
|
|
MoveLine = pool.get('account.move.line')
|
|
Account = pool.get('account.account')
|
|
transaction = Transaction()
|
|
context = transaction.context
|
|
cursor = transaction.connection.cursor()
|
|
|
|
move = Move.__table__()
|
|
line = MoveLine.__table__()
|
|
account = Account.__table__()
|
|
|
|
group_by = (line.party, line.account,)
|
|
columns = (group_by + (Sum(Coalesce(line.debit, 0)).as_('debit'),
|
|
Sum(Coalesce(line.credit, 0)).as_('credit'),
|
|
(Sum(Coalesce(line.debit, 0)) -
|
|
Sum(Coalesce(line.credit, 0))).as_('balance')))
|
|
line_query, _ = MoveLine.query_get(line)
|
|
if context.get('date'):
|
|
# Cumulate data from previous fiscalyears
|
|
line_query = line.move.in_(move.select(move.id,
|
|
where=move.date <= context.get('date')))
|
|
where = (line_query &
|
|
(account.company == company.id))
|
|
if accounts:
|
|
where = where & line.account.in_([a.id for a in accounts])
|
|
if parties:
|
|
where = where & line.party.in_([p.id for p in parties])
|
|
cursor.execute(*line.join(account,
|
|
condition=(line.account == account.id)
|
|
).select(*columns, where=where, group_by=group_by))
|
|
|
|
for party, account, debit, credit, balance in cursor.fetchall():
|
|
# SQLite uses float for SUM
|
|
if not isinstance(credit, Decimal):
|
|
credit = Decimal(str(credit))
|
|
if not isinstance(debit, Decimal):
|
|
debit = Decimal(str(debit))
|
|
if not isinstance(balance, Decimal):
|
|
balance = Decimal(str(balance))
|
|
|
|
if account not in res:
|
|
res[account] = {}
|
|
res[account][party] = {
|
|
'credit': credit,
|
|
'debit': debit,
|
|
'balance': balance,
|
|
}
|
|
return res
|