def ping_result_list(request):
context = {}
time_begin, _ = getDateRange(-2)
time_end, _ = getDateRange(-1)
time_range = (time_begin, time_end)
cost_group_list = LinkPingCostStepAggregate.objects.all().order_by('id')
cost_hour_group_list = LinkPingHourAggregate.objects.all().order_by('id')
l = {}
for cost_hour in cost_hour_group_list:
temp = []
for f in cost_hour._meta.fields[2::]:
val = cost_hour.serializable_value(f.attname)
temp.append(str(val))
l[cost_hour.direction] = ','.join(temp)
# print(l)
ping_result_high_loss_list = LinkPingTest.objects.raw(
__queryline_ping(
'loss',
'HAVING high_loss_cnt > {}'.format(__PING_FILTER_HIGH_LOSS_CNT)),
time_range)
ping_result_high_cost_list = LinkPingTest.objects.raw(
__queryline_ping(
'cost',
'HAVING high_cost_cnt > {}'.format(__PING_FILTER_HIGH_LOSS_CNT)),
time_range)
context['cost_group_list'] = cost_group_list
context['cost_hour_group_list'] = l
context['high_loss_list'] = ping_result_high_loss_list
context['high_cost_list'] = ping_result_high_cost_list
return render(request, 'ping_result.html', context)
def port_error_list(request):
order_field = request.GET.get('order_field', 'crc')
time_begin, time_end = getDateRange(-2) # 默认-2
time_range = (time_begin, time_end)
porterror_query = __queryline(order_field)
porterror_all_list = PortErrorDiff.objects.raw(
porterror_query, (time_begin, time_end, time_begin, time_end))
page_of_objects, page_range = pages(request, porterror_all_list)
my_tasks_cnt = 0
if request.user.is_authenticated:
my_tasks_cnt = PortErrorFixRecord.objects.filter(
worker=request.user.first_name, status=False).count()
context = {}
context['records'] = page_of_objects.object_list
context['page_of_objects'] = page_of_objects
context['page_range'] = page_range
context['time_begin'] = timezone.datetime.strftime(time_range[0],
'%Y-%m-%d+%H:%M:%S')
context['time_end'] = timezone.datetime.strftime(time_range[1],
'%Y-%m-%d+%H:%M:%S')
context['order_field'] = order_field
context['my_tasks_cnt'] = my_tasks_cnt
context['porterror_search_form'] = PortErrorSearchForm()
return render(request, 'port_error_list.html', context)
def ping_result_detail(request):
context = {}
source_device = request.GET.get('source_device', '')
target_device = request.GET.get('target_device', '')
target_ip = request.GET.get('target_ip', '')
time_begin, _ = getDateRange(-2)
time_end, _ = getDateRange(-1)
ping_detail_list = LinkPingTest.objects.filter(
source_device=source_device,
target_device=target_device,
record_time__range=(time_begin, time_end)).order_by('-record_time')
page_of_objects, page_range = pages(request, ping_detail_list)
context['records'] = page_of_objects.object_list
context['page_of_objects'] = page_of_objects
context['page_range'] = page_range
context['search_paras'] = '&source_device={}&target_device={}'.format(
source_device, target_device)
return render(request, 'ping_result_detail.html', context)
def natpool_list(request):
context = {}
natpool_all_list = NatPoolUsage.objects.filter(
record_time__range=getDateRange(-2)).annotate(
nat_total=(F('device1_nat_usage') +
F('device2_nat_usage'))).order_by(
F('nat_total').desc())
page_of_objects, page_range = pages(request, natpool_all_list)
context['records'] = page_of_objects.object_list
context['page_of_objects'] = page_of_objects
context['page_range'] = page_range
context['natpool_search_form'] = NatPoolSearchForm()
return render(request, 'natpool.html', context)
def moudle_list(request):
# 默认展示前3天数据
moudle_all_list = OpticalMoudleDiff.objects.filter(
record_time__range=getDateRange(-3))
# moudle_all_list = OpticalMoudleDiff.objects.all()
page_of_objects, page_range = pages(request, moudle_all_list)
context = {}
context['records'] = page_of_objects.object_list
context['page_of_objects'] = page_of_objects
context['page_range'] = page_range
context['moudle_search_form'] = MoudleSearchForm()
return render(request, 'moudle_list.html', context)
def oneway_list(request):
time_begin, time_end = getDateRange(-1)
time_range = (time_begin, time_end)
oneway_all_list = OneWayDevice.objects.raw(
__QUERY_ONEWAY_LIST + __ONEWAY_ORDER_FIELD, time_range)
page_of_objects, page_range = pages(request, oneway_all_list)
context = {}
context['records'] = page_of_objects.object_list
context['page_of_objects'] = page_of_objects
context['page_range'] = page_range
# context['time_begin'] = timezone.datetime.strftime(time_begin, '%Y-%m-%d+%H:%M:%S')
# context['time_end'] = timezone.datetime.strftime(time_end, '%Y-%m-%d+%H:%M:%S')
context[
'search_paras'] = 'time_begin={}&time_end={}&device_name={}'.format(
timezone.datetime.strftime(time_begin, '%Y-%m-%d+%H:%M:%S'),
timezone.datetime.strftime(time_end, '%Y-%m-%d+%H:%M:%S'), '')
context['oneway_search_form'] = OneWaySearchForm()
context['oneway_tag_form'] = OneWayTagForm()
return render(request, 'oneway_list.html', context)
def export_moudle(request):
device_name = request.GET.get('device_name', '')
status = request.GET.get('status', '')
time_begin = request.GET.get('time_begin', '')
print(request.GET.get('time_begin'))
time_end = request.GET.get('time_end', '')
print(request.GET.get('time_end'))
if time_begin == '' or time_end == '':
# 默认下载前一天的数据
time_range = getDateRange(-1)
else:
time_begin = timezone.datetime.strptime(time_begin,
'%Y-%m-%d %H:%M:%S')
time_end = timezone.datetime.strptime(time_end, '%Y-%m-%d %H:%M:%S')
time_range = (time_begin, time_end)
print(time_range)
if device_name == status == '':
moudle_all_list = OpticalMoudleDiff.objects.filter(
record_time__range=time_range)
else:
if device_name != '': # device_name != '' status == ''
moudle_all_list = OpticalMoudleDiff.objects.filter(
device_name__icontains=device_name,
record_time__range=time_range)
elif status != '': # device_name == '' status != ''
moudle_all_list = OpticalMoudleDiff.objects.filter(
status=status, record_time__range=time_range)
else:
moudle_all_list = OpticalMoudleDiff.objects.filter(
record_time__range=time_range)
output = exportXls(OpticalMoudleDiff._meta.fields, moudle_all_list,
('record_time', ))
response = FileResponse(
open(output, 'rb'), as_attachment=True,
filename="moudle_result.xls") # 使用Fileresponse替代以上两行
return response
def dashboard(request):
context = {}
# 设备信息
device_count = Device.objects.all().count()
device_ipman_count = Device.objects.filter(
device_network__icontains='IPMAN').count()
device_cmnet_count = Device.objects.filter(
device_network__icontains='CMNET').count()
device_oth_count = device_count-device_ipman_count-device_cmnet_count
context['device_count'] = device_count
context['device_ipman_count'] = device_ipman_count
context['device_cmnet_count'] = device_cmnet_count
context['device_oth_count'] = device_oth_count
# 模块信息
moudle_new_count = OpticalMoudleDiff.objects.filter(
status='NEW', record_time__range=getDateRange(-1)).count()
moudle_miss_count = OpticalMoudleDiff.objects.filter(
status='MISS', record_time__range=getDateRange(-1)).count()
moudle_ch_count = OpticalMoudleDiff.objects.filter(
status='CH', record_time__range=getDateRange(-1)).count()
context['moudle_new_count'] = moudle_new_count
context['moudle_miss_count'] = moudle_miss_count
context['moudle_ch_count'] = moudle_ch_count
# IP地址信息
ip_count = IpRecord.objects.all().count()
ip_private_count = IpRecord.objects.filter(ip_type='private').count()
ip_public_count = IpRecord.objects.filter(
ip_type__icontains='public').count()
context['ip_count'] = ip_count
context['ip_private_count'] = ip_private_count
context['ip_public_count'] = ip_public_count
if ip_count != 0:
context['ip_private_ratio'] = ip_private_count/ip_count*100
context['ip_public_ratio'] = ip_public_count/ip_count*100
else:
context['ip_private_ratio'] = 0
context['ip_public_ratio'] = 0
# 端口错包信息
crc_port_count = PortErrorDiff.objects.filter(
stateCRC__gt=0, record_time__range=getDateRange(-2)).count()
crc_max_speed = PortErrorDiff.objects.filter(
record_time__range=getDateRange(-2)).aggregate(Max('stateCRC'))
ipv4head_port_count = PortErrorDiff.objects.filter(
stateIpv4HeadError__gt=0, record_time__range=getDateRange(-2)).count()
ipv4head_max_speed = PortErrorDiff.objects.filter(
record_time__range=getDateRange(-2)).aggregate(Max('stateIpv4HeadError'))
context['crc_port_count'] = crc_port_count
context['crc_max_speed'] = crc_max_speed
context['ipv4head_port_count'] = ipv4head_port_count
context['ipv4head_max_speed'] = ipv4head_max_speed
# 单通设备检查
oneway_count = OneWayDevice.objects.filter(record_time__range=getDateRange(-1)).count()
oneway_devices = OneWayDevice.objects.filter(record_time__range=getDateRange(-1)).values('device_name').annotate(port_cnt=Sum('port')).order_by('-port_cnt')
context['oneway_devices'] = oneway_devices
context['oneway_count'] = oneway_count
# nat地址池
heavy_load_pair_devices = NatPoolUsage.objects.filter(record_time__range=getDateRange(-2)).annotate(nat_total=(F('device1_nat_usage')+F('device2_nat_usage'))).order_by(F('nat_total').desc())[0:5]
context['heavy_load_pair_devices'] = heavy_load_pair_devices
# ping
cost_hour_group_list = LinkPingHourAggregate.objects.all().order_by('id')
l = {}
for cost_hour in cost_hour_group_list:
temp = []
for f in cost_hour._meta.fields[2::]:
val = cost_hour.serializable_value(f.attname)
temp.append(str(val))
l[cost_hour.direction] = ','.join(temp)
context['cost_hour_group_list'] = l
# 业务开通情况
# 当天情况
ip_mod_aggregate = IPMod.objects.filter(mod_time__range=getDateRange(-1)).values('mod_type').annotate(Count('ip', distinct=True)).order_by()
ip_alloc_num = IPAllocation.objects.filter(alc_time__range=getDateRange(-1)).values('ip').annotate(Count('ip', distinct=True)).order_by().count()
context['ip_mod_aggregate'] = ip_mod_aggregate
context['ip_alloc_num'] = ip_alloc_num
# 历史7天开通情况
ip_mod_7day_raw_query = '''
SELECT id, mod_type,
sum(if(date_format(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '%%Y-%%m-%%d')=mt, 1, 0)) AS d0,
sum(if(date_format(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '%%Y-%%m-%%d')=mt, 1, 0)) AS d1,
sum(if(date_format(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '%%Y-%%m-%%d')=mt, 1, 0)) AS d2,
sum(if(date_format(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '%%Y-%%m-%%d')=mt, 1, 0)) AS d3,
sum(if(date_format(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '%%Y-%%m-%%d')=mt, 1, 0)) AS d4,
sum(if(date_format(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%%Y-%%m-%%d')=mt, 1, 0)) AS d5,
sum(if(date_format(DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%%Y-%%m-%%d')=mt, 1, 0)) AS d6
FROM (SELECT id, ip, mod_type, DATE_FORMAT(mod_time, '%%Y-%%m-%%d') AS mt FROM MR_REC_ip_mod_record GROUP BY ip, mod_type, mt) AS mod_agg
GROUP BY mod_type
ORDER BY mod_type ASC
'''
ip_mod_dict = {}
ip_mod_7day_result = IPMod.objects.raw(ip_mod_7day_raw_query)
for mod_data in ip_mod_7day_result:
temp = []
for f in ip_mod_7day_result.columns[2::]:
val = mod_data.serializable_value(f)
temp.append(str(val))
ip_mod_dict[mod_data.mod_type] = ','.join(temp)
context['ip_mod_dict'] = ip_mod_dict
ip_alloc_7day_raw_query = '''
SELECT id, COUNT(DISTINCT(ip)) AS cnt, DATE_FORMAT(alc_time, '%%Y-%%m-%%d') AS alct
FROM mr_rec_ip_allocation
WHERE alc_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
GROUP BY alc_time ORDER BY alct ASC
'''
# alloc_date_list = []
# ip_alloc_dict = {}
# temp_ip_alloc_list = []
# ip_alloc_7day_result = IPAllocation.objects.raw(ip_alloc_7day_raw_query)
# for alloc_data in ip_alloc_7day_result:
# alloc_date_list.append(alloc_data.alct)
# temp_ip_alloc_list.append(str(alloc_data.cnt))
# context['ip_alloc_dict'] = {'alloc': ','.join(temp_ip_alloc_list)}
# 其他
today_time = datetime.datetime.now()
context['time_begin'] = '%d-%d-%d+00:00:00' % (
today_time.year, today_time.month, today_time.day)
context['time_end'] = '%d-%d-%d+23:59:59' % (
today_time.year, today_time.month, today_time.day)
return render(request, 'dashboard.html', context)