def test_window(self):
query = self.table.select(
Min(self.table.c1, window=Window([self.table.c2])))
self.assertEqual(
str(query), 'SELECT MIN("a"."c1") OVER "b" FROM "t" AS "a" '
'WINDOW "b" AS (PARTITION BY "a"."c2")')
self.assertEqual(query.params, ())
query = self.table.select(Rank(window=Window([])))
self.assertEqual(
str(query), 'SELECT RANK() OVER "b" FROM "t" AS "a" '
'WINDOW "b" AS ()')
self.assertEqual(query.params, ())
window = Window([self.table.c1])
query = self.table.select(
Rank(filter_=self.table.c1 > 0, window=window),
Min(self.table.c1, window=window))
self.assertEqual(
str(query),
'SELECT RANK() FILTER (WHERE ("a"."c1" > %s)) OVER "b", '
'MIN("a"."c1") OVER "b" FROM "t" AS "a" '
'WINDOW "b" AS (PARTITION BY "a"."c1")')
self.assertEqual(query.params, (0, ))
window = Window([DatePart('year', self.table.date_col)])
query = self.table.select(Min(self.table.c1, window=window))
self.assertEqual(
str(query), 'SELECT MIN("a"."c1") OVER "b" FROM "t" AS "a" '
'WINDOW "b" AS (PARTITION BY DATE_PART(%s, "a"."date_col"))')
self.assertEqual(query.params, ('year', ))
def table_query(cls):
Opportunity = Pool().get('sale.opportunity')
opportunity_history = Opportunity.__table_history__()
columns = [
Min(Column(opportunity_history, '__id')).as_('id'),
opportunity_history.id.as_('opportunity'),
Min(
Coalesce(opportunity_history.write_date,
opportunity_history.create_date)).as_('date'),
Coalesce(opportunity_history.write_uid,
opportunity_history.create_uid).as_('user'),
]
group_by = [
opportunity_history.id,
Coalesce(opportunity_history.write_uid,
opportunity_history.create_uid),
]
for name, field in cls._fields.iteritems():
if name in ('id', 'opportunity', 'date', 'user'):
continue
if hasattr(field, 'set'):
continue
column = Column(opportunity_history, name)
columns.append(column.as_(name))
group_by.append(column)
return opportunity_history.select(*columns, group_by=group_by)
def test_select_having(self):
col1 = self.table.col1
col2 = self.table.col2
query = self.table.select(col1, Min(col2), having=(Min(col2) > 3))
self.assertEqual(
str(query), 'SELECT "a"."col1", MIN("a"."col2") FROM "t" AS "a" '
'HAVING (MIN("a"."col2") > %s)')
self.assertEqual(query.params, (3, ))
def pull(cls, database, connection, name=None):
cursor = connection.cursor()
queue = cls.__table__()
candidates = With('id', 'scheduled_at', 'expected_at',
query=queue.select(
queue.id,
queue.scheduled_at,
queue.expected_at,
where=((queue.name == name) if name else Literal(True))
& (queue.dequeued_at == Null),
order_by=[
queue.scheduled_at.nulls_first,
queue.expected_at.nulls_first]))
selected = With('id', query=candidates.select(
candidates.id,
where=((candidates.scheduled_at <= CurrentTimestamp())
| (candidates.scheduled_at == Null))
& database.lock_id(candidates.id),
order_by=[
candidates.scheduled_at.nulls_first,
candidates.expected_at.nulls_first],
limit=1))
next_timeout = With('seconds', query=candidates.select(
Min(Extract('second',
candidates.scheduled_at - CurrentTimestamp())
),
where=candidates.scheduled_at >= CurrentTimestamp()))
task_id, seconds = None, None
if database.has_returning():
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
where=queue.id == selected.select(selected.id),
with_=[candidates, selected, next_timeout],
returning=[
queue.id, next_timeout.select(next_timeout.seconds)])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, seconds = row
else:
query = queue.select(queue.id,
where=queue.id == selected.select(selected.id),
with_=[candidates, selected])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, = row
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
where=queue.id == task_id)
cursor.execute(*query)
query = next_timeout.select(next_timeout.seconds)
cursor.execute(*query)
row = cursor.fetchone()
if row:
seconds, = row
if not task_id and database.has_channel():
cursor.execute('LISTEN "%s"', (cls.__name__,))
return task_id, seconds
def table_query(cls):
pool = Pool()
Timesheet = pool.get('timesheet.line')
line = Timesheet.__table__()
timesheet = line.select(
Min(line.id * 2 + 1).as_('id'),
line.company.as_('company'),
line.employee.as_('employee'),
Sum(line.duration).as_('duration'),
line.date.as_('date'),
group_by=[line.company, line.employee, line.date])
attendance = super().table_query()
return (attendance.join(
timesheet,
'FULL' if backend.name != 'sqlite' else 'LEFT',
condition=(attendance.company == timesheet.company)
& (attendance.employee == timesheet.employee)
& (attendance.date == timesheet.date)).select(
Coalesce(attendance.id, timesheet.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
Coalesce(attendance.company, timesheet.company).as_('company'),
Coalesce(attendance.employee,
timesheet.employee).as_('employee'),
attendance.duration.as_('duration'),
timesheet.duration.as_('timesheet_duration'),
Coalesce(attendance.date, timesheet.date).as_('date'),
))
def _column_id(cls, tables):
line = tables['line']
template_category = tables['line.product.template_category']
# Pairing function from http://szudzik.com/ElegantPairing.pdf
return Min(
Case((line.id < template_category.id,
(template_category.id * template_category.id) + line.id),
else_=(line.id * line.id) + line.id + template_category.id))
def _column_id(cls, tables, withs):
pool = Pool()
Category = pool.get('product.category')
category = Category.__table__()
move = tables['move']
template_category = tables['move.product.template_category']
# Get a stable number of category over time
# by using number one order bigger.
nb_category = category.select(
Power(10, (Ceil(Log(Max(category.id))) + Literal(1))))
return Min(move.id * nb_category + template_category.id)
def _column_id(cls, tables, withs):
pool = Pool()
Category = pool.get('party.category')
category = Category.__table__()
line = tables['line']
party_category = tables['line.customer.party_category']
# Get a stable number of categories over time
# by using a number one order bigger.
nb_category = category.select(
Power(10, (Ceil(Log(Max(category.id))) + Literal(1))))
return Min(line.id * nb_category + party_category.id)
def get_distance(cls, parties, name):
distances = {p.id: None for p in parties}
query = cls._distance_query()
if query:
cursor = Transaction().connection.cursor()
cursor.execute(*query.select(
query.to.as_('to'),
Min(query.distance).as_('distance'),
group_by=[query.to]))
distances.update(cursor)
return distances
def table_query(cls):
pool = Pool()
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
Tax = pool.get('account.tax')
Date = pool.get('ir.date')
context = Transaction().context
invoice = Invoice.__table__()
invoice_tax = InvoiceTax.__table__()
tax = Tax.__table__()
amount = invoice_tax.base + invoice_tax.amount
month = Extract('MONTH', invoice.invoice_date)
where = ((invoice.company == context.get('company'))
& (invoice.state.in_(['posted', 'paid']))
& (tax.es_vat_list_code != Null)
& (Extract('year', invoice.invoice_date) == context.get(
'date', Date.today()).year))
return (invoice_tax.join(
invoice, condition=invoice_tax.invoice == invoice.id
).join(tax, condition=invoice_tax.tax == tax.id).select(
Max(invoice_tax.id).as_('id'),
Literal(0).as_('create_uid'),
Min(invoice_tax.create_date).as_('create_date'),
Literal(0).as_('write_uid'),
Max(invoice_tax.write_date).as_('write_date'),
invoice.tax_identifier.as_('company_tax_identifier'),
invoice.party.as_('party'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
tax.es_vat_list_code.as_('code'),
Sum(amount).as_('amount'),
Sum(amount,
filter_=month <= Literal(3)).as_('first_period_amount'),
Sum(amount,
filter_=((month > Literal(3)) &
(month <= Literal(6)))).as_('second_period_amount'),
Sum(amount,
filter_=((month > Literal(6)) &
(month <= Literal(9)))).as_('third_period_amount'),
Sum(amount,
filter_=((month > Literal(9)) &
(month <= Literal(12)))).as_('fourth_period_amount'),
invoice.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.type,
invoice.party,
invoice.party_tax_identifier,
invoice.currency,
tax.es_vat_list_code,
]))
def test_order_params(self):
with_ = With(
query=self.table.select(self.table.c, where=(self.table.c > 1)))
w = Window([Literal(8)])
query = Select([Literal(2), Min(self.table.c, window=w)],
from_=self.table.select(where=self.table.c > 3),
with_=with_,
where=self.table.c > 4,
group_by=[Literal(5)],
order_by=[Literal(6)],
having=Literal(7))
self.assertEqual(query.params, (1, 2, 3, 4, 5, 6, 7, 8))
def table_query(cls):
pool = Pool()
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
Move = pool.get('account.move')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
invoice = Invoice.__table__()
invoice_tax = InvoiceTax.__table__()
move = Move.__table__()
period = Period.__table__()
tax = Tax.__table__()
sales = super().table_query()
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear')))
if context.get('period'):
where &= (period.id == context.get('period'))
where &= ((tax.es_ec_purchases_list_code != Null)
& (tax.es_ec_purchases_list_code != ''))
where &= invoice.type == 'in'
purchases = (invoice_tax.join(
invoice, condition=invoice_tax.invoice == invoice.id
).join(tax, condition=invoice_tax.tax == tax.id).join(
move, condition=invoice.move == move.id).join(
period, condition=move.period == period.id).select(
Max(invoice_tax.id).as_('id'),
Literal(0).as_('create_uid'),
Min(invoice_tax.create_date).as_('create_date'),
Literal(0).as_('write_uid'),
Max(invoice_tax.write_date).as_('write_date'),
invoice.tax_identifier.as_('company_tax_identifier'),
invoice.party.as_('party'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
tax.es_ec_purchases_list_code.as_('code'),
Sum(invoice_tax.base).as_('amount'),
invoice.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party,
invoice.party_tax_identifier,
tax.es_ec_purchases_list_code,
invoice.currency,
]))
return sales | purchases
def _get_checkout_column(cls, checkout_table, name):
column, where = None, None
if name == 'checkedout_books':
column = Count(checkout_table.id)
where = checkout_table.return_date == Null
elif name == 'late_checkedout_books':
column = Count(checkout_table.id)
where = (checkout_table.return_date
== Null) & (checkout_table.date < datetime.date.today() +
datetime.timedelta(days=20))
elif name == 'expected_return_date':
column = Min(checkout_table.date)
where = checkout_table.return_date == Null
else:
raise Exception('Invalid function field name %s' % name)
return column, where
def table_query(cls):
pool = Pool()
Line = pool.get('attendance.sheet.line')
line = Line.__table__()
return line.select(
(Min(line.id * 2)).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
line.company.as_('company'),
line.employee.as_('employee'),
Sum(line.duration).as_('duration'),
line.date.as_('date'),
group_by=[line.company, line.employee, line.date])
def order_distance(cls, tables):
party, _ = tables[None]
key = 'distance'
if key not in tables:
query = cls._distance_query()
if not query:
return []
query = query.select(
query.to.as_('to'),
Min(query.distance).as_('distance'),
group_by=[query.to])
join = party.join(query, type_='LEFT',
condition=query.to == party.id)
tables[key] = {
None: (join.right, join.condition),
}
else:
query, _ = tables[key][None]
return [query.distance]
def search_expected_return_date(cls, name, clause):
user = cls.__table__()
checkout = Pool().get('library.user.checkout').__table__()
_, operator, value = clause
if isinstance(value, datetime.date):
value = value + datetime.timedelta(days=-20)
if isinstance(value, (list, tuple)):
value = [(x + datetime.timedelta(days=-20) if x else x)
for x in value]
Operator = SQL_OPERATORS[operator]
query_table = user.join(checkout,
'LEFT OUTER',
condition=checkout.user == user.id)
query = query_table.select(user.id,
where=(checkout.return_date == Null) |
(checkout.id == Null),
group_by=user.id,
having=Operator(Min(checkout.date), value))
return [('id', 'in', query)]
def table_query(cls):
pool = Pool()
Company = pool.get('company.company')
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
Move = pool.get('account.move')
Line = pool.get('account.move.line')
TaxLine = pool.get('account.tax.line')
Tax = pool.get('account.tax')
TaxCode = pool.get('account.tax.code')
TaxCodeLine = pool.get('account.tax.code.line')
Date = pool.get('ir.date')
context = Transaction().context
company = Company.__table__()
invoice = Invoice.__table__()
cancel_invoice = Invoice.__table__()
move = Move.__table__()
cancel_move = Move.__table__()
line = Line.__table__()
tax_line = TaxLine.__table__()
tax = Tax.__table__()
tax_code = TaxCode.__table__()
tax_code_line = TaxCodeLine.__table__()
exclude_invoice_tax = InvoiceTax.__table__()
amount = tax_line.amount
month = Extract('MONTH', invoice.invoice_date)
excluded_taxes = (tax_code_line
.join(tax_code,
condition=(tax_code.id == tax_code_line.code)
).select(
tax_code_line.tax, distinct=True,
where=tax_code.aeat_report.in_(cls.excluded_tax_codes())))
where = ((invoice.company == context.get('company'))
& (tax.es_vat_list_code != Null)
& (Extract('year', invoice.invoice_date)
== context.get('date', Date.today()).year)
# Exclude base amount for es_reported_with taxes because it is
# already included in the base of main tax
& ((tax.es_reported_with == Null) | (tax_line.type == 'tax'))
& ~Exists(cancel_invoice
.join(cancel_move,
condition=cancel_invoice.cancel_move == cancel_move.id)
.select(cancel_invoice.id, distinct=True,
where=((cancel_invoice.id == invoice.id)
& (~cancel_move.origin.like('account.invoice,%')))))
# Use exists to exclude the full invoice when it has multiple taxes
& ~Exists(exclude_invoice_tax.select(
exclude_invoice_tax.invoice,
where=((exclude_invoice_tax.invoice == invoice.id)
& (exclude_invoice_tax.tax.in_(excluded_taxes))))))
return (tax_line
.join(tax, condition=tax_line.tax == tax.id)
.join(line, condition=tax_line.move_line == line.id)
.join(move, condition=line.move == move.id)
.join(invoice, condition=invoice.move == move.id)
.join(company, condition=company.id == invoice.company)
.select(
Min(tax_line.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
invoice.tax_identifier.as_('company_tax_identifier'),
invoice.party.as_('party'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
tax.es_vat_list_code.as_('code'),
Sum(amount).as_('amount'),
Sum(amount, filter_=month <= Literal(3)).as_(
'first_period_amount'),
Sum(amount, filter_=(
(month > Literal(3)) & (month <= Literal(6)))).as_(
'second_period_amount'),
Sum(amount, filter_=(
(month > Literal(6)) & (month <= Literal(9)))).as_(
'third_period_amount'),
Sum(amount, filter_=(
(month > Literal(9)) & (month <= Literal(12)))).as_(
'fourth_period_amount'),
company.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.type,
invoice.party,
invoice.party_tax_identifier,
company.currency,
tax.es_vat_list_code,
]))
def table_query(cls):
pool = Pool()
Company = pool.get('company.company')
Invoice = pool.get('account.invoice')
Move = pool.get('account.move')
Line = pool.get('account.move.line')
TaxLine = pool.get('account.tax.line')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
company = Company.__table__()
invoice = Invoice.__table__()
cancel_invoice = Invoice.__table__()
move = Move.__table__()
cancel_move = Move.__table__()
line = Line.__table__()
tax_line = TaxLine.__table__()
period = Period.__table__()
tax = Tax.__table__()
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear'))
& ~tax.es_exclude_from_vat_book)
where &= ~Exists(cancel_invoice
.join(cancel_move,
condition=cancel_invoice.cancel_move == cancel_move.id)
.select(cancel_invoice.id, distinct=True,
where=((cancel_invoice.id == invoice.id)
& (~cancel_move.origin.like('account.invoice,%')))))
groups = cls.included_tax_groups()
if groups:
where &= tax.group.in_(groups)
if context.get('start_period'):
start_period = Period(context['start_period'])
where &= (period.start_date >= start_period.start_date)
if context.get('end_period'):
end_period = Period(context['end_period'])
where &= (period.end_date <= end_period.end_date)
query = (tax_line
.join(tax, condition=tax_line.tax == tax.id)
.join(line, condition=tax_line.move_line == line.id)
.join(move, condition=line.move == move.id)
.join(period, condition=move.period == period.id)
.join(invoice, condition=invoice.move == move.id)
.join(company, condition=company.id == invoice.company)
.select(
Min(tax_line.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
invoice.id.as_('invoice'),
invoice.invoice_date.as_('invoice_date'),
invoice.party.as_('party'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
Coalesce(tax.es_reported_with, tax.id).as_('tax'),
Sum(tax_line.amount,
filter_=((tax_line.type == 'base')
& (tax.es_reported_with == Null))).as_('base_amount'),
Coalesce(
Sum(tax_line.amount,
filter_=((tax_line.type == 'tax')
& (tax.es_reported_with == Null))),
0).as_('tax_amount'),
Min(tax.id,
filter_=(tax.es_reported_with != Null)).as_(
'surcharge_tax'),
Coalesce(Sum(tax_line.amount,
filter_=((tax_line.type == 'tax')
& (tax.es_reported_with != Null))), 0).as_(
'surcharge_tax_amount'),
where=where,
group_by=[
invoice.id,
invoice.party,
invoice.invoice_date,
invoice.party_tax_identifier,
Coalesce(tax.es_reported_with, tax.id),
]))
return query
def table_query(cls):
pool = Pool()
Attendance = pool.get('attendance.line')
transaction = Transaction()
database = transaction.database
attendance = Attendance.__table__()
if database.has_window_functions():
window = Window([attendance.employee],
order_by=[attendance.at.asc],
frame='ROWS',
start=0,
end=1)
type = NthValue(attendance.type, 1, window=window)
from_ = NthValue(attendance.at, 1, window=window)
to = NthValue(attendance.at, 2, window=window)
date = NthValue(attendance.date, 1, window=window)
query = attendance.select(attendance.id.as_('id'),
attendance.company.as_('company'),
attendance.employee.as_('employee'),
type.as_('type'), from_.as_('from_'),
to.as_('to'), date.as_('date'))
sheet = (Min(query.id * 2,
window=Window([query.employee, query.date])))
else:
next_attendance = Attendance.__table__()
to = next_attendance.select(
next_attendance.at,
where=(next_attendance.employee == attendance.employee)
& (next_attendance.at > attendance.at),
order_by=[next_attendance.at.asc],
limit=1)
query = attendance.select(attendance.id.as_('id'),
attendance.company.as_('company'),
attendance.employee.as_('employee'),
attendance.type.as_('type'),
attendance.at.as_('from_'), to.as_('to'),
attendance.date.as_('date'))
query2 = copy.copy(query)
sheet = query2.select(Min(query2.id * 2),
where=(query2.employee == query.employee)
& (query2.date == query.date))
from_ = Column(query, 'from_')
if backend.name == 'sqlite':
# As SQLite does not support operation on datetime
# we convert datetime into seconds
duration = (SQLiteStrftime('%s', query.to) -
SQLiteStrftime('%s', from_))
else:
duration = query.to - from_
return query.select(
query.id.as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
query.company.as_('company'),
query.employee.as_('employee'),
from_.as_('from_'),
query.to.as_('to'),
query.date.as_('date'),
duration.as_('duration'),
sheet.as_('sheet'),
where=query.type == 'in')
def table_query(cls):
pool = Pool()
Identifier = pool.get('party.identifier')
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
ModelData = pool.get('ir.model.data')
Move = pool.get('account.move')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
company_identifier = Identifier.__table__()
party_identifier = Identifier.__table__()
invoice = Invoice.__table__()
invoice_tax = InvoiceTax.__table__()
move = Move.__table__()
period = Period.__table__()
tax = Tax.__table__()
groups = []
for module, fs_id in cls.tax_groups():
try:
groups.append(ModelData.get_id(module, fs_id))
except KeyError:
# table_query can be called before the XML is loaded
continue
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear')))
where &= invoice.type == 'out'
where &= (company_identifier.code.ilike('BE%')
& (company_identifier.type == 'eu_vat'))
where &= (party_identifier.code.ilike('BE%')
& (party_identifier.type == 'eu_vat'))
where &= tax.group.in_(groups)
return (
invoice_tax.join(
invoice, condition=invoice_tax.invoice == invoice.id).join(
tax, condition=invoice_tax.tax == tax.id).join(
move, condition=invoice.move == move.id).join(
period, condition=move.period == period.id).join(
company_identifier,
condition=invoice.tax_identifier ==
company_identifier.id).join(
party_identifier,
condition=invoice.party_tax_identifier ==
party_identifier.id).
select(Max(invoice_tax.id).as_('id'),
Literal(0).as_('create_uid'),
Min(invoice_tax.create_date).as_('create_date'),
Literal(0).as_('write_uid'),
Max(invoice_tax.write_date).as_('write_date'),
invoice.tax_identifier.as_('company_tax_identifier'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
Sum(invoice_tax.base).as_('turnover'),
Sum(invoice_tax.amount).as_('vat'),
invoice.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party_tax_identifier,
invoice.currency,
]))
def _column_id(cls, tables):
line = tables['line']
return Min(line.id)
def _get_translation_join(self, Model, name, translation, model, table,
from_, language):
if Model.__name__ == 'ir.model.field':
pool = Pool()
IrModel = pool.get('ir.model')
ModelData = pool.get('ir.model.data')
ModelField = pool.get('ir.model.field')
Translation = pool.get('ir.translation')
model = IrModel.__table__()
model_data = ModelData.__table__()
model_field = ModelField.__table__()
msg_trans = Translation.__table__()
if name == 'field_description':
type_ = 'field'
else:
type_ = 'help'
translation = translation.select(
translation.id.as_('id'),
translation.res_id.as_('res_id'),
translation.value.as_('value'),
translation.name.as_('name'),
translation.lang.as_('lang'),
translation.type.as_('type'),
translation.fuzzy.as_('fuzzy'),
)
translation |= (msg_trans.join(
model_data,
condition=(msg_trans.res_id == model_data.db_id)
& (model_data.model == 'ir.message')
& (msg_trans.name == 'ir.message,text')).join(
model_field,
condition=Concat(
Concat(model_data.module, '.'),
model_data.fs_id) == getattr(model_field, name)).join(
model,
condition=model_field.model == model.id).select(
msg_trans.id.as_('id'),
Literal(-1).as_('res_id'),
msg_trans.value.as_('value'),
Concat(Concat(model.model, ','),
model_field.name).as_('name'),
msg_trans.lang.as_('lang'),
Literal(type_).as_('type'),
msg_trans.fuzzy.as_('fuzzy'),
))
if backend.name == 'postgresql' and _sql_version >= (1, 1, 0):
query = translation.select(
translation.res_id.as_('res_id'),
translation.value.as_('value'),
translation.name.as_('name'),
distinct=True,
distinct_on=[translation.res_id, translation.name],
order_by=[
translation.res_id, translation.name, translation.id.desc
])
else:
query = translation.select(
translation.res_id.as_('res_id'),
Min(translation.value).as_('value'),
translation.name.as_('name'),
group_by=[translation.res_id, translation.name])
if Model.__name__ == 'ir.model':
name_ = Concat(Concat(table.model, ','), name)
type_ = 'model'
res_id = -1
elif Model.__name__ == 'ir.model.field':
from_ = from_.join(model,
'LEFT',
condition=model.id == table.model)
name_ = Concat(Concat(model.model, ','), table.name)
if name == 'field_description':
type_ = 'field'
else:
type_ = 'help'
res_id = -1
else:
name_ = '%s,%s' % (Model.__name__, name)
type_ = 'model'
res_id = table.id
query.where = ((translation.lang == language)
& (translation.type == type_)
& (translation.fuzzy == Literal(False)))
return query, from_.join(query,
'LEFT',
condition=(query.res_id == res_id) &
(query.name == name_))
def _column_id(cls, tables, withs):
move = tables['move']
return Min(move.id)
def table_query(cls):
pool = Pool()
Company = pool.get('company.company')
Invoice = pool.get('account.invoice')
Move = pool.get('account.move')
Line = pool.get('account.move.line')
TaxLine = pool.get('account.tax.line')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
company = Company.__table__()
invoice = Invoice.__table__()
cancel_invoice = Invoice.__table__()
move = Move.__table__()
cancel_move = Move.__table__()
line = Line.__table__()
tax_line = TaxLine.__table__()
period = Period.__table__()
tax = Tax.__table__()
sales = super().table_query()
where = invoice.company == context.get('company')
if context.get('start_date'):
where &= (move.date >= context.get('start_date'))
if context.get('end_date'):
where &= (move.date <= context.get('end_date'))
where &= ((tax.es_ec_purchases_list_code != Null)
& (tax.es_ec_purchases_list_code != ''))
where &= tax_line.type == 'base'
where &= invoice.type == 'in'
where &= ~Exists(cancel_invoice
.join(cancel_move,
condition=cancel_invoice.cancel_move == cancel_move.id)
.select(cancel_invoice.id, distinct=True,
where=((cancel_invoice.id == invoice.id)
& (~cancel_move.origin.like('account.invoice,%')))))
purchases = (tax_line
.join(tax, condition=tax_line.tax == tax.id)
.join(line, condition=tax_line.move_line == line.id)
.join(move, condition=line.move == move.id)
.join(period, condition=move.period == period.id)
.join(invoice, condition=invoice.move == move.id)
.join(company, condition=company.id == invoice.company)
.select(
Min(tax_line.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
invoice.tax_identifier.as_('company_tax_identifier'),
invoice.party.as_('party'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
tax.es_ec_purchases_list_code.as_('code'),
Sum(tax_line.amount).as_('amount'),
company.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party,
invoice.party_tax_identifier,
tax.es_ec_purchases_list_code,
company.currency,
]))
return sales | purchases