def verify_code(request):
code = request.GET.get('code', '')
verify_code = request.session.get('verifycode', "").lower()
aeskey = request.session.get('aeskey', '')
if aeskey:
decry_str = decrypt(aeskey)
req_timestamp = decry_str.split(':')[2]
if time.time() - float(req_timestamp) > 3600:
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "重置链接超时"
})
if code == verify_code:
decry_str = decrypt(aeskey)
username = decry_str.split(':')[0]
now_time = decry_str.split(':')[2]
new_key = encrypt(':'.join([username, code, now_time]))
request.session['aeskey'] = 'haha'
return Response(status=status.HTTP_200_OK,
data={
"result": new_key,
"error": "验证码校验成功"
})
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "验证码不正确"
})
def fetch_data(self, from_date):
"""
抓取指定日期的数据
:param from_date:
:return:
"""
cv_dict = {}
for host, port, user, password, grid_id in self.db_list:
try:
self.grid_id = grid_id
fetcher = GetDataManager(host=host,
port=int(port),
user=user,
password=decrypt(password))
except MySQLError as err:
continue
siteid_arr = StationInfo.objects.filter(
grid=grid_id).values_list("company_id")
db_info = DataBaseInfo.objects.filter(
db_name="letaotrailcenter").filter(grid_id=grid_id)
db_qc = db_info.values_list("db_address", "db_port", "db_username",
"db_pwd").distinct()
if len(db_qc) > 0:
try:
self.db_letao = pymysql.connect(
host=db_qc[0][0],
user=db_qc[0][2],
password=decrypt(db_qc[0][3]),
database='letaotrailcenter',
port=int(db_qc[0][1]),
charset="utf8",
connect_timeout=5)
self.cursor_letao = self.db_letao.cursor()
except:
continue
pv_data = fetcher.get_pv_num(each=from_date,
grid_id=self.grid_id,
siteid_arr=siteid_arr,
cursor_letao=self.cursor_letao)
result = {}
for pp4 in pv_data:
pk4 = pp4[0]
pv4 = pp4[1]
if pk4 in result:
result[pk4]["pv"] = pv4
else:
result[pk4] = {"pv": pv4}
for key, values in result.items():
company_id = key
visitor_num = values.get('pv', 0)
cv_dict.update({company_id: visitor_num})
return cv_dict
def reset(request):
passwd = sha.hash(request.GET.get('password', ''))
if not passwd:
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "密码输入为空!"
})
# aeskey = request.session.get('aeskey', '')
aeskey = request.GET.get('new_key', '')
if aeskey:
decry_str = decrypt(aeskey)
username = decry_str.split(':')[0]
req_timestamp = decry_str.split(':')[2]
if time.time() - float(req_timestamp) > 3600:
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "重置链接超时"
})
else:
try:
with connect_ldap() as ldap_conn:
ldap_conn.search(
search_base='ou=Users,dc=xiaoneng,dc=cn',
search_filter='(objectClass=inetOrgPerson)',
search_scope=SUBTREE,
attributes=['userPassword', 'cn'])
for entry in ldap_conn.entries:
if entry.entry_attributes_as_dict['cn'][0] == username:
ldap_conn.modify(
entry.entry_dn,
{'userpassword': [(MODIFY_REPLACE, passwd)]})
if ldap_conn.result['result'] == 0:
return Response({
"result": True,
"error": "密码重置成功"
})
else:
data = {
"result": False,
"error": "str(ldap_conn.result['message'])"
}
return Response(
status=status.HTTP_400_BAD_REQUEST,
data=data)
except Exception as e:
traceback.print_exc()
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": str(e.args)
})
else:
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "未获取到令牌"
})
def get_db_conn(self, dbname):
"""获得kf库的mysql连接对象"""
db = self.grid.db_info.get(db_name="kf")
return Connection(
database=dbname,
host=db.db_address,
port=db.db_port,
user=db.db_username,
password=decrypt(db.db_pwd),
)
def reception_groups(request):
"""小能的客服组保存在bj-v4的t2d_syssetting表中，对应site_id为kf_8008
从中获取到接待组的id和名称，返回给前端
"""
db_info = DataBaseInfo.objects.get(grid__grid_name="bj-v4", db_name="kf")
conn = Connection(database="kf",
host=db_info.db_address,
user=db_info.db_username,
password=decrypt(db_info.db_pwd),
port=int(db_info.db_port))
sql = "SELECT name, id FROM t2d_syssetting WHERE siteid='kf_8008'"
reception_groups = conn.query(sql)
conn.close()
return JsonResponse(reception_groups, safe=False)
def handle(self, *args, **options):
# 连接池
conn_poll = {}
for db_info in DataBaseInfo.objects.all().filter(db_name='kf'):
try:
print(db_info.db_address)
conn = Connection(database=db_info.db_name,
host=db_info.db_address,
port=int(db_info.db_port),
user=db_info.db_username,
password=decrypt(db_info.db_pwd))
conn_poll[db_info.db_address] = conn
except:
print(db_info.db_name)
print(db_info.db_address)
continue
sites = OpenStationManage.objects.all()
for site in sites:
try:
print(site.station_info.company_id)
site_id = site.station_info.company_id
db_host = site.station_info.grid.db_info.get(
db_name="kf").db_address
conn = conn_poll[db_host]
res = conn.get(
"select t2dserver from t_wdk_sit where sitid = '%s'" %
site_id)
if res:
grp_name = parse.urlparse(
res.t2dserver).netloc.split("-in")[0]
site.station_info.server_grp = ServerGroup.objects.get(
group_name__contains=grp_name)
site.station_info.save()
except:
print('error:' + site.station_info.company_id)
continue
for conn in conn_poll.values():
conn.close()
# 修复已有咨询量数据中的servergroup字段信息
site_id_server_grp_ship = dict(StationInfo.objects.all().values_list(
"company_id", "server_grp__group_name"))
for data in InquiriesData.objects.all():
try:
data.server_grp = site_id_server_grp_ship[data.company_id]
data.save()
except:
print('error:' + data.company_id)
continue
def fetch_history(self, from_date, to_date):
"""获取指定时间段内的咨询量
TODO：单独封装成一个自定义命令，类似 python manage.py fetch_inquires ### ###
"""
for host, port, user, password in self.db_list:
try:
fetcher = InquiresFetcher(host=host,
port=int(port),
user=user,
password=decrypt(password))
except MySQLError as err:
logger.warning(err)
continue
dates = dates_during(from_date=from_date, to_date=to_date)
for each in dates:
logger.info(f"正在获取数据库：{host}， 日期：{each}")
result = {}
data = fetcher.fetch_date(each)
for site_id, channel, num in data:
result.setdefault(site_id, {})
result[site_id].setdefault(channel, 0)
result[site_id][channel] += num
for site_id, values in result.items():
try:
station = OpenStationManage.objects.all().get(
station_info__company_id=site_id)
server_grp = station.station_info.server_grp.group_name if station.station_info.server_grp else 0
grid = Grid.objects.get(
group__group_name=server_grp).grid_name
except:
continue
n = 0
for channel, num in values.items():
n = n + 1
InquiriesData.objects.create(
inquires_num=num,
date=each,
channel=channel,
company_id=site_id,
industry=station.company_info.industry.industry,
deploy_way=station.station_info.deploy_way,
cli_version=station.station_info.cli_version,
server_grp=server_grp,
open_id=station.id,
grid=grid,
)
def _fetch_data(self, which):
"""获取数据，传入today或者yesterday"""
result = {}
for host, port, user, password in self.db_list:
try:
fetcher = InquiresFetcher(host=host,
port=int(port),
user=user,
password=decrypt(password))
except MySQLError as err:
logger.warning(err)
continue
data = getattr(fetcher, "fetch_" + which)()
for site_id, channel, num in data:
result.setdefault(site_id, {})
result[site_id].setdefault(channel, 0)
result[site_id][channel] += num
del fetcher
return result
def get_db_list_con(self, dbname):
queryset = DataBaseInfo.objects.filter(db_name=dbname).values(
'db_type', 'db_address', 'db_name', 'db_username', 'db_pwd',
'db_port', 'grid')
db_list_con = []
for dbinfo in queryset:
grid_dbhost = dbinfo['db_address'].strip()
grid_dbuser = dbinfo['db_username'].strip()
grid_dbpwd = dbinfo['db_pwd'].strip()
grid_dbprot = int(dbinfo['db_port'].strip())
try:
grid_dbcon = MysqldbHelper(grid_dbhost, grid_dbuser,
decrypt(grid_dbpwd), dbname,
grid_dbprot)
db_list_con.append(grid_dbcon)
except:
db_list_con.append(grid_dbhost)
logging.error('error dbhost:' + grid_dbhost)
continue
return db_list_con
def get_grid_dbcon(self, **kwargs):
gridname = kwargs.get('gridname', '')
grid_id = kwargs.get('grid_id', '')
dbname = kwargs.get('dbname', '')
if gridname and dbname:
queryset = DataBaseInfo.objects.filter(db_name=dbname).filter(grid__grid_name=gridname) \
.values('db_type', 'db_address', 'db_name', 'db_username', 'db_pwd', 'db_port', 'grid')
elif grid_id and dbname:
queryset = DataBaseInfo.objects.filter(db_name=dbname).filter(grid__pk=grid_id) \
.values('db_type', 'db_address', 'db_name', 'db_username', 'db_pwd', 'db_port', 'grid')
else:
return False
grid_dbhost = queryset[0]['db_address'].strip()
grid_dbuser = queryset[0]['db_username'].strip()
grid_dbpwd = queryset[0]['db_pwd'].strip()
grid_dbprot = int(queryset[0]['db_port'].strip())
grid_dbcon = MysqldbHelper(grid_dbhost, grid_dbuser,
decrypt(grid_dbpwd), dbname, grid_dbprot)
if grid_dbcon == False:
return False
return grid_dbcon
def get_conn_poll(self, **options):
# 连接池
conn_poll = {}
data_query = DataBaseInfo.objects.all().filter(db_name='kf')
if options.get('site_id'):
if isinstance(options.get('site_id'), list):
data_query = data_query.filter(
grid__station_info__company_id__in=options['site_id'])
else:
data_query = data_query.filter(
grid__station_info__company_id=options['site_id'])
for db_info in data_query:
try:
conn = Connection(database=db_info.db_name,
host=db_info.db_address,
port=int(db_info.db_port),
user=db_info.db_username,
password=decrypt(db_info.db_pwd))
conn_poll[db_info.db_address] = conn
except:
continue
return conn_poll
def logic(self):
starttime_ing = (int(
time.mktime(
time.strptime(time.strftime('%Y-%m-%d %H:%M:%S'),
'%Y-%m-%d %H:%M:%S'))) - 24 * 60 * 60) * 1000
starttime = int(
time.mktime(
time.strptime(
time.strftime('%Y-%m-%d') + ' 00:00:00',
'%Y-%m-%d %H:%M:%S'))) * 1000
endtime = int(
time.mktime(
time.strptime(
time.strftime('%Y-%m-%d') + ' 23:59:59',
'%Y-%m-%d %H:%M:%S'))) * 1000
ret = self.get_grid_dbcon()
_ret = []
consult_num_total, consulting_num_total = 0, 0
visitor_num_total = 0
_letao_address_list = []
for k in ret:
consult_num, consulting = 0, 0
db_address = k['db_address']
db_username = k['db_username']
db_pwd = k['db_pwd']
db_port = k['db_port']
if k['db_name'] == 'kf':
try:
db_name = 'kf'
dbcon_kf = MysqldbHelper(db_address, db_username,
decrypt(db_pwd), db_name,
int(db_port))
if dbcon_kf == False:
continue
sql = f"SELECT count(*) as num from t2d_chatscene where starttime>={starttime} and endtime<={endtime}"
consult = dbcon_kf.select(sql)
consult_num_total = int(consult_num_total +
consult[0]['num'])
consult_num = consult[0]['num']
sql = f"SELECT count(*) as num from t2d_chatscene where starttime>={starttime_ing} and endtime=0"
consulting = dbcon_kf.select(sql)
consulting_num_total = int(consulting_num_total +
consulting[0]['num'])
except:
continue
elif k['db_name'] == 'letaotrailcenter':
try:
if _letao_address_list.count(db_address) > 0:
continue
db_name = 'letaotrailcenter'
dbcon_letao = MysqldbHelper(db_address, db_username,
decrypt(db_pwd), db_name,
int(db_port))
if dbcon_letao == False:
continue
date = time.strftime('%Y%m%d')
sql = f'select table_name from information_schema.tables where table_name LIKE "t2d_%_ip_hits_{date}"'
table_name = dbcon_letao.select(sql)
visitor_num = 0
for key in table_name:
sql = f"select count(*) as num from {key['table_name']}"
visitor = dbcon_letao.select(sql)
visitor_num = int(visitor_num + visitor[0]['num'])
visitor_num_total = visitor_num_total + visitor_num
_letao_address_list.append(db_address)
except:
continue
else:
continue
if consult_num >= 25000:
state = "灾难"
elif consult_num >= 18000:
state = "告警"
else:
state = "正常"
strr = {
"grid_name": k['grid_name'],
"consult_num": consult_num,
"threshold": 18000,
"state": state
}
_ret.append(strr)
# redis存入正在咨询量
consulting_str = f"{time.strftime('%H:%M:%S')}|{consulting_num_total}"
consulting_key = "consulting" + time.strftime('%Y%m%d')
yest_consulting_key = "consulting" + (
datetime.date.today() -
datetime.timedelta(days=1)).strftime('%Y%m%d')
if Redis_base().exists(yest_consulting_key):
Redis_base().delete(yest_consulting_key)
Redis_base().lpush(consulting_key, consulting_str)
consulting_json = []
consulting_len = Redis_base().llen(consulting_key)
for k in range(consulting_len):
try:
data = Redis_base().lindex(consulting_key, k)
if data:
data_lsit = str(data).split('|')
data_dict = {data_lsit[0]: data_lsit[1]}
consulting_json.append(data_dict)
except:
consulting_json = consulting_json
sorted_ret = sorted(_ret,
key=lambda _ret: _ret['consult_num'],
reverse=True)
ret_str = {
"consult": {
"total": consult_num_total,
"grid_num": sorted_ret
},
"visitor": {
"total": visitor_num_total
},
"consulting": consulting_json
}
Redis_base().set("classic_gjf", ret_str)
return ret_str
def reset(request):
mode = get_login_model()
mod = login_model
ldap_exit = User.objects.filter(user_profile__create_source=1).filter(
username=request.user.get_username()).exists()
local_exit = User.objects.filter(user_profile__create_source=2).filter(
username=request.user.get_username()).exists()
if mod != 2:
passwd = sha.hash(request.GET.get('password', ''))
if not passwd:
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "密码输入为空!"
})
# aeskey = request.session.get('aeskey', '')
aeskey = request.GET.get('new_key', '')
if aeskey:
decry_str = decrypt(aeskey)
username = decry_str.split(':')[0]
req_timestamp = decry_str.split(':')[2]
if time.time() - float(req_timestamp) > 3600:
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "重置链接超时"
})
else:
try:
with connect_ldap() as ldap_conn:
ldap_conn.search(
search_base=mode.user_ldapsearch,
search_filter='(objectClass=inetOrgPerson)',
search_scope=SUBTREE,
attributes=['userPassword', 'cn'])
for entry in ldap_conn.entries:
if entry.entry_attributes_as_dict['cn'][
0] == username:
ldap_conn.modify(entry.entry_dn, {
'userpassword': [(MODIFY_REPLACE, passwd)]
})
if ldap_exit:
user_info = User.objects.filter(
user_profile__create_source=1
).get(username=request.user.get_username())
user_info.password = passwd
user_info.save()
if local_exit:
user_info = User.objects.filter(
user_profile__create_source=2
).get(username=request.user.get_username())
user_info.password = passwd
user_info.save()
if ldap_conn.result['result'] == 0:
return Response({
"result": True,
"error": "密码重置成功"
})
else:
data = {
"result": False,
"error":
"str(ldap_conn.result['message'])"
}
return Response(
status=status.HTTP_400_BAD_REQUEST,
data=data)
except Exception as e:
traceback.print_exc()
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": str(e.args)
})
else:
return Response(status=status.HTTP_400_BAD_REQUEST,
data={
"result": False,
"error": "未获取到令牌"
})
if mod == 2:
user_info = User.objects.filter(user_profile__create_source=2).get(
username=request.user.get_username())
user_info.password = passwd
user_info.save()
return Response({"result": True, "error": "密码重置成功"})
def get_historys(self, from_date, to_date):
"""
获取指定日期的访客量数据
:param from_date:
:param to_date:
:return:
"""
for host, port, user, password, grid_id in self.db_list:
try:
self.grid_id = grid_id
fetcher = GetDataManager(host=host,
port=int(port),
user=user,
password=decrypt(password))
except MySQLError as err:
continue
dates = dates_during(from_date=from_date, to_date=to_date)
for each in dates:
siteid_arr = StationInfo.objects.filter(
grid=grid_id).values_list("company_id")
db_info = DataBaseInfo.objects.filter(
db_name="letaotrailcenter").filter(grid_id=grid_id)
db_qc = db_info.values_list("db_address", "db_port",
"db_username",
"db_pwd").distinct()
data_create_list = []
if len(db_qc) > 0:
try:
self.db_letao = pymysql.connect(
host=db_qc[0][0],
user=db_qc[0][2],
password=decrypt(db_qc[0][3]),
database='letaotrailcenter',
port=int(db_qc[0][1]),
charset="utf8",
connect_timeout=5)
self.cursor_letao = self.db_letao.cursor()
except:
continue
pv_data = fetcher.get_pv_num(
each=each,
grid_id=self.grid_id,
siteid_arr=siteid_arr,
cursor_letao=self.cursor_letao)
result = {}
for pp4 in pv_data:
pk4 = pp4[0]
pv4 = pp4[1]
if pk4 in result:
result[pk4]["pv"] = pv4
else:
result[pk4] = {"pv": pv4}
for key, values in result.items():
stations = OpenStationManage.objects.all().filter(
station_info__company_id=key)
company_id = key
visitor_num = values.get('pv', 0)
if stations:
for station in stations:
industry = station.company_info.industry.industry
deploy_way = station.station_info.deploy_way
grid = Grid.objects.get(
station_info__company_id=key).grid_name
else:
industry = 0
deploy_way = 0
grid = 0
date_dict = {
"company_id": company_id,
"industry": industry,
"deploy_way": deploy_way,
"date": each,
"visitor_num": visitor_num,
"grid": grid
}
data_create_list.append(VistorData(**date_dict))
else:
continue
ret = VistorData.objects.bulk_create(data_create_list)
return f'{from_date}至{to_date}历史数据同步成功'
def checkpwd(request):
pwd = request.GET['pwd']
possword = decrypt(pwd)
return HttpResponse(possword)
