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 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 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 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 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):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True, nullable=False)
password = db.Column(db.String(120), unique=True, nullable=False)
is_customer_exec = db.Column(db.Boolean, nullable=True)
is_teller = db.Column(db.Boolean, nullable=True)
is_cashier = db.Column(db.Boolean, nullable=True)
def __repr__(self):
return f'Username: {self.username}'
class ChequeAccount(db.Model):
"""
支票账户类
"""
__tablename__ = 'chequeaccount'
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)
creditLimit = db.Column(db.Float)
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 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 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 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 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 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 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 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)
class Customer(db.Model):
cust_id = db.Column(db.Integer, primary_key=True)
cust_ssn = db.Column(db.Integer, index=True, unique=True)
cust_name = db.Column(db.String(20), index=False, unique=False)
cust_age = db.Column(db.Integer, index=False, unique=False)
cust_addr1 = db.Column(db.Text, index=False, unique=False)
cust_addr2 = db.Column(db.Text, index=False, unique=False, nullable=True)
cust_city = db.Column(db.String(20), index=False, unique=False)
cust_state = db.Column(db.String(20), index=False, unique=False)
cust_last_update = db.Column(db.Date, index=False, unique=False)
cust_status = db.Column(db.String(20), index=False, unique=False)
cust_message = db.Column(db.String(20), index=False, unique=False)
account = db.relationship('Account', backref='customer')
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(60), nullable=False)
first_name = db.Column(db.String(255), nullable=False)
last_name = db.Column(db.String(255), nullable=False)
admin = db.Column(db.Boolean, unique=False, default=False)
balance = db.Column(
db.Numeric(precision=10,
scale=2,
asdecimal=True,
decimal_return_scale=2))
logs = db.relationship('TransactionLog', backref='user', lazy=True)
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 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 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 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 Staff(db.Model):
"""
员工类
"""
__tablename__ = 'staff'
id = db.Column(db.String(20), primary_key=True)
branchName = db.Column(db.String(20))
deptId = db.Column(db.Integer)
staffName = db.Column(db.String(20))
staffPhone = db.Column(db.String(20))
staffAddr = db.Column(db.String(40))
startTime = db.Column(db.Date)
__table_args__ = (db.ForeignKeyConstraint(
['branchName', 'deptId'],
['department.branchName', 'department.deptId']), )
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']),
)
