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
