class WikiPage(db.Model):
"""Model for a page on the wiki"""
__tablename__ = 'wiki_page'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), index=True, unique=True)
content = db.Column(db.Text)
modtime = db.Column(db.String(32))
editor = db.Column(db.String(64))
views = db.Column(db.Integer, index=True)
def __init__(self, name, content):
"""Initiialize a WikiPage model"""
self.name = name
self.content = content
self.modtime = datetime.datetime.now().strftime('%m/%d/%Y %I:%M %p')
if current_user:
self.editor = current_user.name
else:
self.editor = 'DLI'
self.views = 0
def __repr__(self):
"""Return a descriptive representation of a WikiPage"""
return '<WikiPage %r>' % self.name
def with_toc(self):
"""Return the page contents with a Table of Contents header"""
full_text = """
[TOC]
{content}
""".format(content=self.content)
return full_text
class PasswordReset(db.Model):
"""Model for password reset key"""
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
key = db.Column(db.String(64))
expiration = db.Column(db.DateTime)
def __init__(self, user, key=None):
"""Initialize a model"""
if key is None:
key = ''.join(
random.choice(string.ascii_letters + string.digits)
for _ in range(60))
self.user = user
self.key = key
# Add one additional day so the user can potentially reset their password at midnight
self.expiration = datetime.datetime.now() + datetime.timedelta(days=8)
def __repr__(self):
"""Return a descriptive representation of password reset"""
return '<Reset password for user %r>' % self.user
@classmethod
def get_by_key(cls, key):
"""Retrieve a user by the associated password reset key"""
pw_reset = PasswordReset.query.filter_by(key=key).first()
if pw_reset is not None:
return pw_reset.user
else:
return None
class RegisterCandidate(db.Model):
"""Model for users who are allowed to register on the site"""
__tablename__ = 'register_candidate'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(64), index=True, unique=True)
send_time = db.Column(db.String(32))
registration_key = db.Column(db.String(64))
def __init__(self, email, registration_key=None):
"""Initialize a RegisterCandidate model"""
if registration_key is None:
registration_key = ''.join(
random.choice(string.ascii_letters + string.digits)
for _ in range(60))
self.email = email
self.registration_key = registration_key
self.send_time = datetime.datetime.now().strftime('%m/%d/%Y %I:%M %p')
def __repr__(self):
"""Return a descriptive representation of a RegisterCandidate"""
return '<Register Candidate %r>' % self.email
def send_link(self):
"""Send a registration link to this user"""
key = self.registration_key
title = 'Activate your account'
url = 'http://{site}/auth/register/{key}'.format(
site=os.environ['DLI_REPORTS_SITE_URL'],
key=key,
)
recipient = self.email
msg = Message(title, recipients=[recipient])
msg.html = """
<p>Hello</p>
<p>You are invited to register at DLI Reports!
Please go to this link to activate your account:</p>
<a href="{url}">{url}</a>
<p>Thank you!</p>
""".format(url=url)
mail.send(msg)
class FieldType(db.Model):
"""Model for the type of a Field"""
__tablename__ = "field_type"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), index=True)
def __init__(self, name):
"""Initialize a FieldType model"""
self.name = name
def __repr__(self):
"""Return a descriptive representation of a FieldType"""
return '<Field Type %r>' % self.name
def __eq__(self, other):
"""Determine if two FieldTypes are equal"""
return other is not None and self.id == other.id
class ChartType(db.Model):
"""Model for a ChartType (eg. Line, Bar, etc.)"""
__tablename__ = 'chart_type'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), index=True)
def __init__(self, name):
"""Initialize a ChartType model"""
self.name = name
def __repr__(self):
"""Return a descriptive representation of a ChartType"""
return '<Chart Type %r>' % self.name
def __eq__(self, other):
"""Determine if two ChartTypes are equal"""
return other is not None and self.id == other.id
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 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 Tag(db.Model):
"""Model for a Tag associated with a Report"""
__tablename__ = "tag"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), index=True, unique=True)
def __init__(self, name):
"""Initialize a Tag model"""
self.name = name
def __repr__(self):
"""Return a descriptive representation of a Tag"""
return '<Tag %r>' % self.name
@classmethod
def get_or_create(cls, name):
"""Either retrieve a tag or create it if it doesn't exist"""
tag = Tag.query.filter_by(name=name).first()
if tag is None:
tag = Tag(name)
db.session.add(tag)
db.session.commit()
return tag
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 FieldData(db.Model):
"""Model for the actual data stored in a Field"""
__tablename__ = "field_data"
id = db.Column(db.Integer, primary_key=True)
ds = db.Column(db.String(16), index=True)
field_id = db.Column(db.Integer, db.ForeignKey("field.id"))
ivalue = db.Column(db.BigInteger)
dvalue = db.Column(db.Float)
svalue = db.Column(db.String(128))
def __init__(self, ds, field, value):
"""Initialize a FieldData model"""
FieldTypeConstants.reload()
self.ds = ds
self.field = field
# Type checking should have already been done from the form
if self.field.ftype == FieldTypeConstants.CURRENCY:
parts = value.replace(',', '').replace('$', '').split('.')
# Convert the value into cents to avoid any floating-point issues
self.ivalue = int(parts[0]) * 100
if len(parts) == 2:
self.ivalue += int(parts[1])
elif self.field.ftype == FieldTypeConstants.DOUBLE:
self.dvalue = value
elif self.field.ftype == FieldTypeConstants.INTEGER:
self.ivalue = value
elif self.field.ftype == FieldTypeConstants.STRING:
self.svalue = value
elif self.field.ftype == FieldTypeConstants.TIME:
# Convert the value into seconds for convenience
if ':' in value:
parts = value.split(':')
elif '.' in value:
# Some people use '.' to denote minutes/seconds
parts = value.split('.')
else:
# If no : or ., assume the value listed is seconds
parts = ['0', value]
# If the user listed something like ':00', make sure we can still parse
parts = [x or '0' for x in parts]
self.ivalue = int(parts[0]) * 60
if len(parts) == 2:
self.ivalue += int(parts[1])
def __repr__(self):
"""Return a descriptive representation of a FieldData"""
return '<FieldData of %r>' % self.field.name
@property
def value(self):
"""Property to easily retrieve the FieldData's value"""
FieldTypeConstants.reload()
ftype = self.field.ftype
if ftype == FieldTypeConstants.CURRENCY:
return float(self.ivalue) / 100
elif ftype == FieldTypeConstants.DOUBLE:
return self.dvalue
elif ftype == FieldTypeConstants.INTEGER:
return self.ivalue
elif ftype == FieldTypeConstants.STRING:
return self.svalue
elif ftype == FieldTypeConstants.TIME:
return self.ivalue
else:
raise NotImplementedError("ERROR: Type %s not supported!" % ftype)
@property
def pretty_value(self):
"""Property to easily retrieve a human-readable FieldData model"""
FieldTypeConstants.reload()
ftype = self.field.ftype
if ftype == FieldTypeConstants.CURRENCY:
dollars = self.ivalue / 100
cents = self.ivalue % 100
return "${dollars}.{cents:02d}".format(
dollars=dollars,
cents=cents,
)
elif ftype == FieldTypeConstants.DOUBLE:
return self.dvalue
elif ftype == FieldTypeConstants.INTEGER:
return self.ivalue
elif ftype == FieldTypeConstants.STRING:
return self.svalue
elif ftype == FieldTypeConstants.TIME:
mins = self.ivalue / 60
secs = self.ivalue % 60
return "{mins}:{secs:02d}".format(
mins=mins,
secs=secs,
)
else:
raise NotImplementedError("ERROR: Type %s not supported!" % ftype)
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()