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_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):
#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');