class FinancialStatus(db.Model, ModelIter):
__tablename__ = 'financial_status'
id = db.Column(SmallInteger, primary_key=True)
label = db.Column(db.String(30, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
class ProjectType(db.Model, ModelIter):
__tablename__ = 'project_types'
id = db.Column(SmallInteger, primary_key=True)
label = db.Column(db.String(30, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
class Transformer(db.Model, ModelIter):
__tablename__ = 'transformers'
id = db.Column(SmallInteger, primary_key=True)
label = db.Column(db.String(30, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
class UserToken(db.Model, ModelIter):
__tablename__ = 'user_tokens'
id = db.Column(BigInteger, primary_key=True)
user_id = db.Column(BigInteger, db.ForeignKey('users.id'), index=True)
token = db.Column(db.String(
64,
collation=configs.DB_COLLATION,
),
unique=True,
nullable=False)
expires = db.Column(db.DateTime(), nullable=False)
target = db.Column(db.String(250, collation=configs.DB_COLLATION),
comment='Target api url where token will be validated',
nullable=False)
user = relationship(User, back_populates='tokens')
def new_token(self, email: str, expires: datetime = None):
while not self.token:
temp_token = random_token(email)
so = self.query.filter_by(token=temp_token).count()
if not so:
self.token = temp_token
self.expires = expires if expires else datetime.utcnow() + timedelta(
hours=4)
class InstallationFollowUp(db.Model, ModelIter):
__tablename__ = 'installation_follow_ups'
fillable = [
'installation_id',
'next_follow_up',
'alert_group_id',
]
id = db.Column(db.Integer, primary_key=True)
installation = relationship(Installation,
uselist=False,
backref='follow_ups',
cascade='all, delete')
next_follow_up = db.Column(db.DateTime)
alert_group = relationship(UserGroup)
comments = relationship(
InstallationFollowUpComment,
primaryjoin=InstallationFollowUpComment.commentable_id == id,
foreign_keys=InstallationFollowUpComment.commentable_id)
alert_group_id = deferred(
db.Column(db.Integer,
db.ForeignKey('user_groups.id'),
index=True,
nullable=False))
installation_id = deferred(
db.Column(db.Integer,
db.ForeignKey('installations.id'),
index=True,
nullable=False))
class InstallationDocument(db.Model, ModelIter):
__tablename__ = 'installation_documents'
allowed_widget = True
fillable = [
'name',
'category',
'object_key',
'installation_id',
]
id = db.Column(db.Integer, primary_key=True)
installation = relationship(Installation, backref='installation_documents')
_name = db.Column('name',
db.String(96, collation=configs.DB_COLLATION),
nullable=False)
category = db.Column(db.String(96, collation=configs.DB_COLLATION),
nullable=False)
object_key = db.Column(db.String(512, collation=configs.DB_COLLATION),
index=True,
nullable=False)
installation_id = deferred(
db.Column(db.Integer, db.ForeignKey('installations.id'), index=True))
@property
def name(self):
return self._name
@name.setter
def name(self, name: str):
self._name = name.upper().strip()
class UserGroup(db.Model, ModelIter):
__tablename__ = 'user_groups'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
users = relationship(User,
secondary=user_user_groups,
backref=db.backref('user_groups'))
class Country(db.Model, ModelIter):
__tablename__ = 'countries'
id = db.Column(SmallInteger, primary_key=True)
name = db.Column(db.String(30, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
abbreviation = db.Column(db.String(2, collation=configs.DB_COLLATION),
nullable=False)
provinces = relationship('Province')
class FinancialEntity(db.Model, ModelIter):
__tablename__ = 'financial_entities'
id = db.Column(SmallInteger, primary_key=True)
name = db.Column(db.String(128, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
primary_phone = db.Column(db.String(10, collation=configs.DB_COLLATION),
nullable=False)
secondary_phone = db.Column(db.String(10, collation=configs.DB_COLLATION),
nullable=True)
class InverterModel(db.Model, ModelIter):
__tablename__ = 'inverter_models'
id = db.Column(SmallInteger, primary_key=True)
label = db.Column(db.String(255, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
details = relationship(
InstallationInverterModel,
backref='inverter_model',
primaryjoin=id == InstallationInverterModel.model_id,
)
class Integration(db.Model, ModelIter):
__tablename__ = 'integrations'
fillable = ['service', 'data']
id = db.Column(db.Integer, primary_key=True)
service = db.Column(db.String(30, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
data = db.Column(MutableList.as_mutable(db.JSON),
comment='A JSON schema of integration service',
nullable=False,
server_default='[]')
class InstallationInverterModel(db.Model, ModelIter):
__tablename__ = 'installations_inverter_models'
id = db.Column(db.Integer, primary_key=True)
installation_id = deferred(
db.Column(db.Integer, db.ForeignKey('installations.id'), index=True))
model_id = deferred(
db.Column(db.Integer, db.ForeignKey('inverter_models.id'), index=True))
quantity = db.Column(db.Integer, nullable=False)
serials = db.Column(
MutableList.as_mutable(db.JSON),
comment='A JSON list of serial numbers, one per quantity',
nullable=False,
server_default='[]') # technically renders quantity useless
class Province(db.Model, ModelIter):
__tablename__ = 'provinces' # AKA states
id = db.Column(db.Integer, primary_key=True)
country = relationship(Country, uselist=False, backref='countries')
name = db.Column(db.String(30, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
__table_args__ = (UniqueConstraint('country_id',
'name',
name='province_country_id'), )
country_id = db.Column(db.Integer,
db.ForeignKey('countries.id'),
index=True)
class User(db.Model, ModelIter):
__tablename__ = 'users'
allowed_widget = True
fillable = ['password', 'email', 'first_name', 'last_name', 'deleted']
id = db.Column(BigInteger, primary_key=True)
email = db.Column(db.String(50, collation=configs.DB_COLLATION),
nullable=False,
unique=True)
password = db.Column(db.String(80, collation=configs.DB_COLLATION),
nullable=True)
first_name = db.Column(db.String(50, collation=configs.DB_COLLATION),
nullable=False,
index=True)
last_name = db.Column(db.String(50, collation=configs.DB_COLLATION),
nullable=False,
index=True)
created_at = db.Column(db.DateTime(),
nullable=False,
default=datetime.utcnow)
deleted = db.Column(db.Boolean,
nullable=False,
server_default='0',
index=True)
roles = relationship('Role',
secondary=user_roles,
lazy='joined',
backref=db.backref('users', lazy='dynamic'))
tokens = relationship('UserToken', back_populates='user')
attributes = relationship('UserAttributes',
back_populates='user',
lazy='joined',
uselist=False)
audit = relationship('Audit')
@hybrid_property
def name(self):
return '{} {}'.format(self.first_name, self.last_name)
def hash_password(self):
self.password = generate_password_hash(str(
self.password).encode('ascii'),
method='sha256')
def password_correct(self, plain_password):
return check_password_hash(self.password, plain_password)
def get_token(self):
exp = datetime.utcnow() + timedelta(minutes=30)
return {
'value':
jwt.encode({
'email': self.email,
'exp': exp
},
configs.SECRET_KEY,
algorithm='HS256').decode('utf-8'),
'expires':
round(exp.timestamp())
}
class UserAttributes(db.Model, ModelIter):
__tablename__ = 'user_attributes'
ua_id = db.Column(BigInteger, primary_key=True)
user_id = db.Column(BigInteger, db.ForeignKey('users.id'), index=True)
user_access = db.Column(db.Text(collation=configs.DB_COLLATION),
comment='A JSON schema of table/rows access',
nullable=False,
default='{}')
user_preferences = db.Column(db.Text(collation=configs.DB_COLLATION),
comment='A JSON schema user preferences',
nullable=False,
default='{}')
user = relationship(User, back_populates='attributes', uselist=False)
@property
def preferences(self):
return json.loads(self.user_preferences)
@property
def access(self):
return json.loads(self.user_access)
class Role(db.Model, ModelIter):
__tablename__ = 'roles'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30, collation=configs.DB_COLLATION), index=True)
permissions = db.Column(db.Text(collation=configs.DB_COLLATION))
@property
def get_permissions(self):
combined_permissions = default_access.copy()
if self.permissions:
for key, userGrants in json.loads(self.permissions).items():
for defaultKey, defaultGrants in default_access.items():
if key == defaultKey:
for grant in defaultGrants:
if grant not in userGrants:
userGrants.append(grant)
combined_permissions.update({key: userGrants})
return combined_permissions
class Audit(db.Model, ModelIter):
__tablename__ = 'audits'
id = db.Column(BigInteger, primary_key=True)
date = db.Column(db.DateTime(),
nullable=False,
index=True,
default=datetime.utcnow)
user_id = db.Column(BigInteger,
db.ForeignKey('users.id'),
index=True,
nullable=True)
ip = db.Column(db.String(15), nullable=False)
endpoint = db.Column(db.String(255, collation=configs.DB_COLLATION),
nullable=False)
method = db.Column(db.String(7, collation=configs.DB_COLLATION),
nullable=False)
headers = db.Column(db.Text(collation=configs.DB_COLLATION))
payload = db.Column(db.Text(collation=configs.DB_COLLATION))
response = db.Column(db.Text(collation=configs.DB_COLLATION))
user = relationship(User, uselist=False)
class CompanyProfile(db.Model, ModelIter):
__tablename__ = 'company_profile'
allowed_widget = True
fillable = ['name', 'address', 'contact', 'logo']
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30, collation=configs.DB_COLLATION),
unique=True,
nullable=False)
address = db.Column(db.Text(collation=configs.DB_COLLATION),
nullable=False)
contact = db.Column(db.String(10, collation=configs.DB_COLLATION),
nullable=False)
logo = db.Column(db.LargeBinary)
settings = db.Column(MutableDict.as_mutable(db.JSON),
comment='A JSON schema for global settings',
nullable=False,
server_default='{}')
class UserMessage(db.Model, ModelIter):
__tablename__ = 'user_messages'
allowed_widget = True
id = db.Column(BigInteger, primary_key=True)
user = relationship(User, uselist=False)
date = db.Column(db.DateTime(), nullable=False, default=datetime.utcnow)
read = db.Column(db.Boolean,
nullable=False,
index=True,
server_default='0')
subject = db.Column(db.String(255, collation=configs.DB_COLLATION),
nullable=False)
message = db.Column(db.Text(collation=configs.DB_COLLATION))
user_id = db.Column(BigInteger,
db.ForeignKey('users.id'),
index=True,
nullable=True)
class Commentable(db.Model, ModelIter):
__tablename__ = 'comments'
allowed_widget = True
id = db.Column(db.Integer, primary_key=True)
user = relationship(User, backref='notes')
comment = db.Column(db.String)
date = db.Column(db.DateTime(), default=datetime.utcnow)
commentable_id = deferred(db.Column(db.Integer, index=True,
nullable=False))
commentable_name = db.Column(db.String(96, collation=configs.DB_COLLATION),
nullable=False)
user_id = deferred(
db.Column(BigInteger,
db.ForeignKey('users.id'),
index=True,
nullable=True))
#__table_args__ = (db.Index('note_model_name_id_idx', commentable_name, commentable_id), )
__mapper_args__ = {
'polymorphic_on': commentable_name,
'polymorphic_identity': 'comment'
}
from sqlalchemy.dialects import sqlite
from dal import db
from dal.shared import ModelIter
from config import random_token, configs
from config.routes import default_access
# sqlite is used for testing and it does not auto increment Big Int since there's no support
BigInteger = db.BigInteger().with_variant(sqlite.INTEGER(), 'sqlite')
admin_access = {'company': '*'}
admin_preferences = {}
user_roles = db.Table(
'user_roles', db.Column('id', BigInteger, primary_key=True),
db.Column('user_id', BigInteger, db.ForeignKey('users.id'), index=True),
db.Column('role_id', BigInteger, db.ForeignKey('roles.id'), index=True))
user_user_groups = db.Table(
'user_user_groups', db.Column('id', BigInteger, primary_key=True),
db.Column('user_id', BigInteger, db.ForeignKey('users.id'), index=True),
db.Column('group_id',
BigInteger,
db.ForeignKey('user_groups.id'),
index=True))
class User(db.Model, ModelIter):
__tablename__ = 'users'
allowed_widget = True
class TrCapacity(db.Model, ModelIter):
__tablename__ = 'tr_capacities'
id = db.Column(SmallInteger, primary_key=True)
label = db.Column(db.Numeric(5, 1), unique=True, nullable=False)
class InstallationStatus(db.Model, ModelIter):
__tablename__ = 'installation_status'
id = db.Column(db.Integer, primary_key=True)
installation = relationship(Installation,
uselist=False,
backref=backref('status', uselist=False),
cascade='all, delete')
design_done = db.Column(db.DateTime()) # Carpeta Movida
proposition_ready = db.Column(db.DateTime()) # Propuesta Lista
proposition_delivered = db.Column(db.DateTime()) # Entrega de Propuesta
approved = db.Column(db.Boolean)
documents_filed = db.Column(db.DateTime()) # Recopilación de Documentos
signed_contract = db.Column(db.DateTime()) # Firma de Contrato
annex_a = db.Column(db.DateTime()) # Anexo A
initial_payment = db.Column(db.DateTime()) # Pago Inicial
structural_installation = db.Column(
db.DateTime()) # Instalacion de Estructura
no_objection_letter = db.Column(db.DateTime()) # Carta de No Objeción
final_installation = db.Column(db.DateTime()) # Instalacion final
annex_b = db.Column(db.DateTime()) # Anexo B
distributor_supervision = db.Column(
db.DateTime()) # Supervisión Distribuidora
in_interconnection_agreement = db.Column(
db.DateTime()) # Entrada Acuerdo de Interconexion
out_interconnection_agreement = db.Column(
db.DateTime()) # Salida Acuerdo de Interconexion
rc_policy = db.Column(db.DateTime()) # Póliza RC
in_metering_agreement = db.Column(
db.DateTime()) # Entrada Acuerdo de Medición Neta
out_metering_agreement = db.Column(
db.DateTime()) # Salida Acuerdo de Medición Neta
metering_letter = db.Column(db.DateTime()) # Carta Medidor
metering_payment = db.Column(db.DateTime()) # Pago Medidor
meter_deployment = db.Column(db.DateTime()) # Cambio Medidor
service_start = db.Column(db.DateTime()) # Encendido
installation_id = deferred(
db.Column(db.Integer,
db.ForeignKey('installations.id'),
index=True,
nullable=False))
@property
def status(self):
if self.approved is False:
return 'Declinado'
elif self.design_done is not None:
if self.proposition_ready is not None or self.proposition_delivered is not None:
if self.approved is True and (
self.documents_filed is not None
or self.signed_contract is not None or self.annex_a
is not None or self.initial_payment is not None):
if self.structural_installation is not None or self.no_objection_letter is not None \
and self.final_installation is not None:
if self.annex_b is not None or self.distributor_supervision is not None or \
self.in_interconnection_agreement is not None or \
self.out_interconnection_agreement is not None or self.rc_policy is not None or \
self.in_metering_agreement is not None or self.out_metering_agreement is not None or \
self.metering_letter is not None or self.metering_payment is not None or \
self.meter_deployment is not None:
if self.service_start is not None:
return 'Encendido'
return 'Distribuidora'
return 'Instalacion'
return 'Cerrado'
return 'Negociación'
return 'Diseño'
return 'Levantamiendo'
class InstallationFinancing(db.Model, ModelIter):
__tablename__ = 'installation_financing'
allowed_widget = True
fillable = [
'installation_id', 'financial_entity_id', 'status_id', 'request_date',
'response_date', 'requested_amount', 'assigned_official',
'official_phone', 'official_email', 'approved_rate',
'retention_percentage', 'insurance', 'number_of_payments',
'payments_amount', 'status_id'
]
id = db.Column(db.Integer, primary_key=True)
installation = relationship(Installation,
uselist=False,
backref=backref('financing', uselist=False),
cascade='all, delete')
financial_entity = relationship(FinancialEntity,
uselist=False,
backref=backref('financing',
uselist=False))
request_date = db.Column(db.DateTime())
response_date = db.Column(db.DateTime())
requested_amount = db.Column(db.Numeric(10, 2), nullable=False)
assigned_official = db.Column(db.String(60,
collation=configs.DB_COLLATION))
official_phone = db.Column(db.String(10, collation=configs.DB_COLLATION))
official_email = db.Column(db.String(50, collation=configs.DB_COLLATION),
nullable=False)
approved_rate = db.Column(db.SmallInteger)
retention_percentage = db.Column(db.SmallInteger)
insurance = db.Column(db.Numeric(10, 2))
number_of_payments = db.Column(db.SmallInteger)
payments_amount = db.Column(db.Numeric(10, 2))
status = relationship(FinancialStatus, uselist=False, backref='financing')
installation_id = deferred(
db.Column(db.Integer,
db.ForeignKey('installations.id'),
index=True,
nullable=False))
financial_entity_id = deferred(
db.Column(db.Integer,
db.ForeignKey('financial_entities.id'),
index=True,
nullable=False))
status_id = deferred(
db.Column(db.Integer,
db.ForeignKey('financial_status.id'),
index=True,
nullable=False))
class Tension(db.Model, ModelIter):
__tablename__ = 'tensions' # AKA electric tension
id = db.Column(SmallInteger, primary_key=True)
label = db.Column(db.SmallInteger, unique=True, nullable=False)
class Installation(db.Model, ModelIter):
__tablename__ = 'installations'
allowed_widget = True
fillable = [
'installed_capacity', # kWp
'egauge_url',
'egauge_serial',
'egauge_mac',
'start_date',
'specific_yield', # kWh/kWp/Year
'project_id',
'sale_type_id',
'price_per_kwp',
'responsible_party',
'setup_summary'
]
id = db.Column(db.Integer, primary_key=True)
# project can have multiple installations because customers may ask to add more equipment after project is done
project = relationship(CustomerProject,
uselist=False,
backref='installations',
cascade='all, delete')
responsible_party = db.Column(db.String(32))
installed_capacity = db.Column(db.Numeric(8, 3), nullable=False)
sale_type = relationship(SaleType, uselist=False, lazy='joined')
price_per_kwp = db.Column(db.Numeric(10, 2), nullable=False)
setup_summary = db.Column(
MutableDict.as_mutable(db.JSON),
comment=
'A JSON schema that allows free form data, i.e. historical consumption data',
server_default=('{'
'"historical_consumption": [],'
'"historical_power": [],'
'"expected_generation": []'
'}'))
panels = relationship(
InstallationPanelModel,
backref='installations',
primaryjoin=id == InstallationPanelModel.installation_id,
lazy='joined')
inverters = relationship(
InstallationInverterModel,
backref='installations',
primaryjoin=id == InstallationInverterModel.installation_id,
lazy='joined')
egauge_url = db.Column(db.String(255, collation=configs.DB_COLLATION))
egauge_serial = db.Column(db.String(255, collation=configs.DB_COLLATION))
egauge_mac = db.Column(MacAddress)
start_date = db.Column(db.DateTime())
specific_yield = db.Column(db.SmallInteger)
@property
def installation_size(self):
if self.installed_capacity < 50:
return 'Pequeño'
elif 51 <= self.installed_capacity <= 200:
return 'Mediano'
elif 201 <= self.installed_capacity <= 500:
return 'Comercial Pequeño'
elif 501 <= self.installed_capacity <= 1000:
return 'Comercial Mediano'
elif 1001 <= self.installed_capacity <= 1500:
return 'Comercial Grande'
else:
return 'Utilidad'
@property
def total_investment(self):
return self.installed_capacity * self.price_per_kwp
@property
def annual_production(self):
return self.installed_capacity * self.specific_yield
sale_type_id = deferred(
db.Column(db.Integer,
db.ForeignKey('sale_types.id'),
index=True,
nullable=False))
project_id = deferred(
db.Column(db.Integer,
db.ForeignKey('customer_projects.id'),
index=True,
nullable=False))
class CustomerProject(db.Model, ModelIter):
__tablename__ = 'customer_projects'
allowed_widget = True
fillable = [
'name',
'address',
'lat',
'long',
'coordinates',
'nic',
'nic_title',
'circuit',
'ct',
'project_type_id',
'customer_id',
'province_id',
'distributor_id',
'rate_id',
'transformer_id',
'tr_capacity_id',
'phase_id',
'tension_id',
]
id = db.Column(db.Integer, primary_key=True)
customer = relationship(Customer,
uselist=False,
backref='customer_projects',
cascade='all, delete')
project_type = relationship(ProjectType, uselist=False, lazy='joined')
name = db.Column(db.String(30, collation=configs.DB_COLLATION),
nullable=False)
address = db.Column(db.Text(collation=configs.DB_COLLATION),
nullable=False)
province = relationship(Province, uselist=False, lazy='joined')
lat = db.Column(db.Float)
long = db.Column(db.Float)
coordinates = composite(Point, lat, long)
distributor = relationship(Distributor, uselist=False, lazy='joined')
nic = db.Column(db.Integer)
nic_title = db.Column(db.String(64, collation=configs.DB_COLLATION))
rate = relationship(Rate, uselist=False, lazy='joined')
circuit = db.Column(db.String(30, collation=configs.DB_COLLATION))
transformer = relationship(Transformer, uselist=False, lazy='joined')
ct = db.Column(db.String(32)) # transformer ID number
capacity = relationship(TrCapacity, uselist=False, lazy='joined')
phase = relationship(Phase, uselist=False, lazy='joined')
tension = relationship(Tension, uselist=False, lazy='joined')
project_type_id = deferred(
db.Column(db.Integer, db.ForeignKey('project_types.id')))
customer_id = deferred(
db.Column(db.Integer,
db.ForeignKey('customers.id'),
index=True,
nullable=False))
province_id = deferred(
db.Column(db.Integer, db.ForeignKey('provinces.id'), nullable=False))
distributor_id = deferred(
db.Column(db.Integer, db.ForeignKey('distributors.id')))
rate_id = deferred(db.Column(db.Integer, db.ForeignKey('rates.id')))
transformer_id = deferred(
db.Column(db.Integer, db.ForeignKey('transformers.id')))
tr_capacity_id = deferred(
db.Column(db.Integer, db.ForeignKey('tr_capacities.id')))
phase_id = deferred(db.Column(db.Integer, db.ForeignKey('phases.id')))
tension_id = deferred(db.Column(db.Integer, db.ForeignKey('tensions.id')))
class Customer(db.Model, ModelIter):
__tablename__ = 'customers'
allowed_widget = True
fillable = [
'first_name',
'last_name',
'identification_number',
'primary_email',
'secondary_email',
'primary_phone',
'secondary_phone',
'address',
'source_project_id',
'province_id',
]
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(30, collation=configs.DB_COLLATION),
nullable=False)
last_name = db.Column(db.String(30, collation=configs.DB_COLLATION),
nullable=False,
index=True)
identification_number = db.Column(
db.String(25, collation=configs.DB_COLLATION),
comment='National ID. i.e. Cedula, License',
unique=True)
primary_email = db.Column(db.String(50, collation=configs.DB_COLLATION),
nullable=False,
unique=True)
secondary_email = db.Column(db.String(50, collation=configs.DB_COLLATION),
nullable=True)
primary_phone = db.Column(db.String(10, collation=configs.DB_COLLATION),
nullable=False,
unique=True)
secondary_phone = db.Column(db.String(10, collation=configs.DB_COLLATION),
nullable=True)
address = db.Column(db.Text(collation=configs.DB_COLLATION),
nullable=False)
source_project = relationship(SourceProject, uselist=False, lazy='joined')
province = relationship(Province, uselist=False, lazy='joined')
created_on = db.Column(db.DateTime(),
nullable=False,
default=datetime.utcnow)
updated_on = db.Column(db.DateTime(),
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
province_id = deferred(
db.Column(db.Integer, db.ForeignKey('provinces.id'), nullable=False))
source_project_id = db.Column(db.Integer,
db.ForeignKey('source_projects.id'))
