data-verification/Xiaomi/app02.py

import os
import numpy as np
import pandas as pd
from sqlalchemy import create_engine

########################################################################################################
# 将小米的订单中，属于小米后台的数据剔除
# data目录：小米后台下载的对账单
# 0730-0809目录：我方ftp下载的对账单
# out目录：给小米生成的新的对账单
########################################################################################################

curr_path = os.path.dirname(os.path.abspath(__file__))

# 小米后台数据
data_path = os.path.join(curr_path,"data")

print( data_path )

r_dict = {}

for root,dirs,files in os.walk( data_path ):
    for file in files:
        file_item = os.path.join(root,file)
        print( file_item )

        df = pd.read_csv( file_item, usecols=[2] ,encoding='utf8')
        for value in df.values:
            order_id = value[0].strip()
            #print( order_id )
            r_dict[ order_id ] = 1

#print( r_dict )

#  蓝色火焰后台数据
my_path = os.path.join(curr_path,"0914-1018")
out_path = os.path.join(curr_path,"out")

print( my_path )
print( out_path )


df1 = pd.DataFrame(columns=(['订单号', '手机号码', '规格', '商户订单号', '收单日期', '回调日期', '归属地', '价格', '充值状态', '状态描述']))
for root,dirs,files in os.walk( my_path ):
    success_result = [['订单号', '手机号码', '规格', '商户订单号', '收单日期', '回调日期', '归属地', '价格', '充值状态', '状态描述']]
    fail_result = [['订单号', '手机号码', '规格', '商户订单号', '收单日期', '回调日期', '归属地', '价格', '充值状态', '状态描述']]

    #df1 = pd.DataFrame(columns=(['订单号', '手机号码', '规格', '商户订单号', '收单日期', '回调日期', '归属地', '价格', '充值状态', '状态描述']))

    for file in files:
        file_item = os.path.join(root,file)
        print( file_item )

        df = pd.read_csv( file_item )
        df1 = df1.append(df)
        df1['订单号'] = df1['订单号'].astype(str) + "\t"
        #print(df1)
        #result = [['订单号','手机号码','规格','商户订单号','收单日期','回调日期','归属地','价格','充值状态','状态描述']]
        for value in df.values:
            order_id = value[3].strip()
            #print( order_id )
            if order_id in r_dict:
                #print( order_id )
                pass
            elif  value[8] ==  6:
                success_result.append(value)
            elif  value[8] ==  4:
                fail_result.append(value)
    '''
        #print( result )
        dt = np.dtype((str, 32))
        f_value = np.array( result, dtype=dt)
        print( f_value )

        frame = pd.DataFrame(f_value)
        
        f_path = os.path.basename(file_item)
        print( f_path )

        write_path = os.path.join(out_path,f_path)
        print( write_path )


        frame.to_csv(write_path, index=False, header=0 , sep=',',encoding='utf-8')


        #f_path = os.path.splitext(file_item)
        #write_path = f_path[0] + ".xlsx"
        #print( write_path )

        #frame = pd.DataFrame(f_value, index=['订单号','手机号码','规格','商户订单号','收单日期','回调日期','归属地','价格','充值状态','状态描述'])
        #frame = pd.DataFrame(f_value)
        #frame.to_excel( write_path )
        '''
    #print( result )
    dt = np.dtype((str, 32))
    f_success_value = np.array(success_result, dtype=dt)
    f_fail_value = np.array(fail_result, dtype=dt)
    #print(f_success_value)

    success_frame = pd.DataFrame(f_success_value)
    #print(success_frame)
    success_frame.iloc[:,0] = success_frame.iloc[:,0].astype(str) + "\t"

    fail_frame = pd.DataFrame(f_fail_value)
    fail_frame.iloc[:,0] = success_frame.iloc[:,0].astype(str) + "\t"

    total_fram = success_frame.append(fail_frame)
    total_fram.iloc[:,0] = success_frame.iloc[:,0].astype(str) + "\t"

    f_path = os.path.basename(file_item)
    #print( f_path )

    f_success_path = f_path.replace('.csv','成功订单.csv')
    f_fail_path = f_path.replace('.csv','失败订单.csv')
    t_path = f_path.replace('.csv','我方总订单.csv')

    #print("f_success:%s"%f_success_path)

    write_success_path = os.path.join(out_path,f_success_path)
    write_fail_path = os.path.join(out_path,f_fail_path)
    write_total_path = os.path.join(out_path,f_path)

    write_t_path = os.path.join(out_path,t_path)
    #print(write_success_path)

    success_frame.to_csv(write_success_path, index=False, header=0, sep=',', encoding='utf-8-sig')
    fail_frame.to_csv(write_fail_path, index=False, header=0, sep=',', encoding='utf-8-sig')
    total_fram.to_csv(write_total_path, index=False, header=0, sep=',', encoding='utf-8-sig')

    df1.to_csv(write_t_path, index=False, header=1, sep=',', encoding='utf-8-sig')