# -*- coding: utf-8 -*-
# Author: wang yan
# Time: 2020/10/30
# Company: 嘉兴市规划研究院有限公司

from PyQt5 import QtCore, QtGui, QtWidgets
import pyodbc
import threading
import os


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(690, 468)
        MainWindow.setFixedSize(690,480)
        self.mainwindow = MainWindow
        
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.horizontalLayoutWidget = QtWidgets.QWidget(self.centralwidget)
        self.horizontalLayoutWidget.setGeometry(QtCore.QRect(50, 30, 311, 141))
        self.horizontalLayoutWidget.setObjectName("horizontalLayoutWidget")
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout(self.horizontalLayoutWidget)
        self.horizontalLayout_2.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        self.verticalLayout_2 = QtWidgets.QVBoxLayout()
        self.verticalLayout_2.setObjectName("verticalLayout_2")
        self.server_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget)
        self.server_lbl.setObjectName("server_lbl")
        self.verticalLayout_2.addWidget(self.server_lbl)
        self.port_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget)
        self.port_lbl.setObjectName("port_lbl")
        self.verticalLayout_2.addWidget(self.port_lbl)
        self.uid_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget)
        self.uid_lbl.setObjectName("uid_lbl")
        self.verticalLayout_2.addWidget(self.uid_lbl)
        self.pwd_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget)
        self.pwd_lbl.setObjectName("pwd_lbl")
        self.verticalLayout_2.addWidget(self.pwd_lbl)
        self.horizontalLayout_2.addLayout(self.verticalLayout_2)
        self.verticalLayout_3 = QtWidgets.QVBoxLayout()
        self.verticalLayout_3.setObjectName("verticalLayout_3")
        self.server_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget)
        self.server_txt.setObjectName("server_txt")
        self.verticalLayout_3.addWidget(self.server_txt)
        self.port_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget)
        self.port_txt.setObjectName("port_txt")
        self.verticalLayout_3.addWidget(self.port_txt)
        self.uid_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget)
        self.uid_txt.setObjectName("uid_txt")
        self.verticalLayout_3.addWidget(self.uid_txt)
        self.pwd_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget)
        self.pwd_txt.setObjectName("pwd_txt")
        self.verticalLayout_3.addWidget(self.pwd_txt)
        self.horizontalLayout_2.addLayout(self.verticalLayout_3)
        self.horizontalLayoutWidget_2 = QtWidgets.QWidget(self.centralwidget)
        self.horizontalLayoutWidget_2.setGeometry(QtCore.QRect(50, 220, 311, 161))
        self.horizontalLayoutWidget_2.setObjectName("horizontalLayoutWidget_2")
        self.horizontalLayout_3 = QtWidgets.QHBoxLayout(self.horizontalLayoutWidget_2)
        self.horizontalLayout_3.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout_3.setObjectName("horizontalLayout_3")
        self.verticalLayout_4 = QtWidgets.QVBoxLayout()
        self.verticalLayout_4.setObjectName("verticalLayout_4")
        self.match_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget_2)
        self.match_lbl.setObjectName("match_lbl")
        self.verticalLayout_4.addWidget(self.match_lbl)
        self.std_addr_men_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget_2)
        self.std_addr_men_lbl.setObjectName("std_addr_men_lbl")
        self.verticalLayout_4.addWidget(self.std_addr_men_lbl)
        self.std_addr_lou_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget_2)
        self.std_addr_lou_lbl.setObjectName("std_addr_lou_lbl")
        self.verticalLayout_4.addWidget(self.std_addr_lou_lbl)
        self.std_addr_hu_lbl = QtWidgets.QLabel(self.horizontalLayoutWidget_2)
        self.std_addr_hu_lbl.setObjectName("std_addr_hu_lbl")
        self.verticalLayout_4.addWidget(self.std_addr_hu_lbl)
        self.horizontalLayout_3.addLayout(self.verticalLayout_4)
        self.verticalLayout_5 = QtWidgets.QVBoxLayout()
        self.verticalLayout_5.setObjectName("verticalLayout_5")
        self.match_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget_2)
        self.match_txt.setObjectName("match_txt")
        self.verticalLayout_5.addWidget(self.match_txt)
        self.std_addr_men_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget_2)
        self.std_addr_men_txt.setObjectName("std_addr_men_txt")
        self.verticalLayout_5.addWidget(self.std_addr_men_txt)
        self.std_addr_lou_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget_2)
        self.std_addr_lou_txt.setObjectName("std_addr_lou_txt")
        self.verticalLayout_5.addWidget(self.std_addr_lou_txt)
        self.std_addr_hu_txt = QtWidgets.QLineEdit(self.horizontalLayoutWidget_2)
        self.std_addr_hu_txt.setObjectName("std_addr_hu_txt")
        self.verticalLayout_5.addWidget(self.std_addr_hu_txt)
        self.horizontalLayout_3.addLayout(self.verticalLayout_5)
        self.connect_btn = QtWidgets.QPushButton(self.centralwidget)
        self.connect_btn.setGeometry(QtCore.QRect(50, 180, 311, 28))
        self.connect_btn.setObjectName("connect_btn")
        self.match_btn = QtWidgets.QPushButton(self.centralwidget)
        self.match_btn.setGeometry(QtCore.QRect(50, 390, 311, 28))
        self.match_btn.setObjectName("match_btn")
        self.textBrowser = QtWidgets.QTextBrowser(self.centralwidget)
        self.textBrowser.setGeometry(QtCore.QRect(380, 30, 251, 381))
        self.textBrowser.setObjectName("textBrowser")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 690, 26))
        self.menubar.setObjectName("menubar")
        self.menu = QtWidgets.QMenu(self.menubar)
        self.menu.setObjectName("menu")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.menubar.addAction(self.menu.menuAction())
        self.readFileAction = QtWidgets.QAction('读取配置文件',MainWindow)
        self.readFileAction.triggered.connect(self.somethingInit)
        self.menubar.addAction(self.readFileAction)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow) 

        self.database_conn = False
        self.match_btn.setEnabled(False)
        self.CONN_thread = threading.Thread(target=self.connect_database)
        self.MATCH_thread = threading.Thread(target=self.match_standAddr_thread)

        
    def somethingInit(self):
        fileName,fileType = QtWidgets.QFileDialog.getOpenFileName(self.mainwindow, "选址配置文件（登录信息、表信息）", os.getcwd(), 
                                                                    "All Files(*);;Text Files(*.txt)")

        with open(fileName,'r', encoding='UTF-8') as f:
            infos = f.readlines()

        self.server_txt.setText(infos[0].strip())
        self.port_txt.setText(infos[1].strip())
        self.uid_txt.setText(infos[2].strip())
        self.pwd_txt.setText(infos[3].strip())
        self.match_txt.setText(infos[4].strip())
        self.std_addr_men_txt.setText(infos[5].strip())
        self.std_addr_lou_txt.setText(infos[6].strip())
        self.std_addr_hu_txt.setText(infos[7].strip())

    
    # 线程target
    def connect_database(self):
        try:
            self.db = pyodbc.connect(self.CONN)
            self.cursor = self.db.cursor()
            self.match_btn.setEnabled(True)
            self.textBrowser.append('数据库连接成功')
            self.database_conn = True
        except pyodbc.Error as err:
            self.textBrowser.append('数据库连接失败，请检查登录信息是否输入正确')

    def match_standAddr_thread(self):
        self.match_btn.setEnabled(False)
        #匹配代码 比较耗时
        self.textBrowser.append('开始读取标准地址—户室地址')
        # ods_zhzl.dbo.标准地址_户室地址
        self.cursor.execute("select * from %s"%(self.std_addr_hu_txt.text()))
        self.home_address = self.cursor.fetchall()
        self.textBrowser.append('标准地址—户室地址读取完成')
        self.textBrowser.append('开始读取标准地址—楼址')
        #ods_zhzl.dbo.标准地址_建筑物面
        self.cursor.execute("select * from %s"%(self.std_addr_lou_txt.text()))
        self.building_address = self.cursor.fetchall()
        self.textBrowser.append('标准地址—楼址读取完成')
        self.textBrowser.append('开始读取标准地址—门址')
        # ods_zhzl.dbo.标准地址_门址属性
        self.cursor.execute("select * from %s"%(self.std_addr_men_txt.text()))
        self.door_address = self.cursor.fetchall()
        self.textBrowser.append('标准地址—门址读取完成')
        # ods_zhzl.dbo.T_CHEN_A 
        self.textBrowser.append('开始读取待匹配地址表')
        self.cursor.execute("select * from %s"%(self.match_txt.text()))
        self.match_data = self.cursor.fetchall()
        self.textBrowser.append('待匹配地址表读取完成')
        
        
        ##########################开始匹配##################################
        explanation_name = ['街道、镇','社区/行政村','路、街、巷、弄','小区、单位名、自然村、建筑物名','幢、座、农村门牌','室']
        i = 0
        for data in self.match_data:
            temp = 0
            i+=1
            self.textBrowser.append('第%s条地址数据匹配中...'%i)
            for std_building_addr in self.building_address:
                score = 0
                street_tag = False
                community_tag = False
                road_tag = False
                naturalVillage_tag = False
                building_tag = False
                room_tag = False

                if((data[10] is not None) and data[10] == std_building_addr[9]): #street
                    score += 1 * 0.1
                    street_tag = True
                if((std_building_addr[11] is not None) and (data[11] == std_building_addr[11] or data[12] == std_building_addr[11])):
                    score += 1 * 0.1
                    community_tag = True
                if((data[13] is not None) and data[13] == std_building_addr[13]):
                    score += 1 * 0.1
                    road_tag = True
                if((data[15] != '') and (data[15] == std_building_addr[12] or data[15] == std_building_addr[15])): 
                    score += 1 * 0.6
                    naturalVillage_tag = True
                if((data[16] is not None) and data[16] == std_building_addr[32]): # building_num
                    score += 1 * 0.1
                    building_tag = True

                if (data[18] is not None) and naturalVillage_tag==True and building_tag==True:
                    for std_home_addr in self.home_address:
                        if ((data[18]+'室')==std_home_addr[28]) and (data[15] == std_home_addr[11] or data[15] == std_home_addr[14]) \
                            and (data[16]==std_home_addr[29]):
                            score = 1
                            room_tag = True
                            tmp_std_addr = std_home_addr

                if score > temp:
                    temp = score
                    temp_std_addr = std_building_addr
                    temp_street_tag = street_tag
                    temp_community_tag = community_tag
                    temp_road_tag = road_tag
                    temp_naturalVillage_tag = naturalVillage_tag
                    temp_building_tag = building_tag
                    temp_room_tag = room_tag

                if score == 1:
                    break
                    
            self.textBrowser.append('匹配率为%s'%str(temp))
            explanation_bool = [temp_street_tag,temp_community_tag,temp_road_tag,temp_naturalVillage_tag,temp_building_tag,temp_room_tag]
            explanation = dict(zip(explanation_name,explanation_bool))
            explanation_res = ''
            if(self.GetExplanation(explanation)==''):
                explanation_res = '没有匹配上'
                self.textBrowser.append(explanation_res)
            else:
                explanation_res = '匹配上了'+ self.GetExplanation(explanation)
                self.textBrowser.append(explanation_res)

            # 返回标准地址全称、标准地址唯一编码、匹配状态（匹配率）和说明  

            std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set Explanation='%s' where Tid=%d"%(explanation_res,data[1])
            self.cursor.execute(std_addr_sql)
            self.cursor.commit()

            if temp>0.6 and temp<1:
                #返回标准地址全称、标准地址唯一编码
                std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set StandardAddr='%s' where Tid=%d"%(temp_std_addr[35],data[1])
                self.cursor.execute(std_addr_sql)
                self.cursor.commit()

                std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set StandardAddrUUID='%s' where Tid=%d"%(temp_std_addr[2],data[1])
                self.cursor.execute(std_addr_sql)
                self.cursor.commit()

                std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set MatchScores=%s where Tid=%d"%(str(temp),data[1])
                self.cursor.execute(std_addr_sql)
                self.cursor.commit()


            elif temp <= 0.6:
                std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set MatchScores=%s where Tid=%d"%(str(temp),data[1])
                self.cursor.execute(std_addr_sql)
                self.cursor.commit()

            if temp==1:
                #返回标准地址全称、标准地址唯一编码
                std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set StandardAddr='%s' where Tid=%d"%(temp_std_addr[35]+tmp_std_addr[28],data[1])
                self.cursor.execute(std_addr_sql)
                self.cursor.commit()

                std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set StandardAddrUUID='%s' where Tid=%d"%(tmp_std_addr[2],data[1])
                self.cursor.execute(std_addr_sql)
                self.cursor.commit()
                        
                std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set MatchScores=%s where Tid=%d"%(str(temp),data[1])
                self.cursor.execute(std_addr_sql)
                self.cursor.commit()
        self.match_btn.setEnabled(True)
        # self.match_btn.setStyleSheet('background-color: red') 会卡死
        self.textBrowser.append('----匹配完成----')
        
        
    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "地址匹配v1.0"))
        self.server_lbl.setText(_translate("MainWindow", "Server:"))
        self.port_lbl.setText(_translate("MainWindow", "Port:"))
        self.uid_lbl.setText(_translate("MainWindow", "UID:"))
        self.pwd_lbl.setText(_translate("MainWindow", "PWD:"))
        self.match_lbl.setText(_translate("MainWindow", "待匹配表名："))
        self.std_addr_men_lbl.setText(_translate("MainWindow", "标准地址（门）："))
        self.std_addr_lou_lbl.setText(_translate("MainWindow", "标准地址（楼）："))
        self.std_addr_hu_lbl.setText(_translate("MainWindow", "标准地址（户）："))
        self.connect_btn.setText(_translate("MainWindow", "连接数据库"))
        self.match_btn.setText(_translate("MainWindow", "开始匹配"))
        self.menu.setTitle(_translate("MainWindow", "帮助"))
        

    def slotInit(self):
        self.connect_btn.clicked.connect(self.connect_btn_click)
        self.match_btn.clicked.connect(self.match_btn_click)

    def connect_btn_click(self):

        DRIVER = "{SQL Server Native Client 11.0}"
        SERVER = self.server_txt.text()
        PORT = self.port_txt.text()
        UID = self.uid_txt.text()
        PWD = self.pwd_txt.text()
        if(SERVER!="" and PORT!="" and UID!="" and PWD!=""):
            self.CONN = "DRIVER=%s;SERVER=%s,%s;UID=%s;PWD=%s" % (DRIVER, SERVER, PORT, UID, PWD)
            # self.CONN_thread = threading.Thread(target=self.connect_database,args=(self.CONN,))
            while True:
                if self.CONN_thread.is_alive() == False:
                    self.CONN_thread = threading.Thread(target=self.connect_database)
                    self.CONN_thread.start()
                    break

            # 获取表名  有问题
            # for row in self.cursor.tables():
            #     self.textBrowser.append(row.table_name)
        else:
            self.textBrowser.append('请输入完整的登录信息')
            

    def match_btn_click(self):
        if self.match_txt.text()=='' or self.std_addr_men_txt.text()=='' or self.std_addr_men_txt.text()=='' or self.std_addr_men_txt.text()=='':
            self.textBrowser.append('请输入完整的表名，否则无法进行匹配')
        else:
            if(self.database_conn==False):
                self.textBrowser.append('请先连接数据库')
            else:
                while True:
                    if self.MATCH_thread.is_alive() == False:
                        self.MATCH_thread = threading.Thread(target=self.match_standAddr_thread)
                        self.MATCH_thread.start()
                        self.textBrowser.append("#####################开始匹配####################")
                        break


    def GetExplanation(self,dict_value):
        temp = ''
        for key,value in dict_value.items():
            if value ==True:
                temp += key
        return temp


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    ui.slotInit()
    ui.CONN_thread.setDaemon(True) #守护线程CONN_thread
    ui.MATCH_thread.setDaemon(True)
    MainWindow.show()
    sys.exit(app.exec_())