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