class DietPlanItem(db.Model): __tablename__ = 'diet_plan_items' id = db.Column(db.Integer, primary_key=True) diet_plan_id = db.Column(db.Integer, db.ForeignKey('diet_plans.id'), nullable=False) meal_id = db.Column(db.Integer, db.ForeignKey('meals.id'), nullable=False) plan_date = db.Column(db.DateTime(timezone=True), nullable=False) portions = db.Column(db.SmallInteger, default=0, nullable=True) consumed = db.Column(db.Boolean, nullable=True) material_id = db.Column( db.Integer, db.ForeignKey('materials.id'), nullable=True ) # TODO: Allow individual food items to be placed in a dietplan, like an apple a day keeps the doctor away created = db.Column(db.DateTime, default=datetime.datetime.utcnow) dietplan = db.relationship("DietPlan") meal = db.relationship("Meal", back_populates="diet_plans") material = db.relationship("Material") @property def serialize(self): return { 'id': self.id, 'diet_plan_id': self.diet_plan_id, 'plan_date': self.plan_date, 'meal_id': self.meal_id, 'material_id': self.material_id, 'meal': self.meal.title, 'portions': self.portions, 'consumed': self.consumed, }
class Ingredient(db.Model): __tablename__ = 'ingredients' id = db.Column(db.Integer, primary_key=True) quantity = db.Column(db.Float, nullable=False) uom_id = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=False) meal_id = db.Column(db.Integer, db.ForeignKey('meals.id'), nullable=False, index=True) title = db.Column(db.String(80), nullable=False) # gehackte Dosentomaten titleEN = db.Column(db.String(80), nullable=True) # TODO: obsolete processing_part = db.Column(db.String(80), nullable=True) # chopped canned preparation_part = db.Column(db.String(80), nullable=True) # empty base_food_part = db.Column(db.String(80), nullable=True) # Tomatoe created = db.Column(db.DateTime, default=datetime.datetime.utcnow) material_id = db.Column(db.Integer, db.ForeignKey('materials.id'), nullable=True, index=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) material = db.relationship("Material") meal = db.relationship("Meal", back_populates="ingredients") uom = db.relationship("UOM")
class Meal(db.Model): __tablename__ = 'meals' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(80), nullable=False) description = db.Column(db.String(250), nullable=True) portions = db.Column(db.SmallInteger, nullable=True) calories = db.Column(db.String(20), nullable=True) rating = db.Column(db.SmallInteger, nullable=True) image = db.Column(db.String, nullable=True) owner_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) user_group_id = db.Column(db.Integer, db.ForeignKey('user_groups.id')) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) owner = db.relationship("User", back_populates="meals", foreign_keys=[owner_id]) user_group = db.relationship("UserGroup") ingredients = db.relationship("Ingredient", back_populates="meal", cascade="all, delete-orphan") diet_plans = db.relationship("DietPlanItem", back_populates="meal", cascade="all, delete-orphan") @property def serialize(self): return { 'id': self.id, 'title': self.title, 'description': self.description, 'portions': self.portions, 'rating': self.rating, 'image': self.image, 'created': self.created, }
class MaterialUnits(db.Model): __tablename__ = 'material_units' id = db.Column(db.Integer, primary_key=True) material_id = db.Column(db.Integer, db.ForeignKey('materials.id'), nullable=True, index=True) uom_id = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=False) uom_factor_to_base = db.Column( db.Float, nullable=True) # e.g. 1 tbsp (issue) = 20 g (base)
class UserGroupAssociation(db.Model): __tablename__ = 'user_group_association' user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True) user_group_id = db.Column(db.Integer, db.ForeignKey('user_groups.id'), primary_key=True) is_owner = db.Column(db.Boolean, unique=False, default=False) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) group = db.relationship("UserGroup", back_populates="users") user = db.relationship("User", back_populates="groups")
class MaterialForecast(db.Model): __tablename__ = 'material_forecasts' id = db.Column(db.Integer, primary_key=True) inventory_id = db.Column(db.Integer, db.ForeignKey('inventories.id'), index=True) material_id = db.Column(db.Integer, db.ForeignKey('materials.id'), nullable=True, index=True) type = db.Column(db.SmallInteger, nullable=False) # 0 = diet plan # 1 = consumption plan (periodic) # 2 = other plan (one time demand) plan_date_start = db.Column(db.DateTime(timezone=True), nullable=True) plan_date_end = db.Column(db.DateTime(timezone=True), nullable=True) quantity = db.Column(db.Float, nullable=True) quantity_per_day = db.Column(db.Float, nullable=True) quantity_uom = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=True) diet_plan_item_id = db.Column(db.Integer, db.ForeignKey('diet_plan_items.id'), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) inventory = db.relationship("Inventory", foreign_keys=[inventory_id], back_populates="material_forecasts") material = db.relationship("Material") uom = db.relationship("UOM") inventory_item = db.relationship( "InventoryItem", foreign_keys=[inventory_id, material_id], primaryjoin= "and_(MaterialForecast.inventory_id==InventoryItem.inventory_id, MaterialForecast.material_id==InventoryItem.material_id)", backref='forecasts') @property def serialize(self): return { 'id': self.id, 'inventory_id': self.inventory_id, 'material_id': self.material_id, 'plan_date': self.plan_date, 'plan_date_start': self.plan_date_start, 'plan_date_end': self.plan_date_end, 'quantity': self.quantity, 'quantity_uom': self.quantity_uom }
class DietPlan(db.Model): __tablename__ = 'diet_plans' id = db.Column(db.Integer, primary_key=True) creator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) user_group_id = db.Column(db.Integer, db.ForeignKey('user_groups.id'), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) items = db.relationship("DietPlanItem", cascade="all, delete-orphan") creator = db.relationship("User", primaryjoin='User.id == DietPlan.creator_id') user_group = db.relationship("UserGroup")
class FoodComposition(db.Model): __tablename__ = 'food_composition' material_id = db.Column(db.Integer, db.ForeignKey('materials.id'), primary_key=True) nutrient_id = db.Column(db.Integer, db.ForeignKey('nutrients.id'), primary_key=True) per_qty_uom = db.Column(db.String( length=5, convert_unicode=True), db.ForeignKey( 'units_of_measures.uom')) # example per grams of quantity per_qty = db.Column(db.Float) # example per 100 of uom (grams) value = db.Column(db.Float) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) uom = db.relationship("UOM")
class State(db.Model): __tablename__ = 'states' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(60), nullable=True) country_name = db.Column(db.String(60), db.ForeignKey('countries.name'), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow)
class ShoppingOrderItem(db.Model): __tablename__ = 'shopping_order_items' id = db.Column(db.Integer, primary_key=True) inventory_id = db.Column(db.Integer, db.ForeignKey('inventories.id')) material_id = db.Column(db.Integer, db.ForeignKey('materials.id'), nullable=True, index=True) shopping_order_id = db.Column(db.Integer, db.ForeignKey('shopping_orders.id')) title = db.Column(db.String(80), nullable=True) # TODO: obsolete uom_stock_id = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=True) quantity_required = db.Column(db.Float, default=0, nullable=True) quantity_purchased = db.Column(db.Float, default=0, nullable=True) in_basket = db.Column(db.Boolean, default=False) in_basket_time = db.Column(db.DateTime(timezone=True), nullable=True) in_basket_geo_lon = db.Column(db.Float, nullable=True) in_basket_geo_lat = db.Column(db.Float, nullable=True) sort_order = db.Column(db.SmallInteger, nullable=True) item_photo = db.Column(db.String, nullable=True) barcode_photo = db.Column(db.String, nullable=True) ingredients_photo = db.Column(db.String, nullable=True) trade_item_id = db.Column(db.Integer, db.ForeignKey('trade_items.id'), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) material = db.relationship("Material") shopping_order = db.relationship("ShoppingOrder") shopping_order = db.relationship( "ShoppingOrder", foreign_keys=[shopping_order_id], primaryjoin="ShoppingOrderItem.shopping_order_id==ShoppingOrder.id", backref=db.backref("shopping_order_items", cascade="all, delete-orphan")) trade_item = db.relationship("TradeItem") inventory_item = db.relationship( "InventoryItem", foreign_keys=[inventory_id, material_id], primaryjoin= "and_(ShoppingOrderItem.inventory_id==InventoryItem.inventory_id, ShoppingOrderItem.material_id==InventoryItem.material_id)", backref='shopping_order_items')
class FoodPreparationType(db.Model): __tablename__ = 'food_preparation_type' id = db.Column(db.Integer, primary_key=True) bls_code_part = db.Column(db.String(1), nullable=True) food_maingroup_id = db.Column(db.Integer, db.ForeignKey('food_maingroup.id'), nullable=True) titleEN = db.Column(db.String(80), nullable=False) titleDE = db.Column(db.String(80), nullable=False)
class FoodProcessingType(db.Model): __tablename__ = 'food_processing_type' id = db.Column(db.Integer, primary_key=True) bls_code_part = db.Column(db.String(1), nullable=True) food_subgroup_id = db.Column( db.Integer, db.ForeignKey('food_subgroup.id'), nullable=True) # Some foods to have specific processing types titleEN = db.Column(db.String(80), nullable=False) titleDE = db.Column(db.String(80), nullable=False) created = db.Column(db.DateTime, default=datetime.datetime.utcnow)
class FoodSubGroup(db.Model): __tablename__ = 'food_subgroup' id = db.Column(db.Integer, primary_key=True) titleEN = db.Column(db.String(80), nullable=False) titleDE = db.Column(db.String(80), nullable=True) food_maingroup_id = db.Column(db.Integer, db.ForeignKey('food_maingroup.id'), nullable=False) bls_code_part = db.Column(db.String(2), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow)
class Nutrient(db.Model): __tablename__ = 'nutrients' id = db.Column(db.Integer, primary_key=True) value_uom = db.Column(db.String(5), db.ForeignKey( 'units_of_measures.uom')) # example calories, or milligrams titleEN = db.Column(db.String(80), nullable=False) titleDE = db.Column(db.String(80), nullable=True) # TODO: obsolete title = db.Column(db.String(80), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) uom = db.relationship("UOM")
class Inventory(db.Model): __tablename__ = 'inventories' id = db.Column(db.Integer, primary_key=True) creator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) user_group_id = db.Column(db.Integer, db.ForeignKey('user_groups.id'), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) creator = db.relationship("User", back_populates="inventories", primaryjoin='User.id == Inventory.creator_id') material_forecasts = db.relationship("MaterialForecast", back_populates="inventory", cascade="all, delete-orphan") user_group = db.relationship("UserGroup", foreign_keys=[user_group_id]) items = db.relationship("InventoryItem", back_populates="inventory", cascade="all, delete-orphan")
class ShoppingOrder(db.Model): __tablename__ = 'shopping_orders' id = db.Column(db.Integer, primary_key=True) type = db.Column(db.SmallInteger, nullable=False) # 1: shopping list, 2: purchase order plan_forecast_days = db.Column(db.SmallInteger, nullable=False) status = db.Column(db.SmallInteger, nullable=False, default=1) # 1: open, 2: closed closed = db.Column(db.DateTime(timezone=True), nullable=True) place_id = db.Column(db.Integer, db.ForeignKey('places.id'), nullable=True) receipt_photo = db.Column(db.String, nullable=True) creator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) user_group_id = db.Column(db.Integer, db.ForeignKey('user_groups.id'), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) creator = db.relationship( "User", back_populates="shopping_orders", primaryjoin='User.id == ShoppingOrder.creator_id')
class InventoryItem(db.Model): __tablename__ = 'inventory_items' id = db.Column( db.Integer, primary_key=True ) # TODO: Each item is per definition a SKU (Stock keeping unit), consider renaming inventory_id = db.Column(db.Integer, db.ForeignKey('inventories.id'), index=True) material_id = db.Column(db.Integer, db.ForeignKey('materials.id'), nullable=True, index=True) titleEN = db.Column(db.String(160), nullable=True) title = db.Column(db.String(160), nullable=True) uom_stock_id = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=True) uom_base_id = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=False) quantity_stock = db.Column(db.Float, default=0, nullable=True) quantity_stock_user = db.Column(db.Float, nullable=True) quantity_base = db.Column(db.Float, default=0, nullable=True) quantity_base_user = db.Column( db.Float, nullable=True ) # save the quantity entered by the user to recover it from auto toggle setting quantity_conversion_factor = db.Column( db.Float, nullable=True) # to calculate issue units from stock units level = db.Column( db.Float, default=0, nullable=True) # TODO: should be renamed to stock (on hand) re_order_level = db.Column(db.Integer, default=0, nullable=True) re_order_quantity = db.Column(db.Integer, default=0, nullable=True) ignore_forecast = db.Column( db.Boolean, unique=False, default=False ) # Any forecast is ignored so the status will calculate to 0: No Need # type: # 0 = no regular consumption # 1 = every day # 2 = once a week # 3 = once a month # 4 = once a year cp_type = db.Column(db.SmallInteger, default=0, nullable=False) cp_quantity = db.Column(db.Float, default=0, nullable=True) cp_plan_date = db.Column(db.DateTime(timezone=True), nullable=True) # TODO: obsolete cp_plan_date_start = db.Column(db.DateTime(timezone=True), nullable=True) cp_plan_date_end = db.Column(db.DateTime(timezone=True), nullable=True) cp_period = db.Column(db.SmallInteger, nullable=True) # 0 = daily # 1 = weekly # 2 = monthly # 3 = yearly cp_weekday = db.Column(db.SmallInteger, nullable=True) cp_day_in_month = db.Column(db.SmallInteger, nullable=True) cp_end_date = db.Column(db.DateTime(timezone=True), nullable=True) # TODO: depreciated op_plan_date_start = db.Column(db.DateTime(timezone=True), nullable=True) op_plan_date_end = db.Column(db.DateTime(timezone=True), nullable=True) op_quantity = db.Column(db.Float, default=0, nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) inventory = db.relationship("Inventory", foreign_keys=[inventory_id], back_populates="items") material = db.relationship("Material") uom_base = db.relationship("UOM", foreign_keys=[uom_base_id]) uom_stock = db.relationship("UOM", foreign_keys=[uom_stock_id])
class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) email = db.Column(db.String(120), nullable=False, unique=True) picture = db.Column(db.String(250), nullable=True) provider = db.Column(db.String(20), nullable=True) provider_id = db.Column(db.String(120), nullable=True) active = db.Column(db.Boolean(), default=True) confirmed_at = db.Column(db.DateTime()) password_hash = db.Column(db.String(255)) language = db.Column(db.String(6), nullable=False) default_diet_plan_id = db.Column( db.Integer, db.ForeignKey('diet_plans.id'), nullable=True, unique=True ) # TODO: fix this when multiple users share one diet_plan and inventory default_inventory_id = db.Column(db.Integer, db.ForeignKey('inventories.id'), nullable=True, unique=True) default_portions = db.Column(db.SmallInteger, nullable=True) street = db.Column(db.String(120), nullable=True) city = db.Column(db.String(120), nullable=True) state = db.Column(db.String(120), nullable=True) zip = db.Column(db.String(120), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) groups = db.relationship("UserGroupAssociation", back_populates="user") meals = db.relationship("Meal", cascade="all, delete-orphan", back_populates="owner") inventories = db.relationship( "Inventory", primaryjoin='User.id == Inventory.creator_id', back_populates="creator", cascade="all, delete-orphan") diet_plans = db.relationship("DietPlan", primaryjoin='User.id == DietPlan.creator_id', cascade="all, delete-orphan", back_populates="creator") default_diet_plan = db.relationship( "DietPlan", primaryjoin='User.default_diet_plan_id == DietPlan.id', cascade="all, delete-orphan", single_parent=True) default_inventory = db.relationship( "Inventory", primaryjoin='User.default_inventory_id == Inventory.id', cascade="all, delete-orphan", single_parent=True) shopping_orders = db.relationship("ShoppingOrder", cascade="all, delete-orphan") def hash_password(self, password): self.password_hash = pwd_context.encrypt(password) def verify_password(self, password): return pwd_context.verify(password, self.password_hash) def generate_auth_token(self, expiration): s = Serializer(secret_key, expires_in=expiration) return s.dumps({'id': self.id}) # defines how to print objects. Is good for debugging def __repr__(self): return '<User {}>'.format(self.name) @property def is_active(self): return True @property def is_authenticated(self): return True @property def is_anonymous(self): return False def get_id(self): return str(self.id) @property def serialize(self): # Returns object data in easily serializable format return { 'user_id': self.id, 'name': self.name, 'email': self.email, 'picture': self.picture, 'provider': self.provider, 'active': self.active, 'language': self.language, 'default_diet_plan_id': self.default_diet_plan_id, 'default_inventory_id': self.default_inventory_id, 'default_portions': self.default_portions, 'street': self.street, 'city': self.city, 'state': self.state, 'zip': self.zip, }
class Material(db.Model): __tablename__ = 'materials' id = db.Column(db.Integer, primary_key=True, index=True) private = db.Column( db.Boolean, unique=False, default=False ) # user specific food that is not refered to a NDB or similar user_group_id = db.Column( db.Integer, db.ForeignKey('user_groups.id'), nullable=True ) # only required for private food items, for example home made foods title = db.Column(db.String(160), nullable=True) language_code = db.Column(db.String(8), nullable=False) titleEN = db.Column(db.String(160), nullable=True) uom_base_id = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=False) # Physical, e.g. g (Sugar) uom_stock_id = db.Column( db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=True ) # One updated on an inventory, updated here for future inventories uom_issue_id = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=True) # tbsp (4 table spoons of sugar) default_base_units_per_stock_unit = db.Column(db.Float, nullable=True, default=1) bls_code = db.Column(db.String(7), nullable=True) ndb_code = db.Column(db.String(7), nullable=True) ndb_title = db.Column(db.String(160), nullable=True) isBaseFood = db.Column(db.Boolean, unique=False, default=False) parentBaseFood = db.Column(db.Integer, db.ForeignKey('materials.id'), nullable=True) created = db.Column(db.DateTime, default=datetime.datetime.utcnow) # Standard values uom_nutrient_value = db.Column(db.String(5), db.ForeignKey('units_of_measures.uom'), nullable=True) # Classification food_maingroup_id = db.Column(db.Integer, db.ForeignKey('food_maingroup.id'), nullable=True) food_subgroup_id = db.Column(db.Integer, db.ForeignKey('food_subgroup.id'), nullable=True) food_processing_type_id = db.Column( db.Integer, db.ForeignKey('food_processing_type.id'), nullable=True) food_preparation_type_id = db.Column( db.Integer, db.ForeignKey('food_preparation_type.id'), nullable=True) food_edible_weight_id = db.Column( db.Integer, db.ForeignKey('food_weight_reference.id'), nullable=True) user_group = db.relationship("UserGroup") food_main_group = db.relationship("FoodMainGroup") food_subgroup = db.relationship("FoodSubGroup") food_processing_type = db.relationship("FoodProcessingType") food_preparation_type = db.relationship("FoodPreparationType") referencedIn = db.relationship("Ingredient") referencedInventoryItem = db.relationship("InventoryItem", back_populates="material") referencedMaterialForecast = db.relationship("MaterialForecast", back_populates="material") base_uom = db.relationship("UOM", foreign_keys=[uom_base_id]) stock_uom = db.relationship("UOM", foreign_keys=[uom_stock_id]) issue_uom = db.relationship("UOM", foreign_keys=[uom_issue_id])