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("查询格式错误!")