import pyodbc

def CreateNewField(table_name,field_name):
    sql_script = 'alter table {} add {} nvarchar(20) NULL'.format(table_name,field_name)
    cursor.execute(sql_script)
    cursor.commit()

def is_None(value):
    if value is None:
        return 'None'
    else:
        return value

# 创建表:示例
def CreateTable():
    cursor.execute("CREATE TABLE user (id INT PRIMARY KEY, name VARCHAR(255), tele VARCHAR(14), addr VARCHAR(255))")

# 添加数据
def InsertData():
    cursor.execute("INSERT INTO user VALUES(1, 'a', '0-12345678', '0-abcdefg')")
    cursor.execute("INSERT INTO user VALUES(2, 'b', '1-12345678', '1-abcdefg')")
    cursor.execute("INSERT INTO user VALUES(3, 'c', '2-12345678', '2-abcdefg')")
    cursor.execute("INSERT INTO user VALUES(4, 'd', '3-12345678', '3-abcdefg')")
    cursor.commit()

# 修改指定数据
def UpdateTable(cur,table_name,field_name,tuple_values):
    pass

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



#获取待匹配数据
#获取标准地址库数据表
#将待匹配数据表与标准地址库进行比对

if __name__ == '__main__':
    ############################连接数据库#############################################
    DRIVER = "{SQL Server Native Client 11.0}"
    SERVER = "10.22.112.212"
    PORT = 1433
    UID = "chenghu"
    PWD = "chenghu123"
    CONN = "DRIVER=%s;SERVER=%s,%s;UID=%s;PWD=%s" % (DRIVER, SERVER, PORT, UID, PWD)
    db = pyodbc.connect(CONN)
    cursor = db.cursor() #创建游标

    ##########################读取待匹配表和标准地址库数据################################
    cursor.execute("select * from ods_zhzl.dbo.标准地址_户室地址")
    home_address = cursor.fetchall()
    cursor.execute("select * from ods_zhzl.dbo.标准地址_建筑物面")
    building_address = cursor.fetchall()
    cursor.execute("select * from ods_zhzl.dbo.标准地址_门址属性")
    door_address = cursor.fetchall()
    print("门、楼、户标准地址读取完成...")
    print("--标准地址-门址：%d条"%(len(door_address)))
    print("--标准地址-楼址：%d条"%(len(building_address)))
    print("--标准地址-户址：%d条"%(len(home_address)))
    cursor.execute("select * from ods_zhzl.dbo.T_CHEN_A")
    print("待匹配的政务数据表读取完成...")
    match_data = cursor.fetchall()

    ##########################开始匹配##################################
    explanation_name = ['街道、镇','社区/行政村','路、街、巷、弄','小区、单位名、自然村、建筑物名','幢、座、农村门牌','室']
    for data in match_data:
        temp = 0
        
        for std_building_addr in 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 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
        
        
        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(GetExplanation(explanation)==''):
            explanation_res = '没有匹配上'
            print(explanation_res)
            print(temp)
            print(explanation)
        else:
            explanation_res = '匹配上了'+ GetExplanation(explanation)

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

        std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set Explanation='%s' where Tid=%d"%(explanation_res,data[1])
        cursor.execute(std_addr_sql)
        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])
            cursor.execute(std_addr_sql)
            cursor.commit()

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

            std_addr_sql = "update ods_zhzl.dbo.T_CHEN_A set MatchScores=%s where Tid=%d"%(str(temp),data[1])
            cursor.execute(std_addr_sql)
            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])
            cursor.execute(std_addr_sql)
            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])
            cursor.execute(std_addr_sql)
            cursor.commit()

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

            






        
        ################################匹配结果输出至终端#########################################
        # print(temp)
        # print(data[10:19])
        # print(is_None(temp_std_addr[9]),is_None(temp_std_addr[11]),is_None(temp_std_addr[13]),
        #         is_None(temp_std_addr[12]),is_None(temp_std_addr[15]),
        #             is_None(temp_std_addr[32]))   

        ################################匹配结果输出至文件#########################################
        # res = [is_None(temp_std_addr[9]),is_None(temp_std_addr[11]),is_None(temp_std_addr[13]),
        #         is_None(temp_std_addr[12]),is_None(temp_std_addr[15]),
        #             is_None(temp_std_addr[32])]
        # with open('result.txt','a') as f:
        #     f.write(str(temp)+'\n')
        #     f.write(str(data[10:19])+'\n')
        #     f.write(str(res)+'\n')