kedong/smartfarming/views/pest_image.py

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:
                if indentify_result != "0":
                    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
                        try:
                            pest_dict[pest_name] = int(tuple_result[1])
                        except:
                            pass 
            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"))
            ind_url = data.get("indentify_photo")
            if ind_url:
                if ind_url.startswith("http"):
                    save_data["图像"].append(ind_url)
                else:
                    save_data["图像"].append(config_dict["image_url"]["discern"] + ind_url)
            else:
                save_data["图像"].append("")
        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
                    try:
                        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)
                    except:
                        pass
        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