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 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