class User(db.Model):
username = db.Column(db.String(64), primary_key=True)
password = db.Column(db.String(64), nullable=False)
def check_password(self, password):
"""Check hashed password."""
return check_password_hash(self.password, password)
class Admin(db.Model):
"""
操作银行管理系统的人员
"""
__tablename__ = 'admin'
adminName = db.Column(db.String(20), primary_key=True)
adminPasswd = db.Column(db.String(20))
class Loan(db.Model):
id = db.Column(db.Integer, primary_key=True)
money = db.Column(db.Float, nullable=False)
status = db.Column(db.String(64), nullable=False)
bank_ref = db.Column(db.String(64),
db.ForeignKey('bank.name'),
nullable=False)
bank = db.relationship('Bank', backref='loans')
class Branch(db.Model):
"""
支行类
"""
__tablename__ = 'branch'
branchName = db.Column(db.String(20), primary_key=True)
branchCity = db.Column(db.String(20), nullable=True)
branchAsset = db.Column(db.Integer)
class LoanClientAssociation(db.Model):
loan_ref = db.Column(db.String(64),
db.ForeignKey('loan.id'),
primary_key=True)
loan = db.relationship('Loan', backref='loan_client_associations')
client_ref = db.Column(db.String(64),
db.ForeignKey('client.id_number'),
primary_key=True)
client = db.relationship('Client', backref='loan_client_associations')
class User(db.Model, UserMixin):
"""
The database model for user accounts
id: auto-assigned, database assigns an id to each user
username: The users username
email: the users email
first_name: The users first name
last_name: The users surname
account_number: auto-assigned, account number given to all users (send and receive money)
checking: balance in the user's checking account
savings: balance in the user's savings account
"""
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(20), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
first_name = db.Column(db.String(), unique=False, nullable=False)
last_name = db.Column(db.String(), unique=False, nullable=False)
account_number = db.Column(db.Integer(), nullable=False)
password = db.Column(db.String(60), nullable=False)
checking = db.Column(db.Float(), default=0.00)
savings = db.Column(db.Float(), default=0.00)
def __repr__(self):
"""
What is returned when a user instance is printed
:return:
"""
return f"User('{self.id}', {self.username}, {self.account_number})"
class ServiceRelationship(db.Model):
"""
业务关系，描述客户与银行的联系
"""
__tablename__ = 'servicerelationship'
branchName = db.Column(db.String(20),
db.ForeignKey('branch.branchName'),
primary_key=True)
clientId = db.Column(db.String(20),
db.ForeignKey('client.id'),
primary_key=True)
class OpenChequeAccount(db.Model):
"""
开设支票账户
"""
__tablename__ = 'openchequeaccount'
accountId = db.Column(db.String(20),
db.ForeignKey('chequeaccount.id'),
primary_key=True)
clientId = db.Column(db.String(20),
db.ForeignKey('client.id'),
primary_key=True)
class OpenDepositAccount(db.Model):
"""
开设储蓄账户
"""
__tablename__ = 'opendepositaccount'
accountId = db.Column(db.String(20),
db.ForeignKey('depositaccount.id'),
primary_key=True)
clientId = db.Column(db.String(20),
db.ForeignKey('client.id'),
primary_key=True)
class Account(db.Model):
id = db.Column(db.String(64), primary_key=True)
balance = db.Column(db.Float, nullable=False)
open_date = db.Column(db.Integer, nullable=False)
account_type = db.Column(db.String(64), nullable=False)
bank_ref = db.Column(db.String(64),
db.ForeignKey('bank.name'),
nullable=False)
bank = db.relationship('Bank', backref='accounts')
__mapper_args__ = {'polymorphic_on': account_type}
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
date_posted = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
content = db.Column(db.Text, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
def __repr__(self):
return f"Post('{self.title}', '{self.date_posted}')"
class Donate(db.Model):
id = db.Column(db.Integer, primary_key=True)
name_blood = db.Column(db.String(10), nullable=False)
quantity = db.Column(db.Integer(), nullable=False)
date_posted = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
def __repr__(self):
return f"Donate('{self.name_blood}', '{self.quantity}', '{self.date_posted}')"
class Login(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
uname = db.Column(db.String(20), index=False, unique=True)
password = db.Column(db.String(128), index=False, unique=False)
role = db.Column(db.String(20), index=False, unique=False)
last_login = db.Column(db.DateTime, index=False, unique=False)
def set_password(self, password):
self.password= generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password, password)
class Serve(db.Model):
"""
服务。员工服务客户的关系。
"""
__tablename__ = 'serve'
clientId = db.Column(db.String(20),
db.ForeignKey('client.id'),
primary_key=True)
staffId = db.Column(db.String(20),
db.ForeignKey('staff.id'),
primary_key=True)
ServiceType = db.Column(db.String(20))
class OwnLoan(db.Model):
"""
拥有。客户拥有贷款的关系
"""
__tablename__ = 'ownloan'
branchName = db.Column(db.String(20), primary_key=True)
loanId = db.Column(db.String(6), primary_key=True)
clientId = db.Column(db.String(20),
db.ForeignKey('client.id'),
primary_key=True)
__table_args__ = (db.ForeignKeyConstraint(
['branchName', 'loanId'], ['loan.branchName', 'loan.loanId']), )
class Loan(db.Model):
"""
贷款类
"""
__tablename__ = 'loan'
branchName = db.Column(db.String(20), db.ForeignKey('branch.branchName'))
loanId = db.Column(db.String(6))
loanAmount = db.Column(db.Float)
payNum = db.Column(db.Integer)
__table_args__ = (db.PrimaryKeyConstraint('branchName',
'loanId',
name='PK_Loan'), )
class Token(db.Model):
token = db.Column(db.String(64),
default=lambda: str(uuid.uuid4()),
primary_key=True)
expiresAt = db.Column(db.Integer,
nullable=False,
default=lambda: int(datetime.utcnow().timestamp()) +
current_app.config['TOKEN_EXPIRE'])
user_ref = db.Column(db.String(64),
db.ForeignKey('user.username'),
nullable=False)
user = db.relationship('User', backref='tokens')
class Customer(db.Model):
customer_id = db.Column(db.Integer, primary_key=True, nullable=False)
SSH_id = db.Column(db.Integer, nullable=False)
name = db.Column(db.String(50), nullable=False)
age = db.Column(db.Integer, nullable=False)
address = db.Column(db.String(120), nullable=False)
state = db.Column(db.String(50), nullable=False)
city = db.Column(db.String(50), nullable=False)
active = db.Column(db.Boolean, nullable=False)
message = db.Column(db.String(200), nullable=False)
last_updated = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now)
class Department(db.Model):
"""
部门类
"""
__tablename__ = 'department'
branchName = db.Column(db.String(20), db.ForeignKey('branch.branchName'))
deptId = db.Column(db.Integer)
deptName = db.Column(db.String(20))
deptType = db.Column(db.String(20), nullable=True)
deptManagerID = db.Column(db.String(20), unique=True, nullable=True)
__table_args__ = (db.PrimaryKeyConstraint('branchName',
'deptId',
name='PK_Dept'), )
class OpenAccount(db.Model):
"""
开户
用户每开设一个支票账户或储蓄账户的同时也需要在此处创建一条记录。
其用处为一个客户在一个支行只能开设一个储蓄账户和一个支票账户。
该类的目的不在于查询，只在于实施该限制
"""
__tablename__ = 'openaccount'
branchName = db.Column(db.String(20),
db.ForeignKey('branch.branchName'),
primary_key=True)
id = db.Column(db.String(20), db.ForeignKey('client.id'), primary_key=True)
depositAccountId = db.Column(db.String(20))
chequeAccountId = db.Column(db.String(20))
class CustomerAccount(db.Model):
account_id = db.Column(db.Integer, primary_key=True, nullable=False)
customer_id = db.Column(db.Integer,
db.ForeignKey('customer.customer_id'),
nullable=False)
customer = db.relationship("Customer",
backref=db.backref("customer", uselist=False))
account_type = db.Column(db.String(30), nullable=False)
deposit_amount = db.Column(db.Integer, nullable=False)
last_updated = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now)
def __repr__(self):
return f'Account: {self.account_id} Customer_id: {self.customer_id}'
class Client(db.Model):
id_number = db.Column(db.String(64), primary_key=True)
name = db.Column(db.String(64), nullable=False)
phone_number = db.Column(db.String(64), nullable=False)
address = db.Column(db.String(64), nullable=False)
contact_name = db.Column(db.String(64), nullable=False)
contact_phone_number = db.Column(db.String(64), nullable=False)
contact_email = db.Column(db.String(64), nullable=False)
contact_relationship = db.Column(db.String(64), nullable=False)
staff_ref = db.Column(db.String(64),
db.ForeignKey('staff.id_number'),
nullable=False)
staff = db.relationship('Staff', backref='clients')
class Grant(db.Model):
"""
单次付款类
"""
__tablename__ = 'grant'
branchName = db.Column(db.String(20))
loanId = db.Column(db.String(6))
grantCount = db.Column(db.Integer)
grantTime = db.Column(db.Date)
grantMoney = db.Column(db.Float)
__table_args__ = (
db.PrimaryKeyConstraint('branchName',
'loanId',
'grantCount',
name='PK_Grant'),
db.ForeignKeyConstraint(['branchName', 'loanId'],
['loan.branchName', 'loan.loanId']),
)
class Account(db.Model):
acnt_id = db.Column(db.Integer, primary_key=True)
cust_id = db.Column(db.Integer, db.ForeignKey('customer.cust_id'))
acnt_type = db.Column(db.String(20), index=False, unique=False)
acnt_balance = db.Column(db.Integer, index=False, unique=False)
acnt_status = db.Column(db.String(20), index=False, unique=False)
acnt_last_tr_date = db.Column(db.DateTime, index=False, unique=False)
acnt_message = db.Column(db.String(20), index=False, unique=False)
transaction = db.relationship('Transaction', backref='customer')
class Client(db.Model):
"""
客户类
"""
__tablename__ = 'client'
id = db.Column(db.String(20), primary_key=True)
clientName = db.Column(db.String(20))
clientPhone = db.Column(db.String(20))
clientAddr = db.Column(db.String(40))
contactName = db.Column(db.String(20))
contactPhone = db.Column(db.String(20))
contactEmail = db.Column(db.String(40))
contactRelation = db.Column(db.String(10))
class Transaction(db.Model):
tr_id = db.Column(db.Integer, primary_key=True)
tr_amount = db.Column(db.Integer, index=False, unique=False)
tr_type = db.Column(db.String(20), index=False, unique=False)
tr_date = db.Column(db.DateTime, index=False, unique=False)
tr_src= db.Column(db.Integer, db.ForeignKey('account.acnt_id'))
tr_trgt= db.Column(db.Integer, index=False, unique=False)
class Transaction(db.Model):
"""
The database model for a transaction between users
id: auto-assigned, id for each transaction (internal)
sender: the sender of the money (initiator of transaction)
recipient: the user who receives the transaction (money)
amount: the amount sent through the transaction
date: a timestamp when the transaction was sent
"""
id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
sender = db.Column(db.Integer(), nullable=False)
recipient = db.Column(db.Integer(), nullable=False)
amount = db.Column(db.Float(), nullable=False)
date = db.Column(db.DateTime(), default=db.func.current_timestamp())
def __repr__(self):
"""
What is returned when a transaction instance is printed
:return:
"""
return f"Transaction('{self.sender}', {self.recipient}, {self.amount}, {self.date})"
class DepositAccount(db.Model):
"""
储蓄账户类
"""
__tablename__ = 'depositaccount'
id = db.Column(db.String(20), primary_key=True)
branchName = db.Column(db.String(20), db.ForeignKey('branch.branchName'))
accountBalance = db.Column(db.Float)
openTime = db.Column(db.DateTime)
visitTime = db.Column(db.DateTime)
interestRate = db.Column(db.Float)
currencyType = db.Column(db.String(20))
class Staff(db.Model):
id_number = db.Column(db.String(64), primary_key=True)
name = db.Column(db.String(64), nullable=False)
phone_number = db.Column(db.String(64), nullable=False)
address = db.Column(db.String(64), nullable=False)
joined_date = db.Column(db.Integer, nullable=False)
kind = db.Column(db.String(64), nullable=False)
department_ref = db.Column(db.Integer,
db.ForeignKey('department.id'),
nullable=False)
department = db.relationship('Department', backref='staffs')
class TransactionLog(db.Model):
id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
amount = db.Column(
db.Numeric(precision=10,
scale=2,
asdecimal=True,
decimal_return_scale=2))
from_account = db.Column(db.String(120), nullable=False)
to_account = db.Column(db.String(120), nullable=False)
positive = db.Column(db.Boolean, unique=False, default=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
