class Department(db.Model):
"""Model for DLI's departments"""
__tablename__ = "department"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), index=True, unique=True)
users = db.relationship(
User,
backref="department",
lazy="dynamic",
)
fields = db.relationship(
Field,
backref="department",
)
def __init__(self, name):
"""Initialize a Department model"""
self.name = name
def __repr__(self):
"""Return a descriptive representation of a Department"""
return '<Department %r>' % self.name
class Field(db.Model):
"""Model for a Field within a Report"""
__tablename__ = "field"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32))
ftype_id = db.Column(db.Integer, db.ForeignKey("field_type.id"))
ftype = db.relationship(FieldType)
department_id = db.Column(db.Integer, db.ForeignKey("department.id"))
data_points = db.relationship(
FieldData,
backref='field',
lazy='dynamic',
)
def __init__(self, name, ftype, department):
"""Initialize a Field model"""
self.name = name
self.ftype = ftype
self.department = department
def __repr__(self):
"""Return a descriptive representation of a Field"""
return '<Field %r>' % self.name
def get_data_for_date(self, ds, pretty=False):
"""Retrieve the FieldData instance for the given date stamp"""
data_point = self.data_points.filter_by(ds=ds).first()
if pretty:
if data_point is not None:
data_point = data_point.pretty_value
else:
data_point = ""
return data_point
@property
def identifier(self):
"""Property to uniquely identify this Field"""
return '{}: {}'.format(self.department.name, self.name)
class Location(db.Model):
"""Model for DLI's physical locations"""
__tablename__ = "location"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), index=True, unique=True)
users = db.relationship(
User,
backref="location",
)
def __init__(self, name):
"""Initialize a Location model"""
self.name = name
def __repr__(self):
"""Return a descriptive representation of a Location"""
return '<Location %r>' % self.name
class Chart(db.Model):
"""Model for a DLI Chart"""
__tablename__ = "chart"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), index=True)
with_table = db.Column(db.Boolean)
owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), index=True)
ctype_id = db.Column(db.Integer, db.ForeignKey("chart_type.id"))
ctype = db.relationship(ChartType, backref="charts")
fields = db.relationship(
Field,
secondary=chart_fields,
backref='charts',
)
tags = db.relationship(
Tag,
secondary=chart_tags,
backref='charts',
)
def __init__(self, name, with_table, user, ctype, fields, tags):
"""Initialize a Chart model"""
self.name = name
self.with_table = with_table
self.user = user
self.ctype = ctype
self.fields = fields
self.tags = tags
def __repr__(self):
"""Return a descriptive representation of a Chart"""
return '<Chart %r>' % self.name
@property
def is_pie_chart(self):
"""Return whether or not this chart is a Pie Chart"""
ChartTypeConstants.reload()
return self.ctype == ChartTypeConstants.PIE
def data_points(self, min_date, max_date=None, ds_format=False):
"""Retrieve the data points needed for this chart"""
if ds_format:
min_ds = min_date
max_ds = max_date
else:
min_ds = min_date.strftime('%Y-%m-%d')
max_ds = datetime.datetime.now().strftime('%Y-%m-%d')
if max_date:
max_ds = max_date.strftime('%Y-%m-%d')
return {
field.identifier: {
str(fdata.ds): str(fdata.value)
for fdata in field.data_points.filter(
FieldData.ds >= min_ds).filter(FieldData.ds <= max_ds)
}
for field in self.fields
}
@property
def tagnames(self):
"""Helper function to get the names of the Report's tags"""
return [tag.name for tag in self.tags]
def generated_js(self):
"""Property that represents this chart generated as C3 JavaScript"""
min_date = datetime.datetime.now()
ChartTypeConstants.reload()
if self.ctype != ChartTypeConstants.PIE:
min_date = min_date - datetime.timedelta(days=14)
generate = 'true'
if self.ctype == ChartTypeConstants.TABLE_ONLY:
generate = 'false'
return """
var data_points = {data_points};
var time_series = {time_series};
var chart_type = "{chart_type}";
var generate = {should_generate};
""".format(
time_series=get_time_series_sequence(min_date),
data_points=self.data_points(min_date),
chart_type=self.ctype.name,
should_generate=generate,
)
class Report(db.Model):
"""Model for a DLI Report"""
__tablename__ = "report"
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), index=True)
name = db.Column(db.String(128))
fields = db.relationship(
Field,
secondary=report_fields,
backref='reports',
)
tags = db.relationship(
Tag,
secondary=report_tags,
backref='reports',
)
def __init__(self, user, name, fields, tags):
"""Initialize a Report model"""
self.user = user
self.name = name
self.fields = fields
self.tags = tags
def __repr__(self):
"""Return a descriptive representation of a Report"""
return '<Report %r>' % self.name
@property
def tagnames(self):
"""Helper function to get the names of the Report's tags"""
return [tag.name for tag in self.tags]
def generate_filename(self, start_ds, end_ds):
"""Generate the filename for the Excel sheet for downloads"""
return "{filename}-{start}-to-{end}.xlsx".format(
filename=self.name,
start=start_ds,
end=end_ds,
)
def collect_dept_data_for_template(self, ds):
"""Collect all of the department data for this Report
Collect department data for this Report on a given day in a format
that is easy to template for render_template functions in Jinja2
"""
dept_data = collections.defaultdict(list)
for field in self.fields:
dept_data[field.department.name].append({
'name':
field.name,
'value':
field.get_data_for_date(ds, pretty=True),
})
return dept_data
def excel_filepath_for_ds(self, start_ds, end_ds):
"""Return the absolute filepath for the Excel sheet on the given ds"""
return os.path.join(
os.path.abspath(os.path.dirname(__file__)),
EXCEL_FILE_DIR,
self.generate_filename(start_ds, end_ds),
)
def excel_file_exists(self, start_ds, end_ds):
"""Determine whether or not an Excel file for this ds exists"""
return os.path.exists(self.excel_filepath_for_ds(start_ds, end_ds))
def create_excel_file(self, start_ds, end_ds):
"""Generate an Excel sheet with this Report's data
Arguments:
start_ds - Date stamp for the start day of Report data to generate
end_ds - Date stamp for the end day of Report data to generate
"""
excel_helper = ExcelSheetHelper(
filepath=self.excel_filepath_for_ds(start_ds, end_ds),
report=self,
date_list=generate_date_list(
datetime.datetime.strptime(start_ds, '%Y-%m-%d'),
datetime.datetime.strptime(end_ds, '%Y-%m-%d'),
step=1,
),
)
excel_helper.write_all(self.collect_dept_fields(start_ds, end_ds))
excel_helper.finalize()
def remove_excel_files(self):
"""Delete the Excel files for this Report"""
basepath = os.path.join(
os.path.abspath(os.path.dirname(__file__)),
EXCEL_FILE_DIR,
)
globpath = os.path.join(basepath, self.name + '*.xlsx')
for filename in glob.glob(globpath):
os.remove(os.path.join(basepath, filename))
def collect_dept_fields(self, start_ds, end_ds):
"""Collect all of the department data for this Report
The best way we can do this is to create a complex dict:
{dept : {field : {ds : value}}}
Arguments:
start_ds - the beginning ds for data to collect
end_ds - the ending ds for data to collect
"""
dept_data = {}
for field in self.fields:
field_data = {
pt.ds: pt.value
for pt in field.data_points.filter(
FieldData.ds >= start_ds).filter(FieldData.ds <= end_ds)
}
if not dept_data.get(field.department.name):
dept_data[field.department.name] = {}
dept_data[field.department.name][field] = field_data
return dept_data
class User(db.Model, UserMixin):
"""Model for users of the site"""
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64))
email = db.Column(db.String(64), index=True, unique=True)
password = db.Column(db.String(128))
is_admin = db.Column(db.Boolean)
location_id = db.Column(db.Integer, db.ForeignKey("location.id"))
dept_id = db.Column(db.Integer, db.ForeignKey("department.id"))
pw_reset = db.relationship(
PasswordReset,
backref="user",
)
reports = db.relationship(
Report,
backref="user",
)
charts = db.relationship(
Chart,
backref="user",
)
error_reports = db.relationship(
ErrorReport,
backref="user",
)
favorite_reports = db.relationship(
Report,
secondary=report_users,
backref='favorite_users',
)
favorite_charts = db.relationship(
Chart,
secondary=chart_users,
backref='favorite_users',
)
def __init__(self, name, email, password, location, department):
"""Initialize a User model"""
UserMixin.__init__(self)
self.name = name
self.email = email
self.password = generate_password_hash(password)
self.location = location
self.department = department
self.is_admin = False
def set_password(self, new_password):
"""Change the user's password to the new password"""
self.password = generate_password_hash(new_password)
def check_password(self, password):
"""Check the user's password against the given value"""
return check_password_hash(self.password, password)
def favorite(self, report):
"""Add a report to the user's list of favorite reports"""
if report not in self.favorite_reports:
self.favorite_reports.append(report)
def unfavorite(self, report):
"""Remove a report from the user's list of favorite reports"""
if report in self.favorite_reports:
self.favorite_reports.remove(report)
def favorite_chart(self, chart):
"""Add a chart to the user's list of favorite charts"""
if chart not in self.favorite_charts:
self.favorite_charts.append(chart)
def unfavorite_chart(self, chart):
"""Remove a chart from the user's list of favorite charts"""
if chart in self.favorite_charts:
self.favorite_charts.remove(chart)
def __repr__(self):
"""Return a descriptive representation of a User"""
return '<User %r>' % self.email
@classmethod
def get_by_email(cls, email):
"""Retrieve a user by their email address"""
return User.query.filter_by(email=email).first()