class HealthServiceTimeSlot(db.Model):
__tablename__ = 'health_service_timeslots'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
start = db.Column('start',
db.DateTime(timezone=True),
nullable=False,
info={'label': u'เริ่ม'})
end = db.Column('end',
db.DateTime(timezone=True),
nullable=False,
info={'label': u'สิ้นสุด'})
service_id = db.Column(db.ForeignKey('health_service_services.id'))
site_id = db.Column(db.ForeignKey('health_service_sites.id'))
staff_id = db.Column(db.ForeignKey('staff_account.id'), nullable=False)
created_by = db.relationship('StaffAccount')
quota = db.Column(db.Integer, info={'label': 'Quota'})
cancelled_at = db.Column(db.DateTime(timezone=True))
site = db.relationship(HealthServiceSite, backref=db.backref('slots'))
service = db.relationship(HealthServiceService,
backref=db.backref('slots'))
@property
def remaining(self):
return self.quota - len(self.bookings)
@property
def is_available(self):
return self.remaining > 0
class DocReceiveRecord(db.Model):
__tablename__ = 'doc_receive_records'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
predefined_comment = db.Column(
db.String(255),
info={
'label':
'Predefined Comment',
'choices':
[(c, c) for c in
[u'แจ้งเพื่อทราบ', u'แจ้งเพื่อพิจารณา', u'ขอความร่วมมือเข้าร่วม']]
})
comment = db.Column(db.Text, info={'label': 'Additional Comment'})
sent_at = db.Column(db.DateTime(timezone=True))
sender_id = db.Column(db.ForeignKey('staff_account.id'))
round_org_id = db.Column(db.ForeignKey('doc_round_orgs.id'))
round_org = db.relationship(DocRoundOrg,
backref=db.backref('sent_records',
lazy='dynamic'))
doc_id = db.Column(db.ForeignKey('doc_documents.id'))
rejected = db.Column(db.Boolean(), default=False)
members = db.relationship(StaffPersonalInfo, secondary=receipt_receivers)
sender = db.relationship(StaffAccount)
doc = db.relationship(DocDocument,
backref=db.backref('doc_receipts',
lazy='dynamic',
cascade='all, delete-orphan'))
class EduQACourse(db.Model):
__tablename__ = 'eduqa_courses'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
th_code = db.Column(db.String(255), nullable=False, info={'label': u'รหัส'})
en_code = db.Column(db.String(255), nullable=False, info={'label': u'English Code'})
th_name = db.Column(db.String(255), nullable=False, info={'label': u'ชื่อภาษาไทย'})
en_name = db.Column(db.String(255), nullable=False, info={'label': u'English Title'})
semester = db.Column(db.String(), info={'label': u'ภาคการศึกษา'})
academic_year = db.Column(db.String(), info={'label': u'ปีการศึกษา'})
th_desc = db.Column(db.Text(), info={'label': u'คำอธิบายรายวิชา'})
en_desc = db.Column(db.Text(), info={'label': u'Description'})
lecture_credit = db.Column(db.Numeric(), default=0, info={'label': u'หน่วยกิตบรรยาย'})
lab_credit = db.Column(db.Numeric(), default=0, info={'label': u'หน่วยกิตปฏิบัติ'})
created_at = db.Column(db.DateTime(timezone=True))
updated_at = db.Column(db.DateTime(timezone=True))
creator_id = db.Column(db.ForeignKey('staff_account.id'))
updater_id = db.Column(db.ForeignKey('staff_account.id'))
creator = db.relationship(StaffAccount, foreign_keys=[creator_id])
updater = db.relationship(StaffAccount, foreign_keys=[updater_id])
category_id = db.Column(db.ForeignKey('eduqa_course_categories.id'))
category = db.relationship(EduQACourseCategory,
backref=db.backref('courses', lazy='dynamic'))
revision_id = db.Column(db.ForeignKey('eduqa_curriculum_revisions.id'))
revision = db.relationship(EduQACurriculumnRevision,
backref=db.backref('courses', lazy='dynamic'))
@property
def credits(self):
return self.lecture_credit + self.lab_credit
class EduQAInstructor(db.Model):
__tablename__ = 'eduqa_course_instructors'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
account_id = db.Column(db.ForeignKey('staff_account.id'))
account = db.relationship(StaffAccount, backref=db.backref('instructed_courses',
lazy='dynamic'))
courses = db.relationship('EduQACourse',
secondary=course_instructors,
backref=db.backref('instructors', lazy='dynamic'))
@property
def fullname(self):
return self.account.personal_info.fullname
class HealthServiceBooking(db.Model):
__tablename__ = 'health_service_bookings'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
slot_id = db.Column(db.ForeignKey('health_service_timeslots.id'))
slot = db.relationship(HealthServiceTimeSlot,
backref=db.backref('bookings'))
cancelled = db.Column(db.DateTime(timezone=True))
created_at = db.Column(db.DateTime(timezone=True))
updated_at = db.Column(db.DateTime(timezone=True))
confirmed_at = db.Column(db.DateTime(timezone=True))
user_id = db.Column(db.ForeignKey('health_service_app_users.id'))
user = db.relationship('HealthServiceAppUser',
backref=db.backref('bookings'))
class DocDocument(db.Model):
__tablename__ = 'doc_documents'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
round_id = db.Column(db.ForeignKey('doc_rounds.id'))
round = db.relationship(DocRound,
backref=db.backref('documents',
lazy='dynamic',
order_by='DocDocument.number',
cascade='all, delete-orphan'))
number = db.Column(db.Integer(), info={'label': u'Number'})
deadline = db.Column(db.DateTime(timezone=True),
info={'label': 'Deadline'})
addedAt = db.Column(db.DateTime(timezone=True))
file_name = db.Column(db.String(255))
url = db.Column(db.String(255))
priority = db.Column(db.String(255),
info={
'label':
'Priority',
'choices':
[(c, c)
for c in [u'ปกติ', u'ด่วน', u'ด่วนที่สุด']]
})
stage = db.Column(db.String(255),
info={
'label':
'Stage',
'choices':
[(c, c) for c in [u'drafting', u'ready', u'sent']]
})
title = db.Column(db.String(255), info={'label': 'Title'})
summary = db.Column(db.Text(), info={'label': 'Summary'})
comment = db.Column(db.Text(), info={'label': 'Comment'})
category_id = db.Column(db.ForeignKey('doc_categories.id'))
category = db.relationship(DocCategory,
backref=db.backref(
'documents',
lazy='dynamic',
cascade='all, delete-orphan'))
def get_recipients(self, round_org_id):
receipt = self.doc_receipts.filter_by(round_org_id=round_org_id,
doc_id=self.id).first()
if receipt:
return receipt.members
else:
return []
class Test(db.Model):
__tablename__ = 'tests'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, nullable=False, unique=True)
results = db.relationship('TestResult',
cascade="delete",
backref=db.backref('test', lazy=True))
class Role(db.Model):
__tablename__ = 'sys_role'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(64), unique=True, comment=u'角色名称')
describe = db.Column(db.String(200), unique=True, comment=u'角色描述')
status = db.Column(db.Integer, default=1, comment=u'状态')
createTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'创建时间')
createBy = db.Column(db.String(50), comment=u'创建人')
updateTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'修改时间')
updateBy = db.Column(db.String(50), comment=u'修改人')
# @property
# def permissions(self):
# permissions = Permission.query.join(sys_role_permission).join(Role).filter(Role.id == self.id)
# return permissions
menus = db.relationship('Menu', secondary=sys_role_menu, backref=db.backref('menus', lazy='dynamic'))
# @property
# def menus(self):
# menus = Menu.query.join(sys_role_menu).join(Role).filter(Role.id == self.id).order_by(Menu.type, Menu.order).all()
# return menus
@property
def permission_names(self):
strs = ''
for i in range(len(self.menus)):
strs = strs + self.menus[i].name + ','
return strs
@property
def create_time(self):
return self.createTime.strftime('%Y-%m-%d')
@property
def menu_ids(self):
ids = []
for i in range(len(self.menus)):
if self.menus[i].url is not None:
ids.append(self.menus[i].id)
return ids
def __init__(self):
pass
def save(self):
globals.db.add(self)
globals.db.commit()
def update(self):
globals.db.commit()
def delete(self):
globals.db.delete(self)
globals.db.commit()
def __repr__(self):
return '<Role %r>' % self.name
class EduQACurriculumnRevision(db.Model):
__tablename__ = 'eduqa_curriculum_revisions'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
curriculum_id = db.Column(db.ForeignKey('eduqa_curriculums.id'))
curriculum = db.relationship(EduQACurriculum, backref=db.backref('revisions'))
revision_year = db.Column(db.Date(), nullable=False, info={'label': u'วันที่ปรับปรุงล่าสุด'})
def __str__(self):
return u'{}: ปี {}'.format(self.curriculum, self.revision_year)
class VehicleBooking(db.Model):
__tablename__ = 'scheduler_vehicle_bookings'
id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True)
vehicle_id = db.Column('vehicle_id',
db.ForeignKey('scheduler_vehicle_resources.id'),
nullable=False)
vehicle = db.relationship('VehicleResource',
backref=db.backref('bookings'))
title = db.Column('title', db.String(255), nullable=False)
init_milage = db.Column('init_milage', db.Integer, nullable=True)
end_milage = db.Column('end_milage', db.Integer, nullable=True)
toll_fee = db.Column('toll_fee', db.Float(), default=0.0)
distance = db.Column('distance', db.Integer, nullable=True)
init_location = db.Column('init_location', db.String(255), nullable=True)
destination = db.Column('destination', db.String(255), nullable=True)
start = db.Column('start', db.DateTime(timezone=True), nullable=False)
end = db.Column('end', db.DateTime(timezone=True), nullable=False)
iocode_id = db.Column('iocode_id', db.ForeignKey('iocodes.id'))
iocode = db.relationship('IOCode', backref=db.backref('vehicle_bookings'))
org_id = db.Column('org_id', db.ForeignKey('orgs.id'))
org = db.relationship('Org', backref=db.backref('vehicle_bookings'))
num_passengers = db.Column('num_passengers', db.Integer())
approved = db.Column('approved', db.Boolean(), default=False)
closed = db.Column('closed', db.Boolean(), default=False)
created_at = db.Column('created_at',
db.DateTime(timezone=True),
server_default=func.now())
created_by = db.Column('created_by', db.ForeignKey('staff_account.id'))
updated_at = db.Column('updated_at',
db.DateTime(timezone=True),
server_default=None)
updated_by = db.Column('updated_by', db.ForeignKey('staff_account.id'))
cancelled_at = db.Column('cancelled_at',
db.DateTime(timezone=True),
server_default=None)
cancelled_by = db.Column('cancelled_by', db.ForeignKey('staff_account.id'))
approved_by = db.Column('approved_by', db.ForeignKey('staff_account.id'))
approved_at = db.Column('approved_at',
db.DateTime(timezone=True),
server_default=None)
desc = db.Column('desc', db.Text())
google_event_id = db.Column('google_event_id', db.String(64))
google_calendar_id = db.Column('google_calendar_id', db.String(255))
class EduQAAcademicStaff(db.Model):
__tablename__ = 'eduqa_academic_staff'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
roles = db.Column(db.String(), info={'label': u'บทบาท',
'choices': (
('staff', u'อาจารย์ประจำ'),
('head', u'ประธานหลักสูตร'),
('committee', u'ผู้รับผิดชอบหลักสูตร')
)})
curriculumn_id = db.Column(db.ForeignKey('eduqa_curriculum_revisions.id'))
curriculumn = db.relationship(EduQACurriculumnRevision, backref=db.backref('staff'))
class SmartClassOnlineAccount(db.Model):
__tablename__ = 'smartclass_scheduler_online_accounts'
id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
name = db.Column('name', db.String(), nullable=False)
resource_type_id = db.Column(
'resource_type_id',
db.ForeignKey('smartclass_scheduler_resource_types.id'))
resource_type = db.relationship(SmartClassResourceType,
backref=db.backref('resources'))
def __str__(self):
return self.name
class EduQACurriculum(db.Model):
__tablename__ = 'eduqa_curriculums'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
program_id = db.Column(db.ForeignKey('eduqa_programs.id'),
)
program = db.relationship(EduQAProgram, backref=db.backref('curriculums'))
th_name = db.Column(db.String(), nullable=False,
info={'label': u'ชื่อ'})
en_name = db.Column(db.String(), nullable=False,
info={'label': 'Title'})
def __str__(self):
return self.th_name
class DocRoundOrg(db.Model):
__tablename__ = 'doc_round_orgs'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
sent_at = db.Column(db.DateTime(timezone=True))
round_id = db.Column(db.ForeignKey('doc_rounds.id'))
org_id = db.Column(db.ForeignKey('orgs.id'))
finished_at = db.Column(db.DateTime(timezone=True))
org = db.relationship('Org')
round = db.relationship(DocRound,
backref=db.backref('targets',
lazy='dynamic',
cascade='all, delete-orphan'))
class DocDocumentReach(db.Model):
__tablename__ = 'doc_document_reaches'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
reached_at = db.Column(db.DateTime(timezone=True))
reacher_id = db.Column(db.ForeignKey('staff_account.id'))
reacher = db.relationship(StaffAccount,
backref=db.backref('doc_reaches',
lazy='dynamic'))
doc_id = db.Column(db.ForeignKey('doc_documents.id'))
starred = db.Column(db.Boolean(False))
note = db.Column(db.Text())
doc = db.relationship(DocDocument,
backref=db.backref('reaches',
lazy='dynamic',
cascade='all, delete-orphan'))
round_org_id = db.Column(db.ForeignKey('doc_round_orgs.id'))
round_org = db.relationship(DocRoundOrg,
backref=db.backref('doc_reaches',
lazy='dynamic'))
sender_comment = db.Column(db.Text())
receiver_comment = db.Column(db.Text())
receiver_commented_at = db.Column(db.DateTime(timezone=True))
created_at = db.Column(db.DateTime(timezone=True))
class EduQACourseSession(db.Model):
__tablename__ = 'eduqa_course_sessions'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
course_id = db.Column(db.ForeignKey('eduqa_courses.id'))
start = db.Column(db.DateTime(timezone=True), nullable=False, info={'label': u'เริ่ม'})
end = db.Column(db.DateTime(timezone=True), nullable=False, info={'label': u'สิ้นสุด'})
type_ = db.Column(db.String(255), info={'label': u'รูปแบบการสอน',
'choices': [(c, c) for c in (u'บรรยาย', u'ปฏิบัติการ', u'กิจกรรม', u'สอบ')]})
desc = db.Column(db.Text())
course = db.relationship(EduQACourse, backref=db.backref('sessions', lazy='dynamic'))
instructors = db.relationship('EduQAInstructor',
secondary=session_instructors,
backref=db.backref('sessions', lazy='dynamic'))
@property
def total_hours(self):
delta = self.end - self.start
return u'{} ชม. {} นาที'.format(delta.seconds//3600, (delta.seconds//60)%60)
@property
def total_seconds(self):
delta = self.end - self.start
return delta.seconds
class DocRoundOrgReach(db.Model):
__tablename__ = 'doc_round_org_reaches'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
reached_at = db.Column(db.DateTime(timezone=True))
reacher_id = db.Column(db.ForeignKey('staff_account.id'))
reacher = db.relationship(StaffAccount)
created_at = db.Column(db.DateTime(timezone=True))
round_org_id = db.Column(db.ForeignKey('doc_round_orgs.id'))
round_org = db.relationship(DocRoundOrg,
backref=db.backref(
'round_reaches',
lazy='dynamic',
cascade='all, delete-orphan'))
class RoomResource(db.Model):
__tablename__ = 'scheduler_room_resources'
id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True)
location = db.Column('location', db.String(length=16))
number = db.Column('number', db.String(16))
occupancy = db.Column('occupancy', db.Integer(), nullable=False)
desc = db.Column('desc', db.Text())
business_hour_start = db.Column('business_hour_start', db.Time())
business_hour_end = db.Column('business_hour_end', db.Time())
availability_id = db.Column('availability_id',
db.ForeignKey('scheduler_room_avails.id'))
type_id = db.Column('type_id', db.ForeignKey('scheduler_room_types.id'))
reservations = db.relationship('RoomEvent', backref='room')
equipments = db.relationship(AssetItem, backref=db.backref('room'))
def __repr__(self):
return u'Room: {}, ID: {}'.format(self.number, self.id)
class SmartClassOnlineAccountEvent(db.Model):
__tablename__ = 'smartclass_scheduler_online_account_events'
id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True)
account_id = db.Column(
'account_id',
db.ForeignKey('smartclass_scheduler_online_accounts.id'),
nullable=False)
title = db.Column('title',
db.String(255),
nullable=False,
info={'label': u'กิจกรรม'})
start = db.Column('start',
db.DateTime(timezone=True),
nullable=False,
info={'label': u'เริ่ม'})
end = db.Column('end',
db.DateTime(timezone=True),
nullable=False,
info={'label': u'สิ้นสุด'})
occupancy = db.Column('occupancy',
db.Integer(),
info={'label': u'ผู้เข้าร่วม'})
approved = db.Column('approved', db.Boolean(), default=True)
created_at = db.Column('created_at',
db.DateTime(timezone=True),
server_default=func.now())
created_by = db.Column('created_by', db.ForeignKey('staff_account.id'))
creator = db.Column('creator',
db.String(),
nullable=True,
info={'label': u'ชื่อผู้ลงทะเบียน'})
updated_at = db.Column('updated_at',
db.DateTime(timezone=True),
server_default=None)
updated_by = db.Column('updated_by', db.ForeignKey('staff_account.id'))
cancelled_at = db.Column('cancelled_at',
db.DateTime(timezone=True),
server_default=None)
cancelled_by = db.Column('cancelled_by', db.ForeignKey('staff_account.id'))
approved_by = db.Column('approved_by', db.ForeignKey('staff_account.id'))
approved_at = db.Column('approved_at',
db.DateTime(timezone=True),
server_default=None)
note = db.Column('note', db.Text(), info={'label': u'หมายเหตุ'})
account = db.relationship(SmartClassOnlineAccount,
backref=db.backref('events'))
class Challenge(db.Model):
""" Challenge Model for storing recipe challenges """
__tablename__ = "challenge"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
recipe_id = db.Column(db.Integer, db.ForeignKey('recipe.id'))
recipe = db.relationship("Recipe",
backref=db.backref("challenge", uselist=False))
start_date = db.Column(db.DateTime)
end_date = db.Column(db.DateTime)
@hybrid_property
def num_contestants(self):
return self.recipe.remix_count
@hybrid_property
def top_three(self):
return self.recipe.remixes[:3]
class RoomEvent(db.Model):
__tablename__ = 'scheduler_room_reservations'
id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True)
room_id = db.Column('room_id',
db.ForeignKey('scheduler_room_resources.id'),
nullable=False)
category_id = db.Column('category_id',
db.ForeignKey('scheduler_event_categories.id'))
category = db.relationship('EventCategory', backref=db.backref('events'))
title = db.Column('title', db.String(255), nullable=False)
start = db.Column('start', db.DateTime(timezone=True), nullable=False)
end = db.Column('end', db.DateTime(timezone=True), nullable=False)
iocode_id = db.Column('iocode_id', db.ForeignKey('iocodes.id'))
occupancy = db.Column('occupancy', db.Integer())
# number of sets of food/refreshment requested
refreshment = db.Column('refreshment', db.Integer(), default=0)
request = db.Column('request', db.Text()) # comma separated list of things
approved = db.Column('approved', db.Boolean(), default=True)
created_at = db.Column('created_at',
db.DateTime(timezone=True),
server_default=func.now())
created_by = db.Column('created_by', db.ForeignKey('staff_account.id'))
updated_at = db.Column('updated_at',
db.DateTime(timezone=True),
server_default=None)
updated_by = db.Column('updated_by', db.ForeignKey('staff_account.id'))
cancelled_at = db.Column('cancelled_at',
db.DateTime(timezone=True),
server_default=None)
cancelled_by = db.Column('cancelled_by', db.ForeignKey('staff_account.id'))
approved_by = db.Column('approved_by', db.ForeignKey('staff_account.id'))
approved_at = db.Column('approved_at',
db.DateTime(timezone=True),
server_default=None)
extra_items = db.Column('extra_items', db.JSON)
note = db.Column('note', db.Text())
google_event_id = db.Column('google_event_id', db.String(64))
google_calendar_id = db.Column('google_calendar_id', db.String(255))
class Post(db.Model):
"""
Description of Post model.
Rows
-----------
:post_id: int [pk]
:authorId: varchar [ref: > users.id]
:title: varchar
:body: text
:post_time: timestamp [not null]
:avg_rating: float
:num_rating: int
"""
# Columns
post_id = db.Column(db.Integer, primary_key=True)
# _author_id = db.Column(db.String(256), db.ForeignKey(
# 'user.username'), nullable=False)
author_name = db.Column(db.String(256), nullable=False)
title = db.Column(db.String(128), nullable=False)
body = db.Column(db.Text, nullable=False)
post_time = db.Column(db.DateTime, default=datetime.datetime.now())
avg_rating = db.Column(db.Float, default=0.0)
num_rating = db.Column(db.Integer, default=0)
# Relationships
# author = db.relationship('User', backref='posts', lazy=False)
tags = db.relationship('Tag', secondary=postTagJunction, lazy='subquery',
backref=db.backref('posts', lazy=True))
images = db.relationship('ImgLink', secondary=imgPostJunction,
lazy='subquery')
def __init__(self, author, title, body):
self.author_name = author
self.title = title
self.body = body
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
@staticmethod
def getArticlesByTags(tagList, connector='OR'):
"""
Get all articles that have a tag in tagList.
If connector is AND intersection of all posts set for each tag will be
returned. If it is OR, union will be returned
"""
stmt = select([Post,
postTagJunction.c.post_id.label("pid"),
postTagJunction.c.tag_id]).distinct().where(and_(postTagJunction.c.post_id == Post.post_id,
postTagJunction.c.tag_id.in_(tagList)))
result = db.session.execute(stmt)
results = result.fetchall()
# return results
# taking only unique values
unique_results = dict()
# print(results.c)
for result in results:
# print(dict(result))
if unique_results.get(result.post_id, 0) == 0:
unique_results[result.post_id] = result
# print(unique_results[1].post_id)
return unique_results.values()
@staticmethod
def getArticles(post_id):
return Post.query.filter_by(post_id=post_id).first()
@staticmethod
def getRandomizedArticles(size):
return sample(Post.query.all(), size)
def addTags(self, list_of_tags):
self.tags.extend(list_of_tags)
db.session.commit()
def associateImage(self, imgId):
img = ImgLink.query.filter_by(id=imgId).first()
self.images.append(img)
db.session.commit()
def tagDump(self):
dump = []
for tag in self.tags:
dump.append(tag.name)
return dump
def linkDump(self):
dump = []
for img in self.images:
dump.append(img.link)
return dump
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
posts = db.relationship("Post", backref="author", lazy="dynamic")
comments = db.relationship("Comment", backref="author", lazy="dynamic")
about_me = db.Column(db.String(140))
last_seen = db.Column(db.DateTime, default=datetime.utcnow)
followed = db.relationship(
"User",
secondary=followers,
primaryjoin=(followers.c.follower_id == id),
secondaryjoin=(followers.c.followed_id == id),
backref=db.backref("followers", lazy="dynamic"),
lazy="dynamic",
)
posts_liked = db.relationship(
"Post",
secondary=likes,
primaryjoin=(likes.c.user_id == id),
secondaryjoin=(likes.c.post_id == id),
backref=db.backref("likes", lazy="dynamic"),
lazy="dynamic",
)
messages_sent = db.relationship('Message', foreign_keys='Message.sender_id', backref='author', lazy='dynamic')
messages_received = db.relationship('Message', foreign_keys='Message.recipient_id', backref='recipient', lazy='dynamic')
last_message_read_time = db.Column(db.DateTime)
notifications = db.relationship('Notification', backref='user', lazy='dynamic')
def __repr__(self):
return f"<User: {self.username}>"
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password_hash, password)
def avatar(self, size):
digest = md5(self.email.lower().encode("utf-8")).hexdigest()
return f"https://www.gravatar.com/avatar/{digest}?d=identicon&s={size}"
def follow(self, user):
if not self.is_following(user):
self.followed.append(user)
def unfollow(self, user):
if self.is_following(user):
self.followed.remove(user)
def is_following(self, user):
return self.followed.filter(followers.c.followed_id == user.id).count() > 0
def followed_posts(self):
followed = Post.query.join(
followers, (followers.c.followed_id == Post.user_id)
).filter(followers.c.follower_id == self.id)
return followed.union(self.posts).order_by(Post.timestamp.desc())
def like_post(self, post):
if not self.like_post.liked(post):
self.posts_liked.append(post)
def unlike_post(self, post):
if self.liked(post):
self.like_post.remove(post)
def liked(self, post):
return self.posts_liked.filter(likes.c.post_id==post.id).count() > 0
def get_reset_password_token(self, expires_in=600):
return jwt.encode(
{"reset_password": self.id, "exp": time() + expires_in},
current_app.config["SECRET_KEY"],
algorithm="HS256",
)
@staticmethod
def verify_reset_password_token(token):
try:
id = jwt.decode(
token, current_app.config["SECRET_KEY"], algorithms=["HS256"]
)["reset_password"]
except:
return
return User.query.get(id)
def new_messages(self):
last_read_time = self.last_message_read_time or datetime(1900, 1, 1)
return Message.query.filter_by(recipient=self).filter(
Message.timestamp > last_read_time
).count()
def add_notification(self, name, data):
self.notifications.filter_by(name=name).delete()
n = Notification(name=name, payload_json=json.dumps(data), user=self)
db.session.add(n)
return n
def notify_followers(self, name, data):
for follower in self.followers:
follower.notifications.filter_by(name=name).delete()
follower.add_notification(name, data)
class UserEntity(db.Model, UserMixin, CRUDMixin):
""" Stores the basic information about the user.
Implements the functions as required by:
https://flask-login.readthedocs.org/en/latest/
"""
__tablename__ = 'User'
id = db.Column("usrID", db.Integer, primary_key=True)
email = db.Column("usrEmail", db.String(255), nullable=False, unique=True)
first = db.Column("usrFirst", db.String(255), nullable=False)
last = db.Column("usrLast", db.String(255), nullable=False)
minitial = db.Column("usrMI", db.String(1), nullable=False)
added_at = db.Column("usrAddedAt",
db.DateTime,
nullable=False,
server_default='0000-00-00 00:00:00')
modified_at = db.Column("usrModifiedAt", db.TIMESTAMP, nullable=False)
email_confirmed_at = db.Column("usrEmailConfirmedAt",
db.DateTime,
nullable=False,
server_default='0000-00-00 00:00:00')
active = db.Column("usrIsActive",
db.Boolean(),
nullable=False,
server_default='1')
access_expires_at = db.Column("usrAccessExpiresAt",
db.DateTime,
nullable=False,
server_default='0000-00-00 00:00:00')
password_hash = db.Column("usrPasswordHash",
db.String(255),
nullable=False,
server_default='')
# @OneToMany
roles = db.relationship(RoleEntity,
secondary=UserRoleEntity.__tablename__,
backref=db.backref('users'),
lazy='dynamic')
"""
`lazy` defines when SQLAlchemy will load the data from the:
'select' (which is the default) means that SQLAlchemy will load the
data as necessary in one go using a standard select statement.
'joined' tells SQLAlchemy to load the relationship in the same query as the
parent using a JOIN statement.
'subquery' works like 'joined' but instead SQLAlchemy will use a subquery.
'dynamic' is special and useful if you have may items. Instead of loading
the items SQLAlchemy will return another query object which you can
further refine before loading them items. This is usually what you want
if you expect more than a handful of items for this relationship.
"""
def is_active(self):
""" An user can be blocked by setting a flag in the """
return self.active
def is_expired(self):
""" An user can be blocked by setting expiration date to yesterday"""
return self.access_expires_at < datetime.today()
def is_anonymous(self):
""" Flag instances of valid users """
return False
def is_authenticated(self):
""" Returns True if the user is authenticated, i.e. they have provided
valid credentials.
(Only authenticated users will fulfill the criteria of login_required.)
"""
return True
def get_id(self):
""" The id encrypted in the session """
return unicode(self.id)
def get_roles(self):
""" Return text representation of user roles """
return [role.name for role in self.roles]
def get_name(self):
"""
:rtype string
:return concat(first, ' ', last)
"""
return "{} {}".format(self.first.encode('utf-8'),
self.last.encode('utf-8'))
def get_email_verification_token(self, salt, secret):
"""
:rtype string
:return the email verification token stored in the
"""
return utils.get_email_token(self.email, salt, secret)
def serialize(self):
"""Return object data for jsonification"""
return {
'id':
self.id,
'email':
self.email,
'roles': [r.name for r in self.roles],
'first':
self.first,
'last':
self.last,
'minitial':
self.minitial,
'is_active':
True if self.active else False,
'is_expired':
True if self.is_expired() else False,
'added_at':
utils.localize_est_date(self.added_at),
'email_confirmed_at':
utils.localize_est_datetime(self.email_confirmed_at),
'access_expires_at':
utils.localize_est_datetime(self.access_expires_at)
}
def __repr__(self):
return "<UserEntity (usrID: {0.id}, usrEmail: {0.email}, " \
"usrIsActive: {0.active})>".format(self)
class User(db.Model, UserMixin):
"""
Description of User model.
Columns
-----------
:id: int [pk]
:username: varchar(128) [not NULL]
:password: varchar(128) [not NULL]
:first_name: varchar(255) [not NULL]
:last_name: varchar(255)
:dob: date
:email: varchar(255) [not NULL]
:fb_handle: varchar(255)
:g_handle: varchar(255)
:medium_handle: varchar(255)
:twitter_handle: varchar(255)
:linkedin_handle: varchar(255)
:bio: text
:occupation: varchar(255)
:profile_picture: int
:last_login: timestamp
:creation_time: timestamp
:is_verified: boolean
"""
# Columns
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(128), unique=True, nullable=False)
password = db.Column(db.String(128), nullable=False)
first_name = db.Column(db.String(255), default="")
last_name = db.Column(db.String(255), default="")
dob = db.Column(db.DateTime)
email = db.Column(db.String(255), nullable=False)
fb_handle = db.Column(db.String(255))
g_handle = db.Column(db.String(255))
medium_handle = db.Column(db.String(255))
twitter_handle = db.Column(db.String(255))
linkedin_handle = db.Column(db.String(255))
profile_picture = db.Column(db.Integer)
bio = db.Column(db.Text)
occupation = db.Column(db.String(255))
last_login = db.Column(db.DateTime)
creation_time = db.Column(db.DateTime)
is_verified = db.Column(db.Boolean, default=False)
# Relationships
tags = db.relationship('Tag', secondary=userTagJunction, lazy='subquery',
backref=db.backref("users", lazy=True))
saves = db.relationship('Post', secondary=userPostInteraction, lazy=True,
backref=db.backref("savers", lazy=True))
payments = db.relationship('Payment', backref='user', lazy=True)
def __init__(self, username, password, email):
self.username = username
self.password = generate_password_hash(password)
self.email = email
self.is_verified = False
self.profile_picture = 1
db.session.add(self)
db.session.commit()
@staticmethod
@login_manager.user_loader
def load_user(id):
return User.query.filter_by(id=id).first()
def update_col(self, key, value):
self.__dict__[key] = value
db.session.commit()
def check_password(self, password):
return check_password_hash(self.password, password)
def resetPassword(self, newPassword):
# Pass in a hashed password
self.password = generate_password_hash(newPassword)
db.session.commit()
def addPayment(self, data):
pay = Payment(self, data.get("amount"), data.get("api_response"))
self.payments.append(pay)
db.session.commit()
def get_id(self):
return self.id
# We do not need to implement update metadata.
# Actually, it can be updated ad hoc by assignment without calling commit.
def isVerified(self):
return self.is_verified
def setVerified(self):
self.is_verified = True
db.session.commit()
def setNewTag(self, tag):
self.tags.append(tag)
db.session.commit()
def setTagPriority(self, tag, priority):
result = self.getTagPriority(tag)
if result is None:
self.setNewTag(tag)
s = userTagJunction.update().\
values(priority=PriorityType(priority)).\
where(and_(
userTagJunction.c.user_id == self.id,
userTagJunction.c.keyword_id == tag.id))
db.session.execute(s)
db.session.commit()
def getTagPriority(self, tag):
s = select([userTagJunction]).where(and_(
userTagJunction.c.user_id == self.id,
userTagJunction.c.keyword_id == tag.id))
result = list(db.session.execute(s))
try:
return result[0]["priority"]
except IndexError:
return None
def savePost(self, post):
if post not in self.saves:
self.saves.append(post)
else:
self.saves.remove(post)
db.session.commit()
def ratePost(self, post, rating):
try:
s = userPostInteraction.update().\
where(user_id=self.id, post_id=post.post_id).\
values(rating=rating)
db.session.execute(s)
except BaseException:
# User has not yet saved the post so there is no entry here
s = userPostInteraction.insert().\
values(save=False, rating=rating,
user_id=self.id, post_id=post.post_id)
db.session.execute(s)
finally:
db.session.commit()
class UserModel(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
email = db.Column(db.String(255), unique=True, nullable=False)
username = db.Column(db.String(50), unique=True)
password_hash = db.Column(db.String(100))
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
card_lists = db.relationship('CardListModel',
secondary=user_card_list,
backref=db.backref('users', lazy='dynamic'))
cards = db.relationship('CardModel', backref='user', lazy='dynamic')
comments = db.relationship('CommentModel', backref='user', lazy='dynamic')
@property
def password(self):
raise AttributeError('password: write-only field')
@password.setter
def password(self, password):
self.password_hash = flask_bcrypt.generate_password_hash(
password).decode('utf-8')
def check_password(self, password):
return flask_bcrypt.check_password_hash(self.password_hash, password)
def json(self):
return {'id': self.id, 'email': self.email, 'username': self.username}
@staticmethod
def encode_auth_token(user_id):
"""
Generates the Auth Token
:return: string
"""
try:
payload = {
'exp':
datetime.datetime.utcnow() +
datetime.timedelta(days=1, seconds=5),
'iat':
datetime.datetime.utcnow(),
'sub':
user_id
}
return jwt.encode(payload, key, algorithm='HS256')
except Exception as e:
return e
@staticmethod
def decode_auth_token(auth_token):
"""
Decodes the auth token
:param auth_token:
:return: integer|string
"""
try:
payload = jwt.decode(auth_token, key, algorithms='HS256')
is_blacklisted_token = BlacklistToken.check_blacklist(auth_token)
if is_blacklisted_token:
return 'Token blacklisted. Please log in again.'
else:
return payload['sub']
except jwt.ExpiredSignatureError:
return 'Signature expired. Please log in again.'
except jwt.InvalidTokenError:
return 'Invalid token. Please log in again.'
def __repr__(self):
return f"<User name:'{self.username}', id:'{self.id}', role: {self.role}>"
class Post(Base, SearchableMixin):
"""
Description of User model.
Columns
-----------
:id: int [pk]
:title: Text [not NULL]
:author_id: int [Foreign Key]
:creation_time: DateTime [not NULL]
:last_edit_time: DateTime [not NULL]
:post_body: Text
# Relationships
:comments: Relationship -> Comments (one to many)
"""
# Columns
id = db.Column(db.Integer, db.ForeignKey("base.id"), primary_key=True)
post_id = db.Column(db.Integer,
autoincrement=True,
primary_key=True,
unique=True)
title = db.Column(db.Text, nullable=False)
post_movie = db.Column(db.String(20))
tags = db.Column(db.JSON)
__searchable__ = ['title', 'body', 'tags']
__mapper_args__ = {
'polymorphic_identity': 'post',
'inherit_condition': (id == Base.id)
}
comments = db.relationship(
'Comment',
primaryjoin="(Post.post_id == Comment.parent_post_id)",
backref=db.backref('post'),
lazy='dynamic')
def __init__(self, author_id, post_movie, title, post_body, tags):
super().__init__(author_id, post_body, "post")
self.title = title
self.post_movie = post_movie
self.tags = tags
db.session.add(self)
db.session.commit()
def add_comment(self, author_id, comment_body):
parent_post_id = self.id
comment = Comment(author_id, parent_post_id, comment_body)
self.comments.append(comment)
db.session.commit()
return comment.id
def update_col(self, key, value):
setattr(self, key, value)
db.session.commit()
def delete_post(self, post_id):
post = Post.query.filter_by(id=post_id).delete()
db.session.commit()
class User(db.Model, UserMixin):
__tablename__ = 'sys_user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(80), unique=True, nullable=False, comment=u'用户名')
password_hash = db.Column(db.String(128), comment="加密密码")
email = db.Column(db.String(120), unique=True, comment=u'email')
phone = db.Column(db.String(20), unique=True, comment=u'手机号码')
avatar = db.Column(db.String(200), comment=u'头像')
status = db.Column(db.Integer, default=1, comment=u'状态')
lastLoginTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'最后登录时间')
createTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'创建时间')
createBy = db.Column(db.String(50), comment=u'创建人')
updateTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'修改时间')
updateBy = db.Column(db.String(50), comment=u'修改人')
roles = db.relationship(
'Role',
secondary=sys_user_role,
backref=db.backref('users', lazy='dynamic')
)
@property
def role_names(self):
strs = ''
for i in range(len(self.roles)):
strs = strs + self.roles[i].name + ','
return strs
# @property
# def permissions(self):
# permissions = Permission.query.join(sys_role_permission).join(Role).join(sys_user_role).join(User). \
# filter(
# User.id == self.id
# )
# return permissions
@property
def menus(self):
menus = Menu.query.join(sys_role_menu).join(Role).join(sys_user_role).join(User). \
filter(
User.id == self.id
).order_by(Menu.type, Menu.order).all()
return menus
def check(self, action):
permission = self.permissions.filter(Permission.action == action).first()
return bool(permission)
def __init__(self, username, password):
self.username = username
self.password = password
def __init__(self, username, phone):
self.username = username
self.password = phone
@property
def password(self):
return self.password_hash
# raise AttributeError('不能直接获取明文密码！')
@property
def create_time(self):
return self.createTime.strftime('%Y-%m-%d')
# def auth(method):
# @functools.wraps(method)
# def wrapper(*args, **kwargs):
# user_id = session.get('user_id')
# if not user_id:
# return abort(403)
# return method(*args, **kwargs)
#
# return wrapper
@password.setter
def password(self, password):
self.password_hash = generate_password_hash(password)
def verify_password(self, password):
return check_password_hash(self.password_hash, password)
def get_id(self):
return self.username
def update(self):
globals.db.commit()
def delete(self):
globals.db.delete(self)
globals.db.commit()
def save(self):
globals.db.add(self)
globals.db.commit()
class Recipe(db.Model):
""" Recipe Model for storing recipes """
__tablename__ = "recipe"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(255), nullable=False)
created_on = db.Column(db.DateTime, nullable=False)
cooktime = db.Column(db.String(255), nullable=False)
preptime = db.Column(db.String(255), nullable=False)
totaltime = db.Column(db.String(255), nullable=False)
username = db.Column(db.String(50),
db.ForeignKey('user.username'),
nullable=False)
remixes = db.relationship("Recipe",
backref=db.backref('parent', remote_side=[id]))
parent_id = db.Column(db.Integer, db.ForeignKey('recipe.id'))
public = db.Column(db.Boolean)
difficulty = db.Column(db.Integer, nullable=True)
servings = db.Column(db.String(255), nullable=False)
source = db.Column(db.String(255), nullable=False)
calories = db.Column(db.Integer, nullable=True)
cost = db.Column(db.Float, nullable=True)
description = db.Column(db.Text)
f_image = db.Column(db.Integer, nullable=True)
images = db.relationship('Image',
order_by='Image.id',
backref='recipe',
cascade='all,delete,delete-orphan')
ingredients = db.relationship('Ingredient',
order_by='Ingredient.number',
collection_class=ordering_list('number',
count_from=1),
backref='recipe',
lazy=True,
cascade='all,delete,delete-orphan')
steps = db.relationship('Step',
order_by='Step.number',
collection_class=ordering_list('number',
count_from=1),
backref='recipe',
lazy=True,
cascade='all,delete,delete-orphan')
@hybrid_property
def remix_count(self):
return len(self.remixes)
@remix_count.expression
def _remix_count_expression(cls):
q = db.select([db.func.count(Recipe.parent_id)]).\
where(Recipe.parent_id == cls.id).\
label("remix_count")
return q
@hybrid_property
def featured_image(self):
if self.f_image is not None:
return self.images[self.f_image]
return None
@hybrid_property
def community_images(self):
return (list(
filter(lambda img: img.username != self.username, self.images)) + [
r.featured_image
for r in self.remixes if r.featured_image is not None
])
@hybrid_property
def owner_images(self):
return list(
filter(lambda img: img.username == self.username, self.images))
@hybrid_property
def likes_count(self):
return len(self.likers)
#TODO make has_liked work when nested, as in viewing other user's recipes
@hybrid_method
def has_liked(self, username):
return db.session.query(likes).filter(
likes.c.username == username).filter(
likes.c.recipe_id == self.id).first() != None
@likes_count.expression
def _likes_count_expression(cls):
return (db.select([
db.func.count(likes.c.username).label("likes_count")
]).where(likes.c.recipe_id == cls.id).label("sum_likes"))
def __repr__(self):
return "<Recipe '{}'>".format(self.title)
class Post(db.Model):
"""
Description of Post model.
Rows
-----------
:post_id: int [pk]
:authorId: varchar [ref: > users.id]
:title: varchar
:body: text
:post_time: timestamp [not null]
:avg_rating: float
:num_rating: int
"""
# Columns
post_id = db.Column(db.Integer, primary_key=True)
_author_id = db.Column(db.String(256),
db.ForeignKey('user.username'),
nullable=False)
title = db.Column(db.String(128), nullable=False)
body = db.Column(db.Text, nullable=False)
post_time = db.Column(db.DateTime, default=datetime.datetime.now())
avg_rating = db.Column(db.Float, default=0.0)
num_rating = db.Column(db.Integer, default=0)
# Relationships
author = db.relationship('User', backref='posts', lazy=False)
tags = db.relationship('Tag',
secondary=postTagJunction,
lazy='subquery',
backref=db.backref('posts', lazy=True))
# savers = db.relationship('User', secondary=postSaves, lazy=True,
# backref=db.backref('posts', lazy='subquery'))
images = db.relationship('ImgLink',
secondary=imgPostJunction,
lazy='subquery')
def __init__(self, author, title, body):
self.author = author
self.title = title
self.body = body
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
# Getters and Setters for the fields
# @hybrid_property
# def author_id(self):
# return self.author
# @author_id.setter
# def author_id(self, authorId):
# try:
# user = User.query.filter_by(id=authorId).first()
# if user.last_logout != None:
# raise LoginError
# else:
# self._author_id = authorId
# except:
# # Stub to be handled later
# print("Get back to login page")
@classmethod
def getArticlesByTags(cls, tagList, connector='AND'):
"""
Get all articles that have a tag in tagList.
If connector is AND intersection of all posts set for each tag will be
returned. If it is OR, union will be returned
"""
if connector == 'AND':
posts = set()
for tag in tagList:
if len(posts) == 0:
posts = set(cls.query.filter(tag in cls.tags).all())
else:
posts.intersection(
set(cls.query.filter(tag in cls.tags).all()))
return list(posts)
elif connector == 'OR':
posts = set()
for tag in tagList:
if len(posts) == 0:
posts = set(cls.query.filter(tag in cls.tags).all())
else:
posts.union(set(cls.query.filter(tag in cls.tags).all()))
return list(posts)
@staticmethod
def getArticles(post_id):
return Post.query.filter_by(post_id=post_id).first()
@staticmethod
def getRandomizedArticles(size):
return sample(Post.query.all(), size)
