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'})