class Equipment(db.Model):
__tablename__ = 'equipment'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
gaode_longitude = db.Column(db.DECIMAL(precision='15,6'))
gaode_latitude = db.Column(db.DECIMAL(precision='15,6'))
status = db.Column(db.Enum('正常', '故障'), server_default='正常') # 设备状态
location = db.Column(db.String(100))
admin_id = db.Column(db.Integer, db.ForeignKey('admin_people.id')) # 仪器管理者id
admin = db.relationship('Admin_people', backref=db.backref('equipments'))
class ProductModel(db.Model):
__tablename__ = "products"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
description = db.Column(db.String(100), nullable=False)
price = db.Column(db.DECIMAL(asdecimal=False), nullable=False)
image = db.Column(db.String(100), nullable=False)
#image_id = db.Column(db.Integer, db.ForeignKey("images.id"), nullable=False)
#image = db.relationship("ImageModel")
@classmethod
def find_by_name(cls, name: str) -> "ProductModel":
return cls.query.filter_by(name=name).first()
@classmethod
def find_all(cls) -> List["ProductModel"]:
return cls.query.all()
@classmethod
def find_all_images(cls) -> List["ProductModel"]:
return cls.query.all()
def save_to_db(self) -> None:
db.session.add(self)
db.session.commit()
def delete_from_db(self) -> None:
db.session.delete(self)
db.session.commit()
class UpVotesModel(db.Model):
__tablename__ = 'upvotes'
id = db.Column(db.Integer, primary_key=True)
recipes_id = db.Column(db.Integer, db.ForeignKey('recipes.id'))
vote_score = db.Column(db.DECIMAL(3, 2))
def __init__(self, id, recipes_id, vote_score):
self.id = id
self.recipes_id = recipes_id
self.vote_score = vote_score
def json(self):
return {
'id': self.id,
'recipes_id': self.recipes_id,
'vote_score': json.dumps(self.vote_score),
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class AutoInsuranceModel(db.Model):
__tablename__ = 'auto_insurance'
customer_id = db.Column(db.Integer,
db.ForeignKey('customer.customer_id',
ondelete='CASCADE',
onupdate='CASCADE'),
primary_key=True)
a_start_date = db.Column(db.Date, nullable=False)
a_end_date = db.Column(db.Date, nullable=False)
a_premium_amount = db.Column(db.DECIMAL(12, 2), nullable=False)
a_policy_status = db.Column(db.Enum("C", "P"))
customer = db.relationship('CustomerModel')
def __init__(self, customer_id, a_start_date, a_end_date, a_premium_amount,
a_policy_status):
self.customer_id = customer_id
self.a_start_date = a_start_date
self.a_end_date = a_end_date
self.a_premium_amount = a_premium_amount
self.a_policy_status = a_policy_status
def json(self):
return {
'a_customer_id': self.a_customer_id,
'a_start_data': self.a_start_date,
'a_end_date': self.a_end_date,
'a_premium_amount': self.a_premium_amount,
'a_policy_status': self.a_policy_status
}
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(customer_id=_id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class HInsPaymentModel(db.Model):
__tablename__ = 'h_ins_payment'
h_payment_id = db.Column(db.Integer, primary_key=True)
h_pay_amount = db.Column(db.DECIMAL(12, 2), nullable=False)
h_payment_date = db.Column(db.Date, nullable=False)
h_payment_method = db.Column(db.Enum("Paypal", "Credit", "Debit", "Check"),
nullable=False)
h_invoice_id = db.Column(
db.Integer,
db.ForeignKey('home_invoice.h_invoice_id',
ondelete='CASCADE',
onupdate='CASCADE'))
def __init__(self, h_pay_amount, h_payment_date, h_payment_method,
h_invoice_id):
self.h_pay_amount = h_pay_amount
self.h_payment_date = h_payment_date
self.h_payment_method = h_payment_method
self.h_invoice_id = h_invoice_id
def json(self):
return {
'h_payment_id': self.h_payment_id,
'h_pay_amount': self.h_pay_amount,
'h_payment_date': self.h_payment_date,
'h_payment_method': self.h_payment_method,
'h_invoice_id': self.h_invoice_id
}
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(h_payment_id=_id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class IngredientsModel(db.Model):
__tablename__ = 'ingredients'
id = db.Column(db.Integer, primary_key=True)
recipes_id = db.Column(db.Integer,
db.ForeignKey('recipes.id', ondelete='CASCADE'))
amount = db.Column(db.DECIMAL(3, 2))
ingredient = db.Column(db.String)
measurement = db.Column(db.String)
recipes = db.relationship('RecipeModel', single_parent=True)
def __init__(self, id, amount, ingredient, measurement):
self.id = id
self.amount = amount
self.ingredient = ingredient
self.measurement = measurement
def json(self):
return {
'id': self.id,
'amount': json.dumps(self.amount),
'ingredient': self.ingredient,
'measurement': self.measurement,
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class HomeInvoiceModel(db.Model):
__tablename__ = 'home_invoice'
h_invoice_id = db.Column(db.Integer, primary_key=True)
h_invoice_date = db.Column(db.Date, nullable=False)
h_pay_due_date = db.Column(db.Date, nullable=False)
h_invoice_amount = db.Column(db.DECIMAL(12, 2), nullable=False)
customer_id = db.Column(db.Integer,
db.ForeignKey('home_insurance.customer_id', onupdate='CASCADE', ondelete='CASCADE'))
def __init__(self, h_invoice_date, h_pay_due_date, h_invoice_amount, customer_id):
self.h_invoice_date = h_invoice_date
self.h_pay_due_date = h_pay_due_date
self.h_invoice_amount = h_invoice_amount
self.customer_id = customer_id
def json(self):
return {
'h_invoice_id': self.h_invoice_id,
'h_invoice_date': self.h_invoice_date,
'h_pay_due_date': self.h_pay_due_date,
'h_invoice_amount': self.h_invoice_amount,
'customer_id': self.customer_id
}
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(h_invoice_id=_id).first()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class RecipeModel(db.Model):
__tablename__ = 'recipes'
# create recipe table
id = db.Column(
db.Integer,
primary_key=True,
)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
cuisine_id = db.Column(db.Integer, db.ForeignKey('cuisine.id'))
name = db.Column(db.String(150))
description = db.Column(db.Text)
image_path = db.Column(db.String(100))
total_time = db.Column(db.Integer)
prep_time = db.Column(db.Integer)
cook_time = db.Column(db.Integer)
level = db.Column(db.String(45))
source = db.Column(db.String(45))
rating = db.Column(db.DECIMAL(3, 2))
time_added = db.Column(db.Date)
steps = db.relationship('StepsModel', cascade='save-update, merge, delete')
comments = db.relationship('CommentsModel',
backref='comments',
cascade="all, delete-orphan",
lazy='dynamic',
passive_deletes=True)
user = db.relationship('UserModel', backref='user')
categories = db.relationship('CategoriesModel',
backref='categories',
secondary='recipes_has_categories')
allergens = db.relationship('AllergensModel',
secondary='recipes_has_allergens')
courses = db.relationship('CoursesModel',
backref='courses',
secondary='recipes_has_courses')
cuisine = db.relationship('CuisineModel', backref='cuisine')
upvotes = db.relationship('UpVotesModel', backref='upvotes')
ingredients = db.relationship('IngredientsModel',
cascade='save-update, merge, delete')
def __init__(
self,
user_id,
cuisine_id,
name,
description,
image_path,
total_time,
prep_time,
cook_time,
level,
source,
):
self.user_id = user_id
self.cuisine_id = cuisine_id
self.name = name
self.description = description
self.image_path = image_path
self.total_time = total_time
self.prep_time = prep_time
self.cook_time = cook_time
self.level = level
self.source = source
# Return recipe as JSON object
def json(self):
return {
'id': self.id,
'name': self.name,
'cuisine_id': self.cuisine_id,
'description': self.description,
'image_path': self.image_path,
'total_time': self.total_time,
'prep_time': self.prep_time,
'cook_time': self.cook_time,
'level': self.level,
'source': self.source,
'rating': json.dumps(
self.rating), # JSON encoder and decoder for DECIMAL number
'steps': [steps.json() for steps in self.steps],
'time_added': self.time_added.isoformat(),
'comments': [comments.json() for comments in self.comments.all()],
'user': self.user.json(),
'allergens': [allergens.json() for allergens in self.allergens],
'courses': [courses.json() for courses in self.courses],
'cuisine': self.cuisine.json(),
'ingredients':
[ingredients.json() for ingredients in self.ingredients],
'categories':
[categories.json() for categories in self.categories],
}
# return list with some information for faster loading
def recipe_list(self):
return {
'id': self.id,
'name': self.name,
'cuisine_id': self.cuisine_id,
'description': self.description,
'image_path': self.image_path,
'total_time': self.total_time,
'prep_time': self.prep_time,
'cook_time': self.cook_time,
'level': self.level,
'rating': json.dumps(
self.rating), # JSON encoder and decoder for DECIMAL number
'time_added': self.time_added.isoformat(),
'user': self.user.json(),
}
# Find recipe by ID
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
# Find all recipes
@classmethod
def find_all(cls):
return cls.query.order_by(cls.time_added.desc()).all()
# find recipes sorted by rating
@classmethod
def find_top_recipes(cls):
return cls.query.order_by(cls.rating.desc()).limit(12).all()
# filter recipes by filter
@classmethod
def find_by_filter(cls, filters):
return cls.query.filter_by(**filters).all()
# find all recipes by cuisine
@classmethod
def find_by_cuisine(cls, filters):
return cls.query.join(
CuisineModel.cuisine).filter(CuisineModel.name == filters).all()
# find all recipes by category
@classmethod
def find_by_category(cls, filters):
return cls.query.join(CategoriesModel.categories).filter(
CategoriesModel.name == filters).all()
# find all recipes by course
@classmethod
def find_by_courses(cls, filters):
return cls.query.join(
CoursesModel.courses).filter(CoursesModel.name == filters).all()
# find recipes by name
@classmethod
def search_by_filter(cls, filters):
return cls.query.filter(cls.name.like('%' + filters['name'] +
'%')).all()
# Delete recipe from db
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
# Save recipe to db
def save_recipe_to_db(self, category):
self.recipes_has_categories.append(
RecipeCategory(id=None,
recipes_id=self.id,
categories_id=category.id))
db.session.add(self)
db.session.commit()
def save_allergen_to_db(self, allergens):
self.recipes_has_allergens.append(
RecipeAllergens(id=None,
recipes_id=self.id,
allergens_id=allergens.id))
db.session.add(self)
db.session.commit()
def save_course_to_db(self, courses):
self.recipes_has_courses.append(
RecipeCourses(id=None, recipes_id=self.id, courses_id=courses.id))
db.session.add(self)
db.session.commit()
def save_step_to_db(self, step):
self.steps.append(step)
db.session.add(self)
db.session.commit()
def save_ingredient_to_db(self, ingredients):
self.ingredients.append(ingredients)
db.session.add(self)
db.session.commit()
def delete_category_from_db(self, category):
db.session.delete(category)
db.session.commit()
def delete_allergen_from_db(self, allergen):
db.session.delete(allergen)
db.session.commit()
def delete_course_from_db(self, course):
db.session.delete(course)
db.session.commit()
class UserModel(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
firstName = db.Column(db.String(100))
lastName = db.Column(db.String(100))
password = db.Column(db.String(80))
email = db.Column(db.String(200))
birthday = db.Column(db.String(20))
income = db.Column(db.Integer)
picture = db.Column(db.String(200))
householdType = db.Column(db.String(100))
householdCount = db.Column(db.Integer)
addressLine1 = db.Column(db.String(200))
addressLine2 = db.Column(db.String(200))
addressPostalCode = db.Column(db.String(10))
lat = db.Column(db.DECIMAL(9, 6))
lng = db.Column(db.DECIMAL(9, 6))
pin = db.Column(db.String(10))
pinExpiry = db.Column(db.DateTime)
totalRating = db.Column(db.Integer)
noOfRatings = db.Column(db.Integer)
items = db.relationship("ItemModel",
back_populates="user",
cascade="all, delete",
passive_deletes=True)
availabilities = db.relationship("UserAvailabilityModel",
back_populates="user",
cascade="all, delete",
passive_deletes=True)
requests = db.relationship("RequestModel", back_populates="user")
ratingsGiven = db.relationship("RatingModel",
back_populates="rater",
foreign_keys=[RatingModel.raterID])
ratingsReceived = db.relationship("RatingModel",
back_populates="ratee",
foreign_keys=[RatingModel.rateeID])
credit = db.relationship("CreditModel", back_populates="user")
queueItems = db.relationship("QueueItemModel", back_populates="user")
deliveries = db.relationship("DeliveryModel", back_populates="deliverer")
def __init__(self,
firstName, lastName, password, email, birthday, income, \
householdType, addressLine1, addressLine2, addressPostalCode, householdCount, lat, lng, \
picture=None, pin=None, pinExpiry=None, averageRating=0, noOfRatings=0):
self.firstName = firstName
self.lastName = lastName
self.password = password
self.email = email
self.birthday = birthday
self.income = income
self.picture = picture
self.householdType = householdType
self.householdCount = householdCount
self.addressLine1 = addressLine1
self.addressLine2 = addressLine2
self.addressPostalCode = addressPostalCode
self.lat = lat
self.lng = lng
self.pin = pin
self.pinExpiry = pinExpiry
self.averageRating = averageRating
self.noOfRatings = noOfRatings
def json(self):
return {
'id': self.id,
'firstName': self.firstName,
'lastName': self.lastName,
'email': self.email,
'birthday': self.birthday,
'income': self.income,
'picture': self.picture,
'householdType': self.householdType,
'householdCount': self.householdCount,
'addressLine1': self.addressLine1,
'addressLine2': self.addressLine2,
'addressPostalCode': self.addressPostalCode,
'lat': str(self.lat),
'lng': str(self.lng)
}
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_email(cls, email):
return cls.query.filter_by(email=email).first()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
class HomeModel(db.Model):
__tablename__ = 'home'
home_id = db.Column(db.Integer, primary_key=True)
purchase_date = db.Column(db.Date, nullable=False)
purchase_value = db.Column(db.DECIMAL(12), nullable=False)
area_sqft = db.Column(db.Integer, nullable=False)
home_type = db.Column(db.Enum("S", "M", "C", "T"), nullable=False)
auto_fire_notif = db.Column(db.Enum("Y", "N"), nullable=False)
security_sys = db.Column(db.Enum("Y", "N"), nullable=False)
swimming_pool = db.Column(db.Enum("U", "O", "I", "M", "N"))
basement = db.Column(db.Enum("Y", "N"), nullable=False)
customer_id = db.Column(
db.Integer,
db.ForeignKey('home_insurance.customer_id',
onupdate='CASCADE',
ondelete='CASCADE'))
home_insurance = db.relationship('HomeInsuranceModel')
def __init__(self, purchase_date, purchase_value, area_sqft, home_type,
auto_fire_notif, security_sys, swimming_pool, basement,
customer_id):
self.purchase_date = purchase_date
self.purchase_value = purchase_value
self.area_sqft = area_sqft
self.home_type = home_type
self.auto_fire_notif = auto_fire_notif
self.security_sys = security_sys
self.swimming_pool = swimming_pool
self.basement = basement
self.customer_id = customer_id
def json(self):
return {
'home_id': self.home_id,
'purchase_date': self.purchase_date,
'purchase_value': self.purchase_value,
'area_sqft': self.area_sqft,
'home_type': self.home_type,
'auto_fire_notif': self.auto_fire_notif,
'security_sys': self.security_sys,
'swimming_pool': self.swimming_pool,
'basement': self.basement,
'customer_id': self.customer_id
}
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(home_id=_id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class TaskModel(db.Model):
__tablename__ = "tasks"
id = db.Column(db.Integer, primary_key=True)
desc = db.Column(db.String(128), nullable=False)
ecoPoints = db.Column(db.Integer, nullable=False)
savings = db.Column(db.DECIMAL(precision=8, scale=2), nullable=False)
weekly = db.Column(db.Boolean, nullable=False)
created = db.Column(db.DateTime, server_default=db.func.now())
updated = db.Column(db.DateTime, server_default=db.func.now())
deleted = db.Column(db.DateTime, server_default=None)
category_id = db.Column(db.Integer,
db.ForeignKey("categories.id"),
nullable=False)
category = db.relationship("CategoryModel")
def __init__(self, desc, ecoPoints, savings, weekly, category):
self.desc = desc
self.ecoPoints = ecoPoints
self.savings = savings
self.weekly = weekly
self.category_id = getattr(
CategoryModel.find_existing_by_name(category), "id", None)
def json(self):
if DEBUG:
return {
"id":
self.id,
"desc":
self.desc,
"ecoPoints":
self.ecoPoints,
"savings":
format(float(self.savings), ".2f"),
"weekly":
self.weekly,
"category":
getattr(CategoryModel.find_existing_by_id(self.category_id),
"name", None),
"created":
self.created.timestamp(),
"updated":
self.updated.timestamp(),
"deleted":
None if self.deleted is None else self.deleted.timestamp()
}
return {
"id":
self.id,
"desc":
self.desc,
"ecoPoints":
self.ecoPoints,
"savings":
format(float(self.savings), ".2f"),
"weekly":
self.weekly,
"category":
getattr(CategoryModel.find_existing_by_id(self.category_id),
"name", None)
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id).first()
@classmethod
def find_existing_by_id(cls, id):
return cls.query.filter_by(id=id).filter_by(deleted=None).first()
@classmethod
def find_all(cls):
return cls.query.all()
@classmethod
def find_all_existing(cls):
return cls.query.filter_by(deleted=None).all()
@classmethod
def find_new(cls, last_fetch):
return cls.query.filter(
cls.created > datetime.fromtimestamp(last_fetch),
cls.deleted == None)
@classmethod
def find_deleted(cls, last_fetch):
return cls.query.filter(
cls.created <= datetime.fromtimestamp(last_fetch),
cls.deleted > datetime.fromtimestamp(last_fetch))
@classmethod
def find_updated(cls, last_fetch):
return cls.query.filter(
cls.created <= datetime.fromtimestamp(last_fetch),
cls.deleted == None,
cls.updated > datetime.fromtimestamp(last_fetch))
def update(self, data):
for k in data:
if k == "category":
setattr(
self, "category_id",
getattr(CategoryModel.find_existing_by_name(data[k]), "id",
None))
else:
setattr(self, k, data[k])
setattr(self, "updated", datetime.now())
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.deleted = datetime.now()
db.session.add(self)
db.session.commit()
