scripting_tools/bigdata_django/zhijian/qxz_zhijian_new.py

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file '.\ui文件\气象站质检工具.ui'
#
# Created by: PyQt5 UI code generator 5.15.4
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.

# pyinstaller -n "气象站(v3.2)" -D -w f:\image_down_code\qxz_zhijian.py -i .\LOGO.ico

from PyQt5 import QtCore, QtGui, QtWidgets

import os
import sys
import re
import ast
from urllib import parse
import json
import time
import datetime
import requests
import uuid
import pymongo
import pymysql
from collections import defaultdict, Counter
from xlrd import open_workbook
from xlsxwriter.workbook import Workbook
import openpyxl


save_filename = ""
pwd_str = "yf6021"
toji_format = {
    'font_name' : '宋体',
    'font_size': 14,
    'font_color': 'black',
    'text_wrap': True,
    'bold': False,
    'fg_color': '92D050',
    'align': 'center',
    'valign': 'vcenter',
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}
title_format = {
    'font_name' : '宋体',
    'font_size': 12,
    'bold': True,
    'align': 'center',
    'valign': 'vcenter',
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}
merge_title_format = {
    'font_name' : '宋体',
    'font_size': 26,
    'bold': True,
    'align': 'center',
    'valign': 'vcenter',
    "fg_color": "8DB4E2",
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}
explain_formal = {
    'font_name' : '宋体',
    'font_size': 11,
    'font_color': 'black',
    'text_wrap': True,
    'align': 'justify',
    'valign': 'vcenter',
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}
formal_format = {
    'font_name' : '宋体',
    'font_size': 11,
    'font_color': 'black',
    'fg_color': '77E88C',
    'text_wrap': True,
    'align': 'center',
    'valign': 'vcenter',
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}
common_format = {
    'font_name' : '宋体',
    'font_size': 11,
    'font_color': 'black',
    "fg_color": 'E7EC73',
    'text_wrap': True,
    'align': 'center',
    'valign': 'vcenter',
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}
error_format = {
    'font_name' : '宋体',
    'font_size': 11,
    'font_color': 'black',
    "fg_color": 'F4746A',
    'text_wrap': True,
    'align': 'center',
    'valign': 'vcenter',
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}

default_formal = {
    'font_name': '宋体',
    'font_size': 11,
    'font_color': 'black',
    'text_wrap': True,
    'align': 'center',
    'valign': 'vcenter',
    'border': 1,
    'top': 1,
    'left': 1,
    'right': 1,
    'bottom': 1
}


class QxzCand:
    def __init__(self):
        self.func_dict = {
            "空气温度": self.get_cond_101,
            "空气湿度": self.get_cond_102,
            "土壤温度": self.get_cond_106,
            "土壤含水率": self.get_cond_107,
            "风速": self.get_cond_108,
            "风向": self.get_cond_109,
            "降雨量累计": self.get_cond_115,
            "光合有效辐射": self.get_cond_123,
            "气压": self.get_cond_127,
            "盐分": self.get_cond_154,
            "电导率": self.get_cond_211,
            "磷": self.get_cond_156,
            "日照时数": self.get_cond_113,
            "PM2.5": self.get_cond_145,
            "PM10": self.get_cond_146,
            "总辐射": self.get_cond_105,
            "负氧离子": self.get_cond_162,
            "二氧化碳": self.get_cond_120,
            "蒸发量": self.get_cond_110,
            "土壤PH": self.get_cond_128,
            "紫外辐射": self.get_cond_138,
            "水质pH": self.get_cond_228,
            "水位": self.get_cond_230,
            "水质电导率": self.get_cond_178,
            "光照度": self.get_cond_112,
            "硫化氢": self.get_cond_219,
            "水温": self.get_cond_144,
            "浊度": self.get_cond_151,
            "氨气": self.get_cond_153,
            "氧气": self.get_cond_220,
            "PM100": self.get_cond_203,
            "露点温度": self.get_cond_202,
            "氮": self.get_cond_155,
            "钾": self.get_cond_157,
            "溶解氧": self.get_cond_148,

        }

        self.cond_msg_dict = {}
        for k, v in self.func_dict.items():
            self.cond_msg_dict[k] = self.get_cond_msg

        self.head_dict = {
            "ID": "/",
            "检验项目": "判定标准",
            "经度": "合格条件(绿色)：\n113°46′13″±30″范围内",
            "纬度": "合格条件(绿色)：\n35°1′45″±30″范围内",
            "固件版本号": "合格条件(绿色)：\n用检验时的输入作为导入的标准.\n/ 无法显示,替换为 -",
            "电压": "合格条件(绿色)：\n11.5~12.5",
            "信号强度": "合格条件(绿色)：\n>14",
            "上传数据条数": "合格条件(绿色)：\n1、大于7条\n2、两条数据时间间隔小于20分钟的出现频次2次以内、两条数据间隔大于40分钟出现频率2次以内",
            "风速": "合格条件(绿色)：\n1、0<示值<10\n2、有1条合格 即可",
            "风向": "合格条件(绿色)：\n1、0<示值<360\n2、有1条合格即可",
            "降雨量累计": "合格条件(绿色)：\n1、0<示值\n2、00：00示值归零\n3、有1条合格即可",
            "土壤含水率": "合格条件(绿色)：\n1、0<示值<100\n2、有1条合格即可",
            "土壤温度": "合格条件(绿色)：\n1.≠0且在-5~35°C之间\n2、有1条合格即可",
            "盐分": "合格条件(绿色)：\n1、0<示值<1000#2、有1条合格即可",
            "电导率": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即",
            "氮": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "磷": "合格条件(绿色)：\n1、0<示值且不能为负值\n2、有1条合格即可",
            "光合有效辐射": "合格条件(绿色)：\n1、0<示值且不能为负值\n2、有1条合格即可",
            "日照时数": "合格条件(绿色)：\n1、0.1≤示值≤0.2且不能为负值2、有1条合格即可",
            "PM2.5": "合格条件(绿色)：\n1、0<示值且不能为负值\n2、有1条合格即可",
            "PM10": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "负氧离子": "合格条件(绿色)：\n1、0<示值<1000\n2、有1条合格即可",
            "总辐射": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "二氧化碳": "合格条件(绿色)：\n1、400<示值<1500\n2、有1条合格即可",
            "蒸发量": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "土壤PH": "合格条件(绿色)：\n1、6~8\n2、有1条合格即可",
            "钾": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "紫外辐射": "合格条件(绿色)：\n1、0<示 值\n2、有1条合格即可",
            "空气温度": "合格条件(绿色)：\n1.-10~35°C，但不允许为0°C\n2、有1条合格即可",
            "空气湿度": "合格条件(绿色)：\n1、0<示值<100\n2、有1条合格即可",
            "水质pH": "合格条件(绿色)：\n1、6≤示值≤8\n2、有1条合格即可",
            "水位": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "水质电导率": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "气压": "合格条件(绿色)：\n1、1000<示值<1100\n2、有1条合格即可",
            "光照度": "合格条件(绿色)：\n1、0<示值<200000\n2、有1条合格即可",
            "硫化氢": "合格条件(绿色)：\n1、示值=0\n2、有1条合格即可",
            "水温": "合格条件(绿色)：\n1.0~30°C，但不允许为0°C\n2、 有1条合格即可",
            "溶解氧": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "浊度": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "氨气": "合格条件(绿色)：\n1、示值=0\n2、有1条合格即可",
            "氧气": "合格条件(绿色)：\n1、0<示值\n2、有1条合格即可",
            "PM100": "合格条件(绿色)：\n1、0<示值\n2 、有1条合格即可",
            "露点温度": "合格条件(绿色)：\n1.-10~35°C，但不允许为0°C\n2、有1条合格即可",
            "sim卡信息": "合格条件(绿色)：\n1、大于等于180天单元格显示绿色\n2、小于180天大于等于30天单元格显示黄色、\n3、小于30天单元格显示为红色",
            "单台合格数": "合格条件(绿色)：\n所有显示数值全部在合格范围内，则判定为合格"
        }

    def get_cond_lat_msg(self, info):
        return {
            "status": int(info["status"]),
            "msg": f"纬度为: {info['lat']}"
        }

    def get_cond_lng_msg(self, info):
        return {
            "status": int(info["status"]),
            "msg": f"经度为: {info['lng']}"
        }

    def get_cond_volt_msg(self, info):
        return self.get_cond_msg(info)

    def get_cond_rssi_msg(self, info):
        return self.get_cond_msg(info)

    def get_cond_version_msg(self, info):
        status = info["status"]
        value = info["dver_num"]
        old_value = info["old_value"]
        device_version = value.replace('/', '-')
        old_version = old_value.replace('/', '-')
        return {
            "status": status,
            "msg": f"设备:{device_version}, 输入:{old_version}"
        }

    def get_cond_sim_msg(self, info):
        return info

    def get_time_uptime_msg(self, info):
        total_count = int(info["total_count"])
        uptime_20 = int(info["uptime_20"])
        uptime_40 = int(info["uptime_40"])

        msg = f"数据量：{total_count},间隔<20分钟：{uptime_20},间隔>40分钟：{uptime_40}"
        status = 0
        if total_count > 7 and uptime_20 <= 2 and uptime_40 <= 2:
            status = 1
        return {
            "status": status,
            "msg": msg
        }

    def get_cond_msg(self, info):
        """获取输出结果"""
        max_value = info['max_value']
        min_value = info['min_value']
        status = int(info['status'])
        msg = f"最大值:{max_value},最小值:{min_value}"
        return {"status": status, "msg": msg}

    def template_func(self, ek, cond_str):
        """获取模板函数"""
        cond = {
            "$function": {
                "args": [f"${ek}"],
                "lang": "js",
                "body": f"""
function(values_list){{
    let max_value = Math.max(...values_list);
    let min_value = Math.min(...values_list);
    let status = 0;
    for (let i = 0; i < values_list.length; i++){{
        let v = values_list[i];
        if ({cond_str}){{
            status = 1;
            break;
        }}
    }}
    return {{"max_value": max_value, "min_value": min_value, "status": status}}
}}
"""
            }
        }
        return cond

    def get_cond_101(self, ek):
        """获取空气温度条件"""
        cond = self.template_func(ek, "v != 0 && -10 <= v && v <= 35")
        return cond

    def get_cond_102(self, ek):
        """获取空气湿度条件"""
        cond = self.template_func(ek, "0 < v && v < 100")
        return cond

    def get_cond_106(self, ek):
        """获取土壤温度条件"""
        cond = self.template_func(ek, "v != 0 && -5 <= v && v <= 35")
        return cond

    def get_cond_107(self, ek):
        """获取土壤含水率条件"""
        cond = self.template_func(ek, "0 < v && v < 100")
        return cond

    def get_cond_108(self, ek):
        """获取风速条件"""
        cond = self.template_func(ek, "0 < v && v < 10")
        return cond

    def get_cond_109(self, ek):
        """获取风向条件"""
        cond = self.template_func(ek, "0 < v && v < 360")
        return cond

    def get_cond_115(self, ek):
        """获取降雨量累计条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_123(self, ek):
        """获取光合有效辐射条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_127(self, ek):
        """获取气压条件"""
        cond = self.template_func(ek, "1000 < v && v < 1100")
        return cond

    def get_cond_154(self, ek):
        """获取盐分条件"""
        cond = self.template_func(ek, "0 < v && v < 1000")
        return cond

    def get_cond_211(self, ek):
        """获取电导率条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_155(self, ek):
        """获取氮条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_156(self, ek):
        """获取磷条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_113(self, ek):
        """获取日照时数条件"""
        cond = self.template_func(ek, "0.1 <= v && v <= 0.2")
        return cond

    def get_cond_145(self, ek):
        """获取PM2.5条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_146(self, ek):
        """获取PM10条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_105(self, ek):
        """获取总辐射条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_162(self, ek):
        """获取负氧离子条件"""
        cond = self.template_func(ek, "0 < v && v < 1000")
        return cond

    def get_cond_120(self, ek):
        """获取二氧化碳条件"""
        cond = self.template_func(ek, "400 < v && v < 1500")
        return cond

    def get_cond_110(self, ek):
        """获取蒸发量条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_128(self, ek):
        """获取土壤PH条件"""
        cond = self.template_func(ek, "6 <= v && v <= 8")
        return cond

    def get_cond_157(self, ek):
        """获取钾条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_138(self, ek):
        """获取紫外辐射条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_228(self, ek):
        """获取水质pH条件"""
        cond = self.template_func(ek, "6 <= v && v <= 8")
        return cond

    def get_cond_230(self, ek):
        """获取水位条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_178(self, ek):
        """获取水质电导率条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_112(self, ek):
        """获取光照度条件"""
        cond = self.template_func(ek, "0 < v && v < 200000")
        return cond

    def get_cond_219(self, ek):
        """获取硫化氢条件"""
        cond = self.template_func(ek, "v == 0")
        return cond

    def get_cond_144(self, ek):
        """获取水温条件"""
        cond = self.template_func(ek, "0 < v && v <= 30")
        return cond

    def get_cond_148(self, ek):
        """获取溶解氧条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_151(self, ek):
        """获取浊度条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_153(self, ek):
        """获取氨气条件"""
        cond = self.template_func(ek, "v == 0")
        return cond

    def get_cond_220(self, ek):
        """获取氧气条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_203(self, ek):
        """获取PM100条件"""
        cond = self.template_func(ek, "0 < v")
        return cond

    def get_cond_202(self, ek):
        """获取露点温度条件"""
        cond = self.template_func(ek, "-10 < v && v < 35")
        return cond




class Ui_MainWindow(object):
    """GUI界面"""
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(701, 644)
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(12)
        MainWindow.setFont(font)
        icon = QtGui.QIcon("logo.ico")
        MainWindow.setWindowIcon(icon)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.pageTitleLabel = QtWidgets.QLabel(self.centralwidget)
        self.pageTitleLabel.setGeometry(QtCore.QRect(139, 50, 429, 42))
        font = QtGui.QFont()
        font.setFamily("楷体")
        font.setPointSize(28)
        self.pageTitleLabel.setFont(font)
        self.pageTitleLabel.setTextFormat(QtCore.Qt.AutoText)
        self.pageTitleLabel.setObjectName("pageTitleLabel")
        self.inputFileLabel = QtWidgets.QLabel(self.centralwidget)
        self.inputFileLabel.setGeometry(QtCore.QRect(180, 130, 121, 21))
        self.inputFileLabel.setObjectName("inputFileLabel")
        self.inputFileEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.inputFileEdit.setGeometry(QtCore.QRect(310, 130, 151, 21))
        self.inputFileEdit.setFocusPolicy(QtCore.Qt.NoFocus)
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(8)
        self.inputFileEdit.setFont(font)
        self.inputFileEdit.setObjectName("inputFileEdit")
        self.inputFileTool = QtWidgets.QToolButton(self.centralwidget)
        self.inputFileTool.setGeometry(QtCore.QRect(470, 130, 71, 21))
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(10)
        self.inputFileTool.setFont(font)
        self.inputFileTool.setObjectName("inputFileTool")
        self.savePathLabel = QtWidgets.QLabel(self.centralwidget)
        self.savePathLabel.setGeometry(QtCore.QRect(180, 170, 121, 21))
        self.savePathLabel.setObjectName("savePathLabel")
        self.savePathEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.savePathEdit.setGeometry(QtCore.QRect(310, 170, 151, 21))
        self.savePathEdit.setFocusPolicy(QtCore.Qt.NoFocus)
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(8)
        self.savePathEdit.setFont(font)
        self.savePathEdit.setObjectName("savePathEdit")
        self.savePathTool = QtWidgets.QToolButton(self.centralwidget)
        self.savePathTool.setGeometry(QtCore.QRect(470, 170, 71, 21))
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(10)
        self.savePathTool.setFont(font)
        self.savePathTool.setObjectName("savePathTool")
        self.startTimeLabel = QtWidgets.QLabel(self.centralwidget)
        self.startTimeLabel.setGeometry(QtCore.QRect(180, 210, 121, 21))
        self.startTimeLabel.setObjectName("startTimeLabel")
        self.startTimeEdit = QtWidgets.QDateTimeEdit(self.centralwidget)
        self.startTimeEdit.setGeometry(QtCore.QRect(310, 210, 151, 21))
        self.startTimeEdit.setMaximumDateTime(QtCore.QDateTime(QtCore.QDate(2025, 12, 31), QtCore.QTime(23, 59, 59)))
        self.startTimeEdit.setMinimumDateTime(QtCore.QDateTime(QtCore.QDate(2023, 4, 24), QtCore.QTime(0, 0, 0)))
        self.startTimeEdit.setObjectName("startTimeEdit")
        self.endTimeLabel = QtWidgets.QLabel(self.centralwidget)
        self.endTimeLabel.setGeometry(QtCore.QRect(180, 250, 121, 21))
        self.endTimeLabel.setObjectName("endTimeLabel")
        self.endTimeEdit = QtWidgets.QDateTimeEdit(self.centralwidget)
        self.endTimeEdit.setGeometry(QtCore.QRect(310, 250, 151, 21))
        self.endTimeEdit.setMaximumDateTime(QtCore.QDateTime(QtCore.QDate(2025, 12, 31), QtCore.QTime(23, 59, 59)))
        self.endTimeEdit.setMinimumDateTime(QtCore.QDateTime(QtCore.QDate(2023, 4, 25), QtCore.QTime(0, 0, 0)))
        self.endTimeEdit.setObjectName("endTimeEdit")
        self.platLabel = QtWidgets.QLabel(self.centralwidget)
        self.platLabel.setGeometry(QtCore.QRect(180, 290, 121, 21))
        self.platLabel.setObjectName("dianjiLabel")
        self.platBox = QtWidgets.QComboBox(self.centralwidget)
        self.platBox.setGeometry(QtCore.QRect(310, 290, 151, 21))
        self.platBox.addItems(['大数据平台','四情平台'])
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(11)
        self.platBox.setFont(font)
        self.platBox.setObjectName("platBox")
        self.stm8vsLabel = QtWidgets.QLabel(self.centralwidget)
        self.stm8vsLabel.setGeometry(QtCore.QRect(180, 330, 121, 21))
        self.stm8vsLabel.setObjectName("stm8vsLabel")
        self.stm8vsEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.stm8vsEdit.setGeometry(QtCore.QRect(310, 330, 151, 21))
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(11)
        self.stm8vsEdit.setFont(font)
        self.stm8vsEdit.setObjectName("stm8vsEdit")
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(11)
        self.orderLabel = QtWidgets.QLabel(self.centralwidget)
        self.orderLabel.setGeometry(QtCore.QRect(180, 370, 121, 21))
        self.orderLabel.setObjectName("dverLabel")
        self.orderEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.orderEdit.setGeometry(QtCore.QRect(310, 370, 151, 21))
        font = QtGui.QFont()
        font.setFamily("Arial")
        font.setPointSize(11)
        self.orderEdit.setFont(font)
        self.orderEdit.setObjectName("dverEdit")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(300, 410, 111, 41))
        self.pushButton.setObjectName("pushButton")
        self.progressBar = QtWidgets.QProgressBar(self.centralwidget)
        self.progressBar.setGeometry(QtCore.QRect(130, 470, 491, 31))
        self.progressBar.setProperty("value", 0)
        self.progressBar.setVisible(False)
        self.progressBar.setObjectName("progressBar")
        MainWindow.setCentralWidget(self.centralwidget)
        self.retranslateUi(MainWindow)
        self.inputFileTool.clicked.connect(self.input_file_path)
        self.savePathTool.clicked.connect(self.out_save_location)
        self.pushButton.clicked.connect(self.on_click)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "气象站质检工具"))
        self.savePathTool.setText(_translate("MainWindow", "选择文件夹"))
        self.inputFileTool.setText(_translate("MainWindow", "选择文件"))
        self.pageTitleLabel.setText(_translate("MainWindow", "<html><head/><body><p><span style=\" font-weight:600; color:#3a45aa;\">云飞气象站设备质检工具</span></p></body></html>"))
        self.savePathLabel.setText(_translate("MainWindow", "|输出文件位置："))
        self.inputFileLabel.setText(_translate("MainWindow", "|输入文件位置："))
        self.startTimeLabel.setText(_translate("MainWindow", "|开始时间："))
        self.endTimeLabel.setText(_translate("MainWindow", "|结束时间："))
        self.platLabel.setText(_translate("MainWindow", "|检验平台："))
        self.stm8vsLabel.setText(_translate("MainWindow", "|主板版本号："))
        self.orderLabel.setText(_translate("MainWindow", "|任务单号："))
        self.pushButton.setText(_translate("MainWindow", "开始导出"))

    def input_file_path(self):
        file_path = QtWidgets.QFileDialog.getOpenFileName(None,"选取文件","./","All Files (*.xlsx;*.xls);;Text Files (*.txt);;ALL(*)")
        self.inputFileEdit.setText(file_path[0])
        self.inputFileEdit.setStyleSheet("color:black;")

    def out_save_location(self):
        fname = QtWidgets.QFileDialog.getExistingDirectory(None, '选取文件夹', './')
        self.savePathEdit.setText(fname)
        self.savePathEdit.setStyleSheet("color:black;")

    def on_click(self):
        file_path = self.inputFileEdit.text()
        save_path = self.savePathEdit.text()
        start_time = self.startTimeEdit.dateTime().toPyDateTime()
        end_time = self.endTimeEdit.dateTime().toPyDateTime()
        set_plat = self.platBox.currentText()
        set_stm8vs = self.stm8vsEdit.text()
        set_order = self.orderEdit.text()
        
        if not all ([file_path,save_path,start_time,end_time,set_stm8vs,set_order]):
            QtWidgets.QMessageBox.information(None, "提示", "选项未填写完整")
        elif end_time < start_time:
            QtWidgets.QMessageBox.information(None, "提示", "结束日期应在开始日期之后")
        else:
            read_dict = {}
            if file_path.split(".")[-1] == "xlsx":
                wb = openpyxl.load_workbook(file_path)
                sheet = wb[wb.sheetnames[0]]
                row_num = sheet.max_row
                d_list = []
                for row in range(2, row_num + 1):
                    cell = sheet.cell(row, 1)
                    try:
                        cell_value = cell.value.strip()
                        if cell_value:
                            d_list.append(cell.value)
                    except Exception as e:
                        continue

                read_dict["设备ID"] = d_list
            else:
                xls = open_workbook(file_path)
                sheet_object = xls.sheets()[0]
                ncols = sheet_object.ncols
                d_list = []
                col_value = sheet_object.col_values(0)
                for d_i in col_value[1:]:
                    try:
                        d_i_v = d_i.strip()
                        d_list.append(d_i_v)
                    except Exception as e:
                        continue
                read_dict["设备ID"] = d_list
            device_list = read_dict.get("设备ID")

            if device_list:
                self.inputFileTool.setEnabled(False)
                self.savePathTool.setEnabled(False)
                self.startTimeEdit.setEnabled(False)
                self.endTimeEdit.setEnabled(False)
                self.platBox.setEnabled(False)
                self.stm8vsEdit.setEnabled(False)
                self.orderEdit.setEnabled(False)
                self.pushButton.setEnabled(False)
                self.pushButton.setText("执行中...")
                self.runThread = SCDThread(device_list,save_path,start_time,end_time,set_plat,set_stm8vs,set_order)
                self.runThread.proess_signal.connect(self.set_progressbar_value)
                self.runThread.start()
                self.progressBar.setVisible(True)
            else:
                QtWidgets.QMessageBox.information(None, "提示", "输入文件无'设备ID'列或该列无数据")

    def set_progressbar_value(self, value):
        self.progressBar.setValue(value)
        if value == 100:
            QtWidgets.QMessageBox.information(None, "提示", "文件导出完毕！导出文件名:\n{}".format(save_filename))
            self.inputFileTool.setEnabled(True)
            self.inputFileEdit.setText("")
            self.savePathTool.setEnabled(True)
            self.savePathEdit.setText("")
            self.startTimeEdit.setEnabled(True)
            self.endTimeEdit.setEnabled(True)
            self.platBox.setEnabled(True)
            self.stm8vsEdit.setEnabled(True)
            self.stm8vsEdit.setText("")
            self.orderEdit.setEnabled(True)
            self.orderEdit.setText("")
            self.pushButton.setEnabled(True)
            self.pushButton.setText("开始导出")
            self.progressBar.setVisible(False)
            self.progressBar.setValue(0)
            return


class SCDThread(QtCore.QThread):
    """涉及进度条需主界面动态执行GUI,线程执行业务逻辑,避免主页面卡死"""
    proess_signal = QtCore.pyqtSignal(int)
    def __init__(self,device_list,save_path,start_time,end_time,set_plat,set_stm8vs,set_order):
        super(SCDThread, self).__init__()
        self.device_list = device_list
        self.save_path = save_path
        self.start_time = time.mktime(start_time.timetuple())
        self.end_time = time.mktime(end_time.timetuple())
        self.start_time_str = start_time.strftime("%y-%m-%d %H:%M:%S")
        self.end_time_str = end_time.strftime("%y-%m-%d %H:%M:%S")
        self.set_plat = set_plat
        self.set_stm8vs = set_stm8vs
        self.set_order = set_order

        self.user = parse.quote_plus("root")
        self.passwd = parse.quote_plus("yfkj@6020")
        self.myclient = pymongo.MongoClient("mongodb://{0}:{1}@8.136.98.49:57017/".format(self.user,self.passwd))
        self.db = self.myclient.smartfarming
        self.device_collection = self.db.sa_device
        self.qxz_collection = self.db.sa_device_qxz_data

        self.config = {
            'host': '120.27.222.26',
            'port': 3306,
            'user': 'yfwlw',
            'password': 'sql_yfkj_6019',
            'db': 'yfwlw',
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor,
        }
        self.connection = pymysql.connect(**self.config)
        self.cursor = self.connection.cursor()

        self.cond = QxzCand()
        self.mongo_ping()
    
    def mongo_ping(self):
        """mongo-ping预防连接失效"""
        # try:
        #     self.myclient.admin.command('ping')
        # except:  # "ConnectionFailure"
        self.myclient = pymongo.MongoClient("mongodb://{0}:{1}@8.136.98.49:57017/".format(self.user,self.passwd))
        self.db = self.myclient.smartfarming
        self.device_collection = self.db.sa_device
        self.qxz_collection = self.db.sa_qxz_data
        self.qxz_info_record_collection = self.db.sa_qxz_info_record
        self.qxz_base_info_collection = self.db.sa_qxz_base_info
        # self.sa_qxz_conf = self.db.
    
    def sql_ping(self):
        """mysql-ping 预防连接失效"""
        try:
            self.connection.ping()
        except:
            self.connection = pymysql.connect(**self.config)
            self.cursor = self.connection.cursor()

    def __time_dif(self,checkdatetime):
        """计算时间差"""
        nowdatetime = datetime.datetime.now()
        checkdatetime = datetime.datetime.strptime(checkdatetime, "%Y-%m-%d %H:%M:%S")
        timedif = checkdatetime - nowdatetime
        return timedif.days
    
    def device_their_platform(self,shortId):
        """确定设备所在平台以及完整设备号"""
        self.mongo_ping()
        self.sql_ping()
        regex = re.compile('.*{}$'.format(shortId))
        bd_device_dict = self.device_collection.find_one(
            filter = {"device_id":regex,"device_type_id":5},
            projection = {'_id': 0},
            sort = [('uptime', pymongo.DESCENDING)]
        )
        device_sql = "SELECT * FROM AppInfoManage_qxzstatus_new WHERE equip_id_id LIKE '%{}' ORDER BY upl_time DESC LIMIT 1;".format(shortId)
        self.cursor.execute(device_sql)
        sq_device_dict = self.cursor.fetchone()
        if bd_device_dict and sq_device_dict:
            bd_upltime = bd_device_dict["uptime"]
            sq_upltime = time.mktime(sq_device_dict["upl_time"].timetuple())
            if bd_upltime >= sq_upltime:
                d_id = bd_device_dict["id"]
                deviceId = bd_device_dict["device_id"]
                platform = "大数据平台"
            else:
                d_id = ""
                deviceId = sq_device_dict["equip_id_id"]
                platform = "四情平台"
        elif bd_device_dict and not sq_device_dict:
            d_id = bd_device_dict["id"]
            deviceId = bd_device_dict["device_id"]
            platform = "大数据平台"
            return d_id,deviceId,platform
        elif not bd_device_dict and sq_device_dict:
            d_id = ""
            deviceId = sq_device_dict["equip_id_id"]
            platform = "四情平台"
        else:
            d_id = ""
            deviceId = "平台无此设备"
            platform = "未知"
        print("deviceId:",deviceId)
        return d_id,deviceId,platform
    
    def sim_info(self, iccid):
        """查询卡信息"""
        url = "http://8.136.98.49:10001/iotcard/platsimview/inquiries/"
        try:
            response = requests.request("POST", url, data={"iccid": iccid})
        except:
            return [0, "查询卡信息异常稍后重试"]
        else:
            res_data = json.loads(response.text)
            if res_data["msg"] == "success" and res_data["data"]:
                expiry_date = res_data["data"]["expiry_date"]
                if expiry_date == "未知":
                    return [0, "未查询到卡信息"]
                else:
                    time_difference = self.__time_dif(expiry_date)
                    if time_difference >= 180:
                        return [1, "有效期剩余{}天".format(time_difference)]
                    elif 30 <= time_difference < 180:
                        return [2, "有效期剩余{}天".format(time_difference)]
                    else:
                        return [0, "有效期剩余{}天".format(time_difference)]
            else:
                return [0, "查询无结果"]

    def _get_data_new(self):
        """获取最新设备数据信息"""
        pipeline = [
            {
                "$match": {
                    "device_id": {"$in": self.device_list}
                }
            },
            {
                "$group": {
                    "_id": {"device_id": "$device_id"},
                    "last_msg": {"$last": "$$ROOT"}
                }
            },
            {
                "$replaceRoot": {"newRoot": "$last_msg"}
            }
        ]
        cursor = self.db.sa_qxz_data.aggregate(pipeline, allowDiskUse=True)
        data_dict = {item['device_id']: item for item in cursor}
        return data_dict

    def _get_bigdata_qxz_conf(self):
        data_dict = self._get_data_new()
        cursor = self.db.sa_qxz_conf.find({"device_id": {"$in": self.device_list}})
        title_name_set = set()
        qxz_conf_dict = {}
        for item in cursor:
            device_id = item['device_id']
            qxz_data = data_dict[device_id]
            config_dict = {}
            counter = Counter()
            for k, v in item.items():
                if k.startswith('e') and v and qxz_data.get(k, None):
                    t_name = v.split('#')[0]
                    t_index = counter.get(t_name, "")
                    config_dict[k] = f"{t_name}#{t_index}"
                    counter[t_name] += 1
            n_list = list(config_dict.values())
            title_name_set.update(n_list)
            qxz_conf_dict[device_id] = config_dict
        title_name_list = sorted(list(title_name_set))
        for i in title_name_list:
            print(i)
        for k, v in qxz_conf_dict.items():
            print(k, v)
        return title_name_list, qxz_conf_dict

    def get_qxz_element_data(self, device_id, conf_dict):
        group_dict = {
            "_id": "$device_id",
            "total_count": {"$count": {}},
            "uptime_list": {"$push": "$uptime"}
        }

        project_dict = {
            "_id": 0,
            "device_id": "$_id",
            "total_count": "$total_count",
            "uptime_info": {
                "$function": {
                    "args": ["$uptime_list"],
                    "lang": "js",
                    "body": """
function(uptime_list){
    let old_uptime = uptime_list[0];
    let uptime_20 = 0;
    let uptime_40 = 0;
    for (let i = 1; i < uptime_list.length; i++) {
        let uptime = uptime_list[i];
        let diff_time = uptime - old_uptime;

        if (diff_time < 1200){
            uptime_20 += 1;
        } else if (diff_time > 2400){
            uptime_40 += 1;
        }
        old_uptime = uptime;
    }
    return {"uptime_20": uptime_20, "uptime_40": uptime_40}
}
"""
                }
            }
        }
        for ek in conf_dict.keys():
            group_dict[ek] = {"$push": f"${ek}"}
            try:
                k = conf_dict[ek]
                func_name = self.get_conf_key(k)
                project_dict[ek] = self.cond.func_dict[func_name](ek)
            except KeyError as e:
                continue

        pipeline = [
            {
                "$match": {
                    "device_id": device_id,
                    "uptime": {
                        "$gte": self.start_time,
                        "$lt": self.end_time
                    }
                }
            },
            {
                "$sort": {"uptime": 1}
            },
            {
                "$project": {
                    "device_id": "$device_id",
                    "uptime": "$uptime",
                    "tmp_list": {
                        "$filter": {
                            "input": {"$objectToArray": "$$ROOT"},
                            "as": "item",
                            "cond": {
                                "$and": [
                                    {
                                        "$regexMatch": {
                                            "input": "$$item.k",
                                            "regex": "^e\d+",
                                            "options": "i"
                                        }
                                    },
                                    {
                                        "$regexMatch": {
                                            "input": "$$item.v",
                                            "regex": ".*#.*",
                                            "options": "i"
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            },
            {
                "$addFields": {
                    "tmp_obj": {
                        "$arrayToObject": {
                            "$map": {
                                "input": "$tmp_list",
                                "as": "item",
                                "in": {
                                    "k": "$$item.k",
                                    "v": {
                                        "$toInt": {
                                            "$toDouble": {"$arrayElemAt": [{"$split": ["$$item.v", "#"]}, 0]}
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            },
            {
                "$replaceRoot": {
                    "newRoot": {
                        "$mergeObjects": ["$tmp_obj", {"device_id": "$device_id", "uptime": "$uptime"}]
                    }
                }
            },
            {
                "$group": group_dict
            },
            {
                "$project": project_dict
            }
        ]

        cursor = self.db.sa_qxz_data.aggregate(pipeline, allowDiskUse=True)
        result = {k: v for item in cursor for k, v in item.items()}
        return result

    def get_qxz_volt_or_rssi_data(self):
        pipeline = [
            {
                "$match": {
                    "device_id": {
                        "$in": self.device_list
                    }
                },
            },
            {
                "$project": {
                    "device_id": "$device_id",
                    "volt": {
                        "$toDouble": "$volt"
                    },
                    "rssi": {
                        "$toDouble": "$rssi"
                    }
                }
            },
            {
                "$group": {
                    "_id": "$device_id",
                    "max_volt": {
                        "$max": "$volt"
                    },
                    "min_volt": {
                        "$min": "$volt"
                    },
                    "max_rssi": {
                        "$max": "$rssi"
                    },
                    "min_rssi": {
                        "$min": "$rssi"
                    }
                }
            },
            {
                "$project": {
                    "_id": 0,
                    "device_id": "$_id",
                    "volt_info": {
                        'max_value': "$max_volt", 'min_value': "$min_volt",
                        'status': {
                            "$cond": {
                                "if": {
                                    "$and": [
                                        {"$lte": ["$max_volt", 12.5]},
                                        {"$gte": ["$min_volt", 11.5]}
                                    ]
                                },
                                "then": 1,
                                "else": 0
                            }
                        }
                    },
                    "rssi_info": {
                        'max_value': "$max_rssi", 'min_value': "$min_rssi",
                        'status': {
                            "$cond": {
                                "if": {
                                    "$gte": ["$min_rssi", 14]
                                },
                                "then": 1,
                                "else": 0
                            }
                        }
                    }
                }
            }
        ]
        cursor = self.qxz_info_record_collection.aggregate(pipeline, allowDiskUse=True)
        result = {item['device_id']: item for item in cursor}
        return result

    def get_qxz_device_data(self):
        pipeline = [
            {
                "$match": {
                    "device_id": {
                        "$in": self.device_list
                    }
                },
            },
            {
                "$project": {
                    "device_id": "$device_id",
                    "dver_num": "$dver_num",
                    "lng": {
                        "$convert": {
                            "input": "$lng",
                            "to": "double",
                            "onError": 0,
                            "onNull": 0
                        }
                    },
                    "lat": {
                        "$convert": {
                            "input": "$lat",
                            "to": "double",
                            "onError": 0,
                            "onNull": 0
                        }
                    }
                }
            },
            {
                "$project": {
                    "_id": 0,
                    "device_id": "$device_id",
                    "dver_info": {
                        "status": {
                            "$cond": {
                                "if": {
                                    "$eq": ["$dver_num", self.set_stm8vs]
                                },
                                "then": 1,
                                "else": 0
                            }
                        },
                        "dver_num": "$dver_num",
                        "old_value": self.set_stm8vs
                    },
                    "lng_info": {
                        "status": {
                            "$cond": {
                                "if": {
                                    "$and": [
                                        {"$lte": ["$lng", 113.7869444]},
                                        {"$gte": ["$lng", 113.7536111]}
                                    ]
                                },
                                "then": 1,
                                "else": 0
                            }
                        },
                        "lng": "$lng"
                    },
                    "lat_info": {
                        "status": {
                            "$cond": {
                                "if": {
                                    "$and": [
                                        {"$lte": ["$lat", 35.0458333]},
                                        {"$gte": ["$lat", 35.0125]}
                                    ]
                                },
                                "then": 1,
                                "else": 0
                            }
                        },
                        "lat": "$lat"
                    }
                }
            }
        ]
        cursor = self.device_collection.aggregate(pipeline, allowDiskUse=True)
        result = {item['device_id']: item for item in cursor}
        return result

    def get_sim_data(self):
        pipeline = [
            {
                "$match": {
                    "device_id": {
                        "$in": self.device_list
                    }
                }
            },
            {
                "$project": {
                    "_id": 0,
                    "device_id": "$device_id",
                    "iccid": "$iccid"
                }
            }
        ]
        cursor = self.qxz_base_info_collection.aggregate(pipeline, allowDiskUse=True)
        result = {item['device_id']: item["iccid"] for item in cursor}
        return result

    def parse_data(self, device_id, qxz_conf_dict, vr_dict, device_data_dict, sim_data):
        """解析获取元素数据"""
        element_dict = {}
        element_dict["ID#"] = device_id
        element_dict["检验项目#"] = device_id
        try:
            conf_dict = qxz_conf_dict[device_id]
            device_data = self.get_qxz_element_data(device_id, conf_dict)
            element_dict.update({conf_dict[k]: v for k, v in device_data.items() if k in conf_dict})

            vr_info = vr_dict[device_id]
            dd_info = device_data_dict[device_id]

            element_dict["经度#"] = dd_info["lng_info"]
            element_dict["纬度#"] = dd_info["lat_info"]
            element_dict["固件版本号#"] = dd_info["dver_info"]

            element_dict["电压#"] = vr_info["volt_info"]
            element_dict["信号强度#"] = vr_info["rssi_info"]
            sim_info = self.sim_info(sim_data[device_id])
            element_dict["sim卡信息#"] = {
                "status": sim_info[0],
                "msg": sim_info[1]
            }
            element_dict["上传数据条数#"] = {
                "total_count": device_data['total_count'],
                "uptime_20": device_data["uptime_info"]["uptime_20"],
                "uptime_40": device_data["uptime_info"]["uptime_40"]
            }
        except KeyError as e:
            pass
        return element_dict

    def get_conf_key(self, k):
        key = k.split('#')[0]
        if "土壤含水率" in key:
            key = "土壤含水率"
        if "土壤温度" in key:
            key = "土壤温度"
        return key
      
    def run(self):
        """主业务逻辑,涉及进度条不能模块化,慢慢捋"""
        title_name_list = ["ID#", "检验项目#", "电压#", "信号强度#", "经度#", "纬度#", "固件版本号#", "sim卡信息#",
                           "上传数据条数#"]

        default_func_dict = {
            "电压": self.cond.get_cond_volt_msg,
            "信号强度": self.cond.get_cond_rssi_msg,
            "经度": self.cond.get_cond_lng_msg,
            "纬度": self.cond.get_cond_lat_msg,
            "固件版本号": self.cond.get_cond_version_msg,
            "上传数据条数": self.cond.get_time_uptime_msg,
            "sim卡信息": self.cond.get_cond_sim_msg
        }
        head_name_list, qxz_conf_dict = self._get_bigdata_qxz_conf()
        for n in head_name_list:
            if n not in title_name_list:
                title_name_list.append(n)
        title_name_list.append("单台合格数#")

        device_data_dict = self.get_qxz_device_data()
        vr_dict = self.get_qxz_volt_or_rssi_data()
        sim_data = self.get_sim_data()

        proess = 0
        now_time = datetime.datetime.now()
        global save_filename
        save_filename = self.set_order + "_" + now_time.strftime("%m%d") + ".xlsx"
        save_path = os.path.join(self.save_path,save_filename)

        workbook = Workbook(save_path)
        worksheet = workbook.add_worksheet()
        merge_title_style = workbook.add_format(merge_title_format)
        toji_style = workbook.add_format(toji_format)
        default_style = workbook.add_format(default_formal)
        red_style = workbook.add_format(error_format)
        green_style = workbook.add_format(formal_format)
        yellow_style = workbook.add_format(common_format)
        style_dict = {
            0: red_style,
            1: green_style,
            2: yellow_style,
            3: default_style
        }

        head_list = [i.split("#")[0] for i in title_name_list]
        worksheet.merge_range(0, 0, 0, len(head_list) - 1, "物联网气象站设备质检表格", merge_title_style)

        for index, k in enumerate(title_name_list):
            value = k.split('#')[0]
            worksheet.write(2, index, value, default_style)

        for index, k in enumerate(title_name_list):
            key = self.get_conf_key(k)
            try:
                value = self.cond.head_dict[key]
            except KeyError as e:
                value = "无判定条件"
            worksheet.write(3, index, value, default_style)

        row_index = 4
        qualified = 0
        for device_id in self.device_list:
            self.mongo_ping()
            element_dict = self.parse_data(device_id, qxz_conf_dict, vr_dict, device_data_dict, sim_data)
            not_qualified = 0
            for index, k in enumerate(title_name_list):
                status, msg = 3, "无数据"
                try:
                    value = element_dict[k]
                    key = self.get_conf_key(k)
                    if key in ["ID", "检验项目"]:
                        rt = {"status": 1, "msg": value}
                    elif key in default_func_dict:
                        rt = default_func_dict[key](value)
                    else:
                        rt = self.cond.cond_msg_dict[key](value)
                    status, msg = rt['status'], rt['msg']
                except Exception as e:
                    print('-----------', e)
                    pass

                if status == 0:
                    not_qualified += 1

                if k == "单台合格数#":
                    status, msg = 0, f"不合格: {not_qualified}"
                    if not_qualified == 0:
                        status, msg = 1, "合格"
                column_style = style_dict[status]
                worksheet.write(row_index, index, str(msg), column_style)

                proess = (index + 1) / len(self.device_list) * 100
                if int(proess) == 100:
                    self.proess_signal.emit(99)
                else:
                    self.proess_signal.emit(int(proess))

            if not_qualified == 0:
                qualified += 1
            row_index += 1

        toji_data = [
            "任务单号", self.set_order, "检验时间", self.start_time_str, self.end_time_str,
            "报告日期", now_time.strftime("%y-%m-%d %H:%M:%S"), "合格数", qualified
        ]
        for i in range(len(head_list) - len(toji_data)):
            toji_data.append("          ")

        for index, k in enumerate(toji_data):
            value = str(k)
            worksheet.write(1, index, value, toji_style)
            c_n = len(value) + 15
            worksheet.set_column(index, index, c_n)

        worksheet.protect(pwd_str)
        workbook.close()
        self.cursor.close()
        self.connection.close()
        self.myclient.close()
        self.proess_signal.emit(100)


if __name__== "__main__":
    QtWidgets.QApplication.setAttribute(QtCore.Qt.AA_EnableHighDpiScaling)
    QtGui.QGuiApplication.setAttribute(QtCore.Qt.HighDpiScaleFactorRoundingPolicy.PassThrough)
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())