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)