def __register__(cls, module_name):
pool = Pool()
Country = pool.get('country.country')
Language = pool.get('ir.lang')
country = Country.__table__()
language = Language.__table__()
table = cls.__table__()
cursor = Transaction().connection.cursor()
super().__register__(module_name)
table_h = cls.__table_handler__()
# Migration from 5.2: replace country by country_code
if table_h.column_exist('country'):
query = table.update([table.country_code],
country.select(
country.code,
where=country.id == table.country))
cursor.execute(*query)
table_h.drop_column('country')
# Migration from 5.2: replace language by language_code
if table_h.column_exist('language'):
query = table.update([table.language_code],
language.select(
Substring(language.code, 0, 2),
where=language.id == table.language))
cursor.execute(*query)
table_h.drop_column('language')
def table_query(cls):
pool = Pool()
Property = pool.get('ir.property')
Field = pool.get('ir.model.field')
property_history = Property.__table_history__()
field = Field.__table__()
return property_history.join(
field, condition=field.id == property_history.field).select(
Max(Column(property_history, '__id')).as_('id'),
Max(property_history.create_uid).as_('create_uid'),
Max(property_history.create_date).as_('create_date'),
Max(property_history.write_uid).as_('write_uid'),
Max(property_history.write_date).as_('write_date'),
Coalesce(property_history.write_date,
property_history.create_date).as_('date'),
Trim(Substring(property_history.res, ',.*'), 'LEADING',
',').cast(cls.template.sql_type().base).as_('template'),
Trim(property_history.value, 'LEADING', ',').cast(
cls.cost_price.sql_type().base).as_('cost_price'),
where=(field.name == 'cost_price')
& property_history.res.like('product.template,%'),
group_by=(property_history.id,
Coalesce(property_history.write_date,
property_history.create_date),
property_history.res, property_history.value))
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 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 order_field(tables):
Service = Pool().get('lims.service')
field = Service._fields[name]
table, _ = tables[None]
service_tables = tables.get('service')
if service_tables is None:
service = Service.__table__()
service_tables = {
None:
(service,
(table.origin.like('lims.service,%') &
(Service.id.sql_cast(
Substring(table.origin,
Position(',', table.origin) + Literal(1)))
== service.id))),
}
tables['service'] = service_tables
return field.convert_order(name, service_tables, Service)
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 __register__(cls, module_name):
cursor = Transaction().connection.cursor()
table = backend.TableHandler(cls, module_name)
created_347 = table.column_exist('include_347')
super(Party, cls).__register__(module_name)
# We need to reload table as it may be modified by __register__
table = backend.TableHandler(cls, module_name)
if (not created_347 and table.column_exist('include_347')):
sql_table = cls.__table__()
identifier = Pool().get('party.identifier').__table__()
query = identifier.select(identifier.party,
where=Substring(identifier.code, 1,
2) == 'ES')
cursor.execute(*sql_table.update(columns=[sql_table.include_347],
values=[True],
where=(sql_table.id.in_(query))))
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 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 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 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 transition_erase(self):
pool = Pool()
Party = pool.get('party.party')
cursor = Transaction().connection.cursor()
def convert_from(table, tables):
right, condition = tables[None]
if table:
table = table.join(right, condition=condition)
else:
table = right
for k, sub_tables in tables.items():
if k is None:
continue
table = convert_from(table, sub_tables)
return table
resources = self.get_resources()
parties = replacing = [self.ask.party]
with Transaction().set_context(active_test=False):
while replacing:
replacing = Party.search([
('replaced_by', 'in', list(map(int, replacing))),
])
parties += replacing
for party in parties:
self.check_erase(party)
to_erase = self.to_erase(party.id)
for Model, domain, resource, columns, values in to_erase:
assert issubclass(Model, ModelSQL)
assert len(columns) == len(values)
if 'active' in Model._fields:
records = Model.search(domain)
Model.write(records, {'active': False})
tables, where = Model.search_domain(domain, active_test=False)
from_ = convert_from(None, tables)
table, _ = tables[None]
query = from_.select(table.id, where=where)
if columns:
model_tables = [Model.__table__()]
if Model._history:
model_tables.append(Model.__table_history__())
for table in model_tables:
sql_columns, sql_values = [], []
for column, value in zip(columns, values):
column = Column(table, column)
sql_columns.append(column)
sql_values.append(
value(column) if callable(value) else value)
cursor.execute(*table.update(
sql_columns, sql_values, where=table.id.in_(
query)))
if resource:
for Resource in resources:
model_tables = [Resource.__table__()]
if Resource._history:
model_tables.append(Resource.__table_history__())
for table in model_tables:
cursor.execute(*table.delete(
where=table.resource.like(Model.__name__ +
',%')
& Model.id.sql_cast(
Substring(
table.resource,
Position(',', table.resource) +
Literal(1))).in_(query)))
return 'end'
def __register__(cls, module_name):
Party = Pool().get('party.party')
Model = Pool().get('ir.model')
ModelField = Pool().get('ir.model.field')
Property = Pool().get('ir.property')
PaymentProfile = Pool().get('party.payment_profile')
TableHandler = backend.get('TableHandler')
cursor = Transaction().cursor
table = TableHandler(cursor, cls, module_name)
migration_needed = False
if not table.column_exist('credit_account'):
migration_needed = True
migrate_last_four_digits = False
if not table.column_exist('last_four_digits'):
migrate_last_four_digits = True
super(PaymentTransaction, cls).__register__(module_name)
if migration_needed and not Pool.test:
# Migration
# Set party's receivable account as the credit_account on
# transactions
transaction = cls.__table__()
party = Party.__table__()
property = Property.__table__()
account_model, = Model.search([
('model', '=', 'party.party'),
])
account_receivable_field, = ModelField.search([
('model', '=', account_model.id),
('name', '=', 'account_receivable'),
('ttype', '=', 'many2one'),
])
update = transaction.update(
columns=[transaction.credit_account],
values=[
Trim(
Substring(property.value, ',.*'), 'LEADING', ','
).cast(cls.credit_account.sql_type().base)
],
from_=[party, property],
where=(
transaction.party == party.id
) & (
property.res == Concat(Party.__name__ + ',', party.id)
) & (
property.field == account_receivable_field.id
) & (
property.company == transaction.company
)
)
cursor.execute(*update)
if migrate_last_four_digits and not Pool.test:
transaction = cls.__table__()
payment_profile = PaymentProfile.__table__()
cursor.execute(*transaction.update(
columns=[transaction.last_four_digits],
values=[payment_profile.last_4_digits],
from_=[payment_profile],
where=(transaction.payment_profile == payment_profile.id)
))
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 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)