def subbank_delete(data):
r"""
:param data: a tuple of data just consists of subbank name
"""
db = getDB()
try:
cur = db.cursor()
cur.callproc('subbank_delete', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception("删除支行失败！")
def take_loan_search(data):
r"""
:param conditions: a tuple only contains 贷款号
"""
db = getDB()
try:
cur = db.cursor()
sql = f"SELECT 贷款号,身份证号 from 借贷 where 贷款号 = '{data[0]}'"
cur.execute(sql)
return cur.fetchall()
except Exception as e:
print(e)
raise Exception("查询格式错误！")
def own_search(data):
r"""
:param conditions: a tuple only contains 账户号
"""
db = getDB()
try:
cur = db.cursor()
sql = f"SELECT 账户号,身份证号,最近访问日期 from 拥有账户 where 账户号 = '{data[0]}'"
cur.execute(sql)
return cur.fetchall()
except Exception as e:
print(e)
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 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 account_delete(data):
r"""
:param data: a tuple of data consists of 账户ID
:sql param(账户号 varchar(20))
"""
db = getDB()
try:
cur = db.cursor()
cur.callproc('account_delete', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception('注销账户失败!')
def employee_delete(data):
r"""
:param data: a tuple of data jsut consists of employee ID
:sql param(IN 身份证号 varchar(18))
"""
db = getDB()
try:
cur = db.cursor()
cur.callproc('employee_delete', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception("删除员工数据失败！")
def client_delete(data):
r"""
:param data: a tuple of data just consists of client ID
:sql param(IN 身份证号 varchar(18))
"""
db = getDB()
try:
cur = db.cursor()
cur.callproc('client_delete', data)
db.commit()
except Exception as e:
print(e)
db.rollback()
raise Exception('删除客户失败！')
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 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 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 loan_delete(data):
r"""
:param data: a tuple of data consists of 贷款号 and err code ( for refusal of deleting "发放中" state loans)
"""
db = getDB()
try:
cur = db.cursor()
data = data + [""]
cur.callproc('loan_delete', data)
cur.execute("select @_loan_delete_1")
result = int(cur.fetchall()[0][0])
if result == 1:
raise Exception("无法删除发放中的贷款")
db.commit()
except Exception as e:
print(e)
db.rollback()
raise e
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('修改账户信息失败!')
def account_search(conditions):
r"""
:param conditions: a tuple of conditions. each element contains "name" and "condition" fields
"""
db = getDB()
try:
cur = db.cursor()
sqlc = "SELECT 账户.账户号, 余额, 开户日期, 支行名, 账户类型, 负责人身份证号, 利率, 货币类型, NULL as 透支余额 from 账户,储蓄账户 where 账户.账户号=储蓄账户.账户号 and "
sqlz = "SELECT 账户.账户号, 余额, 开户日期, 支行名, 账户类型, 负责人身份证号, NULL as 利率, NULL as 货币类型, 透支余额 from 账户,支票账户 where 账户.账户号=支票账户.账户号 and "
for con in conditions:
if con['name'] == '账户号':
con['name'] = '账户.账户号'
if con['name'] == '利率' or con['name'] == '货币类型':
sqlc = add_conditinos(con, sqlc)
sqlz += '账户类型 = \'储蓄\' and '
elif con['name'] == '透支余额':
sqlz = add_conditinos(con, sqlz)
sqlz += '账户类型 = \'支票\' and '
else:
sqlc = add_conditinos(con, sqlc)
sqlz = add_conditinos(con, sqlz)
sqlc = sqlc[:-4]
sqlz = sqlz[:-4]
sql = f"{sqlc} UNION {sqlz}"
#if sql[-4:] == "and ":
# 去除多余的 "and "
# sql = sql[:-4]
cur.execute(sql)
return cur.fetchall()
except Exception as e:
print(e)
raise Exception("查询格式错误！")
