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)