def get_sql_data_in_clock_in_form(self, data):
begin_time = self.driver.get_element('startTime_travel').get_attribute(
'value')
end_time = self.driver.get_element('endTime_travel').get_attribute(
'value')
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_form()
cursor = connect.cursor()
sql = "SELECT DEVICE_IMEI,GPS_TIME,ON_OFF FROM clock_on_off WHERE DEVICE_IMEI = '" + self.get_dev_imei(
) + "' and GPS_TIME BETWEEN '" + begin_time + "' and '" + end_time + "'"
if data['clock_in_type'] != 'all':
sql += " and ON_OFF = '" + data['clock_in_type'] + "'"
sql += " order by GPS_TIME desc;"
cursor.execute(sql)
data = cursor.fetchall()
data_list = []
for range in data:
data_list.append({
'imei': range[0],
'time': str(range[1]),
'on_off': range[2]
})
cursor.close()
connect.close()
return data_list
def get_sql_total_number_in_obd_vehicel_condition_form(self):
begin_time = self.driver.get_element(
'x,//*[@id="startTime_travel"]').get_attribute('value')
end_time = self.driver.get_element(
'x,//*[@id="endTime_travel"]').get_attribute('value')
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_form()
cursor = connect.cursor()
sql = "SELECT GPS_TIME,SPEED,ROTATING_SPEED,WATER_TEMPERATURE,BATTERY_VOLTAGE,FUELCONSUMPTION_1,TOTAL_MILEAGE FROM gt_obd_his WHERE DEVICE_IMEI = '" + self.search_imei(
) + "' and GPS_TIME BETWEEN '" + begin_time + "' and '" + end_time + "' and ERROR_CODE is NULL and RAPID_ACCELERATION = 0 and RAPID_DECELERATION = 0 ORDER BY CREATE_TIME DESC;"
print(sql)
cursor.execute(sql)
data = cursor.fetchall()
data_list = []
for range in data:
data_list.append({
'begin_time': str(range[0]),
'speed': range[1],
'rotating_speed': range[2],
'water_temperature': range[3],
'battery_voltage': range[4],
'fuel': range[5],
'total_mileage': range[6],
})
cursor.close()
connect.close()
return data_list
def get_sql_total_number_in_tracel_form(self):
begin_time = self.driver.get_element(
'x,//*[@id="startTime_travel"]').get_attribute('value')
end_time = self.driver.get_element(
'x,//*[@id="endTime_travel"]').get_attribute('value')
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_form()
cursor = connect.cursor()
sql = "select START_TIME,END_TIME,TRIP_TIME,MILEAGE,FUELCONSUMPTION_TOTAL,FUELCONSUMPTION_AVG FROM gt_obd_trip WHERE DEVICE_IMEI = '" + self.search_imei(
) + "' and (START_TIME BETWEEN '" + begin_time + "' and '" + end_time + "' or END_TIME BETWEEN '" + begin_time + "' and '" + end_time + "' or (START_TIME<='" + begin_time + "' and END_TIME >= '" + end_time + "')) order by CREATE_TIME desc;"
cursor.execute(sql)
data = cursor.fetchall()
data_list = []
for range in data:
data_list.append({
'startTime': str(range[0]),
'endTime': str(range[1]),
'tripTime': self.chang_time_formeat(range[2]),
'mileage': float('%.2f' % range[3]),
'totalFuelConsumption': float('%.2f' % range[4]),
'avgFuelConsumption': float('%.2f' % range[5]),
})
cursor.close()
connect.close()
return data_list
def setUp(self):
# 前置条件
# 实例化对象
self.driver = AutomateDriver(choose='firefox')
self.base_url = self.driver.base_url
self.base_page = BasePage(self.driver, self.base_url)
self.command_management_page = CommandManagementPage(
self.driver, self.base_url)
self.log_in_base = LogInBase(self.driver, self.base_url)
self.command_management_page_read_csv = CommandManagementPageReadCsv()
self.connect_sql = ConnectSql()
self.search_sql = SearchSql()
self.assert_text = AssertText()
# 打开页面，填写用户名、密码、点击登录
self.base_page.open_page()
self.driver.set_window_max()
self.driver.implicitly_wait(5)
self.driver.clear_cookies()
self.log_in_base.log_in_jimitest()
self.log_in_base.click_account_center_button()
self.current_account = self.log_in_base.get_log_in_account()
# 登录之后点击控制台，然后点击指令管理
self.command_management_page.click_control_after_click_command_management(
)
sleep(3)
def get_consume_number_in_mysql(self, begin_time, end_time, account):
data = []
conncet_sql = ConnectSql()
conncet = conncet_sql.connect_tuqiang_sql()
cursor = conncet.cursor()
massage_sql = "SELECT b.amount FROM vs_bill b INNER JOIN user_info o on b.userId = o.userId WHERE o.account = '" + account + "' and b.alarmPushType = 4 and b.creationDate BETWEEN '" + begin_time + "' and '" + end_time + "';"
cursor.execute(massage_sql)
massage_data = cursor.fetchall()
data.append(massage_data[0][0])
telephone_sql = "SELECT b.amount FROM vs_bill b INNER JOIN user_info o on b.userId = o.userId WHERE o.account = '" + account + "' and b.alarmPushType = 3 and b.creationDate BETWEEN '" + begin_time + "' and '" + end_time + "';"
cursor.execute(telephone_sql)
telephone_data = cursor.fetchall()
data.append(telephone_data[0][0])
year_card_sql = "SELECT b.imei FROM rc_recharge b INNER JOIN user_info o on b.userId = o.userId WHERE o.account = '" + account + "' and b.cardType = 1 and b.creationDate BETWEEN '" + begin_time + "' and '" + end_time + "';"
cursor.execute(year_card_sql)
year_card_data = cursor.fetchall()
data.append(len(year_card_data))
life_card_sql = "SELECT b.imei FROM rc_recharge b INNER JOIN user_info o on b.userId = o.userId WHERE o.account = '" + account + "' and b.cardType = 2 and b.creationDate BETWEEN '" + begin_time + "' and '" + end_time + "';"
cursor.execute(life_card_sql)
life_card_data = cursor.fetchall()
data.append(len(life_card_data))
cursor.close()
conncet.close()
return data
def setUp(self):
# 前置条件
# 实例化对象
self.driver = AutomateDriver()
self.base_url = self.driver.base_url
self.base_page = BasePage(self.driver, self.base_url)
self.alarm_info_page = AlarmInfoPage(self.driver, self.base_url)
self.statistical_form_page_read_csv = StatisticalFormPageReadCsv()
self.log_in_base = LogInBase(self.driver, self.base_url)
self.statistical_form_page = StatisticalFormPage(
self.driver, self.base_url)
self.connect_sql = ConnectSql()
self.search_sql = SearchSql(self.driver, self.base_url)
# 打开页面，填写用户名、密码、点击登录
self.base_page.open_page()
self.driver.set_window_max()
self.driver.implicitly_wait(5)
self.driver.clear_cookies()
self.assert_text = AssertText()
self.log_in_base.log_in_jimitest()
# 登录之后点击控制台，然后点击指令管理
self.statistical_form_page.click_control_after_click_statistical_form_page(
)
sleep(3)
def get_search_result_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT orgId FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql_01)
orgId = cursor.fetchall()[0][0]
sql_02 = "SELECT orgName,orgSupId,orgTel FROM system_org_info WHERE (id = '" + str(orgId) + "' OR orgSupId = '" + str(orgId) + "') AND " \
"orgName LIKE '%" + key + "%';"
cursor.execute(sql_02)
data_02 = cursor.fetchall()[0]
print(data_02)
orgName = data_02[0]
orgSupId = data_02[1]
orgTel = data_02[2]
sql_03 = "SELECT orgName FROM `system_org_info` WHERE id = '%s';" % orgSupId
cursor.execute(sql_03)
orgSupName = cursor.fetchall()[0][0]
org_info = []
org_info.append(orgName)
org_info.append(orgSupName)
org_info.append(orgTel)
print(org_info)
cursor.close()
connect.close()
return org_info
def setUp(self):
self.driver = AutomateDriverServer(choose='chrome')
self.base_url = self.driver.base_url
self.base_page = BasePageServer(self.driver, self.base_url)
self.login_page = LoginPage(self.driver, self.base_url)
self.cust_manage_basic_info_and_add_cust_page = CustManageBasicInfoAndAddCustPage(
self.driver, self.base_url)
self.account_center_page_details = AccountCenterDetailsPage(
self.driver, self.base_url)
self.cust_manage_cust_list_page = CustManageCustListPage(
self.driver, self.base_url)
self.cust_manage_my_dev_page = CustManageMyDevPage(
self.driver, self.base_url)
self.cust_manage_lower_account_page = CustManageLowerAccountPage(
self.driver, self.base_url)
self.account_center_page_navi_bar = AccountCenterNaviBarPage(
self.driver, self.base_url)
self.driver.set_window_max()
self.log_in_base = LogInBaseServer(self.driver, self.base_url)
self.cust_manage_page_read_csv = CustManagePageReadCsv()
self.search_sql = SearchSql()
self.connect_sql = ConnectSql()
self.driver.wait(1)
self.driver.clear_cookies()
self.driver.wait(1)
def get_year_and_life_card_number_in_mysql(self, account):
conncet_sql = ConnectSql()
conncet = conncet_sql.connect_tuqiang_sql()
cursor = conncet.cursor()
sql = "SELECT v.yearCard,v.lifetimeCard FROM rc_recharge_card_stock v INNER JOIN user_info u on v.userId = u.userId WHERE u.account = '%s';" % account
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
conncet.close()
return data
def get_dev_name_in_sql(self, param):
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_sql()
cousor = connect.cursor()
sql = "SELECT deviceName FROM equipment_mostly WHERE imei = '%s';" % param
cousor.execute(sql)
dev_name = cousor.fetchall()[0][0]
cousor.close()
connect.close()
return dev_name
def get_telephone_and_massage_alarm_number_in_mysql(self, account):
conncet_sql = ConnectSql()
conncet = conncet_sql.connect_tuqiang_sql()
cursor = conncet.cursor()
sql = "SELECT v.phoneRecord,v.infoRecord FROM rc_recharge_card_stock v INNER JOIN user_info u on v.userId = u.userId WHERE u.account = '%s';" % account
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
conncet.close()
return data
def get_sql_total_after_click_search_risk_share(self, data):
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_sql()
cursor = connect.cursor()
sql = "SELECT t.id FROM t_share_area t WHERE t.name LIKE '%" + data['risk_name'] + "%';"
print(sql)
cursor.execute(sql)
sql_data = cursor.fetchall()
cursor.close()
connect.close()
return len(sql_data)
def get_user_sup_org_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT o.orgName FROM `system_user_info` u INNER JOIN `system_org_info` o on u.orgId = o.id WHERE u.loginUser = '******';" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0][0]
print(data)
cursor.close()
connect.close()
return data
def setUp(self):
self.driver = AutomateDriver()
self.base_url = self.driver.base_url
self.base_page = BasePage(self.driver, self.base_url)
self.dev_manage_page = DevManagePages(self.driver, self.base_url)
self.driver.set_window_max()
self.log_in_base = LogInBase(self.driver, self.base_url)
self.dev_manage_page_read_csv = DevManagePageReadCsv()
self.connect_sql = ConnectSql()
self.driver.wait(1)
self.driver.clear_cookies()
self.driver.wait(1)
def get_add_role_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT r.roleName FROM `system_user_info` u INNER JOIN `system_role_info` r ON u.id = r.createUserId " \
"WHERE u.loginUser = '******' ORDER BY r.createTime DESC;" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0][0]
print(data)
cursor.close()
connect.close()
return data
def get_command_imei_user_account(self, command_imei):
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_sql()
cursor = connect.cursor()
sql = "SELECT e.account FROM equipment_mostly e WHERE e.imei = '%s';" % command_imei
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
connect.close()
return data[0][0]
def get_dev_user_name(self):
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_sql()
cursor = connect.cursor()
sql = "SELECT o.nickName FROM equipment_mostly m INNER JOIN user_info o on m.userId = o.userId WHERE m.imei = %s;" % self.get_imei(
)
cursor.execute(sql)
data = cursor.fetchall()
user_name = data[0][0]
cursor.close()
connect.close()
return user_name
def setUp(self):
self.driver = AutomateDriverServer()
self.base_url = self.driver.base_url
self.base_page = BasePageServer(self.driver, self.base_url)
self.login_page = LoginPage(self.driver, self.base_url)
self.account_center_page_navi_bar = AccountCenterNaviBarPage(
self.driver, self.base_url)
self.log_in_page_read_csv = LogInPageReadCsv()
self.driver.set_window_max()
self.connect_sql = ConnectSql()
self.driver.wait(1)
self.driver.clear_cookies()
self.driver.wait(1)
def get_current_org_info_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT o.orgName,o.orgTel FROM `system_user_info` u INNER JOIN system_org_info o ON u.orgId = o.id WHERE u.loginUser = '******';" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0]
current_org_info = []
current_org_info.append(data[0])
current_org_info.append(data[1])
print(current_org_info)
cursor.close()
connect.close()
return current_org_info
def get_sql_total_after_click_search(self, data, account):
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_sql()
cursor = connect.cursor()
sql = "SELECT o.id FROM geozone_info o INNER JOIN user_info u on o.userid = u.userId WHERE u.account = '" + account + "'"
if data['defence_name'] != '':
sql += " and o.geoname like '%" + data['defence_name'] + "%'"
sql += ";"
print(sql)
cursor.execute(sql)
sql_data = cursor.fetchall()
cursor.close()
connect.close()
return len(sql_data)
def get_belong_to_superior_org_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT o.orgSupId FROM `system_user_info` u INNER JOIN `system_org_info` o on u.orgId = o.id WHERE u.loginUser = '******';" % user_account
cursor.execute(sql_01)
orgSupId = cursor.fetchall()[0][0]
sql_02 = "SELECT orgName FROM `system_org_info` WHERE id = '%s';" % orgSupId
cursor.execute(sql_02)
sup_org_name = cursor.fetchall()[0][0]
print(sup_org_name)
cursor.close()
connect.close()
return sup_org_name
def get_add_org_by_sql(self, user_account):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT o.orgName FROM `system_user_info` u INNER JOIN `system_org_info` o ON u.id = o.createUserId WHERE " \
"u.loginUser = '******' ORDER BY o.createTime DESC;" % user_account
cursor.execute(sql)
data = cursor.fetchall()[0]
print(data)
data_01 = data[0]
print(data_01)
cursor.close()
connect.close()
return data_01
def get_search_result_rolename_by_sql(self, user_account, key):
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql_01 = "SELECT r.roleName FROM `system_user_info` u INNER JOIN `system_role_info` r ON u.id = r.createUserId " \
"WHERE u.loginUser = '******' AND r.roleName LIKE '%" + key + "%';"
cursor.execute(sql_01)
data = cursor.fetchall()
roleName = []
for i in range(len(data)):
roleName.append(data[i][0])
print(roleName)
cursor.close()
connect.close()
return roleName
def setUp(self):
self.driver = AutomateDriverServer(choose='chrome')
self.base_url = self.driver.base_url
self.base_page = BasePageServer(self.driver, self.base_url)
self.log_in_base = LogInBaseServer(self.driver, self.base_url)
self.safe_area_page = SafeAreaPage(self.driver, self.base_url)
self.safe_area_page_read_csv = SafeAreaPageReadCsv()
self.assert_text = AssertText()
self.connect_sql = ConnectSql()
self.safe_area_search_sql = SafeAreaSearchSql()
self.base_page.open_page()
self.base_page.click_chinese_button()
self.driver.set_window_max()
self.log_in_base.log_in_jimitest()
def get_account_info(self, user_account):
# 获取当前登录账号的客户信息
connect_sql = ConnectSql()
connect = connect_sql.connect_tuqiang_sql()
cursor = connect.cursor()
sql = "SELECT o.nickName,o.phone,o.email FROM user_info o WHERE o.account = '%s';" % user_account
cursor.execute(sql)
data = cursor.fetchall()
data_list = []
for range in data:
for range1 in range:
data_list.append(range1)
cursor.close()
connect.close()
return data_list
def setUp(self):
self.driver = AutomateDriverServer()
self.base_url = self.driver.base_url
self.base_page = BasePageServer(self.driver, self.base_url)
self.login_page = LoginPage(self.driver, self.base_url)
self.help_page = HelpPage(self.driver, self.base_url)
self.account_center_page_operation_log = AccountCenterOperationLogPage(self.driver, self.base_url)
self.help_page_sql = HelpPageSql()
self.account_center_page_read_csv = AccountCenterPageReadCsv()
self.connect_sql = ConnectSql()
self.log_in_base = LogInBaseServer(self.driver, self.base_page)
self.driver.set_window_max()
self.driver.wait(1)
self.driver.clear_cookies()
self.driver.wait(1)
def setUp(self):
self.driver = AutomateDriverServer()
self.base_url = self.driver.base_url
self.base_page = BasePageServer(self.driver, self.base_url)
self.log_in_base = LogInBaseServer(self.driver, self.base_url)
self.safe_area_page = SafeAreaPage(self.driver, self.base_url)
self.base_page.open_page()
self.base_page.click_chinese_button()
self.driver.set_window_max()
self.assert_text = AssertText()
self.log_in_base.log_in()
self.safe_area_page.click_control_after_click_safe_area()
self.connect_sql = ConnectSql()
self.search_sql = SafeAreaSearchSql()
def setUp(self):
self.driver = AutomateDriver()
self.base_url = self.driver.base_url
self.base_page = BasePage(self.driver, self.base_url)
self.global_dev_search_page = GlobalDevSearchPage(self.driver, self.base_url)
self.account_center_page_navi_bar = AccountCenterNaviBarPages(self.driver, self.base_url)
self.driver.set_window_max()
self.global_account_search_page = GlobalAccountSearchPage(self.driver, self.base_url)
self.log_in_base = LogInBase(self.driver, self.base_url)
self.global_search_page_read_csv = GlobleSearchPageReadCsv()
self.search_sql = SearchSql()
self.driver.wait(1)
self.connect_sql = ConnectSql()
self.driver.clear_cookies()
self.driver.wait(1)
def setUp(self):
# 前置条件
# 实例化对象
self.driver = AutomateDriver()
self.base_url = self.driver.base_url
self.base_page = BasePage(self.driver, self.base_url)
self.statistical_form_page = StatisticalFormPage(
self.driver, self.base_url)
self.statistical_form_page_read_csv = StatisticalFormPageReadCsv()
self.log_in_base = LogInBase(self.driver, self.base_url)
self.connect_sql = ConnectSql()
self.search_sql = SearchSql(self.driver, self.base_url)
self.obd_form_page = ObdFormPage(self.driver, self.base_url)
# 打开页面，填写用户名、密码、点击登录
self.base_page.open_page()
self.driver.set_window_max()
self.driver.implicitly_wait(5)
self.driver.clear_cookies()
self.log_in_base.log_in_jimitest()
self.assert_text = AssertText()
current_handle = self.driver.get_current_window_handle()
self.statistical_form_page.click_control_after_click_statistical_form_page(
)
sleep(3)
self.base_page.change_windows_handle(current_handle)
def get_account_info_by_sql(self, user_account):
# 获取当前登录账号的客户信息
connect_sql = ConnectSql()
connect = connect_sql.connect_risk_sql()
cursor = connect.cursor()
sql = "SELECT loginUser,userName,userTel FROM `system_user_info` WHERE loginUser = '******';" % user_account
cursor.execute(sql)
data = cursor.fetchall()
print(data)
data_list = []
data_list.append(data[0][0])
data_list.append(data[0][1])
data_list.append(data[0][2])
cursor.close()
connect.close()
return data_list