class Post(db.Model, ResourceMixin):
__tablename__ = 'post'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
content = db.Column(db.String(1024), nullable=False)
# ===================== Relationships ==================================
account_id = db.Column(db.Integer,
db.ForeignKey('account.id'),
nullable=False)
account = db.relationship('Account',
backref=db.backref('posts', lazy=True))
@classmethod
def find(cls, identity):
return Post.query.filter((cls.title == identity)
| (cls.id == identity)).first()
# @classmethod
# def get_all_posts(cls):
# return Post.query.all()
@classmethod
def get_all_posts(cls, page=None):
if page is None:
return Post.query.order_by(Post.create_date.desc()).all()
else:
return Post.query.order_by(Post.create_date.desc()).paginate(
page=page, per_page=5)
class CityWeather(db.Model):
__tablename__ = 'cityweather'
id = db.Column(db.Integer, primary_key=True) # ID
time = db.Column(db.String(32))
city = db.Column(db.String(128)) # 城市名字
AQI = db.Column(db.String(32)) # AQI
quality = db.Column(db.String(8)) # 空气质量
pm = db.Column(db.String(32)) # pm2.5
class Book(db.Model):
id = db.Column(db.Integer, primary_key = True)
title = db.Column(db.String(100), nullable=False)
description = db.Column(db.String(200), nullable=False)
author_id = db.Column(db.Integer, db.ForeignKey('author.id'), nullable=False)
def __init__(self, title, description, author_id):
self.title = title
self.description = description
self.author_id = author_id
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(10), unique=True)
description = db.Column(db.String(200))
price = db.Column(db.Float)
qty = db.Column(db.Integer)
def __init__(self, name, description, price, qty):
self.name = name
self.description = description
self.price = price
self.qty = qty
class Author(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
book = db.relationship('Book', backref='author', lazy='dynamic')
def __init__(self, name):
self.name = name
class BookTags(db.Model):
__tablename__ = "book_tags"
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=True)
goodreads_book_id = db.Column(db.Integer)
tag_id = db.Column(db.Integer)
tag_name = db.Column(db.String(120))
count = db.Column(db.Integer)
@classmethod
def get_tags(cls, gid, n=5):
"""Gets list of tags for a given book.
Args:
gid ([string]): Goodreads book id provided in the dataset.
Encountered as `goodreads_book_id`
Returns:
[List]: List of tag objects, each of which has a two fields (tag_id, count)
"""
return [
x.__dict__ for x in (cls.query.filter_by(
goodreads_book_id=gid).order_by(cls.count.desc()).limit(n))
]
class BookTags(
db.Model
): # the table book_tags in the form of a class, that was able by SQL Alchemy ORM
__tablename__ = "book_tags" #identifying the name of the table
id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=True) #the id column
goodreads_book_id = db.Column(
db.Integer
) #the column containing the id of the books from the goodreads dataset
tag_id = db.Column(
db.Integer) #The column containing the id of the tags from the dataset
tag_name = db.Column(
db.String(120)
) #the column containing the name of the tag corresponding to the tag id
count = db.Column(db.Integer)
@classmethod
def get_tags(cls, gid, n=5):
"""Gets list of tags for a given book.
Args:
gid ([string]): Goodreads book id provided in the dataset.
Encountered as `goodreads_book_id`
Returns:
[List]: List of tag objects, each of which has a two fields (tag_id, count)
"""
return [
x.__dict__ for x in (cls.query.filter_by(
goodreads_book_id=gid).order_by(cls.count.desc()).limit(n))
]
class Books(db.Model): #the books table as a class where all the columns are identified
__tablename__ = "books"
id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=True)
book_id = db.Column(db.Integer)
goodreads_book_id = db.Column(db.Integer)
isbn13 = db.Column(db.Integer)
authors = db.Column(db.String(200))
original_publication_year = db.Column(db.Integer)
original_title = db.Column(db.String(120))
title = db.Column(db.String(400))
language_code = db.Column(db.String(40))
average_rating = db.Column(db.Float)
ratings_count = db.Column(db.Integer)
image_url = db.Column(db.String(200))
@classmethod
def get_paginated(cls, page):
return cls.query.paginate(page, 12, False).items
@classmethod
def get_list(cls, ids):
return cls.query.filter(cls.book_id.in_(ids))
@classmethod
def get_list_from_goodreads_ids(cls, ids):
return cls.query.filter(cls.goodreads_book_id.in_(ids))
@classmethod
def get_book(cls, id):
try:
res = cls.query.filter_by(book_id=id).first()
tags = BookTags.get_tags(res.__dict__.get("goodreads_book_id"), n=10)
tag_names = [ x.get("tag_name") for x in tags ]
res.__dict__["tags"] = tag_names
if res is not None:
return res.__dict__
return None
except Exception as e:
logging.error(e)
return None
class User(BaseModel, db.Model):
__tablename__ = 'user'
id = db.Column(db.BIGINT, primary_key=True)
name = db.Column(db.String(32), default="")
password = db.Column(db.String(256), default="")
ext_property = db.Column(db.JSON, default={})
is_available = db.Column(db.INT, default=1)
create_ts = db.Column(db.TIMESTAMP, default=datetime.now())
update_ts = db.Column(db.TIMESTAMP, default=datetime.now())
def is_active(self):
return self.id != None
def is_authenticated(self):
return self.id != None
def get_id(self):
return self.id
def is_anonymous(self):
return self.id == None
class Record(db.Model):
__tablename__ = 'record'
id = db.Column(db.String(30), primary_key=True)
score = db.Column(db.Integer, nullable=False, default=0)
def save(self):
db.session.add(self)
db.session.commit()
return self
@classmethod
def find(cls, id):
return Record.query.filter(cls.id == id).first()
class Task(db.Model):
id = db.Column(db.Integer, primary_key=True)
task_name = db.Column(db.String(80), unique=True)
date_created = db.Column(db.DateTime, default=datetime.datetime.utcnow)
date_modified = db.Column(db.DateTime, default=datetime.datetime.utcnow)
def __init__(self, task_name):
self.task_name = task_name
def __repr__(self):
return '<Task {self.task_name}>'
def to_json(self):
return {
'id': self.id,
'task_name': self.task_name,
'date_created': self.date_created,
'date_modified': self.date_modified
}
class Jobs(db.Model):
__tablename__="data"
sno=db.Column(db.Integer,primary_key=True)
company=db.Column(db.String(100))
location=db.Column(db.String(100))
role=db.Column(db.String(60))
type=db.Column(db.String(30))
sector=db.Column(db.String(50))
link=db.Column(db.String(200))
def __init__(self,sno,company,location,role,type,sector,link):
self.sno=sno
self.company=company
self.location=location
self.role=role
self.type=type
self.sector=sector
self.link=link
self.list=[]
class Account(db.Model, UserMixin, ResourceMixin):
__tablename__ = 'account'
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(50), nullable=False)
last_name = db.Column(db.String(50), nullable=False)
username = db.Column(db.String(30), nullable=False, unique=True)
email = db.Column(db.String(50), nullable=False, unique=True)
password = db.Column(db.String(1024), nullable=False)
active = db.Column('is_active',
db.Boolean(),
nullable=False,
server_default='1')
profile_pic = db.Column(db.String(300),
nullable=False,
default='static/images/default.jpg')
sign_in_count = db.Column(db.Integer, nullable=False, default=0)
current_sign_in_date = db.Column(db.DateTime)
current_sign_in_ip = db.Column(db.String(200))
last_sign_in_date = db.Column(db.DateTime)
last_sign_in_ip = db.Column(db.String(200))
@classmethod
def find(cls, identity):
return Account.query.filter((cls.email == identity)
| (cls.username == identity)).first()
def is_active(self):
return self.active
def serialize_token(self, expiration=300):
private_key = app.config['SECRET_KEY']
serializer = TimedJSONWebSignatureSerializer(private_key, expiration)
return serializer.dumps({'email': self.email}).decode('utf-8')
@classmethod
def deserialize_token(cls, token):
private_key = TimedJSONWebSignatureSerializer(app.config['SECRET_KEY'])
try:
decoded_payload = private_key.loads(token)
return Account.find(decoded_payload.get('email'))
except Exception:
return None
def update_activity_tracking(self, ip_address):
self.sign_in_count = self.sign_in_count + 1
self.last_sign_in_date = self.current_sign_in_date
self.last_sign_in_ip = self.current_sign_in_ip
self.current_sign_in_date = datetime.now()
self.current_sign_in_ip = ip_address
self.save()
def get_posts(self, page=None):
if page is None:
return self.posts
else:
return Post.query.filter(Post.account_id == self.id)\
.order_by(Post.create_date.desc())\
.paginate(page=page, per_page=5)
class Applicant(db.Model, ResourceMixin):
__tablename__ = 'applicant'
ATTAINMENT = OrderedDict([
('none', 'No formal education'),
('primary', 'Primary education'),
('secondary', 'Secondary education/High school'),
('ged', 'General Education Diploma'),
('vocational', 'Vocational qualification'),
('bachelor\'s', 'Bachelor\'s degree'),
('master\'s', 'Master\'s degree'),
('doctoral', 'Doctorate or higher')
])
SHIFT = OrderedDict([
('any', 'Any'),
('day', 'Day Shift'),
('mid', 'Mid Shift'),
('night', 'Night Shift'),
('shift', 'Shifting')
])
MARITAL_STATUS = OrderedDict([
('single', 'Single'),
('married', 'Married'),
('widowed', 'Widowed'),
('divorced', 'Divorced'),
('separated', 'Separated')
])
STATUS = OrderedDict([
('pending', 'Pending'),
('phone_invite', 'Phone Invite'),
('no_reach', 'Can\'t be reached'),
('declined', 'Declined'),
('interview', 'For interview'),
('passed', 'Passed'),
('failed', 'Failed'),
('hired', 'Hired')
])
id = db.Column(db.Integer, primary_key=True)
# Personal Information
last_name = db.Column(db.String(50), nullable=False)
first_name = db.Column(db.String(50), nullable=False)
middle_name = db.Column(db.String(50), nullable=True)
birthdate = db.Column(db.DateTime, nullable=True, default=datetime.strptime('0001-01-01 12:00 AM', '%Y-%m-%d %I:%M %p'))
email = db.Column(db.String(50), nullable=False, unique=True)
address = db.Column(db.String(300), nullable=True)
mobile1 = db.Column(db.String(11), nullable=False, unique=True)
mobile2 = db.Column(db.String(11), nullable=True)
landline = db.Column(db.String(11), nullable=True)
marital_status = db.Column(db.Enum(*MARITAL_STATUS, name='marital_status', native_enum=False),
index=True, nullable=False, server_default='single')
# Scholastic Information
educational_attainment = db.Column(db.Enum(*ATTAINMENT, name='educational_attainment', native_enum=False),
index=True, nullable=False, server_default='bachelor\'s')
course = db.Column(db.String(30), nullable=True)
graduation_year = db.Column(db.String(4), nullable=True)
# Job Preference
applied_position = db.Column(db.String(50), nullable=True)
expected_salary = db.Column(db.Integer, nullable=False, default=0)
preferred_shift = db.Column(db.Enum(*SHIFT, name='preferred_shift', native_enum=False),
index=True, nullable=False, server_default='any')
preferred_location = db.Column(db.String(50), nullable=True)
# Call Information
status = db.Column(db.Enum(*STATUS, name='status', native_enum=False),
index=True, nullable=False, server_default='pending')
remarks = db.Column(db.String(300), nullable=True)
# Additional Information
acquire_date = db.Column(db.DateTime, nullable=False, default=datetime.now())
source = db.Column(db.String(30), nullable=True)
interview_datetime = db.Column(db.DateTime, nullable=True, default=datetime.strptime('0001-01-01 12:00 AM', '%Y-%m-%d %I:%M %p'))
# ===================== Relationships ==================================
hr_id = db.Column(db.Integer, db.ForeignKey('account.id'))
hr = db.relationship('Account', backref=db.backref('applicants', lazy=True))
@classmethod
def find_applicant(cls, id):
return Applicant.query.filter(cls.id == id).first()
@classmethod
def count(cls):
return Applicant.query.count()
class Account(db.Model, ResourceMixin, UserMixin):
__tablename__ = 'account'
ROLE = OrderedDict([
('it', 'IT'),
('admin', 'HR Admin'),
('hr', 'HR')
])
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(50), nullable=False)
last_name = db.Column(db.String(50), nullable=False)
username = db.Column(db.String(30), nullable=False, unique=True)
email = db.Column(db.String(50), nullable=False, unique=True)
mobile = db.Column(db.String(11), nullable=False)
password = db.Column(db.String(50), nullable=False)
role = db.Column(db.Enum(*ROLE, name='role', native_enum=False),
index=True, nullable=False, server_default='it')
active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
profile_pic = db.Column(db.String(300), nullable=False, default='static/images/default.jpg')
# Activity tracking for IT
sign_in_count = db.Column(db.Integer, nullable=False, default=0)
current_sign_in_date = db.Column(db.DateTime)
current_sign_in_ip = db.Column(db.String(200))
last_sign_in_date = db.Column(db.DateTime)
last_sign_in_ip = db.Column(db.String(200))
@classmethod
def find_account(cls, identity):
return Account.query.filter((cls.username == identity)
| (cls.email == identity)).first()
@classmethod
def get_all_accounts(cls):
return Account.query.all()
@classmethod
def get_all_active_hr(cls):
return Account.query.filter(cls.role == 'hr' \
and cls.active == True).all()
@classmethod
def count(cls):
return Account.query.count()
@classmethod
def hr_count(cls):
return Account.query.filter(cls.role == 'hr' \
and cls.active == True).count()
def is_active(self):
return self.active
def update_activity_tracking(self, ip_address):
self.sign_in_count = self.sign_in_count + 1
self.last_sign_in_date = self.current_sign_in_date
self.last_sign_in_ip = self.current_sign_in_ip
self.current_sign_in_date = datetime.now()
self.current_sign_in_ip = ip_address
self.save()
class User(db.Model):
__tablename__ = "users"
user_id = db.Column(db.String(120), primary_key=True, unique=True)
name = db.Column(db.String(120))
email = db.Column(db.String(120), unique=True)
pwd = db.Column(db.String(120))
is_admin = db.Column(db.Boolean)
def __init__(self, name=None, email=None, password=None, is_admin=False):
self.user_id = member_token()
self.name = name
self.email = email
self.pwd = password
self.is_admin = is_admin
def __repr__(self):
return "email: {0},\n is_admin: {1}".format(self.email, self.is_admin)
@property
def is_active(self):
return True
@property
def is_authenticated(self):
return True
@property
def is_anonymous(self):
return False
def get_id(self):
return str(self.name)
def get_name(self):
return self.name
def get_books(self):
book_obj_lst = UserBooks.get_books(user_id=self.user_id)
bmap = {b.get("book_id"): b for b in book_obj_lst}
books = Books.get_list(ids=[b.get("book_id") for b in book_obj_lst])
res = []
for b in books:
b.__dict__["reading_state"] = bmap[
b.__dict__["book_id"]]["reading_state"]
res.append(b.__dict__)
return res
@classmethod
def verify_user(cls, email, password):
u = cls.query.filter_by(email=email).first()
if u is not None:
if verify_password(password, u.pwd):
return u
return None
@classmethod
def register(cls, name, email, is_admin, password=None):
try:
existing = cls.query.filter_by(email=email).first()
if existing:
raise EmailExistsError
if password is None or len(password) < 8:
raise PasswordTooShortError
hashed_password = salt_and_hash(password)
u = User(name=name,
email=email,
password=hashed_password,
is_admin=is_admin)
db.session.add(u)
db.session.commit()
return cls.query.filter_by(email=email).first()
except Exception as e:
raise e
@classmethod
def update_password(cls, email, password=None):
try:
if password is None:
return Exception
u = cls.query.filter_by(email=email).first()
hashed_password = salt_and_hash(password)
u.pwd = hashed_password
db.session.add(u)
db.session.commit()
return cls.query.filter_by(email=email).first()
except Exception as e:
print(e)
return None
@classmethod
def update(cls, user_email, **kwargs):
u = cls.query.filter_by(email=user_email).first()
if not u:
raise UserDoesNotExistError
if kwargs.get("email"):
print("Updating email: ", kwargs.get("email"))
u.email = kwargs.get("email")
if kwargs.get("name"):
print("Updating name: ", kwargs.get("name"))
u.name = kwargs.get("name")
if kwargs.get("password"):
print("Updating password: "******"password"))
u.pwd = salt_and_hash(kwargs.get("password"))
if kwargs.get("is_admin"):
print("Updating is_admin: ", kwargs.get("is_admin"))
u.is_admin = kwargs.get("is_admin")
db.session.add(u)
db.session.commit()
return True
@classmethod
def update_password(cls, email, password):
u = cls.query.filter_by(email=email).first()
u.pwd = salt_and_hash(password)
db.session.add(u)
db.session.commit()
return True
@classmethod
def update_email(cls, old_email, email):
u = cls.query.filter_by(email=old_email).first()
email_is_taken = cls.query.filter_by(email=email).first()
if email_is_taken:
raise EmailExistsError
u.email = email
db.session.add(u)
db.session.commit()
return True
@classmethod
def update_name(cls, email, name):
u = cls.query.filter_by(email=email).first()
u.name = name
db.session.add(u)
db.session.commit()
return True
@classmethod
def delete(cls, email):
if email is not None:
u = cls.query.filter_by(email=email).first()
db.session.delete(u)
db.session.commit()
return True
return False
