class CompanyProject(object):
def __init__(self):
self.logger = Log()
self.config = ReadYaml(FileUtil.getProjectObsPath() +
'/config/config.yaml').getValue()
self.db = DBUtil(host=self.config['Mysql_host'],
port=self.config['Mysql_port'],
user=self.config['Mysql_user'],
passwd=self.config['Mysql_passwd'],
dbname=self.config['Mysql_dbname'],
charset=self.config['Mysql_charset'])
self.today = str(datetime.date.today())
self.endDate = str(datetime.date.today() + datetime.timedelta(days=1))
self.partnerNo = self.config['partnerNo']
def select_project(self):
sql = 'SELECT projectId, projectName FROM YD_TMS_PROJECT where partnerNo = \'{0}\' '.format(
self.config['partnerNo'])
project_list = self.db.execute_select_many_record(sql)[0]
return project_list
def add_project(self):
header = HeaderDict().wuliuyun_header()
url = 'https://{0}/api/tms/customer/addProject'.format(
self.config['tms_api_host'])
body_dict = {
"projectName": "自动化测试", # 项目名称，必填，长度大于0不超过20的字符串，唯一性校验：一个公司下的项目名唯一
"custId": '', # 客户ID，非必填
"startTime": self.today, # 开始日期，必填，格式：'yyyy-MM-dd'
"endTime": self.endDate # 结束日期，必填，格式：'yyyy-MM-dd'
}
try:
response = HttpClient().post_json(url=url,
header_dict=header,
body_dict=body_dict)
self.logger.info('##### 请求消息头：{0} #####'.format(
response.request.headers))
self.logger.info('##### 请求消息体：{0} #####'.format(
response.request.body))
self.logger.info('##### 返回消息体：{0} #####'.format(response.json()))
return response
except Exception as error:
self.logger.info(
'#### 发生错误：{0} ####\t\t#### 返回None ####'.format(error))
return None
def get_project(self):
projects_list = self.select_project()
if projects_list:
# 更新项目有效时间，使项目生效，返回生效项目列表
project_id = projects_list[0]
sql = 'UPDATE YD_TMS_PROJECT set endTime=\'{0}\' where partnerNo = \'{1}\' and projectId = \'{2}\''.format(
self.endDate, self.config['partnerNo'], project_id)
self.db.execute_sql(sql)
the_project = self.select_project()
return the_project
else:
self.add_project()
the_project = self.select_project()
return the_project
class CreateCustomer(object):
'''新增客户'''
def __init__(self):
self.logger = Log()
self.config = ReadYaml(FileUtil.getProjectObsPath() +
'/config/config.yaml').getValue()
def create_customer(self, customerName, customerCode, phone,
customerDeveloper):
'''新增客户'''
try:
response = CustomerCreate().customer_create(
customerName=customerName,
customerCode=customerCode,
phone=phone,
customerDeveloper=customerDeveloper)
self.logger.info('新增的客户名称是: {0}'.format(customerName))
# 判断客户名称和客户编号是否重复
if response.json()['code'] == 0:
return response.json()['content']
elif response.json()['code'] == 9020103:
sql = 'DELETE FROM YD_TMS_CUSTOMER WHERE customerName = \'{0}\' and partnerNo = \'{1}\''.format(
customerName, self.config['partnerNo'])
self.DBUtil = DBUtil(host=self.config['db_host'],
port=self.config['db_port'],
user=self.config['db_user'],
passwd=self.config['db_passwd'],
dbname=self.config['db_dbname'],
charset=self.config['db_charset'])
self.DBUtil.execute_sql(sql)
customerId = CustomerCreate().customer_create(
customerName=customerName,
customerCode=customerCode,
phone=phone,
customerDeveloper=customerDeveloper).json()['content']
return customerId
elif response.json()['code'] == 9020104:
sql = 'DELETE FROM YD_TMS_CUSTOMER WHERE customerCode = \'{0}\'and partnerNo = \'{1}\''.format(
customerCode, self.config['partnerNo'])
self.DBUtil = DBUtil(host=self.config['db_host'],
port=self.config['db_port'],
user=self.config['db_user'],
passwd=self.config['db_passwd'],
dbname=self.config['db_dbname'],
charset=self.config['db_charset'])
self.DBUtil.execute_sql(sql)
customerId = CustomerCreate().customer_create(
customerName=customerName,
customerCode=customerCode,
phone=phone,
customerDeveloper=customerDeveloper).json()['content']
return customerId
else:
self.logger.info('新增客户返回错误:{0}'.format(response.json()))
return None
except Exception as e:
self.logger.error('新增客户发生异常:{0}'.format(e))
return None
def delete_waybill(self):
# 删除待发车运单
db = DBUtil(host=self.config['Mysql_host'],
port=self.config['Mysql_port'],
user=self.config['Mysql_user'],
passwd=self.config['Mysql_passwd'],
dbname=self.config['Mysql_dbname'],
charset=self.config['Mysql_charset'])
sql = 'UPDATE YD_APP_TRANSPORTCASH SET delStatus = 1 where mobile = \'{0}\' AND partnerNo = \'{1}\' AND billStatus = \'W\' AND delStatus = 0'.format(
self.mobile, self.partnerNo)
db.execute_sql(sql)
class TestProjectUpdate(unittest.TestCase):
'''修改项目'''
def setUp(self):
self.logger = Log()
self.config = ReadYaml(FileUtil.getProjectObsPath() + '/config/config.yaml').getValue()
self.logger.info('########################### TestProjectUpdate START ###########################')
self.projectName = '测试项目'
self.startTime = time.strftime('%Y-%M-%d')
self.endTime = time.strftime('%Y-%m-%d', time.localtime(time.time() + 86400))
self.customerName = '测试之家'
self.customerCode = 'CSZJ201710200001'
self.phone = DataUtil().createmoble()
self.projectId = CreateProject().create_project(self.projectName,self.startTime,self.endTime,self.customerName,
self.customerCode,self.phone,'黄经理')
def tearDown(self):
self.logger.info('############################ TestProjectUpdate END ############################')
def test_project_update_projectName_success(self):
'''修改项目名称'''
project_list = ProjectSelect().project_select(projectStatus='0',projectName=self.projectName).json()[
'content'][ 'dataList']
if project_list != [] and len(project_list) == 1:
for project in project_list:
self.customerId = project['custId']
else:
self.logger.error('Please check the results of the project for error')
self.projectName_update = '修改项目'
project_update_list = ProjectSelect().project_select(projectStatus='0', projectName=self.projectName_update).json()[
'content']['dataList']
if project_update_list != [] and len(project_update_list) == 1:
sql = 'DELETE FROM YD_TMS_PROJECT WHERE projectName = \'{0}\'and partnerNo = \'{1}\''.format(
self.projectName_update, self.config['partnerNo'])
self.DBUtil = DBUtil(host=self.config['db_host'], port=self.config['db_port'],
user=self.config['db_user'], passwd=self.config['db_passwd'],
dbname=self.config['db_dbname'], charset=self.config['db_charset'])
self.DBUtil.execute_sql(sql)
else:
self.logger.error('Please check the results of the project for error')
response = ProjectUpdate().project_update(projectId=self.projectId,updateType='1',
projectName=self.projectName_update,custId=self.customerId,
startTime =self.startTime,endTime=self.endTime)
self.logger.info('修改项目名称返回状态码：{0}'.format(response))
self.assertEqual(response.status_code, 200)
self.assertEqual(response.json()['code'], 0)
self.logger.info('修改项目名称返回：{0}'.format(response.json()))
response_get = ProjectGet().project_get(self.projectId)
self.assertEqual(self.projectName_update, response_get.json()['content']['projectName'], 'Project updated fail!')
class CreateUser(object):
'''新增账号'''
def __init__(self):
self.logger = Log()
self.config = ReadYaml(FileUtil.getProjectObsPath() + '/config/config.yaml').getValue()
def create_user(self,roleName,menuJson,name,userName,mobile,isLoginApp,carType):
'''新增账号'''
try:
#选择角色
role_list = RoleCompanyGet().role_company_get().json()['content']
if role_list == []:
roleId = CreateRole().create_role(roleName,menuJson)
self.logger.info('公司没有角色时新增角色,新增角色名称是:{0}'.format(roleName))
else:
roleId = random.choice(role_list)['roleId']
self.logger.info('新增账号的角色ID是:{0}'.format(roleId))
#选择项目数据权限
projectId = CreateProject().project_data_permission_choice()
#新增账号
response = UserCreate().user_create(roleId,name,userName,mobile,isLoginApp,projectId,carType)
if response.json()['code'] == 0:
return response.json()['content'],roleId
# 需求变动（已删除的子账号不能再新建）
# elif response.json()['code'] == 9110028:
# user_list = UserSelect().user_select().json()['content']['dataList']
# for user in user_list:
# if user['userName'] == userName:
# UserDelete().user_delete(user['loginId'])
# userId = UserCreate().user_create(roleId,name,userName,mobile,isLoginApp,projectId,carType).json()['content']
# return userId,roleId
# elif response.json()['code'] == 9110029:
# user_list = UserSelect().user_select().json()['content']['dataList']
# for user in user_list:
# if user['mobile'] == mobile:
# UserDelete().user_delete(user['loginId'])
# userId = UserCreate().user_create(roleId,name,userName,mobile,isLoginApp,projectId,carType).json()['content']
# return userId,roleId
elif response.json()['code'] == 9110028:
sql = 'DELETE FROM YD_APP_APPROVAL_USER WHERE userName = \'{0}\' and partnerNo = \'{1}\''.format(
userName,self.config['partnerNo'])
self.DBUtil = DBUtil(host=self.config['db_host'], port=self.config['db_port'],
user=self.config['db_user'], passwd=self.config['db_passwd'],
dbname=self.config['db_dbname'], charset=self.config['db_charset'])
print(sql)
self.DBUtil.execute_sql(sql)
userId = UserCreate().user_create(roleId, name, userName, mobile, isLoginApp, projectId, carType
).json()[ 'content']
return userId, roleId
elif response.json()['code'] == 9110029:
sql = 'DELETE FROM YD_APP_APPROVAL_USER WHERE mobile = \'{0}\' and partnerNo = \'{1}\''.format(mobile,
self.config['partnerNo'])
self.DBUtil = DBUtil(host=self.config['db_host'], port=self.config['db_port'],
user=self.config['db_user'], passwd=self.config['db_passwd'],
dbname=self.config['db_dbname'], charset=self.config['db_charset'])
print(sql)
self.DBUtil.execute_sql(sql)
print(sql)
userId = UserCreate().user_create(roleId, name, userName, mobile, isLoginApp, projectId, carType
).json()['content']
return userId, roleId
else:
self.logger.info('新增账号返回错误:{0}'.format(response.json()))
return None,None
except Exception as e:
self.logger.error('新增账号发生异常:{0}'.format(e))
return None
class DbOperation(object):
def __init__(self):
self.config = ReadYaml(FileUtil.getProjectObsPath() +
'/config/config.yaml').getValue()
self.db = DBUtil(host=self.config['Mysql_host'],
port=self.config['Mysql_port'],
user=self.config['Mysql_user'],
passwd=self.config['Mysql_passwd'],
dbname=self.config['Mysql_dbname'],
charset=self.config['Mysql_charset'])
def select_driver_info(self, mobile):
data_dict = {}
sql = 'SELECT idNo,name,isCertifacate,pyAuthFlag,carNo,isCarCertificate from YD_APP_USER where mobile = \'{0}\' and source = \'register\''.format(
mobile)
data = self.db.execute_select_one_record(sql)
data_dict['idNo'] = data[0]
data_dict['name'] = data[1]
data_dict['isCertifacate'] = data[2]
data_dict['AuthFlag'] = data[3]
data_dict['carNo'] = data[4]
data_dict['isCarCertificate'] = data[5]
return data_dict
def initialize_driver_info(self, mobile):
sql = 'UPDATE `YD_APP_USER` SET `carNo`=NULL, `idNo`=NULL, `name`=NULL, `portraitId`=NULL, `coreLoginId`=NULL,' \
' `custId`=\'0\', `isCarCertificate`=NULL, `isCardBindFlag`=NULL, `isCertifacate`=NULL, `portrait`=NULL,' \
' `pyAuthFlag`=NULL, `pyAuthResult`=NULL, `clientId`=NULL, `introducerPhone`=NULL WHERE (`mobile`=\'{0}\'' \
' and `source`=\'register\')'.format(mobile)
self.db.execute_sql(sql)
def update_driver_info(self):
custId = self.config['custId_unregister']
mobile = self.config['mobile_unregister']
name = self.config['name_unregister']
idNo = self.config['idNo_unregister']
sql = 'UPDATE `YD_APP_USER` SET `carNo`=NULL, `idNo`={0}, `name`=\'{1}\', `portraitId`=NULL, `coreLoginId`={0},' \
' `custId`={2}, `isCarCertificate`=NULL, `isCardBindFlag`=NULL, `isCertifacate`=\'Y\', `portrait`=NULL,' \
' `pyAuthFlag`=\'0\', `pyAuthResult`=NULL, `clientId`=NULL, `introducerPhone`=NULL WHERE (`mobile`={3}' \
' and `source`=\'register\')'.format(idNo, name, custId, mobile)
self.db.execute_sql(sql)
def certificate_driver_info(self, mobile):
custId = self.config['custId_unregister']
name = self.config['name_unregister']
idNo = self.config['idNo_unregister']
sql = 'UPDATE `YD_APP_USER` SET `carNo`=\'皖A12345\', `idNo`={0}, `name`=\'{1}\', `portraitId`=NULL, `coreLoginId`={0},' \
' `custId`={2}, `isCarCertificate`=\'Y\', `isCardBindFlag`=NULL, `isCertifacate`=\'Y\', `portrait`=NULL,' \
' `pyAuthFlag`=\'0\', `pyAuthResult`=NULL, `clientId`=NULL, `introducerPhone`=NULL WHERE (`mobile`={3}' \
' and `source`=\'register\')'.format(idNo, name, custId, mobile)
self.db.execute_sql(sql)
def select_wallet_state(self, mobile):
# 查询手机号已开通钱包个数
sql_select = 'select COUNT(*) FROM YD_APP_MYBANK_OPEN_ACCOUNT where mobile = \'{0}\' and accountOpened = 1'.format(
mobile)
state = self.db.execute_select_one_record(sql_select)[0]
return state
def delete_wallet_driver(self, mobile):
sql_select = 'SELECT id from YD_APP_MYBANK_OPEN_ACCOUNT where mobile =\'{0}\''.format(
mobile)
ids = self.db.execute_select_many_record(sql_select)
if ids:
for id in ids:
sql_del = 'DELETE FROM `YD_APP_MYBANK_OPEN_ACCOUNT` WHERE id=\'{0}\' '.format(
id[0])
self.db.execute_sql(sql_del)
def delete_waybill_driver(self, mobile):
sql_select = 'SELECT id from YD_APP_TRANSPORTCASH where mobile = {0} and delStatus = 0'.format(
mobile)
waybill_list = self.db.execute_select_many_record(sql_select)
for waybill_id in waybill_list:
id = waybill_id[0]
sql_del = 'UPDATE YD_APP_TRANSPORTCASH SET delStatus = 1 WHERE id = {0}'.format(
id)
self.db.execute_sql(sql_del)
def select_waybill_state(self, mobile):
# 返回运单状态数组
sql_select = 'SELECT billStatus from YD_APP_TRANSPORTCASH where mobile = {0} and delStatus = 0 and partnerNo = \'jUTViKluU\''.format(
mobile)
state = self.db.execute_select_one_record(sql_select)
return state
def add_wallet_consignor(self, mobile):
sql_select = 'SELECT loginId from YD_APP_USER where mobile = {0} and source = \'register\''.format(
mobile)
loginId = self.db.execute_select_one_record(sql_select)[0]
sql_consignor = 'SELECT id FROM YD_APP_PAYMENT_RECEIVER WHERE loginId = \'{}\' ORDER BY id DESC LIMIT 1'.format(
loginId)
consignor_id = self.db.execute_select_one_record(sql_consignor)
if consignor_id:
sql_update = 'UPDATE YD_APP_PAYMENT_RECEIVER SET isAvailable = 1 , isDelete = 0 WHERE id = {0}'.format(
consignor_id[0])
self.db.execute_sql(sql_update)
else:
sql_insert = 'INSERT INTO YD_APP_PAYMENT_RECEIVER (`createDate`, `isOpenPayMent`, `loginId`, `receiverId`, ' \
'`receiverIdNo`, `receiverMobile`, `receiverName`, `receiverRelation`, `updateDate`, `comfirm`, ' \
'`isAvailable`, `receiverLoginId`, `isDelete`) VALUES (\'2018-11-01 15:16:18\', NULL, \'{0}\', ' \
'NULL, \'320621199210280523\', \'18020329066\', \'王燕\', \'其它\', NULL, 1, 1, ' \
'\'APP20170418150043iFVLP\', \'0\')'.format(loginId)
self.db.execute_insert_sql(sql_insert)
def delete_wallet_consignor(self, mobile):
sql_select = 'SELECT loginId from YD_APP_USER where mobile = {0} and source = \'register\''.format(
mobile)
loginId = self.db.execute_select_one_record(sql_select)[0]
sql_consignor = 'SELECT id from YD_APP_PAYMENT_RECEIVER where loginId = \'{0}\' and isAvailable = 1 and isDelete = 0'.format(
loginId)
consignor_list = self.db.execute_select_many_record(sql_consignor)
for id in consignor_list:
sql_delete = 'UPDATE YD_APP_PAYMENT_RECEIVER SET isAvailable = 0,isDelete = 1 WHERE id = {0}'.format(
id[0])
self.db.execute_sql(sql_delete)
def update_wallet_card_state(self, mobile):
sql_select = 'SELECT c.id from YD_APP_MYBANK_OPEN_ACCOUNT a LEFT JOIN YD_APP_USER b on a.loginId = b.loginId LEFT JOIN YD_APP_MYBANK_BIND_CARD c on a.mybankId = c.mybankId where b.mobile = \'{}\' ORDER BY c.id DESC LIMIT 1'.format(
mobile)
card_id = self.db.execute_select_one_record(sql_select)[0]
sql_update = 'UPDATE YD_APP_MYBANK_BIND_CARD SET ifBindCardSuccess = 1, isUsable = 1 WHERE id = {}'.format(
card_id)
self.db.execute_sql(sql_update)
def shipper_bankcard_del(self, shipper_mobile):
# 将用户已绑定银行卡设置为失效
sql_select = 'SELECT a.id from YD_APP_SPECIAL_BANK_BIND a LEFT JOIN YD_APP_APPROVAL_USER b on a.loginId = b.loginId where b.mobile = \'{0}\''.format(
shipper_mobile)
bankcard_ids = self.db.execute_select_many_record(sql_select)
if bankcard_ids:
for card_id in bankcard_ids:
sql_update = 'UPDATE YD_APP_SPECIAL_BANK_BIND SET cardStatus=\'N\' WHERE (id=\'{0}\')'.format(
card_id[0])
self.db.execute_sql(sql_update)
def shipper_bankcard_add(self, shipper_mobile):
# 将用户之前绑定的第一张银行卡设置为生效
sql_select = 'SELECT a.id from YD_APP_SPECIAL_BANK_BIND a LEFT JOIN YD_APP_APPROVAL_USER b on a.loginId = b.loginId where b.mobile = \'{0}\''.format(
shipper_mobile)
bankcard_ids = self.db.execute_select_many_record(sql_select)
if bankcard_ids:
sql_update = 'UPDATE YD_APP_SPECIAL_BANK_BIND SET cardStatus=\'V\' WHERE (id=\'{0}\')'.format(
bankcard_ids[0][0])
self.db.execute_sql(sql_update)
else:
sql_insert = 'INSERT INTO YD_APP_SPECIAL_BANK_BIND (`acctName`, `bankCity`, `bankName`, `bankPrinc`, `cardName`, `cardNo`, `cardStatus`, `cardType`, `createTime`, `idNo`, `idType`, `loginId`, `logoUrl`, `mobile`, `orgCode`, `subbranchBankName`, `bank`) VALUES (\'刘新宇\', \'340100\', \'建设银行\', \'340000\', \'龙卡通\', \'6217001630031143297\', \'V\', \'借记卡\', \'2018-11-09 13:48:22\', \'142201199305027054\', \'01\', \'APPSHIPPER20180829100001MgiIm\', \'http://yudian.ufile.ucloud.com.cn/019bd30c-39bb-4680-a32c-1d8703f90136.jpg?UCloudPublicKey=ucloudtangshd@weifenf.com14355492830001993909323&Expires=&Signature=pPx9/MwLLhB80/JgqSS8UPOeIh8=\', \'18056070532\', \'01050000\', \'测试建设支行\', \'建设银行\')'
self.db.execute_sql(sql_insert)
class CreateProject(object):
'''新增项目'''
def __init__(self):
self.logger = Log()
self.config = ReadYaml(FileUtil.getProjectObsPath() +
'/config/config.yaml').getValue()
def create_project(self, projectName, startTime, endTime, customerName,
customerCode, phone, customerDeveloper):
'''新增项目'''
try:
customerId = CreateCustomer().create_customer(
customerName=customerName,
customerCode=customerCode,
phone=phone,
customerDeveloper=customerDeveloper)
response = ProjectCreate().project_create(projectName=projectName,
custId=customerId,
startTime=startTime,
endTime=endTime)
self.logger.info('新增的项目名称是: {0}'.format(projectName))
# 判断项目名称是否重复
if response.json()['code'] == 0:
return response.json()['content']
elif response.json()['code'] == 9020502:
sql = 'DELETE FROM YD_TMS_PROJECT WHERE projectName = \'{0}\'and partnerNo = \'{1}\''.format(
projectName, self.config['partnerNo'])
self.DBUtil = DBUtil(host=self.config['db_host'],
port=self.config['db_port'],
user=self.config['db_user'],
passwd=self.config['db_passwd'],
dbname=self.config['db_dbname'],
charset=self.config['db_charset'])
self.DBUtil.execute_sql(sql)
projectId = ProjectCreate().project_create(
projectName=projectName,
custId=customerId,
startTime=startTime,
endTime=endTime).json()['content']
return projectId
else:
self.logger.info('新增的项目名称返回错误:{0}'.format(response.json()))
return None
except Exception as e:
self.logger.error('新增项目发生异常:{0}'.format(e))
return None
#选择项目
def project_choice(self,
projectName='德邦物流',
customerName='德邦集团',
customerCode='DB20171101100',
phone='13077327043',
customerDeveloper='张经理'):
project_list = ProjectSelect().project_select(
rows='1000', projectStatus='1').json()['content']['dataList']
if project_list == []:
startTime = time.strftime('%Y-%m-%d')
endTime = time.strftime('%Y-%m-%d',
time.localtime(time.time() + 2592000))
projectId = CreateProject().create_project(projectName, startTime,
endTime, customerName,
customerCode, phone,
customerDeveloper)
project = {'projectId': projectId, 'projectName': projectName}
self.logger.info('新建的项目是: {0}'.format(project))
else:
project = random.sample(project_list, 1)[0]
self.logger.info('选择的项目是: {0}'.format(project))
return project
##选择项目数据权限
def project_data_permission_choice(self):
project_list = ProjectSelect().project_select(
rows='1000', projectStatus='1').json()['content']['dataList']
if project_list == []:
projectId = ''
self.logger.info('项目数据权限选择不限')
else:
project = random.sample(project_list, 1)[0]
projectId = project['projectId']
self.logger.info('项目数据权限选择: {0}'.format(project['projectName']))
return projectId