class Seat(db.Model):
__tablename__ = 'Seat'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
seat_code = db.Column(db.Integer)
seat_type = db.Column(db.String(20))
plane_id = db.Column(db.String(20), db.ForeignKey('Plane.id'))
class AircompanyAirline(db.Model):
__tablename__ = 'AircompanyAirline'
id = db.Column(db.Integer,
primary_key=True,
nullable=False,
autoincrement=True)
air_company_name = db.Column(db.String(80),
db.ForeignKey('AirCompany.name'))
air_line_id = db.Column(db.Integer, db.ForeignKey('AirLine.id'))
class PR(db.Model):
__tablename__ = 'pr'
id = db.Column(db.Integer, primary_key=True, unique=True)
pid = db.Column(db.Integer,
db.ForeignKey('parking_spot_information.id'),
nullable=False)
rid = db.Column(db.Integer,
db.ForeignKey('resident_information.id'),
nullable=False)
db.UniqueConstraint(pid, rid, name='prid_constrain')
class Plane(db.Model):
__tablename__ = 'Plane'
id = db.Column(db.String(20), primary_key=True) # 飞机型号
num_seat = db.Column(db.Integer, nullable=False)
num_first_class_seat = db.Column(db.Integer, nullable=False)
num_business_class_seat = db.Column(db.Integer, nullable=False)
num_economy_class_seat = db.Column(db.Integer, nullable=False)
db.CheckConstraint('num_first_class + num_tourist_class == num_seat',
name='check1')
class AirLine(db.Model):
__tablename__ = 'AirLine'
id = db.Column(db.Integer,
primary_key=True,
nullable=False,
autoincrement=True)
start = db.Column(db.String(80), nullable=False)
destination = db.Column(db.String(80), nullable=False)
airline_date = db.Column(db.Date, nullable=False)
mileage = db.Column(db.FLOAT)
db.CheckConstraint('mileage > 0', name='check3')
class WI(JsonifyModel):
__tablename__ = 'wi'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
wid = db.Column(db.Integer,
db.ForeignKey('warehouse.id', ondelete="CASCADE"),
nullable=False)
iid = db.Column(db.Integer,
db.ForeignKey('inventory.id', ondelete="CASCADE"),
nullable=False)
number = db.Column(db.Integer,
db.CheckConstraint('number > 0'),
nullable=False)
wi_unique = db.UniqueConstraint(wid, iid)
class Submission(db.Model):
__tablename__ = 'submission'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
tid = db.Column(db.Integer, db.ForeignKey('teacher.id'))
check_fine = db.Column(db.Float)
basic_salary = db.Column(db.Float)
bonus = db.Column(db.Float)
tax = db.Column(db.Float)
comment = db.Column(db.String(128))
date = db.Column(db.Date)
approve = db.Column(db.Integer, default=0)
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(32), index=True)
password_hash = db.Column(db.String(64))
# 加密密码
def hash_password(self, password):
self.password_hash = pwd_context.encrypt(password)
# 验证密码
def verify_password(self, password):
return pwd_context.verify(password, self.password_hash)
# 生成token，并设置过期时间
def generate_auth_token(self, expiration=expire_time):
s = Serializer(app.config['SECRET_KEY'], expires_in=expiration)
return s.dumps({'id': self.id})
# 验证token的方法
# 返回 1: token过期或失效 2: token对应用户与global不同 0: 找到用户
def verify_token(self, token):
s = Serializer(app.config['SECRET_KEY'])
try:
data = s.loads(token)
except SignatureExpired:
return 1 # token过期
except BadSignature:
return 1 # token无效
if self.id == data['id']:
return 0
else:
return 2
# 静态的验证token的方法, 返回对象
@staticmethod
def verify_auth_token(token):
s = Serializer(app.config['SECRET_KEY'])
try:
data = s.loads(token)
except SignatureExpired:
return None # token过期
except BadSignature:
return None # token无效
user = User.query.get(data['id'])
return user
class Management(JsonifyModel):
__tablename__ = 'management'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
username = db.Column(db.String(20), nullable=False, unique=True)
password = db.Column(db.String(32), nullable=False)
@classmethod
def try_login(cls, username: str,
password: str) -> Union[Tuple[int, str], bool]:
m = md5()
m.update(password.encode())
pass_with_hash = m.hexdigest()
query_res: Management = cls.query.filter_by(
username=username, password=pass_with_hash).first()
if query_res is None:
return False
else:
return query_res.id, query_res.username
class Customer(db.Model):
__tablename__ = 'Customer'
username = db.Column(db.String(32), primary_key=True)
name = db.Column(db.String(32), nullable=False)
password = db.Column(db.String(128), nullable=False)
phone_number = db.Column(db.String(20), nullable=False)
age = db.Column(db.Integer)
gender = db.Column(db.String(20))
ID_number = db.Column(db.String(80), unique=True)
type = db.Column(db.String(20), default="NO-VIP")
class MaintenanceInformation(db.Model):
__tablename__ = 'maintenance_information'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
hid = db.Column(db.Integer, db.ForeignKey('house_information.id'))
apply_date = db.Column(db.Date)
fix_date = db.Column(db.Date, nullable=True)
fix_number = db.Column(db.String(32), nullable=True)
pay_amount = db.Column(db.Float, nullable=True)
comment = db.Column(db.String(128))
from_balance = db.Column(db.Integer, default=0)
db.CheckConstraint('fix_date >= apply_date', name='check_date')
db.CheckConstraint('pay_amount > 0', name='check_pay')
class Inventory(JsonifyModel):
__tablename__ = 'inventory'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(128), nullable=False, index=True, unique=True)
comment = db.Column(db.String(128))
@classmethod
def change_inventory_num(cls, inventory_id: int, warehouse_id: int,
number: int):
wi_query: Optional[WI] = WI.query.filter_by(iid=inventory_id,
wid=warehouse_id).first()
if wi_query:
if wi_query.number + number < 0:
raise Exception
if wi_query.number + number == 0:
db.session.delete(wi_query)
else:
wi_query.number += number
else:
if number < 0:
raise Exception
wi_new = WI(iid=inventory_id, wid=warehouse_id, number=number)
db.session.add(wi_new)
db.session.commit()
@classmethod
def get_page(cls, page: int,
per_page: int) -> List[Tuple['Inventory', int]]:
query_in_wi = db.session.query(
WI.iid,
func.sum(WI.number).label('total_number')).group_by(
WI.iid).subquery()
left_join_inventory = db.session.query(
Inventory, query_in_wi.c.total_number).join(query_in_wi,
isouter=True)
res: List[Tuple['Inventory',
Optional[int]]] = left_join_inventory.paginate(
page, per_page=per_page, error_out=False).items
return [(inv, 0 if num is None else num) for inv, num in res]
@classmethod
def len(cls):
return cls.query.count()
class Warehouse(JsonifyModel):
__tablename__ = 'warehouse'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20), nullable=False, unique=True)
lng = db.Column(db.Float, nullable=False)
lat = db.Column(db.Float, nullable=False)
location = db.Column(db.String(120))
specific_location = db.Column(db.String(120))
@classmethod
def get_page(cls, page: int, per_page: int) -> List['Warehouse']:
return cls.query.paginate(page, per_page=per_page,
error_out=False).items
@classmethod
def len(cls):
return cls.query.count()
@classmethod
def get_inventoried_warehouse(cls,
iid: int) -> List[Tuple['Warehouse', int]]:
wi_query = db.session.query(
WI.wid, WI.number.label('number')).filter_by(iid=iid).subquery()
warehouse_with_num = db.session.query(cls, wi_query.c.number).join(
wi_query, isouter=True).all()
return [(d[0], 0 if not d[1] else d[1]) for d in warehouse_with_num]
class ResidentInformation(db.Model):
__tablename__ = 'resident_information'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(32), unique=True)
password = db.Column(db.String(128), nullable=False)
phone_number = db.Column(db.String(20))
name = db.Column(db.String(10))
job = db.Column(db.String(64))
class PropertyFeeInformation(db.Model):
__tablename__ = "property_fee_information"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
hid = db.Column(db.Integer,
db.ForeignKey('house_information.id'),
nullable=False)
date = db.Column(db.Date, nullable=False)
pay_date = db.Column(db.Date, nullable=True)
pay_amount = db.Column(db.Float, nullable=False)
ispay = db.Column(db.Integer, nullable=False)
handle_rid = db.Column(db.Integer,
db.ForeignKey('resident_information.id'),
nullable=True)
order_number = db.Column(db.String(32), nullable=True)
db.CheckConstraint('pay_date >= date', name='check_pay_date')
db.CheckConstraint('pay_amount > 0', name='check_pay_amount_positive')
class ParkingSpotPayInformation(db.Model):
__tablename__ = "parking_spot_pay_information"
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
pid = db.Column(db.Integer,
db.ForeignKey('parking_spot_information.id'),
nullable=False)
date = db.Column(db.Date, nullable=False)
pay_date = db.Column(db.Date, nullable=True)
pay_amount = db.Column(db.Float, nullable=False)
handle_rid = db.Column(db.Integer,
db.ForeignKey('resident_information.id'),
nullable=True)
ispay = db.Column(db.Integer, nullable=False)
db.CheckConstraint('pay_date >= date', name='check_pay_date')
class Ticket(db.Model):
__tablename__ = 'Ticket'
id = db.Column(db.Integer,
primary_key=True,
nullable=False,
autoincrement=True)
ticket_book_date = db.Column(db.Date, nullable=False)
ticket_pay_price = db.Column(db.FLOAT)
ticket_class = db.Column(db.String(20), nullable=False)
flight_id = db.Column(db.String(20), db.ForeignKey('Flight.id'))
customer_username = db.Column(db.String(32),
db.ForeignKey("Customer.username"))
seat_id = db.Column(db.Integer, db.ForeignKey("Seat.id"))
class HouseInformation(db.Model):
__tablename__ = 'house_information'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
building_number = db.Column(db.String(5))
room_number = db.Column(db.String(5))
area = db.Column(db.Float)
family_size = db.Column(db.Integer)
maintenance_balance = db.Column(db.Float)
db.UniqueConstraint(building_number,
room_number,
name='check_position_unique')
db.CheckConstraint('area > 0', name='check_area_positive')
db.CheckConstraint('family_size >= 0', name='check_family_size_positive')
db.CheckConstraint('maintenance_balance >= 0',
name='check_maintenance_balance_positive')
class Deliveryman(JsonifyModel):
__tablename__ = 'deliveryman'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(64), nullable=False)
username = db.Column(db.String(64), nullable=False, unique=True)
password = db.Column(db.String(128), nullable=False)
lng = db.Column(db.Float, nullable=True)
lat = db.Column(db.Float, nullable=True)
@classmethod
def try_login(cls, username: str, password: str) -> Union[Tuple[int, str], bool]:
m = md5()
m.update(password.encode())
pass_with_hash = m.hexdigest()
query_res: Deliveryman = cls.query.filter_by(
username=username,
password=pass_with_hash
).first()
if query_res is None:
return False
else:
return query_res.id, query_res.name
@classmethod
def get_page(cls, page: int, per_page: int) -> List['Deliveryman']:
return cls.query.paginate(page, per_page=per_page, error_out=False).items
@classmethod
def len(cls):
return cls.query.count()
@classmethod
def update_position(cls, did: int, lng: float, lat: float) -> bool:
query_res: Deliveryman = cls.query.filter_by(id=did).first()
if not query_res:
return False
query_res.lat = lat
query_res.lng = lng
db.session.commit()
return True
class Teacher(db.Model):
__tablename__ = 'teacher'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(10), nullable=False)
username = db.Column(db.String(10), nullable=False, unique=True)
password = db.Column(db.String(128), nullable=False)
age = db.Column(db.Integer)
gender = db.Column(db.Integer)
education = db.Column(db.String(20))
title = db.Column(db.String(10))
marry = db.Column(db.Integer)
did = db.Column(db.Integer, db.ForeignKey(Department.id))
hiredate = db.Column(db.DateTime)
status = db.Column(db.Integer)
class Department(db.Model):
__tablename__ = 'department'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20))
class Airport(db.Model):
__tablename__ = 'Airport'
id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
start_name = db.Column(db.String(50), nullable=False)
arrive_name = db.Column(db.String(50), nullable=False)
class VIPCustomer(db.Model):
__tablename__ = 'VIPCustomer'
username = db.Column(db.String(32), primary_key=True)
VIP_class = db.Column(db.String(20), default="VIP1")
score = db.Column(db.Integer, default=0)
class Flight(db.Model):
__tablename__ = 'Flight'
id = db.Column(db.String(20), primary_key=True)
start_time = db.Column(db.Time, nullable=False)
arrive_time = db.Column(db.Time, nullable=False)
first_class_price = db.Column(db.FLOAT, nullable=False)
business_class_price = db.Column(db.FLOAT, nullable=False)
economy_class_price = db.Column(db.FLOAT, nullable=False)
num_remain_seat = db.Column(db.Integer)
num_remain_first_class = db.Column(db.Integer)
num_remain_business_class = db.Column(db.Integer)
num_remain_economy_class = db.Column(db.Integer)
air_line_id = db.Column(db.Integer, db.ForeignKey('AirLine.id'))
airport_id = db.Column(db.Integer, db.ForeignKey("Airport.id"))
plane_id = db.Column(db.String(20), db.ForeignKey('Plane.id'))
air_company_name = db.Column(db.String(80),
db.ForeignKey('AirCompany.name'))
db.CheckConstraint('num_remain_seat > 0', name='check4')
db.CheckConstraint('num_remain_first_class > 0', name='check5')
db.CheckConstraint('num_remain_business_class > 0', name='check6')
db.CheckConstraint('num_remain_economy_class > 0', name='check7')
class Check(db.Model):
__tablename__ = 'check'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
tid = db.Column(db.Integer, db.ForeignKey('teacher.id'))
date = db.Column(db.Date)
check = db.Column(db.Integer)
class AirCompany(db.Model):
__tablename__ = 'AirCompany'
name = db.Column(db.String(80), primary_key=True)
password = db.Column(db.String(128), nullable=False)
call_number = db.Column(db.Integer)
class PropertyManagementInformation(db.Model):
__tablename__ = 'property_management_information'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(32), nullable=False, unique=True)
password = db.Column(db.String(128), nullable=False)
class HROffice(db.Model):
__tablename__ = 'hr_office'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(20), nullable=False)
password = db.Column(db.String(128), nullable=False)
class RH(db.Model):
__tablename__ = 'rh'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
rid = db.Column(db.Integer, db.ForeignKey('resident_information.id'))
hid = db.Column(db.Integer, db.ForeignKey('house_information.id'))
