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)