2020-11-19

仓库自动导出 每日入库数据 并清洗数据（主要是对 入库数据只保留一天内一个款式多个尺码，再剔除本月内 重复数据）

import os
import sys
import time
import xlrd
import xlwt
import win32gui, win32api
import win32con
from pynput import mouse, keyboard
import csv
import os
import pandas as pd
import datetime


def getYesterday():
    today=datetime.date.today()
    oneday=datetime.timedelta(days=1)
    yesterday=today-oneday
    return yesterday
f  = getYesterday()
dc = f.strftime("%Y-%m-%d ").replace('-','')+'235959'


mouse_control = mouse.Controller()
keyboard_control = keyboard.Controller()

t = time.strftime('%m.%d',time.localtime(time.time()))
yue=time.strftime('%Y%m',time.localtime(time.time()))
yue1 = yue+'01'

t_name = 'C:\\Users\\Administrator\\Desktop\\新建文件夹\\'+t+'.csv'
name_file = t+'.csv'

def click_and_wait(x, y):
    time.sleep(1)
    mouse_control.position = (x, y)
    time.sleep(0.5)
    mouse_control.click(mouse.Button.left, 1)
    time.sleep(1)


def click_and_send_text(x, y, text):
    time.sleep(0.5)
    mouse_control.position = (x, y)
    time.sleep(0.5)
    mouse_control.click(mouse.Button.left, 1)
    time.sleep(0.5)

    keyboard_control.type(text)
    time.sleep(0.5)

def close_erp_tabs():
    mouse_control.position = (57, 113)
    mouse_control.click(mouse.Button.right, 1)
    time.sleep(0.5)

    mouse_control.position = (90, 190)
    mouse_control.click(mouse.Button.left)
    time.sleep(0.5)

def create_product(filename):

    # 点开统计
    click_and_wait(822, 46)

    # 点库存
    click_and_wait(827, 285)

    # 点入库汇总账
    click_and_wait(960, 280)

    # 选择仓库
    click_and_wait(214, 155)

    # 选择河南仓
    click_and_wait(96, 216)

    # 确定
    click_and_wait(264, 438)

    # 点击更多
    click_and_wait(1328, 162)

    # 入库类别
    click_and_wait(1322, 313)

    # 选择仓库
    click_and_wait(1224, 470)

    # 搜索
    click_and_wait(1283, 161)
    #click_and_wait(1283, 161)
    # 导出
    # 右键
    mouse_control.position = (496, 200) # 鼠标放在的位置
    mouse_control.click(mouse.Button.right, 1)  # 全选货品  # 鼠标右键单击一次

    click_and_wait(540, 258)  # 左键
    #click_and_wait(894, 340)
    # 右键选中

    click_and_send_text(830, 338,t_name)  # 输入货品编码
    keyboard_control.press(keyboard.Key.enter)
    time.sleep(1)


    click_and_wait(1079, 609)  # 否

#导出本月全部 入库明细
    #click_and_wait(450, 155)
    click_and_send_text(322,157, yue1)
    #click_and_wait(330, 233)
    click_and_send_text(498, 152,dc)
    click_and_wait(1282, 156)

    mouse_control.position = (496, 200)  # 鼠标放在的位置
    mouse_control.click(mouse.Button.right, 1)  # 全选货品  # 鼠标右键单击一次

    click_and_wait(540, 258)  # 左键
    # click_and_wait(894, 340)
    # 右键选中

    click_and_send_text(830, 338, 'quan')  # 输入货品编码
    keyboard_control.press(keyboard.Key.enter)
    time.sleep(1)

    click_and_wait(1079, 609)  # 否

    #关闭erp
    click_and_wait(1900, 16)
    click_and_wait(940, 608)

def open_erp():
    wdt_handle = win32gui.FindWindow('CoolWindow', '旺店通ERP旗舰版')
    if not wdt_handle:  # 旺店通ERP旗舰版.exe - 快捷方式
        path1 = os.path.dirname(os.path.abspath(__file__))
        ph_ls = os.listdir(os.path.join(path1))
        erp_path = None
        for ph_ls_ in ph_ls:
            if '旺店通ERP旗舰版' in ph_ls_:
                erp_path = os.path.join(path1, ph_ls_)
        if not erp_path:
            print('没有找到erp快捷方式')
            return None
        # os.startfile('D:\\旺店通ERP旗舰版\\旺店通ERP旗舰版.exe')
        os.startfile(erp_path)
        time.sleep(10)
        wdt_handle = win32gui.FindWindow('CoolWindow', '旺店通ERP旗舰版')
        if not wdt_handle:
            return None

    win32gui.SetWindowPos(wdt_handle, win32con.HWND_TOPMOST, 0, 0, 0, 0, win32con.SWP_NOMOVE | win32con.SWP_NOSIZE)
    time.sleep(1)
    win32gui.SetWindowPos(wdt_handle, win32con.HWND_NOTOPMOST, 0, 0, 0, 0, win32con.SWP_NOMOVE | win32con.SWP_NOSIZE)
    time.sleep(1)

    # 关闭弹出的对话框
    handle = win32gui.FindWindow('#32770 (Dialog)', '旺店通管理系统')
    if handle != 0:
        keyboard_control.press(keyboard.Key.esc)
        time.sleep(1)

    # 关闭弹出的对话框
    handle = win32gui.FindWindow('#32770', '旺店通ERP旗舰版')
    if handle != 0:
        keyboard_control.press(keyboard.Key.esc)
        time.sleep(1)

    ddhh = win32gui.GetForegroundWindow()
    title = win32gui.GetWindowText(ddhh)
    if title == '修改订单':
        # 点“取消”关闭订单换货
        mouse_control.position = (180, 47)
        mouse_control.click(mouse.Button.left, 1)
        time.sleep(0.5)
    #close_erp_tabs()
    return wdt_handle

# if __name__ == '__main__':
#     x = win32api.GetSystemMetrics(win32con.SM_CXSCREEN)
#     y = win32api.GetSystemMetrics(win32con.SM_CYSCREEN)
#     info = input('运行前先关闭正在运行的erp，执行回车后就不要再动鼠标'+'\n'+':')
#     time.sleep(3)
#     h = open_erp()
#     if not h:
#         print('没有打开ERP')
#         sys.exit(1)
#     path = os.path.dirname(os.path.abspath(__file__))
#     sku_list = []
#     # 入库
#     if str(info) != '1':
#         close_erp_tabs()
#         create_product(os.path.join(path, 'batch_excel.xlsx'))
#
#     print('文件已导出'+' : '+t+'.csv')
#     #cost_in_store(products_info)

def is_str(string2):
    """
    检查整个字符串是否包含中文
    :param string: 需要检查的字符串
    :return: bool
    """
    output_string = ""
    cm_int = ''
    for char in string2:
        if not char.isdigit():
            output_string = output_string + char
        else:
            cm_int = cm_int + char

    return output_string

def take_sku(csv_path):
    try:
        df_1 = pd.read_csv(os.path.join(os.path.dirname(os.path.abspath(__file__)), csv_path))  # 读取csv文件
    except:
        df_1 = pd.read_excel(os.path.join(os.path.dirname(os.path.abspath(__file__)), csv_path)) # 读取普通文件
    df_1 = df_1[['商家编码', '货品编号', '规格名称']]
    lists_3 = df_1.values.tolist()
    hpbm = sorted({lists_3_[1] for lists_3_ in lists_3 if str(lists_3_[1]) != 'nan'})
    # print(len(hpbm))
    sku_list = []
    for hpbm_ in hpbm:
        sku_on = [lists_3_ for lists_3_ in lists_3 if hpbm_ in lists_3_]
        cc = []
        for sku_on_ in sku_on:
            aa = is_str(sku_on_[2][-3:])
            aa = sku_on_[2][:-3] + aa
            if aa not in cc:
                cc.append(aa)
        for cc_ in cc:
            for sku_on_ in sku_on:
                if cc_ in sku_on_[2]:
                    sku_list.append(sku_on_[0])
                    break

    return sku_list

def csv_save(sku_list):
    if not os.path.isfile('样衣sku.csv'):
        with open('样衣sku.csv', 'a', newline='') as csv_file:
            writer = csv.writer(csv_file)
            writer.writerow(['商家编码', '数量', '是否是曾', '折后价', '备注'])

    with open('样衣sku.csv', 'a', newline='') as csv_file:
        writer = csv.writer(csv_file)
        for sku_list_ in sku_list:
            if sku_list_ is not None:
                writer.writerow([sku_list_, '1'])

def jihe():
    quan = pd.read_csv('C:\\Users\\Administrator\\Desktop\\新建文件夹\\quan.csv')
    data = pd.read_csv('C:\\Users\\Administrator\\Desktop\\新建文件夹\\样衣sku.csv',encoding='gb18030')
    data = data[~data['商家编码'].isin(quan['商家编码'].tolist())]
    data.to_csv('C:\\Users\\Administrator\\Desktop\\新建文件夹\\result.csv',index=False)
    return data


if __name__ == '__main__':
    x = win32api.GetSystemMetrics(win32con.SM_CXSCREEN)
    y = win32api.GetSystemMetrics(win32con.SM_CYSCREEN)
    info = input('运行前先关闭正在运行的erp，执行回车后就不要再动鼠标' + '\n' + ':')
    time.sleep(3)
    h = open_erp()
    if not h:
        print('没有打开ERP')
        sys.exit(1)
    path = os.path.dirname(os.path.abspath(__file__))
    sku_list = []
    if str(info) != '1':
        close_erp_tabs()
        create_product(os.path.join(path, 'batch_excel.xlsx'))
    print('文件已导出' + ' : ' + t + '.csv')


    sku_list = take_sku(name_file)
    csv_save(sku_list)
    print('完成数据清洗！')
    ond = jihe()
    print('完成与之前样衣去重操作！')