def addRule(self,policy):
if policy in self.specificRules:
raise PolicyAlreadyExistsException
Log.policycontrol("rule added %s"%str(policy))
self.specificRules[str(policy)]=policy
def run(self):
try:
self.__s=SockServer(SocketModeFactory.TCP, ControlHandler,1)
#self.__s.taskManager.config(autokick=False)
self.__s.setReuseAdress()
self.__addr = ("localhost",self.port)
self.__s.bind(self.__addr)
Log.info("control interface started: %s"%(self.__addr,))
self.__s.start()
except socket.error:
Log.error("control interface could not bound on %s",(self.__addr,))
def connectRemote(self,connection,respHeader,c):
if self._socketType=="UNIX":
sc=SockClient(self._socket,self.uri,self)
else:
sc=SockClient(self._socket,connection,self)
self.sockClient=sc
sc.start()
WebSocketHandler.handshakeSuccessful(self, respHeader,c)
Log.info("".join([str(self._socketType)," bridge established to ",str(self.uri)]))
def init():
if not os.path.exists(Authentication.keyPath):
Log.warning("security token does not exist, generating new random value")
Authentication.key=Authentication.generateRandomBytes(2048)
with open(Authentication.keyPath,"wb") as f:
f.write(Authentication.key)
else:
with open(Authentication.keyPath,"rb") as f:
Authentication.key=f.read()
pass
def __init__(self):
# 判断是否配置日志路径，没有使用默认
if Log.has_key('path') is False:
Log['path'] = './runtime/logs'
# 判断日志路径是否存在，不存在创建
if not os.path.exists(Log['path']) is True:
os.makedirs(Log['path'])
# 设置日志保存路径
self.__path = Log['path']
def bootstrap():
def cmd(cmd):
print("cmd",cmd)
def exit_():
print("exit keystroke detected...")
s.stop()
#generate folder structure
pathlist=["../resources","../resources/logs"]
for i in pathlist:
if not os.path.exists(i):
os.mkdir(i)
#init config
StdConfig.getInstance()
try:
policy=PolicyControl.getInstance()
except ParseError as e:
Log.error("Policy XML malformed")
Authentication.init()
proxyWSPort=StdConfig.getInstance().getProxyPort()
adress=("localhost",proxyWSPort)
#s=ThreadedSockServer(SocketModeFactory.TCP,DynamicThreadPoolTaskManager, WebSocketProxyHandler,policy.getMaxConnections())
s=SockServer(SocketModeFactory.TCP, WebSocketProxyHandler,policy.getMaxConnections())
if StdConfig.getInstance().isControlInterfaceEnabled():
ci = ControlInterface(StdConfig.getInstance().getControlPort())
ci.start()
it=InputThread(exit_,cmd)
s.setReuseAdress()
try:
s.bind(adress)
except socket.error:
Log.error("Socket could not be bound on port %s"%proxyWSPort)
Log.info("websocket proxyserver started: %s"%(adress,))
it.start()
s.start()
Log.info("websocket proxyserver stopped: %s"%(adress,))
sys.exit(0)
def aMessage(self,msg):
'''
handles incoming messages and delegates to corresponding methods
'''
try:
recv=json.loads(msg.decode())
except Exception as e:
Log.debug("invalid control command")
self.protocolError()
return
try:
if not self.authenticated:
if self.inObj(["cmd","user","password"],recv) and recv['cmd'] == Message.CMD_AUTHREQ:
if recv["user"] == StdConfig.getInstance().getCtrlUsername() and recv["password"] == StdConfig.getInstance().getCtrlPassword():
self.toClient(Message("", "", Message.STATUS_LOGINOK))
self.authenticated=True
Log.debug("logged in")
else:
Log.debug("log in rejected");
self.toClient(Message("", "", Message.STATUS_AUTHENTICATION_REJECTED))
self.wronglogins+=1
if self.wronglogins >= 3:
self.doDisconnect()
else:
self.toClient(Message("", "", Message.STATUS_NOT_AUTHENTICATED))
else:
#authenticated
if self.inObj(["cmd","user","password"],recv) and recv['cmd'] == Message.CMD_AUTHREQ:
#already autenticated
self.toClient(Message("", "", Message.STATUS_OK))
return
if self.inObj(["cmd","status"], recv) and recv["status"] == "cmd":
if recv['cmd'] in self._cmds:
body=""
if "body" in recv:
body=recv["body"]
self._cmds[recv['cmd']](body)
return
self.protocolError()
except Exception as e:
self.protocolError()
raise e
def _cmdDelete(self,args,update=True):
Log.debug("delete " + args)
self._policyControl.removeRule(args)
if update:
self._cmdList()
class FarmInfo(unittest.TestCase):
farm = FarmMap()
log = Log("开始执行").logger
tool = Tool()
def external_farm_info(self, driver):
self.log.info(
"--------------------开始执行地图上的外部蜂场信息校验case----------------------")
address = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[3]/span'
).text
vehicle_length_get = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[4]/div[1]/span'
).text
vehicle_length = self.tool.replace(vehicle_length_get)
expect_hive_num_get = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[4]/div[2]/span'
).text
expect_hive_num = str(
self.tool.replace(expect_hive_num_get)).split('箱')[0]
friend_name_get = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[4]/div[3]/span'
).text
friend_name = self.tool.replace(friend_name_get)
contact_number_get = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[4]/div[4]/span'
).text
contact_number = self.tool.replace(contact_number_get)
join_date_get = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[4]/div[5]/span'
).text
join_date = self.tool.replace(join_date_get)
leave_date_get = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[4]/div[6]/span'
).text
leave_date = self.tool.replace(leave_date_get)
plant_name = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[2]/div/div'
).text
creator = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[5]/span'
).text
time.sleep(5)
while not creator != []:
driver.refresh()
time.sleep(10)
creator = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[5]/span'
).text
creator_split = [x[:-1] for x in creator.split('：')[1].split('(')]
creator_name = creator_split[0]
creator_time = creator_split[1].replace('/', '-')
swarm_info_list = self.farm.query_swarm_info(plant_name, creator_time,
creator_name)
address_info_sql = swarm_info_list[0].get('address')
altitude_sql = round(swarm_info_list[0].get('altitude'), 2)
address_sql = address_info_sql + '(海拔' + str(altitude_sql) + '米)'
vehicle_length_sql_key = swarm_info_list[0].get('vehicle_length')
vehicle_length_get_sql = self.farm.query_config_value(
10003, vehicle_length_sql_key)[0].get('value')
expect_hive_num_sql = swarm_info_list[0].get('expect_hive_num')
friend_name_sql = swarm_info_list[0].get('user_name')
contact_number_sql = swarm_info_list[0].get('contact_number')
join_date_sql = swarm_info_list[0].get('join_date')
leave_date_sql = swarm_info_list[0].get('leave_date')
self.assertEqual(address, address_sql)
self.assertEqual(vehicle_length, vehicle_length_get_sql)
self.assertEqual(int(expect_hive_num), expect_hive_num_sql)
self.assertEqual(friend_name, friend_name_sql)
self.assertEqual(contact_number, contact_number_sql)
self.assertEqual(join_date, join_date_sql)
self.assertEqual(leave_date, leave_date_sql)
swarm_id = swarm_info_list[0].get('id')
img_sql = self.farm.query_swarm_info_img(swarm_id)
img_list_sql = []
i = 0
while i < 4:
img = img_sql[i].get('url')
img_split = img.split('中')
img_list_sql = img_list_sql + list(img_split)
i = i + 1
img_path = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[1]/div[1]/div[1]/div'
)
img_path.click()
time.sleep(3)
img_list = []
i = 0
while i < 4:
img = driver.find_element_by_xpath(
'/html/body/div[2]/div/div[2]/div/div[2]/div/div/div/div[1]/img'
).get_attribute('src').split('中')
driver.find_element_by_xpath(
'/html/body/div[2]/div/div[2]/div/div[2]/div/div/div/img[3]'
).click()
img_list = img_list + list(img)
i = i + 1
img_list.reverse()
if img_list_sql == img_list:
self.log.info('--------------------照片信息核对通过！------------------')
else:
raise Exception('--------------------照片信息核对失败！------------------')
self.log.info(
"--------------------地图上的外部蜂场信息case校验通过----------------------")
driver.refresh()
time.sleep(5)
def settle_farm_info(self, driver, i):
self.log.info(
"--------------------开始执行地图上的待入驻/已入驻蜂场信息校验case----------------------"
)
swarm_name = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[2]/div[1]/div/div'
).text
address = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[2]/div[3]/span'
).text
expect_hive_num_str = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[2]/div[4]/div[2]/span'
).text
expect_hive_num = expect_hive_num_str.split('：')[1].split('箱')[0]
if i == 1:
creator = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[2]/div[6]/span'
).text
else:
creator = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[2]/div[7]/span'
).text
while not creator != []:
driver.refresh()
time.sleep(10)
creator = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div/div[5]/span'
).text
creator_split = [x[:-1] for x in creator.split('：')[1].split('(')]
create_time = creator_split[1].replace('/', '-')
swarm_sql = self.farm.query_swarm(swarm_name=swarm_name,
address=address,
expect_hive_num=expect_hive_num,
create_time=create_time)
if len(swarm_sql) != 0:
self.log.info(
"--------------------地图上的待入驻/已入驻蜂场信息case校验通过----------------------"
)
driver.refresh()
time.sleep(5)
class Tool(object):
L = Log('Tool')
@staticmethod
def parse_int(num):
"""
数字串从后往前每隔三个数字加一个逗号， 例如:1234567 ---> 1,234,567
:param num:数字串
:return:
"""
to_str = str(num)
count = 0
sumstr = ''
for one_str in to_str[::-1]:
count += 1
if count % 3 == 0 and count != len(to_str):
one_str = ',' + one_str
sumstr = one_str + sumstr
else:
sumstr = one_str + sumstr
return sumstr
@staticmethod
def replace(value):
"""
将前端默认填充的“--”转换为None
:param value:需要转换的字符
:return:
"""
value_split = str(value).split('：')
value_split_1 = value_split[1]
if value_split_1 == '--':
value = None
else:
value = value_split_1
return value
@staticmethod
def get_point(driver):
"""
随机点击canvas画布，知道点到元素点
:param driver: 驱动
:return:
"""
canvas = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[1]/canvas[1]')
canvas_style = driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[1]/canvas[1]').get_attribute(
'style')
canvas_split = canvas_style.split(';')
height = ((canvas_split[2].split(':'))[1].split('px'))[0]
width = ((canvas_split[3].split(':'))[1].split('px'))[0]
hw_split = [{
'height': int(height) / 2,
'width': int(width) / 2
}, {
'height': int(height),
'width': int(width)
}, {
'height': int(height),
'width': int(width) / 2
}, {
'height': int(height) / 2,
'width': int(width)
}]
for i in range(len(hw_split)):
height = hw_split[i].get('height')
width = hw_split[i].get('width')
try:
if driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[5]/div/div[2]/div/div/div/div'
):
print(
'------------------------已定位到canvas上的数据------------------------'
)
break
except Exception as e:
print('---------------循环点击暂未点击到元素---------------', e)
finally:
for x in range(int(height)):
for y in range(int(width)):
action_chains = ActionChains(driver)
action_chains.move_to_element(canvas)
action_chains.move_by_offset(x, y).click().perform()
y = y + 1
break
x = x + 1
break
def removeRule(self,rule):
if type(rule)==type(""):
del self.specificRules[rule]
elif type(rule)==type(Policy()):
del self.specificRules[str(rule)]
Log.policycontrol("rule removed %s"%str(rule))
class Farmmap(unittest.TestCase):
farm = FarmMap()
log = Log("开始执行").logger
tool = Tool()
farm_info = FarmInfo()
driver = webdriver.Firefox(executable_path='C:\Program Files\Mozilla Firefox\geckodriver')
driver.get("http://qa-flowers.zhuihuazu.com/login?from=%2Fdashboard%2Fmap")
# opt = FirefoxOptions()
# opt.headless = True # 设置无界面浏览器模式
def farmmap(self):
self.log.info("----------------------开始执行登录case------------------------------")
# 无界面浏览器
# driver = Firefox(options=self.opt)
# 有界面浏览器
time.sleep(5)
# 切换到手机号登录
self.driver.find_element_by_css_selector('.switchover___JHtst').click()
# 定位手机号输入框
phone = self.driver.find_element_by_id('basic_mobile')
phone.send_keys('18683346691')
# 定位验证码输入框
verifyCode = self.driver.find_element_by_id('basic_verifyCode')
verifyCode.send_keys('8888')
# 点击登录按钮
self.driver.find_element_by_css_selector('.ant-btn').click()
time.sleep(5)
# 切换页面至数据看板-蜂场蜂场页面
self.driver.get('http://qa-flowers.zhuihuazu.com/dashboard/map')
# time.sleep(10)
# self.log.info("--------------------开始执行蜂场统计数据看板模块case----------------------")
# time.sleep(10)
# self.log.info("--------------------开始执行外部蜂场统计case----------------------")
# swarm = driver.find_element_by_xpath('/html/body/div/div/section/section/main/div/div[1]/div/p[2]/span').text
# swarm_count_format = None
# sql_swarm_count = self.farm.query_swarm_count()[0]
# # 累计蜂场个数
# for key in sql_swarm_count:
# count = sql_swarm_count[key]
# # 格式化数字串
# swarm_count_format = str(self.tool.parse_int(count))
# today_count_format = None
# sql_today_new_swarm = self.farm.query_today_new_swarm()[0]
# for key in sql_today_new_swarm: # 当日新增蜂友数量
# count = sql_today_new_swarm[key]
# today_count_format = str(self.tool.parse_int(count))
# time.sleep(5)
# external_farm = driver.find_element_by_xpath('//*[@id="countTotal"]/div/p[1]/span')
# text = external_farm.text
# if swarm_count_format in text:
# self.log.info('-----------------外部蜂场累计总个数检验通过-------------------')
# if today_count_format in text:
# self.log.info('-----------------当日新增外部蜂场数据检验通过-----------------')
# else:
# raise Exception('-----------------当日新增外部蜂场个数检验未通过，请核对！-------------------')
#
# else:
# raise Exception('-----------------外部蜂场累计总个数检验未通过，请核对！-------------------')
# self.log.info("--------------------开始执行蜂友数量统计case----------------------")
# friend = driver.find_element_by_xpath('//*[@id="countTotal"]/div/p[2]/span').text
# sql_friend_have_user_id = (self.farm.query_num_bee_friend_have_user_id())[0].get('有手机号的蜂友数量')
# sql_friend_not_user_id = (self.farm.query_num_bee_friend_not_user_id())[0].get('无手机号的蜂友数量')
# sql_friend_all = str(self.tool.parse_int(sql_friend_have_user_id + sql_friend_not_user_id))
# sql_friend_today = (self.farm.query_num_bee_friend_today())[0].get('今日新增蜂友')
# sql_friend_today = self.tool.parse_int(sql_friend_today)
# if sql_friend_all in friend:
# self.log.info("--------------------累计蜂友数量统计检验通过----------------------")
# if sql_friend_today in friend:
# self.log.info("--------------------今日新增蜂友数量统计检验通过----------------------")
# else:
# raise Exception("-----------------今日新增蜂友数量统计检验未通过，请核对！-------------------")
# else:
# raise Exception("-----------------累计蜂友数量统计检验未通过，请核对！-------------------")
# self.log.info("--------------------开始执行推广人员数量统计case----------------------")
# promotion_staff = driver.find_element_by_xpath(
# '//*[@id="countTotal"]/div/p[3]/span').text
# sql_promotion_staff = str(self.tool.parse_int((self.farm.query_num_promotion_staff())[0].get('COUNT(*)')))
# if sql_promotion_staff in promotion_staff:
# self.log.info("--------------------推广人员数量统计检验通过----------------------")
# else:
# raise Exception("-----------------推广人员数量统计检验未通过，请核对！-------------------")
# self.log.info("--------------------开始执行自有蜂场数量统计case----------------------")
# nectar_num = driver.find_element_by_xpath(
# '//*[@id="countTotal"]/div/p[4]/span').text
# sql_num_nectar = str(self.tool.parse_int((self.farm.query_num_nectar())[0].get('自有蜂场总数')))
# sql_num_nectar_settled = str(self.tool.parse_int((self.farm.query_num_nectar_settled())[0].get('自有入驻蜂场数')))
# if sql_num_nectar in nectar_num:
# self.log.info("--------------------自有蜂场总数统计检验通过----------------------")
# if sql_num_nectar_settled in nectar_num:
# self.log.info("--------------------自有已入驻蜂场总数统计检验通过----------------------")
# else:
# raise Exception("-----------------自有已入驻蜂场总数统计检验未通过，请核对！-------------------")
# else:
# raise Exception("-----------------自有蜂场总数统计检验未通过，请核对！-------------------")
# self.log.info("--------------------开始执行养蜂老师数量统计case----------------------")
# beekeeper_teacher = driver.find_element_by_xpath(
# '//*[@id="countTotal"]/div/p[5]/span').text
# sql_beekeeper_teacher = str(self.tool.parse_int((self.farm.query_num_beekeeper_teacher())[0].get('自有养蜂人')))
# if sql_beekeeper_teacher in beekeeper_teacher:
# self.log.info("-------------------养蜂老师数量统计检验通过----------------------")
# else:
# raise Exception("-----------------养蜂老师数量统计检验未通过，请核对！-------------------")
# self.log.info("--------------------开始执行展示地图所有数据case----------------------")
self.log.info("--------------------开始执行地图筛选case----------------------")
# 取消选中蜂场展示的筛选枚举值：外部蜂场
# beekeeper_lis = ['//*[@id="staffMap"]/div[1]/div/div[2]/div/div/div/div/div/div[2]/div/div/div[1]/span[2]/span', '//*[@id="staffMap"]/div[1]/div/div[2]/div/div/div/div/div/div[2]/div/div/div[2]/span[2]/span']
# for i in range(len(beekeeper_lis)):
# self.driver.find_element_by_xpath(
# '//*[@id="staffMap"]/div[2]/div/div[2]/div/div/div/div/div/div[2]/div/div/div[1]/span[2]/span')
# self.driver.find_element_by_xpath('//*[@id="staffMap"]/div[1]/div/div[1]').click()
# self.driver.find_element_by_xpath(beekeeper_lis[i]).click()
# time.sleep(5)
# self.farm_info.test_farm_info(self.driver)
time.sleep(3)
self.log.info("--------------------开始执行地图上的外部蜂场信息筛选case----------------------")
farm_list = ['//*[@id="staffMap"]/div[2]/div/div[2]/div/div/div/div/div/div[2]/div/div/div[1]/span[2]/span',
'//*[@id="staffMap"]/div[2]/div/div[2]/div/div/div/div/div/div[2]/div/div/div[2]/span[2]/span',
'//*[@id="staffMap"]/div[2]/div/div[2]/div/div/div/div/div/div[2]/div/div/div[3]/span[2]/span']
for i in range(len(farm_list)):
self.driver.find_element_by_xpath('//*[@id="staffMap"]/div[2]/div/div[1]').click()
self.driver.find_element_by_xpath(
'//*[@id="staffMap"]/div[2]/div/div[2]/div/div/div/div/div/div[2]/div/div/div[1]/span[2]/span').click()
if i == 0:
self.driver.find_element_by_xpath(farm_list[i]).click()
self.tool.get_point(self.driver)
time.sleep(5)
self.farm_info.external_farm_info(self.driver)
else:
self.driver.find_element_by_xpath(farm_list[i]).click()
self.driver.find_element_by_xpath('//*[@id="staffMap"]/div[2]/div/div[1]').click()
self.tool.get_point(self.driver)
time.sleep(5)
self.farm_info.settle_farm_info(self.driver, i)
def disconnected(self, reason):
Log.info("".join([str(self._socketType)," bridge closed for ",str(self.uri)," > ",reason]))
if hasattr(self,"sockClient"):
self.sockClient.stop()
WebSocketHandler.disconnected(self, reason)
def handshakeSuccessful(self, respHeader,c):
c=list(map(lambda s: s.lstrip(),c.split(b",")))
self._socketType = None
if b"proxy" not in c:
Log.error("An incoming connection has no proxy field",False)
self.doDisconnect()
return
self.uri=base64.b64decode(self.uri)
if(b"TCP" in c):
if self._policyControl.tcpAllowed():
self._socket=SocketModeFactory.TCP.generateSocket()
self._socketType="TCP"
else:
Log.policycontrol("An incoming tcp connection was rejected: TCP socket types disabled")
self.doDisconnect()
return
if(b"UNIX" in c):
if self._policyControl.unixAllowed():
self._socket=SocketModeFactory.UNIX.generateSocket()
self._socketType="UNIX"
else:
Log.policycontrol("An incoming unix connection was rejected: UNIX socket types disabled")
self.doDisconnect()
return
if(b"UDP" in c):
if self._policyControl.udpAllowed():
self._socketType="UDP"
self._socket=SocketModeFactory.UDP.generateSocket()
else:
Log.policycontrol("An incoming udp connection was rejected: UDP socket types disabled")
self.doDisconnect()
return
if self._socketType is None:
Log.warning("Incoming request has no socket specified")
self.doDisconnect()
return
if self._socketType=="UNIX":
uri=self.uri
destination=uri
else:
port=self.uri[self.uri.rfind(b":")+1:]
uri=self.uri[:self.uri.rfind(b":")]
destination=(uri,int(port))
#policy control
authStrings = list( filter(lambda elem: len(elem)>10 and ( chr(elem[0])=="H" or chr(elem[0])=="S") ,c))
if self._policyControl.hasAccess(self._socketType,self.requestHeader[b'Origin'],destination, authStrings):
self.connectRemote(destination,respHeader,c)
else:
Log.policycontrol("Access denied for: %s connection to %s"%(self._socketType,destination))
self.doDisconnect(False)
def hasAccess(self,type_,src_,destination,auth):
'''
checks if the script has access
@param type_: TCP,UDP,UNIX
@param src: the source url of the script [is null if local]
@param destination: the destination url/ip to connect to
@param auth:
'''
#Log.debug("checking policy: \n type: %s \n src: %s \n dest: %s \n auth: %s"%(type_,src_, destination,auth))
d_uri, d_port = Policies.splitURI(destination)
src = src_.decode()
if src == "null":
src = "localhost"
incomingRequestPolicy = Policy("", d_uri, d_port,src, type_)
#print(str(self.policies))
matchcount=0
matchaction = None
for k,rule in self.policies.specificRules.items():
if self.matches(rule,incomingRequestPolicy):
if matchcount != 0 and matchaction != rule.action:
Log.warning("multiple rules with conflicting actions detected: %s"%k)
matchcount += 1
matchaction = rule.action
if matchaction != None:
return self.__proceed(matchaction,incomingRequestPolicy)
Log.debug("passed specific")
#no specific rule found:
#testing for:
# trustedSource
# trustedDest
# localSource
# general rule
SALTLEN = 8
for authElem in auth:
# trustedSource
if chr(authElem[0]) == "S":
authString1 = Authentication.hash(b"", authElem[1:SALTLEN+1])[1].encode()
if authString1 == authElem[SALTLEN+1:]:
#trusted source detected
Log.policycontrol("Sourcekey detected")
return self.__proceed(self.policies.trustedSource,incomingRequestPolicy,"sourcekey")
Log.debug("passed srckey")
# trustedDest
if chr(authElem[0]) == "H":
deststr = d_uri+":"+str(d_port)
authString1 = Authentication.hash(deststr.encode(), authElem[1:SALTLEN+1])[1].encode()
authString2 = Authentication.hash(d_uri.encode(), authElem[1:SALTLEN+1])[1].encode()
if authString1 == authElem[SALTLEN+1:] or authString2 == authElem[SALTLEN+1:]:
Log.policycontrol("Hostkey detected")
return self.__proceed(self.policies.trustedDest,incomingRequestPolicy,"hostkey")
Log.debug("passed hostkey")
# localSource
if src == b"localhost":
return self.__proceed(self.policies.localSource,incomingRequestPolicy)
Log.debug("passed local")
# general rule
return self.__proceed(self.policies.unknownPolicyRule,incomingRequestPolicy)
Log.debug("passed general")
return False
class Bee(object):
L = Log("ConfigInformationSql")
db = DataBaseOperate()
class FarmMap(object):
L = Log("ConfigInformationSql")
db = DataBaseOperate()
def query_swarm_count(self):
"""
查询外部蜂场数量
:return:
"""
sql = """SELECT
Count( * )
FROM
`fc-bee`.t_swarm_info si
WHERE
si.is_delete = 0;"""
return self.db.operate(host_ip, sql)
def query_today_new_swarm(self):
"""
今日新增外部蜂场
:return:
"""
sql = """SELECT Count( * )
FROM `fc-bee`.t_swarm_info
WHERE is_delete = 0
AND lat IS NOT NULL
AND date_format(create_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d');"""
return self.db.operate(host_ip, sql)
def query_num_bee_friend_have_user_id(self):
"""
累计蜂友总数，开通账号的蜂友只统计 蜂友\养蜂总监\老师, 所有未开通账号的蜂友(不区分账号来源，区分账号是否注销)
有手机号的蜂友数量
:return:
"""
sql = """SELECT count(DISTINCT tbf.user_id) AS '有手机号的蜂友数量'
FROM `fc-bee`.t_bee_friend tbf
LEFT JOIN `fc-bee`.t_user_role AS tur ON tur.user_id = tbf.user_id AND tur.is_delete = 0
WHERE tbf.is_delete = 0
AND tbf.status <> 3
AND tbf.user_id IS NOT NULL
AND (tur.role_code IS NULL OR tur.role_code IN (1000, 1002, 1003));"""
return self.db.operate(host_ip, sql)
def query_num_bee_friend_not_user_id(self):
"""
累计蜂友总数，开通账号的蜂友只统计 蜂友\养蜂总监\老师, 所有未开通账号的蜂友(不区分账号来源，区分账号是否注销)
无手机号的蜂友数量
:return:
"""
sql = """SELECT count(1) AS '无手机号的蜂友数量'
FROM `fc-bee`.t_bee_friend
WHERE is_delete = 0
AND user_id IS NULL;"""
return self.db.operate(host_ip, sql)
def query_num_bee_friend_today(self):
"""
今日新增蜂友（不包含注销的，有手机号的蜂友+无手机号的蜂友）
:return:
"""
sql = """SELECT count(1) AS '今日新增蜂友'
FROM `fc-bee`.t_bee_friend
WHERE is_delete = 0
AND status <> 3
AND to_days(now()) = to_days(create_time);"""
return self.db.operate(host_ip, sql)
def query_num_promotion_staff(self):
"""
推广人员数量
:return:
"""
sql = """SELECT COUNT(*) # 推广人员数量
FROM `fc-bee`.t_bee_friend AS tbf
INNER JOIN `fc-bee`.t_user_role AS tur ON tbf.user_id = tur.user_id
AND tur.is_delete = 0
AND tur.role_code IN (1006)
# , 1004, 1005
WHERE
# tbf.lat IS NOT NULL AND # 推广人员位置
tbf.is_delete = 0
AND tbf.status <> 3;"""
return self.db.operate(host_ip, sql)
def query_num_nectar(self):
"""
自有蜂场总数（只统计待入驻/已完成的未删除的)
:return:
"""
sql = """SELECT count(1) AS '自有蜂场总数'
FROM `fc-bee`.t_nectar_source
WHERE is_delete = 0;"""
return self.db.operate(host_ip, sql)
def query_num_nectar_settled(self):
"""
自有入驻蜂场数（只统计已入驻的未删除的)
:return:
"""
sql = """SELECT count(1) AS '自有入驻蜂场数'
FROM `fc-bee`.t_nectar_source
WHERE is_delete = 0
AND status = 2;"""
return self.db.operate(host_ip, sql)
def query_num_beekeeper_teacher(self):
"""
自有养蜂人（不包含注销的，有手机号的蜂友）
:return:
"""
sql = """SELECT
count(1) AS '自有养蜂人'
FROM `fc-bee`.t_user_role tur
LEFT JOIN `fc-bee`.t_bee_friend tbf ON tbf.user_id = tur.user_id
WHERE tur.role_code IN (1002, 1003)
AND tbf.status <> 3
AND tbf.is_delete = 0
AND tur.is_delete = 0;"""
return self.db.operate(host_ip, sql)
def query_swarm_info(self, plant_name, create_time, user_name):
"""
外部蜂场信息
:param plant_name: 当前蜜源名称
:param create_time: 创建时间
:param user_name: 采集人真实姓名
:return:
"""
sql = """SELECT
i.*
FROM
`fc-bee`.t_swarm_info i
LEFT JOIN `world-user`.t_user u ON i.creator_id = u.id
WHERE
i.is_delete = 0
AND i.cur_nectar_type IN (SELECT sp.`code` FROM `fc-bee`.t_nectar_source_plant sp WHERE sp.is_delete = 0 AND sp.plant_name = '%s')
AND i.create_time LIKE '%s%%%%'
AND u.username = '******';""" % (plant_name, create_time,
user_name)
return self.db.operate(host_ip, sql)
def query_config_value(self, code, value):
"""
查询配置文件value
:param code: 配置类型
:param value:配置key
:return:
"""
sql = """SELECT
c.`value`
FROM
`fc-bee`.t_config c
WHERE
c.is_delete = 0
AND c.`code` = '%s'
AND c.`key` = '%s';""" % (code, value)
return self.db.operate(host_ip, sql)
def query_swarm_info_img(self, swarm_id):
"""
查询外部蜂场照片信息
:param swarm_id: 外部蜂场id
:return:
"""
sql = """SELECT
a.url
FROM
`fc-bee`.t_swarm_attach a
WHERE
a.is_delete = 0
AND a.swarm_id = '%s';""" % swarm_id
return self.db.operate(host_ip, sql)
def query_swarm(self, swarm_name, address, expect_hive_num, create_time):
"""
通过指定特殊字段值查询是否存在蜂场数据
:param swarm_name: 蜂场名称
:param address: 蜂场详细地址
:param expect_hive_num: 预计投放箱数
:param create_time: 创建时间
:return:
"""
sql = """SELECT
*
FROM
`fc-bee`.t_nectar_source
WHERE
`name` = '%s'
AND `address` = '%s'
AND `expect_hive_num` = '%s'
AND `create_time` LIKE '%s%%%%';""" % (
swarm_name, address, expect_hive_num, create_time)
return self.db.operate(host_ip, sql)
class Bee(object):
L = Log("ConfigInformationSql")
db = DataBaseOperate()
class DataBaseOperate(object):
L = Log("DataBaseOperate")
def operate(self, host, sql):
if host == "192.168.62.244":
user, password, port = "farm", "HEFAWW", 3306
# elif host == "39.104.65.76":
# user, password, port = "qa", "eYKvb5LXczUh7285UJt2T8070NkP", 3306
# elif host == "132.232.47.119":
# user, password, port = "ms", "MiaoShu@2018", 3306
# elif host == "39.104.28.40":
# user, password, port = "ms", "ms@test_2019", 3306
# elif host == "161.117.1.134":
# user, password, port = "root", "jj8ePh5ZHnb3dmtGaPl2", 3306
# elif host == "47.88.222.252":
# user, password, port = "qa", "vFLDbvhvxhR.4uK26fLa0lLnVS44S0", 3306
# elif host == "192.168.62.73":
# user, password, port = "root", "Knight01", 3306
elif host == "192.168.62.211":
user, password, port = "farm", "WorldFarm", 3306
# elif host == "203.6.234.220":
# user, password, port = "xinheng", "v1LDwAUWzEgWM", 3306
# elif host == "47.74.225.79":
# user, password, port = "qa", "vFLDbvhvxhR.4uK26fLa0lLnVS44S0", 3306
else:
raise Exception("IP域名错误")
db = pymysql.connect(host=host,
port=port,
user=user,
passwd=password)
con = db.cursor(cursor=pymysql.cursors.DictCursor)
try:
# 此处新增 单次连接执行多条SQL的功能, 兼容书写时首尾多输入空格的情况
sql_list = sql.strip().split(";")
try:
# 此处兼容以分号结尾的单句SQL仍返回一维列表
sql_list.remove('')
except ValueError as e:
self.L.logger.error(e)
raise Exception("SQL请以分号 ; 结束")
if len(sql_list) < 2:
con.execute(sql)
self.L.logger.debug(sql)
effect_row = con.rowcount
if sql.lower().startswith('select'):
self.L.logger.debug(sql)
# if effect_row != 1:
# self.L.logger.info(sql)
# else:
# pass
self.L.logger.debug("影响行数 %s" % effect_row)
else:
pass
results = con.fetchall()
db.commit()
# print(results)
for result in results:
for fields in result:
if isinstance(result[fields], datetime.datetime):
result[fields] = str(result[fields].strftime('%Y-%m-%d %H:%M:%S'))
elif isinstance(result[fields], datetime.date):
result[fields] = str(result[fields].strftime('%Y-%m-%d'))
elif isinstance(result[fields], decimal.Decimal):
result[fields] = float(result[fields])
else:
results = []
for sql in sql_list:
if sql != '':
con.execute(sql)
self.L.logger.debug(sql)
effect_row = con.rowcount
if sql.lower().startswith('select'):
self.L.logger.debug(sql)
# if effect_row != 1:
# self.L.logger.info(sql)
# else:
# pass
else:
pass
self.L.logger.debug("影响行数 %s" % effect_row)
results.append(con.fetchall())
db.commit()
else:
pass
for result in results:
for r in result:
for fields in r:
if isinstance(r[fields], datetime.datetime):
r[fields] = str(r[fields].strftime('%Y-%m-%d %H:%M:%S'))
elif isinstance(r[fields], datetime.date):
r[fields] = str(r[fields].strftime('%Y-%m-%d'))
elif isinstance(r[fields], decimal.Decimal):
r[fields] = float(r[fields])
con.close()
# if sql.lower().startswith('select'):
self.L.logger.debug("\n" + json.dumps(results, ensure_ascii=False,
sort_keys=True, indent=2, separators=(',', ': ')))
# else:
# pass
return results
except Exception as e:
db.rollback()
self.L.logger.error(e)
raise KeyError(e)
