def search_payment_amount(cls, name, clause):
pool = Pool()
Payment = pool.get('account.payment')
Account = pool.get('account.account')
_, operator, value = clause
Operator = fields.SQL_OPERATORS[operator]
table = cls.__table__()
payment = Payment.__table__()
account = Account.__table__()
payment_amount = Sum(Coalesce(payment.amount, 0))
main_amount = Abs(table.credit - table.debit) - payment_amount
second_amount = Abs(table.amount_second_currency) - payment_amount
amount = Case((table.second_currency == Null, main_amount),
else_=second_amount)
value = cls.payment_amount.sql_format(value)
query = table.join(
payment,
type_='LEFT',
condition=(table.id == payment.line) &
(payment.state != 'failed')).join(
account, condition=table.account == account.id).select(
table.id,
where=account.kind.in_(['payable', 'receivable']),
group_by=(table.id, account.kind, table.second_currency),
having=Operator(amount, value))
return [('id', 'in', query)]
def test_abs(self):
abs_ = Abs(self.table.c1)
self.assertEqual(str(abs_), 'ABS("c1")')
self.assertEqual(abs_.params, ())
abs_ = Abs(-12)
self.assertEqual(str(abs_), 'ABS(%s)')
self.assertEqual(abs_.params, (-12, ))
def test_mapping(self):
class MyAbs(Function):
_function = 'MY_ABS'
params = ('test', )
abs_ = Abs(self.table.c1)
flavor = Flavor(function_mapping={
Abs: MyAbs,
})
Flavor.set(flavor)
try:
self.assertEqual(str(abs_), 'MY_ABS("c1")')
self.assertEqual(abs_.params, ('test', ))
finally:
Flavor.set(Flavor())
def test_mapping(self):
class MyAbs(Function):
_function = 'MY_ABS'
params = ('test', )
class MyOverlay(FunctionKeyword):
_function = 'MY_OVERLAY'
_keywords = ('', 'PLACING', 'FROM', 'FOR')
class MyCurrentTime(FunctionNotCallable):
_function = 'MY_CURRENT_TIME'
class MyTrim(Trim):
_function = 'MY_TRIM'
abs_ = Abs(self.table.c1)
overlay = Overlay(self.table.c1, 'test', 2)
current_time = CurrentTime()
trim = Trim(' test ')
flavor = Flavor(
function_mapping={
Abs: MyAbs,
Overlay: MyOverlay,
CurrentTime: MyCurrentTime,
Trim: MyTrim,
})
Flavor.set(flavor)
try:
self.assertEqual(str(abs_), 'MY_ABS("c1")')
self.assertEqual(abs_.params, ('test', ))
self.assertEqual(str(overlay),
'MY_OVERLAY("c1" PLACING %s FROM %s)')
self.assertEqual(overlay.params, ('test', 2))
self.assertEqual(str(current_time), 'MY_CURRENT_TIME')
self.assertEqual(current_time.params, ())
self.assertEqual(str(trim), 'MY_TRIM(BOTH %s FROM %s)')
self.assertEqual(trim.params, (
' ',
' test ',
))
finally:
Flavor.set(Flavor())
def test_insert_function(self):
query = self.table.insert([self.table.c], [[Abs(-1)]])
self.assertEqual(str(query), 'INSERT INTO "t" ("c") VALUES (ABS(%s))')
self.assertEqual(query.params, (-1, ))
def get_amount(cls, invoices, names):
pool = Pool()
InvoiceTax = pool.get('account.withholding.tax')
Move = pool.get('account.move')
MoveLine = pool.get('account.move.line')
cursor = Transaction().cursor
untaxed_amount = dict((i.id, _ZERO) for i in invoices)
tax_amount = dict((i.id, _ZERO) for i in invoices)
total_amount = dict((i.id, _ZERO) for i in invoices)
type_name = cls.tax_amount._field.sql_type().base
tax = InvoiceTax.__table__()
to_round = False
for sub_ids in grouped_slice(invoices):
red_sql = reduce_ids(tax.withholding, sub_ids)
cursor.execute(
*tax.select(tax.withholding,
Coalesce(Sum(tax.amount), 0).as_(type_name),
where=red_sql,
group_by=tax.withholding))
for invoice_id, sum_ in cursor.fetchall():
# SQLite uses float for SUM
if not isinstance(sum_, Decimal):
sum_ = Decimal(str(sum_))
to_round = True
tax_amount[invoice_id] = sum_
# Float amount must be rounded to get the right precision
if to_round:
for invoice in invoices:
tax_amount[invoice.id] = invoice.currency.round(
tax_amount[invoice.id])
invoices_move = set()
invoices_no_move = set()
for invoice in invoices:
if invoice.move:
invoices_move.add(invoice.id)
else:
invoices_no_move.add(invoice.id)
invoices_move = cls.browse(invoices_move)
invoices_no_move = cls.browse(invoices_no_move)
type_name = cls.total_amount._field.sql_type().base
invoice = cls.__table__()
move = Move.__table__()
line = MoveLine.__table__()
to_round = False
for sub_ids in grouped_slice(invoices_move):
red_sql = reduce_ids(invoice.id, sub_ids)
cursor.execute(
*invoice.join(move, condition=invoice.move == move.id).join(
line, condition=move.id == line.move).select(
invoice.id,
Coalesce(
Sum(
Case((line.second_currency == invoice.currency,
Abs(line.amount_second_currency) *
Sign(line.debit - line.credit)),
else_=line.debit -
line.credit)), 0).cast(type_name),
where=(invoice.account == line.account) & red_sql,
group_by=invoice.id))
for invoice_id, sum_ in cursor.fetchall():
# SQLite uses float for SUM
if not isinstance(sum_, Decimal):
sum_ = Decimal(str(sum_))
to_round = True
total_amount[invoice_id] = sum_
for invoice in invoices_move:
# Float amount must be rounded to get the right precision
if to_round:
total_amount[invoice.id] = invoice.currency.round(
total_amount[invoice.id])
untaxed_amount[invoice.id] = (total_amount[invoice.id] -
tax_amount[invoice.id])
for invoice in invoices_no_move:
total_amount[invoice.id] = (untaxed_amount[invoice.id] +
tax_amount[invoice.id])
result = {
'untaxed_amount': untaxed_amount,
'tax_amount': tax_amount,
'total_amount': total_amount,
}
for key in result.keys():
if key not in names:
del result[key]
return result