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)