class Metadata(db.Model): id = db.Column(db.Integer, primary_key=True) nyt_id = db.Column(db.String(99)) review_type = db.Column(db.String(500)) month = db.Column(db.String(99)) year = db.Column(db.String(99)) document_type = db.Column(db.String(250)) headline = db.Column(db.String(500)) byline = db.Column(db.String(500)) page = db.Column(db.String(500)) pub_date = db.Column(db.String(500)) word_count = db.Column(db.String(99)) review_word_count = db.Column(db.Integer) ocr_transcription = db.Column(db.String(99999)) corrected_transcription = db.Column(db.String(99999)) perceived_author_name = db.Column(db.String(99)) perceived_author_gender = db.Column(db.String(99)) reviewed_work_title = db.Column(db.String(500)) nyt_pdf_endpoint = db.Column(db.String(500)) reviewed_work = db.Column(db.Integer, db.ForeignKey('work.id')) def __repr__(self): return 'Review %r' % (self.nyt_id,)
class UsedVacation(db.Model): __table_name__ = 'used_vacation' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.String(30), db.ForeignKey('user.google_id'), nullable=False) user = db.relationship('User', backref=db.backref('used_vacations', cascade='all, delete', lazy=True)) summary = db.Column(db.String(30), nullable=False) start_date = db.Column(db.Date, nullable=False) end_date = db.Column(db.Date, nullable=False) kind = db.Column(db.String(10), nullable=False) reference = db.Column(db.String(30), nullable=True) event_id = db.Column(db.String(200), nullable=False) def __repr__(self): return "Google id : %r / 내용 : %r / 시작일 : %r / 종료일 : %r / 휴가 종류 : %r" % (self.user.google_id, self.summary, str(self.start_date), str(self.end_date), self.kind) def get_period(self): period = self.end_date - self.start_date if period.days < 0: raise Exception("End date earlier than Start date") return period
class Weight(db.Model): """Data model for weights""" __tablename__ = 'weights' id = db.Column(db.Integer, primary_key=True) admin_id = db.Column(db.Integer, db.ForeignKey(Admin.id)) created = db.Column(db.DateTime, index=False, unique=False, nullable=False, default=datetime.utcnow()) weight = db.Column(db.Float, index=False, unique=False, nullable=False) weight_date = db.Column(db.DateTime, index=False, unique=False, nullable=False) def __repr__(self): return '<Weight {}>'.format(self.weight)
class Timer(db.Model): __tablename__ = 'timers' id = db.Column(db.Integer, primary_key=True) work_interval = db.Column(db.String()) rest_interval = db.Column(db.String()) sound = db.Column(db.String()) mood = db.Column(db.Boolean()) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) def __init__(self, work_interval, rest_interval, sound, mood, user_id): self.work_interval = work_interval self.rest_interval = rest_interval self.sound = sound self.mood = mood self.user_id = user_id def __repr__(self): return f"<Timer {self.id}>" def delete(self): db.session.delete(self) db.session.commit()
class Episode(db.Model): __tablename__ = 'episode' id = db.Column(db.Integer, primary_key=True) animation_id = db.Column(db.Integer, db.ForeignKey('animation.id'), nullable=False) torrent_id = db.Column(db.Integer) ep_num = db.Column(db.Integer, nullable=False) # sync_path = db.Column(db.String(300)) # video_path = db.Column(db.String(300), nullable=False) filename = db.Column(db.String(200), default=None) video_ext = db.Column(db.String(10), default=None) upload_time = db.Column(db.DateTime, default=datetime.now()) view_count = db.Column(db.Integer, default=0) def hasSync(self): if isfile(self.getSyncFullPath()): return True else: return False def getSyncFullPath(self): return '{path}/{title}/{filename}.vtt'.\ format( path=app.config['ANI_SYNC_DIR'], title=self.animation.synonyms, filename=self.filename ) def getVideoURL(self): return 'http://test.alien.moe/animations/{title}/{filename}.{ext}'.\ format( title=parse.quote(self.animation.synonyms), filename=parse.quote(self.filename), ext=self.video_ext ) def __repr__(self): return '<Episode animation=%r, ep_num=%r>' % (self.animation, self.ep_num)
class FlicketCategory(PaginatedAPIMixin, Base): __tablename__ = 'flicket_category' id = db.Column(db.Integer, primary_key=True) category = db.Column(db.String(field_size['category_max_length'])) department_id = db.Column(db.Integer, db.ForeignKey(FlicketDepartment.id)) department = db.relationship(FlicketDepartment, back_populates='categories') def __init__(self, category, department): """ :param category: """ self.category = category self.department = department # # make the default sort order the category name # __mapper_args__ = { # "order_by": category.asc() # } def to_dict(self): """ Returns a dictionary object about the category and its department :return: """ data = { 'id': self.id, 'category': self.category, 'department': self.department.department, 'links': { 'self': url_for('bp_api_v2.get_category', id=self.id) } } return data
class Post(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(100), nullable=False) url = db.Column(db.String(250), nullable=False) date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow()) content = db.Column(db.Text, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) comments = db.relationship('Comment', backref='post', lazy=True) likes = db.relationship('PostLike', backref='post', lazy=True) trustworthy_submissions = db.relationship('TrustworthySubmission', backref='post', lazy=True) keywords = db.relationship('KeyWord', backref='post', lazy=True) votes = 0 can_upvote = True can_downvote = True def set_votable(self): self.votes = 0 for vote in self.likes: self.votes = self.votes + 1 if vote.is_upvote else self.votes - 1 if current_user.is_authenticated: upvoted = PostLike.query.filter_by(post_id=self.id)\ .filter_by(user_id=current_user.id).filter_by(is_upvote=True).count() downvoted = PostLike.query.filter_by(post_id=self.id)\ .filter_by(user_id=current_user.id).filter_by(is_upvote=False).count() if upvoted - downvoted > 0: self.can_upvote = False if upvoted - downvoted < 0: self.can_downvote = False def __repr__(self): return f"Post('{self.title}', '{self.date_posted}')"
class Thread(Base): title = db.Column(db.String(144), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) def __init__(self, title): self.title = title def get_comments(self): stmt = text( "SELECT Comment.* FROM Comment WHERE thread_id = :thread_id;" ).params(thread_id=self.id) res = db.engine.execute(stmt) response = [] for row in res: response.append(row) response = list(response) response.sort(key=dateSort) return response def get_main_comment(self): comments = Comment.query.filter_by(thread_id=self.id).order_by( Comment.date_created) return comments[0] def get_comment_user(self, comment): return User.query.filter_by(id=comment.account_id).first() def exceeds_comment_count(self): return Comment.query.filter_by( thread_id=self.id).count() > app.config["COMMENT_LIMIT"]
class VideoGame(db.Model): __tablename__ = "videogame" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(144), nullable=False) releaseyear = db.Column(db.Integer, nullable=False) genre = db.Column(db.String(50), nullable=False) developer_id = db.Column(db.Integer, db.ForeignKey('developer.id'), nullable=False) def __init__(self, name, year, genre, developer): self.name = name self.releaseyear = year self.genre = genre self.developer_id = developer def get_by_developer(dev_id): stmt = text( "SELECT videogame.id, videogame.name, videogame.genre, videogame.releaseyear" " FROM videogame WHERE videogame.developer_id = :dev_id").params( dev_id=dev_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({ "id": row[0], "name": row[1], "genre": row[2], "releaseyear": row[3] }) return response
class state1_data(db.Model): id = db.Column( db.Integer, primary_key=True ) # Mode of the design (Should be "synthetic" or "decompose") reaction_time = db.Column(db.Float) # Total time of the reaction medium_id = db.Column(db.Integer, db.ForeignKey('mediumDB.id')) # Used medium's id medium = db.relationship('mediumDB', backref='all_design') flora = db.Column(db.String(320)) # All flora set (With dirty list) make_matter = db.Column( db.String(320)) # All matters for synthetic (With dirty list) resolve_matter = db.Column( db.String(320)) # All matters for decomposing (With dirty list) md5 = db.Column(db.String( 60)) # All data's md5 (For multi used of the calculating result) def refresh_md5(self): # Refresh the md5 hash with all the data src = str(self.reaction_time) + str( self.medium_id ) + self.flora + self.make_matter + self.resolve_matter m = hashlib.md5() m.update(src) self.md5 = m.hexdigest() def __init__(self): self.md5 = '' self.flora = '[]' self.make_matter = '[]' self.resolve_matter = '[]' def __repr__(self): return '<State 1 data %r>' % self.md5 def save(self): self.refresh_md5() db.session.add(self) db.session.commit()
class ServerInfoModel(db.Model): """ Additional Server info model """ __tablename__ = 'servers_info' id = db.Column(db.Integer, db.ForeignKey('servers.id'), primary_key=True) price = db.Column(db.Float, nullable=False) count = db.Column(db.Integer, default=10) def to_json(self): return {'price': self.price, 'count': self.count} @staticmethod def update_server_available(server_id, delta): """ Method to update ServerInfoModel.count on delta :param server_id: :param delta: value to update :return: updated count """ db.session.query(ServerInfoModel).filter(ServerInfoModel.id == server_id).\ update({ServerInfoModel.count: ServerInfoModel.count + delta}) db.session.commit() try: count = db.session.query(ServerInfoModel.count).\ filter(ServerInfoModel.id == server_id).first()[0] except TypeError: raise NoServerException( "Server with id: {} not found".format(server_id)) else: return count
class CourseMaterial(db.Model): __tablename__ = 'Course_Material' id = db.Column(db.Integer, nullable = False, primary_key=True) course_id = db.Column(db.Integer(), db.ForeignKey('EducationalСourse.id'), nullable = False ) name = db.Column(db.String(100), nullable = False) content = db.Column(db.String(10000), nullable = False) deleted = db.Column(db.Integer() ) created_on = db.Column(db.DateTime(), default=datetime.utcnow) updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow) def __init__(self, course_id, name, content): self.course_id = course_id self.name = name self.content = content self.deleted = 0 def set_content(self, content): self.content = content def delete(self): self.deleted = 1 def __repr__(self): return "<CourseMaterial Info {}:{}>".format(self.id, self.name)
class Comment(db.Model): id = db.Column(db.Integer, primary_key=True) post_id = db.Column(db.Integer, db.ForeignKey('post.id', ondelete='CASCADE'), nullable=False) author_name = db.Column(db.String(80), nullable=False) content = db.Column(db.Text, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) @classmethod def create(cls, form): commentAuthor = form['author'] commentContent = form['content'] commentPostId = form['post_id'] comment = Comment(post_id=commentPostId, author_name=commentAuthor, content=commentContent) db.session.add(comment) db.session.commit() return comment def serialize(self): return { 'id': self.id, 'author': self.author_name, 'content': self.content, 'created_at': self.created_at.strftime('%d/%m/%Y') }
class StockEntry(db.Model): article_id = db.Column(db.Integer, db.ForeignKey('article.id'), primary_key=True, nullable=False) article = db.relationship( 'Article' ) # article = db.relationship('Article',backref=db.backref('entries')) si relation manytomany quantity = db.Column(db.Integer, default=0) #def __init__(self, article, quantity): # self.article = article # self.quantity = quantity def price(self): return self.article.price * self.article.quantity def toString(self): return '{} x {}'.format(self.article.toString(), self.article.quantity) def update(self, values): self.quantity = values['quantity'] db.session.commit()
class Discussion(Base): title = db.Column(db.String(100), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) messages = db.relationship("Message", backref='discussion', cascade='all', lazy=True) tags = db.relationship("Tag", cascade='all', secondary=discussion_tag, backref='discussions', lazy=True) def __init__(self, title): self.title = title def set_account_id(self, id): self.account_id = id def set_tags(self, tags): self.tags = tags
class Topics(db.Model): id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column( db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) name = db.Column(db.String(144), nullable=False) desc = db.Column(db.String(144), nullable=True) account_id = db.Column( db.Integer, db.ForeignKey('account.id'), nullable=False) kategoriat = relationship("Categories", secondary="topics_categories") def __init__(self, name, desc): self.name = name self.desc = desc @staticmethod def find_topics(category_id): stmt = text( "SELECT Topics.id, Topics.name, Topics.desc, Topics.date_created, Categories.id FROM Topics " "LEFT JOIN topics_categories ON topics_categories.topics_id = Topics.id " "LEFT JOIN Categories ON Categories.id = topics_categories.categories_id " "LEFT JOIN Posts ON Posts.topics_id = Topics.id " "WHERE Categories.id = :category " "ORDER BY Topics.date_created DESC ").params(category=category_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({"id": row[0], "name": row[1], "desc": row[2], "created": row[3], "caid": row[4]}) return response
class Bucketlist(db.Model): """ creates bucket lists """ __tablename__ = 'bucketlists' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) created_by = db.Column(db.Integer, db.ForeignKey('users.email')) date_created = db.Column(db.DateTime, default=datetime.utcnow()) date_modified = db.Column(db.DateTime, onupdate=datetime.utcnow()) items = db.relationship('Item', backref='bucketlist', lazy='dynamic') def save(self): """ Save a bucketlist into the database """ db.session.add(self) db.session.commit() def delete(self): """ delete a bucketlist from the database """ db.session.delete(self) db.session.commit() def __repr__(self): return '<Bucketlist %r>' % self.name
class Tilaus(db.Model): __tablename__ = "order" id = db.Column(db.Integer, primary_key=True) order_date = db.Column(db.DateTime, default=db.func.current_timestamp()) delivery_date = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) name = db.Column(db.String(144)) address = db.Column(db.String(144)) phone = db.Column(db.String(15)) delivered = db.Column(db.Boolean, nullable=False) price = db.Column(db.Float(10), nullable=False) sent = db.Column(db.Boolean, nullable=False) orderedPizzas=db.relationship("OrderPizza", backref='order', lazy=True) account_id=db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) def __init__(self, account_id, price=0): self.account_id = account_id self.delivered = False self.price = price self.sent = False @staticmethod def find_pizzas_for_order(order_id): user_id=current_user.get_id() stmt=text("SELECT Pizza.name, Pizza.price, Order_pizza.order_id FROM Pizza, Order_pizza" " WHERE (Order_pizza.pizza_id = Pizza.id AND Order_pizza.order_id = :orderid)").params(orderid= order_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({"name":row[0], "price":row[1]}) return response
class Diet(Base): account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) name = db.Column(db.String(100), nullable=False) edited = db.Column(db.DateTime, nullable=False) meals = db.relationship("Meal", lazy=True, cascade='all, delete') meals_query = db.relationship("Meal", lazy="dynamic") @property def energy(self): energy = 0 for meal in self.meals: energy += meal.energy return energy @property def protein(self): protein = 0 for meal in self.meals: protein += meal.protein return protein @property def carb(self): carb = 0 for meal in self.meals: carb += meal.carb return carb @property def fat(self): fat = 0 for meal in self.meals: fat += meal.fat return fat
class Ambulance(db.Model): __tablename__ = 'ambulance' id = db.Column('id_ambulance', db.Integer, primary_key=True) plate_number = db.Column(db.Integer, nullable=False, unique=True) brand = db.Column(db.String(45), nullable=False) model = db.Column(db.String(45), nullable=False) mileage = db.Column(db.Integer, nullable=False) available = db.Column(db.Boolean, nullable=False) driver_id = db.Column(db.Integer, db.ForeignKey('driver.dni')) driver = db.relationship('Driver', back_populates='ambulance') dispatches = db.relationship('Dispatch', backref='ambulance') def __init__(self, plate_number, brand, model, mileage=0, available=True): self.plate_number = plate_number self.brand = brand self.model = model self.mileage = mileage self.available = available def __repr__(self): return '%s %s, #%s' % (self.brand, self.model, self.plate_number)
class Post(Base): postName = db.Column(db.String(300), nullable=False) accountId = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) comments = db.relationship("Comment", backref='post') def __init__(self, name): self.postName = name @staticmethod def get_the_post_with_most_comments(): if os.environ.get("HEROKU"): stmt = text( 'SELECT Post."postName", Post."accountId", COUNT(Comment.id) FROM Post LEFT JOIN Comment ON Post.id = comment."postId" GROUP BY Post."postName", Post."accountId" ORDER BY Count(Comment.id) DESC LIMIT 1;' ) else: stmt = text( "SELECT Post.postName, Post.accountId, COUNT(Comment.id) FROM Post" " LEFT JOIN Comment ON Comment.postId = Post.id" " GROUP BY Post.postName" " ORDER BY Count(Comment.id) DESC" " LIMIT 1") res = db.engine.execute(stmt) response = [] for row in res: response.append(row[0]) response.append(row[1]) response.append(row[2]) return response
class Item(db.Model): """ Creates bucketlist items """ __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) description = db.Column(db.String(120)) is_done = db.Column(db. String, default="False") date_created = db.Column(db.DateTime, default=datetime.utcnow()) date_modified = db.Column(db.DateTime, onupdate=datetime.utcnow()) bucketlist_id = db.Column(db.Integer, db.ForeignKey('bucketlists.id')) def save(self): """ Save an item into the database """ db.session.add(self) db.session.commit() def delete(self): """ delete an item from database """ db.session.delete(self) db.session.commit() def __repr__(self): return '<item %r>' % self.description
class Item(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) title = db.Column(db.String()) description = db.Column(db.String()) price = db.Column(db.Float()) status = db.Column(db.String(), default='available') category = db.Column(db.String()) charity = db.Column(db.String()) charity_url = db.Column(db.String()) charity_score = db.Column(db.Integer()) charity_score_image = db.Column(db.String()) image = db.Column(db.String()) # minutes ONLY auction_length = db.Column(db.Integer(), default=5) created_at = db.Column(db.BigInteger()) auction_end = db.Column(db.BigInteger()) bids = db.relationship('Bid', backref='items', lazy='select') def __repr__(self): return '<id {}>'.format(self.id)
class Session(db.Model): id = db.Column(db.Integer, primary_key=True) date = db.Column(db.Date, default=db.func.current_date(), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) results = db.relationship("Result", backref='session', lazy=True) @staticmethod def count_sessions_last_30_days(): if os.environ.get("HEROKU"): stmt = text( "SELECT COUNT(DISTINCT date) FROM session WHERE date > current_date - interval " "'30' AND account_id = :id;") else: stmt = text( "SELECT COUNT(DISTINCT date) FROM session WHERE date BETWEEN date('now', '-30 days') AND date(" "'now', 'localtime') AND account_id = :id;") res = db.engine.execute(stmt, id=current_user.id) result = [] for row in res: result.append(row[0]) return result[0] @staticmethod def count_sessions(): stmt = text("SELECT COUNT(*) FROM session;") res = db.engine.execute(stmt) result = [] for row in res: result.append(row[0]) return result[0]
class Dish(db.Model): """ Model for dishes """ __tablename__ = 'dishes' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) name_uz = db.Column(db.String(100)) image_id = db.Column(db.String(150)) image_path = db.Column(db.String(150)) description = db.Column(db.String(500)) description_uz = db.Column(db.String(500)) show_usd = db.Column(db.Boolean, default=False) is_hidden = db.Column(db.Boolean, default=False) price = db.Column(db.Float) number = db.Column(db.Integer, default=1) category_id = db.Column(db.Integer, db.ForeignKey('dish_categories.id')) quantity = db.Column(db.Integer, default=0) def get_full_name(self): return self.category.get_nested_names() + ' |=>| ' + self.name def get_full_name_uz(self): return self.category.get_nested_names_uz() + ' |=>| ' + self.name_uz
class FlicketAction(PaginatedAPIMixin, Base): """ SQL table that stores the action history of a ticket. For example, if a user claims a ticket that action is stored here. The action is associated with ticket_id and latest post_id (if exists). """ __tablename__ = 'flicket_ticket_action' id = db.Column(db.Integer, primary_key=True) ticket_id = db.Column(db.Integer, db.ForeignKey(FlicketTicket.id)) ticket = db.relationship(FlicketTicket) post_id = db.Column(db.Integer, db.ForeignKey(FlicketPost.id)) post = db.relationship(FlicketPost) action = db.Column(db.String(field_size['action_max_length'])) data = db.Column(db.JSON(none_as_null=True)) user_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) user = db.relationship(FlicketUser, foreign_keys=[user_id]) recipient_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) recipient = db.relationship(FlicketUser, foreign_keys=[recipient_id]) date = db.Column(db.DateTime) def output_action(self): """ Method used in ticket view to show what action has taken place in ticket. :return: """ _date = self.date.strftime('%d-%m-%Y %H:%M') if self.action == 'open': return ( f'Ticket opened' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'assign': return ( f'Ticket assigned to <a href="mailto:{self.recipient.email}">{self.recipient.name}</a>' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'claim': return ( f'Ticked claimed' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'status': return ( f'Ticket status has been changed to "{self.data["status"]}"' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'priority': return ( f'Ticket priority has been changed to "{self.data["priority"]}"' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'release': return ( f'Ticket released' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'close': return ( f'Ticked closed' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'department_category': return ( f'Ticket category has been changed to "{self.data["department_category"]}"' f' by <a href="mailto:{self.user.email}">{self.user.name}</a> | {_date}' ) if self.action == 'subscribe': return ( f'<a href="mailto:{self.recipient.email}">{self.recipient.name}</a> has been subscribed to ticket' f' by <a href="mailto:{self.user.email}">{self.user.name}</a>. | {_date}' ) if self.action == 'unsubscribe': return ( f'<a href="mailto:{self.recipient.email}">{self.recipient.name}</a> ' f'has been un-subscribed from ticket' f' by <a href="mailto:{self.user.email}">{self.user.name}</a>. | {_date}' ) def to_dict(self): """ :return: dict() """ data = { 'id': self.id, 'ticket_id': self.ticket_id, 'post_id': self.post_id, 'action': self.action, 'data': self.data, 'user_id': self.user_id, 'recipient_id': self.recipient_id, 'date': self.date, 'links': { 'self': app.config['base_url'] + url_for('bp_api.get_action', id=self.id), 'actions': app.config['base_url'] + url_for('bp_api.get_actions', ticket_id=self.ticket_id), } } return data def __repr__(self): return ( f'<Class FlicketAction: ticket_id={self.ticket_id}, post_id={self.ticket_id}, action={self.action!r}, ' f'data={self.data}, user_id={self.user_id}, recipient_id={self.recipient_id}, date={self.date}>' )
class FlicketHistory(PaginatedAPIMixin, Base): """ A database to track the editing of tickets and posts. """ __tablename__ = 'flicket_history' id = db.Column(db.Integer, primary_key=True) post_id = db.Column(db.Integer, db.ForeignKey(FlicketPost.id)) post = db.relationship(FlicketPost) topic_id = db.Column(db.Integer, db.ForeignKey(FlicketTicket.id)) topic = db.relationship(FlicketTicket) date_modified = db.Column(db.DateTime()) original_content = db.Column(db.String(field_size['content_max_length'])) user_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) user = db.relationship(FlicketUser) def to_dict(self): """ :return: dict() """ ticket_url, post_url = None, None if self.topic_id: ticket_url = app.config['base_url'] + url_for('bp_api.get_ticket', id=self.topic_id) if self.post_id: post_url = app.config['base_url'] + url_for('bp_api.get_post', id=self.post_id) data = { 'id': self.id, 'date_modified': self.date_modified, 'original_content': self.original_content, 'post_id': self.post_id, 'topic_id': self.topic_id, 'user_id': self.user_id, 'links': { 'self': app.config['base_url'] + url_for('bp_api.get_history', id=self.id), 'histories': app.config['base_url'] + url_for('bp_api.get_histories'), 'post': post_url, 'ticket': ticket_url, 'user': app.config['base_url'] + url_for('bp_api.get_user', id=self.user_id), } } return data def __repr__(self): return "<FlicketHistory: id={}, post_id={}, topic_id={}>".format( self.id, self.posts_id, self.topic_id)
class FlicketTicket(PaginatedAPIMixin, Base): __tablename__ = 'flicket_topic' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(field_size['title_max_length']), index=True) content = db.Column(db.String(field_size['content_max_length'])) started_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) user = db.relationship(FlicketUser, foreign_keys='FlicketTicket.started_id') date_added = db.Column(db.DateTime()) date_modified = db.Column(db.DateTime()) modified_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) modified = db.relationship(FlicketUser, foreign_keys='FlicketTicket.modified_id') status_id = db.Column(db.Integer, db.ForeignKey(FlicketStatus.id)) current_status = db.relationship(FlicketStatus) category_id = db.Column(db.Integer, db.ForeignKey(FlicketCategory.id)) category = db.relationship(FlicketCategory) assigned_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) assigned = db.relationship(FlicketUser, foreign_keys='FlicketTicket.assigned_id') ticket_priority_id = db.Column(db.Integer, db.ForeignKey(FlicketPriority.id)) ticket_priority = db.relationship(FlicketPriority) posts = db.relationship("FlicketPost", back_populates="ticket") hours = db.Column(db.Numeric(10, 2), server_default='0') last_updated = db.Column( db.DateTime(), server_default=datetime.datetime.now().strftime('%Y-%m-%d')) # find all the images associated with the topic uploads = db.relationship( 'FlicketUploads', primaryjoin="and_(FlicketTicket.id == FlicketUploads.topic_id)") # finds all the users who are subscribed to the ticket. subscribers = db.relationship('FlicketSubscription', order_by='FlicketSubscription.user_def') # finds all the actions associated with the ticket actions = db.relationship( 'FlicketAction', primaryjoin="FlicketTicket.id == FlicketAction.ticket_id") # finds all the actions associated with the ticket and not associated with any post actions_nonepost = db.relationship( 'FlicketAction', primaryjoin="and_(FlicketTicket.id == FlicketAction.ticket_id, " "FlicketAction.post_id == None)") @property def num_replies(self): n_replies = FlicketPost.query.filter_by(ticket_id=self.id).count() return n_replies @property def id_zfill(self): return str(self.id).zfill(5) @property def department_category(self): return f'{self.category.department.department} / {self.category.category}' def is_subscribed(self, user): for s in self.subscribers: if s.user == user: return True return False def can_unsubscribe(self, user): """ Return true if user is admin, super_user or is trying to unsubscribe them self. :param user: :return: """ if any([g.user.is_admin, g.user.is_super_user ]) and self.is_subscribed(user): return True if self.is_subscribed(user) and user.id == g.user.id: return True return False @staticmethod def carousel_query(): """ Return all 'open' 'high priority' tickets for carousel. :return: """ tickets = FlicketTicket.query.filter(FlicketTicket.ticket_priority_id == 3). \ filter(FlicketTicket.status_id == 1).limit(100) return tickets @staticmethod def form_redirect(form, url='flicket_bp.tickets'): """ :param form: :param url: :return: """ department = '' category = '' status = '' user_id = '' user = FlicketUser.query.filter_by(username=form.username.data).first() if user: user_id = user.id # convert form inputs to it's table title if form.department.data: department = FlicketDepartment.query.filter_by( id=form.department.data).first().department if form.category.data: category = FlicketCategory.query.filter_by( id=form.category.data).first().category if form.status.data: status = FlicketStatus.query.filter_by( id=form.status.data).first().status redirect_url = url_for(url, content=form.content.data, department=department, category=category, status=status, user_id=user_id) return redirect_url @property def total_hours(self): """ Sums all hours related to ticket (posts + ticket itself). :return: """ hours = db.session.query(func.sum( FlicketPost.hours)).filter_by(ticket_id=self.id).scalar() or 0 return hours + self.hours def get_subscriber_emails(self): """ Function to return a list of email addresses of subscribed users. :return: """ emails = list() for subscriber in self.subscribers: if not subscriber.user.disabled: emails.append(subscriber.user.email) return emails @staticmethod def my_tickets(ticket_query): """ Function to return all tickets created by or assigned to user. :return: """ ticket_query = ticket_query.filter( (FlicketTicket.started_id == g.user.id) | (FlicketTicket.assigned_id == g.user.id)) return ticket_query @staticmethod def my_subscribed_tickets(ticket_query): """ Function to return all tickets subscribed to by user. :return: query """ return ticket_query.filter( FlicketTicket.subscribers.any( FlicketSubscription.user_id == g.user.id)) @staticmethod def query_tickets(form=None, **kwargs): """ Returns a filtered query and modified form based on form submission :param form: :param kwargs: :return: """ ticket_query = FlicketTicket.query if kwargs['status'] is None: ticket_query = ticket_query.filter( FlicketTicket.current_status.has( FlicketStatus.status != 'Closed')) if kwargs['assigned_id']: ticket_query = ticket_query.filter_by( assigned_id=kwargs['assigned_id']) if kwargs['created_id']: ticket_query = ticket_query.filter_by( started_id=kwargs['created_id']) for key, value in kwargs.items(): if key == 'status' and value: ticket_query = ticket_query.filter( FlicketTicket.current_status.has( FlicketStatus.status == value)) if form: form.status.data = FlicketStatus.query.filter_by( status=value).first().id if key == 'category' and value: ticket_query = ticket_query.filter( FlicketTicket.category.has( FlicketCategory.category == value)) if form: form.category.data = FlicketCategory.query.filter_by( category=value).first().id if key == 'department' and value: department_filter = FlicketDepartment.query.filter_by( department=value).first() ticket_query = ticket_query.filter( FlicketTicket.category.has( FlicketCategory.department == department_filter)) if form: form.department.data = department_filter.id if key == 'user_id' and value: # ticket_query = ticket_query.filter_by(assigned_id=int(value)) ticket_query = ticket_query.filter( (FlicketTicket.assigned_id == int(value)) | (FlicketTicket.started_id == int(value))) user = FlicketUser.query.filter_by(id=value).first() if form: form.username.data = user.username if key == 'content' and value: # search the titles if form: form.content.data = key f1 = FlicketTicket.title.ilike('%' + value + '%') f2 = FlicketTicket.content.ilike('%' + value + '%') f3 = FlicketTicket.posts.any( FlicketPost.content.ilike('%' + value + '%')) ticket_query = ticket_query.filter(f1 | f2 | f3) return ticket_query, form @staticmethod def sorted_tickets(ticket_query, sort): """ Function to return sorted tickets. :param ticket_query: :param sort: :return: """ if sort == 'priority': ticket_query = ticket_query.order_by( FlicketTicket.ticket_priority_id, FlicketTicket.id) elif sort == 'priority_desc': ticket_query = ticket_query.order_by( FlicketTicket.ticket_priority_id.desc(), FlicketTicket.id) elif sort == 'title': ticket_query = ticket_query.order_by(FlicketTicket.title, FlicketTicket.id) elif sort == 'title_desc': ticket_query = ticket_query.order_by(FlicketTicket.title.desc(), FlicketTicket.id) elif sort == 'ticketid': ticket_query = ticket_query.order_by(FlicketTicket.id) elif sort == 'ticketid_desc': ticket_query = ticket_query.order_by(FlicketTicket.id.desc()) elif sort == 'addedby': ticket_query = ticket_query.join(FlicketUser, FlicketTicket.user) \ .order_by(FlicketUser.name, FlicketTicket.id) elif sort == 'addedby_desc': ticket_query = ticket_query.join(FlicketUser, FlicketTicket.user) \ .order_by(FlicketUser.name.desc(), FlicketTicket.id) elif sort == 'addedon': ticket_query = ticket_query.order_by(FlicketTicket.date_added, FlicketTicket.id) elif sort == 'addedon_desc': ticket_query = ticket_query.order_by( FlicketTicket.date_added.desc(), FlicketTicket.id) elif sort == 'last_updated': ticket_query = ticket_query.order_by(FlicketTicket.last_updated, FlicketTicket.id) elif sort == 'last_updated_desc': ticket_query = ticket_query.order_by( FlicketTicket.last_updated.desc(), FlicketTicket.id) elif sort == 'replies': replies_count = func.count(FlicketPost.id).label('replies_count') ticket_query = ticket_query.outerjoin(FlicketTicket.posts).group_by(FlicketTicket.id) \ .order_by(replies_count, FlicketTicket.id) elif sort == 'replies_desc': replies_count = func.count(FlicketPost.id).label('replies_count') ticket_query = ticket_query.outerjoin(FlicketTicket.posts).group_by(FlicketTicket.id) \ .order_by(replies_count.desc(), FlicketTicket.id) elif sort == 'department_category': ticket_query = ticket_query.join(FlicketCategory, FlicketTicket.category) \ .join(FlicketDepartment, FlicketCategory.department) \ .order_by(FlicketDepartment.department, FlicketCategory.category, FlicketTicket.id) elif sort == 'department_category_desc': ticket_query = ticket_query.join(FlicketCategory, FlicketTicket.category) \ .join(FlicketDepartment, FlicketCategory.department) \ .order_by(FlicketDepartment.department.desc(), FlicketCategory.category.desc(), FlicketTicket.id) elif sort == 'status': ticket_query = ticket_query.order_by(FlicketTicket.status_id, FlicketTicket.id) elif sort == 'status_desc': ticket_query = ticket_query.order_by( FlicketTicket.status_id.desc(), FlicketTicket.id) elif sort == 'assigned': ticket_query = ticket_query.outerjoin(FlicketUser, FlicketTicket.assigned) \ .order_by(FlicketUser.name, FlicketTicket.id) elif sort == 'assigned_desc': ticket_query = ticket_query.outerjoin(FlicketUser, FlicketTicket.assigned) \ .order_by(FlicketUser.name.desc(), FlicketTicket.id) elif sort == 'time': total_hours = (FlicketTicket.hours + func.sum(FlicketPost.hours)).label('total_hours') ticket_query = ticket_query.outerjoin(FlicketTicket.posts).group_by(FlicketTicket.id) \ .order_by(total_hours, FlicketTicket.id) elif sort == 'time_desc': total_hours = (FlicketTicket.hours + func.sum(FlicketPost.hours)).label('total_hours') ticket_query = ticket_query.outerjoin(FlicketTicket.posts).group_by(FlicketTicket.id) \ .order_by(total_hours.desc(), FlicketTicket.id) return ticket_query def from_dict(self, data): """ :param data: :return: """ for field in ['title', 'content', 'category_id', 'ticket_priority_id']: if field in data: setattr(self, field, data[field]) def to_dict(self): """ :return: dict() """ modified_by = None assigned = None if self.modified_id: modified_by = app.config['base_url'] + url_for('bp_api.get_user', id=self.modified_id) if self.assigned_id: assigned = app.config['base_url'] + url_for('bp_api.get_user', id=self.assigned_id) data = { 'id': self.id, 'assigned_id': self.assigned_id, 'category_id': self.category_id, 'content': self.content, 'date_added': self.date_added, 'date_modified': self.date_modified, 'modified_id': self.modified_id, 'started_id': self.started_id, 'status_id': self.status_id, 'title': self.title, 'ticket_priority_id': self.ticket_priority_id, 'links': { 'self': app.config['base_url'] + url_for('bp_api.get_ticket', id=self.id), 'assigned': assigned, 'priority': app.config['base_url'] + url_for('bp_api.get_priority', id=self.ticket_priority_id), 'started_ny': app.config['base_url'] + url_for('bp_api.get_user', id=self.started_id), 'modified_by': modified_by, 'category': app.config['base_url'] + url_for('bp_api.get_category', id=self.category_id), 'status': app.config['base_url'] + url_for('bp_api.get_status', id=self.status_id), 'subscribers': app.config['base_url'] + url_for('bp_api.get_subscriptions', ticket_id=self.id), 'tickets': app.config['base_url'] + url_for('bp_api.get_tickets'), 'histories': app.config['base_url'] + url_for('bp_api.get_histories', topic_id=self.id), } } return data def __repr__(self): return (f'<FlicketTicket: ' f'id={self.id}, ' f'title="{self.title}", ' f'created_by={self.user}, ' f'category={self.category}' f'status={self.current_status}' f'assigned={self.assigned}>')
class FlicketPost(PaginatedAPIMixin, Base): __tablename__ = 'flicket_post' id = db.Column(db.Integer, primary_key=True) ticket_id = db.Column(db.Integer, db.ForeignKey(FlicketTicket.id)) ticket = db.relationship(FlicketTicket, back_populates='posts') content = db.Column(db.String(field_size['content_max_length'])) user_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) user = db.relationship(FlicketUser, foreign_keys='FlicketPost.user_id') date_added = db.Column(db.DateTime()) date_modified = db.Column(db.DateTime()) modified_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) modified = db.relationship(FlicketUser, foreign_keys='FlicketPost.modified_id') hours = db.Column(db.Numeric(10, 2), server_default='0') # finds all the images associated with the post uploads = db.relationship( 'FlicketUploads', primaryjoin="and_(FlicketPost.id == FlicketUploads.posts_id)") # finds all the actions associated with the post actions = db.relationship( 'FlicketAction', primaryjoin="FlicketPost.id == FlicketAction.post_id") def to_dict(self): """ :return: dict() """ data = { 'id': self.id, 'content': self.content, 'data_added': self.date_added, 'date_modified': self.date_modified, 'ticket_id': self.ticket_id, 'user_id': self.user_id, 'links': { 'self': app.config['base_url'] + url_for('bp_api.get_post', id=self.id), 'created_by': app.config['base_url'] + url_for('bp_api.get_user', id=self.user_id), 'posts': app.config['base_url'] + url_for('bp_api.get_posts', ticket_id=self.ticket_id), } } return data def __repr__(self): return "<FlicketPost: id={}, ticket_id={}, content={}>".format( self.id, self.ticket_id, self.content)
class Community(db.Model, Mixin): __tablename__ = 'community' class STATUS: ( ACTIVE, DELETED, ) = range(2) TITLE = dict([(ACTIVE, 'active'), (DELETED, 'deleted')]) class TYPE: ( PUBLIC, PRIVATE, ) = range(2) TITLE = dict([(PUBLIC, 'public'), (PRIVATE, 'private')]) id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) description = db.Column(db.String(255)) image_id = db.Column(db.Integer, db.ForeignKey('file.id')) create_date = db.Column(db.DateTime, default=datetime.now) type = db.Column(db.Integer, default=TYPE.PUBLIC) status = db.Column(db.Integer, default=STATUS.ACTIVE) owner_id = db.Column(db.Integer, db.ForeignKey('users.id')) owner = db.relationship("User", backref="community", lazy="joined") image = db.relationship("File", backref="community", lazy="joined") members = db.relationship("User", secondary="community_member", backref="communities", lazy='joined') community_members = db.relationship("CommunityMember", backref=db.backref("community"), lazy='joined') posts = db.relationship("Post", backref="community", order_by=(Post.datetime.desc())) def has_member(self, user): return user is self.owner or user in self.members def member_status_title(self, user): status = self.member_status(user) return None if status is None else CommunityMember.STATUS.TITLE[status] def member_status(self, user): try: cm = next(cm for cm in self.community_members if cm.user == user) return cm.status except StopIteration: return None @property def count_members(self): return len([ cm for cm in self.community_members if cm.status == cm.STATUS.ACCEPTED ]) + 1 @classmethod def all_active(cls): return cls.query.filter(cls.status == cls.STATUS.ACTIVE).order_by( cls.id.desc()).all() @classmethod def all_mine(cls): user = auth.service.get_user() communities = [ c.community for c in CommunityMember.query.filter( CommunityMember.user == user).all() if c.community.status == Community.STATUS.ACTIVE ] mine_communities = cls.query.filter( cls.owner == user, cls.status == cls.STATUS.ACTIVE).all() return mine_communities + communities