def convert_domain(self, domain, tables, Model):
name, operator, value = domain[:3]
if '.' not in name:
return super().convert_domain(domain, tables, Model)
database = Transaction().database
table, _ = tables[None]
name, key = name.split('.', 1)
Operator = SQL_OPERATORS[operator]
raw_column = self.sql_column(table)
column = self._domain_column(operator, raw_column, key)
expression = Operator(column, self._domain_value(operator, value))
if operator in {'=', '!='}:
# Try to use custom operators in case there is indexes
try:
if value is None:
expression = database.json_key_exists(raw_column, key)
if operator == '=':
expression = operators.Not(expression)
# we compare on multi-selection by doing an equality check and
# not a contain check
elif not isinstance(value, (list, tuple)):
expression = database.json_contains(
raw_column, dumps({key: value}))
if operator == '!=':
expression = operators.Not(expression)
expression &= database.json_key_exists(raw_column, key)
return expression
except NotImplementedError:
pass
elif operator.endswith('in'):
# Try to use custom operators in case there is indexes
if not value:
expression = Literal(operator.startswith('not'))
else:
op = '!=' if operator.startswith('not') else '='
try:
in_expr = Literal(False)
for v in value:
in_expr |= database.json_contains(
self._domain_column(op, raw_column, key), dumps(v))
if operator.startswith('not'):
in_expr = ~in_expr
expression = in_expr
except NotImplementedError:
pass
expression = self._domain_add_null(column, operator, value, expression)
return expression
def search_receivable_payable(cls, name, clause):
pool = Pool()
MoveLine = pool.get('account.move.line')
Account = pool.get('account.account')
User = pool.get('res.user')
Date = pool.get('ir.date')
line = MoveLine.__table__()
account = Account.__table__()
if name not in ('receivable', 'payable', 'receivable_today',
'payable_today'):
raise Exception('Bad argument')
_, operator, value = clause
user = User(Transaction().user)
if not user.company:
return []
company_id = user.company.id
code = name
today_query = Literal(True)
if name in ('receivable_today', 'payable_today'):
code = name[:-6]
today_query = ((line.maturity_date <= Date.today())
| (line.maturity_date == Null))
Operator = fields.SQL_OPERATORS[operator]
# Need to cast numeric for sqlite
cast_ = MoveLine.debit.sql_cast
amount = cast_(Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0)))
if operator in {'in', 'not in'}:
value = [cast_(Literal(Decimal(v or 0))) for v in value]
else:
value = cast_(Literal(Decimal(value or 0)))
query = line.join(account,
condition=account.id == line.account).select(
line.party,
where=(account.kind == code)
& (line.party != Null)
& (line.reconciliation == Null)
& (account.company == company_id)
& today_query,
group_by=line.party,
having=Operator(amount, value))
return [('id', 'in', query)]
def convert_domain(self, domain, tables, Model):
from ..modelsql import convert_from
pool = Pool()
Rule = pool.get('ir.rule')
Target = self.get_target()
transaction = Transaction()
table, _ = tables[None]
name, operator, value = domain[:3]
if Target._history and transaction.context.get('_datetime'):
target = Target.__table_history__()
history_where = (Coalesce(target.write_date, target.create_date) <=
transaction.context['_datetime'])
else:
target = Target.__table__()
history_where = None
origin_field = Target._fields[self.field]
origin = getattr(Target, self.field).sql_column(target)
origin_where = None
if origin_field._type == 'reference':
origin_where = origin.like(Model.__name__ + ',%')
origin = Cast(
Substring(origin,
Position(',', origin) + Literal(1)),
Target.id.sql_type().base)
if '.' not in name:
if value is None:
where = origin != value
if history_where:
where &= history_where
if origin_where:
where &= origin_where
query = target.select(origin, where=where)
expression = ~table.id.in_(query)
if operator == '!=':
return ~expression
return expression
else:
if isinstance(value, basestring):
target_name = 'rec_name'
else:
target_name = 'id'
else:
_, target_name = name.split('.', 1)
target_domain = [(target_name, ) + tuple(domain[1:])]
if origin_field._type == 'reference':
target_domain.append((self.field, 'like', Model.__name__ + ',%'))
rule_domain = Rule.domain_get(Target.__name__, mode='read')
if rule_domain:
target_domain = [target_domain, rule_domain]
target_tables = {
None: (target, None),
}
tables, expression = Target.search_domain(target_domain,
tables=target_tables)
query_table = convert_from(None, target_tables)
query = query_table.select(origin, where=expression)
return table.id.in_(query)
def test_not(self):
for not_ in [Not(self.table.c), ~self.table.c]:
self.assertEqual(str(not_), '(NOT "c")')
self.assertEqual(not_.params, ())
not_ = Not(Literal(False))
self.assertEqual(str(not_), '(NOT %s)')
self.assertEqual(not_.params, (False,))
def count_ip(cls):
cursor = Transaction().connection.cursor()
table = cls.__table__()
_, ip_network = cls.ipaddress()
cursor.execute(*table.select(Count(Literal('*')),
where=(table.ip_network == str(ip_network))
& (table.create_date >= cls.delay())))
return cursor.fetchone()[0]
def test_less(self):
less = Less(self.table.c1, self.table.c2)
self.assertEqual(str(less), '("c1" < "c2")')
self.assertEqual(less.params, ())
less = Less(Literal(0), self.table.c2)
self.assertEqual(str(less), '(%s < "c2")')
self.assertEqual(less.params, (0, ))
def test_and(self):
and_ = And((self.table.c1, self.table.c2))
self.assertEqual(str(and_), '("c1" AND "c2")')
self.assertEqual(and_.params, ())
and_ = And((Literal(True), self.table.c2))
self.assertEqual(str(and_), '(%s AND "c2")')
self.assertEqual(and_.params, (True, ))
def test_set_sql_value(self):
"Test cannot set SQL value"
Integer = Pool().get('test.integer_default')
integer = Integer()
with self.assertRaises(ValueError):
integer.integer = Literal(42)
def __setup__(cls):
super(ModelExclude, cls).__setup__()
t = cls.__table__()
cls._sql_constraints = [
('exclude', Exclude(t, (t.value, Equal),
where=t.condition == Literal(True)),
"Value must be unique."),
]
def test_set_sql_value(self):
"Test cannot set SQL value"
Numeric = Pool().get('test.numeric')
numeric = Numeric()
with self.assertRaises(ValueError):
numeric.numeric = Literal(42)
def test_set_sql_value(self):
"Test cannot set SQL value"
Char = self.Char()
char = Char()
with self.assertRaises(ValueError):
char.char = Literal('Foo')
def test_set_sql_value(self):
"Test cannot set SQL value"
Float = Pool().get('test.float')
float_ = Float()
with self.assertRaises(ValueError):
float_.float = Literal(42)
def count(cls, login, device_cookie=None):
cursor = Transaction().connection.cursor()
table = cls.__table__()
cursor.execute(*table.select(Count(Literal('*')),
where=(table.login == login)
& (table.device_cookie == device_cookie)
& (table.create_date >= cls.delay())))
return cursor.fetchone()[0]
def test_set_sql_value(self):
"Test cannot set SQL value"
Boolean = Pool().get('test.boolean')
boolean = Boolean()
with self.assertRaises(ValueError):
boolean.boolean = Literal(True)
def convert_domain(self, domain, tables, Model):
"Return a SQL expression for the domain using tables"
table, _ = tables[None]
name, operator, value = domain
assert name == self.name
method = getattr(Model, 'domain_%s' % name, None)
if method:
return method(domain, tables)
Operator = SQL_OPERATORS[operator]
column = self.sql_column(table)
expression = Operator(column, self._domain_value(operator, value))
if isinstance(expression, operators.In) and not expression.right:
expression = Literal(False)
elif isinstance(expression, operators.NotIn) and not expression.right:
expression = Literal(True)
expression = self._domain_add_null(column, operator, value, expression)
return expression
def test_set_sql_value(self):
"Test cannot set SQL value"
Binary = Pool().get('test.binary')
binary = Binary()
with self.assertRaises(ValueError):
binary.binary = Literal('foo')
def table_query(cls):
pool = Pool()
Line = pool.get('attendance.sheet.line')
line = Line.__table__()
return line.select(
(Min(line.id * 2)).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
line.company.as_('company'),
line.employee.as_('employee'),
Sum(line.duration).as_('duration'),
line.date.as_('date'),
group_by=[line.company, line.employee, line.date])
def test_select_group_by(self):
column = self.table.c
query = self.table.select(column, group_by=column)
self.assertEqual(str(query),
'SELECT "a"."c" FROM "t" AS "a" GROUP BY "a"."c"')
self.assertEqual(query.params, ())
output = column.as_('c1')
query = self.table.select(output, group_by=output)
self.assertEqual(str(query),
'SELECT "a"."c" AS "c1" FROM "t" AS "a" GROUP BY "c1"')
self.assertEqual(query.params, ())
query = self.table.select(Literal('foo'), group_by=Literal('foo'))
self.assertEqual(str(query),
'SELECT %s FROM "t" AS "a" GROUP BY %s')
self.assertEqual(query.params, ('foo', 'foo'))
def get_receivable_payable(cls, parties, names):
'''
Function to compute receivable, payable (today or not) for party ids.
'''
result = {}
pool = Pool()
MoveLine = pool.get('account.move.line')
Account = pool.get('account.account')
AccountType = pool.get('account.account.type')
User = pool.get('res.user')
Date = pool.get('ir.date')
cursor = Transaction().connection.cursor()
line = MoveLine.__table__()
account = Account.__table__()
account_type = AccountType.__table__()
for name in names:
if name not in ('receivable', 'payable', 'receivable_today',
'payable_today'):
raise Exception('Bad argument')
result[name] = dict((p.id, Decimal('0.0')) for p in parties)
user = User(Transaction().user)
if not user.company:
return result
company_id = user.company.id
exp = Decimal(str(10.0**-user.company.currency.digits))
amount = Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0))
for name in names:
code = name
today_where = Literal(True)
if name in ('receivable_today', 'payable_today'):
code = name[:-6]
today_where = ((line.maturity_date <= Date.today())
| (line.maturity_date == Null))
for sub_parties in grouped_slice(parties):
sub_ids = [p.id for p in sub_parties]
party_where = reduce_ids(line.party, sub_ids)
cursor.execute(
*line.join(account, condition=account.id == line.account).
join(account_type,
condition=account.type == account_type.id).select(
line.party,
amount,
where=(getattr(account_type, code)
& (line.reconciliation == Null)
& (account.company == company_id)
& party_where
& today_where),
group_by=line.party))
for party, value in cursor.fetchall():
# SQLite uses float for SUM
if not isinstance(value, Decimal):
value = Decimal(str(value))
result[name][party] = value.quantize(exp)
return result
def get_active(cls, numbers, name):
pool = Pool()
Coupon = pool.get('sale.promotion.coupon')
Sale = pool.get('sale.sale')
Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number')
table = cls.__table__()
coupon = Coupon.__table__()
sale = Sale.__table__()
sale_number = Sale_Number.__table__()
context = Transaction().context
cursor = Transaction().connection.cursor()
party = context.get('party')
query = (table
.join(sale_number, 'LEFT',
condition=table.id == sale_number.number)
.join(coupon, condition=table.coupon == coupon.id))
if party:
query = query.join(sale, 'LEFT',
condition=(sale_number.sale == sale.id)
& (sale.party == party))
active = Case(
((coupon.number_of_use > 0) & (coupon.per_party),
Count(sale.id) < coupon.number_of_use),
((coupon.number_of_use > 0)
& ~Coalesce(coupon.per_party, False),
Count(sale_number.sale) < coupon.number_of_use),
else_=Literal(True))
else:
active = Case(
((coupon.number_of_use > 0)
& ~Coalesce(coupon.per_party, False),
Count(sale_number.sale) < coupon.number_of_use),
else_=Literal(True))
query = query.select(table.id, active,
group_by=[table.id, coupon.number_of_use, coupon.per_party])
result = {}
for sub_numbers in grouped_slice(numbers):
query.where = reduce_ids(table.id, map(int, sub_numbers))
cursor.execute(*query)
result.update(dict(cursor.fetchall()))
return result
def __register__(cls, module):
pool = Pool()
Party = pool.get('party.party')
Identifier = pool.get('party.identifier')
cursor = Transaction().connection.cursor()
party = Party.__table__()
address = cls.__table__()
identifier = Identifier.__table__()
super().__register__(module)
table_h = cls.__table_handler__(module)
party_h = Party.__table_handler__(module)
# Migrate from 6.2: replace siren and siret by identifier
if party_h.column_exist('siren'):
cursor.execute(
*identifier.insert([
identifier.party, identifier.type, identifier.code,
identifier.active
],
party.select(party.id,
Literal('fr_siren'),
party.siren,
party.active,
where=(party.siren != Null)
& (party.siren != ''))))
if table_h.column_exist('siret_nic'):
cursor.execute(*identifier.insert(
[
identifier.party, identifier.address, identifier.type,
identifier.code, identifier.active
],
address.join(party, condition=address.party == party.id).
select(address.party,
address.id,
Literal('fr_siret'),
Concat(party.siren, address.siret_nic),
address.active,
where=(address.siret_nic != Null)
& (address.siret_nic != '')
& (party.siren != Null)
& (party.siren != ''))))
table_h.drop_column('siret_nic')
party_h.drop_column('siren')
def _columns(cls, tables, withs):
line = tables['line']
currency_company = withs['currency_company']
currency_sale = withs['currency_sale']
revenue = cls.revenue.sql_cast(
Sum(line.quantity * line.unit_price * currency_company.rate /
currency_sale.rate))
return [
cls._column_id(tables, withs).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
line.company.as_('company'),
revenue.as_('revenue'),
Count(line.order, distinct=True).as_('number'),
]
def __setup__(cls):
super().__setup__()
t = cls.__table__()
cls._sql_constraints = [
('name_unique',
Exclude(t, (t.name, Equal), where=t.active == Literal(True)),
'web_shop.msg_shop_name_unique'),
]
def _amount_where(cls, tax_line, move_line, move):
context = Transaction().context
periods = context.get('periods', [])
where = super(Tax, cls)._amount_where(tax_line, move_line, move)
return ((where
& (tax_line.on_cash_basis == False)
| (tax_line.on_cash_basis == Null))
| ((tax_line.period.in_(periods) if periods else Literal(False))
& (tax_line.on_cash_basis == True)))
def convert_domain(self, domain, tables, Model):
if not self.translate:
return super(FieldTranslate,
self).convert_domain(domain, tables, Model)
table, _ = tables[None]
name, operator, value = domain
model, join, column = self._get_translation_column(Model, name)
column = Coalesce(NullIf(column, ''), self.sql_column(model))
column = self._domain_column(operator, column)
Operator = SQL_OPERATORS[operator]
assert name == self.name
where = Operator(column, self._domain_value(operator, value))
if isinstance(where, operators.In) and not where.right:
where = Literal(False)
elif isinstance(where, operators.NotIn) and not where.right:
where = Literal(True)
where = self._domain_add_null(column, operator, value, where)
return table.id.in_(join.select(model.id, where=where))
def test_create_with_sql_value(self):
"Test create binary with SQL value"
Binary = Pool().get('test.binary')
binary, = Binary.create([{
'binary': Literal('foo'),
}])
self.assertEqual(binary.binary, cast(b'foo'))
def test_update1(self):
query = self.table.update([self.table.c], ['foo'])
self.assertEqual(str(query), 'UPDATE "t" SET "c" = %s')
self.assertEqual(query.params, ('foo', ))
query.where = (self.table.b == Literal(True))
self.assertEqual(str(query),
'UPDATE "t" SET "c" = %s WHERE ("t"."b" = %s)')
self.assertEqual(query.params, ('foo', True))
def table_query(cls):
pool = Pool()
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
Move = pool.get('account.move')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
invoice = Invoice.__table__()
invoice_tax = InvoiceTax.__table__()
move = Move.__table__()
period = Period.__table__()
tax = Tax.__table__()
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear')))
if context.get('period'):
where &= (period.id == context.get('period'))
where &= ((tax.ec_sales_list_code != Null)
& (tax.ec_sales_list_code != ''))
where &= invoice.type == 'out'
return (invoice_tax
.join(invoice,
condition=invoice_tax.invoice == invoice.id)
.join(tax, condition=invoice_tax.tax == tax.id)
.join(move, condition=invoice.move == move.id)
.join(period, condition=move.period == period.id)
.select(
Max(invoice_tax.id).as_('id'),
Literal(0).as_('create_uid'),
Min(invoice_tax.create_date).as_('create_date'),
Literal(0).as_('write_uid'),
Max(invoice_tax.write_date).as_('write_date'),
invoice.tax_identifier.as_('company_tax_identifier'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
tax.ec_sales_list_code.as_('code'),
Sum(invoice_tax.base).as_('amount'),
invoice.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party_tax_identifier,
tax.ec_sales_list_code,
invoice.currency,
]))
def __setup__(cls):
super().__setup__()
t = cls.__table__()
cls._sql_constraints = [
('country_code_unique',
Exclude(t, (t.country_code, Equal),
where=t.active == Literal(True)),
'party.msg_address_subdivision_country_code_unique')
]
cls._order.insert(0, ('country_code', 'ASC NULLS LAST'))
def test_select_union_order(self):
query1 = self.table.select()
query2 = Table('t2').select()
union = query1 | query2
union.order_by = Literal(1)
self.assertEqual(str(union),
'SELECT * FROM "t" AS "a" UNION '
'SELECT * FROM "t2" AS "b" '
'ORDER BY %s')
self.assertEqual(union.params, (1,))
