def _domain_value(self, operator, value):
value = super(Numeric, self)._domain_value(operator, value)
db_type = backend.name()
if db_type == 'sqlite':
if isinstance(value, (Select, CombiningQuery)):
return value
# Must be casted as Decimal is adapted to bytes
type_ = self.sql_type().base
if operator in ('in', 'not in'):
return [Cast(Literal(v), type_) for v in value]
elif value is not None:
return Cast(Literal(value), type_)
return value
def __register__(cls, module_name):
TableHandler = backend.TableHandler
cursor = Transaction().connection.cursor()
sql_table = cls.__table__()
code_exists = True
date_exists = True
if backend.TableHandler.table_exist(cls._table):
table = backend.TableHandler(cls, module_name)
code_exists = table.column_exist('code')
date_exists = table.column_exist('date')
super(Activity, cls).__register__(module_name)
table = backend.TableHandler(cls, module_name)
# Migration from 3.2: Remove type and direction fields
table.not_null_action('type', action='remove')
table.not_null_action('direction', action='remove')
# Migration from 3.2: Add code field
if (not code_exists and table.column_exist('type') and
table.column_exist('direction')):
cursor.execute(*sql_table.update(
columns=[sql_table.code],
values=[sql_table.id],
where=sql_table.code == Null))
table.not_null_action('code', action='add')
# Migration from 3.4.1: subject is no more required
table.not_null_action('subject', 'remove')
# Migration from 5.2
if not date_exists:
cursor.execute(*sql_table.update(
columns=[sql_table.date, sql_table.time],
values=[Cast(sql_table.dtstart, 'DATE'),
Cast(sql_table.dtstart, 'TIME')]))
cursor.execute(*sql_table.update(
columns=[sql_table.duration],
values=[sql_table.dtend - sql_table.dtstart],
where=sql_table.dtend != Null))
cursor.execute(*sql_table.update(
columns=[sql_table.state],
values=['done'],
where=sql_table.state == 'held'))
cursor.execute(*sql_table.update(
columns=[sql_table.state],
values=['canceled'],
where=sql_table.state == 'not_held'))
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
type_ = MoveLine.debit.sql_type().base
amount = Cast(Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0)),
type_)
if operator in {'in', 'not in'}:
value = [Cast(Literal(Decimal(v or 0)), type_) for v in value]
else:
value = Cast(Literal(Decimal(value or 0)), type_)
query = line.join(account,
condition=account.id == line.account).select(
line.party,
where=account.active
& (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 get_lines_to_pay(cls, invoices, name):
pool = Pool()
Move = pool.get('account.move')
Line = pool.get('account.move.line')
Account = pool.get('account.account')
line = Line.__table__()
account = Account.__table__()
move = Move.__table__()
invoice = cls.__table__()
cursor = Transaction().connection.cursor()
_, origin_type = Move.origin.sql_type()
lines = super(Invoice, cls).get_lines_to_pay(invoices, name)
for sub_ids in grouped_slice(invoices):
red_sql = reduce_ids(invoice.id, sub_ids)
query = invoice.join(move,
condition=((move.origin == Concat('account.invoice,',
Cast(invoice.id, origin_type))))
).join(line, condition=(line.move == move.id)
).join(account, condition=(
(line.account == account.id) &
Case((invoice.type == 'out',
account.kind == 'receivable'),
else_=account.kind == 'payable'))).select(
invoice.id, line.id,
where=(line.maturity_date != None) & red_sql,
order_by=(invoice.id, line.maturity_date))
cursor.execute(*query)
for invoice_id, line_id in cursor.fetchall():
if line_id not in lines[invoice_id]:
lines[invoice_id].append(line_id)
return lines
def table_query(cls):
pool = Pool()
Sheet = pool.get('lims.analysis_sheet')
Compilation = pool.get('lims.interface.compilation')
sheet = Sheet.__table__()
compilation = Compilation.__table__()
context = Transaction().context
where = Literal(True)
if context.get('laboratory'):
where &= sheet.laboratory == context.get('laboratory')
if context.get('from_date'):
where &= compilation.date_time >= datetime.combine(
context.get('from_date'), time(0, 0))
if context.get('to_date'):
where &= compilation.date_time <= datetime.combine(
context.get('to_date'), time(23, 59))
columns = []
for fname, field in cls._fields.items():
if hasattr(field, 'set'):
continue
if fname == 'date':
column = Cast(compilation.date_time, 'date').as_(fname)
else:
column = Column(sheet, fname).as_(fname)
columns.append(column)
return sheet.join(compilation,
condition=sheet.compilation == compilation.id).select(*columns,
where=where)
def aeat_party_expression(cls, tables):
'''
Returns a couple of sql expression and tables used by sql query to
compute the aeat party.
'''
pool = Pool()
Invoice = pool.get('account.invoice')
table, _ = tables[None]
is_invoice = table.origin.like(Invoice.__name__ + ',%')
if 'invoice' in tables:
invoice, _ = tables['invoice']
else:
invoice = Invoice.__table__()
tables['invoice'] = {
None:
(invoice,
(is_invoice
& (invoice.id == Cast(
Substring(table.origin,
Position(',', table.origin) + Literal(1)),
Invoice.id.sql_type().base)))),
}
return Case((is_invoice, invoice.party), else_=Null), tables
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 get_condition(sql_type, clause, table):
operator = clause[1]
value = clause[2]
sql_types = [
Numeric('numeric').sql_type().base,
Integer('integer').sql_type().base,
Float('float').sql_type().base,
]
if sql_type in sql_types and value:
if isinstance(value, (list, tuple)):
value = [Cast(v, sql_type) for v in value]
else:
value = Cast(value, sql_type)
if value is None:
value = False
column = Cast(
Substring(table.value,
Position(',', table.value) + Literal(1)), sql_type)
Operator = SQL_OPERATORS[operator]
# All negative clauses will be negated later
if operator in ('in', 'not in'):
return column.in_(value)
elif ((value is False or value is None) and operator in ('=', '!=')):
return column != Null
elif operator == 'not like':
return column.like(value)
elif operator == 'not ilike':
return column.ilike(value)
elif operator == '!=':
return column == value
return Operator(column, value)
def _domain_column(self, operator, column, key=None):
database = Transaction().database
column = database.json_get(super()._domain_column(operator, column),
key)
if operator.endswith('like'):
column = Cast(column, database.sql_type('VARCHAR').base)
if self.search_unaccented and operator.endswith('ilike'):
column = database.unaccent(column)
return column
def union_columns(cls, model):
pool = Pool()
Model = pool.get(model)
table = Model.__table__()
columns = [cls.union_shard(table.id, model).as_('id')]
for name in sorted(cls._fields.keys()):
field = cls._fields[name]
if name == 'id' or hasattr(field, 'set'):
continue
column = cls.union_column(name, field, table, Model)
columns.append(Cast(column, field.sql_type().base).as_(name))
return table, columns
def search_payment_days(cls, name, clause):
Party = Pool().get('party.party')
if backend.name == 'sqlite':
# Sqlite does not support UNNEST function
return ['OR', [
('type', '=', 'out'),
('party.customer_payment_days',) + tuple(clause[1:]),
], [
('type', '=', 'in'),
('party.supplier_payment_days',) + tuple(clause[1:]),
]]
party = Party.__table__()
_, operator, value = clause
Operator = fields.SQL_OPERATORS[operator]
customer_days = party.select(party.id,
Unnest(RegExpSplitToArray(party.customer_payment_days,
r'\s+')).as_('day'))
customer_days = customer_days.select(customer_days.id,
where=(Operator(Cast(NullIf(customer_days.day, ''), 'int'),
value)))
supplier_days = party.select(party.id,
Unnest(RegExpSplitToArray(party.supplier_payment_days,
r'\s+')).as_('day'))
supplier_days = supplier_days.select(supplier_days.id,
where=(Operator(Cast(NullIf(supplier_days.day, ''), 'int'),
value)))
return ['OR', [
('type', '=', 'out'),
('party', 'in', customer_days),
], [
('type', '=', 'in'),
('party', 'in', supplier_days),
]]
def get_condition(sql_type, clause, table):
operator = clause[1]
value = clause[2]
sql_types = [
Numeric('numeric').sql_type().base,
Integer('integer').sql_type().base,
Float('float').sql_type().base,
]
if sql_type in sql_types and value:
if isinstance(value, (list, tuple)):
value = [Cast(v, sql_type) for v in value]
else:
value = Cast(value, sql_type)
if value is None:
value = False
column = Cast(Substring(table.value,
Position(',', table.value) + Literal(1)),
sql_type)
Operator = SQL_OPERATORS[operator]
# All negative clauses will be negated later
if operator in ('in', 'not in'):
return column.in_(value)
elif ((value is False or value is None)
and operator in ('=', '!=')):
return column != Null
elif operator == 'not like':
return column.like(value)
elif operator == 'not ilike':
return column.ilike(value)
elif operator == '!=':
return column == value
return Operator(column, value)
def clean_properties_from_4_2(cls):
from sql import Null, Table, Cast
from sql.operators import Like, Concat
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
property = Table('ir_property')
cursor = Transaction().connection.cursor()
cursor.execute(
*property.select(property.res, where=property.res != Null))
res_model_names = list(
set([x[0].split(',')[0] for x in cursor.fetchall()]))
to_delete = {}
for res_model_name in res_model_names:
table_name = res_model_name.replace('.', '_')
res_model = Table(table_name)
query_table = property.join(res_model,
'LEFT OUTER',
condition=(property.res == Concat(
res_model_name + ',',
Cast(res_model.id, 'VARCHAR'))))
cursor.execute(
*query_table.select(property.id,
where=Like(property.res, res_model_name +
',%') & (res_model.id == Null)))
property_ids = [x[0] for x in cursor.fetchall()]
if property_ids:
to_delete[res_model_name] = property_ids
if to_delete:
cursor.execute(*property.delete(
where=property.id.in_(sum([p
for p in to_delete.values()], []))))
for res_model_name, property_ids in to_delete.items():
if property_ids:
print '[%s] - %s Inconsistent record(s) removed' % (
res_model_name, len(property_ids))
else:
print 'Nothing to do - Exisiting property records are clean'
def convert_domain(self, domain, tables, Model):
if '.' not in domain[0]:
return super(Reference, self).convert_domain(domain, tables, Model)
pool = Pool()
name, operator, value, target = domain[:4]
Target = pool.get(target)
table, _ = tables[None]
name, target_name = name.split('.', 1)
assert name == self.name
column = self.sql_column(table)
target_domain = [(target_name,) + tuple(domain[1:3])
+ tuple(domain[4:])]
if 'active' in Target._fields:
target_domain.append(('active', 'in', [True, False]))
query = Target.search(target_domain, order=[], query=True)
return (Cast(Substring(column,
Position(',', column) + Literal(1)),
Model.id.sql_type().base).in_(query)
& column.like(target + ',%'))
def convert_domain(self, domain, tables, Model):
Target = self.get_target()
target = Target.__table__()
table, _ = tables[None]
name, operator, value = domain[:3]
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 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:])]
query = Target.search(target_domain, order=[], query=True)
where = target.id.in_(query)
if origin_where:
where &= origin_where
query = target.select(origin, where=where)
return table.id.in_(query)
def restore_default_party_lang_from_4_2(cls):
from trytond.transaction import Transaction
from sql import Null, Table, Cast
from sql.operators import Concat
from trytond.pool import Pool
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
pool = Pool()
property = Table('ir_property')
Lang = pool.get('ir.lang')
field = pool.get('ir.model.field').__table__()
lang = Lang.__table__()
cursor = Transaction().connection.cursor()
query_table = property.join(
lang,
condition=(property.value == Concat(
'ir.lang,',
Cast(lang.id,
'VARCHAR')))).join(field,
condition=((property.field == field.id)
& (field.name == 'lang')))
cursor.execute(
*query_table.select(lang.id, where=property.res == Null))
result = cursor.fetchone()
if result:
result = list(result)
default_lang = Lang(result[0])
print 'Default Language restored [%s]' % default_lang.rec_name
pool.get('party.configuration.party_lang').create([{
'party_lang':
default_lang
}])
else:
print 'No default language on party configuration found'
def convert_domain(self, domain, tables, Model):
from ..modelsql import convert_from
pool = Pool()
Rule = pool.get('ir.rule')
Target = self.get_target()
Relation = pool.get(self.relation_name)
transaction = Transaction()
table, _ = tables[None]
name, operator, value = domain[:3]
if Relation._history and transaction.context.get('_datetime'):
relation = Relation.__table_history__()
history_where = (Coalesce(relation.write_date,
relation.create_date) <=
transaction.context['_datetime'])
else:
relation = Relation.__table__()
history_where = None
origin_field = Relation._fields[self.origin]
origin = getattr(Relation, self.origin).sql_column(relation)
origin_where = None
if origin_field._type == 'reference':
origin_where = origin.like(Model.__name__ + ',%')
origin = Cast(
Substring(origin,
Position(',', origin) + Literal(1)),
Relation.id.sql_type().base)
target = getattr(Relation, self.target).sql_column(relation)
if '.' not in name:
if operator in ('child_of', 'not child_of'):
if Target != Model:
query = Target.search([(domain[3], 'child_of', value)],
order=[],
query=True)
where = (target.in_(query) & (origin != Null))
if history_where:
where &= history_where
if origin_where:
where &= origin_where
query = relation.select(origin, where=where)
expression = table.id.in_(query)
if operator == 'not child_of':
return ~expression
return expression
if isinstance(value, basestring):
targets = Target.search([('rec_name', 'ilike', value)],
order=[])
ids = [t.id for t in targets]
elif not isinstance(value, (list, tuple)):
ids = [value]
else:
ids = value
if not ids:
expression = table.id.in_([None])
if operator == 'not child_of':
return ~expression
return expression
else:
return self.convert_domain_child((name, operator, ids),
tables)
if value is None:
where = origin != value
if history_where:
where &= history_where
if origin_where:
where &= origin_where
query = relation.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)
relation_domain = [
('%s.%s' % (self.target, target_name), ) + tuple(domain[1:])
]
if origin_field._type == 'reference':
relation_domain.append(
(self.origin, 'like', Model.__name__ + ',%'))
rule_domain = Rule.domain_get(Relation.__name__, mode='read')
if rule_domain:
relation_domain = [relation_domain, rule_domain]
relation_tables = {
None: (relation, None),
}
tables, expression = Relation.search_domain(relation_domain,
tables=relation_tables)
query_table = convert_from(None, relation_tables)
query = query_table.select(origin, where=expression)
return table.id.in_(query)
def test_cast(self):
for cast in [Cast(self.column, 'int'), self.column.cast('int')]:
self.assertEqual(str(cast), 'CAST("c" AS int)')
self.assertEqual(cast.params, ())
def json_contains(self, column, json):
return JSONContains(Cast(column, 'jsonb'), Cast(json, 'jsonb'))
def json_all_keys_exist(self, column, keys):
return JSONAllKeyExist(Cast(column, 'jsonb'), keys)
def json_any_keys_exist(self, column, keys):
return JSONAnyKeyExist(Cast(column, 'jsonb'), keys)
def json_key_exists(self, column, key):
return JSONKeyExists(Cast(column, 'jsonb'), key)
def json_get(self, column, key=None):
column = Cast(column, 'jsonb')
if key:
column = JSONBExtractPath(column, key)
return column
def union_shard(cls, column, model):
models = cls.union_models()
length = len(models)
i = models.index(model)
return ((Cast(column, 'BIGINT') * length) + i)
def sql_cast(self, expression):
return Cast(expression, self.sql_type().base)
def convert_domain(self, domain, tables, Model):
pool = Pool()
Rule = pool.get('ir.rule')
Property = pool.get('ir.property')
IrModel = pool.get('ir.model')
Field = pool.get('ir.model.field')
cursor = Transaction().connection.cursor()
name, operator, value = domain
sql_type = self._field.sql_type().base
property_cond = Rule.query_get('ir.property')
property_ = Property.__table__()
model_field = Field.__table__()
model = IrModel.__table__()
# Fetch res ids that comply with the domain
join = property_.join(model_field,
condition=model_field.id == property_.field)
join = join.join(model,
condition=model.id == model_field.model)
cond = ((model.model == Model.__name__)
& (model_field.name == name))
if property_cond:
cond &= property_.id.in_(property_cond)
cursor.execute(*join.select(
Cast(Substring(property_.res,
Position(',', property_.res) + Literal(1)),
Model.id.sql_type().base),
property_.id,
# Use a Case because the condition created by get_condition
# could result in an invalid Cast
where=Case(
(cond, self.get_condition(sql_type, domain, property_)),
else_=(Literal(1) == Literal(0)))))
props = cursor.fetchall()
default = None
for prop in props:
if not prop[0]:
default = prop[1]
break
if (not default
or ((value is False or value is None)
and operator in ['=', '!='])
or (operator in ['not like', 'not ilike', 'not in', '!='])):
dom_operator = 'in' # default operator
if (((value is False or value is None) and operator == '=')
or ((value is not False and value is not None)
and operator in [
'not like', 'not ilike', 'not in', '!='])):
dom_operator = 'not in'
return [('id', dom_operator, [x[0] for x in props])]
# Fetch the res ids that doesn't use the default value
cursor.execute(*property_.select(
Cast(Substring(property_.res,
Position(',', property_.res) + Literal(1)),
Model.id.sql_type().base),
where=property_cond & (property_.res != Null)))
fetchall = cursor.fetchall()
if not fetchall:
return [('id', 'in', [x[0] for x in props])]
else:
other_ids = [x[0] for x in fetchall]
res_ids = Model.search(['OR',
('id', 'in', [x[0] for x in props]),
('id', 'not in', other_ids)
])
return [('id', 'in', res_ids)]
def test_cast(self):
self.assertEqual(str(Cast(self.column, 'int')), 'CAST("c" AS int)')
def search_origin_shipment(cls, name, clause):
pool = Pool()
LineMove = pool.get('account.invoice.line-stock.move')
Move = pool.get('stock.move')
ShipmentOut = pool.get('stock.shipment.out')
ShipmentOutReturn = pool.get('stock.shipment.out.return')
ShipmentIn = pool.get('stock.shipment.in')
ShipmentInReturn = pool.get('stock.shipment.in.return')
invoice_line = cls.__table__()
line_move = LineMove.__table__()
move = Move.__table__()
shipment_out = ShipmentOut.__table__()
shipment_out_return = ShipmentOutReturn.__table__()
shipment_in = ShipmentIn.__table__()
shipment_in_return = ShipmentInReturn.__table__()
field, operator_, value = clause
Operator = fields.SQL_OPERATORS[operator_]
try:
locale = Transaction().context.get('locale')
format_date = (locale.get('date', '%Y-%m-%d')
if locale else '%Y-%m-%d')
value_date = (datetime.strptime(value.replace(
'%', ''), format_date).strftime('%Y-%m-%d') if value else None)
except ValueError:
value_date = None
if value_date:
if Operator in (operators.Like, operators.ILike):
Operator = operators.Equal
elif Operator in (operators.NotLike, operators.NotILike):
Operator = operators.NotEqual
sql_where = (
Operator(shipment_out.effective_date, value_date)
| Operator(shipment_out_return.effective_date, value_date)
| Operator(shipment_in.effective_date, value_date)
| Operator(shipment_in_return.effective_date, value_date))
else:
sql_where = (Operator(shipment_out.number, value)
| Operator(shipment_out_return.number, value)
| Operator(shipment_in.number, value)
| Operator(shipment_in_return.number, value))
query = invoice_line.join(
line_move,
condition=invoice_line.id == line_move.invoice_line).join(
move, condition=move.id == line_move.stock_move)
query = query.join(shipment_out,
'LEFT',
condition=Cast(
Substring(
move.shipment,
Position(',', move.shipment) + Literal(1)),
'INTEGER') == shipment_out.id)
query = query.join(shipment_out_return,
'LEFT',
condition=Cast(
Substring(
move.shipment,
Position(',', move.shipment) + Literal(1)),
'INTEGER') == shipment_out_return.id)
query = query.join(shipment_in,
'LEFT',
condition=Cast(
Substring(
move.shipment,
Position(',', move.shipment) + Literal(1)),
'INTEGER') == shipment_in.id)
query = query.join(shipment_in_return,
'LEFT',
condition=Cast(
Substring(
move.shipment,
Position(',', move.shipment) + Literal(1)),
'INTEGER') == shipment_in_return.id)
query = query.select(invoice_line.id, where=sql_where)
return [('id', 'in', query)]
def test_cast_no_expression(self):
cast = Cast(1.1, 'int')
self.assertEqual(str(cast), 'CAST(%s AS int)')
self.assertEqual(cast.params, (1.1, ))
def search_origin_reference(cls, name, clause):
pool = Pool()
Invoice = pool.get('account.invoice')
try:
SaleLine = pool.get('sale.line')
Sale = pool.get('sale.sale')
except:
Sale = None
try:
PurchaseLine = pool.get('purchase.line')
Purchase = pool.get('purchase.purchase')
except:
Purchase = None
invoice_type = Transaction().context.get('invoice_type', 'both')
invoice_line = cls.__table__()
invoice_line2 = cls.__table__()
invoice = Invoice.__table__()
if Sale:
sale_line = SaleLine.__table__()
sale = Sale.__table__()
if Purchase:
purchase_line = PurchaseLine.__table__()
purchase = Purchase.__table__()
field, operator_, value = clause
if operator_ == '!=':
PYSQL_CONDITION = 'not'
elif operator_ == '=':
PYSQL_CONDITION = 'and'
elif operator_ == 'not ilike':
PYSQL_CONDITION = 'not'
else:
PYSQL_CONDITION = 'and'
Operator = fields.SQL_OPERATORS[operator_]
if name.endswith('date'):
sql_where = (Operator(invoice.invoice_date, value))
else:
sql_where = (Operator(invoice.reference, value))
query = (invoice_line.join(
invoice_line2,
'LEFT',
condition=(
(Cast(
Substring(invoice_line.origin,
Position(',', invoice_line.origin) + Literal(1)),
'INTEGER') == invoice_line2.id)
& (Like(invoice_line.origin, 'account.invoice.line,%')))).join(
invoice,
'LEFT',
condition=(invoice_line2.invoice == invoice.id)))
# sales
if Sale and (invoice_type == 'out' or invoice_type == 'both'):
query = query.join(
sale_line,
'LEFT',
condition=((Cast(
Substring(invoice_line.origin,
Position(',', invoice_line.origin) + Literal(1)),
'INTEGER') == sale_line.id)
& (Like(invoice_line.origin, 'sale.line,%'))))
query = query.join(sale,
'LEFT',
condition=(sale_line.sale == sale.id))
if name.endswith('date'):
sql_where = (sql_where | (Operator(sale.sale_date, value)))
else:
if PYSQL_CONDITION == 'and':
sql_where = (sql_where
| (Operator(sale.reference, value))
| (Operator(sale.number, value)))
else:
sql_where = (sql_where
| (Operator(sale.reference, value))
& (Operator(sale.number, value)))
# purchase
if Purchase and (invoice_type == 'in' or invoice_type == 'both'):
query = query.join(
purchase_line,
'LEFT',
condition=((Cast(
Substring(invoice_line.origin,
Position(',', invoice_line.origin) + Literal(1)),
'INTEGER') == purchase_line.id)
& (Like(invoice_line.origin, 'purchase.line,%'))))
query = query.join(
purchase,
'LEFT',
condition=(purchase_line.purchase == purchase.id))
if name.endswith('date'):
sql_where = (sql_where
| (Operator(purchase.purchase_date, value)))
else:
if PYSQL_CONDITION == 'and':
sql_where = (sql_where
| (Operator(purchase.reference, value))
| (Operator(purchase.number, value)))
else:
sql_where = (sql_where
| (Operator(purchase.reference, value))
& (Operator(purchase.number, value)))
query = query.select(invoice_line.id, where=sql_where)
return [('id', 'in', query)]
def convert_domain(self, domain, tables, Model):
pool = Pool()
Target = self.get_target()
Relation = pool.get(self.relation_name)
relation = Relation.__table__()
table, _ = tables[None]
name, operator, value = domain[:3]
origin_field = Relation._fields[self.origin]
origin = getattr(Relation, self.origin).sql_column(relation)
origin_where = None
if origin_field._type == 'reference':
origin_where = origin.like(Model.__name__ + ',%')
origin = Cast(
Substring(origin,
Position(',', origin) + Literal(1)),
Relation.id.sql_type().base)
target = getattr(Relation, self.target).sql_column(relation)
if '.' not in name:
if operator in ('child_of', 'not child_of'):
if Target != Model:
query = Target.search([(domain[3], 'child_of', value)],
order=[],
query=True)
where = (target.in_(query) & (origin != Null))
if origin_where:
where &= origin_where
query = relation.select(origin, where=where)
expression = table.id.in_(query)
if operator == 'not child_of':
return ~expression
return expression
if isinstance(value, basestring):
targets = Target.search([('rec_name', 'ilike', value)],
order=[])
ids = [t.id for t in targets]
elif not isinstance(value, (list, tuple)):
ids = [value]
else:
ids = value
if not ids:
expression = table.id.in_([None])
if operator == 'not child_of':
return ~expression
return expression
else:
return self.convert_domain_child((name, operator, ids),
tables)
if value is None:
where = origin != value
if origin_where:
where &= origin_where
query = relation.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:])]
query = Target.search(target_domain, order=[], query=True)
where = target.in_(query)
if origin_where:
where &= origin_where
query = relation.select(origin, where=where)
return table.id.in_(query)
