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 ErrorReport(db.Model):
"""Class representing bug reports and feature requests"""
id = db.Column(db.Integer, primary_key=True)
is_bug = db.Column(db.Boolean)
error_text = db.Column(db.Text)
user_text = db.Column(db.Text)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
sent = db.Column(db.Boolean)
time = db.Column(db.DateTime)
def __init__(self, is_bug, error_text, user_text, user):
"""Initialize the ErrorReport model"""
self.is_bug = is_bug
self.error_text = error_text
self.user_text = user_text
self.user = user
self.sent = False
self.time = datetime.datetime.now()
@property
def email_format(self):
"""Format the ErrorReport neatly for emailing purposes"""
this_type = 'Bug Report' if self.is_bug else 'Feature Request'
error_text = ''
if self.error_text:
error_text = 'Error: {text}'.format(text=self.error_text)
return "{id}. {type} sent by {user} at {time}\n{user_text}\n{error_text}\n".format(
id=self.id,
type=this_type,
user=self.user.name,
time=self.time.strftime('%I:%M%p on %Y-%m-%d'),
user_text=self.user_text,
error_text=error_text,
)
def __repr__(self):
"""Return a human-readable representation of this ErrorReport"""
this_type = 'Bug Report' if self.is_bug else 'Feature Request'
return '<ErrorReport #{id} ({type})>'.format(id=self.id,
type=this_type)
@classmethod
def send_new(cls):
"""Send new ErrorReports to the project developers"""
error_reports = cls.query.filter_by(sent=False).all()
today = datetime.datetime.today().strftime('%Y-%m-%d')
if error_reports:
title = 'Bug Reports and Feature Requests {}'.format(today)
msg = Message(title,
recipients=[os.environ['DLI_REPORTS_DEV_EMAIL']])
msg.body = '\n\n'.join(er.email_format for er in error_reports)
mail.send(msg)
for er in error_reports:
er.sent = True
db.session.commit()
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 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)
"""
import collections
import datetime
import glob
import os
import xlsxwriter
from dli_app import db
EXCEL_FILE_DIR = "excel-files"
report_fields = db.Table(
'report_fields',
db.Column('report_id', db.Integer, db.ForeignKey('report.id')),
db.Column('field_id', db.Integer, db.ForeignKey('field.id')),
)
report_tags = db.Table(
'report_tags',
db.Column('report_id', db.Integer, db.ForeignKey('report.id')),
db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
)
chart_fields = db.Table(
'chart_fields',
db.Column('chart_id', db.Integer, db.ForeignKey('chart.id')),
db.Column('field_id', db.Integer, db.ForeignKey('field.id')),
)
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()
from werkzeug.security import check_password_hash
from werkzeug.security import generate_password_hash
from dli_app import db
from dli_app import login_manager
from dli_app import mail
from dli_app.mod_admin.models import ErrorReport
from dli_app.mod_reports.models import Chart
from dli_app.mod_reports.models import Field
from dli_app.mod_reports.models import Report
report_users = db.Table(
'report_users',
db.Column('report_id', db.Integer, db.ForeignKey('report.id')),
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
)
chart_users = db.Table(
'chart_users',
db.Column('chart_id', db.Integer, db.ForeignKey('chart.id')),
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
)
@login_manager.user_loader
def user_loader(user_id):
"""Unique user loader for the login manager"""
return User.query.get(user_id)