def account_add(data):
r"""
:param data: a tuple of data consists of all account columns, 储蓄account columns(except account number)
and 支票account columns(except account number). Totally 10 values（1 err code)
Specially, class must be '支票' or '储蓄'
:sqlparam (IN 账户号 varchar(20), IN 余额 decimal(15,2), IN 开户日期 DATE,IN 支行名 varchar(20), IN 账户类型 varchar(10),
IN 负责人身份证号 varchar(18), IN 利率 decimal, IN 货币类型 varchar(20), IN 透支余额 decimal, OUT err binary)
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
check_leagl(data)
# 补充 OUT 字段
data += ("", )
cur.callproc('account_add', data)
cur.execute("select @_account_add_9")
result = int(cur.fetchall()[0][0])
if result == 1:
raise Exception("无效账户类型")
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception('添加账户失败！')
def subbank_update(data):
r"""
:param data: a tuple of data (oldname , newname , city , deposit)
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.callproc('subbank_update', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception("更新支行数据失败！")
def take_loan(data):
r"""
:param data: a tuple of data consists of all 借贷 columns
:sql param(IN 贷款号 varchar(20), IN 身份证号 varchar(18))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.callproc('take_loan', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise e
def own_account(data):
r"""
:param data: a tuple of data consists of 身份证号 and 账户号
:sql param(身份证号 varchar(18),账户号 varchar(20))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.callproc('own_account', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise e
def subbank_add(data):
r"""
:param data: a tuple of data consists of all 支行 columns
:sql param(IN 支行名 varchar(20), IN 所在城市 varchar(20), IN 资产 decimal(15,2))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.callproc('subbank_add', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception("添加支行失败！")
def loan_add(data):
r"""
:param data: a tuple of data consists of all 贷款 columns
:sql param(IN 贷款号 varchar(20), IN 名字 varchar(20), IN 金额 decimal(15,2),
IN 状态 varchar(45), IN 负责人身份证号 varchar(18))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.callproc('loan_add', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception('添加贷款失败！')
def employee_add(data):
r"""
:param data: a tuple of data consists of all 员工 columns
:sql param(IN 身份证号 varchar(18), IN 姓名 varchar(20), IN 联系电话 varchar(20),
IN 家庭住址 varchar(1024), IN 开始工作日期 DATE, IN 支行名字 varchar(20))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.callproc('employee_add', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception("添加员工失败！")
def client_add(data):
r"""
:param data: a tuple of data consists of all 客户 columns
:sql param(IN 身份证号 varchar(18), IN 姓名 varchar(20), IN 联系电话 varchar(20),
IN 家庭住址 varchar(1024), IN 联系人姓名 varchar(20), IN 联系人电话varchar(20), IN 联系人邮箱 varchar(100),
IN 关系 varchar(10))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.callproc('client_add', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception('添加客户失败！')
def employee_update(data):
r"""
:param data: a tuple of data just consists of 身份证号 and all 员工 columns
:sql param(IN 身份证号 varchar(18), IN 姓名 varchar(20), IN 联系电话 varchar(20),
IN 家庭住址 varchar(1024), IN 开始工作日期 DATE, IN 支行名字 varchar(20))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
# 去除第一个身份证号数据（这里多传一个身份证数据，是为了让各个update接口数据格式一致，均为：主键+所有字段）
data = data[1:]
cur.callproc('employee_update', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception("修改员工数据失败！")
def client_update(data):
r"""
:param data: a tuple of data consists of all 客户 columns
:sql param(IN 身份证号 varchar(18), IN 姓名 varchar(20), IN 联系电话 varchar(20),
IN 家庭住址 varchar(1024), IN 联系人姓名 varchar(20), IN 联系人电话varchar(20), IN 联系人邮箱 varchar(100),
IN 关系 varchar(10))
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
# 去除第一个身份证号数据（这里多传一个身份证数据，是为了让各个update接口数据格式一致，均为：主键+所有字段）
data = data[1:]
cur.callproc('client_update', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception('更新客户信息失败!')
def fund_add(data):
r"""
:param data: a tuple of data consists of all 款项 columns and err code
:sql param(IN 款项号 varchar(10), IN 贷款号 varchar(20), IN 日期 DATE,
IN 金额 decimal(15,2), OUT err binary)
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
cur.execute(
f"select 款项号 from 款项 where 贷款号 = '{data[0]}' order by 款项号 limit 1;"
)
fund = cur.fetchall()
if len(fund) > 0:
fundID = fund[0][0]
pos = fundID.find("KX")
newIDInt = int(fundID[pos + 2:]) + 1
if pos != -1:
newFundID = data[0] + "KX" + str(newIDInt)
else:
newFundID = data[0] + "KX01"
else:
newFundID = data[0] + "KX01"
data = (newFundID, ) + data + ("", )
cur.callproc('fund_add', data)
cur.execute('select @_fund_add_4')
result = int(cur.fetchall()[0][0])
if result == 1:
raise Exception('此笔款项超过可发放的贷款总金额')
db.commit()
except Exception as e:
print(e)
db.rollback()
raise e
def account_update(data):
r"""
:param data: a tuple of data consists of all account columns, 储蓄account columns(except account number)
and 支票account columns(except account number). Totally 10 values
Specially, class must be '支票' or '储蓄'
:sqlparam (IN 身份证号 varchar(18), IN 账户号 varchar(20), IN 余额 decimal(15,2), IN 开户日期 DATE,IN 支行名 varchar(20), IN 账户类型 varchar(10),
IN 负责人身份证号 varchar(18),IN 利率 decimal, IN 货币类型 varchar(20), IN 透支余额 decimal(15,2))
PS: class cannot be changed!
"""
db = getDB()
data = transNULL(data)
try:
cur = db.cursor()
check_leagl(data)
# 去除多传的账户号，为了保持接口一致
data = data[:1] + data[2:]
cur.callproc('account_update', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception('修改账户信息失败!')