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()