def dailyesgraph(request, apikey):
#일간 에러 발생비율을 보여준다.
default = {"max": {"key": 5, "value": 0}, "tags": []}
retention = TimeRange.weekly #7일치 데이터를 보여줌
username = request.user
valid, message, userElement, projectElement = validUserPjt(
username, apikey)
if not valid:
return HttpResponse(json.dumps(default), 'application/json')
#일간 데이터를 얻어오기위한 쿼리 작성
#sql = "select count(*) as errorcount ,appversion, DATE_FORMAT(CONVERT_TZ(datetime,'UTC',%(timezone)s),'%%y-%%m-%%d') as errorday "
sql = "select count(*) as errorcount ,appversion, DATE_FORMAT(datetime,'%%y-%%m-%%d') as errorday "
sql = sql + "from instances A, errors B "
sql = sql + "where A.iderror = B.iderror "
sql = sql + "and B.pid = %(pidinput)s "
sql = sql + "and B.status in (0,1) "
sql = sql + "and A.datetime > %(pasttime)s"
sql = sql + "group by errorday"
past, today = getTimeRange(retention, projectElement.timezone)
params = {
'timezone':
projectElement.timezone,
'pidinput':
projectElement.pid,
'pasttime':
'%d-%d-%d %d:%d:%d' %
(past.year, past.month, past.day, past.hour, past.minute, past.second)
}
places = ErrorsbyApp.objects.raw(sql, params)
#얻어온 데이터를 JSON으로 파싱한다.
maxvalue = 0
value = {'key': 0, 'value': 0}
for i in range(retention - 1, -1, -1):
day1 = getTimezoneMidNight(
projectElement.timezone) + datetime.timedelta(days=-i)
#print >> sys.stderr,'day',day1,day1.month,day1.day,day1.hour,day1.minute
#print >> sys.stderr,'utc',toTimezone(day1,'UTC')
instanceCount = 0
for idx, pl in enumerate(places):
if day1.strftime('%y-%m-%d') == pl.errorday:
instanceCount = pl.errorcount
break
value = {'key': 0, 'value': instanceCount}
if maxvalue < instanceCount: #maxvalue!
maxvalue = instanceCount
value['key'] = day1.strftime('%m / %d')
default['tags'].append(value)
default['max']['key'] = len(default['tags'])
default['max']['value'] = maxvalue
return HttpResponse(json.dumps(default), 'application/json')
def dailyesgraph(request, apikey):
#일간 에러 발생비율을 보여준다.
default = {
"max":{"key":5, "value":0},
"tags":[
]
}
retention = TimeRange.weekly #7일치 데이터를 보여줌
username = request.user
valid , message , userElement, projectElement = validUserPjt(username,apikey)
if not valid:
return HttpResponse(json.dumps(default),'application/json')
#일간 데이터를 얻어오기위한 쿼리 작성
#sql = "select count(*) as errorcount ,appversion, DATE_FORMAT(CONVERT_TZ(datetime,'UTC',%(timezone)s),'%%y-%%m-%%d') as errorday "
sql = "select count(*) as errorcount ,appversion, DATE_FORMAT(datetime,'%%y-%%m-%%d') as errorday "
sql = sql + "from instances A, errors B "
sql = sql + "where A.iderror = B.iderror "
sql = sql + "and B.pid = %(pidinput)s "
sql = sql + "and B.status in (0,1) "
sql = sql + "and A.datetime > %(pasttime)s"
sql = sql + "group by errorday"
past, today = getTimeRange(retention,projectElement.timezone)
params = {'timezone':projectElement.timezone,'pidinput':projectElement.pid,'pasttime':'%d-%d-%d %d:%d:%d' % (past.year,past.month,past.day,past.hour,past.minute,past.second)}
places = ErrorsbyApp.objects.raw(sql, params)
#얻어온 데이터를 JSON으로 파싱한다.
maxvalue = 0
value = {'key' : 0 , 'value' : 0}
for i in range(retention-1,-1,-1):
day1 = getTimezoneMidNight(projectElement.timezone) + datetime.timedelta(days = -i)
#print >> sys.stderr,'day',day1,day1.month,day1.day,day1.hour,day1.minute
#print >> sys.stderr,'utc',toTimezone(day1,'UTC')
instanceCount = 0
for idx, pl in enumerate(places):
if day1.strftime('%y-%m-%d') == pl.errorday:
instanceCount = pl.errorcount
break;
value = {'key' : 0 , 'value' : instanceCount}
if maxvalue < instanceCount: #maxvalue!
maxvalue = instanceCount
value['key'] = day1.strftime('%m / %d')
default['tags'].append(value)
default['max']['key'] = len(default['tags'])
default['max']['value'] = maxvalue
return HttpResponse(json.dumps(default),'application/json')
def chartdata_sbav(request,apikey):
jsonData = json.loads(request.POST['json'],encoding='utf-8')
retention = int(jsonData['retention'])
username = request.user
valid , message , userElement, projectElement = validUserPjt(username,apikey)
if not valid:
return HttpResponseRedirect('/urqa')
print 'retention', retention
# Common Data
past, today = getTimeRange(retention,projectElement.timezone)
#print 'past',past, 'today',today
errorElements = Errors.objects.filter(pid=projectElement,status__in=[Status.New,Status.Open],lastdate__range=(past,today)).order_by('errorclassname','errorweight')
#instanceElements = Instances.objects.select_related().filter(iderror__in=errorElements,datetime__range=(past,today))
AppruncountElemtns = Appruncount.objects.filter(pid=projectElement,date__range=(past,today))
result = {}
# Chart 0 session by appversion
appversions = AppruncountElemtns.values('appversion').distinct().order_by('appversion')
categories = []
appcount_data = {}
for appversion in appversions:
appcount_data[appversion['appversion']] = []
for i in range(retention-1,-1,-1):
day1 = getTimezoneMidNight('UTC') + datetime.timedelta(days = -i)
categories.append(day1.strftime('%b-%d'))
for appversion in appversions:
#appcount_data[appversion['appversion']].append(Appruncount.objects.filter(pid=projectElement,appversion=appversion['appversion'],date__range=(day1,day2)))
runcounts = Appruncount.objects.filter(pid=projectElement,appversion=appversion['appversion'],date=day1.strftime('20%y-%m-%d'))
result_runcount = 0
for runcount in runcounts:
result_runcount = result_runcount + runcount.runcount
appcount_data[appversion['appversion']].append(result_runcount);
appver_data = []
for appversion in appversions:
appver_data.append(
{
'name': appversion['appversion'],
'data': appcount_data[appversion['appversion']]
}
)
chart_sbav = {'categories':categories,'data':appver_data}
result['chart_sbav'] = chart_sbav
return HttpResponse(json.dumps(result), 'application/json');
def chartdata_sbav(request, apikey):
#App version별 Client의 Session을 보여주는 차트
jsonData = json.loads(request.POST['json'], encoding='utf-8')
retention = int(jsonData['retention'])
username = request.user
valid, message, userElement, projectElement = validUserPjt(
username, apikey)
if not valid:
return HttpResponseRedirect('/urqa')
#print 'retention', retention
if retention == 1:
retention = 24 #retention을 24로 변경 24시를 의미
past, today = getTimeRangeExactHour(retention, projectElement.timezone)
strformat = '%y-%m-%d %H'
dateformat = '%%y-%%m-%%d %%H'
else:
past, today = getTimeRange(retention, projectElement.timezone)
strformat = '%y-%m-%d'
dateformat = '%%y-%%m-%%d'
#########################################
#90% 에 해당하는 appversion 리스트 얻어오는 로직
# 1. 전체 session 수 구하기
# 2. 전체 세션수 대비 90% 에 해당하는 app version 리스트만 가져옴
#########################################
sql2 = 'SELECT appversion ,sum(appruncount) as total FROM appruncount2 where pid = %(pidinput)s and datetime >= %(pasttime)s group by appversion order by total desc'
params2 = {
'pidinput':
projectElement.pid,
'pasttime':
'%d-%d-%d %d:%d:%d' %
(past.year, past.month, past.day, past.hour, past.minute, past.second)
}
totalSession = TotalSession.objects.raw(sql2, params2)
sum = 0
for idx, pl in enumerate(totalSession):
sum = sum + pl.total
ratio = float(sum) / 1.1
ratioappversion = ()
ratioappversion = list(ratioappversion)
sum = 0
recentVersion = '0.0'
othersNumber = 0
for idx, pl in enumerate(totalSession):
if len(ratioappversion) == 0:
ratioappversion.append(str(pl.appversion))
if LooseVersion(recentVersion) < LooseVersion(pl.appversion):
if not pl.appversion == "unknown":
recentVersion = pl.appversion
if sum <= ratio:
ratioappversion.append(str(pl.appversion))
else:
othersNumber = othersNumber + pl.total
sum = sum + pl.total
#check recent version is exist in array
if not recentVersion in ratioappversion:
ratioappversion.append(str(recentVersion))
ratioappversion = tuple(ratioappversion)
if len(ratioappversion) == 1:
ratioappversion = str(ratioappversion)[:len(str(ratioappversion)) -
2] + str(ratioappversion)[-1]
#날짜별 Session수를 얻어오기 위한 Query생성
sql = 'SELECT idappruncount2 as idsessionbyapp, sum(appruncount) as runcount, appversion, DATE_FORMAT(CONVERT_TZ(datetime,"UTC",%(timezone)s),"' + dateformat + '") as sessionday'
sql = sql + ' from urqa.appruncount2'
if len(ratioappversion) == 0:
sql = sql + ' where pid = %(pidinput)s and datetime >= %(pasttime)s and appversion'
else:
sql = sql + ' where pid = %(pidinput)s and datetime >= %(pasttime)s and appversion in ' + str(
ratioappversion)
sql = sql + ' Group by appversion, sessionday'
params = {
'timezone':
projectElement.timezone,
'pidinput':
projectElement.pid,
'pasttime':
'%d-%d-%d %d:%d:%d' %
(past.year, past.month, past.day, past.hour, past.minute, past.second)
}
places = SessionbyApp.objects.raw(sql, params)
appversions = []
dates = []
for idx, pl in enumerate(places):
if not pl.appversion in appversions:
appversions.append(pl.appversion)
if not pl.sessionday in dates:
dates.append(pl.sessionday)
result = {}
categories = []
appcount_data = {}
for appversion in appversions:
appcount_data[appversion] = []
new_places = []
for idx, pl in enumerate(places):
new_places.append({
'appversion': pl.appversion,
'sessionday': pl.sessionday,
'runcount': pl.runcount
})
#시간,날짜별로 Session Data를 나눔
for i in range(retention - 1, -1, -1):
if retention == 24: # Statistics 하루치
day1 = getTimezoneHour(
projectElement.timezone) + datetime.timedelta(hours=-i)
if day1.hour == 0:
categories.append(day1.strftime('%b-%d'))
else:
categories.append(day1.strftime('%H'))
else:
day1 = getTimezoneMidNight(
projectElement.timezone) + datetime.timedelta(days=-i)
categories.append(day1.strftime('%b-%d'))
for appversion in appversions:
result_runcount = 0
for idx in range(0, len(new_places)):
if new_places[idx]['appversion'] == appversion and new_places[
idx]['sessionday'] == day1.strftime(strformat):
result_runcount = new_places[idx]['runcount']
new_places.pop(idx)
break
appcount_data[appversion].append(int(result_runcount))
appver_data = []
for appversion in appversions:
appver_data.append({
'name': appversion,
'data': appcount_data[appversion]
})
chart_sbav = {'categories': categories, 'data': appver_data}
result['chart_sbav'] = chart_sbav
return HttpResponse(json.dumps(result), 'application/json')
def chartdata_ebav(request, apikey):
#App version별 Client의 Error 수를 보여주는 차트
jsonData = json.loads(request.POST['json'], encoding='utf-8')
retention = int(jsonData['retention'])
username = request.user
valid, message, userElement, projectElement = validUserPjt(
username, apikey)
if not valid:
return HttpResponseRedirect('/urqa')
# Common Data
result = {}
#appcount_data = {}
categories = []
#appver_data = []
if retention == 1:
retention = 24 #retention을 24로 변경 24시를 의미
past, today = getTimeRangeExactHour(retention, projectElement.timezone)
strformat = '%y-%m-%d %H'
dateformat = '%%y-%%m-%%d %%H'
else:
past, today = getTimeRange(retention, projectElement.timezone)
strformat = '%y-%m-%d'
dateformat = '%%y-%%m-%%d'
#########################################
#90% 에 해당하는 appversion 리스트 얻어오는 로직
# 1. 전체 session 수 구하기
# 2. 전체 세션수 대비 90% 에 해당하는 app version 리스트만 가져옴
#########################################
#sql2 = 'SELECT appversion ,count(*) as total FROM instances A, errors B where A.iderror = B.iderror and pid = %(pidinput)s and B.status in (0,1) and datetime >= %(pasttime)s group by appversion order by total desc'
sql2 = "SELECT appversion ,count(*) as total "
sql2 = sql2 + "FROM instances "
sql2 = sql2 + "where pid = %(pidinput)s and datetime >= %(pasttime)s "
sql2 = sql2 + "group by appversion "
sql2 = sql2 + "order by total desc"
params2 = {
'pidinput':
projectElement.pid,
'pasttime':
'%d-%d-%d %d:%d:%d' %
(past.year, past.month, past.day, past.hour, past.minute, past.second)
}
totalSession = TotalSession.objects.raw(sql2, params2)
#======= 장고로 변환 테스트가 안되어 있음;;
# pasttime= '%d-%d-%d %d:%d:%d' % (past.year,past.month,past.day,past.hour,past.minute,past.second)
#
# totalSession = sorted(Instances.objects.values('appversion').annotate(total=Count('*')).filter(
# pid=projectElement, datetime__gte=pasttime
# ).group_by('appversion'), key=lambda instance: instance['total'], reverse=True)
sum = 0
for idx, pl in enumerate(totalSession):
sum = sum + pl.total
ratio = float(sum) / 1.1
ratioappversion = ()
ratioappversion = list(ratioappversion)
sum = 0
recentVersion = '0.0'
othersNumber = 0
for idx, pl in enumerate(totalSession):
if len(ratioappversion) == 0:
ratioappversion.append(str(pl.appversion))
if LooseVersion(recentVersion) < LooseVersion(pl.appversion):
if not pl.appversion == "unknown":
recentVersion = pl.appversion
if sum <= ratio:
ratioappversion.append(str(pl.appversion))
else:
othersNumber = othersNumber + pl.total
sum = sum + pl.total
#check recent version is exist in array
if not recentVersion in ratioappversion:
ratioappversion.append(str(recentVersion))
ratioappversion = tuple(ratioappversion)
if len(ratioappversion) == 1:
ratioappversion = str(ratioappversion)[:len(str(ratioappversion)) -
2] + str(ratioappversion)[-1]
#Error Count를 얻어올 Query를 생성한다.
sql = "select count(*) as errorcount ,appversion, DATE_FORMAT(CONVERT_TZ(datetime,'UTC',%(timezone)s),'" + dateformat + "') as errorday "
sql = sql + "from instances "
sql = sql + "where pid = %(pidinput)s "
if len(ratioappversion) == 0:
sql = sql + ' and datetime >= %(pasttime)s and appversion '
else:
sql = sql + ' and datetime >= %(pasttime)s and appversion in ' + str(
ratioappversion)
sql = sql + "group by errorday,appversion"
past, today = getTimeRange(retention, projectElement.timezone)
params = {
'timezone':
projectElement.timezone,
'pidinput':
projectElement.pid,
'pasttime':
'%d-%d-%d %d:%d:%d' %
(past.year, past.month, past.day, past.hour, past.minute, past.second)
}
places = ErrorsbyApp.objects.raw(sql, params)
#listing app version
appversions = []
dates = []
for idx, pl in enumerate(places):
if not pl.appversion in appversions:
appversions.append(pl.appversion)
if not pl.errorday in dates:
dates.append(pl.errorday)
result = {}
categories = []
appcount_data = {}
for appversion in appversions:
appcount_data[appversion] = []
new_places = []
for idx, pl in enumerate(places):
new_places.append({
'appversion': pl.appversion,
'errorday': pl.errorday,
'errorcount': pl.errorcount
})
#시간,날짜별로 Error Count를 나눔
for i in range(retention - 1, -1, -1):
if retention == 24:
day1 = getTimezoneHour(
projectElement.timezone) + datetime.timedelta(hours=-i)
if day1.hour == 0:
categories.append(day1.strftime('%b-%d'))
else:
categories.append(day1.strftime('%H'))
else:
day1 = getTimezoneMidNight(
projectElement.timezone) + datetime.timedelta(days=-i)
categories.append(day1.strftime('%b-%d'))
for appversion in appversions:
result_runcount = 0
for idx in range(0, len(new_places)):
if new_places[idx]['appversion'] == appversion and new_places[
idx]['errorday'] == day1.strftime(strformat):
result_runcount = new_places[idx]['errorcount']
new_places.pop(idx)
break
appcount_data[appversion].append(int(result_runcount))
appver_data = []
for appversion in appversions:
appver_data.append({
'name': appversion,
'data': appcount_data[appversion]
})
chart_ebav = {'categories': categories, 'data': appver_data}
result['chart_sbav'] = chart_ebav
chart1 = {'categories': categories, 'data': appver_data}
result['chart1'] = chart1
#print >>sys.stderr, chart1
return HttpResponse(json.dumps(result), 'application/json')
def chartdata_ebav(request,apikey):
jsonData = json.loads(request.POST['json'],encoding='utf-8')
retention = int(jsonData['retention'])
username = request.user
valid , message , userElement, projectElement = validUserPjt(username,apikey)
if not valid:
return HttpResponseRedirect('/urqa')
# Common Data
result = {}
appcount_data = {}
categories = []
appver_data = []
sql = "select count(*) as errorcount ,appversion, DATE_FORMAT(A.datetime, '%%m-%%d') as errorday "
sql = sql + "from instances A, errors B "
sql = sql + "where A.iderror = B.iderror "
sql = sql + "and pid = %(pidinput)s "
sql = sql + "and B.status in (0,1) "
sql = sql + "and A.datetime > (curdate() - interval %(intervalinput)s day) "
sql = sql + "group by DATE_FORMAT(A.datetime, '%%m%%d'),appversion"
params = {'pidinput':projectElement.pid,'intervalinput':retention - 1}
places = ErrorsbyApp.objects.raw(sql, params)
#listing app version
appversions = []
for idx, pl in enumerate(places):
appversions.append(pl.appversion)
appversionList = list(set(appversions))
appversionList.sort()
#loop for retention
dates = []
for idx, pl in enumerate(places):
dates.append(pl.errorday)
dateList = list(set(dates))
dateList.sort()
returnValue = []
for version in appversionList:
dataList = [0] * len(dateList)
for index, date in enumerate(dateList):
for idx, pl in enumerate(places):
if pl.appversion == version and pl.errorday == date:
dataList[index] = pl.errorcount
returnValue.append(
{
'name': version,
'data': dataList
}
)
for i in range(retention-1,-1,-1):
category_time = getTimezoneMidNight(projectElement.timezone) + datetime.timedelta(days = -i)
categories.append(category_time.strftime('%b-%d'))
chart1 = {'categories':categories,'data':returnValue}
result['chart1'] = chart1
print >>sys.stderr, chart1
return HttpResponse(json.dumps(result), 'application/json');
def chartdata(request,apikey):
jsonData = json.loads(request.POST['json'],encoding='utf-8')
retention = int(jsonData['retention'])
username = request.user
valid , message , userElement, projectElement = validUserPjt(username,apikey)
if not valid:
return HttpResponseRedirect('/urqa')
print 'retention', retention
past, today = getTimeRange(retention,projectElement.timezone)
#print 'past',past, 'today',today
errorElements = Errors.objects.filter(pid=projectElement,status__in=[Status.New,Status.Open],lastdate__range=(past,today)).order_by('errorclassname','errorweight')
instanceElements = Instances.objects.select_related().filter(iderror__in=errorElements,datetime__range=(past,today))
result = {}
categories = []
appversions = instanceElements.values('appversion').distinct().order_by('appversion')
appcount_data = {}
for appversion in appversions:
appcount_data[appversion['appversion']] = []
for i in range(retention-1,-1,-1):
category_time = getTimezoneMidNight(projectElement.timezone) + datetime.timedelta(days = -i)
categories.append(category_time.strftime('%b-%d'))
p1, dummy = getTimeRange(i+1, projectElement.timezone)
p2, dummy = getTimeRange(i, projectElement.timezone)
instances = instanceElements.filter(datetime__range=(p1,p2))
for appversion in appversions:
appcount_data[appversion['appversion']].append(instances.filter(appversion=appversion['appversion']).count())
#print appversion['appversion'], ' hello', instances.filter(appversion=appversion['appversion']).count()
#print categories
#print appcount_data
appver_data = []
for appversion in appversions:
appver_data.append(
{
'name': appversion['appversion'],
'data': appcount_data[appversion['appversion']]
}
)
chart1 = {'categories':categories,'data':appver_data}
result['chart1'] = chart1
#chart2
chart2 = []
pre_class = ''
#print 'past',past
for e in errorElements:
instanceCount = Instances.objects.filter(iderror=e,datetime__gte=past).count()
if pre_class != e.errorclassname:
pre_class = e.errorclassname
chart2.append([e.errorclassname, instanceCount])
else:
last = len(chart2)
chart2[last-1] = [e.errorclassname,chart2[last-1][1] + (instanceCount)]
result['chart2'] = chart2
#chart3 - Device error
temp_data = {}
activities = []
instances = instanceElements.order_by('device')
for i in instances:
if i.device:
device = i.device
else:
device = "Unknown"
if not device in activities:
activities.append(device)
temp_data[device] = 1
else:
temp_data[device] += 1
sorted_dic = sorted(temp_data.iteritems(), key=operator.itemgetter(1), reverse=True)
categories = []
temp_data = []
i = 0
others_count = 0
for l,v in sorted_dic:
i += 1
if i>25:
others_count += v
else:
categories.append(l)
temp_data.append(v)
if others_count != 0:
categories.append('Others')
temp_data.append(others_count)
dev_data = [{'name':'Device','data':temp_data}]
chart3 = {'categories':categories,'data':dev_data}
result['chart3'] = chart3
#print 'chart3',chart3
#chart4
temp_data = {}
activities = []
instances = instanceElements.order_by('lastactivity')
for i in instances:
if i.lastactivity:
lastactivity = i.lastactivity
else:
lastactivity = "Unknown"
if not lastactivity in activities:
activities.append(lastactivity)
temp_data[lastactivity] = 1
else:
temp_data[lastactivity] += 1
sorted_dic = sorted(temp_data.iteritems(), key=operator.itemgetter(1), reverse=True)
categories = []
temp_data = []
i = 0
others_count = 0
for l,v in sorted_dic:
i += 1
if i>25:
others_count += v
else:
categories.append(l)
temp_data.append(v)
if others_count != 0:
categories.append('Others')
temp_data.append(others_count)
act_data = [{'name':'Activity','data':temp_data}]
chart4 = {'categories':categories,'data':act_data}
result['chart4'] = chart4
#Chart4
categories = []
ver_data = []
temp_data = {}
instances = instanceElements.order_by('-appversion','-osversion')
appv_idx = -1
for i in instances:
if not i.appversion in categories:
appv_idx += 1
categories.append(i.appversion)
if not i.osversion in temp_data:
temp_data[i.osversion] = []
while len(temp_data[i.osversion]) <= appv_idx:
temp_data[i.osversion].append(0)
#score = float(i.iderror.errorweight) / i.iderror.numofinstances
temp_data[i.osversion][appv_idx] += 1#score
for t in temp_data:
idx = 0
for e in temp_data[t]:
temp_data[t][idx] = e#round(e,2)
idx += 1
ver_data.append({'name':t,'data':temp_data[t]})
#print categories
#print ver_data
#ver_data[appv_idx][]
#print categories
chart5 = {'categories':categories,'data':ver_data}
result['chart5'] = chart5
#print 'chart4',chart4
return HttpResponse(json.dumps(result), 'application/json');
def chartdata_sbav(request,apikey):
#App version별 Client의 Session을 보여주는 차트
jsonData = json.loads(request.POST['json'],encoding='utf-8')
retention = int(jsonData['retention'])
username = request.user
valid , message , userElement, projectElement = validUserPjt(username,apikey)
if not valid:
return HttpResponseRedirect('/urqa')
#print 'retention', retention
if retention == 1:
retention = 24 #retention을 24로 변경 24시를 의미
past, today = getTimeRangeExactHour(retention,projectElement.timezone)
strformat = '%y-%m-%d %H'
dateformat = '%%y-%%m-%%d %%H'
else:
past, today = getTimeRange(retention,projectElement.timezone)
strformat = '%y-%m-%d'
dateformat = '%%y-%%m-%%d'
#########################################
#90% 에 해당하는 appversion 리스트 얻어오는 로직
# 1. 전체 session 수 구하기
# 2. 전체 세션수 대비 90% 에 해당하는 app version 리스트만 가져옴
#########################################
sql2 = 'SELECT appversion ,sum(appruncount) as total FROM appruncount2 where pid = %(pidinput)s and datetime >= %(pasttime)s group by appversion order by total desc'
params2 = {'pidinput':projectElement.pid,'pasttime':'%d-%d-%d %d:%d:%d' % (past.year,past.month,past.day,past.hour,past.minute,past.second)}
totalSession = TotalSession.objects.raw(sql2, params2)
sum = 0
for idx, pl in enumerate(totalSession):
sum = sum + pl.total
ratio = float(sum) / 1.1
ratioappversion = ()
ratioappversion = list(ratioappversion)
sum = 0
recentVersion = '0.0'
othersNumber = 0
for idx, pl in enumerate(totalSession):
if len(ratioappversion) == 0:
ratioappversion.append(str(pl.appversion))
if LooseVersion(recentVersion) < LooseVersion(pl.appversion):
if not pl.appversion == "unknown":
recentVersion = pl.appversion
if sum <= ratio:
ratioappversion.append(str(pl.appversion))
else:
othersNumber = othersNumber + pl.total
sum = sum + pl.total
#check recent version is exist in array
if not recentVersion in ratioappversion:
ratioappversion.append(str(recentVersion))
ratioappversion = tuple(ratioappversion)
if len(ratioappversion) == 1:
ratioappversion = str(ratioappversion)[:len(str(ratioappversion)) - 2] + str(ratioappversion)[-1]
#날짜별 Session수를 얻어오기 위한 Query생성
sql = 'SELECT idappruncount2 as idsessionbyapp, sum(appruncount) as runcount, appversion, DATE_FORMAT(CONVERT_TZ(datetime,"UTC",%(timezone)s),"' + dateformat +'") as sessionday'
sql = sql + ' from urqa.appruncount2'
if len(ratioappversion) == 0:
sql = sql + ' where pid = %(pidinput)s and datetime >= %(pasttime)s and appversion'
else:
sql = sql + ' where pid = %(pidinput)s and datetime >= %(pasttime)s and appversion in ' + str(ratioappversion)
sql = sql + ' Group by appversion, sessionday'
params = {'timezone':projectElement.timezone,'pidinput':projectElement.pid,'pasttime':'%d-%d-%d %d:%d:%d' % (past.year,past.month,past.day,past.hour,past.minute,past.second)}
places = SessionbyApp.objects.raw(sql, params)
appversions = []
dates = []
for idx, pl in enumerate(places):
if not pl.appversion in appversions:
appversions.append(pl.appversion)
if not pl.sessionday in dates:
dates.append(pl.sessionday)
result = {}
categories = []
appcount_data = {}
for appversion in appversions:
appcount_data[appversion] = []
new_places = []
for idx, pl in enumerate(places):
new_places.append({'appversion':pl.appversion,'sessionday':pl.sessionday,'runcount':pl.runcount})
#시간,날짜별로 Session Data를 나눔
for i in range(retention-1,-1,-1):
if retention == 24: # Statistics 하루치
day1 = getTimezoneHour(projectElement.timezone) + datetime.timedelta(hours = -i)
if day1.hour == 0:
categories.append(day1.strftime('%b-%d'))
else:
categories.append(day1.strftime('%H'))
else:
day1 = getTimezoneMidNight(projectElement.timezone) + datetime.timedelta(days = -i)
categories.append(day1.strftime('%b-%d'))
for appversion in appversions:
result_runcount = 0
for idx in range(0,len(new_places)):
if new_places[idx]['appversion'] == appversion and new_places[idx]['sessionday'] == day1.strftime(strformat):
result_runcount = new_places[idx]['runcount']
new_places.pop(idx)
break
appcount_data[appversion].append(int(result_runcount))
appver_data = []
for appversion in appversions:
appver_data.append(
{
'name': appversion,
'data': appcount_data[appversion]
}
)
chart_sbav = {'categories':categories,'data':appver_data}
result['chart_sbav'] = chart_sbav
return HttpResponse(json.dumps(result), 'application/json');
def chartdata_ebav(request,apikey):
#App version별 Client의 Error 수를 보여주는 차트
jsonData = json.loads(request.POST['json'],encoding='utf-8')
retention = int(jsonData['retention'])
username = request.user
valid , message , userElement, projectElement = validUserPjt(username,apikey)
if not valid:
return HttpResponseRedirect('/urqa')
# Common Data
result = {}
#appcount_data = {}
categories = []
#appver_data = []
if retention == 1:
retention = 24 #retention을 24로 변경 24시를 의미
past, today = getTimeRangeExactHour(retention,projectElement.timezone)
strformat = '%y-%m-%d %H'
dateformat = '%%y-%%m-%%d %%H'
else:
past, today = getTimeRange(retention,projectElement.timezone)
strformat = '%y-%m-%d'
dateformat = '%%y-%%m-%%d'
#########################################
#90% 에 해당하는 appversion 리스트 얻어오는 로직
# 1. 전체 session 수 구하기
# 2. 전체 세션수 대비 90% 에 해당하는 app version 리스트만 가져옴
#########################################
#sql2 = 'SELECT appversion ,count(*) as total FROM instances A, errors B where A.iderror = B.iderror and pid = %(pidinput)s and B.status in (0,1) and datetime >= %(pasttime)s group by appversion order by total desc'
sql2 = "SELECT appversion ,count(*) as total "
sql2 = sql2 + "FROM instances "
sql2 = sql2 + "where pid = %(pidinput)s and datetime >= %(pasttime)s "
sql2 = sql2 + "group by appversion "
sql2 = sql2 + "order by total desc"
params2 = {'pidinput':projectElement.pid,'pasttime':'%d-%d-%d %d:%d:%d' % (past.year,past.month,past.day,past.hour,past.minute,past.second)}
totalSession = TotalSession.objects.raw(sql2, params2)
#======= 장고로 변환 테스트가 안되어 있음;;
# pasttime= '%d-%d-%d %d:%d:%d' % (past.year,past.month,past.day,past.hour,past.minute,past.second)
#
# totalSession = sorted(Instances.objects.values('appversion').annotate(total=Count('*')).filter(
# pid=projectElement, datetime__gte=pasttime
# ).group_by('appversion'), key=lambda instance: instance['total'], reverse=True)
sum = 0
for idx, pl in enumerate(totalSession):
sum = sum + pl.total
ratio = float(sum) / 1.1
ratioappversion = ()
ratioappversion = list(ratioappversion)
sum = 0
recentVersion = '0.0'
othersNumber = 0
for idx, pl in enumerate(totalSession):
if len(ratioappversion) == 0:
ratioappversion.append(str(pl.appversion))
if LooseVersion(recentVersion) < LooseVersion(pl.appversion):
if not pl.appversion == "unknown":
recentVersion = pl.appversion
if sum <= ratio:
ratioappversion.append(str(pl.appversion))
else:
othersNumber = othersNumber + pl.total
sum = sum + pl.total
#check recent version is exist in array
if not recentVersion in ratioappversion:
ratioappversion.append(str(recentVersion))
ratioappversion = tuple(ratioappversion)
if len(ratioappversion) == 1:
ratioappversion = str(ratioappversion)[:len(str(ratioappversion)) - 2] + str(ratioappversion)[-1]
#Error Count를 얻어올 Query를 생성한다.
sql = "select count(*) as errorcount ,appversion, DATE_FORMAT(CONVERT_TZ(datetime,'UTC',%(timezone)s),'" + dateformat + "') as errorday "
sql = sql + "from instances "
sql = sql + "where pid = %(pidinput)s "
if len(ratioappversion) == 0:
sql = sql + ' and datetime >= %(pasttime)s and appversion '
else:
sql = sql + ' and datetime >= %(pasttime)s and appversion in ' + str(ratioappversion)
sql = sql + "group by errorday,appversion"
past, today = getTimeRange(retention,projectElement.timezone)
params = {'timezone':projectElement.timezone,'pidinput':projectElement.pid,'pasttime':'%d-%d-%d %d:%d:%d' % (past.year,past.month,past.day,past.hour,past.minute,past.second)}
places = ErrorsbyApp.objects.raw(sql, params)
#listing app version
appversions = []
dates = []
for idx, pl in enumerate(places):
if not pl.appversion in appversions:
appversions.append(pl.appversion)
if not pl.errorday in dates:
dates.append(pl.errorday)
result = {}
categories = []
appcount_data = {}
for appversion in appversions:
appcount_data[appversion] = []
new_places = []
for idx, pl in enumerate(places):
new_places.append({'appversion':pl.appversion,'errorday':pl.errorday,'errorcount':pl.errorcount})
#시간,날짜별로 Error Count를 나눔
for i in range(retention-1,-1,-1):
if retention == 24:
day1 = getTimezoneHour(projectElement.timezone) + datetime.timedelta(hours = -i)
if day1.hour == 0:
categories.append(day1.strftime('%b-%d'))
else:
categories.append(day1.strftime('%H'))
else:
day1 = getTimezoneMidNight(projectElement.timezone) + datetime.timedelta(days = -i)
categories.append(day1.strftime('%b-%d'))
for appversion in appversions:
result_runcount = 0
for idx in range(0,len(new_places)):
if new_places[idx]['appversion'] == appversion and new_places[idx]['errorday'] == day1.strftime(strformat):
result_runcount = new_places[idx]['errorcount']
new_places.pop(idx)
break
appcount_data[appversion].append(int(result_runcount))
appver_data = []
for appversion in appversions:
appver_data.append(
{
'name': appversion,
'data': appcount_data[appversion]
}
)
chart_ebav = {'categories':categories,'data':appver_data}
result['chart_sbav'] = chart_ebav
chart1 = {'categories':categories,'data':appver_data}
result['chart1'] = chart1
#print >>sys.stderr, chart1
return HttpResponse(json.dumps(result), 'application/json');
