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 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 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 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 AircompanyPlane(db.Model): __tablename__ = 'AircompanyPlane' id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True) air_company_name = db.Column(db.String(80), db.ForeignKey('AirCompany.name')) plane_id = db.Column(db.String(20), db.ForeignKey('Plane.id'))
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 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 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 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 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 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 Salary(db.Model): __tablename__ = 'salary' 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) date = db.Column(db.Date) comment = db.Column(db.String(128))
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 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 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 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 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 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 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 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 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)