class Productcategory(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(40), nullable=False, unique=True)
description = db.Column(db.String(255), nullable=False)
def __repr__(self):
return f'Productcategory <{self.title} | {self.description}>'
class Orgtype(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(40), nullable=False, unique=True)
description = db.Column(db.String(255), nullable=False)
def __repr__(self):
return f'Organization type <{self.title} | {self.description}>'
class Surveypicture(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(30), nullable=False)
picture_filename = db.Column(db.String(15), nullable=False)
# Foreign keys
survey_id = db.Column(db.Integer, db.ForeignKey('survey.id'))
def __repr__(self):
return f'Survey picture <{self.title}>'
class Workorderattachment(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(30), nullable=False, default='Default title')
attachment_filename = db.Column(db.String(15),
nullable=False,
default=secrets.token_hex(8))
# Foreign keys
workorder_id = db.Column(db.Integer, db.ForeignKey('workorder.id'))
def __repr__(self):
return f'Attachment <{self.title}>'
class Comment(db.Model):
id = db.Column(db.Integer, primary_key=True)
content = db.Column(db.Text, nullable=False)
create_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
update_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
# Foreign keys
workorder_id = db.Column(db.Integer, db.ForeignKey('workorder.id'))
def __repr__(self):
return f'<Comment <{self.comment} | Created {self.create_date} | Updated {self.update_date}> '
class Location(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
address = db.Column(db.String(100), nullable=False)
postal_code = db.Column(db.String(10), nullable=False)
city = db.Column(db.String(30), nullable=False)
country = db.Column(db.String(30), nullable=False)
coordinate_lat = db.Column(db.Float)
coordinate_long = db.Column(db.Float)
# Foreign keys
workorder_id = db.Column(db.Integer, db.ForeignKey('workorder.id'))
survey_id = db.Column(db.Integer, db.ForeignKey('survey.id'))
def __repr__(self):
return f'Location <{self.name} | {self.address} | {self.postal_code} | {self.city} | {self.country}>'
class Contactperson(db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(30), nullable=False)
last_name = db.Column(db.String(30), nullable=False)
title = db.Column(db.String(40))
email = db.Column(db.String(120))
phone_number = db.Column(db.String(20))
# Foreign keys
parent_org = db.Column(db.Integer, db.ForeignKey('organization.id'))
def __repr__(self):
return f' ContactPerson <{self.first_name} | {self.last_name} | {self.title} |{self.email} | {self.phone_number}>'
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
product_number = db.Column(db.String(8), nullable=False, unique=True)
product_name = db.Column(db.String(30), nullable=False)
unit_of_material = db.Column(db.String(8), nullable=False, default='pcs')
price = db.Column(db.Float(), nullable=False)
# Foreign keys
product_category = db.Column(db.Integer,
db.ForeignKey('productcategory.id'))
def __repr__(self):
return f'Product <{self.id} | {self.product_number} | {self.product_name} | {self.unit_of_material} | {self.price}>'
class Lineitem(db.Model):
id = db.Column(db.Integer, primary_key=True)
discount = db.Column(db.DECIMAL(3, 2), default=0.00)
quantity = db.Column(db.Integer, nullable=False)
total = db.Column(db.Float, nullable=False)
# Foreign keys
workorder_id = db.Column(db.Integer, db.ForeignKey('workorder.id'))
product_id = db.Column(db.Integer, db.ForeignKey('product.id'))
product = db.relationship('Product', backref='lineitem')
def __repr__(self):
return f'LineItem <Workorder ID {self.workorder_id} | Product ID {self.product_id} | Quantity {self.quantity} | Total {self.total}>'
class User(db.Model, UserMixin):
# When the login token expires in seconds
expires_sec = 1800
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(30), nullable=False)
last_name = db.Column(db.String(30), nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(120), nullable=False)
# Foreign keys
org_id = db.Column(db.Integer, db.ForeignKey('organization.id'))
# Backref to show surveys done by user
surveys = db.relationship('Survey', backref='user', lazy=True)
def set_password(self, password):
"""Set the password of the user"""
self.password = bcrypt.generate_password_hash(password).decode('utf-8')
def check_password(self, password):
"""Check that the submitted password matches to password stored in DB."""
return bcrypt.check_password_hash(self.password, password)
def get_password_token(self, expires_in=600):
"""Create JWT token for password reset email which expire time is specified in expises_in variable.
Returns UTF-8 formatted JWT token to attach reset email"""
return jwt.encode(
{
'reset_password': self.id,
'exp': time() + expires_in
},
app.config['SECRET_KEY'],
algorithm='HS256').decode('utf-8')
@staticmethod
def verify_password_token(token):
"""Check that the submitted token for password reset matches to token created by the application and returns the user information if token is correct"""
try:
id = jwt.decode(token,
app.config['SECRET_KEY'],
algorithms=['HS256'])['reset_password']
except:
return
return User.query.get(id)
def __repr__(self):
return f'User <{self.first_name} | {self.last_name} | {self.email} |>'
class Organization(db.Model):
id = db.Column(db.Integer, primary_key=True)
org_name = db.Column(db.String(100), nullable=False)
org_number = db.Column(db.String(20), nullable=False, unique=True)
address = db.Column(db.String(100), nullable=False)
postal_code = db.Column(db.String(10), nullable=False)
city = db.Column(db.String(30), nullable=False)
country = db.Column(db.String(30), nullable=False)
# One-to-many and many-to-many relations
org_type = db.relationship('Orgtype',
secondary=org_type_rel,
backref='organizations',
lazy=True)
contact_persons = db.relationship('Contactperson',
backref='organization',
lazy=True)
users = db.relationship('User', backref='organization', lazy=True)
# Foreign keys
workorder_id = db.Column(db.Integer, db.ForeignKey('workorder.id'))
def __repr__(self):
return f'Organization <{self.org_name} | {self.org_number} | {self.city} | {self.country}'
class Charger(db.Model):
id = db.Column(db.Integer, primary_key=True)
manufacturer = db.Column(db.String(30), nullable=False)
model = db.Column(db.String(30), nullable=False)
product_no = db.Column(db.String(30), nullable=False, unique=True)
price = db.Column(db.Float, nullable=False)
type_of_outlet = db.Column(db.String(10), nullable=False)
no_of_outlets = db.Column(db.Integer) # Number of outlets in the charger
dc_ac = db.Column(db.String(2), nullable=False)
communication = db.Column(db.String(20), nullable=False)
mounting_wall = db.Column(
db.Boolean, nullable=False) # Can charger be mounted directly to wall?
mounting_ground = db.Column(
db.Boolean, nullable=False) # Can charger be installed to ground?
max_power = db.Column(db.Float, nullable=False)
mcb = db.Column(db.Boolean, nullable=False)
rcd_typea = db.Column(db.Boolean, nullable=False)
rcd_typeb = db.Column(db.Boolean, nullable=False)
automatic_rcd = db.Column(db.Boolean, nullable=False)
pwr_outage_eq = db.Column(db.Boolean, nullable=False)
mid_meter = db.Column(db.Boolean, nullable=False)
mid_readable = db.Column(
db.Boolean,
nullable=False) # Is the MID-meter readable from outside w/o tools
max_cable_d = db.Column(db.Integer, nullable=False)
cable_cu_allowed = db.Column(db.Boolean, nullable=False)
cable_al_allowed = db.Column(db.Boolean, nullable=False)
# Create FlaskForm SelectField choices tuple with format ('id', 'title')
def manufacturer_choices(self):
return (self.manufacturer.lower(), self.manufacturer)
def model_choices(self):
return (self.model.lower(), self.model)
def __repr__(self):
return f'Charger <{self.manufacturer} |{self.model} |{self.product_no} |{self.dc_ac} | {self.type_of_outlet} | {self.no_of_outlets} |{self.communication} | {self.max_power}>'
import jwt
from sitesurvey import db, app, login_manager, bcrypt
@login_manager.user_loader
def load_user(user_id):
return User.query.get(int(user_id))
# Many to many relationship tables
# Organizations and organization association table
org_type_rel = db.Table(
'org_type',
db.Column('org_id', db.Integer, db.ForeignKey('organization.id')),
db.Column('type_id', db.Integer, db.ForeignKey('orgtype.id')))
# Surveys and contact person association table
survey_contact_rel = db.Table(
'survey_contact_person',
db.Column('survey_id', db.Integer, db.ForeignKey('survey.id')),
db.Column('contact_id', db.Integer, db.ForeignKey('contactperson.id')))
class User(db.Model, UserMixin):
# When the login token expires in seconds
expires_sec = 1800
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(30), nullable=False)
class Workorder(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(30), nullable=False)
create_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
update_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
requested_date = db.Column(
db.DateTime) # Requested delivery date when the project is ready
ready_date = db.Column(db.DateTime) # When the project was actually ready
status = db.Column(db.String(30), nullable=False, default='created')
public_chargers = db.Column(db.Integer, nullable=False)
public_installation_location = db.Column(db.Text, nullable=False)
public_charging_power = db.Column(db.Float, nullable=False)
private_chargers = db.Column(db.Integer, nullable=False)
private_installation_location = db.Column(db.Text, nullable=False)
private_charging_power = db.Column(db.Float, nullable=False)
installation_type = db.Column(db.String(10), nullable=False)
total = db.Column(db.Float, nullable=False, default=0.0)
# Backrefs
org = db.relationship('Organization', backref='workorder', lazy=True)
location = db.relationship('Location', backref='workorder', lazy=True)
line_items = db.relationship('Lineitem', backref='workorder', lazy=True)
attachments = db.relationship('Workorderattachment',
backref='workorder',
lazy=True)
comments = db.relationship('Comment', backref='workorder', lazy=True)
def order_total(self):
""" Calculate the order total based on the line_items in the workorder """
order_total = 0
for line in self.line_items:
print(type(line.total))
order_total += float(line.total)
return order_total
def __repr__(self):
return f'Workorder <{self.id} | {self.title} | Created {self.create_date} | Updated {self.update_date} | {self.status}>'
class Survey(db.Model):
id = db.Column(db.Integer, primary_key=True)
create_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
update_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
status = db.Column(db.String(30), nullable=False, default='created')
# Installation related information
installation_method = db.Column(db.String(30), nullable=False)
concrete_foundation = db.Column(db.Boolean)
grid_connection = db.Column(db.Integer)
grid_cable = db.Column(db.String(15))
max_power = db.Column(db.Float)
consumption_fuse = db.Column(db.Integer)
maincabinet_rating = db.Column(db.Integer)
empty_fuses = db.Column(db.Boolean)
number_of_slots = db.Column(db.Integer)
signal_strength = db.Column(db.Float)
installation_location = db.Column(db.String(255))
# Foreign keys to User model
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
workorder_id = db.Column(db.Integer, db.ForeignKey('workorder.id'))
# Table relationships and backrefs
contact_person = db.relationship('Contactperson',
secondary=survey_contact_rel,
backref='surveys',
lazy=True)
pictures = db.relationship('Surveypicture', backref='survey', lazy=True)
location = db.relationship('Location', backref='survey', lazy=True)
def __repr__(self):
return f'Survey <Survey ID {self.id} | Workorder ID {self.workorder_id}>'
from datetime import datetime
import secrets
from sitesurvey import db
from sitesurvey.user.models import survey_contact_rel
from sitesurvey.product.models import Product, Productcategory
# Many-to-Many relationship table(s)
product_category_rel = db.Table(
'product_category',
db.Column('product_id', db.Integer, db.ForeignKey('product.id')),
db.Column('product_category_id', db.Integer,
db.ForeignKey('productcategory.id')))
class Survey(db.Model):
id = db.Column(db.Integer, primary_key=True)
create_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
update_date = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
status = db.Column(db.String(30), nullable=False, default='created')
# Installation related information
installation_method = db.Column(db.String(30), nullable=False)
concrete_foundation = db.Column(db.Boolean)
grid_connection = db.Column(db.Integer)
grid_cable = db.Column(db.String(15))
max_power = db.Column(db.Float)