def load_documents(court=None):
with connection().cursor() as cursor:
documents = Table('document')
cases = Table('vw_case_for_advocates')
pre_select = documents.join(
cases, type_='INNER',
condition=(cases.id_case==documents.case_id)
)
specific_court_columns = []
for specific_court in Courts.ALL:
court_documents = Table(specific_court.document_db_table)
for col in specific_court.document_db_table_columns:
specific_court_columns.append(Column(court_documents, col).as_('{}__{}'.format(specific_court.name, col)))
pre_select = pre_select.join(
court_documents, type_='LEFT',
condition=(documents.id_document==court_documents.document_id)
)
select = pre_select.select(Column(documents, '*'), *specific_court_columns)
if court is not None:
select.where = documents.court_id == court.id
query, args = tuple(select)
cursor.execute(query, args)
cols = [col[0] for col in cursor.description]
for fetched in cursor:
row = dict(zip(cols, fetched))
current_court = court if court is not None else Courts.BY_ID[row['court_id']]
row['court_id'] = current_court.name
yield current_court.create_document(row)
def load_cases(court=None):
with connection().cursor() as cursor:
cases = Table('vw_case_for_advocates')
annulment = Table('vw_computed_case_annulment')
latest_advocates = Table('vw_latest_tagging_advocate')
latest_results = Table('vw_latest_tagging_case_result')
select = cases.join(
latest_advocates, type_='LEFT',
condition=(latest_advocates.case_id == cases.id_case) & (latest_advocates.status == 'processed')
).join(
latest_results, type_='LEFT',
condition=(latest_results.case_id == cases.id_case) & (latest_results.status == 'processed')
).join(
annulment, type_='LEFT',
condition=(annulment.annuled_case == cases.id_case)
).select(
Column(cases, '*'),
latest_advocates.advocate_id,
latest_results.case_result,
annulment.annuled_case,
annulment.annuling_case
)
if court is not None:
select.where = cases.court_id == court.id
query, args = tuple(select)
cursor.execute(query, args)
cols = [col[0] for col in cursor.description]
for fetched in cursor:
row = dict(zip(cols, fetched))
current_court = court if court is not None else Courts.BY_ID[row['court_id']]
row['court_id'] = current_court.name
yield Case(row)
def restore_default_party_lang_from_4_2(cls):
from trytond.transaction import Transaction
from sql import Null, Table, Cast
from sql.operators import Concat
from trytond.pool import Pool
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
pool = Pool()
property = Table('ir_property')
Lang = pool.get('ir.lang')
field = pool.get('ir.model.field').__table__()
lang = Lang.__table__()
cursor = Transaction().connection.cursor()
query_table = property.join(lang, condition=(
property.value == Concat('ir.lang,', Cast(lang.id, 'VARCHAR'))
)).join(field, condition=((property.field == field.id) &
(field.name == 'lang')))
cursor.execute(
*query_table.select(lang.id, where=property.res == Null))
result = cursor.fetchone()
if result:
result = list(result)
default_lang = Lang(result[0])
print('Default Language restored [%s]' % default_lang.rec_name)
pool.get('party.configuration.party_lang'
).create([{'party_lang': default_lang}])
else:
print('No default language on party configuration found')
def clean_properties_from_4_2(cls):
from sql import Null, Table, Cast
from sql.operators import Like, Concat
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
property = Table('ir_property')
cursor = Transaction().connection.cursor()
cursor.execute(
*property.select(property.res, where=property.res != Null))
res_model_names = list(
set([x[0].split(',')[0] for x in cursor.fetchall()]))
to_delete = {}
for res_model_name in res_model_names:
table_name = res_model_name.replace('.', '_')
res_model = Table(table_name)
query_table = property.join(res_model,
'LEFT OUTER',
condition=(property.res == Concat(
res_model_name + ',',
Cast(res_model.id, 'VARCHAR'))))
cursor.execute(
*query_table.select(property.id,
where=Like(property.res, res_model_name +
',%') & (res_model.id == Null)))
property_ids = [x[0] for x in cursor.fetchall()]
if property_ids:
to_delete[res_model_name] = property_ids
if to_delete:
cursor.execute(*property.delete(
where=property.id.in_(sum([p
for p in to_delete.values()], []))))
for res_model_name, property_ids in to_delete.items():
if property_ids:
print '[%s] - %s Inconsistent record(s) removed' % (
res_model_name, len(property_ids))
else:
print 'Nothing to do - Exisiting property records are clean'
def clean_properties_from_4_2(cls):
from sql import Null, Table, Cast
from sql.operators import Like, Concat
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
property = Table('ir_property')
cursor = Transaction().connection.cursor()
cursor.execute(*property.select(property.res,
where=property.res != Null))
res_model_names = list(set([x[0].split(',')[0]
for x in cursor.fetchall()]))
to_delete = {}
for res_model_name in res_model_names:
table_name = res_model_name.replace('.', '_')
res_model = Table(table_name)
query_table = property.join(res_model, 'LEFT OUTER', condition=(
property.res == Concat(res_model_name + ',',
Cast(res_model.id, 'VARCHAR'))
))
cursor.execute(*query_table.select(property.id,
where=Like(property.res, res_model_name + ',%') &
(res_model.id == Null)))
property_ids = [x[0] for x in cursor.fetchall()]
if property_ids:
to_delete[res_model_name] = property_ids
if to_delete:
cursor.execute(
*property.delete(where=property.id.in_(
sum([p for p in list(to_delete.values())], []))))
for res_model_name, property_ids in list(to_delete.items()):
if property_ids:
print('[%s] - %s Inconsistent record(s) removed' % (
res_model_name, len(property_ids)))
else:
print('Nothing to do - Exisiting property records are clean')
def load_advocates():
with connection().cursor() as cursor:
advocates = Table('advocate')
info = Table('advocate_info')
select = advocates.join(
info,
condition=(advocates.id_advocate == info.advocate_id) & (info.valid_to == None)
).select(
info.degree_before,
info.degree_after,
info.name,
info.surname,
advocates.id_advocate.as_('id'),
advocates.identification_number.as_('registration_number'),
advocates.registration_number.as_('identification_number')
)
query, args = tuple(select)
cursor.execute(query, args)
cols = [col[0] for col in cursor.description]
for fetched in cursor:
row = dict(zip(cols, fetched))
yield Advocate(row)
def restore_default_party_lang_from_4_2(cls):
from trytond.transaction import Transaction
from sql import Null, Table, Cast
from sql.operators import Concat
from trytond.pool import Pool
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
pool = Pool()
property = Table('ir_property')
Lang = pool.get('ir.lang')
field = pool.get('ir.model.field').__table__()
lang = Lang.__table__()
cursor = Transaction().connection.cursor()
query_table = property.join(
lang,
condition=(property.value == Concat(
'ir.lang,',
Cast(lang.id,
'VARCHAR')))).join(field,
condition=((property.field == field.id)
& (field.name == 'lang')))
cursor.execute(
*query_table.select(lang.id, where=property.res == Null))
result = cursor.fetchone()
if result:
result = list(result)
default_lang = Lang(result[0])
print 'Default Language restored [%s]' % default_lang.rec_name
pool.get('party.configuration.party_lang').create([{
'party_lang':
default_lang
}])
else:
print 'No default language on party configuration found'
def __register__(cls, module):
connection = Transaction().connection
cursor = connection.cursor()
super().__register__(module)
table_h = cls.__table_handler__(module)
template_lot_type_table_name = config.get(
'table',
'product.template-stock.lot.type',
default='product.template-stock.lot.type'.replace('.', '_'))
lot_type_table_name = config.get('table',
'stock.lot.type',
default='stock.lot.type'.replace(
'.', '_'))
# Migration from 5.2: fill lot_required
if (table_h.table_exist(template_lot_type_table_name)
and table_h.table_exist(lot_type_table_name)):
table = cls.__table__()
template_lot_type = Table(template_lot_type_table_name)
lot_type = Table(lot_type_table_name)
cursor_select = connection.cursor()
cursor_select.execute(*template_lot_type.select(
template_lot_type.template,
distinct_on=template_lot_type.template))
for template_id, in cursor_select:
cursor.execute(*template_lot_type.join(
lot_type, condition=template_lot_type.type == lot_type.id
).select(lot_type.code,
where=template_lot_type.template == template_id))
value = cls.lot_required.sql_format([t for t, in cursor])
cursor.execute(*table.update([table.lot_required], [value],
where=table.id == template_id))
table_h.drop_table('product.template-stock.lot.type',
template_lot_type_table_name)
table_h.drop_table('stock.lot.type', lot_type_table_name)
def startElement(self, name, attributes):
cursor = Transaction().cursor
values = {}
self.xml_id = attributes['id']
for attr in ('name', 'icon', 'sequence', 'parent', 'action', 'groups'):
if attributes.get(attr):
values[attr] = attributes.get(attr)
if attributes.get('active'):
values['active'] = bool(eval(attributes['active']))
if values.get('parent'):
values['parent'] = self.mh.get_id(values['parent'])
action_name = None
if values.get('action'):
action_id = self.mh.get_id(values['action'])
# TODO maybe use a prefetch for this:
action = Table('ir_action')
report = Table('ir_action_report')
act_window = Table('ir_action_act_window')
wizard = Table('ir_action_wizard')
url = Table('ir_action_url')
act_window_view = Table('ir_action_act_window_view')
view = Table('ir_ui_view')
icon = Table('ir_ui_icon')
cursor.execute(*action.join(
report, 'LEFT', condition=action.id == report.action
).join(
act_window, 'LEFT', condition=action.id == act_window.action
).join(wizard, 'LEFT', condition=action.id == wizard.action).join(
url, 'LEFT', condition=action.id == url.action
).join(act_window_view,
'LEFT',
condition=act_window.id == act_window_view.act_window).join(
view, 'LEFT', condition=view.id == act_window_view.view
).join(icon, 'LEFT', condition=action.icon ==
icon.id).select(action.name,
action.type,
view.type,
view.field_childs,
icon.name,
where=(report.id == action_id)
| (act_window.id == action_id)
| (wizard.id == action_id)
| (url.id == action_id),
order_by=act_window_view.sequence,
limit=1))
action_name, action_type, view_type, field_childs, icon_name = \
cursor.fetchone()
values['action'] = '%s,%s' % (action_type, action_id)
icon = attributes.get('icon', '')
if icon:
values['icon'] = icon
elif icon_name:
values['icon'] = icon_name
elif action_type == 'ir.action.wizard':
values['icon'] = 'tryton-executable'
elif action_type == 'ir.action.report':
values['icon'] = 'tryton-print'
elif action_type == 'ir.action.act_window':
if view_type == 'tree':
if field_childs:
values['icon'] = 'tryton-tree'
else:
values['icon'] = 'tryton-list'
elif view_type == 'form':
values['icon'] = 'tryton-new'
elif view_type == 'graph':
values['icon'] = 'tryton-graph'
elif view_type == 'calendar':
values['icon'] = 'tryton-calendar'
elif action_type == 'ir.action.url':
values['icon'] = 'tryton-web-browser'
else:
values['icon'] = 'tryton-new'
if values.get('groups'):
raise Exception("Please use separate records for groups")
if not values.get('name'):
if not action_name:
raise Exception(
"Please provide at least a 'name' attributes "
"or a 'action' attributes on the menuitem tags.")
else:
values['name'] = action_name
if values.get('sequence'):
values['sequence'] = int(values['sequence'])
self.values = values
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')
TableHandler = backend.get('TableHandler')
if not 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, basestring):
field_names = [field_names]
if isinstance(value_names, basestring):
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))
'constraint_name': 'fk_contraint_name',
'table_name': 'table',
'column_name': 'table_2',
'foreign_table_name': 'table2',
'foreign_column_name': 'id'
}
}
return fks[field]
q = OOQuery('table', dummy_fk)
sql = q.select(['field1', 'field2', 'table_2.name']).where([
('field1', '=', Field('table_2.name'))
])
t = Table('table')
t2 = Table('table2')
join = t.join(t2)
join.condition = join.left.table_2 == join.right.id
sel = join.select(t.field1.as_('field1'), t.field2.as_('field2'), t2.name.as_('table_2.name'))
sel.where = And((join.left.field1 == join.right.name,))
expect(tuple(sql)).to(equal(tuple(sel)))
with it('must support joins'):
def dummy_fk(table, field):
fks = {
'table_2': {
'constraint_name': 'fk_contraint_name',
'table_name': 'table',
'column_name': 'table_2',
'foreign_table_name': 'table2',
'foreign_column_name': 'id'
}
return {
'table_2': {
'constraint_name': 'fk_contraint_name',
'table_name': 'table',
'column_name': 'table_2',
'foreign_table_name': 'table2',
'foreign_column_name': 'id'
}
}
q = OOQuery('table', dummy_fk)
sql = q.select(['field1', 'field2']).where([
('table_2.code', '=', 'XXX')
])
t = Table('table')
join = t.join(Table('table2'))
join.condition = join.left.table_2 == join.right.id
sel = join.select(t.field1, t.field2)
sel.where = And((join.right.code == 'XXX',))
expect(tuple(sql)).to(equal(tuple(sel)))
with it('must support deep joins'):
def dummy_fk(table):
if table == 'table':
return {
'table_2_id': {
'constraint_name': 'fk_contraint_name',
'table_name': 'table',
'column_name': 'table_2_id',
'foreign_table_name': 'table2',
'foreign_column_name': 'id'
