#import msoffcrypto
from .flightDB import flightDB
from pypinyin import pinyin, Style
#import io
import openpyxl
from unitls.LogerinTxt import app_logger
import datetime

def get_full_pinyin1(word, style=Style.NORMAL, separator=''):
    pinyin_list = pinyin(word, style=style)
    #使用列表推导式获取每个汉字的第一个拼音（不带声调），并使用 separator 连接
    full_pinyin = separator.join([''.join(syllable[0]) for syllable in pinyin_list])
    return full_pinyin
def get_full_pinyin2(word, style=Style.NORMAL, separator=''):
    return ''.join([word[0][0] for word in pinyin(word, style=Style.NORMAL)])

def get_dic(database:flightDB, fileName):
    dic=["维修一队","维修二队","维修三队","维修四队","支援人员"]
    try:
        fdb = database
        IDS=fdb.getSingledata("工号",'workerinfo')
        IDSNOW=[]
        try:
            workbook = openpyxl.load_workbook(filename=fileName) #非加密文档
            for sheetname in dic:
                mysheet = workbook[sheetname]
                myrows = list(mysheet.values)[1:]
                mytitle = list(mysheet.values)[0]
                for myrow in myrows:
                    mydics0 = {}
                    mydics1 = {}
                    for i in range(0, (len(mytitle))):
                        a = get_full_pinyin1(str(myrow[0])) + ":" + get_full_pinyin2(str(myrow[0]))
                        mydics0[mytitle[i]] = "%s"%myrow[i]
                        mydics0[mytitle[11]] = "%s" % a
                        if i != 3 and i != 11:
                            mydics1[mytitle[i]] = "'%s'" % myrow[i]
                        elif i == 11:
                            mydics1[mytitle[11]] = "'%s'" % a
                    #a= fdb.getSingledata("工号",'workerinfo')
                    if str(mydics0["工号"]) in str(IDS) or str(mydics0["工号"]) in IDSNOW:
                        fdb.lazyUpdateItem('workerinfo',mydics1,"工号='%s'"%mydics0["工号"])
                    else:
                        fdb.lazyInsertData('workerinfo',mydics0)
                        IDSNOW.append(mydics0["工号"])
                #print(1)
            fdb.FunctionCommit()
            return 1
        except Exception as e:
            print(datetime.datetime.now(),'[数据库操作]数据库获取错误!!检查通讯录文件!人员显示功能失效!!')
            app_logger.log_error(e)
            # 需要QT输出警告弹框信息
            fdb.FunctionCommit()
            return 0

    except Exception as e:
        app_logger.log_error(e)