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)