scripting_tools/bigdata_django/zhijian/qxz_zhijian_simple.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 cgi import print_arguments
from multiprocessing.managers import DictProxy
from operator import delitem
from unittest import result
from PyQt5 import QtCore, QtGui, QtWidgets
import hashlib
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
from aliyunsdkcore.vendored.requests.auth import HTTPBasicAuth


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': 22,
    '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~15",
            "信号强度": "合格条件(绿色)：\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≤示值\n2、有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、900<示值<1150\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, "900 < v && v < 1150")
        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")
        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, 8, 1), QtCore.QTime(8, 30, 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, 8, 1), QtCore.QTime(9, 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 = "1"
        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 = str(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 = str(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.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}@114.115.147.140:27017/".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.qxz_base_info_collection = self.db.sa_qxz_base_info

        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()
        device_list_tp = []
        for d in device_list:
            d_id, device_id, platform = self.device_their_platform(d)
            device_list_tp.append(device_id)
        self.device_list = device_list_tp
    
    def mongo_ping(self):
        """mongo-ping预防连接失效"""
        # try:
        #     self.myclient.admin.command('ping')
        # except:  # "ConnectionFailure"
        self.myclient = pymongo.MongoClient("mongodb://{0}:{1}@114.115.147.140:27017/".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 = "未知"
        return d_id,deviceId,platform
    
    def sim_updata(self, iccid):
        # 时间戳 用于获取sign
        timestamp = int(time.time())
        current_milli_time = lambda: int(round(time.time() * 1000))
        data_1 = "appid=%s&iccid=%s&timestamp=%s%s"%("102420177762",iccid,current_milli_time(),"6397d7e6a56589f1d93284e9800493e1")
        sign = hashlib.sha256(data_1.encode('utf-8')).hexdigest()
        data = {"appid": "102420177762", "iccid": iccid, "timestamp":current_milli_time(),"sign":sign}
        url  = "https://api.simboss.com/2.0/device/detail"    
        try:
            status = 1
            ret = requests.post(url, data=data)
            code = json.loads(ret.text)["code"]
            if code == "0":
                status = 1
            else:
                url = 'http://sim.brlink.cn/api/open/iotcard/card'
                appkey = "iaO2DKgS8KdlnVgU"
                appsecret = "qzKgO4sBdzMrjRwv9H22S9ufepNv8Hl5ehPqkYVD31DCICjyKwqUdj7zihQQKfgx"
                status = 2
                ret = requests.post(url,json={'iccid':iccid},auth=HTTPBasicAuth(appkey,appsecret),timeout=(5,10))
                print(ret)
                codes = json.loads(ret.text)["code"]
                if codes == 0:
                    status = 2
                else:
                    url = "https://jsnl.xmnengjia.com/open/api/module/cards"
                    data = {"iccids":[iccid]}
                    data = json.dumps(data)
                    ret = requests.post(url,data=data,timeout=(10,30))
                    print(ret.text)
                    status = 3
        except:
            status = 0
            ret = 0
        return status,ret



    def get_position(self, lng, lat):
        if lng and lat:
            try:
                ret = requests.post("http://api.map.baidu.com/geocoder?location=%s,%s&coord_type=gcj02&output=json"%(lat,lng))
                ret_json = json.loads(ret.text)
                province, city, district = ret_json["result"]["addressComponent"]["province"], \
                                        ret_json["result"]["addressComponent"]["city"], \
                                        ret_json["result"]["addressComponent"]["district"]
                
                return province + city + district
            except Exception as e:
                return False
        else:
            return False

      
    def run(self):
        """主业务逻辑,涉及进度条不能模块化,慢慢捋"""
        title_name_list = ["ID#", "检验项目#", "土壤温度#", "土壤湿度#", "地理位置#", "流量卡有效期#", "数据条数#", "判定#"]
        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), "采集仪检验原始记录单", merge_title_style)

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

        tit = [
            "/",
            "/",
            "显示数据且数据不能为0",
            "显示数据且数据不能为0",
            "显示地理位置为河南省新乡县",
            "显示数据，不作为考评项目",
            "数据条数>=92,且两条数据间隔为30±2分钟(显示数据条数，若不符合进行描述)",
            "/"
        ]
        for index, k in enumerate(tit):
            worksheet.write(3, index, k, default_style)
        row_index = 4
        qualified = 0
        for device_id in self.device_list:
            worksheet.write(row_index, 0, device_id, default_style)
            worksheet.write(row_index, 1, device_id, default_style)
            at, ah, position, sim, cbd_data_count = None, None, None, None, None
            self.mongo_ping()
            device = self.device_collection.find_one({"device_id": device_id}, {'_id':0,'id':0})
            device_conf = self.db.sa_qxz_conf.find_one({"device_id": device_id}, {'_id':0,'id':0})
            device_data = self.db.sa_qxz_data.find({"device_id": device_id, "uptime": {"$gte": self.start_time, "$lte": self.end_time}})
            device_base_info = self.qxz_base_info_collection.find_one({"device_id": device_id})
            device_conf = dict(device_conf)
            device_data = list(device_data)
            # 获取温度与湿度
            tp = {}
            for k, v in device_conf.items():
                if v:
                    if "土壤含水率" in str(v):
                        tp["ah"] = k
                    if  "土壤温度" in str(v):
                        tp["at"] = k
            print(tp)
            at_lst = []
            ah_lst = []
            if device_data:
                at_lst = []
                ah_lst = []
                for i in device_data:
                    if tp.get("at"):
                        temp = float((i.get(tp.get("at"))).split("#")[0])
                        at_lst.append(temp)
                    if tp.get("ah"):
                        temp = float(i.get(tp.get("ah")).split("#")[0])
                        ah_lst.append(float(temp))
            print(at_lst)
            print(ah_lst)
            if at_lst:
                if 0.0 in at_lst:
                    at = [0, f"{min(at_lst)} ~ {max(at_lst)}" ]
                else:
                    at = [1, f"{min(at_lst)} ~ {max(at_lst)}" ]
            else:
                at = [0, "暂无数据"]

            if ah_lst:
                if 0.0 in ah_lst:
                    ah = [0, f"{min(ah_lst)} ~ {max(ah_lst)}  有 0 存在记录中" ]
                else:
                    ah = [1, f"{min(ah_lst)} ~ {max(ah_lst)}  有 0 存在记录中" ]
            else:
                ah = [0, "暂无数据"]

            # 数据条数量及数据间隔
            range_time_lst = [i.get("uptime") for i in device_data]
            diff_list = [range_time_lst[i] - range_time_lst[i-1] for i in range(1, len(range_time_lst))]
            rg_time = [i for i in diff_list if i > 32 * 60 or i < 28 * 60]
            if len(diff_list) >= 92 and len(rg_time) == 0:
                cbd_data_count = f"数据条件为{len(diff_list)}"
                cbd_data_count = [1, cbd_data_count]
            else:
                cbd_data_count = f"数据条件为{len(diff_list)},数据间隔不在30±2之间的数据有{len(rg_time)}条"
                cbd_data_count = [0, cbd_data_count]
            # 获取地理位置 
            province = device.get("province")
            city = device.get("city")
            district = device.get("district")
            if province and city and district:
                position = province + city + district
                pos = [1, position]
            else:
                lng = device.get("lng")
                lat = device.get("lat")
                if lng and lat:
                    position = self.get_position(lng, lat)
                    if position:
                        pos = [1, position]
                    else:
                        pos = [0, ""]
                else:
                    pos = [0, ""]
            # 获取SIM卡信息
            smf = device_base_info.get("iccid")
            if smf:
                url = "http://114.115.147.140:8002/api/api_gateway?method=forecast.send_control.sim_query"
                response = requests.post(url=url, data={"iccid": smf})
                response = json.loads(response.text)
                s_date = response.get("data", {}).get("data", {}).get("data").get("expireDate")
                day = self.__time_dif(s_date)
                sim = [1, str(day)]
            else:
                sim = [0, ""]
            print(at, ah, pos, sim, cbd_data_count)
            is_true = True
            for index, value in enumerate([at, ah, pos, sim, cbd_data_count]):
                if index != 3 and value[0] == 0:
                    is_true = False
                worksheet.write(row_index, index + 2, value[1], style_dict[value[0]])
            vk = "合格" if is_true else "不合格"
            vi = 1 if is_true else 0
            worksheet.write(row_index, 7, vk, style_dict[vi])
            row_index += 1
            if is_true:
                qualified += 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_())