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"))
Esempio n. 2
0
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 "执行其他任务"
Esempio n. 13
0
    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))
Esempio n. 15
0
    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, "数据详情见附件")
Esempio n. 16
0
    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, "数据详情见附件")