class HomeHub(Model):
__tablename__ = 'home_hubs'
home_hub_id = Column(db.Integer,
autoincrement=True,
primary_key=True,
nullable=False)
service_location_id = Column(
db.Integer,
db.ForeignKey('service_locations.service_location_id'),
unique=True,
nullable=False)
market_id = Column(db.Integer,
db.ForeignKey('markets.market_id'),
nullable=False)
is_active = Column(db.Boolean(), default=False, nullable=False)
is_archived = Column(db.Boolean(), default=False, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Methods
def __repr__(self):
return f'<HomeHub home_hub_id={self.home_hub_id} service_location_id={self.service_location_id} created_at={self.created_at}>'
# Relationships
pv = relationship('Pv', backref=db.backref('home_hub'), uselist=False)
class Pv(Model):
__tablename__ = 'pvs'
pv_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
home_hub_id = Column(db.Integer,
db.ForeignKey('home_hubs.home_hub_id'),
unique=True,
nullable=False)
meter_id = Column(db.Integer,
db.ForeignKey('meters.meter_id'),
unique=True,
nullable=False)
q_rated = Column(db.Float, nullable=False)
is_active = Column(db.Boolean(), default=False, nullable=False)
is_archived = Column(db.Boolean(), default=False, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
def __repr__(self):
return f'<Pv pv_id={self.pv_id} home_hub_id={self.home_hub_id} created_at={self.created_at}>'
class MeterInterval(Model):
__tablename__ = 'meter_intervals'
meter_interval_id = Column(db.Integer,
autoincrement=True,
primary_key=True,
nullable=False)
meter_id = Column(db.Integer,
db.ForeignKey('meters.meter_id'),
nullable=False)
rate_id = Column(db.Integer,
db.ForeignKey('rates.rate_id'),
nullable=False)
start_time = Column(TIMESTAMP, nullable=False)
end_time = Column(TIMESTAMP, nullable=False)
e = Column(db.Float, nullable=False)
qmtp = Column(db.Float, nullable=False)
p_bid = Column(db.Float, default=0, nullable=False)
q_bid = Column(db.Float, default=0, nullable=False)
mode = Column(db.Boolean(create_constraint=True),
default=0,
nullable=False)
is_bid = Column(db.Boolean(), default=False, nullable=False)
@staticmethod
def get_interval_coverage(interval_id_list):
'''Takes in list of meter interval ids,
returns list of tuples for start and end times in ISO8601 format'''
selected_intervals = MeterInterval.query.filter(
MeterInterval.meter_interval_id.in_(interval_id_list)).all()
start_end_tuples_list = []
for meter_interval in selected_intervals:
start_end_tuples_list.append(
(meter_interval.start_time, meter_interval.end_time))
return start_end_tuples_list
# Methods
def __repr__(self):
return f'<MeterInterval meter_interval_id={self.meter_interval_id} meter_id={self.meter_id} end_time={self.end_time} e={self.e}>'
class TransformerInterval(Model):
__tablename__ = 'transformer_intervals'
transformer_interval_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
transformer_id = Column(db.Integer,
db.ForeignKey('transformers.transformer_id'),
nullable=False)
import_capacity = Column(db.Float, nullable=False)
export_capacity = Column(db.Float, nullable=False)
q = Column(db.Float, nullable=False)
unresp_load = Column(db.Float, nullable=False)
start_time = Column(TIMESTAMP, nullable=False)
end_time = Column(TIMESTAMP, nullable=False)
# Methods
def __repr__(self):
return f'<TransformerInterval transformer_interval_id={self.transformer_interval_id} transformer_id={self.transformer_id}>'
# Relationships
transformer = relationship('Transformer', backref=db.backref('transformer_intervals'))
class Notification(Model):
__tablename__ = 'notifications'
notification_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
alert_type_id = Column(db.Integer,
db.ForeignKey('alert_types.alert_type_id'),
nullable=False)
email = Column(db.String(255), nullable=False)
is_active = Column(db.Boolean, default=False, nullable=False)
created_by = Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Unique constraint for alert_type_id and email
__table_args__ = (UniqueConstraint('alert_type_id',
'email',
name='_alert_type_id_email_uc'), )
# Methods
@validates('email')
def empty_string_to_null(self, key, value):
# converts empty string to null as field is not nullable
# throws an exeption to keep the db from being populated with empty strings
# email validation is already done on the react side for certain pages
# this is an extra layer preventing unwanted data
value = str(value).strip()
if value == '':
return None
else:
return value
def __repr__(self):
return f'<Notification notification_id={self.notification_id} alert_type_id={self.alert_type_id} is_active={self.is_active}>'
class DeviceEventSource(Model):
__tablename__ = 'device_events'
des_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
event_data = Column(db.JSON)
created_at = Column(TIMESTAMP, server_default=func.now())
ed_id = Column(db.Integer,
db.ForeignKey(EventingDevice.ed_id),
unique=False,
nullable=False)
class Group(Model):
__tablename__ = 'groups'
group_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
role_id = Column(db.Integer,
db.ForeignKey('roles.role_id'),
primary_key=True,
nullable=False)
user_id = Column(db.Integer,
db.ForeignKey('users.id'),
primary_key=True,
nullable=False)
is_active = Column(db.Boolean(), default=False, nullable=False)
is_archived = Column(db.Boolean(), default=False, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Unique constraint for role_id and user_id
__table_args__ = (UniqueConstraint('role_id',
'user_id',
name='_role_user_uc'), )
# Methods
def __repr__(self):
return f'<Group group_id={self.group_id} role_id={self.role_id} user_id={self.user_id}>'
class Channel(Model):
__tablename__ = 'channels'
channel_id = Column(db.Integer,
autoincrement=True,
primary_key=True,
nullable=False)
meter_id = Column(db.Integer,
db.ForeignKey('meters.meter_id'),
nullable=False)
setting = Column(db.Integer, nullable=False)
channel_type = Column(db.Enum(ChannelType), nullable=False)
# Methods
def __repr__(self):
return f'<Channel channel_id={self.channel_id} meter_id={self.meter_id} channel_type={self.channel_type}>'
class Login(UserMixin, Model):
__tablename__ = 'logins'
login_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
user_id = Column(db.Integer,
db.ForeignKey('users.id'),
unique=True,
nullable=False)
username = Column(db.String(50), unique=True, nullable=False)
password_hash = db.Column(db.String(128))
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Methods
def __repr__(self):
return f'<Login login_id={self.login_id} user_id={self.user_id} updated_at={self.updated_at}>'
@property
def password(self):
raise AttributeError('Password is not a readable attribute.')
@password.setter
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def check_password(self, password):
is_correct_password = check_password_hash(self.password_hash, password)
if not is_correct_password:
raise ValueError('Incorrect Password')
return is_correct_password
class HceBids(Model):
__tablename__ = 'hce_bids'
bid_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
start_time = Column(TIMESTAMP, nullable=False)
end_time = Column(TIMESTAMP, nullable=False)
p_bid = Column(db.Float, default=0, nullable=False)
q_bid = Column(db.Float, default=0, nullable=False)
is_supply = Column(db.Boolean(), default=True, nullable=False)
comment = Column(db.String(512), nullable=False)
market_id = Column(db.Integer,
db.ForeignKey('markets.market_id'),
nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
def __repr__(self):
return f'<HceBids bid_id={self.bid_id}>'
class AlertType(Model):
__tablename__ = 'alert_types'
alert_type_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
utility_id = Column(db.Integer,
db.ForeignKey('utilities.utility_id'),
nullable=False)
name = Column(db.Enum(AlertName), nullable=False)
limit = Column(db.Float, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Unique constraint for utility_id and name
__table_args__ = (UniqueConstraint('utility_id',
'name',
name='_utility_name_uc'), )
# Relationships
notifications = relationship('Notification',
backref=db.backref('alert_type'))
alerts = relationship('Alert', backref=db.backref('alert_type'))
# Methods
def __repr__(self):
return f'<AlertType alert_type_id={self.alert_type_id} utility_id={self.utility_id} name={self.name}>'
class ServiceLocation(Model):
__tablename__ = 'service_locations'
service_location_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
alternate_service_location_id = Column(db.String(64), unique=True)
address_id = Column(db.Integer,
db.ForeignKey('addresses.address_id'),
unique=True,
nullable=False)
map_location = Column(db.String(64), nullable=False)
is_active = Column(db.Boolean(), default=False, nullable=False)
is_archived = Column(db.Boolean(), default=False, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
def __repr__(self):
return f'<ServiceLocation service_location_id={self.service_location_id} address_id={self.address_id}>'
# Relationships
home_hub = relationship('HomeHub',
backref=db.backref('service_location'),
uselist=False)
meters = relationship('Meter', backref=db.backref('service_location'))
class Meter(Model):
__tablename__ = 'meters'
# Composite primary key: meter_id, utility_id, and service_location_id
meter_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
utility_id = Column(db.Integer,
db.ForeignKey('utilities.utility_id'),
primary_key=True,
nullable=False)
service_location_id = Column(
db.Integer,
db.ForeignKey('service_locations.service_location_id'),
primary_key=True,
nullable=False)
home_hub_id = Column(db.Integer,
db.ForeignKey('home_hubs.home_hub_id'),
nullable=False)
transformer_id = Column(db.Integer,
db.ForeignKey('transformers.transformer_id'),
nullable=True)
alternate_meter_id = Column(db.String(64), unique=True)
feeder = Column(db.String(45), nullable=False)
substation = Column(db.String(45), nullable=False)
meter_type = Column(db.Enum(MeterType), nullable=False)
is_active = Column(db.Boolean(), default=False, nullable=False)
is_archived = Column(db.Boolean(), default=False, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Methods
def get_interval_count(self, start, end):
'''Takes in start and end ISO8601 times,
returns the interval count (integer) between start / end times, inclusively'''
self_intervals = self.meter_intervals
selected_intervals = []
if start == None:
start = datetime.now() - timedelta(days=1)
if end == None:
end = datetime.now()
for meter_interval in self_intervals:
if meter_interval.start_time >= start and meter_interval.end_time <= end:
selected_intervals.append(meter_interval)
return len(selected_intervals)
def get_rates(self):
'''Returns meter instance's rates as a list'''
rates = []
for meter_interval in self.meter_intervals:
if meter_interval.rate.description not in rates:
rates.append(meter_interval.rate.description)
return rates
def get_channels(self):
'''Returns meter instance's channel settings as a list'''
channels = Meter.query.\
join(Meter.channels). \
all()
return channels
def get_all_intervals(self):
'''Returns all meter instances's intervals in a list'''
intervals_list = []
for meter_interval in self.meter_intervals:
intervals_list.append(meter_interval.meter_interval_id)
return intervals_list
def __repr__(self):
return f'<Meter meter_id={self.meter_id} is_active={self.is_active}>'
# Relationships
channels = relationship('Channel', backref=db.backref('meter'))
meter_intervals = relationship('MeterInterval',
backref=db.backref('meter'))
class Alert(Model):
__tablename__ = 'alerts'
alert_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
alert_type_id = Column(db.Integer,
db.ForeignKey('alert_types.alert_type_id'),
nullable=False)
assigned_to = Column(db.String(255),
db.ForeignKey('users.email'),
nullable=False)
description = Column(db.Text, nullable=False)
status = Column(db.Enum(Status), nullable=False)
context = Column(db.Enum(ContextType), nullable=False)
context_id = Column(db.String(64), nullable=False)
resolution = Column(db.Text, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Methods
def __repr__(self):
return f'<Alert alert_id={self.alert_id} alert_type_id={self.alert_type_id} created_at={self.created_at}>'
def get_filter_type(self):
'''Returns filter type as a string for alert frontend set-up'''
if self.alert_type.name.value == 'Import Capacity' \
or self.alert_type.name.value == 'Export Capacity' \
or self.alert_type.name.value == 'Load Yellow' \
or self.alert_type.name.value == 'Load Red':
return 'Capacity bounds'
if self.alert_type.name.value == 'Price Yellow' \
or self.alert_type.name.value == 'Price Red' \
or self.alert_type.name.value == 'Price':
return 'Price alerts'
if self.alert_type.name.value == 'Telecomm':
return 'Telecomm alerts'
if self.alert_type.name.value == 'Resource':
return 'Resource depletion'
if self.alert_type.name.value == 'Peak Event':
return 'Peak event'
def create_alert_notification_message(self):
'''Returns tuple of alert type name and notification message for alert'''
# Create appropriate notification message
if self.alert_type.name.value == 'Resource':
message = 'TESS is showing "battery" resource depleted.'
elif self.alert_type.name.value == 'Telecomm':
message = 'TESS is observing a telecomm alert.'
elif self.alert_type.name.value == 'Price':
message = f'TESS is observing a price alert at ${self.alert_type.limit}/MW.'
elif self.alert_type.name.value == 'Load Yellow' \
or self.alert_type.name.value == 'Load Red':
message = f'TESS {self.alert_type.name.value} alert: {self.context.value} {self.context_id} is above {self.alert_type.limit}% capacity at {self.created_at}.'
elif self.alert_type.name.value == 'Price Yellow' \
or self.alert_type.name.value == 'Price Red':
message = f'TESS {self.alert_type.name.value} alert: {self.context.value} {self.context_id} is above {self.alert_type.limit}% of the alert price at {self.created_at}.'
elif self.alert_type.name.value == 'Import Capacity':
message = f'TESS System alert: {self.context.value} {self.context_id} is above {self.alert_type.limit} kW import capacity at {self.created_at}.'
elif self.alert_type.name.value == 'Export Capacity':
message = f'TESS System alert: {self.context.value} {self.context_id} is above {self.alert_type.limit} kW export capacity at {self.created_at}.'
return (self.alert_type.name.value, message)
from flask_security import RoleMixin, UserMixin
from web.database import db
roles_users = db.Table(
"roles_users",
db.Column("user_id", db.Integer(), db.ForeignKey("web_users.id")),
db.Column("role_id", db.Integer(), db.ForeignKey("web_roles.id")),
)
class Role(db.Model, RoleMixin):
__tablename__ = "web_roles"
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 "%s - %s" % (self.name, self.description)
class User(db.Model, UserMixin):
__tablename__ = "web_users"
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(255), unique=True)
password = db.Column(db.String(255))
last_login_at = db.Column(db.DateTime())
current_login_at = db.Column(db.DateTime())
last_login_ip = db.Column(db.String(100))
current_login_ip = db.Column(db.String(100))
login_count = db.Column(db.Integer)
class User(UserMixin, Model):
__tablename__ = 'users'
id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
# User email information
email = Column(db.String(255), unique=True, nullable=False)
email_confirmed_at = Column(TIMESTAMP)
# User information
first_name = Column(db.String(64), nullable=False)
last_name = Column(db.String(64), nullable=False)
address_id = Column(db.Integer,
db.ForeignKey('addresses.address_id'),
nullable=False)
utility_id = Column(db.Integer,
db.ForeignKey('utilities.utility_id'),
nullable=False)
is_active = Column(db.Boolean(), default=False, nullable=False)
is_archived = Column(db.Boolean(), default=False, nullable=False)
updated_at = Column(
TIMESTAMP,
nullable=False,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
created_at = Column(TIMESTAMP, server_default=func.now())
# Methods
def get_roles(self):
'''Returns list of user role objects'''
roles = []
for group in self.groups:
roles.append(group.role)
return roles
def does_user_role_exist(self, role_name):
'''Returns true or false whether user has assigned role'''
for group in self.groups:
if group.role.name.value == role_name:
return True
return False
def __repr__(self):
return f'<User id={self.id} email_id={self.email}>'
# Relationships
login = relationship('Login', backref=db.backref('user'), uselist=False)
notifications = relationship('Notification', backref=db.backref('user'))
alerts = relationship('Alert', backref=db.backref('user'))
