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 ProductsMeta(db.Model):
# create product meta details table with one to one relationship with products table
__tablename__ = "product_meta"
id = db.Column(db.Integer, primary_key=True)
price = db.Column(db.Integer)
description =db.Column(db.Text())
product_id = db.Column(db.Integer, db.ForeignKey("products.product_id"))
image = db.Column(db.Text())
class Conversation(db.Model):
""" Conversation Model for storing user's chat and bot's generated responses """
__tablename__ = "conversation"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
message = db.Column(db.Text(), nullable=False)
sender = db.Column(db.Text(), nullable=False)
generated_on = db.Column(db.DateTime, nullable=False)
response = db.Column(db.Text(), nullable=True, default="")
def __repr__(self):
return "<Conversation '{}'>".format(self.response)
class Message(db.Model):
__tablename__ = "message"
# DATA COLUMNS
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
body = db.Column(db.Text(4294000000), nullable=False)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
replies_to_message_id = db.Column(db.Integer,
db.ForeignKey('message.id'),
nullable=True)
# FOREIGN KEYS COLUMNS
sender_id = db.Column(db.String(255),
db.ForeignKey('user.id'),
nullable=False)
receiver_id = db.Column(db.Integer,
db.ForeignKey('chat.id'),
nullable=False)
# RELATIONSHIPS
sender_user = db.relationship('User',
back_populates='sent_messages',
lazy=True)
receiver_chat = db.relationship('Chat',
back_populates='received_messages',
lazy=True)
replies_to_message = db.relationship('Message', remote_side='[Message.id]')
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 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 VehicleResource(db.Model):
__tablename__ = 'scheduler_vehicle_resources'
id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True)
license = db.Column('license', db.String(8), nullable=False)
maker = db.Column('maker', db.String(16), nullable=False)
model = db.Column('model', db.String(16))
year = db.Column('year', db.String(4))
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_vehicle_avails.id'))
type_id = db.Column('type_id', db.ForeignKey('scheduler_vehicle_types.id'))
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 Club(Base):
cid = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255))
chairman = db.Column(db.String(255))
memberno = db.Column(db.Integer)
avatar = db.Column(db.String(1024))
desc = db.Column(db.Text())
address = db.Column(db.String(1024))
def __init__(self):
pass
def __repr__(self):
return "<Club `{}`>".format(self.name)
@classmethod
def get_all_clubs(cls):
clubs = cls.query.all()
return clubs
@classmethod
def find_club_by_id(cls, clubids):
club = cls.query.filter_by(cid=clubids).first()
return club
@classmethod
def find_clubs_by_ids(cls, clubids):
clubs = cls.query.filter(cls.cid.in_(clubids)).all()
return clubs
@classmethod
def update_club_info(cls, clubid, club):
cls.query.filter_by(cid=clubid).update({
"name": club.name,
"chairman": club.chairman,
"address": club.chairman,
"desc": club.desc,
})
db.session.commit()
@classmethod
def update_avatar(cls, clubid, path):
cls.query.filter_by(cid=clubid).update({
"avatar": path,
})
db.session.commit()
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 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 Activity(Base):
acid = db.Column(db.Integer, primary_key=True)
cid = db.Column(db.Integer)
title = db.Column(db.String(255))
content = db.Column(db.Text())
deadline = db.Column(db.Date())
activity_time = db.Column(db.Date())
activity_address = db.Column(db.String(255))
@classmethod
def create(cls, activity):
db.session.add(activity)
db.session.commit()
@classmethod
def update(cls, activity):
cls.query.filter_by(acid=activity.acid).update({
"title": activity.title,
"content": activity.content,
"deadline": activity.deadline,
"activity_time": activity.activity_time,
"activity_address": activity.activity_address,
})
db.session.commit()
@classmethod
def delete(cls, id):
cls.query.filter_by(acid=id).delete()
db.session.commit()
@classmethod
def get_by_id(cls, id):
activity = cls.query.filter_by(acid=id).first()
return activity
@classmethod
def get_activities(cls, limit):
activity = cls.query.order_by(desc(cls.date_created)).limit(limit).all()
return activity
@classmethod
def get_activities_of_club(cls, clubid):
activities = cls.query.filter_by(cid=clubid).order_by(desc(cls.date_created)).all()
return activities
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 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 Recipe(db.Model):
__tablename__ = "recipes"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text(), nullable=False)
description = db.Column(db.Text(), nullable=False)
steps = db.Column(db.Text(), nullable=False)
account_id = db.Column(db.Integer, db.ForeignKey(
"accounts.id",
ondelete="CASCADE",
), nullable=False, index=True)
ingredient_amounts = db.relationship(
"RecipeIngredient", backref="recipe", lazy=True,
passive_deletes=True)
def __init__(self, name, description, steps):
self.name = name
self.description = description
self.steps = steps
def get_ingredients(self):
stmt = text("""
SELECT
ri.amount AS amount,
ri.amount_unit AS amount_unit,
ri.group_name AS group_name,
i.id AS id,
i.name AS name
FROM ingredients i, recipe_ingredient ri
WHERE
ri.recipe_id = :recipe_id
AND i.account_id = :account_id
AND ri.ingredient_id = i.id
ORDER BY ri.id
""").bindparams(recipe_id=self.id, account_id=self.account_id)
return db.session().execute(stmt)
def get_shopping_list_amounts(self):
stmt = text("""
SELECT
i.id AS id,
SUM(sli.amount) AS amount,
sli.amount_unit AS unit
FROM (
SELECT DISTINCT
i.id AS id,
i.name AS name
FROM ingredients i, recipe_ingredient ri
WHERE
i.account_id = :account_id
AND i.id = ri.ingredient_id
AND ri.recipe_id = :recipe_id
) i
LEFT JOIN shopping_list_items sli
ON sli.ingredient_id = i.id
WHERE sli.account_id = :account_id
GROUP BY i.id, sli.amount_unit
""").bindparams(recipe_id=self.id, account_id=self.account_id)
return db.session().execute(stmt)
def insert_ingredients_from_form(self, form):
ingredients = []
missing_ingredients = []
ingredients_by_name = {}
for recipe_ingredient_form in form.ingredient_amounts:
name = recipe_ingredient_form.data["name"].strip()
lower_name = name.lower()
existing_ingredient = ingredients_by_name.get(lower_name)
if not existing_ingredient:
existing_ingredient = Ingredient.query.filter(
Ingredient.account_id == self.account_id,
func.lower(Ingredient.name) == lower_name,
).first()
if not existing_ingredient:
existing_ingredient = Ingredient(name)
existing_ingredient.account_id = self.account_id
missing_ingredients.append(existing_ingredient)
ingredients_by_name[lower_name] = existing_ingredient
ingredients.append(existing_ingredient)
db.session().bulk_save_objects(missing_ingredients, return_defaults=True)
db.session().flush()
recipe_ingredients = []
for index, recipe_ingredient_form in enumerate(form.ingredient_amounts):
amount, unit = recipe_ingredient_form.parse_amount()
recipe_ingredients.append(RecipeIngredient(
amount=amount,
amount_unit=unit,
ingredient_id=ingredients[index].id,
recipe_id=self.id,
group_name=recipe_ingredient_form.group.data,
))
db.session().bulk_save_objects(recipe_ingredients)
db.session().flush()
class Content(db.Model):
__tablename__ = 'content'
# DATA COLUMNS
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
body = db.Column(db.Text(4294000000), nullable=False)
timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
# FOREIGN KEYS COLUMNS
author_id = db.Column(db.String(255),
db.ForeignKey('user.id'),
nullable=False)
# RELATIONSHIPS
author = db.relationship('User',
back_populates='published_content',
lazy=True)
comments = db.relationship('Comment',
back_populates='commented_content',
lazy='dynamic',
foreign_keys='Comment.commented_content_id',
cascade="delete")
likes = db.relationship('Likes',
back_populates='content',
lazy='dynamic',
cascade="delete")
dislikes = db.relationship('Dislikes',
back_populates='content',
lazy='dynamic',
cascade="delete")
@hybrid_property
def comments_num(self):
sum = self.comments.count()
if sum > 0:
for content in self.comments.all():
sum += content.comments_num
return sum
@hybrid_property
def likes_num(self):
return self.likes.count()
@hybrid_property
def dislikes_num(self):
return self.dislikes.count()
@hybrid_method
def liked(self, user):
return db.session.query(
Content.query.filter(Content.id == self.id).join(Likes).filter(
Likes.user_id == user.id).exists()).scalar()
@hybrid_method
def disliked(self, user):
return db.session.query(
Content.query.filter(Content.id == self.id).join(Dislikes).filter(
Dislikes.user_id == user.id).exists()).scalar()
@staticmethod
def get_content_with_user_data(content_query, user):
return content_query.outerjoin(Likes).filter(
or_(Likes.user_id == user.id,
Likes.user_id == None)).outerjoin(Dislikes).filter(
or_(Dislikes.user_id == user.id,
Dislikes.user_id == None)).with_entities(
Content,
cast(func.count(Likes.user_id),
sqlalchemy.Boolean).label('liked'),
cast(func.count(Dislikes.user_id),
sqlalchemy.Boolean).label(
'disliked')).group_by(Content.id)
# INHERITANCE
type = db.Column(db.String(255))
__mapper_args__ = {
'polymorphic_identity': 'content',
'polymorphic_on': type
}
class HealthServiceService(db.Model):
__tablename__ = 'health_service_services'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(255), nullable=False, info={'label': 'Service'})
detail = db.Column(db.Text(), nullable=True, info={'label': 'Detail'})
