def get_charge_method(self, order_id):
order_charge_method = util.get_table('order_charge_method')
order = util.get_table('order')
charge_method = util.get_table('charge_method')
charge_method_type = util.get_table('charge_method_type')
res = (self.session.query(order, order_charge_method, charge_method,
charge_method_type)
.join(order_charge_method, charge_method, charge_method_type)
.filter(order_charge_method.order_id == order_id, charge_method.active == 1)
.order_by(order.date_created, order_charge_method.percent.desc()).all())
return res
def get_role_row(self, table_name, params):
table_object = util.get_table(table_name)
table_object_role = util.get_table(table_name + "_role")
criteria = [table_object_role.role_id == self.role.id, table_object.id == table_object_role.table_object_id]
for key, value in params.items():
criteria.append(getattr(table_object, key) == value)
result = self.session.query(table_object, table_object_role).filter(*criteria).first()
return result
def get_tables(self, depth = 2):
self.tables[self.table_name] = {'level': 0,
'link_display_name': None,
'parent': None,
'link_data': None,
'link_type': None,
'table_object': util.get_table(self.table_name),
'table_meta_data': (self.role_access_control.has_access
('TableObject', {'name': self.table_name}))}
self.initialize_fields(self.table_name)
self.tables['history'] = {'level': 0,
'parent': None,
'link_display_name': None,
'link_data': None,
'link_type': None,
'table_object': util.get_table('history'),
'table_meta_data': self.role_access_control.has_access('TableObject',
{'name': 'history'})}
self.initialize_fields('history')
if depth > 0 and self.tables[self.table_name]['table_meta_data']:
# if self.tables[self.table_name]['table_meta_data'].note_enabled == 1:
# self.tables['history'] = {'level': 1,
# 'parent': self.table_name,
# 'link_display_name': 'entry_id',
# 'link_data': None,
# 'link_type': 'table_id',
# 'table_object': util.get_table('note'),
# 'table_meta_data': self.role_access_control.has_access('TableObject',
# {'name': 'note'})}
# self.initialize_fields('note')
data = self.role_access_control.get_link_tables(self.tables[self.table_name]['table_meta_data'], depth)
for index, link_data in enumerate(data):
# link_data - {'parent': table_object_name, 'level': level, 'table_meta_data': table_meta_data,
# 'link_data': row.TableObjectChildren, 'link_type': 'child'}
table_name = link_data['table_meta_data'].name
self.initialize_fields(table_name)
link_display_name = self.fields[table_name].fields_by_id[(link_data['link_data'].child_table_object_id,
link_data['link_data'].child_link_field_id)] \
.Field.display_name
self.tables[table_name] = {'level': link_data['level'],
'parent': link_data['parent'],
'link_data': link_data['link_data'],
'table_object': util.get_table(table_name),
'table_meta_data': link_data['table_meta_data'],
'link_display_name': link_display_name,
'link_type': link_data['link_type']}
def update_rows(self, table, updates, ids):
ids.sort()
table_model = util.get_table(table)
filters = [
table_model.id.in_(ids),
getattr(table_model, 'organization_id').in_(self.org_ids)
]
rows = table_model.query.filter(*filters).order_by('id').all()
updated = []
for i, row in enumerate(rows):
row_updates = []
for col, val in updates.items():
try:
col_obj = getattr(table_model, col)
if isinstance(col_obj.type, DateTime) and val == 'now':
val = func.now()
setattr(row, col, val)
row_updates.append(col)
except AttributeError:
print('failed to update')
# commit if we were able to make all updates for the row
if len(updates) == len(row_updates):
self.session.commit()
updated.append(ids[i])
# change table version in cache
current_app.cache.increment_version([table])
else:
self.session.rollback()
print('rollback')
return updated
def set_work_items(self, work_item_group_id, save_items, parent, work_items = None):
table_model = util.get_table('work_item')
work_item_table_object = self.session.query(models.TableObject).filter_by(name='work_item').first()
table_object_id = None
success = False
parent_id = None
try:
for item in save_items:
table_object_id = self.session.query(models.TableObject.id).filter_by(name=item['table']).first()[0]
old_item_exists = None
if work_items:
# see if item already saved
for old_item in work_items:
if old_item.WorkItem.table_object_id == table_object_id and old_item.WorkItem.row_id == item['id']:
old_item_exists = old_item
break
continue # don't need to add this one it is already there
# see if a null row exists, and update row_id
for i, old_item in enumerate(work_items):
if old_item.WorkItem.table_object_id == table_object_id and old_item.WorkItem.row_id == None:
old_item_exists = old_item
work_items.pop(i)
break
if old_item_exists:
old_item_row = self.session.query(models.WorkItem).filter_by(id=old_item_exists.WorkItem.id).first()
setattr(old_item_row, 'row_id', item['id'])
else: # insert new row
new_name = work_item_table_object.get_new_name()
if parent:
parent_table_object_id = self.session.query(models.TableObject.id).filter_by(name=parent['table']).first()[0]
filters = [
(models.WorkItem.work_item_group_id == work_item_group_id),
(models.WorkItem.table_object_id ==
parent_table_object_id),
(models.WorkItem.row_id == parent['id'])
]
parent_id = self.session.query(models.WorkItem.id).filter(*filters).first()[0]
new_row = table_model(name = new_name, active = 1,
organization_id = self.current_org_id, work_item_group_id = work_item_group_id,
table_object_id = table_object_id, row_id = item['id'],
parent_id = parent_id)
self.session.add(new_row)
except:
self.session.rollback()
print('rollback')
logging.info(
'save_work_item rollback- params: '
+ ' work_item_group_id: ' + str(work_item_group_id)
+ ' parent: ' + str(parent)
+ ' save_items: ' + json.dumps(save_items)
)
else:
print('commit')
self.session.commit()
success = True
return success
def get_table_by_id(self, table_id):
table_object = util.get_table('table_object')
criteria = [getattr(table_object, 'id') == table_id]
result = self.session.query(table_object).filter(*criteria).first()
return result
def dynamic_field_data(self, dynamic_field_id, field_definition_id):
dynamic_field = self.session.query(models.Fields).filter_by(id=dynamic_field_id).all()
dynamic_table = util.get_table(dynamic_field.foreign_key_table_object_id, 'id')
return (self.session.query(dynamic_table).
options(defer('id','name','order','description','date_created','last_modified','active',
'organization_id','display_name')).
filter_by(id=field_definition_id).first())
def get_row(self, table_name, params):
table_object = util.get_table(table_name)
criteria = []
for key, value in params.items():
criteria.append(getattr(table_object, key) == value)
result = self.session.query(table_object).filter(*criteria).first()
return result
def get_search_results(self, table_name, params):
table = util.get_table(table_name)
filters = []
for key, value in params.items():
filters.append((getattr(table, key)).like('%' + value + '%'))
# only return organizations the user belongs to
if table_name == 'Organization':
filters.append((getattr(table, 'id')).in_(self.org_ids))
return table.query.filter(*filters).order_by(getattr(table, 'name'))
def get_price(self, criteria):
result = None
org_row = self.get_row('organization', {'id': self.current_org_id})
org_type_id = getattr(org_row, 'organization_type_id')
if org_type_id:
where = []
table_object = util.get_table('price_list')
where.append(getattr(table_object, 'price_item_id') == criteria['price_item_id'])
where.append(getattr(table_object, 'organization_type_id') ==
org_type_id)
result = self.session.query(table_object).filter(*where).first()
return result
def get_line_items(self, from_date, to_date, org_list = []):
line_item = util.get_table('line_item')
price_item = util.get_table('price_item')
service_type = util.get_table('service_type')
order = util.get_table('order')
order_charge_method = util.get_table('order_charge_method')
charge_method = util.get_table('charge_method')
charge_method_type = util.get_table('charge_method_type')
invoice = util.get_table('invoice')
institution = util.get_table('institution')
filters = [
line_item.active == 1,
line_item.date_created >= from_date,
line_item.date_created <= to_date,
line_item.price_per_unit > 0,
order.active == 1
]
if org_list:
filters.append(models.Organization.name.in_(org_list))
res = (self.session.query(line_item, price_item, service_type, order,
order_charge_method, charge_method, charge_method_type, models.User,
models.Organization, models.OrganizationType, invoice, institution)
.join((price_item, line_item.price_item_id == price_item.id),
(service_type, price_item.service_type_id ==
service_type.id),
(order, line_item.order_id == order.id),
(order_charge_method, order.id == order_charge_method.order_id),
(charge_method, order_charge_method.charge_method_id == charge_method.id),
(charge_method_type, charge_method.charge_method_type_id == charge_method_type.id),
(models.User, models.User.id == order.submitter_id),
(models.Organization, line_item.organization_id ==
models.Organization.id),
(models.OrganizationType, models.Organization.organization_type_id ==
models.OrganizationType.id)
)
.outerjoin((invoice, invoice.id == line_item.invoice_id),
(institution, institution.id ==
models.Organization.institution_id))
.filter(*filters).order_by((line_item.invoice_id == None), line_item.invoice_id, models.Organization.name, charge_method.id).all())
return res
def set_organization_id(self, row_org_id = None):
if row_org_id is not None:
if isinstance(row_org_id, int):
return row_org_id
else:
org_table_object = util.get_table('organization')
org_record = self.organization_access_control.session.query(org_table_object). \
filter_by(name = row_org_id).first()
if org_record:
return org_record.id
if self.organization_access_control.current_org_id is not None:
return self.organization_access_control.current_org_id
else:
return 1
def get_descendant_data(self, child_table_name, child_link_field_id, parent_ids):
field = self.session.query(models.Field).filter_by(id=child_link_field_id).first()
child_table = util.get_table(child_table_name)
parent_column = getattr(child_table, field.display_name)
filters = [getattr(child_table, 'organization_id').in_(self.org_ids), parent_column.in_(parent_ids)]
# logging.info('child_table_name: ' + child_table_name)
# logging.info('parent_column.name: ' + parent_column.name)
rows = self.session.query(child_table).\
filter(*filters).order_by('order', 'name').all()
row_data = []
for index, row in enumerate(rows):
row_data.append({'parent_id': getattr(row, field.display_name), 'instance': row})
return row_data
def set_foreign_key_field_id(self, table_name, field, value):
if isinstance( value, int ):
return value
fk_field_display = self.fields[table_name].fields[table_name + "|" + field].FK_Field
fk_table_data = self.fields[table_name].fields[table_name + "|" + field].FK_TableObject
fk_table_object = util.get_table(fk_table_data.name)
if fk_table_data.name == 'field':
res = self.organization_access_control.session.query(fk_table_object). \
filter(getattr(fk_table_object, fk_field_display.display_name) == value). \
filter(fk_table_object.table_object_id == self.tables[table_name]['table_meta_data'].id)
fk_id = res.first()
else:
fk_id = self.organization_access_control.session.query(fk_table_object). \
filter(getattr(fk_table_object, fk_field_display.display_name) == value).first()
if fk_id:
return fk_id.id
else:
return None
def insert_row(self, table, fields = {}):
new_row = None
table_model = util.get_table(table)
table_table_object = self.session.query(models.TableObject).filter_by(name=table).first()
# ensure one inserts under ones current_org_id
fields['organization_id'] = self.current_org_id
try:
new_name = table_table_object.get_new_name()
new_row = table_model(name = new_name, **fields)
self.session.add(new_row)
except:
self.session.rollback()
print('rollback')
str_fields = ''
for field in fields:
str_fields += str(field)
logging.info(
'insert_row into ' + table + ' rollback- params: ' +
str_fields
)
else:
print('commit')
self.session.commit()
return new_row
def get_foreign_key_data(self, fk_table_data, fk_field_data, params=None):
# logging.info(fk_table_data)
# logging.info(fk_field_data)
results = [(-99, '')]
if fk_field_data is not None:
fk_table_object = util.get_table(fk_table_data.name)
filters = [
getattr(fk_table_object, 'organization_id').in_(self.org_ids),
getattr(fk_table_object, 'active') == 1
]
if params is None:
rows = self.session.query(getattr(fk_table_object, 'id'), getattr(fk_table_object, fk_field_data.display_name).label('name')).filter(*filters).order_by('order').all()
else:
for key, value in params.items():
filters.append(getattr(fk_table_object, key) == value)
rows = self.session.query(getattr(fk_table_object, 'id'),
getattr(fk_table_object,
fk_field_data['foreign_key'])).filter(*filters).order_by('order').all()
for row in rows:
results.append((row.id, row.name))
return results
def get_table_query_data(self, field_dict, criteria={}, active = 1):
results = []
tables = set([])
joins = []
table_models = {}
outer_joins = []
columns = []
fk_columns = []
aliases = {}
wheres = []
group_by = []
first_table_named = None # set to first table name, dont add to joins
for field in field_dict.values():
# Get the table to display, fk table for fks
if field.is_foreign_key:
table_name = field.FK_TableObject.name
else:
table_name = field.TableObject.name
if table_name in table_models:
table_model = table_models[table_name]
else:
table_model = util.get_table(table_name)
table_models[table_name] = table_model
# handle fks
if field.is_foreign_key:
if field.TableObject.name in table_models:
fk_table_model = table_models[field.TableObject.name]
else:
fk_table_model = util.get_table(field.TableObject.name)
table_models[field.TableObject.name] = fk_table_model
# create alias to the fk table
# solves the case of more than one join to same table
alias_name = field.Field.display_name + '_' + field.FK_TableObject.name + '_' + field.FK_Field.display_name
aliases[alias_name] = aliased(table_model, name = alias_name)
outer_joins.append((
aliases[alias_name],
getattr(fk_table_model, field.Field.display_name) == aliases[alias_name].id
))
col = getattr(aliases[alias_name],
field.FK_Field.display_name)
else: # non-fk field
tables.add(table_model)
col = getattr(table_model, field.Field.display_name)
if field.type == 'file': # give name as well
col = getattr(table_model, 'name') + '/' + col
# add to joins if not first table, avoid joining to self
if (not first_table_named
or (first_table_named == field.TableObject.name)):
first_table_named = field.TableObject.name
else:
if table_model not in joins:
joins.append(table_model)
if field.group_by == 1:
group_by.append(col)
if field.group_func:
col = getattr(func, field.group_func)(col.op('SEPARATOR')(', '))
columns.append(col.label(field.name))
criteria_key = (field.TableObject.name, field.Field.display_name)
# don't include criteria for self foreign keys
if criteria_key in criteria and not (field.is_foreign_key and
field.FK_TableObject.name == first_table_named):
if type(criteria[criteria_key]) is list:
wheres.append(col.in_(criteria[criteria_key]))
elif type(criteria[criteria_key]) is dict:
if ('from' in criteria[criteria_key]
and 'to' in criteria[criteria_key]
):
wheres.extend([col >= criteria[criteria_key]['from'],
col <= criteria[criteria_key]['to']])
if( 'compare' in criteria[criteria_key]
and 'value' in criteria[criteria_key]
):
if criteria[criteria_key]['compare'] == 'greater than':
wheres.append(col > criteria[criteria_key]['value'])
elif criteria[criteria_key]['compare'] == '!=':
wheres.append(col != criteria[criteria_key]['value'])
else:
wheres.append(col == criteria[criteria_key])
id_cols = []
# add organization id checks on all tables, does not include fk tables
for table_model in tables:
# add a row id that is the id of the first table named
id_table_name = table_model.__table__.name.lower()
id_table_col = getattr(table_model, 'id')
if id_table_name == first_table_named:
col = id_table_col
columns.append(col.label('DT_RowId'))
id_cols.append(id_table_name + '-' + cast(id_table_col, String))
wheres.append(getattr(table_model, 'organization_id').in_(self.org_ids))
wheres.append(getattr(table_model, 'active') == active)
first = True
for c in id_cols:
if first:
id_col = c
first = False
else:
id_col = id_col.concat('|' + c)
columns.append(id_col.label('DT_row_label'))
start = time.time()
results = (
self.session.query(*columns).
join(*joins).
outerjoin(*outer_joins).
filter(*wheres).group_by(*group_by).all()
)
print('query: ' + str(time.time() - start))
return results
def get_long_text(self, lt_id):
table = util.get_table("long_text")
return table.query.filter_by(id=lt_id).first()
def get_attr_from_id(self, table_object_id, row_id, attr):
table_object = self.session.query(models.TableObject).filter_by(id=table_object_id).first()
table_model = util.get_table(table_object.name)
row = self.session.query(table_model).filter_by(id=row_id).first()
return getattr(row, attr)