def get_total(cls, categories, names):
pool = Pool()
ReportingCategory = pool.get('sale.reporting.category')
table = cls.__table__()
reporting_category = ReportingCategory.__table__()
cursor = Transaction().connection.cursor()
categories = cls.search([
('parent', 'child_of', [c.id for c in categories]),
])
ids = [c.id for c in categories]
parents = {}
reporting_categories = []
for sub_ids in grouped_slice(ids):
sub_ids = list(sub_ids)
where = reduce_ids(table.id, sub_ids)
cursor.execute(*table.select(table.id, table.parent, where=where))
parents.update(cursor.fetchall())
where = reduce_ids(reporting_category.id, sub_ids)
cursor.execute(
*reporting_category.select(reporting_category.id, where=where))
reporting_categories.extend(r for r, in cursor.fetchall())
result = {}
reporting_categories = ReportingCategory.browse(reporting_categories)
for name in names:
values = dict.fromkeys(ids, 0)
values.update(
(c.id, getattr(c, name)) for c in reporting_categories)
result[name] = cls._sum_tree(categories, values, parents)
return result
def validate_move(cls, moves):
'''
Validate balanced move
'''
pool = Pool()
MoveLine = pool.get('tmi.move.line')
line = MoveLine.__table__()
cursor = Transaction().connection.cursor()
amounts = {}
move2draft_lines = {}
for sub_move_ids in grouped_slice([m.id for m in moves]):
red_sql = reduce_ids(line.move, sub_move_ids)
#cursor.execute(*line.select(line.move,
#Sum(line.debit - line.credit),
# where=red_sql,
# group_by=line.move))
#amounts.update(dict(cursor.fetchall()))
cursor.execute(*line.select(line.move, line.id,
where=red_sql & (line.state == 'draft'),
order_by=line.move))
move2draft_lines.update(dict((k, [j[1] for j in g])
for k, g in groupby(cursor.fetchall(), itemgetter(0))))
valid_moves = []
draft_moves = []
for move in moves:
#if move.id not in amounts:
# continue
#amount = amounts[move.id]
# SQLite uses float for SUM
#if not isinstance(amount, Decimal):
# amount = Decimal(amount)
draft_lines = MoveLine.browse(move2draft_lines.get(move.id, []))
#if not move.company.currency.is_zero(amount):
# draft_moves.append(move.id)
# continue
if not draft_lines:
continue
valid_moves.append(move.id)
for move_ids, state in (
(valid_moves, 'valid'),
(draft_moves, 'draft'),
):
if move_ids:
for sub_ids in grouped_slice(move_ids):
red_sql = reduce_ids(line.move, sub_ids)
# Use SQL to prevent double validate loop
cursor.execute(*line.update(
columns=[line.state],
values=[state],
where=red_sql))
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_receivable_payable(cls, parties, names):
'''
Function to compute receivable, payable (today or not) for party ids.
'''
result = {}
pool = Pool()
MoveLine = pool.get('account.move.line')
Account = pool.get('account.account')
AccountType = pool.get('account.account.type')
User = pool.get('res.user')
Date = pool.get('ir.date')
cursor = Transaction().connection.cursor()
line = MoveLine.__table__()
account = Account.__table__()
account_type = AccountType.__table__()
for name in names:
if name not in ('receivable', 'payable', 'receivable_today',
'payable_today'):
raise Exception('Bad argument')
result[name] = dict((p.id, Decimal('0.0')) for p in parties)
user = User(Transaction().user)
if not user.company:
return result
company_id = user.company.id
exp = Decimal(str(10.0**-user.company.currency.digits))
with Transaction().set_context(company=company_id):
today = Date.today()
amount = Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0))
for name in names:
code = name
today_where = Literal(True)
if name in ('receivable_today', 'payable_today'):
code = name[:-6]
today_where = ((line.maturity_date <= today)
| (line.maturity_date == Null))
for sub_parties in grouped_slice(parties):
sub_ids = [p.id for p in sub_parties]
party_where = reduce_ids(line.party, sub_ids)
cursor.execute(
*line.join(account, condition=account.id == line.account).
join(account_type,
condition=account.type == account_type.id).select(
line.party,
amount,
where=(getattr(account_type, code)
& (line.reconciliation == Null)
& (account.company == company_id)
& party_where
& today_where),
group_by=line.party))
for party, value in cursor:
# SQLite uses float for SUM
if not isinstance(value, Decimal):
value = Decimal(str(value))
result[name][party] = value.quantize(exp)
return result
def get_creationdate(cls, uri, cache=None):
Party = Pool().get('party.party')
party_id = cls.vcard(uri)
cursor = Transaction().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 i in range(0, len(ids), cursor.IN_MAX):
sub_ids = ids[i:i + cursor.IN_MAX]
red_sql, red_ids = reduce_ids('id', sub_ids)
cursor.execute('SELECT id, '
'EXTRACT(epoch FROM create_date) '
'FROM "' + Party._table + '" '
'WHERE ' + red_sql, red_ids)
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_balance_query(cls, sub_ids):
pool = Pool()
Budget = pool.get('account.budget')
BudgetAccount = pool.get('account.budget-account.account')
Move = pool.get('account.move')
MoveLine = pool.get('account.move.line')
table = cls.__table__()
table_a = Budget.__table__()
table_c = Budget.__table__()
account = BudgetAccount.__table__()
move = Move.__table__()
line = MoveLine.__table__()
balance = Sum(Coalesce(line.credit, 0) - Coalesce(line.debit, 0))
return table.join(table_a,
condition=(table_a.id == table.budget)
).join(table_c,
condition=(table_c.left >= table_a.left)
& (table_c.right <= table_a.right)
).join(account, condition=account.budget == table_c.id
).join(line, condition=line.account == account.account
).join(move, condition=((move.id == line.move)
& (move.period == table.period))).select(
table.id, balance,
where=reduce_ids(table.id, sub_ids),
group_by=table.id)
def get_pending_amount(cls, agents, name):
pool = Pool()
Commission = pool.get('commission')
commission = Commission.__table__()
cursor = Transaction().connection.cursor()
ids = [a.id for a in agents]
amounts = dict.fromkeys(ids, None)
for sub_ids in grouped_slice(ids):
where = reduce_ids(commission.agent, sub_ids)
where &= commission.invoice_line == Null
query = commission.select(commission.agent,
Sum(commission.amount),
where=where,
group_by=commission.agent)
cursor.execute(*query)
amounts.update(dict(cursor.fetchall()))
digits = cls.pending_amount.digits
exp = Decimal(str(10.0**-digits[1]))
for agent_id, amount in amounts.iteritems():
if amount:
# SQLite uses float for SUM
if not isinstance(amount, Decimal):
amount = Decimal(str(amount))
amounts[agent_id] = amount.quantize(exp)
return amounts
def get_lines_to_pay(cls, invoices, name):
pool = Pool()
Move = pool.get('account.move')
Line = pool.get('account.move.line')
Account = pool.get('account.account')
line = Line.__table__()
account = Account.__table__()
move = Move.__table__()
invoice = cls.__table__()
cursor = Transaction().connection.cursor()
_, origin_type = Move.origin.sql_type()
lines = super(Invoice, cls).get_lines_to_pay(invoices, name)
for sub_ids in grouped_slice(invoices):
red_sql = reduce_ids(invoice.id, sub_ids)
query = invoice.join(move,
condition=((move.origin == Concat('account.invoice,',
Cast(invoice.id, origin_type))))
).join(line, condition=(line.move == move.id)
).join(account, condition=(
(line.account == account.id) &
Case((invoice.type == 'out',
account.kind == 'receivable'),
else_=account.kind == 'payable'))).select(
invoice.id, line.id,
where=(line.maturity_date != None) & red_sql,
order_by=(invoice.id, line.maturity_date))
cursor.execute(*query)
for invoice_id, line_id in cursor.fetchall():
if line_id not in lines[invoice_id]:
lines[invoice_id].append(line_id)
return lines
def validate_active(self):
#Deactivate mandate as unit mandate on canceled state
if (self.id > 0) and self.state=='canceled':
condoparties = Pool().get('condo.party').__table__()
condopayments = Pool().get('condo.payment').__table__()
cursor = Transaction().connection.cursor()
cursor.execute(*condopayments.select(condopayments.id,
where=(condopayments.sepa_mandate == self.id) & (
(condopayments.state == 'draft') | (condopayments.state == 'approved')),
))
ids = [ids for (ids,) in cursor.fetchall()]
if len(ids):
self.raise_user_error('Can\'t cancel mandate "%s".\nThere are %s payments in draft or approved state with this mandate!',
(self.identification, len(ids)))
cursor.execute(*condoparties.select(condoparties.id,
where=(condoparties.sepa_mandate == self.id)))
ids = [ids for (ids,) in cursor.fetchall()]
if len(ids):
self.raise_user_warning('warn_canceled_mandate',
'Mandate "%s" will be canceled as mean of payment in %d unit(s)/apartment(s)!', (self.identification, len(ids)))
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(condoparties.id, sub_ids)
# Use SQL to prevent double validate loop
cursor.execute(*condoparties.update(
columns=[condoparties.sepa_mandate],
values=[None],
where=red_sql))
def get_duration(cls, works, name):
pool = Pool()
Line = pool.get('timesheet.line')
transaction = Transaction()
cursor = transaction.connection.cursor()
context = transaction.context
table_w = cls.__table__()
line = Line.__table__()
ids = [w.id for w in works]
durations = dict.fromkeys(ids, None)
where = Literal(True)
if context.get('from_date'):
where &= line.date >= context['from_date']
if context.get('to_date'):
where &= line.date <= context['to_date']
if context.get('employees'):
where &= line.employee.in_(context['employees'])
query_table = table_w.join(line,
'LEFT',
condition=line.work == table_w.id)
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(table_w.id, sub_ids)
cursor.execute(*query_table.select(table_w.id,
Sum(line.duration),
where=red_sql & where,
group_by=table_w.id))
for work_id, duration in cursor.fetchall():
# SQLite uses float for SUM
if duration and not isinstance(duration, datetime.timedelta):
duration = datetime.timedelta(seconds=duration)
durations[work_id] = duration
return durations
def _get_duration_timesheet(works, invoiced):
pool = Pool()
TimesheetLine = pool.get('timesheet.line')
cursor = Transaction().cursor
line = TimesheetLine.__table__()
durations = {}
twork2work = dict((w.work.id, w.id) for w in works if w.work)
ids = twork2work.keys()
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(line.work, sub_ids)
if invoiced:
where = line.invoice_line != Null
else:
where = line.invoice_line == Null
cursor.execute(*line.select(line.work, Sum(line.duration),
where=red_sql & where,
group_by=line.work))
for twork_id, duration in cursor.fetchall():
if duration:
# SQLite uses float for SUM
if not isinstance(duration, datetime.timedelta):
duration = datetime.timedelta(seconds=duration)
durations[twork2work[twork_id]] = duration
return durations
def get_patient_status(cls, patients, name):
cursor = Transaction().connection.cursor()
pool = Pool()
Registration = pool.get('gnuhealth.inpatient.registration')
registration = Registration.__table__()
# Will store statuses {patient: True/False, ...}
ids = list(map(int, patients))
result = dict.fromkeys(ids, False)
for sub_ids in grouped_slice(ids):
# SQL expression for relevant patient ids
clause_ids = reduce_ids(registration.id, sub_ids)
# Hospitalized patient ids
query = registration.select(
registration.patient,
Literal(True),
where=(registration.state == 'hospitalized') & clause_ids,
group_by=registration.patient)
# Update dictionary of patient ids with True statuses
cursor.execute(*query)
result.update(cursor.fetchall())
return result
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 read(cls, ids, fields_names=None):
Rule = Pool().get('ir.rule')
cursor = Transaction().cursor
table = cls.__table__()
if len(set(ids)) != cls.search([('id', 'in', ids)], count=True):
cls.raise_user_error('access_error', cls.__doc__)
writable_ids = []
domain = Rule.query_get(cls.__name__, mode='write')
if domain:
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(table.id, sub_ids)
cursor.execute(*table.select(
table.id, where=red_sql & table.id.in_(domain)))
writable_ids.extend(x[0] for x in cursor.fetchall())
else:
writable_ids = ids
writable_ids = set(writable_ids)
if fields_names is None:
fields_names = []
fields_names = fields_names[:]
to_remove = set()
for field in ('classification', 'calendar', 'transp'):
if field not in fields_names:
fields_names.append(field)
to_remove.add(field)
res = super(Event, cls).read(ids, fields_names=fields_names)
for record in res:
if record['classification'] == 'confidential' \
and record['id'] not in writable_ids:
cls._clean_confidential(record, record['transp'])
for field in to_remove:
del record[field]
return res
def _get_quantity_to_invoice_progress(cls, works):
pool = Pool()
Progress = pool.get('project.work.invoiced_progress')
cursor = Transaction().connection.cursor()
table = cls.__table__()
progress = Progress.__table__()
invoiced_progress = {}
quantities = {}
for sub_works in grouped_slice(works):
sub_works = list(sub_works)
where = reduce_ids(table.id,
[x.id for x in sub_works if x.list_price])
cursor.execute(
*table.join(progress, condition=progress.work ==
table.id).select(table.id,
Sum(progress.progress),
where=where,
group_by=table.id))
invoiced_progress.update(dict(cursor))
for work in sub_works:
delta = ((work.progress or 0) -
invoiced_progress.get(work.id, 0.0))
if work.list_price and delta > 0:
quantity = delta
if work.price_list_hour:
quantity *= work.effort_hours
if work.unit_to_invoice:
quantity = work.unit_to_invoice.round(quantity)
quantities[work.id] = quantity
return quantities
def test0020reduce_ids_one_hole(self):
'''
Test reduce_ids continue list with one hole.
'''
self.assert_(('(((id >= %s) AND (id <= %s)) OR '
'((id >= %s) AND (id <= %s)))', [0, 9, 20, 29])
== reduce_ids('id', range(10) + map(lambda x: x + 20, range(10))))
def _get_quantity_to_invoice_timesheet(cls, works):
pool = Pool()
TimesheetLine = pool.get('timesheet.line')
cursor = Transaction().connection.cursor()
line = TimesheetLine.__table__()
durations = defaultdict(datetime.timedelta)
twork2work = {tw.id: w.id for w in works for tw in w.timesheet_works}
for sub_ids in grouped_slice(twork2work.keys()):
red_sql = reduce_ids(line.work, sub_ids)
cursor.execute(*line.select(line.work,
Sum(line.duration),
where=red_sql
& (line.invoice_line == Null),
group_by=line.work))
for twork_id, duration in cursor:
if duration:
# SQLite uses float for SUM
if not isinstance(duration, datetime.timedelta):
duration = datetime.timedelta(seconds=duration)
durations[twork2work[twork_id]] += duration
quantities = {}
for work in works:
duration = durations[work.id]
if work.list_price:
hours = duration.total_seconds() / 60 / 60
if work.unit_to_invoice:
hours = work.unit_to_invoice.round(hours)
quantities[work.id] = hours
return quantities
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_name):
pool = Pool()
StatementLine = pool.get('account.statement.line')
cursor = Transaction().cursor
sql_table = cls.__table__()
super(Move, cls).__register__(module_name)
# Migration from 3.4:
# account.statement.line origin changed to account.statement
statement_line = StatementLine.__table__()
cursor.execute(*sql_table.join(statement_line,
condition=(
Concat(StatementLine.__name__ + ',', statement_line.id)
== sql_table.origin
)
).select(sql_table.id, statement_line.statement,
order_by=(sql_table.id, statement_line.statement)))
for statement_id, values in groupby(cursor.fetchall(), itemgetter(1)):
ids = [x[0] for x in values]
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(sql_table.id, sub_ids)
cursor.execute(*sql_table.update(
columns=[sql_table.origin],
values=['account.statement,%s' % statement_id],
where=red_sql))
def generate(cls, pains):
pool = Pool()
Payment = pool.get('condo.payment')
payment = Payment.__table__()
cursor = Transaction().connection.cursor()
for pain in pains:
pids = [p.id for group in pain.groups for p in group.payments]
for sub_ids in grouped_slice(pids):
red_sql = reduce_ids(payment.id, sub_ids)
cursor.execute(*payment.update(columns=[payment.state], values=['approved'], where=red_sql))
try:
tmpl = pain.get_sepa_template()
message = (
tmpl.generate(pain=pain, datetime=datetime, normalize=sepadecode.sepa_conversion)
.filter(remove_comment)
.render()
)
pain.message = message
pain.save()
except:
Transaction().rollback()
cls.raise_user_error('generate_error', (pain.reference, pain.company.party.name))
else:
Transaction().commit()
def get_creationdate(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 'creationdate' in cache[Model.__name__][object_id]:
return cache[Model.__name__][object_id][
'creationdate']
else:
ids = [object_id]
res = None
cursor = Transaction().cursor
for i in range(0, len(ids), cursor.IN_MAX):
sub_ids = ids[i:i + cursor.IN_MAX]
red_sql, red_ids = reduce_ids('id', sub_ids)
cursor.execute('SELECT id, '
'EXTRACT(epoch FROM create_date) '
'FROM "' + Model._table + '" '
'WHERE ' + red_sql, red_ids)
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][
'creationdate'] = date
if res is not None:
return res
return time.time()
def get_history_datetime(cls, invoices, name):
pool = Pool()
Party = pool.get('party.party')
Address = pool.get('party.address')
PaymentTerm = pool.get('account.invoice.payment_term')
table = cls.__table__()
party = Party.__table__()
address = Address.__table__()
payment_term = PaymentTerm.__table__()
cursor = Transaction().connection.cursor()
invoice_ids = [i.id for i in invoices]
datetimes = dict.fromkeys(invoice_ids)
for ids in grouped_slice(invoice_ids):
cursor.execute(
*table.join(party, condition=table.party == party.id).join(
address, condition=table.invoice_address == address.id
).join(payment_term,
'LEFT',
condition=table.payment_term == payment_term.id).
select(table.id,
Greatest(table.numbered_at, party.create_date,
address.create_date, payment_term.create_date),
where=reduce_ids(table.id, ids)
& (table.numbered_at != Null)))
datetimes.update(cursor)
return datetimes
def get_pending_amount(cls, agents, name):
pool = Pool()
Commission = pool.get('commission')
commission = Commission.__table__()
cursor = Transaction().connection.cursor()
ids = [a.id for a in agents]
amounts = dict.fromkeys(ids, None)
for sub_ids in grouped_slice(ids):
where = reduce_ids(commission.agent, sub_ids)
where &= commission.invoice_line == Null
query = commission.select(commission.agent, Sum(commission.amount),
where=where,
group_by=commission.agent)
cursor.execute(*query)
amounts.update(dict(cursor.fetchall()))
digits = cls.pending_amount.digits
exp = Decimal(str(10.0 ** -digits[1]))
for agent_id, amount in amounts.items():
if amount:
# SQLite uses float for SUM
if not isinstance(amount, Decimal):
amount = Decimal(str(amount))
amounts[agent_id] = amount.quantize(exp)
return amounts
def get_creationdate(cls, uri, cache=None):
Party = Pool().get('party.party')
party = Party.__table__()
party_id = cls.vcard(uri)
cursor = Transaction().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 convert_domain_tree(self, domain, tables):
Target = self.get_target()
target = Target.__table__()
table, _ = tables[None]
name, operator, ids = domain
red_sql = reduce_ids(target.id, (i for i in ids if i is not None))
if operator.endswith('child_of'):
tree = With('id', recursive=True)
tree.query = target.select(target.id, where=red_sql)
tree.query |= (target.join(
tree, condition=Column(target,
name) == tree.id).select(target.id))
else:
tree = With('id', name, recursive=True)
tree.query = target.select(target.id,
Column(target, name),
where=red_sql)
tree.query |= (target.join(tree,
condition=target.id == Column(
tree, name)).select(
target.id, Column(target,
name)))
expression = table.id.in_(tree.select(tree.id, with_=[tree]))
if operator.startswith('not'):
return ~expression
return expression
def generate(cls, pains):
pool = Pool()
Payment = pool.get('condo.payment')
payment = Payment.__table__()
cursor = Transaction().connection.cursor()
for pain in pains:
pids = [p.id for group in pain.groups for p in group.payments]
for sub_ids in grouped_slice(pids):
red_sql = reduce_ids(payment.id, sub_ids)
cursor.execute(*payment.update(
columns=[payment.state],
values=['approved'],
where=red_sql))
try:
tmpl = pain.get_sepa_template()
message = tmpl.generate(pain=pain,
datetime=datetime, normalize=unicodedata.normalize,
).filter(remove_comment).render()
pain.message = message
pain.save()
except:
Transaction().rollback()
cls.raise_user_error('generate_error', (pain.reference,pain.company.party.name))
else:
Transaction().commit()
def write(cls, recurrences, values, *args):
events = Pool().get('recurrence.event').__table__()
cursor = Transaction().connection.cursor()
kwrd = {}
actions = iter((recurrences, values) + args)
for records, values in zip(actions, actions):
for r in records:
kwrd[r.id] = r.next_call
super(Recurrence, cls).write(recurrences, values, *args)
actions = iter((recurrences, values) + args)
for records, values in zip(actions, actions):
for r in records:
if kwrd[r.id] != r.next_call:
ids = [e.id for e in r.events]
if len(ids):
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(events.id, sub_ids)
cursor.execute(
*events.update(columns=[events.rnext_call],
values=[r.next_call],
where=red_sql))
def _get_duration_timesheet(works, invoiced):
pool = Pool()
TimesheetLine = pool.get('timesheet.line')
cursor = Transaction().connection.cursor()
line = TimesheetLine.__table__()
durations = defaultdict(datetime.timedelta)
twork2work = {tw.id: w.id for w in works for tw in w.timesheet_works}
ids = twork2work.keys()
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(line.work, sub_ids)
if invoiced:
where = line.invoice_line != Null
else:
where = line.invoice_line == Null
cursor.execute(*line.select(line.work, Sum(line.duration),
where=red_sql & where,
group_by=line.work))
for twork_id, duration in cursor.fetchall():
if duration:
# SQLite uses float for SUM
if not isinstance(duration, datetime.timedelta):
duration = datetime.timedelta(seconds=duration)
durations[twork2work[twork_id]] += duration
return durations
def search(cls,
args,
offset=0,
limit=None,
order=None,
count=False,
query=False):
lines = super().search(args, offset, limit, order, count, query)
Move = Pool().get('account.move')
cursor = Transaction().connection.cursor()
table = cls.__table__()
move = Move.__table__()
if lines and isinstance(lines, list):
red_sql = reduce_ids(table.id, [x.id for x in lines])
# This sorting criteria must be the one used by the 'balance'
# functional field above, so remember to modify that if you
# want to change the order.
cursor.execute(*table.join(move, condition=table.move ==
move.id).select(table.id,
where=red_sql,
order_by=(move.date,
move.number,
table.id)))
result = cursor.fetchall()
ids = [x[0] for x in result]
lines = cls.browse(ids)
return lines
def test_reduce_ids_complex(self):
'Test reduce_ids complex list'
self.assertEqual(
reduce_ids(self.table.id,
range(10) + range(25, 30) + range(15, 20)),
(((self.table.id >= 0) & (self.table.id <= 14))
| (self.table.id.in_(range(25, 30)))))
def __register__(cls, module_name):
pool = Pool()
StatementLine = pool.get('account.statement.line')
cursor = Transaction().connection.cursor()
sql_table = cls.__table__()
super(Move, cls).__register__(module_name)
# Migration from 3.4:
# account.statement.line origin changed to account.statement
statement_line = StatementLine.__table__()
cursor.execute(*sql_table.join(statement_line,
condition=(
Concat(StatementLine.__name__ + ',', statement_line.id)
== sql_table.origin
)
).select(sql_table.id, statement_line.statement,
order_by=(sql_table.id, statement_line.statement)))
for statement_id, values in groupby(cursor.fetchall(), itemgetter(1)):
ids = [x[0] for x in values]
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(sql_table.id, sub_ids)
cursor.execute(*sql_table.update(
columns=[sql_table.origin],
values=['account.statement,%s' % statement_id],
where=red_sql))
def test_reduce_ids_one_hole(self):
'Test reduce_ids continue list with one hole'
self.assertEqual(
reduce_ids(self.table.id,
list(range(10)) + list(range(20, 30))),
((self.table.id >= 0) & (self.table.id <= 9))
| ((self.table.id >= 20) & (self.table.id <= 29)))
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_measurements(cls, shipments, names):
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
cursor = Transaction().connection.cursor()
table = cls.__table__()
move = Move.__table__()
location = Location.__table__()
measurements = defaultdict(lambda: defaultdict(lambda: None))
query = table.join(
move,
type_='LEFT',
condition=cls._measurements_move_condition(table, move)).join(
location,
condition=cls._measurements_location_condition(
table, move, location)).select(table.id,
Sum(move.internal_weight),
Sum(move.internal_volume),
group_by=[table.id])
for sub_shipments in grouped_slice(shipments):
query.where = reduce_ids(table.id, [s.id for s in sub_shipments])
cursor.execute(*query)
for id_, weight, volume in cursor:
if 'weight' in names:
measurements['weight'][id_] = weight
if 'volume' in names:
measurements['volume'][id_] = volume
return measurements
def test0040reduce_ids_complex(self):
'''
Test reduce_ids complex list.
'''
self.assertEqual(('(((id >= %s) AND (id <= %s)) OR '
'(id IN (%s,%s,%s,%s,%s)))', [0, 14, 25, 26, 27, 28, 29]),
reduce_ids('id', range(10) + map(lambda x: x + 25, range(5))
+ map(lambda x: x + 5, range(10))))
def get_lastmodified(cls, uri, cache=None):
Todo = Pool().get('calendar.todo')
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 i in range(0, len(ids), cursor.IN_MAX / 2):
sub_ids = ids[i:i + cursor.IN_MAX / 2]
red_id_sql, red_id_ids = reduce_ids('id', sub_ids)
red_parent_sql, red_parent_ids = reduce_ids('parent',
sub_ids)
cursor.execute('SELECT COALESCE(parent, id), '
'MAX(EXTRACT(epoch FROM '
'COALESCE(write_date, create_date))) '
'FROM "' + Todo._table + '" '
'WHERE ' + red_id_sql + ' '
'OR ' + red_parent_sql + ' '
'GROUP BY parent, id', red_id_ids + red_parent_ids)
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 test_reduce_ids_float(self):
'Test reduce_ids with integer as float'
self.assertEqual(reduce_ids(self.table.id,
[1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0,
15.0, 18.0, 19.0, 21.0]),
(((self.table.id >= 1.0) & (self.table.id <= 12.0))
| (self.table.id.in_([15.0, 18.0, 19.0, 21.0]))))
self.assertRaises(AssertionError, reduce_ids, self.table.id, [1.1])
def test_reduce_ids_float(self):
'Test reduce_ids with integer as float'
self.assertEqual(reduce_ids(self.table.id,
[1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0,
15.0, 18.0, 19.0, 21.0]),
(((self.table.id >= 1.0) & (self.table.id <= 12.0))
| (self.table.id.in_([15.0, 18.0, 19.0, 21.0]))))
self.assertRaises(AssertionError, reduce_ids, self.table.id, [1.1])
def test0050reduce_ids_complex_small_continue(self):
'''
Test reduce_ids complex list with small continue.
'''
self.assertEqual(('(((id >= %s) AND (id <= %s)) '
'OR (id IN (%s,%s,%s,%s)))', [1, 12, 15, 18, 19, 21]),
reduce_ids('id', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 15, 18,
19, 21]))
def test_reduce_ids_complex_small_continue(self):
'Test reduce_ids complex list with small continue'
self.assertEqual(
reduce_ids(
self.table.id,
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 15, 18, 19, 21]),
(((self.table.id >= 1) & (self.table.id <= 12))
| (self.table.id.in_([15, 18, 19, 21]))))
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 get_receivable_payable(cls, parties, names):
'''
Function to compute receivable, payable (today or not) for party ids.
'''
result = {}
pool = Pool()
MoveLine = pool.get('account.move.line')
Account = pool.get('account.account')
User = pool.get('res.user')
Date = pool.get('ir.date')
cursor = Transaction().cursor
line = MoveLine.__table__()
account = Account.__table__()
for name in names:
if name not in ('receivable', 'payable',
'receivable_today', 'payable_today'):
raise Exception('Bad argument')
result[name] = dict((p.id, Decimal('0.0')) for p in parties)
user = User(Transaction().user)
if not user.company:
return result
company_id = user.company.id
line_query, _ = MoveLine.query_get(line)
amount = Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0))
for name in names:
code = name
today_where = Literal(True)
if name in ('receivable_today', 'payable_today'):
code = name[:-6]
today_where = ((line.maturity_date <= Date.today())
| (line.maturity_date == Null))
for sub_parties in grouped_slice(parties):
sub_ids = [p.id for p in sub_parties]
party_where = reduce_ids(line.party, sub_ids)
cursor.execute(*line.join(account,
condition=account.id == line.account
).select(line.party, amount,
where=(account.active
& (account.kind == code)
& (line.reconciliation == Null)
& (account.company == company_id)
& line_query
& party_where
& today_where
& (account.kind == code)),
group_by=line.party))
for party, value in cursor.fetchall():
# SQLite uses float for SUM
if not isinstance(value, Decimal):
value = Decimal(str(value))
result[name][party] = value
return result
def test0055reduce_ids_float(self):
'''
Test reduce_ids with integer as float.
'''
self.assert_(('(((id >= %s) AND (id <= %s)) OR (id IN (%s,%s,%s,%s)))',
[1.0, 12.0, 15.0, 18.0, 19.0, 21.0])
== reduce_ids('id', [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0,
10.0, 11.0, 12.0, 15.0, 18.0, 19.0, 21.0]))
self.assertRaises(AssertionError, reduce_ids, 'id', [1.1])
def get_debit_credit_balance(cls, journals, names):
pool = Pool()
MoveLine = pool.get('account.move.line')
Move = pool.get('account.move')
Account = pool.get('account.account')
AccountType = pool.get('account.account.type')
Company = pool.get('company.company')
context = Transaction().context
cursor = Transaction().connection.cursor()
result = {}
ids = [j.id for j in journals]
for name in ['debit', 'credit', 'balance']:
result[name] = dict.fromkeys(ids, 0)
company_id = Transaction().context.get('company')
if not company_id:
return result
company = Company(company_id)
line = MoveLine.__table__()
move = Move.__table__()
account = Account.__table__()
account_type = AccountType.__table__()
where = ((move.date >= context.get('start_date'))
& (move.date <= context.get('end_date'))
& ~account_type.receivable
& ~account_type.payable
& (move.company == company.id))
for sub_journals in grouped_slice(journals):
sub_journals = list(sub_journals)
red_sql = reduce_ids(move.journal, [j.id for j in sub_journals])
query = line.join(
move, 'LEFT', condition=line.move == move.id).join(
account, 'LEFT',
condition=line.account == account.id).join(
account_type,
'LEFT',
condition=account.type == account_type.id).select(
move.journal,
Sum(line.debit),
Sum(line.credit),
where=where & red_sql,
group_by=move.journal)
cursor.execute(*query)
for journal_id, debit, credit in cursor.fetchall():
# SQLite uses float for SUM
if not isinstance(debit, Decimal):
debit = Decimal(str(debit))
if not isinstance(credit, Decimal):
credit = Decimal(str(credit))
result['debit'][journal_id] = company.currency.round(debit)
result['credit'][journal_id] = company.currency.round(credit)
result['balance'][journal_id] = company.currency.round(debit -
credit)
return result
def _purchase_cost(cls, works):
'Compute direct purchase cost'
pool = Pool()
Currency = pool.get('currency.currency')
PurchaseLine = pool.get('purchase.line')
InvoiceLine = pool.get('account.invoice.line')
Invoice = pool.get('account.invoice')
Company = pool.get('company.company')
cursor = Transaction().connection.cursor()
table = cls.__table__()
purchase_line = PurchaseLine.__table__()
invoice_line = InvoiceLine.__table__()
invoice = Invoice.__table__()
company = Company.__table__()
amounts = defaultdict(Decimal)
work_ids = [w.id for w in works]
work2currency = {}
iline2work = {}
for sub_ids in grouped_slice(work_ids):
where = reduce_ids(table.id, sub_ids)
cursor.execute(*table.join(purchase_line,
condition=purchase_line.work == table.id
).join(invoice_line,
condition=invoice_line.origin == Concat(
'purchase.line,', purchase_line.id)
).join(invoice,
condition=invoice_line.invoice == invoice.id
).select(invoice_line.id, table.id,
where=where & ~invoice.state.in_(['draft', 'cancel'])))
iline2work.update(cursor.fetchall())
cursor.execute(*table.join(company,
condition=table.company == company.id
).select(table.id, company.currency,
where=where))
work2currency.update(cursor.fetchall())
currencies = Currency.browse(set(work2currency.values()))
id2currency = {c.id: c for c in currencies}
invoice_lines = InvoiceLine.browse(list(iline2work.keys()))
for invoice_line in invoice_lines:
invoice = invoice_line.invoice
work_id = iline2work[invoice_line.id]
currency_id = work2currency[work_id]
currency = id2currency[currency_id]
if currency != invoice.currency:
with Transaction().set_context(date=invoice.currency_date):
amount = Currency.compute(invoice.currency,
invoice_line.amount, currency)
else:
amount = invoice_line.amount
amounts[work_id] += amount
return amounts
def _purchase_cost(cls, works):
'Compute direct purchase cost'
pool = Pool()
Currency = pool.get('currency.currency')
PurchaseLine = pool.get('purchase.line')
InvoiceLine = pool.get('account.invoice.line')
Invoice = pool.get('account.invoice')
Company = pool.get('company.company')
cursor = Transaction().cursor
table = cls.__table__()
purchase_line = PurchaseLine.__table__()
invoice_line = InvoiceLine.__table__()
invoice = Invoice.__table__()
company = Company.__table__()
amounts = defaultdict(Decimal)
work_ids = [w.id for w in works]
work2currency = {}
iline2work = {}
for sub_ids in grouped_slice(work_ids):
where = reduce_ids(table.id, sub_ids)
cursor.execute(*table.join(purchase_line,
condition=purchase_line.work == table.id
).join(invoice_line,
condition=invoice_line.origin == Concat(
'purchase.line,', purchase_line.id)
).join(invoice,
condition=invoice_line.invoice == invoice.id
).select(invoice_line.id, table.id,
where=where & ~invoice.state.in_(['draft', 'cancel'])))
iline2work.update(cursor.fetchall())
cursor.execute(*table.join(company,
condition=table.company == company.id
).select(table.id, company.currency,
where=where))
work2currency.update(cursor.fetchall())
currencies = Currency.browse(set(work2currency.itervalues()))
id2currency = {c.id: c for c in currencies}
invoice_lines = InvoiceLine.browse(iline2work.keys())
for invoice_line in invoice_lines:
invoice = invoice_line.invoice
work_id = iline2work[invoice_line.id]
currency_id = work2currency[work_id]
currency = id2currency[currency_id]
if currency != invoice.currency:
with Transaction().set_context(date=invoice.currency_date):
amount = Currency.compute(invoice.currency,
invoice_line.amount, currency)
else:
amount = invoice_line.amount
amounts[work_id] += amount
return amounts
def cancel(cls, pains):
pool = Pool()
Payment = pool.get('condo.payment')
payment = Payment.__table__()
cursor = Transaction().connection.cursor()
pids = [p.id for payments in [group.payments for pain in pains for group in pain.groups] for p in payments]
for sub_ids in grouped_slice(pids):
red_sql = reduce_ids(payment.id, sub_ids)
cursor.execute(*payment.update(columns=[payment.state], values=['failed'], where=red_sql))
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 get_quantity_executed(cls, lines, name):
cursor = Transaction().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")
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 i in range(0, len(product_ids), cursor.IN_MAX):
sub_ids = product_ids[i : i + cursor.IN_MAX]
red_sql, red_ids = reduce_ids("product", sub_ids)
cursor.execute(
"SELECT m.product, "
"SUM(m.internal_quantity) AS quantity "
'FROM "' + Move._table + '" AS m '
'JOIN "' + Location._table + '" AS fl '
"ON m.from_location = fl.id "
'JOIN "' + Location._table + '" AS tl '
"ON m.to_location = tl.id "
'LEFT JOIN "' + LineMove._table + '" AS lm '
"ON m.id = lm.move "
"WHERE " + red_sql + " "
"AND fl.left >= %s AND fl.right <= %s "
"AND tl.left >= %s AND tl.right <= %s "
"AND m.state != %s "
"AND COALESCE(m.effective_date, m.planned_date) >= %s "
"AND COALESCE(m.effective_date, m.planned_date) <= %s "
"AND lm.id IS NULL "
"GROUP BY m.product",
red_ids
+ [
forecast.warehouse.left,
forecast.warehouse.right,
forecast.destination.left,
forecast.destination.right,
"cancel",
forecast.from_date,
forecast.to_date,
],
)
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 cancel(cls, pains):
pool = Pool()
Payment = pool.get('condo.payment')
payment = Payment.__table__()
cursor = Transaction().connection.cursor()
pids = [p.id for payments in [group.payments for pain in pains for group in pain.groups] for p in payments]
for sub_ids in grouped_slice(pids):
red_sql = reduce_ids(payment.id, sub_ids)
cursor.execute(*payment.update(
columns=[payment.state],
values=['failed'],
where=red_sql))
def get_debit_credit_balance(cls, journals, names):
pool = Pool()
MoveLine = pool.get('account.move.line')
Move = pool.get('account.move')
context = Transaction().context
cursor = Transaction().cursor
result = {}
ids = [j.id for j in journals]
for name in ['debit', 'credit', 'balance']:
result[name] = dict.fromkeys(ids, 0)
line = MoveLine.__table__()
move = Move.__table__()
where = ((move.date >= context['start_date'])
& (move.date <= context['end_date']))
for sub_journals in grouped_slice(journals):
sub_journals = list(sub_journals)
red_sql = reduce_ids(move.journal, [j.id for j in sub_journals])
accounts = None
for journal in sub_journals:
credit_account = (journal.credit_account.id
if journal.credit_account else None)
debit_account = (journal.debit_account.id
if journal.debit_account else None)
clause = ((move.journal == journal.id)
& (((line.credit != Null)
& (line.account == credit_account))
| ((line.debit != Null)
& (line.account == debit_account))))
if accounts is None:
accounts = clause
else:
accounts |= clause
query = line.join(move, 'LEFT', condition=line.move == move.id
).select(move.journal, Sum(line.debit), Sum(line.credit),
where=where & red_sql & accounts,
group_by=move.journal)
cursor.execute(*query)
for journal_id, debit, credit in cursor.fetchall():
# SQLite uses float for SUM
if not isinstance(debit, Decimal):
debit = Decimal(str(debit))
if not isinstance(credit, Decimal):
credit = Decimal(str(credit))
result['debit'][journal_id] = debit
result['credit'][journal_id] = credit
result['balance'][journal_id] = debit - credit
return result
def _get_invoiced_amount_progress(cls, works):
pool = Pool()
Progress = pool.get('project.work.invoiced_progress')
InvoiceLine = pool.get('account.invoice.line')
Company = pool.get('company.company')
Currency = pool.get('currency.currency')
cursor = Transaction().cursor
table = cls.__table__()
progress = Progress.__table__()
invoice_line = InvoiceLine.__table__()
company = Company.__table__()
amounts = defaultdict(Decimal)
work2currency = {}
work_ids = [w.id for w in works]
for sub_ids in grouped_slice(work_ids):
where = reduce_ids(table.id, sub_ids)
cursor.execute(*table.join(progress,
condition=progress.work == table.id
).join(invoice_line,
condition=progress.invoice_line == invoice_line.id
).select(table.id,
Sum(progress.effort_duration * invoice_line.unit_price),
where=where,
group_by=table.id))
for work_id, amount in cursor.fetchall():
if isinstance(amount, datetime.timedelta):
amount = amount.total_seconds()
# Amount computed in second instead of hours
if amount is not None:
amount /= 60 * 60
else:
amount = 0
amounts[work_id] = amount
cursor.execute(*table.join(company,
condition=table.company == company.id
).select(table.id, company.currency,
where=where))
work2currency.update(cursor.fetchall())
currencies = Currency.browse(set(work2currency.itervalues()))
id2currency = {c.id: c for c in currencies}
for work in works:
currency = id2currency[work2currency[work.id]]
amounts[work.id] = currency.round(Decimal(amounts[work.id]))
return amounts
def _get_invoiced_amount_timesheet(cls, works):
pool = Pool()
TimesheetWork = pool.get('timesheet.work')
TimesheetLine = pool.get('timesheet.line')
InvoiceLine = pool.get('account.invoice.line')
Company = pool.get('company.company')
Currency = pool.get('currency.currency')
cursor = Transaction().cursor
table = cls.__table__()
timesheet_work = TimesheetWork.__table__()
timesheet_line = TimesheetLine.__table__()
invoice_line = InvoiceLine.__table__()
company = Company.__table__()
amounts = {}
work2currency = {}
work_ids = [w.id for w in works]
for sub_ids in grouped_slice(work_ids):
where = reduce_ids(table.id, sub_ids)
cursor.execute(*table.join(timesheet_work,
condition=table.work == timesheet_work.id
).join(timesheet_line,
condition=timesheet_line.work == timesheet_work.id
).join(invoice_line,
condition=timesheet_line.invoice_line == invoice_line.id
).select(table.id,
Sum(timesheet_line.duration * invoice_line.unit_price),
where=where,
group_by=table.id))
amounts.update(cursor.fetchall())
cursor.execute(*table.join(company,
condition=table.company == company.id
).select(table.id, company.currency,
where=where))
work2currency.update(cursor.fetchall())
currencies = Currency.browse(set(work2currency.itervalues()))
id2currency = {c.id: c for c in currencies}
for work in works:
currency = id2currency[work2currency[work.id]]
amount = amounts.get(work.id, 0)
if isinstance(amount, datetime.timedelta):
amount = amount.total_seconds()
amount = amount / 60 / 60
amounts[work.id] = currency.round(Decimal(str(amount)))
return amounts
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 validate_active(self):
#Cancel mandates with account number of this bank account.
if (self.id > 0) and not self.active:
mandates = Pool().get('condo.payment.sepa.mandate').__table__()
condoparties = Pool().get('condo.party').__table__()
cursor = Transaction().connection.cursor()
red_sql = reduce_ids(mandates.account_number, [x.id for x in self.numbers])
cursor.execute(*mandates.select(mandates.id,
mandates.identification,
where=red_sql &
(mandates.state != 'canceled')))
for id, identification in cursor.fetchall():
cursor.execute(*condoparties.select(
condoparties.id,
where=(condoparties.sepa_mandate == id) &
(condoparties.active == True)))
ids = [ids for (ids,) in cursor.fetchall()]
if len(ids):
self.raise_user_warning('warn_deactive_mandate.%d' % id,
'Mandate "%s" will be canceled and deactivate as mean of payment in %d unit(s)/apartment(s)!', (identification, len(ids)))
# Use SQL to prevent double validate loop
cursor.execute(*mandates.update(
columns=[mandates.state],
values=['canceled'],
where=(mandates.id == id)))
for sub_ids in grouped_slice(ids):
red_sql = reduce_ids(condoparties.id, sub_ids)
cursor.execute(*condoparties.update(
columns=[condoparties.sepa_mandate],
values=[None],
where=red_sql))
def has_payments(self, mandates, name):
pool = Pool()
Payment = pool.get('account.payment')
payment = Payment.__table__
cursor = Transaction().cursor
has_payments = dict.fromkeys([m.id for m in mandates], False)
for sub_ids in grouped_slice(mandates):
red_sql = reduce_ids(payment.sepa_mandate, sub_ids)
cursor.execute(*payment.select(payment.sepa_mandate, Literal(True),
where=red_sql,
group_by=payment.sepa_mandate))
has_payments.update(cursor.fetchall())
return {'has_payments': has_payments}
def validate(cls, paymentgroups):
super(CondoPaymentGroup, cls).validate(paymentgroups)
table = cls.__table__()
payments = Pool().get('condo.payment').__table__()
for paymentgroup in paymentgroups:
if paymentgroup.readonly:
with Transaction().new_transaction(readonly=True) as transaction,\
transaction.connection.cursor() as cursor:
cursor.execute(*table.select(table.date,
where=(table.id == paymentgroup.id) &
(table.date != paymentgroup.date)))
if cursor.fetchone():
cls.raise_user_error('readonly_paymentgroup', (paymentgroup.reference)
)
return
cursor = Transaction().connection.cursor()
cursor.execute(*payments.select(payments.id,
where=(payments.group == paymentgroup.id) &
(payments.date < paymentgroup.date) &
(payments.state != 'draft')))
if cursor.fetchall():
cls.raise_user_error('payments_approved', (paymentgroup.reference)
)
paymentgroup.check_today()
paymentgroup.check_businessdate()
paymentgroup.company_has_sepa_creditor_identifier()
#if has drafted payments with due date before new date
#update date field of payments
cursor.execute(*payments.select(payments.id,
where=(payments.group == paymentgroup.id) &
(payments.date < paymentgroup.date) &
(payments.state == 'draft')))
ids_draft = [ids for (ids,) in cursor.fetchall()]
if len(ids_draft):
for sub_ids in grouped_slice(ids_draft):
red_sql = reduce_ids(payments.id, sub_ids)
# Use SQL to prevent double validate loop
cursor.execute(*payments.update(
columns=[payments.date],
values=[paymentgroup.date],
where=red_sql))
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')
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 i in range(0, len(ids), cursor.IN_MAX):
sub_ids = ids[i:i + cursor.IN_MAX]
red_sql, red_ids = reduce_ids('p.id', sub_ids)
cursor.execute('SELECT p.id, '
'MAX(EXTRACT(epoch FROM '
'COALESCE(p.write_date, p.create_date))), '
'MAX(EXTRACT(epoch FROM '
'COALESCE(a.write_date, a.create_date))), '
'MAX(EXTRACT(epoch FROM '
'COALESCE(c.write_date, c.create_date))) '
'FROM "' + Party._table + '" p '
'LEFT JOIN "' + Address._table + '" a '
'ON p.id = a.party '
'LEFT JOIN "' + ContactMechanism._table + '" c '
'ON p.id = c.party '
'WHERE ' + red_sql + ' '
'GROUP BY p.id', red_ids)
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 get_quantity_executed(cls, lines, name):
cursor = Transaction().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
