def transition_confirm(self):
cursor = Transaction().connection.cursor()
pool = Pool()
NotebookLine = pool.get('lims.notebook.line')
AnalyticProfessional = pool.get('lims.notebook.line.professional')
sql_table = NotebookLine.__table__()
NOW = datetime.now()
warnings = False
messages = ''
export_results = self.start.results_importer.exportResults()
previous_professionals = []
new_professionals = []
for line in self.result.result_lines:
columns = []
values = []
prevent_line = False
outcome = 'OK'
if line.imported_result != '-1000.0':
if not line.imported_end_date:
prevent_line = True
outcome = gettext('lims_instrument.msg_end_date')
elif (line.imported_end_date and line.start_date
and line.start_date > line.imported_end_date):
prevent_line = True
outcome = gettext(
'lims_instrument.msg_end_date_start_date')
elif (line.imported_inj_date and line.start_date
and line.start_date > line.imported_inj_date):
prevent_line = True
outcome = gettext(
'lims_instrument.msg_inj_date_start_date')
elif (line.imported_end_date and line.imported_inj_date
and line.imported_inj_date > line.imported_end_date):
prevent_line = True
outcome = gettext('lims_instrument.msg_inj_date_end_date')
else:
columns.append(sql_table.result)
values.append(line.imported_result)
columns.append(sql_table.end_date)
values.append(line.imported_end_date)
columns.append(sql_table.injection_date)
values.append(line.imported_inj_date)
else:
columns.append(sql_table.result)
values.append(Null)
columns.append(sql_table.result_modifier)
values.append(Literal('na'))
columns.append(sql_table.report)
values.append(Literal(False))
columns.append(sql_table.annulled)
values.append(Literal(True))
columns.append(sql_table.annulment_date)
values.append(NOW)
columns.append(sql_table.end_date)
values.append(Null)
columns.append(sql_table.injection_date)
values.append(Null)
columns.append(sql_table.literal_result)
values.append(line.imported_literal_result)
columns.append(sql_table.chromatogram)
values.append(line.imported_chromatogram)
columns.append(sql_table.device)
values.append(line.imported_device and line.imported_device.id
or Null)
columns.append(sql_table.dilution_factor)
values.append(line.imported_dilution_factor)
columns.append(sql_table.rm_correction_formula)
values.append(line.imported_rm_correction_formula)
line_previous_professionals = []
if line.imported_professionals:
profs = self.get_professionals(line.imported_professionals)
if profs:
validated, msg = self.check_professionals(
profs, line.method)
if validated:
line_previous_professionals = [
p for p in line.professionals
]
line_new_professionals = [{
'notebook_line': line.id,
'professional': p[0],
} for p in profs]
else:
prevent_line = True
outcome = msg
else:
prevent_line = True
outcome = gettext('lims_instrument.msg_professionals',
code=str(line.imported_professionals))
if prevent_line:
warnings = True
message = '%s [%s] (%s): %s\n' % (line.fraction.number,
line.analysis.code,
line.repetition, outcome)
messages += message
# Update rawresults
if export_results:
rawresults = self.start.results_importer.rawresults
number = line.fraction.number
if number in rawresults:
code = line.analysis.code
if code in rawresults[number]:
rep = line.repetition
if rep in rawresults[number][code]:
rawresults[number][code][rep]['outcome'] = (
outcome)
else:
previous_professionals.extend(line_previous_professionals)
new_professionals.extend(line_new_professionals)
columns.append(sql_table.imported_result)
values.append(Null)
columns.append(sql_table.imported_literal_result)
values.append(Null)
columns.append(sql_table.imported_end_date)
values.append(Null)
columns.append(sql_table.imported_professionals)
values.append(Null)
columns.append(sql_table.imported_chromatogram)
values.append(Null)
columns.append(sql_table.imported_device)
values.append(Null)
columns.append(sql_table.imported_dilution_factor)
values.append(Null)
columns.append(sql_table.imported_rm_correction_formula)
values.append(Null)
columns.append(sql_table.imported_inj_date)
values.append(Null)
# Write Results to Notebook lines
cursor.execute(*sql_table.update(
columns, values, where=(sql_table.id == line.id)))
# Update Professionals
AnalyticProfessional.delete(previous_professionals)
AnalyticProfessional.create(new_professionals)
if warnings:
self.warning.msg = messages
return 'warning'
if export_results:
return 'end' # 'export'
return 'end'
def convert_domain(self, domain, tables, Model):
from ..modelsql import convert_from
pool = Pool()
Rule = pool.get('ir.rule')
Target = self.get_target()
Relation = pool.get(self.relation_name)
transaction = Transaction()
table, _ = tables[None]
name, operator, value = domain[:3]
assert operator not in {'where', 'not where'} or '.' not in name
if Relation._history and transaction.context.get('_datetime'):
relation = Relation.__table_history__()
history_where = (Coalesce(relation.write_date,
relation.create_date) <=
transaction.context['_datetime'])
else:
relation = Relation.__table__()
history_where = None
origin_field = Relation._fields[self.origin]
origin = getattr(Relation, self.origin).sql_column(relation)
origin_where = None
if origin_field._type == 'reference':
origin_where = origin.like(Model.__name__ + ',%')
origin = Cast(
Substring(origin,
Position(',', origin) + Literal(1)),
Relation.id.sql_type().base)
target = getattr(Relation, self.target).sql_column(relation)
if '.' not in name:
if operator.endswith('child_of') or operator.endswith('parent_of'):
if Target != Model:
if operator.endswith('child_of'):
target_operator = 'child_of'
else:
target_operator = 'parent_of'
target_domain = [
(domain[3], target_operator, value),
]
if self.filter:
target_domain.append(self.filter)
query = Target.search(target_domain, order=[], query=True)
where = (target.in_(query) & (origin != Null))
if history_where:
where &= history_where
if origin_where:
where &= origin_where
query = relation.select(origin, where=where)
expression = table.id.in_(query)
if operator.startswith('not'):
return ~expression
return expression
if isinstance(value, str):
target_domain = [('rec_name', 'ilike', value)]
if self.filter:
target_domain.append(self.filter)
targets = Target.search(target_domain, order=[])
ids = [t.id for t in targets]
else:
if not isinstance(value, (list, tuple)):
ids = [value]
else:
ids = value
if self.filter:
targets = Target.search(
[('id', 'in', ids), self.filter], order=[])
ids = [t.id for t in targets]
if not ids:
expression = Literal(False)
if operator.startswith('not'):
return ~expression
return expression
else:
return self.convert_domain_tree((name, operator, ids),
tables)
if value is None:
where = origin != value
if history_where:
where &= history_where
if origin_where:
where &= origin_where
if self.filter:
query = Target.search(self.filter, order=[], query=True)
where &= target.in_(query)
query = relation.select(origin, where=where)
expression = ~table.id.in_(query)
if operator == '!=':
return ~expression
return expression
else:
if isinstance(value, str):
target_name = 'rec_name'
else:
target_name = 'id'
else:
_, target_name = name.split('.', 1)
if operator not in {'where', 'not where'}:
relation_domain = [
('%s.%s' % (self.target, target_name), ) + tuple(domain[1:])
]
if origin_field._type == 'reference':
relation_domain.append(
(self.origin, 'like', Model.__name__ + ',%'))
else:
relation_domain = [self.target, operator, value]
rule_domain = Rule.domain_get(Relation.__name__, mode='read')
if rule_domain:
relation_domain = [relation_domain, rule_domain]
if self.filter:
relation_domain = [
relation_domain,
(self.target, 'where', self.filter),
]
relation_tables = {
None: (relation, None),
}
tables, expression = Relation.search_domain(relation_domain,
tables=relation_tables)
query_table = convert_from(None, relation_tables)
query = query_table.select(origin, where=expression)
return table.id.in_(query)
def get_receivable_payable(cls, parties, names):
'''
Function to compute receivable, payable (today or not) for party ids.
'''
result = {}
pool = Pool()
MoveLine = pool.get('account.move.line')
Account = pool.get('account.account')
User = pool.get('res.user')
Date = pool.get('ir.date')
cursor = Transaction().connection.cursor()
line = MoveLine.__table__()
account = Account.__table__()
for name in names:
if name not in ('receivable', 'payable', 'receivable_today',
'payable_today'):
raise Exception('Bad argument')
result[name] = dict((p.id, Decimal('0.0')) for p in parties)
user = User(Transaction().user)
if not user.company:
return result
company_id = user.company.id
to_round = False
amount = Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0))
for name in names:
code = name
today_where = Literal(True)
if name in ('receivable_today', 'payable_today'):
code = name[:-6]
today_where = ((line.maturity_date <= Date.today())
| (line.maturity_date == Null))
for sub_parties in grouped_slice(parties):
sub_ids = [p.id for p in sub_parties]
party_where = reduce_ids(line.party, sub_ids)
cursor.execute(
*line.join(account, condition=account.id == line.account).
select(line.party,
amount,
where=(account.active
& (account.kind == code)
& (line.reconciliation == Null)
& (account.company == company_id)
& party_where
& today_where),
group_by=line.party))
for party, value in cursor.fetchall():
# SQLite uses float for SUM
if not isinstance(value, Decimal):
value = Decimal(str(value))
to_round = True
result[name][party] = value
# Float amount must be rounded to get the right precision
if to_round:
currency = user.company.currency
for name, values in result.iteritems():
for id, value in values.iteritems():
values[id] = currency.round(value)
return result
def convert_domain(self, domain, tables, Model):
pool = Pool()
Rule = pool.get('ir.rule')
Target = self.get_target()
table, _ = tables[None]
name, operator, value = domain[:3]
column = self.sql_column(table)
if '.' not in name:
if operator.endswith('child_of') or operator.endswith('parent_of'):
if Target != Model:
if operator.endswith('child_of'):
target_operator = 'child_of'
else:
target_operator = 'parent_of'
query = Target.search([
(domain[3], target_operator, value),
],
order=[],
query=True)
expression = column.in_(query)
if operator.startswith('not'):
return ~expression
return expression
if isinstance(value, basestring):
targets = Target.search([('rec_name', 'ilike', value)],
order=[])
ids = [t.id for t in targets]
elif not isinstance(value, (list, tuple)):
ids = [value]
else:
ids = value
if not ids:
expression = Literal(False)
if operator.startswith('not'):
return ~expression
return expression
elif self.left and self.right:
return self.convert_domain_mptt((name, operator, ids),
tables)
else:
return self.convert_domain_tree((name, operator, ids),
tables)
# Used for Many2Many where clause
if operator.endswith('where'):
query = Target.search(value, order=[], query=True)
expression = column.in_(query)
if operator.startswith('not'):
return ~expression
return expression
if not isinstance(value, basestring):
return super(Many2One,
self).convert_domain(domain, tables, Model)
else:
target_name = 'rec_name'
else:
_, target_name = name.split('.', 1)
target_domain = [(target_name, ) + tuple(domain[1:])]
if 'active' in Target._fields:
target_domain.append(('active', 'in', [True, False]))
if self.target_search == 'subquery':
query = Target.search(target_domain, order=[], query=True)
return column.in_(query)
else:
target_tables = self._get_target_tables(tables)
target_table, _ = target_tables[None]
rule_domain = Rule.domain_get(Target.__name__, mode='read')
if rule_domain:
target_domain = [target_domain, rule_domain]
_, expression = Target.search_domain(target_domain,
tables=target_tables)
return expression
def convert_domain(self, domain, tables, Model):
pool = Pool()
Rule = pool.get('ir.rule')
Property = pool.get('ir.property')
IrModel = pool.get('ir.model')
Field = pool.get('ir.model.field')
cursor = Transaction().cursor
name, operator, value = domain
sql_type = self._field.sql_type().base
property_cond = Rule.query_get('ir.property')
property_ = Property.__table__()
model_field = Field.__table__()
model = IrModel.__table__()
# Fetch res ids that comply with the domain
join = property_.join(model_field,
condition=model_field.id == property_.field)
join = join.join(model, condition=model.id == model_field.model)
cond = ((model.model == Model.__name__) & (model_field.name == name))
if property_cond:
cond &= property_.id.in_(property_cond)
cursor.execute(
*join.select(Cast(
Substring(property_.res,
Position(',', property_.res) + Literal(1)),
Model.id.sql_type().base),
property_.id,
where=cond
& self.get_condition(sql_type, domain, property_)))
props = cursor.fetchall()
default = None
for prop in props:
if not prop[0]:
default = prop[1]
break
if (not default or
((value is False or value is None) and operator in ['=', '!='])
or (operator in ['not like', 'not ilike', 'not in', '!='])):
dom_operator = 'in' # default operator
if (((value is False or value is None) and operator == '=') or
((value is not False and value is not None)
and operator in ['not like', 'not ilike', 'not in', '!='])):
dom_operator = 'not in'
return [('id', dom_operator, [x[0] for x in props])]
# Fetch the res ids that doesn't use the default value
cursor.execute(*property_.select(Cast(
Substring(property_.res,
Position(',', property_.res) + Literal(1)),
Model.id.sql_type().base),
where=property_cond
& (property_.res != Null)))
fetchall = cursor.fetchall()
if not fetchall:
return [('id', 'in', [x[0] for x in props])]
else:
other_ids = [x[0] for x in cursor.fetchall()]
res_ids = Model.search([
'OR', ('id', 'in', [x[0] for x in props]),
('id', 'not in', other_ids)
])
return [('id', 'in', res_ids)]
def pull(cls, database, connection, name=None):
cursor = connection.cursor()
queue = cls.__table__()
queue_c = cls.__table__()
queue_s = cls.__table__()
candidates = With(
'id',
'scheduled_at',
'expected_at',
query=queue_c.select(
queue_c.id,
queue_c.scheduled_at,
queue_c.expected_at,
where=((queue_c.name == name) if name else Literal(True))
& (queue_c.dequeued_at == Null),
order_by=[
queue_c.scheduled_at.nulls_first,
queue_c.expected_at.nulls_first
]))
selected = queue_s.select(
queue_s.id,
where=((queue_s.name == name) if name else Literal(True))
& (queue_s.dequeued_at == Null)
& ((queue_s.scheduled_at <= CurrentTimestamp())
| (queue_s.scheduled_at == Null)),
order_by=[
queue_s.scheduled_at.nulls_first,
queue_s.expected_at.nulls_first
],
limit=1)
if database.has_select_for():
For = database.get_select_for_skip_locked()
selected.for_ = For('UPDATE')
next_timeout = With(
'seconds',
query=candidates.select(
Min(
Extract('EPOCH',
candidates.scheduled_at - CurrentTimestamp())),
where=candidates.scheduled_at >= CurrentTimestamp()))
task_id, seconds = None, None
if database.has_returning():
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
where=queue.id.in_(selected),
with_=[candidates, next_timeout],
returning=[
queue.id,
next_timeout.select(next_timeout.seconds)
])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, seconds = row
else:
query = queue.select(queue.id,
where=queue.id.in_(selected),
with_=[candidates])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, = row
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
where=queue.id == task_id)
cursor.execute(*query)
query = next_timeout.select(next_timeout.seconds)
cursor.execute(*query)
row = cursor.fetchone()
if row:
seconds, = row
if not task_id and database.has_channel():
cursor.execute('LISTEN "%s"', (cls.__name__, ))
return task_id, seconds
def table_query():
pool = Pool()
context = Transaction().context
Gp = pool.get('disc.gp')
gp = Gp.__table__()
Iglesia = pool.get('disc.iglesia')
iglesia = Iglesia.__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']
if context.get('fecha_fin'):
where &= reporte.fecha_fin <= context['fecha_fin']
if context.get('distrito'):
where &= reporte.distrito == context['distrito']
subquery = (
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'),
reporte_linea.gp,
reporte.iglesia,
Max(reporte.distrito).as_('distrito'),
#reporte.mes,
(Sum(reporte_linea.cantidad)).as_('total'),
where=where,
group_by=[reporte_linea.gp, reporte.iglesia]))
if context.get('distrito'):
distrito = context['distrito']
where = subquery.distrito == distrito
iglesias = Iglesia.search(['distrito', '=', distrito])
if iglesias:
for iglesia in iglesias:
where |= gp.iglesia == iglesia.id
where &= gp.active == True
else:
where = Literal(True)
#where &= subquery.distrito == None
#if iglesias:
# where &= gp.iglesia in (x.id for x in iglesias)
#print "WHERE: " + str(where)
query = (subquery.join(gp, 'FULL',
condition=gp.id == subquery.gp).select(
Max(gp.id * 1000).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(Coalesce(subquery.total,
0))).as_('total'),
gp.iglesia,
subquery.distrito,
(gp.id).as_('gp'),
(gp.active),
where=where,
group_by=(gp.id, subquery.distrito,
gp.active),
order_by=Sum(Coalesce(subquery.total,
0)).desc,
))
return query
def test_create_with_sql_value(self):
"Test cannot create with SQL value"
Char = self.Char()
with self.assertRaises(ValueError):
Char.create([{'char': Literal('Foo')}])
def close(cls, periods):
pool = Pool()
Product = pool.get('product.product')
Location = pool.get('stock.location')
Move = pool.get('stock.move')
Date = pool.get('ir.date')
transaction = Transaction()
connection = transaction.connection
database = transaction.database
# XXX: A move in the period could be inserted before the lock
# from a different transaction. It will not be taken in the pbl
# computation but it is quite rare because only past periods are
# closed.
database.lock(connection, Move._table)
if database.has_select_for():
move = Move.__table__()
query = move.select(Literal(1), for_=For('UPDATE', nowait=True))
with connection.cursor() as cursor:
cursor.execute(*query)
locations = Location.search([
('type', 'not in', ['warehouse', 'view']),
],
order=[])
today = Date.today()
recent_date = max(period.date for period in periods)
if recent_date >= today:
cls.raise_user_error('close_period_future_today')
if Move.search([('state', '=', 'assigned'),
[
'OR',
[
('effective_date', '=', None),
('planned_date', '<=', recent_date),
],
('effective_date', '<=', recent_date),
]]):
cls.raise_user_error('close_period_assigned_move')
for grouping in cls.groupings():
Cache = cls.get_cache(grouping)
to_create = []
for period in periods:
with Transaction().set_context(
stock_date_end=period.date,
stock_date_start=None,
stock_assign=False,
forecast=False,
stock_destinations=None,
):
pbl = Product.products_by_location(
[l.id for l in locations], grouping=grouping)
for key, quantity in pbl.items():
values = {
'location': key[0],
'period': period.id,
'internal_quantity': quantity,
}
for i, field in enumerate(grouping, 1):
values[field] = key[i]
to_create.append(values)
if to_create:
Cache.create(to_create)
def migrate_property(model_name,
field_names,
ValueModel,
value_names,
parent=None,
fields=None):
"Migrate property from model_name.field_name to ValueModel.value_name"
pool = Pool()
Field = pool.get('ir.model.field')
Model = pool.get('ir.model')
if not backend.TableHandler.table_exist('ir_property'):
return
cursor = Transaction().connection.cursor()
field = Field.__table__()
model = Model.__table__()
table = ValueModel.__table__()
if fields is None:
fields = []
if isinstance(field_names, str):
field_names = [field_names]
if isinstance(value_names, str):
value_names = [value_names]
def split_value(value):
return value.split(',')[1]
cast_funcs = {
'numeric': lambda v: Decimal(split_value(v)) if v else None,
'integer': lambda v: int(split_value(v)) if v else None,
'float': lambda v: float(split_value(v)) if v else None,
'char': lambda v: split_value(v) if v else None,
'selection': lambda v: split_value(v) if v else None,
'many2one': lambda v: int(split_value(v)) if v else None,
'reference': lambda v: v,
}
casts = []
queries = []
for field_name, value_name in zip(field_names, value_names):
value_field = getattr(ValueModel, value_name)
casts.append(cast_funcs[value_field._type])
property_ = Table('ir_property')
columns = [
Literal(None).as_(f)
if f != value_name else property_.value.as_(value_name)
for f in value_names
]
if parent:
columns.append(property_.res.as_(parent))
where = property_.res.like(model_name + ',%')
else:
where = property_.res == Null
columns.extend([Column(property_, f).as_(f) for f in fields])
query = property_.join(field,
condition=property_.field == field.id).join(
model,
condition=field.model == model.id).select(
*columns,
where=where
& (field.name == field_name)
& (model.model == model_name))
queries.append(query)
union = Union(*queries)
columns = [Max(Column(union, f)).as_(f) for f in value_names]
if parent:
columns.append(Column(union, parent).as_(parent))
pcolumns = [Column(union, parent)]
else:
pcolumns = []
vcolumns = [Column(union, f).as_(f) for f in fields]
cursor.execute(
*union.select(*(columns + vcolumns), group_by=pcolumns + vcolumns))
columns = [Column(table, f) for f in value_names]
if parent:
pcolumns = [Column(table, parent)]
else:
pcolumns = []
vcolumns = [Column(table, f) for f in fields]
values = []
l = len(value_names)
for row in cursor.fetchall():
value = [c(v) for v, c in zip(row, casts)]
if parent:
value.append(int(row[l].split(',')[1]) if row[l] else None)
i = 1
else:
i = 0
value.extend(row[l + i:])
values.append(value)
if (values and not (
# No property defined
len(values) == 1 and all(x is None
for x in values[0][:len(columns)]))):
# Delete previous migrated values
cursor.execute(*table.delete())
cursor.execute(
*table.insert(columns + pcolumns + vcolumns, values=values))
def compute_quantities_query(cls,
location_ids,
with_childs=False,
grouping=('product', ),
grouping_filter=None):
"""
Prepare a query object to compute for each location and product the
stock quantity in the default uom of the product.
The context with keys:
stock_date_end: if set the date of the stock computation.
stock_date_start: if set return the delta of the stock between the
two dates, (ignored if stock_date_end is missing).
stock_assign: if set compute also the assigned outgoing moves as
done.
forecast: if set compute the forecast quantity.
stock_destinations: A list of location ids. If set, restrict the
computation to moves from and to those locations.
stock_skip_warehouse: if set, quantities on a warehouse are no more
quantities of all child locations but quantities of the storage
zone.
If with_childs, it computes also for child locations.
grouping is a tuple of Move field names and defines how stock moves are
grouped.
grouping_filter is a tuple of values, for the Move's field at the same
position in grouping tuple, used to filter which moves are used to
compute quantities. It must be None or have the same number of
elements than grouping. If no grouping_filter is provided it
returns quantities for all products.
The query return the location as first column, after the fields in
grouping, and the last column is the quantity.
"""
pool = Pool()
Rule = pool.get('ir.rule')
Location = pool.get('stock.location')
Date = pool.get('ir.date')
Period = pool.get('stock.period')
Move = pool.get('stock.move')
move = Move.__table__()
today = Date.today()
if not location_ids:
return None
context = Transaction().context.copy()
for field in grouping:
if field not in Move._fields:
raise ValueError('"%s" has no field "%s"' % (Move, field))
assert grouping_filter is None or len(grouping_filter) == len(grouping)
move_rule_query = Rule.query_get('stock.move')
PeriodCache = Period.get_cache(grouping)
period = None
if PeriodCache:
period_cache = PeriodCache.__table__()
if not context.get('stock_date_end'):
context['stock_date_end'] = datetime.date.max
# date end in the past or today: filter on state done
if (context['stock_date_end'] < today
or (context['stock_date_end'] == today
and not context.get('forecast'))):
state_date_clause = lambda stock_assign: (
move.state.in_(
['done', 'assigned' if stock_assign else 'done'])
& (((move.effective_date == Null)
& (move.planned_date <= context['stock_date_end']))
| (move.effective_date <= context['stock_date_end'])))
state_date_clause_in = state_date_clause(False)
state_date_clause_out = state_date_clause(
context.get('stock_assign'))
# future date end: filter move on state done and date
# before today, or on all state and date between today and
# date_end.
else:
state_date_clause = lambda stock_assign: (
(move.state.in_(
['done', 'assigned' if stock_assign else 'done'])
& (((move.effective_date == Null)
& (move.planned_date <= today))
| (move.effective_date <= today)))
| (move.state.in_(['done', 'assigned', 'draft'])
&
(((move.effective_date == Null)
&
(Coalesce(move.planned_date, datetime.date.max) <=
context['stock_date_end'])
&
(Coalesce(move.planned_date, datetime.date.max) >= today))
| ((move.effective_date <= context['stock_date_end'])
& (move.effective_date >= today)))))
state_date_clause_in = state_date_clause(False)
state_date_clause_out = state_date_clause(
context.get('stock_assign'))
if context.get('stock_date_start'):
if context['stock_date_start'] > today:
state_date_clause = lambda: (
move.state.in_(['done', 'assigned', 'draft'])
& (((move.effective_date == Null)
& ((move.planned_date >= context['stock_date_start'])
| (move.planned_date == Null)))
| (move.effective_date >= context['stock_date_start'])))
state_date_clause_in &= state_date_clause()
state_date_clause_out &= state_date_clause()
else:
state_date_clause = lambda stock_assign: (
(move.state.in_(['done', 'assigned', 'draft'])
& (((move.effective_date == Null)
& ((move.planned_date >= today)
| (move.planned_date == Null)))
| (move.effective_date >= today)))
| (move.state.in_(
['done', 'assigned' if stock_assign else 'done'])
& (((move.effective_date == Null)
&
(((move.planned_date >= context['stock_date_start'])
& (move.planned_date < today))
| (move.planned_date == Null)))
|
((move.effective_date >= context['stock_date_start'])
& (move.effective_date < today)))))
state_date_clause_in &= state_date_clause(False)
state_date_clause_out &= state_date_clause(
context.get('stock_assign'))
elif PeriodCache:
periods = Period.search([
('date', '<', context['stock_date_end']),
('state', '=', 'closed'),
],
order=[('date', 'DESC')],
limit=1)
if periods:
period, = periods
state_date_clause = lambda: (Coalesce(
move.effective_date, move.planned_date, datetime.date.max)
> period.date)
state_date_clause_in &= state_date_clause()
state_date_clause_out &= state_date_clause()
if with_childs:
location_query = Location.search([
('parent', 'child_of', location_ids),
],
query=True,
order=[])
else:
location_query = location_ids[:]
if PeriodCache:
from_period = period_cache
where = where_period = Literal(True)
if grouping_filter and any(grouping_filter):
for fieldname, grouping_ids in zip(grouping, grouping_filter):
if not grouping_ids:
continue
column = Column(move, fieldname)
if PeriodCache:
cache_column = Column(period_cache, fieldname)
if isinstance(grouping_ids[0], (int, long, float, Decimal)):
where &= reduce_ids(column, grouping_ids)
if PeriodCache:
where_period &= reduce_ids(cache_column, grouping_ids)
else:
where &= column.in_(grouping_ids)
if PeriodCache:
where_period &= cache_column.in_(grouping_ids)
if context.get('stock_destinations'):
destinations = context['stock_destinations']
dest_clause_from = move.from_location.in_(destinations)
dest_clause_to = move.to_location.in_(destinations)
if PeriodCache:
dest_clause_period = period_cache.location.in_(destinations)
else:
dest_clause_from = dest_clause_to = dest_clause_period = \
Literal(True)
# The main select clause is a union between three similar subqueries.
# One that sums incoming moves towards locations, one that sums
# outgoing moves and one for the period cache. UNION ALL is used
# because we already know that there will be no duplicates.
move_keys_alias = [Column(move, key).as_(key) for key in grouping]
move_keys = [Column(move, key) for key in grouping]
query = move.select(move.to_location.as_('location'),
Sum(move.internal_quantity).as_('quantity'),
*move_keys_alias,
where=state_date_clause_in
& where
& move.to_location.in_(location_query)
& (move.id.in_(move_rule_query)
if move_rule_query else Literal(True))
& dest_clause_from,
group_by=[move.to_location] + move_keys)
query = Union(
query,
move.select(move.from_location.as_('location'),
(-Sum(move.internal_quantity)).as_('quantity'),
*move_keys_alias,
where=state_date_clause_out
& where
& move.from_location.in_(location_query)
& (move.id.in_(move_rule_query)
if move_rule_query else Literal(True))
& dest_clause_to,
group_by=[move.from_location] + move_keys),
all_=True)
if PeriodCache:
period_keys = [
Column(period_cache, key).as_(key) for key in grouping
]
query = Union(query,
from_period.select(
period_cache.location.as_('location'),
period_cache.internal_quantity.as_('quantity'),
*period_keys,
where=(period_cache.period
== (period.id if period else None))
& where_period
& period_cache.location.in_(location_query)
& dest_clause_period),
all_=True)
query_keys = [Column(query, key).as_(key) for key in grouping]
columns = ([query.location.as_('location')] + query_keys +
[Sum(query.quantity).as_('quantity')])
query = query.select(*columns, group_by=[query.location] + query_keys)
return query
def test_select_order(self):
c = self.table.c
query = self.table.select(c, order_by=Literal(1))
self.assertEqual(str(query),
'SELECT "a"."c" FROM "t" AS "a" ORDER BY %s')
self.assertEqual(query.params, (1,))
def table_query(cls):
pool = Pool()
Identifier = pool.get('party.identifier')
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
ModelData = pool.get('ir.model.data')
Move = pool.get('account.move')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
company_identifier = Identifier.__table__()
party_identifier = Identifier.__table__()
invoice = Invoice.__table__()
invoice_tax = InvoiceTax.__table__()
move = Move.__table__()
period = Period.__table__()
tax = Tax.__table__()
groups = []
for module, fs_id in cls.tax_groups():
try:
groups.append(ModelData.get_id(module, fs_id))
except KeyError:
# table_query can be called before the XML is loaded
continue
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear')))
where &= invoice.type == 'out'
where &= ((company_identifier.code.ilike('BE%')
& (company_identifier.type == 'eu_vat'))
| (company_identifier.type == 'be_vat'))
where &= ((party_identifier.code.ilike('BE%')
& (party_identifier.type == 'eu_vat'))
| (party_identifier.type == 'be_vat'))
where &= tax.group.in_(groups)
return (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)
.join(company_identifier,
condition=invoice.tax_identifier == company_identifier.id)
.join(party_identifier,
condition=invoice.party_tax_identifier == party_identifier.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_tax_identifier.as_('party_tax_identifier'),
Sum(invoice_tax.base).as_('turnover'),
Sum(invoice_tax.amount).as_('vat'),
invoice.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party_tax_identifier,
invoice.currency,
]))
def pull(cls, database, connection, name=None):
cursor = connection.cursor()
queue = cls.__table__()
candidates = With(
'id',
'scheduled_at',
'expected_at',
query=queue.select(
queue.id,
queue.scheduled_at,
queue.expected_at,
where=((queue.name == name) if name else Literal(True))
& (queue.dequeued_at == Null),
order_by=[
queue.scheduled_at.nulls_first,
queue.expected_at.nulls_first
],
limit=1000))
selected = With(
'id',
query=candidates.select(
candidates.id,
where=((candidates.scheduled_at <= CurrentTimestamp())
| (candidates.scheduled_at == Null))
& database.lock_id(candidates.id),
order_by=[
candidates.scheduled_at.nulls_first,
candidates.expected_at.nulls_first
],
limit=1))
next_timeout = With(
'seconds',
query=candidates.select(
Min(
Extract('second',
candidates.scheduled_at - CurrentTimestamp())),
where=candidates.scheduled_at >= CurrentTimestamp()))
task_id, seconds, queue_name, priority = None, None, None, None
if database.has_returning():
query = queue.update(
[queue.dequeued_at], [CurrentTimestamp()],
where=queue.id == selected.select(selected.id),
with_=[candidates, selected, next_timeout],
returning=[
queue.id,
next_timeout.select(next_timeout.seconds), queue.name,
queue.priority
])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, seconds, queue_name, priority = row
else:
query = queue.select(queue.id,
queue.name,
priority,
where=queue.id == selected.select(
selected.id),
with_=[candidates, selected])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, queue_name, priority = row
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
where=queue.id == task_id)
cursor.execute(*query)
query = next_timeout.select(next_timeout.seconds)
cursor.execute(*query)
row = cursor.fetchone()
if row:
seconds, = row
if not task_id and database.has_channel():
cursor.execute('LISTEN "%s"', (cls.__name__, ))
return task_id, seconds, queue_name, priority
def test_literal(self):
literal = Literal(1)
self.assertEqual(str(literal), '%s')
self.assertEqual(literal.params, (1,))
def get(cls, model_name, mode='read'):
"Return dictionary of non-global and global rules"
pool = Pool()
RuleGroup = pool.get('ir.rule.group')
Model = pool.get('ir.model')
RuleGroup_Group = pool.get('ir.rule.group-res.group')
User_Group = pool.get('res.user-res.group')
rule_table = cls.__table__()
rule_group = RuleGroup.__table__()
rule_group_group = RuleGroup_Group.__table__()
user_group = User_Group.__table__()
model = Model.__table__()
transaction = Transaction()
assert mode in cls.modes
cursor = transaction.connection.cursor()
user_id = transaction.user
# root user above constraint
if user_id == 0:
user_id = transaction.context.get('user')
if not user_id:
return {}, {}
cursor.execute(*rule_table.join(
rule_group, condition=rule_group.id == rule_table.rule_group
).join(model, condition=rule_group.model == model.id).select(
rule_table.id,
where=(model.model == model_name)
& (getattr(rule_group, 'perm_%s' % mode) == Literal(True))
& (rule_group.id.in_(
rule_group_group.join(
user_group,
condition=(rule_group_group.group == user_group.group
)).select(rule_group_group.rule_group,
where=user_group.user == user_id))
| (rule_group.default_p == Literal(True))
| (rule_group.global_p == Literal(True)))))
ids = [x for x, in cursor]
# Test if there is no rule_group that have no rule
cursor.execute(*rule_group.join(
model, condition=rule_group.model == model.id).select(
rule_group.id,
where=(model.model == model_name)
& ~rule_group.id.in_(rule_table.select(rule_table.rule_group))
& rule_group.id.in_(
rule_group_group.join(user_group,
condition=rule_group_group.group ==
user_group.group).
select(rule_group_group.rule_group,
where=user_group.user == user_id))))
no_rules = cursor.fetchone()
clause = defaultdict(lambda: ['OR'])
clause_global = defaultdict(lambda: ['OR'])
decoder = PYSONDecoder(cls._get_context())
# Use root user without context to prevent recursion
with transaction.set_user(0), transaction.set_context(user=0):
for rule in cls.browse(ids):
assert rule.domain, ('Rule domain empty,'
'check if migration was done')
dom = decoder.decode(rule.domain)
if rule.rule_group.global_p:
clause_global[rule.rule_group].append(dom)
else:
clause[rule.rule_group].append(dom)
if no_rules:
group_id = no_rules[0]
clause[RuleGroup(group_id)] = []
return clause, clause_global
def transition_erase(self):
pool = Pool()
Party = pool.get('party.party')
cursor = Transaction().connection.cursor()
def convert_from(table, tables):
right, condition = tables[None]
if table:
table = table.join(right, condition=condition)
else:
table = right
for k, sub_tables in tables.items():
if k is None:
continue
table = convert_from(table, sub_tables)
return table
resources = self.get_resources()
parties = replacing = [self.ask.party]
with Transaction().set_context(active_test=False):
while replacing:
replacing = Party.search([
('replaced_by', 'in', list(map(int, replacing))),
])
parties += replacing
for party in parties:
self.check_erase(party)
to_erase = self.to_erase(party.id)
for Model, domain, resource, columns, values in to_erase:
assert issubclass(Model, ModelSQL)
assert len(columns) == len(values)
if 'active' in Model._fields:
records = Model.search(domain)
Model.write(records, {'active': False})
tables, where = Model.search_domain(domain, active_test=False)
from_ = convert_from(None, tables)
table, _ = tables[None]
query = from_.select(table.id, where=where)
if columns:
model_tables = [Model.__table__()]
if Model._history:
model_tables.append(Model.__table_history__())
for table in model_tables:
sql_columns, sql_values = [], []
for column, value in zip(columns, values):
column = Column(table, column)
sql_columns.append(column)
sql_values.append(
value(column) if callable(value) else value)
cursor.execute(*table.update(
sql_columns, sql_values,
where=table.id.in_(query)))
if resource:
for Resource in resources:
model_tables = [Resource.__table__()]
if Resource._history:
model_tables.append(Resource.__table_history__())
for table in model_tables:
cursor.execute(*table.delete(
where=table.resource.like(
Model.__name__ + ',%')
& Model.id.sql_cast(
Substring(table.resource,
Position(',', table.resource)
+ Literal(1))).in_(query)))
return 'end'
def search(cls, domain, offset=0, limit=None, order=None, count=False,
query=False):
cursor = Transaction().connection.cursor()
# Clean transaction cache
for cache in Transaction().cache.values():
if cls.__name__ in cache:
del cache[cls.__name__]
if not cls.get_table():
return super().search(domain, offset, limit, order, count, query)
# Get domain clauses
sql_table = cls.get_sql_table()
tables, expression = cls.search_domain(domain,
tables={None: (sql_table, None)})
# Get order by
order_by = []
order_types = {
'DESC': Desc,
'ASC': Asc,
}
null_ordering_types = {
'NULLS FIRST': NullsFirst,
'NULLS LAST': NullsLast,
None: lambda _: _
}
if order is None or order is False:
order = cls._order
for oexpr, otype in order:
fname, _, extra_expr = oexpr.partition('.')
field = cls._fields[fname]
if not otype:
otype, null_ordering = 'ASC', None
else:
otype = otype.upper()
try:
otype, null_ordering = otype.split(' ', 1)
except ValueError:
null_ordering = None
Order = order_types[otype]
NullOrdering = null_ordering_types[null_ordering]
forder = field.convert_order(oexpr, tables, cls)
order_by.extend((NullOrdering(Order(o)) for o in forder))
main_table, _ = tables[None]
table = convert_from(None, tables)
if count:
cursor.execute(*table.select(Count(Literal('*')),
where=expression, limit=limit, offset=offset))
return cursor.fetchone()[0]
columns = [main_table.id]
select = table.select(*columns,
where=expression, order_by=order_by, limit=limit, offset=offset)
if query:
return select
cursor.execute(*select)
res = [x[0] for x in cursor.fetchall()]
return cls.browse(res)
def run(options):
main_lang = config.get('database', 'language')
init = {}
for db_name in options.database_names:
init[db_name] = False
database = backend.Database(db_name)
database.connect()
if options.update or options.check_update:
if not database.test():
logger.info("init db")
database.init()
init[db_name] = True
elif not database.test():
raise Exception('"%s" is not a Tryton database.' % db_name)
for db_name in options.database_names:
if options.update or options.check_update:
with Transaction().start(db_name, 0) as transaction,\
transaction.connection.cursor() as cursor:
database = backend.Database(db_name)
database.connect()
if not database.test():
raise Exception('"%s" is not a Tryton database.' % db_name)
lang = Table('ir_lang')
cursor.execute(*lang.select(
lang.code, where=lang.translatable == Literal(True)))
lang = set([x[0] for x in cursor.fetchall()])
lang.add(main_lang)
else:
lang = set()
lang |= set(options.languages)
# XUNG
# Create upgrade version control table if it doesn't exist
_init_upgrade_version_control_table(db_name)
pool = _init_pool(db_name, options, lang)
if options.update_modules_list:
with Transaction().start(db_name, 0) as transaction:
Module = pool.get('ir.module')
Module.update_list()
if lang:
with Transaction().start(db_name, 0) as transaction:
pool = Pool()
Lang = pool.get('ir.lang')
languages = Lang.search([
('code', 'in', lang),
])
Lang.write(languages, {
'translatable': True,
})
for db_name in options.database_names:
with Transaction().start(db_name, 0) as transaction:
pool = Pool()
User = pool.get('res.user')
Configuration = pool.get('ir.configuration')
configuration = Configuration(1)
with transaction.set_context(active_test=False):
admin, = User.search([('login', '=', 'admin')])
if options.email is not None:
admin.email = options.email
elif init[db_name]:
admin.email = input('"admin" email for "%s": ' % db_name)
if init[db_name] or options.password:
configuration.language = main_lang
# try to read password from environment variable
# TRYTONPASSFILE, empty TRYTONPASSFILE ignored
passpath = os.getenv('TRYTONPASSFILE')
password = ''
if passpath:
try:
with open(passpath) as passfile:
password, = passfile.read().splitlines()
except Exception as err:
sys.stderr.write('Can not read password '
'from "%s": "%s"\n' % (passpath, err))
if not password and not options.reset_password:
while True:
password = getpass('"admin" password for "%s": ' %
db_name)
password2 = getpass('"admin" password confirmation: ')
if password != password2:
sys.stderr.write(
'"admin" password confirmation '
'doesn\'t match "admin" password.\n')
continue
if not password:
sys.stderr.write('"admin" password is required.\n')
continue
break
if not options.reset_password:
admin.password = password
admin.save()
if options.reset_password:
User.reset_password([admin])
if options.test_email:
send_test_email(options.test_email)
if options.hostname is not None:
configuration.hostname = options.hostname or None
configuration.save()
def _get_translation_join(self, Model, name,
translation, model, table, from_, language):
if Model.__name__ == 'ir.model.field':
pool = Pool()
IrModel = pool.get('ir.model')
ModelData = pool.get('ir.model.data')
ModelField = pool.get('ir.model.field')
Translation = pool.get('ir.translation')
model = IrModel.__table__()
model_data = ModelData.__table__()
model_field = ModelField.__table__()
msg_trans = Translation.__table__()
if name == 'field_description':
type_ = 'field'
else:
type_ = 'help'
translation = translation.select(
translation.id.as_('id'),
translation.res_id.as_('res_id'),
translation.value.as_('value'),
translation.name.as_('name'),
translation.lang.as_('lang'),
translation.type.as_('type'),
translation.fuzzy.as_('fuzzy'),
)
translation |= (msg_trans
.join(model_data,
condition=(msg_trans.res_id == model_data.db_id)
& (model_data.model == 'ir.message')
& (msg_trans.name == 'ir.message,text'))
.join(model_field,
condition=Concat(
Concat(model_data.module, '.'),
model_data.fs_id) == getattr(model_field, name))
.join(model,
condition=model_field.model == model.id)
.select(
msg_trans.id.as_('id'),
Literal(-1).as_('res_id'),
msg_trans.value.as_('value'),
Concat(
Concat(model.model, ','),
model_field.name).as_('name'),
msg_trans.lang.as_('lang'),
Literal(type_).as_('type'),
msg_trans.fuzzy.as_('fuzzy'),
))
if backend.name == 'postgresql' and _sql_version >= (1, 1, 0):
query = translation.select(
translation.res_id.as_('res_id'),
translation.value.as_('value'),
translation.name.as_('name'),
distinct=True,
distinct_on=[translation.res_id, translation.name],
order_by=[
translation.res_id,
translation.name,
translation.id.desc])
else:
query = translation.select(
translation.res_id.as_('res_id'),
Min(translation.value).as_('value'),
translation.name.as_('name'),
group_by=[translation.res_id, translation.name])
if Model.__name__ == 'ir.model':
name_ = Concat(Concat(table.model, ','), name)
type_ = 'model'
res_id = -1
elif Model.__name__ == 'ir.model.field':
from_ = from_.join(model, 'LEFT',
condition=model.id == table.model)
name_ = Concat(Concat(model.model, ','), table.name)
if name == 'field_description':
type_ = 'field'
else:
type_ = 'help'
res_id = -1
else:
name_ = '%s,%s' % (Model.__name__, name)
type_ = 'model'
res_id = table.id
query.where = (
(translation.lang == language)
& (translation.type == type_)
& (translation.fuzzy == Literal(False))
)
return query, from_.join(query, 'LEFT',
condition=(query.res_id == res_id) & (query.name == name_))
def test_filter_case_count_star(self):
count = Count(Literal('*'), filter_=self.table.a > 0)
self.assertEqual(str(count), 'COUNT(CASE WHEN ("a" > %s) THEN %s END)')
self.assertEqual(count.params, (0, 1))
def table_query(cls):
pool = Pool()
Company = pool.get('company.company')
Invoice = pool.get('account.invoice')
InvoiceTax = pool.get('account.invoice.tax')
Move = pool.get('account.move')
Line = pool.get('account.move.line')
TaxLine = pool.get('account.tax.line')
Tax = pool.get('account.tax')
TaxCode = pool.get('account.tax.code')
TaxCodeLine = pool.get('account.tax.code.line')
Date = pool.get('ir.date')
context = Transaction().context
company = Company.__table__()
invoice = Invoice.__table__()
cancel_invoice = Invoice.__table__()
move = Move.__table__()
cancel_move = Move.__table__()
line = Line.__table__()
tax_line = TaxLine.__table__()
tax = Tax.__table__()
tax_code = TaxCode.__table__()
tax_code_line = TaxCodeLine.__table__()
exclude_invoice_tax = InvoiceTax.__table__()
amount = tax_line.amount
month = Extract('MONTH', invoice.invoice_date)
excluded_taxes = (tax_code_line
.join(tax_code,
condition=(tax_code.id == tax_code_line.code)
).select(
tax_code_line.tax, distinct=True,
where=tax_code.aeat_report.in_(cls.excluded_tax_codes())))
where = ((invoice.company == context.get('company'))
& (tax.es_vat_list_code != Null)
& (Extract('year', invoice.invoice_date)
== context.get('date', Date.today()).year)
# Exclude base amount for es_reported_with taxes because it is
# already included in the base of main tax
& ((tax.es_reported_with == Null) | (tax_line.type == 'tax'))
& ~Exists(cancel_invoice
.join(cancel_move,
condition=cancel_invoice.cancel_move == cancel_move.id)
.select(cancel_invoice.id, distinct=True,
where=((cancel_invoice.id == invoice.id)
& (~cancel_move.origin.like('account.invoice,%')))))
# Use exists to exclude the full invoice when it has multiple taxes
& ~Exists(exclude_invoice_tax.select(
exclude_invoice_tax.invoice,
where=((exclude_invoice_tax.invoice == invoice.id)
& (exclude_invoice_tax.tax.in_(excluded_taxes))))))
return (tax_line
.join(tax, condition=tax_line.tax == tax.id)
.join(line, condition=tax_line.move_line == line.id)
.join(move, condition=line.move == move.id)
.join(invoice, condition=invoice.move == move.id)
.join(company, condition=company.id == invoice.company)
.select(
Min(tax_line.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'),
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'),
company.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.type,
invoice.party,
invoice.party_tax_identifier,
company.currency,
tax.es_vat_list_code,
]))
def convert_domain(self, domain, tables, Model):
from ..modelsql import convert_from
pool = Pool()
Rule = pool.get('ir.rule')
Target = self.get_target()
transaction = Transaction()
table, _ = tables[None]
name, operator, value = domain[:3]
assert operator not in {'where', 'not where'} or '.' not in name
if Target._history and transaction.context.get('_datetime'):
target = Target.__table_history__()
history_where = (Coalesce(target.write_date, target.create_date) <=
transaction.context['_datetime'])
else:
target = Target.__table__()
history_where = None
origin_field = Target._fields[self.field]
origin = getattr(Target, self.field).sql_column(target)
origin_where = None
if origin_field._type == 'reference':
origin_where = origin.like(Model.__name__ + ',%')
origin = Cast(
Substring(origin,
Position(',', origin) + Literal(1)),
Target.id.sql_type().base)
if '.' not in name:
if value is None:
where = origin != value
if history_where:
where &= history_where
if origin_where:
where &= origin_where
if self.filter:
query = Target.search(self.filter, order=[], query=True)
where &= origin.in_(query)
query = target.select(origin, where=where)
expression = ~table.id.in_(query)
if operator == '!=':
return ~expression
return expression
else:
if isinstance(value, str):
target_name = 'rec_name'
else:
target_name = 'id'
else:
_, target_name = name.split('.', 1)
if operator not in {'where', 'not where'}:
target_domain = [(target_name, ) + tuple(domain[1:])]
else:
target_domain = value
if origin_field._type == 'reference':
target_domain.append((self.field, 'like', Model.__name__ + ',%'))
rule_domain = Rule.domain_get(Target.__name__, mode='read')
if rule_domain:
target_domain = [target_domain, rule_domain]
if self.filter:
target_domain = [target_domain, self.filter]
target_tables = {
None: (target, None),
}
tables, expression = Target.search_domain(target_domain,
tables=target_tables)
query_table = convert_from(None, target_tables)
query = query_table.select(origin, where=expression)
expression = table.id.in_(query)
if operator == 'not where':
expression = ~expression
return expression
def table_query(cls):
pool = Pool()
Company = pool.get('company.company')
Invoice = pool.get('account.invoice')
Move = pool.get('account.move')
Line = pool.get('account.move.line')
TaxLine = pool.get('account.tax.line')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
company = Company.__table__()
invoice = Invoice.__table__()
cancel_invoice = Invoice.__table__()
move = Move.__table__()
cancel_move = Move.__table__()
line = Line.__table__()
tax_line = TaxLine.__table__()
period = Period.__table__()
tax = Tax.__table__()
sales = super().table_query()
where = invoice.company == context.get('company')
if context.get('start_date'):
where &= (move.date >= context.get('start_date'))
if context.get('end_date'):
where &= (move.date <= context.get('end_date'))
where &= ((tax.es_ec_purchases_list_code != Null)
& (tax.es_ec_purchases_list_code != ''))
where &= tax_line.type == 'base'
where &= invoice.type == 'in'
where &= ~Exists(cancel_invoice
.join(cancel_move,
condition=cancel_invoice.cancel_move == cancel_move.id)
.select(cancel_invoice.id, distinct=True,
where=((cancel_invoice.id == invoice.id)
& (~cancel_move.origin.like('account.invoice,%')))))
purchases = (tax_line
.join(tax, condition=tax_line.tax == tax.id)
.join(line, condition=tax_line.move_line == line.id)
.join(move, condition=line.move == move.id)
.join(period, condition=move.period == period.id)
.join(invoice, condition=invoice.move == move.id)
.join(company, condition=company.id == invoice.company)
.select(
Min(tax_line.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'),
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(tax_line.amount).as_('amount'),
company.currency.as_('currency'),
where=where,
group_by=[
invoice.tax_identifier,
invoice.party,
invoice.party_tax_identifier,
tax.es_ec_purchases_list_code,
company.currency,
]))
return sales | purchases
def trigger_action(cls, records, trigger):
"""
Trigger the action define on trigger for the records
"""
pool = Pool()
TriggerLog = pool.get('ir.trigger.log')
Model = pool.get(trigger.model.model)
ActionModel = pool.get(trigger.action_model.model)
cursor = Transaction().connection.cursor()
trigger_log = TriggerLog.__table__()
ids = list(map(int, records))
# Filter on limit_number
if trigger.limit_number:
new_ids = []
for sub_ids in grouped_slice(ids):
sub_ids = list(sub_ids)
red_sql = reduce_ids(trigger_log.record_id, sub_ids)
cursor.execute(
*trigger_log.select(trigger_log.record_id,
Count(Literal(1)),
where=red_sql
& (trigger_log.trigger == trigger.id),
group_by=trigger_log.record_id))
number = dict(cursor.fetchall())
for record_id in sub_ids:
if record_id not in number:
new_ids.append(record_id)
continue
if number[record_id] < trigger.limit_number:
new_ids.append(record_id)
ids = new_ids
# Filter on minimum_time_delay
if trigger.minimum_time_delay:
new_ids = []
for sub_ids in grouped_slice(ids):
sub_ids = list(sub_ids)
red_sql = reduce_ids(trigger_log.record_id, sub_ids)
cursor.execute(*trigger_log.select(
trigger_log.record_id,
Max(trigger_log.create_date),
where=(red_sql & (trigger_log.trigger == trigger.id)),
group_by=trigger_log.record_id))
delay = dict(cursor.fetchall())
for record_id in sub_ids:
if record_id not in delay:
new_ids.append(record_id)
continue
# SQLite return string for MAX
if isinstance(delay[record_id], str):
datepart, timepart = delay[record_id].split(" ")
year, month, day = map(int, datepart.split("-"))
timepart_full = timepart.split(".")
hours, minutes, seconds = map(
int, timepart_full[0].split(":"))
if len(timepart_full) == 2:
microseconds = int(timepart_full[1])
else:
microseconds = 0
delay[record_id] = datetime.datetime(
year, month, day, hours, minutes, seconds,
microseconds)
if (datetime.datetime.now() - delay[record_id] >=
trigger.minimum_time_delay):
new_ids.append(record_id)
ids = new_ids
records = Model.browse(ids)
if records:
getattr(ActionModel, trigger.action_function)(records, trigger)
if trigger.limit_number or trigger.minimum_time_delay:
to_create = []
for record in records:
to_create.append({
'trigger': trigger.id,
'record_id': record.id,
})
if to_create:
TriggerLog.create(to_create)
def table_query(cls):
pool = Pool()
Company = pool.get('company.company')
Invoice = pool.get('account.invoice')
Move = pool.get('account.move')
Line = pool.get('account.move.line')
TaxLine = pool.get('account.tax.line')
Period = pool.get('account.period')
Tax = pool.get('account.tax')
context = Transaction().context
company = Company.__table__()
invoice = Invoice.__table__()
cancel_invoice = Invoice.__table__()
move = Move.__table__()
cancel_move = Move.__table__()
line = Line.__table__()
tax_line = TaxLine.__table__()
period = Period.__table__()
tax = Tax.__table__()
where = ((invoice.company == context.get('company'))
& (period.fiscalyear == context.get('fiscalyear'))
& ~tax.es_exclude_from_vat_book)
where &= ~Exists(cancel_invoice
.join(cancel_move,
condition=cancel_invoice.cancel_move == cancel_move.id)
.select(cancel_invoice.id, distinct=True,
where=((cancel_invoice.id == invoice.id)
& (~cancel_move.origin.like('account.invoice,%')))))
groups = cls.included_tax_groups()
if groups:
where &= tax.group.in_(groups)
if context.get('start_period'):
start_period = Period(context['start_period'])
where &= (period.start_date >= start_period.start_date)
if context.get('end_period'):
end_period = Period(context['end_period'])
where &= (period.end_date <= end_period.end_date)
query = (tax_line
.join(tax, condition=tax_line.tax == tax.id)
.join(line, condition=tax_line.move_line == line.id)
.join(move, condition=line.move == move.id)
.join(period, condition=move.period == period.id)
.join(invoice, condition=invoice.move == move.id)
.join(company, condition=company.id == invoice.company)
.select(
Min(tax_line.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'),
invoice.id.as_('invoice'),
invoice.invoice_date.as_('invoice_date'),
invoice.party.as_('party'),
invoice.party_tax_identifier.as_('party_tax_identifier'),
Coalesce(tax.es_reported_with, tax.id).as_('tax'),
Sum(tax_line.amount,
filter_=((tax_line.type == 'base')
& (tax.es_reported_with == Null))).as_('base_amount'),
Coalesce(
Sum(tax_line.amount,
filter_=((tax_line.type == 'tax')
& (tax.es_reported_with == Null))),
0).as_('tax_amount'),
Min(tax.id,
filter_=(tax.es_reported_with != Null)).as_(
'surcharge_tax'),
Coalesce(Sum(tax_line.amount,
filter_=((tax_line.type == 'tax')
& (tax.es_reported_with != Null))), 0).as_(
'surcharge_tax_amount'),
where=where,
group_by=[
invoice.id,
invoice.party,
invoice.invoice_date,
invoice.party_tax_identifier,
Coalesce(tax.es_reported_with, tax.id),
]))
return query
def lock_id(self, id, timeout=None):
return Literal(True)
def table_query(self=None):
Config = Pool().get('purchase.configuration')
config = Config(1) # 库存地配置
Lot = Pool().get('stock.lot')
product_lot = Lot.__table__()
where = Literal(True)
ProductDrop = Pool().get('product.template')
Product = Pool().get('product.product')
UomCategory = Pool().get('product.category')
con_date = Pool().get('hrp_internal_delivery.internal_straights')
Date = Pool().get('ir.date')
today = Date.today()
start_time = Transaction().context.get('start_time')
if Transaction().context.get('end_time') != None:
end_time = Transaction().context.get('end_time')
else:
timeStamp = int(time.mktime(time.strptime('5000-01-01', "%Y-%m-%d")))
rang_date = datetime.datetime.utcfromtimestamp(timeStamp).date()
end_time = rang_date
if Transaction().context.get('location'):
location_id = Transaction().context['location']
value_none = []
location_ids = Transaction().context.get('location')
if location_ids == config.warehouse.storage_location.id: # 中心药库
location_frozen_id = config.return_of.id # 中心药库冻结区
elif location_ids == config.hospital.storage_location.id: # 住院药房
location_frozen_id = config.hospital_freeze.id # 住院药房冻结区
elif location_ids == config.outpatient_service.storage_location.id: # 门诊药房
location_frozen_id = config.outpatient_freeze.id # 门诊药房冻结区
elif location_ids == config.medical.storage_location.id: # 体检药房
location_frozen_id = config.medical.freeze_location.id # 体检药房冻结区
elif location_ids == config.endoscopic.storage_location.id: # 内镜药房
location_frozen_id = config.endoscopic.freeze_location.id # 内镜药房冻结区
elif location_ids == config.preparation.storage_location.id: # 制剂室
location_frozen_id = config.preparation.freeze_location.id # 制剂室冻结区
elif location_ids == config.ward.storage_location.id: # 放射科
location_frozen_id = config.ward.freeze_location.id # 放射科冻结区
elif location_ids == config.herbs.storage_location.id: # 草药房
location_frozen_id = config.herbs.freeze_location.id # 草药房冻结区
else:
location_frozen_id = config.return_of.id # 药库冻结区
with Transaction().set_context(stock_date_end=today): # 查看具体库下面的批次对应的数量
warehouse_quant = Product.products_by_location([location_frozen_id], with_childs=True,
grouping=('product', 'lot'))
for key, value in warehouse_quant.items():
if value > 0.0:
if key[-1] != None:
product_lots = Lot.search([('id', '=', key[-1])])[0].shelf_life_expiration_date
condition = Transaction().context.get('condition')
if condition == None:
if product_lots >= start_time and product_lots <= end_time:
value_none.append(key[-1])
elif condition == '01':
get_month = con_date.get_today_month(3)
timeStamp = int(time.mktime(time.strptime(get_month, "%Y-%m-%d")))
rang_date = datetime.datetime.utcfromtimestamp(timeStamp).date()
if product_lots >= start_time and product_lots <= rang_date:
value_none.append(key[-1])
elif condition == '02':
get_month = con_date.get_today_month(6)
timeStamp = int(time.mktime(time.strptime(get_month, "%Y-%m-%d")))
rang_date = datetime.datetime.utcfromtimestamp(timeStamp).date()
if product_lots >= start_time and product_lots <= rang_date:
value_none.append(key[-1])
else:
if product_lots <= start_time:
value_none.append(key[-1])
else:
pass
with Transaction().set_context(stock_date_end=today): # 查看具体库下面的批次对应的数量
warehouse_quant = Product.products_by_location([location_id], with_childs=True,
grouping=('product', 'lot'))
lists = []
list_lot = []
product_id = []
intersection_product = []
for i in value_none:
list_lot.append(i)
for key, value in warehouse_quant.items():
if value > 0.0:
if key[-1] != None:
product_lots = Lot.search([('id', '=', key[-1])])[0].shelf_life_expiration_date
condition = Transaction().context.get('condition')
if condition == None:
if product_lots >= start_time and product_lots <= end_time:
lists.append(key[-1])
product_id.append(key[1])
list_lot.append(key[-1])
if condition == '01':
get_month = con_date.get_today_month(3)
timeStamp = int(time.mktime(time.strptime(get_month, "%Y-%m-%d")))
rang_date = datetime.datetime.utcfromtimestamp(timeStamp).date()
if product_lots >= start_time and product_lots <= rang_date:
lists.append(key[-1])
product_id.append(key[1])
list_lot.append(key[-1])
if condition == '02':
get_month = con_date.get_today_month(6)
timeStamp = int(time.mktime(time.strptime(get_month, "%Y-%m-%d")))
rang_date = datetime.datetime.utcfromtimestamp(timeStamp).date()
if product_lots >= start_time and product_lots <= rang_date:
lists.append(key[-1])
product_id.append(key[1])
list_lot.append(key[-1])
if condition == '03':
if product_lots <= start_time:
lists.append(key[-1])
product_id.append(key[1])
list_lot.append(key[-1])
else:
pass
if Transaction().context.get('drug_type'): # 通过药品种类进行区分
drug_type_id_list = []
if Transaction().context['drug_type'] == '00':
drug_name = u'西药'
elif Transaction().context['drug_type'] == '01':
drug_name = u'中成药'
elif Transaction().context['drug_type'] == '02':
drug_name = u'中草药'
elif Transaction().context['drug_type'] == '03':
drug_name = u'颗粒中'
elif Transaction().context['drug_type'] == '04':
drug_name = u'原料药'
elif Transaction().context['drug_type'] == '05':
drug_name = u'敷药'
elif Transaction().context['drug_type'] == '06':
drug_name = ''
elif Transaction().context['drug_type'] == '07':
drug_name = u'同位素'
else:
drug_name = ''
if drug_name == '':
pass
else:
uom_category = UomCategory.search([('name', '=', drug_name)])
Drug_type_id = ProductDrop.search([('categories', '=', [uom_category[0].id])])
if Drug_type_id:
for i in Drug_type_id:
intersection_product.append(i)
drug_type_id_list.append(i.products[0].id)
else:
pass
intersection_id = [val for val in product_id if val in drug_type_id_list]
ids_list = []
for ids in intersection_id:
find_lot_id = Lot.search([('product', '=', ids)])
if find_lot_id:
for i in find_lot_id:
ids_list.append(i.id)
ggg = [val for val in list_lot if val in ids_list]
del list_lot[:]
for i in ggg:
list_lot.append(i)
if Transaction().context.get('product'):
product_product = []
product_ids = Transaction().context.get('product')
product_lots = Lot.search([('product', '=', product_ids)])
if product_lots:
for i in product_lots:
product_product.append(i.id)
test_lot_id = [val for val in product_product if val in list_lot]
del list_lot[:]
for i in test_lot_id:
list_lot.append(i)
if intersection_product == []:
pass
else:
lot_list = []
intersection_product_id = [val for val in product_product if val in intersection_product]
if intersection_product_id:
for ids in intersection_product_id:
find_lot_id = Lot.search([('product', '=', ids)])
if find_lot_id:
for i in find_lot_id:
lot_list.append(i.id)
del list_lot[:]
lot_number = [val for val in lot_list if val in list_lot]
for i in lot_number:
list_lot.append(i)
product_lot_all = Lot.search([('id', 'in', list_lot)], query=True, order=[])
where &= product_lot.id.in_(product_lot_all)
Result = product_lot.select(
product_lot.id.as_('id'),
Max(product_lot.create_uid).as_('create_uid'),
Max(product_lot.create_date).as_('create_date'),
Max(product_lot.write_uid).as_('write_uid'),
Max(product_lot.write_date).as_('write_date'),
product_lot.number.as_('lot'),
product_lot.product.as_('product'),
product_lot.shelf_life_expiration_date.as_('term_of_validity'),
where=where,
group_by=product_lot.id)
return Result
def get(cls, model_name, mode='read'):
"Return dictionary of non-global and global rules"
pool = Pool()
RuleGroup = pool.get('ir.rule.group')
Model = pool.get('ir.model')
RuleGroup_Group = pool.get('ir.rule.group-res.group')
User_Group = pool.get('res.user-res.group')
rule_table = cls.__table__()
rule_group = RuleGroup.__table__()
rule_group_group = RuleGroup_Group.__table__()
user_group = User_Group.user_group_all_table()
model = Model.__table__()
transaction = Transaction()
assert mode in cls.modes
model_names = []
model2field = defaultdict(list)
def update_model_names(Model, path=None):
if Model.__name__ in model_names:
return
model_names.append(Model.__name__)
if path:
model2field[Model.__name__].append(path)
for field_name in Model.__access__:
field = getattr(Model, field_name)
Target = field.get_target()
if path:
target_path = path + '.' + field_name
else:
target_path = field_name
update_model_names(Target, target_path)
update_model_names(pool.get(model_name))
cursor = transaction.connection.cursor()
user_id = transaction.user
# root user above constraint
if user_id == 0:
return {}, {}
cursor.execute(*rule_table.join(rule_group,
condition=rule_group.id == rule_table.rule_group
).join(model,
condition=rule_group.model == model.id
).select(rule_table.id,
where=(model.model.in_(model_names))
& (getattr(rule_group, 'perm_%s' % mode) == Literal(True))
& (rule_group.id.in_(
rule_group_group.join(
user_group,
condition=(rule_group_group.group
== user_group.group)
).select(rule_group_group.rule_group,
where=user_group.user == user_id)
)
| (rule_group.default_p == Literal(True))
| (rule_group.global_p == Literal(True))
)))
ids = [x for x, in cursor]
# Test if there is no rule_group that have no rule
cursor.execute(*rule_group.join(model,
condition=rule_group.model == model.id
).select(rule_group.id,
where=(model.model.in_(model_names))
& ~rule_group.id.in_(rule_table.select(rule_table.rule_group))
& rule_group.id.in_(rule_group_group.join(user_group,
condition=rule_group_group.group == user_group.group
).select(rule_group_group.rule_group,
where=user_group.user == user_id))))
no_rules = cursor.fetchone()
clause = defaultdict(lambda: ['OR'])
clause_global = defaultdict(lambda: ['OR'])
decoder = PYSONDecoder(cls._get_context())
# Use root user without context to prevent recursion
with transaction.set_user(0), transaction.set_context(user=0):
for rule in cls.browse(ids):
assert rule.domain, ('Rule domain empty,'
'check if migration was done')
dom = decoder.decode(rule.domain)
target_model = rule.rule_group.model.model
if target_model in model2field:
target_dom = ['OR']
for field in model2field[target_model]:
target_dom.append((field, 'where', dom))
dom = target_dom
if rule.rule_group.global_p:
clause_global[rule.rule_group].append(dom)
else:
clause[rule.rule_group].append(dom)
if no_rules:
group_id = no_rules[0]
clause[RuleGroup(group_id)] = []
return clause, clause_global
def _case_expression(self):
expression = super(Count, self)._case_expression
if (isinstance(self.expression, Literal) and expression.value == '*'):
expression = Literal(1)
return expression
