python_files/cppc_python脚本/服务器脚本/demo_外购监测.py

# -*- codeing = utf-8 -*-
# @Time : 2023/3/12 16:05
# @Author : Clown
# @File : demo_外购监测.py
# @Software : PyCharm
import pandas as pd
import pymysql
import json
from dateutil.parser import parse
from datetime import datetime,timedelta
from demo_id配对工具 import selectCustomerIdForm
import time

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,
            port=port,
            charset='utf8mb4',
            local_infile=1)
        print ('\nconnect to mysql server 成功')
        print ('---------------------------------------')
    except:
        print ("\ncould not connect to mysql server")
        db = "连接失败"
    return db


#按要求筛选门店，当前要求为满足近30天单量大于0的门店
def selectShopsMeetTheRequirements(db,end_date,requ_fate,file_path):
    wmplate_dict = {'美团':'meituan',
                    '饿了么':'eleme'}
    cursor = db.cursor()
    end_date = parse (end_date).date ()
    start_date = (end_date + timedelta (days=-requ_fate+1)).strftime ('%Y-%m-%d')
    print(start_date,end_date)
    sql = f'''SELECT 
            `品牌`,
            `平台`,
            `门店id`,
            SUM(`收入`) AS `总收入`,
            SUM(`有效单量`) AS `总单量`,
            COUNT(`日期`) AS `营业天数`
        FROM 
            shop_daily_operation_data_app 
        WHERE 
            `有效单量` > 0 
            AND `日期` >= '{start_date}' 
            AND `日期` <= '{end_date}'  
        GROUP BY 
            `品牌`,
            `平台`,
            `门店id` 
        ORDER BY
            `营业天数` DESC'''
    cursor.execute(sql)
    value_out_list = cursor.fetchall ()
    print(len(value_out_list))
    df_out = []
    for value_out in value_out_list:
        row = list(value_out)
        out_list = selectCustomerIdForm(db,int(row[2]),wmplate_dict[row[1]])
        if len(out_list) == 0:
            customer_id = ''
            customer_name = ''
            yidinghuo_id = ''
        else:
            customer_id = out_list[0][0]
            customer_name = json.loads(out_list[0][1])['customer_name']
            yidinghuo_ids = json.loads (out_list[0][1])['yidinghuo']
            if len(yidinghuo_ids) == 0:
                yidinghuo_id = ''
            elif len(yidinghuo_ids) == 1:
                yidinghuo_id = yidinghuo_ids[0]
            else:
                yidinghuo_id = ','.join(yidinghuo_ids)

        row.append(customer_id)
        row.append(customer_name)
        row.append (yidinghuo_id)
        df_out.append(row)
    df_out = pd.DataFrame(df_out,columns=['品牌','平台','门店id','总收入','总单量','营业天数','店务通编号','门店名称','易订货id'])
    file_path = file_path+f'/selectShopsMeetTheRequirements【{start_date}to{end_date}】.xlsx'
    df_out.to_excel(file_path)
    cursor.close()
    return file_path

def selectCustomerInfo(db,customer_id):
    cursor = db.cursor ()
    sql = f'''SELECT * FROM customer_id_form WHERE customer_id = '{str(customer_id)}';'''
    cursor.execute (sql)
    value_out_list = cursor.fetchall ()
    cursor.close ()
    if len(value_out_list)==0:
        dict_out = 0
    else:
        dict_out = json.loads(value_out_list[0][1])
    return dict_out

def sku_in_jianguan_dict(db):
    cursor = db.cursor ()
    sql = '''SELECT * FROM supply_chains_waigoujicha'''
    cursor.execute (sql)
    value_out_list = cursor.fetchall ()
    cursor.close ()
    df_in = pd.DataFrame(value_out_list,columns=['品牌','商品关键词','供应链原料id','原料名称','售卖单位','单件数量','考核方式（商品/单量）'])
    brand_names = list(set(df_in['品牌']))
    dict_out = {}
    for brand_name in  brand_names:
        dict_out[brand_name] = {}
        df_in0 = df_in[df_in['品牌']==brand_name]
        sku_kinds = list(set(df_in0['考核方式（商品/单量）']))
        for sku_kind in sku_kinds:
            dict_out[brand_name][sku_kind]=[]
            df_in1 = df_in0[df_in0['考核方式（商品/单量）'] == sku_kind]
            sku_key_names = list(set(df_in1['商品关键词']))
            for sku_key_name in sku_key_names:
                df_in2 = df_in1[df_in1['商品关键词'] == sku_key_name]
                sku_ids = list(set(df_in2['供应链原料id']))
                dict_out0 = {'sku_key_name':sku_key_name,
                             'sku_ids':sku_ids}
                dict_out[brand_name][sku_kind].append(dict_out0)
    # print(dict_out)
    return dict_out,df_in

def mapWmOrderData(db,max,min,wm_ids):
    cursor = db.cursor ()
    if max == min:
        sql = f'''SELECT 
                SUM(`收入`) AS `区间收入`,
                SUM(`有效单量`) AS `区间单量`
            FROM 
                shop_daily_operation_data_app 
            WHERE 
                `门店id` IN {wm_ids} 
                AND `日期` >= '{min}' 
                AND `日期` <= '{max}';'''
    else:
        sql = f'''SELECT 
                        SUM(`收入`) AS `区间收入`,
                        SUM(`有效单量`) AS `区间单量`
                    FROM 
                        shop_daily_operation_data_app 
                    WHERE 
                        `门店id` IN {wm_ids} 
                        AND `日期` > '{min}' 
                        AND `日期` <= '{max}';'''

    cursor.execute (sql)
    value_out_list = cursor.fetchall ()
    cursor.close ()
    row_cnt = len (value_out_list)
    if row_cnt == 0:
        value_out = {'区间收入':0,
                     '区间单量':0}
    else:
        value_out = {'区间收入':value_out_list[0][0],
                     '区间单量':value_out_list[0][1]}
    return value_out

def mapOrderData(db,end_date,customer_id,customer_name,wm_ids,dh_ids,sku_ids,sku_key_name):
    cursor = db.cursor ()
    sql = f'''SELECT t3.`ordersDate`,SUM( t3.`totalNum` ) AS `totalNum` FROM (SELECT t1.`ordersDate`,t1.`skuId`,t1.`skuPcs`,t1.`totalNum` * t2.`PcsNum` AS `totalNum` FROM (SELECT `ordersDate`,`skuId`,`skuPcs`,SUM( `skuOrdNum` ) AS `totalNum` FROM supply_chains_orders_app WHERE `skuId` IN {sku_ids}  AND `customersId` IN {dh_ids} AND `ordersDate` <= '{end_date}' GROUP BY `ordersDate`,`skuId`,`skuPcs` ORDER BY `ordersDate` DESC )t1 LEFT JOIN (SELECT * FROM supply_chains_waigoujicha)t2 ON t1.`skuId` = t2.`skuId` AND t1.`skuPcs` = t2.`skuPcs` ) t3 GROUP BY t3.`ordersDate` ORDER BY t3.`ordersDate` DESC;'''
    cursor.execute (sql)
    value_out_list = cursor.fetchall ()
    cursor.close()
    row_cnt = len(value_out_list)

    #基础参数
    max = 0
    min = ''
    i = -1
    result = ''
    result_error = ''
    result_ok = '订货正常'
    result_no = '订货异常'
    result_label = ''
    totalNum_sum = 0
    odr_cnt_sum = 0
    totalNum_near1 = 0
    odr_cnt_near1 = 0
    difference_near1 = 0
    min_near = 0

    #输出列表
    df_out_detail = []
    df_out_result = []

    if row_cnt == 0:
        result = f'门店无【{sku_key_name}】订货记录'
        result_error = f'门店过往无【{sku_key_name}】订货记录，请核实门店供应平台的id是否有效或是否完整'
        result_label = '无法查询'
    else:
        for i in range(row_cnt):
            if i == 0:
                max = end_date
            else:
                max = value_out_list[i - 1][0]
            min = value_out_list[i][0]
            totalNum = value_out_list[i][1]
            #查询订单逻辑 min<  =<max
            wmOrder = mapWmOrderData (db, max, min, wm_ids)
            income = wmOrder['区间收入']
            odr_cnt = wmOrder['区间单量']
            if income is None:
                result_error = f'此门店可能无订货记录，或平台id可能存在不全，或历史有过平台店铺重开操作，平台数据统计截至{max}【此情况下门店订货数据统计仅统计至该截至日期，截至日期前的【{sku_key_name}】订货数据将不纳入监测范围】'
                result_label = '数据不完整'
                break
            else:
                if i == 0:
                    totalNum_near1 = int (totalNum)
                    odr_cnt_near1 = int (odr_cnt)
                    difference_near1 = odr_cnt_near1 - totalNum_near1
                    min_near = min
                totalNum_sum = totalNum_sum + totalNum
                odr_cnt_sum = odr_cnt_sum + odr_cnt
                detail_dict = {'门店编号':str(customer_id),
                               '门店名称':customer_name,
                               '商品关键词':sku_key_name,
                               '批次(由近至远)':i+1,
                               '区间开始日期<':min,
                               '区间结束日期≥':max,
                               '批次订货量':totalNum,
                               '区间平台单量':odr_cnt,
                               '区间平台收入':income}
                df_out_detail.append(detail_dict)
                # print(detail_dict)
                # print (customer_id,i + 1, max, min, totalNum, income, odr_cnt)
                if totalNum_sum >= odr_cnt_sum:
                    result = f'基于门店近{i + 1}次【{sku_key_name}】订货记录及对应周期订单数据判断，截至{min}门店【{result_ok}】'
                    result_label = result_ok
                    break
                else:
                    result = f'基于门店近{i + 1}次【{sku_key_name}】订货记录【订货量{totalNum_sum}】及对应周期订单数据【单量{odr_cnt_sum}】判断，截至{min}门店【{result_no}】,按最近一次订货日期【{min_near}】订货量【{totalNum_near1}】至数据起算日【{end_date}】单量【{odr_cnt_near1}】差额计违约数量为【{difference_near1}】'
                    result_label = result_no

    if result_label == '订货正常':
        result_dict = {'门店编号':str(customer_id),
                       '门店名称':customer_name,
                       '商品关键词':sku_key_name,
                       '监管数据起算日':end_date,
                       '监管数据计算截至日':min,
                       '区间订货批次数':i+1,
                       '区间订货量':int(totalNum_sum),
                       '区间单量':int(odr_cnt_sum),
                       '判定结果':result_label,
                       '备注':result,
                       '最近一次订货量':'',
                       '截至数据起算日单量':'',
                       '违约数量':'',
                       '执行日志':result_error}
    else:
        result_dict = {'门店编号': str (customer_id),
                       '门店名称': customer_name,
                       '商品关键词': sku_key_name,
                       '监管数据起算日': end_date,
                       '监管数据计算截至日': min,
                       '区间订货批次数': i + 1,
                       '区间订货量': int (totalNum_sum),
                       '区间单量': int (odr_cnt_sum),
                       '判定结果': result_label,
                       '备注': result,
                       '最近一次订货量': totalNum_near1,
                       '截至数据起算日单量': odr_cnt_near1,
                       '违约数量': difference_near1,
                       '执行日志': result_error}
    df_out_result.append(result_dict)
    # print(result_dict)
    # print(customer_id,customer_name,totalNum_sum,odr_cnt_sum,result_label,result,result_error)
    return df_out_detail,df_out_result

def waiGouJiCha(db,file_path,file_path_out,sku_in_jianguan_dict,df_sup_goods,end_date):
    end_date = parse (end_date).date ()
    df_in = pd.read_excel(file_path)
    cnt = 0
    customer_id_list = list(set(df_in['店务通编号']))
    df_out_detail = []
    df_out_result = []
    for customer_id in customer_id_list:
        cnt += 1
        dict_out = selectCustomerInfo (db, customer_id)
        if dict_out == 0:
            print(f'当前店务通门店编码【{customer_id}】在customer_id_form中无返回值')
        else:
            customer_name = dict_out['customer_name'] #门店名
            wm_ids = tuple(dict_out['meituan'] + dict_out['eleme']+['',''])#外卖平台id
            dh_id = list(dict_out['yidinghuo'])
            dh_id.append(customer_id)
            dh_ids = tuple(dh_id+['',''])#订货平台id
            brand_name = list(set(df_in[df_in['店务通编号']==customer_id]['品牌']))[0]
            sku_jianguan_kinds = ['单量']
            for sku_jianguan_kind in sku_jianguan_kinds:
                sku_infos = sku_in_jianguan_dict[brand_name][sku_jianguan_kind]
                for sku_info in sku_infos:
                    sku_key_name = sku_info['sku_key_name']
                    sku_ids = tuple(sku_info['sku_ids']+['',''])#商品id
                    print (customer_id, customer_name, wm_ids, dh_ids, sku_ids, sku_key_name)
                    out_detail,out_result = mapOrderData (db, end_date,customer_id,customer_name, wm_ids, dh_ids, sku_ids, sku_key_name)
                    df_out_detail = df_out_detail + out_detail
                    df_out_result = df_out_result + out_result
    print(f'共计门店数{cnt}')
    df_out_detail = pd.DataFrame(df_out_detail)
    df_out_result = pd.DataFrame(df_out_result)

    writer = pd.ExcelWriter (file_path_out+f'/门店稽查数据报告(数据截至日期{end_date}).xlsx')
    df_out_result.to_excel(writer,sheet_name='外购稽查结果',index=False)
    df_out_detail.to_excel(writer,sheet_name='稽查数据源',index=False)
    df_in.to_excel (writer, sheet_name='门店清单',columns=['品牌', '平台', '门店id', '总收入', '总单量', '营业天数', '店务通编号', '门店名称', '易订货id'], index=False)
    df_sup_goods.to_excel(writer,sheet_name='监管产品清单',index=False)
    writer.save()

if __name__ == '__main__':
    time_start = time.time()
    if 1 == 1:
        host = 'localhost'
        passwd = '111???clown'
        db_name = 'hexingxing'
        port = 3306
        db = linkTomySql (host, passwd, db_name, port)
        #end_date必须为有数据的当天
        end_date = '20230312'
        requ_fate = 30
    if 1==0:
        wm_ids = [13223788,506074673]
        wm_ids = tuple(wm_ids+['',''])
        customer_id = '6917635395090128896'
        customer_name = '周浦店'
        dh_ids = ["6917635395090128896",'20211019003325']
        dh_ids = tuple(dh_ids+['',''])
        # sku_ids = ["P2953993404",'P5665728343']
        # sku_ids = ["P1956508934", 'P5456508916', 'P6958185748', 'P1658181079','P2562679809']
        sku_ids = ["P5864140704",'P864145484']
        sku_ids = tuple(sku_ids+['',''])
        sku_key_name = '四件套'
        end_date = parse (end_date).date ()
        mapOrderData (db, end_date,customer_id,customer_name, wm_ids, dh_ids, sku_ids, sku_key_name)
    if 1== 1:
        try:
            file_path = r'C:\Users\ClownHe\Desktop\门店外购监测'
            customer_df_path = selectShopsMeetTheRequirements (db, end_date, requ_fate, file_path)
            sku_in_jianguan_dict,df_sup_goods = sku_in_jianguan_dict (db)
            waiGouJiCha(db,customer_df_path,file_path,sku_in_jianguan_dict,df_sup_goods,end_date)
        except Exception as e:
            print(e)

    if 1 == 0:
        file_path = r'C:\Users\ClownHe\Desktop\门店外购监测' + '/供应链考核规则表-脚本.xlsx'
        sku_in_jianguan_dict (db)
    db.close ()
    time_end = time.time()
    print(f'总执行时间{int(time_end -time_start)/60}min')