def start(self):
foodtime = {'1':'早餐','2':'中餐','3':'晚餐','10':'夜宵'}
#写文件
xlswriter = XLSWriter(self.file_path + "recipemobilephotodailysummary_" + self.curDate + ".xls")
xlswriter.writerow(["日期","类别","数量"], sheet_name=u'数据详情')
sql = """\
select to_date(p.createtime) createtime, p.topicid,count(p.id) from hd_haodou_photo_%s.photo p
where to_date(p.createtime) = '%s'
and p.\`From\` in (1,2,3)
and p.topicid in (1,2,3,10)
group by to_date(p.createtime),p.topicid
order by to_date(createtime);
"""
cursor = self.hive.execute(sql%(self.shortDate,self.curDate))
for cols in cursor:
row = re.split(r"\s+",cols)
_time = row[0]
_type = foodtime[row[1]]
_count = row[2]
xlswriter.writerow([_time,_type,_count], sheet_name=u'数据详情')
xlswriter.save()
ms = MailSender(self.file_path + "recipemobilephotodailysummary_" + self.curDate + ".xls", ("%s手机客户端发布早餐、午餐、晚餐、夜宵四个标签的作品数"%self.curDate) + ".xls")
#ms.sendMail("*****@*****.**", "%s手机客户端发布四个标签的作品数"%self.curDate, "数据产生时间%s"%datetime.now().strftime("%Y-%m-%d"))
ms.sendMail("*****@*****.**", "%s手机客户端发布四个标签的作品数"%self.curDate, "数据产生时间%s"%datetime.now().strftime("%Y-%m-%d"))
from Configuration import Configuration
from XLSLoader import XLSLoader
from XLSWriter import XLSWriter
inputFilename = "Input1.xls"
outputFilename = "Output-2.xls"
print("\nLOADING")
loader = XLSLoader(inputFilename)
scenarios = []
for scenarioName in loader.yamlOutput['Scenarios']:
print("Loading scenario " + scenarioName + " from " + inputFilename +
"...")
scenarios.append(
Scenario(scenarioName, loader.yamlOutput['Scenarios'][scenarioName]))
print("OK")
print("\nRUNNING")
for scenario in scenarios:
print("Running scenario " + scenario.name + ":")
for year in Configuration.years:
print("\t" + str(year), end=" ")
for sector in scenario.sectorsP:
sector.step()
print("\n")
print("\nWRITER")
print("Writing to file: " + outputFilename)
writer = XLSWriter(scenarios)
writer.dump(outputFilename)
def start(self):
filename = self.file_path + "channelAnalysisOfBrushActivationBehavior_three_day_one_run.txt"
if os.path.exists(filename) == True:
pstuff = pickle.dumps(self._datetime)
self.saveToFile(filename, pstuff)
files = open(filename)
value = ""
for chunk in files:
value += chunk
prevDatetime = pickle.loads(value.strip())
if (self._datetime - prevDatetime).days < 4:
print "当前时间", prevDatetime, "未达4天退出"
sys.exit(2)
else:
print "当前时间", prevDatetime, "已达4天，分析开始执行"
pstuff = pickle.dumps(self._datetime)
self.saveToFile(filename, pstuff)
else:
print "当前时间", self._datetime.strftime("%Y-%m-%d"), "已达4天，分析开始执行"
pstuff = pickle.dumps(self._datetime)
self.saveToFile(filename, pstuff)
mindate = self._datetime - timedelta(days=(self.delay + 2))
mindate = mindate.strftime("%Y-%m-%d")
maxdate = self._datetime - timedelta(days=(self.delay))
maxdate = maxdate.strftime("%Y-%m-%d")
sql = """\
select first_channel,device_id from bing.dw_app_device_ds
where app_id=2 and to_date(first_day) between '""" + mindate + """' and '""" + maxdate + """' and first_version like '""" + self.check_version + """%';
"""
self.originalChannelMap = {}
p = re.compile('^(\w*)_v(\w*)$')
#前取3天的菜谱安卓新增用户
print ctime(), "前取3天的菜谱安卓新增用户"
#cursor = self.shark.execute(sql%(mindate,maxdate))
cursor = self.hive.execute(sql)
for cols in cursor:
row = re.split(r"\s+", cols)
channel = str(row[0])
#校验渠道号格式
m = p.findall(channel)
if not m:
continue
deviceid = str(row[1])
if channel not in self.originalChannelMap:
self.originalChannelMap[channel] = {}
self.originalChannelMap[channel][deviceid] = 1
#写文件
xlswriter = XLSWriter(self.file_path +
"channelAnalysisOfBrushActivationBehavior_" +
self.curDate + ".xls")
problem = self.getRetentionRate(xlswriter)
problem += "<hr>\n"
problem += "<br>\n"
problem += self.getSurvivalProbability(xlswriter)
problem += "<hr>\n"
problem += "<br>\n"
problem += self.getUserRegister(xlswriter)
self.getMobileType(xlswriter)
"""
problem = self.getMobileType(xlswriter)
"""
xlswriter.save()
os.popen('zip -j -r ' + self.file_path +
'channelAnalysisOfBrushActivationBehavior_' + self.curDate +
'.zip ' + self.file_path +
'channelAnalysisOfBrushActivationBehavior_' + self.curDate +
'.xls')
html = """\
<html>
<head></head>
<body>
%s
</body>
</html>
"""
#发邮件
print ctime(), "发邮件"
ms = MailSender(
self.file_path + "channelAnalysisOfBrushActivationBehavior_" +
self.curDate + ".zip", "安卓渠道刷量抽查_" + self.curDate + ".zip")
ms.sendMail("*****@*****.**", "安卓渠道%s数据质量分析报告" % self.curDate,
html % problem)
ms.sendMail("*****@*****.**", "安卓渠道%s数据质量分析报告" % self.curDate,
html % problem)
os.popen('rm -f ' + self.file_path +
'channelAnalysisOfBrushActivationBehavior_' + self.curDate +
'.xls')
def start(self):
filename = self.file_path+"channelAnalysisOfBrushActivationBehavior_three_day_one_run.txt"
if os.path.exists(filename) == True:
pstuff=pickle.dumps(self._datetime)
self.saveToFile(filename, pstuff)
files=open(filename)
value = ""
for chunk in files:
value += chunk
prevDatetime = pickle.loads(value.strip())
if (self._datetime - prevDatetime).days < 4:
print "当前时间",prevDatetime,"未达4天退出"
sys.exit(2)
else:
print "当前时间",prevDatetime,"已达4天，分析开始执行"
pstuff=pickle.dumps(self._datetime)
self.saveToFile(filename, pstuff)
else:
print "当前时间",self._datetime.strftime("%Y-%m-%d"),"已达4天，分析开始执行"
pstuff=pickle.dumps(self._datetime)
self.saveToFile(filename, pstuff)
mindate = self._datetime - timedelta(days = (self.delay+2))
mindate = mindate.strftime("%Y-%m-%d")
maxdate = self._datetime - timedelta(days = (self.delay))
maxdate = maxdate.strftime("%Y-%m-%d")
sql = """\
select first_channel,device_id from bing.dw_app_device_ds
where app_id=2 and to_date(first_day) between '""" + mindate + """' and '""" + maxdate + """' and first_version like '""" + self.check_version + """%';
"""
self.originalChannelMap = {}
p = re.compile('^(\w*)_v(\w*)$')
#前取3天的菜谱安卓新增用户
print ctime(),"前取3天的菜谱安卓新增用户"
#cursor = self.shark.execute(sql%(mindate,maxdate))
cursor = self.hive.execute(sql)
for cols in cursor:
row = re.split(r"\s+",cols)
channel = str(row[0])
#校验渠道号格式
m = p.findall(channel)
if not m:
continue
deviceid = str(row[1])
if channel not in self.originalChannelMap:
self.originalChannelMap[channel] = {}
self.originalChannelMap[channel][deviceid] = 1
#写文件
xlswriter = XLSWriter(self.file_path+"channelAnalysisOfBrushActivationBehavior_"+self.curDate+".xls")
problem = self.getRetentionRate(xlswriter)
problem += "<hr>\n"
problem += "<br>\n"
problem += self.getSurvivalProbability(xlswriter)
problem += "<hr>\n"
problem += "<br>\n"
problem += self.getUserRegister(xlswriter)
self.getMobileType(xlswriter)
"""
problem = self.getMobileType(xlswriter)
"""
xlswriter.save()
os.popen('zip -j -r '+self.file_path+'channelAnalysisOfBrushActivationBehavior_'+self.curDate+'.zip '+self.file_path+'channelAnalysisOfBrushActivationBehavior_'+self.curDate+'.xls')
html = """\
<html>
<head></head>
<body>
%s
</body>
</html>
"""
#发邮件
print ctime(),"发邮件"
ms = MailSender(self.file_path+"channelAnalysisOfBrushActivationBehavior_"+self.curDate+".zip", "安卓渠道刷量抽查_"+self.curDate+".zip")
ms.sendMail("*****@*****.**", "安卓渠道%s数据质量分析报告"%self.curDate, html%problem)
ms.sendMail("*****@*****.**", "安卓渠道%s数据质量分析报告"%self.curDate, html%problem)
os.popen('rm -f '+self.file_path+'channelAnalysisOfBrushActivationBehavior_'+self.curDate+'.xls')
def start(self):
t = time.mktime(self._datetime.timetuple())
#算本月一号
monthFirstDay = time.strftime('%Y-%m-%d',time.localtime(self.tu.get_month_begin(t)))
#算本周一
weekFirstDay = time.strftime('%Y-%m-%d',time.localtime(self.tu.get_week_begin(t)))
if monthFirstDay == self.curDate or weekFirstDay == self.curDate or self.testModel==True:
print "判断当前时间是否在每月一号"
print "判断当前时间是否在每周一"
if monthFirstDay == self.curDate:
print "当前时间%s是在本月第一天"%self.curDate
if weekFirstDay == self.curDate or self.testModel==True:
#判断如果还未到自然周第一日则不做计算
print "当前时间%s是在本周一"%self.curDate
sql = """\
set hive.support.sql11.reserved.keywords=false;
select username,userid,sum(feeds+favorites),sum(feeds),sum(favorites),sum(mreplay+wrepaly),sum(mreplay),sum(wrepaly),sum(foods+lives),sum(foods),sum(lives),sum(recommend),sum(digest),sum(lovelifedigest),sum(foodsdigest),sum(loveliferecommend),sum(foodsrecommend) from(
select u.username,f.userid,count(f.feedid) feeds, 0 as favorites, 0 as mreplay, 0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.userfeed f
left outer join haodou_passport_{date}.user u on f.userid=u.userid
where to_date(f.createtime) between '{mind}' and '{maxd}'
and f.channel in(3,4,5) and f.type in (110,111,112,113,303,403)
group by u.username,f.userid
union all
select u.username,g.userid,0 as feeds, sum(g.favoritecount) favorites, 0 as mreplay, 0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where to_date(g.createtime) between '{mind}' and '{maxd}'
and g.status=1
group by u.username,g.userid
union all
select u.username,c.userid,0 as feeds, 0 as favorites, count(distinct(c.itemid)) mreplay, 0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_comment_{date}.comment c
left outer join haodou_passport_{date}.user u on c.userid=u.userid
where to_date(c.createtime) between '{mind}' and '{maxd}'
and c.status=1 and c.type=6 and length(html2text(c.content))>=3 and c.platform in(1,2,3)
group by u.username,c.userid
union all
select u.username,c.userid,0 as feeds, 0 as favorites, 0 as mreplay, count(distinct(c.itemid)) wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_comment_{date}.comment c
left outer join haodou_passport_{date}.user u on c.userid=u.userid
where to_date(c.createtime) between '{mind}' and '{maxd}'
and c.status=1 and c.type=6 and length(html2text(c.content))>=10 and c.platform =0
group by u.username,c.userid
union all
select u.username,g.userid, 0 as feeds, 0 as favorites, 0 as mreplay,0 as wrepaly, if(g.cateid in %(do_food_group)s, count(g.topicid), 0) foods, if(g.cateid in %(love_life_group)s, count(g.topicid), 0) lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where to_date(g.createtime) between '{mind}' and '{maxd}' and g.status=1
group by u.username,g.userid,g.cateid
union all
select u.username,g.userid, 0 as feeds, 0 as favorites, 0 as mreplay,0 as wrepaly, 0 as foods, 0 as lives, if(g.recommend=1, count(g.topicid), 0) as recommend, if(g.digest=1, count(g.topicid), 0) as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where g.status=1
and to_date(g.createtime) between '{mind}' and '{maxd}'
group by u.username,g.userid,g.recommend,g.digest
union all
select u.username,g.userid, 0 as feeds, 0 as favorites, 0 as mreplay,0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, if(g.digest=1 and g.cateid in %(love_life_group)s,count(g.topicid),0) lovelifedigest, if(g.digest=1 and g.cateid in %(do_food_group)s,count(g.topicid),0) foodsdigest, if(g.recommend=1 and g.cateid in %(love_life_group)s,count(g.topicid),0) loveliferecommend, if(g.recommend=1 and g.cateid in %(do_food_group)s,count(g.topicid),0) foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where g.status=1 and to_date(g.createtime) between '{mind}' and '{maxd}'
group by u.username,g.userid,g.digest,g.cateid,g.recommend
) temp
group by username,userid;
""" % {"do_food_group": do_food_group, "love_life_group": love_life_group}
#having (foods > 0 or lives > 0 or recommend > 0 or digest > 0 or lovelifedigest > 0 or foodsdigest > 0 or loveliferecommend > 0 or foodsrecommend > 0)
cursor = None
#写文件
xlswriter = XLSWriter(self.file_path+"recipegroupmobilewebdatasummary_"+self.curDate+".xls")
if monthFirstDay == self.curDate:
cursql = sql.replace("{date}", self.nowDate);
cursql = cursql.replace("{mind}", self.startMonthDate);
cursql = cursql.replace("{maxd}", self.endMonthDate);
cursor = self.hive.execute(cursql)
print "%s %s 小组手机端网站端数据月度需求"%(self.startMonthDate,self.endMonthDate)
xlswriter.writerow(["用户名","用户ID","收藏总次数","豆圈","收藏话题次数","总回复话题数","手机端回复话题数","网站端回复话题数","发布话题","做美食","爱生活","推荐","精华","爱生活精华话题数","做美食精华话题数","爱生活被推荐话题数","做美食被推荐话题数","第一次发布时间","最近发布话题时间"], sheet_name=u'月度数据详情')
userids = ""
for cols in cursor:
row = re.split(r"\s+",cols)
userid = str(row[1])
if "" != userid:
userids += userid + ","
if "" != userids:
userids = userids[0:len(userids)-1]
self.findMinMaxTime(userids)
for cols in cursor:
row = re.split(r"\s+",cols)
username = str(row[0])
userid = int(row[1])
fcnt = int(row[2])
feeds = int(row[3])
ftcnt = int(row[4])
trcnt = int(row[5])
mrcnt = int(row[6])
wrcnt = int(row[7])
tcnt = int(row[8])
foods = int(row[9])
lives = int(row[10])
rcd = int(row[11])
digt = int(row[12])
ashjh = int(row[13])
zmsjh = int(row[14])
ashtj = int(row[15])
zmstj = int(row[16])
mindate = ""
maxdate = ""
if userid in self.usersTime:
mindate = self.usersTime[userid]["min"]
maxdate = self.usersTime[userid]["max"]
xlswriter.writerow([username,userid,fcnt,feeds,ftcnt,trcnt,mrcnt,wrcnt,tcnt,foods,lives,rcd,digt,ashjh,zmsjh,ashtj,zmstj,mindate,maxdate], sheet_name=u'月度数据详情')
if weekFirstDay == self.curDate or self.testModel==True:
cursql = sql.replace("{date}", self.nowDate);
cursql = cursql.replace("{mind}", self.startWeekDate);
cursql = cursql.replace("{maxd}", self.endWeekDate);
cursor = self.hive.execute(cursql)
print "%s %s 小组手机端网站端数据周度需,"%(self.startWeekDate,self.endWeekDate)
xlswriter.writerow(["用户名","用户ID","收藏总次数","豆圈","收藏话题次数","总回复话题数","手机端回复话题数","网站端回复话题数","发布话题","做美食","爱生活","推荐","精华","爱生活精华话题数","做美食精华话题数","爱生活被推荐话题数","做美食被推荐话题数","第一次发布时间","最近发布话题时间"], sheet_name=u'周度数据详情')
userids = ""
for cols in cursor:
row = re.split(r"\s+",cols)
userid = str(row[1])
if "" != userid:
userids += userid + ","
if "" != userids:
userids = userids[0:len(userids)-1]
self.findMinMaxTime(userids)
for cols in cursor:
row = re.split(r"\s+",cols)
username = str(row[0])
userid = int(row[1])
fcnt = int(row[2])
feeds = int(row[3])
ftcnt = int(row[4])
trcnt = int(row[5])
mrcnt = int(row[6])
wrcnt = int(row[7])
tcnt = int(row[8])
foods = int(row[9])
lives = int(row[10])
rcd = int(row[11])
digt = int(row[12])
ashjh = int(row[13])
zmsjh = int(row[14])
ashtj = int(row[15])
zmstj = int(row[16])
mindate = ""
maxdate = ""
if userid in self.usersTime:
mindate = self.usersTime[userid]["min"]
maxdate = self.usersTime[userid]["max"]
xlswriter.writerow([username,userid,fcnt,feeds,ftcnt,trcnt,mrcnt,wrcnt,tcnt,foods,lives,rcd,digt,ashjh,zmsjh,ashtj,zmstj,mindate,maxdate], sheet_name=u'周度数据详情')
xlswriter.save()
#os.popen("echo \"email body \" |mail -a " + self.file_path+"recipegroupmobilewebdatasummary_"+self.curDate+".xls" + " -s \"subject\" [email protected]")
if monthFirstDay == self.curDate:
fn = "%s %s 小组手机端网站端数据月度需求"%(self.startMonthDate,self.endMonthDate)
ms = MailSender(self.file_path+"recipegroupmobilewebdatasummary_"+self.curDate+".xls", fn + ".xls")
ms.sendMail("*****@*****.**", "%s %s 小组手机端/网站端相关数据需求"%(self.startMonthDate,self.endMonthDate), "月度数据详情")
ms.sendMail("*****@*****.**", "%s %s 小组手机端/网站端相关数据需求"%(self.startMonthDate,self.endMonthDate), "月度数据详情")
if self.testModel == False:
ms.sendMail("*****@*****.**", "%s %s 小组手机端/网站端相关数据需求"%(self.startMonthDate,self.endMonthDate), "月度数据详情")
if weekFirstDay == self.curDate or self.testModel==True:
fn = "%s %s 小组手机端网站端数据周度需求"%(self.startWeekDate,self.endWeekDate)
ms = MailSender(self.file_path+"recipegroupmobilewebdatasummary_"+self.curDate+".xls", fn + ".xls")
ms.sendMail("*****@*****.**", "%s %s 小组手机端/网站端相关数据需求"%(self.startWeekDate,self.endWeekDate), "周度数据详情")
if self.testModel == False:
ms.sendMail("*****@*****.**", "%s %s 小组手机端/网站端相关数据需求"%(self.startWeekDate,self.endWeekDate), "周度数据详情")
else:
print "当前时间 %s 未到周一"%self.curDate
print "执行其他任务"
def statisticSummary(self):
today = self._datetime.strftime("%Y-%m-%d")
#sql = "SELECT * FROM bi_app_channel_analysis WHERE date(request_time)='%s' and appid=2"
#cursor = self.db.execute(sql%(today))
channels = {}
for row in self.biAppChannelAnalysis:
channel = str(row[3])
if channel not in channels:
channels[channel] = {}
#使用时长
channels[channel]['use_time'] = int(row[5])
#启动次数
channels[channel]['startup'] = int(row[4])
#设备列表
#未来需要按每IP获取设备数用以计算日活
#userip = str(row[2])
channels[channel]['devices'] = []
channels[channel]['devices'].append(str(row[1]))
#活跃用户数
channels[channel]['active_user'] = 1
else:
channels[channel]['use_time'] += int(row[5])
channels[channel]['active_user'] += 1
channels[channel]['startup'] += int(row[4])
channels[channel]['devices'].append(str(row[1]))
channels[channel]['appid'] = str(row[6])
insertSql = "INSERT INTO bi_app_channel_summary (request_time,channel,new_user,increment_time,startup,use_time,active_user,seven_active_user,thirty_active_user,survival_probability,seven_survival_probability,thirty_survival_probability,uninstall,appid) "
insertSql = insertSql + "VALUES ('%s','%s',%s,'%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#获取渠道卸载用户数
printLog("获取渠道卸载用户数")
uninstalls = {}
while True:
uninstalls = self.getPersistentData(
"get:recipeChannelUninstallMap:" + today)
if len(uninstalls) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
#写文件
xlswriter = XLSWriter(self.file_path %
("channel_" + self.curDate + ".xls"))
xlswriter.writerow([
"日期", "当日新增用户", "当日增量最多时间", "包号", "版本", "昨日活跃用户", "7天活跃用户",
"过去30天活跃用户", "次日留存率", "7天留存率", "30天留存率", "昨日卸载数", "启动次数", "单次使用时长"
],
sheet_name=u'渠道信息')
printLog("清空旧的原始数据")
self.db.execute(
"DELETE FROM bi_app_channel_summary WHERE request_time='%s 00:00:00' and appid=2"
% today)
printLog("生成昨日渠道明细表")
yesterdayMap = {}
while True:
yesterdayMap = self.getPersistentData("get:yesterdayMap:" + today)
if len(yesterdayMap) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
weekMap = {}
while True:
weekMap = self.getPersistentData("get:weekMap:" + today)
if len(weekMap) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
monthMap = {}
while True:
monthMap = self.getPersistentData("get:monthMap:" + today)
if len(monthMap) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
survivalYesterdayMap = self.before.getSurvivalYesterdayMap()
survivalWeekMap = self.before.getSurvivalWeekMap()
survivalMonthMap = self.before.getSurvivalMonthMap()
for channel in channels:
spName = self.splitChannelName(channel)
#应用ID
appid = channels[channel]['appid']
startup = channels[channel]['startup']
use_time = channels[channel]['use_time']
#昨日活跃用户数
active_user = channels[channel]['active_user']
#当日新增用户数
new_user = 0
if channel in self.newusers:
new_user = self.newusers[channel]['count']
#当日增量最多时间
increment_time = "00:00:00"
increment_time_count = 0
if channel in self.newusers:
for c_t in self.newusers[channel].keys():
if c_t != "count":
if increment_time_count <= self.newusers[channel][c_t]:
increment_time_count = self.newusers[channel][c_t]
increment_time = c_t
#7天活跃用户率
min_active_user = 0
for day in range(1, 8, 1):
if channel in weekMap:
if day in weekMap[channel]:
min_active_user = min_active_user + len(
weekMap[channel][day])
if min_active_user > 0:
min_active_user = round(
float(active_user) / float(active_user + min_active_user) *
100, 2)
#30天活跃用户率
max_active_user = 0
for day in range(1, 31, 1):
if channel in monthMap:
if day in monthMap[channel]:
max_active_user = max_active_user + len(
monthMap[channel][day])
if max_active_user > 0:
max_active_user = round(
float(active_user) / float(active_user + max_active_user) *
100, 2)
todayMap = dict(
zip(channels[channel]['devices'], range(active_user)))
#昨日渠道留存率
sp = 0.0
if channel in survivalYesterdayMap:
sp = self.survivalProbability(todayMap,
survivalYesterdayMap[channel])
#第7日留存率
minsp = 0.0
if channel in survivalWeekMap:
minsp = self.survivalProbability(todayMap,
survivalWeekMap[channel])
#第30日留存率
maxsp = 0.0
if channel in survivalMonthMap:
maxsp = self.survivalProbability(todayMap,
survivalMonthMap[channel])
#昨日卸载用户数(率？)
#uninstall_rate = 0
uninstall_user = 0
if channel in uninstalls:
uninstall_user = uninstalls[channel]
#if active_user !=0: (率？)
# uninstall_rate = int(uninstall_user/float(active_user)*100)
try:
self.db.execute(
insertSql %
(today, channel, new_user, increment_time, startup,
use_time, active_user, min_active_user, max_active_user,
sp, minsp, maxsp, uninstall_user, 2))
if channel in self.channelExt:
xlswriter.writerow([
self.curDate, new_user, increment_time, spName[0],
spName[1],
str(active_user),
str(min_active_user) + "%",
str(max_active_user) + "%",
str(sp) + "%",
str(minsp) + "%",
str(maxsp) + "%",
str(uninstall_user),
str(startup),
str(int(use_time / startup))
],
sheet_name=u'渠道信息')
print self.curDate, new_user, increment_time, spName[
0], spName[1], str(
active_user), str(min_active_user) + "%", str(
max_active_user) + "%", str(sp) + "%", str(
minsp) + "%", str(maxsp) + "%", str(
uninstall_user), str(startup), str(
int(use_time / startup))
except Exception:
printLog("insert error!", ctime())
errorMsg = "入库统计结果数据 错误<br />"
errorMsg += "错误数据：日期=>" + str(today) + " 渠道=>" + str(
channel) + " 新增用户数=>" + str(new_user) + " 最多增量时间=>" + str(
increment_time
) + " 启动次数=>" + str(startup) + " 使用时长=>" + str(
use_time
) + " 用户活跃数=>" + str(active_user) + " 7天活跃用户=>" + str(
min_active_user
) + " 30天活跃用户=>" + str(max_active_user) + " 次日留存率=>" + str(
sp) + " 7天留存率=>" + str(minsp) + " 30天留存率=>" + str(
maxsp) + " 昨日卸载数=>" + str(
uninstall_user) + " 应用类型=>" + str(appid)
printLog(errorMsg)
traceback.print_exc()
continue
xlswriter.save()
def saveXLS(self):
#写文件
xlswriter = XLSWriter(
self.file_path %
("recipegrouptopicdatasummary_" + self.curDate + ".xls"))
xlswriter.writerow(["日期", "总PV", "总UV", "发帖数", "发帖人数", "回复数", "回复人数"],
sheet_name=u'数据汇总')
for date in self.serialDate:
pv = 0
if "pv" in self.topicData[date]:
pv = self.topicData[date]["pv"]
uv = 0
if "uv" in self.topicData[date]:
uv = self.topicData[date]["uv"]
tc = 0
if "topic" in self.topicData[date]:
tc = self.topicData[date]["topic"]
tr = 0
if "topicusers" in self.topicData[date]:
tr = self.topicData[date]["topicusers"]
cc = 0
if "comment" in self.topicData[date]:
cc = self.topicData[date]["comment"]
cr = 0
if "commentusers" in self.topicData[date]:
cr = self.topicData[date]["commentusers"]
xlswriter.writerow([date, pv, uv, tc, tr, cc, cr],
sheet_name=u'数据汇总')
xlswriter.writerow([
"日期", "网站端PV", "网站端UV", "", "日期", "移动端PV", "移动端UV", "", "日期",
"微信PV", "微信UV", "", "日期", "QQ空间PV", "QQ空间UV", "", "日期", "腾讯微博PV",
"腾讯微博UV", "", "日期", "新浪微博PV", "新浪微博UV"
],
sheet_name=u'数据明细')
for date in self.serialDate:
webpv = 0
webuv = 0
mpv = 0
muv = 0
wpv = 0
wuv = 0
qqpv = 0
qquv = 0
tpv = 0
tuv = 0
xpv = 0
xuv = 0
if date in self.topicData:
if "webpv" in self.topicData[date]:
webpv = self.topicData[date]["webpv"]
if "webuv" in self.topicData[date]:
webuv = self.topicData[date]["webuv"]
if "mpv" in self.topicData[date]:
mpv = self.topicData[date]["mpv"]
if "muv" in self.topicData[date]:
muv = self.topicData[date]["muv"]
if "wpv" in self.topicData[date]:
wpv = self.topicData[date]["wpv"]
if "wuv" in self.topicData[date]:
wuv = self.topicData[date]["wuv"]
if "qqpv" in self.topicData[date]:
qqpv = self.topicData[date]["qqpv"]
if "qquv" in self.topicData[date]:
qquv = self.topicData[date]["qquv"]
if "tpv" in self.topicData[date]:
tpv = self.topicData[date]["tpv"]
if "tuv" in self.topicData[date]:
tuv = self.topicData[date]["tuv"]
if "xpv" in self.topicData[date]:
xpv = self.topicData[date]["xpv"]
if "xuv" in self.topicData[date]:
xuv = self.topicData[date]["xuv"]
xlswriter.writerow([
date, webpv, webuv, "", date, mpv, muv, "", date, wpv, wuv, "",
date, qqpv, qquv, "", date, tpv, tuv, "", date, xpv, xuv
],
sheet_name=u'数据明细')
if "topicsdata" in self.topicData:
xlswriter.writerow([
"用户昵称", "用户ID", "话题标题", "话题链接", "创建日期", "浏览数", "回复数",
"回复人数(一级回复的回复人数)", "收藏数(该话题的收藏数)", "推荐(推荐即表格显示推荐)",
"精华(精华即表格显示精华)", "被赠送豆币数量"
],
sheet_name=u'话题详情')
for data in self.topicData["topicsdata"]:
xlswriter.writerow([
data["username"], data["userid"], data["title"],
data["web"], data["createtime"], data["viewcount"],
data["comment"], data["commentusers"], data["favorite"],
data["recommend"], data["digest"], data["wealth"]
],
sheet_name=u'话题详情')
if "userids" in self.topicData:
xlswriter.writerow(["用户昵称", "用户ID", "天数"], sheet_name=u'连续发布话题天数')
userids = self.topicData["userids"]
for userid in userids.keys():
xlswriter.writerow([
userids[userid]["username"],
int(userid), userids[userid]["top"]
],
sheet_name=u'连续发布话题天数')
index = 1
if "share" in self.topicData:
xlswriter.writerow(["楼层", "用户名", "用户ID", "有无图片", "图片数量"],
sheet_name=u'话题传播分享统计')
shares = self.topicData["share"]
for share in shares:
xlswriter.writerow([
index, share["username"], share["userid"], share["type"],
share["pcount"]
],
sheet_name=u'话题传播分享统计')
index += 1
if "eightFloorUser" in self.topicData:
xlswriter.writerow(
["用户ID", "用户昵称", "话题标题", "话题链接", "创建时间", "第8条回复内容"],
sheet_name=u'逢8有喜回复有奖活动统计')
datas = self.topicData["eightFloorUser"]
for data in datas:
xlswriter.writerow([
data["userid"], data["username"], data["title"],
data["web"], data["createtime"], data["content"]
],
sheet_name=u'逢8有喜回复有奖活动统计')
if "invite" in self.topicData:
xlswriter.writerow([
"邀请人用户名", "邀请人用户ID", "邀请人话题发布数", "被邀请人用户名", "被邀请人用户ID",
"被邀请人话题发布数"
],
sheet_name=u'邀请好友')
datas = self.topicData["invite"]
for data in datas:
xlswriter.writerow([
data["userid"], data["username"], data["cnt"],
data["invite"], data["inviteusername"], data["invitecnt"]
],
sheet_name=u'邀请好友')
if "endOfYear" in self.topicData:
xlswriter.writerow([
"排名", "用户ID", "用户名", "话题标题", "话题链接", "回复数", "手机端回复数", "回复人数",
"浏览数", "收藏数", "小组名", "推荐", "精华"
],
sheet_name=u'优秀热门话题')
datas = self.topicData["endOfYear"][0]
for data in datas:
xlswriter.writerow([
data["rank"] + 1, data["userid"], data["username"],
data["title"], data["web"], data["comment"],
data["mcomment"], data["cusers"], data["viewcount"],
data["favorite"], data["cateid"], data["recommend"],
data["digest"]
],
sheet_name=u'优秀热门话题')
xlswriter.writerow([
"排名", "用户ID", "用户名", "豆窝", "话题总数", "推荐话题总数", "精华话题总数", "爱生活话题",
"做美食话题", "有效评论话题数", "回复总数", "手机端回复数", "有效回复总数", "优质回复总数",
"第一次创建话题时间", "最后一次创建话题时间"
],
sheet_name=u'话题评论详情')
i = 1
datas = self.topicData["endOfYear"][1]
for user in datas:
xlswriter.writerow([
i, user["userid"], user["username"], user["web"],
user["cnt"], user["recommend"], user["digest"],
user["life"], user["food"], user["alive"], user["comment"],
user["mcomment"], user["ac"], user["topc"],
user["mindate"], user["maxdate"]
],
sheet_name=u'话题评论详情')
i += 1
if "robFloor" in self.topicData:
xlswriter.writerow(["楼层", "用户ID", "用户名", "回复内容", "回复时间", "帖子状态"],
sheet_name=u'小组抢楼活动数据')
datas = self.topicData["robFloor"]
for data in datas:
xlswriter.writerow([
data["floor"], data["userid"], data["username"],
data["content"], data["createtime"], data["status"]
],
sheet_name=u'小组抢楼活动数据')
xlswriter.save()
print "发邮件"
ms = MailSender(
self.file_path %
("recipegrouptopicdatasummary_" + self.curDate + ".xls"),
"%s %s-%s活动统计数据汇总.xls" %
(self.keyword, self.startDate, self.endDate))
ms.sendMail(
"*****@*****.**", "%s %s - %s 活动统计数据汇总" %
(self.keyword, self.startDate, self.endDate), "数据详情见附件")
#coding = utf-8
def statisticSummary(self):
today = self._datetime.strftime("%Y-%m-%d")
#sql = "SELECT * FROM bi_app_channel_analysis WHERE date(request_time)='%s' and appid=2"
#cursor = self.db.execute(sql%(today))
channels = {}
for row in self.biAppChannelAnalysis:
channel = str(row[3])
if channel not in channels:
channels[channel] = {}
#使用时长
channels[channel]['use_time'] = int(row[5])
#启动次数
channels[channel]['startup'] = int(row[4])
#设备列表
#未来需要按每IP获取设备数用以计算日活
#userip = str(row[2])
channels[channel]['devices'] = []
channels[channel]['devices'].append(str(row[1]))
#活跃用户数
channels[channel]['active_user'] = 1
else:
channels[channel]['use_time'] += int(row[5])
channels[channel]['active_user'] += 1
channels[channel]['startup'] += int(row[4])
channels[channel]['devices'].append(str(row[1]))
channels[channel]['appid'] = str(row[6])
insertSql = "INSERT INTO bi_app_channel_summary (request_time,channel,new_user,increment_time,startup,use_time,active_user,seven_active_user,thirty_active_user,survival_probability,seven_survival_probability,thirty_survival_probability,uninstall,appid) "
insertSql = insertSql + "VALUES ('%s','%s',%s,'%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#获取渠道卸载用户数
printLog("获取渠道卸载用户数")
uninstalls = {}
while True:
uninstalls = self.getPersistentData("get:recipeChannelUninstallMap:" + today)
if len(uninstalls) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
#写文件
xlswriter = XLSWriter(self.file_path%("channel_"+self.curDate+".xls"))
xlswriter.writerow(["日期","当日新增用户","当日增量最多时间","包号","版本","昨日活跃用户","7天活跃用户","过去30天活跃用户","次日留存率","7天留存率","30天留存率","昨日卸载数","启动次数","单次使用时长"], sheet_name=u'渠道信息')
printLog("清空旧的原始数据")
self.db.execute("DELETE FROM bi_app_channel_summary WHERE request_time='%s 00:00:00' and appid=2"%today)
printLog( "生成昨日渠道明细表")
yesterdayMap = {}
while True:
yesterdayMap = self.getPersistentData("get:yesterdayMap:" + today)
if len(yesterdayMap) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
weekMap = {}
while True:
weekMap = self.getPersistentData("get:weekMap:" + today)
if len(weekMap) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
monthMap = {}
while True:
monthMap = self.getPersistentData("get:monthMap:" + today)
if len(monthMap) == 0:
printLog("数据没有生成，等待中...")
time.sleep(10)
continue
else:
break
survivalYesterdayMap = self.before.getSurvivalYesterdayMap()
survivalWeekMap = self.before.getSurvivalWeekMap()
survivalMonthMap = self.before.getSurvivalMonthMap()
for channel in channels:
spName = self.splitChannelName(channel)
#应用ID
appid = channels[channel]['appid']
startup = channels[channel]['startup']
use_time = channels[channel]['use_time']
#昨日活跃用户数
active_user = channels[channel]['active_user']
#当日新增用户数
new_user = 0
if channel in self.newusers:
new_user = self.newusers[channel]['count']
#当日增量最多时间
increment_time = "00:00:00"
increment_time_count = 0
if channel in self.newusers:
for c_t in self.newusers[channel].keys():
if c_t != "count":
if increment_time_count <= self.newusers[channel][c_t]:
increment_time_count = self.newusers[channel][c_t]
increment_time = c_t
#7天活跃用户率
min_active_user = 0
for day in range(1,8,1):
if channel in weekMap:
if day in weekMap[channel]:
min_active_user = min_active_user + len(weekMap[channel][day])
if min_active_user >0:
min_active_user = round(float(active_user)/float(active_user + min_active_user)*100,2)
#30天活跃用户率
max_active_user = 0
for day in range(1,31,1):
if channel in monthMap:
if day in monthMap[channel]:
max_active_user = max_active_user + len(monthMap[channel][day])
if max_active_user >0:
max_active_user = round(float(active_user)/float(active_user + max_active_user)*100,2)
todayMap = dict(zip(channels[channel]['devices'], range(active_user)))
#昨日渠道留存率
sp = 0.0
if channel in survivalYesterdayMap:
sp = self.survivalProbability(todayMap,survivalYesterdayMap[channel])
#第7日留存率
minsp = 0.0
if channel in survivalWeekMap:
minsp = self.survivalProbability(todayMap,survivalWeekMap[channel])
#第30日留存率
maxsp = 0.0
if channel in survivalMonthMap:
maxsp = self.survivalProbability(todayMap,survivalMonthMap[channel])
#昨日卸载用户数(率？)
#uninstall_rate = 0
uninstall_user = 0
if channel in uninstalls:
uninstall_user = uninstalls[channel]
#if active_user !=0: (率？)
# uninstall_rate = int(uninstall_user/float(active_user)*100)
try:
self.db.execute(insertSql%(today,channel,new_user,increment_time,startup,use_time,active_user,min_active_user,max_active_user,sp,minsp,maxsp,uninstall_user,2))
if channel in self.channelExt:
xlswriter.writerow([self.curDate, new_user, increment_time, spName[0], spName[1], str(active_user),str(min_active_user)+"%",str(max_active_user)+"%",str(sp)+"%",str(minsp)+"%",str(maxsp)+"%",str(uninstall_user),str(startup),str(int(use_time/startup))], sheet_name=u'渠道信息')
print self.curDate, new_user, increment_time, spName[0], spName[1], str(active_user),str(min_active_user)+"%",str(max_active_user)+"%",str(sp)+"%",str(minsp)+"%",str(maxsp)+"%",str(uninstall_user),str(startup),str(int(use_time/startup))
except Exception:
printLog("insert error!",ctime())
errorMsg = "入库统计结果数据 错误<br />"
errorMsg += "错误数据：日期=>" + str(today) + " 渠道=>" + str(channel) + " 新增用户数=>" + str(new_user) + " 最多增量时间=>" + str(increment_time) + " 启动次数=>" + str(startup) + " 使用时长=>" + str(use_time) + " 用户活跃数=>" + str(active_user) + " 7天活跃用户=>" + str(min_active_user) + " 30天活跃用户=>" + str(max_active_user) + " 次日留存率=>" + str(sp) + " 7天留存率=>" + str(minsp) + " 30天留存率=>" + str(maxsp) + " 昨日卸载数=>" + str(uninstall_user) + " 应用类型=>" + str(appid)
printLog(errorMsg)
traceback.print_exc()
continue
xlswriter.save()
def userAnalysis(self):
t = time.mktime(self._datetime.timetuple())
nowMonthDate = time.strftime('%Y-%m-%d',time.localtime(self.tu.get_month_begin(t)))
if nowMonthDate == self.curDate or self.testModel==True:
print "判断当前时间是否在每月一号"
print "当前时间%s是在本月第一天"%self.curDate
sql = """ \
select userid,username,sum(ct) as ct,sum(ct1),sum(ct2) from(
select st.userid,u.username,count(distinct(st.topicid)) as ct,0 as ct1, 0 as ct2
from qnc_haodou_pai_""" + self.nowDate + """.shoptopic st
left outer join hd_haodou_passport_""" + self.nowDate + """.user u on st.userid=u.userid
where st.userid in (674318,2171713,2301227,29485)
and to_date(st.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """' and st.status=1
group by st.userid,u.username
union all
select c.userid,u.username,0 as ct,count(distinct(if(st.type=1,c.itemid,0))) as ct1 ,count(distinct(if(st.type=2,c.itemid,0))) as ct2
from qnc_haodou_pai_""" + self.nowDate + """.shoptopic st
inner join qnc_haodou_comment_""" + self.nowDate + """.comment c on c.itemid=st.topicid
left outer join hd_haodou_passport_""" + self.nowDate + """.user u on c.userid=u.userid
where c.userid in (674318,2171713,2301227,29485)
and c.type=11 and c.replyid=0 and c.status=1 and st.status=1
and to_date(c.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
and to_date(st.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
group by c.userid,u.username
) tmp
group by userid,username
order by ct desc;
"""
sql = """ \
SELECT userid,
CASE
WHEN userid=674318 THEN 'wlpxsj'
WHEN userid=2171713 THEN '夏雪飞扬'
WHEN userid=29485 THEN '小包子'
WHEN userid=2301227 THEN 'floraoy'
END AS username
,SUM(ct) AS ct,SUM(ct1),SUM(ct2) FROM(
SELECT st.userid,COUNT(DISTINCT(st.topicid)) AS ct,0 AS ct1, 0 AS ct2
FROM haodou_pai.ShopTopic st
WHERE st.userid IN (674318,2171713,2301227,29485)
AND DATE(st.createtime) BETWEEN '""" + self.startMonthDate + """' AND '""" + self.endMonthDate + """' AND st.status=1
GROUP BY st.userid
UNION ALL
SELECT c.userid,0 AS ct,COUNT(DISTINCT(IF(st.type=1,c.itemid,0))) AS ct1 ,COUNT(DISTINCT(IF(st.type=2,c.itemid,0))) AS ct2
FROM haodou_pai.ShopTopic st
INNER JOIN haodou_comment.Comment c ON c.itemid=st.topicid
WHERE c.userid IN (674318,2171713,2301227,29485)
AND c.type=11 AND c.replyid=0 AND c.status=1 AND st.status=1
AND DATE(c.createtime) BETWEEN '""" + self.startMonthDate + """' AND '""" + self.endMonthDate + """'
AND DATE(st.createtime) BETWEEN '""" + self.startMonthDate + """' AND '""" + self.endMonthDate + """'
GROUP BY c.userid
) tmp
GROUP BY userid,username
ORDER BY ct DESC
"""
print sql
month = time.strftime('%m',time.localtime(self.tu.get_month_begin(t)))
print "生成去哪吃部分用户%s月份操作数据"%(month)
#写文件
xlswriter = XLSWriter(self.file_path+"qunachispecialdataanalysis_"+self.curDate+".xls")
xlswriter.writerow(["用户ID","用户昵称","发帖数","回复数"], sheet_name=u'数据详情')
xlswriter.writerow(["","","","走街寻店","特色小吃"], sheet_name=u'数据详情')
cursor = self.db.execute(sql)
for row in cursor:
userid = str(row[0])
username = row[1]
topic = int(row[2])
street = int(row[3])
foods = int(row[4])
xlswriter.writerow([userid,username,topic,street,foods], sheet_name=u'数据详情')
xlswriter.save()
_title = "去哪吃部分用户%s月份操作数据"%(month)
ms = MailSender(self.file_path+"qunachispecialdataanalysis_"+self.curDate+".xls", _title + ".xls")
ms.sendMail("*****@*****.**", _title, "数据详情见附件")
if self.testModel==False:
ms.sendMail("*****@*****.**", _title, "数据详情见附件")
def foodMaster(self):
t = time.mktime(self._datetime.timetuple())
nowMonthDate = time.strftime('%Y-%m-%d',time.localtime(self.tu.get_month_begin(t)))
if nowMonthDate == self.curDate or self._datetime.strftime("%d") == self.testtime or self.testModel==True:
if self._datetime.strftime("%d") == self.testtime:
#时间到了21号（实际是22号）自动统计当月1号到20号的数据
print "判断当前日期是否在本月21号"
print "当前时间%s是在本月21号"%self.curDate
if nowMonthDate == self.curDate or self.testModel==True:
print "判断当前时间是否在每月一号"
print "当前时间%s是在本月第一天"%self.curDate
sql = """\
select vip,userid,username,sum(ct) as ct,sum(lever2),sum(lever3),sum(lever4),sum(lever5)
from (
select gfmt(uv.vip) as vip,uv.userid as userid,u.username as username,count(distinct(ps.shareid)) as ct,0 as lever2,0 as lever3,0 as lever4,0 as lever5
from qnc_haodou_center_""" + self.nowDate + """.uservipinfo uv
inner join hd_haodou_passport_""" + self.nowDate + """.user u on uv.userid=u.userid
inner join qnc_haodou_pai_""" + self.nowDate + """.paishare ps on ps.userid=uv.userid
where ps.status=1 and to_date(ps.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
and (gfmt(uv.vip) like '美食地主V%' or gfmt(uv.vip) = '实习美食地主') and ps.status=1
group by gfmt(uv.vip),uv.userid,u.username having vip is not null
union all
select gfmt(uv.vip) as vip,uv.userid as userid,u.username as username,0 as ct, sum(if(st.rate=2,1,0)) as lever2,sum(if(st.rate=3,1,0)) as lever3,sum(if(st.rate=4,1,0)) as lever4,sum(if(st.rate=5,1,0)) as lever5
from qnc_haodou_center_""" + self.nowDate + """.uservipinfo uv
inner join qnc_haodou_pai_""" + self.nowDate + """.shoptopic st on st.userid=uv.userid
inner join hd_haodou_passport_""" + self.nowDate + """.user u on uv.userid=u.userid
where to_date(st.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
and (gfmt(uv.vip) like '美食地主V%' or gfmt(uv.vip) = '实习美食地主') and st.type=1 and st.status=1
group by gfmt(uv.vip),uv.userid,u.username having vip is not null
) tmp
group by vip,userid,username
order by ct desc;
"""
if self._datetime.strftime("%d") == self.testtime:
print "生成去哪吃美食地主%s号到%s号考核数据"%(self.startMonthDate,self.endMonthDate)
month = time.strftime('%m',time.localtime(self.tu.get_month_begin(t)))
if nowMonthDate == self.curDate or self.testModel==True:
print "生成去哪吃美食地主%s月份考核数据"%(month)
#写文件
xlswriter = XLSWriter(self.file_path+"qunachidataanalysissummary_"+self.curDate+".xls")
xlswriter.writerow(["地主级别","用户ID","用户名","美食发布数","走街巡店二星级以上数","走街巡店三星级以上数","走街巡店四星级以上数","走街巡店五星级以上数"], sheet_name=u'数据详情')
cursor = self.hive.execute(sql)
for cols in cursor:
row = re.split(r"\s+",cols)
vip = str(row[0])
userid = str(row[1])
username = str(row[2])
ct = int(row[3])
lever2 = int(row[4])
lever3 = int(row[5])
lever4 = int(row[6])
lever5 = int(row[7])
xlswriter.writerow([vip,userid,username,ct,lever2,lever3,lever4,lever5], sheet_name=u'数据详情')
xlswriter.save()
_title = ""
if self._datetime.strftime("%d") == self.testtime:
_title = "去哪吃美食地主%s号到%s号考核数据"%(self.startMonthDate,self.endMonthDate)
if nowMonthDate == self.curDate or self.testModel==True:
_title = "去哪吃美食地主%s月份考核数据"%(month)
ms = MailSender(self.file_path+"qunachidataanalysissummary_"+self.curDate+".xls", _title + ".xls")
ms.sendMail("*****@*****.**", _title, "数据详情见附件")
if self.testModel==False:
ms.sendMail("*****@*****.**", _title, "数据详情见附件")
def start(self):
t = time.mktime(self._datetime.timetuple())
#算本月一号
monthFirstDay = time.strftime(
'%Y-%m-%d', time.localtime(self.tu.get_month_begin(t)))
#算本周一
weekFirstDay = time.strftime('%Y-%m-%d',
time.localtime(self.tu.get_week_begin(t)))
if monthFirstDay == self.curDate or weekFirstDay == self.curDate or self.testModel == True:
print "判断当前时间是否在每月一号"
print "判断当前时间是否在每周一"
if monthFirstDay == self.curDate:
print "当前时间%s是在本月第一天" % self.curDate
if weekFirstDay == self.curDate or self.testModel == True:
#判断如果还未到自然周第一日则不做计算
print "当前时间%s是在本周一" % self.curDate
sql = """\
set hive.support.sql11.reserved.keywords=false;
select username,userid,sum(feeds+favorites),sum(feeds),sum(favorites),sum(mreplay+wrepaly),sum(mreplay),sum(wrepaly),sum(foods+lives),sum(foods),sum(lives),sum(recommend),sum(digest),sum(lovelifedigest),sum(foodsdigest),sum(loveliferecommend),sum(foodsrecommend) from(
select u.username,f.userid,count(f.feedid) feeds, 0 as favorites, 0 as mreplay, 0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.userfeed f
left outer join haodou_passport_{date}.user u on f.userid=u.userid
where to_date(f.createtime) between '{mind}' and '{maxd}'
and f.channel in(3,4,5) and f.type in (110,111,112,113,303,403)
group by u.username,f.userid
union all
select u.username,g.userid,0 as feeds, sum(g.favoritecount) favorites, 0 as mreplay, 0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where to_date(g.createtime) between '{mind}' and '{maxd}'
and g.status=1
group by u.username,g.userid
union all
select u.username,c.userid,0 as feeds, 0 as favorites, count(distinct(c.itemid)) mreplay, 0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_comment_{date}.comment c
left outer join haodou_passport_{date}.user u on c.userid=u.userid
where to_date(c.createtime) between '{mind}' and '{maxd}'
and c.status=1 and c.type=6 and length(html2text(c.content))>=3 and c.platform in(1,2,3)
group by u.username,c.userid
union all
select u.username,c.userid,0 as feeds, 0 as favorites, 0 as mreplay, count(distinct(c.itemid)) wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_comment_{date}.comment c
left outer join haodou_passport_{date}.user u on c.userid=u.userid
where to_date(c.createtime) between '{mind}' and '{maxd}'
and c.status=1 and c.type=6 and length(html2text(c.content))>=10 and c.platform =0
group by u.username,c.userid
union all
select u.username,g.userid, 0 as feeds, 0 as favorites, 0 as mreplay,0 as wrepaly, if(g.cateid in %(do_food_group)s, count(g.topicid), 0) foods, if(g.cateid in %(love_life_group)s, count(g.topicid), 0) lives, 0 as recommend, 0 as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where to_date(g.createtime) between '{mind}' and '{maxd}' and g.status=1
group by u.username,g.userid,g.cateid
union all
select u.username,g.userid, 0 as feeds, 0 as favorites, 0 as mreplay,0 as wrepaly, 0 as foods, 0 as lives, if(g.recommend=1, count(g.topicid), 0) as recommend, if(g.digest=1, count(g.topicid), 0) as digest, 0 as lovelifedigest, 0 as foodsdigest, 0 as loveliferecommend, 0 as foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where g.status=1
and to_date(g.createtime) between '{mind}' and '{maxd}'
group by u.username,g.userid,g.recommend,g.digest
union all
select u.username,g.userid, 0 as feeds, 0 as favorites, 0 as mreplay,0 as wrepaly, 0 as foods, 0 as lives, 0 as recommend, 0 as digest, if(g.digest=1 and g.cateid in %(love_life_group)s,count(g.topicid),0) lovelifedigest, if(g.digest=1 and g.cateid in %(do_food_group)s,count(g.topicid),0) foodsdigest, if(g.recommend=1 and g.cateid in %(love_life_group)s,count(g.topicid),0) loveliferecommend, if(g.recommend=1 and g.cateid in %(do_food_group)s,count(g.topicid),0) foodsrecommend
from haodou_center_{date}.grouptopic g
left outer join haodou_passport_{date}.user u on g.userid=u.userid
where g.status=1 and to_date(g.createtime) between '{mind}' and '{maxd}'
group by u.username,g.userid,g.digest,g.cateid,g.recommend
) temp
group by username,userid;
""" % {
"do_food_group": do_food_group,
"love_life_group": love_life_group
}
#having (foods > 0 or lives > 0 or recommend > 0 or digest > 0 or lovelifedigest > 0 or foodsdigest > 0 or loveliferecommend > 0 or foodsrecommend > 0)
cursor = None
#写文件
xlswriter = XLSWriter(self.file_path +
"recipegroupmobilewebdatasummary_" +
self.curDate + ".xls")
if monthFirstDay == self.curDate:
cursql = sql.replace("{date}", self.nowDate)
cursql = cursql.replace("{mind}", self.startMonthDate)
cursql = cursql.replace("{maxd}", self.endMonthDate)
cursor = self.hive.execute(cursql)
print "%s %s 小组手机端网站端数据月度需求" % (self.startMonthDate,
self.endMonthDate)
xlswriter.writerow([
"用户名", "用户ID", "收藏总次数", "豆圈", "收藏话题次数", "总回复话题数",
"手机端回复话题数", "网站端回复话题数", "发布话题", "做美食", "爱生活", "推荐", "精华",
"爱生活精华话题数", "做美食精华话题数", "爱生活被推荐话题数", "做美食被推荐话题数",
"第一次发布时间", "最近发布话题时间"
],
sheet_name=u'月度数据详情')
userids = ""
for cols in cursor:
row = re.split(r"\s+", cols)
userid = str(row[1])
if "" != userid:
userids += userid + ","
if "" != userids:
userids = userids[0:len(userids) - 1]
self.findMinMaxTime(userids)
for cols in cursor:
row = re.split(r"\s+", cols)
username = str(row[0])
userid = int(row[1])
fcnt = int(row[2])
feeds = int(row[3])
ftcnt = int(row[4])
trcnt = int(row[5])
mrcnt = int(row[6])
wrcnt = int(row[7])
tcnt = int(row[8])
foods = int(row[9])
lives = int(row[10])
rcd = int(row[11])
digt = int(row[12])
ashjh = int(row[13])
zmsjh = int(row[14])
ashtj = int(row[15])
zmstj = int(row[16])
mindate = ""
maxdate = ""
if userid in self.usersTime:
mindate = self.usersTime[userid]["min"]
maxdate = self.usersTime[userid]["max"]
xlswriter.writerow([
username, userid, fcnt, feeds, ftcnt, trcnt, mrcnt,
wrcnt, tcnt, foods, lives, rcd, digt, ashjh, zmsjh,
ashtj, zmstj, mindate, maxdate
],
sheet_name=u'月度数据详情')
if weekFirstDay == self.curDate or self.testModel == True:
cursql = sql.replace("{date}", self.nowDate)
cursql = cursql.replace("{mind}", self.startWeekDate)
cursql = cursql.replace("{maxd}", self.endWeekDate)
cursor = self.hive.execute(cursql)
print "%s %s 小组手机端网站端数据周度需," % (self.startWeekDate,
self.endWeekDate)
xlswriter.writerow([
"用户名", "用户ID", "收藏总次数", "豆圈", "收藏话题次数", "总回复话题数",
"手机端回复话题数", "网站端回复话题数", "发布话题", "做美食", "爱生活", "推荐", "精华",
"爱生活精华话题数", "做美食精华话题数", "爱生活被推荐话题数", "做美食被推荐话题数",
"第一次发布时间", "最近发布话题时间"
],
sheet_name=u'周度数据详情')
userids = ""
for cols in cursor:
row = re.split(r"\s+", cols)
userid = str(row[1])
if "" != userid:
userids += userid + ","
if "" != userids:
userids = userids[0:len(userids) - 1]
self.findMinMaxTime(userids)
for cols in cursor:
row = re.split(r"\s+", cols)
username = str(row[0])
userid = int(row[1])
fcnt = int(row[2])
feeds = int(row[3])
ftcnt = int(row[4])
trcnt = int(row[5])
mrcnt = int(row[6])
wrcnt = int(row[7])
tcnt = int(row[8])
foods = int(row[9])
lives = int(row[10])
rcd = int(row[11])
digt = int(row[12])
ashjh = int(row[13])
zmsjh = int(row[14])
ashtj = int(row[15])
zmstj = int(row[16])
mindate = ""
maxdate = ""
if userid in self.usersTime:
mindate = self.usersTime[userid]["min"]
maxdate = self.usersTime[userid]["max"]
xlswriter.writerow([
username, userid, fcnt, feeds, ftcnt, trcnt, mrcnt,
wrcnt, tcnt, foods, lives, rcd, digt, ashjh, zmsjh,
ashtj, zmstj, mindate, maxdate
],
sheet_name=u'周度数据详情')
xlswriter.save()
#os.popen("echo \"email body \" |mail -a " + self.file_path+"recipegroupmobilewebdatasummary_"+self.curDate+".xls" + " -s \"subject\" [email protected]")
if monthFirstDay == self.curDate:
fn = "%s %s 小组手机端网站端数据月度需求" % (self.startMonthDate,
self.endMonthDate)
ms = MailSender(
self.file_path + "recipegroupmobilewebdatasummary_" +
self.curDate + ".xls", fn + ".xls")
ms.sendMail(
"*****@*****.**", "%s %s 小组手机端/网站端相关数据需求" %
(self.startMonthDate, self.endMonthDate), "月度数据详情")
ms.sendMail(
"*****@*****.**", "%s %s 小组手机端/网站端相关数据需求" %
(self.startMonthDate, self.endMonthDate), "月度数据详情")
if self.testModel == False:
ms.sendMail(
"*****@*****.**", "%s %s 小组手机端/网站端相关数据需求" %
(self.startMonthDate, self.endMonthDate), "月度数据详情")
if weekFirstDay == self.curDate or self.testModel == True:
fn = "%s %s 小组手机端网站端数据周度需求" % (self.startWeekDate,
self.endWeekDate)
ms = MailSender(
self.file_path + "recipegroupmobilewebdatasummary_" +
self.curDate + ".xls", fn + ".xls")
ms.sendMail(
"*****@*****.**", "%s %s 小组手机端/网站端相关数据需求" %
(self.startWeekDate, self.endWeekDate), "周度数据详情")
if self.testModel == False:
ms.sendMail(
"*****@*****.**", "%s %s 小组手机端/网站端相关数据需求" %
(self.startWeekDate, self.endWeekDate), "周度数据详情")
else:
print "当前时间 %s 未到周一" % self.curDate
print "执行其他任务"
def start(self):
#写文件
xlswriter = XLSWriter(self.file_path +
"recipe2014jiachangcaisummary_" + self.curDate +
".xls")
xlswriter.writerow(["日期", "用户ID", "用户昵称", "奖品", "豆窝地址"],
sheet_name=u'所有中奖用户抽奖信息')
sql = """\
select ua.userid,if(u.username is null or u.username = '******' or u.username = '******','-',u.username),ca.name,concat('http://wo.haodou.com/',ua.userid,'/') from rcp_haodou_act_%s.2014jiachangcaiuseraward ua
inner join rcp_haodou_act_%s.2014jiachangcaiaward ca on ua.awardid=ca.awardid
left outer join hd_haodou_passport_%s.user u on ua.userid=u.userid
where ua.createtime between '%s 00:00:00' and '%s 23:59:59' and ua.userid<>0;
"""
cursor = self.shark.execute(
sql % (self.shortDate, self.shortDate, self.shortDate,
self.curDate, self.curDate))
for cols in cursor:
row = re.split(r"\s+", cols)
userid = str(row[0])
username = str(row[1])
award = str(row[2])
web = str(row[3])
xlswriter.writerow([self.curDate, userid, username, award, web],
sheet_name=u'所有中奖用户抽奖信息')
xlswriter.save()
#统计中奖情况
sql = """\
select name,sum(ct) ct from(
select ca.name,0 ct from rcp_haodou_act_%s.2014jiachangcaiaward ca
union all
select ca.name,count(ua.userid) ct from rcp_haodou_act_%s.2014jiachangcaiaward ca
left outer join rcp_haodou_act_%s.2014jiachangcaiuseraward ua on ua.awardid=ca.awardid
where ua.createtime between '%s 00:00:00' and '%s 23:59:59'
group by ca.name
) tmp
group by name
order by ct;
"""
tds = ""
cursor = self.shark.execute(
sql % (self.shortDate, self.shortDate, self.shortDate,
self.curDate, self.curDate))
for cols in cursor:
row = re.split(r"\s+", cols)
award = str(row[0])
count = str(row[1])
tds += "<tr>\n"
tds += "<td align='center' valign='middle' style='border: 1px solid #C7D8EA;line-height: 16px; font-size: 14px;'>" + award + "</td>\n"
tds += "<td align='center' valign='middle' style='border: 1px solid #C7D8EA;line-height: 16px; font-size: 14px;'>" + count + "</td>\n"
tds += "</tr>"
html = """\
<html>
<head></head>
<body>
<table cellspacing='0' cellpadding='0' width='400px' style='border: 1px solid #C7D8EA;'>
<tr>
<td colspan='2' align='center' style='border: 1px solid #C7D8EA;background: rgb(14,86,173); color: white;line-height: 20px;'><b>国民家常菜%s抽奖基本情况</b></td>
</tr>
<tr>
<td style='border: 1px solid #C7D8EA;'><p style='color:#000099;' align='center'>奖品类型</p></td>
<td style='border: 1px solid #C7D8EA;'><p style='color:#000099;' align='center'>中奖人数</p></td>
</tr>
%s
</table>
</body>
</html>
"""
ms = MailSender(
self.file_path + "recipe2014jiachangcaisummary_" + self.curDate +
".xls", ("国民家常菜%s抽奖信息" % self.curDate) + ".xls")
ms.sendMail("*****@*****.**", "国民家常菜%s抽奖信息" % self.curDate,
html % (self.curDate, tds))
ms.sendMail("*****@*****.**", "国民家常菜%s抽奖信息" % self.curDate,
html % (self.curDate, tds))
ms.sendMail("*****@*****.**", "国民家常菜%s抽奖信息" % self.curDate,
html % (self.curDate, tds))
def start(self):
#写文件
xlswriter = XLSWriter(self.file_path + "recipe2014jiachangcaisummary_" + self.curDate + ".xls")
xlswriter.writerow(["日期","用户ID","用户昵称","奖品","豆窝地址"], sheet_name=u'所有中奖用户抽奖信息')
sql = """\
select ua.userid,if(u.username is null or u.username = '******' or u.username = '******','-',u.username),ca.name,concat('http://wo.haodou.com/',ua.userid,'/') from rcp_haodou_act_%s.2014jiachangcaiuseraward ua
inner join rcp_haodou_act_%s.2014jiachangcaiaward ca on ua.awardid=ca.awardid
left outer join hd_haodou_passport_%s.user u on ua.userid=u.userid
where ua.createtime between '%s 00:00:00' and '%s 23:59:59' and ua.userid<>0;
"""
cursor = self.shark.execute(sql%(self.shortDate,self.shortDate,self.shortDate,self.curDate,self.curDate))
for cols in cursor:
row = re.split(r"\s+",cols)
userid = str(row[0])
username = str(row[1])
award = str(row[2])
web = str(row[3])
xlswriter.writerow([self.curDate,userid,username,award,web], sheet_name=u'所有中奖用户抽奖信息')
xlswriter.save()
#统计中奖情况
sql = """\
select name,sum(ct) ct from(
select ca.name,0 ct from rcp_haodou_act_%s.2014jiachangcaiaward ca
union all
select ca.name,count(ua.userid) ct from rcp_haodou_act_%s.2014jiachangcaiaward ca
left outer join rcp_haodou_act_%s.2014jiachangcaiuseraward ua on ua.awardid=ca.awardid
where ua.createtime between '%s 00:00:00' and '%s 23:59:59'
group by ca.name
) tmp
group by name
order by ct;
"""
tds = ""
cursor = self.shark.execute(sql%(self.shortDate,self.shortDate,self.shortDate,self.curDate,self.curDate))
for cols in cursor:
row = re.split(r"\s+",cols)
award = str(row[0])
count = str(row[1])
tds += "<tr>\n"
tds += "<td align='center' valign='middle' style='border: 1px solid #C7D8EA;line-height: 16px; font-size: 14px;'>" + award + "</td>\n"
tds += "<td align='center' valign='middle' style='border: 1px solid #C7D8EA;line-height: 16px; font-size: 14px;'>" + count + "</td>\n"
tds += "</tr>"
html = """\
<html>
<head></head>
<body>
<table cellspacing='0' cellpadding='0' width='400px' style='border: 1px solid #C7D8EA;'>
<tr>
<td colspan='2' align='center' style='border: 1px solid #C7D8EA;background: rgb(14,86,173); color: white;line-height: 20px;'><b>国民家常菜%s抽奖基本情况</b></td>
</tr>
<tr>
<td style='border: 1px solid #C7D8EA;'><p style='color:#000099;' align='center'>奖品类型</p></td>
<td style='border: 1px solid #C7D8EA;'><p style='color:#000099;' align='center'>中奖人数</p></td>
</tr>
%s
</table>
</body>
</html>
"""
ms = MailSender(self.file_path + "recipe2014jiachangcaisummary_" + self.curDate + ".xls", ("国民家常菜%s抽奖信息"%self.curDate) + ".xls")
ms.sendMail("*****@*****.**", "国民家常菜%s抽奖信息"%self.curDate, html%(self.curDate,tds))
ms.sendMail("*****@*****.**", "国民家常菜%s抽奖信息"%self.curDate, html%(self.curDate,tds))
ms.sendMail("*****@*****.**", "国民家常菜%s抽奖信息"%self.curDate, html%(self.curDate,tds))
def userAnalysis(self):
t = time.mktime(self._datetime.timetuple())
nowMonthDate = time.strftime(
'%Y-%m-%d', time.localtime(self.tu.get_month_begin(t)))
if nowMonthDate == self.curDate or self.testModel == True:
print "判断当前时间是否在每月一号"
print "当前时间%s是在本月第一天" % self.curDate
sql = """ \
select userid,username,sum(ct) as ct,sum(ct1),sum(ct2) from(
select st.userid,u.username,count(distinct(st.topicid)) as ct,0 as ct1, 0 as ct2
from qnc_haodou_pai_""" + self.nowDate + """.shoptopic st
left outer join hd_haodou_passport_""" + self.nowDate + """.user u on st.userid=u.userid
where st.userid in (674318,2171713,2301227,29485)
and to_date(st.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """' and st.status=1
group by st.userid,u.username
union all
select c.userid,u.username,0 as ct,count(distinct(if(st.type=1,c.itemid,0))) as ct1 ,count(distinct(if(st.type=2,c.itemid,0))) as ct2
from qnc_haodou_pai_""" + self.nowDate + """.shoptopic st
inner join qnc_haodou_comment_""" + self.nowDate + """.comment c on c.itemid=st.topicid
left outer join hd_haodou_passport_""" + self.nowDate + """.user u on c.userid=u.userid
where c.userid in (674318,2171713,2301227,29485)
and c.type=11 and c.replyid=0 and c.status=1 and st.status=1
and to_date(c.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
and to_date(st.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
group by c.userid,u.username
) tmp
group by userid,username
order by ct desc;
"""
sql = """ \
SELECT userid,
CASE
WHEN userid=674318 THEN 'wlpxsj'
WHEN userid=2171713 THEN '夏雪飞扬'
WHEN userid=29485 THEN '小包子'
WHEN userid=2301227 THEN 'floraoy'
END AS username
,SUM(ct) AS ct,SUM(ct1),SUM(ct2) FROM(
SELECT st.userid,COUNT(DISTINCT(st.topicid)) AS ct,0 AS ct1, 0 AS ct2
FROM haodou_pai.ShopTopic st
WHERE st.userid IN (674318,2171713,2301227,29485)
AND DATE(st.createtime) BETWEEN '""" + self.startMonthDate + """' AND '""" + self.endMonthDate + """' AND st.status=1
GROUP BY st.userid
UNION ALL
SELECT c.userid,0 AS ct,COUNT(DISTINCT(IF(st.type=1,c.itemid,0))) AS ct1 ,COUNT(DISTINCT(IF(st.type=2,c.itemid,0))) AS ct2
FROM haodou_pai.ShopTopic st
INNER JOIN haodou_comment.Comment c ON c.itemid=st.topicid
WHERE c.userid IN (674318,2171713,2301227,29485)
AND c.type=11 AND c.replyid=0 AND c.status=1 AND st.status=1
AND DATE(c.createtime) BETWEEN '""" + self.startMonthDate + """' AND '""" + self.endMonthDate + """'
AND DATE(st.createtime) BETWEEN '""" + self.startMonthDate + """' AND '""" + self.endMonthDate + """'
GROUP BY c.userid
) tmp
GROUP BY userid,username
ORDER BY ct DESC
"""
print sql
month = time.strftime('%m',
time.localtime(self.tu.get_month_begin(t)))
print "生成去哪吃部分用户%s月份操作数据" % (month)
#写文件
xlswriter = XLSWriter(self.file_path +
"qunachispecialdataanalysis_" +
self.curDate + ".xls")
xlswriter.writerow(["用户ID", "用户昵称", "发帖数", "回复数"],
sheet_name=u'数据详情')
xlswriter.writerow(["", "", "", "走街寻店", "特色小吃"],
sheet_name=u'数据详情')
cursor = self.db.execute(sql)
for row in cursor:
userid = str(row[0])
username = row[1]
topic = int(row[2])
street = int(row[3])
foods = int(row[4])
xlswriter.writerow([userid, username, topic, street, foods],
sheet_name=u'数据详情')
xlswriter.save()
_title = "去哪吃部分用户%s月份操作数据" % (month)
ms = MailSender(
self.file_path + "qunachispecialdataanalysis_" + self.curDate +
".xls", _title + ".xls")
ms.sendMail("*****@*****.**", _title, "数据详情见附件")
if self.testModel == False:
ms.sendMail("*****@*****.**", _title, "数据详情见附件")
def foodMaster(self):
t = time.mktime(self._datetime.timetuple())
nowMonthDate = time.strftime(
'%Y-%m-%d', time.localtime(self.tu.get_month_begin(t)))
if nowMonthDate == self.curDate or self._datetime.strftime(
"%d") == self.testtime or self.testModel == True:
if self._datetime.strftime("%d") == self.testtime:
#时间到了21号（实际是22号）自动统计当月1号到20号的数据
print "判断当前日期是否在本月21号"
print "当前时间%s是在本月21号" % self.curDate
if nowMonthDate == self.curDate or self.testModel == True:
print "判断当前时间是否在每月一号"
print "当前时间%s是在本月第一天" % self.curDate
sql = """\
select vip,userid,username,sum(ct) as ct,sum(lever2),sum(lever3),sum(lever4),sum(lever5)
from (
select gfmt(uv.vip) as vip,uv.userid as userid,u.username as username,count(distinct(ps.shareid)) as ct,0 as lever2,0 as lever3,0 as lever4,0 as lever5
from qnc_haodou_center_""" + self.nowDate + """.uservipinfo uv
inner join hd_haodou_passport_""" + self.nowDate + """.user u on uv.userid=u.userid
inner join qnc_haodou_pai_""" + self.nowDate + """.paishare ps on ps.userid=uv.userid
where ps.status=1 and to_date(ps.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
and (gfmt(uv.vip) like '美食地主V%' or gfmt(uv.vip) = '实习美食地主') and ps.status=1
group by gfmt(uv.vip),uv.userid,u.username having vip is not null
union all
select gfmt(uv.vip) as vip,uv.userid as userid,u.username as username,0 as ct, sum(if(st.rate=2,1,0)) as lever2,sum(if(st.rate=3,1,0)) as lever3,sum(if(st.rate=4,1,0)) as lever4,sum(if(st.rate=5,1,0)) as lever5
from qnc_haodou_center_""" + self.nowDate + """.uservipinfo uv
inner join qnc_haodou_pai_""" + self.nowDate + """.shoptopic st on st.userid=uv.userid
inner join hd_haodou_passport_""" + self.nowDate + """.user u on uv.userid=u.userid
where to_date(st.createtime) between '""" + self.startMonthDate + """' and '""" + self.endMonthDate + """'
and (gfmt(uv.vip) like '美食地主V%' or gfmt(uv.vip) = '实习美食地主') and st.type=1 and st.status=1
group by gfmt(uv.vip),uv.userid,u.username having vip is not null
) tmp
group by vip,userid,username
order by ct desc;
"""
if self._datetime.strftime("%d") == self.testtime:
print "生成去哪吃美食地主%s号到%s号考核数据" % (self.startMonthDate,
self.endMonthDate)
month = time.strftime('%m',
time.localtime(self.tu.get_month_begin(t)))
if nowMonthDate == self.curDate or self.testModel == True:
print "生成去哪吃美食地主%s月份考核数据" % (month)
#写文件
xlswriter = XLSWriter(self.file_path +
"qunachidataanalysissummary_" +
self.curDate + ".xls")
xlswriter.writerow([
"地主级别", "用户ID", "用户名", "美食发布数", "走街巡店二星级以上数", "走街巡店三星级以上数",
"走街巡店四星级以上数", "走街巡店五星级以上数"
],
sheet_name=u'数据详情')
cursor = self.hive.execute(sql)
for cols in cursor:
row = re.split(r"\s+", cols)
vip = str(row[0])
userid = str(row[1])
username = str(row[2])
ct = int(row[3])
lever2 = int(row[4])
lever3 = int(row[5])
lever4 = int(row[6])
lever5 = int(row[7])
xlswriter.writerow([
vip, userid, username, ct, lever2, lever3, lever4, lever5
],
sheet_name=u'数据详情')
xlswriter.save()
_title = ""
if self._datetime.strftime("%d") == self.testtime:
_title = "去哪吃美食地主%s号到%s号考核数据" % (self.startMonthDate,
self.endMonthDate)
if nowMonthDate == self.curDate or self.testModel == True:
_title = "去哪吃美食地主%s月份考核数据" % (month)
ms = MailSender(
self.file_path + "qunachidataanalysissummary_" + self.curDate +
".xls", _title + ".xls")
ms.sendMail("*****@*****.**", _title, "数据详情见附件")
if self.testModel == False:
ms.sendMail("*****@*****.**", _title, "数据详情见附件")