class Post(db.Model):
id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
date_created = db.Column(TIMESTAMP(timezone=True), default=datetime.utcnow)
created_by = db.Column(UUID(as_uuid=True),
db.ForeignKey('registered_user.id'),
nullable=False)
content = db.Column(db.String(1000), nullable=False)
status = db.Column(db.String,
db.ForeignKey('review_status_kind.name'),
default='pending')
prompt_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('prompt.id'),
nullable=False)
reviews = db.relationship('PostReview', lazy='dynamic')
def with_reviews(self):
self.reviews = [r for r in self.reviews.all()]
return self
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def get_pending(cls):
return cls.query.filter_by(status='pending').all()
@classmethod
def get_approved(cls):
return cls.query.filter_by(status='approved').all()
@classmethod
def get_rejected(cls):
return cls.query.filter_by(status='rejected').all()
class FoodKind(BaseMixin, UserDefinedNameMixin, db.Model):
user_id = db.Column(UUID(as_uuid=True), db.ForeignKey('app_user.id'))
unit_of_measurement_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('unit_of_measurement.id'),
default=UNIT_OF_MEASURE_IS_SELF_ID)
serving_size = db.Column(db.Float, default=0.0)
unit_of_measurement = db.relationship('UnitOfMeasurement', lazy='joined')
nutrition_info = db.relationship('FoodKindNutritionInfo',
lazy='joined',
uselist=False)
categories = db.relationship('FoodCategory',
secondary='food_kind_category',
lazy='subquery',
backref=db.backref('food_kinds', lazy=True))
stock_items = db.relationship('StockItem', lazy=True)
def full_dict(self):
remove = ['unit_of_measurement_id']
return {
**{
key: getattr(self, key)
for key in self.cols_dict().keys() if key not in remove
}, 'unit_of_measurement': self.unit_of_measurement,
'categories': self.categories,
'nutrition_info': self.nutrition_info or {}
}
class Notification(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String, nullable=False, default='unread')
title = db.Column(db.String)
description = db.Column(db.String)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
interview_id = db.Column(db.Integer,
db.ForeignKey('job.id'),
nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, user_id, title, description, interview_id):
self.user_id = user_id
self.title = title
self.description = description
self.interview_id = interview_id
@classmethod
def get_pag(cls, data, id):
return cls.query.filter(cls.user_id == id).order_by(
cls.created_at.desc()).paginate(data['page'], 10, False)
@classmethod
def unreadNotificationsCount(cls, id):
return cls.query.filter(cls.user_id == id,
cls.status == 'unread').count()
class PromptReview(db.Model):
id = db.Column(UUID(as_uuid=True), primary_key=True)
prompt_id = db.Column(UUID(as_uuid=True), db.ForeignKey('prompt.id'), nullable=False)
date_created = db.Column(TIMESTAMP(timezone=True), default=datetime.utcnow)
date_modified = db.Column(TIMESTAMP(timezone=True))
created_by = db.Column(UUID(as_uuid=True), db.ForeignKey('registered_user.id'), nullable=False)
is_approved = db.Column(db.Boolean, nullable=False)
comments = db.Column(db.String(400))
class StockItem(UuidPrimaryKeyMixin, CreatedUpdatedTimestampMixin, db.Model):
stock_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('stock.id'),
nullable=False)
item_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('item.id'),
nullable=False)
snapshots = db.relationship('StockItemSnapshot', lazy=True)
class UserProfile(TimestampMixin, db.Model):
user_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('registered_user.id'),
primary_key=True)
role_id = db.Column(db.Integer, db.ForeignKey('user_role.id'))
username = db.Column(db.String, unique=True, nullable=False)
role = db.relationship('UserRole', lazy='joined')
def save_to_db(self):
db.session.add(self)
db.session.commit()
class StockItemSnapshot(UuidPrimaryKeyMixin, CreatedUpdatedTimestampMixin,
db.Model):
'''Represents the state of an item at any given time'''
stock_item_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('stock_item.id'),
nullable=False)
maintainer_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('maintainer.id'),
nullable=False)
unit_of_measure_id = db.Column(db.Integer,
db.ForeignKey('unit_of_measure.id'),
nullable=False)
quantity = db.Column(db.Float, nullable=False, default=0.0)
class Curriculum_Category(db.Model, BaseModelMixin):
category_id = db.Column(db.Integer,
db.ForeignKey('category.id'),
primary_key=True)
curriculum_id = db.Column(db.Integer,
db.ForeignKey('curriculum.id'),
primary_key=True)
category = db.relationship("Category")
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self):
pass
class Message(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String, nullable=False, default='unread')
text = db.Column(db.String)
created_by = db.Column(db.Integer,
db.ForeignKey('user.id'),
nullable=False)
to_interview = db.Column(db.Integer,
db.ForeignKey('interview.id'),
nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
read_at = db.Column(db.DateTime)
def __init__(self, created_by, text):
self.created_by = created_by
self.text = text
class Requirement(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
key = db.Column(db.String)
value = db.Column(db.String)
job_id = db.Column(db.Integer, db.ForeignKey('job.id'), nullable=False)
def __init__(self, key, value):
self.key = key
self.value = value
class StockItemState(TimestampMixin, db.Model):
stock_item_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock_item.id'))
packaging_kind_id = db.Column(UUID(as_uuid=True), db.ForeignKey('packaging_kind.id'))
packaging_state_id = db.Column(UUID(as_uuid=True), db.ForeignKey('packaging_state.id'))
quantity = db.Column(db.Integer, default=0)
# has lazy loaded 'backref'ed relationship field 'food_item'
packaging_kind = db.relationship('PackagingKind', lazy='joined')
packaging_state = db.relationship('PackagingState', lazy='joined')
def full_dict(self):
remove = ['packaging_kind_id', 'packaging_state_id']
return {
**{ key: getattr(self, key) for key in self.cols_dict().keys() if key not in remove },
'packaging_kind': self.packaging_kind,
'packaging_state': self.packaging_state
}
class Search(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.String)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, text, user_id):
self.text = text
self.user_id = user_id
class StockItem(TimestampMixin, db.Model):
stock_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock.id'))
food_kind_id = db.Column(UUID(as_uuid=True), db.ForeignKey('food_kind.id'))
date_item_was_new = db.Column(DATE(), default=datetime.utcnow)
expiration_date = db.Column(DATE(), nullable=False)
# 'states' field in StockItem instance is sqlalchemy query object which can
# be used to specify whether or not to grab all previous 'StockItemState's (a lot)
# or just the most recent one...or something else
states = db.relationship('StockItemState', lazy='dynamic', backref=db.backref('food_item', lazy=True))
food_kind = db.relationship('FoodKind', lazy='joined')
def full_dict(self):
current_state = self.states.order_by(text('date_created desc')).first()
return {
**self.cols_dict(),
'food_kind': self.food_kind.full_dict(),
'current_state': current_state.full_dict() if current_state else None,
}
class Language(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
curriculum_id = db.Column(db.Integer,
db.ForeignKey('curriculum.id'),
nullable=False)
def __init__(self, name):
self.name = name
class Rating(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String)
score = db.Column(db.Integer)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
company_id = db.Column(db.Integer,
db.ForeignKey('company.id'),
nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, description, score, user_id, company_id):
self.description = description
self.score = score
self.user_id = user_id
self.company_id = company_id
@classmethod
def get_by_user_id_company(cls, id, companyId):
return cls.query.filter(cls.user_id == id
and cls.company_id == companyId).first()
class Application(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String, nullable=False, default='created')
job_id = db.Column(db.Integer, db.ForeignKey('job.id'), nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, user_id):
self.user_id = user_id
@classmethod
def get_pag(cls, data):
filtro = cls.query
filtro = filtro.filter(cls.status == data['status'])
if 'user' in data:
filtro = filtro.filter(cls.user_id == data['user'])
if 'job' in data:
filtro = filtro.filter(cls.job_id == data['job'])
if 'order' in data and data['order'] == 'asc':
filtro = filtro.order_by(cls.created_at.asc())
else:
filtro = filtro.order_by(cls.created_at.desc())
if 'page' in data and 'per_page' in data:
return filtro.paginate(int(data['page']), int(data['per_page']),
False, 40)
else:
return filtro.paginate(1, 10, False)
@classmethod
def get_by_user_and_job(cls, user_id, job_id):
filtro = cls.query
filtro = filtro.filter(cls.user_id == user_id)
filtro = filtro.filter(cls.job_id == job_id)
return filtro.first()
class UserLogin(db.Model):
id = db.Column(db.Integer, primary_key=True)
date_created = db.Column(TIMESTAMP(timezone=True), default=datetime.utcnow)
user_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('registered_user.id'),
nullable=False)
def __repr__(self):
return f"UserLogin {self.user_id} - {self.date_created}"
def save_to_db(self):
db.session.add(self)
db.session.commit()
class Education(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
place = db.Column(db.String)
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
curriculum_id = db.Column(db.Integer,
db.ForeignKey('curriculum.id'),
nullable=False)
def __init__(self, name):
self.name = name
class Subcategory(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
jobs = db.relationship('Job', backref='subcategory', lazy=False, cascade='all, delete-orphan')
category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, name):
self.name = name
@classmethod
def getByName(cls, name):
return cls.query.filter(cls.name == name).first()
class RegisteredUser(db.Model):
id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
oauth_openid = db.Column(
db.String, unique=True) # if the user registered through OAuth2
username = db.Column(db.String)
email = db.Column(db.String, unique=True, nullable=False)
role_id = db.Column(db.Integer, db.ForeignKey('user_role.id'))
role = db.relationship('UserRole', lazy='joined')
logins = db.relationship('UserLogin', lazy='dynamic')
posts = db.relationship('Post', lazy='dynamic')
prompts = db.relationship('Prompt', lazy='dynamic')
def __repr__(self):
return f"RegisteredUser {self.id} - {self.email} - {self.username} - {self.role}"
@classmethod
def get_profile_user(cls, user_id):
'''Use this method to fetch data to display when any given logged on user views
their profile information'''
user = cls.query.get(user_id)
if user:
return dict(
id=user.id,
username=user.username,
email=user.email,
role=user.role,
posts=[p for p in user.posts.all()],
prompts=[p for p in user.prompts.all()],
)
@classmethod
def get_profile_admin(cls, user_id):
'''Use this method to fetch data about users for admins to review/use in their tasks'''
user = cls.query.get(user_id)
if user:
return dict(
id=user.id,
username=user.username,
email=user.email,
role=user.role,
posts=[p.with_reviews() for p in user.posts.all()],
prompts=[p.with_reviews() for p in user.prompts.all()],
logins=[l for l in user.logins.all()],
)
def save_to_db(self):
db.session.add(self)
db.session.commit()
class ItemModel(db.Model):
__tablename__ = "items"
id = db.Column(db.Integer, primary_key=True)
item = db.Column(db.String(80))
price = db.Column(db.Float(precision=2))
store_id = db.Column(db.Integer, db.ForeignKey("stores.id"))
store = db.relationship('StoreModel')
def __init__(self, item, price, store_id):
self.item = item
self.price = price
self.store_id = store_id
def json(self):
return {
"name": self.item,
"price": self.price,
"store_id": self.store_id
}
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(item=name).first()
# @classmethod
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 get_all(cls):
return cls.query.all()
@classmethod
def delete_all(cls):
return cls.query.delete()
class Stock(TimestampMixin, UserDefinedNameMixin, db.Model):
user_id = db.Column(UUID(as_uuid=True), db.ForeignKey('app_user.id'))
# each snapshot is a collection of 'stock_item_state's. a snapshot
# represents the state of a 'stock' at a given moment in time
snapshots = db.relationship('Snapshot',
lazy='dynamic',
backref=db.backref('stock', lazy=True))
# represents the 'food_item's in a 'stock'
stock_items = db.relationship('StockItem', lazy='joined')
def __init__(self, **kwargs):
super(Stock, self).__init__(**kwargs)
self.user_id = kwargs.get('user_id')
def full_dict(self):
return {
**self.cols_dict(), 'snapshots': [s for s in self.snapshots.all()],
'items': [item.full_dict() for item in self.stock_items]
}
class Role(db.Model):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(50), unique=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
def __init__(self, name):
self.name = name
def __repr__(self):
return '<Role {}>'.format(self.name)
def json(self):
return {"id": self.id, "name": self.name}, 200
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def find_role_by_name(cls, name):
return cls.query.filter_by(name=name).first()
class Snapshot(TimestampMixin, db.Model):
stock_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock.id'))
stock_item_states = db.relationship('StockItemState',
secondary='snapshot_stock_item_state',
lazy='subquery')
# has lazy loaded 'backref' prop 'stock'
from sqlalchemy.dialects.postgresql import UUID
from app.database.db import db
snapshot_stock_item_state = db.Table(
'snapshot_stock_item_state',
db.Column('snapshot_id', db.ForeignKey('snapshot.id'), primary_key=True),
db.Column('stock_item_state_id',
db.ForeignKey('stock_item_state.id'),
primary_key=True),
)
from app.database.db import db
user_role_permission = db.Table(
'user_role_permission',
db.Column('role_id',
db.Integer,
db.ForeignKey('user_role.id'),
primary_key=True),
db.Column('permission_id',
db.Integer,
db.ForeignKey('user_permission.id'),
primary_key=True))
class FoodKindNutritionInfo(TimestampMixin, db.Model):
food_kind_id = db.Column(UUID(as_uuid=True), db.ForeignKey('food_kind.id'), primary_key=True)
calories_per_serving = db.Column(db.Integer, default=0)
notes = db.Column(db.String)
class Job(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.String, nullable=False)
title = db.Column(db.String, nullable=False)
location = db.Column(db.String)
workday = db.Column(db.String)
contract_type = db.Column(db.String)
salary = db.Column(db.Integer)
salary_max = db.Column(db.Integer)
description = db.Column(db.String)
requirements = db.relationship('Requirement',
backref='job',
lazy=False,
cascade='all, delete-orphan')
applications = db.relationship('Application',
backref='job',
lazy=False,
cascade='all, delete-orphan')
company_id = db.Column(db.Integer, db.ForeignKey('company.id'))
interviews = db.relationship('Interview',
backref='job',
lazy=False,
cascade='all, delete-orphan')
subcategory_id = db.Column(db.Integer, db.ForeignKey('subcategory.id'))
active = db.Column(db.Boolean, default=True)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, url, title):
self.url = url
self.title = title
@classmethod
def get_pag(cls, data):
filtro = cls.query
filtro = filtro.filter(Job.active == True)
if 'search' in data and data['search'] is not None:
filtro = filtro.filter(
db.or_(Job.title.contains(data['search'].strip()),
Job.description.contains(data['search'].strip())))
if 'workday' in data and data['workday'] is not None:
filtro = filtro.filter(Job.workday == data['workday'])
if 'category' in data and data['category'] is not None:
categoryId = str(data['category'])
subcat = [
row for row in db.engine.execute(
'select * from subcategory where subcategory.category_id = '
+ categoryId)
]
filtro = filtro.filter(Job.subcategory_id == subcat[0]['id'])
if 'minSalary' in data and data['minSalary'] is not None:
filtro = filtro.filter(Job.salary > int(data['minSalary']))
if 'location' in data and data['location'] is not None:
if data['location'] == 'remote':
filtro = filtro.filter(Job.location == data['location'])
else:
filtro = filtro.filter(
func.lower(Job.location).contains(
data['location'].strip().lower()))
if 'freelance' in data and data[
'freelance'] is not None and data['freelance'] is True:
filtro = filtro.filter(func.lower(Job.url).contains('workana'))
if 'page' in data and 'per_page' in data:
return filtro.paginate(data['page'], data['per_page'], False, 40)
else:
return filtro.paginate(1, 10, False)
@classmethod
def get_pag_company(cls, data):
filtro = cls.query
if 'company_id' in data and data['company_id'] is not None:
filtro = filtro.filter(Job.company_id == data['company_id'])
if 'page' in data and 'per_page' in data:
return filtro.paginate(data['page'], data['per_page'], False, 40)
else:
return filtro.paginate(1, 10, False)
@classmethod
def search_by_title(cls, title):
filtro = cls.query.filter(Job.title.contains(title))
return filtro.paginate(1, 5, False).items
@classmethod
def search_by_url(cls, url):
filtro = cls.query.filter(Job.url == url)
filtro = filtro.filter(Job.active == True).first()
return filtro
@classmethod
def get_all_by_url(cls, url):
filtro = cls.query.filter(Job.url.contains(url))
return filtro
@classmethod
def get_id(cls):
filtro = db.engine.execute(
"select setval('job_id_seq', (select max(id) from job));")
return [row for row in filtro]
@classmethod
def search_by_id(cls, id):
filtro = cls.query.filter(Job.id == id)
filtro = filtro.filter(Job.active == True).first()
return filtro
from sqlalchemy.dialects.postgresql import UUID
from app.database.db import db
item_tag = db.Table(
'item_tag',
db.Column('item_id', UUID, db.ForeignKey('item.id'), primary_key=True),
db.Column('tag_id', UUID, db.ForeignKey('tag.id'), primary_key=True))
from sqlalchemy.dialects.postgresql import UUID
from app.database.db import db
stock_maintainer = db.Table(
'stock_maintainer',
db.Column('stock_id', UUID, db.ForeignKey('stock.id'), primary_key=True),
db.Column('maintainer_id',
UUID,
db.ForeignKey('maintainer.id'),
primary_key=True))
