class Organizations(db.Model):
__tablename__ = "organizations"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
description = db.Column(db.String(80))
def __init__(self, name, description):
self.name = name
self.description = description
def json(self):
return {
"id": self.id,
"name": self.name,
"description": self.description
}, 200
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def find_org_by_name(cls, name):
return cls.query.filter_by(name=name).first()
@classmethod
def get_all_organizations(cls):
return cls.query.all()
@classmethod
def test(cls):
return True
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 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 User(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(256), unique=True, nullable=False)
password = db.Column(db.String(256), nullable=False)
role = db.Column(db.String(20), nullable=False, default="cliente")
curriculum = db.relationship('Curriculum',
backref='user',
lazy=False,
cascade='all, delete-orphan')
applications = db.relationship('Application',
backref='user',
lazy=False,
cascade='all, delete-orphan')
notifications = db.relationship('Notification',
backref='user',
lazy=False,
cascade='all, delete-orphan')
ratings = db.relationship('Rating', backref='user', lazy=False)
#messages = db.relationship('Message', backref='user', lazy=False, cascade='all, delete-orphan')
interviews = db.relationship('Interview',
backref='user',
lazy=False,
cascade='all, delete-orphan')
#para estadisticas
created_at = db.Column(db.DateTime, default=datetime.utcnow)
last_login = db.Column(db.DateTime)
def __init__(self, email, password):
self.email = email
self.password = Bcrypt().generate_password_hash(password).decode()
def password_is_valid(self, password):
return Bcrypt().check_password_hash(self.password, password)
@classmethod
def get_by_email(cls, email):
return cls.query.filter(User.email == email).first()
@classmethod
def search_by_email(cls, email):
filtro = cls.query.filter(User.email.contains(email))
return filtro.paginate(1, 5, False).items
class RevokedToken(db.Model):
jti = db.Column(db.String(120), primary_key=True)
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def is_revoked(cls, jti):
return bool(cls.query.filter_by(jti=jti).first())
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 StoreModel(db.Model):
__tablename__ = "stores"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
items = db.Column(db.String(80))
items = db.relationship("ItemModel", lazy="dynamic")
# why use lazy dynamic -- > creates a query builder and therefore we have an option
def __init__(self, name):
self.name = name
def json(self):
return {
"name": self.name,
"items": [item.json() for item in self.items.all()]
}
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=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 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 UserModel(db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80))
password = db.Column(db.String(30))
def __init__(self, username, password):
self.username = username
self.password = password
@classmethod
def find_by_username(cls, name):
return cls.query.filter_by(username=name).first()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
def save(self):
db.session.add(self)
db.session.commit()
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), nullable=False, unique=True)
password = db.Column(db.String(), nullable=False, server_default='')
name = db.Column(db.String(50))
email = db.Column(db.String(50))
roles = db.relationship('Role', backref='user', lazy='dynamic')
def __init__(self, username, password, name, email):
self.username = username
self.password = password
self.name = name
def json(self):
return {
"id": self.id,
"username": self.username,
"name": self.name,
"email": self.email
}, 200
def save_to_db(self):
db.session.add(self)
db.session.commit()
def remove_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_user_by_username(cls, username):
return cls.query.filter_by(username=username).first()
@classmethod
def find_user_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
class AttributeModel(db.Model):
__tablename__ = "attributes"
id = db.Column(db.Integer, primary_key=True)
product_id = db.Column(db.Integer)
attribute_id = db.Column(db.Integer)
attribute_key = db.Column(db.String(50))
attribute_value = db.Column(db.String(80))
def __init__(self, product_id, attribute_id, attribute_key,
attribute_value):
self.product_id = product_id
self.attribute_id = attribute_id
self.attribute_key = attribute_key
self.attribute_value = attribute_value
def json(self):
return {
"id": self.id,
"product_id": self.product_id,
"attribute_id": self.attribute_id,
"attribute_key": self.attribute_key,
"attribute_value": self.attribute_value
}, 200
def save_to_db(self):
db.session.add(self)
db.session.commit()
def remove_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def get_attr_by_id(cls, attribute_id):
return cls.query.filter_by(
attribute_id=attribute_id).first().attribute_value
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 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 Curriculum(db.Model, BaseModelMixin):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
birth_date = db.Column(db.DateTime)
phone = db.Column(db.String)
country = db.Column(db.String)
address = db.Column(db.String)
avatar = db.Column(db.String)
description = db.Column(db.String)
education = db.relationship('Education',
backref='curriculum',
lazy=False,
cascade='all, delete-orphan')
workexperience = db.relationship('WorkExperience',
backref='curriculum',
lazy=False,
cascade='all, delete-orphan')
languages = db.relationship('Language',
backref='curriculum',
lazy=False,
cascade='all, delete-orphan')
categories = db.relationship('Curriculum_Category')
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __init__(self, name):
self.name = name
@classmethod
def get_by_user_id(cls, id):
return cls.query.filter(cls.user_id == id).first()
@classmethod
def get_all_by_job_id(cls, id):
res = db.engine.execute(
'select distinct u.* from "user" as u, job as j, curriculum as c, category as cat, subcategory as sc, curriculum__category as cc where j.id = '
+ str(id) +
' and j.subcategory_id = sc.id and cc.category_id = sc.category_id and cc.curriculum_id = c.id and u.id = c.user_id'
)
return [row for row in res]
@classmethod
def get_all_recommended_users_by_filters(cls, data):
sqlQueryStringBeforeWhere = 'select distinct u.* from "user" as u, curriculum as c, category as cat'
sqlQueryStringAfterWhere = ' where u."role" = ' + "'cliente' and c.user_id = u.id"
if 'category' in data['filters'] and data['filters'][
'category'] is not None:
sqlQueryStringBeforeWhere += ', curriculum__category as cc'
sqlQueryStringAfterWhere += ' and cc.curriculum_id = c.id and u.id = c.user_id and cat.id = ' + str(
data['filters']['category']) + ' and cc.category_id = cat.id'
if 'language' in data['filters'] and data['filters'][
'language'] is not None and len(
data['filters']['language']
) > 0 and data['filters']['language'][0] != ' ':
sqlQueryStringBeforeWhere += ', language as l'
sqlQueryStringAfterWhere += " and l.name = '" + data['filters'][
'language'] + "' and l.curriculum_id = c.id"
if 'ageRange' in data['filters'] and data['filters'][
'ageRange'] is not None:
if 'maxAge' in data['filters']['ageRange'] and data['filters'][
'ageRange']['maxAge']:
sqlQueryStringAfterWhere += " and DATE_PART('year', now()::date) - DATE_PART('year', c.birth_date::date) <= " + str(
data['filters']['ageRange']['maxAge'])
if 'minAge' in data['filters']['ageRange'] and data['filters'][
'ageRange']['minAge']:
sqlQueryStringAfterWhere += " and DATE_PART('year', now()::date) - DATE_PART('year', c.birth_date::date) >= " + str(
data['filters']['ageRange']['minAge'])
sqlQueryString = sqlQueryStringBeforeWhere + sqlQueryStringAfterWhere + ';'
print(sqlQueryString)
res = db.engine.execute(sqlQueryString)
return [row for row in res]
class Prompt(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(240), nullable=False)
status = db.Column(db.String,
db.ForeignKey('review_status_kind.name'),
default='pending')
posts = db.relationship('Post',
lazy='dynamic',
backref=db.backref('prompt',
lazy='joined',
uselist=False))
reviews = db.relationship('PromptReview', lazy='dynamic')
# def as_dict(self, include_reviews=False):
# ret = {}
# if include_reviews:
# ret.update({'reviews': self.reviews.all()})
# ret.update({ c.name: getattr(self, c.name) for c in self.__table__.columns })
# return ret
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def get_paginated(cls, status='', include_reviews=False, desc=True):
result = cls.query.filter_by(status=status)\
.order_by(cls.date_created.desc() if desc else cls.date_created.asc())\
.paginate()
return dict(page=result.page,
has_next=result.has_next,
per_page=result.per_page,
items=[x.as_dict(include_reviews) for x in result.items])
@classmethod
def new_to_old(cls, status='', cursor=None, limit=50):
if not status:
raise ValueError('no status provied')
conditions = cls.status == status, # tuple
if cursor:
prompt = cls.query.get(cursor)
conditions = cls.status == status, cls.date_created < prompt.date_created # tuple
return cls.query.filter(*conditions)\
.order_by(cls.date_created.desc())\
.limit(limit)\
.all()
@classmethod
def old_to_new(cls, status='', cursor=None, limit=50):
if not status:
raise ValueError('no status provied')
conditions = cls.status == status,
if cursor:
prompt = cls.query.get(cursor)
conditions = cls.status == status, cls.date_created > prompt.date_created
return cls.query.filter(*conditions)\
.order_by(cls.date_created.asc())\
.limit(limit)\
.all()
