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 get_lastmodified(cls, uri, cache=None):
pool = Pool()
Party = pool.get('party.party')
Address = pool.get('party.address')
ContactMechanism = pool.get('party.contact_mechanism')
party = Party.__table__()
address = Address.__table__()
contact_mechanism = ContactMechanism.__table__()
cursor = Transaction().cursor
party_id = cls.vcard(uri)
if party_id:
if cache is not None:
cache.setdefault('_contact', {})
ids = cache['_contact'].keys()
if party_id not in ids:
ids.append(party_id)
elif 'lastmodified' in cache['_contact'][party_id]:
return cache['_contact'][party_id]['lastmodified']
else:
ids = [party_id]
res = None
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(party.id, sub_ids)
cursor.execute(*party.join(
address, 'LEFT', condition=party.id == address.party).join(
contact_mechanism,
'LEFT',
condition=party.id == contact_mechanism.party).select(
party.id,
Max(
Extract(
'EPOCH',
Coalesce(party.write_date,
party.create_date))),
Max(
Extract(
'EPOCH',
Coalesce(address.write_date,
address.create_date))),
Max(
Extract(
'EPOCH',
Coalesce(contact_mechanism.write_date,
contact_mechanism.create_date))),
where=red_sql,
group_by=party.id))
for party_id2, date_p, date_a, date_c in cursor.fetchall():
date = max(date_p, date_a, date_c)
if party_id2 == party_id:
res = date
if cache is not None:
cache['_contact'].setdefault(party_id2, {})
cache['_contact'][party_id2]['lastmodified'] = date
if res is not None:
return res
return super(Collection, cls).get_lastmodified(uri, cache=cache)
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 table_query(cls):
query = super(SaleOpportunityMonthly, 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_column = Extract('MONTH', opportunity.start_date).as_('month')
query.columns += (Max(
Extract('MONTH', opportunity.start_date) +
Extract('YEAR', opportunity.start_date) *
100).cast(type_id).as_('id'), year_column, month_column)
query.group_by = (year_column, month_column, opportunity.company)
return query
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 get_creationdate(cls, uri, cache=None):
Party = Pool().get('party.party')
party = Party.__table__()
party_id = cls.vcard(uri)
cursor = Transaction().connection.cursor()
if party_id is None:
raise DAV_NotFound
if party_id:
if cache is not None:
cache.setdefault('_contact', {})
ids = cache['_contact'].keys()
if party_id not in ids:
ids.append(party_id)
elif 'creationdate' in cache['_contact'][party_id]:
return cache['_contact'][party_id]['creationdate']
else:
ids = [party_id]
res = None
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(party.id, sub_ids)
cursor.execute(*party.select(party.id,
Extract('EPOCH', party.create_date),
where=red_sql))
for party_id2, date in cursor.fetchall():
if party_id2 == party_id:
res = date
if cache is not None:
cache['_contact'].setdefault(party_id2, {})
cache['_contact'][party_id2]['creationdate'] = date
if res is not None:
return res
return super(Collection, cls).get_creationdate(uri, cache=cache)
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 table_query(cls):
pool = Pool()
Line = pool.get('timesheet.line')
line = Line.__table__()
year_column = Extract('YEAR', line.date).as_('year')
week_column = Extract('WEEK', line.date).as_('week')
return line.select(Max(
Extract('WEEK', line.date) + Extract('YEAR', line.date) * 100 +
line.employee * 1000000).as_('id'),
Max(line.create_uid).as_('create_uid'),
Max(line.create_date).as_('create_date'),
Max(line.write_uid).as_('write_uid'),
Max(line.write_date).as_('write_date'),
year_column,
week_column,
line.employee,
Sum(line.duration).as_('duration'),
group_by=(year_column, week_column, line.employee))
def table_query(cls):
pool = Pool()
Line = pool.get('timesheet.line')
line = Line.__table__()
type_name = cls.year.sql_type().base
year_column = Extract('YEAR', line.date).cast(type_name).as_('year')
month_column = Extract('MONTH', line.date).as_('month')
return line.select(
Max(Extract('MONTH', line.date)
+ Extract('YEAR', line.date) * 100
+ line.employee * 1000000).as_('id'),
Max(line.create_uid).as_('create_uid'),
Max(line.create_date).as_('create_date'),
Max(line.write_uid).as_('write_uid'),
Max(line.write_date).as_('write_date'),
year_column,
month_column,
line.employee,
Sum(line.duration).as_('duration'),
group_by=(year_column, month_column, line.employee))
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
table = cls.__table_handler__(module_name)
sql_table = cls.__table__()
pool = Pool()
Work = pool.get('project.work')
work = Work.__table__()
created_progress = not table.column_exist('progress')
effort_exist = table.column_exist('effort_duration')
super().__register__(module_name)
# Migration from 5.0: Effort renamed into to progress
if created_progress and effort_exist:
# Don't use UPDATE FROM because SQLite does not support it.
value = work.select((Extract('EPOCH', sql_table.effort_duration) /
Extract('EPOCH', work.effort_duration)),
where=work.id == sql_table.work)
cursor.execute(*sql_table.update([sql_table.progress], [value]))
def table_query(cls):
pool = Pool()
Line = pool.get('timesheet.line')
Month = pool.get('ir.calendar.month')
line = Line.__table__()
month = Month.__table__()
year_column = Extract('YEAR', line.date).as_('year')
month_index = Extract('MONTH', line.date)
return line.join(month, condition=month_index == month.id).select(
Max(
Extract('MONTH', line.date) +
Extract('YEAR', line.date) * 100 +
line.employee * 1000000).as_('id'),
Max(line.create_uid).as_('create_uid'),
Max(line.create_date).as_('create_date'),
Max(line.write_uid).as_('write_uid'),
Max(line.write_date).as_('write_date'),
year_column,
month.id.as_('month'),
line.employee,
Sum(line.duration).as_('duration'),
group_by=(year_column, month.id, line.employee))
def table_query(cls):
pool = Pool()
Month = pool.get('ir.calendar.month')
month = Month.__table__()
query = super(SaleOpportunityMonthly, cls).table_query()
opportunity, = query.from_
month_timestamp = DateTrunc('MONTH', opportunity.start_date)
id_ = Extract('EPOCH', month_timestamp)
month = cls.month.sql_cast(month_timestamp)
query.columns += (
id_.as_('id'),
month.as_('month'),
)
query.group_by = [id_, month, opportunity.company]
return query
def get_lastmodified(cls, uri, cache=None):
Todo = Pool().get('calendar.todo')
todo = Todo.__table__()
transaction = Transaction()
cursor = transaction.connection.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,
transaction.database.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 get_creationdate(cls, uri, cache=None):
Todo = Pool().get('calendar.todo')
todo = Todo.__table__()
cursor = Transaction().connection.cursor()
calendar_id = cls.calendar(uri)
if not calendar_id:
calendar_id = cls.calendar(uri, ics=True)
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 'creationdate' in cache['_calendar'][
Todo.__name__][todo_id]:
return cache['_calendar'][
Todo.__name__][todo_id]['creationdate']
else:
ids = [todo_id]
res = None
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(todo.id, sub_ids)
cursor.execute(
*todo.select(todo.id,
Extract('EPOCH', todo.create_date),
where=red_sql))
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]['creationdate'] = date
if res is not None:
return res
return super(Collection, cls).get_creationdate(uri, cache=cache)
def get_lastmodified(cls, uri, cache=None):
pool = Pool()
Calendar = pool.get('calendar.calendar')
Event = pool.get('calendar.event')
calendar = Calendar.__table__()
event = Event.__table__()
transaction = Transaction()
cursor = transaction.connection.cursor()
calendar_id = cls.calendar(uri)
if calendar_id:
if not (uri[10:].split('/', 1) + [None])[1]:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Calendar.__name__, {})
ids = list(cache['_calendar'][Calendar.__name__].keys())
if calendar_id not in ids:
ids.append(calendar_id)
elif 'lastmodified' in cache['_calendar'][
Calendar.__name__][calendar_id]:
return cache['_calendar'][Calendar.__name__][
calendar_id]['lastmodified']
else:
ids = [calendar_id]
res = None
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(calendar.id, sub_ids)
cursor.execute(*calendar.select(calendar.id,
Extract('EPOCH', Coalesce(calendar.write_date,
calendar.create_date)),
where=red_sql))
for calendar_id2, date in cursor.fetchall():
if calendar_id2 == calendar_id:
res = date
if cache is not None:
cache['_calendar'][Calendar.__name__]\
.setdefault(calendar_id2, {})
cache['_calendar'][Calendar.__name__][
calendar_id2]['lastmodified'] = date
if res is not None:
return res
else:
event_id = cls.event(uri, calendar_id=calendar_id)
if event_id:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Event.__name__, {})
ids = list(cache['_calendar'][Event.__name__].keys())
if event_id not in ids:
ids.append(event_id)
elif 'lastmodified' in cache['_calendar'][
Event.__name__][event_id]:
return cache['_calendar'][Event.__name__][
event_id]['lastmodified']
else:
ids = [event_id]
res = None
# for sub_ids in grouped_slice(ids,
# transaction.database.IN_MAX / 2):
for sub_ids in grouped_slice(ids):
red_id_sql = reduce_ids(event.id, sub_ids)
red_parent_sql = reduce_ids(event.parent, sub_ids)
cursor.execute(*event.select(
Coalesce(event.parent, event.id),
Max(Extract('EPOCH', Coalesce(event.write_date,
event.create_date))),
where=red_id_sql | red_parent_sql,
group_by=(event.parent, event.id)))
for event_id2, date in cursor.fetchall():
if event_id2 == event_id:
res = date
if cache is not None:
cache['_calendar'][Event.__name__]\
.setdefault(event_id2, {})
cache['_calendar'][Event.__name__][
event_id2]['lastmodified'] = date
if res is not None:
return res
calendar_ics_id = cls.calendar(uri, ics=True)
if calendar_ics_id:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Calendar.__name__, {})
ids = list(cache['_calendar'][Calendar.__name__].keys())
if calendar_ics_id not in ids:
ids.append(calendar_ics_id)
elif 'lastmodified ics' in cache['_calendar'][
Calendar.__name__][calendar_ics_id]:
return cache['_calendar'][Calendar.__name__][
calendar_ics_id]['lastmodified ics']
else:
ids = [calendar_ics_id]
res = None
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(event.calendar, sub_ids)
cursor.execute(*event.select(event.calendar,
Max(Extract('EPOCH', Coalesce(event.write_date,
event.create_date))),
where=red_sql,
group_by=event.calendar))
for calendar_id2, date in cursor.fetchall():
if calendar_id2 == calendar_ics_id:
res = date
if cache is not None:
cache['_calendar'][Calendar.__name__]\
.setdefault(calendar_id2, {})
cache['_calendar'][Calendar.__name__][
calendar_id2]['lastmodified ics'] = date
if res is not None:
return res
return super(Collection, cls).get_lastmodified(uri, cache=cache)
def get_creationdate(cls, uri, cache=None):
Calendar = Pool().get('calendar.calendar')
Event = Pool().get('calendar.event')
calendar = Calendar.__table__()
event = Event.__table__()
calendar_id = cls.calendar(uri)
if not calendar_id:
calendar_id = cls.calendar(uri, ics=True)
if calendar_id:
if not (uri[10:].split('/', 1) + [None])[1]:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Calendar.__name__, {})
ids = list(cache['_calendar'][Calendar.__name__].keys())
if calendar_id not in ids:
ids.append(calendar_id)
elif 'creationdate' in cache['_calendar'][
Calendar.__name__][calendar_id]:
return cache['_calendar'][Calendar.__name__][
calendar_id]['creationdate']
else:
ids = [calendar_id]
res = None
cursor = Transaction().connection.cursor()
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(calendar.id, sub_ids)
cursor.execute(*calendar.select(calendar.id,
Extract('EPOCH', calendar.create_date),
where=red_sql))
for calendar_id2, date in cursor.fetchall():
if calendar_id2 == calendar_id:
res = date
if cache is not None:
cache['_calendar'][Calendar.__name__]\
.setdefault(calendar_id2, {})
cache['_calendar'][Calendar.__name__][
calendar_id2]['creationdate'] = date
if res is not None:
return res
else:
event_id = cls.event(uri, calendar_id=calendar_id)
if event_id:
if cache is not None:
cache.setdefault('_calendar', {})
cache['_calendar'].setdefault(Event.__name__, {})
ids = list(cache['_calendar'][Event.__name__].keys())
if event_id not in ids:
ids.append(event_id)
elif 'creationdate' in cache['_calendar'][
Event.__name__][event_id]:
return cache['_calendar'][Event.__name__][
event_id]['creationdate']
else:
ids = [event_id]
res = None
cursor = Transaction().connection.cursor()
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(event.id, sub_ids)
cursor.execute(*event.select(event.id,
Extract('EPOCH', event.create_date),
where=red_sql))
for event_id2, date in cursor.fetchall():
if event_id2 == event_id:
res = date
if cache is not None:
cache['_calendar'][Event.__name__]\
.setdefault(event_id2, {})
cache['_calendar'][Event.__name__][
event_id2]['creationdate'] = date
if res is not None:
return res
return super(Collection, cls).get_creationdate(uri, cache=cache)
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,
]))