24 11 2021
# -*- coding:utf-8 -*-

import os
import re
import cv2
import sys
import csv
import time
import json
import xlrd
import xlwt
import autoit
import shutil
import ctypes
import psutil
import socket
import ftplib
import inspect
import logging
import zipfile
import pymysql
import requests
import datetime
import calendar
import pyautogui
import threading
import pyperclip
import subprocess
import numpy as np
import configparser
import pandas as pd
import xlwings as xw
from ftplib import FTP
from PIL import ImageGrab
from pynput import keyboard
from xlutils.copy import copy
from selenium import webdriver
# from Crypto.Cipher import AES
from PythonCode.Common import ImageSearch
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
from dateutil.relativedelta import relativedelta
from selenium.webdriver.support.select import Select
from selenium.webdriver.chrome.options import Options
from PythonCode.Common.ImageSearch import ImageSearchs
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from PythonCode.Common.ownerException import NotFoundKeyException, PortHasBeenUsedException


def log(filename):
    '''
    log,文件名要传入绝对路径,
    :param author: 201916820
    :param filename:{str} log文件保存路径
    :return:{object} 返回log对象
    '''
    dstdir = os.path.split(filename)[0]  # 父文件路径
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)

    # 创建一个logger
    logger = logging.getLogger()
    logger.setLevel(logging.DEBUG)
    # 创建一个handler,用于写入日志文件
    fh = logging.FileHandler(filename)
    fh.setLevel(logging.INFO)
    # 再创建一个handler,用于输出到控制台
    ch = logging.StreamHandler()
    ch.setLevel(logging.INFO)
    # 定义handler的输出格式
    formatter = logging.Formatter('%(asctime)s %(module)s %(funcName)s %(lineno)d [%(levelname)s] :  %(message)s')
    fh.setFormatter(formatter)
    ch.setFormatter(formatter)

    # 给logger添加handler
    logger.addHandler(fh)
    logger.addHandler(ch)
    # 记录一条日志
    logger.info('')
    # logger.warning()
    # logger.debug()
    # logger.error()
    return logger


def renamefile(srcdir, dstdir, fileName):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    files = None
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return 'NO FILE'  # 空文件夹,不进行复制操作
    for file_m in files:
        fileName = '%s.%s' % (fileName, file_m.split('.')[1])
        file_s = os.path.join(srcdir, file_m)
        file_d = os.path.join(dstdir, fileName)
        if os.path.isfile(file_s):
            os.rename(file_s, file_d)
            return file_d


def remove_file2parent_temp(account, filePath):
    '''
    移动文件到../../Temp文件夹下
    :param account:
    :param filePath:
    :return:
    '''
    path_temp = os.path.abspath(filePath + os.path.sep + "../../Back")
    if not os.path.exists(path_temp):
        os.makedirs(path_temp)  # 创建新文件夹路径
    files = os.listdir(filePath)
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        shutil.move(file_s, path_temp)  # 移动文件


def check_last_creat_file(account, filePath):
    if '/' in account:
        account = account.split('/')[1]
    files = os.listdir(filePath)
    file_dict = {}
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        if not os.path.isfile(file_s):
            continue
        file_time = os.stat(file_s).st_mtime
        file_dict[file_time] = file_s
    if not file_dict:
        return
    max_time = max(file_dict.keys())
    return file_dict[max_time]


def movefile(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    files = None
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        if os.path.isfile(file_v):
            files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件


def move_file_and_dir(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    files = None
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件


def mycopyfile(srcfile, dstfile):
    if not os.path.isfile(srcfile):
        print("%s not exist!" % (srcfile))
    else:
        fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        shutil.copyfile(srcfile, dstfile)  # 复制文件
        print("copy %s -> %s" % (srcfile, dstfile))


def rename(srcfile, dstdir, new_name):
    '''
    文件重命名
    :param srcfile:{str} 原始文件
    :param dstdir: {str} 新文件文件夹
    :param new_name: {str} 文件名
    :return: None
    '''
    # if not os.path.exists(dstdir):
    #     return
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    if new_name:  # 重命名文件
        new_name = os.path.join(dstdir, new_name)
        os.rename(srcfile, new_name)


def stop_thread(thread):
    '''
    关闭多线程
    :param thread:{object} 线程对象
    :return:None
    '''
    _async_raise(thread.ident, SystemExit)


def _async_raise(tid, exctype):
    """raises the exception, performs cleanup if needed"""
    tid = ctypes.c_long(tid)
    if not inspect.isclass(exctype):
        exctype = type(exctype)
    res = ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, ctypes.py_object(exctype))
    if res == 0:
        raise ValueError("invalid thread id")
    elif res != 1:
        ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, None)
        raise SystemError("PyThreadState_SetAsyncExc failed")


class Properties(object):
    '''
    读取property文件,返回字典
    '''

    def __init__(self, fileName):
        self.fileName = fileName
        self.properties = {}

    def __getDict(self, strName, dictName, value):

        if (strName.find('.') > 0):
            k = strName.split('.')[0]
            dictName.setdefault(k, {})
            return self.__getDict(strName[len(k) + 1:], dictName[k], value)
        else:
            dictName[strName] = value
            return

    def getProperties(self):
        try:
            pro_file = open(self.fileName, 'Ur')
            for line in pro_file.readlines():
                line = line.strip().replace('\n', '')
                if line.find("#") != -1:
                    line = line[0:line.find('#')]
                if line.find('=') > 0:
                    strs = line.split('=')
                    strs[1] = line[len(strs[0]) + 1:]
                    self.__getDict(strs[0].strip(), self.properties, strs[1].strip())
        except Exception as e:
            raise e
        else:
            pro_file.close()
        return self.properties


# class AESCoder():
#     def __init__(self):
#         self.__encryptKey = "iEpSxImA0vpMUAabsjJWug=="
#         self.__key = base64.b64decode(self.__encryptKey)
#
#     # AES加密
#     def encrypt(self, data):
#         BS = 16
#         pad = lambda s: s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         encrData = cipher.encrypt(pad(data))
#         # encrData = base64.b64encode(encrData)
#         return encrData
#
#     # AES解密
#     def decrypt(self, encrData):
#         unpad = lambda s: s[0:-s[-1]]
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         decrData = unpad(cipher.decrypt(encrData))
#         return decrData.decode('utf-8')


def Write2CsvF(pathTag, datas, codeflag="utf-8", delimiter="|"):
    print(pathTag)
    if not os.path.exists(os.path.dirname(pathTag)):
        os.makedirs(os.path.dirname(pathTag))

    try:
        with open(pathTag, "w", newline='', encoding=codeflag) as csvFile:

            writer = csv.writer(csvFile, delimiter=delimiter)

            if (str(type(datas)).find("str") + 1):
                writer.writerow(datas)

            elif (str(type(datas)).find("list") + 1):
                writer.writerows(datas)

            elif (str(type(datas)).find("dict") + 1):
                for keys, values in datas.items():
                    writer.writerow([keys, values])

            else:
                csvFile.close()
                return 1

            csvFile.close()
    except:
        print("检查文件名, 文件是否已打开.")

    return 0


def write2Json(msg, file_name):
    '''
    写入json文件
    :param msg:
    :param file_name:
    :return:
    '''
    json_msg = json.dumps(msg, ensure_ascii=False)
    with open(file_name, 'w') as file:
        file.write(json_msg)
        file.close()


def readJsonFile(filename):
    '''
    读取json文件
    :param filename:
    :return:
    '''
    with open(filename) as file:
        json_msg = json.load(file)
    return json_msg


def ReadFromConfig(pathFile, nameSection, dictvalue=''):
    # try:
    if os.path.exists(pathFile):
        with open(pathFile, encoding='UTF-8') as fileIni:
            cf = configparser.ConfigParser()
            # cf.read(pathFile, encoding="utf-8")
            cf.read(pathFile)
            infoConfig = cf.items(nameSection)  # type : [tuple,]
            infoConfig = dict(infoConfig)  # 转为 dict
            # print(infoConfig)
            if dictvalue != "":
                return infoConfig[dictvalue]

        return infoConfig
    else:
        return "error"


def WriteFile(filename, content, write='a'):
    '''
    将一条数据写入到文件中
    :param filename: 写入文件的路径
    :param content: 要写入的内容,换行符一\n分割
    :param write: 写入的方式,a为往后面追加,为写入删除之前的数据
    :return: 成功返回True,失败返回失败的原因
    '''
    try:
        fpath, fname = os.path.split(filename)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        with open(filename, write) as f:  # 如果filename不存在会自动创建, 'w'表示写数据,写之前会清空文件中的原有数据!
            f.write(content)
        return True
    except Exception as e:
        return e


# 文件压缩
def zip_ya(startdir, file_news):
    # startdir = ".\\123"  #要压缩的文件夹路径
    # file_news = startdir +'.zip' # 压缩后文件夹的名字
    z = zipfile.ZipFile(file_news, 'w', zipfile.ZIP_DEFLATED)  # 参数一:文件夹名
    for dirpath, dirnames, filenames in os.walk(startdir):
        fpath = dirpath.replace(startdir, '')  # 这一句很重要,不replace的话,就从根目录开始复制
        fpath = fpath and fpath + os.sep or ''  # 这句话理解我也点郁闷,实现当前文件夹以及包含的所有文件的压缩
        for filename in filenames:
            z.write(os.path.join(dirpath, filename), fpath + filename)
            # print ('压缩成功')
    z.close()


def get_all_file(dirname):
    filelist = []
    for root, dirs, files in os.walk(dirname):
        for name in files:
            filelist.append(os.path.join(root, name))
    return filelist


def zip_dir(dirname, zipfilename):
    filelist = []
    if os.path.isfile(dirname):
        filelist.append(dirname)
    else:
        for root, dirs, files in os.walk(dirname):
            for name in files:
                filelist.append(os.path.join(root, name))

    zf = zipfile.ZipFile(zipfilename, "w", zipfile.zlib.DEFLATED)
    for tar in filelist:
        arcname = tar[len(dirname):]
        zf.write(tar, arcname)


def get_datetime_string(datetime_obj=None, string_format=1, time_interval=0):
    '''
    @ Song Yu
    传入时间和时间差,获取两者相加后的目标时间
    :param datetime_obj: datetime对象
    :param string_format: 返回的数据格式,传入参数与返回数据映射如下
                1  20191018
                2  2019/10/18
                3  2019-10-18
                4  2019_10_18
                5  201910
                6  201910 152711
                7  15:27:11
                8  152711
                9  作为字典返回所有日期数据
    :param time_interval: 目标时间与当前时间的时间间隔
    :return:
    '''
    if not datetime_obj:
        datetime_obj = datetime.datetime.now()
    if type(datetime_obj) == datetime.date:
        datetime_obj = datetime.datetime.combine(datetime_obj, datetime.time(0, 0))
    if type(datetime_obj) == datetime.datetime:
        time_delta = datetime.timedelta(days=time_interval)
        target_date = datetime_obj + time_delta
        datetime_dict = {'date': target_date,
                         "Y": '{}'.format(target_date.year),
                         'M': '{:0>2d}'.format(target_date.month),
                         'D': '{:0>2d}'.format(target_date.day),
                         'h': '{:0>2d}'.format(target_date.hour),
                         "m": '{:0>2d}'.format(target_date.minute),
                         "s": '{:0>2d}'.format(target_date.second)
                         }
        if string_format == 1:  # 日期格式化并返回
            return '{}{}{}'.format(datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 2:
            return '{}/{}/{}'.format(datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 3:
            return '{}-{}-{}'.format(datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 4:
            return '{}_{}_{}'.format(datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 5:
            return '{}{}'.format(datetime_dict['Y'], datetime_dict['M'])
        elif string_format == 6:
            return '{}{}{} {}{}{}'.format(
                datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'],
                datetime_dict['h'], datetime_dict['m'], datetime_dict['s'])
        elif string_format == 7:
            return '{}:{}:{}'.format(datetime_dict['h'], datetime_dict['m'], datetime_dict['s'])
        elif string_format == 8:
            return '{}{}{}'.format(datetime_dict['h'], datetime_dict['m'], datetime_dict['s'])
        elif string_format == 9:
            return datetime_dict
    else:
        return None


def get_datetime_string_v2(datetime_obj=None, time_interval=0, get_str_type='date', date_separator='',
                           time_separator=''):
    if not datetime_obj:
        datetime_obj = datetime.datetime.now()
    if type(datetime_obj) == datetime.date:
        datetime_obj = datetime.datetime.combine(datetime_obj, datetime.time(0, 0))
    if type(datetime_obj) == datetime.datetime:
        time_delta = datetime.timedelta(days=time_interval)
        target_date = datetime_obj + time_delta
        datetime_dict = {'date': target_date,
                         "Y": '{}'.format(target_date.year),
                         'M': '{:0>2d}'.format(target_date.month),
                         'D': '{:0>2d}'.format(target_date.day),
                         'h': '{:0>2d}'.format(target_date.hour),
                         "m": '{:0>2d}'.format(target_date.minute),
                         "s": '{:0>2d}'.format(target_date.second)
                         }
        if get_str_type == 'date':
            return '{year}{date_separator}{month}{date_separator}{day}'.format(year=datetime_dict['Y'],
                                                                               month=datetime_dict['M'],
                                                                               day=datetime_dict['D'],
                                                                               date_separator=date_separator)
        elif get_str_type == 'time':
            return '{hour}{time_separator}{minute}{time_separator}{second}'.format(hour=datetime_dict['h'],
                                                                                   minute=datetime_dict['m'],
                                                                                   second=datetime_dict['s'],
                                                                                   time_separator=time_separator)
        elif get_str_type == 'datetime':
            return '{year}{date_separator}{month}{date_separator}{day} {hour}{time_separator}{minute}{time_separator}{second}'.format(
                year=datetime_dict['Y'],
                month=datetime_dict['M'],
                day=datetime_dict['D'],
                hour=datetime_dict['h'],
                minute=datetime_dict['m'],
                second=datetime_dict['s'],
                date_separator=date_separator,
                time_separator=time_separator,
            )
        elif get_str_type == 'datetime_dict':
            return datetime_dict


def getLastMonthDate(format=0):
    first_day = datetime.date(datetime.date.today().year, datetime.date.today().month, 1)  # 本月第一天
    last = first_day - datetime.timedelta(1)  # 获取上个月最后一天日期
    if format == 0:
        return str(last)
    else:
        return str(last).replace('-', '')


class Rfs(object):
    status = {
        'NoDevice': 'key未接入或者未供电',
        'NoLink': 'key未连接',
        'Linked': '连接成功',
    }
    local_ip = '10.1.2.87'
    pathFile = r"D:\ChinaCoalAuto\Common\Config\USBHubConfig.ini"  # 配置文件路径

    def get_allPort_msg(self):
        result = {}
        temp_result = os.popen('RfsNu -l')
        res = temp_result.read()
        No = 1
        for line in res.splitlines():
            if 'UsbNumber' in line:
                temp = line.strip().split(' ')
                result[No] = {
                    temp[0].split(':')[0]: temp[0].split(':')[1],
                    temp[1].split(':')[0]: temp[1].split(':')[1],
                    temp[2].split(':')[0]: temp[2].split(':')[1],
                    temp[3].split(':')[0]: temp[3].split(':')[1],
                    temp[-1].split(':')[0]: temp[-1].split(':')[1],
                }
                No += 1
        return result

    def get_port_msg(self, port):
        port_status = self.get_allPort_msg()
        if isinstance(port, int):
            return port_status[port]

    def is_linked(self, port):
        port_status = self.get_port_msg(port)
        if 'NoDevice' == port_status['linkstatus']:
            return 'NoDevice', port_status['linked']
        elif 'NoLink' == port_status['linkstatus']:
            return 'NoLink', port_status['linked']
        elif 'Linked' == port_status['linkstatus']:
            return 'Linked', port_status['linked']

    def wait_for_linked(self, portName, wait_time):
        port = int(self.ReadFromConfig(Rfs.pathFile, "Main", portName))
        status = None
        while wait_time >= 0:
            status, ip = self.is_linked(port)
            print(port, status)
            if status == 'Linked':
                if self.local_ip in ip:
                    break
                else:
                    raise PortHasBeenUsedException('The port has been used')
            else:
                time.sleep(1)
                wait_time -= 1

        if status != 'Linked':
            raise NotFoundKeyException('Not checked the key')  # key未连接时,抛出异常结束后续操作

    # 读取配置文件 Section->{k:v}
    def ReadFromConfig(self, pathFile, nameSection, dictvalue=''):
        # try:
        if os.path.exists(pathFile):
            with open(pathFile, encoding='UTF-8') as fileIni:
                cf = configparser.ConfigParser()
                cf.read(pathFile, encoding='UTF-8')
                infoConfig = cf.items(nameSection)  # type : [tuple,]
                infoConfig = dict(infoConfig)  # 转为 dict
                # print(infoConfig)
                if dictvalue != "":
                    return infoConfig[dictvalue]
            return infoConfig
        else:
            return "error"


class Mysql(object):
    '''
    操作mysql数据库
    '''

    def __init__(self, host, user, password, port=3306, database=None, logger=None, **kwargs):
        '''
        初始化连接数据库
        :parameter huang
        :param host: 地址
        :param user:用户名
        :param password:密码
        :param database:数据库名称
        :param port:端口号
        :param kwargs: 以下参数
                 unix_socket=None,charset='', sql_mode=None,
                 read_default_file=None, conv=None, use_unicode=None,
                 client_flag=0, cursorclass=Cursor, init_command=None,
                 connect_timeout=10, ssl=None, read_default_group=None,
                 compress=None, named_pipe=None,autocommit=False, db=None,
                 passwd=None, local_infile=False,max_allowed_packet=16*1024*1024,
                 defer_connect=False,auth_plugin_map=None, read_timeout=None,
                 write_timeout=None,bind_address=None, binary_prefix=False,
                 program_name=None,server_public_key=None
        '''
        self.logger = logger
        print('连接到mysql服务器...')
        self.logger and self.logger.info('连接到mysql服务器...')
        self.db = pymysql.connect(host=host, user=user, password=password, database=database, port=port, **kwargs)
        print('数据库连接成功!')
        self.logger and self.logger.info('数据库连接成功!')

    def insertdb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行sql语句
            self.db.commit()  # 提交到数据库执行
        except Exception as e:
            logging.error(e)
            logging.error('插入数据失败!')
            self.db.rollback()  # 回滚
        finally:
            cursor.close()

    def updatedb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行SQL语句
            self.db.commit()  # 提交到数据库执行
        except:
            logging.error('更新数据失败!')
            self.db.rollback()  # 发生错误时回滚
        finally:
            cursor.close()

    def update_db_many(self, sql, data):
        '''
            批量插入
        :param sql:
        :param data:列表字典
        :return:
            example:
                db = Mysql("localhost", "root", "admin123", 'ccyboa_service', port=3306)
                sql = ("INSERT INTO `ops_data_bank_copy`(batch_num,data_comment_1,data_decimal_1,data_datetime_5,data_int_1,is_delete) "
                       "VALUES(%s,%s,%s,%s,%s,%s)")
                data = [
                    ('test_20111111', 'test_2019', '33004', '2019/01/02', 234, 1),
                    ('test_2011222', 'test_2018', '33001', '2019/01/03', 235, 1),
                    ('test_2011333', 'test_2017', '33002', '2019/01/04', 234, 1),
                    ('test_20114441', 'test_2019', '33004', '2019/01/05', 234, 1),
                ]
                db.insert_db_many(sql,data)
        '''
        cursor = self.db.cursor()
        try:
            cursor.executemany(sql, data)  # 批量执行多条插入SQL语句
            self.db.commit()  # 提交事务
        except Exception as e:
            logging.error(e)
            self.db.rollback()  # 有异常,回滚事务
            raise e
        finally:
            cursor.close()

    def select_db_fetchall(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        # sql = "SELECT * FROM ops_data_bank where batch_num like '%20191202'"
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchall()  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            return results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchone(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchone()  # 获取查询的所有记录
            return title_name, results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchmany(self, sql, size=None, *args):
        '''
        数据库结果查询
        :param sql:
        :param size: 条目
        :param args:
        :return: 数据元组
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql, *args)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchmany(size)  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            print(results)
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def closedb(self):
        '''
        关闭数据库
        :return:
        '''
        self.db.close()

    def __del__(self):
        self.closedb()
        print("数据库关闭成功,__del__对象已经销毁")


class MysqlHelper(object):

    def __init__(self, logger=None):

        self.logger = logger
        self.conn = None

    def connect(self, sql_setting):
        print('连接到mysql服务器...')
        self.logger and self.logger.info('连接到mysql服务器...')
        self.conn = pymysql.connect(
            host=sql_setting['host'],
            user=sql_setting['user'],
            password=sql_setting['password'],
            database=sql_setting['database'],
            port=sql_setting['port'],
        )
        self.cursor = self.conn.cursor()
        print('数据库连接成功!')
        self.logger and self.logger.info('数据库连接成功!')

    def insert_line(self, table, raise_error=True, **kwargs):
        res_info = {
            'status': False,
            'id': None,
            'rowcount': 1,
        }
        status = False
        sql = "INSERT INTO {}(".format(table)
        keys = ''
        values = ''
        for k, v in kwargs.items():
            keys += '{},'.format(k)
            if v == 'NULL':
                values += "{},".format(v)
            else:
                values += "'{}',".format(v)
        keys = keys.rstrip(',')
        values = values.rstrip(',')
        sql = sql + keys + ")values(" + values + ")"
        print('sql', sql)
        try:
            self.cursor.execute(sql)  # 执行sql语句
            self.conn.commit()  # 提交到数据库执行
            # 影响的行数
            rowcount = self.cursor.rowcount
            res_info['status'] = True
            res_info['id'] = self.cursor.lastrowid  # 最后插入的数据id
        except Exception as e:
            print('插入数据失败!', e)
            self.logger and self.logger.info('插入数据失败!', e)
            self.conn.rollback()  # 回滚
            res_info['status'] = False
            if raise_error:
                raise e
        return res_info

    def insert_list(self, table, keys, values, raise_error=True):
        res_info = {
            'status': False,
            'id': None,
            'rowcount': 1,
        }
        status = False
        sql = "INSERT INTO {table}({keys}) VALUES({values})".format(table=table, keys=','.join(keys),
                                                                    values=','.join(['%s' for item in keys]))
        print(sql)
        try:
            self.cursor.executemany(sql, values)  # 执行sql语句
            self.conn.commit()  # 提交到数据库执行
            res_info['status'] = True
            res_info['id'] = self.cursor.lastrowid  # 最后插入的数据id
            res_info['rowcount'] = self.cursor.rowcount  # 影响的行数
        except Exception as e:
            print('插入数据失败!', e)
            self.logger and self.logger.info('插入数据失败!', e)
            self.conn.rollback()  # 回滚
            res_info['status'] = False
            if raise_error:
                raise e
        return res_info

    def delete(self, table, condition, ):
        status = False
        sql = "DELETE FROM {} where {}".format(table, condition)
        print('sql', sql)
        try:
            self.cursor.execute(sql)  # 执行sql语句
            self.conn.commit()  # 提交到数据库执行
            # 影响的行数
            rowcount = self.cursor.rowcount
            status = True
            # return rowcount
        except Exception as e:
            print('删除数据失败!', e)
            self.logger and self.logger.info('删除数据失败!', e)
            self.conn.rollback()  # 回滚
            status = False
        return status

    def update(self, table, condition, raise_error=True, **kwargs):
        res_info = {
            'status': False,
            'id': None,
            'rowcount': 1,
        }
        # UPDATE t_person SET NAME = '大乔' WHERE country = '蜀国'
        sql = "UPDATE {} SET ".format(table)
        keys = ""
        values = ""
        for k, v in kwargs.items():
            if v == 'NULL':
                sql += '{}={},'.format(k, v)
            else:
                sql += '{}="{}",'.format(k, v)
        sql = sql.rstrip(',')
        sql += 'WHERE {}'.format(condition)
        print('update sql', sql)
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            # 提交到数据库执行
            self.conn.commit()
            # 影响的行数
            rowcount = self.cursor.rowcount
            res_info['status'] = True
        except Exception as e:
            print('更新数据失败!', e)
            self.logger and self.logger.info('更新数据失败!', e)
            self.conn.rollback()  # 回滚
            res_info['status'] = False
            if raise_error:
                raise e
        return res_info

    def select(self, sql):
        print(sql)
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            # 提交到数据库执行
            self.conn.commit()
            # 影响的行数
            rowcount = self.cursor.rowcount
        except Exception as e:
            print('查询数据失败!', e)
            self.logger and self.logger.info('查询数据失败!', e)
            self.conn.rollback()  # 回滚
        title_name = [mem[0] for mem in self.cursor.description]
        results = self.cursor.fetchall()  # 获取查询的所有记录
        data_list = list()
        for item in results:
            data_list.append(dict(zip(title_name, item)))
        return data_list

    def update_db_many(self, sql, data):
        '''
            批量插入
        :param sql:
        :param data:列表字典
        :return:
            example:
                db = Mysql("localhost", "root", "admin123", 'ccyboa_service', port=3306)
                sql = ("INSERT INTO `ops_data_bank_copy`(batch_num,data_comment_1,data_decimal_1,data_datetime_5,data_int_1,is_delete) "
                       "VALUES(%s,%s,%s,%s,%s,%s)")
                data = [
                    ('test_20111111', 'test_2019', '33004', '2019/01/02', 234, 1),
                    ('test_2011222', 'test_2018', '33001', '2019/01/03', 235, 1),
                    ('test_2011333', 'test_2017', '33002', '2019/01/04', 234, 1),
                    ('test_20114441', 'test_2019', '33004', '2019/01/05', 234, 1),
                ]
                db.insert_db_many(sql,data)
        '''
        cursor = self.conn.cursor()
        try:
            cursor.executemany(sql, data)  # 批量执行多条插入SQL语句
            self.conn.commit()  # 提交事务
        except Exception as e:
            self.logger.error(e)
            self.conn.rollback()  # 有异常,回滚事务
            raise e
        finally:
            cursor.close()

    def select_fetchall(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.conn.cursor()
        # sql = "SELECT * FROM ops_data_bank where batch_num like '%20191202'"
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [item[0] for item in cursor.description]
            results = cursor.fetchall()  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            return results
        except Exception as e:
            self.logger.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_list(self, table, condition, ):
        '''
        数据库查询
        :param sql:
        :return:
        '''

        sql = "SELECT * FROM {TABLE} where {CONDITION}".format(TABLE=table, CONDITION=condition)
        print('sql', sql)
        try:
            self.cursor.execute(sql)  # 执行sql语句
        except Exception as e:
            print('查询数据失败!', e)
            self.logger and self.logger.error('查询数据失败!', e)
        title_name = [mem[0] for mem in self.cursor.description]
        results = self.cursor.fetchall()  # 获取查询的所有记录
        data_list = list()
        for item in results:
            data_list.append(dict(zip(title_name, item)))
        return data_list

    def select_db_fetchone(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchone()  # 获取查询的所有记录
            return title_name, results
        except Exception as e:
            self.logger.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchmany(self, sql, size=None, *args):
        '''
        数据库结果查询
        :param sql:
        :param size: 条目
        :param args:
        :return: 数据元组
        '''
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql, *args)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchmany(size)  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            print(results)
        except Exception as e:
            self.logger.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def close_connect(self):
        self.conn.close()

    def __del__(self):
        self.close_connect()
        print("数据库关闭成功,__del__对象已经销毁")
        self.logger and self.logger.info('数据库关闭成功,__del__对象已经销毁')


def csvToList(excelPath):
    '''
    打开csv文件,转换成list
    :param excelPath:
    :return:
    '''
    if os.path.exists(excelPath):
        # 打开csv
        # excelPath = "D:/autoit/项目/华鑫证券/文档/增值税取数文档及模板/附表二.csv"
        print("打开路径下的文件:", excelPath)
        try:
            df = pd.read_csv(open(excelPath), index_col=False, header=None, error_bad_lines=False)
        except Exception as e:
            print("打开文件失败:", e)
            df = pd.read_csv(open(excelPath, encoding='UTF-8'), index_col=False, header=None, error_bad_lines=False)
        train_data = pd.np.array(df)  # np.ndarray()
        train_x_list = train_data.tolist()  # list
    else:
        train_x_list = [[]]
    return train_x_list


def listToCSV(csvPath, csvList):
    '''
    讲列表写入到csv
    :param csvPath: 写入的路径
    :param csvList: 写入的列表
    :return:
    '''
    df_data = pd.DataFrame(csvList)
    df_data.to_csv(csvPath, header=False, index=False, encoding='gb2312')


def resultWriteCSV(resultPath, resultTemplatePath, writeResult):
    '''
    将结果写入到csv文件中
    :param resultPath: 写入文件的路径
    :param resultTemplatePath: 模板的路径
    :param writeResult: 写入的数据值
    :return:
    '''
    if not os.path.exists(resultPath):
        mycopyfile(resultTemplatePath, resultPath)
    readLists = csvToList(resultPath)
    for i in range(0, len(readLists)):
        if readLists[i][0] == writeResult[0]:
            readLists[i][1] = writeResult[1]
            readLists[i][2] = writeResult[2]
            readLists[i][3] = writeResult[3]
            readLists[i][4] = writeResult[4]
    listToCSV(resultPath, readLists)


def create_chrome_debugger(url):
    """
    创建谷歌浏览器,成功返回
    :param url:要打开的url
    :return: 若成功,返回driver对象,失败返回False
    :param url:
    :return:
    """
    try:
        # --user-data-dir="--user-data-dir=C:\Users\MrAme\AppData\Local\Google\Chrome\User Data"
        p = subprocess.Popen(
            r'chrome.exe --remote-debugging-port=9222 ',
            shell=True,
            stdin=subprocess.PIPE,
            stdout=subprocess.PIPE, stderr=subprocess.STDOUT, cwd=os.path.dirname(
                r'C:\Program Files\Google\Chrome\Application\chrome.exe'))
        chrome_option = Options()
        chrome_option.add_experimental_option("debuggerAddress", "127.0.0.1:9222")
        driver = webdriver.Chrome(chrome_options=chrome_option)
        driver.maximize_window()  # 最大化窗口
        driver.get(url)
    except Exception as e:
        print("creatChrome:", e)
        driver = False

    return driver


def create_chrome(url):
    """
    创建谷歌浏览器,成功返回
    :param url:要打开的url
    :return: 若成功,返回driver对象,失败返回False
    :param url:
    :return:
    """
    try:
        options = webdriver.ChromeOptions()
        # options.add_argument(r'--user-data-dir=C:\Users\MrAme\AppData\Local\Google\Chrome\User Data') # 个人资料路径
        # 关闭“chrome正受到自动测试软件的控制”
        # V75以及以下版本
        # option.add_argument('disable-infobars')
        # V76以及以上版本
        options.add_experimental_option('useAutomationExtension', False)
        options.add_experimental_option('excludeSwitches', ['enable-automation'])
        prefs = {}
        # 设置这两个参数就可以避免密码提示框的弹出
        prefs['credentials_enable_service'] = False
        prefs['profile.password_manager_enabled'] = False
        # download
        prefs['download.prompt_for_download'] = True

        options.add_experimental_option('prefs', prefs)
        # 不自动关闭浏览器
        options.add_experimental_option("detach", True)
        # options.add_argument('-proxy-server=http://122.192.29.105:8118')
        # options.add_argument('headless') # 设置后台运行
        # driver = webdriver.Chrome(options=options)
        driver = webdriver.Chrome(chrome_options=options)
        driver.maximize_window()
        driver.get(url)
    except Exception as e:
        print("creatChrome:", e)
        driver = False

    return driver


# def creatChrome(url):
#     """
#     创建谷歌浏览器,成功返回
#     :param url:要打开的url
#     :return: 若成功,返回driver对象,失败返回False
#     :param url:
#     :return:
#     """
#     try:
#         driver = webdriver.Chrome()
#         driver.maximize_window()  # 最大化窗口
#         driver.get(url)
#     except Exception as e:
#         driver = False
#
#     return driver


def createIE(url):
    """
    创建IE浏览器,成功返回
    :param url:要打开的url
    :return: 若成功,返回driver对象,失败返回False
    """
    try:
        driver = webdriver.Ie()
        driver.maximize_window()  # 最大化窗口
        driver.get(url)
    except Exception as e:
        print("creatChrame:", e)
        driver = False

    return driver


def element_exist(driver, element):
    try:
        driver.find_element_by_xpath(element)
    except Exception as e:
        print("[Error:element_exist]", e)
        return False
    return True


def wait_element_exist(driver, element, timeout=5, raise_error=True):
    """

    :param driver:
    :param element:
    :param timeout:
    :param raise_error:
    :return:
    """
    status = False
    try:
        WebDriverWait(driver, timeout, 0.5).until(
            EC.presence_of_element_located((By.XPATH, element)))
        status = True
    except Exception as e:
        if raise_error:
            raise e
        print("[Error:wait_element_exist]", e)
        status = False
    return status


def wait_element_click(driver, element, timeout=5, raise_error=True):
    status = False
    try:
        WebDriverWait(driver, timeout, 0.5).until(EC.presence_of_element_located((By.XPATH, element)))
        driver.find_element_by_xpath(element).click()
        print('点击成功 {}'.format(element))
        status = True
    except Exception as e:
        if raise_error:
            raise e
        print("[Error:wait_click_element]:{},element={}".format(e, element))
        status = False
    return status


def wait_element_send(driver, element, value, timeout=5, raise_error=True):
    """

    :param driver:
    :param element:
    :param value:
    :param timeout:
    :return:
    """
    status = False
    try:
        WebDriverWait(driver, timeout, 0.5).until(EC.presence_of_element_located((By.XPATH, element)))
        driver.find_element_by_xpath(element).clear()
        driver.find_element_by_xpath(element).send_keys(str(value))
        status = True
    except Exception as e:
        if raise_error:
            raise e
        print("[Error:sendKeys]", e)
        status = False
    return status


def wait_frame_switch(driver, element, timeout=5, raise_error=False):
    status = False
    try:
        WebDriverWait(driver, timeout, 0.5).until(EC.presence_of_element_located((By.XPATH, element)))
        driver.switch_to.frame(driver.find_element_by_xpath(element))
        print("切换iframe成功")
        status = True
    except Exception as e:
        if raise_error:
            raise e
        print("[Error:wait_frame_switch]", e)
        status = False
        print("切换iframe失败")
    time.sleep(0.2)
    return status


def element_get_text_excluding_children(element):
    """
    :param element:
    :return:
    """
    children = element.find_elements_by_xpath('*')
    original_text = element.text
    for child in children:
        original_text = original_text.replace(child.text, '', 1)
    return original_text


def clickJump(driver, element):
    """
    Xpath点击某个元素
    :param driver:
    :param element:
    :return:
    """
    try:
        driver.find_element_by_xpath(element).click()
        clickJumpResult = True
        print('点击成功')
    except Exception as e:
        print("[Error:clickJump]", e)
        clickJumpResult = False
        print('点击失败')
    time.sleep(0.5)
    return clickJumpResult


def JsClick(driver, jspath):
    flag = True
    try:
        js = jspath
        driver.execute_script(js)
        return flag
    except Exception as e:
        print(e)
        flag = False
        return flag


def sendKeys(driver, element, value):
    """
    输入网页页面元素
    :param driver: 浏览器对象
    :param element: 要输入的元素
    :param value: 输入的值
    :return:
    """
    try:
        driver.find_element_by_xpath(element).clear()
        driver.find_element_by_xpath(element).send_keys(str(value))
        sendKeysResult = True
    except Exception as e:
        print("[Error:sendKeys]", e)
        sendKeysResult = False
    return sendKeysResult


def get_attributes(driver, element, attributes, defaultResult=""):
    try:
        text = driver.find_element_by_xpath(element).get_attribute(attributes)
        clickJumpResult = text
    except Exception as e:

        print("[Error:element]", e)
        clickJumpResult = defaultResult

    return clickJumpResult


def get_text(driver, element):
    try:
        text = driver.find_element_by_xpath(element).text
        clickJumpresult = text
    except Exception as e:

        print("[Error:element]", e)
        clickJumpresult = ""

    return clickJumpresult


def alertClick(driver, clickButton="确定", timeOut=10):
    try:
        # 等待alert弹出框可见
        WebDriverWait(driver, timeOut).until(EC.alert_is_present())
        # 从html页面切换到alert弹框
        alert = driver.switch_to.alert
        # 获取alert的文本内容
        slertText = alert.text
        if clickButton == "确定":
            # 接受--选择“确定”
            alert.accept()
        elif clickButton == "取消":
            alert.dismiss()
        alertClickResult = True
    except Exception as e:
        print("[Error:alertClick]", e)
        alertClickResult = False
    return alertClickResult


def selectorSelect(driver, element, value, selectorType="value"):
    '''
    select元素,下拉点击某个属性值
    :param driver: 浏览器driver对象
    :param element: 浏览器元素
    :param value: value值
    :param selectorType: 选择的类型,按值选择;按索引选择;按文本选择
    :return: 现在成功返回true,失败返回false
    '''
    try:
        selector = Select(driver.find_element_by_xpath(element))
        if selectorType == "value":
            selector.select_by_value(value)
        elif selectorType == "index":
            selector.select_by_index(value)
        elif selectorType == "text":
            selector.select_by_visible_text(value)
        selectorSelectResult = True
    except Exception as e:
        print("[Error:selectorSelect]", e)
        selectorSelectResult = False
    return selectorSelectResult


def switchToFrame(driver, frameXpath):
    """
    跳转到指定iframe标签里
    :param driver: 浏览器的driver对象
    :param frameXpath: iframe的xpath
    :return:
    """
    try:
        driver.switch_to.frame(driver.find_element_by_xpath(frameXpath))
        switchToFrameResult = True
        print("切换iframe成功")
    except Exception as e:
        print("[Error:switchToFrame]", e)
        switchToFrameResult = False
        print("切换iframe失败")
    time.sleep(0.2)
    return switchToFrameResult


def switchToFatherIframe(driver):
    '''
    跳出iframe标签,切换上一级iframe
    :param driver: 浏览器的driver对象
    :return:
    '''
    try:
        driver.switch_to.parent_frame()
        switchToDefault = True
    except Exception as e:
        print("[Error:switchToDefault]", e)
        switchToDefault = False
    return switchToDefault


def writeListToXls(list2D, filepath, rows="", colums=0, sheet_No=0):
    '''
    仅支持xls格式
    :param list2D:  需要添加的二维列表
    :param filepath: 文件路径
    :return:文件写入结果
    '''
    result = False
    try:
        if os.path.exists(filepath):
            # ,行数为空则获取行数
            if rows == "":
                rows = int(xlrd.open_workbook(filepath).sheets()[sheet_No].nrows)
                rows = int(xlrd.open_workbook(filepath).sheets()[sheet_No].nrows)
            data = xlrd.open_workbook(filepath, formatting_info=True)
            ws = copy(data)
            worksheet = ws.get_sheet(sheet_No)
            for row in range(len(list2D)):
                for colum in range(len(list2D[row])):
                    worksheet.write(rows + row, colums + colum, list2D[row][colum])
            ws.save(filepath)
        else:
            # 模板不存在
            workbook = xlwt.Workbook(encoding='ascii')
            worksheet = workbook.add_sheet('sheet_1')
            for row in range(len(list2D)):
                for colum in range(len(list2D[row])):
                    worksheet.write(row, colum, list2D[row][colum])
            workbook.save(filepath)
        result = True
    except Exception as e:
        print(e)
        result = False
    finally:
        return result


def write_list_to_xls_style(list_2D, file_path, start_row=0, start_column=0, sheet_index=0, font_height=10 * 20,
                            font_color_index=0,
                            align_center=False, border=False, append_mode=False):
    """
    # 仅支持xls格式
    @param list_2D: 需要添加的二维列表
    @param file_path: 文件路径
    @param start_row:数据的写入位置 行下标
    @param start_column:数据的写入位置 列下标
    @param sheet_index:sheet编号
    @param font_height:字体大小,10为字号,20为衡量单位
    @param font_color_index:字体颜色 常用颜色 黑色0 白色1 红色2 蓝色4 黄色5 紫色6 青色7
    @param align_center:文字是否居中
    @param border:是否有边框
    @param append_mode:如果是追加模式 则 数据写入起始行数为已经存在的行数
    @return:
    """
    style = xlwt.XFStyle()  # 格式信息
    font = xlwt.Font()  # 字体基本设置
    # font.name = u'宋体'
    font.colour_index = font_color_index
    font.height = font_height
    style.font = font

    if border:
        border = xlwt.Borders()  # 给单元格加框线
        border.left = xlwt.Borders.THIN  # 左
        border.top = xlwt.Borders.THIN  # 上
        border.right = xlwt.Borders.THIN  # 右
        border.bottom = xlwt.Borders.THIN  # 下
        border.left_colour = 0x40  # 设置框线颜色,0x40是黑色,颜色真的巨多,都晕了
        border.right_colour = 0x40
        border.top_colour = 0x40
        border.bottom_colour = 0x40
        style.borders = border
        '''
        # 细实线:1,小粗实线:2,细虚线:3,中细虚线:4,大粗实线:5,双线:6,细点虚线:7
        # 大粗虚线:8,细点划线:9,粗点划线:10,细双点划线:11,粗双点划线:12,斜点划线:13
        borders.left = 1
        borders.right = 2
        borders.top = 3
        borders.bottom = 4
        borders.left_colour = i
        borders.right_colour = i
        borders.top_colour = i
        borders.bottom_colour = i
        '''
        # style.num_format_str = '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_)'

    if align_center:
        al = xlwt.Alignment()
        al.horz = 0x02  # 设置水平居中
        al.vert = 0x01  # 设置垂直居中
        style.alignment = al

    if os.path.exists(file_path):  # 如果文件存在 则在原来文件的基础上写入
        if append_mode:  # 如果是追加模式 则 数据写入起始行数为已经存在的行数
            start_row = int(xlrd.open_workbook(file_path).sheets()[sheet_index].nrows)
        origin_worksheet = xlrd.open_workbook(file_path, formatting_info=True)
        workbook = copy(origin_worksheet)
        worksheet = workbook.get_sheet(sheet_index)
    else:  # 如果文件不存在 直接创建文件写入
        workbook = xlwt.Workbook(encoding='ascii')
        worksheet = workbook.add_sheet('sheet_1')

    for row in range(len(list_2D)):
        for column in range(len(list_2D[row])):
            worksheet.write(start_row + row, start_column + column, list_2D[row][column], style)
    workbook.save(file_path)


def SaveasFile(FilePath):
    path = os.path.split(FilePath)[0]
    createPath(path)
    saveasTitle = "另存为"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:2]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("另存为窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def IESaveas(filePath):
    imagePath = r'D:\亿利\PythonCode\sanjiao.bmp'
    imagePath2 = r'D:\亿利\PythonCode\lingcunwei.bmp'
    ImageSearchs = ImageSearch.ImageSearchs()
    ImageSearchs.search(imagePath)
    ImageSearchs.click(imagePath)
    time.sleep(0.5)
    autoit.send("A")
    # ImageSearchs.search(imagePath2)
    # ImageSearchs.click(imagePath2)
    SaveasFile(filePath)


def clear_send(driver, element, value):
    '''
    输入网页页面元素
    :param driver: 浏览器对象
    :param element: 要输入的元素
    :param value: 输入的值
    :return:
    '''
    try:
        # 设置焦点
        ele = driver.find_element_by_xpath(element)
        # 利用js将为元素设置焦点
        driver.execute_script("arguments[0].focus();", ele)
        driver.find_element_by_xpath(element).click()
        driver.find_element_by_xpath(element).send_keys(Keys.CONTROL, "a")
        driver.find_element_by_xpath(element).send_keys(Keys.DELETE)
        # driver.find_element_by_xpath(element).send_keys(str(value))
        driver.find_element_by_xpath(element).send_keys(str(value))
        sendKeysResult = True
    except Exception as e:
        print("[Error:sendKeys]", e)
        sendKeysResult = False
    return sendKeysResult


def FileExists(filePath):
    '''
    判断文件是否存在
    :param filePath:
    :return:
    '''
    if os.path.exists(filePath):
        return True
    else:
        return False


def createPath(path):
    isExists = os.path.exists(path)
    if not isExists:
        os.makedirs(path)
        print("创建成功:{}".format(path))
    else:
        print("已经存在:{}".format(path))


def waitFileDownload(filePath, waitTime):
    '''
    等待文件下载成功
    :param filePath:文件路径
    :param waitTime:等待时间
    :return:
    '''
    downloadResult = False
    i = 0
    while i < waitTime:
        if os.path.exists(filePath):
            print("文件下载成功")
            downloadResult = True
            break
        else:
            time.sleep(1)
            i += 1
    return downloadResult


def check_file_exist_wait(file_path, timeout=5, raise_error=False):
    status = False
    try:
        print("检查文件是否存在 {}".format(file_path))
        count = 0
        while count < timeout:
            count += 1
            print('当前第{}次检查文件'.format(count))
            if os.path.exists(file_path):
                print("文件{}存在".format(file_path))
                status = True
                break
            else:
                pass
            time.sleep(1)
        if not status:
            print('执行{}秒内没有找到文件'.format(timeout, file_path))
            if raise_error:
                raise Exception('执行{}秒内没有找到文件'.format(timeout, file_path))
    except Exception as e:
        if raise_error:
            raise e
        status = False
    return status


def mycopyfile(srcfile, dstfile):
    '''
    文件复制到指定路径
    :param srcfile:
    :param dstfile:
    :return:
    '''
    if not os.path.isfile(srcfile):
        print("%s not exist!" % (srcfile))
    else:
        fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        shutil.copyfile(srcfile, dstfile)  # 复制文件
        print("copy %s -> %s" % (srcfile, dstfile))


def csvToList(excelPath):
    '''
    打开csv文件,转换成list
    :param excelPath:
    :return:
    '''
    try:
        if os.path.exists(excelPath):
            # 打开csv
            # excelPath = "D:/autoit/项目/华鑫证券/文档/增值税取数文档及模板/附表二.csv"
            print("打开路径下的文件:", excelPath)
            try:
                df = pd.read_csv(open(excelPath), index_col=False, header=None, error_bad_lines=False, dtype=str)
            except Exception as e:
                df = pd.read_csv(open(excelPath, encoding='UTF-8'), index_col=False, header=None, error_bad_lines=False,
                                 dtype=str)
            # train_data = numpy.array(df)  # np.ndarray()
            train_x_list = df.values.tolist()  # list
        else:
            train_x_list = [[]]
    except Exception as e:
        print(e)
        train_x_list = [[]]
    return train_x_list


def df_to_dict_list(df):
    """

    @param df: dataframe对象
    @return: [
            {column1:value1,column2:value2},
            {column1:value3,column2:value4},
            ]
    """
    values_list = df.values.tolist()
    res_list = []
    for i in values_list:
        res_list.append(dict(zip(df.keys(), i)))
    return res_list


def df_to_2d_list(df):
    keys_list = list(df.keys())
    values_list = list(df.values.tolist())
    data_2d_list = [keys_list] + values_list
    return data_2d_list


def list_to_dict_list(list):
    """

    @param list: 传入列表格式的数据
    @return:
    """
    res_list = []
    for i in list[1:]:
        res_list.append(dict(zip(list[0], i)))
    return res_list


def listToCSV(csvPath, csvList):
    '''
    讲列表写入到csv
    :param csvPath: 写入的路径
    :param csvList: 写入的列表
    :return:
    '''
    path = os.path.split(csvPath)[0]
    createPath(path)
    with open(csvPath, 'w'):
        pass
    df_data = pd.DataFrame(csvList)
    # df_data.to_csv(csvPath, header=False, index=False, encoding='gb2312')
    df_data.to_csv(csvPath, header=False, index=False)


def resultWriteCSV(resultPath, writeName, writeResult):
    '''
    将结果写入到csv文件中
    :param resultPath: 写入文件的路径
    :param writeName: 写入行的名字
    :param writeResult: 写入的数据值
    :return:
    '''
    # if not os.path.exists(resultPath):
    #     mycopyfile(resultTemplatePath, resultPath)
    readLists = csvToList(resultPath)
    for i in range(0, len(readLists)):
        if readLists[i][0] == writeName:
            readLists[i][1] = writeResult
    listToCSV(resultPath, readLists)


def image(driver, element, path):
    """
    元素截图
    :param element: 要截图的元素
    :param path: 图片保存路径
    :return:
    """
    try:
        path1 = os.path.split(path)[0]
        createPath(path1)
        # driver.save_screenshot(path)
        # left = element.location['x']
        # top = element.location['y']
        # right = element.location['x'] + element.size['width']
        # bottom = element.location['y'] + element.size['height']
        #
        # # 打开刚才的截图
        # im = Image.open(path)
        # # 截取对应位置
        # im = im.crop((left, top, right, bottom))
        # # 保存覆盖原有截图
        # im.save(path)
        element.screenshot(path)
        result = True
    except Exception as e:
        print(e)
        result = False
    return result


def SelectImage(FilePath):
    saveasTitle = "打开"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:1]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("打开为窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def search_image(imagepath):
    image_search = ImageSearch.ImageSearchs()
    res = image_search.search(imagepath)
    return res


def SaveFile(FilePath):
    saveasTitle = "选择文件/文件夹"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:1]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("选择文件/文件夹窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def combinations(iterable, r):
    # combinations('ABCD', 2) --> AB AC AD BC BD CD
    # combinations(range(4), 3) --> 012 013 023 123
    pool = iterable
    n = len(pool)
    if r > n:
        return
    indices = list(range(r))
    yield tuple(pool[i] for i in indices)
    while True:
        for i in reversed(range(r)):
            if indices[i] != i + n - r:
                break
        else:
            return
        indices[i] += 1
        for j in range(i + 1, r):
            indices[j] = indices[j - 1] + 1
        yield tuple(pool[i] for i in indices)


def listToExcel(list, path):
    # list = [[[["中电建商业保理", "电建一句"], ["中电建商业保理", "电建er句"]], ["中电建商业保理", 5000]], [[["中电建商业保理", "电建san句"], ["中电建商业保理", "电建si句"]], ["中电建商业保理", 6000]]]
    lista = []
    for i in list:
        for j in i[0]:
            listb = [i[1], j]
            lista.append(listb)
    df = pd.DataFrame(lista, columns=['来帐数据', '匹配成功的数据'])
    # 保存到本地excel
    df.to_excel(path, index=False)


def dict_list_to_list(dict_list):
    """

    @param dict_list: 列表套字典的数据类型
    @return: 列索引列表,数据列表
    """
    lis = []
    # if not dict_list:
    #     return
    columns = list(dict_list[0].keys())
    lis.append(columns)
    for i in dict_list:
        lis.append(list(i.values()))
    return lis


def getDate(day):
    end_date = (datetime.datetime.now() + datetime.timedelta(days=day - 1)).strftime("%Y-%m-%d")
    date_list = []
    begin_date = datetime.datetime.strptime(time.strftime('%Y-%m-%d', time.localtime(time.time())), "%Y-%m-%d")
    end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
    while begin_date <= end_date:
        date_str = begin_date.strftime("%Y-%m-%d")
        date_list.append(date_str)
        begin_date += datetime.timedelta(days=1)
    return date_list


def openEXE(path, title):
    """
    先关闭软件再打开软件
    :param path:软件启动路径
    :param title:软件窗口标题
    :return:成功:True, 失败:False
    """
    try:
        autoit.win_close(title)
    except:
        print("窗口不存在,不需要关闭")
    try:
        os.startfile(path)
        result = autoit.win_wait(title, timeout=2)
    except:
        result = 0
    if result == 1:
        return True
    else:
        return False


# 获取剪切板内容
def get_clipboard_text():
    import win32gui
    import win32api
    import win32con
    import win32clipboard as w
    w.OpenClipboard()
    d = w.GetClipboardData(win32con.CF_TEXT)
    w.CloseClipboard()
    return d.decode('GBK')


def getMonthStartEnd():
    """
    获取当月起止日期:格式:2020.09.01 2020.09.30
    :return:2020.09.01 2020.09.30
    """
    now = datetime.datetime.now().date()
    this_month_start = str(datetime.datetime(now.year, now.month, 1).date()).replace("-", ".")
    this_month_end = str(
        datetime.datetime(now.year, now.month, calendar.monthrange(now.year, now.month)[1]).date()).replace("-", ".")
    return this_month_start, this_month_end


def getMonthStartNowEnd():
    """
    获取当月起止日期:格式:2020.09.01 2020.09.15 2020.09.30
    :return:2020.09.01 2020.09.15 2020.09.30
    """
    now = datetime.datetime.now().date()
    this_month_start = str(datetime.datetime(now.year, now.month, 1).date()).replace("-", ".")
    this_month_end = str(
        datetime.datetime(now.year, now.month, calendar.monthrange(now.year, now.month)[1]).date()).replace("-", ".")
    this_month_now = str(now).replace("-", ".")
    return this_month_start, this_month_now, this_month_end


def get_special_date(src_date_obj=None, first_day=1):
    if not src_date_obj:
        src_date_obj = datetime.datetime.now().date()
    now_month_days = calendar.monthrange(src_date_obj.year, src_date_obj.month)[1]  # 当月天数

    now_month_first_day = datetime.date(src_date_obj.year, src_date_obj.month, first_day)  # 当前月第一天
    now_month_last_day = now_month_first_day + datetime.timedelta(days=now_month_days - first_day)  # 当月最后一天

    pre_month_last_day = now_month_first_day - datetime.timedelta(days=first_day)
    pre_month_first_day = datetime.date(pre_month_last_day.year, pre_month_last_day.month, first_day)

    next_month_first_day = now_month_first_day + datetime.timedelta(days=now_month_days)  # 下个月的第一天
    # 求下个月的最后一天
    next_month_days = calendar.monthrange(next_month_first_day.year, next_month_first_day.month)[1]  # 下月天数
    next_month_last_day = next_month_first_day + datetime.timedelta(days=next_month_days - first_day)
    dst_date_dict = {
        'pre_month_first_day': pre_month_first_day,
        'pre_month_last_day': pre_month_last_day,
        'now_month_first_day': now_month_first_day,
        'now_month_last_day': now_month_last_day,
        'next_month_first_day': next_month_first_day,
        'next_month_last_day': next_month_last_day,
    }
    return dst_date_dict


def get_date_span(date_str, date_span):
    """
    获取时间跨度起始值
    @param date_str: 2020-02-02 str
    @param date_span: 10
    @return: 2020.02.12 str
    """
    date_obj = datetime.datetime.strptime(date_str, "%Y-%m-%d")
    res_date_obj = date_obj + relativedelta(days=date_span)
    res_date_str = str(res_date_obj.date())
    return res_date_str


def inputText(title, input_box, text):
    """
    资金系统对输入框输入内容
    :param title:窗口标题
    :param input_box:控件
    :param text:内容
    :return:
    """
    try:
        autoit.control_click(title, input_box)
        autoit.send('^a')
        time.sleep(1)
        autoit.send('{DEL}')
        time.sleep(1)
        autoit.send(text)
        time.sleep(1)
        autoit.send('{ENTER}')
        Result = True
    except Exception as e:
        print(e)
        Result = False
    return Result


def addResultExcel(logging, list, path):
    """
    新建或者追加写入EXCEL文件
    :param logging: 日志
    :param list: 要追加的列表
    :param path: EXCEL文件路径
    :return:成功:True  失败:False
    """
    try:
        logging.info("开始追加写入Excel文件")
        pathParent = os.path.split(path)[0]
        createPath(pathParent)
        # 判断文件是否存在,如果存在就读取文件
        isExists = os.path.exists(path)
        data_list = []
        if isExists:
            data_list = pd.read_excel(path, header=None).values.tolist()
            if data_list:
                list = list[1:]
        newList = data_list + list
        df = pd.DataFrame(newList[1:], columns=newList[0])
        # 将df中的每一列都转换为object类型
        # 保存到本地excel
        df.to_excel(path, index=False)
        Result = True
    except Exception as e:
        logging.exception(e)
        Result = False
    logging.info("追加写入Excel文件结束")
    return Result


def click_move_mouse(img_path, OffsetX=0, OffsetY=0, timeOut=10, sign=False):
    """
    :param sign: 移动后是否点击
    :param timeOut:图片查找超时时间
    :param img_path:图片路径
    :param OffsetX:x偏移
    :param OffsetY:y偏移
    :return:成功:True 失败:False
    """
    Result = False
    print("开始识别图片,并点击")
    im = ImageSearchs()
    if im.click(img_path, OffsetX=OffsetX, OffsetY=OffsetY, timeOut=timeOut):
        Result = True
        print("点击成功")
    if not Result:
        gui_wait_image_click(img_path, confidence=0.99)
    time.sleep(1)
    autoit.mouse_move(160, 10)
    if sign:
        autoit.mouse_click()
    return Result


def click_control_send_text(title, path, text, timeOut=10):
    """
    autoit点击控件并输入文字
    @param title: 窗口标题
    @param path: 控件
    @param text: 输入内容
    @param timeOut: 超时时间
    @return:
    """
    Result = False
    time.sleep(1)
    if autoit.control_click(title, path):
        Result = True
    time.sleep(1)
    autoit.send('^a')
    time.sleep(1)
    autoit.send('{DEL}')
    time.sleep(1)
    autoit.send(text)
    return Result


def GetData(Title, Control_List):
    """
    获取控件的值
    :param Title: 控件窗口标题
    :param Control_List: 控件列表
    :return: 成功:数据列表 失败:空列表
    """
    ResultList = []
    try:
        for control in Control_List:
            text = autoit.control_get_text(Title, control)
            ResultList.append(text)
        ResultList.append(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    except Exception as e:
        print(e)
        ResultList = []
    return ResultList


def task_kill(name, logger=None):
    """
    # 检测是否存在进程 如存在则杀掉
    杀掉进程
    :return:
    """
    result = False

    try:
        os.system("taskkill /F /IM {}".format(name))
        result = True
    except:
        pass
    return result


def WriteExcel(logging, file_path):
    """
    全选复制,并将粘贴板内容写入excel文件
    :param file_path: 写入文件路径
    :param logging:
    :return:
    """
    try:
        autoit.send('^a')
        time.sleep(1)
        autoit.send('^c')
        # 获取剪切板内容
        res = get_clipboard_text()
        # 将数据构造为特定格式
        excel_data_list = []
        for line in res.split('\n'):
            line = line.strip()
            word_list = line.split('\t')
            excel_data_list.append(word_list)
        header_length = len(excel_data_list[0])
        excel_data_list[0].append('操作时间')
        for line in excel_data_list[1:]:
            while len(line) < header_length:
                line.append("")
            line.append(str(datetime.datetime.now()))
        # 将数据写入excel
        logging.info("复制的数据为:\n%s" % excel_data_list)
        res = addResultExcel(logging, excel_data_list, file_path)
        if res:
            logging.info('写入成功')
            WriteResult = True
        else:
            logging.info('写入失败')
            WriteResult = True
    except Exception as e:
        logging.exception(e)
        WriteResult = False
    return WriteResult


def AllCopyList(logging):
    """
    全选复制,并将粘贴板内容写入excel文件
    :param logging:
    :return:
    """
    try:
        autoit.send('^a')
        time.sleep(1)
        autoit.send('^c')
        # 获取剪切板内容
        res = get_clipboard_text()
        # 将数据构造为特定格式
        excel_data_list = []
        for line in res.split('\n'):
            line = line.strip()
            word_list = line.split('\t')
            excel_data_list.append(word_list)
        header_length = len(excel_data_list[0])
        excel_data_list[0].append('操作时间')
        for line in excel_data_list[1:]:
            while len(line) < header_length:
                line.append("")
            line.append(str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
        # 将数据写入excel
        logging.info("复制的数据为:\n%s" % excel_data_list)
    except Exception as e:
        logging.exception(e)
        excel_data_list = []
    return excel_data_list


def SaveasFile(FilePath):
    path = os.path.split(FilePath)[0]
    createPath(path)
    saveasTitle = "另存为"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:2]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("另存为窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def ExportFile(FilePath, title=None, timeout=5, raise_error=True):
    try:
        path = os.path.split(FilePath)[0]
        createPath(path)
        saveasTitle = "导出为Excel"
        if title:
            saveasTitle = title
        savePathControl = "[CLASS:Edit; INSTANCE:1]"
        saveControl = "[CLASS:Button; INSTANCE:2]"

        autoit.win_wait(saveasTitle, timeout)
        if autoit.win_exists(saveasTitle) == 1:
            time.sleep(1)
            autoit.control_set_text(saveasTitle, savePathControl, FilePath)
            time.sleep(1)
            autoit.control_click(saveasTitle, saveControl)
    except Exception as e:
        print(e)
        if raise_error:
            raise e
        time.sleep(1)


def excel_border(excel_file):
    # 打开存好的excel
    app = xw.App()  # 设置应用
    wb = xw.Book(excel_file)  # 打开文件
    ws = wb.sheets['Sheet1']  # 选择表格

    last_column = ws.range(1, 1).end('right').get_address(0, 0)[0]  # 获取最后列
    last_row = ws.range(1, 1).end('down').row  # 获取最后行

    a_range = f'A1:{last_column}{last_row}'  # 生成表格的数据范围

    # 设置边框
    ws.range(a_range).api.Borders(8).LineStyle = 1  # 上边框
    ws.range(a_range).api.Borders(9).LineStyle = 1  # 下边框
    ws.range(a_range).api.Borders(7).LineStyle = 1  # 左边框
    ws.range(a_range).api.Borders(10).LineStyle = 1  # 右边框
    ws.range(a_range).api.Borders(12).LineStyle = 1  # 内横边框
    ws.range(a_range).api.Borders(11).LineStyle = 1  # 内纵边框

    # 保存并关闭excel
    wb.save("E:/Data/小蜜蜂超市销售报表2.xlsx")
    wb.close()
    app.quit()


def get_gray_text(title, re_left, re_right):
    """
    获取窗口灰色输入框里的内容
    @param title:窗口标题
    @param re_left: 待匹配字符串左边的字符
    @param re_right: 待匹配字符串右边的字符
    @return:left和right中间的分组字符串
    """
    try:
        # 获取页面中所有的text
        win_text = autoit.win_get_text(title)
        # 去掉字符串中的/n/t/r
        win_text = win_text.replace('\n', '').replace('\r', '').replace(' ', '')
        # 提取left和right中间的分组字符串
        re_search_str = re.search('{}(.*){}'.format(re_left, re_right), win_text).group(1)
    except Exception as e:
        print("[Error:get_gray_text]", e)
        re_search_str = 'error'
    return re_search_str


def get_dict_list_field_list(lis, field):
    '''

    @param lis: 传入的字典列表
    @param field: 需要查找的field
    @return: 返回的field对应的value列表
    '''
    res = []
    for i in lis:
        res.append(i[field])
    return res


def dict_list_get_value_by_condition(dict_list, condition_key, condition_value, get_key_name):
    """
    根据条件从字典列表中取值
    @param dict_list: [{},{},{}]
    @param condition_key:
    @param condition_value:
    @param get_key_name:
    @return:
    """
    for dict_line in dict_list:
        if dict_line[condition_key] == condition_value:
            return dict_line[get_key_name]
    return None


def dict_list_get_line_by_condition(dict_list, condition_key, condition_value):
    """
    根据条件从字典列表中取值
    @param dict_list: [{},{},{}]
    @param condition_key:
    @param condition_value:
    @param get_key_name:
    @return:
    """
    for dict_line in dict_list:
        if dict_line[condition_key] == condition_value:
            return dict_line
    return None


def dict_list_get_list_by_condition(dict_list, condition_key, condition_value, contain=False, exclude=False):
    res_list = list()
    if not exclude:
        for dict_line in dict_list:
            if type(condition_value) == list:
                if contain:
                    if any([dict_line[condition_key].__contains__(item) for item in condition_value]):
                        res_list.append(dict_line)
                elif not contain:
                    if any([dict_line[condition_key] == item for item in condition_value]):
                        res_list.append(dict_line)
            else:
                if contain:
                    if dict_line[condition_key].__contains__(condition_value):
                        res_list.append(dict_line)
                elif not contain:
                    if dict_line[condition_key] == condition_value:
                        res_list.append(dict_line)
    elif exclude:
        for dict_line in dict_list:
            if type(condition_value) == list:
                if contain:
                    if not any([dict_line[condition_key].__contains__(item) for item in condition_value]):
                        res_list.append(dict_line)
                elif not contain:
                    if not any([dict_line[condition_key] == item for item in condition_value]):
                        res_list.append(dict_line)
            else:
                if contain:
                    if not dict_line[condition_key].__contains__(condition_value):
                        res_list.append(dict_line)
                elif not contain:
                    if dict_line[condition_key] != condition_value:
                        res_list.append(dict_line)
    return res_list


def switch_keyboard_type(language='EN', lower_case=True, logger=None, clean_desktop=True):
    import win32gui
    import win32api
    import win32con
    from win32con import WM_INPUTLANGCHANGEREQUEST
    # set keyboard layout
    # 设置键盘布局

    # 语言代码
    # https://msdn.microsoft.com/en-us/library/cc233982.aspx
    LID = {0x0804: "Chinese (Simplified) (People's Republic of China)",
           0x0409: 'English (United States)'}

    # 获取前景窗口句柄
    hwnd = win32gui.GetForegroundWindow()

    # 获取前景窗口标题
    title = win32gui.GetWindowText(hwnd)
    logger and logger.info('当前窗口:' + title)

    # 获取键盘布局列表
    im_list = win32api.GetKeyboardLayoutList()
    im_list = list(map(hex, im_list))
    logger and logger.info(im_list)
    result = ''
    if language == 'EN':
        # 设置键盘布局为英文
        result = win32api.SendMessage(
            hwnd,
            WM_INPUTLANGCHANGEREQUEST,
            0,
            0x0409)
        if result == 0:
            logger and logger.info('设置英文键盘成功!')
    elif language == 'CN':
        # 设置键盘布局为中文
        result = win32api.SendMessage(
            hwnd,
            WM_INPUTLANGCHANGEREQUEST,
            0,
            0x0804)
        if result == 0:
            logger and logger.info('设置中文键盘成功!')

    if lower_case:
        if win32api.GetKeyState(win32con.VK_CAPITAL) == 1:
            logger and logger.info('当前键盘状态:大写')
            win32api.keybd_event(win32con.VK_CAPITAL, 0, 0, 0)
            win32api.keybd_event(win32con.VK_CAPITAL, 0, win32con.KEYEVENTF_KEYUP, 0)
            logger and logger.info('切换小写键盘成功!')
    if clean_desktop:
        logger and logger.info('清理桌面')
        autoit.send('#m')


class VideoRecord:
    def __init__(self, video_name, video_dir, logger=None):
        self.flag = False
        self.video_name = video_name
        self.date_str = datetime.datetime.now().strftime('%Y-%m-%d')
        self.datetime_str = datetime.datetime.now().strftime('%Y-%m-%d %H-%M-%S')
        self.video_dir = video_dir
        self.video_path = \
            r'{video_dir}\{date_str}\{video_name}\{datetime_str}.avi'.format(
                video_dir=self.video_dir,
                date_str=self.date_str,
                video_name=self.video_name,
                datetime_str=self.datetime_str,
            )
        self.logger = logger
        self.start_time = None
        self.final_time = None

    def video_record(self):  # 录入视频
        screen = ImageGrab.grab()  # 获取当前屏幕
        width, high = screen.size  # 获取当前屏幕的大小
        fourcc = cv2.VideoWriter_fourcc('X', 'V', 'I', 'D')  # MPEG-4编码,文件后缀可为.avi .asf .mov等
        createPath(self.video_dir)
        createPath(os.path.dirname(self.video_path))
        video = cv2.VideoWriter(self.video_path, fourcc, 15, (width, high))  # (文件名,编码器,帧率,视频宽高)
        self.logger.info('录制开始')
        self.start_time = time.time()
        while True:
            if self.flag:
                self.logger.info('录制结束')
                self.final_time = time.time()
                video.release()  # 释放
                time.sleep(1)
                self.video_info()
                break
            im = ImageGrab.grab()  # 图片为RGB模式
            imm = cv2.cvtColor(np.array(im), cv2.COLOR_RGB2BGR)  # 转为opencv的BGR模式
            video.write(imm)  # 写入
            # time.sleep(5) # 等待5秒再次循环

    def on_press(self, key):  # 监听按键
        if key == keyboard.Key.home:
            self.logger.info('捕获到用户输入录屏终止按键 录屏终止')
            self.flag = True  # 改变
            return False  # 返回False,键盘监听结束!

    def video_info(self):  # 视频信息
        self.logger.info('打印录屏信息')
        video = cv2.VideoCapture(self.video_path)  # 记得文件名加格式不要错!
        fps = video.get(cv2.CAP_PROP_FPS)
        frame_sum = video.get(cv2.CAP_PROP_FRAME_COUNT)
        size = (int(video.get(cv2.CAP_PROP_FRAME_WIDTH)), int(video.get(cv2.CAP_PROP_FRAME_HEIGHT)))
        self.logger.info('帧率:{:.1f}'.format(fps))
        self.logger.info('帧数:{:.1f}'.format(frame_sum))
        self.logger.info('分辨率:{}'.format(size))
        self.logger.info('视频时间:{:.3f}秒'.format(int(frame_sum) / fps if frame_sum else 0))
        self.logger.info('录制时间:{:.3f}秒'.format((self.final_time - self.start_time)))
        if frame_sum == 0:
            self.logger.info('推荐帧率:0.00')
        else:
            self.logger.info(
                '推荐帧率:{:.2f}'.format((fps * ((int(frame_sum) / fps) / (self.final_time - self.start_time)))))

    def keyboard_join(self):
        with keyboard.Listener(on_press=self.on_press) as listener:
            if self.flag:
                listener.join()
            else:
                listener.join()


def video_record_decorator(video_name=None):  # video_name=None, logger=None
    from PythonCode import Setting
    from functools import wraps
    def wrapper(func):
        def inner(*args, **kwargs):
            check_video_folder(args[0].logger, Setting.VIDEO_RECORD_DIR, Setting.VIDEO_RECORD_DIR_ROM_LIMIT)
            print(111, args[0].logger)
            video_record = VideoRecord(video_name, Setting.VIDEO_RECORD_DIR, args[0].logger)
            record_th = threading.Thread(target=video_record.video_record)
            record_th.setDaemon(True)
            record_th.start()
            ret = func(*args, **kwargs)
            time.sleep(5)
            video_record.flag = True
            record_th.join()
            return ret

        return inner

    return wrapper


def clear_and_send(content):
    time.sleep(1)
    autoit.send('^a')
    time.sleep(1)
    autoit.send('{DELETE}')
    autoit.send('{BACKSPACE 20}')
    time.sleep(1)
    autoit.send(content)
    time.sleep(1)


def gui_wait_image(img_path, region=None, timeout=5, confidence=0.99, raise_error=True):
    """
    :param timeOut:图片查找超时时间
    :param img_path:图片路径
    :param region:格式 region=(0, 0, 810, 630) 限制搜索图片的区域
    :param confidence:模糊匹配相似度 不传值为默认相似度 数据类型为数字,范围为0到1
    :return:成功:True 失败:False
    """
    status = False
    try:
        print("开始识别图片 {}".format(img_path))
        search_count = 0
        while search_count < timeout:
            search_count += 1
            print('当前第{}次查找图片'.format(search_count))
            if confidence:  # 如果confidence传值了 则相似度使用confidence的值
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, confidence=confidence)
            else:  # 如果confidence未传值 则使用默认相似度
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, )
            if img_pos_center:  # 搜索到图片位置
                status = True
                print('识别图片成功 {}'.format(img_path))
                break
            time.sleep(1)
        if not status:
            print('执行{}秒内没有找到图片'.format(timeout, img_path))
            if raise_error:
                raise Exception('执行{}秒内没有找到图片:{}'.format(timeout, img_path))
    except Exception as e:
        if raise_error:
            raise e
        status = False
    return status


def gui_wait_image_click(img_path, OffsetX=0, OffsetY=0, region=None, timeout=5, sign=False, homing=True,
                         confidence=0.99,
                         double_click=False, right_click=False, raise_error=True):
    """
    :param img_path:图片路径
    :param OffsetX:x偏移
    :param OffsetY:y偏移
    :param sign: 点击图片后移动到初始坐标后是否点击
    :param timeOut:图片查找超时时间
    :param region:格式 region=(0, 0, 810, 630) 限制搜索图片的区域
    :param confidence:模糊匹配相似度 不传值为默认相似度 数据类型为数字,范围为0到1
    :return:成功:True 失败:False
    """
    pyautogui.FAILSAFE = False
    status = False
    try:
        print("开始识别图片,并点击 {}".format(img_path))
        search_count = 0
        while search_count < timeout:
            search_count += 1
            print('当前第{}次查找图片'.format(search_count))
            if confidence:  # 如果confidence传值了 则相似度使用confidence的值
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, confidence=confidence)
            else:  # 如果confidence未传值 则使用默认相似度
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, )
            if img_pos_center:  # 搜索到图片位置
                x = img_pos_center[0] + OffsetX
                y = img_pos_center[1] + OffsetY
                pyautogui.moveTo(x, y, duration=0.2)
                if double_click:
                    pyautogui.doubleClick()
                else:
                    if right_click:
                        pyautogui.rightClick()
                    else:
                        pyautogui.click()
                status = True
                print('点击图片成功 {}'.format(img_path))
                break
            time.sleep(1)
        if not status:
            print('执行{}秒内没有找到图片'.format(timeout))
            if raise_error:
                raise Exception('执行{}秒内没有找到图片:{}'.format(timeout, img_path))

        time.sleep(0.2)
        if homing:
            pyautogui.moveTo(160, 10, duration=0.2)
            if sign:
                pyautogui.click()
    except Exception as e:
        if raise_error:
            raise e
        status = False
    return status


def check_process_list(name, logger=None, ):
    """
    检测某进程数量
    :param logger:
    :return: 0 1 2
    """
    process_list = []
    for proc in psutil.process_iter():
        try:
            pinfo = proc.as_dict(attrs=['pid', 'name'])
            process_list.append(pinfo)
        except psutil.NoSuchProcess:
            pass

    count = [item['name'] for item in process_list].count(name)
    return count


def check_other_python_process(logger):
    logger.info('程序准备启动')
    time.sleep(3)
    process_list = []
    for proc in psutil.process_iter():
        try:
            pinfo = proc.as_dict(attrs=['pid', 'name'])
            process_list.append(pinfo)
        except psutil.NoSuchProcess:
            pass
    logger.info(process_list)
    if [dic['name'] for dic in process_list].count('python.exe') >= 2:
        logger.info('存在其他Python进程,当前进程终止。')
        sys.exit()


def by_paste_enter_text(text, clean=True, check_all=True, enter=True):
    pyperclip.copy(text)  # 复制内容到剪切板
    if clean:
        if check_all:
            time.sleep(0.5)
            pyautogui.hotkey('ctrl', 'a')  # 全选输入框内的内容
            time.sleep(0.5)
            autoit.send('{BACKSPACE}')
            time.sleep(0.5)
            pyautogui.hotkey('ctrl', 'a')  # 全选输入框内的内容
            time.sleep(0.5)
            autoit.send('{DELETE}')
        else:
            time.sleep(0.5)
            autoit.send('{BACKSPACE 50}')
            time.sleep(0.5)
            autoit.send('{DELETE 50}')
            time.sleep(0.5)
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴
    if enter:
        autoit.send('{ENTER}')


def check_video_folder(logger, file_path, limit=10, days=31):
    '''

    @param file_path: 录屏文件夹路径
    @param limit: 录屏文件大小限制 单位:GB
    @param days: 录屏文件保留天数
    @return:
    '''
    from PythonCode import Setting
    from os.path import join, getsize
    logger.info('开始检查录屏文件夹占用空间')
    while 1:
        size = 0
        for root, dirs, files in os.walk(file_path):
            size += sum([getsize(join(root, name)) for name in files])
        gsize = size / 1024 / 1024 / 1024
        logger.info('当前文件夹大小:{:.2f}GB,限制大小:{}GB'.format(gsize, limit))
        if gsize > limit:
            for file_dir in os.listdir(file_path):
                logger.info('文件夹占用空间超限,删除:', file_dir)
                shutil.rmtree(os.path.join(file_path, file_dir))
                break
        else:
            break
    logger.info('文件夹占用检查完毕')


def return_executing_status(file_path, process_name, date_str=None):
    """
    检查流程运行结果
    @param file_path:
    @param process_name:
    @param date_str:
    @return:
    """
    if not date_str:
        date_obj = datetime.datetime.now().date()
        date_str = str(date_obj)
    df = pd.read_excel(file_path, keep_default_na=False)
    status = df.loc[[date_str], [process_name]]
    status = status.values.tolist()[0][0]
    if not status or not float(status):
        return False
    else:
        return True


def str_replace_by_map_file(logger, input_str, map_file):
    """

    @param logger:
    @param input_str:
    @param map_file:
    @return: 替换后的字串 如出现异常则return原字符串
    """
    try:
        output_str = input_str
        folder, file = os.path.split(map_file)
        filename, file_type = os.path.splitext(file)
        char_map_list = []
        if file_type == '.txt':
            logger.info('文件是txt')
        elif file_type == '.csv':
            logger.info('文件是csv')
            char_map_list = df_to_dict_list(pd.read_csv(open(map_file), keep_default_na=False))
        for char_map_line in char_map_list:
            if char_map_line['原字符'] in output_str:
                output_str = output_str.replace(char_map_line['原字符'], char_map_line['新字符'])
        logger.info('str_replace_by_map_file:将{}替换为{}'.format(input_str, output_str))
        return output_str
    except Exception as e:
        logger.info('异常错误{}'.format(e))
        return input_str


def num_to_excel_col(num: int) -> str:
    num = int(num) + 1
    if not 1 <= num <= 18278:
        raise ValueError("Invalid column index {0}".format(num))

    excel_col_name = ""
    while num > 0:
        num, remainder = divmod(num, 26)

        if remainder == 0:
            remainder = 26
            num -= 1
        excel_col_name = chr(remainder + 64) + excel_col_name

    return excel_col_name


class MyFTP:

    def __init__(self, host, port=21, logger=None):

        self.host = host
        self.port = port
        self.ftp = FTP()
        # 重新设置下编码方式
        self.ftp.encoding = 'gbk'
        self.file_list = []
        self.compare_size = False
        self.logger = logger

    def login(self, username, password):
        try:
            timeout = 60
            socket.setdefaulttimeout(timeout)
            # 0主动模式 1 #被动模式
            self.ftp.set_pasv(True)
            # 打开调试级别2,显示详细信息
            # self.ftp.set_debuglevel(2)

            self.debug_print('开始尝试连接到 %s' % self.host)
            self.ftp.connect(self.host, self.port)
            self.debug_print('成功连接到 %s' % self.host)

            self.debug_print('开始尝试登录到 %s' % self.host)
            self.ftp.login(username, password)
            self.debug_print('成功登录到 %s' % self.host)
            self.debug_print(self.ftp.welcome)
        except Exception as err:
            self.debug_print("FTP 连接或登录失败 ,错误描述为:%s" % err)
            pass

    def is_same_size(self, local_file, remote_file):

        try:
            remote_file_size = self.ftp.size(remote_file)
        except Exception as err:
            # self.debug_print("is_same_size() 错误描述为:%s" % err)
            remote_file_size = -1

        try:
            local_file_size = os.path.getsize(local_file)
        except Exception as err:
            # self.debug_print("is_same_size() 错误描述为:%s" % err)
            local_file_size = -1

        self.debug_print('local_file_size:%d  , remote_file_size:%d' % (local_file_size, remote_file_size))
        if remote_file_size == local_file_size:
            return 1
        else:
            return 0

    def download_file(self, local_file, remote_file):

        self.debug_print("download_file()---> local_path = %s ,remote_path = %s" % (local_file, remote_file))

        if self.compare_size and self.is_same_size(local_file, remote_file):
            self.debug_print('%s 文件大小相同,无需下载' % local_file)
            return

        try:
            self.debug_print('>>>>>>>>>>>>下载文件 %s ...' % remote_file)
            buf_size = 1024
            file_handler = open(local_file, 'wb')
            self.ftp.retrbinary('RETR %s' % remote_file, file_handler.write, buf_size)
            self.debug_print('>>>>>>>>>>>>下载文件 %s ... 完成' % remote_file)
        except Exception as err:
            self.debug_print('下载文件出错,出现异常:%s ' % err)
            return

    def download_folder(self, local_path, remote_path):

        self.debug_print("download_file_tree()--->  local_path = %s ,remote_path = %s" % (local_path, remote_path))
        try:
            self.ftp.cwd(remote_path)
        except Exception as err:
            self.debug_print('远程目录%s不存在,继续...' % remote_path + " ,具体错误描述为:%s" % err)
            return

        if not os.path.isdir(local_path):
            self.debug_print('本地目录%s不存在,先创建本地目录' % local_path)
            os.makedirs(local_path)

        self.debug_print('切换至目录: %s' % self.ftp.pwd())

        self.file_list = []
        # 方法回调
        # self.ftp.dir(self.get_file_list)
        self.file_list = self.ftp.nlst(remote_path)
        self.debug_print('self.file_list:{}'.format(self.file_list))

        remote_names = self.file_list
        self.debug_print('远程目录 列表: %s' % remote_names)
        self.debug_print('remote_names:{}'.format(remote_names))
        for item in remote_names:
            local = os.path.join(local_path, os.path.split(item)[-1])
            if self.check_path_type(item):
                self.debug_print("download_file()---> 下载文件: {}".format(item))
                self.download_file(local, item)
            else:
                self.debug_print("download_file_tree()---> 下载目录: {}".format(item))
                self.download_folder(local, item)
            self.ftp.cwd("..")
            self.debug_print('返回上层目录 %s' % self.ftp.pwd())
        return True

    def upload_file(self, local_file, remote_file):

        if not os.path.isfile(local_file):
            self.debug_print('%s 不存在' % local_file)
            return

        if self.compare_size and self.is_same_size(local_file, remote_file):
            self.debug_print('跳过相等的文件: %s' % local_file)
            return
        try:
            self.debug_print('>>>>>>>>>>>>上传文件 %s ...' % local_file)
            buf_size = 1024
            file_handler = open(local_file, 'rb')
            self.ftp.storbinary('STOR %s' % remote_file, file_handler, buf_size)
            self.debug_print('>>>>>>>>>>>>上传文件 %s ... 完成' % local_file)
        except Exception as err:
            self.debug_print('上传文件出错,出现异常:%s ' % err)
            return

    def upload_folder(self, local_path, remote_path):

        if not os.path.isdir(local_path):
            self.debug_print('本地目录 %s 不存在' % local_path)
            return

        try:
            self.ftp.cwd(remote_path)
        except ftplib.error_perm:
            self.ftp.mkd(remote_path)
            self.ftp.cwd(remote_path)

        self.debug_print('切换至远程目录: %s' % self.ftp.pwd())

        local_name_list = os.listdir(local_path)
        for local_name in local_name_list:
            src = os.path.join(local_path, local_name)
            if os.path.isdir(src):
                try:
                    self.ftp.mkd(local_name)
                except Exception as err:
                    self.debug_print("目录已存在 %s ,具体错误描述为:%s" % (local_name, err))
                self.debug_print("upload_folder()---> 上传目录: %s" % local_name)
                self.upload_folder(src, local_name)
            else:
                self.debug_print("upload_folder()---> 上传文件: %s" % local_name)
                self.upload_file(src, local_name)
        self.ftp.cwd("..")

    def close(self):
        self.debug_print("close()---> FTP退出")
        self.ftp.quit()

    def debug_print(self, s):

        if self.logger:
            self.write_log(s)
        else:
            print(s)

    def write_log(self, log_str):

        # time_now = time.localtime()
        # date_now = time.strftime('%Y-%m-%d', time_now)
        # format_log_str = "%s ---> %s \n " % (date_now, log_str)
        # print(format_log_str)
        # self.log_file.write(format_log_str)
        self.logger.info(log_str)

    def get_file_list(self, line):

        file_arr = self.get_file_name(line)
        # 去除  . 和  ..
        if file_arr[1] not in ['.', '..']:
            self.file_list.append(file_arr)

    def get_file_name(self, line):

        pos = line.rfind(':')
        while (line[pos] != ' '):
            pos += 1
        while (line[pos] == ' '):
            pos += 1
        file_arr = [line[0], line[pos:]]
        return file_arr

    def delete_folder(self, url):
        self.debug_print('url:' + url)
        currntFiles = self.ftp.nlst(url)
        self.debug_print('currntFiles:{}'.format(currntFiles))
        for file in currntFiles:
            # path = url + '/' + file
            if self.check_path_type(file):
                self.ftp.delete(file)
            else:
                self.delete_folder(file)
        self.ftp.rmd(url)
        # 使用nlst函数展示当前目录时,默认进入该目录下,需要退出该目录
        self.ftp.cwd("..")

    def check_path_type(self, file_name):

        rec = None
        try:
            rec = self.ftp.cwd(file_name)  # 需要判断的元素
            self.ftp.cwd("..")  # 如果能通过路劲打开必为文件夹,在此返回上一级
        except ftplib.error_perm as fe:
            rec = fe  # 不能通过路劲打开必为文件,抓取其错误信息

        finally:
            resutStr = str(rec).split(' ')
            resultstr = resutStr[0]
            if resultstr == '250':
                self.debug_print('文件夹' + file_name)
                return False
            elif resultstr == '550':
                self.debug_print('文件' + file_name)
                return True
            else:
                self.debug_print('不识别' + file_name)
                return True

    def __del__(self):
        self.close()


def send_email(file_path=None, subject='', text='', receivers=None, logger=None):
    import smtplib
    from PythonCode import Setting
    from email.mime.text import MIMEText
    from email.mime.base import MIMEBase
    from email.mime.multipart import MIMEMultipart
    from email.header import Header
    from email import encoders  # 转码
    try:
        if not file_path:
            file_path = []
        elif not isinstance(file_path, list):
            file_path = [file_path]
        for file_path_index, file_path_item in enumerate(file_path):
            if not os.path.exists(file_path_item):
                logger.info('email:没有指定文件')
                raise Exception('email:没有指定文件')
            if os.path.isdir(file_path_item):
                import zipfile  # 导入模块
                '''
                    压缩指定文件夹
                    :param dirpath: 目标文件夹路径
                    :param outFullName:  压缩文件保存路径+XXXX.zip
                    :return: 无
                '''
                zip = zipfile.ZipFile(r'{}.zip'.format(file_path_item), 'w', zipfile.ZIP_DEFLATED)
                for path, dirnames, filenames in os.walk(file_path_item):
                    # 去掉目标和路径,只对目标文件夹下边的文件及文件夹进行压缩(包括父文件夹本身)
                    this_path = os.path.abspath('.')
                    fpath = path.replace(this_path, '')
                    for filename in filenames:
                        zip.write(os.path.join(path, filename), os.path.join(fpath, filename))
                zip.close()
                file_path[file_path_index] = r'{}.zip'.format(file_path_item)
        print("file_path:", file_path)
        # 发送邮件
        # 第三方 SMTP 服务
        email_host = Setting.EMAIL_SETTING['email_host']  # 设置服务器   # 勿动
        email_username = Setting.EMAIL_SETTING['email_username']  # 用户名
        email_password = Setting.EMAIL_SETTING['email_password']  # 口令
        # 设置收件人和发件人
        email_sender = Setting.EMAIL_SETTING['email_username']
        if not receivers:
            receivers = list()
            receivers.append(Setting.EMAIL_SETTING['email_username'])  # 接收邮件
            print('receivers', receivers)
        # 创建一个带附件的实例对象
        message = MIMEMultipart()
        # 邮件主题、收件人、发件人
        subject = subject  # 邮件主题
        message['Subject'] = Header(subject, 'utf-8')
        message['From'] = Header("{}".format(email_sender), )  # 发件人 #  'utf-8'
        message['To'] = Header("{}".format(';'.join(receivers)), 'utf-8')  # 收件人
        # 邮件正文内容
        message.attach(MIMEText(text, 'plain', 'utf-8'))
        # 构造附件
        file_path = file_path
        for file_path_item in file_path:
            f = open(file_path_item, 'rb', )
            send_content = f.read()
            att = MIMEBase('application', 'octet-stream')
            att.set_payload(send_content)
            # 用Base64编码
            encoders.encode_base64(att)
            att["Content-Type"] = 'application/octet-stream'
            file_name = os.path.basename(file_path_item)
            att.add_header("Content-Disposition", "attachment", filename=("utf-8", "", file_name))
            message.attach(att)
        smtp_obj = smtplib.SMTP()
        smtp_obj.connect(email_host, 25)  # 25 为 SMTP 端口号
        smtp_obj.login(email_username, email_password)
        smtp_obj.sendmail(email_sender, receivers, message.as_string(), )
        smtp_obj.quit()
        logger.info('邮件发送成功')
        return True
    except Exception as e:
        import traceback
        logger.error(e)
        logger.error(traceback.print_exc())
        return False


def autoit_win_wait(title, timeout=5, raise_error=True):
    """
    """
    status = False
    try:
        autoit.win_wait(title, timeout=timeout)
        status = True
    except Exception as e:
        if raise_error:
            raise e
        status = False
    return status


def autoit_control_get_text(title, control, timeout=5, raise_error=True):
    text = None
    status = False
    search_count = 0
    while search_count < timeout:
        search_count += 1
        print('当前第{}次查找控件'.format(search_count))
        try:
            text = autoit.control_get_text(title, control)
            print('找到控件')
            status = True
            return text
        except Exception as e:
            pass
        time.sleep(1)
    if not status:
        print('执行{}秒内没有找到控件'.format(timeout, control))
        if raise_error:
            raise Exception('执行{}秒内没有找到控件'.format(timeout, control))


def autoit_control_click(title, control, timeout=5, raise_error=True):
    status = False
    search_count = 0
    while search_count < timeout:
        search_count += 1
        print('当前第{}次查找控件'.format(search_count))
        try:
            autoit.control_click(title, control)
            print('找到控件')
            status = True
            break
        except Exception as e:
            pass
        time.sleep(1)
    if not status:
        print('执行{}秒内没有找到控件'.format(timeout, control))
        if raise_error:
            raise Exception('执行{}秒内没有找到控件'.format(timeout, control))


def autoit_control_wait(title, control, click=False, timeout=5, raise_error=True):
    status = False
    search_count = 0
    while search_count < timeout:
        search_count += 1
        print('当前第{}次查找控件'.format(search_count))
        try:
            print('找到控件')
            if click:
                autoit.control_click(title, control)
                print('点击成功')
            status = True
            break
        except Exception as e:
            pass
        time.sleep(1)
    if not status:
        print('执行{}秒内没有找到控件'.format(timeout, control))
        if raise_error:
            raise Exception('执行{}秒内没有找到控件'.format(timeout, control))


def send_sms(phone_num_list, param_list):
    from qcloudsms_py import SmsMultiSender, SmsSingleSender
    from qcloudsms_py.httpclient import HTTPError

    # 腾讯短信
    TENCENT_SMS_APP_ID = 1400328843
    TENCENT_SMS_APP_KEY = "5fbbf47f54a623bc42b6fc1e0ffb4056"
    TENCENT_SMS_SIGN = "UNITEBAN"

    TENCENT_SMS_TEMPLATE = {
        'warn': 1337118,
        'info': 1337568,
    }

    def send_sms_single(phone_num, template_id=TENCENT_SMS_TEMPLATE['info'], param_list=param_list):
        """
        单条发送短信
        :param phone_num: 手机号
        :param template_id: 腾讯云短信模板ID
        :param template_param_list: 短信模板所需参数列表,例如:【验证码:{1},描述:{2}】,则传递参数 [888,666]按顺序去格式化模板
        :return:
        """
        appid = TENCENT_SMS_APP_ID
        appkey = TENCENT_SMS_APP_KEY
        sms_sign = TENCENT_SMS_SIGN
        sender = SmsSingleSender(appid, appkey)
        try:
            response = sender.send_with_param(86, phone_num, template_id, param_list, sign=sms_sign)
        except HTTPError as e:
            response = {'result': 1000, 'errmsg': "网络异常发送失败"}
        return response

    def send_sms_multi(phone_num_list, template_id=TENCENT_SMS_TEMPLATE['info'], param_list=param_list):
        """
        批量发送短信
        :param phone_num_list:手机号列表
        :param template_id:腾讯云短信模板ID
        :param param_list:短信模板所需参数列表,例如:【验证码:{1},描述:{2}】,则传递参数 [888,666]按顺序去格式化模板
        :return:
        """
        appid = TENCENT_SMS_APP_ID
        appkey = TENCENT_SMS_APP_KEY
        sms_sign = TENCENT_SMS_SIGN
        sender = SmsMultiSender(appid, appkey)
        try:
            response = sender.send_with_param(86, phone_num_list, template_id, param_list, sign=sms_sign)
        except HTTPError as e:
            response = {'result': 1000, 'errmsg': "网络异常发送失败"}
        return response

    return send_sms_multi(phone_num_list, param_list=param_list)


def init_mouse_position(x=160, y=10):
    pyautogui.moveTo(x, y, duration=0.2)


def client_run_env_init(logger):
    check_other_python_process(logger=logger)
    switch_keyboard_type(logger=logger)
    init_mouse_position()


def client_run_env_init_decorator():
    from PythonCode import Setting
    from functools import wraps
    def wrapper(func):
        def inner(*args, **kwargs):
            client_run_env_init(args[0].logger)
            ret = func(*args, **kwargs)
            return ret

        return inner

    return wrapper


def remove_dir_content(dir_path):
    for root, dirs, files in os.walk(dir_path, topdown=False):
        for file in files:
            os.remove(os.path.join(root, file))  # 删除文件
        # 第二步:删除空文件夹
        for dir in dirs:
            os.rmdir(os.path.join(root, dir))  # 删除空目录

def backup_dir(src_dir, dst_dir, empty_src_dir=False):
    """

    :param src_dir:
    :param dst_dir:
    :param empty_src_dir:
    :return:
    """
    shutil.copytree(src_dir,dst_dir)
    if empty_src_dir:
        for root, dirs, files in os.walk(src_dir, topdown=False):
            for file in files:
                os.remove(os.path.join(root, file))  # 删除文件
            # 第二步:删除空文件夹
            for dir in dirs:
                os.rmdir(os.path.join(root, dir))  # 删除空目录
# -*- coding:utf-8 -*-
import re
# from Crypto.Cipher import AES
import configparser
import csv
import os
import time
import shutil
import logging
import zipfile
import datetime

import pandas as pd
import xlrd
import xlwt
import inspect
import ctypes
import requests
import json
from xlutils.copy import copy
import pymysql

from PythonCode.Common.ownerException import NotFoundKeyException, PortHasBeenUsedException

# __all__ = [
# #     'PATHPAREBT', 'PATHROOT', 'log', 'renamefile', 'getFormatData'
# # ]
PATHPAREBT = os.path.split(os.path.realpath(__file__))[0]
PATHROOT = os.path.abspath(os.path.dirname(PATHPAREBT) + os.path.sep + "..")

def log(filename):
    '''
    log,文件名要传入绝对路径,
    :param author: 201916820
    :param filename:{str} log文件保存路径
    :return:{object} 返回log对象
    '''
    dstdir = os.path.split(filename)[0]  # 父文件路径
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)

    # 创建一个logger
    logger = logging.getLogger()
    logger.setLevel(logging.DEBUG)
    # 创建一个handler,用于写入日志文件
    fh = logging.FileHandler(filename)
    fh.setLevel(logging.INFO)
    # 再创建一个handler,用于输出到控制台
    ch = logging.StreamHandler()
    ch.setLevel(logging.INFO)
    # 定义handler的输出格式
    formatter = logging.Formatter('%(asctime)s [%(levelname)s] :  %(message)s')
    fh.setFormatter(formatter)
    ch.setFormatter(formatter)

    # 给logger添加handler
    logger.addHandler(fh)
    logger.addHandler(ch)
    # 记录一条日志
    logger.info('')
    # logger.warning()
    # logger.debug()
    # logger.error()
    return logger


def renamefile(srcdir, dstdir, fileName):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return 'NO FILE'  # 空文件夹,不进行复制操作
    for file_m in files:
        fileName = '%s.%s' % (fileName, file_m.split('.')[1])
        file_s = os.path.join(srcdir, file_m)
        file_d = os.path.join(dstdir, fileName)
        if os.path.isfile(file_s):
            os.rename(file_s, file_d)
            return file_d


def remove_file2parent_temp(account, filePath):
    '''
    移动文件到../../Temp文件夹下
    :param account:
    :param filePath:
    :return:
    '''
    path_temp = os.path.abspath(filePath + os.path.sep + "../../Back")
    if not os.path.exists(path_temp):
        os.makedirs(path_temp)  # 创建新文件夹路径
    files = os.listdir(filePath)
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        shutil.move(file_s, path_temp)  # 移动文件


def check_last_creat_file(account, filePath):
    if '/' in account:
        account = account.split('/')[1]
    files = os.listdir(filePath)
    file_dict = {}
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        if not os.path.isfile(file_s):
            continue
        file_time = os.stat(file_s).st_mtime
        file_dict[file_time] = file_s
    if not file_dict:
        return
    max_time = max(file_dict.keys())
    return file_dict[max_time]


def movefile(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        if os.path.isfile(file_v):
            files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件


def move_file_and_dir(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件



def mycopyfile(srcfile, dstfile):
    if not os.path.isfile(srcfile):
        print("%s not exist!" % (srcfile))
    else:
        fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        shutil.copyfile(srcfile, dstfile)  # 复制文件
        print("copy %s -> %s" % (srcfile, dstfile))


def FileMove(srcfile, dstfile):
    time = getFormatData(format=8)
    if os.path.exists(srcfile):
        try:
            spath, sname = os.path.split(srcfile)
            fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
            dstfile = os.path.join(fpath,time+'_'+sname)
            if not os.path.exists(fpath):
                os.makedirs(fpath)  # 创建路径
            shutil.move(srcfile, dstfile)  # 复制文件
            print("文件移动成功")
        except:
            return "文件移动失败"
    else:
        return "文件不存在"

def rename(srcfile, dstdir, new_name):
    '''
    文件重命名
    :param srcfile:{str} 原始文件
    :param dstdir: {str} 新文件文件夹
    :param new_name: {str} 文件名
    :return: None
    '''
    # if not os.path.exists(dstdir):
    #     return
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    if new_name:  # 重命名文件
        new_name = os.path.join(dstdir, new_name)
        os.rename(srcfile, new_name)

def stop_thread(thread):
    '''
    关闭多线程
    :param thread:{object} 线程对象
    :return:None
    '''
    _async_raise(thread.ident, SystemExit)


def _async_raise(tid, exctype):
    """raises the exception, performs cleanup if needed"""
    tid = ctypes.c_long(tid)
    if not inspect.isclass(exctype):
        exctype = type(exctype)
    res = ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, ctypes.py_object(exctype))
    if res == 0:
        raise ValueError("invalid thread id")
    elif res != 1:
        ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, None)
        raise SystemError("PyThreadState_SetAsyncExc failed")



def getfileName(argv, bankName, count):
    timeArray = time.localtime(time.time())
    filename = getFormatData(argv=argv, format=3) + time.strftime(r"-%H-%M-%S", timeArray)
    return "%s_%s_%s" % (bankName, count, filename)

class Properties(object):
    '''
    读取property文件,返回字典
    '''

    def __init__(self, fileName):
        self.fileName = fileName
        self.properties = {}

    def __getDict(self, strName, dictName, value):

        if (strName.find('.') > 0):
            k = strName.split('.')[0]
            dictName.setdefault(k, {})
            return self.__getDict(strName[len(k) + 1:], dictName[k], value)
        else:
            dictName[strName] = value
            return

    def getProperties(self):
        try:
            pro_file = open(self.fileName, 'Ur')
            for line in pro_file.readlines():
                line = line.strip().replace('\n', '')
                if line.find("#") != -1:
                    line = line[0:line.find('#')]
                if line.find('=') > 0:
                    strs = line.split('=')
                    strs[1] = line[len(strs[0]) + 1:]
                    self.__getDict(strs[0].strip(), self.properties, strs[1].strip())
        except Exception as e:
            raise e
        else:
            pro_file.close()
        return self.properties


# class AEScoder():
#     def __init__(self):
#         self.__encryptKey = "iEpSxImA0vpMUAabsjJWug=="
#         self.__key = base64.b64decode(self.__encryptKey)
#     # AES加密
#     def encrypt(self,data):
#         BS = 16
#         pad = lambda s: s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         encrData = cipher.encrypt(pad(data))
#         #encrData = base64.b64encode(encrData)
#         return encrData
#     # AES解密
#     def decrypt(self,encrData):
#         unpad = lambda s: s[0:-s[-1]]
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         decrData = unpad(cipher.decrypt(encrData))
#         return decrData.decode('utf-8')



def Write2CsvF(pathTag, datas, codeflag="utf-8", delimiter="|"):
    print(pathTag)
    if not os.path.exists(os.path.dirname(pathTag)):
        os.makedirs(os.path.dirname(pathTag))

    try:
        with open(pathTag, "w", newline='', encoding=codeflag) as csvFile:

            writer = csv.writer(csvFile, delimiter=delimiter)

            if (str(type(datas)).find("str") + 1):
                writer.writerow(datas)

            elif (str(type(datas)).find("list") + 1):
                writer.writerows(datas)

            elif (str(type(datas)).find("dict") + 1):
                for keys, values in datas.items():
                    writer.writerow([keys, values])

            else:
                csvFile.close()
                return 1

            csvFile.close()
    except:
        print("检查文件名, 文件是否已打开.")

    return 0

def write2Json(msg, file_name):
    '''
    写入json文件
    :param msg:
    :param file_name:
    :return:
    '''
    json_msg = json.dumps(msg, ensure_ascii=False)
    with open(file_name, 'w') as file:
        file.write(json_msg)
        file.close()


def readJsonFile(filename):
    '''
    读取json文件
    :param filename:
    :return:
    '''
    with open(filename) as file:
        json_msg = json.load(file)
    return json_msg

def ReadFromConfig(pathFile, nameSection, dictvalue=''):
    # try:
    if os.path.exists(pathFile):
        with open(pathFile, encoding='UTF-8') as fileIni:
            cf = configparser.ConfigParser()
            # cf.read(pathFile, encoding="utf-8")
            cf.read(pathFile)
            infoConfig = cf.items(nameSection)  # type : [tuple,]
            infoConfig = dict(infoConfig)  # 转为 dict
            # print(infoConfig)
            if dictvalue != "":
                return infoConfig[dictvalue]

        return infoConfig
    else:
        return "error"


def WriteFile(filename, content, write='a'):
    '''
    将一条数据写入到文件中
    :param filename: 写入文件的路径
    :param content: 要写入的内容,换行符一\n分割
    :param write: 写入的方式,a为往后面追加,为写入删除之前的数据
    :return: 成功返回True,失败返回失败的原因
    '''
    try:
        fpath, fname = os.path.split(filename)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        with open(filename, write) as f:  # 如果filename不存在会自动创建, 'w'表示写数据,写之前会清空文件中的原有数据!
            f.write(content)
        return True
    except Exception as e:
        return e


# 文件压缩
def zip_ya(startdir, file_news):
    # startdir = ".\\123"  #要压缩的文件夹路径
    # file_news = startdir +'.zip' # 压缩后文件夹的名字
    z = zipfile.ZipFile(file_news, 'w', zipfile.ZIP_DEFLATED)  # 参数一:文件夹名
    for dirpath, dirnames, filenames in os.walk(startdir):
        fpath = dirpath.replace(startdir, '')  # 这一句很重要,不replace的话,就从根目录开始复制
        fpath = fpath and fpath + os.sep or ''  # 这句话理解我也点郁闷,实现当前文件夹以及包含的所有文件的压缩
        for filename in filenames:
            z.write(os.path.join(dirpath, filename), fpath + filename)
            # print ('压缩成功')
    z.close()


def get_all_file(dirname):
    filelist = []
    for root, dirs, files in os.walk(dirname):
        for name in files:
            filelist.append(os.path.join(root, name))
    return filelist


def zip_dir(dirname, zipfilename):
    filelist = []
    if os.path.isfile(dirname):
        filelist.append(dirname)
    else:
        for root, dirs, files in os.walk(dirname):
            for name in files:
                filelist.append(os.path.join(root, name))

    zf = zipfile.ZipFile(zipfilename, "w", zipfile.zlib.DEFLATED)
    for tar in filelist:
        arcname = tar[len(dirname):]
        zf.write(tar, arcname)


def get_datetime_from_datetime(the_datetime, format=1, interval=-1):
    '''
    传入时间和时间差,获取两者相加后的目标时间
    :param the_datetime: 可似乎日期时间
    :param format: 返回的数据格式,传入参数与返回数据映射如下
                1  20191018
                2  2019/10/18
                3  2019-10-18
                4  2019_10_18
                5  date备用格式
                6  date备用格式
                7  15:27:11
                8  152711
                9  作为字典返回所有日期数据
    :param interval: 目标时间与当前时间的时间间隔
    :return:
    '''
    if isinstance(the_datetime, datetime.datetime):
        time_delta = datetime.timedelta(days=interval)
        target_date = the_datetime + time_delta
        yestd = {'date': target_date,
                 "Y": target_date.year, 'M': target_date.month, 'D': target_date.day,
                 'h': target_date.hour, "m": target_date.minute, "s": target_date.second}
        if int(target_date.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % target_date.month
        if int(target_date.day) < 10:
            yestd["D"] = '0%s' % target_date.day
    else:
        return None

    if format == 1:  # 日期格式化并返回
        return '%s%s%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 2:
        return '%s/%s/%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 3:
        return '%s-%s-%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 4:
        return '%s_%s_%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 5:
        pass  # date备用格式
    elif format == 6:
        pass
    elif format == 7:
        return '%s:%s:%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 8:
        return '%s%s%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 9:
        return yestd


def getFormatData(argv=None, format=1, interval=0):
    '''
    时间获取函数,如果命令行参数argv带有时间参数,验证后返回该时间,否则返回系统当前时间与时间差interval相加后的日期时间
    :param argv: sys.argv,传入的命令行参数
    :param format: 返回的数据格式,传入参数与返回数据映射如下
                    1  20191018
                    2  2019/10/18
                    3  2019-10-18
                    4  2019_10_18
                    5  date备用格式
                    6  date备用格式
                    7  15:27:11
                    8  152711
                    9  作为字典返回所有日期数据
    :param interval: 目标时间与当前时间的时间间隔,不对命令行传入的时间进行运算。
    :return:
    '''
    # timeArray = time.localtime(time.time())
    # timeeee = time.strftime(r"%Y%m%d %H:%M:%S", timeArray)

    time_delta = datetime.timedelta(days=interval)  # 设置时间间隔
    sys_time = datetime.datetime.now() + time_delta  # 获取系统时间

    if (argv and len(argv) == 1) or (not argv) or (len(argv) >= 2 and 'null' == argv[1]):  # 命令行未传入时间参数
        yestd = {'date': sys_time,
                 "Y": sys_time.year, 'M': sys_time.month, 'D': sys_time.day,
                 'h': sys_time.hour, "m": sys_time.minute, "s": sys_time.second}
        if int(sys_time.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % sys_time.month
        if int(sys_time.day) < 10:
            yestd["D"] = '0%s' % sys_time.day
        print(argv)
    else:  # 命令行传入了时间参数
        print(argv[1])
        # match = re.search(r'\d{4}[-/]\d{1,2}[-/]\d{1,2}', '2019/01/29')
        match = re.search(r'20\d{2}-\d{1,2}-\d{1,2}', argv[1])  # 时间格式匹配
        match2 = re.search(r'20\d{2}/\d{1,2}/\d{1,2}', argv[1])
        match3 = re.search(r'20\d{2}\d{1,2}\d{1,2}', argv[1])
        if match:
            print('match: %s' % match)
            yesterday = datetime.datetime.strptime(match.group(), '%Y-%m-%d')
        elif match2:
            print('match2: %s' % match2)
            yesterday = datetime.datetime.strptime(match2.group(), '%Y/%m/%d')
        elif match3:
            print('match3: %s' % match3)
            yesterday = datetime.datetime.strptime(match3.group(), '%Y%m%d')
        else:
            return None
        yestd = {'date': yesterday,
                 "Y": yesterday.year, 'M': yesterday.month, 'D': yesterday.day,
                 'h': sys_time.hour, "m": sys_time.minute, "s": sys_time.second}
        if int(yesterday.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % yesterday.month
        if int(yesterday.day) < 10:
            yestd["D"] = '0%s' % yesterday.day

    if format == 1:  # 日期格式化并返回
        return '%s%s%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 2:
        return '%s/%s/%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 3:
        return '%s-%s-%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 4:
        return '%s_%s_%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 5:
        pass  # date备用格式
    elif format == 6:
        pass
    elif format == 7:
        return '%s:%s:%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 8:
        return '%s%s%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 9:
        return yestd


def getLastMonthDate(format=0):
    first_day = datetime.date(datetime.date.today().year, datetime.date.today().month, 1)  # 本月第一天
    last = first_day - datetime.timedelta(1)  # 获取上个月最后一天日期
    if format == 0:
        return str(last)
    else:
        return str(last).replace('-', '')


class Rfs(object):
    status = {
        'NoDevice': 'key未接入或者未供电',
        'NoLink': 'key未连接',
        'Linked': '连接成功',
    }
    local_ip = '10.1.2.87'
    pathFile = r"D:\ChinaCoalAuto\Common\Config\USBHubConfig.ini"  # 配置文件路径

    def get_allPort_msg(self):
        result = {}
        temp_result = os.popen('RfsNu -l')
        res = temp_result.read()
        No = 1
        for line in res.splitlines():
            if 'UsbNumber' in line:
                temp = line.strip().split(' ')
                result[No] = {
                    temp[0].split(':')[0]: temp[0].split(':')[1],
                    temp[1].split(':')[0]: temp[1].split(':')[1],
                    temp[2].split(':')[0]: temp[2].split(':')[1],
                    temp[3].split(':')[0]: temp[3].split(':')[1],
                    temp[-1].split(':')[0]: temp[-1].split(':')[1],
                }
                No += 1
        return result

    def get_port_msg(self, port):
        port_status = self.get_allPort_msg()
        if isinstance(port, int):
            return port_status[port]

    def is_linked(self, port):
        port_status = self.get_port_msg(port)
        if 'NoDevice' == port_status['linkstatus']:
            return 'NoDevice', port_status['linked']
        elif 'NoLink' == port_status['linkstatus']:
            return 'NoLink', port_status['linked']
        elif 'Linked' == port_status['linkstatus']:
            return 'Linked', port_status['linked']

    def wait_for_linked(self, portName, wait_time):
        port = int(self.ReadFromConfig(Rfs.pathFile, "Main", portName))
        status = None
        while wait_time >= 0:
            status, ip = self.is_linked(port)
            print(port, status)
            if status == 'Linked':
                if self.local_ip in ip:
                    break
                else:
                    raise PortHasBeenUsedException('The port has been used')
            else:
                time.sleep(1)
                wait_time -= 1

        if status != 'Linked':
            raise NotFoundKeyException('Not checked the key')  # key未连接时,抛出异常结束后续操作

    # 读取配置文件 Section->{k:v}
    def ReadFromConfig(self, pathFile, nameSection, dictvalue=''):
        # try:
        if os.path.exists(pathFile):
            with open(pathFile, encoding='UTF-8') as fileIni:
                cf = configparser.ConfigParser()
                cf.read(pathFile, encoding='UTF-8')
                infoConfig = cf.items(nameSection)  # type : [tuple,]
                infoConfig = dict(infoConfig)  # 转为 dict
                # print(infoConfig)
                if dictvalue != "":
                    return infoConfig[dictvalue]
            return infoConfig
        else:
            return "error"


class Mysql(object):
    '''
    操作mysql数据库
    '''

    def __init__(self, host, user, password, database=None, port=3306, **kwargs):
        '''
        初始化连接数据库
        :parameter huang
        :param host: 地址
        :param user:用户名
        :param password:密码
        :param database:数据库名称
        :param port:端口号
        :param kwargs: 以下参数
                 unix_socket=None,charset='', sql_mode=None,
                 read_default_file=None, conv=None, use_unicode=None,
                 client_flag=0, cursorclass=Cursor, init_command=None,
                 connect_timeout=10, ssl=None, read_default_group=None,
                 compress=None, named_pipe=None,autocommit=False, db=None,
                 passwd=None, local_infile=False,max_allowed_packet=16*1024*1024,
                 defer_connect=False,auth_plugin_map=None, read_timeout=None,
                 write_timeout=None,bind_address=None, binary_prefix=False,
                 program_name=None,server_public_key=None
        '''
        logging.info('连接到mysql服务器...')
        self.db = pymysql.connect(host, user, password, database, port, **kwargs)
        logging.info('数据库连接成功!')

    def insertdb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行sql语句
            self.db.commit()  # 提交到数据库执行
        except Exception as e:
            logging.error(e)
            logging.error('插入数据失败!')
            self.db.rollback()  # 回滚
        finally:
            cursor.close()

    def updatedb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行SQL语句
            self.db.commit()  # 提交到数据库执行
        except:
            logging.error('更新数据失败!')
            self.db.rollback()  # 发生错误时回滚
        finally:
            cursor.close()

    def update_db_many(self, sql, data):
        '''
            批量插入
        :param sql:
        :param data:列表字典
        :return:
            example:
                db = Mysql("localhost", "root", "admin123", 'ccyboa_service', port=3306)
                sql = ("INSERT INTO `ops_data_bank_copy`(batch_num,data_comment_1,data_decimal_1,data_datetime_5,data_int_1,is_delete) "
                       "VALUES(%s,%s,%s,%s,%s,%s)")
                data = [
                    ('test_20111111', 'test_2019', '33004', '2019/01/02', 234, 1),
                    ('test_2011222', 'test_2018', '33001', '2019/01/03', 235, 1),
                    ('test_2011333', 'test_2017', '33002', '2019/01/04', 234, 1),
                    ('test_20114441', 'test_2019', '33004', '2019/01/05', 234, 1),
                ]
                db.insert_db_many(sql,data)
        '''
        cursor = self.db.cursor()
        try:
            cursor.executemany(sql, data)  # 批量执行多条插入SQL语句
            self.db.commit()  # 提交事务
        except Exception as e:
            logging.error(e)
            self.db.rollback()  # 有异常,回滚事务
            raise e
        finally:
            cursor.close()

    def select_db_fetchall(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        # sql = "SELECT * FROM ops_data_bank where batch_num like '%20191202'"
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchall()  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            return results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchone(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchone()  # 获取查询的所有记录
            return title_name, results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchmany(self, sql, size=None, *args):
        '''
        数据库结果查询
        :param sql:
        :param size: 条目
        :param args:
        :return: 数据元组
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql, *args)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchmany(size)  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            print(results)
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def closedb(self):
        '''
        关闭数据库
        :return:
        '''
        self.db.close()

    def __del__(self):
        self.closedb()
        print("数据库关闭成功,__del__对象已经销毁")


# class bank_balance(object):
#     data_id = ''
#     batch_num = ''
#     account_no = ''
#     account_name = ''
#     account_balance = ''
#     bank_name = ''
#     account_date = ''
#     insert_time = ''
#     is_delete = ''
#     source_id = ''
#     versionNo = ''
#
#     def change_batch_num(self):
#         '''
#         修改批号
#         :return:
#         '''
#         batch_num = self.batch_num[1]
#         temp = batch_num.split('_')[:-1]
#         temp.append(getFormatData(interval=-1))
#         self.batch_num = (self.batch_num[0], '_'.join(temp))
#
#     def change_versionNo(self, db):
#         '''
#         查询修改版本号
#         :param db:
#         :return:
#         '''
#         sql = "(SELECT IFNULL(MAX(versionNo),0)+1  FROM ops_data_bank_balance " \
#               "WHERE batch_num = '%s'  AND account_no	 = '%s');" % (self.batch_num[1], self.account_no[1])
#         result_data = db.select_db_fetchone(sql)
#         version_no = result_data[1][0]
#         self.versionNo = (self.versionNo[0], version_no)
#
#     def set_property(self, sql_result):
#         '''
#         根据查询结果为对象属性赋值,值为元组,对应值分别为  属性=(数据库字段名,数据库属性对应数据)
#         :param sql_result:
#         :return:
#         '''
#         if len(sql_result) != 2:
#             return
#         for index, name_column in enumerate(sql_result[0]):
#             if 'data_id' == name_column:
#                 self.data_id = (name_column, sql_result[1][index])
#             elif 'batch_num' == name_column:
#                 self.batch_num = (name_column, sql_result[1][index])
#             elif 'account_no' == name_column:
#                 self.account_no = (name_column, sql_result[1][index])
#             elif 'account_name' == name_column:
#                 self.account_name = (name_column, sql_result[1][index])
#             elif 'account_balance' == name_column:
#                 self.account_balance = (name_column, str(sql_result[1][index]))
#             elif 'bank_name' == name_column:
#                 self.bank_name = (name_column, sql_result[1][index])
#             elif 'account_date' == name_column:
#                 self.account_date = (name_column, str(sql_result[1][index]))
#             elif 'insert_time' == name_column:
#                 self.insert_time = (name_column, str(sql_result[1][index]))
#             elif 'is_delete' == name_column:
#                 self.is_delete = (name_column, sql_result[1][index])
#             elif 'source_id' == name_column:
#                 self.source_id = (name_column, sql_result[1][index])
#             elif 'versionNo' == name_column:
#                 self.versionNo = (name_column, sql_result[1][index])
#
#     def get_sql_data(self, db, account):
#         '''
#         数据库根据id排序,获取最后一条数据,属性赋值
#                         sql_select_data = ("(SELECT * FROM `ops_data_bank_balance` WHERE account_no = '%s' "
#                            "AND is_delete = 0 ORDER BY account_date	DESC LIMIT 1);" % account)
#         :param db:
#         :param account:
#         :return:
#         '''
#         sql_select_data = ("(SELECT * FROM `ops_data_bank_balance` WHERE account_no = '%s' "
#                            "AND is_delete = 0 ORDER BY data_id	DESC LIMIT 1);" % account)
#         result_data = db.select_db_fetchone(sql_select_data)
#         self.set_property(result_data)
#
#     def insert_balance(self, db):
#         '''
#         数据库数据插入
#                 sql="INSERT INTO `ops_data_bank_balance`( `batch_num`, `account_no`, `account_name`, `account_balance`, " \
#                         "`bank_name`, `account_date`, `is_delete`, `source_id`, `versionNo`) " \
#             "VALUES('BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191205','770901040000769','中煤新集能源股份有限公司'," \
#             "'66343.49','中国农业银行','2019-12-01 00:00:00','0','','2');"
#         :param db:
#         :return:
#         '''
#         sql2 = "INSERT INTO `ops_data_bank_balance`( `batch_num`, `account_no`, `account_name`, `account_balance`, " \
#                "`bank_name`, `account_date`, `is_delete`, `source_id`, `versionNo`) " \
#                "VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s');" % (
#                    self.batch_num[1], self.account_no[1], self.account_name[1],
#                    self.account_balance[1], self.bank_name[1], self.account_date[1],
#                    self.is_delete[1], '', self.versionNo[1])
#         db.insertdb(sql2)
#         logging.info('余额插入成功')
#
#
# def insert_bank_balance(account):
#     '''
#         SELECT * FROM `ops_data_bank_balance` WHERE account_no	 = '770901040000769' AND is_delete = 0 ORDER BY data_id	DESC LIMIT 1;
#         SELECT  FROM ops_data_bank_balance WHERE batch_num = 'BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191203' ;
#         (SELECT IFNULL(MAX(versionNo),0)+1  FROM ops_data_bank_balance WHERE batch_num = 'BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191204' );
#         @:param 参数账号信息
#         @:param
#         :return:
#             # 查询根据账号余额数据 银行余额类保存相关数据属性
#             # 查询版本
#             # 生成sql插入语句
#             # 插入结果数据
#             # 查询插入结果检验
#             # 确认插入成功
#     '''
#     balance = bank_balance()  # 余额类
#     db = Mysql("localhost", "root", "root", 'ccyboa_service', port=3306)
#     balance.get_sql_data(db, account)  # 根据id排序查找数据
#     balance.change_batch_num()  # 修改batch_num
#     balance.change_versionNo(db)  # 获取versionNo并修改
#     balance.insert_balance(db)  # 数据插入
#
#     balance2 = bank_balance()  # 余额类
#     balance2.get_sql_data(db, account)  # 根据id排序查找数据,余额类添加相关属性
#     if balance.batch_num[1] == balance2.batch_num[1] and balance.versionNo[1] == balance2.versionNo[1]:
#         print('数据库查询道的数据和数据库插入的数据一致,success')
#         return True
#     else:
#         print('数据库查询道的数据和数据库插入的数据不一致,Fail')
#         return False

def csvToList(excelPath):
    '''
    打开csv文件,转换成list
    :param excelPath:
    :return:
    '''
    if os.path.exists(excelPath):
        # 打开csv
        # excelPath = "D:/autoit/项目/华鑫证券/文档/增值税取数文档及模板/附表二.csv"
        print("打开路径下的文件:",excelPath)
        try:
            df = pd.read_csv(open(excelPath), index_col=False, header=None, error_bad_lines=False)
        except Exception as e:
            print("打开文件失败:", e)
            df = pd.read_csv(open(excelPath, encoding='UTF-8'), index_col=False, header=None, error_bad_lines=False)
        train_data = pd.np.array(df)  # np.ndarray()
        train_x_list = train_data.tolist()  # list
    else:
        train_x_list = [[]]
    return train_x_list


def listToCSV(csvPath, csvList):
    '''
    讲列表写入到csv
    :param csvPath: 写入的路径
    :param csvList: 写入的列表
    :return:
    '''
    df_data = pd.DataFrame(csvList)
    df_data.to_csv(csvPath, header=False, index=False, encoding='gb2312')


def resultWriteCSV(resultPath, resultTemplatePath, writeResult):
    '''
    将结果写入到csv文件中
    :param resultPath: 写入文件的路径
    :param resultTemplatePath: 模板的路径
    :param writeResult: 写入的数据值
    :return:
    '''
    if not os.path.exists(resultPath):
        mycopyfile(resultTemplatePath, resultPath)
    readLists = csvToList(resultPath)
    for i in range(0, len(readLists)):
        if readLists[i][0] == writeResult[0]:
            readLists[i][1] = writeResult[1]
            readLists[i][2] = writeResult[2]
            readLists[i][3] = writeResult[3]
            readLists[i][4] = writeResult[4]
    listToCSV(resultPath, readLists)

 

延伸阅读
    < /body> < /html>