class Planning(db.Model):
"""
A published planning.
This model links between an order sheet and truck availability sheet.
"""
order_sheet_id = db.Column(db.Integer,
db.ForeignKey('order_sheet.id'),
primary_key=True)
truck_sheet_id = db.Column(db.Integer,
db.ForeignKey('truck_sheet.id'),
primary_key=True)
published_on = db.Column(db.DateTime, server_default=func.now())
user_id = db.Column(db.Integer, db.ForeignKey(User.id))
order_sheet = db.relationship('OrderSheet',
backref=db.backref(
'planning',
cascade='all, delete-orphan',
uselist=False))
truck_sheet = db.relationship('TruckSheet',
backref=db.backref(
'planning',
cascade='all, delete-orphan',
uselist=False))
user = db.relationship(User, backref=db.backref('plannings'))
def __init__(self, truck_sheet_id, order_sheet_id, user_id):
# The publishing time is calculated automatically,
# while the other three properties are taken as inputs
self.truck_sheet_id = truck_sheet_id
self.order_sheet_id = order_sheet_id
self.user_id = user_id
class PropertiesMixin(object):
"""
Adds a key-value pair to a model. This is used in both the
TruckProperties and the OrderProperties
"""
key = db.Column(db.String, primary_key=True)
value = db.Column(db.String, nullable=False)
class SheetMixin(object):
"""
Mixin to add the common columns used by both
:class:`backend.models.TruckSheet` and :class`backend.models.OrderSheet`.
"""
query_class = SheetQuery
id = db.Column(db.Integer, primary_key=True)
upload_date = db.Column(db.DateTime, server_default=func.now())
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(40), nullable=False)
_password = db.Column('password',
db.LargeBinary(60),
nullable=False)
role = db.Column(db.String(20), nullable=False, default='view-only')
def __init__(self, username: str, password: str, role: str = 'view-only'):
self.username = username
self.password = password
self.role = role
@db.validates('role')
def validate_role(self, key, value):
if value not in current_app.config['ROLES']:
raise ValueError(f'A user\'s role has to be one of '
f'"administrator", "planner" or "view-only", '
f'it cannot be "{value}"')
return value
@hybrid_property
def password(self):
return self._password
@password.setter
def password(self, value: str):
"""
Hashes and stores the password using bcrypt.
The password is first hashed using sha256,
after which it is base64 encoded.
This is to prevent bcrypt only using the first 72 characters.
:param value: the new password of the user
:type value: str
"""
encoded = b64encode(sha256(value.encode()).digest())
self._password = bcrypt.hashpw(encoded, bcrypt.gensalt())
def check_password(self, password: str):
"""
Checks if the password of the user is correct.
:return: whether the password was correct
:rtype: bool
"""
encoded = b64encode(sha256(password.encode()).digest())
return bcrypt.checkpw(encoded, self.password)
class TruckProperties(PropertiesMixin, db.Model):
"""
Makes a relation from a truck to a key value pair.
This is used to store any dynamic information that needs to be stored
next to the required columns of the :class:`backend.models.Truck` model.
"""
s_number = db.Column(db.Integer,
db.ForeignKey('truck.s_number', ondelete='CASCADE'),
primary_key=True)
# The relationship from truck to truck_properties is set to be usable as
# a Python dictionary object
truck = db.relationship(
'Truck',
backref=db.backref('properties',
collection_class=attribute_mapped_collection('key'),
cascade='all, delete-orphan'))
class Truck(ValidationMixin, db.Model):
"""
A single row in a truck availability sheet.
The columns are all required to create a planning. The non-required columns
are stored in the others relation with
:class:`backend.models.TruckProperties`.
"""
s_number = db.Column(db.Integer, primary_key=True)
sheet_id = db.Column(db.Integer,
db.ForeignKey('truck_sheet.id', ondelete='CASCADE'))
truck_id = db.Column(db.String, nullable=False)
availability = db.Column(db.Boolean, nullable=False)
truck_type = db.Column(db.String, nullable=False)
business_type = db.Column(db.String, nullable=False)
terminal = db.Column(db.String, nullable=False)
hierarchy = db.Column(db.Float, nullable=False)
use_cost = db.Column(db.Float, nullable=False)
date = db.Column(db.Date, nullable=False)
starting_time = db.Column(db.Time, nullable=False)
# The `others` field is for every property that is not required.
others = association_proxy(
'properties',
'value',
creator=lambda k, v: TruckProperties(key=k, value=v))
# The orders assigned to this particular truck
orders = db.relationship('Order', backref='truck')
def __init__(self,
truck_id: str,
availability: bool,
truck_type: str,
business_type: str,
terminal: str,
hierarchy: float,
use_cost: float,
date: datetime.date,
starting_time: datetime.time,
sheet_id: int = None,
**kwargs):
self.sheet_id = sheet_id
self.truck_id = truck_id
self.availability = availability
self.truck_type = truck_type
self.business_type = business_type
self.terminal = terminal
self.hierarchy = hierarchy
self.use_cost = use_cost
self.date = date
self.starting_time = starting_time
self.others = kwargs
def assign_orders(self, orders, departure_times):
"""
Assigns a list of orders to this truck,
departure time has to be included.
:param orders: List of order objects to be assigned to this truck
:type orders: List[:class:`backend.models.Order`]
:param departure_times: List of departure times for the orders.
:type departure_times: List[str]
"""
for order, departure_time in zip(orders, departure_times):
order.truck = self
order.departure_time = departure_time
class Order(ValidationMixin, db.Model):
"""
A single row in an order sheet.
The columns are all required to create a planning. The non-required columns
are stored in the others relation with
:class:`backend.models.OrderProperties`.
`truck_s_number` and `departure_time` can only be set after the creation
of a row.
"""
query_class = OrderQuery
order_number = db.Column(db.Integer, primary_key=True)
sheet_id = db.Column(db.Integer,
db.ForeignKey('order_sheet.id', ondelete='CASCADE'))
inl_terminal = db.Column(db.String, nullable=False)
truck_type = db.Column(db.String, nullable=False)
truck_s_number = db.Column(db.Integer, db.ForeignKey('truck.s_number'))
departure_time = db.Column(db.Time)
hierarchy = db.Column(db.Float, nullable=False)
delivery_deadline = db.Column(db.Time, nullable=False)
driving_time = db.Column(db.Integer, nullable=False)
process_time = db.Column(db.Integer, nullable=False)
others = association_proxy(
'properties',
'value',
creator=lambda k, v: OrderProperties(key=k, value=v))
def __init__(self,
inl_terminal: str,
truck_type: str,
hierarchy: float,
delivery_deadline: dt.time,
driving_time: int,
process_time: int,
sheet_id: int = None,
truck_s_number: int = None,
departure_time: dt.time = None,
**kwargs):
self.id = sheet_id
self.inl_terminal = inl_terminal
self.truck_type = truck_type
self.hierarchy = hierarchy
self.delivery_deadline = delivery_deadline
self.driving_time = driving_time
self.process_time = process_time
self.truck_s_number = truck_s_number
self.departure_time = departure_time
self.others = kwargs
@db.validates('departure_time')
def validate_departure_time(self, key, value):
"""
Validates if the departure time set for this order is valid.
The departure time should be set in between the assigned truck's
starting time and the latest departure time for this order:
self.truck.starting_time <= self.departure_time <= self.latest_dep_time
"""
if value is None:
return None
# Check if departure time is before the
# latest departure time of the order
if value > self.latest_dep_time:
raise ValueError(
f'The latest departure time for this order is '
f'{self.latest_dep_time.strftime("%H:%M")}, the truck cannot '
f'depart at {value.strftime("%H:%M")}.')
# If this object has not been flushed yet, the relation truck cannot
# be found. We should get the truck using the truck id
if self.truck is None:
truck = Truck.query.get_or_404(self.truck_s_number)
else:
truck = self.truck
# Check if departure time is after the starting time of the truck
if value < truck.starting_time:
raise ValueError(
f'The truck\'s starting time is '
f'{truck.starting_time.strftime("%H:%M")}, which is later'
f' than the set departure time {value.strftime("%H:%M")}.')
return value
@db.validates('truck', 'truck_s_number')
def validate_truck(self, key, truck):
"""
Validates if the truck assigned to this order can carry out this order.
"""
if truck is None:
return None
# If `truck` is a key, get the truck associated
if key == 'truck_s_number':
truck = Truck.query.get_or_404(truck)
truck_types = current_app.config['TRUCK_TYPES']
# Check if the truck can carry out the order
if truck_types.index(truck.truck_type) \
< truck_types.index(self.truck_type):
raise ValueError(
f'The truck assigned to this order cannot carry out this order'
f': The truck type is {truck.truck_type}, which cannot carry '
f'out {self.truck_type} orders.')
# Return either the truck or the key
if key == 'truck_s_number':
return truck.s_number
return truck
@hybrid_property
def service_time(self):
"""
Calculates the service time of the order
"""
return 2 * self.driving_time + self.process_time
@hybrid_property
def latest_dep_time(self):
"""
Calculates the latest departure time of the order
"""
time_as_date = dt.datetime.combine(dt.date(1, 1, 1),
self.delivery_deadline)
return (time_as_date - dt.timedelta(minutes=self.driving_time)).time()
@hybrid_property
def end_time(self):
"""
Calculates the end time of the order
"""
time_as_date = dt.datetime.combine(dt.date(1, 1, 1),
self.departure_time)
return (time_as_date + dt.timedelta(minutes=self.service_time)).time()