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()