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)
