class Student(db.Model):
__tablename__ = 'students'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer(), db.ForeignKey('users.id'), nullable = False, unique = True)
educational_group_id = db.Column(db.Integer(), db.ForeignKey('EducationalGroup.id'), nullable = False)
entry_year = db.Column(db.Integer(), nullable = False)
degree = db.Column(db.String(100), nullable = False)
tuition_format = db.Column(db.String(100), nullable = False)
tuition_base = db.Column(db.String(100), nullable = False)
__table_args__ = (db.CheckConstraint(degree.in_(["бакалавр", "специалист", "магистр"])),
db.CheckConstraint(tuition_format.in_(["очная", "заочная", "вечерняя"])),
db.CheckConstraint(tuition_base.in_(["контрактная", "бюджетная"])),
)
def __init__(self, user_id, educational_group_id, entry_year, degree, tuition_format, tuition_base):
self.user_id = user_id
self.educational_group_id = educational_group_id
self.entry_year = entry_year
self.degree = degree
self.tuition_format = tuition_format
self.tuition_base = tuition_base
@staticmethod
def create_student(name, surname, second_name, educational_group_id, entry_year, degree, tuition_format, tuition_base):
user = User.create_user(name, surname, second_name)
student = Student(user_id = user.id, educational_group_id=educational_group_id, entry_year=entry_year, degree=degree, tuition_format=tuition_format, tuition_base=tuition_base)
db_add_objects(student)
return student
def __repr__(self):
return "<Student {}:educational_group_id:{}>".format(self.id, self.educational_group_id)
class Tag(db.Model):
__tablename__ = "tag"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True, nullable=False)
background = db.Column(db.String(6), nullable=False, default="000000")
color = db.Column(db.String(6), nullable=False, default="ffffff")
issues = db.relationship("Issue",
secondary=tags_association,
back_populates="tags")
__table_args__ = (
db.CheckConstraint("length(name) > 0", name="name_min_length"),
db.CheckConstraint("length(background) > 5",
name="background_min_length"),
db.CheckConstraint("length(background) < 7",
name="background_max_length"),
db.CheckConstraint("length(color) > 5", name="color_min_length"),
db.CheckConstraint("length(color) < 7", name="color_max_length"),
)
@db.validates("name")
def validate_background(self, key, name: str) -> str:
if len(name) < 1:
raise ValueError(TOO_SHORT)
return name
@db.validates("background")
def validate_background(self, key, background: str) -> str:
if len(background) <= 5 or len(background) >= 7:
raise ValueError(TYPE_NOT_COLOR)
return background
@db.validates("color")
def validate_background(self, key, color: str) -> str:
if len(color) <= 5 or len(color) >= 7:
raise ValueError(TYPE_NOT_COLOR)
return color
def __repr__(self) -> str:
"""
:return: Returns a string representation of the object
"""
return f"{self.__class__.__name__}" \
f"('name': '{self.name}', 'background': '{self.background}', 'color': '{self.color}')"
def __iter__(self) -> None:
"""
:return: Yields the items attributes as a dictionary
"""
yield "id", self.id
yield "name", self.name
yield "background", self.background
yield "color", self.color
def __len__(self) -> int:
"""
:return: Returns the total number of the tables SQL columns
"""
return 4
class Message(db.Model):
"""
A message model with a relationship with the Users tables for sender and receiver.
Both sides can access the message and read it. When one side deletes a message, the respective id becomes null.
If both sides delete the message, it is deleted from the database.
"""
__tablename__ = "messages"
id = db.Column(db.Integer, primary_key=True)
subject = db.Column(db.String(50))
content = db.Column(db.String(250))
sent_date = db.Column(db.DateTime, index=True, default=datetime.utcnow)
sender_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=True)
receiver_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=True)
sender_name = db.Column(db.String(30))
receiver_name = db.Column(db.String(30))
read = db.Column(db.Boolean, default=False)
__table_args__ = (db.CheckConstraint(sender_id != receiver_id), )
def __init__(self, sender_id, receiver_id, sender, receiver, subject,
content):
self.sender_id = sender_id
self.receiver_id = receiver_id
self.sender_name = sender
self.receiver_name = receiver
self.subject = subject
self.content = content
def __repr__(self):
return f'Sender: {self.sender_name}, Receiver: {self.receiver_name}, ' \
f'Date Sent: {self.sent_date}, Subject: {self.subject}'
class Type(db.Model):
__tablename__ = "type"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), nullable=False, unique=True)
__table_args__ = (db.CheckConstraint("length(name) > 0",
name="name_min_length"), )
@db.validates("name")
def validate_background(self, key, name: str) -> str:
if len(name) < 1:
raise ValueError(TOO_SHORT)
return name
def __repr__(self) -> str:
"""
:return: Returns a string representation of the object
"""
return f"{self.__class__.__name__}('name': '{self.name}')"
def __iter__(self) -> None:
"""
:return: Yields the items attributes as a dictionary
"""
yield "id", self.id
yield "name", self.name
def __len__(self) -> int:
"""
:return: Returns the total number of the tables SQL columns
"""
return 2
class Employee(db.Model):
"""
An employee of the hospital. It abstracts the information of the different types of
employees that the hospital has.
"""
__tablename__ = 'employee'
dni = db.Column(db.Integer, primary_key=True, autoincrement=False)
name = db.Column(db.String(45), nullable=False)
last_name = db.Column(db.String(45), nullable=False)
address = db.Column(db.String(128), nullable=False, default='Desconocida')
phone_number = db.Column(db.String(10), unique=True, default='N/A')
salary = db.Column(db.Float, nullable=False)
available = db.Column(db.Boolean, nullable=False)
type = db.Column(db.String(3), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'employee',
'polymorphic_on': type
}
__table_args__ = (db.CheckConstraint('salary > 0'), )
def __init__(self, dni, name, ln, addr, pn, salary, available, _type):
self.dni = dni
self.name = name
self.last_name = ln
self.address = addr
self.phone_number = pn
self.salary = salary
self.available = available
self.type = _type
def __repr__(self):
return '%s %s' % (self.name, self.last_name)
class ParamedicTeam(db.Model):
__tablename__ = 'paramedics_team'
id = db.Column('id_params_team',
db.Integer,
nullable=False,
primary_key=True)
type = db.Column(db.String(2), nullable=False)
available = db.Column(db.Boolean, nullable=False)
operation_fee = db.Column(db.Float, nullable=False)
members = db.relationship('Paramedic', back_populates='team')
dispatches = db.relationship('Dispatch', backref='paramedics_team')
__table_args__ = (db.CheckConstraint("""
type = 'SB' OR
type = 'SA' OR
type = 'SV'"""), )
def __init__(self, _type, available, of):
self.type = _type
self.available = available
self.operation_fee = of
def _pretty_type(self):
if self.type == 'SB':
return u'Soporte Básico'
elif self.type == 'SA':
return 'Soporte Avanzado'
else:
return 'Soporte Vital'
def __repr__(self):
return 'Equipo %s: %s' % (self.id, self._pretty_type())
class User(db.Model, UserMixin):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), nullable=False)
last_name = db.Column(db.String(32), nullable=False)
genre = db.Column(db.CHAR(1),
db.CheckConstraint('genre in (\'M\', \'F\')'),
nullable=False)
username = db.Column(db.String(80), nullable=False, unique=True)
email = db.Column(db.String(120), nullable=False, unique=True)
_password = db.Column('password', db.String(128), nullable=False)
def __init__(self, name, last_name, genre, username, email, pw):
self.name = name
self.last_name = last_name
self.genre = genre
self.username = username
self.email = email
self._password = bcrypt.generate_password_hash(pw)
@hybrid_property
def password(self):
return self._password
@password.setter
def _set_password(self, pw):
self._password = bcrypt.generate_password_hash(pw)
def validate_password(self, password):
return bcrypt.check_password_hash(bytes(self.password), password)
def __repr__(self):
return 'User <%r>' % self.username
class User(db.Model):
__tablename__ = "user"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), nullable=False)
created_on = db.Column(db.DateTime, default=datetime.utcnow())
__table_args__ = (db.CheckConstraint("length(name) > 0",
name="name_min_length"), )
@db.validates("name")
def validate_background(self, key, name: str) -> str:
if len(name) < 1:
raise ValueError(TOO_SHORT)
return name
def __repr__(self) -> str:
"""
:return: Returns a string representation of the object
"""
return f"{self.__class__.__name__}('name': '{self.name}', 'created_on': '{str(self.created_on)}')"
def __iter__(self) -> None:
"""
:return: Yields the items attributes as a dictionary
"""
yield "id", self.id
yield "name", self.name
yield "created_on", str(self.created_on)
def __len__(self) -> int:
"""
:return: Returns the total number of the tables SQL columns
"""
return 3
class Resource(Base):
account_id = db.Column(db.Integer,
db.ForeignKey("account.id"),
nullable=False,
index=True)
address = db.Column(db.String(144), nullable=False)
type = db.Column(db.String(144), nullable=False, index=True)
name = db.Column(db.String(144), nullable=False)
price = db.Column(db.Numeric,
db.CheckConstraint("price >= 0 AND price <= 1000000"),
nullable=False)
bookings = db.relationship("Booking",
lazy=True,
backref=db.backref("resource", lazy=False),
cascade="all, delete-orphan")
__table_args__ = (UniqueConstraint("address",
"type",
"name",
name="unique_atn"), )
def __init__(self, account_id, address, type, name, price, communities):
self.account_id = account_id
self.address = address
self.type = type
self.name = name
self.price = price
self.communities = communities
def __str__(self):
return "%s, %s %s" % (self.address, self.type, self.name)
def price_rnd(self):
return self.price.quantize(Decimal('.01'), rounding=ROUND_DOWN)
def price_str(self):
return PRICE % self.price
@staticmethod
def get_allowed():
if not current_user.is_authenticated:
return []
stmt = text(
"SELECT * FROM resource WHERE id IN "
"(SELECT DISTINCT resource_id "
"FROM community_resource, admin "
"WHERE community_resource.community_id "
"= admin.community_id "
"AND admin.account_id = :user_id) "
"OR id IN "
"(SELECT DISTINCT resource_id "
"FROM community_resource, account "
"WHERE community_resource.community_id "
"= account.community_id "
"AND account.id = :user_id)").params(user_id=current_user.get_id())
return db.session.query(Resource).from_statement(stmt).all()
class Dispatch(db.Model):
__tablename__ = 'dispatch'
id_ambulance = db.Column(db.Integer,
db.ForeignKey('ambulance.id_ambulance'),
nullable=False)
id_params_team = db.Column(db.Integer,
db.ForeignKey('paramedics_team.id_params_team'),
nullable=False)
id_emergency = db.Column(db.Integer,
db.ForeignKey('emergency.id_emergency'),
nullable=False)
dispatch_hour = db.Column(db.DateTime, nullable=False)
arrival_hour = db.Column(db.DateTime)
distance = db.Column(db.Integer)
status = db.Column(db.Integer, nullable=False)
fee = db.Column(db.Float)
__table_args__ = (db.PrimaryKeyConstraint('id_ambulance', 'id_params_team',
'id_emergency'),
db.CheckConstraint('status >= 1 AND status <= 5'))
def __init__(self,
ambulance,
paramedics,
emergency,
dispatch_hour,
arrival_hour=None,
distance=0,
status=1,
fee=0):
self.id_ambulance = ambulance
self.id_params_team = paramedics
self.id_emergency = emergency
self.dispatch_hour = dispatch_hour
self.arrival_hour = arrival_hour
self.distance = distance
self.status = status
self.fee = fee
def pretty_status(self):
if self.status == 1:
return 'En ruta'
elif self.status == 2:
return 'En el sitio'
elif self.status == 3:
return 'Volviendo'
elif self.status == 4:
return 'Completado'
elif self.status == 5:
return 'Cancelado'
else:
raise AttributeError(
'status should be some number between 1 and 5')
class InsurancePlan(db.Model):
__tablename__ = 'insurance_plan'
id = db.Column('id_insurance_plan', db.Integer, primary_key=True)
category = db.Column(db.Integer, nullable=False)
coverage_percentage = db.Column(db.Integer, nullable=False)
description = db.Column(db.String(60), nullable=False)
__table_args__ = (db.CheckConstraint('category >= 1 AND category <= 4'), )
def __init__(self, category, coverage_percentage, description):
self.category = category
self.coverage_percentage = coverage_percentage
self.description = description
class Invoice(Base):
price = db.Column(db.Numeric, db.CheckConstraint("price >= 0"),
nullable=False)
paid = db.Column(db.Boolean, nullable=False, index=True)
bookings = db.relationship("Booking", secondary=invoice_booking,
lazy="subquery", single_parent=True,
backref=db.backref("invoice", lazy=True,
cascade="all"))
def __init__(self, bookings):
self.paid = False
self.bookings = bookings
self.calculate_price()
def __str__(self):
return str(self.id)
def price_str(self):
return PRICE % self.price
def calculate_price(self):
self.price = sum([Decimal(b.price) for b in self.bookings])
@staticmethod
def get_allowed(filter_unpaid=False):
if not current_user.is_authenticated:
return []
query = ("SELECT * FROM invoice WHERE id IN "
"(SELECT invoice_id FROM invoice_booking "
"INNER JOIN booking "
"ON invoice_booking.booking_id = booking.id "
"WHERE booking.account_id IN "
"(SELECT id FROM account "
"WHERE community_id IN "
"(SELECT community.id FROM community "
"INNER JOIN admin "
"ON community.id = admin.community_id "
"WHERE admin.account_id = :user_id) "
"OR id = :user_id)) ")
params = {"user_id": current_user.get_id()}
if filter_unpaid:
query += "AND paid IS :false "
params["false"] = False
query += "ORDER BY date_created"
stmt = text(query).params(params)
return db.session.query(Invoice).from_statement(stmt).all()
class Patient(db.Model):
__tablename__ = 'patient'
id = db.Column(db.Integer,
primary_key=True,
unique=True,
autoincrement=True)
patientSSN = db.Column(db.Integer, primary_key=True, unique=True)
name = db.Column(db.String(30), nullable=False)
age = db.Column(db.Integer,
db.CheckConstraint('age>0 and age<200'),
nullable=False)
dateOfAdmission = db.Column(db.DateTime, default=datetime.utcnow)
typeOfBed = db.Column(db.String, nullable=False)
address = db.Column(db.String, nullable=False)
state = db.Column(db.String)
city = db.Column(db.String)
status = db.Column(db.String, nullable=False)
medicine_bill = db.relationship('Medicine', secondary=billing)
test_bill = db.relationship('Test', secondary=billing)
class Comment(db.Model):
__tablename__ = "comment"
id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey('issue.id'))
author = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
content = db.Column(db.Text, nullable=False)
edited = db.Column(db.Boolean, default=False)
created_on = db.Column(db.DateTime, default=datetime.utcnow())
__table_args__ = (db.CheckConstraint("length(content) > 0",
name="content_min_length"), )
@db.validates("name")
def validate_background(self, key, content: str) -> str:
if len(content) < 1:
raise ValueError(TOO_SHORT)
return content
def __repr__(self) -> str:
"""
:return: Returns a string representation of the object
"""
return f"{self.__class__.__name__}" \
f"('id': '{self.id}', 'parent_id': '{self.parent_id}', 'author': '{self.author}'," \
f" 'content': '{self.content}', 'edited': '{self.edited}', 'created_on': '{self.created_on}')"
def __iter__(self) -> None:
"""
:return: Yields the items attributes as a dictionary
"""
yield "id", self.id
yield "parent_id", self.parent_id
yield "author", self.author
yield "content", self.content
yield "edited", self.edited
yield "created_on", str(self.created_on)
def __len__(self) -> int:
"""
:return: Returns the total number of the tables SQL columns
"""
return 6
class Favorite(db.Model):
__tablename__ = "Favorite"
source_id = db.Column(db.Integer,
db.ForeignKey("Profile.id"),
nullable=False)
target_id = db.Column(db.Integer,
db.ForeignKey("Profile.id"),
nullable=False)
date_created = db.Column(db.DateTime,
default=func.now(),
server_default=func.now(),
nullable=False)
__table_args__ = (
db.PrimaryKeyConstraint("source_id", "target_id"),
db.CheckConstraint("source_id != target_id"),
) # Can't favorite oneself.
def __init__(self, source_id, target_id):
self.source_id = source_id
self.target_id = target_id
class Paramedic(Employee):
__tablename__ = 'paramedic'
dni = db.Column(db.Integer,
db.ForeignKey('employee.dni'),
primary_key=True)
specialization = db.Column(db.CHAR(3), nullable=False, default='UNK')
id_params_team = db.Column(db.Integer,
db.ForeignKey('paramedics_team.id_params_team'))
team = db.relationship('ParamedicTeam', back_populates='members')
__mapper_args__ = {'polymorphic_identity': 'PRM'}
__table_args__ = (db.CheckConstraint("""
specialization = 'PAB' OR
specialization = 'APA' OR
specialization = 'AEM' OR
specialization = 'TEM'"""), )
def __init__(self, dni, name, ln, addr, pn, salary, available, _type,
specialization):
super(Paramedic, self).__init__(dni, name, ln, addr, pn, salary,
available, _type)
self.specialization = specialization
class Booking(Base):
account_id = db.Column(db.Integer,
db.ForeignKey("account.id"),
nullable=False,
index=True)
resource_id = db.Column(db.Integer,
db.ForeignKey("resource.id"),
nullable=False,
index=True)
start_dt = db.Column(db.DateTime, nullable=False, index=True)
end_dt = db.Column(db.DateTime, nullable=False, index=True)
price = db.Column(db.Numeric,
db.CheckConstraint("price >= 0"),
nullable=False)
__table_args__ = (CheckConstraint("start_dt < end_dt",
name="time_direction"), )
def __init__(self, account_id, resource_id, start_dt, end_dt):
self.account_id = account_id
self.resource_id = resource_id
self.start_dt = start_dt
self.end_dt = end_dt
self.calculate_price()
def __str__(self):
start_dt = check_type_dt(self.start_dt)
start_date = start_dt.strftime("%Y-%m-%d")
return (PRICE + ", %s, %s, %s") % (self.price, self.account,
self.resource, start_date)
def str_no_account(self):
start_dt = check_type_dt(self.start_dt).strftime("%Y-%m-%d %H:%M")
return (PRICE + ", %s, %s") % (self.price, self.resource, start_dt)
def start_date_str(self):
return check_type_dt(self.start_dt).strftime("%Y-%m-%d")
def start_time_str(self):
return check_type_dt(self.start_dt).strftime("%H:%M")
def end_date_str(self):
return check_type_dt(self.end_dt).strftime("%Y-%m-%d")
def end_time_str(self):
return check_type_dt(self.end_dt).strftime("%H:%M")
def price_str(self):
return PRICE % self.price
def calculate_price(self):
time_span = self.end_dt - self.start_dt
hours = Decimal(time_span.total_seconds()) / 3600
price = Resource.query.get(self.resource_id).price * hours
self.price = price.quantize(Decimal('.01'), rounding=ROUND_DOWN)
@staticmethod
def is_free_time_slot(b):
query = ("SELECT id FROM booking "
"WHERE resource_id = :resource_id "
"AND end_dt > :start_dt "
"AND start_dt < :end_dt")
stmt = (text(query + " AND id <> :booking_id").params(
booking_id=b.id,
resource_id=b.resource_id,
start_dt=b.start_dt,
end_dt=b.end_dt)
if b.id else text(query).params(resource_id=b.resource_id,
start_dt=b.start_dt,
end_dt=b.end_dt))
return not db.engine.execute(stmt).fetchone()
@staticmethod
def get_allowed_by_account(invoice_id=None):
if not current_user.is_authenticated:
return []
query = ("SELECT * FROM booking WHERE (account_id IN "
"(SELECT account.id FROM account "
"INNER JOIN admin "
"ON account.community_id = admin.community_id "
"WHERE admin.account_id = :user_id) "
"OR account_id = :user_id) "
"AND id NOT IN "
"(SELECT booking_id FROM invoice_booking")
params = {"user_id": current_user.get_id()}
if invoice_id:
query += " WHERE invoice_id <> :invoice_id"
params["invoice_id"] = invoice_id
query += ") ORDER BY start_dt"
stmt = text(query).params(params)
return db.session.query(Booking).from_statement(stmt).all()
@staticmethod
def get_allowed_by_resource(from_dt=None,
to_dt=None,
resource_ids=None,
filter_not_in_invoice=False):
if not current_user.is_authenticated:
return []
query = ("SELECT * FROM booking WHERE (resource_id IN "
"(SELECT DISTINCT community_resource.resource_id "
"FROM community_resource INNER JOIN admin "
"ON admin.community_id = community_resource.community_id "
"WHERE admin.account_id = :user_id) "
"OR resource_id IN "
"(SELECT community_resource.resource_id "
"FROM community_resource INNER JOIN account "
"ON account.community_id = community_resource.community_id "
"WHERE account.id = :user_id))")
params = {"user_id": current_user.get_id()}
if from_dt:
query += " AND end_dt > :from_dt"
params["from_dt"] = from_dt
if to_dt:
query += " AND start_dt < :to_dt"
params["to_dt"] = to_dt
if resource_ids:
query += " AND resource_id IN (:resource_id_0"
params["resource_id_0"] = resource_ids[0]
list_length = len(resource_ids)
if list_length > 1:
for i in range(1, list_length):
query += ", :resource_id_%d" % i
params["resource_id_%d" % i] = resource_ids[i]
query += ")"
if filter_not_in_invoice:
query += (" AND id NOT IN "
"(SELECT DISTINCT booking_id FROM invoice_booking)")
query += " ORDER BY start_dt"
stmt = text(query).params(params)
return db.session.query(Booking).from_statement(stmt).all()
class Issue(db.Model):
__tablename__ = "issue"
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(60), nullable=False)
reporter = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
assignee = db.Column(db.Integer, db.ForeignKey('user.id'), default=None)
description = db.Column(db.Text)
type = db.Column(db.Integer, db.ForeignKey("type.id"), default=None)
tags = db.relationship("Tag",
secondary=tags_association,
back_populates="issues")
status = db.Column(db.Integer, default=1)
priority = db.Column(db.Integer, default=3)
comments = db.relationship("Comment", backref="parent")
created_on = db.Column(db.DateTime, default=datetime.utcnow())
__table_args__ = (
db.CheckConstraint("length(title) > 0", name="title_min_length"),
db.CheckConstraint(f"priority < {len(data_models.PRIORITY) + 1}",
name="priority_max_length"),
db.CheckConstraint(f"status < {len(data_models.STATUS) + 1}",
name="status_max_length"),
)
@db.validates("title")
def validate_background(self, key, title: str) -> str:
if len(title) < 1:
raise ValueError(TOO_SHORT)
return title
@db.validates("priority")
def validate_background(self, key, priority: int) -> int:
if not data_models.validate_priority(priority):
raise ValueError(OUT_OF_RANGE)
return priority
@db.validates("status")
def validate_background(self, key, status: int) -> int:
if not data_models.validate_status(status):
raise ValueError(OUT_OF_RANGE)
return status
def __repr__(self) -> str:
"""
:return: Returns a string representation of the object.
"""
return f"{self.__class__.__name__}" \
f"('title': '{self.title}', 'reporter': '{self.reporter}', 'assignee': '{self.assignee}'," \
f"'description': '{self.description}', 'type': '{self.type}', 'status': '{self.status}'," \
f"'priority': '{self.priority}')"
def __iter__(self) -> None:
"""
:return: Yields the items attributes as a dictionary.
"""
yield "id", self.id
yield "title", self.title
yield "reporter", self.reporter
yield "assignee", self.assignee
yield "description", self.description
yield "type", self.type
yield "tags", self.tags
yield "status", self.status
yield "priority", self.priority
yield "comments", self.comments
yield "created_on", str(self.created_on)
def __len__(self) -> int:
"""
:return: Returns the total number of the tables SQL columns
"""
return 9
class Conversation(db.Model):
__tablename__ = "Conversation"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
date_created = db.Column(db.DateTime,
default=func.now(),
server_default=func.now(),
nullable=False)
alpha = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False)
beta = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False)
__table_args__ = (
db.UniqueConstraint("alpha", "beta"),
db.CheckConstraint("beta > alpha"),
)
def __init__(self, alpha, beta):
"""An alpha is always less than beta, and never equal."""
self.alpha = alpha
self.beta = beta
@staticmethod
def get_new_message_count():
return {"count": "123"}
@staticmethod
def find_my_conversations():
statement = text(
'SELECT "Conversation".id AS "conversation_id", "Conversation".date_created AS "conversation_started", '
'(SELECT LEFT(content, 32) FROM "Message" WHERE "Message".conversation_id = "Conversation".id ORDER BY date_created DESC LIMIT 1) AS "latest_message",'
'(SELECT date_created FROM "Message" WHERE "Message".conversation_id = "Conversation".id ORDER BY date_created DESC LIMIT 1) AS "date_created",'
'(SELECT handle FROM "Account" WHERE id=4) AS "alpha",'
'(SELECT handle FROM "Account" WHERE id=3) AS "beta"'
'FROM "Conversation" WHERE alpha=:id OR beta=:id;')
connection = db.engine.connect()
result_set = connection.execute(statement, id=current_user.id)
response = []
for row in result_set:
alpha = row["alpha"]
beta = row["beta"]
if alpha == current_user.handle:
other_profile = beta
else:
other_profile = alpha
response.append({
"conversation_id": row["conversation_id"],
"date_created": row["date_created"],
"other_profile": other_profile,
"latest_message": row["latest_message"]
})
connection.close()
return response
@staticmethod
def get_conversation(conversation_id):
statement = text(
'SELECT id, date_created AS "sent", content, '
'(SELECT "Account".handle FROM "Account" WHERE "Account".id = "Message".source_id) AS "source", '
'(SELECT "Account".handle FROM "Account" WHERE "Account".id = "Message".target_id) AS "target"'
'FROM "Message" WHERE conversation_id = :conversation_id ORDER BY date_created ASC;'
)
connection = db.engine.connect()
result_set = connection.execute(statement,
conversation_id=conversation_id)
response = []
for row in result_set:
if row["source"] == current_user.handle:
own_message = True
else:
own_message = False
response.append({
"id": row["id"],
"sent": row["sent"],
"content": row["content"],
"own_message": own_message
})
connection.close()
return response
@staticmethod
def __get_date_string(datetime):
return str(datetime.hour) + ":" + str(datetime.minute) + ":" + str(datetime.second) \
+ " " + str(datetime.day) + "." + str(datetime.month) + "." + str(datetime.year)
class Message(db.Model):
__tablename__ = "Message"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
conversation_id = db.Column(db.Integer,
db.ForeignKey("Conversation.id"),
nullable=False)
source_id = db.Column(db.Integer,
db.ForeignKey("Profile.id"),
nullable=False)
target_id = db.Column(db.Integer,
db.ForeignKey("Profile.id"),
nullable=False)
date_created = db.Column(db.DateTime,
default=func.now(),
server_default=func.now(),
nullable=False)
content = db.Column(db.String(1024), nullable=False)
has_source_deleted = db.Column(db.Boolean,
default=False,
server_default='f',
nullable=False)
has_target_deleted = db.Column(db.Boolean,
default=False,
server_default='f',
nullable=False)
read = db.Column(db.Boolean,
default=False,
server_default='f',
nullable=False)
__table_args__ = (db.CheckConstraint("source_id != target_id"),
) # Can't send a message to oneself.
def __init__(self, conversation_id, source_id, target_id, content):
self.conversation_id = conversation_id
self.source_id = source_id
self.target_id = target_id
self.content = content
@staticmethod
def get_latest_message_in_conversation(conversation_id):
statement = text(
'SELECT * FROM "Message" WHERE conversation_id=:conversation_id ORDER BY date_created DESC LIMIT 1;'
)
connection = db.engine.connect()
result_set = connection.execute(statement,
conversation_id=conversation_id)
response = []
for row in result_set:
own_message = False
if row[2] == current_user.id:
own_message = True
response.append({
"date_created": row[4],
"content": row[5],
"own_message": own_message
})
connection.close()
return response[0]