class State(BaseModel): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(64), nullable=False) abbreviation = db.Column(db.String(2), nullable=False) def __repr__(self): return '<State {} ({})>'.format(self.title, self.id)
class Well(BaseModel): id = db.Column(db.Integer, primary_key=True) site_id = db.Column(db.Integer, db.ForeignKey('site.id')) frequency_id = db.Column(db.Integer) active = db.Column(db.Boolean, default=True) title = db.Column(db.String(255), default='', index=True) top_of_casing = db.Column(db.Float(10), default=0.0) xpos = db.Column(db.Integer, default=0) ypos = db.Column(db.Integer, default=0) xpos_fields = db.Column(db.Integer, default=0) ypos_fields = db.Column(db.Integer, default=0) sort = db.Column(db.Integer, default=0) sampletechnique = db.Column(db.String(255), default='') material = db.Column(db.String(255)) diameter = db.Column(db.Float(9)) screenlength = db.Column(db.Float(9)) est_depth_to_water = db.Column(db.Float(10)) depth_to_bottom = db.Column(db.Float(9)) purge_water_disposal = db.Column(db.String(255)) latitude = db.Column(db.Float(9)) longitude = db.Column(db.Float(9)) notes = db.Column(db.Text) def __repr__(self): return '<Well {} ({})>'.format(self.title, self.id)
class SiteMap(BaseModel): id = db.Column(db.Integer, primary_key=True) site_id = db.Column(db.Integer, db.ForeignKey('site.id')) title = db.Column(db.String(255)) width = db.Column(db.Integer) height = db.Column(db.Integer) url = db.Column(db.String(255)) scale = db.Column(db.Float(12), default=1.0) site = db.relationship('Site', backref=db.backref('sitemaps', lazy='dynamic', cascade='all')) def __repr__(self): return '<SiteMap {} ({})>'.format(self.title, self.id) def json(self): res = dict() for field in self._fields(): res[field] = getattr(self, field) res['wells'] = [] for well in self.wells: res['wells'].append(well.json()) return res
class Substance(BaseModel): id = db.Column(db.Integer, primary_key=True) substance_group_id = db.Column(db.Integer, db.ForeignKey('substance_group.id'), nullable=False, default=0) active = db.Column(db.Boolean, default=False, index=True) hard_ref = db.Column(db.Integer, default=0) title = db.Column(db.String(255), index=True) abbreviation = db.Column(db.String(255)) cas = db.Column(db.String(255)) cas_sanitized = db.Column(db.String(255), index=True) sort = db.Column(db.Integer, default=0) unit = db.Column(db.String(16)) precision = db.Column(db.Integer, default=2) criteria = db.Column(db.Float(12), default=0.0) field_data = db.Column(db.Boolean, default=False) @validates('cas') def update_slug(self, key, cas): if cas and len(cas): self.cas_sanitized = cas.replace('-', '').replace(' ', '').upper() return cas def __repr__(self): return '<Substance {} ({})>'.format(self.title, self.id)
class Upload(BaseModel): id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer, db.ForeignKey('company.id')) lab_id = db.Column(db.Integer, db.ForeignKey('lab.id')) site_id = db.Column(db.Integer, db.ForeignKey('site.id')) created_at = db.Column(db.DateTime, default=db.func.now()) upload_type = db.Column(db.String(255)) sample_date = db.Column(db.DateTime) filename = db.Column(db.String(255)) url = db.Column(db.String(255)) sent = db.Column(db.Boolean, default=False) lab_upload = db.Column(db.Boolean, default=False) # True if the lab uploaded this file rather than user lab = db.relationship('Lab', backref=db.backref('uploads', lazy='dynamic', cascade='all')) company = db.relationship('Company', backref=db.backref('uploads', lazy='dynamic', cascade='all')) @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query elif g.current_role == 'LabAdmin' or g.current_role == 'LabAssociate': return self.query \ .join(self.company) \ .filter(Company.lab == g.current_lab) \ .order_by(Upload.created_at.desc()) elif g.current_role == 'CompanyAdmin' or g.current_role == 'CompanyAssociate': return self.query \ .join(self.company) \ .join(Company.users) \ .filter(Company.lab == g.current_lab) \ .filter(User.id == current_user.id) \ .order_by(Upload.created_at.desc()) elif g.current_role == 'ClientManager': return self.query \ .join(self.company) \ .join(self.users) \ .filter(Company.lab == g.current_lab) \ .filter(User.id == current_user.id) \ .order_by(Upload.created_at.desc()) elif g.current_role == 'Technician': return self.query \ .join(self.company) \ .join(self.sites) \ .join(Site.users) \ .filter(Company.lab == g.current_lab) \ .filter(User.id == current_user.id) \ .order_by(Upload.created_at.desc()) def __repr__(self): return '<Upload {} ({})>'.format(self.filename, self.id) def json(self): res = dict() for field in self._fields(): res[field] = getattr(self, field) res['created_at'] = self.created_at.isoformat() + 'Z' return res
class Frequency(BaseModel): id = db.Column(db.Integer, primary_key=True) sort = db.Column(db.Integer) title = db.Column(db.String(255)) timediff = db.Column(db.String(64)) peryear = db.Column(db.Integer) unit = db.Column(db.String(64)) default = db.Column(db.Boolean, default=False) def __repr__(self): return '<Frequency {} ({})>'.format(self.title, self.frequency_id)
class Contact(BaseModel): id = db.Column(db.Integer, primary_key=True) active = db.Column(db.Boolean, default=True) title = db.Column(db.String(255)) address = db.Column(db.String(255)) city = db.Column(db.String(128)) state = db.Column(db.String(64)) zip = db.Column(db.String(32)) contact = db.Column(db.String(255)) phone = db.Column(db.String(32)) cell = db.Column(db.String(32)) fax = db.Column(db.String(32)) email = db.Column(db.String(255)) notes = db.Column(db.Text) type = db.Column(db.Text) site_id = db.Column(db.Integer, db.ForeignKey('site.id')) site = db.relationship('Site', backref=db.backref('contacts', lazy='dynamic', cascade='all')) @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query elif g.current_role == 'LabAdmin' or g.current_role == 'LabAssociate': return self.query.filter_by(lab_id=g.current_lab.id) elif g.current_role == 'CompanyAdmin' or g.current_role == 'CompanyAssociate': return self.query \ .join(self.site) \ .join(Site.client) \ .join(Client.company) \ .join(Company.users) \ .filter(self.lab == g.current_lab) \ .filter(User.id == current_user.id) elif g.current_role == 'ClientManager': return self.query \ .join(self.site) \ .join(Site.client) \ .join(Client.users) \ .filter(self.lab == g.current_lab) \ .filter(User.id == current_user.id) elif g.current_role == 'Technician': return self.query \ .join(self.site) \ .join(Site.users) \ .filter(self.lab == g.current_lab) \ .filter(User.id == current_user.id) return self.query.filter(False) def __repr__(self): return '<Contact {} ({})>'.format(self.title, self.id)
class Company(BaseModel): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255), default='') active = db.Column(db.Boolean, default=True) address = db.Column(db.String(255)) city = db.Column(db.String(128)) state = db.Column(db.String(64)) zip = db.Column(db.String(32)) contact = db.Column(db.String(255), default='') phone = db.Column(db.String(32)) cell = db.Column(db.String(32)) fax = db.Column(db.String(32)) email = db.Column(db.String(255)) notes = db.Column(db.Text) lab_id = db.Column(db.Integer, db.ForeignKey('lab.id')) lab = db.relationship('Lab', backref=db.backref('companies', lazy='dynamic', cascade='all')) sites = db.relationship('Site', lazy='dynamic', backref='companies', cascade='all') def __repr__(self): return '<Company {} ({})>'.format(self.title, self.id) @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query.filter_by(lab_id=g.current_lab.id) elif g.current_role == 'LabAdmin' or g.current_role == 'LabAssociate': return self.query.filter_by(lab_id=g.current_lab.id) elif g.current_role == 'CompanyAdmin' or g.current_role == 'CompanyAssociate': return current_user.companies.filter_by(lab_id=g.current_lab.id) elif g.current_role == 'ClientManager' or g.current_role == 'Technician': return current_user.companies return self.query.filter_by(lab_id=g.current_lab.id) def json(self): res = dict() for field in self._fields(): res[field] = getattr(self, field) res['user_ids'] = [] for user in self.users: res['user_ids'].append(user.id) # Set is_deletable to True if the company has no active users res['is_deletable'] = not bool(self.users.filter_by(active=True).count()) return res
class Test(BaseModel): id = db.Column(db.Integer, primary_key=True) state_id = db.Column(db.Integer, db.ForeignKey('state.id')) active = db.Column(db.Boolean, default=True) sort = db.Column(db.Integer, default=0) title = db.Column(db.String(255), default='') abbrev = db.Column(db.String(128)) preservative = db.Column(db.String(255)) filter = db.Column(db.String(255)) field = db.Column(db.Boolean, default=False) substance_ids = db.Column(db.Text) test_material_ids = db.Column(db.Text) test_material_qty = db.Column(db.Text) def __repr__(self): return '<Test {} ({})>'.format(self.title, self.id)
class Role(BaseModel): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), unique=True) description = db.Column(db.String(255)) def __repr__(self): return '<Role {} ({})>'.format(self.name, self.id) @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query elif g.current_role == 'Anonymous': return self.query.filter(False) else: return self.query.filter(self.id >= current_user.role.id)
class SampleValue(BaseModel): id = db.Column(db.Integer, primary_key=True) substance_id = db.Column(db.Integer, db.ForeignKey('substance.id'), default=0) sample_id = db.Column(db.Integer, db.ForeignKey('sample.id'), default=0) well_id = db.Column(db.Integer, db.ForeignKey('well.id'), default=0) upload_id = db.Column(db.Integer, db.ForeignKey('upload.id'), default=None) value = db.Column(db.Float(12), default=0.0) free_product = db.Column(db.Boolean, default=False) non_detect = db.Column(db.Boolean, default=False) less_than = db.Column( db.Float(12), default=0.0) # Save less than values for the sticklers details = db.Column(db.String(20), default=None) new = db.Column(db.Boolean, default=True) paid_level_1 = db.Column(db.Boolean, default=False) paid_level_2 = db.Column(db.Boolean, default=False) sample = db.relationship('Sample', backref=db.backref('values', lazy='dynamic', cascade='all')) substance = db.relationship('Substance', lazy='joined') well = db.relationship('Well', backref=db.backref('sample_values', lazy='dynamic', cascade='all')) def __repr__(self): return '<SampleValue {}>'.format(self.id)
class RoleDeprecated(BaseModel): user_role_id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255), default='') access = db.Column(db.Text) def __repr__(self): return '<RoleDeprecated {} ({})>'.format(self.title, self.user_role_id)
class Sampler(BaseModel): id = db.Column(db.Integer, primary_key=True) active = db.Column(db.Boolean, default=True) title = db.Column(db.String(255)) address = db.Column(db.String(255)) city = db.Column(db.String(128)) state = db.Column(db.String(64)) zip = db.Column(db.String(32)) contact = db.Column(db.String(255), default='') phone = db.Column(db.String(32)) cell = db.Column(db.String(32)) fax = db.Column(db.String(32)) email = db.Column(db.String(255)) notes = db.Column(db.Text) def __repr__(self): return '<Sampler {} ({})>'.format(self.title, self.id)
class Transaction(BaseModel): id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) lab_id = db.Column(db.Integer, db.ForeignKey('lab.id')) site_id = db.Column('site_id', db.Integer, db.ForeignKey('site.id')) company_id = db.Column('company_id', db.Integer, db.ForeignKey('company.id')) created_at = db.Column(db.DateTime, default=db.func.now()) total_amount = db.Column(db.Float(12)) lab_earnings_amount = db.Column(db.Float(12)) samples = db.Column(JSON) stripe_transaction_id = db.Column(db.String(255)) stripe_card_id = db.Column(db.String(255)) cc_last4 = db.Column(db.Integer) user = db.relationship('User', backref=db.backref('transactions', lazy='dynamic')) def __repr__(self): return '<Transaction {} ({})>'.format(self.total_amount, self.id)
class TestMaterial(BaseModel): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255), default='') active = db.Column(db.Boolean, default=True) sort = db.Column(db.Integer, default=0) bottle = db.Column(db.Boolean, default=False) def __repr__(self): return '<TestMaterial {} ({})>'.format(self.title, self.id)
class SubstanceGroup(BaseModel): id = db.Column(db.Integer, primary_key=True) active = db.Column(db.Boolean, nullable=False, default=True, index=True) hard_ref = db.Column(db.Integer, nullable=False, default=0) title = db.Column(db.String(255), nullable=False, default='', index=True) sort = db.Column(db.Integer, nullable=False, default=0) def __repr__(self): return '<SubstanceGroup {} ({})>'.format(self.title, self.id)
class WellImage(BaseModel): id = db.Column(db.Integer, primary_key=True) well_id = db.Column(db.Integer, db.ForeignKey('well.id')) sort = db.Column(db.Integer) last_modified = db.Column(db.Integer) height_md = db.Column(db.Integer) width_md = db.Column(db.Integer) width_sm = db.Column(db.Integer) height_sm = db.Column(db.Integer) url_md = db.Column(db.String(255)) url_lg = db.Column(db.String(255)) filestack_handle = db.Column(db.String(255)) well = db.relationship('Well', backref=db.backref('well_image', lazy='dynamic', cascade='all')) def __repr__(self): return '<WellImage {}>'.format(self.id)
class Criteria(BaseModel): id = db.Column(db.Integer, primary_key=True) state_id = db.Column(db.Integer, db.ForeignKey('state.id')) active = db.Column(db.Boolean, default=True) title = db.Column(db.String(10), nullable=False) values = db.relationship('CriteriaValues', backref=db.backref('criteria', cascade='all')) def __repr__(self): return '<Criteria {} ({})>'.format(self.title, self.id)
class Office(BaseModel): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255)) contact = db.Column(db.String(255), default=None) address = db.Column(db.String(80)) city = db.Column(db.String(255)) state = db.Column(db.String(2)) zip = db.Column(db.String(32)) phone = db.Column(db.String(32), default=None) cell = db.Column(db.String(32), default=None) email = db.Column(db.String(255), default=None) lat = db.Column(db.Float(10)) lng = db.Column(db.Float(10)) def __repr__(self): return '<Office {} ({})>'.format(self.title, self.id)
class Client(BaseModel): id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer, db.ForeignKey('company.id')) name = db.Column(db.String(255)) created_at = db.Column(db.DateTime, default=db.func.now()) company = db.relationship('Company', backref=db.backref('clients', lazy='dynamic', cascade='all')) @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query elif g.current_role == 'LabAdmin' or g.current_role == 'LabAssociate': return self.query \ .join(self.company) \ .filter(Company.lab == g.current_lab) elif g.current_role == 'CompanyAdmin' or g.current_role == 'CompanyAssociate': return self.query \ .join(self.company) \ .join(Company.users) \ .filter(Company.lab == g.current_lab) \ .filter(User.id == current_user.id) elif g.current_role == 'ClientManager': return self.query \ .join(self.company) \ .join(self.users) \ .filter(Company.lab == g.current_lab) \ .filter(User.id == current_user.id) elif g.current_role == 'Technician': return self.query \ .join(self.company) \ .join(self.sites) \ .join(Site.users) \ .filter(Company.lab == g.current_lab) \ .filter(User.id == current_user.id) return self.query.filter(False) def json(self): res = dict() for field in self._fields(): res[field] = getattr(self, field) res['user_ids'] = [] for user in self.users: res['user_ids'].append(user.id) return res def __repr__(self): return '<Client {} ({})>'.format(self.name, self.id)
class Schedule(BaseModel): id = db.Column(db.Integer, primary_key=True) active = db.Column(db.Boolean, default=True) finished = db.Column(db.Boolean, default=False) site_id = db.Column(db.Integer, db.ForeignKey('site.id'), default=0) date = db.Column(db.DateTime) timeofday = db.Column(db.String(255)) starttime = db.Column(db.String(16)) endtime = db.Column(db.String(16)) typeofactivity = db.Column(db.String(255)) release_number = db.Column(db.String(255)) frequency_association = db.Column(db.String(255)) test_ids = db.Column(JSON) tests = db.relationship('Test', secondary=schedule_tests, backref=db.backref('schedules', lazy='dynamic', cascade='all')) site = db.relationship('Site', backref=db.backref('schedules', lazy='dynamic', cascade='all')) schedule_well_tests = db.relationship('ScheduleWellTests', lazy='dynamic', cascade='all') gauged_wells = db.relationship('Well', secondary=gauged_wells, lazy='dynamic', cascade='all') skipped_wells = db.relationship('Well', secondary=skipped_wells, lazy='dynamic', cascade='all') def __repr__(self): return '<Schedule {}>'.format(self.id) @hybrid_property def role_query(self): return self.query def json(self): res = dict() for field in self._fields(): res[field] = getattr(self, field) res['test_ids'] = [] for test in self.tests: res['test_ids'].append(test.id) res['gauged_well_ids'] = [] for gauged_well in self.gauged_wells: res['gauged_well_ids'].append(gauged_well.id) res['skipped_well_ids'] = [] for skipped_well in self.skipped_wells: res['skipped_well_ids'].append(skipped_well.id) return res
class UserDeprecated(BaseModel): user_id = db.Column(db.Integer, primary_key=True) user_role_id = db.Column(db.Integer, db.ForeignKey('role_deprecated.user_role_id'), default=0) username = db.Column(db.String(128), unique=True) email = db.Column(db.String(128), default='') require_password_change = db.Column(db.Boolean, default=True) suspended = db.Column(db.Boolean, default=False) login_timestamp = db.Column(db.Integer, default=0) failed_login_timestamp = db.Column(db.Integer, default=0) login_failures = db.Column(db.Integer, default=0) hash_timestamp = db.Column(db.Integer, default=0) access = db.Column(db.Text) info = db.Column(db.Text) name = db.Column(db.String(255), default=None) company = db.Column(db.String(255), default=None) address = db.Column(db.String(255), default=None) phone = db.Column(db.String(255), default=None) fax = db.Column(db.String(255), default=None) created = db.Column(db.DateTime, default=db.func.now()) id = db.Column(db.Integer) def __repr__(self): return '<UserDeprecated {} ({})>'.format(self.username, self.user_id)
timediff = db.Column(db.String(64)) peryear = db.Column(db.Integer) unit = db.Column(db.String(64)) default = db.Column(db.Boolean, default=False) def __repr__(self): return '<Frequency {} ({})>'.format(self.title, self.frequency_id) well_date = db.Table( 'well_date', db.Column('well_id', db.Integer, db.ForeignKey('well.id'), default=0), db.Column('active', db.Boolean, default=True), db.Column('position', db.Integer), db.Column('gaugeonly', db.Boolean, default=False), db.Column('test_ids', db.String(255)), ) well_date_set = db.Table( 'well_date_set', db.Column('site_id', db.Integer, db.ForeignKey('site.id')), db.Column('position', db.Integer), db.Column('date', db.Date), db.Column('schedule_id', db.Integer, default=None), ) well_options = db.Table( 'well_options', db.Column('id', db.Integer, primary_key=True), db.Column('field', db.String(64)), db.Column('title', db.String(255)),
class User(UserMixin, BaseModel): id = db.Column(db.Integer, primary_key=True) role_id = db.Column(db.Integer, db.ForeignKey('role.id')) lab_id = db.Column(db.Integer, db.ForeignKey('lab.id')) invitee_id = db.Column(db.Integer, db.ForeignKey('user.id')) created_at = db.Column(db.DateTime, default=db.func.now()) name = db.Column(db.String(255)) email = db.Column(db.String(255)) photo_url = db.Column(db.String(255)) password = db.Column(db.String(255)) active = db.Column(db.Boolean(), default=False) pending = db.Column(db.Boolean(), default=True) phone = db.Column(db.String(15)) invite_code = db.Column(db.String(255)) reset_code = db.Column(db.String(255)) role = db.relationship('Role', backref=db.backref('users', lazy='dynamic')) lab = db.relationship('Lab', backref=db.backref('users', lazy='dynamic')) invitee = db.relationship('User', uselist=False, remote_side=[id], backref=db.backref('invites', uselist=False)) companies = db.relationship('Company', secondary=users_companies, lazy='dynamic', backref=db.backref('users', lazy='dynamic', cascade='all')) clients = db.relationship('Client', secondary=users_clients, lazy='dynamic', backref=db.backref('users', lazy='dynamic', cascade='all')) sites = db.relationship('Site', secondary=users_sites, lazy='dynamic', backref=db.backref('users', lazy='dynamic', cascade='all')) def __repr__(self): return '<User {} ({})>'.format(self.email, self.id) @property def public_fields(self): return ['id', 'email', 'name', 'photo_url', 'phone', 'active', 'role_id', 'lab_id', 'company_ids', 'client_ids', 'site_ids'] @property def is_active(self): return self.active @property def company_ids(self): return [company.id for company in self.companies.all()] @property def client_ids(self): return [client.id for client in self.clients.all()] @property def site_ids(self): return [site.id for site in self.sites.all()] def json(self): res = dict() for field in self._fields(): res[field] = getattr(self, field) res['company_ids'] = self.company_ids res['client_ids'] = self.client_ids res['site_ids'] = self.client_ids # Set first name res['firstname'] = firstname(self.name) return res @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query elif g.current_role == 'Anonymous': return self.query.filter_by(lab=g.current_lab, pending=True) return self.query.filter_by(lab=g.current_lab)
class Lab(BaseModel): id = db.Column(db.Integer, primary_key=True) active = db.Column(db.Boolean, default=True) title = db.Column(db.String(255), default='') address = db.Column(db.String(255), default='') city = db.Column(db.String(128), default='') state = db.Column(db.String(64), default='') zip = db.Column(db.String(32), default='') phone = db.Column(db.String(255), default='') contact = db.Column(db.String(255), default='') cell = db.Column(db.String(32)) fax = db.Column(db.String(32)) email = db.Column(db.String(255)) notes = db.Column(db.Text) shipping_company = db.Column(db.String(255), default='') shipping_account = db.Column(db.String(255), default='') shipping_notes = db.Column(db.Text) url = db.Column(db.Text, unique=True) photo_url = db.Column(db.String(255)) def __repr__(self): return '<Lab {} ({})>'.format(self.title, self.id) @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query return self.query.filter_by(id=g.current_lab.id)
criteria_id = db.Column(db.Integer, db.ForeignKey('criteria.id'), nullable=False) substance_id = db.Column(db.Integer, db.ForeignKey('substance.id'), nullable=False) value = db.Column(db.Float(12), nullable=False) class State(BaseModel): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(64), nullable=False) abbreviation = db.Column(db.String(2), nullable=False) def __repr__(self): return '<State {} ({})>'.format(self.title, self.id) substance_state_names = db.Table( 'substance_state_names', db.Column('substance_id', db.Integer, db.ForeignKey('substance.id'), nullable=False), db.Column('state_id', db.Integer, db.ForeignKey('state.id'), nullable=False), db.Column('title', db.String(255), nullable=False), )
class Site(BaseModel): id = db.Column(db.Integer, primary_key=True) state_id = db.Column(db.Integer, db.ForeignKey('state.id')) company_id = db.Column(db.Integer, db.ForeignKey('company.id')) additional_owner_ids = db.Column(JSON) consultant_id = db.Column(db.Integer) additional_consultant_ids = db.Column(JSON) sampler_id = db.Column(db.Integer) lab_id = db.Column(db.Integer, db.ForeignKey('lab.id')) manager_id = db.Column(db.Integer) created_at = db.Column(db.DateTime, default=db.func.now()) active = db.Column(db.Boolean, default=True) title = db.Column(db.String(255), index=True) sort = db.Column(db.Integer, default=0) facility_id = db.Column(db.String(255)) contact = db.Column(db.String(255)) contact_phone = db.Column(db.String(255)) contact_email = db.Column(db.String(255)) notes = db.Column(db.Text) address = db.Column(db.String(255)) city = db.Column(db.String(128)) state = db.Column(db.String(64)) zip = db.Column(db.String(32)) county = db.Column(db.String(64)) latitude = db.Column(db.Float(9), default=0) longitude = db.Column(db.Float(9), default=0) start_sampling_on = db.Column(db.DateTime) history = db.Column(db.Text) background = db.Column(db.Text) summary = db.Column(db.Text) qaqc_duplicates = db.Column(db.Boolean, default=False) qaqc_duplicates_per_samples = db.Column(db.Integer, default=0) qaqc_duplicates_type = db.Column(db.String(16)) qaqc_duplicates_well_ids = db.Column(JSON) qaqc_duplicates_test_ids = db.Column(JSON) qaqc_msmsds = db.Column(db.Boolean, default=False) qaqc_msmsds_per_samples = db.Column(db.Integer, default=0) qaqc_msmsds_type = db.Column(db.String(16)) qaqc_msmsds_well_ids = db.Column(JSON) qaqc_msmsds_test_ids = db.Column(JSON) qaqc_fieldblanks = db.Column(db.Boolean, default=False) qaqc_fieldblanks_per_samples = db.Column(db.Integer, default=0) qaqc_fieldblanks_type = db.Column(db.String(16)) qaqc_fieldblanks_test_ids = db.Column(JSON) qaqc_tripblanks = db.Column(db.Boolean, default=False) qaqc_tripblanks_per_samples = db.Column(db.Integer, default=0) qaqc_tripblanks_type = db.Column(db.String(16)) qaqc_tripblanks_test_ids = db.Column(JSON) qaqc_equipmentblanks = db.Column(db.Boolean, default=False) qaqc_equipmentblanks_per_samples = db.Column(db.Integer, default=0) qaqc_equipmentblanks_type = db.Column(db.String(16)) qaqc_equipmentblanks_test_ids = db.Column(JSON) client_id = db.Column(db.Integer, db.ForeignKey('client.id')) lab = db.relationship('Lab', backref=db.backref('sites', lazy='dynamic', cascade='all')) client = db.relationship('Client', backref=db.backref('sites', lazy='dynamic', cascade='all')) wells = db.relationship('Well', backref='sites', lazy='dynamic', cascade='all') substances = db.relationship('Substance', secondary=substances_sites, lazy='dynamic', backref=db.backref('sites', lazy='dynamic')) data = db.relationship('SiteData', backref=db.backref('sites', lazy='joined', cascade='all')) def __repr__(self): return '<Site {} ({})>'.format(self.title, self.id) @hybrid_property def role_query(self): if g.current_role == 'Admin': return self.query.filter_by(lab_id=g.current_lab.id) elif g.current_role == 'LabAdmin' or g.current_role == 'LabAssociate': return self.query.filter_by(lab_id=g.current_lab.id) elif g.current_role == 'CompanyAdmin' or g.current_role == 'CompanyAssociate': return self.query \ .join(self.client) \ .join(Client.company) \ .join(Company.users) \ .filter(self.lab == g.current_lab) \ .filter(User.id == current_user.id) elif g.current_role == 'ClientManager': return self.query \ .join(self.client) \ .join(Client.users) \ .filter(self.lab == g.current_lab) \ .filter(User.id == current_user.id) elif g.current_role == 'Technician': return self.query \ .join(self.users) \ .filter(self.lab == g.current_lab) \ .filter(User.id == current_user.id) return self.query.filter(False) def json(self): res = dict() for field in self._fields(): res[field] = getattr(self, field) res['user_ids'] = [] for user in self.users: res['user_ids'].append(user.id) # Append wells res['well_ids'] = [] for well in self.wells: res['well_ids'].append(well.id) # Append substances res['substance_ids'] = [] for substance in self.substances: res['substance_ids'].append(substance.id) return res
class SiteData(BaseModel): __tablename__ = 'site_data' id = db.Column(db.Integer, primary_key=True) site_id = db.Column(db.Integer, db.ForeignKey('site.id')) contact_qc = db.Column(db.Boolean(), default=False) date_release_discovered = db.Column(db.DateTime()) confirmed_release_number = db.Column(db.Text()) site_classification = db.Column(db.Integer()) previous_classification = db.Column(db.Integer()) # Type of RBCA Evaluation, Tier 1, Tier 2, or Tier 3 type_of_rbca_evaluation = db.Column(db.String(255)) # Substances released substances_released_gasoline = db.Column(db.Boolean()) substances_released_diesel = db.Column(db.Boolean()) substances_released_ethanol = db.Column(db.Boolean()) substances_released_ethanol_e10 = db.Column(db.Boolean()) substances_released_ethanol_e85 = db.Column(db.Boolean()) substances_released_other = db.Column(db.String(255)) # Has contamination migrated off-site above Tier 1 Residential RBSLs? contamination_migrated_off_site = db.Column(db.Boolean, default=False) impacted_parties_notified = db.Column(db.Boolean, default=False) # Predominant groundwater flow direction groundwater_flow_direction = db.Column(db.String(255)) depth_to_groundwater = db.Column(db.String(255)) # is mobile NAPL present? mobile_napl_present_currently = db.Column(db.Boolean, default=False) mobile_napl_present_previously = db.Column(db.Boolean, default=False) mobile_napl_present_recovered = db.Column(db.Boolean, default=False) mobile_napl_present_recovered_gallons = db.Column(db.String(255)) mobile_napl_present_recovered_date = db.Column(db.String(255)) # is migrating NAPL present? migrating_napl_present_currently = db.Column(db.Boolean, default=False) migrating_napl_actions_taken = db.Column(db.Boolean, default=False) # Since last report soil_remediated_since_last_report = db.Column(db.String(255)) groundwater_remediated_since_last_report = db.Column(db.String(255)) soil_remediated = db.Column(db.String(255)) groundwater_remediated = db.Column(db.String(255)) # Have toxic vapors? vapors_in_confined_space = db.Column(db.Boolean, default=False) # Drinking water supply affected? drinking_water_affected_currently = db.Column(db.Boolean, default=False) drinking_water_affected_previously = db.Column(db.Boolean, default=False) drinking_water_affected_private = db.Column(db.Boolean, default=False) drinking_water_affected_private_num_wells = db.Column(db.Integer()) drinking_water_affected_public = db.Column(db.Boolean, default=False) drinking_water_affected_public_num_wells = db.Column(db.Integer()) drinking_water_affected_municiple = db.Column(db.Boolean, default=False) drinking_water_affected_municiple_num_wells = db.Column(db.Integer()) # Surface water been contaminated? surface_water_contaminated = db.Column(db.Boolean, default=False) # Estimated distance and direction from point of release to nearest point_of_release_to_private_well = db.Column(db.String(255)) point_of_release_to_municipal_well = db.Column(db.String(255)) point_of_release_to_surface_water = db.Column(db.String(255)) # Is site within a wellhead protection zone? wellhead_protection_zone = db.Column(db.Boolean, default=False) # Type of report: report_type = db.Column(db.String(255)) report_type_other = db.Column(db.String(255)) def __repr__(self): return '<SiteData {}>'.format(self.id)