def test_mul(self):
for mul in [
Mul(self.table.c1, self.table.c2),
self.table.c1 * self.table.c2
]:
self.assertEqual(str(mul), '("c1" * "c2")')
self.assertEqual(mul.params, ())
def get_warehouse_query(cls):
"""
Returns the data warehouse select query. This query only returns a
select query and the tables themselves, but does not execute it. This
gives downstream modules room to change the query.
"""
table = lambda pool_name: Pool().get(pool_name).__table__()
sale_line = table('sale.line')
sale_sale = table('sale.sale')
product_template = table('product.template')
product_product = table('product.product')
product_category = table('product.category')
party_party = table('party.party')
shipment_address = table('party.address')
invoice_address = table('party.address')
invoice_country = table('country.country')
invoice_subdivision = table('country.subdivision')
shipment_country = table('country.country')
currency_currency = table('currency.currency')
shipment_subdivision = table('country.subdivision')
tables = {
'sale.line': sale_line,
'sale.sale': sale_sale,
'product.template': product_template,
'product.product': product_product,
'product.category': product_category,
'party.party': party_party,
}
columns = [
sale_line.id.as_('id'),
sale_line.quantity.as_('quantity'),
Mul(sale_line.quantity, sale_line.unit_price).as_('amount'),
product_product.code.as_('product_code'),
product_template.name.as_('product_name'),
product_category.name.as_('product_category'),
party_party.name.as_('party_name'),
party_party.id.as_('party_id'),
# Sale primary data
sale_sale.id.as_('sale_id'),
sale_sale.reference.as_('sale_reference'),
currency_currency.code.as_('currency'),
sale_sale.state.as_('state'),
# Address, country information
invoice_country.code.as_('invoice_country_code'),
invoice_country.name.as_('invoice_country_name'),
invoice_subdivision.code.as_('invoice_state_code'),
invoice_subdivision.name.as_('invoice_state_name'),
shipment_country.code.as_('shipment_country_code'),
shipment_country.name.as_('shipment_country_name'),
shipment_subdivision.code.as_('shipment_state_code'),
shipment_subdivision.name.as_('shipment_state_name'),
# Sale date and subparts
sale_sale.sale_date.as_('sale_date'),
ToChar(sale_sale.sale_date, 'YYYY').as_('sale_year'),
ToChar(sale_sale.sale_date, 'MM').as_('sale_month'),
ToChar(sale_sale.sale_date, 'dd').as_('sale_day'),
]
from_ = sale_line.join(
sale_sale, condition=(sale_line.sale == sale_sale.id)).join(
product_product, 'LEFT OUTER',
(sale_line.product == product_product.id)).join(
product_template, 'LEFT OUTER',
(product_product.template == product_template.id)).join(
product_category, 'LEFT OUTER',
(product_template.category == product_category.id)
).join(
party_party, 'LEFT OUTER',
(sale_sale.party == party_party.id)).join(
shipment_address, 'LEFT OUTER',
(sale_sale.shipment_address
== shipment_address.id)).join(
shipment_country, 'LEFT OUTER',
(shipment_address.country
== shipment_country.id)).join(
shipment_subdivision, 'LEFT OUTER',
(shipment_address.subdivision
== shipment_subdivision.id)).join(
invoice_address, 'LEFT OUTER',
(sale_sale.invoice_address
== invoice_address.id)).join(
invoice_country, 'LEFT OUTER',
(invoice_address.country
== invoice_country.id)
).join(
invoice_subdivision,
'LEFT OUTER',
(invoice_address.subdivision
== invoice_subdivision.id
)).join(
currency_currency,
'LEFT OUTER',
(sale_sale.currency
== currency_currency.id))
try:
sale_channel = table('sale.channel')
except KeyError:
# Module is not installed
pass
else:
from_ = from_.join(sale_channel, 'LEFT OUTER',
(sale_sale.channel == sale_channel.id))
columns.extend([
sale_channel.code.as_('channel_code'),
sale_channel.name.as_('channel_name'),
])
# Build a where clause for states
where = (sale_sale.state.in_(
('confirmed', 'processing', 'done'))) & (sale_line.type == 'line')
return from_, columns, where, tables
def __mul__(self, other):
from sql.operators import Mul
return Mul(self, other)
def search_balance(cls, name, clause):
pool = Pool()
MoveLine = pool.get('account.move.line')
Account = pool.get('account.account')
Company = pool.get('company.company')
User = pool.get('res.user')
line = MoveLine.__table__()
account = Account.__table__()
if name not in ('hat_balance', 'pocket_balance'):
raise Exception('Bad argument')
company_id = None
user_id = Transaction().user
if user_id == 0 and 'user' in Transaction().context:
user_id = Transaction().context['user']
user = User(user_id)
if Transaction().context.get('company'):
child_companies = Company.search([
('parent', 'child_of', [user.main_company.id]),
])
if Transaction().context['company'] in child_companies:
company_id = Transaction().context['company']
if not company_id:
if user.company:
company_id = user.company.id
elif user.main_company:
company_id = user.main_company.id
if not company_id:
return []
line_query, _ = MoveLine.query_get(line)
Operator = fields.SQL_OPERATORS[clause[1]]
if name == 'hat_balance':
field = line.artist
where_clause = (line.artist != None)
sign = -1
if name == 'pocket_balance':
field = line.party
where_clause = (line.party != None)
sign = 1
query = line.join(account,
condition=account.id == line.account).select(
field,
where=account.active
& (account.kind == 'hat')
& where_clause
& (line.reconciliation == None)
& (account.company == company_id)
& line_query,
group_by=field,
having=Operator(
Mul(
sign,
Sum(
Coalesce(line.debit, 0) -
Coalesce(line.credit, 0))),
Decimal(clause[2] or 0)))
return [('id', 'in', query)]