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 Person(SurrogatePK, Model):
__tablename__ = 'person'
PER_PAGE = 50
STATUS_VALID = 1
STATUS_BANNED = 0
TYPE_TIMEOUT = 0
TYPE_WALLET = 1
SEARCH_KEY = ('name', 'tabel_id', 'card', 'hard_id')
MANDATORY_PARAMETERS = ('name', 'tabel_id', 'hard_id')
OPTIONAL_PARAMETERS = ('payment_id', 'card')
name = db.Column(db.Text, nullable=False)
tabel_id = db.Column(db.String(150))
birthday = db.Column(db.Date())
firm_id = ReferenceCol('firm', nullable=False)
firm = relationship('Firm', backref='person')
card = db.Column(db.String(8))
payment_id = db.Column(db.String(20), nullable=False, index=True)
hard_id = db.Column(db.String(128), nullable=False)
creation_date = db.Column(db.DateTime, nullable=False)
status = db.Column(db.Integer, nullable=False, index=True)
wallet_status = db.Column(db.Integer, nullable=False, index=True)
type = db.Column(db.Integer, nullable=False, index=True)
def __init__(self):
self.status = self.STATUS_VALID
self.wallet_status = self.STATUS_VALID
self.type = self.TYPE_TIMEOUT
self.creation_date = date_helper.get_current_date()
self.name = u'Пользователь'
@staticmethod
def delete(person):
try:
db.session.query(PersonEvent).\
filter(PersonEvent.person_id == person.id).delete()
db.session.query(PersonWallet).\
filter(PersonWallet.person_id == person.id).delete()
db.session.delete(person)
db.session.commit()
except Exception, e:
db.session.rollback()
logger.error(e)
return False
return True
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer,
db.Sequence('id', start=1001, increment=1),
primary_key=True)
first_name = db.Column(db.String(100))
last_name = db.Column(db.String(100))
username = db.Column(db.String(80), unique=True)
password = db.Column(db.String(255))
email = db.Column(db.String(120))
role = db.Column(db.String(10))
phone = db.Column(db.String(16))
email_alert = db.Column(db.String(3))
sms_alert = db.Column(db.String(3))
last_login = db.Column(db.DateTime)
login_attempts = db.Column(db.String(2))
def __init__(self,
first_name,
last_name,
username,
password,
email,
role='user',
phone=None):
self.first_name = first_name
self.last_name = last_name
self.username = username
self.password = password
self.email = email
self.role = role
self.phone = phone
def verify_password(self, password):
if current_app.config['LDAP'] is True:
flash('LDAP backend not implemented')
else:
return check_password_hash(self.password, password)
# Flask-Login integration
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return self.id
class Term(SurrogatePK, Model):
__tablename__ = 'term'
id = db.Column(db.Integer, primary_key=True)
hard_id = db.Column(db.Integer, unique=True)
type = db.Column(db.Integer, nullable=False)
name = db.Column(db.String(300), nullable=False)
tz = db.Column(db.String(300), nullable=False)
blacklist = db.Column(db.Integer)
auth = db.Column(db.String(16))
settings_id = db.Column(db.Integer, nullable=False)
status = db.Column(db.Integer, index=True)
class EventingDevice(Model):
__tablename__ = 'eventing_devices'
ed_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
device_id = Column(db.String(64), unique=True)
class User(SurrogatePK, Model):
__tablename__ = 'api_user'
STATUS_ACTIVE = 1
STATUS_BANNED = 0
SCHEME_EGGER = 'egger'
SCHEME_BCK = 'bck'
email = db.Column(db.String(128), nullable=False, index=True)
key = db.Column(db.String(150))
secret = db.Column(db.String(150))
app = db.Column(db.Text, nullable=False)
firm = db.Column(db.Text, nullable=False)
scheme = db.Column(db.String(64), nullable=False)
status = db.Column(db.Integer, index=True)
class Address(Model):
__tablename__ = 'addresses'
address_id = Column(db.Integer,
autoincrement=True,
primary_key=True,
nullable=False)
address = Column(db.String(100), nullable=False)
address2 = Column(db.String(64))
district = Column(db.String(64))
city = Column(db.String(100), nullable=False)
country = Column(db.String(100), nullable=False)
postal_code = Column(db.String(64), nullable=False)
phone = Column(db.String(64))
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'<Address address_id={self.address_id} address={self.address} postal_code={self.postal_code}>'
# Relationships on other tables
users = relationship('User', backref=db.backref('address'))
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 User(UserMixin, SurrogatePK, Model):
"""A user of the app."""
__tablename__ = 'users'
username = Column(db.String(80), unique=True, nullable=False)
email = Column(db.String(80), unique=True, nullable=False)
#: The hashed password
password = Column(db.Binary(128), nullable=True)
created_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
first_name = Column(db.String(30), nullable=True)
last_name = Column(db.String(30), nullable=True)
active = Column(db.Boolean(), default=False)
is_admin = Column(db.Boolean(), default=False)
def __init__(self, username, email, password=None, **kwargs):
"""Create instance."""
db.Model.__init__(self, username=username, email=email, **kwargs)
if password:
self.set_password(password)
else:
self.password = None
def set_password(self, password):
"""Set password."""
self.password = bcrypt.generate_password_hash(password)
def check_password(self, value):
"""Check password."""
return bcrypt.check_password_hash(self.password, value)
@property
def full_name(self):
"""Full user name."""
return '{0} {1}'.format(self.first_name, self.last_name)
def __repr__(self):
"""Represent instance as a unique string."""
return '<User({username!r})>'.format(username=self.username)
class Transformer(Model):
__tablename__ = 'transformers'
transformer_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
feeder = Column(db.String(45), nullable=False)
capacity = Column(db.Integer, 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())
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 Role(SurrogatePK, Model):
"""A role for a user."""
__tablename__ = 'roles'
name = Column(db.String(80), unique=True, nullable=False)
user_id = reference_col('users', nullable=True)
user = relationship('User', backref='roles')
def __init__(self, name, **kwargs):
"""Create instance."""
db.Model.__init__(self, name=name, **kwargs)
def __repr__(self):
"""Represent instance as a unique string."""
return '<Role({name})>'.format(name=self.name)
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 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 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)
active = db.Column(db.Boolean())
confirmed_at = db.Column(db.DateTime())
timezone = db.Column(db.String(25))
steamid = db.Column(db.String(17), unique=True)
roles = db.relationship("Role",
secondary=roles_users,
backref=db.backref("users", lazy="dynamic"))
def __repr__(self):
return "%s - %s" % (self.code, self.name)
class Firm(SurrogatePK, Model):
__tablename__ = 'firm'
name = db.Column(db.String(300), nullable=False)
class OrderItem(db.Model):
"""
Used to store item-level information
"""
order_item_id = db.Column(db.String(32), primary_key=True)
item_number = db.Column(db.String(32))
order_quantity = db.Column(db.String(32))
shipped_quantity = db.Column(db.String(32))
unit = db.Column(db.String(32))
size = db.Column(db.String(32))
brand = db.Column(db.String(32))
description = db.Column(db.String(32))
weight = db.Column(db.String(32))
price = db.Column(db.String(32))
total_price = db.Column(db.String(32))
def __repr__(self):
return f'''{[
self.item_number,
self.order_quantity,
self.shipped_quantity,
self.unit,
self.size,
self.brand,
self.description,
self.weight,
self.price,
self.total_price
]}'''
def set_attributes(self, data):
for key, val in data.items():
if hasattr(self, key):
setattr(self, f'{key}', val)
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)
class Order(db.Model):
account_number = db.Column(db.String(32))
invoice_number = db.Column(db.String(32), primary_key=True)
invoice_term_name = db.Column(db.String(32))
invoice_date = db.Column(db.String(32))
supplier = db.Column(db.String(32))
customer_account_number = db.Column(db.String(32))
vendor = db.Column(db.String(32))
order_items = db.Column(db.String(32))
raw_sold_to_info = db.Column(db.String(32))
invoice_subtotal = db.Column(db.String(32))
def __init__(self, supplier_organization_number, account_number):
self._Page = None
self._Form_dict = None
self.organization_number = supplier_organization_number
self.account_number = account_number
def __repr__(self):
return '<Order {0}>'.format(self.invoice_number)
@property
def Page(self):
return self._Page
@Page.setter
def Page(self, page_obj):
self._Page = page_obj
self._Form_dict = convert_form_to_dict(page_obj.form)
@property
def Form_dict(self):
return self._Form_dict
def add_order_items(self, order_item):
self.order_items.append(order_item)
def set_order_template(self, template_name):
"""
Function to set the order template
"""
# Fetch the required template type
template_data = fetch_json(template_name)
# Fetch the order and date template
self.order_template = template_data.get('mapper').get('order')
self.date_format = template_data.get('date_format')
def extract_keys_using_template(self):
"""
Extract keys from Page object's Form Fields, search template for matches, and return matches with their corresponding indices
"""
order_template = self.order_template
# For each extracted key, search for relevant keys from the json template
matched_keys_raw = {
prefix_dictionary_search(field_key, order_template):
self.Form_dict[field_key]
for field_key, val in self.Form_dict.items() if val is not None
}
# Remove all empty keys
del matched_keys_raw['']
return {
**matched_keys_raw,
**failover(matched_keys_raw, order_template, self._Page)
}
def format_date(self):
"""
Textract may read dates as "YYY MM DD". Reformat to "YYYY-MM-DD" for DB insertion.
If our template provides a date format then use it for conversion.
"""
try:
if self.date_format:
_date = dt.strptime(self.invoice_date, self.date_format)
self.invoice_date = dt.strftime(_date, DB_DATE_FORMAT)
else:
# In case there is no date_format, then simply replace the characters.
self.invoice_date = self.invoice_date.replace(" ", "-")
except:
current_app.logger.warning("Invoice date was not picked.")
def set_order_values(self, page_obj, template_name='sysco.json'):
# Set the order template to the class
self.set_order_template(template_name)
# Set Page object
self.Page = page_obj
# Get Page's Form
searched_form_dict = self.extract_keys_using_template()
# Set attributes using extracted keys
self.set_attributes(searched_form_dict)
# Format Date
self.format_date()
# Perform validation
self.validate()
for k, v in self.__dict__.items():
if k not in ['_Page', '_Form_dict']:
print(k, ':', v)
def set_attributes(self, data):
for key, val in data.items():
if hasattr(self, key):
setattr(self, key, val)
def validate(self):
"""
Function to perform validation
"""
assert self.invoice_number not in (None, 'None', '')
def convert_to_tsv(self):
"""
Used to export Header values as a tsv file. Returns both raw string format and buf
"""
# TO DO: refactor - consider using avro in the future
vals = []
for key in ORDER_HEADER_COLUMN_ORDER:
vals.append(self.__dict__.get(key))
tsv_buf = StringIO()
pd.DataFrame([vals], columns=ORDER_HEADER_COLUMN_ORDER).to_csv(
path_or_buf=tsv_buf, sep='\t', header=True, index=False)
raw_tsv = pd.DataFrame(
[vals], columns=ORDER_HEADER_COLUMN_ORDER).to_csv(path_or_buf=None,
sep='\t',
header=True,
index=False)
return tsv_buf, raw_tsv
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'))
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 Event(SurrogatePK, Model):
__tablename__ = 'event'
name = db.Column(db.String(150), nullable=False)
key = db.Column(db.String(150), nullable=False)