from io import BytesIO import json import time import pandas as pd import datetime import xlwt from django.views.generic import ListView from django.http import HttpResponse from django.utils.encoding import escape_uri_path from django.conf import settings from django.db.models import Q from smartfarming.models.worm_forecast import MongoCBDphoto from smartfarming.models.device import MongoDevice, MongoXYCBData, MongoSCDData from smartfarming.models.weather import QXZdata_New, MongoQXZ_Conf from smartfarming.api.views.forecast.all_dict import insect_dict from kedong.decoration import kedong_deco, PortError config_dict = settings.CONFIG class PestImageSourceExport(ListView): def get(self, request, *args, **kwargs): # 测报灯害虫图像信息溯源数据导出 parameters = request.GET d_ids = parameters.get("d_ids") start_time = int(parameters.get("start_time")) end_time = int(parameters.get("end_time")) select_name = parameters.get("pest_name") filename = parameters.get("filename") page = int(parameters.get("page","1")) page_size = 9999999 amend = parameters.get("amend","0") # 获取统计设备列表 if d_ids: d_id_list = [int(d_id) for d_id in d_ids.split(",")] d_id_dicts = {} device_queryset = MongoDevice.objects.filter(id__in=d_id_list) for device_object in device_queryset: d_id_dicts[device_object.id] = {"location":device_object.province+device_object.city+device_object.district, "device_id":device_object.device_id, "device_code":device_object.device_code, "device_name":device_object.device_name if device_object.device_name else "测报灯" } else: raise PortError("d_ids","参数缺失") # 统计 photo_queryset = MongoCBDphoto.objects.filter(device_id__in=list(d_id_dicts.keys()),photo_status=1,addtime__range=(start_time,end_time)) if amend == "0": photo_queryset = photo_queryset.filter(~Q(indentify_result="")).order_by("-addtime") else: photo_queryset = photo_queryset.filter(~Q(indentify_result="") | ~Q(mark="")).order_by("-addtime") pest_image_data = [] for photo_object in photo_queryset: # 单张照片结果 indentify_result = photo_object.indentify_result mark = photo_object.mark pest_string = "" pest_dict = {} if amend == "1" and mark: if mark == "[]": continue else: mark = json.loads(mark) for index,label in enumerate(mark): pest_name = label.get("text") pest_num = 1 if pest_name not in pest_string: if index != 0: pest_string += "、" pest_string += pest_name if pest_name in pest_dict: pest_dict[pest_name] += pest_num else: pest_dict[pest_name] = 1 else: for index,result in enumerate(indentify_result.split("#")) : if index != 0: pest_string += "、" tuple_result = result.split(",") pest_name = insect_dict.get(tuple_result[0],"未命名") pest_string+=pest_name pest_dict[pest_name] = int(tuple_result[1]) if select_name and select_name not in pest_string: continue addtime = photo_object.addtime __d_id = int(photo_object.device_id) pest_image_data.append({"deviceId":d_id_dicts[__d_id]["device_id"], "deviceName":d_id_dicts[__d_id]["device_name"], "pestName":pest_string, "addtime":addtime, "location":d_id_dicts[__d_id]["location"], "img_url":photo_object.addr, "indentify_photo":photo_object.indentify_photo, "pest_dict":pest_dict }) return_data = pest_image_data[(page-1)*page_size:page*page_size] save_data = dict(设备ID=[],设备名称=[],害虫名称=[],上报时间=[],设备位置=[],图像=[]) for data in return_data: save_data["设备ID"].append(data.get("deviceId")) save_data["设备名称"].append(data.get("deviceName")) save_data["害虫名称"].append(data.get("pestName")) uptime = datetime.datetime.fromtimestamp(data.get("addtime")).strftime("%Y-%m-%d %H:%M:%S") save_data["上报时间"].append(uptime) save_data["设备位置"].append(data.get("location")) save_data["图像"].append(config_dict["image_url"]["discern"] + data.get("indentify_photo")) df = pd.DataFrame(data=save_data) output = BytesIO() df.to_excel(output,index=False) output.seek(0) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment;filename={}.xls'.format(escape_uri_path(filename)) response.write(output.getvalue()) return response class PestBaseDataExport(ListView): # 虫害基础数据 def get(self, request, *args, **kwargs): ''' 测报灯害虫基础数据导出 ''' parameters = request.GET d_ids = parameters.get("d_ids") start_time = int(parameters.get("start_time")) end_time = int(parameters.get("end_time")) select_name = parameters.get("pest_name") page = int(parameters.get("page","1")) page_size = int(parameters.get("page_size",999999)) amend = parameters.get("amend","0") filename = parameters.get("filename") # 获取统计设备列表 if d_ids: d_id_list = [int(d_id) for d_id in d_ids.split(",")] else: raise PortError("d_ids","参数缺失") # 统计 photo_queryset = MongoCBDphoto.objects.filter(device_id__in=d_id_list,photo_status=1,addtime__range=(start_time,end_time)) if amend == "0": photo_queryset = photo_queryset.filter(~Q(indentify_result="")).order_by("-addtime") else: photo_queryset = photo_queryset.filter(~Q(indentify_result="") | ~Q(mark="")).order_by("-addtime") pest_base_data = [] for photo_object in photo_queryset: # 单张照片结果 indentify_result = photo_object.indentify_result mark = photo_object.mark addtime = photo_object.addtime pest_dict = {} if amend == "1" and mark: mark = json.loads(mark) for label in mark: pest_name = label.get("text") pest_num = 1 if select_name and select_name not in pest_name: continue if pest_name in pest_dict: pest_dict[pest_name] += pest_num else: pest_dict[pest_name] = pest_num for label_key,label_value in pest_dict.items(): pest_base_data.append(dict(pest_name=label_key,pest_num=label_value,addtime=addtime)) else: for result in indentify_result.split("#"): tuple_result = result.split(",") pest_name = insect_dict.get(tuple_result[0],"未命名") if select_name and select_name not in pest_name: continue pest_num = int(tuple_result[1]) pest_dict=dict(pest_name=pest_name,pest_num=pest_num,addtime=addtime) pest_base_data.append(pest_dict) return_data = pest_base_data[(page-1)*page_size:page*page_size] save_data = dict(害虫名称=[],害虫数量=[],上报时间=[]) for data in return_data: save_data["害虫名称"].append(data.get("pest_name")) save_data["害虫数量"].append(data.get("pest_num")) uptime = datetime.datetime.fromtimestamp(data.get("addtime")).strftime("%Y-%m-%d %H:%M:%S") save_data["上报时间"].append(uptime) df = pd.DataFrame(data=save_data) output = BytesIO() df.to_excel(output,index=False) output.seek(0) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment;filename={}.xls'.format(escape_uri_path(filename)) response.write(output.getvalue()) return response # 气象站数据导出接口 class QxzDataExport(ListView): """ 气象站数据导出接口 参数说明： device_id 必传 设备号 begin 非必传 开始时间 end 非必传 结束时间 filename 必传 文件名称 """ def get(self,request): device_id = request.GET.get('device_id') f_tbegin = request.GET.get('begin') f_tend = request.GET.get('end') filename = request.GET.get('filename') device_data = QXZdata_New.objects.filter(device_id=device_id).order_by("id") if f_tbegin: device_data = device_data.filter(uptime__gte=f_tbegin) if f_tend: device_data = device_data.filter(uptime__lte=f_tend) book = xlwt.Workbook() # 创建一个excel对象 sheet = book.add_sheet('Sheet1',cell_overwrite_ok=True) # 添加一个sheet页 tes = [] if device_data: try: for x in device_data: otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(x.uptime)) te = [] data = [x.e1,x.e2,x.e3,x.e4,x.e5, x.e6,x.e7,x.e8,x.e9,x.e10, x.e11,x.e12,x.e13,x.e14, x.e15,x.e16,x.e17,x.e18, x.e19,x.e20,x.e21,x.e22, x.e23,x.e24,x.e25,x.e26, x.e27,x.e28,x.e29,x.e30,otherStyleTime] conf_list = [i for i in data if i !='' and i != None ] for y in conf_list: if y: num = str(y.split("#")[0]) te.append(num) tes.append(te) x = MongoQXZ_Conf.objects.get(device_id=device_id) conf = [x.e1,x.e2,x.e3,x.e4,x.e5, x.e6,x.e7,x.e8,x.e9,x.e10, x.e11,x.e12,x.e13,x.e14, x.e15,x.e16,x.e17,x.e18, x.e19,x.e20,x.e21,x.e22, x.e23,x.e24,x.e25,x.e26, x.e27,x.e28,x.e29,x.e30] conf_list = [i for i in conf if i !='' ] tep = [] for x in conf_list: if x: tite = x.replace('#', '') tep.append(tite) tep.append("采集时间") for i in range(len(tep)): # 遍历列 sheet.write(0,i,tep[i]) # 将title数组中的字段写入到0行i列中 for y in range(len(tes)): for i in range(len(tes[y])): sheet.write(y+1,i,tes[y][i]) except: sheet.write(0,0,"错误的时间段") else: sheet.write(0,0,"该时间段内没数据清重新设置时间段") # 将title数组中的字段写入到0行i列中 #写出到IO output = BytesIO() book.save(output) # 重新定位到开始 output.seek(0) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment;filename={}.xls'.format(escape_uri_path(filename)) response.write(output.getvalue()) return response class CbdExport(ListView): """ 设备数据导出接口 device_id 设备号(str) start_time 开始时间(时间戳) end_time 结束时间(时间戳) filename 导出的文件名(str) device_type 设备类型 """ def get(self,request): device_id = request.GET.get("device_id") start_time = request.GET.get("start_time") end_time = request.GET.get("end_time") filename = request.GET.get("filename") device_type = request.GET.get("device_type") # username = request.GET.get("username") book = xlwt.Workbook() sheet = book.add_sheet('Sheet1',cell_overwrite_ok=True) # 添加一个sheet页 try: devices = MongoDevice.objects.get(id=device_id) except: data = {"code":"401","type":"未找到该设备"} data = json.dumps(data,ensure_ascii=False) return HttpResponse(data) if device_type == "4": models = MongoXYCBData elif device_type == "2": models = MongoSCDData else: data = {"code":"403","type":"暂不支持该设备类型"} data = json.dumps(data,ensure_ascii=False) return HttpResponse(data) if device_id: device_data = models.objects.filter(device_id=device_id).order_by("-id") else: data = {"code":"400","type":"参数为空"} data = json.dumps(data,ensure_ascii=False) return HttpResponse(data) if start_time: device_data = device_data.filter(addtime__range=(int(start_time), int(end_time))) sta2 = [] if device_data: for x in device_data: times = time.localtime(x.addtime) times = time.strftime("%Y-%m-%d %H:%M:%S",times) sta2.append({"data": eval(x.device_data), "upl_time":times}) else: sheet.write(1+1,0,"暂无数据") # 性诱性诱测报 if device_type == "4": title = [ "设备ID", "设备开关", "工作状态", "环境温度(℃)", "环境湿度(%)", "充电电压(V)", "电池电压(V)", "充电状态", "电池状态", "诱虫次数", "信号强度", "上报时间" ] for i in range(len(title)): # 遍历列 sheet.write(0, i, title[i]) # 将title数组中的字段写入到0行i列中 for x in range(len(sta2)): # 遍历列表 try: x_d = sta2[x]["data"] ds = "关机" if x_d["ds"] == 0 else "开机" ws = "待机" if x_d["ws"] == 0 else "工作" try: at = x_d["at"] except Exception as e: at = "0" try: ah = x_d["ah"] except Exception as e: ah = "0" infr_ct = x_d["infr_ct"] volt_ct = x_d["volt_ct"] try: cv = x_d["cv"] except Exception as e: cv = "0" try: bv = x_d["bv"] except Exception as e: bv = "0" cs = "非充电" if x_d["cs"] == "0" else "充电" if x_d["bs"] == "0": bs = "正常" elif x_d["bs"] == "1": bs = "欠压" else: bs = "过压" csq = str(x_d["csq"]) dver = x_d["dver"] try: lat = x_d["lat"] lng = x_d["lng"] except Exception as e: lat = "0" lng = "0" table_value = { "设备ID": devices.device_id, "设备开关": ds, "工作状态": ws, "环境温度(℃)": at, "环境湿度(%)": ah, "充电电压(V)": cv, "电池电压(V)": bv, "充电状态": cs, "电池状态": bs, "诱虫次数": infr_ct, "信号强度": csq, "上报时间": sta2[x]["upl_time"] } for i, v in enumerate(title): sheet.write(x + 1, i, table_value.get(v)) except: continue # 杀虫灯 elif device_type == "2": title = ["设备ID", "设备开关", "工作状态", "雨控状态", "温控状态", "倾倒状态", "电击次数", "定时时长(h)", "环境温度(℃)", "环境湿度(%)", "充电电压(V)", "电池电压(V)","信号强度", "上报时间", "经度", "纬度"] for i in range(len(title)): # 遍历列 sheet.write(0, i, title[i]) # 将title数组中的字段写入到0行i列中 for x in range(len(sta2)): # 遍历列表 try: if int(sta2[x]["data"]["ds"]) == 0: ds = "关机" else: ds = "开机" if int(sta2[x]["data"]["ws"]) == 0: ws = "待机" elif int(sta2[x]["data"]["ws"]) == 2: ws = "充电" else: ws = "工作" if int(sta2[x]["data"]["rps"]) == 0: rps = "正常" else: rps = "雨控" if int(sta2[x]["data"]["tps"]) == 0: tps = "正常" else: tps = "温控" if int(sta2[x]["data"]["dps"]) == 0: dps = "正常" else: dps = "保护" try: lat = sta2[x]["data"]["lat"] lng = sta2[x]["data"]["lng"] except: lat = 0 lng = 0 sheet.write(x + 1, 0, devices.device_id) # 将equip_id写入到第x+1行，第0列中 sheet.write(x + 1, 1, ds) # 设备开关 sheet.write(x + 1, 2, ws) # 工作状态 sheet.write(x + 1, 3, rps) # 雨控状态 sheet.write(x + 1, 4, tps) # 温控状态 sheet.write(x + 1, 5, dps) # 倾倒状态 sheet.write(x + 1, 6, sta2[x]["data"]["ct"]) # 电击次数 sheet.write(x + 1, 7, sta2[x]["data"]["tt"]) # 定时时长 sheet.write(x + 1, 8, sta2[x]["data"]["at"]) # 环境温度 sheet.write(x + 1, 9, sta2[x]["data"]["ah"]) # 环境湿度 sheet.write(x + 1, 10, sta2[x]["data"]["cv"]) # 充电电压 sheet.write(x + 1, 11, sta2[x]["data"]["bv"]) # 电池电压 sheet.write(x + 1, 12, sta2[x]["data"]["csq"]) # 信号强度 sheet.write(x + 1, 13, sta2[x]["upl_time"]) # 上报时间 sheet.write(x + 1, 14, lng) # 经度 sheet.write(x + 1, 15, lat) # 纬度 except: continue sheet.col(0).width = 256 * 18 sheet.col(2).width = 256 * 11 sheet.col(3).width = 256 * 11 sheet.col(4).width = 256 * 13 sheet.col(8).width = 256 * 11 sheet.col(9).width = 256 * 11 sheet.col(10).width = 256 * 20 # 写出到IO output = BytesIO() book.save(output) # 重新定位到开始 output.seek(0) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment;filename={}.xls'.format(escape_uri_path(filename)) response.write(output.getvalue()) return response