class Field(db.Model): id = db.Column(db.Integer(), primary_key=True) field_group_id = db.Column(db.Integer(), db.ForeignKey(FieldGroup.id)) field_group = db.relationship(FieldGroup, backref='fields') order = db.Column(db.Integer()) field_type_id = db.Column(db.Integer(), db.ForeignKey(FieldType.id)) field_type = db.relationship(FieldType, lazy="joined") field_name = db.Column(db.String) label = db.Column(db.String) required = db.Column(db.Boolean, default=0) reportable = db.Column(db.Boolean, default=0) max_length = db.Column(db.Integer(), default=0) default = db.Column(db.String, default="") choices = db.Column(db.String, default="") allowed_file_extensions = db.Column(db.String, default="") download_filename_format = db.Column(db.String, default="") validation_regex = db.Column(db.String, default="") description = db.Column(db.UnicodeText, default="") def format_value(self, value): return self.field_type.format_value(value) def data_value(self, value): return self.field_type.data_value(value) def get_default(self): if self.default == '': return None else: return self.default @property def has_choices(self): return self.field_type.has_choices def get_choices(self): if self.field_type.is_boolean: return ['Yes', 'No'] elif not self.choices: return [] else: return [(c, c) for c in self.choices.split("|")] def get_allowed_file_extensions(self): return self.allowed_file_extensions.split("|") def get_label(self): if self.label: return self.label else: return self.field_name def __repr__(self): return 'Field(field_name="{}", order="{}", field_type="{}")'.format( self.field_name, self.order, self.field_type.name )
class AbstractSection(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) publication_id = db.Column(db.Integer(), db.ForeignKey(Publication.id)) publication = db.relationship(Publication, lazy="joined", backref='abstracts') label = db.Column(db.String(200)) text = db.Column(db.UnicodeText())
class UploadData(db.Model): id = db.Column(db.Integer(), primary_key=True) upload_id = db.Column(db.Integer(), db.ForeignKey(Upload.id)) upload = db.relationship(Upload, backref=db.backref("data")) field_id = db.Column(db.Integer(), db.ForeignKey(Field.id)) field = db.relationship(Field) value = db.Column(db.String) def __repr__(self): items = ("%s = %r" % (k, v) for k, v in self.__dict__.items()) return "<%s: {%s}>" % (self.__class__.__name__, ', '.join(items))
class Publication(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) pm_id = db.Column(db.Integer()) journal = db.Column(db.String(200)) published_date = db.Column(db.Date) title = db.Column(db.UnicodeText()) academics = db.relationship("Academic", secondary=academics_publications, collection_class=set, backref=db.backref("publications", lazy="joined"))
class Upload(db.Model): id = db.Column(db.Integer(), primary_key=True) study_id = db.Column(db.Integer(), db.ForeignKey(Study.id)) study_number = db.Column(db.String(20)) uploader_id = db.Column(db.Integer(), db.ForeignKey(User.id)) date_created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) study = db.relationship(Study, backref=db.backref("uploads")) uploader = db.relationship(User) completed = db.Column(db.Boolean, default=0) deleted = db.Column(db.Boolean, default=0)
class Author(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) publication_id = db.Column(db.Integer(), db.ForeignKey(Publication.id)) publication = db.relationship(Publication, lazy="joined", backref='authors') last_name = db.Column(db.String(100)) fore_name = db.Column(db.String(100)) initials = db.Column(db.String(100)) affiliation = db.Column(db.UnicodeText()) @property def full_name(self): return f'{self.fore_name} {self.last_name}'
class TaskFile(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) filename = db.Column(db.UnicodeText()) local_filepath = db.Column(db.UnicodeText()) task_id = db.Column(db.Integer, db.ForeignKey(Task.id)) task = db.relationship(Task, backref=backref('files', cascade='all, delete-orphan')) field_id = db.Column(db.Integer, db.ForeignKey(Field.id)) field = db.relationship(Field, lazy="joined") def set_filename_and_save(self, file): self.filename = file.filename local_filepath = self._new_local_filepath( filename=file.filename, parent=str(self.task.id), ) self.local_filepath = str(local_filepath) local_filepath.parent.mkdir(parents=True, exist_ok=True) file.save(local_filepath) def _new_local_filepath(self, filename, parent=None): result = pathlib.Path(current_app.config["FILE_UPLOAD_DIRECTORY"]) if parent: result = result.joinpath(secure_filename(parent)) result = result.joinpath( secure_filename("{}_{}".format(uuid.uuid1().hex, filename))) return result
class Organisation(db.Model, CommonMixin): CARDIOVASCULAR = 'BRC Cardiovascular Theme' LIFESTYLE = 'BRC Lifestyle Theme' PRECICION = 'BRC Precision Medicine Theme' RESPIRATORY = 'BRC Respiratory Theme' LDC = 'Leicester Diabetes Centre' PRC = 'Patient Recruitment Centre' RandI = 'R&I' OTHER = 'Other - please specify' all_organisations = [ CARDIOVASCULAR, LIFESTYLE, PRECICION, RESPIRATORY, LDC, PRC, RandI, OTHER ] id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(255)) @classmethod def get_organisation(cls, name): return Organisation.query.filter_by(name=name).one() @classmethod def get_other(cls): return cls.get_organisation(Organisation.OTHER)
class Study(db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(100)) date_created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) allow_duplicate_study_number = db.Column(db.Boolean, nullable=False, default=False) allow_empty_study_number = db.Column(db.Boolean, nullable=False, default=False) study_number_format = db.Column(db.String(50)) study_number_name = db.Column(db.String(100)) field_group_id = db.Column(db.Integer(), db.ForeignKey(FieldGroup.id)) field_group = db.relationship(FieldGroup, backref=db.backref("study")) owners = db.relationship( User, secondary=studies_owners, backref=db.backref("owned_studies", lazy="dynamic"), ) collaborators = db.relationship( User, secondary=studies_collaborators, backref=db.backref("collaborator_studies", lazy="dynamic"), ) def __str__(self): return self.name @property def upload_count(self): return len([u for u in self.uploads if not u.deleted]) def upload_count_for_user(self, user): return len( [u for u in self.uploads if not u.deleted and u.uploader == user]) @property def outstanding_upload_count(self): return len( [u for u in self.uploads if not u.deleted and not u.completed]) def get_study_number_name(self): return self.study_number_name or 'Study Number'
class TaskAssignedUser(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) task_id = db.Column(db.Integer, db.ForeignKey(Task.id), nullable=False) task = db.relationship(Task, backref="assigned_user_history") user_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False) user = db.relationship(User) notes = db.Column(db.String(255))
class FieldGroup(db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String) def __str__(self): return self.name def get_field_for_field_name(self, field_name): return {f.field_name: f for f in self.fields}.get(field_name)
class TaskStatus(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) task_id = db.Column(db.Integer, db.ForeignKey(Task.id), nullable=False) task = db.relationship(Task, backref="status_history") notes = db.Column(db.String(255)) task_status_type_id = db.Column(db.Integer, db.ForeignKey(TaskStatusType.id), nullable=False) task_status_type = db.relationship(TaskStatusType, backref="assigned_tasks")
class Task(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(255)) organisation_id = db.Column(db.Integer, db.ForeignKey(Organisation.id)) organisation = db.relationship(Organisation, lazy="joined", backref='tasks') organisation_description = db.Column(db.String(255)) service_id = db.Column(db.Integer, db.ForeignKey(Service.id)) service = db.relationship(Service, lazy="joined", backref='tasks') requestor_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False) requestor = db.relationship(User, lazy="joined", backref='tasks', foreign_keys=[requestor_id]) current_status_type_id = db.Column(db.Integer, db.ForeignKey(TaskStatusType.id), nullable=False) current_status_type = db.relationship(TaskStatusType) current_assigned_user_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=True) current_assigned_user = db.relationship( User, foreign_keys=[current_assigned_user_id]) @property def long_name(self): return "{}: {}".format(self.service.name, self.name) @property def total_todos(self): return len(self.todos) @property def required_todos(self): return len([t for t in self.todos if t.is_required]) @property def complete_todos(self): return len([t for t in self.todos if t.is_complete]) @property def notification_email_addresses(self): return self.service.notification_email_addresses + [ self.requestor.email ] def get_data_for_task_id(self, field_id): return next((t for t in self.data if t.field_id == field_id), None)
class UploadFile(db.Model): id = db.Column(db.Integer(), primary_key=True) upload_id = db.Column(db.Integer(), db.ForeignKey(Upload.id)) upload = db.relationship(Upload, backref=db.backref("files")) field_id = db.Column(db.Integer(), db.ForeignKey(Field.id)) field = db.relationship(Field) filename = db.Column(db.String(500)) def get_download_filename(self): if len(self.field.download_filename_format or '') == 0: return self.filename else: return self.field.download_filename_format.format( file=self) + os.path.splitext(self.filename)[-1] def filepath(self): return os.path.join( secure_filename("{}_{}".format(self.upload.study.id, self.upload.study.name)), secure_filename("{}_{}_{}".format(self.id, self.upload.study_number, self.filename)), )
class Academic(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) google_scholar_id = db.Column(db.String(255)) name = db.Column(db.String(500)) affiliation = db.Column(db.String(500)) cited_by = db.Column(db.Integer) h_index = db.Column(db.Integer) i10_index = db.Column(db.Integer) is_updating = db.Column(db.Boolean) @property def pubmed_name(self): firstname, *_, lastname = self.name.split() return f'{lastname} {firstname[0]}'
class TaskData(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) value = db.Column(db.UnicodeText()) task_id = db.Column(db.Integer, db.ForeignKey(Task.id)) task = db.relationship(Task, backref=backref('data', cascade='all, delete-orphan')) field_id = db.Column(db.Integer, db.ForeignKey(Field.id)) field = db.relationship(Field, lazy="joined") @property def formated_value(self): return self.field.format_value(self.value) @property def data_value(self): return self.field.data_value(self.value)
class Site(db.Model): LBRC = "Leicester Biomedical Research Centre" id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(255)) number = db.Column(db.String(20)) date_created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) def __str__(self): return self.name @property def name_and_number(self): number_portion = "" if self.number: number_portion = " ({})".format(self.number) return self.name + number_portion
class ToDo(AuditMixin, CommonMixin, db.Model): OUTSTANDING = 'Outstanding' COMPLETED = 'Completed' NOT_REQUIRED = 'Not Required' _status_map = { -1: NOT_REQUIRED, 0: OUTSTANDING, 1: COMPLETED, } @staticmethod def get_status_code_from_name(name): return {v: k for k, v in ToDo._status_map.items()}[name] id = db.Column(db.Integer(), primary_key=True) task_id = db.Column(db.Integer, db.ForeignKey(Task.id)) task = db.relationship(Task, backref='todos') description = db.Column(db.UnicodeText()) status = db.Column(db.Integer, db.CheckConstraint("status IN (-1, 0, 1)"), nullable=False, default=0) @property def status_name(self): return ToDo._status_map[self.status] @property def is_outstanding(self): return self.status == 0 @property def is_required(self): return self.status > -1 @property def is_complete(self): return self.status == 1
class Service(AuditMixin, CommonMixin, db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(255)) generic_recipients = db.Column(db.String(255)) suppress_owner_email = db.Column(db.Boolean) field_group_id = db.Column(db.Integer, db.ForeignKey(FieldGroup.id)) field_group = db.relationship(FieldGroup) introduction = db.Column(db.UnicodeText()) def __str__(self): return self.name def get_field_for_field_name(self, field_name): if self.field_group: return self.field_group.get_field_for_field_name(field_name) @property def notification_email_addresses(self): return list( filter(len, [ r.email for r in self.owners if not self.suppress_owner_email ] + re.split(r'[;,\s]+', self.generic_recipients or '')))
class FieldType(db.Model): BOOLEAN = 'BooleanField' INTEGER = 'IntegerField' RADIO = 'RadioField' STRING = 'StringField' TEXTAREA = 'TextAreaField' FILE = 'FileField' MULTIPLE_FILE = 'MultipleFileField' DESCRIPTION = 'DescriptionField' SELECT = 'SelectField' MULTISELECT = 'SelectMultipleField' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String) is_file = db.Column(db.Boolean) def format_value(self, value): if self.name == FieldType.INTEGER: if value is None or not value.isnumeric(): return '' else: return format_number(int(value)) elif self.name == FieldType.BOOLEAN: return format_yesno(value) else: return value def data_value(self, value): if self.name == FieldType.BOOLEAN: return format_boolean(value) else: return value @property def html_tag(self): if self.name == FieldType.DESCRIPTION: return 'p' elif self.name == FieldType.RADIO: return 'ul' elif self.name == FieldType.TEXTAREA: return 'textarea' elif self.name in [FieldType.SELECT, FieldType.MULTISELECT]: return 'select' else: return 'input' @property def html_input_type(self): if self.name == FieldType.BOOLEAN: return 'checkbox' elif self.name == FieldType.INTEGER: return 'text' elif self.name == FieldType.STRING: return 'text' elif self.name == FieldType.FILE: return 'file' elif self.name == FieldType.MULTIPLE_FILE: return 'file' @property def is_boolean(self): return self.name == FieldType.BOOLEAN @property def is_select_multiple(self): return self.name == FieldType.MULTISELECT @property def is_textarea(self): return self.name == FieldType.TEXTAREA @property def has_choices(self): return self.name in [FieldType.MULTISELECT, FieldType.SELECT, FieldType.RADIO] @staticmethod def all_field_type_name(): return [ FieldType.BOOLEAN, FieldType.DESCRIPTION, FieldType.FILE, FieldType.INTEGER, FieldType.MULTIPLE_FILE, FieldType.RADIO, FieldType.STRING, FieldType.TEXTAREA, FieldType.SELECT, FieldType.MULTISELECT, ] @classmethod def _get_field_type(cls, name): return FieldType.query.filter_by(name=name).one() @classmethod def get_boolean(cls): return cls._get_field_type(FieldType.BOOLEAN) @classmethod def get_integer(cls): return cls._get_field_type(FieldType.INTEGER) @classmethod def get_radio(cls): return cls._get_field_type(FieldType.RADIO) @classmethod def get_string(cls): return cls._get_field_type(FieldType.STRING) @classmethod def get_textarea(cls): return cls._get_field_type(FieldType.TEXTAREA) @classmethod def get_file(cls): return cls._get_field_type(FieldType.FILE) @classmethod def get_multifile(cls): return cls._get_field_type(FieldType.MULTIPLE_FILE) @classmethod def get_description(cls): return cls._get_field_type(FieldType.DESCRIPTION) @classmethod def get_select(cls): return cls._get_field_type(FieldType.SELECT) @classmethod def get_multiselect(cls): return cls._get_field_type(FieldType.MULTISELECT) def __str__(self): return self.name
class TaskStatusType(db.Model, CommonMixin): CREATED = 'Created' IN_PROGRESS = 'In Progress' DONE = 'Done' AWAITING_INFORMATION = 'Awaiting Information' CANCELLED = 'Cancelled' DECLINED = 'Declined' DUPLICATE = 'Duplicate' all_details = { CREATED: { 'is_complete': False, 'is_active': False, }, IN_PROGRESS: { 'is_complete': False, 'is_active': True, }, DONE: { 'is_complete': True, 'is_active': False, }, AWAITING_INFORMATION: { 'is_complete': False, 'is_active': False, }, CANCELLED: { 'is_complete': True, 'is_active': False, }, DECLINED: { 'is_complete': True, 'is_active': False, }, DUPLICATE: { 'is_complete': True, 'is_active': False, }, } @classmethod def get_task_status(cls, name): return TaskStatusType.query.filter_by(name=name).one() @classmethod def get_created(cls): return cls.get_task_status(TaskStatusType.CREATED) @classmethod def get_created_id(cls): return cls.get_created().id @classmethod def get_in_progress(cls): return cls.get_task_status(TaskStatusType.IN_PROGRESS) @classmethod def get_done(cls): return cls.get_task_status(TaskStatusType.DONE) @classmethod def get_awaiting_information(cls): return cls.get_task_status(TaskStatusType.AWAITING_INFORMATION) @classmethod def get_cancelled(cls): return cls.get_task_status(TaskStatusType.CANCELLED) @classmethod def get_declined(cls): return cls.get_task_status(TaskStatusType.DECLINED) @classmethod def get_duplicate(cls): return cls.get_task_status(TaskStatusType.DUPLICATE) id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(255)) is_complete = db.Column(db.Boolean) is_active = db.Column(db.Boolean)
import uuid import pathlib import re from flask import current_app from sqlalchemy.orm import backref from werkzeug.utils import secure_filename from lbrc_flask.database import db from lbrc_flask.security import User as BaseUser, AuditMixin from lbrc_flask.forms.dynamic import Field, FieldGroup from lbrc_flask.model import CommonMixin services_owners = db.Table( "services_owners", db.Column("service_id", db.Integer(), db.ForeignKey("service.id")), db.Column("user_id", db.Integer(), db.ForeignKey("user.id")), ) class User(BaseUser): __table_args__ = {'extend_existing': True} owned_services = db.relationship("Service", lazy="joined", secondary=services_owners, backref='owners') @property def service_owner(self): return len(self.owned_services) > 0
from lbrc_flask.database import db from lbrc_flask.security import User as BaseUser users_studies = db.Table( 'users_studies', db.Column( 'user_id', db.Integer(), db.ForeignKey('user.id'), primary_key=True, ), db.Column( 'study_id', db.Integer(), db.ForeignKey('study.id'), primary_key=True, ), ) class User(BaseUser): __table_args__ = {'extend_existing': True} studies = db.relationship("Study", secondary=users_studies, backref=db.backref("users", lazy="joined")) def __str__(self): return 'user'
number_portion = "" if self.number: number_portion = " ({})".format(self.number) return self.name + number_portion class User(BaseUser): __table_args__ = {'extend_existing': True} site_id = db.Column(db.Integer, db.ForeignKey(Site.id)) site = db.relationship(Site) studies_owners = db.Table( "studies_owners", db.Column("study_id", db.Integer(), db.ForeignKey("study.id")), db.Column("user_id", db.Integer(), db.ForeignKey("user.id")), ) studies_collaborators = db.Table( "studies_collaborators", db.Column("study_id", db.Integer(), db.ForeignKey("study.id")), db.Column("user_id", db.Integer(), db.ForeignKey(User.id)), ) class Study(db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(100)) date_created = db.Column(db.DateTime,