class Opportunity(db.Model):
__tablename__ = 'opportunity'
# table columns
id = db.Column(db.String, primary_key=True)
program_id = db.Column(db.Integer,
db.ForeignKey('program.id'),
nullable=False)
title = db.Column(db.String(200), nullable=False)
short_description = db.Column(db.String(2000), nullable=False)
gdoc_id = db.Column(db.String(200))
gdoc_link = db.Column(db.String(200), nullable=False)
card_id = db.Column(db.String)
program_name = db.Column(db.String)
stage = db.Column(db.Integer, default=1)
org_name = db.Column(db.String(255), nullable=False)
is_active = db.Column(db.Boolean, default=True, nullable=False)
# relationships
applications = db.relationship('OpportunityApp',
back_populates='opportunity',
cascade='all, delete, delete-orphan')
program = db.relationship('Program', back_populates='opportunities')
@hybrid_property
def status(self):
return OpportunityStage(self.stage)
class Resume(db.Model):
__tablename__ = 'resume'
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey("contact.id"),
nullable=False)
name = db.Column(db.String(100), nullable=False)
date_created = db.Column(db.Date, nullable=False)
gdoc_id = db.Column(db.String(255))
class User(BaseModel, UserMixin):
__tablename__ = 'users'
username = db.Column(db.String(20))
# 存取密码的散列值
password_hash = db.Column(db.String(128))
avatar = db.Column(db.String(60), default='avatar_default.png')
def set_hash_password(self, password):
self.password_hash = generate_password_hash(password)
db.session.commit()
def validate_password(self, password):
return check_password_hash(self.password_hash, password)
def set_avatar(self, new_avatar):
self.avatar = new_avatar
db.session.commit()
@property
def is_admin(self):
"""判断是否是 admin 用户, 账号硬编码为 admin, admin"""
admin = self.__class__.get(username='******')
if self.password_hash != admin.password_hash or self.username != admin.username:
return False
return True
@classmethod
def validate_login(cls, form):
username = form.get('username', '')
password = form.get('password', '')
user = cls.get(username=username)
if user is not None and user.validate_password(password):
return user
return None
@classmethod
def register(cls, form):
username = form.get('username', '')
password = form.get('password', '')
if username and password and cls.get(username=username) is None:
# 检查用户名不能重复
user = cls.new(username=username)
user.set_hash_password(password)
return user
return None
def __eq__(self, other):
if not isinstance(other, User):
return False
return self.name == other.name
def __hash__(self):
return hash(self.name)
class Movie(BaseModel):
__tablename__ = 'movies'
title = db.Column(db.String(60))
year = db.Column(db.String(4))
def __eq__(self, other):
if not isinstance(other, Movie):
return False
return self.title == other.title \
and self.year == other.year
def __hash__(self):
return hash((self.title, self.year))
class ProgramContact(db.Model):
__tablename__ = 'program_contact'
#table columns
id = db.Column(db.Integer, nullable=False, primary_key=True)
contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'), nullable=False)
program_id = db.Column(db.Integer, db.ForeignKey('program.id'), nullable=False)
card_id = db.Column(db.String(25))
stage = db.Column(db.Integer)
is_approved = db.Column(db.Boolean, default=False)
is_active = db.Column(db.Boolean, default=True)
#relationship fields
program = db.relationship('Program', back_populates='contacts')
contact = db.relationship('Contact', back_populates='programs')
# for more info on why to use setattr() read this:
# https://medium.com/@s.azad4/modifying-python-objects-within-the-sqlalchemy-framework-7b6c8dd71ab3
def update(self, **update_dict):
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
db.session.commit()
@hybrid_property
def applications(self):
return [app for app in self.contact.applications
if app.program_id == self.program_id]
class OpportunityApp(db.Model):
__tablename__ = 'opportunity_app'
#table columns
id = db.Column(db.String, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
opportunity_id = db.Column(db.String,
db.ForeignKey('opportunity.id'),
nullable=False)
resume_id = db.Column(db.Integer,
db.ForeignKey('resume_snapshot.id'),
nullable=True)
interest_statement = db.Column(db.String(2000), nullable=True)
stage = db.Column(db.Integer, nullable=False, default=0)
is_active = db.Column(db.Boolean, nullable=False, default=True)
interview_date = db.Column(db.Date)
interview_time = db.Column(db.String)
resume = db.relationship('ResumeSnapshot')
contact = db.relationship('Contact', back_populates='applications')
opportunity = db.relationship('Opportunity')
__table_args__ = (db.Index('oppapp_contact_opportunity',
'contact_id',
'opportunity_id',
unique=True), )
#calculated fields
@hybrid_property
def status(self):
return ApplicationStage(self.stage)
@hybrid_property
def program_id(self):
return self.opportunity.program_id
@hybrid_property
def interview_completed(self):
if self.interview_date and self.interview_time:
interview_scheduled = dt.datetime.strptime(
f'{self.interview_date} {self.interview_time}',
'%Y-%m-%d %H:%M:%S')
return interview_scheduled < dt.datetime.now()
else:
return False
# for more info on why to use setattr() read this:
# https://medium.com/@s.azad4/modifying-python-objects-within-the-sqlalchemy-framework-7b6c8dd71ab3
def update(self, **update_dict):
for field, value in update_dict.items():
print(field, value)
if field in UPDATE_FIELDS:
setattr(self, field, value)
class Token(BaseModel):
__tablename__ = "token"
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
token = db.Column(db.String(250))
token1 = db.Column(db.String(250))
@staticmethod
async def generate_key():
return binascii.hexlify(os.urandom(20)).decode()
@staticmethod
async def get_or_create(user_id):
check = await Token.query.where(Token.user_id == user_id).gino.first()
if check:
return check.token
key = await Token.generate_key()
token = await Token.create(user_id=user_id, token=key)
return token.token
@staticmethod
async def check_token(token):
return await Token.query.where(Token.token == token).gino.first()
class Email(db.Model):
__tablename__ = 'email'
#table columns
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
is_primary = db.Column(db.Boolean, default=False)
email = db.Column(db.String(100), nullable=False)
type = db.Column(db.Enum(Type, name='EmailType'))
#relationships
contact = db.relationship('Contact', back_populates='email_primary')
class ResumeSection(db.Model):
__tablename__ = 'resume_section'
#table columns
id = db.Column(db.Integer, primary_key=True)
resume_id = db.Column(db.Integer, db.ForeignKey('resume.id'), nullable=False)
name = db.Column(db.String(100), nullable=False)
min_count = db.Column(db.Integer)
max_count = db.Column(db.Integer)
#relationships
#resume = db.relationship('Resume', back_populates='sections')
items = db.relationship('ResumeItem', back_populates='section',
cascade='all, delete, delete-orphan')
class Program(db.Model):
#table columns
id = db.Column(db.Integer, nullable=False, primary_key=True)
name = db.Column(db.String(100), nullable=False)
trello_board_id = db.Column(db.String)
#relationship fields
program_apps = db.relationship('ProgramApp',
back_populates='program',
cascade='all, delete, delete-orphan')
opportunities = db.relationship('Opportunity',
back_populates='program',
cascade='all, delete, delete-orphan')
class UserSession(UserMixin, db.Model):
__tablename__ = 'user_session'
#table columns
# This is the session id, not the user's id
# This means it changes every time the user starts a new session
id = db.Column(db.String, primary_key=True)
auth_id = db.Column(db.String, nullable=False)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
jwt = db.Column(db.String(1000), nullable=False)
expiration = db.Column(db.DateTime,
default=datetime.utcnow,
nullable=False)
#relationships
contact = db.relationship('Contact')
@hybrid_property
def is_authenticated(self):
return self.expiration > datetime.utcnow()
class Tag(db.Model):
__tablename__ = 'tag'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
type = db.Column(db.Enum(TagType, name='TagType'), nullable=False)
status = db.Column(db.Enum(TagStatusType, name='TagStatusType'))
class Template(db.Model):
__tablename__ = "template"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
template_url = db.Column(db.String(500), nullable=False)
json = db.Column(db.String(500), nullable=False)
class Contact(db.Model):
__tablename__ = 'contact'
#table columns
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(100), nullable=False)
last_name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String)
phone_primary = db.Column(db.String(25))
account_id = db.Column(db.String(255), nullable=True)
terms_agreement = db.Column(db.Boolean, default=False)
stage = db.Column(db.Integer, default=1)
card_id = db.Column(db.String)
#relationships
emails = db.relationship('Email',
back_populates='contact',
cascade='all, delete, delete-orphan')
email_primary = db.relationship("Email",
primaryjoin=db.and_(
id == Email.contact_id,
Email.is_primary == True),
uselist=False)
addresses = db.relationship('ContactAddress', back_populates='contact')
address_primary = db.relationship('ContactAddress',
primaryjoin=db.and_(
id == ContactAddress.contact_id,
ContactAddress.is_primary == True),
back_populates='contact',
uselist=False)
achievements = db.relationship('Achievement',
back_populates='contact',
cascade='all, delete, delete-orphan')
skill_items = db.relationship('ContactSkill',
cascade='all, delete, delete-orphan')
capability_skill_suggestions = db.relationship(
'CapabilitySkillSuggestion', cascade='all, delete, delete-orphan')
experiences = db.relationship('Experience',
back_populates='contact',
cascade='all, delete, delete-orphan')
program_apps = db.relationship('ProgramApp',
back_populates='contact',
cascade='all, delete, delete-orphan')
programs_interested = db.relationship('ProgramApp',
primaryjoin=db.and_(
id == ProgramApp.contact_id,
ProgramApp.is_interested),
back_populates='contact')
applications = db.relationship('OpportunityApp',
back_populates='contact',
cascade='all, delete, delete-orphan')
sessions = db.relationship('UserSession',
cascade='all, delete, delete-orphan')
profile = db.relationship('Profile',
back_populates='contact',
uselist=False,
cascade='all, delete, delete-orphan')
race = db.relationship('Race',
back_populates='contact',
cascade='all, delete, delete-orphan',
uselist=False)
def add_skill(self, skill):
contact_skill = (ContactSkill.query.filter_by(
contact_id=self.id, skill_id=skill.id).first())
if contact_skill:
contact_skill.deleted = False
else:
contact_skill = ContactSkill(skill, self)
self.skill_items.append(contact_skill)
return contact_skill
@hybrid_property
def skills(self):
skills = []
for skill_item in self.skill_items:
if not skill_item.deleted:
skills.append(skill_item.skill)
return sorted(skills, key=lambda skill: skill.name)
@hybrid_property
def email_main(self):
if not self.email:
return self.email_primary.email
else:
return self.email
@hybrid_property
def tag_skills_complete(self):
return (len(self.skills) >= 3)
@hybrid_property
def add_experience_complete(self):
complete_experience = [
exp for exp in self.experiences if exp.type == Type('Work')
and exp.tag_skills_complete and exp.add_achievements_complete
]
status = (len(complete_experience) >= 1)
exp_dict = {
'is_complete': status,
'components': {
'tag_skills': status,
'add_achievements': status
}
}
return exp_dict
@hybrid_property
def add_education_complete(self):
complete_education = [
exp for exp in self.experiences if exp.type == Type('Education')
]
return (len(complete_education) >= 1)
@hybrid_property
def add_portfolio_complete(self):
complete_portfolio = [
exp for exp in self.experiences
if exp.type == Type('Accomplishment')
]
return (len(complete_portfolio) >= 1)
@hybrid_property
def profile_complete(self):
profile_status = (self.add_experience_complete['is_complete']
and self.add_education_complete)
profile_dict = {
'is_complete': profile_status,
'components': {
'tag_skills': self.tag_skills_complete,
'add_experience': self.add_experience_complete,
'add_education': self.add_education_complete,
'add_portfolio': self.add_portfolio_complete,
}
}
return profile_dict
@hybrid_property
def about_me_complete(self):
if self.profile:
about_me_status = (self.profile.candidate_info_complete
and self.profile.value_alignment_complete
and self.profile.interests_and_goals_complete
and
self.profile.programs_and_eligibility_complete)
about_me_dict = {
'is_complete': about_me_status,
'components': {
'candidate_information':
self.profile.candidate_info_complete,
'value_alignment': self.profile.value_alignment_complete,
'programs': self.profile.programs_and_eligibility_complete,
'interests': self.profile.interests_and_goals_complete,
}
}
else:
about_me_dict = {
'is_complete': False,
'components': {
'candidate_information': False,
'value_alignment': False,
'programs': False,
'interests': False,
}
}
return about_me_dict
@hybrid_property
def submit_complete(self):
return {'is_complete': self.stage >= 2}
@hybrid_property
def status(self):
return ContactStage(self.stage)
@hybrid_property
def race_list(self):
race_dict = {
'american_indian': 'American Indian or Alaskan Native',
'asian': 'Asian',
'black': 'Black or African Descent',
'hawaiian': 'Native Hawaiian or Other Pacific Islander',
'hispanic': 'Hispanic or Latinx',
'south_asian': 'South Asian',
'white': 'White',
'not_listed': 'Not Listed',
}
race_list = [
race_dict[r] for r in race_dict.keys() if self.race.getattr(r)
]
return race_list
@hybrid_property
def instructions(self):
instructions_dict = {
'profile': self.profile_complete,
'about_me': self.about_me_complete,
'submit': self.submit_complete
}
return instructions_dict
def query_program_contact(self, program_id):
return next((p for p in self.programs if p.program_id == program_id),
None)
def update(self, **update_dict):
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
class Experience(db.Model):
__tablename__ = 'experience'
# table columns
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String)
host = db.Column(db.String, nullable=False)
title = db.Column(db.String, nullable=False)
degree_other = db.Column(db.String(100))
degree = db.Column(db.String(100))
link = db.Column(db.String)
link_name = db.Column(db.String)
start_month = db.Column(db.Enum(Month, name='MonthType'), nullable=False)
start_year = db.Column(db.Integer, nullable=False)
end_month = db.Column(db.Enum(Month, name='MonthType'), nullable=False)
end_year = db.Column(db.Integer, nullable=False)
type = db.Column(db.Enum(Type, name='ExperienceType'))
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
location = db.Column(db.String(255))
# relationships
contact = db.relationship('Contact')
achievements = db.relationship('Achievement',
back_populates='experience',
cascade='all, delete, delete-orphan')
skill_items = db.relationship('ExperienceSkill',
cascade='all, delete, delete-orphan')
def add_skill(self, skill):
contact_skill = self.contact.add_skill(skill)
exp_skill = (ExperienceSkill.query.filter_by(
experience_id=self.id, parent_id=contact_skill.id).first())
if not exp_skill:
exp_skill = ExperienceSkill(contact_skill, self)
self.skill_items.append(exp_skill)
return exp_skill
# calculated fields
@hybrid_property
def skills(self):
skills = []
for skill_item in self.skill_items:
if not skill_item.deleted:
skills.append(skill_item.skill)
return sorted(skills, key=lambda skill: skill.name)
@hybrid_property
def date_end(self):
if self.end_month == Month.none or self.end_year == 0:
return dt.datetime.today()
else:
end_str = f'1 {self.end_month.value}, {self.end_year}'
return dt.datetime.strptime(end_str, '%d %B, %Y')
@hybrid_property
def date_start(self):
if self.start_month == Month.none or self.start_year == 0:
return dt.datetime.today()
else:
start_str = f'1 {self.start_month.value}, {self.start_year}'
return dt.datetime.strptime(start_str, '%d %B, %Y')
@hybrid_property
def date_length(self):
end = self.date_end
start = self.date_start
return (end.year - start.year) * 12 + end.month - start.month
@hybrid_property
def length_year(self):
return math.floor(self.date_length / 12)
@hybrid_property
def length_month(self):
return self.date_length % 12
@hybrid_property
def is_current(self):
if self.end_month == Month.none or self.end_year == 0:
return True
else:
return False
class Contact(db.Model):
__tablename__ = 'contact'
#table columns
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(100), nullable=False)
last_name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String)
phone_primary = db.Column(db.String(25))
account_id = db.Column(db.String(255), nullable=True)
terms_agreement =db.Column(db.Boolean, default=False)
stage = db.Column(db.Integer, default=1)
#relationships
emails = db.relationship('Email',
back_populates='contact',
cascade='all, delete, delete-orphan')
email_primary = db.relationship("Email",
primaryjoin=db.and_(
id == Email.contact_id,
Email.is_primary == True),
uselist=False)
addresses = db.relationship('ContactAddress',
back_populates='contact')
address_primary = db.relationship('ContactAddress',
primaryjoin=db.and_(
id == ContactAddress.contact_id,
ContactAddress.is_primary == True),
back_populates='contact',
uselist=False)
achievements = db.relationship('Achievement',
back_populates='contact',
cascade='all, delete, delete-orphan')
skill_items = db.relationship('ContactSkill',
cascade='all, delete, delete-orphan')
capability_skill_suggestions = db.relationship(
'CapabilitySkillSuggestion',
cascade='all, delete, delete-orphan'
)
experiences = db.relationship('Experience',
back_populates='contact',
cascade='all, delete, delete-orphan')
programs = db.relationship('ProgramContact',
back_populates='contact',
cascade='all, delete, delete-orphan')
program_apps = db.relationship('ProgramApp',
back_populates='contact',
cascade='all, delete, delete-orphan')
applications = db.relationship('OpportunityApp',
back_populates='contact',
cascade='all, delete, delete-orphan')
sessions = db.relationship('UserSession',
cascade='all, delete, delete-orphan')
profile = db.relationship('Profile',
back_populates='contact',
uselist=False,
cascade='all, delete, delete-orphan')
race = db.relationship('Race',
back_populates='contact',
cascade='all, delete, delete-orphan',
uselist=False)
def add_skill(self, skill):
contact_skill = (ContactSkill.query
.filter_by(contact_id=self.id,
skill_id=skill.id)
.first())
if contact_skill:
contact_skill.deleted = False
else:
contact_skill = ContactSkill(skill, self)
self.skill_items.append(contact_skill)
return contact_skill
@hybrid_property
def skills(self):
skills = []
for skill_item in self.skill_items:
if not skill_item.deleted:
skills.append(skill_item.skill)
return sorted(skills, key=lambda skill: skill.name)
@hybrid_property
def email_main(self):
if not self.email:
return self.email_primary.email
else:
return self.email
def query_program_contact(self, program_id):
return next((p for p in self.programs
if p.program_id == program_id), None)
def update(self, **update_dict):
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
