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)