#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):
    try:
        fdb = database
        try:
            ''''
            decrypted_workbook = io.BytesIO()
            with open(fileName, 'rb') as file:
                office_file = msoffcrypto.OfficeFile(file)
                office_file.load_key(password='Scal_0177')
                office_file.decrypt(decrypted_workbook)
            workbook = openpyxl.load_workbook(filename=decrypted_workbook,data_only=True)
            '''
            workbook = openpyxl.load_workbook(filename=fileName) #非加密文档
            mysheet = workbook["主表"]
            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(fdb.getSingledata("工号",'workerinfo')):
                    fdb.lazyUpdateItem('workerinfo',mydics1,"工号='%s'"%mydics0["工号"])
                else:
                    fdb.lazyInsertData('workerinfo',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)