def getAppUserCountTrend(request):
print 'getAppUserCountTrend'
stt = request.GET.get('stt')
edt = request.GET.get('edt')
src = request.GET.get('src')
i_data_dict = v4.get_data_dic_templete(stt, edt)
a_data_dict = v4.get_data_dic_templete(stt, edt)
stt = datetime.datetime.strptime(stt, '%Y-%m-%d')
edt = datetime.datetime.strptime(edt, '%Y-%m-%d')
stt = str(stt.strftime('%Y%m%d'))
edt = str(edt.strftime('%Y%m%d'))
a_response = {}
i_response = {}
if src == None or src == '':
print 0
try:
i_response = bigQuery.active_user('ios', stt, edt)
except Exception, e:
print(e, 'ios err')
try:
a_response = bigQuery.active_user(
'android', ('20161102' if stt < '20161102' else stt), (edt))
except Exception, e:
print(e, 'android err')
def getIOSorAndroidResultDict(stt, edt, type):
data_dict = v4.get_data_dic_templete(stt, edt)
#try:
c_stt = datetime.datetime.strptime(stt, '%Y-%m-%d')
c_edt = datetime.datetime.strptime(edt, '%Y-%m-%d')
response = bigQuery.active_user(type, c_stt.strftime('%Y%m%d'),
c_edt.strftime('%Y%m%d'))
delta = datetime.timedelta(days=1)
i = 0
print response
#while i <=c_edt:
for i in range(len(response)):
#c_stt.strftime("%Y%m%d")
try:
d = datetime.datetime.strptime(response[i]['dt'], '%Y%m%d')
print d
data_dict[datetime.datetime.strftime(
d, '%Y-%m-%d')] = response[i]['uv']
i = i + 1
except Exception, e:
print e
pass
c_stt += delta
print data_dict
def getOac(request):
stt = request.GET.get('stt')
edt = request.GET.get('edt')
src = request.GET.get('src')
channel = request.GET.get('channel')
sql = ' select count(oi.order_id)/count(distinct oi.order_id),\'oac\' type ,date_format(date_sub(oi.create_time,interval 7 HOUR),\'%Y-%m-%d\') d from order_info oi '\
' inner join order_sub_info osi '\
' on oi.order_no = osi.order_no '\
' inner join order_sub_detail osd '\
' on osi.sub_order_no = osd.sub_order_no '\
' inner join user_channel uc '\
' on oi.pin=uc.pin' \
' where date_format(date_sub(oi.create_time,interval 7 HOUR),\'%Y-%m-%d\') BETWEEN %(stt)s and %(edt)s '
sql = sql + 'and oi.status_code = 28 '
if src != None and src != '' and len(src) > 0:
sql = sql + ' and oi.source=\'' + src + '\''
if channel != None and channel != '' and len(channel) > 0:
sql = sql + ' and channel=\'' + channel + '\''
sql = sql + ' group by d'
cursor = connections['logdb_fdl'].cursor()
cursor.execute(sql, {'stt': stt, 'edt': edt})
result = cursor.fetchall()
data_dict = v4.get_data_dic_templete(stt, edt)
for res in result:
data_dict[str(res[2])] = res[0]
print res[0], ',', res[1]
data_list = sorted(data_dict.iteritems(), key=lambda d: d[0])
res_value = []
res_datetime = []
for i in range(len(data_list)):
res_value.append(data_list[i][1])
res_datetime.append(data_list[i][0])
jsondata = {'data': res_value, 'datetime': res_datetime}
jsondata = json.dumps(jsondata,
sort_keys=True,
separators=(',', ': '),
default=defaultencode) # 使用dumps方法格式化数据
return HttpResponse(jsondata)
def getRevenueInPeriod(request):
stt = request.GET.get('stt')
edt = request.GET.get('edt')
uc = request.GET.get('uc')
src = request.GET.get('src')
sql = ' select sum(pay_amount/100),date_format(date_sub(oi.create_time,interval 7 HOUR),\'%Y-%m-%d\') d from order_info oi '\
' left JOIN user_channel uc '\
' on oi.pin=uc.pin '\
' where date_format(date_sub(oi.create_time,interval 7 HOUR),\'%Y-%m-%d\') BETWEEN %(stt)s and %(edt)s '\
' and status_code = 28 '
if src != None and src != '' and len(src) > 0:
sql = sql + 'and lower(oi.source) =lower(\'' + src + '\')'
if uc != None and len(uc) > 0:
sql = sql + ' and lower(uc.channel) =lower(\'' + uc + '\')'
sql = sql + ' group by d'
cursor = connections['logdb_fdl'].cursor()
cursor.execute(sql, {'stt': stt, 'edt': edt})
result = cursor.fetchall()
data_dict = v4.get_data_dic_templete(stt, edt)
for res in result:
data_dict[str(res[1])] = res[0]
data_list = sorted(data_dict.iteritems(), key=lambda d: d[0])
res_value = []
res_datetime = []
for i in range(len(data_list)):
res_value.append(data_list[i][1])
res_datetime.append(data_list[i][0])
print res_value
print res_datetime
jsondata = {'datetime': res_datetime, 'data': res_value}
jsondata = json.dumps(jsondata,
sort_keys=True,
separators=(',', ': '),
default=defaultencode) # 使用dumps方法格式化数据
print jsondata
return HttpResponse(jsondata)
def getUserFunnel(request,
src=None,
funnel=None,
stt=None,
edt=None,
return_type=0):
src = request.GET.get('src')
funnel = request.GET.get('funnel')
stt = request.GET.get('stt')
edt = request.GET.get('edt')
print funnel
print src
data_dict = v4.get_data_dic_templete(stt, edt)
#if (funnel=='total' and stt<'2016-11-02' and src =='android') or (funnel=='total' and stt<'2016-11-02' and src != None and src==''):
# return HttpResponse({'android start time cannnot be smaller than 2016-11-02'})
if funnel == None or (funnel != 'pv' and funnel != 'cart'
and funnel != '_check' and funnel != '_order'):
if funnel == 'total':
if src == None or src == '':
h5_response = v4.getUserOfSomePeriod(params, params.h5_ID, stt,
edt)
pc_response = v4.getUserOfSomePeriod(params, params.pc_ID, stt,
edt)
h5_data_dic = v4.get_data_dic_templete(stt, edt)
pc_data_dic = v4.get_data_dic_templete(stt, edt)
if h5_response != None and h5_response.get('reports')[0].get(
'data').get('rows') != None:
for res in h5_response.get('reports')[0].get('data').get(
'rows'):
r_value = res.get('metrics')[0].get('values')
c = datetime.datetime.strptime(
str(res.get('dimensions')[1]), '%Y%m%d')
r_date = str(c.strftime('%Y-%m-%d')) #实际存在的日期
h5_data_dic[r_date] = int(str(r_value[0]))
if pc_response != None and pc_response.get('reports')[0].get(
'data').get('rows') != None:
for res in pc_response.get('reports')[0].get('data').get(
'rows'):
r_value = res.get('metrics')[0].get('values')
c = datetime.datetime.strptime(
str(res.get('dimensions')[1]), '%Y%m%d')
r_date = str(c.strftime('%Y-%m-%d')) #实际存在的日期
pc_data_dic[r_date] = int(str(r_value[0]))
ios_data_dic = getIOSorAndroidResultDict(stt, edt, 'ios')
android_data_dic = getIOSorAndroidResultDict(
stt, edt, 'android')
date_keys = ios_data_dic.keys()
total_data_dict = v4.get_data_dic_templete(stt, edt)
for d in date_keys:
total_data_dict[d] = int(android_data_dic[d]) + int(
ios_data_dic[d]) + int(h5_data_dic[d]) + int(
pc_data_dic[d])
data_dict = total_data_dict
elif src != None and (src == 'h5' or src == 'pc'):
return getWebUserCountTrend(request)
elif src != None and (src == 'ios'):
ios_data_dic = getIOSorAndroidResultDict(stt, edt, 'ios')
data_dict = ios_data_dic
elif src != None and (src == 'android'):
android_data_dic = v4.get_data_dic_templete(stt, edt)
android_data_dic = getIOSorAndroidResultDict(
('2016-11-02' if stt < '2016-11-02' else stt), (edt),
'android')
data_dict = android_data_dic
else:
return HttpResponse('funnel table name error')
data_list = sorted(data_dict.iteritems(), key=lambda d: d[0])
res_value = []
res_datetime = []
for i in range(len(data_list)):
res_value.append(data_list[i][1])
res_datetime.append(data_list[i][0])
print res_value
print res_datetime
jsondata = {'datetime': res_datetime, 'data': res_value}
jsondata = json.dumps(jsondata,
sort_keys=True,
separators=(',', ': '),
default=defaultencode) # 使用dumps方法格式化数据
return HttpResponse(jsondata)
else:
print 1
sql = 'select count(distinct `UUID`),date_format(date_sub(FROM_UNIXTIME('+funnel+'.log_time/1000),interval 7 HOUR),\'%Y-%m-%d\') d from '+funnel+\
' where date_format(date_sub(FROM_UNIXTIME('+funnel+'.log_time/1000),interval 7 HOUR),\'%Y-%m-%d\') between %(stt)s and %(edt)s '
if src != None and src != '' and len(src) > 0:
sql = sql + 'and lower(src) = lower(\'' + src + '\')'
sql = sql + 'group by d'
cursor = connections['logdb_fdl'].cursor()
cursor.execute(sql, {'stt': stt, 'edt': edt})
result = cursor.fetchall()
cursor.close()
for res in result:
data_dict[str(res[1])] = res[0]
data_list = sorted(data_dict.iteritems(), key=lambda d: d[0])
res_value = []
res_datetime = []
for i in range(len(data_list)):
res_value.append(data_list[i][1])
res_datetime.append(data_list[i][0])
print res_value
print res_datetime
jsondata = {'datetime': res_datetime, 'data': res_value}
jsondata = json.dumps(jsondata,
sort_keys=True,
separators=(',', ': '),
default=defaultencode) # 使用dumps方法格式化数据
if return_type == 0:
return HttpResponse(jsondata)
else:
return jsondata
def getWebUserCountTrend(request):
print 'getWebUserCountTrend'
stt = request.GET.get('stt') #开始时间
edt = request.GET.get('edt') #结束时间
filter = request.GET.get('lp') #落地页路径
src = request.GET.get('src') #all或web或pc
#params=Params.Params()
print id(params)
if filter == None or filter == '' or len(filter) == 0:
filter = None
pc_response = None
h5_response = None
if src == 'h5':
h5_response = v4.getUserOfSomePeriod(params, params.h5_ID, stt, edt,
filter)
elif src == 'pc':
pc_response = v4.getUserOfSomePeriod(params, params.pc_ID, stt, edt,
filter)
elif src == '':
h5_response = v4.getUserOfSomePeriod(params, params.h5_ID, stt, edt,
filter)
pc_response = v4.getUserOfSomePeriod(params, params.pc_ID, stt, edt,
filter)
else:
return HttpResponse('param err')
#print h5_response
#print pc_response
h5_data_dic = v4.get_data_dic_templete(stt, edt)
pc_data_dic = v4.get_data_dic_templete(stt, edt)
if h5_response != None and h5_response.get('reports')[0].get('data').get(
'rows') != None:
for res in h5_response.get('reports')[0].get('data').get('rows'):
r_value = res.get('metrics')[0].get('values')
c = datetime.datetime.strptime(str(res.get('dimensions')[1]),
'%Y%m%d')
r_date = str(c.strftime('%Y-%m-%d')) #实际存在的日期
h5_data_dic[r_date] = int(str(r_value[0]))
if pc_response != None and pc_response.get('reports')[0].get('data').get(
'rows') != None:
for res in pc_response.get('reports')[0].get('data').get('rows'):
r_value = res.get('metrics')[0].get('values')
c = datetime.datetime.strptime(str(res.get('dimensions')[1]),
'%Y%m%d')
r_date = str(c.strftime('%Y-%m-%d')) #实际存在的日期
pc_data_dic[r_date] = int(str(r_value[0]))
h5_data_list = sorted(h5_data_dic.iteritems(), key=lambda d: d[0])
pc_data_list = sorted(pc_data_dic.iteritems(), key=lambda d: d[0])
#print h5_data_list
#print pc_data_list
res_value = []
res_datetime = []
for i in range(len(h5_data_list)):
res_value.append(h5_data_list[i][1] + pc_data_list[i][1])
res_datetime.append(h5_data_list[i][0])
#print res_value
#print res_datetime
jsondata = {'datetime': res_datetime, 'data': res_value}
jsondata = json.dumps(jsondata,
sort_keys=True,
separators=(',', ': '),
default=defaultencode) # 使用dumps方法格式化数据
# print jsondata
response = HttpResponse(jsondata)
return response
dt = datetime.datetime.strptime(dt, '%Y%m%d')
dt = dt.strftime('%Y-%m-%d')
i_data_dict[dt] = uv
for i in range(len(a_response)):
dt = a_response[i]['dt']
uv = a_response[i]['uv']
dt = datetime.datetime.strptime(dt, '%Y%m%d')
dt = dt.strftime('%Y-%m-%d')
a_data_dict[dt] = uv
total_data_dict = v4.get_data_dic_templete(request.GET.get('stt'),
request.GET.get('edt'))
date_keys = total_data_dict.keys()
for k in date_keys:
total_data_dict[k] = a_data_dict[k] + i_data_dict[k]
data_list = sorted(total_data_dict.iteritems(), key=lambda d: d[0])
res_value = []
res_datetime = []
for i in range(len(data_list)):
res_value.append(data_list[i][1])
res_datetime.append(data_list[i][0])
print res_value
print res_datetime
jsondata = {'datetime': res_datetime, 'data': res_value}
jsondata = json.dumps(jsondata,
sort_keys=True,
def getDailyUVTrend(src, str_min_time, str_max_time):
data_dict = v4.get_data_dic_templete(str_min_time, str_max_time)
if src == 'h5' or src == 'pc':
uv_response = v4.getDailyUVInPeriod(
params, (params.h5_ID if src == 'h5' else params.pc_ID),
str_min_time, str_max_time)
if uv_response != None and uv_response.get('reports')[0].get(
'data').get('rows') != None:
for res in uv_response.get('reports')[0].get('data').get('rows'):
r_value = res.get('metrics')[0].get('values')
c = datetime.datetime.strptime(str(res.get('dimensions')[0]),
'%Y%m%d')
r_date = str(c.strftime('%Y-%m-%d')) #实际存在的日期
data_dict[r_date] = r_value[0]
elif src == 'ios' or src == 'android':
uv_response = bigQuery.daily_visit_total(
'ios' if src == 'ios' else 'android',
datetime.datetime.strptime(str_min_time,
'%Y-%m-%d').strftime('%Y%m%d'),
datetime.datetime.strptime(str_max_time,
'%Y-%m-%d').strftime('%Y%m%d'))
for i in range(len(uv_response)):
data_dict[datetime.datetime.strptime(
str(uv_response[i]['dt']),
'%Y%m%d').strftime('%Y-%m-%d')] = uv_response[i]['uv']
else:
h5_data_dict = v4.get_data_dic_templete(str_min_time, str_max_time)
#pc_data_dict=v4.get_data_dic_templete(str_min_time,str_max_time)
ios_data_dict = v4.get_data_dic_templete(str_min_time, str_max_time)
android_data_dict = v4.get_data_dic_templete(str_min_time,
str_max_time)
h5_uv_response = v4.getDailyUVInPeriod(
params, (params.h5_ID if src == 'h5' else params.pc_ID),
str_min_time, str_max_time)
if h5_uv_response != None and h5_uv_response.get('reports')[0].get(
'data').get('rows') != None:
for res in h5_uv_response.get('reports')[0].get('data').get(
'rows'):
r_value = res.get('metrics')[0].get('values')
c = datetime.datetime.strptime(str(res.get('dimensions')[0]),
'%Y%m%d')
r_date = str(c.strftime('%Y-%m-%d')) #实际存在的日期
h5_data_dict[r_date] = r_value[0]
# pc_uv_response= v4.getDailyUVInPeriod(params,(params.h5_ID if src == 'h5' else params.pc_ID),str_min_time,str_max_time)
# print 'pc_uv_response:', pc_uv_response
# if pc_uv_response != None and pc_uv_response.get('reports')[0].get('data').get('rows')!= None:
# for res in pc_uv_response.get('reports')[0].get('data').get('rows'):
# r_value = res.get('metrics')[0].get('values')
# c = datetime.datetime.strptime(str(res.get('dimensions')[0]),'%Y%m%d')
# r_date=str(c.strftime('%Y-%m-%d'))#实际存在的日期
# pc_data_dict[r_date]=r_value[0]
ios_uv_response = bigQuery.daily_visit_total(
'ios',
datetime.datetime.strptime(str_min_time,
'%Y-%m-%d').strftime('%Y%m%d'),
datetime.datetime.strptime(str_max_time,
'%Y-%m-%d').strftime('%Y%m%d'))
for i in range(len(ios_uv_response)):
ios_data_dict[datetime.datetime.strptime(
str(ios_uv_response[i]['dt']),
'%Y%m%d').strftime('%Y-%m-%d')] = ios_uv_response[i]['uv']
android_uv_response = bigQuery.daily_visit_total(
'android',
datetime.datetime.strptime(str_min_time,
'%Y-%m-%d').strftime('%Y%m%d'),
datetime.datetime.strptime(str_max_time,
'%Y-%m-%d').strftime('%Y%m%d'))
for i in range(len(android_uv_response)):
android_data_dict[datetime.datetime.strptime(
str(android_uv_response[i]['dt']),
'%Y%m%d').strftime('%Y-%m-%d')] = android_uv_response[i]['uv']
days = h5_data_dict.keys()
# print h5_data_dict,ios_data_dict,android_data_dict
for day in days:
data_dict[day] = int(str(
h5_data_dict[day])) + ios_data_dict[day] + android_data_dict[
day] #+pc_data_dict[day]
#print 'data_dict:',data_dict
return data_dict
def getTopicThroughtRate(request):
#stt = request.GET.get('stt')
#edt = request.GET.get('edt')
src = request.GET.get('src')
if src == None:
src = ''
sql = topic_sql
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
# print result
str_min_time = result[0][3]
str_max_time = result[len(result) - 1][3]
max_time = datetime.datetime.strptime(str_max_time, '%Y-%m-%d')
str_max_time = (max_time + datetime.timedelta(PERIOD)).strftime('%Y-%m-%d')
topics = {}
for res in result:
stt = str(res[3])
cv = v4.get_data_dic_templete(
stt, (datetime.datetime.strptime(stt, '%Y-%m-%d') +
datetime.timedelta(PERIOD)).strftime('%Y-%m-%d'))
uv = v4.get_data_dic_templete(
stt, (datetime.datetime.strptime(stt, '%Y-%m-%d') +
datetime.timedelta(PERIOD)).strftime('%Y-%m-%d'))
rate = v4.get_data_dic_templete(
stt, (datetime.datetime.strptime(stt, '%Y-%m-%d') +
datetime.timedelta(PERIOD)).strftime('%Y-%m-%d'))
topics[str(res[0])] = {
'tci_id': str(res[0]),
'title': str(res[2]),
'stt': str(res[3]),
'cv': cv,
'uv': uv,
'rate': rate
}
if src == 'h5':
h5_response = v4.getTopicClickTrend(
params=params,
view_id=params.h5_ID,
sdate=str_min_time,
edate=str_max_time,
expression="^Mobile_Daily_Topic_/topic/")
# print h5_response
period_uv = getDailyUVTrend(src, str_min_time, str_max_time)
topics = formatTopicDataWeb(period_uv, topics, h5_response)
elif src == 'pc':
pass
# pc_response = v4.getTopicClickTrend(params=params,view_id=params.pc_ID,sdate=str_min_time,edate=str_max_time,expression="^PC_Daily_Topic_/topic/")
# topics = formatTopicDataWeb(period_uv,topics,pc_response)
# period_uv = getDailyUVTrend(src,str_min_time,str_max_time)
elif src == 'ios' or src == 'android':
tmp_list = {}
for res in result:
tmp_list[res[0]] = datetime.datetime.strptime(
res[3], '%Y-%m-%d').strftime('%Y%m%d')
cv_query_json = bigQuery.daily_click_format(src, tmp_list)
period_uv = getDailyUVTrend(src, str_min_time, str_max_time)
topics = formatTopicDataAPP(period_uv, topics, cv_query_json)
else:
h5_response = v4.getTopicClickTrend(
params=params,
view_id=params.h5_ID,
sdate=str_min_time,
edate=str_max_time,
expression="^Mobile_Daily_Topic_/topic/")
h5_period_uv = getDailyUVTrend('h5', str_min_time, str_max_time)
h5_topics = formatTopicDataWeb(h5_period_uv, topics, h5_response)
# pc_response = v4.getTopicClickTrend(params=params,view_id=params.pc_ID,sdate=str_min_time,edate=str_max_time,expression="^PC_Daily_Topic_/topic/")
# pc_topics = formatTopicDataWeb(period_uv,topics,pc_response)
tmp_list = {}
for res in result:
tmp_list[res[0]] = datetime.datetime.strptime(
res[3], '%Y-%m-%d').strftime('%Y%m%d')
cv_query_json = bigQuery.daily_click_format('ios', tmp_list)
ios_period_uv = getDailyUVTrend('ios', str_min_time, str_max_time)
ios_topics = formatTopicDataAPP(ios_period_uv, topics, cv_query_json)
tmp_list = {}
for res in result:
tmp_list[res[0]] = datetime.datetime.strptime(
res[3], '%Y-%m-%d').strftime('%Y%m%d')
cv_query_json = bigQuery.daily_click_format('android', tmp_list)
android_period_uv = getDailyUVTrend('android', str_min_time,
str_max_time)
android_topics = formatTopicDataAPP(android_period_uv, topics,
cv_query_json)
tci_ids = h5_topics.keys()
total_topics = {}
for tci_id in tci_ids:
total_topics[tci_id] = {}
#pc_topics[tci_id]
total_topics[tci_id]['sort_cv'] = {}
total_topics[tci_id]['sort_uv'] = {}
total_topics[tci_id]['sort_rate'] = {}
total_topics[tci_id]['stt'] = ios_topics[tci_id]['stt']
total_topics[tci_id]['title'] = ios_topics[tci_id]['title']
total_topics[tci_id]['tci_id'] = ios_topics[tci_id]['tci_id']
for i in range(PERIOD + 1):
total_topics[tci_id]['sort_cv']['cv_Day'+str(i+1)]=ios_topics[tci_id]['sort_cv']['cv_Day'+str(i+1)]\
+android_topics[tci_id]['sort_cv']['cv_Day'+str(i+1)]\
+h5_topics[tci_id]['sort_cv']['cv_Day'+str(i+1)]
total_topics[tci_id]['sort_uv']['uv_Day'+str(i+1)]=ios_topics[tci_id]['sort_uv']['uv_Day'+str(i+1)]\
+android_topics[tci_id]['sort_uv']['uv_Day'+str(i+1)]\
+h5_topics[tci_id]['sort_uv']['uv_Day'+str(i+1)]
total_topics[tci_id]['sort_rate']['Day'+str(i+1)]=0 if total_topics[tci_id]['sort_uv']['uv_Day'+str(i+1)]==0\
else total_topics[tci_id]['sort_cv']['cv_Day'+str(i+1)]*1.0/total_topics[tci_id]['sort_uv']['uv_Day'+str(i+1)]
topics = total_topics
# print topics
# print topics
#v4.getTopicClickTrend(params=params,view_id=params.g_pc_ID,sdate=str_min_time,edate=str_max_time)
topics = calAvgTopicThroughtRate(topics)
jsondata = topics
jsondata = json.dumps(jsondata,
sort_keys=True,
separators=(',', ': '),
default=defaultencode) # 使用dumps方法格式化数据
return HttpResponse(jsondata)