class SerialNumberModel(db.Model):
__tablename__ = 'serial_number'
id = db.Column(db.Integer, primary_key=True)
order_id = db.Column(db.Integer, db.ForeignKey('order.id'), nullable=False)
menu_id = db.Column(db.Integer, db.ForeignKey('menu.id'), nullable=False)
serial_number = db.Column(db.String(255), nullable=False)
create_date = db.Column(db.DATETIME, default=datetime.datetime.utcnow())
order = db.relationship('OrderModel', back_populates='serial_number_list')
menu = db.relationship('MenuModel', back_populates='serials')
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def find_duplicate_link(cls, order_id: int, serial_number: str, menu_id: int):
return cls.query.filter_by(order_id=order_id, serial_number=serial_number, menu_id=menu_id).first()
@classmethod
def find_by_order(cls, order: OrderModel):
return cls.query.filter_by(order_id=order.id)
@classmethod
def get_by_id(cls, serial_id: int):
return cls.query.get(serial_id)
@classmethod
def get_by_serial_number(cls, serial_number: str):
db_result = cls.query.filter_by(serial_number=serial_number).first()
result = None
if db_result:
result = db_result
return result
class FilledForm(db.Model):
id = db.Column(db.String(256), primary_key=True)
username = db.Column(db.String(128),
db.ForeignKey('user.username'),
nullable=False)
form = db.Column(db.String(256), db.ForeignKey('form.id'), nullable=False)
response = db.Column(db.Text)
def __init__(self, response, user, form):
super().__init__(
**{
'response': response,
'username': user.username,
'form': form,
'id': str(uuid.uuid4())
})
def create(self):
db.session.add(self)
try:
db.session.commit()
return True
except IntegrityError:
return False
@staticmethod
def get(form, username):
return FilledForm.query.filter_by(form=form, username=username).first()
class Comment(db.Model):
id = db.Column(db.Integer, primary_key=True)
author = db.Column(db.String(30))
email = db.Column(db.String(254))
site = db.Column(db.String(255))
body = db.Column(db.Text)
from_admin = db.Column(db.Boolean, default=False)
reviewed = db.Column(db.Boolean, default=False)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
replied_id = db.Column(db.Integer, db.ForeignKey('comment.id'))
post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
post = db.relationship('Post', back_populates='comments')
replies = db.relationship('Comment',
back_populates='replied',
cascade='all, delete-orphan')
replied = db.relationship('Comment',
back_populates='replies',
remote_side=[id])
# Same with:
# replies = db.relationship('Comment', backref=db.backref('replied', remote_side=[id]),
# cascade='all,delete-orphan')
# class Link(db.Model):
# id = db.Column(db.Integer, primary_key=True)
# name = db.Column(db.String(30))
# url = db.Column(db.String(255))
class AssociationModel(db.Model):
__tablename_ = 'association_menu_order'
menu_id = db.Column(db.Integer, db.ForeignKey('menu.id'), primary_key=True)
order_id = db.Column(db.Integer, db.ForeignKey('order.id'), primary_key=True)
counts = db.Column(db.Integer, default=0)
menu = db.relationship('MenuModel', back_populates='orders')
order = db.relationship('OrderModel', back_populates='menus')
class Like(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
joke_id = db.Column(db.Integer, db.ForeignKey('joke.id'), nullable=False)
def __repr__(self):
return 'Like - {}'.format(self.id)
class Phone(db.Model):
id = db.Column(db.Integer, primary_key=True)
value = db.Column(db.String(50), nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
type_id = db.Column(db.Integer,
db.ForeignKey('person_type.id'),
nullable=False)
def __repr__(self):
return '<Phone {0!r}>'.format(self.id)
class Kids(db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(50))
last_name = db.Column(db.String(50))
age = db.Column(db.Integer)
allergies = db.Column(db.String(255))
food = db.Column(db.String(5))
token = db.Column(db.String(255))
emergency_id = db.Column(db.Integer, db.ForeignKey('emergency.id'))
guardian_id = db.Column(db.Integer, db.ForeignKey('guardian.id'))
class ExpenseModel(db.Model):
__tablename__ = "expenses"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
total_amount = db.Column(db.Integer, unique=False, nullable=False)
expense_category = db.Column(db.Integer, db.ForeignKey("expense_categories.id"))
created_date = db.Column(db.DateTime, default=datetime.utcnow)
def save_to_db(self):
db.session.add(self)
db.session.commit()
return self.id
def delete_by_id(self):
print(self)
obj = ExpenseModel.query.filter_by(id=self).first()
db.session.delete(obj)
db.session.commit()
def delete_expense(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_user(cls, user_id):
return cls.query.filter_by(user_id=user_id).first()
@classmethod
def return_all(cls, user_id):
sql = text(
"select users.username, expenses.total_amount, expense_categories.category_name, divided_expenses.amount from users left join expenses on users.id = expenses.user_id left join divided_expenses on expenses.id = divided_expenses.expense_id left join expense_categories on expense_categories.id=expenses.expense_category"
)
result = db.engine.execute(sql)
a = []
for row in result:
a.append(
{
"username": row[0],
"expense": row[1],
"category": row[2],
"dividedExpense": row[3],
}
)
return a
@classmethod
def delete_all(cls):
try:
num_rows_deleted = db.session.query(cls).delete()
db.session.commit()
return {"message": "{} row(s) deleted".format(num_rows_deleted)}
except:
return {"message": "Something went wrong"}
class ActivityJoinModel(db.Model):
__tablename__ = "ActivityJoin"
id = db.Column(Integer, primary_key=True)
userID = db.Column(Integer, db.ForeignKey('User.id'))
activityID = db.Column(Integer, db.ForeignKey('Activity.id'))
joinTime = db.Column(DATETIME)
def __init__(self, userID, activityID):
self.userID = userID
self.activityID = activityID
self.joinTime = datetime.now()
class PoolJoinModel(db.Model):
__tablename__ = "PoolJoin"
id = db.Column(Integer, primary_key=True)
userID = db.Column(Integer, db.ForeignKey('User.id'))
poolID = db.Column(Integer, db.ForeignKey('Pool.id'))
joinTime = db.Column(DATETIME)
def __init__(self, poolID, userID):
self.poolID = poolID
self.userID = userID
self.joinTime = datetime.now()
class UsedSkills(db.Model):
__tablename__ = 't_used_skills'
id = db.Column(db.Integer, primary_key=True)
skill_id = db.Column(db.Integer, db.ForeignKey('d_skills.id'))
user_id = db.Column(db.Integer, db.ForeignKey('t_users.user_id'))
used = db.Column(db.Integer, default=1)
def save_to_db(self):
db.session.add(self)
db.session.commit()
class LabeledDatasetModel(db.Model):
__tablename__ = "labeled_dataset"
__table_args__ = (db.UniqueConstraint("unlabeled_dataset_id",
"user_id",
name="uix_1"), )
id = db.Column(db.Integer, primary_key=True)
unlabeled_dataset_id = db.Column(db.Integer,
db.ForeignKey("unlabeled_dataset.id"))
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
label = db.Column(db.Integer)
user = db.relationship("UserModel", back_populates="evaluations")
evaluation = db.relationship("UnlabeledDatasetModel",
back_populates="users")
@classmethod
def get_unlabeled(cls, user_id):
subquery = (cls.query.filter(cls.user_id == user_id).with_entities(
cls.unlabeled_dataset_id).distinct(cls.unlabeled_dataset_id))
dataset = UnlabeledDatasetModel.query.filter(
~UnlabeledDatasetModel.id.in_(subquery)).all()
return {
"dataset":
list(
map(
lambda row: {
"id": row.id,
"item_1": row.item_1,
"item_2": row.item_2,
},
dataset,
))
}
@classmethod
def get_all(cls):
dataset = UnlabeledDatasetModel.query.all()
return {
"dataset":
list(
map(
lambda row: {
"id": row.id,
"item_1": row.item_1,
"item_2": row.item_2,
},
dataset,
))
}
def save(self):
db.session.add(self)
db.session.commit()
class DividedExpenseModel(db.Model):
__tablename__ = "divided_expenses"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
expense_id = db.Column(db.Integer, db.ForeignKey("expenses.id"))
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
amount = db.Column(db.Integer, nullable=False)
created_date = db.Column(db.DateTime, default=datetime.utcnow)
def save_to_db(self):
db.session.add(self)
db.session.commit()
class RecruitModel(db.Model):
__tablename__ = "Recruit"
id = db.Column(Integer, primary_key=True)
userID = db.Column(Integer, db.ForeignKey('User.id'))
postsID = db.Column(Integer, db.ForeignKey('Posts.id'))
time = db.Column(DATE)
isSuccess = db.Column(Boolean) # 是否要
def __init__(self, userID, postsID, isSuccess):
self.userID = userID
self.postsID = postsID
self.isSuccess = isSuccess
self.time = datetime.now()
class LoveRelationModel(db.Model):
__tablename__ = "LoveRelation"
id = db.Column(Integer, primary_key=True)
fromID = db.Column(Integer, db.ForeignKey('User.id'))
toID = db.Column(Integer, db.ForeignKey('User.id'))
poolID = db.Column(Integer, db.ForeignKey('Pool.id'))
loveTime = db.Column(DATETIME)
def __init__(self, fromID, toID, poolID):
self.fromID = fromID
self.toID = toID
self.poolID = poolID
self.loveTime = datetime.now()
class ProjectGroupInfo(db.Model):
__tablename__ = 'op_project_group_info'
__table_args__ = {'schema': 'datastage'}
project_id = db.Column(
db.String(200),
db.ForeignKey('datastage.op_project_static_info.project_id'),
primary_key=True)
group_id = db.Column(
db.String(200),
db.ForeignKey('datastage.op_group_static_info.group_id'),
primary_key=True)
group_static_info = db.relationship("GroupStaticInfo",
backref="project_group_info")
class OrderModel(db.Model):
__tablename__ = 'order'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
create_date = db.Column(db.DATETIME, default=datetime.datetime.utcnow())
customized_message = db.Column(db.String(255))
serial_number_list = db.relationship('SerialNumberModel', back_populates='order', lazy=True)
owner = db.relationship('UserModel', back_populates='order_list')
menus = db.relationship('AssociationModel', back_populates='order')
is_obsolete = db.Column(db.Boolean, default=False)
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def get_by_id(cls, order_id: int):
return cls.query.get(order_id)
@classmethod
def find_valid_by_user(cls, user: UserModel):
return cls.query.filter_by(user_id=user.id, is_obsolete=False)
@classmethod
def find_history_by_user(cls, user: UserModel):
return cls.query.filter_by(user_id=user.id, is_obsolete=True)
class Message(db.Model):
"""Messages for our website"""
__table_args__ = (db.UniqueConstraint("message_type", "product_id",
"version"), )
pk = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(120), nullable=False)
message_type = db.Column(db.String, nullable=False)
version = db.Column(db.String(5), nullable=False)
product_id = db.Column(db.Integer,
db.ForeignKey("product.pk"),
nullable=False)
product = db.relationship("Product", back_populates="messages")
@validates("message_type")
def validate_type(self, key, value):
assert value in MESSAGE_TYPES
return value
def __init__(self, body, message_type):
self.body = body
self.message_type = message_type
@property
def sms_body(self):
return "{}: {!r}".format(self.body, self.product)
def to_dict(self):
return {
"pk": self.pk,
"body": self.body,
"message_type": self.message_type,
"product_id": self.product_id,
}
class IngredientModel(db.Model):
__tablename__ = 'ingredients'
id = db.Column(db.Integer, primary_key = True)
item = db.Column(db.String(120), unique = False, nullable = False)
recipe = db.Column(db.Integer, db.ForeignKey("recipes.id"), nullable=False)
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def return_all(cls):
def to_json(x):
return {
'id': x.id,
'item': x.item,
'recipe': x.recipe
}
return {'ingredients': list(map(lambda x: to_json(x), IngredientModel.query.all()))}
@property
def serialize(self):
return {
'item': self.item,
}
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
first_name = db.Column(db.String(80), nullable=False)
middle_name = db.Column(db.String(80))
surname = db.Column(db.String(80), nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(120), nullable=False)
photoname = db.Column(db.String(120), nullable=True)
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
role = db.relationship('Role', backref=db.backref('users', lazy=True))
general_info = db.relationship('GeneralInfo',
backref=db.backref('users', lazy=True))
highschool_info = db.relationship('HighschoolInfo',
backref=db.backref('users', lazy=True))
population_info = db.relationship('PopulationInfo',
backref=db.backref('users', lazy=True))
university_info = db.relationship('UniversityInfo',
backref=db.backref('users', lazy=True))
family = db.relationship('Family', backref=db.backref('users', lazy=True))
interests = db.relationship('Interests',
backref=db.backref('users', lazy=True))
def __repr__(self):
return "<User {0!r}>".format(self.username)
def set_password(self, password):
self.password = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password, password)
class Employee(db.Model):
__tablename__ = 'employee'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
gender = db.Column(db.String)
mobile = db.Column(db.String)
address = db.Column(db.String)
text = db.Column(db.Text)
c_time = db.Column(db.DateTime)
departmentid = db.Column(db.Integer, db.ForeignKey('department.id'))
department = db.relationship('Department',
backref=db.backref('employees',
lazy='dynamic'))
def __init__(self,
name=None,
gender=None,
mobile=None,
address=None,
text=None,
c_time=None):
self.name = name
self.gender = gender
self.mobile = mobile
self.address = address
self.text = text
self.c_time = datetime.now()
class Users(db.Model, UserMixin):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), unique=True)
pwd = db.Column(db.String(255))
end_date = db.Column(db.DateTime)
record_id = db.Column(db.Integer, db.ForeignKey('pay_record.id'))
def __init__(self, name, pwd):
self.name = name
self.pwd = generate_password_hash(pwd)
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
@property
def password(self):
raise AttributeError("密码不允许读取")
# 检查密码
def check_password_hash(self, password):
return check_password_hash(self.pwd, password)
def __repr__(self):
return 'User object: %s ' % self.name
class Tags(db.Model):
__tablename__ = 'TAGS'
id = db.Column(db.Integer, primary_key=True)
recipe_id = db.Column(db.Integer,
db.ForeignKey('RECIPE.id'),
nullable=False)
name = db.Column(db.String(32))
def __init__(self, recipe_id, name):
self.recipe_id = recipe_id
self.name = name
def json(self):
return {'id': self.id, 'recipe_id': self.recipe_id, 'name': self.name}
@classmethod
def find_by_recipe_id(self, recipe_id):
tags = self.query.filter_by(recipe_id=recipe_id).all()
tags_str = ''
for tag in tags:
tags_str += tag.name
tags_str += "-"
tags_str = tags_str[:-1]
return tags_str
def add_tag(self):
db.session.add(self)
db.session.commit()
return self.id
def get_tags(self, name, username):
_user = User.query.filter_by(username=username).first()
_recipe = Recipe.query.filter_by(user_id=_user.id, name=name)
tagList = Tags.query.filter_by(recipe_id=_recipe.id).all()
return tagList
class Final_Photos(db.Model):
__tablename__ = 'FINAL_PHOTOS'
id = db.Column(db.Integer, primary_key=True)
recipe_id = db.Column(db.Integer,
db.ForeignKey('RECIPE.id'),
nullable=False)
photo_link = db.Column(db.String)
def __init__(self, recipe_id, photo_link):
self.recipe_id = recipe_id
self.photo_link = photo_link
def json(self):
return {'id': self.id, 'recipe_id': self.recipe_id}
@classmethod
def delete_photo(self, name, username):
_user = User.query.filter_by(username=username).first()
_recipe = Recipe.query.filter_by(user_id=_user.id, name=name)
Final_Photos.query.filter_by(recipe_id=_recipe.id, id=self.id).delete()
db.session.commit()
def get_photos(recipe_id):
photoList = Final_Photos.query.filter_by(recipe_id=recipe_id).all()
return photoList
def create_to(self):
db.session.add(self)
db.session.commit()
class Device(db.Model):
__tablename__ = 'device_tracker'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('tracking_user.id'), nullable=False)
name = db.Column(db.String(80), nullable=False)
tipodispositivo = db.Column(db.String(40), nullable=False)
macaddress = db.Column(db.String(40), nullable=False)
latitud = db.Column(db.String(40), nullable=True)
longitud = db.Column(db.String(40), nullable=True)
def save(self):
db.session.add(self)
db.session.commit()
@staticmethod
def get_by_user(ids):
return Device.query.filter_by(user_id=ids).all()
@staticmethod
def get_by_userTrack(ids):
tracking = list()
result = Device.query.filter_by(user_id=ids).options(load_only('name', 'macaddress', 'latitud', 'longitud')).all()
for item in result:
keys = ['name','macaddress','latitud','longitud']
datos = [item.name,item.macaddress,item.latitud,item.longitud]
tracking.append(dict(zip(keys,datos)))
return tracking
class Trip(db.Model):
__tablename__ = 'trips_table'
trip_id = db.Column(db.Integer, primary_key=True, autoincrement=True) # pylint: disable=no-member
user_id = db.Column(db.Integer,
db.ForeignKey('user_table.user_id'),
nullable=False) # pylint: disable=no-member
trip_json = db.Column(db.Text, nullable=True) # pylint: disable=no-member
is_public = db.Column(db.Boolean, nullable=False) # pylint: disable=no-member
def save_to_db(self):
db.session.add(self) # pylint: disable=no-member
db.session.commit() # pylint: disable=no-member
@classmethod
def remove(cls):
db.session.query(cls).delete() # pylint: disable=no-member
@classmethod
def find_by_tid(cls, tid):
return cls.query.filter_by(trip_id=tid).first()
@classmethod
def find_all_trips(cls, uid):
return cls.query.filter_by(user_id=uid)
# Gets all trips from a user that is public
@classmethod
def find_all_public_trips(cls, uid):
return cls.query.filter(cls.user_id == uid, cls.public == True)
@classmethod
def does_trip_exist(cls, jsonTrip):
trip = cls.query.filter_by(trip_json=jsonTrip).first()
return {"exists": trip is not None}
class RoleFuncs(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
def __repr__(self):
return '<RoleFuncs {0!r}>'.format(self.id)
class MenuModel(db.Model):
__tablename__ = 'menu'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255))
menu_type = db.Column(db.Enum(*MenuTypes.get_enum_labels()), default=MenuTypes.CUSTOMIZED.value) # menu type
coffee_option = db.Column(db.Enum(*CoffeeOption.get_enum_labels()), default=CoffeeOption.CoffeeA.value) # coffee option
owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
taste_level = db.Column(db.Enum(*TasteLevels.get_enum_labels()), default=TasteLevels.STANDARD.value) # taste level
water_level = db.Column(db.Enum(*WaterLevels.get_enum_labels()), default=WaterLevels.STANDARD.value) # water level
foam_level = db.Column(db.Enum(*FoamLevels.get_enum_labels()), default=FoamLevels.STANDARD.value) # foam level
grind_size = db.Column(db.Enum(*SizeLevels.get_enum_labels()), default=SizeLevels.MEDIUM.value) # particle size
create_date = db.Column(db.DATETIME, default=datetime.datetime.utcnow())
owner = db.relationship('UserModel', back_populates='menu_list')
orders = db.relationship('AssociationModel', back_populates='menu')
serials = db.relationship('SerialNumberModel', back_populates='menu')
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def find_by_user(cls, owner: UserModel):
return cls.query.filter_by(owner_id=owner.id)
@classmethod
def get_by_owner_and_id(cls, menu_id: int, owner: UserModel):
return cls.query.filter_by(id=menu_id, owner_id=owner.id).one_or_none()
@classmethod
def get_by_id(cls, menu_id: int):
return cls.query.get(menu_id)
class Session(db.Model):
username = db.Column(db.String(256),
db.ForeignKey('user.username'),
nullable=False)
id = db.Column(db.String(256), primary_key=True)
start_time = db.Column(db.DateTime)
end_time = db.Column(db.DateTime)
domains = db.relationship('Domain', backref='session', lazy=True)
def __init__(self, data):
data['start_time'] = cvt_date(data['start_time'])
data['end_time'] = cvt_date(data['end_time'])
super().__init__(**data)
try:
db.session.add(self)
db.session.commit()
except Exception as e:
print(e)
def detail(self):
data = self.__dict__.copy()
del data['_sa_instance_state']
data['domains'] = [domain.detail() for domain in self.domains]
return data
def delete(self):
db.session.delete(self)
db.session.commit()
class UserModel(db.Model):
__tablename__ = 'usuario'
usrId = db.Column(db.String(15), primary_key=True)
nomUsr = db.Column(db.String(126), nullable=False)
pwd = db.Column(db.String(255), nullable=False)
noTel = db.Column(db.String(22), nullable=True)
correo = db.Column(db.String(126), nullable=False)
rfcTutor = db.Column(db.String(30), nullable=True)
fechaAlta = db.Column(db.Date, nullable=False)
idTipoUsr = db.Column(db.Integer, db.ForeignKey('tipousr.idTipoUsr'))
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def find_by_username(cls, usrId):
return cls.query.filter_by(usrId=usrId).first()
@staticmethod
def generate_hash(password):
return sha256.hash(password)
@staticmethod
def verify_hash(password, hash):
return sha256.verify(password, hash)
