def search_measurements(cls, name, clause): pool = Pool() Move = pool.get('stock.move') Location = pool.get('stock.location') table = cls.__table__() move = Move.__table__() location = Location.__table__() _, operator, value = clause Operator = fields.SQL_OPERATORS[operator] if name == 'weight': measurement = Sum(move.internal_weight) else: measurement = Sum(move.internal_volume) 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, group_by=[table.id], having=Operator(measurement, value)) return [('id', 'in', query)]
def table_query(): pool = Pool() context = Transaction().context Gp = pool.get('disc.gp') gp = Gp.__table__() Reporte = pool.get('disc.reporte') reporte = Reporte.__table__() ReporteLinea = pool.get('disc.reporte.linea') reporte_linea = ReporteLinea.__table__() where = Literal(True) if context.get('fecha_inicio'): where &= reporte.fecha_inicio >= context['fecha_inicio'] # print "FECHA INICIO: " + str(context['fecha_inicio']) if context.get('fecha_fin'): where &= reporte.fecha_fin <= context['fecha_fin'] # print "FECHA FIN: " + str(context['fecha_fin']) #if context.get('distrito'): # where &= reporte.distrito == context['distrito'] # print "DISTRITO: " + str(context['distrito']) #print "WHERE: " + str(where) subquery = (reporte_linea .join(reporte, condition=reporte_linea.reporte == reporte.id) .select( Max(reporte_linea.id * 1005).as_('id'), Max(reporte_linea.create_uid).as_('create_uid'), Max(reporte_linea.create_date).as_('create_date'), Max(reporte_linea.write_uid).as_('write_uid'), Max(reporte_linea.write_date).as_('write_date'), (Sum(reporte_linea.cantidad)).as_('total'), (reporte_linea.gp).as_('gp'), where = where, group_by=(reporte_linea.gp), having= Sum(reporte_linea.cantidad)>0, order_by=(reporte_linea.gp), ) ) query = (gp .join(subquery,'LEFT', condition= gp.id == subquery.gp) .select( Max(gp.id * 1002).as_('id'), Max(gp.create_uid).as_('create_uid'), Max(gp.create_date).as_('create_date'), Max(gp.write_uid).as_('write_uid'), Max(gp.write_date).as_('write_date'), (Sum(subquery.total)).as_('total'), (gp.id).as_('gp'), where= subquery.gp == None, #or subquery.total is None, #having= Sum(subquery.total)<1, group_by=(gp.id), ) ) #print "QUERY: " + str(query) return query
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 get_account_values_by_party(cls, parties, accounts, company): ''' Function to compute credit,debit and balance for party ids. ''' res = {} pool = Pool() Move = pool.get('account.move') MoveLine = pool.get('account.move.line') Account = pool.get('account.account') transaction = Transaction() context = transaction.context cursor = transaction.connection.cursor() move = Move.__table__() line = MoveLine.__table__() account = Account.__table__() group_by = ( line.party, line.account, ) columns = (group_by + (Sum(Coalesce(line.debit, 0)).as_('debit'), Sum(Coalesce(line.credit, 0)).as_('credit'), (Sum(Coalesce(line.debit, 0)) - Sum(Coalesce(line.credit, 0))).as_('balance'))) line_query, _ = MoveLine.query_get(line) if context.get('date'): # Cumulate data from previous fiscalyears line_query = line.move.in_( move.select(move.id, where=move.date <= context.get('date'))) where = (line_query & (account.company == company.id)) if accounts: where = where & line.account.in_([a.id for a in accounts]) if parties: where = where & line.party.in_([p.id for p in parties]) cursor.execute( *line.join(account, condition=(line.account == account.id)).select( *columns, where=where, group_by=group_by)) for party, account, debit, credit, balance in cursor.fetchall(): # SQLite uses float for SUM if not isinstance(credit, Decimal): credit = Decimal(str(credit)) if not isinstance(debit, Decimal): debit = Decimal(str(debit)) if not isinstance(balance, Decimal): balance = Decimal(str(balance)) if account not in res: res[account] = {} res[account][party] = { 'credit': credit, 'debit': debit, 'balance': balance, } return res
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 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().connection.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 table_query(cls): pool = Pool() Invoice = pool.get('account.invoice') InvoiceTax = pool.get('account.invoice.tax') Tax = pool.get('account.tax') Date = pool.get('ir.date') context = Transaction().context invoice = Invoice.__table__() invoice_tax = InvoiceTax.__table__() tax = Tax.__table__() amount = invoice_tax.base + invoice_tax.amount month = Extract('MONTH', invoice.invoice_date) where = ((invoice.company == context.get('company')) & (invoice.state.in_(['posted', 'paid'])) & (tax.es_vat_list_code != Null) & (Extract('year', invoice.invoice_date) == context.get( 'date', Date.today()).year)) return (invoice_tax.join( invoice, condition=invoice_tax.invoice == invoice.id ).join(tax, condition=invoice_tax.tax == tax.id).select( Max(invoice_tax.id).as_('id'), Literal(0).as_('create_uid'), Min(invoice_tax.create_date).as_('create_date'), Literal(0).as_('write_uid'), Max(invoice_tax.write_date).as_('write_date'), invoice.tax_identifier.as_('company_tax_identifier'), invoice.party.as_('party'), invoice.party_tax_identifier.as_('party_tax_identifier'), tax.es_vat_list_code.as_('code'), Sum(amount).as_('amount'), Sum(amount, filter_=month <= Literal(3)).as_('first_period_amount'), Sum(amount, filter_=((month > Literal(3)) & (month <= Literal(6)))).as_('second_period_amount'), Sum(amount, filter_=((month > Literal(6)) & (month <= Literal(9)))).as_('third_period_amount'), Sum(amount, filter_=((month > Literal(9)) & (month <= Literal(12)))).as_('fourth_period_amount'), invoice.currency.as_('currency'), where=where, group_by=[ invoice.tax_identifier, invoice.type, invoice.party, invoice.party_tax_identifier, invoice.currency, tax.es_vat_list_code, ]))
def get_day_busy_hours(cls, activities, name): cursor = Transaction().connection.cursor() table = cls.__table__() employees = [x.employee.id for x in activities] min_date = min([x.date for x in activities]) max_date = max([x.date for x in activities]) query = table.select( table.employee, table.date, Sum(table.duration), where=((table.employee.in_(employees)) & (table.date >= min_date) & (table.date <= max_date)), group_by=(table.employee, table.date)) cursor.execute(*query) records = cursor.fetchall() sums = {} for record in records: sums[(record[0], record[1])] = record[2] res = {} for activity in activities: res[activity.id] = sums.get((activity.employee.id, activity.date), datetime.timedelta()) return res
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_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 _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 table_query(): pool = Pool() context = Transaction().context Reporte = pool.get('disc.reporte') reporte = Reporte.__table__() ReporteLinea = pool.get('disc.reporte.linea') reporte_linea = ReporteLinea.__table__() where = Literal(True) if Transaction().context.get('fecha_inicio'): where &= reporte.fecha_inicio >= Transaction( ).context['fecha_inicio'] if Transaction().context.get('fecha_fin'): where &= reporte.fecha_fin <= Transaction().context['fecha_fin'] return (reporte.join( reporte_linea, condition=reporte_linea.reporte == reporte.id).select( Max(reporte_linea.id * 1000).as_('id'), Max(reporte.create_uid).as_('create_uid'), Max(reporte.create_date).as_('create_date'), Max(reporte.write_uid).as_('write_uid'), Max(reporte.write_date).as_('write_date'), Max(reporte.campo).as_('campo'), reporte_linea.gp, (Sum(reporte_linea.cantidad)).as_('total'), where=where, group_by=(reporte_linea.gp), ))
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_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 search_deposit(cls, name, clause): pool = Pool() MoveLine = pool.get('account.move.line') Account = pool.get('account.account') AccountType = pool.get('account.account.type') User = pool.get('res.user') line = MoveLine.__table__() account = Account.__table__() account_type = AccountType.__table__() user = User(Transaction().user) if not user.company: return [] line_clause, _ = MoveLine.query_get(line) Operator = fields.SQL_OPERATORS[clause[1]] query = (line.join(account, condition=account.id == line.account).join( account_type, condition=account.type == account_type.id).select( line.party, where=account.active & account_type.deposit & (line.party != Null) & (line.reconciliation == Null) & (account.company == user.company.id) & line_clause, group_by=line.party, having=Operator( Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0)), Decimal(clause[2] or 0)))) return [('id', 'in', query)]
def get_cost(self, name): pool = Pool() Work = pool.get('production.work') Cycle = pool.get('production.work.cycle') table = self.__table__() work = Work.__table__() cycle = Cycle.__table__() cursor = Transaction().connection.cursor() cost = super(Production, self).get_cost(name) cursor.execute(*table.join(work, 'LEFT', condition=work.production == table.id ).join(cycle, 'LEFT', condition=cycle.work == work.id ).select(Sum(cycle.cost), where=(cycle.state == 'done') & (table.id == self.id))) cycle_cost, = cursor.fetchone() if cycle_cost is not None: # SQLite uses float for SUM if not isinstance(cycle_cost, Decimal): cycle_cost = Decimal(cycle_cost) cost += cycle_cost digits = self.__class__.cost.digits return cost.quantize(Decimal(str(10 ** -digits[1])))
def search_payment_amount(cls, name, clause): pool = Pool() Payment = pool.get('account.payment') Account = pool.get('account.account') _, operator, value = clause Operator = fields.SQL_OPERATORS[operator] table = cls.__table__() payment = Payment.__table__() account = Account.__table__() payment_amount = Sum(Coalesce(payment.amount, 0)) main_amount = Abs(table.credit - table.debit) - payment_amount second_amount = Abs(table.amount_second_currency) - payment_amount amount = Case((table.second_currency == Null, main_amount), else_=second_amount) value = cls.payment_amount.sql_format(value) query = table.join( payment, type_='LEFT', condition=(table.id == payment.line) & (payment.state != 'failed')).join( account, condition=table.account == account.id).select( table.id, where=account.kind.in_(['payable', 'receivable']), group_by=(table.id, account.kind, table.second_currency), having=Operator(amount, value)) return [('id', 'in', query)]
def get_deposit_balance(self, deposit_account): 'Return the deposit account balance (debit - credit) for the party' pool = Pool() MoveLine = pool.get('account.move.line') transaction = Transaction() cursor = transaction.connection.cursor() line = MoveLine.__table__() assert deposit_account.type.deposit where = ((line.account == deposit_account.id) & (line.party == self.id) & (line.reconciliation == Null)) if transaction.database.has_select_for(): cursor.execute(*line.select( Literal(1), where=where, for_=For('UPDATE', nowait=True))) else: MoveLine.lock() cursor.execute(*line.select(Sum( Coalesce(line.debit, 0) - Coalesce(line.credit, 0)), where=where)) amount, = cursor.fetchone() if amount and not isinstance(amount, Decimal): currency = deposit_account.company.currency amount = currency.round(Decimal(str(amount))) return amount or Decimal(0)
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 table_query(cls): pool = Pool() Timesheet = pool.get('timesheet.line') line = Timesheet.__table__() timesheet = line.select( Min(line.id * 2 + 1).as_('id'), line.company.as_('company'), line.employee.as_('employee'), Sum(line.duration).as_('duration'), line.date.as_('date'), group_by=[line.company, line.employee, line.date]) attendance = super().table_query() return (attendance.join( timesheet, 'FULL' if backend.name != 'sqlite' else 'LEFT', condition=(attendance.company == timesheet.company) & (attendance.employee == timesheet.employee) & (attendance.date == timesheet.date)).select( Coalesce(attendance.id, timesheet.id).as_('id'), Literal(0).as_('create_uid'), CurrentTimestamp().as_('create_date'), cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'), cls.write_date.sql_cast(Literal(Null)).as_('write_date'), Coalesce(attendance.company, timesheet.company).as_('company'), Coalesce(attendance.employee, timesheet.employee).as_('employee'), attendance.duration.as_('duration'), timesheet.duration.as_('timesheet_duration'), Coalesce(attendance.date, timesheet.date).as_('date'), ))
def _column_revenue(cls, tables, withs, sign): move = tables['move'] currency = withs['currency_rate'] currency_company = withs['currency_rate_company'] return Sum(sign * cls.revenue.sql_cast(move.quantity) * Coalesce(move.unit_price, 0) * Coalesce(currency_company.rate / currency.rate, 0))
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 _grouped_columns(cls, line): return [ Max(line.statement).as_('statement'), Max(line.number).as_('number'), Max(line.date).as_('date'), Sum(line.amount).as_('amount'), Max(line.party).as_('party'), ]
def _column_cost(cls, tables, withs, sign): move = tables['move'] cost = super()._column_cost(tables, withs, sign) if Transaction().context.get('include_shipment_cost'): cost += Sum( sign * cls.cost.sql_cast(move.internal_quantity) * Coalesce(move.shipment_out_cost_price, 0)) return cost
def get_balance(cls, items, names): ''' Function to compute hat balance for artist or pocket balance party items. ''' res = {} pool = Pool() MoveLine = pool.get('account.move.line') Account = pool.get('account.account') User = pool.get('res.user') cursor = Transaction().cursor line = MoveLine.__table__() account = Account.__table__() for name in names: if name not in ('hat_balance', 'pocket_balance'): raise Exception('Bad argument') res[name] = dict((i.id, Decimal('0.0')) for i in items) user_id = Transaction().user if user_id == 0 and 'user' in Transaction().context: user_id = Transaction().context['user'] user = User(user_id) if not user.company: return res company_id = user.company.id with Transaction().set_context(posted=False): line_query, _ = MoveLine.query_get(line) clause = () if name == 'hat_balance': field = line.artist clause = line.artist.in_([i.id for i in items]) if name == 'pocket_balance': field = line.party clause = line.party.in_([i.id for i in items]) for name in names: query = line.join( account, condition=account.id == line.account).select( field, Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0)), where=account.active & (account.kind == name[:-8]) & clause & (line.reconciliation == None) & (account.company == company_id) & line_query, group_by=field) cursor.execute(*query) for id_, sum in cursor.fetchall(): # SQLite uses float for SUM if not isinstance(sum, Decimal): sum = Decimal(str(sum)) res[name][id_] = -sum return res
def table_query(cls): Opportunity = Pool().get('sale.opportunity') opportunity = Opportunity.__table__() return opportunity.select( Max(opportunity.create_uid).as_('create_uid'), Max(opportunity.create_date).as_('create_date'), Max(opportunity.write_uid).as_('write_uid'), Max(opportunity.write_date).as_('write_date'), opportunity.company, Count(Literal(1)).as_('number'), Sum( Case((opportunity.state.in_( cls._converted_state()), Literal(1)), else_=Literal(0))).as_('converted'), Sum( Case((opportunity.state.in_(cls._won_state()), Literal(1)), else_=Literal(0))).as_('won'), Sum( Case((opportunity.state.in_(cls._lost_state()), Literal(1)), else_=Literal(0))).as_('lost'), Sum(opportunity.amount).as_('amount'), Sum( Case((opportunity.state.in_( cls._converted_state()), opportunity.amount), else_=Literal(0))).as_('converted_amount'), Sum( Case((opportunity.state.in_( cls._won_state()), opportunity.amount), else_=Literal(0))).as_('won_amount'))
def get_quantity_executed(cls, lines, name): cursor = Transaction().connection.cursor() pool = Pool() Move = pool.get('stock.move') Location = pool.get('stock.location') Uom = pool.get('product.uom') Forecast = pool.get('stock.forecast') LineMove = pool.get('stock.forecast.line-stock.move') move = Move.__table__() location_from = Location.__table__() location_to = Location.__table__() line_move = LineMove.__table__() result = dict((x.id, 0) for x in lines) def key(line): return 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 != 'cancelled') & (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: line = product2line[product_id] result[line.id] = Uom.compute_qty(line.product.default_uom, quantity, line.uom) return result
def _columns(cls, tables, withs): move = tables['move'] from_location = tables['move.from_location'] to_location = tables['move.to_location'] template = tables['move.product.template'] sign = Case((from_location.type.in_(cls._to_location_types()) & to_location.type.in_(cls._from_location_types()), -1), else_=1) return super()._columns(tables, withs) + [ move.product.as_('product'), Sum(sign * move.internal_quantity).as_('internal_quantity'), template.default_uom.as_('unit'), ]
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().connection.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=( Concat(cls.__name__ + ',', table.id) == timesheet_work.origin) ).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 search_receivable_payable(cls, name, clause): 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') line = MoveLine.__table__() account = Account.__table__() account_type = AccountType.__table__() if name not in ('receivable', 'payable', 'receivable_today', 'payable_today'): raise Exception('Bad argument') _, operator, value = clause user = User(Transaction().user) if not user.company: return [] company_id = user.company.id with Transaction().set_context(company=company_id): today = Date.today() code = name today_query = Literal(True) if name in ('receivable_today', 'payable_today'): code = name[:-6] today_query = ((line.maturity_date <= today) | (line.maturity_date == Null)) Operator = fields.SQL_OPERATORS[operator] # Need to cast numeric for sqlite cast_ = MoveLine.debit.sql_cast amount = cast_(Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0))) if operator in {'in', 'not in'}: value = [cast_(Literal(Decimal(v or 0))) for v in value] else: value = cast_(Literal(Decimal(value or 0))) query = (line.join(account, condition=account.id == line.account).join( account_type, condition=account.type == account_type.id).select( line.party, where=(getattr(account_type, code) & (line.party != Null) & (line.reconciliation == Null) & (account.company == company_id) & today_query), group_by=line.party, having=Operator(amount, value))) return [('id', 'in', query)]