def delete_system(business_name):
# 根据系统名称删除所有测试数据
if business_name == '' or business_name == ' ' or business_name is None or len(
business_name) == 0:
business_ids = mydb.select(
conn=DB_CONN,
table='Tbl_Business_System',
column='id',
condition='name = "{}"'.format(business_name))
else:
business_ids = mydb.select(
conn=DB_CONN,
table='Tbl_Business_System',
column='id',
condition='position("{}" in name)'.format(business_name))
for business_id in business_ids:
mydb.delete(conn=DB_CONN,
table='Tbl_Business_System_Permission',
condition='business_system_id = {}'.format(business_id[0]))
mydb.delete(conn=DB_CONN,
table='Tbl_Business_System_Config',
condition='business_system_id = {}'.format(business_id[0]))
mydb.delete(conn=DB_CONN,
table='Tbl_Business_System',
condition='id = {}'.format(business_id[0]))
def get_rap_corporations(rap_db, ci_db, project=None):
"""
从rap数据库获取 corporation_id
:param rap_db: rap数据库连接
:param ci_db: ci数据库连接
:param project: 项目名称
:return: [{'dict_id': dict_id, 'corporation_id': id}, {'dict_id': dict_id, 'corporation_id': id}]
"""
rap_corporations_ids = list()
corporations = MysqlClent.select(rap_db, 'tb_corporation', 'id,name')
LOG.debug('从rap数据库获取 corporation信息：{}'.format(corporations))
condition = None
if project:
condition = 'code="{}"'.format(project)
raps = MysqlClent.select(ci_db, 't_dict', 'id,code,rap', condition)
LOG.debug('从auto_ci数据库获取项目名称信息：{}'.format(raps))
for rap_info in raps:
dict_id, code, rap = rap_info
if rap:
corporation = list(filter(lambda x: x[1] == rap, corporations))
if len(corporation) == 1:
rap_corporations_ids.append({'dict_id': dict_id, 'corporation_id': corporation[0][0]})
else:
LOG.warning('请确认项目{0}在auto_ci中rap信息{1}是否正确'.format(code, rap))
get_rap_corporations_none_rap(rap_db, ci_db, code)
else:
get_rap_corporations_none_rap(rap_db, ci_db, code)
LOG.debug('dict_id和corporation_id对应关系：{}'.format(rap_corporations_ids))
return rap_corporations_ids
def delete_tanant_adm(business_name, tenant_name, adm_name):
# 清除机构管理员测试数据
business_ids = mydb.select(
conn=DB_CONN,
table='Tbl_Business_System',
column='id',
condition='position("{}" in name)'.format(business_name))
for business_id in business_ids:
tenant_ids = mydb.select(
conn=DB_CONN,
table='Tbl_Tenant',
column='id',
condition='business_system_id = {} and position("{}" in name)'.
format(business_id[0], tenant_name))
for tenant_id in tenant_ids:
mydb.delete(
conn=DB_CONN,
table='Tbl_Tenant_Administrator',
condition=
'business_system_id = {} and tenant_id = {} and position("{}" in name)'
.format(business_id[0], tenant_id[0], adm_name))
mydb.delete(conn=DB_CONN,
table='Tbl_Tenant',
condition='id = "{}"'.format(tenant_id[0]))
mydb.delete(conn=DB_CONN,
table='Tbl_Business_System',
condition='position("{}" in name)'.format(business_name))
def get_rap_corporations_none_rap(rap_db, ci_db, project=None):
"""
从rap数据库获取 corporation_id
:param rap_db: rap数据库连接
:param ci_db: ci数据库连接
:param project: 项目名称
:return: [{'dict_id': dict_id, 'corporation_id': id}, {'dict_id': dict_id, 'corporation_id': id}]
"""
rap_corporations_ids = list()
condition = None if project is None else 'name regexp "{}$"'.format(project)
corporations = MysqlClent.select(rap_db, 'tb_corporation', 'id,name', condition)
LOG.debug('从rap数据库获取 corporation信息：{}'.format(corporations))
for corporation_id, corporation_name in corporations:
project_name = re.split('[-—]', corporation_name)[-1]
LOG.debug('从rap数据库获取项目名称信息：{}'.format(project_name))
rap = MysqlClent.select(ci_db, 't_dict', 'id,rap', 'code="{}"'.format(project_name))
LOG.debug('从auto_ci数据库获取项目名称信息：{}'.format(rap))
if rap:
id_dict = {'dict_id': rap[0][0], 'corporation_id': corporation_id}
LOG.debug('rap数据库和auto_ci数据库获取项目名称有对应关系：{}'.format(id_dict))
rap_corporations_ids.append(id_dict)
if rap[0][1] != corporation_name:
LOG.debug('rap数据库和auto_ci数据库项目{1}名称不同，更新项目名称：{0}'.format(corporation_name, project_name))
MysqlClent.update(ci_db, 't_dict', 'rap="{}"'.format(corporation_name),
'code="{}"'.format(project_name))
else:
LOG.warning('项目{0}在数据库中不存在，请确认是否需要在auto_ci中增加项目{1}'.format(corporation_name, project_name))
LOG.debug('dict_id和corporation_id对应关系：{}'.format(rap_corporations_ids))
return rap_corporations_ids
def delete_databases(tanant_name):
# 根据机构名删除Tbl_PlatformUser表中对应数据、删除Tbl_CompanyDataSource表中对应数据，
# 并删除数据库，删除Tbl_Business表中数据
tenantUuids = mydb.select(
conn=DB_CONN,
table='Tbl_Business',
column='tenantUuid',
condition='position("{}" in name)'.format(tanant_name))
for tenantUuid in tenantUuids:
# 查询dbname
dbname = mydb.select(conn=DB_CONN,
table='Tbl_CompanyDataSource',
column='db_name',
condition='tenantUuid = "{}"'.format(
tenantUuid[0]))
# 根据dbname删除db
mydb.drop_database(conn=DB_CONN,
database_name="{}".format(dbname[0][0]))
# 删除Tbl_CompanyDataSource表中删除db的记录数据
mydb.delete(conn=DB_CONN,
table='Tbl_CompanyDataSource',
condition='tenantUuid = "{}"'.format(tenantUuid[0]))
# 删除Tbl_PlatformUser表中该机构的用户
mydb.delete(conn=DB_CONN,
table='Tbl_PlatformUser',
condition='tenantUuid = "{}" '.format(tenantUuid[0]))
# 删除Tbl_Business表中name为tanant_name的数据
mydb.delete(conn=DB_CONN,
table='Tbl_Business',
condition='position("{}" in name)'.format(tanant_name))
def get_info(table_name, column, *condition):
string_list = list()
if len(condition) == 0:
info = mydb.select(la.DB, table_name, column)
else:
info = mydb.select(la.DB, table_name, column, ' AND '.join(condition))
if len(info) > 0:
string_list = [i if i is not None else '' for i in info[0]]
return string_list
def get_sysdata():
sysdatas = list()
parent_ids = mydb.select(la.DB, 'Tbl_SysDataEnumConfig', 'id',
'datum_type = "datum_type_incoming" AND item_code != "incoming_tbxx"')
parents = [i[0] for i in parent_ids]
for parent_id in parents:
sysdata = mydb.select(la.DB, 'Tbl_SysDataEnumConfig', 'id', 'parent_id = {}'.format(parent_id))
sysdatas.extend([i[0] for i in sysdata])
sysdatas.extend(parents)
return [{"id": i} for i in sysdatas]
def get_info(table_name, column, *condition):
string_list = list()
if len(condition) == 0:
info = mydb.select(la.DB, table_name, column)
else:
info = mydb.select(la.DB, table_name, column, ','.join(condition))
if len(info) == 0:
string_list.append('')
else:
for _str in info[0]:
if _str is None:
_str = ''
string_list.append(_str)
return string_list
def test_30_delete_test_user(self):
# test-API
user_uuids = MysqlClent.select(loginAction.DB, 'Tbl_PlatformUserProfile', 'platform_user_profile_uuid',
'name like "%test-API%"')
MysqlClent.delete(loginAction.DB, 'Tbl_PlatformUserProfile', 'name like "%test-API%"')
for user_uuid in user_uuids:
MysqlClent.delete(loginAction.DB, 'Tbl_PlatformUserPrivilegeRelation',
'platform_user_uuid="{}"'.format(user_uuid))
def get_collection_uuid(platform_user_profile_uuid):
"""
根据系统用户uuid，查询催收人员uuid
"""
return mydb.select(
la.DB, 'Tbl_PlatformUserProfileCollection', 'collection_uuid',
"platform_user_profile_uuid='{}'".format(
platform_user_profile_uuid))[0][0]
def delete_user(user_name):
# 根据用户名删除用户
if user_name == '' or user_name == ' ' or user_name is None or len(
user_name.strip()) == 0:
user_ids = mydb.select(conn=DB_CONN,
table='Tbl_User',
column='id',
condition='"{}" = realname'.format(user_name))
else:
user_ids = mydb.select(
conn=DB_CONN,
table='Tbl_User',
column='id',
condition='position("{}" in realname)'.format(user_name))
for user_id in user_ids:
mydb.delete(conn=DB_CONN,
table='Tbl_User',
condition='id = "{}"'.format(user_id[0]))
def select_list(*condition):
"""
查询nodes_hierarchy表id字段，转换成数组
:param condition: 查询条件
:return: 返回数组查询结果
"""
db = get_testlink_db()
results = mydb.select(db, 'nodes_hierarchy', 'id', ' AND '.join(condition))
return [_result[0] for _result in results]
def delete_user(user_name):
# 根据用户名删除用户
user_ids = mydb.select(
conn=DB_CONN,
table='Tbl_User',
column='id',
condition='position("{}" in realname)'.format(user_name))
for user_id in user_ids:
mydb.delete(conn=DB_CONN,
table='Tbl_User',
condition='id = "{}"'.format(user_id[0]))
def delete_role(role_name):
if role_name == '' or role_name == ' ' or role_name is None or len(
role_name) == 0:
role_ids = mydb.select(conn=DB_CONN,
table='Tbl_Role',
column='id',
condition='name = {} '.format(role_name))
else:
role_ids = mydb.select(
conn=DB_CONN,
table='Tbl_Role',
column='id',
condition='position("{}" in name)'.format(role_name))
for role_id in role_ids:
mydb.delete(conn=DB_CONN,
table='Tbl_Role_Permission',
condition='role_id = {}'.format(role_id[0]))
mydb.delete(conn=DB_CONN,
table='Tbl_Role',
condition='id = {}'.format(role_id[0]))
def delete_role(role_name):
# 根据角色名删除角色
role_ids = mydb.select(
conn=DB_CONN,
table='Tbl_Role',
column='id',
condition='position("{}" in name)'.format(role_name))
for role_id in role_ids:
mydb.delete(conn=DB_CONN,
table='Tbl_Role_Permission',
condition='role_id = {}'.format(role_id[0]))
mydb.delete(conn=DB_CONN,
table='Tbl_Role',
condition='id = {}'.format(role_id[0]))
def get_project(project_name=None):
"""
获取项目名称
:param project_name:项目名称
:return:返回项目名称数组
"""
pro = list()
if project_name is None:
projects = MysqlClent.select(DB_CONN, 't_dict', 'code')
pro = [project[0] for project in projects]
else:
if MysqlClent.select_rows(DB_CONN, 't_dict', 'code',
'code={}'.format(project_name)):
pro.append(project_name)
else:
raise MyError(
'项目{}在auto_ci中不存在，需要使用请在表t_dict中新增！'.format(project_name))
LOGGER.debug('The project is {}'.format(pro))
return pro
def get_rap_groups(rap_db, ci_db, rap_info):
"""
获取rap项目groups
:param rap_db: rap数据库连接
:param ci_db: ci数据库连接
:param rap_info: {'dict_id': dict_id, 'corporation_id': id}
:return: [dict_id, {'module1': [40, 42, 55, 80], 'module2': [...]}]
"""
rap_group_result, groups = list(), dict()
dict_id, corporation_id = rap_info['dict_id'], rap_info['corporation_id']
rap_group_result.append(dict_id)
production_lines = MysqlClent.select(rap_db, 'tb_production_line', 'id,name',
'corporation_id="{}"'.format(corporation_id))
LOG.debug('产品线信息{}'.format(production_lines))
for production_line_id, production_line_name in production_lines:
module_code = re.split('[-—]', production_line_name)[-1]
if not FileUtils.is_valid(module_code):
LOG.warning('模块名称{}无效，请确认是否需要修改'.format(module_code))
continue
LOG.debug('英文模块名{}'.format(module_code))
group_infos = MysqlClent.select_join(rap_db, 'tb_project as p,tb_group as g', 'p.id,p.name', 'g.id=p.group_id',
'g.production_line_id={} GROUP BY p.id'.format(production_line_id))
LOG.debug('rap_group信息{}'.format(group_infos))
rap_groups = list()
for group_id, group_name in group_infos:
rap_groups.append(group_id)
rap_group_name = MysqlClent.select_col(ci_db, 't_rap_group', 'rap_group_name',
'dict_id={0} AND module="{1}"'.format(dict_id, module_code))
LOG.debug('auto_ci库t_rap_group表对应关系查询结构{}'.format(rap_group_name))
if not [gn for gn in rap_group_name if gn == group_name]:
insert_value = {"dict_id": dict_id, "module": module_code, "rap_group_name": group_name}
LOG.debug('auto_ci库t_rap_group表没有对应关系，新增数据：{}'.format(insert_value))
MysqlClent.insert(ci_db, 't_rap_group', insert_value)
LOG.debug('rap库tb_group表对应id{}'.format(rap_groups))
if rap_groups:
groups[module_code] = rap_groups
rap_group_result.append(groups)
LOG.debug('项目、模块、rap_group对应关系：{}'.format(rap_group_result))
return rap_group_result
def update_rap_api(ci_db, rap, version_id, select_col, value_dict, condition):
"""
更新rap数据表
:param ci_db: ci数据库连接信息
:param rap: rap数据
:param version_id: 版本信息
:param select_col: 查询字段列表
:param value_dict: 更新值字典
:param condition:查询条件
:return:
"""
LOG.debug('rap数据{}'.format(rap))
LOG.debug('版本信息数据{}'.format(version_id))
LOG.debug('查询字段列表{}'.format(select_col))
LOG.debug('更新值字典{}'.format(value_dict))
LOG.debug('查询条件{}'.format(condition))
rap.insert(1, version_id)
LOG.debug('rap数据{}'.format(rap))
ci_rap = MysqlClent.select(ci_db, 't_rap', ','.join(select_col), condition)[0]
LOG.debug('ci_rap数据{}'.format(ci_rap))
if ci_rap != rap:
LOG.debug('更新接口，数据如下：【{}】'.format(value_dict))
MysqlClent.update_dict(ci_db, 't_rap', value_dict, condition)
def get_label_uuid(contact_uuid):
contact_uuid = mydb.select(la.DB, 'Tbl_ContractCustomerLabel', 'contract_customer_label_uuid',
"contract_uuid='{}' ORDER BY id DESC".format(contact_uuid))
return contact_uuid[0][0]
def update_contract_machine_pass(contract_uuid, user_uuid):
"""
修改订单状态，绕过机审和签订合同
:param contract_uuid: 订单UUID
:param user_uuid: 用户UUID
:return:
"""
la.LOGGER.info('contract_uuid : {0} user_uuid : {1}'.format(contract_uuid, user_uuid))
contract_state = {"qifa_machine_audit": "风控规则预置通过", "submit_state": "提交成功",
"contract_state": "生效"}
update_contract_state(contract_uuid, contract_state)
get_uuid = lambda x: ''.join(str(x).split('-'))
contract_info_col = ['contract_info_uuid', 'contract_uuid', 'fdd_number', 'contract_name', 'type', 'platform',
'sign_state', 'view_url', 'download_url', 'state']
contract_name = ["分期还款服务合同", "委托付款函", "通知函"]
condtion = ['contract_uuid="{}"'.format(contract_uuid)]
for _name in contract_name:
_edtion = 'contract_name="{}"'.format(_name)
condtion.append(_edtion)
contract_info_rows = mydb.select_rows(la.DB, 'Tbl_ContractInfo', 'id', ' AND '.join(condtion))
if contract_info_rows == 0:
contract_info = mydb.select(la.DB, 'Tbl_ContractInfo', ','.join(contract_info_col), condtion[1])[0]
la.LOGGER.info('查询Tbl_ContractInfo中"{0}"的值{1}'.format(_name, contract_info))
contract_info[0:2] = get_uuid(uuid.uuid1()), contract_uuid
la.LOGGER.info('插入Tbl_ContractInfo中"{0}"的值{1}'.format(_name, contract_info))
mydb.insert(la.DB, 'Tbl_ContractInfo', dict(zip(contract_info_col, contract_info)))
condtion.remove(_edtion)
translog_rows = mydb.select_rows(la.DB, 'Tbl_TransLog', 'id', "user_id='{}'".format(user_uuid))
if translog_rows == 0:
translog_col = ['out_id', 'trans_log_uuid', 'trans_seq_id', 'user_id', 'cust_id', 'trans_type',
'trans_amt', 'fee_amt', 'trans_state', 'resp_code', 'err_code', 'chk_state',
'bank_state', 'oper_seq_id', 'trans_name', 'trans_obj', 'card_acct_type', 'card_id',
'card_opening_bank', 'card_acct_name', 'card_line_num', 'bank_remark',
'bank_seq_id', 'acct_seq_id', 'ret_fee', 'bank_proc_desc',
'gate_id', 'pay_channel_id', 'cash_type', 'state', 'business_data']
translog_value = mydb.select(la.DB, 'Tbl_TransLog', ','.join(translog_col))[0]
translog_dict = dict(zip(translog_col, translog_value))
la.LOGGER.info('查询Tbl_TransLog的值{}'.format(translog_value))
translog_dict['trans_log_uuid'], translog_dict['user_id'] = get_uuid(uuid.uuid1()), user_uuid
translog_dict['trans_amt'], translog_dict['fee_amt'], translog_dict['bank_state'] = 1000, 0.0, 'bank_state_S'
translog_dict['trans_state'], translog_dict['chk_state'], = 'trans_state_S', 'chk_state_S'
la.LOGGER.info('插入Tbl_TransLog的值{}'.format(translog_dict))
# 交易流水表 新增数据
mydb.insert(la.DB, 'Tbl_TransLog', translog_dict)
qifamachinelog_rows = mydb.select_rows(la.DB, 'Tbl_QifaMachineLog', 'id', condtion[0])
if qifamachinelog_rows == 0:
qifamachinelog_col = ['qifa_machine_log_uuid', 'contract_uuid', 'qifa_machine_audit', 'score',
'risk_warning', 'state']
qifamachinelog_value = [get_uuid(uuid.uuid1()), contract_uuid, 'qifa_merchant_audit_pass', '67',
'中风险，建议核查', 'enabled']
la.LOGGER.info('插入Tbl_QifaMachineLog的值{}'.format(qifamachinelog_value))
# 系统机审表 新增数据
mydb.insert(la.DB, 'Tbl_QifaMachineLog', dict(zip(qifamachinelog_col, qifamachinelog_value)))
condtion.append('user_uuid="{}"'.format(user_uuid))
user_ocr_info_rows = mydb.select_rows(la.DB, 'Tbl_UserOcrInfo', 'id', ' AND '.join(condtion))
if user_ocr_info_rows == 0:
user_ocr_info_col = ['user_ocr_uuid', 'contract_uuid', 'user_uuid', 'first_key', 'second_key', 'third_key',
'qiniu_config_id', 'authentication_state', 'state', 'note']
user_ocr_info_value = mydb.select(la.DB, 'Tbl_UserOcrInfo', ','.join(user_ocr_info_col), 'note IS NOT NULL')[0]
la.LOGGER.info('查询Tbl_UserOcrInfo的值{}'.format(user_ocr_info_value))
user_ocr_info_value[0:3] = get_uuid(uuid.uuid1()), contract_uuid, user_uuid
user_ocr_info_value[-1] = str(user_ocr_info_value[-1]).replace('\"', '\\\"')
la.LOGGER.info('插入bl_UserOcrInfo的值{}'.format(user_ocr_info_value))
# 客户OCR信息表 新增人脸识别数据
mydb.insert(la.DB, 'Tbl_UserOcrInfo', dict(zip(user_ocr_info_col, user_ocr_info_value)))
user_personal_info_rows = mydb.select_rows(la.DB, 'Tbl_UserPersonalInfo', 'id', ' AND '.join(condtion))
if user_personal_info_rows == 0:
user_personal_info_col = ['user_person_uuid', 'contract_uuid', 'user_uuid', 'datum_type_marry_id',
'datum_type_education_id', 'datum_type_housing_id', 'live_province',
'live_province_name', 'live_city', 'live_city_name', 'live_region',
'live_region_name', 'live_detail', 'state']
user_personal_info_value = mydb.select(la.DB, 'Tbl_UserPersonalInfo', ','.join(user_personal_info_col))[0]
la.LOGGER.info('查询Tbl_UserPersonalInfo的值{}'.format(user_personal_info_value))
user_personal_info_value[0:3] = get_uuid(uuid.uuid1()), contract_uuid, user_uuid
la.LOGGER.info('插入Tbl_UserPersonalInfo的值{}'.format(user_personal_info_value))
# 客户个人信息表 新增和订单关联的数据
mydb.insert(la.DB, 'Tbl_UserPersonalInfo', dict(zip(user_personal_info_col, user_personal_info_value)))
user_work_info_rows = mydb.select_rows(la.DB, 'Tbl_UserWorkInfo', 'id', ' AND '.join(condtion))
if user_work_info_rows == 0:
user_work_info_col = ['user_work_uuid', 'contract_uuid', 'user_uuid', 'company_name', 'position',
'work_phone', 'datum_type_income_id', 'datum_type_worktime_id', 'company_province',
'company_province_name', 'company_city', 'company_city_name', 'company_region',
'company_region_name', 'company_detail', 'state']
user_work_info_value = mydb.select(la.DB, 'Tbl_UserWorkInfo', ','.join(user_work_info_col))[0]
la.LOGGER.info('查询Tbl_UserWorkInfo的值{}'.format(user_work_info_value))
user_work_info_value[0:3] = get_uuid(uuid.uuid1()), contract_uuid, user_uuid
la.LOGGER.info('插入Tbl_UserWorkInfo的值{}'.format(user_work_info_value))
# 客户工作信息表 新增和订单关联的数据
mydb.insert(la.DB, 'Tbl_UserWorkInfo', dict(zip(user_work_info_col, user_work_info_value)))
user_contact_info_rows = mydb.select_rows(la.DB, 'Tbl_UserContactInfo', 'id', ' AND '.join(condtion))
user_contact_info_col = ['user_contact_uuid', 'contract_uuid', 'user_uuid', 'name', 'phone',
'datum_type_contact_id', 'state']
user_contact_info_value = mydb.select(la.DB, 'Tbl_UserContactInfo', ','.join(user_contact_info_col))[0]
la.LOGGER.info('查询Tbl_UserContactInfo的值{}'.format(user_contact_info_value))
user_contact_info_value[0:3] = get_uuid(uuid.uuid1()), contract_uuid, user_uuid
if user_contact_info_rows == 0:
# 客户联系人信息表 新增和订单关联的数据
la.LOGGER.info('插入Tbl_UserContactInfo的值{}'.format(user_contact_info_value))
mydb.insert(la.DB, 'Tbl_UserContactInfo', dict(zip(user_contact_info_col, user_contact_info_value)))
elif user_contact_info_rows > 1:
mydb.delete(la.DB, 'Tbl_UserContactInfo',
"contract_uuid='{0}' AND user_uuid='{1}'".format(contract_uuid, user_uuid))
la.LOGGER.info('插入Tbl_UserContactInfo的值{}'.format(user_contact_info_value))
mydb.insert(la.DB, 'Tbl_UserContactInfo', dict(zip(user_contact_info_col, user_contact_info_value)))
def get_gps(install_status=0):
# 根据安装状态查GPS 设备安装状态 0 未安装，1 已安装，2 已拆机
return MysqlClent.select(DB_CONN, table='fk_gps', column='id, code',
condition='install_status = "{}" and is_active = "Y" GROUP BY id DESC LIMIT 1'
.format(install_status))
def get_permission_ids_to_str():
# 返回列表并转换成字符串
ids = mydb.select(conn=DB_CONN, table='Tbl_Permission', column='id')
return ",".join([str(per_id[0]) for per_id in ids])
def get_permission_ids():
# 返回列表
ids = mydb.select(conn=DB_CONN, column='id', table='Tbl_Role_Permission')
return ids
def update_contract_machine_pass(contract_uuid, user_uuid):
"""
修改订单状态，绕过机审和签订合同
:param contract_uuid: 订单UUID
:param user_uuid: 用户UUID
:return:
"""
la.LOGGER.info('contract_uuid : {0} user_uuid : {1}'.format(
contract_uuid, user_uuid))
contract_state = {
"qifa_machine_audit": "风控规则预置通过",
"submit_state": "提交成功",
"contract_state": "生效"
}
update_contract_state(contract_uuid, contract_state)
value_dict = {"loan_periods": 3}
mydb.update_dict(la.DB, 'Tbl_Contract', value_dict,
'contract_uuid="{}"'.format(contract_uuid))
get_uuid = lambda x: ''.join(str(x).split('-'))
# contract_info_col = ['contract_info_uuid', 'contract_uuid', 'fdd_number', 'contract_name', 'type', 'platform',
# 'sign_state', 'view_url', 'download_url', 'state']
# contract_name = ["分期还款服务合同", "委托付款函", "通知函"]
contract_name = {
'服务完结确认书_QA.pdf': "A",
'应收账款转让协议书_QA.pdf': "B",
'债权转让通知书_QA.pdf': "C",
'居间服务协议_QA.pdf': "D",
'医疗美容项目服务合同_QA.pdf': "E"
}
condtion = ['contract_uuid="{}"'.format(contract_uuid)]
for _name in contract_name.keys():
_edtion = 'contract_name="{}"'.format(_name)
condtion.append(_edtion)
contract_info_rows = mydb.select_rows(la.DB, 'Tbl_ContractInfo', 'id',
' AND '.join(condtion))
if contract_info_rows == 0:
insert_dict = {
'contract_info_uuid': str(uuid.uuid1()).split('-'),
'contract_uuid': contract_uuid,
'fdd_number': "2020011618014346820480_" + contract_name[_name],
'contract_name': _name,
'type': '1',
'platform': '1',
'sign_state': '0',
'view_url': '',
'download_url': '',
'state': 'enabled'
}
# contract_info = mydb.select(la.DB, 'Tbl_ContractInfo', ','.join(contract_info_col), condtion[1])[0]
# la.LOGGER.info('查询Tbl_ContractInfo中"{0}"的值{1}'.format(_name, contract_info))
# contract_info[0:2] = get_uuid(uuid.uuid1()), contract_uuid
# la.LOGGER.info('插入Tbl_ContractInfo中"{0}"的值{1}'.format(_name, contract_info))
# mydb.insert(la.DB, 'Tbl_ContractInfo', dict(zip(contract_info_col, contract_info)))
mydb.insert(la.DB, 'Tbl_ContractInfo', insert_dict)
condtion.remove(_edtion)
translog_rows = mydb.select_rows(la.DB, 'Tbl_TransLog', 'id',
"user_id='{}'".format(user_uuid))
if not translog_rows:
translog_dict = {
'out_id': '2019112910110111420480',
'trans_log_uuid': get_uuid(uuid.uuid1()),
'trans_seq_id': '2019112911111294628672',
'user_id': user_uuid,
'cust_id': '',
'trans_type': 'trans_type_P001',
'trans_amt': 1000,
'fee_amt': 0.0,
'trans_state': 'trans_state_S',
'resp_code': '',
'err_code': '',
'chk_state': 'chk_state_S',
'bank_state': 'bank_state_S',
'oper_seq_id': '',
'trans_name': 'testAPI',
'trans_obj': 'testAPI',
'card_acct_type': 'card_acct_type_private',
'card_id': '213105529995110',
'card_opening_bank': '招商银行',
'card_acct_name': 'testAPI',
'card_line_num': '34646546',
'bank_remark': 'testAPI',
'bank_seq_id': '',
'acct_seq_id': '',
'ret_fee': 'ret_fee_no',
'bank_proc_desc': '',
'gate_id': '',
'pay_channel_id': '',
'cash_type': 'cash_type_T0',
'state': 'enabled',
'business_data': ''
}
la.LOGGER.info('插入Tbl_TransLog的值{}'.format(translog_dict))
# 交易流水表 新增数据
mydb.insert(la.DB, 'Tbl_TransLog', translog_dict)
qifamachinelog_rows = mydb.select_rows(la.DB, 'Tbl_QifaMachineLog', 'id',
condtion[0])
if qifamachinelog_rows == 0:
qifamachinelog_col = [
'qifa_machine_log_uuid', 'contract_uuid', 'qifa_machine_audit',
'score', 'risk_warning', 'state'
]
qifamachinelog_value = [
get_uuid(uuid.uuid1()), contract_uuid, 'qifa_merchant_audit_pass',
'67', '中风险，建议核查', 'enabled'
]
la.LOGGER.info('插入Tbl_QifaMachineLog的值{}'.format(qifamachinelog_value))
# 系统机审表 新增数据
mydb.insert(la.DB, 'Tbl_QifaMachineLog',
dict(zip(qifamachinelog_col, qifamachinelog_value)))
condtion.append('user_uuid="{}"'.format(user_uuid))
user_ocr_info_rows = mydb.select_rows(la.DB, 'Tbl_UserOcrInfo', 'id',
' AND '.join(condtion))
if user_ocr_info_rows == 0:
user_ocr_info_col = [
'user_ocr_uuid', 'contract_uuid', 'user_uuid', 'first_key',
'second_key', 'third_key', 'qiniu_config_id',
'authentication_state', 'state', 'note'
]
user_ocr_info_value = mydb.select(la.DB, 'Tbl_UserOcrInfo',
','.join(user_ocr_info_col),
'note IS NOT NULL')[0]
la.LOGGER.info('查询Tbl_UserOcrInfo的值{}'.format(user_ocr_info_value))
user_ocr_info_value[0:3] = get_uuid(
uuid.uuid1()), contract_uuid, user_uuid
user_ocr_info_value[-1] = str(user_ocr_info_value[-1]).replace(
'\"', '\\\"')
la.LOGGER.info('插入bl_UserOcrInfo的值{}'.format(user_ocr_info_value))
# 客户OCR信息表 新增人脸识别数据
mydb.insert(la.DB, 'Tbl_UserOcrInfo',
dict(zip(user_ocr_info_col, user_ocr_info_value)))
user_personal_info_rows = mydb.select_rows(la.DB, 'Tbl_UserPersonalInfo',
'id', ' AND '.join(condtion))
if user_personal_info_rows == 0:
user_personal_info_col = [
'user_person_uuid', 'contract_uuid', 'user_uuid',
'datum_type_marry_id', 'datum_type_education_id',
'datum_type_housing_id', 'live_province', 'live_province_name',
'live_city', 'live_city_name', 'live_region', 'live_region_name',
'live_detail', 'state'
]
user_personal_info_value = mydb.select(
la.DB, 'Tbl_UserPersonalInfo', ','.join(user_personal_info_col))[0]
la.LOGGER.info(
'查询Tbl_UserPersonalInfo的值{}'.format(user_personal_info_value))
user_personal_info_value[0:3] = get_uuid(
uuid.uuid1()), contract_uuid, user_uuid
la.LOGGER.info(
'插入Tbl_UserPersonalInfo的值{}'.format(user_personal_info_value))
# 客户个人信息表 新增和订单关联的数据
mydb.insert(
la.DB, 'Tbl_UserPersonalInfo',
dict(zip(user_personal_info_col, user_personal_info_value)))
user_work_info_rows = mydb.select_rows(la.DB, 'Tbl_UserWorkInfo', 'id',
' AND '.join(condtion))
if user_work_info_rows == 0:
user_work_info_col = [
'user_work_uuid', 'contract_uuid', 'user_uuid', 'company_name',
'position', 'work_phone', 'datum_type_income_id',
'datum_type_worktime_id', 'company_province',
'company_province_name', 'company_city', 'company_city_name',
'company_region', 'company_region_name', 'company_detail', 'state'
]
user_work_info_value = mydb.select(la.DB, 'Tbl_UserWorkInfo',
','.join(user_work_info_col))[0]
la.LOGGER.info('查询Tbl_UserWorkInfo的值{}'.format(user_work_info_value))
user_work_info_value[0:3] = get_uuid(
uuid.uuid1()), contract_uuid, user_uuid
la.LOGGER.info('插入Tbl_UserWorkInfo的值{}'.format(user_work_info_value))
# 客户工作信息表 新增和订单关联的数据
mydb.insert(la.DB, 'Tbl_UserWorkInfo',
dict(zip(user_work_info_col, user_work_info_value)))
user_contact_info_rows = mydb.select_rows(la.DB, 'Tbl_UserContactInfo',
'id', ' AND '.join(condtion))
user_contact_info_col = [
'user_contact_uuid', 'contract_uuid', 'user_uuid', 'name', 'phone',
'datum_type_contact_id', 'state'
]
user_contact_info_value = mydb.select(la.DB, 'Tbl_UserContactInfo',
','.join(user_contact_info_col))[0]
la.LOGGER.info('查询Tbl_UserContactInfo的值{}'.format(user_contact_info_value))
user_contact_info_value[0:3] = get_uuid(
uuid.uuid1()), contract_uuid, user_uuid
if user_contact_info_rows == 0:
# 客户联系人信息表 新增和订单关联的数据
la.LOGGER.info(
'插入Tbl_UserContactInfo的值{}'.format(user_contact_info_value))
mydb.insert(la.DB, 'Tbl_UserContactInfo',
dict(zip(user_contact_info_col, user_contact_info_value)))
elif user_contact_info_rows > 1:
mydb.delete(
la.DB, 'Tbl_UserContactInfo',
"contract_uuid='{0}' AND user_uuid='{1}'".format(
contract_uuid, user_uuid))
la.LOGGER.info(
'插入Tbl_UserContactInfo的值{}'.format(user_contact_info_value))
mydb.insert(la.DB, 'Tbl_UserContactInfo',
dict(zip(user_contact_info_col, user_contact_info_value)))
def test_01api_78dk_platform_tm_first_viewFirstCheckContract(self):
# 初审信息查询
# {'uid': '合同uuid'}
# contract_uuid = '1e9fe3d0be2344e2acfda1a025a607e2'
sql = 'contract_uuid="' + contract_uuid + '" and state ="enabled"'
channelid = MysqlClent.select(loginAction.DB,
'Tbl_Contract',
condition=sql)
# print(type(channelid[0]),channelid[0])
# 查询商户名称
sql = 'id="' + str(channelid[0][4]) + '" and state ="enabled"'
merchantname = MysqlClent.select_one(loginAction.DB,
'Tbl_MerchantProfile', 'name',
sql)
# 查询资金包
sql = 'id="' + str(channelid[0][6]) + '" and state ="enabled"'
fundname = MysqlClent.select_one(loginAction.DB, 'Tbl_FundPackage',
'name', sql)
print(merchantname, fundname, channelid[0][22])
# fundSideName 资方名称
sql = 'select Tbl_FundSide.name from Tbl_FundSide ,Tbl_FundPackage,Tbl_FundSidePackageRelation WHERE ' \
'Tbl_FundPackage.id={} and Tbl_FundPackage.id=Tbl_FundSidePackageRelation.fund_package_id and ' \
'Tbl_FundSide.id=Tbl_FundSidePackageRelation.fund_side_id ;'.format(channelid[0][6])
fundsidename = MysqlClent.executed_one(loginAction.DB, sql)
res = PlatformAction.test_api_78dk_platform_tm_first_viewFirstCheckContract(
contract_uuid)
# # 法大大地址：
contract_uuidurl = 'select Tbl_ContractInfo.view_url from Tbl_ContractInfo,Tbl_Contract where ' \
'Tbl_Contract.contract_uuid=\'{}\' and Tbl_Contract.id=Tbl_ContractInfo.contract_id' \
.format(contract_uuid)
contract_uuid1 = MysqlClent.executed_one(loginAction.DB,
contract_uuidurl)
# # 分数
score = 'select score from Tbl_QifaMachineLog where contract_uuid=\'{}\';'.format(
contract_uuid)
score1 = MysqlClent.executed_all(loginAction.DB, score)
# 查询门店信息
storenamesql = 'select Tbl_Store.business_address from Tbl_Store,Tbl_Contract where ' \
'Tbl_Contract.storm_id={} and Tbl_Contract.storm_id=Tbl_Store.id and ' \
'Tbl_Contract.contract_uuid=\'{}\';'.format(channelid[0][5], contract_uuid)
storename = MysqlClent.executed_all(loginAction.DB, storenamesql)
Assertion.verity(json.loads(res)['msg'], '成功')
Assertion.verity(json.loads(res)['code'], '10000')
Assertion.verity(json.loads(res)['data']['fddUrl'], contract_uuid1)
Assertion.verity(
json.loads(res)['data']['earlyRepaymentSupport'], channelid[0][21])
Assertion.verity(json.loads(res)['data']['merchantName'], merchantname)
# Assertion.verity(json.loads(res)['data']['aliscore'], '550000.00000000')
Assertion.verity(
json.loads(res)['data']['contractNumber'], channelid[0][8])
Assertion.verity(json.loads(res)['data']['fundPackageName'], fundname)
# Assertion.verity(json.loads(res)['data']['score'], str(score1[0][0]))
Assertion.verity(json.loads(res)['data']['fundSideName'], fundsidename)
# Assertion.verity(json.loads(res)['data']['repaymentMethod'], 'DBDX')
# Assertion.verity(json.loads(res)['data']['storeName'], storename[0][0])
# 查询商户贴息费率 discount_rate
discountRatesql = 'select discount_rate FROM Tbl_Contract WHERE contract_uuid="{}" ' \
'and state ="enabled" '.format(contract_uuid)
discountRate = MysqlClent.executed_all(loginAction.DB, discountRatesql)
print('bbbbbbbbbbbbbbbbb', discountRate)
# Assertion.verity(json.loads(res)['data']['discountRate'], discountRate[0][0])
# 查询产品名称
productNamesql = ' select Tbl_ProductDetail.name from Tbl_ProductDetail,Tbl_Contract where ' \
'Tbl_Contract.product_id=Tbl_ProductDetail.id and Tbl_Contract.contract_uuid=\'{}\'; ' \
.format(contract_uuid)
productName = MysqlClent.executed_all(loginAction.DB, productNamesql)
Assertion.verity(
json.loads(res)['data']['productName'], productName[0][0])
Assertion.verity(json.loads(res)['data']['aliOpinion'], '网商审核通过')
# 查询期数信息
loanPeriodssql = 'SELECT loan_periods FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
loanPeriods = MysqlClent.executed_all(loginAction.DB, loanPeriodssql)
Assertion.verity(
json.loads(res)['data']['loanPeriods'], loanPeriods[0][0])
# 查询个人基本资料
personsql = 'SELECT tpb.* FROM Tbl_Contract tc, Tbl_ContractPersonBasicRelation tcpr, Tbl_PersonBasicInfo tpb ' \
'where tc.contract_uuid=\'{}\' and tc.id=tcpr.contract_id and tcpr.person_basic_info_id=tpb.id;' \
.format(contract_uuid)
person = MysqlClent.executed_all(loginAction.DB, personsql)
# Assertion.verity(json.loads(res)['data']['person'], '个人基本资料')
Assertion.verity(
json.loads(res)['data']['person']['homeType'], person[0][6])
Assertion.verity(
json.loads(res)['data']['person']['idCardNumber'], person[0][3])
Assertion.verity(
json.loads(res)['data']['person']['immediateFamilyName'],
person[0][7])
Assertion.verity(
json.loads(res)['data']['person']['immediateFamilyPhone'],
person[0][9])
Assertion.verity(
json.loads(res)['data']['person']['immediateFamilyRelation'],
person[0][8])
Assertion.verity(
json.loads(res)['data']['person']['name'], person[0][2])
Assertion.verity(
json.loads(res)['data']['person']['phone'], person[0][4])
Assertion.verity(
json.loads(res)['data']['person']['typeOfJob'], person[0][5])
# 查询 overdue_handling_fee_rate 逾期手续费率 - 手续费'
overdue_handling_fee_ratesql = 'SELECT overdue_handling_fee_rate FROM Tbl_Contract where contract_uuid=\'{}\';' \
.format(contract_uuid)
overdueHandlingFeeRate = MysqlClent.executed_all(
loginAction.DB, overdue_handling_fee_ratesql)
# Assertion.verity(json.loads(res)['data']['overdueHandlingFeeRate'], overdueHandlingFeeRate[0][0])
# 提前还款手续费
earlyRepaymentHandlingFeesql = 'SELECT early_repayment_handling_fee FROM Tbl_Contract where contract_uuid=\'{}\';' \
.format(contract_uuid)
earlyRepaymentHandlingFee = MysqlClent.executed_all(
loginAction.DB, earlyRepaymentHandlingFeesql)
# Assertion.verity(json.loads(res)['data']['earlyRepaymentHandlingFee'], earlyRepaymentHandlingFee[0][0])
# submit_state
submitStatesql = 'SELECT submit_state FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
submitState = MysqlClent.executed_all(loginAction.DB, submitStatesql)
# Assertion.verity(json.loads(res)['data']['submitState'], submitState[0][0])
# 用户签订日期查询
signingDatesql = 'SELECT signing_date FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
signingDate = MysqlClent.executed_all(loginAction.DB, signingDatesql)
# Assertion.verity(json.loads(res)['data']['signingDate'], signingDate[0][0])
# 查询分期手续费率 精度问题
periodRatesql = 'SELECT period_rate FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
periodRate = MysqlClent.executed_all(loginAction.DB, periodRatesql)
# Assertion.verity(json.loads(res)['data']['periodRate'], periodRate[0][0])
# 查询宽限期
gracePeriodsql = 'SELECT grace_period FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
gracePeriod = MysqlClent.executed_all(loginAction.DB, gracePeriodsql)
# Assertion.verity(json.loads(res)['data']['gracePeriod'], gracePeriod[0][0])
# 查询贷款金额 loan_amount 精度问题 '20000' != Decimal('20000.00000000')
loan_amountsql = 'SELECT loan_amount FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
loanAmount = MysqlClent.executed_all(loginAction.DB, loan_amountsql)
# Assertion.verity(json.loads(res)['data']['loanAmount'], loanAmount[0][0])
# 查询 逾期手续费率 - 本金
overduePrincipalRatesql = 'SELECT overdue_principal_rate FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
overduePrincipalRate = MysqlClent.executed_all(
loginAction.DB, overduePrincipalRatesql)
# Assertion.verity(json.loads(res)['data']['overduePrincipalRate'], overduePrincipalRate[0][0])
# 提前还款周期
earlyRepaymentFreeCyclesql = 'SELECT early_repayment_free_cycle FROM Tbl_Contract where contract_uuid=\'{}\';'.format(
contract_uuid)
earlyRepaymentFreeCycle = MysqlClent.executed_all(
loginAction.DB, earlyRepaymentFreeCyclesql)
# 支付宝审核日志
alipayAuditLogssql = 'SELECT talog.opinion,talog.alipay_audit FROM Tbl_Contract tc ,Tbl_AlipayAuditLog talog ' \
'where tc.contract_uuid=\'{}\' and talog.contract_id=tc.id;'.format(contract_uuid)
alipayAuditLogs = MysqlClent.executed_all(loginAction.DB,
alipayAuditLogssql)
Assertion.verity(
json.loads(res)['data']['alipayAuditLogs']['opinion'],
alipayAuditLogs[0][0])
# 影响资料信息
contractImagessql = 'SELECT tcim.* FROM Tbl_Contract tc ,Tbl_ContractImage tcim where tc.contract_uuid=\'{}\'' \
' and tc.id=tcim.contract_id ORDER BY tcim.id ASC;'.format(contract_uuid)
contractImages = MysqlClent.executed_all(loginAction.DB,
contractImagessql)
# 身份证照片(正面)
Assertion.verity(
json.loads(res)['data']['contractImages'][0]['key'],
contractImages[0][4])
Assertion.verity(
json.loads(res)['data']['contractImages'][0]['keyName'],
contractImages[0][6])
# 身份证照片(反面)
Assertion.verity(
json.loads(res)['data']['contractImages'][1]['key'],
contractImages[1][4])
Assertion.verity(
json.loads(res)['data']['contractImages'][1]['keyName'],
contractImages[1][6])