def search_payment_amount(cls, name, clause):
pool = Pool()
Payment = pool.get('account.payment')
Account = pool.get('account.account')
_, operator, value = clause
Operator = fields.SQL_OPERATORS[operator]
table = cls.__table__()
payment = Payment.__table__()
account = Account.__table__()
payment_amount = Sum(Coalesce(payment.amount, 0))
main_amount = Abs(table.credit - table.debit) - payment_amount
second_amount = Abs(table.amount_second_currency) - payment_amount
amount = Case((table.second_currency == Null, main_amount),
else_=second_amount)
value = cls.payment_amount.sql_format(value)
query = table.join(
payment,
type_='LEFT',
condition=(table.id == payment.line) &
(payment.state != 'failed')).join(
account, condition=table.account == account.id).select(
table.id,
where=account.kind.in_(['payable', 'receivable']),
group_by=(table.id, account.kind, table.second_currency),
having=Operator(amount, value))
return [('id', 'in', query)]
def convert_order(self, name, tables, Model):
if not self.translate:
return super().convert_order(name, tables, Model)
assert name == self.name
table, _ = tables[None]
column = self._get_translation_order(tables, Model, name)
return [Coalesce(NullIf(column, ''), self.sql_column(table))]
def convert_domain(self, domain, tables, Model):
pool = Pool()
Translation = pool.get('ir.translation')
IrModel = pool.get('ir.model')
if not self.translate:
return super(FieldTranslate,
self).convert_domain(domain, tables, Model)
table = Model.__table__()
translation = Translation.__table__()
model = IrModel.__table__()
name, operator, value = domain
join = self._get_translation_join(Model, name, translation, model,
table)
Operator = SQL_OPERATORS[operator]
assert name == self.name
column = Coalesce(NullIf(translation.value, ''),
self.sql_column(table))
where = Operator(column, self._domain_value(operator, value))
if isinstance(where, operators.In) and not where.right:
where = Literal(False)
elif isinstance(where, operators.NotIn) and not where.right:
where = Literal(True)
where = self._domain_add_null(column, operator, value, where)
return tables[None][0].id.in_(join.select(table.id, where=where))
def _get_translation_order(self, tables, Model, name):
from trytond.ir.lang import get_parent_language
pool = Pool()
Translation = pool.get('ir.translation')
IrModel = pool.get('ir.model')
table, _ = tables[None]
join = table
language = Transaction().language
column = None
while language:
key = name + '.translation-' + language
if key not in tables:
translation = Translation.__table__()
model = IrModel.__table__()
translation, join = self._get_translation_join(
Model, name, translation, model, table, table, language)
if join.left == table:
tables[key] = {
None: (join.right, join.condition),
}
else:
tables[key] = {
None: (join.left.right, join.left.condition),
'translation': {
None: (join.right, join.condition),
},
}
else:
if 'translation' not in tables[key]:
translation, _ = tables[key][None]
else:
translation, _ = tables[key]['translation'][None]
column = Coalesce(NullIf(column, ''), translation.value)
language = get_parent_language(language)
return column
def get_lastmodified(cls, uri, cache=None):
pool = Pool()
object_name, object_id = cls._uri2object(uri, cache=cache)
if object_name == 'ir.attachment':
Model = pool.get(object_name)
if object_id:
if cache is not None:
cache.setdefault(Model.__name__, {})
ids = cache[Model.__name__].keys()
if object_id not in ids:
ids.append(object_id)
elif 'lastmodified' in cache[Model.__name__][object_id]:
return cache[Model.__name__][object_id][
'lastmodified']
else:
ids = [object_id]
res = None
cursor = Transaction().cursor
table = Model.__table__()
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(table.id, sub_ids)
cursor.execute(*table.select(table.id,
Extract('EPOCH',
Coalesce(table.write_date, table.create_date)),
where=red_sql))
for object_id2, date in cursor.fetchall():
if object_id2 == object_id:
res = date
if cache is not None:
cache[Model.__name__].setdefault(object_id2, {})
cache[Model.__name__][object_id2][
'lastmodified'] = date
if res is not None:
return res
return time.time()
def __register__(cls, module):
pool = Pool()
Subscription = pool.get('sale.subscription')
transaction = Transaction()
cursor = transaction.connection.cursor()
table = cls.__table__()
subscription = Subscription.__table__()
# Migration from 4.8: start_date required
if backend.TableHandler.table_exist(cls._table):
table_h = cls.__table_handler__(module)
if table_h.column_exist('start_date'):
cursor.execute(
*table.update([table.start_date],
subscription.select(subscription.start_date,
where=subscription.id ==
table.subscription),
where=table.start_date == Null))
super(Line, cls).__register__(module)
table_h = cls.__table_handler__(module)
# Migration from 4.8: drop required on description
table_h.not_null_action('description', action='remove')
# Migration from 5.2: replace consumed by consumed_until
if table_h.column_exist('consumed'):
cursor.execute(*table.update([table.consumed_until], [
Case((table.consumed,
Coalesce(table.next_consumption_date, table.end_date)),
else_=Null)
]))
table_h.drop_column('consumed')
def _get_target_tables(self, tables):
Target = self.get_target()
table, _ = tables[None]
target_tables = tables.get(self.name)
context = Transaction().context
if target_tables is None:
if Target._history and context.get('_datetime'):
target = Target.__table_history__()
target_history = Target.__table_history__()
history_condition = Column(target, '__id').in_(
target_history.select(
Max(Column(target_history, '__id')),
where=Coalesce(target_history.write_date,
target_history.create_date) <=
context['_datetime'],
group_by=target_history.id))
else:
target = Target.__table__()
history_condition = None
condition = target.id == self.sql_column(table)
if history_condition:
condition &= history_condition
target_tables = {
None: (target, condition),
}
tables[self.name] = target_tables
return target_tables
def table_query(cls):
pool = Pool()
Month = pool.get('ir.calendar.month')
month = Month.__table__()
query = super(SaleOpportunityEmployeeMonthly, cls).table_query()
opportunity, = query.from_
type_id = cls.id.sql_type().base
type_year = cls.year.sql_type().base
year_column = Extract(
'YEAR', opportunity.start_date).cast(type_year).as_('year')
month_index = Extract('MONTH', opportunity.start_date)
query.from_ = opportunity.join(month,
condition=month_index == month.index)
query.columns += (
Max(
Extract('MONTH', opportunity.start_date) +
Extract('YEAR', opportunity.start_date) * 100 +
Coalesce(opportunity.employee, 0) *
1000000).cast(type_id).as_('id'),
year_column,
month.id.as_('month'),
opportunity.employee,
)
query.group_by = (year_column, month.id, opportunity.employee,
opportunity.company)
return query
def __register__(cls, module_name):
pool = Pool()
ModelData = pool.get('ir.model.data')
model_data = ModelData.__table__()
cursor = Transaction().connection.cursor()
super(User, cls).__register__(module_name)
table = cls.__table_handler__(module_name)
# Migration from 3.0
if table.column_exist('password') and table.column_exist('salt'):
sqltable = cls.__table__()
password_hash_new = Concat('sha1$', Concat(sqltable.password,
Concat('$', Coalesce(sqltable.salt, ''))))
cursor.execute(*sqltable.update(
columns=[sqltable.password_hash],
values=[password_hash_new]))
table.drop_column('password')
table.drop_column('salt')
# Migration from 4.2: Remove required on name
table.not_null_action('name', action='remove')
# Migration from 5.6: Set noupdate to admin
cursor.execute(*model_data.update(
[model_data.noupdate], [True],
where=(model_data.model == cls.__name__)
& (model_data.module == 'res')
& (model_data.fs_id == 'user_admin')))
def get_active(cls, numbers, name):
pool = Pool()
Coupon = pool.get('sale.promotion.coupon')
Sale = pool.get('sale.sale')
Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number')
table = cls.__table__()
coupon = Coupon.__table__()
sale = Sale.__table__()
sale_number = Sale_Number.__table__()
context = Transaction().context
cursor = Transaction().connection.cursor()
party = context.get('party')
query = (table.join(sale_number,
'LEFT',
condition=table.id == sale_number.number).join(
coupon, condition=table.coupon == coupon.id))
if party:
query = query.join(sale,
'LEFT',
condition=(sale_number.sale == sale.id)
& (sale.party == party))
active = Case(
((coupon.number_of_use > 0) &
(coupon.per_party), Count(sale.id) < coupon.number_of_use),
((coupon.number_of_use > 0)
& ~Coalesce(coupon.per_party, False),
Count(sale_number.sale) < coupon.number_of_use),
else_=Literal(True))
else:
active = Case(((coupon.number_of_use > 0)
& ~Coalesce(coupon.per_party, False),
Count(sale_number.sale) < coupon.number_of_use),
else_=Literal(True))
query = query.select(
table.id,
active,
group_by=[table.id, coupon.number_of_use, coupon.per_party])
result = {}
for sub_numbers in grouped_slice(numbers):
query.where = reduce_ids(table.id, map(int, sub_numbers))
cursor.execute(*query)
result.update(dict(cursor.fetchall()))
return result
def get_quantity_executed(cls, lines, name):
cursor = Transaction().connection.cursor()
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Uom = pool.get('product.uom')
Forecast = pool.get('stock.forecast')
LineMove = pool.get('stock.forecast.line-stock.move')
move = Move.__table__()
location_from = Location.__table__()
location_to = Location.__table__()
line_move = LineMove.__table__()
result = dict((x.id, 0) for x in lines)
key = lambda line: line.forecast.id
lines.sort(key=key)
for forecast_id, lines in itertools.groupby(lines, key):
forecast = Forecast(forecast_id)
product2line = dict((line.product.id, line) for line in lines)
product_ids = product2line.keys()
for sub_ids in grouped_slice(product_ids):
red_sql = reduce_ids(move.product, sub_ids)
cursor.execute(*move.join(location_from,
condition=move.from_location == location_from.id
).join(location_to,
condition=move.to_location == location_to.id
).join(line_move, 'LEFT',
condition=move.id == line_move.move
).select(move.product, Sum(move.internal_quantity),
where=red_sql
& (location_from.left >= forecast.warehouse.left)
& (location_from.right <= forecast.warehouse.right)
& (location_to.left >= forecast.destination.left)
& (location_to.right <= forecast.destination.right)
& (move.state != 'cancel')
& (Coalesce(move.effective_date, move.planned_date)
>= forecast.from_date)
& (Coalesce(move.effective_date, move.planned_date)
<= forecast.to_date)
& (line_move.id == Null),
group_by=move.product))
for product_id, quantity in cursor.fetchall():
line = product2line[product_id]
result[line.id] = Uom.compute_qty(line.product.default_uom,
quantity, line.uom)
return result
def get_balance(cls, accounts, name):
pool = Pool()
Line = pool.get('analytic_account.line')
MoveLine = pool.get('account.move.line')
cursor = Transaction().connection.cursor()
table = cls.__table__()
line = Line.__table__()
move_line = MoveLine.__table__()
ids = [a.id for a in accounts]
childs = cls.search([('parent', 'child_of', ids)])
all_ids = list({}.fromkeys(ids + [c.id for c in childs]).keys())
id2account = {}
all_accounts = cls.browse(all_ids)
for account in all_accounts:
id2account[account.id] = account
line_query = Line.query_get(line)
cursor.execute(
*table.join(line, 'LEFT', condition=table.id == line.account).join(
move_line, 'LEFT', condition=move_line.id == line.move_line).
select(table.id,
Sum(Coalesce(line.credit, 0) - Coalesce(line.debit, 0)),
where=(table.type != 'view')
& table.id.in_(all_ids)
& (table.active == True) & line_query,
group_by=table.id))
account_sum = defaultdict(Decimal)
for account_id, value in cursor.fetchall():
account_sum.setdefault(account_id, Decimal('0.0'))
# SQLite uses float for SUM
if not isinstance(value, Decimal):
value = Decimal(str(value))
account_sum[account_id] += value
balances = {}
for account in accounts:
balance = Decimal()
childs = cls.search([
('parent', 'child_of', [account.id]),
])
for child in childs:
balance += account_sum[child.id]
exp = Decimal(str(10.0**-account.currency_digits))
balances[account.id] = balance.quantize(exp)
return balances
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
cast_ = MoveLine.debit.sql_cast
amount = cast_(Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0)))
if operator in {'in', 'not in'}:
value = [cast_(Literal(Decimal(v or 0))) for v in value]
else:
value = cast_(Literal(Decimal(value or 0)))
query = line.join(account,
condition=account.id == line.account).select(
line.party,
where=(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 convert_domain(self, domain, tables, Model):
transaction = Transaction()
context = transaction.context
database = transaction.database
expression = super().convert_domain(domain, tables, Model)
name, operator, value = domain
if operator.endswith('ilike'):
table, _ = tables[None]
if self.translate:
language = transaction.language
model, join, column = self._get_translation_column(
Model, name)
column = Coalesce(NullIf(column, ''), self.sql_column(model))
else:
language = None
column = self.sql_column(table)
column = self._domain_column(operator, column)
threshold = context.get(
'%s.%s.search_similarity' % (Model.__name__, name),
context.get('search_similarity'))
if database.has_similarity() and is_full_text(value) and threshold:
sim_value = unescape_wildcard(value)
sim_value = self._domain_value(operator, sim_value)
expression = (
database.similarity(column, sim_value) >= threshold)
if operator.startswith('not'):
expression = Not(expression)
if self.translate:
expression = table.id.in_(
join.select(model.id, where=expression))
key = '%s.%s.search_full_text' % (Model.__name__, name)
if ((self.search_full_text or context.get(key))
and context.get(key, True)
and database.has_search_full_text()):
if context.get(key) or is_full_text(value):
fts_column = database.format_full_text(
column, language=language)
fts_value = value
if key not in context:
fts_value = unescape_wildcard(fts_value)
fts_value = self._domain_value(operator, fts_value)
fts_value = database.format_full_text_query(
fts_value, language=language)
fts = database.search_full_text(fts_column, fts_value)
if operator.startswith('not'):
fts = Not(fts)
if self.translate:
fts = table.id.in_(
join.select(model.id, where=fts))
if database.has_similarity() and is_full_text(value):
if operator.startswith('not'):
expression |= fts
else:
expression &= fts
else:
expression = fts
return expression
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 _column_cost(cls, tables, withs, sign):
move = tables['move']
cost = super()._column_cost(tables, withs, sign)
if Transaction().context.get('include_shipment_cost'):
cost += Sum(
sign * cls.cost.sql_cast(move.internal_quantity)
* Coalesce(move.shipment_out_cost_price, 0))
return cost
def __register__(cls, module_name):
TableHandler = backend.get('TableHandler')
pool = Pool()
Sequence = pool.get('account.fiscalyear.invoice_sequence')
FiscalYear = pool.get('account.fiscalyear')
sequence = Sequence.__table__()
fiscalyear = FiscalYear.__table__()
sql_table = cls.__table__()
super(Period, cls).__register__(module_name)
cursor = Transaction().connection.cursor()
table = TableHandler(cls, module_name)
# Migration from 4.2: Use Match pattern for sequences
if (table.column_exist('in_invoice_sequence')
and table.column_exist('in_credit_note_sequence')
and table.column_exist('out_invoice_sequence')
and table.column_exist('out_credit_note_sequence')):
cursor.execute(
*sequence.insert(columns=[
sequence.sequence, sequence.fiscalyear, sequence.company,
sequence.period, sequence.out_invoice_sequence, sequence.
out_credit_note_sequence, sequence.in_invoice_sequence,
sequence.in_credit_note_sequence
],
values=sql_table.join(
fiscalyear,
condition=(
fiscalyear.id == sql_table.fiscalyear)
).select(
Literal(10), sql_table.fiscalyear,
fiscalyear.company, sql_table.id,
Coalesce(sql_table.out_invoice_sequence,
fiscalyear.out_invoice_sequence),
Coalesce(
sql_table.out_credit_note_sequence,
fiscalyear.out_credit_note_sequence),
Coalesce(sql_table.in_invoice_sequence,
fiscalyear.in_invoice_sequence),
Coalesce(
sql_table.in_credit_note_sequence,
fiscalyear.in_credit_note_sequence))))
table.drop_column('out_invoice_sequence')
table.drop_column('out_credit_note_sequence')
table.drop_column('in_invoice_sequence')
table.drop_column('in_credit_note_sequence')
def __register__(cls, module_name):
TableHandler = backend.get('TableHandler')
cursor = Transaction().connection.cursor()
table = TableHandler(cls, module_name)
sql_table = cls.__table__()
super(Address, cls).__register__(module_name)
# Migration from 2.4: drop required on sequence
table.not_null_action('sequence', action='remove')
# Migration from 4.0: remove streetbis
if table.column_exist('streetbis'):
value = Concat(Coalesce(sql_table.street, ''),
Concat('\n', Coalesce(sql_table.streetbis, '')))
cursor.execute(*sql_table.update([sql_table.street], [value]))
table.drop_column('streetbis')
def table_query(cls):
pool = Pool()
ProductCostPrice = pool.get('product.cost_price')
history = ProductCostPrice.__table_history__()
return history.select(Max(Column(history, '__id')).as_('id'),
Max(history.create_uid).as_('create_uid'),
Max(history.create_date).as_('create_date'),
Max(history.write_uid).as_('write_uid'),
Max(history.write_date).as_('write_date'),
Coalesce(history.write_date,
history.create_date).as_('date'),
history.product.as_('product'),
history.cost_price.as_('cost_price'),
group_by=(history.id,
Coalesce(history.write_date,
history.create_date),
history.product, history.cost_price))
def table_query():
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
Date = pool.get('ir.date')
move = from_ = Move.__table__()
context = Transaction().context
today = Date.today()
if context.get('product_template') is not None:
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = (product.template == context['product_template'])
else:
product_clause = move.product == context.get('product', -1)
warehouse_id = context.get('warehouse', -1)
warehouse_query = Location.search([
('parent', 'child_of', [warehouse_id]),
],
query=True,
order=[])
date_column = Coalesce(move.effective_date, move.planned_date)
return (from_.select(
Max(move.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
date_column.as_('date'),
where=product_clause
& (move.from_location.in_(warehouse_query)
| move.to_location.in_(warehouse_query))
& (Coalesce(move.effective_date, move.planned_date) != Null)
& (date_column != today),
group_by=(date_column, move.product))
| Select([
Literal(0).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal(today).as_('date'),
]))
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
sql_table = cls.__table__()
table = cls.__table_handler__(module_name)
# Migration from 5.8: rename zip to postal code
table.column_rename('zip', 'postal_code')
super(Address, cls).__register__(module_name)
table = cls.__table_handler__(module_name)
# Migration from 4.0: remove streetbis
if table.column_exist('streetbis'):
value = Concat(Coalesce(sql_table.street, ''),
Concat('\n', Coalesce(sql_table.streetbis, '')))
cursor.execute(*sql_table.update([sql_table.street], [value]))
table.drop_column('streetbis')
def __setup__(cls):
super(Category, cls).__setup__()
t = cls.__table__()
cls._sql_constraints = [
('name_parent_exclude',
Exclude(t, (t.name, Equal), (Coalesce(t.parent, -1), Equal)),
'party.msg_category_name_unique'),
]
cls._order.insert(0, ('name', 'ASC'))
def __setup__(cls):
super(Category, cls).__setup__()
t = cls.__table__()
cls._sql_constraints = [
('name_parent_exclude',
Exclude(t, (t.name, Equal), (Coalesce(t.parent, -1), Equal)),
'The name of a party category must be unique by parent.'),
]
cls._order.insert(0, ('name', 'ASC'))
def get_lastmodified(cls, uri, cache=None):
Todo = Pool().get('calendar.todo')
todo = Todo.__table__()
cursor = Transaction().cursor
calendar_id = cls.calendar(uri)
if calendar_id and (uri[10:].split('/', 1) + [None])[1]:
todo_id = cls.todo(uri, calendar_id=calendar_id)
if todo_id:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Todo.__name__, {})
ids = cache['_calendar'][Todo.__name__].keys()
if todo_id not in ids:
ids.append(todo_id)
elif 'lastmodified' in cache['_calendar'][
Todo.__name__][todo_id]:
return cache['_calendar'][Todo.__name__][
todo_id]['lastmodified']
else:
ids = [todo_id]
res = None
for sub_ids in grouped_slice(ids, cursor.IN_MAX / 2):
red_id_sql = reduce_ids(todo.id, sub_ids)
red_parent_sql = reduce_ids(todo.parent, sub_ids)
cursor.execute(*todo.select(Coalesce(todo.parent, todo.id),
Max(Extract('EPOCH', Coalesce(todo.write_date,
todo.create_date))),
where=red_id_sql | red_parent_sql,
group_by=(todo.parent, todo.id)))
for todo_id2, date in cursor.fetchall():
if todo_id2 == todo_id:
res = date
if cache is not None:
cache['_calendar'][Todo.__name__]\
.setdefault(todo_id2, {})
cache['_calendar'][Todo.__name__][
todo_id2]['lastmodified'] = date
if res is not None:
return res
return super(Collection, cls).get_lastmodified(uri, cache=cache)
def convert_order(self, name, tables, Model):
from trytond.tools import get_parent_language
pool = Pool()
Translation = pool.get('ir.translation')
IrModel = pool.get('ir.model')
if not self.translate:
return super(FieldTranslate,
self).convert_order(name, tables, Model)
assert name == self.name
table, _ = tables[None]
join = table
language = Transaction().language
column = None
while language:
key = name + '.translation-' + language
if key not in tables:
translation = Translation.__table__()
model = IrModel.__table__()
join = self._get_translation_join(Model, name, translation,
model, table, table,
language)
if join.left == table:
tables[key] = {
None: (join.right, join.condition),
}
else:
tables[key] = {
None: (join.left.right, join.left.condition),
'translation': {
None: (join.right, join.condition),
},
}
else:
if 'translation' not in tables[key]:
translation, _ = tables[key][None]
else:
translation, _ = tables[key]['translation'][None]
column = Coalesce(NullIf(column, ''), translation.value)
language = get_parent_language(language)
return [Coalesce(column, self.sql_column(table))]
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')
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))
line_query, _ = MoveLine.query_get(line)
Operator = fields.SQL_OPERATORS[clause[1]]
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)
& line_query & today_query,
group_by=line.party,
having=Operator(
Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0)),
Decimal(clause[2] or 0)))
return [('id', 'in', query)]
def get_deposit(cls, parties, name):
pool = Pool()
MoveLine = pool.get('account.move.line')
Account = pool.get('account.account')
AccountType = pool.get('account.account.type')
User = pool.get('res.user')
cursor = Transaction().connection.cursor()
line = MoveLine.__table__()
account = Account.__table__()
account_type = AccountType.__table__()
values = {p.id: Decimal(0) for p in parties}
user = User(Transaction().user)
if not user.company:
return values
currency = user.company.currency
line_clause, _ = MoveLine.query_get(line)
for sub_parties in grouped_slice(parties):
party_clause = reduce_ids(line.party, [p.id for p in sub_parties])
cursor.execute(
*line.join(account, condition=account.id == line.account).join(
account_type, condition=account.type == account_type.id).
select(
line.party,
# Use credit - debit to positive deposit amount
Sum(Coalesce(line.credit, 0) - Coalesce(line.debit, 0)),
where=account_type.deposit
& party_clause
& (line.reconciliation == Null)
& (account.company == user.company.id)
& line_clause,
group_by=line.party))
for party_id, value in cursor:
# SQLite uses float for SUM
if not isinstance(value, Decimal):
value = currency.round(Decimal(str(value)))
values[party_id] = value
return values
def search_active(cls, name, clause):
pool = Pool()
Coupon = pool.get('sale.promotion.coupon')
Sale = pool.get('sale.sale')
Sale_Number = pool.get('sale.sale-sale.promotion.coupon.number')
table = cls.__table__()
coupon = Coupon.__table__()
sale = Sale.__table__()
sale_number = Sale_Number.__table__()
context = Transaction().context
party = context.get('party')
_, operator, value = clause
Operator = fields.SQL_OPERATORS[operator]
query = (table
.join(sale_number, 'LEFT',
condition=table.id == sale_number.number)
.join(coupon, condition=table.coupon == coupon.id))
if party:
query = query.join(sale, 'LEFT',
condition=(sale_number.sale == sale.id)
& (sale.party == party))
active = Case(
((coupon.number_of_use > 0) & (coupon.per_party),
Count(sale.id) < coupon.number_of_use),
((coupon.number_of_use > 0)
& ~Coalesce(coupon.per_party, False),
Count(sale_number.sale) < coupon.number_of_use),
else_=Literal(True))
else:
active = Case(
((coupon.number_of_use > 0)
& ~Coalesce(coupon.per_party, False),
Count(sale_number.sale) < coupon.number_of_use),
else_=Literal(True))
query = query.select(table.id,
group_by=[table.id, coupon.number_of_use, coupon.per_party],
having=Operator(active, value))
return [('id', 'in', query)]
def add(cls, user):
cursor = Transaction().connection.cursor()
table = cls.__table__()
cursor.execute(*table.delete(
where=(Coalesce(table.write_date, table.create_date) -
CurrentTimestamp()) > cls.timeout()))
session = cls(user=user)
session.save()
return session.key
def get_deposit_balance(self, deposit_account):
'Return the deposit account balance (debit - credit) for the party'
pool = Pool()
MoveLine = pool.get('account.move.line')
transaction = Transaction()
cursor = transaction.connection.cursor()
line = MoveLine.__table__()
assert deposit_account.kind == 'deposit'
transaction.database.lock(transaction.connection, MoveLine._table)
cursor.execute(*line.select(
Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0)),
where=(line.account == deposit_account.id)
& (line.party == self.id)
& (line.reconciliation == Null)))
amount, = cursor.fetchone()
if amount and not isinstance(amount, Decimal):
currency = deposit_account.company.currency
amount = currency.round(Decimal(str(amount)))
return amount or Decimal(0)
