class UserModel(db.Model):
__bind_key__ = 'zentao'
__tablename__ = 'zt_user'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
dept = db.Column(db.Integer)
account = db.Column(db.String(30), unique=True)
password = db.Column(db.String(32))
role = db.Column(db.String(10))
realname = db.Column(db.String(100))
nickname = db.Column(db.String(60))
gender = db.Column(db.Enum('f', 'm'))
mobile = db.Column(db.String(11))
email = db.Column(db.String(90))
qq = db.Column(db.String(20))
address = db.Column(db.String(120))
deleted = db.Column(db.Enum('0', '1'))
def __init__(self, _id, dept, account, password, role, realname, nickname,
gender, deleted):
self.id = _id
self.dept = dept
self.account = account
self.password = password
self.role = role
self.realname = realname
self.nickname = nickname
self.gender = gender
self.deleted = deleted
# 验证密码是否匹配
def check_password(self, type_pwd):
cipher_obj = hashlib.md5(type_pwd.encode('utf-8')) # 传入值md5后验证数据库密码
cipher_str = cipher_obj.hexdigest()
if self.password == cipher_str:
return True
else:
return False
# 查询是否存在账号
@classmethod
def query_by_account(cls, acc):
try:
result = cls.query.filter_by(account=acc, deleted='0').first()
except:
result = None
return result
@classmethod
def query_QA_all(cls):
result = cls.query.filter_by(dept='4', deleted='0').all()
return result
@classmethod
def query_all(cls):
result = cls.query.filter_by(deleted='0').all()
# result = cls.query.filter(UserModel.dept != '5', UserModel.deleted == '0').all() # 筛选掉工程部门的用户
return result
# 根据单选条件查询
@classmethod
def query_conditions(cls, _id, dept, account, role, realname, gender):
result = cls.query.filter_by(deleted='0')
if _id:
result = result.filter_by(id=_id)
if dept:
result = result.filter_by(dept=dept)
if account:
result = result.filter_by(account=account)
if role:
result = result.filter_by(role=role)
if realname:
result = result.filter_by(realname=realname)
if gender:
result = result.filter_by(gender=gender)
return result.all()
# 多选条件查询(一般字段是枚举类型会多选)
@classmethod
def query_checkbox(cls,
dept_list,
role_list,
gender_list,
page_index=1,
page_size=10): # 默认第一页，10条/页
result = cls.query.filter_by(deleted='0')
if dept_list:
result = result.filter(cls.dept.in_(dept_list))
if role_list:
result = result.filter(cls.role.in_(role_list))
if gender_list:
result = result.filter(cls.gender.in_(gender_list))
return result.paginate(page=page_index, per_page=page_size)
class ItemModel(db.Model):
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
price = db.Column(db.Float(precision=2))
store_id = db.Column(
db.Integer,
db.ForeignKey('stores.id')) # set foreign_key of id(stores) table
store = db.relationship('StoreModel') # works as a JOIN in SQLAlchemy
def __init__(self, name, price,
store_id): # there is_no "id" parameter, it won't be used
self.name = name
self.price = price
self.store_id = store_id
def json(self
): # to return a_basic_json_representation(ItemModel)..i.e.item
return {
'name': self.name,
'price': self.price
} # that_is a_dictionary representing_an_item
@classmethod # should_be_a_classMethod BCZ it_returns an_object_of_type_"ItemModel"
def find_by_name(cls, name): # as supposed to a DICTIONARY
# returns the "ItemModel_object"
# return ItemModel.query.filter_by(name=name).first() # SELEST * FROM items WHERE name=name LIMIT_1
#/.filetr_by(id=1)
return cls.query.filter_by(name=name).first()
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
# query = "SELECT * FROM items WHERE name=?"
# result = cursor.execute(query, (name,))
# row = result.fetchone()
# connection.close()
# if row:
# # return {'item': {'name': row[0], 'price': row[1]}} # returns a Dictionary
# return cls(*row) # cls(row[0], row[1]) # return an_object_of_type_"ItemModel" instead_of_dictionary With_arg_unpacking
# def insert(self): # pass "item" as_it_self as_an_argument_of_function_of_ItemModel
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
# query = "INSERT INTO items VALUES (?, ?)"
# # cursor.execute(query, (item['name'], item['price']))
# cursor.execute(query, (self.name, self.price))
# connection.commit()
# connection.close()
def save_to_db(self): # contains_for_both_INSERT_and_UPDATE
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class ItemModel(db.Model):
__tablename__ = 'geometry_columns'
id = db.Column(db.Integer, primary_key=True)
idm = db.Column(db.String(80))
date = db.Column(db.String(80))
name = db.Column(db.String(80))
telephone = db.Column(db.String(80))
email = db.Column(db.String)
categorie = db.Column(db.String)
toelichting = db.Column(db.String)
XCoordinaat = db.Column(db.Float)
YCoordinaat = db.Column(db.Float)
image = db.Column(db.String)
status = db.Column(db.String)
nearestaddress = db.Column(db.String)
nearestpostal = db.Column(db.String)
nearestplace = db.Column(db.String)
def __init__(self, idm, date, name, telephone, email, categorie,
toelichting, XCoordinaat, YCoordinaat, image, status,
nearestaddress, nearestpostal, nearestplace):
self.idm = idm
self.date = date
self.name = name
self.telephone = telephone
self.email = email
self.categorie = categorie
self.toelichting = toelichting
self.XCoordinaat = XCoordinaat
self.YCoordinaat = YCoordinaat
self.image = image
self.status = status
self.nearestaddress = nearestaddress
self.nearestpostal = nearestpostal
self.nearestplace = nearestplace
def json(self):
#return{'id':self.idm,'date':self.date,'name':self.name, 'telephone':self.telephone,'email':self.email,'categorie':self.categorie,'toelichting':self.toelichting,'XCoordinaat':self.XCoordinaat,'YCoordinaat':self.YCoordinaat,'image':self.image,'status':self.status,'nearestaddress':self.nearestaddress,'nearestpostal':self.nearestpostal,'nearestplace':self.nearestplace}
#FeatureCollection(data['features'])
#return (data1)
return {
'type': 'Feature',
'id': self.idm,
"properties": {
'date': self.date,
"name": self.name,
'telephone': self.telephone,
'email': self.email,
'categorie': self.categorie,
'toelichting': self.toelichting,
'image': self.image,
'status': self.status,
'nearestaddress': self.nearestaddress,
'nearestpostal': self.nearestpostal,
'nearestplace': self.nearestplace
},
'geometry': {
'type': 'Point',
'coordinates': [self.XCoordinaat, self.YCoordinaat]
}
}
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(
name=name).first() #SELECT * FROM items WHERE name=name LIMIT 1
@classmethod
def find_by_categorie(cls, categorie):
return cls.query.filter_by(
categorie=categorie
) #SELECT * FROM items WHERE categorie=categorie LIMIT 1
def save_to_db(self):
db.session.add(self)
db.session.commit()
#@classmethod
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class User(db.Model):
'''
auth_level 1 - basic user
auth_level 2 - expert user
auth_level 3 - admin
'''
__tablename__ = 'fossil_finder_users'
id = db.Column(db.Integer, primary_key=True)
auth_level = db.Column(db.Integer, nullable=False)
group_code = db.Column(db.String(255), index=True, unique=True)
group_name = db.Column(db.String(255))
email = db.Column(db.String(255), index=True, unique=True)
password_hash = db.Column(db.String(255), index=True)
verified = db.Column(db.Boolean, nullable=False, default=False)
active = db.Column(db.Boolean, nullable=False, default=True)
def generate_token(self, exp=36000):
s = Serializer(app.config['SERIALIZER_KEY'], expires_in=exp)
return s.dumps({'id': self.id})
def generate_password_hash(self, password):
try:
password_hash = bcrypt.hashpw(password, bcrypt.gensalt())
self.password_hash = password_hash
db.session.commit()
except:
db.session.rollback()
def verify_password(self, password):
return bcrypt.hashpw(
password,
self.password_hash) == self.password_hash and self.verified
def login(self):
session['token'] = self.generate_token()
g.current_user = self
def verify(self):
try:
self.verified = True
db.session.add(self)
db.session.commit()
except:
db.session.rollback()
@classmethod
def from_group_code(cls, group_code):
return cls.query.filter(cls.group_code == group_code).first()
@classmethod
def from_token(cls, token):
s = Serializer(app.config['SERIALIZER_KEY'])
try:
data = s.loads(token)
except SignatureExpired:
return None
except BadSignature:
return None
user = cls.query.get(data['id'])
return user
@classmethod
def from_email(cls, email):
return cls.query.filter(cls.email == email).first()
@classmethod
def create(cls, email=None, password=None, auth_level=1):
if not email or not password:
return
try:
user = cls(email=email, auth_level=1)
db.session.add(user)
db.session.commit()
user.generate_password_hash(password)
return user
except:
db.session.rollback()
return None
class ServiceType(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, unique=True)
image = db.Column(db.String)
description = db.Column(db.String)
from db import db
#association table
asset_tags = db.Table(
'asset_tags',
db.Column("asset_id", db.ForeignKey("assets.id"), primary_key=True),
db.Column("tag_id", db.ForeignKey("tags.id"), primary_key=True))
class Project(db.Model, Serializrable):
__tablename__ = 'project'
id = db.Column(db.Integer,
db.Sequence('id_seq'),
primary_key=True,
autoincrement=True)
pro_name = db.Column(db.String(80), nullable=False, unique=True)
res_name = db.Column(db.String(80), nullable=False)
create_name = db.Column(db.String(80), nullable=False)
finish_time = db.Column(db.String(8), nullable=False)
create_time = db.Column(db.DateTime, default=datetime.datetime.now)
company = db.Column(db.String(80))
category = db.Column(db.String(80))
postcode = db.Column(db.String(80))
contact = db.Column(db.String(80))
tele_phone = db.Column(db.String(80))
u_email = db.Column(db.String(80))
address = db.Column(db.String(256))
#检查单
#check_id = db.Column(db.Integer)
program = db.relationship('Program', backref='pro', lazy=True)
def __repr__(self):
return '<Project %r>' % self.pro_name
class Materialtypes(db.Model):
id = db.Column(db.Integer, unique=True, nullable=False, primary_key=True)
name = db.Column(db.String(20), unique=True, nullable=False)
def __init__(self, name):
self.name = name
class GestorModel(db.Model):
__tablename__ = 'gestores'
id = db.Column(db.Integer, primary_key=True)
cpf = db.Column(db.String(11), unique=True)
username = db.Column(db.String(80))
password = db.Column(db.String(80))
email = db.Column(db.String(100), unique=True)
telefone = db.Column(db.String(30))
tipo = db.Column(db.String(100))
empresa = db.Column(db.String(10))
msg = db.Column(db.String(2))
def __init__(self, cpf, username, msg, password, email, telefone, tipo, empresa):
#def __init__(self, cpf, username, password, email, telefone, tipo, endereco, empresa):
self.cpf = cpf
self.username = username
self.password = password
self.email = email
self.telefone = telefone
self.tipo = tipo
self.msg = msg
self.empresa = empresa
def json(self):
return {
'id': self.id,
'cpf': self.cpf,
'username': self.username,
'tipo': self.tipo,
'email': self.email,
'telefone': self.telefone,
'msg': self.msg,
'empresa': self.empresa
}
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_username(cls, username):
return cls.query.filter_by(username=username).first()
@classmethod
def find_by_email(cls, email):
return cls.query.filter_by(email=email).first()
@classmethod
def find_by_cpf(cls, cpf):
return cls.query.filter_by(cpf=cpf).first()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
class ServerInquiriesModel(db.Model):
'''
Defines the model for the internal representation of server inquiries.
Table will be created after first request was made after starting the app.
'''
__tablename__ = "server_inquiries"
id = db.Column(db.Integer, primary_key=True)
qid = db.Column(db.Integer) # order of questions inside of a surveyid
surveyid = db.Column(db.String(100)) # name of the surveyid
serviceprovider = db.Column(db.String(50)) # name of the service provider
name = db.Column(db.String(30)) # name of a question
type = db.Column(db.String(15)) # type of a question
options = db.Column(db.String) # possible answer options
qdescription = db.Column(db.String(300)) # description of a question
locked = db.Column(
db.Integer) # if inquiry should be deleted after editing
quizmode = db.Column(
db.Integer) # if questions should be answered later by the client user
def __init__(self, qid, surveyid, serviceprovider, name, type, options,
qdescription, locked, quizmode):
self.qid = qid
self.surveyid = surveyid
self.serviceprovider = serviceprovider
self.name = name
self.type = type
self.options = options
self.qdescription = qdescription
self.locked = locked
self.quizmode = quizmode
def __repr__(self):
'''
JSON representation of a server inquiry.
'''
return f" surveyid: {self.surveyid}, serviceprovider: {self.serviceprovider}, qid: {self.qid}, name: {self.name}, type: {self.type}, options: {self.options}, description: {self.qdescription}, locked: {bool(self.locked)}, quizmode: {bool(self.quizmode)}"
def tojson(self):
'''
JSON representation of a server inquiry.
'''
return {
'qid': self.qid,
'surveyid': self.surveyid,
'serviceprovider': self.serviceprovider,
'name': self.name,
'type': self.type,
'options': json.loads(self.options),
'qdescription': self.qdescription,
'locked': bool(self.locked),
'quizmode': bool(self.quizmode)
}
@classmethod
def find_by_name(cls, name):
'''
Checks if a server inquiry to a specific surveyid already exists.
'''
return cls.query.filter_by(name=name).first()
@classmethod
def already_in_db(cls, surveyid, name):
'''
Checks if a server inquiry to a specific surveyid already exists.
'''
return cls.query.filter_by(surveyid=surveyid).filter_by(
name=name).first()
@classmethod
def find_by_surveyid(cls, surveyid):
'''
Returns one server inquiry belonging to a specific surveyid.
'''
return cls.query.filter_by(surveyid=surveyid).first()
@classmethod
def find_all_by_surveyid(cls, surveyid):
'''
Returns all server inquiries belonging to a specific surveyid.
'''
return cls.query.filter_by(surveyid=surveyid).all()
@classmethod ## TODO: implement function from the send reports file
def delete_all_inqs_by_surveyid(cls, surveyid):
'''
TODO: needs to be implemented.
'''
cls.query.filter_by(surveyid=surveyid).delete()
db.session.commit()
#db.session.query(ServerInquiriesModel).filter(ServerInquiriesModel.surveyid==surveyid).delete()
def save_to_db(self):
'''
Saves a server inquiry to the database.
'''
db.session.add(
self
) # a session is collection of objects we want to write into the db.
db.session.commit()
def delete_from_db(self):
'''
Deletes a server inquiry from the database.
'''
db.session.delete(self)
db.session.commit()
class QuestionModel(db.Model):
__tablename__ = 'questions'
question_id = db.Column(db.Integer, primary_key=True)
quiz_id = db.Column(db.Integer, db.ForeignKey('quizes.quiz_id'))
question_category = db.Column(db.String(100))
questiontype_id = db.Column(db.Integer, db.ForeignKey('questiontypes.questiontype_id'))
question_statement = db.Column(db.String(100))
question_correct_entries = db.Column(db.Integer)
question_wrong_entries = db.Column(db.Integer)
question_creation = db.Column(db.DateTime)
question_update = db.Column(db.DateTime)
answers = db.relationship('AnswerModel', lazy='dynamic', cascade="all, delete")
questiontype = db.relationship("QuestionTypeModel")
# All class property names must match to column defined above
# to save the information to the database
# Additional unmatched properties will not save in the database columns
def __init__(self, quiz_id, question_category, questiontype_id, question_statement, question_correct_entries, question_wrong_entries, question_creation=datetime.now(), question_update=datetime.now()):
self.quiz_id = quiz_id
self.question_category = question_category
self.questiontype_id = questiontype_id
self.question_statement = question_statement
self.question_correct_entries = question_correct_entries
self.question_wrong_entries = question_wrong_entries
self.question_creation = question_creation
self.question_update = question_update
def json(self):
return {'question_id': self.question_id, 'quiz_id': self.quiz_id, 'question_category': self.question_category,
'questiontype_id': self.questiontype_id, 'questiontype_name': self.questiontype.questiontype_name,
'question_statement': self.question_statement,
'question_correct_entries': self.question_correct_entries,
'question_wrong_entries': self.question_wrong_entries,
'answers': [answer.json() for answer in self.answers.all()],
'question_creation': self.question_creation.strftime('%Y-%m-%d %X'),
'question_update': self.question_update.strftime('%Y-%m-%d %X')}
@classmethod
def find_by_id(cls, question_id):
return cls.query.filter_by(question_id=question_id).first()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def delete_all(cls):
db.session.query(cls).delete()
db.session.commit()
@classmethod
def query_all(cls):
result = db.session.query(cls.question_id, cls.quiz_id, cls.question_category,
cls.questiontype_id, cls.question_statement,
cls.question_correct_entries, cls.question_wrong_entries,
cls.question_creation, cls.question_update).all()
return [{"question_id": question.question_id, "quiz_id": question.quiz_id,
"question_category": question.question_category, "questiontype_id": question.questiontype_id,
"question": question.question_statement, "question_correct_entries": question.question_correct_entries,
"question_wrong_entries": question.question_wrong_entries,
"question_creation": question.question_creation.strftime('%Y-%m-%d %X'),
"question_update": question.question_update.strftime('%Y-%m-%d %X')} for question in result]
class HomeModel(db.Model):
__tablename__ = 'home'
home_id = db.Column(db.Integer, primary_key=True)
purchase_date = db.Column(db.Date, nullable=False)
purchase_value = db.Column(db.DECIMAL(12), nullable=False)
area_sqft = db.Column(db.Integer, nullable=False)
home_type = db.Column(db.Enum("S", "M", "C", "T"), nullable=False)
auto_fire_notif = db.Column(db.Enum("Y", "N"), nullable=False)
security_sys = db.Column(db.Enum("Y", "N"), nullable=False)
swimming_pool = db.Column(db.Enum("U", "O", "I", "M", "N"))
basement = db.Column(db.Enum("Y", "N"), nullable=False)
customer_id = db.Column(
db.Integer,
db.ForeignKey('home_insurance.customer_id',
onupdate='CASCADE',
ondelete='CASCADE'))
home_insurance = db.relationship('HomeInsuranceModel')
def __init__(self, purchase_date, purchase_value, area_sqft, home_type,
auto_fire_notif, security_sys, swimming_pool, basement,
customer_id):
self.purchase_date = purchase_date
self.purchase_value = purchase_value
self.area_sqft = area_sqft
self.home_type = home_type
self.auto_fire_notif = auto_fire_notif
self.security_sys = security_sys
self.swimming_pool = swimming_pool
self.basement = basement
self.customer_id = customer_id
def json(self):
return {
'home_id': self.home_id,
'purchase_date': self.purchase_date,
'purchase_value': self.purchase_value,
'area_sqft': self.area_sqft,
'home_type': self.home_type,
'auto_fire_notif': self.auto_fire_notif,
'security_sys': self.security_sys,
'swimming_pool': self.swimming_pool,
'basement': self.basement,
'customer_id': self.customer_id
}
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(home_id=_id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class SubTopic(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200), nullable=False)
request_string1 = db.Column(db.String(200), nullable=False)
request_string2 = db.Column(db.String(200), nullable=False)
topic_id = db.Column(db.Integer, db.ForeignKey('topic.id'), nullable=False)
class ClasssModel(db.Model):
__tablename__ = "class"
class_id = db.Column(db.String(20), primary_key=True)
name = db.Column(db.String(80))
school_id = db.Column(db.String(20),
db.ForeignKey("school.school_id"),
nullable=False)
class_1 = db.relationship("SchoolModel")
class_2 = db.relationship("Student_And_ClassModel")
class_3 = db.relationship("Teacher_And_ClassModel")
class_4 = db.relationship("Subject_And_ClassModel")
mark_4 = db.relationship("MarkModel")
history_1 = db.relationship("HistoryModel")
def __init__(self, name, school_id, class_id):
self.class_id = class_id
self.name = name
self.school_id = school_id
def json(self):
return {
"class_id": self.class_id,
"name": self.name,
"school_id": self.school_id,
}
def to_json(data):
if type(data) in (tuple, list):
res = []
for i in data:
res.append(ClasssModel.json(i))
return jso.loads(jso.dumps(res, default=str))
else:
return jso.loads(jso.dumps(ClasssModel.json(data), default=str))
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name).first()
@classmethod
def find_by_class_id(cls, class_id):
return cls.query.filter_by(class_id=class_id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_list_by_name(cls, name, page, per_page):
school_list = (cls.query.filter(
cls.name.like("%" + name + "%")).paginate(page, per_page,
False).items)
return school_list
class StoreModel(db.Model
): # tells SQLAlchemy it's something to save/add to db
# tell ALchemy which table items will be stored in
__tablename__ = "stores"
# tell ALchemy which columns it will contain
# creates an index & makes it easier to search
id = db.Column(db.Integer, primary_key=True) # not used in prior code
name = db.Column(db.String(80)) # can limit size of username
# do a back reference to the ItemModel
# allows a store to see which items are in the items DB
# knows it is a many-to-1 relationship (list of items)
# items = db.relationship('ItemModel')
items = db.relationship(
'ItemModel',
lazy='dynamic') #don't create obj for each item in ItemModel yet
# self.items is no longer a list of items
def __init__(self, name):
"""
Upon creation of StoreModel, will have attribuate "name"
"""
# these items must match the columns above
# if they're not created above, they won't be stored to the DB
self.name = name
def json(self):
"""
Return JSON representation of the model.
"""
# return {'name': self.name, 'items': self.items}
# return {'name': self.name, 'items': [item.json for item in self.items]}
# with lazy='dynamic' self.items is a query builder in items table
# so until calling JSON method we're not looking into the table
return {
'name': self.name,
'items': [item.json() for item in self.items.all()]
}
@classmethod
def find_by_name(cls, name):
"""
This function acts like the GET method - will return information
from the database.
"""
return cls.query.filter_by(name=name).first() # returns ItemModel obj
def save_to_db(self): # changed from insert once SQLAlchemy got involved
"""
This function takes in an ItemModel object and saves it to the DB.
"""
# this is helpful for both update and insert
db.session.add(self) # session = coll of objs to add to DB
db.session.commit()
def del_from_db(self):
"""
This function deletes an ItemModel object from the DB.
"""
db.session.delete(self)
db.session.commit()
class ReservedRunningModel(db.Model):
__tablename__ = 'reserved_running'
rr_id = db.Column(db.Integer, primary_key=True, nullable=False)
# Atributo relacion con la tabla clients
clientId = db.Column(db.Integer,
db.ForeignKey('clients.client_id'),
unique=True)
# Atributo relacion con la tabla motos
motoId = db.Column(db.Integer, db.ForeignKey('motos.id'), unique=True)
# Atributo fecha y hora de la reserva. Formato DD/MM/YYYY HH:MM:SS
dateTimeReserved = db.Column(db.TIMESTAMP)
# Atributo fecha y hora del running(start). Formato DD/MM/YYYY HH:MM:SS
dateTimeStart = db.Column(db.TIMESTAMP)
# Atributo kilometros de la moto antes de hacer el start
kmStart = db.Column(db.Float)
def __init__(self, client, moto):
self.client = client
self.moto = moto
self.dateTimeReserved = datetime.now()
self.kmStart = moto.km_totales
def json_reserved(self):
data = {
'id': self.rr_id,
'client': self.client.json(),
'moto': self.moto.json(),
'dataTimeReserved':
self.convert_date_to_string(self.dateTimeReserved),
'kmStart': self.kmStart
}
return data
def json_start(self):
data = {
'id': self.rr_id,
'client': self.client.json(),
'moto': self.moto.json(),
'dataTimeReserved':
self.convert_date_to_string(self.dateTimeReserved),
'dataTimeStart': self.convert_date_to_string(self.dateTimeStart),
'kmStart': self.kmStart
}
return data
def json_prueba(self):
data = {
'id': self.id,
'matricula': self.matricula,
'model_generic': self.model_generic,
'km_restantes': self.km_restantes,
'address': self.address,
'last_coordinate_latitude': self.last_coordinate_latitude,
'last_coordinate_longitude': self.last_coordinate_longitude
}
return data
def convert_date_to_string(self, dateTime):
"""
Metodo que permite convertir el formato DateTime en formato String
"""
data_time = str(dateTime.day) + "/" \
+ str(dateTime.month) + "/" \
+ str(dateTime.year) + " " \
+ str(dateTime.hour) + ":" \
+ str(dateTime.minute) + ":" \
+ str(dateTime.second)
return data_time
def make_star_moto(self):
"""
Metodo que permite hacer el put de star en reserved
"""
self.dateTimeStart = datetime.now()
db.session.commit()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_id(cls, id):
return ReservedRunningModel.query.filter_by(rr_id=id).first()
@classmethod
def find_by_client(cls, client_id):
return ReservedRunningModel.query.filter_by(clientId=client_id).first()
@classmethod
def find_by_moto(cls, moto_id):
return ReservedRunningModel.query.filter_by(motoId=moto_id).first()
@classmethod
def find_by_client_moto(cls, client_id, moto_id):
return ReservedRunningModel.query.filter_by(
clientId=client_id).filter_by(motoId=moto_id).first()
def update_state_reserved(self):
"""
Metodo que permite actualizar el estado de la moto y ponerlo a 'RESERVED'
"""
self.moto.set_state('RESERVED')
db.session.commit()
def update_state_start(self):
"""
Metodo que permite actualizar el estado de la moto y ponerlo a 'ACTIVE'
"""
self.moto.set_state('ACTIVE')
db.session.commit()
def isReserved(self):
return self.moto.state == "RESERVED"
def isActive(self):
return self.moto.state == "ACTIVE"
def make_remaining_time(self):
"""
Metodo que permite crear el tiempo limite para start moto desde la reserva
"""
"""
Codigo real para la web
time_min = 10
ten_min_more = self.dateTimeReserved + timedelta(minutes=time_min)
return mas10min
"""
# Este tiempo es solo para la demo
time_sg = 30
mas10min = self.dateTimeReserved + timedelta(seconds=time_sg)
return mas10min
def check_remaining_time(self):
"""
Metodo que permite comprobar si no se ha superado el tiempo limite para start moto (True) o si se ha superado (False)
"""
# Si el tiempo reservado es menor al tiempo limite
return datetime.now() <= self.make_remaining_time()
def update_state_available(self):
"""
Metodo que permite actualizar el estado de la moto y ponerlo a 'AVAILABLE'
"""
self.moto.set_state('AVAILABLE')
db.session.commit()
class PasswordRecoveryModel(db.Model):
__tablename__ = 'password_recovery'
__table_args__ = (db.UniqueConstraint('key'), )
SIZE = 32
VALID_UNTIL = timedelta(hours=1)
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id'),
primary_key=True)
key = db.Column(db.String(SIZE), nullable=False)
time = db.Column(db.DateTime(), nullable=False)
def __init__(self, user_id, key=None):
self.user_id = user_id
self.time = datetime.now()
self.key = self.generate_key() if key is None else key
def json(self):
return {
'user_id': self.user_id,
'valid_until': (self.time + self.VALID_UNTIL).strftime('%H:%M:%S')
}
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
def update_from_db(self, data):
"""
Updates through a dictionary with paris of string of name of attribute and it's value. Following same structure
as json(). In case of wanting to modify an attribute of an enum use the string name of one of the values.
Will raise Exception in case of invalid enum value if it isn't contained inside the possible values of the enum.
"""
for attr, newValue in data.items():
if newValue is not None:
cls = getattr(self, attr)
# Checks if value is of the attribute that's trying to be modified is an Enum
if isinstance(cls, Enum):
# Checks if the enum doesn't contain the newValue
if newValue not in type(cls).__members__:
raise Exception(
f"Enum {type(cls).__name__} doesn't have value: {newValue}"
)
# Gets the object of the enum with same name as newValue
setattr(self, attr, type(cls)[newValue])
else:
setattr(self, attr, newValue)
db.session.commit()
def send_email(self, email, url_root):
message = f"Has sol·licitat una recuperació de contrasenya. Accedeix a {url_root}reset?key={self.key} "\
f"per canviar de contrasenya. \n L'enllaç deixarà de ser vàlid en {self.VALID_UNTIL} o si es torna " \
f"a solicitar un canvi en la mateixa compte."
send_email(email, 'Password recovery', message)
def has_time_expired(self):
return self.time + self.VALID_UNTIL < datetime.now()
@classmethod
def find_by_id(cls, user_id):
cls.clean_expired_keys()
return cls.query.filter_by(user_id=user_id).first()
@classmethod
def find_by_key(cls, key):
cls.clean_expired_keys()
return cls.query.filter_by(key=key).first()
@classmethod
def clean_expired_keys(cls):
"""
Cleans all entries that their time has expired. Will be called every time a query to the model is made.
Expiration time is decided through constant class variable VALID_UNTIL.
"""
time = datetime.now() - cls.VALID_UNTIL
cls.query.filter(cls.time <= time).delete()
@classmethod
def generate_key(cls):
"""
Generates a random key avoiding duplicating keys using the most secure random generator of the OS.
The key will be made by a combination of uppercase and lowercase letters and numbers.
"""
new_key = ''.join(random.SystemRandom().choice(string.ascii_letters +
string.digits)
for _ in range(cls.SIZE))
while cls.query.filter_by(key=new_key).count() != 0:
new_key = ''.join(
random.SystemRandom().choice(string.ascii_letters +
string.digits)
for _ in range(cls.SIZE))
return new_key
class Invoice(CoreMixin, Serializer, db.Model):
hotspot_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('hotspot.id'),
nullable=False)
host_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('host.id'),
nullable=False)
start_date = db.Column(db.DateTime, nullable=False)
end_date = db.Column(db.DateTime, nullable=False)
hnt_mined = db.Column(db.Float, nullable=False)
hnt_owed = db.Column(db.Float, nullable=False)
host_reward_percentage = db.Column(db.Integer, nullable=False)
paid = db.Column(db.Boolean, default=False, nullable=False)
paid_at = db.Column(db.DateTime)
payment_method = db.Column(db.String(100))
paid_from_hnt_wallet = db.Column(db.String(100))
paid_to_hnt_wallet = db.Column(db.String(100))
def serialize(self):
host = Host.query.get(self.host_id).serialize()
hotspot = Hotspot.query.get(self.hotspot_id).serialize()
return {
'host_id': self.host_id,
'hotspot_name': hotspot['name'],
'host_first_name': host['first_name'],
'host_last_name': host['last_name'],
'host_reward_percentage': host['reward_percentage'],
'start_date': self.start_date,
'end_date': self.end_date,
'paid': self.paid,
'paid_from_hnt_wallet': self.paid_from_hnt_wallet,
'paid_to_hnt_wallet': self.paid_to_hnt_wallet,
'hnt_mined': self.hnt_mined,
'hnt_owed': self.hnt_owed,
}
@staticmethod
def generate_invoices():
print('generate invoices')
class SubscriberModel(db.Model):
__tablename__ = 'subscriber'
id = db.Column(db.Integer, primary_key=True)
subscriber_id = db.Column(db.Integer,
db.ForeignKey('user.id', ondelete='CASCADE'))
notification_id = db.Column(
db.Integer, db.ForeignKey('notification.id', ondelete='CASCADE'))
subscribername = db.Column(db.String(80))
email = db.Column(db.String(80))
def __init__(self, notification_id, subscribername=None):
print(subscribername)
user = UserModel.find_by_username(str(subscribername))
#self.subscriber_id = subscriber_id
self.notification_id = notification_id
if subscribername == None:
self.subscribername = user.username
else:
self.subscribername = subscribername
self.email = user.email
def json(self):
return {
'id': self.id,
'subscriber_id': self.subscriber_id,
'notification_id': self.notification_id,
'subscribername': self.subscribername,
'email': self.email
}
@classmethod
def update(self, id, param):
group_subscriber = cls.find({"id": id})
if group_subscriber:
db.update(group_subscriber).values(**param)
'''
@classmethod
def find_by_subscriber_id(cls, subscriber_id):
return cls.query.filter_by(subscriber_id=subscriber_id).first()
'''
@classmethod
def find_by_notification_id(cls, id):
return cls.query.filter_by(notification_id=id).first()
@classmethod
def findById(cls, group_subscriber_id):
return cls.query.filter_by(id=group_subscriber_id).first()
@classmethod
def find(cls, **queryArguments):
return list(cls.query.filter_by(**queryArguments))
@classmethod
def findOne(cls, **queryArguments):
return cls.query.filter_by(**queryArguments).first()
def save_to_db(self):
db.session.add(self)
self.commit()
@staticmethod
def commit():
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class PersonModel(db.Model):
__tablename__ = 'Persons'
person_id = db.Column(db.Integer, primary_key=True)
id = db.Column(UUID(as_uuid=True), default=uuid.uuid4, nullable=False)
first_name = db.Column(db.String(100), nullable=False)
middle_name = db.Column(db.String(100))
last_name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(1000), nullable=False)
age = db.Column(db.Integer, nullable=False)
version = db.Column(db.Integer, nullable=False)
latest = db.Column(db.Boolean, nullable=False)
def __init__(self, id, first_name, middle_name, last_name, email, age, version, latest):
self.id = id
self.first_name = first_name
self.middle_name = middle_name
self.last_name = last_name
self.email = email
self.age = age
self.version = version
self.latest = latest
def __repr__(self):
return "PersonModel(person_id=%s, id=%s, first_name=%s, middle_name=%s, last_name=%s, email=%s, age=%s, version=%s, latest=%s)" \
% (self.person_id, self.id, self.first_name, self.middle_name, self.last_name, self.email, self.age, self.version, self.latest)
def json(self):
return {
"first_name": self.first_name,
"last_name": self.last_name,
"middle_name": self.middle_name,
"age": self.age,
"email": self.email,
"id": self.id,
"version": self.version
}
@classmethod
def find_by_id(cls, id) -> "PersonModel":
return cls.query.filter_by(id=id, latest=True).first()
@classmethod
def find_by_id_and_version(cls, id, version) -> "PersonModel":
return cls.query.filter_by(id=id, version=version).first()
@classmethod
def is_unique(cls, first_name, middle_name, last_name) -> "PersonModel":
result = cls.query.filter_by(first_name=first_name, middle_name=middle_name, last_name=last_name).first()
return not result
@classmethod
def find_all(cls) -> List["PersonModel"]:
return cls.query.filter_by(latest=True).all()
def save_to_db(self) -> None:
db.session.add(self)
db.session.commit()
def delete_from_db(self) -> None:
db.session.delete(self)
db.session.commit()
class RecipeModel(db.Model):
"""
This recipe model class
represents the recipe model
"""
__tablename__ = 'recipes'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
description = db.Column(db.Text)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
date_modified = db.Column(db.DateTime, default=db.func.current_timestamp())
category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
created_by = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
def json(self):
"""
This method jsonifies the recipe model
"""
return {'name': self.name, 'description': self.description}
@property
def get_url(self):
return url_for(request.endpoint, _external=True)
@classmethod
def find_by_name(cls, name):
"""
This class method returns the recipe by name
"""
return cls.query.filter_by(name=name).first()
@classmethod
def find_by_category(cls, category_id):
"""
This class method returns the recipe by category id
"""
return cls.query.filter_by(category_id=category_id).first()
@classmethod
def find_by_id(cls, id):
"""
This class method returns the recipe by id
"""
return cls.query.filter_by(id=id).first()
@classmethod
def row_count(cls):
"""
This class method returns the number of rows
"""
return cls.query.count()
def save_to_db(self):
"""This method saves recipe to the database"""
db.session.add(self)
db.session.commit()
def delete_from_db(self):
"""
This method deletes recipe from the database
"""
db.session.delete(self)
db.session.commit()
class BookModel(db.Model):
"""The BookModel object stores information about the book, as well as
the listing objects that are associated with it.
Attributes:
title (string): The title of the book.
subtitle (string): The subtitle of the book.
authors (string): The author/authors of the book.
isbn (int): The isbn number for the book.
categories (string): The categorise of the book.
puhlishedDate (string): The published date of the book.
smallThumbnail (string): A string referencing the small thumbnail of the book.
thumbnail (string): A string referencing the thumbnail of the book.
previewLink (string): A link to preview the book.
infoLink (string): An info link for the book.
canonicalVolumeLink (string): A canononical volume link for the book.
listings (Listing): The current listings of the book.
"""
# Creates a table named 'book'
__tablename__ = 'books'
title = db.Column(db.String)
subtitle = db.Column(db.String)
# many to many relationship to author
authors = db.Column(db.String)
"""authors = relationship(
"AuthorModel",
secondary=association_table1,
back_populates='works')
"""
isbn = db.Column(
db.Integer,
primary_key=True) # only use 13 digit isbn, 10 digit if no 13
categories = db.Column(db.String)
publishedDate = db.Column(db.String)
smallThumbnail = db.Column(db.String)
thumbnail = db.Column(db.String)
previewLink = db.Column(db.String)
infoLink = db.Column(db.String)
canonicalVolumeLink = db.Column(db.String)
# One to many relationship: Book to listings
listings = db.relationship('ListingModel')
def __init__(self, title, subtitle, authors, isbn, categories,
publishedDate, smallThumbnail, thumbnail, previewLink,
infoLink, canonicalVolumeLink):
self.title = title
self.subtitle = subtitle
self.isbn = isbn
self.authors = authors
self.categories = categories
self.publishedDate = publishedDate
self.smallThumbnail = smallThumbnail
self.thumbnail = thumbnail
self.previewLink = previewLink
self.infoLink = infoLink
self.canonicalVolumeLink = canonicalVolumeLink
def get_listings(self):
"""Get a list of book listing jsons.
Args:
none.
Returns:
json[]: A list of jsonified listings.
"""
listing_ids = []
for listing in self.listings:
listing_ids.append(listing.listing_json_w_user())
return listing_ids
# Returns a json object representing the book
def book_json_w_listings(self):
"""Returns a jsonified book item, including a list of
jsonified listings.
Args:
none.
Returns:
json: A json item representing a book.
"""
return {
'isbn': self.isbn,
'title': self.title,
'subtitle': self.subtitle,
'authors': self.authors,
'categories': self.categories,
'publishedDate': self.publishedDate,
'smallThumbnail': self.smallThumbnail,
'thumbnail': self.thumbnail,
'previewLink': self.previewLink,
'infoLink': self.infoLink,
'canonicalVolumeLink': self.canonicalVolumeLink,
'listings': self.get_listings()
}
def book_json_wo_listings(self):
"""Returns a jsonified book item, not including listings.
Args:
none.
Returns:
json: A json item representing a book.
"""
return {
'isbn': self.isbn,
'title': self.title,
'subtitle': self.subtitle,
'authors': self.authors,
'categories': self.categories,
'publishedDate': self.publishedDate,
'smallThumbnail': self.smallThumbnail,
'thumbnail': self.thumbnail,
'previewLink': self.previewLink,
'infoLink': self.infoLink,
'canonicalVolumeLink': self.canonicalVolumeLink
}
def bare_json(self):
"""Returns a jsonified book item, including a list of
listing ids.
Args:
none.
Returns:
json: A json item representing a book.
"""
return {
'isbn': self.isbn,
'title': self.title,
'subtitle': self.subtitle,
'authors': self.authors,
'categories': self.categories,
'publishedDate': self.publishedDate,
'smallThumbnail': self.smallThumbnail,
'thumbnail': self.thumbnail,
'previewLink': self.previewLink,
'infoLink': self.infoLink,
'canonicalVolumeLink': self.canonicalVolumeLink,
"listing_ids": [l.listing_id for l in self.listings]
}
@classmethod
def find_by_isbn(cls, isbn):
"""Finds a book by isbn number.
Args:
isbn (str): The isbn number we are searching for.
Returns:
Book: The book which matches the isbn.
"""
return BookModel.query.filter_by(isbn=isbn).first()
def save_to_db(self):
"""Saves the book to the database.
Args:
none.
Returns:
json: A json item representing the book.
"""
db.session.add(self)
db.session.commit()
return self.book_json_wo_listings()
def delete_from_db(self):
"""Deletes the book from the database.
Args:
none.
Returns:
none.
"""
db.session.delete(self)
db.session.commit()
for listing in self.listings:
listing.delete_from_db()
def __repr__(self):
"""Defines how the book class will appear when printed.
Args:
none.
Returns:
none.
"""
return "<Book(isbn='%s', title='%s', author='%s')>" % (
self.isbn, self.title, self.author)
class TimeTableModel(db.Model):
__tablename__ = 'timetables'
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
college = db.Column(db.String)
branch = db.Column(db.String)
std = db.Column(db.String)
div = db.Column(db.String)
tt_name = db.Column(db.String)
json_string = db.Column(db.String)
def __init__(self, college, branch, std, div, tt_name, json_string):
self.college = college
self.branch = branch
self.std = std
self.div = div
self.tt_name = tt_name
self.json_string = json_string
@classmethod
def find_by_tt_name(cls, tt_name):
return cls.query.filter_by(tt_name=tt_name).first()
def save_tt_to_db(self):
db.session.add(self)
db.session.commit()
@staticmethod
def csv_to_dict(csv_loc):
my_dict = {}
try:
with open(csv_loc) as file:
reader = csv.reader(file)
first_row = next(reader)
for row in reader:
day_list = []
count = 1
while count < len(row):
new_period = {}
new_period.setdefault('course',
row[count].split('/')[0])
new_period.setdefault('teacher',
row[count].split('/')[1] or "")
new_period.setdefault(
'timeFromHour',
first_row[count].split('-')[0].split(':')[0])
new_period.setdefault(
'timeFromMinute',
first_row[count].split('-')[0].split(':')[1])
new_period.setdefault(
'timeToHour',
first_row[count].split('-')[1].split(':')[0])
new_period.setdefault(
'timeToMinute',
first_row[count].split('-')[1].split(':')[1])
count += 1
day_list.append(new_period)
my_dict.setdefault(row[0], day_list)
except:
return {"message": "CSV file Not Found"}
return my_dict
class DinnerModel(db.Model):
# Defining the schema for the table to store the db information.
__tablename__ = "dinners"
##################################################################################################################
### MODEL PROPERTIES #############################################################################################
##################################################################################################################
# Every dinner will have a unique id which will serve as its primary key.
id = db.Column(db.Integer, primary_key=True)
# This will store when the dinner will take place. This is currently being stored in a String for testing purposes,
# but in the future, this will be translated into a python DateTime object. # TODO: Create method to auto-convert
# UNIX to DateTime
timeStamp = db.Column(db.String)
# This will record the time when an invitation is sent out for a dinner. It is similarly encoded in a UNIX style timestamp.
# TODO: Implement automatic datetime conversion.
invitationSentTimeStamp = db.Column(db.String)
# This is a boolean to see if catering was ordered yet.
catering = db.Column(db.Boolean)
# This is a boolean to see if transportation was ordered yet.
transportation = db.Column(db.Boolean)
# This float holds the cost of the entire dinner. This can be updated later, and is defaulted at 0.
cost = db.Column(db.Float)
# The short, one line topic of the dinner
topic = db.Column(db.String)
# A longer description of the topic
description = db.Column(db.String)
# The absolute max amount of students allowed to attend the dinner. This is specified by the professor
studentLimit = db.Column(db.Integer)
# The address of the dinner. Generally the professor's home.
address = db.Column(db.String)
# The dietary restrictions of this dinner. By default, this is set to none.
dietaryRestrictions = db.Column(db.String)
# The current status of the dinner. 2 == completely filled. 1 == spots open, 0 == unclaimed
status = db.Column(db.Integer)
# TODO: Store path to a static directory to hold pictures of the dinner.
# TODO: Implement a child relationship for professors.
# Since the relationship is always one-to-one, the uselist parameter keeps the reference only as one object.
professorID = db.Column(db.String, db.ForeignKey("professors.uniqueID"))
professor = db.relationship("ProfessorModel")
userID = db.Column(db.Integer, db.ForeignKey("users.id"))
user = db.relationship("UserModel")
# TODO: Implement child relationship for applications
applications = db.relationship("ApplicationModel", lazy="dynamic")
# TODO: Implement child relationship for student reviews
studentReviews = db.relationship("StudentReviewModel", lazy="dynamic")
# TODO: Implement child relationship for professor reviews
##################################################################################################################
### /MODEL PROPERTIES ############################################################################################
##################################################################################################################
##################################################################################################################
### MODEL METHODS ################################################################################################
##################################################################################################################
# Constructing a new ProfessorModel object using passed properties for the arguments
def __init__(self,
timeStamp,
topic,
description,
studentLimit,
address,
dietaryRestrictions,
professorID,
userID=None):
# Instantiating the basic information about the dinner
self.timeStamp = timeStamp
self.topic = topic
self.description = description
self.studentLimit = studentLimit
self.address = address
self.dietaryRestrictions = dietaryRestrictions
self.professorID = professorID
if not userID:
self.userID = None
else:
self.userID = userID
# Setting defaults
self.invitationSentTimeStamp = "Not Sent"
self.catering = False
self.transportation = False
self.status = 0
# Return a json representation of the object (note that this returns a dict since Flask automatically converts into json)
def json(self):
applicationJSON = [app.json() for app in self.applications]
studentReviewJSON = [
studentReview.json() for studentReview in self.studentReviews
]
if UserModel.find_by_id(self.userID):
userString = self.user.infojson()
else:
userString = "No User Selected"
return {
"id": self.id,
"timeStamp": self.timeStamp,
"topic": self.topic,
"description": self.description,
"studentLimit": self.studentLimit,
"address": self.address,
"dietaryRestrictions": self.dietaryRestrictions,
"status": self.status,
"invitationSentTimeStamp": self.invitationSentTimeStamp,
"catering": self.catering,
"transportation": self.transportation,
"professorID": self.professorID,
"professor": self.professor.json(),
"userID": self.userID,
"user": userString,
"applications": applicationJSON,
"studentReviews": studentReviewJSON
}
# Write this particular professor model instance to the DB. Note this also will automatically perform an update as well from a PUT request.
def save_to_db(self):
db.session.add(self)
db.session.commit()
# Delete this profes from the db
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
# Find a professor via their unique ID. This will automatically return an object in place of a SQL row.
@classmethod
def find_by_id(cls, id):
found = cls.query.filter_by(id=id).first(
) # All ID numbers are unique so this should always return one object
return found
@classmethod
def return_all(cls):
allDinners = cls.query.all()
allDinnersJSON = [dinner.json() for dinner in allDinners]
return allDinnersJSON
@classmethod
def return_all_objects(cls):
allDinners = cls.query.all()
return allDinners
@classmethod
def return_by_professorID(cls, profID):
allDinners = cls.query.filter_by(professorID=profID)
return allDinners
@classmethod
def return_all_dinners_by_status(cls, status):
allDinners = cls.query.filter_by(status=status)
allDinnersJSON = [dinner.json() for dinner in allDinners]
return allDinnersJSON
@classmethod
def return_by_status_and_id(cls, status, id):
allDinners = cls.query.filter_by(userID=id).filter_by(status=status)
allDinnersJSON = [dinner.json() for dinner in allDinners]
return allDinnersJSON
@classmethod
def return_by_userID(cls, id):
allDinners = cls.query.filter_by(userID=id)
allDinnersJSON = [dinner.json() for dinner in allDinners]
return allDinnersJSON
@classmethod
def return_last_item(cls):
return db.session.query(cls).order_by(cls.id.desc()).first()
class Author_Relationships(db.Model):
__tablename__ = 'author_relationships'
# AuthorRelationship_id = db.Column(db.Integer, primary_key=True)
AuthorRelationship_id = db.Column(db.String(100), primary_key=True)
authorServer1_id = db.Column(db.Integer)
author1_id = db.Column(db.String(100))
author1_name = db.Column(db.String(60))
authorServer2_id = db.Column(db.Integer)
author2_id = db.Column(db.String(100))
author2_name = db.Column(db.String(60))
relationship_type = db.Column(
db.Integer
) # if 1, author1 is following author 2, if 2 then author2 is following author1, if 3 then both are friends
# db.PrimaryKeyConstraint(authorServer1_id, author1_id)
# db.PrimaryKeyConstraint(authorServer2_id, author2_id)
def __new__(cls, datum=None):
"""
Input: For datum, see comments in __init__
Description:
Checks whether the keys are inside datum dictionary.
If not found, then it returns None
"""
# When the DB will query and retrieve objects, __new__ will have to called to create the objects and datum wont be provided
if datum == None:
return super(Author_Relationships, cls).__new__(cls)
if ('AuthorRelationship_id' and 'authorServer1_id' and 'author1_id'
and 'authorServer2_id' and 'author2_id'
and 'relationship_type') not in datum.keys():
return None
else:
return super(Author_Relationships, cls).__new__(cls)
def __init__(self, datum=None):
"""
Input:
datum is a dictionary with keys as column names and values as their corresponding values.
eg, datum['author1_id']=3, etc
Description:
This constructor sets the values of fields based on datum dictionary. If any field
is missing from datum, its default value will be inserted.
TODO:
"""
if datum == None:
self.AuthorRelationship_id = uuid.uuid4().hex
return
self.AuthorRelationship_id = datum['AuthorRelationship_id']
self.author1_id = datum['author1_id']
self.author2_id = datum['author2_id']
self.authorServer1_id = datum['authorServer1_id']
self.authorServer2_id = datum['authorServer2_id']
self.relationship_type = datum['relationship_type']
if "author1_name" in datum.keys():
self.author1_name = datum["author1_name"]
if "author2_name" in datum.keys():
self.author2_name = datum["author2_name"]
def insert(self):
"""
Call this method for inserting an Author_Relationships row into the DB.
Before inserting, it makes sure that the authors, servers are all present in their respective DB.
"""
willInsert = True
if Authors.query.filter(
Authors.author_id == self.author1_id).all() == []:
willInsert = False
if Authors.query.filter(
Authors.author_id == self.author2_id).all() == []:
willInsert = False
if Servers.query.filter(
Servers.server_index == self.authorServer1_id).all() == []:
willInsert = False
if Servers.query.filter(
Servers.server_index == self.authorServer2_id).all() == []:
willInsert = False
if willInsert is True:
db.session.add(self)
db.session.commit()
return True #Returns true as it succesfully inserted the row.
return False #In case insertion failed
def updateRow(self):
"""
Call this method to update any changes made to any rows in the DB, such as changing the relationship type.
"""
self.insert()
@staticmethod
def deleteRowsByQuery(query_param):
"""
Read query method's description for query_param.
This method uses static method query for first retrieving a set of rows that matches the query given in query_param
and then deletes
"""
rows = Author_Relationships.query(query_param)
if rows == []:
return
for row in rows:
db.session.delete(row)
db.session.commit()
@staticmethod
def query(query_param):
"""
query param is a dictionary containing query information.
Types of queries:
1) query_param['server_author_1']=[server1_obj, author1_obj]
query_param['server_author_2']=[server2_obj, author2_obj]
2) query_param['server_author_1']=[server1_obj, author1_obj]
3) query_param['server_author_1']=[server1_obj, author1_obj]
query_param['relationship_type']=relationship_type value
4) query_param['server_author_2']=[server2_obj, author2_obj]
5) query_param['server_author_2']=[server2_obj, author2_obj]
query_param['relationship_type']=relationship_type value
6) query_param['relationship_type']=relationship_type value
7) query_param={} // This gives back all the rows
8) query_param["author_ids"]=[author1_id, author2_id] #Add test later
9) query_param["server_author_id1"]=[server1_index, author1_id, type]
10) query_param["server_author_id2"]=[server2_index, author2_id, type]
11) query_param["server_author_id1"]=[server1_index, author1_id]
query_param["server_author_id2"]=[server2_index, author2_id]
"""
if query_param == {}:
return db.session.query(Author_Relationships).all()
if "areFollowers" in query_param.keys():
author1_id, author2_id = query_param["areFollowers"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.relationship_type == 1).all()
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.relationship_type == 2).all()
return results
if "areFriends" in query_param.keys():
author1_id, author2_id = query_param["areFriends"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.relationship_type == 3).all()
return results
if ("server_author_id1"
in query_param.keys()) and ("server_author_id2"
in query_param.keys()):
server1_id, author1_id = query_param["server_author_id1"]
server2_id, author2_id = query_param["server_author_id2"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.author2_id == author2_id,
Author_Relationships.authorServer1_id == server1_id,
Author_Relationships.authorServer2_id == server2_id,
).all()
print "printing all!"
print db.session.query(Author_Relationships).all()
return results
if "server_author_id1" in query_param.keys():
server1_id, author1_id, relationship_type = query_param[
"server_author_id1"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1_id,
Author_Relationships.authorServer1_id == server1_id,
Author_Relationships.relationship_type ==
relationship_type).all()
return results
if "server_author_id2" in query_param.keys():
server2_id, author2_id, relationship_type = query_param[
"server_author_id2"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author2_id == author2_id,
Author_Relationships.authorServer2_id == server2_id,
Author_Relationships.relationship_type ==
relationship_type).all()
return results
if ("server_author_1"
in query_param.keys()) and ("server_author_2"
in query_param.keys()):
server1, author1 = query_param["server_author_1"]
server2, author2 = query_param["server_author_2"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1,
Author_Relationships.author2_id == author2,
Author_Relationships.authorServer1_id == server1,
Author_Relationships.authorServer2_id == server2).all()
return results
###### For querying with author1
if "server_author_1" in query_param.keys():
server1, author1 = query_param["server_author_1"]
if "relationship_type" in query_param.keys():
relationship_type = query_param["relationship_type"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1,
Author_Relationships.authorServer1_id == server1,
Author_Relationships.relationship_type ==
relationship_type).all()
else:
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author1_id == author1,
Author_Relationships.authorServer1_id == server1,
).all()
return results
###### For querying with author2
if "server_author_2" in query_param.keys():
server2, author2 = query_param["server_author_2"]
if "relationship_type" in query_param.keys():
relationship_type = query_param["relationship_type"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author2_id == author2,
Author_Relationships.authorServer2_id == server2,
Author_Relationships.relationship_type ==
relationship_type).all()
else:
results = db.session.query(Author_Relationships).filter(
Author_Relationships.author2_id == author2,
Author_Relationships.authorServer2_id == server2,
).all()
return results
###### For a given relationship_type get all the rows.
if "relationship_type" in query_param.keys():
relationship_type = query_param["relationship_type"]
results = db.session.query(Author_Relationships).filter(
Author_Relationships.relationship_type ==
relationship_type).all()
return results
print "returning None"
return None
class Home(db.Model):
__tablename__ = 'home'
id = db.Column(db.Integer, primary_key=True)
data = db.Column(JSON)
class Role(db.Model, RoleMixin):
"""
User Role Class
"""
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(80), unique=True)
description = db.Column(db.String(255))
def __str__(self):
return self.name
# Define relationship
roles_users = db.Table(
'roles_users', db.Column('user_id', db.Integer(),
db.ForeignKey('user.id')),
db.Column('role_id', db.Integer(), db.ForeignKey('role.id')))
class User(db.Model, UserMixin):
"""
User Class
"""
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(255))
last_name = db.Column(db.String(255))
email = db.Column(db.String(255), unique=True)
password = db.Column(db.String(255))
active = db.Column(db.Boolean())
confirmed_at = db.Column(db.DateTime())
roles = db.relationship('Role',
class Topic(db.Model):
__tablename__ = "topics"
id = db.Column(UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
unique=True)
subject = db.Column(db.String(128), nullable=True)
description = db.Column(db.Text, nullable=True)
created_by = db.Column(
UUID(as_uuid=True),
db.ForeignKey("users.id"),
)
updated_by = db.Column(
UUID(as_uuid=True),
db.ForeignKey("users.id"),
)
created_at = db.Column(ISO8601DateTime,
nullable=False,
default=datetime.datetime.now)
updated_at = db.Column(ISO8601DateTime,
nullable=False,
default=datetime.datetime.now,
onupdate=datetime.datetime.now)
deleted_at = db.Column(ISO8601DateTime, nullable=True)
messages = db.relationship("Message", lazy="dynamic")
creator = db.relationship(User, primaryjoin=(created_by == User.id))
updator = db.relationship(User, primaryjoin=(updated_by == User.id))
def __init__(self, subject, description, created_by, updated_by):
self.subject = subject
self.description = description
self.created_by = created_by
self.updated_by = updated_by
def json(self) -> Dict:
return {
"id": str(self.id),
"subject": self.subject,
"description": self.description,
"created_by": self.creator.json(),
"updated_by": self.updator.json(),
"created_at": self.created_at,
"updated_at": self.updated_at,
"deleted_at": self.deleted_at,
"messages_count": len(self.messages.all()),
"messages": self.paginated_messages
}
@property
def paginated_messages(self) -> List[Dict]:
"""Paginated messages representation."""
from app.api.rest.messages.models import Message
_ordered_msgs = self.messages.order_by(Message.created_at.desc())
_paginated_msgs = _ordered_msgs.paginate(
page=1,
per_page=current_app.config.get("COMMENTS_PER_PAGE"),
error_out=False).items
return [message.json() for message in _paginated_msgs]
@classmethod
def find(cls, **kwargs) -> "Topic":
"""Find a database entry that matches given keyword argument."""
keys = list(kwargs.keys())
if (len(keys) == 1 and keys[0] in cls.__table__.columns):
return cls.query.filter_by(**kwargs).first()
@classmethod
def find_all(cls, page: int) -> List[Dict]:
"""Find all topics in the database that are not deleted yet."""
if not page:
raise ValueError
topics = (cls.query.filter_by(deleted_at=None).order_by(
func.lower(cls.subject)).paginate(
page=page,
per_page=current_app.config.get("TOPICS_PER_PAGE"),
error_out=False))
pagination_data = (topics.has_next, topics.next_num,
[topic.json() for topic in topics.items])
return pagination_data
def insert(self) -> None:
"""Insert into the database."""
db.session.add(self)
db.session.commit()
def delete(self) -> None:
"""Mark a topic as deleted in the database."""
self.deleted_at = datetime.datetime.now()
db.session.commit()
class ItemModel(db.Model):
# specify table name and columns
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80)) # 80 character maximum to limit the size
price = db.Column(
db.Float(precision=2)) # 2 numbers after the decimal point
store_id = db.Column(
db.Integer, db.ForeignKey('stores.id')
) # add a store id to link the items and its belonging store
store = db.relationship('StoreModel') # how SQLAlchemy does join
def __init__(self, name, price, store_id):
self.name = name
self.price = price
self.store_id = store_id
# return a JSON representation of model, basically a dictionary
def json(self):
return {'name': self.name, 'price': self.price}
# move following methods from item.py in resource to here, models, since they don't belong to a resource
# keep this as class method since it will return a dictionary other than a model object
@classmethod
def find_by_name(cls, name):
### use SQLAlchemy
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "SELECT * FROM items WHERE name = ?"
# result = cursor.execute(query, (name,))
# row = result.fetchone()
# connection.close()
#
# if row:
# return cls(*row) # parse all elements in row to a item model
###
# SQLAlchemy will transit a row to ItemModel if it can, .query is using a query builder
# return ItemModel.query.filter_by(name = name).first()
return cls.query.filter_by(
name=name).first() # SELECT * FROM items WHERE name = name LIMIT 1
# return a item model object
# modify following 2 methods as not class method since they can use item model object directly
def save_to_db(self):
### use SQLAlchemy
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "INSERT INTO items VALUES (?, ?)"
# cursor.execute(query, (self.name, self.price))
#
# connection.commit()
# connection.close()
###
# save the model into database, SQLAlchemy can automatically translate model to row in a database, so we just tell it the object - self
db.session.add(self)
db.session.commit()
# it can update so we change this method to do insertion and updating, then we don't need another separate update method but we create another delete_from_db method for better use
### don't need
# def update(self):
# connection = sqlite3.connect('data.db')
# cursor = connection.cursor()
#
# query = "UPDATE items SET price = ? WHERE name = ?"
# cursor.execute(query, (self.price, self.name)) # match the values in-order
#
# connection.commit()
# connection.close()
###
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class CompanyModel(db.Model):
__tablename__ = 'tblCompany'
id = db.Column(db.Integer, primary_key=True)
companyName = db.Column(db.String(400))
roundSize = db.Column(db.Numeric(23, 2))
postMoney = db.Column(db.Numeric(23, 2))
preMoneyVal = db.Column(db.Numeric(23, 2))
postMoneyVal = db.Column(db.Numeric(23, 2))
investmentCost = db.Column(db.Numeric(23, 2))
equity = db.Column(db.Numeric(23, 2))
HQ = db.Column(db.String(80))
fundId = db.Column(db.Integer)
fund_id = db.Column(db.Integer, db.ForeignKey('tblFund.id'))
vertical_id = db.Column(db.Integer, db.ForeignKey('tblSeries.id'))
series_id = db.Column(db.Integer, db.ForeignKey('tblVertical.id'))
seriesTbl = db.relationship('SeriesModel')
verticalTbl = db.relationship('VerticalModel')
fundTbl = db.relationship('FundModel')
def __init__(self, companyName, roundSize, preMoneyVal, investmentCost,
equity, HQ):
self.companyName = companyName
self.roundSize = roundSize
self.preMoneyVal = preMoneyVal
self.postMoney = preMoneyVal + roundSize
self.investmentCost = investmentCost
self.equity = equity
self.HQ = HQ
def json(self):
return {
'id': self.id,
'companyName': self.companyName,
'roundSize': self.roundSize,
'preMoneyValue': self.preMoneyVal,
'postMoney': self.postMoney,
'investmentCost': self.investmentCost,
'equity': self.equity,
'HQ': self.HQ,
'series_id': self.series_id,
'vertical_id': self.vertical_id,
'fund_id': self.fund_id
}
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name).first()
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id).first()
@classmethod
def find_all(cls):
return cls.query.all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()