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');