python_files/cppc_python脚本/服务器脚本/tool_for_goods_demo_v3.py

# -*- codeing = utf-8 -*-
# @Time : 2022/8/31 17:52
# @Author : Clown
# @File : tool_for_goods_demo.py
# @Software : PyCharm

import requests
import random
from datetime import datetime,timedelta
import time
import os
import shutil
import json
from all_key_table import update_key_value_pair_7qiaoPlus
import warnings
import re
import csv
import pandas as pd
from requests_toolbelt import MultipartEncoder,MultipartEncoderMonitor
import pymysql
import traceback
from chardet.universaldetector import UniversalDetector
# warnings.filterwarnings('ignore')

def get_filelist(path):
    """
    获取路径下所有csv文件的路径列表
    """
    Filelist = []
    for home, dirs, files in os.walk(path):
        for filename in files:
            if ".csv" in filename:
                Filelist.append(os.path.join(home, filename))
    return Filelist

def read_file(file):
    """
    逐个读取文件的内容
    """
    with open(file, 'rb') as f:
        return f.read()

def get_encode_info(file):
    """
    逐个读取文件的编码方式
    """
    with open(file, 'rb') as f:
        detector = UniversalDetector()
        for line in f.readlines():
            detector.feed(line)
            if detector.done:
                break
        detector.close()
        return detector.result['encoding']

def convert_encode2utf8(file, original_encode, des_encode):
    """
    将文件的编码方式转换为utf-8，并写入原先的文件中。
    """
    file_content = read_file(file)
    file_decode = file_content.decode(original_encode, 'ignore')
    file_encode = file_decode.encode(des_encode)
    with open(file, 'wb') as f:
        f.write(file_encode)

def read_and_convert(path):
    """
    读取文件并转换
    """
    Filelist = get_filelist(path=path)
    fileNum= 0
    for filename in Filelist:
        try:
            file_content = read_file(filename)
            encode_info = get_encode_info(filename)
            if encode_info != 'utf-8':
                fileNum +=1
                convert_encode2utf8(filename, encode_info, 'utf-8')
                # print('成功转换 %s 个文件 %s '%(fileNum,filename))
        except BaseException:
            print(filename,'->This file is error,Please recheck_again!')

def recheck_again(path):
    """
    再次判断文件是否为utf-8
    """
    # print('---------------------以下文件仍存在问题---------------------')
    Filelist = get_filelist(path)
    for filename in Filelist:
        encode_info_ch = get_encode_info(filename)
        if encode_info_ch != 'utf-8':
            print(filename,'--encoding_by(',encode_info_ch,") mark error ")

    # print('--------------------------检查结束--------------------------')

# '以上为文件编码转换'

def linkTomySql(host, passwd, db_name, port):
    '''连接至数据库返回【db】,v2新增local_infile=1 打开文件导入权限'''
    try:
        # 本地连接为：localhost  服务器连接为：124.222.188.59
        db = pymysql.connect (
            host=host, user="root",
            passwd=passwd,
            db=db_name,
            charset='utf8mb4',
            local_infile=1,
            port=port)
        # print ('\nconnect to mysql server 成功')
        # print ('---------------------------------------')

    except:
        print ("\nCould not connect to mysql server")
        db = "连接失败"
    return db

def read_key_value_pair(db, brand_name, wm_plate, owner):
    '''按条件读取，数据库中all_key_table表里的key_value_pair字段中的值，以键值对的形式输出
       db:数据库，
       brand_name：品牌名，
       wm_plate:外卖平台MEITUAN或ELEME，
       owner:账号权限all或one
    '''
    cursor = db.cursor ()
    sql = f'SELECT key_value_pair FROM all_key_table WHERE brand_name = "{brand_name}" AND wm_plate = "{wm_plate}" AND owner = "{owner}";'
    cursor.execute (sql)
    pair = json.loads (cursor.fetchall ()[0][0])
    return pair

#20220907更新order_bill
def order_bill(mtinput,elminput,row_id):
    '''

    :param mtinput: 美团账单文件访问路径
    :param elminput: 饿了么账单文件访问路径
    :return: bill_dict,error_files 账单字典  美团{'row_id:row_id':{'ID:****':10.01}} or 饿了么{'row_id:row_id':{'id:****':10.01}},错误文件列表
    '''
    bill_dict = {}
    bill_id = 'row_id'+row_id
    bill_dict[bill_id]={}
    error_files = []
    try:
    # 读取美团文件
        for a, b, files in os.walk (mtinput, topdown=False):
            if len (files) == 0:
                print ('No Meituan files!')

            else:
                for file in files:
                    try:
                        data_m = pd.read_excel (mtinput + '/' + file, sheet_name='订单明细', dtype='str')
                        df = data_m[data_m['交易类型'] == '外卖订单']  # 按列条件筛选数据
                        cnt = len (df['交易类型'])
                        for i in range (cnt):
                            row = df.iloc[i]
                            dingdanid = 'ID:' + str (row['订单号'])
                            pingtaifuwufei = float (row['平台服务费']) + float (row['公益捐款'])
                            bill_dict[bill_id][dingdanid] = round (pingtaifuwufei, 2)
                    except  Exception as me:
                        error_files.append(file)
                        print ('mt', me)



        for a, b, files in os.walk (elminput, topdown=False):
            if len (files) == 0:
                print ('No Eleme files!')

            else:
                for file in files:
                    try:
                        data_e = pd.read_excel (elminput + '/' + file, sheet_name='外卖订单明细', dtype='str')
                        df = data_e[data_e['订单类型'] == '外卖订单']  # 按列条件筛选数据
                        cnt = len (df['订单类型'])
                        for i in range (cnt):
                            row = df.iloc[i]
                            dingdanid = 'id:' + str (row['订单编号'])
                            jiesuanjine = float (row['结算金额'])
                            bill_dict[bill_id][dingdanid] = round (jiesuanjine, 2)
                    except Exception as ee:
                        error_files.append (file)
                        print ('elm', ee)
    except Exception as b_e:
        print('bill',b_e)
    # print(bill_dict)
    return bill_dict,error_files

#20220907更新files_download
def order_formgoods(mtinput, elminput,  save_path, row_id, banner, bill_dict):
    '''

    :param mtinput: str 美团文件夹地址
    :param elminput: str 饿了么文件夹地址
    :param save_path: str 综合保存文件的地址
    :param row_id str
    :param banner: str 品牌名，默认为 空
    :return: formgoods_out_file_path(输出文件的访问地址),formgoods_out_file_name(输出的文件名称),error_files(解析失败文件list类型)
    '''

    mo = 0
    num =0

    # 20220907更新bill_id,bill_dict_list
    bill_id = 'row_id' + row_id
    bill_dict_list = bill_dict[bill_id]

    time_str = time.strftime("%Y%m%d%H%M%S", time.localtime())

    output = save_path +'/' + row_id + '/out'

    error_files = []

    # 20220907更新title '单品优惠后单价','单品实收单价'
    title = ["日期", '订单编号', '订单状态', '下单时段', '平台商品名称', '销量', "原价", '单品优惠后单价', '单品实收单价', '平台', '城市', '平台门店id', '平台门店名称']

    formgoods_out_file_path = output+"/%s"%str(banner)+"订单数据(商品信息）%s.csv"%str(time_str)

    formgoods_out_file_name = banner + "订单数据(商品信息）%s.csv"%str(time_str)

    with open(output+"/%s"%str(banner)+"订单数据(商品信息）%s.csv"%str(time_str),"a",newline='',encoding="utf-8-sig",errors="ignore") as t:#encoding="utf-8",
        writer = csv.writer(t)
        writer.writerow(title)

        #读取美团文件
        for a, b, files in os.walk (mtinput, topdown=False):
            if len (files) == 0:
                print('No Meituan files!')
                mf = 0
            else:
                for file in files:
                    try:
                        with open(mtinput+'/'+file,encoding="utf-8") as f:
                            data = pd.read_csv(f,low_memory=False)#,low_memory=False
                            list_1 = data['日期']

                            no = 0

                            for n in list_1:
                                num += 1
                                dingdan = data.iloc[no]
                                dingdanid = dingdan['订单编号']

                                # 20220907更新 新增拆解单品优惠后均价all_sale_count,all_sale_discount,discount_rate,income_rate
                                all_sale_count = float (dingdan['商品原价'])
                                all_sale_discount = float (dingdan['商品原价']) + float (dingdan['包装费']) - float (
                                    dingdan['商家活动支出'])
                                discount_rate = all_sale_discount / all_sale_count

                                try:
                                    pingtaifuwufei = bill_dict_list[dingdanid]
                                    all_sale_income = all_sale_discount + pingtaifuwufei
                                    income_rate = all_sale_income / all_sale_count
                                except:
                                    income_rate = 'no_bill'

                                try:
                                    riqi_o = time.strptime(str(dingdan['日期']),"%Y-%m-%d")
                                    riqi = time.strftime("%Y%m%d",riqi_o)
                                except:
                                    try:
                                        riqi_o = time.strptime(str(dingdan['日期']), "%Y/%m/%d")
                                        riqi = time.strftime("%Y%m%d", riqi_o)
                                    except:
                                        riqi_o = time.strptime (str(dingdan['日期']), "%Y%m%d")
                                        riqi = time.strftime ("%Y%m%d", riqi_o)
                                try:
                                    try:
                                        shiduan0 = datetime.strptime(dingdan['下单时间'],'%Y/%m/%d %H:%M:%S').strftime("%H") #下单时段
                                    except:
                                        shiduan0 = datetime.strptime (dingdan['下单时间'], '%Y/%m/%d %H:%M').strftime ("%H")  # 下单时段
                                except:
                                    try:
                                        shiduan0 = datetime.strptime(dingdan['下单时间'], '%Y-%m-%d %H:%M:%S').strftime("%H")  # 下单时段
                                    except:
                                        shiduan0 = datetime.strptime (dingdan['下单时间'], '%Y-%m-%d %H:%M').strftime ("%H")  # 下单时段

                                try:
                                    try:
                                        shiduan1 = datetime.strptime(dingdan['下单时间'],'%Y/%m/%d %H:%M:%S').strftime("%M") #下单时段
                                    except:
                                        shiduan1 = datetime.strptime (dingdan['下单时间'], '%Y/%m/%d %H:%M').strftime ("%M")  # 下单时段
                                except:
                                    try:
                                        shiduan1 = datetime.strptime(dingdan['下单时间'], '%Y-%m-%d %H:%M:%S').strftime("%M")  # 下单时段
                                    except:
                                        shiduan1 = datetime.strptime (dingdan['下单时间'], '%Y-%m-%d %H:%M').strftime ("%M")  # 下单时段

                                if int(shiduan1) < 30:
                                    shiduan1 = ':00'
                                else:
                                    shiduan1 = ':30'

                                shiduan = shiduan0 + shiduan1


                                pingtai = "美团"
                                city = dingdan['门店所在城市']

                                shopid = dingdan['门店id']
                                shop = dingdan['门店名称']
                                xinxi = dingdan['商品信息']
                                zhuangtai = dingdan['订单状态']
                                no += 1
                                p = r'[^\/].*?\,单价[0-9]*?\.[0-9]*?\*数量\d+'
                                r = re.compile(p)
                                cont = []
                                cont.append(xinxi)
                                for i in cont:
                                    a = r.finditer(i)
                                    for b in a:
                                      mo += 1
                                      m = b.group()
                                      shangpin = m.split(",单价")[0]
                                      danjia_no = m.split(",单价")[1]
                                      danjia = danjia_no.split("*数量")[0]
                                      shuliang = danjia_no.split("*数量")[1]

                                      # 20220907更新 新增拆解单品优惠后均价sale_discount,sale_income
                                      sale_discount = float (danjia) * discount_rate
                                      try:
                                          sale_income = round (float (danjia) * income_rate, 2)
                                      except:
                                          sale_income = 'no_bill'

                                      ms = [riqi, str (dingdanid), zhuangtai, shiduan, shangpin, shuliang, danjia,
                                            round (sale_discount, 2), sale_income, pingtai, city, shopid, shop]
                                      writer.writerow(ms)
                                    #   print ('\r[美团]已拆解%s行，拆解出%s行' % (str (num), str (mo)), end='')
                    except Exception as e:
                        print(e)
                        error_files.append(file)
                mf = 1
        #读取饿了么文件
        for a, b, files in os.walk (elminput, topdown=False):
            if len (files) == 0:
                print ('No ELeme files!')
                ef = 0
            else:
                for file in files:
                    try:
                        data = pd.read_excel(elminput+'/'+file)
                        list_1 = data['日期']
                        no = 0
                        for n in list_1:
                            num += 1
                            dingdan = data.iloc[no]
                            dingdanid = "id:" + str (dingdan['订单单号'])
                            
                            # 20220907更新 新增拆解单品优惠后均价all_sale_count,all_sale_discount，discount_rate
                            all_sale_count = float (dingdan['菜品原价'])
                            all_sale_discount = float (dingdan['菜品原价']) + float (dingdan['餐盒费']) - float (
                                dingdan['商家成本'])
                            discount_rate = all_sale_discount / all_sale_count
                            try:
                                jiesuanjine = bill_dict_list[dingdanid]
                                income_rate = jiesuanjine / all_sale_count
                            except:
                                income_rate = 'no_bill'

                            try:
                                riqi_o = time.strptime (dingdan['日期'], "%Y-%m-%d")
                                riqi = time.strftime ("%Y%m%d", riqi_o)
                            except:
                                riqi_o = time.strptime (dingdan['日期'], "%Y/%m/%d")
                                riqi = time.strftime ("%Y%m%d", riqi_o)
                            try:
                                shiduan0 = datetime.strptime (dingdan['下单时间'], '%Y/%m/%dT%H:%M:%S').strftime ("%H")  # 下单时段
                            except:
                                shiduan0 = datetime.strptime (dingdan['下单时间'], '%Y-%m-%dT%H:%M:%S').strftime ("%H")  # 下单时段
                                
                            try:
                                shiduan1 = datetime.strptime (dingdan['下单时间'], '%Y/%m/%dT%H:%M:%S').strftime ("%M")  # 下单时段
                            except:
                                shiduan1 = datetime.strptime (dingdan['下单时间'], '%Y-%m-%dT%H:%M:%S').strftime ("%M")  # 下单时段

                            if int (shiduan1) < 30:
                                shiduan1 = ':00'
                            else:
                                shiduan1 = ':30'
                                
                            shiduan = shiduan0 + shiduan1

                            pingtai = "饿了么"
                            city = dingdan['门店所在城市']
                            shopid = dingdan['门店编号']
                            shop = dingdan['门店名称']
                            xinxi = dingdan['商品信息']
                            zhuangtai = dingdan['订单状态']
                            no += 1
                            p = r'[^\+].*?\_\d+\*\d+\.\d+'
                            r = re.compile (p)
                            cont = []
                            cont.append (xinxi)
                            for i in cont:
                                a = r.finditer (i)
                                for b in a:
                                    mo += 1
                                    m = b.group ()
                                    shangpin = m.split ("_")[0]
                                    danjia_no = m.split ("_")[1]
                                    danjia = danjia_no.split ("*")[1]
                                    shuliang = danjia_no.split ("*")[0]
                                    # 20220907更新 新增拆解单品优惠后均价sale_discount,sale_income
                                    sale_discount = float (danjia) * discount_rate
                                    try:
                                        sale_income = round (float (danjia) * income_rate, 2)
                                    except:
                                        sale_income = 'no_bill'

                                    ms = [riqi, dingdanid, zhuangtai, shiduan, shangpin, shuliang, danjia,
                                          round (sale_discount, 2), sale_income,
                                          pingtai, city, shopid, shop]

                                    writer.writerow (ms)

                                    # print ('\r[饿了么]已拆解%s行，拆解出%s行' % (str (num), str (mo)), end='')
                    except Exception as e:
                        print(e)
                        # print(e)
                        error_files.append(file)
                ef = 1
    none_file = mf + ef #none_file = 0 #此参数用来判断是否上传文件，如果为0则不上传文件
    return formgoods_out_file_path,formgoods_out_file_name,error_files,none_file
    
def order_actions_e(elminput, save_path, row_id, banner):
    '''
    :param elminput: str 饿了么文件夹地址
    :param save_path: str 综合保存文件的地址
    :param row_id str
    :param banner: str 品牌名，默认为 空
    :return: formactions_out_file_pathE(输出文件的访问地址),formactions_out_file_nameE(输出的文件名称),error_files(解析失败文件list类型)
    '''
    mo = 0
    num = 0

    time_str = time.strftime ("%Y%m%d%H%M%S", time.localtime ())

    output = save_path + '/' + row_id + '/out'

    formactions_out_file_pathE = output + "/" + banner + "elm订单数据(活动信息）%s.csv" % str (time_str)
    formactions_out_file_nameE = banner + "elm订单数据(活动信息）%s.csv" % str (time_str)

    error_files = []
    none_file = 1  # 此参数用来判断是否上传文件，如果为0则不上传文件

    title = ["日期", '门店名称', '门店id', '门店所在城市', '订单编号', '下单时间', "活动明细", "分类1", "分类2", "商家承担费用", '平台承担费用', '规则备注']
    with open (output + "/" + banner + "elm订单数据(活动信息）%s.csv" % str (time_str), "a", newline='',encoding="utf-8-sig",errors="ignore") as t:
        writer = csv.writer (t)
        writer.writerow (title)
        for a, b, files in os.walk (elminput, topdown=False):
            if len (files) == 0:
                print ('No ELeme files!')
                none_file = 0
            else:
                for file in files:
                    try:
                        data = pd.read_excel(elminput+'/'+file,dtype='str')
                        list_1 = data['日期']
                        no = 0

                        for n in list_1:
                            num += 1
                            dingdan = data.iloc[no]
                            riqi = dingdan['日期']
                            shop = dingdan['门店名称']
                            shopid = dingdan['门店编号']
                            city = dingdan['门店所在城市']
                            dingdanid = dingdan['订单单号']
                            xiadanshijian = dingdan['下单时间']

                            xinxi = dingdan['活动信息']
                            no += 1
                            try:
                                xinxi_list = xinxi.split ('+')

                                for b in xinxi_list:
                                    mo += 1

                                    try:
                                        p = r'【.*?】【.*?】'
                                        r = re.compile (p)
                                        fenlei = r.search (b).group ()
                                    except:
                                        p = r'【.*?】'
                                        r = re.compile (p)
                                        fenlei = r.search (b).group ()

                                    try:
                                        b1 = re.split (":", b)[0]
                                        p1 = r'【(.*)】【(.*)】(.*)'
                                        r1 = re.compile (p1)
                                        fenlei1 = r1.search (b1).group (3)
                                    except:
                                        b1 = re.split (":", b)[0]
                                        p1 = r'【(.*)】(.*)'
                                        r1 = re.compile (p1)
                                        fenlei1 = r1.search (b1).group (2)

                                    try:
                                        q = r'商户补贴\d+\.\d+'
                                        s = re.compile (q)
                                        cash = s.search (b).group ()
                                        cash_no = re.search ('\d+\.\d+', cash, flags=0).group ()
                                    except:
                                        cash_no = 0

                                    try:
                                        q1 = r'平台补贴\d+\.\d+'
                                        s1 = re.compile (q1)
                                        cash1 = s1.search (b).group ()
                                        cash_no1 = re.search ('\d+\.\d+', cash1, flags=0).group ()
                                    except:
                                        cash_no1 = 0

                                    try:
                                        rule = b.split ('规则:')[1]
                                    except:
                                        rule = ""

                                    ms = [[riqi, shop, shopid, city, (str (dingdanid) + "\t"), xiadanshijian, b, fenlei, fenlei1, cash_no, cash_no1, rule]]
                                    writer.writerows (ms)

                            except:
                                ms = [[riqi, shop, shopid, city, (str (dingdanid) + "\t"), xiadanshijian, "null", "null", "null", 0, 0, "null"]]
                                writer.writerows (ms)
                            # print ('\r 已拆解%s行，拆解出%s行' % (str (num), str (mo)), end='')
                    except Exception as e:
                        print(e)
                        error_files.append('【活动信息无法解析】'+file)

    return formactions_out_file_pathE,formactions_out_file_nameE,error_files,none_file
    
def order_actions_m(mtinput, save_path, row_id, banner):
    '''
    :param mtinput: str 美团文件夹地址
    :param save_path: str 综合保存文件的地址
    :param row_id str
    :param banner: str 品牌名，默认为 空
    :return: formactions_out_file_pathM(输出文件的访问地址),formactions_out_file_nameM(输出的文件名称),error_files(解析失败文件list类型)
    '''
    mo = 0
    num = 0

    time_str = time.strftime ("%Y%m%d%H%M%S", time.localtime ())

    output = save_path + '/' + row_id + '/out'

    formactions_out_file_pathM = output + "/" + banner + "mt订单数据(活动信息）%s.csv" % str (time_str)
    formactions_out_file_nameM = banner + "mt订单数据(活动信息）%s.csv" % str (time_str)

    error_files = []
    none_file = 1  # 此参数用来判断是否上传文件，如果为0则不上传文件

    title = ["日期", '门店名称', '门店id', '门店所在城市', '订单编号', '下单时间', "活动明细", "分类", "商家承担费用", '平台承担费用', '代理商承担费用', '折扣商品', '原价', '现价']
    with open (output + "/%s" % str (banner) + "mt订单数据(活动信息）%s.csv" % str (time_str), "a", newline='', encoding="utf-8-sig", errors="ignore") as t:
        writer = csv.writer (t)
        writer.writerow (title)
        for a, b, files in os.walk (mtinput, topdown=False):
            if len (files) == 0:
                print('No Meituan files!')
                none_file = 0
            else:
                for file in files:
                    try:
                        with open (mtinput + '/' + file,encoding='utf-8') as f:
                            data = pd.read_csv (f, low_memory=False)
                            list_1 = data['日期']
                            no = 0

                            for n in list_1:
                                num += 1
                                dingdan = data.iloc[no]
                                riqi = dingdan['日期']
                                shop = dingdan['门店名称']
                                shopid = dingdan['门店id']
                                city = dingdan['门店所在城市']
                                dingdanid = dingdan['订单编号']
                                xiadanshijian = dingdan['下单时间']

                                xinxi = dingdan['活动信息']
                                no += 1
                                try:
                                    xinxi_list = xinxi.split ('/')

                                    for b in xinxi_list:
                                        mo += 1

                                        try:
                                            p = r'会员红包|减配|商家代金券|津贴|购买|支付红包|门店新客立减|平台新客立减|首单立减|跨店免运费'
                                            r = re.compile (p)
                                            fenlei = r.search (b).group ()
                                        except:
                                            try:
                                                p1 = r'满\d+\.\d+元减'
                                                r1 = re.compile (p1)
                                                fenlei1 = r1.search (b).group ()
                                                fenlei = "订单满减"
                                            except:
                                                p2 = r'满\d+\.\d+元赠'
                                                r2 = re.compile (p2)
                                                fenlei2 = r2.search (b).group ()
                                                fenlei = "订单满赠"

                                        try:
                                            q = r'商家承担:\d*?\.\d*?元'
                                            s = re.compile (q)
                                            cash = s.search (b).group ()
                                            cash_no = re.search ('\d+\.\d+', cash, flags=0).group ()
                                        except:
                                            cash_no = 0

                                        try:
                                            q11 = r'代理商承担:\d*?\.\d*?元'
                                            s11 = re.compile (q11)
                                            cash11 = s11.search (b).group ()
                                            cash_no11 = re.search ('\d+\.\d+', cash11, flags=0).group ()
                                        except:
                                            cash_no11 = 0

                                        try:
                                            q1 = r'新美大承担:\d*?\.\d*?元'
                                            s1 = re.compile (q1)
                                            cash1 = s1.search (b).group ()
                                            cash_no1 = re.search ('\d+\.\d+', cash1, flags=0).group ()
                                        except:
                                            cash_no1 = 0

                                        try:
                                            q2 = r'购买(.*)原价(.*)元现价(.*)元\['
                                            s2 = re.compile (q2)
                                            cash_no2 = s2.search (b).group (1)
                                            cash_no3 = s2.search (b).group (2)
                                            cash_no4 = s2.search (b).group (3)
                                            # cash_no2 = re.search('[^购买](.*)',cash2,flags=0).group()
                                        except:
                                            cash_no2 = ''
                                            cash_no3 = ''
                                            cash_no4 = ''

                                        ms = [[riqi, shop, shopid, city, dingdanid, xiadanshijian, b, fenlei, cash_no, cash_no1, cash_no11, cash_no2, cash_no3, cash_no4]]
                                        writer.writerows (ms)

                                except:
                                    ms = [[riqi, shop, shopid, city, dingdanid, xiadanshijian, "null", "null", 0, 0, 0, "", "", ""]]
                                    writer.writerows (ms)
                                # print ('\r 已拆解%s行，拆解出%s行' % (str (num), str (mo)), end='')
                    except Exception as e:
                        print(e)
                        error_files.append('【活动信息无法解析】'+file)

    return formactions_out_file_pathM, formactions_out_file_nameM, error_files, none_file

def update_7qiaoPlus_row(Token, applicationId, formModelId, variables, row_id, version):
    '''
    :param Token: str() 密钥
    :param applicationId: str() 应用id
    :param formModelId: str() 表单id
    :param variables: 参数键值对，传入需更新的内容{
      //"字段名" : "值"
      "fieldName": "fieldValue",
      "age": "22",
      "单行文本" : "文本内容",
      "数字" : 123,
      "单项选择" : "1",
      "多项选择" : ["1", "2", "3"],
      //人员、部门选择控件需要传入对应的id
      "人员单选" : "123412341243",
      "人员多选" : ["123","345","456"],
      "日期" : "2020-10-10",
      "日期时间" : "2020-10-10 10:10:00",
      "时间" : "10:10:00",
      "富文本" : "文本内容",
      //图片上传、文件上传、音频、视频可以通过调用文件上传接口，将返回的结果“data”值进行传参
      "图片上传" : "data",
      //手写签名可以通过调用文件上传接口，将返回的结果“fileUrl”值进行传参
      "手写签名" : "fileUrl"
    }
    :param row_id: 表单实例id
    :param version: 更新版本 需要通过函数 select_7qiaoPlus_row 获取
    :return:
    '''
    headers_api = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36',
        "Content-type": "application/json",
        "X-Auth0-Token": Token}
    url_update = f'https://qiqiao.do1.com.cn/plus/cgi-bin/open/applications/{applicationId}/forms/{formModelId}'
    param_json = {"variables": '',
                  "id": row_id,
                  "version": version,
                  "loginUserId": "92874ed35ecb45f51a58adcf18e99b5e"
                  }
    param_json['variables'] = variables
    try:
        resp = requests.put (url_update, json=param_json, headers=headers_api).json ()
        if resp['code'] != 0:
            print(resp['code'])
        else:
            ...
            # print('Done')
    except Exception as e:
        print(e)

def select_7qiaoPlus_row(Token, applicationId, formModelId, row_id):
    '''
    :param Token: str() 密钥
    :param applicationId: str() 应用id
    :param formModelId: str() 表单id
    :param row_id: str() 表单实例id
    :return: resp json()格式
    '''
    headers_api = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36',
        "Content-type": "application/json",
        "X-Auth0-Token": Token}
    url_select = f'https://qiqiao.do1.com.cn/plus/cgi-bin/open/applications/{applicationId}/forms/{formModelId}/{row_id}'
    resp = requests.get (url_select, headers=headers_api).json ()

    return resp

#20220907更新files_download
def files_download(Token, applicationId, resp, save_path, row_id, fieldName):
    '''

    :param Token: str
    :param applicationId: str
    :param resp: dict
    :param save_path: str
    :param fieldName: str '输入文件'
    :return: version, save_path_e, save_path_m
    '''
    #创建文件夹用于存储下载的文件
    path = save_path + '/' + row_id
    save_path_e = save_path + '/' + row_id +'/elmsave'
    save_path_m = save_path + '/' + row_id +'/mtsave'
    os.mkdir (path)
    os.mkdir (save_path_e)
    os.mkdir (save_path_m)

    # 新增功能20220907
    save_path_e_bill = save_path + '/' + row_id + '/elmbillsave'
    save_path_m_bill = save_path + '/' + row_id + '/mtbillsave'
    os.mkdir (save_path_e_bill)
    os.mkdir (save_path_m_bill)

    version = resp['data']['version']
    files_info = resp['data']['variables'][fieldName]

    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36',
                   "Content-type": "application/json",
                   "X-Auth0-Token": Token}

    for file_info in files_info:

        fileId = file_info['fileId']
        name = file_info['name']

        a = name.split('.')

        # 新增功能20220907 以下if...elif..elif..else
        if 'xlsx' in a and 'csv' not in a and 'xls' not in a:
            if '账单' in name:
                url_down = f'https://qiqiao.do1.com.cn/plus/cgi-bin/open/file_download/applications/{applicationId}/files/{fileId}'
                resp_down = requests.get (url_down, headers=headers).content

                with open (save_path_e_bill + '/%s' % str (name), mode='wb') as f:
                    f.write (resp_down)
                print (name, 'bill->Download Over')
            else:
                url_down = f'https://qiqiao.do1.com.cn/plus/cgi-bin/open/file_download/applications/{applicationId}/files/{fileId}'
                resp_down = requests.get (url_down, headers=headers).content

                with open (save_path_e + '/%s' % str (name), mode='wb') as f:
                    f.write (resp_down)
                print (name, '->Download Over')

        elif 'csv' in a and 'xlsx' not in a and 'xls' not in a:
            url_down = f'https://qiqiao.do1.com.cn/plus/cgi-bin/open/file_download/applications/{applicationId}/files/{fileId}'
            resp_down = requests.get (url_down, headers=headers).content
            with open (save_path_m + '/%s' % str (name), mode='wb') as f:
                f.write (resp_down)
            print (name, '->Download Over')

        elif 'xls' in a and 'xlsx' not in a and 'csv' not in a:
            url_down = f'https://qiqiao.do1.com.cn/plus/cgi-bin/open/file_download/applications/{applicationId}/files/{fileId}'
            resp_down = requests.get (url_down, headers=headers).content
            with open (save_path_m_bill + '/%s' % str (name), mode='wb') as f:
                f.write (resp_down)
            print (name, 'bill->Download Over')
        else:
            print ('file_name error!')
    # 新增功能20220907 return有更新
    return version, save_path_e, save_path_m, save_path_e_bill, save_path_m_bill

def my_callback(monitor):
    # 进度条读取
    progress = (monitor.bytes_read / monitor.len) * 100
    # print("\r 文件上传进度：%d%%(%d/%d)" % (progress, monitor.bytes_read, monitor.len), end=" ")
    return progress

def files_upload(Token, applicationId, formModelId, file_name, file_path):
    '''

    :param Token: str
    :param applicationId: str
    :param formModelId: str
    :param file_name: str 文件名
    :param file_path: str 文件打开路径
    :return: resp_upload: dict josn(格式) 常用取值方式: resp_upload['data']
    '''
    url_upload = f'https://qiqiao.do1.com.cn/plus/cgi-bin/open/file_upload/applications/{applicationId}/form_models/{formModelId}'

    params = 'fieldType=FILEUPLOAD'

    # 流文件上传方式
    data_upload = MultipartEncoder (
        {'files': (file_name, open (file_path, 'rb'), 'text/csv')})

    data_upload = MultipartEncoderMonitor (data_upload, my_callback)

    headers_upload = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36',
        "Content-type": data_upload.content_type,
        "X-Auth0-Token": Token}

    resp_upload = requests.post (url_upload, headers=headers_upload, params=params, data=data_upload).json ()

    return resp_upload

#20220907更新tool_for_use
def tool_for_use(row_id,gongneng):

    applicationId = 'c10510196559404b99b0f8591e8baa5b'  # 测试
    formModelId = '6311c1161e8a542078bf5eff'  # 文件上传下载
    fieldName = '输入文件'  # 需提取文件的字段名
    # row_id = '6814692804769693696'

    save_path = '/home/python_flies/files_handle'

    banner = ''

    host = 'localhost'
    passwd = '111...Clown'
    db_name = 'zuzu_data'
    port = 63306
    db = linkTomySql (host, passwd, db_name, port)

    key_json = read_key_value_pair (db, '道一云', '7qiaoPlus', 'all')
    Token = key_json['data']

    try:
        shutil.rmtree (save_path + '/' + row_id)
    except:
        ...

    resp = select_7qiaoPlus_row (Token, applicationId, formModelId, row_id)
    code = resp['code']

    if code == -401:
        update_key_value_pair_7qiaoPlus (db, '道一云', '7qiaoPlus', 'all', '')
        key_json = read_key_value_pair (db, '道一云', '7qiaoPlus', 'all')
        Token = key_json['data']
        resp = select_7qiaoPlus_row (Token, applicationId, formModelId, row_id)
    else:
        ...

    # 20220907更新 version, save_path_e, save_path_m, save_path_e_bill, save_path_m_bill
    version, save_path_e, save_path_m, save_path_e_bill, save_path_m_bill = files_download (Token, applicationId,resp, save_path, row_id, fieldName)
    
    read_and_convert (save_path_m)
    recheck_again (save_path_m)
    print ('Encoding Translate Over!')

    out_files = []
    mark = 2
    
    output = save_path + '/' + row_id + '/out'
    os.mkdir (output)
    
    try:
        # 20220907更新 bill_dict
        bill_dict,error_files_bill = order_bill (save_path_m_bill, save_path_e_bill, row_id)

        if gongneng == '1':
            # 20220907更新 bill_dict
            file_path, file_name, error_files, none_file = order_formgoods (save_path_m, save_path_e, save_path, row_id,banner, bill_dict)
            if none_file >0:
                resp_upload = files_upload (Token, applicationId, formModelId, file_name, file_path)
                out_files.append(resp_upload['data'][0])
            else:
                ...
    
            error_file_list = error_files + error_files_bill
            if len (error_file_list) > 0:
                error_msg = '\n2.【下方为拆解失败的文件，请检查对应文件是否有误！】\n' + '\n'.join (error_files+ error_files_bill)
            else:
                error_msg = ''
    
        elif gongneng == '2':
            file_pathAE, file_nameAE, error_filesAE, none_fileAE = order_actions_e(save_path_e,save_path,row_id,banner)
            if none_fileAE == 1:
                resp_upload_AE = files_upload (Token, applicationId, formModelId, file_nameAE, file_pathAE)
                out_files.append (resp_upload_AE['data'][0])
            else:
                ...
    
            file_pathAM, file_nameAM, error_filesAM, none_fileAM = order_actions_m (save_path_m, save_path, row_id, banner)
            if none_fileAM == 1:
                resp_upload_AM = files_upload (Token, applicationId, formModelId, file_nameAM, file_pathAM)
                out_files.append (resp_upload_AM['data'][0])
            else:
                ...
    
            error_file_list =  error_filesAE + error_filesAM + error_files_bill
            if len (error_file_list) > 0:
                error_msg = '\n2.【下方为拆解失败的文件，请检查对应文件是否有误！】\n' + '\n'.join ( error_filesAE + error_filesAM + error_files_bill)
            else:
                error_msg = ''
    
        elif gongneng == '3':
            # 20220907更新 bill_dict
            file_path, file_name, error_files, none_file = order_formgoods (save_path_m, save_path_e, save_path, row_id, banner, bill_dict)
            if none_file > 0:
                resp_upload = files_upload (Token, applicationId, formModelId, file_name, file_path)
                out_files.append (resp_upload['data'][0])
            else:
                ...
    
            file_pathAE, file_nameAE, error_filesAE, none_fileAE = order_actions_e (save_path_e, save_path, row_id, banner)
            if none_fileAE == 1:
                resp_upload_AE = files_upload (Token, applicationId, formModelId, file_nameAE, file_pathAE)
                out_files.append (resp_upload_AE['data'][0])
            else:
                ...
    
            file_pathAM, file_nameAM, error_filesAM, none_fileAM = order_actions_m (save_path_m, save_path, row_id, banner)
            if none_fileAM == 1:
                resp_upload_AM = files_upload (Token, applicationId, formModelId, file_nameAM, file_pathAM)
                out_files.append (resp_upload_AM['data'][0])
            else:
                ...
            error_file_list = error_files + error_filesAE + error_filesAM + error_files_bill
            if len(error_file_list) >0:
                error_msg = '\n2.【下方为拆解失败的文件，请检查对应文件是否有误！】\n'+'\n'.join(error_files+error_filesAE+error_filesAM + error_files_bill)
            else:
                error_msg = ''
    
        else:
    
            error_msg = '\n2.【请选择您要的功能】'
            mark = 1
        
    except Exception as eee:
        print(eee)
        error_msg = '\n2.【服务存在未知错误，请联系信息部】'
        mark = 1

    variables = {'输出文件': out_files,
                 '执行结果': f'1.【请至输出文件下载】{error_msg}',
                 '拆解状态':mark,
                 '校验文件拆解按钮':'0'}

    update_7qiaoPlus_row (Token, applicationId, formModelId, variables, row_id, version)

    shutil.rmtree (save_path + '/' + row_id)

    print (f'\nTASK_ID【{row_id}】【function：{gongneng}】')

if __name__ == '__main__':

    row_id = '6815966588991406080'
    tool_for_use (row_id)



    '''

    applicationId = '62cfb64cb433f27040103cca'  # 测试
    formModelId = '630f1fb798c136661c4d03a6'  # 文件上传下载
    fieldName = '输入文件'  #需提取文件的字段名
    row_id = '6814692804769693696'

    save_path = 'C:/Users/ClownHe/Desktop/goods'

    banner = ''

    host = 'localhost'
    passwd = '111???clown'
    db_name = 'hexingxing'
    db = linkTomySql (host, passwd, db_name)

    update_key_value_pair_7qiaoPlus (db, '道一云', '7qiaoPlus', 'all', '')
    key_json = read_key_value_pair (db, '道一云', '7qiaoPlus', 'all')
    Token = key_json['data']

    resp = select_7qiaoPlus_row (Token, applicationId, formModelId, row_id)

    version, save_path_e, save_path_m = files_download (Token, applicationId, resp, save_path, row_id, fieldName)

    file_path,file_name = order_formgoods (save_path_m, save_path_e, save_path, row_id, banner)

    resp_upload = files_upload(Token, applicationId, formModelId, file_name, file_path)

    variables = {'输出文件':resp_upload['data'],
                 '执行结果':'已拆解成功，请至输出文件下载！'}

    update_7qiaoPlus_row (Token, applicationId, formModelId, variables, row_id, version)

    shutil.rmtree(save_path+'/'+row_id)

    print('\nok')
    
    '''