scripting_tools/bigdata_django/zhijian/qxz/main.py

# encoding=utf-8
from PyQt5 import QtCore, QtGui, QtWidgets
import os
import sys
import datetime
from xlrd import open_workbook
from xlsxwriter.workbook import Workbook
import openpyxl


from mongoclient import (
    get_qxz_conf, 
    device_detail_deivce_id, 
    get_sa_qxz_info_record,
    get_sa_qxz_data,
    get_conf_data,
    get_sim_info
)
from utils import (
    merge_title_format, 
    toji_format, 
    formal_format, 
    error_format, 
    common_format, 
    default_formal,
    get_excel_content,
    head_dict,
    sim_info
)

save_filename = ""


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(2028, 12, 31), QtCore.QTime(23, 59, 59)))
        self.startTimeEdit.setMinimumDateTime(QtCore.QDateTime(QtCore.QDate(2024, 1, 1), QtCore.QTime(18, 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(2024, 2, 1), QtCore.QTime(20, 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 = 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()
                        if d_i_v:
                            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.device_list = device_list
        self.start_time = start_time
        self.end_time = end_time
        self.set_plat = set_plat
        self.set_stm8vs = set_stm8vs
        self.set_order = set_order


    def run(self):
        # start = "2023-08-28 10:00:00"
        # end = "2023-08-31 20:00:00"
        start = self.start_time.strftime("%Y-%m-%d %H:%M:%S")
        end = self.end_time.strftime("%Y-%m-%d %H:%M:%S")
        dever_num = self.set_stm8vs
        # 获取所有的device_id
        # device_list = get_excel_content("F:\\scripting_tools\\热通量增加测试.xlsx")
        save_filename = self.set_order + "_" + datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S") + ".xlsx"
        save_path = os.path.join(self.save_path,save_filename)
        workbook = Workbook(save_path)
        device_list = self.device_list
        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 = {
            2: red_style,
            1: green_style,
            3: yellow_style,
            4: default_style
        }

        title_name_list = [
            "ID", 
            "检验项目", 
            "电压", 
            "信号强度", 
            "经度", 
            "纬度", 
            "固件版本号", 
            "sim卡信息",
            "位置信息"
        ]
        # 查找第一台设备的配置文件，获取配置项
        device_id, _, _, _, _ = device_detail_deivce_id(device_list[0])
        conf = get_qxz_conf(device_id)
        title_name_list += [v.split("#")[0] for k, v in conf.items()]
        title_name_list.append("单台合格数")
        head_list = [i.split("#")[0] for i in title_name_list]
        worksheet.merge_range(0, 0, 0, len(head_list) - 1, "物联网气象站设备质检表格", merge_title_style)
        toji_data = [
            "任务单号", 
            self.set_order, 
            "检验时间", 
            start, 
            end,
            "报告日期", 
            (datetime.datetime.now()).strftime("%y-%m-%d %H:%M:%S"), 
            "合格数"
        ]
        # 生成Excel表格第2行， 本次数据概要
        for index, k in enumerate(toji_data):
            value = str(k)
            worksheet.write(1, index, value, toji_style)
        # 生成Excel表格第3行， 检测标题
        for index, k in enumerate(title_name_list):
            worksheet.write(2, index, k, default_style)
        # 生成Excel表格第4行， 检测标准
        for index, k in enumerate(title_name_list):
            value = (head_dict.get(k, ["无判定条件", ""]))[0]
            worksheet.write(3, index, value, default_style)
            plus = 12 if index in [0, 1] else 8
            c_n = len(value) / 2 + plus
            worksheet.set_column(index, index, c_n)
        # 输入数据
        is_pass_count = 0
        print(head_dict)
        for index, id in enumerate(device_list):
            device_id, dver_num, lng, lat, position = device_detail_deivce_id(id)
            # 获取电压，信号强度历史数据
            valt_rssi = get_sa_qxz_info_record(device_id, start, end)
            # 获取sim卡信息
            sim = get_sim_info(device_id, start, end)
            sim_in = sim_info(sim)
            # 第一部分拼接 表头及数据
            org_data = {
                "id_1": {"data": id, "explan": "ID"},
                "id_2": {"data": device_id, "explan": "检验项目"},
                "id_3": valt_rssi.get("volt"),  # 电压
                "id_4": valt_rssi.get("rssi"),  # 信号强度
                "id_5": {"data": lng, "explan": "经度"},
                "id_6": {"data": lat, "explan": "纬度"},
                "id_7": {"data": [dver_num.replace("/", "-"), dever_num], "explan": "固件版本号"},
                "id_8": {"data": sim_in, "explan": "sim卡信息"},
                "id_9": {"data": position, "explan": "位置信息"},
            }
            conf = get_qxz_conf(device_id)
            # 获取数据
            qx_data = get_sa_qxz_data(device_id, start, end, conf)
            # 第二部分拼接 表头及数据   获取气象与数据结合
            conf_data = get_conf_data(conf, qx_data)
            org_data.update(conf_data)
            i = 0
            is_right = 0
            for t, v in org_data.items():
                # 执行方法
                name = v.get("explan")
                data = v.get("data")
                func = head_dict.get(name)[1]
                if data:
                    # print(name)
                    # print(data)
                    # print("-"*100)
                    temp = func(data)
                    if temp[0] == 1:
                        is_right += 1
                else:
                    temp = [2, "没有数据"]
                worksheet.write(index + 4, i, temp[1], style_dict[temp[0]])
                i += 1
            last = []
            if is_right == len(title_name_list) - 1:
                last = [1, "合格"]
                is_pass_count += 1
            else:
                last = [2, "不合格"]
            worksheet.write(index + 4, i, last[1], style_dict[last[0]])
            self.proess_signal.emit(int((index + 1)/len(device_list) * 100))
        worksheet.write(1, 8, is_pass_count, toji_style)
        worksheet.protect("yfkj")
        workbook.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_())