def table_query(self=None):
price_profit_loss = Pool().get('hrp_report.price_profit_loss_content')
ProfitLoss = price_profit_loss.__table__()
where = Literal(True)
condition = []
if Transaction().context.get('location') != None:
condition.append(('location', '=', Transaction().context.get('location')))
if Transaction().context.get('start_time') != None:
condition.append(('effective_date', '>=', Transaction().context.get('start_time')))
if Transaction().context.get('end_time') != None:
condition.append(('effective_date', '<=', Transaction().context.get('end_time')))
if Transaction().context.get('drug_type') != None:
if Transaction().context.get('drug_type') == '06':
pass
else:
condition.append(('drug_type', '=', Transaction().context.get('drug_type')))
product_ids = price_profit_loss.search([condition], query=True, order=[])
where &= ProfitLoss.id.in_(product_ids)
Result = ProfitLoss.select(
ProfitLoss.id.as_('id'),
Max(ProfitLoss.create_uid).as_('create_uid'),
Max(ProfitLoss.create_date).as_('create_date'),
Max(ProfitLoss.write_uid).as_('write_uid'),
Max(ProfitLoss.write_date).as_('write_date'),
ProfitLoss.uom,
where=where,
group_by=ProfitLoss.id)
return Result
def table_query():
pool = Pool()
inventory_two = pool.get('hrp_inventory.inventory_two').__table__()
inventory_lines = pool.get(
'hrp_inventory.inventory_two_lines').__table__()
join1 = Join(inventory_lines, inventory_two)
join1.condition = join1.right.id == inventory_lines.inventory
where = Literal(True)
if Transaction().context.get('inventor_time'):
where &= inventory_two.id == Transaction().context['inventor_time']
where &= inventory_lines.category == Transaction(
).context['category']
where &= inventory_lines.type != 'balance'
Result = join1.select(
join1.left.id.as_('id'),
Max(inventory_lines.create_uid).as_('create_uid'),
Max(inventory_lines.create_date).as_('create_date'),
Max(inventory_lines.write_uid).as_('write_uid'),
Max(inventory_lines.write_date).as_('write_date'),
inventory_lines.code,
inventory_lines.uom,
inventory_lines.name,
inventory_lines.product,
inventory_lines.drug_specifications,
inventory_lines.cost_pice,
inventory_lines.type,
inventory_lines.differences_why,
where=where,
group_by=inventory_lines.id)
return Result
def table_query():
pool = Pool()
Evaluation = pool.get('gnuhealth.patient.evaluation')
evaluation = Evaluation.__table__()
source = evaluation
where = evaluation.diagnosis != None
if Transaction().context.get('start_date'):
where &= evaluation.evaluation_start >= \
Transaction().context['start_date']
if Transaction().context.get('end_date'):
where &= evaluation.evaluation_start <= \
Transaction().context['end_date']
if Transaction().context.get('group'):
DiseaseGroupMembers = pool.get('gnuhealth.disease_group.members')
diseasegroupmembers = DiseaseGroupMembers.__table__()
join = Join(evaluation, diseasegroupmembers)
join.condition = join.right.name == evaluation.diagnosis
where &= join.right.disease_group == Transaction().context['group']
source = join
select = source.select(evaluation.diagnosis.as_('id'),
Max(evaluation.create_uid).as_('create_uid'),
Max(evaluation.create_date).as_('create_date'),
Max(evaluation.write_uid).as_('write_uid'),
Max(evaluation.write_date).as_('write_date'),
evaluation.diagnosis.as_('disease'),
Count(evaluation.diagnosis).as_('cases'),
where=where,
group_by=evaluation.diagnosis)
if Transaction().context.get('number_records'):
select.limit = Transaction().context['number_records']
return select
def table_query():
pool = Pool()
evaluation = pool.get('gnuhealth.patient.evaluation').__table__()
party = pool.get('party.party').__table__()
patient = pool.get('gnuhealth.patient').__table__()
du = pool.get('gnuhealth.du').__table__()
sector = pool.get('gnuhealth.operational_sector').__table__()
join1 = Join(evaluation, patient)
join1.condition = join1.right.id == evaluation.patient
join2 = Join(join1, party)
join2.condition = join2.right.id == join1.right.name
join3 = Join(join2, du)
join3.condition = join3.right.id == join2.right.du
join4 = Join(join3, sector)
join4.condition = join4.right.id == join3.right.operational_sector
where = Literal(True)
if Transaction().context.get('start_date'):
where &= evaluation.evaluation_start >= \
Transaction().context['start_date']
if Transaction().context.get('end_date'):
where &= evaluation.evaluation_start <= \
Transaction().context['end_date']
return join4.select(join4.right.id,
Max(evaluation.create_uid).as_('create_uid'),
Max(evaluation.create_date).as_('create_date'),
Max(evaluation.write_uid).as_('write_uid'),
Max(evaluation.write_date).as_('write_date'),
join4.right.id.as_('sector'),
Count(join4.right.id).as_('evaluations'),
where=where,
group_by=join4.right.id)
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 table_query(cls):
pool = Pool()
Property = pool.get('ir.property')
Field = pool.get('ir.model.field')
property_history = Property.__table_history__()
field = Field.__table__()
return property_history.join(
field, condition=field.id == property_history.field).select(
Max(Column(property_history, '__id')).as_('id'),
Max(property_history.create_uid).as_('create_uid'),
Max(property_history.create_date).as_('create_date'),
Max(property_history.write_uid).as_('write_uid'),
Max(property_history.write_date).as_('write_date'),
Coalesce(property_history.write_date,
property_history.create_date).as_('date'),
Trim(Substring(property_history.res, ',.*'), 'LEADING',
',').cast(cls.template.sql_type().base).as_('template'),
Trim(property_history.value, 'LEADING', ',').cast(
cls.cost_price.sql_type().base).as_('cost_price'),
where=(field.name == 'cost_price')
& property_history.res.like('product.template,%'),
group_by=(property_history.id,
Coalesce(property_history.write_date,
property_history.create_date),
property_history.res, property_history.value))
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 _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 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 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 table_query():
pool = Pool()
Date = pool.get('ir.date')
Location = Pool().get('stock.location')
Product = pool.get('product.product')
Template = pool.get('product.template')
AvailableMedicineLine = pool.get(
"hrp_inventory.available_medicine_line")
template = pool.get('product.template').__table__()
products = pool.get('product.product').__table__()
available_medicine_line = pool.get(
'hrp_inventory.available_medicine_line').__table__()
where = Literal(True)
ids = []
if Transaction().context.get('location') and Transaction().context.get(
'category'):
with Transaction().set_context(stock_date_end=Date.today(),
stock_assign=True):
pbl = Product.products_by_location([
Transaction().context['location'],
Location(
Transaction().context['location']).freeze_location.id
],
with_childs=True)
for key, value in pbl.items():
if value > 0 and key[1] is not None:
if Product(key[1]).template.categories[
0].id == Transaction().context['category']:
ids.append(key[1])
set(ids)
where &= products.id.in_(ids)
if not ids:
where = Literal(False)
if Transaction().context.get('shelves_code'):
ids2 = []
lines = AvailableMedicineLine.search([
('product', 'in', ids),
('warehouse', '=', Transaction().context['location'])
])
for i in lines:
ids2.append(i)
jiao = [val for val in ids2 if val in ids]
where &= products.id.in_(jiao)
Result = products.select(products.id.as_('id'),
Max(products.create_uid).as_('create_uid'),
Max(products.create_date).as_('create_date'),
Max(products.write_uid).as_('write_uid'),
Max(products.write_date).as_('write_date'),
where=where,
group_by=products.id)
return Result
def commit(cls, transaction):
table = Table(cls._table)
reset = cls._reset.pop(transaction, None)
if not reset:
return
database = transaction.database
dbname = database.name
if not _clear_timeout and transaction.database.has_channel():
with transaction.connection.cursor() as cursor:
# The count computed as
# 8000 (max notify size) / 64 (max name data len)
for sub_reset in grouped_slice(reset, 125):
cursor.execute(
'NOTIFY "%s", %%s' % cls._channel,
(json.dumps(list(sub_reset), separators=(',', ':')),))
else:
connection = database.get_connection(
readonly=False, autocommit=True)
try:
with connection.cursor() as cursor:
for name in reset:
cursor.execute(*table.select(table.name, table.id,
table.timestamp,
where=table.name == name,
limit=1))
if cursor.fetchone():
# It would be better to insert only
cursor.execute(*table.update([table.timestamp],
[CurrentTimestamp()],
where=table.name == name))
else:
cursor.execute(*table.insert(
[table.timestamp, table.name],
[[CurrentTimestamp(), name]]))
cursor.execute(*table.select(
Max(table.timestamp),
where=table.name == name))
timestamp, = cursor.fetchone()
cursor.execute(*table.select(
_cast(Max(table.timestamp)),
where=table.name == name))
timestamp, = cursor.fetchone()
inst = cls._instances[name]
inst._clear(dbname, timestamp)
connection.commit()
finally:
database.put_connection(connection)
cls._clean_last = datetime.now()
reset.clear()
def commit(cls, transaction):
table = Table(cls._table)
reset = cls._reset.setdefault(transaction, set())
if not reset:
return
database = transaction.database
dbname = database.name
if not _clear_timeout and transaction.database.has_channel():
with transaction.connection.cursor() as cursor:
# JCA: Fix for https://bugs.tryton.org/issue8781
resets = list(reset)
for i in range(0, len(resets), 10):
cursor.execute('NOTIFY "%s", %%s' % cls._channel,
(json.dumps(resets[i:i + 10],
separators=(',', ':')), ))
else:
connection = database.get_connection(readonly=False,
autocommit=True)
try:
with connection.cursor() as cursor:
for name in reset:
cursor.execute(*table.select(table.name,
table.id,
table.timestamp,
where=table.name == name,
limit=1))
if cursor.fetchone():
# It would be better to insert only
cursor.execute(*table.update(
[table.timestamp], [CurrentTimestamp()],
where=table.name == name))
else:
cursor.execute(
*table.insert([table.timestamp, table.name],
[[CurrentTimestamp(), name]]))
cursor.execute(*table.select(Max(table.timestamp),
where=table.name == name))
timestamp, = cursor.fetchone()
cursor.execute(
*table.select(_cast(Max(table.timestamp)),
where=table.name == name))
timestamp, = cursor.fetchone()
inst = cls._instances[name]
inst._clear(dbname, timestamp)
connection.commit()
finally:
database.put_connection(connection)
reset.clear()
def get_access(cls, models):
'Return fields access for models'
# root user above constraint
if Transaction().user == 0:
return defaultdict(lambda: defaultdict(
lambda: defaultdict(lambda: True)))
pool = Pool()
Model = pool.get('ir.model')
ModelField = pool.get('ir.model.field')
UserGroup = pool.get('res.user-res.group')
user = Transaction().user
field_access = cls.__table__()
ir_model = Model.__table__()
model_field = ModelField.__table__()
user_group = UserGroup.__table__()
accesses = {}
for model in models:
maccesses = cls._get_access_cache.get((user, model))
if maccesses is None:
break
accesses[model] = maccesses
else:
return accesses
default = {}
accesses = dict((m, default) for m in models)
cursor = Transaction().connection.cursor()
cursor.execute(*field_access.join(model_field,
condition=field_access.field == model_field.id
).join(ir_model,
condition=model_field.model == ir_model.id
).join(user_group, 'LEFT',
condition=user_group.group == field_access.group
).select(
ir_model.model,
model_field.name,
Max(Case((field_access.perm_read == True , 1), else_=0)),
Max(Case((field_access.perm_write == True, 1), else_=0)),
Max(Case((field_access.perm_create == True, 1), else_=0)),
Max(Case((field_access.perm_delete == True, 1), else_=0)),
where=ir_model.model.in_(models)
& ((user_group.user == user) | (field_access.group == Null)),
group_by=[ir_model.model, model_field.name]))
for m, f, r, w, c, d in cursor.fetchall():
accesses[m][f] = {'read': r, 'write': w, 'create': c, 'delete': d}
for model, maccesses in accesses.iteritems():
cls._get_access_cache.set((user, model), maccesses)
return accesses
def get_access(cls, models):
'Return access for models'
# root user above constraint
if Transaction().user == 0:
return defaultdict(lambda: defaultdict(lambda: True))
pool = Pool()
Model = pool.get('ir.model')
UserGroup = pool.get('res.user-res.group')
cursor = Transaction().cursor
user = Transaction().user
model_access = cls.__table__()
ir_model = Model.__table__()
user_group = UserGroup.__table__()
access = {}
for model in models:
maccess = cls._get_access_cache.get((user, model), default=-1)
if maccess == -1:
break
access[model] = maccess
else:
return access
default = {'read': True, 'write': True, 'create': True, 'delete': True}
access = dict((m, default) for m in models)
cursor.execute(*model_access.join(
ir_model, 'LEFT', condition=model_access.model == ir_model.id
).join(user_group,
'LEFT',
condition=user_group.group == model_access.group).select(
ir_model.model,
Max(Case((model_access.perm_read, 1), else_=0)),
Max(Case((model_access.perm_write, 1), else_=0)),
Max(Case((model_access.perm_create, 1), else_=0)),
Max(Case((model_access.perm_delete, 1), else_=0)),
where=ir_model.model.in_(models)
& ((user_group.user == user)
| (model_access.group == Null)),
group_by=ir_model.model))
access.update(
dict((m, {
'read': r,
'write': w,
'create': c,
'delete': d
}) for m, r, w, c, d in cursor.fetchall()))
for model, maccess in access.iteritems():
cls._get_access_cache.set((user, model), maccess)
return access
def table_query(cls):
pool = Pool()
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
Move = pool.get('account.move')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
invoice = Invoice.__table__()
invoice_tax = InvoiceTax.__table__()
move = Move.__table__()
period = Period.__table__()
tax = Tax.__table__()
sales = super().table_query()
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear')))
if context.get('period'):
where &= (period.id == context.get('period'))
where &= ((tax.es_ec_purchases_list_code != Null)
& (tax.es_ec_purchases_list_code != ''))
where &= invoice.type == 'in'
purchases = (invoice_tax.join(
invoice, condition=invoice_tax.invoice == invoice.id
).join(tax, condition=invoice_tax.tax == tax.id).join(
move, condition=invoice.move == move.id).join(
period, condition=move.period == period.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_ec_purchases_list_code.as_('code'),
Sum(invoice_tax.base).as_('amount'),
invoice.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party,
invoice.party_tax_identifier,
tax.es_ec_purchases_list_code,
invoice.currency,
]))
return sales | purchases
def table_query():
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
move = Move.__table__()
product_id = Transaction().context.get('product')
warehouse_id = Transaction().context.get('warehouse', -1)
warehouse_query = Location.search([
('parent', 'child_of', [warehouse_id]),
], query=True, order=[])
date_column = Coalesce(move.effective_date, move.planned_date
).as_('date')
return move.select(
Max(move.id).as_('id'),
Literal(0).as_('create_uid'),
Now().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
date_column,
where=(move.product == product_id)
& (move.from_location.in_(warehouse_query)
| move.to_location.in_(warehouse_query))
& (Coalesce(move.effective_date, move.planned_date) != Null),
group_by=(date_column, move.product))
def table_query(cls):
pool = Pool()
Month = pool.get('ir.calendar.month')
month = Month.__table__()
query = super(SaleOpportunityEmployeeMonthly, cls).table_query()
opportunity, = query.from_
type_id = cls.id.sql_type().base
type_year = cls.year.sql_type().base
year_column = Extract(
'YEAR', opportunity.start_date).cast(type_year).as_('year')
month_index = Extract('MONTH', opportunity.start_date)
query.from_ = opportunity.join(month,
condition=month_index == month.index)
query.columns += (
Max(
Extract('MONTH', opportunity.start_date) +
Extract('YEAR', opportunity.start_date) * 100 +
Coalesce(opportunity.employee, 0) *
1000000).cast(type_id).as_('id'),
year_column,
month.id.as_('month'),
opportunity.employee,
)
query.group_by = (year_column, month.id, opportunity.employee,
opportunity.company)
return query
def __register__(cls, module_name):
pool = Pool()
Move = pool.get('stock.move')
PurchaseLine = pool.get('purchase.line')
Purchase = pool.get('purchase.purchase')
cursor = Transaction().connection.cursor()
sql_table = cls.__table__()
move = Move.__table__()
line = PurchaseLine.__table__()
purchase = Purchase.__table__()
# Migration from 3.8: New supplier field
cursor.execute(*sql_table.select(
sql_table.supplier, where=sql_table.supplier == Null, limit=1))
if cursor.fetchone():
value = sql_table.join(
move,
condition=(Concat(
cls.__name__ + ',', sql_table.id) == move.shipment)).join(
line,
condition=(Concat(
PurchaseLine.__name__ + ',',
line.id) == move.origin)).join(
purchase,
condition=(
purchase.id == line.purchase)).select(
Max(purchase.party))
cursor.execute(*sql_table.update(columns=[sql_table.supplier],
values=[value]))
super(ShipmentInReturn, cls).__register__(module_name)
def _get_target_tables(self, tables):
Target = self.get_target()
table, _ = tables[None]
target_tables = tables.get(self.name)
context = Transaction().context
if target_tables is None:
if Target._history and context.get('_datetime'):
target = Target.__table_history__()
target_history = Target.__table_history__()
history_condition = Column(target, '__id').in_(
target_history.select(
Max(Column(target_history, '__id')),
where=Coalesce(
target_history.write_date,
target_history.create_date)
<= context['_datetime'],
group_by=target_history.id))
else:
target = Target.__table__()
history_condition = None
condition = target.id == self.sql_column(table)
if history_condition:
condition &= history_condition
target_tables = {
None: (target, condition),
}
tables[self.name] = target_tables
return target_tables
def table_query():
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
move = from_ = Move.__table__()
context = Transaction().context
if context.get('product_template') is not None:
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = (product.template == context['product_template'])
else:
product_clause = move.product == context.get('product', -1)
warehouse_id = context.get('warehouse', -1)
warehouse_query = Location.search([
('parent', 'child_of', [warehouse_id]),
],
query=True,
order=[])
date_column = Coalesce(move.effective_date,
move.planned_date).as_('date')
return from_.select(
Max(move.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
date_column,
where=product_clause
& (move.from_location.in_(warehouse_query)
| move.to_location.in_(warehouse_query))
& (Coalesce(move.effective_date, move.planned_date) != Null),
group_by=(date_column, move.product))
def last_visit_query(cls):
pool = Pool()
Visit = pool.get('sale.direct.visit')
tomorrow = Literal(
datetime.combine(date.today() + timedelta(days=1),
datetime.min.time()))
visit = Visit.__table__()
last_visit = visit.select(
visit.address.as_('address'),
Max(visit.time).as_('time'),
group_by=[visit.address],
)
visit = Visit.__table__()
revisit_required = (visit.revisit_time != Null)
revisit_today = (visit.revisit_time < tomorrow)
return visit.join(
last_visit,
condition=((visit.address == last_visit.address)
& (visit.time == last_visit.time))).select(
visit.id.as_('id'),
visit.address.as_('address'),
visit.time.as_('last_visit_time'),
visit.type.as_('last_visit_type'),
visit.notes.as_('last_visit_notes'),
revisit_required.as_('revisit_required'),
revisit_today.as_('revisit_today'),
visit.revisit_time.as_('revisit_due'),
)
def table_query(cls):
pool = Pool()
ProductCostPrice = pool.get('product.cost_price')
history = ProductCostPrice.__table_history__()
return history.select(Max(Column(history, '__id')).as_('id'),
Max(history.create_uid).as_('create_uid'),
Max(history.create_date).as_('create_date'),
Max(history.write_uid).as_('write_uid'),
Max(history.write_date).as_('write_date'),
Coalesce(history.write_date,
history.create_date).as_('date'),
history.product.as_('product'),
history.cost_price.as_('cost_price'),
group_by=(history.id,
Coalesce(history.write_date,
history.create_date),
history.product, history.cost_price))
def table_query(cls):
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
Date = pool.get('ir.date')
move = from_ = Move.__table__()
context = Transaction().context
today = Date.today()
if context.get('product_template') is not None:
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = (product.template == context['product_template'])
else:
product_clause = move.product == context.get('product', -1)
if 'warehouse' in context:
warehouse = Location(context.get('warehouse'))
if context.get('stock_skip_warehouse'):
location_id = warehouse.storage_location.id
else:
location_id = warehouse.id
else:
location_id = -1
warehouse = With('id',
query=Location.search([
('parent', 'child_of', [location_id]),
],
query=True,
order=[]))
date_column = Coalesce(move.effective_date, move.planned_date)
return (from_.select(
Max(move.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
date_column.as_('date'),
move.company.as_('company'),
where=product_clause
& ((move.from_location.in_(warehouse.select(warehouse.id))
& ~move.to_location.in_(warehouse.select(warehouse.id)))
| (~move.from_location.in_(warehouse.select(warehouse.id))
& move.to_location.in_(warehouse.select(warehouse.id))))
& ((date_column < today) & (move.state == 'done')
| (date_column >= today)),
group_by=(date_column, move.product, move.company),
with_=warehouse)
| Select([
Literal(0).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal(today).as_('date'),
Literal(context.get('company', -1)).as_('company'),
]))
def table_query():
pool = Pool()
Line = pool.get('timesheet.line')
line = Line.__table__()
where = Literal(True)
if Transaction().context.get('start_date'):
where &= line.date >= Transaction().context['start_date']
if Transaction().context.get('end_date'):
where &= line.date <= Transaction().context['end_date']
return line.select(line.employee.as_('id'),
Max(line.create_uid).as_('create_uid'),
Max(line.create_date).as_('create_date'),
Max(line.write_uid).as_('write_uid'),
Max(line.write_date).as_('write_date'),
line.employee,
Sum(line.duration).as_('duration'),
where=where,
group_by=line.employee)
def table_query(cls):
pool = Pool()
Line = pool.get('timesheet.line')
line = Line.__table__()
year_column = Extract('YEAR', line.date).as_('year')
week_column = Extract('WEEK', line.date).as_('week')
return line.select(Max(
Extract('WEEK', line.date) + Extract('YEAR', line.date) * 100 +
line.employee * 1000000).as_('id'),
Max(line.create_uid).as_('create_uid'),
Max(line.create_date).as_('create_date'),
Max(line.write_uid).as_('write_uid'),
Max(line.write_date).as_('write_date'),
year_column,
week_column,
line.employee,
Sum(line.duration).as_('duration'),
group_by=(year_column, week_column, line.employee))
def _column_id(cls, tables, withs):
pool = Pool()
Category = pool.get('party.category')
category = Category.__table__()
line = tables['line']
party_category = tables['line.customer.party_category']
# Get a stable number of categories over time
# by using a number one order bigger.
nb_category = category.select(
Power(10, (Ceil(Log(Max(category.id))) + Literal(1))))
return Min(line.id * nb_category + party_category.id)
def _column_id(cls, tables, withs):
pool = Pool()
Category = pool.get('product.category')
category = Category.__table__()
move = tables['move']
template_category = tables['move.product.template_category']
# Get a stable number of category over time
# by using number one order bigger.
nb_category = category.select(
Power(10, (Ceil(Log(Max(category.id))) + Literal(1))))
return Min(move.id * nb_category + template_category.id)
def table_query(cls):
pool = Pool()
Line = pool.get('timesheet.line')
line = Line.__table__()
type_name = cls.year.sql_type().base
year_column = Extract('YEAR', line.date).cast(type_name).as_('year')
month_column = Extract('MONTH', line.date).as_('month')
return line.select(
Max(Extract('MONTH', line.date)
+ Extract('YEAR', line.date) * 100
+ line.employee * 1000000).as_('id'),
Max(line.create_uid).as_('create_uid'),
Max(line.create_date).as_('create_date'),
Max(line.write_uid).as_('write_uid'),
Max(line.write_date).as_('write_date'),
year_column,
month_column,
line.employee,
Sum(line.duration).as_('duration'),
group_by=(year_column, month_column, line.employee))
def table_query(self=None):
price_list = Pool().get('price_master_datas.pricedata')
PriceList = price_list.__table__()
where = Literal(False)
if Transaction().context.get('type') == '01':
Product = Pool().get('product.product')
drug_code = Transaction().context.get('drug_code')
product_id = Product.search([('id', '=', drug_code)])
if product_id != []:
where = Literal(True)
product_id_price = product_id[0].id
Price = price_list.search(
[('retrieve_the_code', '=', product_id_price)],
query=True,
order=[])
where &= PriceList.id.in_(Price)
if Transaction().context.get('type') == '00':
condition = []
start_time = Transaction().context.get('start_time')
end_time = Transaction().context.get('end_time')
if start_time != None:
condition.append(('effective_date', '>=', start_time), )
if end_time != None:
condition.append(('effective_date', '<=', end_time), )
Price = price_list.search(condition)
if Price != []:
price_id = []
where = Literal(True)
for i in Price:
price_id.append(i.id)
where &= PriceList.id.in_(price_id)
Result = PriceList.select(
PriceList.id.as_('id'),
Max(PriceList.create_uid).as_('create_uid'),
Max(PriceList.create_date).as_('create_date'),
Max(PriceList.write_uid).as_('write_uid'),
Max(PriceList.write_date).as_('write_date'),
PriceList.drug_specifications.as_('drug_specifications'),
where=where,
group_by=PriceList.id)
return Result
