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, "数据详情见附件")