小结:Python自动处理周环比并邮件_python 邮件环比-CSDN博客

本文链接：https://blog.csdn.net/weixin_43650411/article/details/105090826
目标
小结
目标

每周五自动发出周环比相关数据需求邮件
小结

在这里插入图片描述
代码

# *-* coding:utf-8 _*_
'''
# 构建基表
# 筛选
'''
from sqlalchemy import create_engine
from configparser import ConfigParser
import pandas as pd
import time
import os

now = lambda : time.perf_counter()

def connectDB():
	# 连接数据库
	def getLoginInfo():
		CONF = r'C:\Users\ch\Chinasearch\c.s.conf'
		conf = ConfigParser()
		if os.path.exists(CONF):
			conf.read(CONF)
			section = 'SQL Server'
			sa = conf.get(section, 'accountname')
			pw = conf.get(section, 'password')
			host = conf.get(section, 'ip')
			port = conf.get(section, 'port')
			dbname = conf.get(section, 'dbname')
			return sa, pw, host, port, dbname
	try:
		engine = create_engine(
			"mssql+pymssql://%s:%s@%s:%s/%s" % getLoginInfo())
	except Exception as e:
		print("连接失败： {}".format(e))
	else:
		return engine

def basicInfo(conn):
	# 基本信息 + QTD
	sql = ''' select 区域, 用户名, AM, 广告主, 客户, channel 
		from basicInfo
		'''
	return conn.execute(sql).fetchall()

def qtd(conn):
	# QTD
	sql = ''' select * from QTD_Total
		'''
	return conn.execute(sql).fetchall()

def weekSpending(conn, dateStarBefore, dateEndBefore):
	# 周消费
	# eg. dateStarBefore = -15
	# eg. dateEndBefore = -1
	# class_ = 搜索点击、自主投放，新产品
	sql = ''' select *
		from 消费
	    where 日期 between DATEADD(DD, {}, GETDATE()) and DATEADD(DD, {}, GETDATE())
    	  and 金额 > 0
		  and 类别 in ('搜索点击', '自主投放', '新产品')
		'''.format(dateStarBefore, dateEndBefore)
	return conn.execute(sql).fetchall()

def transform(s, basicInfo, qtd, twoWeeks):
    ' 广告主'
    if s == 'ad':
        lis = ['广告主', '客户', 'AM']
    elif s == 'ag':
        lis = ['客户', 'channel']
    df = nearlyTwoWeeksSpend(basicInfo, qtd, twoWeeks, lis)
    lis1 = ['QTD', '上周P4P消费', '本周P4P消费', '上周搜索', '本周搜索', 
            '上周新产品', '本周新产品', '上周排名',  '环比P4P消费', 
            '环比P4P消费增长率']
    df = df.reindex(columns=lis + lis1)
    return df

def nearlyTwoWeeksSpend(basicInfo_, qtd, nearlyTwoWeeksSpending, lis):
    ''' 近两周 消费 
    1.近两周有消费
    2.
    '''
    # 基本信息 + qtd
    df = basicInfo_.merge(qtd, how='left', on='用户名')
    df.sort_values('QTD', inplace=True, ascending=False)
    if '广告主' in lis:
        for ad in df['广告主'].unique():
            df.loc[df['广告主'] == ad, ['客户']] = df.loc[df['广告主'] == ad, 
                   ['客户']].values[0]
            df.loc[df['广告主'] == ad, ['AM']] = df.loc[df['广告主'] == ad, 
                   ['AM']].values[0]
    elif '客户' in lis:
        for ag in df['客户'].unique():
            df.loc[df['客户'] == ag, ['channel']] = df.loc[df['客户'] == ag, 
                   ['channel']].values[0]
    # + 近两周消费
    df = df.merge(transWeek(nearlyTwoWeeksSpending, 6), how='left', on='用户名')
    df = df.merge(transWeek(nearlyTwoWeeksSpending, 13), how='left', on='用户名')
    df.fillna(0, inplace=True)
    # transform
    df['上周P4P消费'] = df['上周搜索'] + df['上周新产品']
    df['本周P4P消费'] = df['本周搜索'] + df['本周新产品']
    df['环比P4P消费'] = df['本周P4P消费'] - df['上周P4P消费']
    # delete sp = 0
    df = df.groupby(lis).sum()
    df.reset_index(inplace=True)
    df = df[(df['上周P4P消费'] + df['本周P4P消费']) > 0]
    # 环比增长率
    df['环比P4P消费增长率'] = df['环比P4P消费'] / df['上周P4P消费']
    df.loc[df['上周P4P消费'] == 0, '环比P4P消费增长率'] = 1
    # 上周消费排名
    df.sort_values('上周P4P消费', ascending=False, inplace=True)
    df.reset_index(inplace=True, drop=True)
    df['上周排名'] = list(map(lambda x: x+1, df.index))
    # 近两周消费排名
    df['flag'] = df['上周P4P消费'] + df['本周P4P消费']
    df.sort_values('flag', ascending=False, inplace=True)
    df.reset_index(inplace=True, drop=True)
    df.index = list(map(lambda x: x+1, df.index))
    df.drop(columns=['flag'], inplace=True)
    return df

# Top15
lis = ['上周P4P消费', '本周P4P消费', '环比P4P消费']
# Top15_1 总
Top15_1 = lambda x, y: ADSpendingRingRatio(x, y)[lis].sum()
# Top15_2 直客 top15
Top15_2 = lambda x, y: DSMaster(x, y)[['广告主'] + lis][:15]
# Top15_3 代理 top5
Top15_3 = lambda x, y: Agency(x, y)[['客户'] + lis][:5]

def GroupAM(basicInfo_, df):
    ' SZ AM 汇总 '
    data = ADSpendingRingRatio(basicInfo_, df)
    data = data.pivot_table(values=['上周P4P消费', '本周P4P消费', '环比P4P消费'],
                            index=['AM'], aggfunc=sum)
    return data
    
def Agency(basicInfo_, df):
    ' SZ 代理消费环比 '
    data = weeklyRingRatio(basicInfo_, df)
    lis1 = ['客户', '上周新产品', '本周新产品', '环比新产品', '上周搜索', 
           '本周搜索', '环比搜索']
    data = data.loc[data['channel'].isin(['代理商']), lis1]
    data['上周P4P消费'] = data['上周搜索'] + data['上周新产品']
    data['本周P4P消费'] = data['本周搜索'] + data['本周新产品']
    data['环比P4P消费'] = data['本周P4P消费'] - data['上周P4P消费']
    data = data.groupby('客户').sum()
    data.reset_index(inplace=True)
    lis = []
    for i in data['客户'].str.findall('[^a-zA-Z.,，]+$'):
        if len(i) == 0:
            lis.append('-')
        else:
            lis.append(i[0])
    data['客户'] = lis
    data.drop(index=data[data['客户'].isin(['-'])].index, inplace=True)
    data.sort_values('本周P4P消费', ascending=False, inplace=True)
    data.reset_index(inplace=True, drop=True)
    data.index = list(map(lambda x: x+1, data.index))
    return data

def DSMaster(basicInfo_, df):
    ' SZ 直客消费环比 '
    ds = ADSpendingRingRatio(basicInfo_, df)
    ds.sort_values('本周P4P消费', ascending=False, inplace=True)
    ds = ds[ds['channel'].isin(['直接客户'])]
    ds.reset_index(inplace=True, drop=True)
    ds.index = list(map(lambda x: x+1, ds.index))
    return ds

def ADSpendingRingRatio(basicInfo_, df):
    ' SZ 广告主消费环比 '
    # 深圳 AM
    lis1 = ['作废', '公司备用', '开户专用', '-']
    lis2 = ['AM', '广告主', 'channel', '用户名']
    basicInfo_ = basicInfo_.loc[basicInfo_['区域'].isin(['SZ']) &
                                ~basicInfo_['AM'].isin(lis1), lis2]
    # 周环费 & 环比
    week = basicInfo_.merge(transWeek(df, 6), how='left', on='用户名')
    week = week.merge(transWeek(df, 13), how='left', on='用户名')
    week.fillna(0, inplace=True)
    week['环比搜索'] = week['本周搜索'] - week['上周搜索']
    week['环比新产品'] = week['本周新产品'] - week['上周新产品']
    lis3 = ['AM', '广告主', 'channel', '上周新产品', '本周新产品', '环比新产品', 
            '上周搜索', '本周搜索', '环比搜索']
    week = week.reindex(columns=lis3)
    week['上周P4P消费'] = week['上周搜索'] + week['上周新产品']
    week['本周P4P消费'] = week['本周搜索'] + week['本周新产品']
    week['环比P4P消费'] = week['本周P4P消费'] - week['上周P4P消费']
    # 近两周 有消费
    # Result
    week = week[week['上周P4P消费'] + week['本周P4P消费'] > 0].groupby(['AM',
               '广告主', 'channel']).sum()
    week.reset_index(inplace=True)
    week.sort_values('环比P4P消费', inplace=True)
    return week

def weeklyRingRatio(basicInfo_, df):
    ' SZ 周环比 '
    # 深圳
    lis = ['作废', '公司备用', '开户专用', '-', '陈熙香', '顾凡凡']
    basicInfo_ = basicInfo_[basicInfo_['区域'].isin(['SZ']) &
                                ~basicInfo_['AM'].isin(lis)]
    # 搜索 近两周日消费明细
    dailySpending = df[df['类别'].isin(['搜索点击'])]
    dailySpending = dailySpending.pivot_table(
            values=['消费'], index=['用户名'], columns=['日期'])
    dailySpending.columns = dailySpending.columns.get_level_values(1)
    # 周消费 及 环比
    week = basicInfo_.merge(transWeek(df, 6), how='left', on='用户名')
    week = week.merge(transWeek(df, 13), how='left', on='用户名')
    week.fillna(0, inplace=True)
    week['环比搜索'] = week['本周搜索'] - week['上周搜索']
    week['环比新产品'] = week['本周新产品'] - week['上周新产品']
    # 有消费
    week = week[week['上周搜索'] + week['上周新产品'] + week['本周搜索'] +
                week['本周新产品'] > 0]
    lis2 = week.columns.tolist()[:6] + ['上周新产品', '本周新产品', '环比新产品', 
                              '上周搜索', '本周搜索', '环比搜索']
    week = week.reindex(columns=lis2)
    # Result
    week = week.merge(dailySpending, how='left', on='用户名')
    return week

def transWeek(df, num):
    dates = df['日期'].unique()
    dates.sort()
    # 筛选 汇总
    # 新产品 = np + inf
    if num == 6:
        df_W = df.loc[df['日期'] <= dates[num], ['用户名', '类别', '消费']
            ].groupby(['用户名', '类别']).sum()
        df_W = df_W.pivot_table(values=['消费'], index=['用户名'], columns=['类别'])
        df_W.reset_index(inplace=True)
        df_W.columns = ['用户名', '上周搜索', '上周新产品', '上周原生']
        df_W.fillna(0, inplace=True)
        df_W['上周新产品'] = df_W['上周新产品'] + df_W['上周原生']
        df_W.drop(columns=['上周原生'], inplace=True)
    elif num == 13:
        df_W = df.loc[df['日期'] > dates[num//2], ['用户名', '类别', '消费']
            ].groupby(['用户名', '类别']).sum()
        df_W = df_W.pivot_table(values=['消费'], index=['用户名'], columns=['类别'])
        df_W.reset_index(inplace=True)
        df_W.columns = ['用户名', '本周搜索', '本周新产品', '本周原生']
        df_W.fillna(0, inplace=True)
        df_W['本周新产品'] = df_W['本周新产品'] + df_W['本周原生']
        df_W.drop(columns=['本周原生'], inplace=True)
    return df_W
    
def getPath(df):
    ' 获取地址 '
    dates = df['日期'].unique()
    dates.sort()
    path = r'C:\Users\ch\Desktop\Output'
    name1 = 'P4P 消费周环比(' + dates[0] + '_' + dates[-1] + ').xlsx'
    name2 = '近两周代理商消费(' + dates[0] + '_' + dates[-1] + ').xlsx'
    name3 = '近两周广告主消费(' + dates[0] + '_' + dates[-1] + ').xlsx'
    name4 = 'Top 50广告主(' + dates[0] + '_' + dates[-1] + ').xlsx'
    # Result
    path1 = os.path.join(path, name1)
    path2 = os.path.join(path, name2)
    path3 = os.path.join(path, name3)
    path4 = os.path.join(path, name4)
    return path1, path2, path3, path4

def sendMail(subject, dat, message, fils):
    import smtplib
    from email import encoders
    from email.header import Header
    from email.mime.text import MIMEText
    from email.utils import parseaddr, formataddr
    from email.mime.multipart import MIMEMultipart, MIMEBase
    
    def _format_addr(s):
        name, addr = parseaddr(s)
        return formataddr((Header(name, 'utf-8').encode(), addr))
    
    def login():
        PATH = r'c:\users\ch\chinasearch\c.s.conf'
        conf = ConfigParser()
        if os.path.exists(PATH):
            conf.read(PATH)
            smt = conf.get('mail_baidu', 'sender server')
            from_addr = conf.get('mail_baidu', 'email')
            pw = conf.get('mail_baidu', 'password')
            to_addr = conf.get('newIOSystem', 'to_addr')
            return smt, from_addr, pw, to_addr
    
    msg = MIMEMultipart()
    msg['From'] = login()[1]
    msg['To'] = login()[3]
    msg['Subject'] = Header(subject, 'utf-8').encode()

    msg.attach(MIMEText(message, 'plain', 'utf-8'))
    for path in fils:
        print(path)
        
        with open(path, 'rb') as f:
            mime = MIMEBase('text', 'plain', filename=os.path.split(path)[-1])
            # 加上必要的头信息
            mime.add_header('Content-Disposition', 'attachment', filename=os.path.split(path)[-1])  # 1.内容传输编码;2.;3.文件名
            mime.add_header('Content-ID', '<0>')
            mime.add_header('X-Attachment-Id', '0')
            # add 附件
            mime.set_payload(f.read())
            encoders.encode_base64(mime)
            msg.attach(mime)
    
    server = smtplib.SMTP(login()[0], 25)
    server.ehlo()
    server.starttls()
    server.ehlo()
    server.set_debuglevel(1)
    server.login(login()[1], login()[2])
    try:
        server.sendmail(login()[1], login()[3].split(','), msg.as_string())
    except Exception as e:
        print('Failed send: {}'.format(e))
    else:
        print('Success send.')
    server.quit()


def main():
    # 主程序
    print("main")
    col1 = ['区域', '用户名', 'AM', '广告主', '客户', 'channel']
    col2 = ['日期', '用户名', '类别', '消费']
    # 连接数据库 获取数据
    with connectDB().begin() as conn:
        df_basicInfo = pd.DataFrame(basicInfo(conn), columns=col1)
        df_qtd = pd.DataFrame(qtd(conn), columns=['用户名', 'QTD'])
        df_two_weeks = pd.DataFrame(weekSpending(conn, -19, -5), columns=col2)
    # 格式统一
    df_basicInfo['客户'] = df_basicInfo['客户'].str.lower()
    df_basicInfo['客户'] = df_basicInfo['客户'].str.title()
    df_basicInfo['客户'] = df_basicInfo['客户'].str.replace(' ', '')
    df_basicInfo['广告主'] = df_basicInfo['广告主'].str.lower()
    df_basicInfo['广告主'] = df_basicInfo['广告主'].str.title()
    df_basicInfo['广告主'] = df_basicInfo['广告主'].str.replace(' ', '')
    # Output
    ## 近两周消费周环比
    # 1.近两周有消费
    # 2.SZ AM
    #
    p1, p2, p3, p4 = getPath(df_two_weeks)
    t1 = Top15_1(df_basicInfo, df_two_weeks)
    t2 = Top15_2(df_basicInfo, df_two_weeks)
    t2.index.name = '直客'
    t3 = Top15_3(df_basicInfo, df_two_weeks)
    t3.index.name = '代理'
    groupAM = GroupAM(df_basicInfo, df_two_weeks)
    adRingRatio = ADSpendingRingRatio(df_basicInfo, df_two_weeks)
    ds = DSMaster(df_basicInfo, df_two_weeks)
    ag = Agency(df_basicInfo, df_two_weeks)
    week = weeklyRingRatio(df_basicInfo, df_two_weeks)
    #
    ## P4P消费周环比
    #
    with pd.ExcelWriter(p1, engine='xlsxwriter') as path:
        t1.to_excel(path, sheet_name='Top 15', header=False)
        t2.to_excel(path, sheet_name='Top 15',startrow=6)
        t3.to_excel(path, sheet_name='Top 15', startrow=24)
        groupAM.to_excel(path, sheet_name='汇总')
        adRingRatio.to_excel(path, sheet_name='广告主消费环比', 
                             freeze_panes=(1, 0), index=False)
        ds.to_excel(path, sheet_name='直客消费环比', freeze_panes=(1, 0), index=False)
        ag.to_excel(path, sheet_name='代理商消费环比', freeze_panes=(1,0), index=False)
        week.to_excel(path, sheet_name='周环比', freeze_panes=(1, 0), index=False)
        #
        # Top 15
        #
        wb = path.book
        sht = path.sheets['Top 15']
        fmt1 = wb.add_format({'num_format': '#,##0'})
        fmt2 = wb.add_format({'num_format': '0.00%'})
        fmt3 = wb.add_format({'num_format': '#,##0', 'bold': True, 'border': 1,
                              'align': 'center'})
        fmt4 = wb.add_format({'bg_color': '#ffc7ce'})
        cond_fmt = {'type':'cell', 'criteria':'less than', 'value':0, 'format':fmt4}
        sht.set_column('A:A', 12)
        sht.set_column('B:B', 45, fmt1)
        sht.set_column('C:H', 12, fmt1)
        sht.set_column('I:I', 25, fmt1)
        ## 直客
        sht.write('F7', '占比本周消费', fmt3)
        sht.write('G7', '预估季度消费', fmt3)
        sht.write('H7', '浮动原因', fmt3)
        sht.write('I7', '预估季度消费(上周提供)', fmt3)
        sht.conditional_format('E8:E22', cond_fmt)
        for i in range(8, 23):
            sht.write('F' + str(i), '=D' + str(i) + '/B2', fmt2)
        ## 代理
        sht.write('F25', '占比本周消费', fmt3)
        sht.write('G25', '预估季度消费', fmt3)
        sht.write('H25', '浮动原因', fmt3)
        sht.write('I25', '预估季度消费(上周提供)', fmt3)
        sht.conditional_format('E26:E30', cond_fmt)
        for i in range(26, 31):
            sht.write('F' + str(i), '=D' + str(i) + '/B2', fmt2)
        #
        # 汇总
        #
        sht2 = path.sheets['汇总']
        sht2.set_column('B:D', 15, fmt1)
        sht2.write(groupAM.shape[0]+1, 0, '合计', fmt3)
        sht2.write(groupAM.shape[0]+1, 1, '=SUM(B2:B5)')
        sht2.write(groupAM.shape[0]+1, 2, '=SUM(C2:C5)')
        sht2.write(groupAM.shape[0]+1, 3, '=SUM(D2:D5)')
        #
        # 广告主消费环比
        #
        sht3 = path.sheets['广告主消费环比']
        sht3.set_column('A:L', 12, fmt1)
        sht3.set_column('B:B', 25)
        sht3.conditional_format('L2:L'+str(adRingRatio.shape[0]+1), cond_fmt)
        #
        # 直客消费环比
        #
        sht4 = path.sheets['直客消费环比']
        sht4.set_column('A:L', 12, fmt1)
        sht4.set_column('B:B', 25)
        sht4.conditional_format('L2:L'+str(ds.shape[0]+1), cond_fmt)
        #
        # 代理商消费环比
        #
        sht5 = path.sheets['代理商消费环比']
        sht5.set_column('B:J', 12, fmt1)
        sht5.set_column('A:A', 25)
        sht5.conditional_format('D1:D'+str(ag.shape[0]+1), cond_fmt)
        sht5.conditional_format('G1:G'+str(ag.shape[0]+1), cond_fmt)
        sht5.conditional_format('J1:J'+str(ag.shape[0]+1), cond_fmt)
        #
        # 周环比
        #
        sht6 = path.sheets['周环比']
        sht6.set_column('A:W', 12, fmt1)
        sht6.set_column('D:E', 25)
        sht6.conditional_format('I1:I'+str(week.shape[0]+1), cond_fmt)
        sht6.conditional_format('L1:L'+str(week.shape[0]+1), cond_fmt)
    #
    ## 近两周 广告主消费
    #
    ad = transform('ad', df_basicInfo, df_qtd, df_two_weeks)
    ad.index.name = '序号'
    with pd.ExcelWriter(p3, engine='xlsxwriter') as path1:
        ad.to_excel(path1, sheet_name='近两周广告主消费', freeze_panes=(1,0))
        # 格式调整
        wb = path1.book
        sht = path1.sheets['近两周广告主消费']
        fmt1 = wb.add_format({'num_format': '#,##0'})
        fmt2 = wb.add_format({'num_format': '0%'})
        fmt3 = wb.add_format({'num_format': '#,##0;[Red](#,##0);'})
        dic = {'type': 'icon_set',
               'icon_style': '3_arrows',
               'icons': [{'criteria': '>', 'type': 'number', 'value': 0},
                         {'criteris': '<', 'type': 'number', 'value':0}]}
        sht.set_column('B:C', 25)
        sht.set_column('D:L', 11, fmt1)
        sht.set_column('N:N', 19, fmt2)
        sht.set_column('M:M', 11, fmt3)
        sht.conditional_format('M2:M'+str(ad.shape[0]+1), dic)
    #
    ## Top 50
    #
    ad50 = ad[:50]
    with pd.ExcelWriter(p4, engine='xlsxwriter') as path3:
        ad50.to_excel(path3, sheet_name='Top50', freeze_panes=(1,0))
        # 格式调整
        wb = path3.book
        fmt1 = wb.add_format({'num_format': '#,##0'})
        fmt2 = wb.add_format({'num_format': '0%'})
        fmt3 = wb.add_format({'num_format': '#,##0;[Red](#,##0);'})
        dic = {'type': 'icon_set',
               'icon_style': '3_arrows',
               'icons': [{'criteria': '>', 'type': 'number', 'value': 0},
                         {'criteris': '<', 'type': 'number', 'value':0}]}
        sht = path3.sheets['Top50']
        sht.set_column('B:C', 25)
        sht.set_column('D:L', 11, fmt1)
        sht.set_column('N:N', 19, fmt2)
        sht.set_column('M:M', 11, fmt3)
        sht.conditional_format('M2:M'+str(ad50.shape[0]+1), dic)
    #
    ## 近两周 代理消费
    #
    ag = transform('ag', df_basicInfo, df_qtd, df_two_weeks)
    ag.index.name = '序号'
    with pd.ExcelWriter(p2, engine='xlsxwriter') as path2:
        ag.to_excel(path2, sheet_name='近两周代理消费', freeze_panes=(1,0))
        # 格式调整
        wb = path2.book
        sht = path2.sheets['近两周代理消费']
        fmt1 = wb.add_format({'num_format': '#,##0'})
        fmt2 = wb.add_format({'num_format': '0%'})
        fmt3 = wb.add_format({'num_format': '#,##0;[Red](#,##0);'})
        dic = {'type': 'icon_set', 
               'icon_style': '3_arrows',
               'icons': [{'criteria': '>', 'type': 'number', 'value': 0},
                         {'criteria': '<', 'type': 'number', 'value': 0}]}
        sht.set_column('B:B', 35)
        sht.set_column('C:K', 11, fmt1)
        sht.set_column('M:M', 19, fmt2)
        sht.set_column('L:L', 15, fmt3)
        sht.conditional_format('L2:L'+str(ag.shape[0]+1), dic)
    #
    ## 发送
    #
    dat = os.path.split(p1)[-1][-9:-5]
    subject = 'P4P消费周环比(' + dat + ')'
    mes = ''' ... '''
    fils = [p1]
    sendMail(subject, dat, mes, fils)
    #
    ## 
    subject = '近两周广告主 || 代理商消费 & TOP 50广告主(' + dat + ')'
    mes = '''... '''
    fils = [p2, p3, p4]
    sendMail(subject, dat, mes, fils)


if __name__ == '__main__':
    try:
        st = now()
        main()
    except Exception as e:
        print('Error: {}'.format(e))
    finally:
        print('Run time: {:3f} min'.format((now() - st)/60))