def test_union3(self):
query = Union(self.q1, self.q2, self.q3)
self.assertEqual(
str(query),
'SELECT * FROM "t1" AS "a" UNION SELECT * FROM "t2" AS "b" '
'UNION SELECT * FROM "t3" AS "c"')
self.assertEqual(query.params, ())
query = Union(Union(self.q1, self.q2), self.q3)
self.assertEqual(
str(query),
'SELECT * FROM "t1" AS "a" UNION SELECT * FROM "t2" AS "b" '
'UNION SELECT * FROM "t3" AS "c"')
self.assertEqual(query.params, ())
query = Union(self.q1, Union(self.q2, self.q3))
self.assertEqual(
str(query),
'SELECT * FROM "t1" AS "a" UNION SELECT * FROM "t2" AS "b" '
'UNION SELECT * FROM "t3" AS "c"')
self.assertEqual(query.params, ())
query = self.q1 | self.q2 | self.q3
self.assertEqual(
str(query),
'SELECT * FROM "t1" AS "a" UNION SELECT * FROM "t2" AS "b" '
'UNION SELECT * FROM "t3" AS "c"')
self.assertEqual(query.params, ())
def test_select_union(self):
query1 = self.table.select()
query2 = Table('t2').select()
union = query1 | query2
self.assertEqual(
str(union),
'SELECT * FROM "t" AS "a" UNION SELECT * FROM "t2" AS "b"')
union.all_ = True
self.assertEqual(
str(union), 'SELECT * FROM "t" AS "a" UNION ALL '
'SELECT * FROM "t2" AS "b"')
self.assertEqual(
str(union.select()), 'SELECT * FROM ('
'SELECT * FROM "t" AS "b" UNION ALL '
'SELECT * FROM "t2" AS "c") AS "a"')
query1.where = self.table.c == 'foo'
self.assertEqual(
str(union),
'SELECT * FROM "t" AS "a" WHERE ("a"."c" = %s) UNION ALL '
'SELECT * FROM "t2" AS "b"')
self.assertEqual(union.params, ('foo', ))
union = Union(query1)
self.assertEqual(str(union), str(query1))
self.assertEqual(union.params, query1.params)
def _joins(cls):
pool = Pool()
Company = pool.get('company.company')
Currency = pool.get('currency.currency')
context = Transaction().context
tables = {}
company = context.get('company')
lines = cls._lines()
tables['line'] = line = Union(*(l(len(lines), i, company)
for i, l in enumerate(lines)))
tables['line.company'] = company = Company.__table__()
withs = {}
currency_sale = With(query=Currency.currency_rate_sql())
withs['currency_sale'] = currency_sale
currency_company = With(query=Currency.currency_rate_sql())
withs['currency_company'] = currency_company
from_item = (line.join(
currency_sale,
condition=(line.currency == currency_sale.currency)
& (currency_sale.start_date <= line.date)
&
((currency_sale.end_date == Null)
| (currency_sale.end_date >= line.date))).join(
company, condition=line.company == company.id).join(
currency_company,
condition=(company.currency == currency_company.currency)
& (currency_company.start_date <= line.date)
& ((currency_company.end_date == Null)
| (currency_company.end_date >= line.date))))
return from_item, tables, withs
def test_union2(self):
query = Union(self.q1, self.q2)
self.assertEqual(
str(query),
'SELECT * FROM "t1" AS "a" UNION SELECT * FROM "t2" AS "b"')
self.assertEqual(query.params, ())
query = self.q1 | self.q2
self.assertEqual(
str(query),
'SELECT * FROM "t1" AS "a" UNION SELECT * FROM "t2" AS "b"')
self.assertEqual(query.params, ())
def table_query(cls):
pool = Pool()
Relation = pool.get('party.relation')
Type = pool.get('party.relation.type')
relation = Relation.__table__()
type = Type.__table__()
tables = {
None: (relation, None)
}
reverse_tables = {
None: (relation, None),
'type': {
None: (type, (relation.type == type.id) &
(type.reverse != Null)),
},
}
columns = []
reverse_columns = []
for name, field in Relation._fields.items():
if hasattr(field, 'get'):
continue
column, reverse_column = cls._get_column(tables, reverse_tables,
name)
columns.append(column)
reverse_columns.append(reverse_column)
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
query = convert_from(None, tables).select(*columns)
reverse_query = convert_from(None, reverse_tables).select(
*reverse_columns)
return Union(query, reverse_query, all_=True)
def _test_select_union_quote(self, quote):
query1 = self.table.select()
query2 = Table('t2').select()
union = query1 | query2
q = lambda x: x.replace('"', quote)
self.assertEqual(str(union),
q('SELECT * FROM "t" AS "a" UNION SELECT * FROM "t2" AS "b"'))
union.all_ = True
self.assertEqual(str(union),
q('SELECT * FROM "t" AS "a" UNION ALL '
'SELECT * FROM "t2" AS "b"'))
self.assertEqual(str(union.select()),
q('SELECT * FROM ('
'SELECT * FROM "t" AS "b" UNION ALL '
'SELECT * FROM "t2" AS "c") AS "a"'))
query1.where = self.table.c == 'foo'
self.assertEqual(str(union),
q('SELECT * FROM "t" AS "a" WHERE ("a"."c" = %s) UNION ALL '
'SELECT * FROM "t2" AS "b"'))
self.assertEqual(union.params, ('foo',))
union = Union(query1)
self.assertEqual(str(union), str(query1))
self.assertEqual(union.params, query1.params)
def table_query(cls):
queries = []
for model in cls.union_models():
table, columns = cls.union_columns(model)
queries.append(table.select(*columns))
return Union(*queries)
def migrate_property(model_name,
field_names,
ValueModel,
value_names,
parent=None,
fields=None):
"Migrate property from model_name.field_name to ValueModel.value_name"
pool = Pool()
Field = pool.get('ir.model.field')
Model = pool.get('ir.model')
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
cursor = Transaction().connection.cursor()
field = Field.__table__()
model = Model.__table__()
table = ValueModel.__table__()
if fields is None:
fields = []
if isinstance(field_names, basestring):
field_names = [field_names]
if isinstance(value_names, basestring):
value_names = [value_names]
def split_value(value):
return value.split(',')[1]
cast_funcs = {
'numeric': lambda v: Decimal(split_value(v)) if v else None,
'integer': lambda v: int(split_value(v)) if v else None,
'float': lambda v: float(split_value(v)) if v else None,
'char': lambda v: split_value(v) if v else None,
'selection': lambda v: split_value(v) if v else None,
'many2one': lambda v: int(split_value(v)) if v else None,
'reference': lambda v: v,
}
casts = []
queries = []
for field_name, value_name in zip(field_names, value_names):
value_field = getattr(ValueModel, value_name)
casts.append(cast_funcs[value_field._type])
property_ = Table('ir_property')
columns = [
Literal(None).as_(f)
if f != value_name else property_.value.as_(value_name)
for f in value_names
]
if parent:
columns.append(property_.res.as_(parent))
where = property_.res.like(model_name + ',%')
else:
where = property_.res == Null
columns.extend([Column(property_, f).as_(f) for f in fields])
query = property_.join(field,
condition=property_.field == field.id).join(
model,
condition=field.model == model.id).select(
*columns,
where=where
& (field.name == field_name)
& (model.model == model_name))
queries.append(query)
union = Union(*queries)
columns = [Max(Column(union, f)).as_(f) for f in value_names]
if parent:
columns.append(Column(union, parent).as_(parent))
pcolumns = [Column(union, parent)]
else:
pcolumns = []
vcolumns = [Column(union, f).as_(f) for f in fields]
cursor.execute(
*union.select(*(columns + vcolumns), group_by=pcolumns + vcolumns))
columns = [Column(table, f) for f in value_names]
if parent:
pcolumns = [Column(table, parent)]
else:
pcolumns = []
vcolumns = [Column(table, f) for f in fields]
values = []
l = len(value_names)
for row in cursor.fetchall():
value = [c(v) for v, c in zip(row, casts)]
if parent:
value.append(int(row[l].split(',')[1]) if row[l] else None)
i = 1
else:
i = 0
value.extend(row[l + i:])
values.append(value)
if (values and not (
# No property defined
len(values) == 1 and all(x is None
for x in values[0][:len(columns)]))):
# Delete previous migrated values
cursor.execute(*table.delete())
cursor.execute(
*table.insert(columns + pcolumns + vcolumns, values=values))
def compute_quantities_query(cls, location_ids, with_childs=False,
grouping=('product',), grouping_filter=None):
"""
Prepare a query object to compute for each location and product the
stock quantity in the default uom of the product.
The context with keys:
stock_date_end: if set the date of the stock computation.
stock_date_start: if set return the delta of the stock between the
two dates, (ignored if stock_date_end is missing).
stock_assign: if set compute also the assigned outgoing moves as
done.
forecast: if set compute the forecast quantity.
stock_destinations: A list of location ids. If set, restrict the
computation to moves from and to those locations.
stock_skip_warehouse: if set, quantities on a warehouse are no more
quantities of all child locations but quantities of the storage
zone.
If with_childs, it computes also for child locations.
grouping is a tuple of Move field names and defines how stock moves are
grouped.
grouping_filter is a tuple of values, for the Move's field at the same
position in grouping tuple, used to filter which moves are used to
compute quantities. It must be None or have the same number of
elements than grouping. If no grouping_filter is provided it
returns quantities for all products.
The query return the location as first column, after the fields in
grouping, and the last column is the quantity.
"""
pool = Pool()
Rule = pool.get('ir.rule')
Location = pool.get('stock.location')
Date = pool.get('ir.date')
Period = pool.get('stock.period')
Move = pool.get('stock.move')
move = Move.__table__()
today = Date.today()
if not location_ids:
return None
context = Transaction().context.copy()
for field in grouping:
if field not in Move._fields:
raise ValueError('"%s" has no field "%s"' % (Move, field))
assert grouping_filter is None or len(grouping_filter) == len(grouping)
move_rule_query = Rule.domain_get('stock.move')
PeriodCache = Period.get_cache(grouping)
period = None
if PeriodCache:
period_cache = PeriodCache.__table__()
if not context.get('stock_date_end'):
context['stock_date_end'] = datetime.date.max
# date end in the past or today: filter on state done
if (context['stock_date_end'] < today
or (context['stock_date_end'] == today
and not context.get('forecast'))):
state_date_clause = lambda stock_assign: (
move.state.in_(['done',
'assigned' if stock_assign else 'done'])
& (
(
(move.effective_date == Null)
& (move.planned_date <= context['stock_date_end'])
)
| (move.effective_date <= context['stock_date_end'])
)
)
state_date_clause_in = state_date_clause(False)
state_date_clause_out = state_date_clause(
context.get('stock_assign'))
# future date end: filter move on state done and date
# before today, or on all state and date between today and
# date_end.
else:
state_date_clause = lambda stock_assign: (
(move.state.in_(['done',
'assigned' if stock_assign else 'done'])
& (
(
(move.effective_date == Null)
& (move.planned_date <= today)
)
| (move.effective_date <= today)
)
)
| (move.state.in_(['done', 'assigned', 'draft'])
& (
(
(move.effective_date == Null)
& (Coalesce(move.planned_date, datetime.date.max)
<= context['stock_date_end'])
& (Coalesce(move.planned_date, datetime.date.max)
>= today)
)
| (
(move.effective_date <= context['stock_date_end'])
& (move.effective_date >= today)
)
)
)
)
state_date_clause_in = state_date_clause(False)
state_date_clause_out = state_date_clause(
context.get('stock_assign'))
if context.get('stock_date_start'):
if context['stock_date_start'] > today:
state_date_clause = lambda: (
move.state.in_(['done', 'assigned', 'draft'])
& (
(
(move.effective_date == Null)
& (
(move.planned_date >=
context['stock_date_start'])
| (move.planned_date == Null)
)
)
| (move.effective_date >= context['stock_date_start'])
)
)
state_date_clause_in &= state_date_clause()
state_date_clause_out &= state_date_clause()
else:
state_date_clause = lambda stock_assign: (
(
move.state.in_(['done', 'assigned', 'draft'])
& (
(
(move.effective_date == Null)
& (
(move.planned_date >= today)
| (move.planned_date == Null)
)
)
| (move.effective_date >= today)
)
)
| (
move.state.in_(['done',
'assigned' if stock_assign else 'done'])
& (
(
(move.effective_date == Null)
& (
(
(move.planned_date >=
context['stock_date_start'])
& (move.planned_date < today)
)
| (move.planned_date == Null)
)
)
| (
(move.effective_date >=
context['stock_date_start'])
& (move.effective_date < today)
)
)
)
)
state_date_clause_in &= state_date_clause(False)
state_date_clause_out &= state_date_clause(
context.get('stock_assign'))
elif PeriodCache:
periods = Period.search([
('date', '<', context['stock_date_end']),
('state', '=', 'closed'),
], order=[('date', 'DESC')], limit=1)
if periods:
period, = periods
state_date_clause = lambda: (
Coalesce(move.effective_date, move.planned_date,
datetime.date.max) > period.date)
state_date_clause_in &= state_date_clause()
state_date_clause_out &= state_date_clause()
if with_childs:
location_query = Location.search([
('parent', 'child_of', location_ids),
], query=True, order=[])
else:
location_query = location_ids[:]
if PeriodCache:
from_period = period_cache
where = where_period = Literal(True)
if grouping_filter and any(grouping_filter):
for fieldname, grouping_ids in zip(grouping, grouping_filter):
if not grouping_ids:
continue
column = Column(move, fieldname)
if PeriodCache:
cache_column = Column(period_cache, fieldname)
if isinstance(grouping_ids[0], (int, long, float, Decimal)):
where &= reduce_ids(column, grouping_ids)
if PeriodCache:
where_period &= reduce_ids(cache_column, grouping_ids)
else:
where &= column.in_(grouping_ids)
if PeriodCache:
where_period &= cache_column.in_(grouping_ids)
if context.get('stock_destinations'):
destinations = context['stock_destinations']
dest_clause_from = move.from_location.in_(destinations)
dest_clause_to = move.to_location.in_(destinations)
if PeriodCache:
dest_clause_period = period_cache.location.in_(destinations)
else:
dest_clause_from = dest_clause_to = dest_clause_period = \
Literal(True)
# The main select clause is a union between three similar subqueries.
# One that sums incoming moves towards locations, one that sums
# outgoing moves and one for the period cache. UNION ALL is used
# because we already know that there will be no duplicates.
move_keys_alias = [Column(move, key).as_(key) for key in grouping]
move_keys = [Column(move, key) for key in grouping]
query = move.select(move.to_location.as_('location'),
Sum(move.internal_quantity).as_('quantity'),
*move_keys_alias,
where=state_date_clause_in
& where
& move.to_location.in_(location_query)
& (move.id.in_(move_rule_query) if move_rule_query
else Literal(True))
& dest_clause_from,
group_by=[move.to_location] + move_keys)
query = Union(query, move.select(move.from_location.as_('location'),
(-Sum(move.internal_quantity)).as_('quantity'),
*move_keys_alias,
where=state_date_clause_out
& where
& move.from_location.in_(location_query)
& (move.id.in_(move_rule_query) if move_rule_query
else Literal(True))
& dest_clause_to,
group_by=[move.from_location] + move_keys),
all_=True)
if PeriodCache:
period_keys = [Column(period_cache, key).as_(key)
for key in grouping]
query = Union(query, from_period.select(
period_cache.location.as_('location'),
period_cache.internal_quantity.as_('quantity'),
*period_keys,
where=(period_cache.period
== (period.id if period else None))
& where_period
& period_cache.location.in_(location_query)
& dest_clause_period),
all_=True)
query_keys = [Column(query, key).as_(key) for key in grouping]
columns = ([query.location.as_('location')]
+ query_keys
+ [Sum(query.quantity).as_('quantity')])
query = query.select(*columns,
group_by=[query.location] + query_keys)
return query