class User(Base):
__tablename__ = 'users'
__table_args__ = dict(extend_existing=True)
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(50), index=True, nullable=False, unique=True)
name = db.Column(db.String(80), nullable=False)
class AddressModel(db.Model):
__tablename__ = "addresses"
id = db.Column(db.Integer, primary_key=True)
is_deleted = db.Column(db.Integer)
line1 = db.Column(db.String(80))
line2 = db.Column(db.String(80))
city = db.Column(db.String(80))
state = db.Column(db.String(80))
zip = db.Column(db.String(10))
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
company_id = db.Column(db.Integer, db.ForeignKey("companies.id"))
# company = db.relationship("CompanyModel")
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
def __init__(self, line1, line2, city, state, zip, company_id, user_id):
self.line1 = line1
self.line2 = line2
self.city = city
self.state = state
self.zip = zip
self.company_id = company_id
self.user_id = user_id
self.is_deleted = 0
def json(self):
return {
"id": self.id,
"line1": self.line1,
"line2": self.line2,
"city": self.city,
"state": self.state,
"zip": self.zip,
"company_id": self.company_id,
"user_id": self.user_id,
"is_deleted": self.is_deleted
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_all(cls):
return cls.query.filter_by(is_deleted=0)
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted = 1
db.session.add(self)
db.session.commit()
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
nickname = db.Column(db.String(64), nullable=False, unique=True)
email = db.Column(db.String(120), nullable=True)
password = db.Column(db.String(64))
def get_id(self):
try:
return unicode(self.id)
except NameError:
return str(self.id)
def __repr__(self):
return '<User %r>' % (self.nickname)
class Books(db.Model):
__tablename__ = 'books'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(64), unique=True, nullable=False)
category = db.Column(db.String(64))
price = db.Column(db.Float)
user_id = db.Column(db.Integer)
status = db.Column(db.Enum('0', '1'), default='1')
def to_dict(self):
return {
'name': self.name,
'category': self.category,
'price': self.price,
}
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), unique=True, nullable=False)
descr = db.Column(db.String(200))
price = db.Column(db.Float, nullable=False)
qty = db.Column(db.Integer, nullable=False)
def __init__(self, name, descr, price, qty):
self.name = name
self.descr = descr
self.price = price
self.qty = qty
def __repr__(self):
return f"Product('{self.id}', '{self.name}')"
class UserModel(db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String(10), default="ACTIVE")
is_deleted = db.Column(db.Integer, default=0)
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
role = db.Column(db.String(15), nullable=False)
username = db.Column(db.String(25), unique=True, nullable=False)
password_hash = db.Column(db.String(128), nullable=False)
email = db.Column(db.String, nullable=False, unique=True)
profile_img = db.Column(db.String, nullable=False)
comments = db.relationship("CommentModel", lazy="dynamic")
def __init__(self, role, username, password_hash, email, profile_img):
# role = (admin, guest, user)
self.role = role
# username = (admin, guest, any user name)
self.username = username
self.password_hash = password_hash
self.email = email
self.profile_img = profile_img
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_by_username(cls, username):
return cls.query.filter_by(username=username, is_deleted=0).first()
@classmethod
def find_by_email(cls, email):
return cls.query.filter_by(email=email, is_deleted=0).first()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted = 1
db.session.add(self)
db.session.commit()
class TrackingModel(db.Model):
__tablename__ = "tracking_logs"
id = db.Column(db.Integer, primary_key=True)
message = db.Column(db.String(200))
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
# need inverse relation so order retrieves all its tracking logs
order_id = db.Column(db.Integer, db.ForeignKey("orders.id"))
# staff who summited the tracking log
# no need for inverse relation
staff_id = db.Column(db.Integer, db.ForeignKey("staffs.id"))
staff = db.relationship("StaffModel")
# user comments, coresponds to an existing staff tracking log
# no need for inverse relation
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
user = db.relationship("UserModel")
is_deleted = db.Column(db.Integer)
attachments = db.relationship("AttachmentModel")
def __init__(self, message, order_id, staff_id, user_id):
self.message = message
self.order_id = order_id
self.staff_id = staff_id
self.user_id = user_id
self.is_deleted = 0
def json(self):
return {
"id": self.id,
"message": self.message,
"order_id": self.order_id,
"staff_id": self.staff_id,
"user_id": self.user_id,
"is_deleted": self.is_deleted
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_by_order_id(cls, order_id):
return cls.query.filter_by(order_id=order_id)
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted = 1
db.session.add(self)
db.session.commit()
class Person(db.Model):
__tablename__ = "person"
#__table_args__ = {'extend_existing': True}
#__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
load_id = db.Column(db.Integer,
db.ForeignKey("load.id", ondelete="CASCADE"),
nullable=False)
load = db.relationship('Load', backref=db.backref('load')) #lazy='joined'
citizen_id = db.Column(db.Integer, index=True, nullable=False)
town = db.Column(db.String(256), index=True, nullable=False)
street = db.Column(db.String(120), nullable=False)
building = db.Column(db.String(120), nullable=False)
apartment = db.Column(db.Integer, nullable=False)
name = db.Column(db.String(128), nullable=False)
birth_date = db.Column(db.Date, nullable=False)
gender = db.Column(db.String(6), nullable=False)
relatives = db.Column(ARRAY(db.Integer))
@staticmethod
def all_fields():
return [
'citizen_id', 'town', 'street', 'building', 'apartment', 'name',
'birth_date', 'gender', 'relatives'
]
# return ['id', 'load_id', 'citizen_id', 'town', 'street',
# 'building', 'apartment', 'name', 'birth_date', 'gender', 'relatives']
def __repr__(self):
return f"id={self.id},\n" \
f"load_id={self.load_id},\n" \
f"citizen_id={self.citizen_id},\n" \
f"town={self.town},\n" \
f"street={self.street},\n" \
f"building={self.building},\n" \
f"apartment={self.apartment},\n" \
f"name={self.name},\n" \
f"birth_date={self.birth_date},\n" \
f"gender={self.gender},\n" \
f"relatives={self.relatives},\n"
class RevokedTokenModel(db.Model):
__tablename__ = "revoked_tokens"
id = db.Column(db.Integer, primary_key=True)
jti = db.Column(db.String(128))
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
def __init__(self, jti):
self.jti = jti
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def is_jti_blacklisted(cls, jti):
jti = cls.query.filter_by(jti=jti).first()
return jti
class AttachmentModel(db.Model):
__tablename__ = "attachments"
id = db.Column(db.Integer, primary_key=True)
attachment_name = db.Column(db.String(200), nullable=False, unique=True)
# need inverse relation so order retrieves all its tracking logs
track_log_id = db.Column(db.Integer, db.ForeignKey("tracking_logs.id"))
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
is_deleted = db.Column(db.Integer, default=0)
def __init__(self, attachment_name, track_log_id):
self.attachment_name = attachment_name
self.track_log_id = track_log_id
def json(self):
return {
"id": self.id,
"attachment_name": self.attachment_name,
"track_log_id": self.track_log_id
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_by_track_log_id(cls, track_log_id):
return cls.query.filter_by(track_log_id=track_log_id)
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted = 1
db.session.add(self)
db.session.commit()
class UserModel(db.Model, UserMixin):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
password_hash = db.Column(db.String(256))
name = db.Column(db.String(50))
email = db.Column(db.String(50))
phone = db.Column(db.String(20))
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
orders = db.relationship("OrderModel", lazy="dynamic")
is_deleted = db.Column(db.Integer)
def __init__(self, hashed_password, name, email, phone):
self.password_hash = hashed_password
self.name = name
self.email = email
self.phone = phone
self.is_deleted = 0
def json(self):
return {
"id": self.id,
"name": self.name,
"email": self.email,
"phone": self.phone,
"orders": [order.json() for order in self.orders.all()],
"is_deleted": self.is_deleted
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name, is_deleted=0).first()
@classmethod
def find_by_email(cls, email):
return cls.query.filter_by(email=email, is_deleted=0).first()
@classmethod
def find_all(cls):
return cls.query.filter_by(is_deleted=0).order_by(cls.name)
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted = 1
db.session.add(self)
db.session.commit()
######################################################################
#### overrides UserMixin. adding "user_" identifier to distinguish
#### from staffs during @flask_login.user_loader callback. ###########
def get_id(self):
try:
return "user_" + str(self.id)
except AttributeError:
raise NotImplementedError('No `id` attribute - override `get_id`')
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
mobile = db.Column(db.String(16), unique=True, nullable=False)
nickname = db.Column(db.String(64), unique=True)
password = db.Column(db.String(128), nullable=False)
class OrderModel(db.Model):
__tablename__ = "orders"
id = db.Column(db.Integer, primary_key=True)
ur_code = db.Column(db.String(100))
# name of the ordered service
name = db.Column(db.String(80))
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
staff_id = db.Column(db.Integer, db.ForeignKey("staffs.id"))
# filled in when user registers new account and add this order in account
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
staff = db.relationship("StaffModel",
back_populates="orders",
uselist=False)
user = db.relationship("UserModel")
company = db.relationship(
CompanyModel,
secondary=
"join(StaffModel, CompanyModel, StaffModel.company_id==CompanyModel.id)",
primaryjoin=staff_id == StaffModel.id,
uselist=False,
backref="orders")
is_deleted = db.Column(db.Integer)
tracking_logs = db.relationship("TrackingModel", lazy="dynamic")
def __init__(self, ur_code, name, staff_id):
self.ur_code = ur_code
self.name = name
self.staff_id = staff_id
# user_id = 1 is unregistered user, updates when user registers account
self.user_id = 1
self.is_deleted = 0
def json(self):
return {
"id": self.id,
"ur_code": self.ur_code,
"name": self.name,
"staff_id": self.staff_id,
"user_id": self.user_id,
"tracking_logs": [log.json() for log in self.tracking_logs.all()],
"is_deleted": self.is_deleted
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_by_ur_code(cls, ur_code):
return cls.query.filter_by(ur_code=ur_code, is_deleted=0).first()
@classmethod
def find_by_staff_id(cls, staff_id):
return db.session.query(OrderModel).filter_by(staff_id=staff_id,
is_deleted=0).order_by(
cls.date)
@classmethod
def find_by_user_id(cls, user_id):
return cls.query.filter_by(user_id=user_id,
is_deleted=0).order_by(cls.date)
@classmethod
def find_by_company(cls, company):
return cls.query.filter(cls.company.has(id=company.id)).order_by(
cls.date)
@classmethod
def find_by_company_id(cls, company_id):
return cls.query.filter(cls.company.has(id=company_id)).order_by(
cls.date)
@classmethod
def find_all(cls):
return cls.query.filter_by(is_deleted=0).order_by(cls.date)
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted = 1
db.session.add(self)
db.session.commit()
class StaffModel(db.Model, UserMixin):
__tablename__ = "staffs"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
role = db.Column(db.String(15))
password_hash = db.Column (db.String(256))
company_id = db.Column(db.Integer, db.ForeignKey("companies.id"))
company = db.relationship("CompanyModel", back_populates="staffs", uselist=False)
orders = db.relationship("OrderModel", back_populates="staff")
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
is_deleted = db.Column(db.Integer)
def __init__(self, name, role,password_hash,company_id):
self.name =name
self.role = role
self.password_hash = password_hash
self.company_id=company_id
self.is_deleted = 0
def json(self):
if not self.company:
ret_msg = {
"id": self.id,
"name": self.name,
"role": self.role,
"is_deleted":self.is_deleted
}
else:
ret_msg = {
"id": self.id,
"name": self.name,
"role": self.role,
"company": self.company.json(),
"is_deleted":self.is_deleted
}
return ret_msg,200
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name, is_deleted=0).first()
@classmethod
def find_all(cls):
return cls.query.filter_by(is_deleted=0).order_by(cls.company_id)
@classmethod
def find_by_company_id(cls, company_id):
return cls.query.filter_by(company_id=company_id, is_deleted=0).order_by(cls.name)
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted=1
db.session.add(self)
db.session.commit()
############################
#### overrides UserMixin. adding "staff_" identifier to distinguish
#### from users during @flask_login.user_loader callback. ###########
def get_id(self):
try:
return "staff_"+str(self.id)
except AttributeError:
raise NotImplementedError('No `id` attribute - override `get_id`')
class CompanyModel(db.Model):
__tablename__ = "companies"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
email = db.Column(db.String(80))
phone = db.Column(db.String(20))
is_deleted = db.Column(db.Integer)
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
staffs = db.relationship("StaffModel", back_populates="company")
addresses = db.relationship("AddressModel", lazy="dynamic")
def __init__(self, name, email, phone):
self.name = name
self.email = email
self.phone = phone
self.is_deleted = 0
def json(self):
return {
"id": self.id,
"name": self.name,
"email": self.email,
"phone": self.phone,
"is_deleted": self.is_deleted,
"addresses": [address.json() for address in self.addresses.all()]
}
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id, is_deleted=0).first()
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name, is_deleted=0).first()
@classmethod
def find_by_email(cls, email):
return cls.query.filter_by(email=email, is_deleted=0).first()
@classmethod
def find_all(cls):
return cls.query.filter_by(is_deleted=0).order_by(CompanyModel.name)
def get_active_addresses(self):
addresses = self.addresses.all()
active_addresses = filter(lambda address: address.is_deleted == 0,
addresses)
return active_addresses
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
self.is_deleted = 1
db.session.add(self)
db.session.commit()
