class Market(Model):
__tablename__ = 'markets'
market_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
source = Column(db.Text, nullable=False)
ts = Column(db.Float, nullable=False)
p_max = 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())
# Methods
def __repr__(self):
return f'<Market market_id={self.market_id} p_max={self.p_max} created_at={self.created_at}>'
# Relationships
home_hubs = relationship('HomeHub', backref=db.backref('market'))
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 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 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 PersonEvent(SurrogatePK, Model):
__tablename__ = 'person_event'
STATUS_ACTIVE = 1
STATUS_BANNED = 0
person_id = ReferenceCol('person', nullable=False)
person = relationship('Person', backref='person_event')
term_id = ReferenceCol('term', nullable=False)
term = relationship('Term', backref='person_event')
event_id = ReferenceCol('event', nullable=False)
event = relationship('Event', backref='person_event')
firm_id = ReferenceCol('firm', nullable=False)
firm = relationship('Firm', backref='person_event')
timeout = db.Column(db.Integer, nullable=False)
status = db.Column(db.Integer, nullable=False)
class PersonWallet(SurrogatePK, Model):
__tablename__ = 'corp_wallet'
person_id = ReferenceCol('person', nullable=False)
person = relationship('Person', backref='corp_wallet')
creation_date = db.Column(db.DateTime, nullable=False)
balance = db.Column(db.Integer, nullable=False)
limit = db.Column(db.Integer, nullable=False)
interval = db.Column(db.Integer, nullable=False, index=True)
status = db.Column(db.Integer, nullable=False, index=True)
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 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 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 Rate(Model):
__tablename__ = 'rates'
rate_id = Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
description = Column(db.Text, nullable=False)
# Methods
def __repr__(self):
return f'<Rate rate_id={self.rate_id} description={self.description}>'
# Relationships
meter_intervals = relationship('MeterInterval', backref=db.backref('rate'))
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 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'))
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}>'
# Relationships on other tables
Meter.pv = relationship('Pv', backref=db.backref('meter'), uselist=False)
##########################
### MARSHMALLOW SCHEMA ###
##########################
class PvSchema(SQLAlchemyAutoSchema):
meter = fields.Nested(MeterSchema(), dump_only=True)
class Meta:
model = Pv
load_instance = True
include_fk = True
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'))
# Relationships on other tables
Address.service_location = relationship('ServiceLocation',
backref=db.backref('address'),
uselist=False)
##########################
### MARSHMALLOW SCHEMA ###
##########################
class ServiceLocationSchema(SQLAlchemyAutoSchema):
address = fields.Nested(AddressSchema(), dump_only=True)
class Meta:
model = ServiceLocation
load_instance = True
include_fk = True