#!/usr/bin/env python
# -*- coding:utf-8 -*-
import os,xlsxwriter,datetime
import ConfigParser
from send_mail import send_mail
path = os.path.split(os.path.realpath(__file__))[0]
###***获取配置文件中的变量
conf_file = path + "\"+"conf.ini"
config=ConfigParser.ConfigParser()
config.read(conf_file)
class RepotData(object):
def day_file(self,d,project,sheet_list_d,project_list_code):
path = os.path.split(os.path.realpath(__file__))[0]
day_path = path+"\"+project+"\"+project_list_code+"\"
if os.path.exists(day_path) == 0:
os.mkdir(day_path)
re_path = day_path+"\day\"
if os.path.exists(re_path) == 0:
os.mkdir(re_path)
# 本月汇总
now_month = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y-%m')
# 今日汇总
now_day = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y-%m-%d')
dayYMD = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y%m%d')
#now = now_day.strftime('%Y-%m-%d') #格式化输出
# 创建表单实例
project_Zn = config.get(project,'projcet_Zn')
CNNAME = config.get(project_list_code,"CNNAME")
file = u"%s(%s)%s.xlsx"%(project_Zn,CNNAME,dayYMD) # 路径+文件名字
work = xlsxwriter.Workbook(re_path+"/"+file)
# 生成工作表,默认表名Sheet1
worksheet1 = work.add_worksheet(u'汇总')
worksheet2 = work.add_worksheet(u'访问数据')
worksheet3 = work.add_worksheet(u'点播数据')
worksheet4 = work.add_worksheet(u'分时段订购')
#worksheet5 = work.add_worksheet(u'订购失败统计')
# 设置单元格格式
#border：边框，align:对齐方式，bg_color：背景颜色，font_size：字体大小，bold：字体加粗
top = work.add_format({'border':1,'align':'center','bg_color':'cccccc','font_size':12,'bold':True})
top_two = work.add_format({'border':1,'align':'center','bg_color':'green','font_size':10,'bold':True})
bold = work.add_format({'bold':True,'border':1,'align':'center','font_size':12})
# 开始写入日报模版
for sheet in sheet_list_d:
if sheet == "worksheet1":
# 设置标题格式,合并
worksheet1.merge_range('A1:P1',u'基础数据',top)
worksheet1.merge_range('Q1:T1',u'分析数据',top)
# 设置单元格长度
worksheet1.set_column("A:S",13)
worksheet1.set_column("H:J",18)
worksheet1.set_column("M:O",18)
TopTwo={
"A2":u"日期",
"B2":u"地市",
"C2":u"订购成功数",
"D2":u"取消数",
"E2":u"订购失败数",
"F2":u"用户访问量",
"G2":u"访问用户数",
"H2":u"新用户访问用户数",
"I2":u"非包月访问用户数",
"J2":u"包月访问用户数",
"K2":u"播放量",
"L2":u"点播用户数",
"M2":u"新用户点播用户数",
"N2":u"非包月点播用户数",
"O2":u"包月点播用户数",
"P2":u"播放时长",
"Q2":u"留存用户数",
"R2":u"包月净增",
"S2":u"订购成功率",
"T2":u"订购转化率"
}
for i in TopTwo:
worksheet1.write(i,TopTwo[i],top)
# Module Successfly
# 基础数据
AS=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T']
count = 3
data = d[sheet]
lcyhs=0
for vlue_list in data[0]:
vlue_list = list(vlue_list)
#获取订购成功数
djcgs = vlue_list[2]
#获取订购数
djs = vlue_list[0]
# 订购 净增
djjz = djcgs-vlue_list[3]
vlue_list.append(djjz)
# 订购 成功率
if djcgs == 0 and djs == 0:
djcgl = "0.00%"
vlue_list.append(djcgl)
elif djs == 0:
djcgl = "0.00%"
vlue_list.append(djcgl)
else:
djcgl = str(float("%.2f"%(djcgs/djs))*100)+ "%"
vlue_list.append(djcgl)
# 订购 转化率
if djcgs == 0 and vlue_list[6] == 0:
djzhl = "0.00%"
vlue_list.append(djzhl)
elif vlue_list[6] == 0:
djzhl = "0.00%"
vlue_list.append(djzhl)
else:
djzhl = str(float("%.2f"%(djcgs/vlue_list[6]))*100)+ "%"
vlue_list.append(djzhl)
for index,vlue in enumerate(vlue_list[1:]) :
eval(sheet).write("%s%s"%(AS[index+1],count),vlue,bold)
count = count + 1
eval(sheet).write("%s%s"%(AS[0],count-1),now_day,bold)
worksheet1.write("A3",now_month,bold)
worksheet1.write("B3",u"本月汇总",bold)
#worksheet1.write("Q3",lcyhs,bold)
elif sheet == "worksheet2":
# 访问数据
worksheet2.set_column("A:J",18)
TopTwo={
"A1":u"日期",
"B1":u"地市",
"C1":u"入口标识",
"D1":u"访问总量",
"E1":u"访问用户数",
"F1":u"非包月访问用户数",
"G1":u"包月访问用户数",
"H1":u"订购发起次数",
"I1":u"订购成功数",
"J1":u"订购转化率"
}
for i in TopTwo:
worksheet2.write(i,TopTwo[i],top)
data = d[sheet]
AS = ['A', 'B', 'C', 'D', 'E','F','G','H','I','J']
count = 2
PM = 0
for vlue_list in data[0]:
vlue_list = list(vlue_list)
with open(path+"/accid.ini","r") as f:
ff = f.readlines()
for line in ff:
#print(line.split())
ssid = line.split()[0]
ssid_name = line.split()[1]
if vlue_list[1] == ssid:
vlue_list[1] = ssid_name
continue
for index,vlue in enumerate(vlue_list):
eval(sheet).write("%s%s" % (AS[index + 1], count), vlue, bold)
count = count + 1
eval(sheet).write("%s%s" % (AS[0], count - 1), now_day, bold)
elif sheet == "worksheet3":
# 点播数据 取值 前100名
worksheet3.set_column("A:F",13)
worksheet3.set_column("B:B",20)
TopTwo={
"A1":u"日期",
"B1":u"资源名称",
"C1":u"播放量",
"D1":u"点播用户数",
"E1":u"播放时长",
"F1":u"排名",
}
for i in TopTwo:
worksheet3.write(i,TopTwo[i],top)
# 写入数据
data = d[sheet]
AS = ['A', 'B', 'C', 'D', 'E', 'F']
count = 2
PM = 0
for vlue_list in data[0]:
PM = PM + 1
vlue_list = list(vlue_list)
vlue_list.append(PM)
for index,vlue in enumerate(vlue_list) :
eval(sheet).write("%s%s"%(AS[index+1],count),vlue,bold)
count = count + 1
eval(sheet).write("%s%s"%(AS[0],count-1),now_day,bold)
elif sheet == "worksheet4":
worksheet4.set_column("A:G",16)
TopTwo={
"A1":u"日期",
"B1":u"小时",
"C1":u"订购发起次数",
"D1":u"订购成功数",
"E1":u"访问数",
"F1":u"非包月用户数",
"G1":u"订购转化率",
}
for i in TopTwo:
worksheet4.write(i,TopTwo[i],top)
data = d[sheet]
AS = ['A', 'B', 'C', 'D', 'E', 'F','G']
count = 2
for vlue_list in data[0]:
vlue_list = list(vlue_list)
# if vlue_list[1] == 0 and vlue_list[3] == 0:
# djzhl = 0
# vlue_list.append(djzhl)
# elif vlue_list[3] == 0:
# djzhl = "0"
# vlue_list.append(djzhl)
# else:
# djzhl = str(float("%.2f" % (vlue_list[1] / vlue_list[3])) * 100) + "%"
# vlue_list.append(djzhl)
for index, vlue in enumerate(vlue_list):
eval(sheet).write("%s%s" % (AS[index + 1], count), vlue, bold)
count = count + 1
eval(sheet).write("%s%s" % (AS[0], count - 1), now_day, bold)
work.close()
return re_path+file,file