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 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 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 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 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 Sample(BaseModel):
id = db.Column(db.Integer, primary_key=True)
schedule_id = db.Column(db.Integer,
db.ForeignKey('schedule.id'),
unique=True,
default=None)
site_id = db.Column(db.Integer, db.ForeignKey('site.id'), default=0)
upload_id = db.Column(db.Integer, db.ForeignKey('upload.id'), default=None)
active = db.Column(db.Boolean, default=True)
date_collected = db.Column(db.Date)
date_extracted = db.Column(db.Date)
date_analyzed = db.Column(db.Date)
site = db.relationship('Site',
backref=db.backref('samples',
lazy='dynamic',
cascade='all'))
schedule = db.relationship('Schedule',
backref=db.backref('samples',
lazy='dynamic',
cascade='all'))
def __repr__(self):
return '<Sample {}>'.format(self.id)
def json(self):
res = dict()
for field in self._fields():
res[field] = getattr(self, field)
res['qty_values'] = self.values.count()
res['substance_ids'] = []
# Convert the dates to the format we want
if self.date_collected:
res['date_collected'] = self.date_collected.strftime("%Y-%m-%d")
if self.date_extracted:
res['date_extracted'] = self.date_extracted.strftime("%Y-%m-%d")
if self.date_analyzed:
res['date_analyzed'] = self.date_analyzed.strftime("%Y-%m-%d")
for value in self.values:
if value.substance.id not in res['substance_ids']:
res['substance_ids'].append(value.substance.id)
return res
class SiteMapWell(BaseModel):
id = db.Column(db.Integer, primary_key=True)
site_map_id = db.Column(db.Integer, db.ForeignKey('site_map.id'))
site_id = db.Column(db.Integer, db.ForeignKey('site.id'))
well_id = db.Column(db.Integer, db.ForeignKey('well.id'))
xpos = db.Column(db.Integer)
ypos = db.Column(db.Integer)
xpos_fields = db.Column(db.Integer)
ypos_fields = db.Column(db.Integer)
sitemap = db.relationship('SiteMap',
backref=db.backref('wells',
lazy='dynamic',
cascade='all'))
well = db.relationship('Well', lazy='joined')
def __repr__(self):
return '<SiteMapWell {} well:{} x:{} y:{}>'.format(
self.id, self.well.title, self.xpos, self.ypos)
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 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 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 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 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 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 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