def handle(self, *args, **options): """ 达标妈妈活跃度, 一个达标过的妈妈与未达标妈妈的数据对比 需求待分析 """ start_week = options.get('start_week')[0] earliest_finish_valuelist = MamaMissionRecord.objects.filter( mission__cat_type=MamaMission.CAT_SALE_MAMA, # status= MamaMissionRecord.FINISHED, finish_time__isnull=False, # mama_id=44 ).values('mama_id').annotate(Min('finish_time')).values_list( 'mama_id', 'finish_time__min') earliest_map = dict(earliest_finish_valuelist) week_list = self.get_start_weeklist(start_week) mama_data = {} base_qs = MamaMissionRecord.objects.filter( mission__cat_type=MamaMission.CAT_SALE_MAMA, # mama_id=44, ) for year_week in week_list: week_start, week_end = week_range( datetime.datetime.strptime('%s-0' % year_week, '%Y-%W-%w')) base_week_qs = base_qs.filter(year_week=year_week, created__range=(week_start, week_end), finish_value__gt=0) mama_week_data = base_week_qs.values_list('mama_id', 'target_value', 'finish_value', 'finish_time') for mama_id, target_value, finish_value, finish_time in mama_week_data: first_finish_time = earliest_map.get(mama_id) or finish_time if not first_finish_time: continue if mama_data.has_key(mama_id): mama_data[mama_id].setdefault( year_week, { 'mama_id': mama_id, 'target_value': target_value, 'finish_value': finish_value, 'first_finish_time': first_finish_time }) else: mama_data.setdefault( mama_id, { year_week: { 'mama_id': mama_id, 'target_value': target_value, 'finish_value': finish_value, 'first_finish_time': first_finish_time } }) week_dd = [] week_len = len(week_list) for week in week_list: week_dl = map(lambda x: [], range(week_len)) week_dd.append(week_dl) for mama_id, data_dict in mama_data.iteritems(): for inn_year_week, mm_data in data_dict.iteritems(): earliest_year_week = mm_data['first_finish_time'].strftime( '%Y-%W') week_dd[week_list.index(earliest_year_week)][week_list.index( inn_year_week)].append(mm_data) def _calc_value(group_data): num_a = len(group_data) num_b = len([ d['finish_value'] for d in group_data if d['finish_value'] >= d['target_value'] ]) unfinish_ll = [(d['finish_value'] * 1.0) / d['target_value'] for d in group_data if d['finish_value'] < d['target_value']] rate_a = unfinish_ll and sum(unfinish_ll) / len(unfinish_ll) or 0 return '/'.join([str(num_a), str(num_b), '%.4f' % rate_a]) print '\t'.join(['week'] + week_list) for index, ddx in enumerate(week_dd): ddx_ll = [week_list[index]] + [_calc_value(ddl) for ddl in ddx] print '\t'.join(ddx_ll)
def index(request): pictures_json = { 'data_count': Pictures.objects.count(), # aggregate 聚合 'total_like_count': Pictures.objects.aggregate(Sum('picture_like'))['picture_like__sum'], 'avg_like_count': Pictures.objects.aggregate(Avg('picture_like'))['picture_like__avg'], 'max_like_count': Pictures.objects.aggregate(Max('picture_like'))['picture_like__max'], # order_by 排序,加负号代表降序 'max_like_count_image_url': Pictures.objects.order_by('-picture_like')[0].picture_url, 'max_like_count_image_date': Pictures.objects.order_by('-picture_like')[0].picture_date, 'max_like_count_image_text': Pictures.objects.order_by('-picture_like')[0].picture_description, 'min_like_count': Pictures.objects.aggregate(Min('picture_like'))['picture_like__min'], 'total_download_count': Pictures.objects.aggregate( Sum('picture_download'))['picture_download__sum'], 'avg_download_count': Pictures.objects.aggregate( Avg('picture_download'))['picture_download__avg'], 'max_download_count': Pictures.objects.aggregate( Max('picture_download'))['picture_download__max'], 'max_download_count_image_url': Pictures.objects.order_by('-picture_download')[0].picture_url, 'max_download_count_image_date': Pictures.objects.order_by('-picture_download')[0].picture_date, 'max_download_count_image_text': Pictures.objects.order_by('-picture_download')[0].picture_description, 'min_download_count': Pictures.objects.aggregate( Min('picture_download'))['picture_download__min'], # 获取所有的图片数据 'num_data': list(Pictures.objects.all().values('id', 'picture_date', 'picture_like', 'picture_download')), } # 获取每个作者的词数量列表 author_poem_list = Poems.objects.values('poem_author').annotate( Count('poem_author')).all().order_by('-poem_author__count') # 获取每个词牌名的词数量列表 poem_title_list = Poems.objects.values('poem_title').annotate( Count('poem_title')).all().order_by('-poem_title__count') poems_json = { 'data_count': Poems.objects.count(), 'author_data_count': PoemsAuthors.objects.count(), 'author_count': Poems.objects.values('poem_author').distinct().count(), 'title_count': Poems.objects.values('poem_title').distinct().count(), 'author_poem_data': list(author_poem_list), 'author_avg_poem_count': Poems.objects.count() / PoemsAuthors.objects.count(), 'poem_title_data': list(poem_title_list), 'poem_avg_title_count': Poems.objects.count() / Poems.objects.values('poem_title').distinct().count(), # 只有一个作品的作者人数 'poem_count_1': author_poem_list.filter(poem_author__count=1).count(), } sentences_json = { 'data_count': Sentences.objects.count(), # 句子的分类数据 'sentence_data': list( Sentences.objects.values( 'sentence_category', 'sentence_category_description').annotate( Count('sentence_category')).all().order_by( '-sentence_category__count')) } # 概览数据 overview_json = { 'total_data_count': pictures_json['data_count'] + poems_json['data_count'] + poems_json['author_data_count'] + sentences_json['data_count'], 'category_count': 3, 'database_table_count': 4, 'pictures_data_count': pictures_json['data_count'], 'pictures_image_count': pictures_json['data_count'], 'pictures_total_like_count': pictures_json['total_like_count'], 'pictures_total_download_count': pictures_json['total_download_count'], 'poems_data_count': poems_json['data_count'] + poems_json['author_data_count'], 'poems_poem_count': poems_json['data_count'], 'poems_author_count': poems_json['author_data_count'], 'sentences_data_count': sentences_json['data_count'], 'sentences_try_count': 6400, 'sentences_category_count': 8, } # 返回 JSON 数据,下同 return JsonResponse({ 'code': '0', 'message': 'ok', 'overview': overview_json, 'pictures': pictures_json, 'poems': poems_json, 'sentences': sentences_json, })
def test_populate_line_item_daily_summary_table(self): """Test that the daily summary table is populated.""" summary_table_name = AZURE_REPORT_TABLE_MAP["line_item_daily_summary"] summary_table = getattr(self.accessor.report_schema, summary_table_name) bills = self.accessor.get_cost_entry_bills_query_by_provider( self.azure_provider_uuid) with schema_context(self.schema): bill_ids = [str(bill.id) for bill in bills.all()] table_name = AZURE_REPORT_TABLE_MAP["line_item"] line_item_table = getattr(self.accessor.report_schema, table_name) tag_query = self.accessor._get_db_obj_query(table_name) possible_keys = [] possible_values = [] with schema_context(self.schema): for item in tag_query: possible_keys += list(item.tags.keys()) possible_values += list(item.tags.values()) li_entry = line_item_table.objects.all().aggregate( Min("usage_date"), Max("usage_date")) start_date = li_entry["usage_date__min"] end_date = li_entry["usage_date__max"] start_date = start_date.date() if isinstance( start_date, datetime.datetime) else start_date end_date = end_date.date() if isinstance( end_date, datetime.datetime) else end_date query = self.accessor._get_db_obj_query(summary_table_name) with schema_context(self.schema): query.delete() initial_count = query.count() self.accessor.populate_line_item_daily_summary_table( start_date, end_date, bill_ids) with schema_context(self.schema): self.assertNotEqual(query.count(), initial_count) summary_entry = summary_table.objects.all().aggregate( Min("usage_start"), Max("usage_start")) result_start_date = summary_entry["usage_start__min"] result_end_date = summary_entry["usage_start__max"] self.assertEqual(result_start_date, start_date) self.assertEqual(result_end_date, end_date) entry = query.order_by("-id") summary_columns = [ "usage_start", "usage_quantity", "pretax_cost", "cost_entry_bill_id", "meter_id", "tags", ] for column in summary_columns: self.assertIsNotNone(getattr(entry.first(), column)) found_keys = [] found_values = [] for item in query.all(): found_keys += list(item.tags.keys()) found_values += list(item.tags.values()) self.assertEqual(set(sorted(possible_keys)), set(sorted(found_keys))) self.assertEqual(set(sorted(possible_values)), set(sorted(found_values)))
def test_aggregate_multi_join(self): vals = Store.objects.aggregate(Max("books__authors__age")) self.assertEqual(vals, {'books__authors__age__max': 57}) vals = Author.objects.aggregate(Min("book__publisher__num_awards")) self.assertEqual(vals, {'book__publisher__num_awards__min': 1})
def get_queryset(self): sensor_type = self.kwargs["sensor_type"] city_slugs = self.request.query_params.get("city", None) from_date = self.request.query_params.get("from", None) to_date = self.request.query_params.get("to", None) interval = self.request.query_params.get("interval", None) if to_date and not from_date: raise ValidationError({"from": "Must be provide along with to query"}) if from_date: validate_date(from_date, {"from": "Must be a date in the format Y-m-d."}) if to_date: validate_date(to_date, {"to": "Must be a date in the format Y-m-d."}) value_type_to_filter = self.request.query_params.get("value_type", None) filter_value_types = value_types[sensor_type] if value_type_to_filter: filter_value_types = set(value_type_to_filter.upper().split(",")) & set( [x.upper() for x in value_types[sensor_type]] ) if not from_date and not to_date: to_date = timezone.now().replace(minute=0, second=0, microsecond=0) from_date = to_date - datetime.timedelta(hours=24) interval = "day" if not interval else interval elif not to_date: from_date = beginning_of_day(from_date) # Get data from_date until the end # of day yesterday which is the beginning of today to_date = beginning_of_today() else: from_date = beginning_of_day(from_date) to_date = end_of_day(to_date) queryset = SensorDataStat.objects.filter( value_type__in=filter_value_types, timestamp__gte=from_date, timestamp__lte=to_date, ) if interval == "month": truncate = TruncMonth("timestamp") elif interval == "day": truncate = TruncDay("timestamp") else: truncate = TruncHour("timestamp") if city_slugs: queryset = queryset.filter(city_slug__in=city_slugs.split(",")) return ( queryset.values("value_type", "city_slug") .annotate( truncated_timestamp=truncate, start_datetime=Min("timestamp"), end_datetime=Max("timestamp"), calculated_average=ExpressionWrapper( Sum(F("average") * F("sample_size")) / Sum("sample_size"), output_field=FloatField(), ), calculated_minimum=Min("minimum"), calculated_maximum=Max("maximum"), ) .values( "value_type", "city_slug", "truncated_timestamp", "start_datetime", "end_datetime", "calculated_average", "calculated_minimum", "calculated_maximum", ) .order_by("city_slug", "-truncated_timestamp") )
def get_seasons(): start_year = MatchDate.objects.all().aggregate(Min('year'))['year__min'] return range(date.today().year, start_year - 1, -1)
def get(self, request, product_id): product = Product.objects.get(id=product_id) detail_data_set = { 'product_id': product.id, 'name': product.name, 'ticker': product.ticker, 'series': product.category.name, 'sub_category': product.category.sub_category.name, 'main_category': product.category.sub_category.main_category.name, 'description': product.description, 'style': product.style, 'colorway': product.colorway, 'retail_price': '$' + str(int(product.retail_price)), 'release_date': product.release_date.date, 'average_price': '$' + str(product.average_price) if product.average_price else '$0', 'price_premium': str(product.price_premium / 10) + '%' if product.price_premium else '0%', 'detail_images': Image.objects.filter(product=product_id, image_type=2)[0].url, 'volatility': str(product.volatility * 100) + '%' if product.volatility else '0.0%' } ################################################################################# # BASIC PRODUCT INFORM Complete related_product_list = [] all_product_count = Product.objects.count() related_range = list( range(product.id + 1, product.id + 16)) if all_product_count // 2 > product.id else list( range(product.id - 1, product.id - 16, -1)) related_product = Product.objects.filter( id__in=related_range).order_by('id') related_product_images = Image.objects.filter( product__in=related_range, image_type=1).order_by('product') for each_product in related_product: related_product_list.append({ 'product_id': each_product.id, 'name': each_product.name, 'thumnail': related_product_images[each_product.id - (product_id + 1)].url, 'average_price': '$' + str(int(each_product.average_price)) if each_product.average_price else '$0' }) detail_data_set['related_product_list'] = related_product_list ################################################################################## # RELATED PRODUCT Complete product_size_info_list = [] week_52_low, week_52_high = None, None product_size_list = [ each.id for each in ProductSize.objects.filter(product=product_id) ] productsize_list = ProductSize.objects.filter(product=product_id) for productsize in productsize_list: target_orders = Order.objects.filter( ask__product_size_id=productsize.id).select_related( 'ask').order_by('date') if len(target_orders) > 1: recent_price, before_price, recent_date = target_orders[ 0].ask.price, target_orders[1].ask.price, target_orders[ 0].date elif len(target_orders) == 1: recent_price, before_price, recent_date = target_orders[ 0].ask.price, 0, target_orders[0].date else: recent_price, before_price, recent_date = 0, 0, None difference = int(recent_price - before_price) percentage = 0 if difference == 0 else int( (difference / recent_price) * 100) lowAsk = Ask.objects.filter( product_size_id=productsize.id).aggregate( Min('price'))['price__min'] highBid = Bid.objects.filter( product_size_id=productsize.id).aggregate( Max('price'))['price__max'] if highBid: if not (week_52_high): week_52_high = int(highBid) else: week_52_high = int( highBid) if week_52_high < highBid else week_52_high if lowAsk: if not (week_52_low): week_52_low = int(lowAsk) else: week_52_low = int( lowAsk) if week_52_low > lowAsk else week_52_low product_size_info_list.append({ 'size': productsize.size.name, 'lowestAsk': '$' + str(int(lowAsk)) if lowAsk else '$0', 'highestBid': '$' + str(int(highBid)) if highBid else '$0', 'lastSale': '$' + str(int(recent_price)), 'lastSize': None, 'difference': '-$' + str(difference)[1:] if difference < 0 else '+$' + str(difference) if difference > 0 else str(difference), 'percentage': str(percentage) + '%' if percentage < 0 else '+' + str(percentage) + '%' if percentage > 0 else str(percentage) + '%', 'lastDate': recent_date }) temp_lastDate = 0 size_all = { 'size': 'All', 'lowestAsk': '$100000', 'highestBid': '$0', 'lastSale': 0, 'lastSize': '', 'difference': 0, 'percentage': 0, 'lastDate': None } for item in product_size_info_list: if not (item['highestBid'] == None) and int( size_all['highestBid'][1:]) < int(item['highestBid'][1:]): size_all['highestBid'] = item['highestBid'] if not (item['lowestAsk'] == None) and int( size_all['lowestAsk'][1:]) > int(item['lowestAsk'][1:]): size_all['lowestAsk'] = item['lowestAsk'] if item['lastDate'] == None: continue else: if temp_lastDate == 0: temp_lastDate = item['lastDate'] if temp_lastDate <= item['lastDate']: temp_lastDate = item['lastDate'] size_all['size'] = 'All' size_all['lastSale'] = item['lastSale'] size_all['lastSize'] = item['size'] size_all['difference'] = item['difference'] size_all['percentage'] = item['percentage'] product_size_info_list.insert(0, size_all) detail_data_set['52week_high'] = '$' + str(week_52_high) detail_data_set['52week_low'] = '$' + str(week_52_low) detail_data_set['product_size_info_list'] = product_size_info_list #################################################################################### # SIZE RELATED INFORMATION Complete all_sale_list = [] target_asks = Ask.objects.filter( product_size_id__in=product_size_list).prefetch_related( 'order_set').order_by('order__date') for ask in target_asks: full_date = str(ask.order_set.get().date).split(' ') all_sale_list.append({ 'size': ProductSize.objects.get(id=ask.product_size_id).size.name, 'sale_price': int(ask.price), 'date': full_date[0], 'time': full_date[1] }) detail_data_set['all_sale_list'] = all_sale_list return JsonResponse({'message': detail_data_set}, status=200)
def get(self, request, id, tipo_id, format=None): cursor = connections['vote_db'].cursor() comuna = Comuna.objects.get(pk=id) comunaSer = ComunaFullSerial(comuna, many=False).data # region = Region.objects.get(pk=comuna.region.id) # regionSer = RegionSerial(region, many=False).data # comunaSer['region'] = regionSer delincuencia = Delincuencia.objects.filter(comuna_id=id) delincuenciaSer = DelincuenciaSerial(delincuencia, many=True).data comunaSer['delincuencia'] = delincuenciaSer educacion = Educacion.objects.filter(comuna_id=id) educacionSer = EducacionSerial(educacion, many=True).data comunaSer['educacion'] = educacionSer pobreza = Pobreza.objects.filter(comuna_id=id) pobrezaSer = PobrezaSerial(pobreza, many=True).data comunaSer['pobreza'] = pobrezaSer salud = Salud.objects.filter(comuna_id=id) saludSer = SaludSerial(salud, many=True).data comunaSer['salud'] = saludSer ambiente = Ambiente.objects.filter(comuna_id=id) ambienteSer = AmbienteSerial(ambiente, many=True).data comunaSer['ambiente'] = ambienteSer educacionLimit = Educacion.objects.filter( establecimiento_id=1).aggregate(Min('psu_promedio'), Max('psu_promedio')) educacionExt = Educacion.objects.filter( Q(psu_promedio=educacionLimit['psu_promedio__min']) | Q(psu_promedio=educacionLimit['psu_promedio__max'])).order_by( 'psu_promedio') educacionExtSer = EducacionSerial(educacionExt, many=True).data pobrezaLimit = Pobreza.objects.aggregate(Min('poblacion_idx'), Max('poblacion_idx')) pobrezaExt = Pobreza.objects.filter( Q(poblacion_idx=pobrezaLimit['poblacion_idx__min']) | Q(poblacion_idx=pobrezaLimit['poblacion_idx__max'])).order_by( 'poblacion_idx') pobrezaExtSer = PobrezaSerial(pobrezaExt, many=True).data ambienteLimit = Ambiente.objects.aggregate(Min('metros_idx'), Max('metros_idx')) ambienteExt = Ambiente.objects.filter( Q(metros_idx=ambienteLimit['metros_idx__min']) | Q(metros_idx=ambienteLimit['metros_idx__max'])).order_by( 'metros_idx') ambienteExtSer = AmbienteSerial(ambienteExt, many=True).data comunaSer['extremos'] = {} comunaSer['extremos']['educacion'] = educacionExtSer comunaSer['extremos']['pobreza'] = pobrezaExtSer comunaSer['extremos']['ambiente'] = ambienteExtSer desempleo = Desempleo.objects.filter(region_id=comuna.region.id) desempleoSer = DesempleoSerial(desempleo, many=True).data comunaSer['desempleo'] = desempleoSer # poblacion = Poblacion.objects.filter(comuna_id=id) # poblacionSer = PoblacionSerial(poblacion, many=True).data # comunaSer['poblacion'] = poblacionSer comunaSer['poblacion_adultos_cnt'] = Poblacion.objects.filter( anno=2016, comuna_id=comunaSer['id']).aggregate( total=Sum('padron_cnt'))['total'] comunaSer['poblacion_adultos_idx'] = ( comunaSer['poblacion_adultos_cnt'] * 100.0) / Poblacion.objects.filter(anno=2016).aggregate( total=Sum('padron_cnt'))['total'] representantes = Representante.objects.filter(comuna_id=id) representantesSer = RepresentanteSerial(representantes, many=True).data comunaSer['candidatos_alcalde'] = representantesSer votos_concejales = [] cursor.execute( "SELECT pacto_id, p.nombre, SUM(votos_cnt) as votos_total FROM resultado r, pacto p WHERE p.id=r.pacto_id and r.comuna_id=%s and r.anno=2012 and r.eleccion_tipo_id=4 group by r.pacto_id ORDER BY votos_total DESC", [id]) rows = cursor.fetchall() for row in rows: # comuna = Comuna.objects.get(pk=row[0]) # comunaSer = ComunaSerial(comuna, many=False).data pacto = {"pacto_id": row[0], "nombre": row[1], "votos_cnt": row[2]} # comunaSer['emitidos_cnt'] = row[1] # comunaSer['poblacion_adultos_cnt'] = row[2] # comunaSer['participacion'] = row[3] votos_concejales.append(pacto) comunaSer['votos_pacto'] = votos_concejales tipos = EleccionTipo.objects.filter(eleccion_grupo_id=tipo_id) for tipo in tipos: min_anno = Participacion.objects.filter( comuna_id=id, eleccion_tipo_id=tipo.id).aggregate(Min('anno')) print min_anno if min_anno['anno__min'] == None: continue elecciones = EleccionFecha.objects.filter( eleccion_tipo_id=tipo.id, anno__gte=min_anno['anno__min']).order_by('-anno') eleccionesSer = EleccionSerial(elecciones, many=True).data for eleccion in eleccionesSer: eleccion['poblacion_adultos_cnt'] = Poblacion.objects.filter( anno=eleccion['anno'], comuna_id=comunaSer['id']).aggregate( total=Sum('padron_cnt'))['total'] participacion = Participacion.objects.filter( anno=eleccion['anno'], eleccion_tipo_id=eleccion['eleccion_tipo']['id'], vuelta=eleccion['vuelta'], comuna_id=comunaSer['id']).values().annotate( emitidos_cnt=Sum('emitidos_cnt'), validos_cnt=Sum('validos_cnt'), blancos_cnt=Sum('blancos_cnt'), nulos_cnt=Sum('nulos_cnt')).order_by('-anno') if participacion.count() > 0: eleccion['participacion'] = ParticipacionSerial( participacion, many=True).data[0] candidatos = Resultado.objects.filter( eleccion_tipo_id=eleccion['eleccion_tipo']['id'], comuna_id=id, anno=eleccion['anno']).order_by('-votos_cnt') eleccion['candidatos'] = ResultadoSerial(candidatos, many=True).data comunaSer[tipo.nombre.lower()] = eleccionesSer return Response(comunaSer)
def handle(self, *args, **options): resultid = int(options['resultid'][0]) email = str(options['email'][0]) # response_data = {} createorupdateL1 = str(options['createorupdateL1'][0]) pl1 = Processinglevels.objects.get(processinglevelid=2) pl0 = Processinglevels.objects.get(processinglevelid=1) valuesadded = 0 tsresultTocopyBulk = [] tsresultL1 =None # print('starting L1 create or update') # print(createorupdateL1) # print('result') # print(resultid) # print('email') # print(email) if createorupdateL1 == "create": #print('create') resultTocopy = Results.objects.get(resultid=resultid) tsresultTocopy = Timeseriesresults.objects.get(resultid=resultid) resultTocopy.resultid = None resultTocopy.processing_level = pl1 resultTocopy.save() tsrvToCopy = Timeseriesresultvalues.objects.filter(resultid=tsresultTocopy) tsresultTocopy.resultid = resultTocopy tsresultTocopy.save() tsresultL1 = tsresultTocopy.resultid # tsrvToCopy.update(resultid=tsresultTocopy) for tsrv in tsrvToCopy: tsrv.resultid = tsresultTocopy try: tsrva = Timeseriesresultvalueannotations.objects.get(valueid = tsrv.valueid) tsrv.valueid = None tsrv.save() tsrva.valueid = tsrv # print(tsrv.valueid) tsrva.save() except ObjectDoesNotExist: tsrv.valueid = None tsresultTocopyBulk.append(tsrv) newtsrv = Timeseriesresultvalues.objects.bulk_create(tsresultTocopyBulk) elif createorupdateL1 == "update": # print('update') tsresultL1 = Timeseriesresults.objects.get(resultid=resultid) resultL1 = Results.objects.get(resultid=resultid) # tsrvL1 = Timeseriesresultvalues.objects.filter(resultid=tsresultL1) tsrvAddToL1Bulk = [] relatedL0result = Results.objects.filter( featureactionid = resultL1.featureactionid).filter( variableid = resultL1.variableid ).filter(unitsid = resultL1.unitsid).filter( processing_level=pl0) # newresult = relatedL0result.resultid relateL0tsresults = Timeseriesresults.objects.filter(resultid__in= relatedL0result) relateL0tsresult = None for L0result in relateL0tsresults: if L0result.intendedtimespacing == tsresultL1.intendedtimespacing and L0result.intendedtimespacingunitsid == tsresultL1.intendedtimespacingunitsid: relateL0tsresult =L0result tsrvL0 = Timeseriesresultvalues.objects.filter(resultid=relateL0tsresult) # print(relateL0tsresult) # maxtsrvL1=Timeseriesresultvalues.objects.filter(resultid=relateL1tsresult).annotate( # Max('valuedatetime')). \ # order_by('-valuedatetime') # print(relateL1tsresult) # for r in maxtsrvL1: # print(r) # print('L1 result') # print(tsresultL1) maxtsrvL0=Timeseriesresultvalues.objects.filter(resultid=relateL0tsresult).annotate( Max('valuedatetime')). \ order_by('-valuedatetime')[0].valuedatetime maxtsrvL1=Timeseriesresultvalues.objects.filter(resultid=tsresultL1).annotate( Max('valuedatetime')). \ order_by('-valuedatetime')[0].valuedatetime mintsrvL0=Timeseriesresultvalues.objects.filter(resultid=relateL0tsresult).annotate( Min('valuedatetime')). \ order_by('valuedatetime')[0].valuedatetime mintsrvL1=Timeseriesresultvalues.objects.filter(resultid=tsresultL1).annotate( Min('valuedatetime')). \ order_by('valuedatetime')[0].valuedatetime # print('max L0') # print(maxtsrvL0) # print('max L1') # print(maxtsrvL1) if maxtsrvL1 < maxtsrvL0: tsrvAddToL1 = tsrvL0.filter(valuedatetime__gt=maxtsrvL1) for tsrv in tsrvAddToL1: tsrv.resultid = tsresultL1 try: tsrva = Timeseriesresultvalueannotations.objects.get(valueid = tsrv.valueid) tsrv.valueid = None tsrv.save() tsrva.valueid = tsrv # print(tsrv.valueid) tsrva.save() except ObjectDoesNotExist: # print('doesnt exist') tsrv.valueid = None tsresultTocopyBulk.append(tsrv) if mintsrvL1 > mintsrvL0: tsrvAddToL1 = tsrvL0.filter(valuedatetime__lt=mintsrvL1) for tsrv in tsrvAddToL1: # print(tsresultL1) tsrv.resultid = tsresultL1 try: tsrva = Timeseriesresultvalueannotations.objects.get(valueid = tsrv.valueid) tsrv.valueid = None tsrv.save() tsrva.valueid = tsrv # print(tsrv.valueid) tsrva.save() except ObjectDoesNotExist: tsrv.valueid = None tsresultTocopyBulk.append(tsrv) newtsrv = Timeseriesresultvalues.objects.bulk_create(tsresultTocopyBulk) valuesadded = newtsrv.__len__() # print('values added') # print(valuesadded) # for tsrv in newtsrv: # print(tsrv.resultid.resultid) # print(tsrv) emailtitle = "L1 complete" tolist = [] emailtext = ' L1 result complete for new or updated result: ' + str(tsresultL1) + '\n' + ' values added to time series: ' + str(valuesadded) for admin in settings.ADMINS: tolist.append(admin['email']) tolist.append(email) # print(tolist) if len(email) > 0: email = EmailMessage(emailtitle, emailtext, settings.EMAIL_FROM_ADDRESS, tolist) # print('email') # print(emailtext) emailout = email.send()
def test_populate_line_item_daily_summary_table(self, mock_vaccum): """Test that the daily summary table is populated.""" summary_table_name = AZURE_REPORT_TABLE_MAP['line_item_daily_summary'] summary_table = getattr(self.accessor.report_schema, summary_table_name) for _ in range(10): bill = self.creator.create_azure_cost_entry_bill( provider_id=self.azure_provider.id) product = self.creator.create_azure_cost_entry_product() meter = self.creator.create_azure_meter() service = self.creator.create_azure_service() self.creator.create_azure_cost_entry_line_item( bill, product, meter, service) bills = self.accessor.get_cost_entry_bills_query_by_provider( self.azure_provider.id) with schema_context(self.schema): bill_ids = [str(bill.id) for bill in bills.all()] table_name = AZURE_REPORT_TABLE_MAP['line_item'] line_item_table = getattr(self.accessor.report_schema, table_name) tag_query = self.accessor._get_db_obj_query(table_name) possible_keys = [] possible_values = [] with schema_context(self.schema): for item in tag_query: possible_keys += list(item.tags.keys()) possible_values += list(item.tags.values()) li_entry = line_item_table.objects.all().aggregate( Min('usage_date_time'), Max('usage_date_time')) start_date = li_entry['usage_date_time__min'] end_date = li_entry['usage_date_time__max'] start_date = start_date.replace(hour=0, minute=0, second=0, microsecond=0) end_date = end_date.replace(hour=0, minute=0, second=0, microsecond=0) query = self.accessor._get_db_obj_query(summary_table_name) with schema_context(self.schema): initial_count = query.count() self.accessor.populate_line_item_daily_summary_table( start_date, end_date, bill_ids) with schema_context(self.schema): self.assertNotEqual(query.count(), initial_count) summary_entry = summary_table.objects.all().aggregate( Min('usage_date_time'), Max('usage_date_time')) result_start_date = summary_entry['usage_date_time__min'] result_end_date = summary_entry['usage_date_time__max'] self.assertEqual(result_start_date, start_date) self.assertEqual(result_end_date, end_date) entry = query.order_by('-id') summary_columns = [ 'usage_date_time', 'usage_quantity', 'pretax_cost', 'offer_id', 'cost_entry_bill_id', 'meter_id', 'tags', ] for column in summary_columns: self.assertIsNotNone(getattr(entry.first(), column)) found_keys = [] found_values = [] for item in query.all(): found_keys += list(item.tags.keys()) found_values += list(item.tags.values()) self.assertEqual(set(sorted(possible_keys)), set(sorted(found_keys))) self.assertEqual(set(sorted(possible_values)), set(sorted(found_values)))
def post(self, request, format=None): serializer = ScoreSerializer(data=request.data, context={'request': request}) if serializer.is_valid(): s = serializer.save() datetime_current = datetime.today() year = datetime_current.year month = datetime_current.month num_days = calendar.monthrange(year, month)[1] datetime_start = datetime(year, month, 1, 0, 0) datetime_end = datetime(year, month, num_days, 23, 59) user_request = User.objects.get(pk=request.user.id) competitor_request = Competitor.objects.get(user=user_request) if s.score <= 25000: s.delete() return Response({ "message": "Score added", "rank": 110 }, status=status.HTTP_201_CREATED) #score_competitor = Score.objects.filter(competitor=competitor_request).filter( # creationDate__range=(datetime_start, datetime_end)).order_by('score')[:1] # competitor_request = Competitor.objects.get(user=user_request) # scores = Score.objects.filter(creationDate__range=(datetime_start, datetime_end)).order_by('score')[:100] scores = Score.objects.filter(creationDate__range=( datetime_start, datetime_end)).values('competitor').annotate( score=Min('score')).order_by('score')[:105] i = 1 for score in scores: if s.score <= score['score']: return Response( { "message": "Score added", "rank": i, "score": s.score, "larger": score['score'] }, status=status.HTTP_201_CREATED) i += 1 if i <= 100: return Response({ "message": "Score added", "rank": i }, status=status.HTTP_201_CREATED) else: return Response({ "message": "Score added", "rank": i }, status=status.HTTP_201_CREATED) return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
def ReproduceSentenceEval(request, pk): if request.method != 'POST': raise Http404 example_inst = get_object_or_404(Example, pk=pk) verb_pk = example_inst.verb.pk verb = example_inst.verb if request.method == 'POST': form = ReproduceSentenceForm(request.POST) if form.is_valid(): # fuzzy string comparison to examine the accuracy of user input user_input = strip_punct_lower(form.cleaned_data['sentence_field']) answer = strip_punct_lower(example_inst.russian_text) score = fuzz.ratio(user_input, answer) request.session['current_score'].append(score) request.session['progress'] += round(1 / 12, 3) * 100 if request.session['progress'] > 95: request.session['progress'] = 100 """ calculate the average score from the session list (this is what gets written to the PerformancePerExample model) """ average_score = sum(request.session['current_score']) / len( request.session['current_score']) average_score = round(average_score, 2) average_score /= 100 try: request.session['quiz_summary'].append( (example_inst.russian_text, example_inst.translation_text, request.session['current_score'][0], request.session['current_score'][1], request.session['current_score'][2], score, int(average_score * 100))) except KeyError: request.session['quiz_summary'] = [] request.session.modified = True """ test whether the user already has a PerformancePerExample for this verb """ if not PerformancePerExample.objects.filter( example=example_inst, user=request.user).exists(): PerformanceObj = PerformancePerExample( example=example_inst, user=request.user, easiness_factor=2.5, last_interval=1, date_last_studied=datetime.date.today(), due_date=datetime.date.today()) else: PerformanceObj = PerformancePerExample.objects.get( example=example_inst, user=request.user) PerformanceObj.update_interval(average_score) # after update, determine which example is due next pk = Example.objects.annotate( due=Min('performanceperexample__due_date', filter=Q(performanceperexample__user_id=request.user.pk ))).filter(verb=verb).order_by( F('due').asc(nulls_first=True))[0].pk """ If the user has previously studied the next example, send them right into the quiz; otherwise, send them to the study page before the quiz """ if PerformancePerExample.objects.filter( pk=pk, user=request.user).exists(): not_studied = 0 else: not_studied = 1 try: request.session['quiz_counter'] += 1 except KeyError: request.session['quiz_counter'] = 1 if request.session['quiz_counter'] >= 3: request.session['quiz_state'] += 1 else: request.session['quiz_state'] = 0 print(request.session['quiz_state'], request.session['quiz_counter']) request.session.modified = True return render(request, 'ruskeyverbs/answer_evaluation.html', context={ 'pk': pk, 'score': score, 'user_input': user_input, 'answer': example_inst.russian_text.replace( stress_mark, ''), 'russian_text': example_inst.russian_text, 'quiz_state': 4, 'verb_pk': verb_pk, 'file': example_inst.example_audio, 'not_studied': not_studied }) else: messages.warning( request, """Something went wrong. You did not enter a valid answer.""") return HttpResponseRedirect(reverse('arrange-words', args=[pk]))
def min(self): q = self.userSitesDay.aggregate(Min('dailyTime')) return self.get_data(q, 'min')
def get(self, request, slug): try: price_range_min_filter = float(request.GET['min-value']) except: price_range_min_filter = 0 try: price_range_max_filter = float(request.GET['max-value']) except: price_range_max_filter = 10 * 100 user_slug = slug category_slug = slug.split('/') parent = None root = Category.objects.all() try: for slug in category_slug[:-1]: parent = root.get(parent=parent, slug=slug) except: return HttpResponse('404 - 1') try: category = get_object_or_404(Category, parent=parent, slug=category_slug[-1]) except: product = get_object_or_404(Product, slug=category_slug[-1], is_publish=True) if product.get_product_url() != user_slug: return HttpResponse('404 - 2') form = CartAddProductForm(request.POST or None, extra={ 'slug': category_slug[-1], 'cart': Cart(request) }) return render(request, 'shop/product-virtual.html', context={ 'product': product, 'form': form }) else: products_by_category = Product.objects.filter( category__in=category.get_descendants(include_self=True), is_publish=True, price__gte=price_range_min_filter, price__lte=price_range_max_filter) context = self.get_pagination(products_by_category) context['all_category'] = Category.objects.all() context['price_range'] = Product.objects.filter( category__in=category.get_descendants(include_self=True), is_publish=True).aggregate(Min('price'), Max('price')) context['price_range']['price__min'] = str( context['price_range']['price__min']) context['price_range']['price__max'] = str( context['price_range']['price__max']) context['obj_selected_category'] = category return render(request, 'shop/shop.html', context=context)
def handle(self, *args, **options): # Loop over classes class_slugs = list(ProteinFamily.objects.filter(parent__slug="000") \ .filter(slug__startswith="00").values_list("slug")) for slug in class_slugs: print("Processing class {}".format(slug[0])) # grab all PDB-codes for this class structure_ids = list(Structure.objects.filter(protein_conformation__protein__family__slug__startswith=slug[0]) \ .values_list("pdb_code__index")) structure_ids = [x[0] for x in structure_ids] # print("Identified the following PDBs") # print(structure_ids) # Get all PDB-codes for G-protein coupled structures in this class # extra: filter 6CMO (unfit reference, see hardcoded exceptions) active_ids = list(SignprotComplex.objects.filter(structure__pdb_code__index__in=structure_ids) \ .exclude(structure__pdb_code__index="6CMO") \ .values_list("structure__pdb_code__index")) active_ids = [x[0] for x in active_ids] # flatten # print("The following PDBs are G-prot complex structures:") # print(active_ids) # Grab most inactive PDB per ligandType -> 2x46 - 6x37 distance present and <13Å (all classes) inactive_ids = list(Distance.objects.filter(distance__lt=1300) \ .filter(gn1="2x46").filter(gn2="6x37") \ .filter(structure__pdb_code__index__in=structure_ids) \ .order_by("structure__protein_conformation__protein__family__parent__parent__name", "distance") \ .distinct("structure__protein_conformation__protein__family__parent__parent__name") \ .values_list("structure__pdb_code__index")) inactive_ids = [x[0] for x in inactive_ids] if len(structure_ids) > 0 and len(active_ids) > 0 and len(inactive_ids) > 0: # print("The following PDBs are inactive state structures:") # print(inactive_ids) # create distance matrix for given structures on lower half TM + G-Prot only residues dis = Distances() dis.lower_only = True # only lower half TM + G-prot only helices dis.load_pdbs(structure_ids) distance_matrix = dis.get_distance_matrix(True, False) # normalize, but don't use the cache distance_matrix = pd.DataFrame(distance_matrix, columns=dis.pdbs, index=dis.pdbs) # # Calculate score per pdbs directly based on distance matrix # scoring_results = {} # for pdb in dis.pdbs: # print("Processing {}".format(pdb)) # min_active_distance = min(distance_matrix.loc[pdb, active_ids]) # min_inactive_distance = min(distance_matrix.loc[pdb, inactive_ids]) # # scoring_results[pdb] = min_active_distance-min_inactive_distance # hierarchical clustering -> create distance matrix from tree hclust = sch.linkage(ssd.squareform(distance_matrix), method='average') tree = sch.to_tree(hclust, False) tree_distance = getDistanceMatrix(tree, dis.pdbs) finalMap = {} for d in tree_distance: finalMap.update(d) # Calculate score per pdbs scoring_results = {} for pdb in dis.pdbs: # print("Processing {}".format(pdb)) min_active_distance = mean([ finalMap[pdb+"_"+x] for x in active_ids ]) min_inactive_distance = mean([ finalMap[pdb+"_"+x] for x in inactive_ids ]) scoring_results[pdb] = min_active_distance-min_inactive_distance # print("{}|{}|{}|{}".format(pdb, scoring_results[pdb], min_active_distance, min_inactive_distance)) # Hardcoded annotations hardcoded = { "6CMO" : "active", # Complex with G prot - irregular conformation "5ZKP" : "unknown" # Unknown state (auto-inhibited with H8?) } distances = list(Distance.objects.filter(gn1="2x46").filter(gn2="6x37") \ .filter(structure__pdb_code__index__in=structure_ids) \ .distinct("gns_pair", "structure") \ .values_list("structure__pdb_code__index", "distance")) range_distance = Distance.objects.filter(gn1="2x46").filter(gn2="6x37") \ .filter(structure__pdb_code__index__in=structure_ids) \ .aggregate(Max('distance'), Min('distance')) min_open = range_distance['distance__min'] max_open = range_distance['distance__max'] # find smallest distance between any active structure and any inactive structure lowest_inactive_distance = min([ finalMap[y+"_"+x] for y in inactive_ids for x in active_ids ]) for entry in distances: # Percentage score percentage = int(round((entry[1]-min_open)/(max_open-min_open)*100)) if percentage < 0: percentage = 0 elif percentage > 100: percentage = 100 # Classification score = scoring_results[entry[0]] structure_state = "inactive" if entry[1] >= 13: # below this distance always inactive structure if score < -0.95*lowest_inactive_distance: structure_state = "active" elif score < 0: structure_state = "intermediate" # UGLY: knowledge-based hardcoded corrections if entry[0] in hardcoded: structure_state = hardcoded[entry[0]] # Store for structure struct = Structure.objects.get(pdb_code__index=entry[0]) struct.state, created = ProteinState.objects.get_or_create(slug=structure_state, defaults={'name': structure_state.capitalize()}) struct.tm6_angle = percentage struct.save() #print("Class {}: structure {} to state {} and opening is {}%".format(slug, entry[0], structure_state, percentage)) elif len(structure_ids) > 0: distances = list(Distance.objects.filter(gn1="2x46").filter(gn2="6x37") \ .filter(structure__pdb_code__index__in=structure_ids) \ .distinct("gns_pair", "structure") \ .values_list("structure__pdb_code__index", "distance")) range_distance = Distance.objects.filter(gn1="2x46").filter(gn2="6x37") \ .aggregate(Max('distance'), Min('distance')) min_open = range_distance['distance__min'] max_open = range_distance['distance__max'] for entry in distances: # Percentage score percentage = int(round((entry[1]-min_open)/(max_open-min_open)*100)) if percentage < 0: percentage = 0 elif percentage > 100: percentage = 100 # Store for structure struct = Structure.objects.get(pdb_code__index=entry[0]) struct.tm6_angle = percentage # Definitely an inactive state structure When distance is smaller than 13Å if entry[1] < 13: struct.state, created = ProteinState.objects.get_or_create(slug="inactive", defaults={'name': "Inactive"}) # Save changes struct.save()
def top(self): smallest_order = Folder.objects.aggregate(Min('order'))["order__min"] self.order = smallest_order - 1 self.save()
def index(request, list_all=0, page=1): """index""" errors = '' success = True active_filters = [] saved_filters = [] data = Traffic.objects filters = WebSavedFilter.objects.all().filter(user=request.user) filter_form = FilterForm() if request.method == 'POST': filter_form = FilterForm(request.POST) if filter_form.is_valid(): cleaned_data = filter_form.cleaned_data in_field = force_escape(cleaned_data['filtered_field']) in_value = force_escape(cleaned_data['filtered_value']) in_filtered_by = int(cleaned_data['filtered_by']) if not request.session.get('web_filter_by', False): request.session['web_filter_by'] = [] request.session['web_filter_by'].append({ 'field': in_field, 'filter': in_filtered_by, 'value': in_value }) else: fitem = { 'field': in_field, 'filter': in_filtered_by, 'value': in_value } if not fitem in request.session['web_filter_by']: request.session['web_filter_by'].append(fitem) request.session.modified = True else: success = False errors = _("The requested filter is already being used") filter_list = request.session.get('web_filter_by') data = gen_dynamic_query(data, filter_list, active_filters, True) else: success = False error_list = filter_form.errors.values()[0] errors = error_list[0] if request.session.get('web_filter_by', False): filter_list = request.session.get('web_filter_by') data = gen_dynamic_query(data, filter_list, active_filters, True) else: if request.session.get('web_filter_by', False): filter_list = request.session.get('web_filter_by') data = gen_dynamic_query(data, filter_list, active_filters, True) data = data.aggregate(count=Count('date'), newest=Max('date'), oldest=Min('date')) if filters.count() > 0: if request.session.get('web_filter_by', False): filter_list = request.session.get('web_filter_by') else: filter_list = [] for filt in filters: loaded = 0 if filter_list: loaded = 0 for fitem in filter_list: if fitem['filter'] == filt.op_field and ( fitem['value'] == filt.value and fitem['field'] == filt.field): loaded = 1 break saved_filters.append({ 'filter_id': filt.id, 'filter_name': force_escape(filt.name), 'is_loaded': loaded }) if request.is_ajax(): if not data['newest'] is None and not data['oldest'] is None: data['newest'] = data['newest'].strftime("%a %d %b %Y @ %H:%M %p") data['oldest'] = data['oldest'].strftime("%a %d %b %Y @ %H:%M %p") else: data['newest'] = '' data['oldest'] = '' response = anyjson.dumps({ 'success': success, 'data': data, 'errors': errors, 'active_filters': active_filters, 'saved_filters': saved_filters }) return HttpResponse( response, content_type='application/javascript; charset=utf-8') return render_to_response('web/reports/index.html', { 'form': filter_form, 'data': data, 'errors': errors, 'active_filters': active_filters, 'saved_filters': saved_filters }, context_instance=RequestContext(request))
def WriteToExcel(weather_data, town=None): output = io.StringIO() workbook = xlsxwriter.Workbook(output) worksheet_s = workbook.add_worksheet("Summary") # excel styles title = workbook.add_format({ 'bold': True, 'font_size': 14, 'align': 'center', 'valign': 'vcenter' }) header = workbook.add_format({ 'bg_color': '#F7F7F7', 'color': 'black', 'align': 'center', 'valign': 'top', 'border': 1 }) cell = workbook.add_format({ 'align': 'left', 'valign': 'top', 'text_wrap': True, 'border': 1 }) cell_center = workbook.add_format({ 'align': 'center', 'valign': 'top', 'border': 1 }) # write title if town: town_text = town.name else: town_text = ugettext("all recorded towns") title_text = "{0} {1}".format(ugettext("Weather History for"), town_text) # merge cells worksheet_s.merge_range('B2:I2', title_text, title) # write header worksheet_s.write(4, 0, ugettext("No"), header) worksheet_s.write(4, 1, ugettext("Town"), header) worksheet_s.write(4, 2, ugettext("Date"), header) worksheet_s.write(4, 3, ugettext("Description"), header) worksheet_s.write(4, 4, ugettext("Max T. (℃)"), header) worksheet_s.write(4, 5, ugettext("Min T. (℃)"), header) worksheet_s.write(4, 6, ugettext("Wind (km/h)"), header) worksheet_s.write(4, 7, ugettext("Precip. (mm)"), header) worksheet_s.write(4, 8, ugettext("Precip. (%)"), header) worksheet_s.write(4, 9, ugettext("Observations"), header) # column widths town_col_width = 10 description_col_width = 10 observations_col_width = 25 # add data to the table for idx, data in enumerate(weather_data): row = 5 + idx worksheet_s.write_number(row, 0, idx + 1, cell_center) worksheet_s.write_string(row, 1, data.town.name, cell) if len(data.town.name) > town_col_width: town_col_width = len(data.town.name) worksheet_s.write(row, 2, data.date.strftime('%d/%m/%Y'), cell_center) worksheet_s.write_string(row, 3, data.description, cell) if len(data.description) > description_col_width: description_col_width = len(data.description) worksheet_s.write_number(row, 4, data.max_temperature, cell_center) worksheet_s.write_number(row, 5, data.min_temperature, cell_center) worksheet_s.write_number(row, 6, data.wind_speed, cell_center) worksheet_s.write_number(row, 7, data.precipitation, cell_center) worksheet_s.write_number(row, 8, data.precipitation_probability, cell_center) observations = data.observations.replace('\r', '') worksheet_s.write_string(row, 9, observations, cell) observations_rows = compute_rows(observations, observations_col_width) worksheet_s.set_row(row, 15 * observations_rows) # change column widths worksheet_s.set_column('B:B', town_col_width) # Town column worksheet_s.set_column('C:C', 11) # Date column worksheet_s.set_column('D:D', description_col_width) # Description column worksheet_s.set_column('E:E', 10) # Max Temp column worksheet_s.set_column('F:F', 10) # Min Temp column worksheet_s.set_column('G:G', 10) # Wind Speed column worksheet_s.set_column('H:H', 11) # Precipitation column worksheet_s.set_column('I:I', 11) # Precipitation % column worksheet_s.set_column('J:J', observations_col_width) # Observations column row = row + 1 # Adding some functions for the data max_temp_avg = Weather.objects.all().aggregate(Avg('max_temperature')) worksheet_s.write_formula(row, 4, '=average({0}{1}:{0}{2})'.format('E', 6, row), cell_center, max_temp_avg['max_temperature__avg']) min_temp_avg = Weather.objects.all().aggregate(Avg('min_temperature')) worksheet_s.write_formula(row, 5, '=average({0}{1}:{0}{2})'.format('F', 6, row), cell_center, min_temp_avg['min_temperature__avg']) wind_avg = Weather.objects.all().aggregate(Avg('wind_speed')) worksheet_s.write_formula(row, 6, '=average({0}{1}:{0}{2})'.format('G', 6, row), cell_center, wind_avg['wind_speed__avg']) precip_sum = Weather.objects.all().aggregate(Sum('precipitation')) worksheet_s.write_formula(row, 7, '=sum({0}{1}:{0}{2})'.format('H', 6, row), cell_center, precip_sum['precipitation__sum']) precip_prob_avg = Weather.objects.all() \ .aggregate(Avg('precipitation_probability')) worksheet_s.write_formula(row, 8, '=average({0}{1}:{0}{2})'.format('I', 6, row), cell_center, precip_prob_avg['precipitation_probability__avg']) # add more Sheets worksheet_c = workbook.add_worksheet("Charts") worksheet_d = workbook.add_worksheet("Chart Data") if town: towns = [town] else: towns = Town.objects.all() # Creating the Line Chart line_chart = workbook.add_chart({'type': 'line'}) # adding dates for the values dates = Weather.objects.order_by('date').filter( town=Town.objects.first()).values_list('date', flat=True) str_dates = [] for d in dates: str_dates.append(d.strftime('%d/%m/%Y')) worksheet_d.write_column('A1', str_dates) worksheet_d.set_column('A:A', 10) # add data for the line chart for idx, t in enumerate(towns): data = Weather.objects.order_by('date').filter(town=t) letter_max_t = chr(ord('B') + idx) letter_min_t = chr(ord('B') + idx + len(towns)) worksheet_d.write_column( "{0}1".format(letter_max_t), data.values_list('max_temperature', flat=True)) worksheet_d.write_column( "{0}1".format(letter_min_t), data.values_list('min_temperature', flat=True)) # add data to line chart series line_chart.add_series({ 'categories': '=Chart Data!$A1:$A${0}'.format(len(dates)), 'values': '=Chart Data!${0}${1}:${0}${2}' .format(letter_max_t, 1, len(data)), 'marker': {'type': 'square'}, 'name': "{0} {1}".format(ugettext("Max T."), t.name) }) line_chart.add_series({ 'categories': '=Chart Data!$A1:$A${0}'.format(len(dates)), 'values': '=Chart Data!${0}${1}:${0}${2}' .format(letter_min_t, 1, len(data)), 'marker': {'type': 'circle'}, 'name': "{0} {1}".format(ugettext("Min T."), t.name) }) # adding other options line_chart.set_title({'name': ugettext("Maximum and Minimum Temperatures")}) line_chart.set_x_axis({ 'text_axis': True, 'date_axis': False }) line_chart.set_y_axis({ 'num_format': '## ℃' }) # Insert Chart to "Charts" Sheet worksheet_c.insert_chart('B2', line_chart, {'x_scale': 2, 'y_scale': 1}) # Creating the column chart bar_chart = workbook.add_chart({'type': 'column'}) # creating data for column chart cell_index = len(towns) * 2 + 2 for idx, t in enumerate(towns): max_wind = Weather.objects.filter(town=t).aggregate(Max('wind_speed')) min_wind = Weather.objects.filter(town=t).aggregate(Min('wind_speed')) worksheet_d.write_string(idx, cell_index, t.name) worksheet_d.write_number( idx, cell_index + 1, max_wind['wind_speed__max']) worksheet_d.write_number( idx, cell_index + 2, min_wind['wind_speed__min']) # add series bar_chart.add_series({ 'name': 'Max Speed', 'values': '=Chart Data!${0}${1}:${0}${2}' .format(chr(ord('A') + cell_index + 1), 1, len(towns)), 'categories': '=Chart Data!${0}${1}:${0}${2}' .format(chr(ord('A') + cell_index), 1, len(towns)), 'data_labels': {'value': True, 'num_format': '#0 "km/h"'} }) bar_chart.add_series({ 'name': 'Min Speed', 'values': '=Chart Data!${0}${1}:${0}${2}' .format(chr(ord('A') + cell_index + 2), 1, len(towns)), 'categories': '=Chart Data!${0}${1}:${0}${2}' .format(chr(ord('A') + cell_index), 1, len(towns)), 'data_labels': {'value': True, 'num_format': '#0 "km/h"'} }) # adding other options bar_chart.set_title({'name': ugettext("Maximum and minimum wind speeds")}) worksheet_c.insert_chart('B20', bar_chart, {'x_scale': 1, 'y_scale': 1}) # Creating the pie chart pie_chart = workbook.add_chart({'type': 'pie'}) # creating data for pie chart pie_values = [] pie_values.append(Weather.objects.filter(max_temperature__gt=20).count()) pie_values.append(Weather.objects.filter(max_temperature__lte=20, max_temperature__gte=10).count()) pie_values.append(Weather.objects.filter(max_temperature__lt=10).count()) pie_categories = ["T >18", "10 < T < 18", "T < 10"] # adding the data to "Chart Data" Sheet cell_index = cell_index + 4 worksheet_d.write_column("{0}1".format(chr(ord('A') + cell_index)), pie_values) worksheet_d.write_column("{0}1".format(chr(ord('A') + cell_index + 1)), pie_categories) # adding the data to the chart pie_chart.add_series({ 'name': ugettext('Temperature statistics'), 'values': '=Chart Data!${0}${1}:${0}${2}' .format(chr(ord('A') + cell_index), 1, 3), 'categories': '=Chart Data!${0}${1}:${0}${2}' .format(chr(ord('A') + cell_index + 1), 1, 3), 'data_labels': {'percentage': True} }) # insert the chart on "Charts" Sheet worksheet_c.insert_chart('J20', pie_chart) # close workbook workbook.close() xlsx_data = output.getvalue() return xlsx_data
def recalculate_concepts(self, concepts, lang=None): """ Recalculated given concepts for given users Args: concepts (dict): user id (int -> set of concepts to recalculate) lang(Optional[str]): language used to get items in all concepts (cached). Defaults to None, in that case are get items only in used concepts """ if len(concepts) == 0: return if lang is None: items = Concept.objects.get_concept_item_mapping( concepts=Concept.objects.filter( pk__in=set(flatten(concepts.values())))) else: items = Concept.objects.get_concept_item_mapping(lang=lang) environment = get_environment() mastery_threshold = get_mastery_trashold() for user, concepts in concepts.items(): all_items = list(set(flatten([items[c] for c in concepts]))) answer_counts = dict( list( zip( all_items, environment.number_of_answers_more_items( all_items, user)))) correct_answer_counts = dict( list( zip( all_items, environment.number_of_correct_answers_more_items( all_items, user)))) predictions = dict( list( zip( all_items, get_predictive_model().predict_more_items(environment, user, all_items, time=None)))) new_user_stats = [] stats_to_delete_condition = Q() for concept in concepts: answer_aggregates = Answer.objects.filter( user=user, item__in=items[concept]).aggregate( time_spent=Sum("response_time"), sessions=Count("session", True), time_first=Min("time"), time_last=Max("time"), ) stats = { "answer_count": sum(answer_counts[i] for i in items[concept]), "correct_answer_count": sum(correct_answer_counts[i] for i in items[concept]), "item_count": len(items[concept]), "practiced_items_count": sum([answer_counts[i] > 0 for i in items[concept]]), "mastered_items_count": sum([ predictions[i] >= mastery_threshold for i in items[concept] ]), "prediction": sum([predictions[i] for i in items[concept]]) / len(items[concept]), "time_spent": answer_aggregates["time_spent"] / 1000, "session_count": answer_aggregates["sessions"], "time_first": answer_aggregates["time_first"].timestamp(), "time_last": answer_aggregates["time_last"].timestamp(), } stats_to_delete_condition |= Q(user=user, concept=concept) for stat_name, value in stats.items(): new_user_stats.append( UserStat(user_id=user, concept_id=concept, stat=stat_name, value=value)) self.filter(stats_to_delete_condition).delete() self.bulk_create(new_user_stats)
def get_context_data(self, **kwargs): context = super(EventsView, self).get_context_data(**kwargs) # Get year range for datepicker. aggregates = Event.objects.aggregate(Min('start_time'), Max('start_time')) context['year_range_min'] = aggregates['start_time__min'].year context['year_range_max'] = aggregates['start_time__max'].year # Did the user set date boundaries? date_str = self.request.GET.get('form_datetime') day_grouper = lambda x: (x.start_time.year, x.start_time.month, x. start_time.day) context['select_date'] = '' # If yes, then filter for dates. if date_str: context['date'] = date_str date_time = parser.parse(date_str) select_events = Event.objects.filter(start_time__gt=date_time)\ .filter(start_time__lt=(date_time + relativedelta(months=1)))\ .order_by('start_time') org_select_events = [] for event_date, events in itertools.groupby(select_events, key=day_grouper): events = sorted(events, key=attrgetter('start_time')) org_select_events.append([date(*event_date), events]) context['select_events'] = org_select_events context['select_date'] = date_time.strftime( "%B") + " " + date_time.strftime("%Y") # If no, then return upcoming events. else: # Upcoming events for the current month. upcoming_events = Event.objects.filter(start_time__gt=timezone.now())\ .filter(start_time__lt=(timezone.now() + relativedelta(months=1)))\ .order_by('start_time') if len(upcoming_events) < 3: # Upcoming events for the next month, plus two or three from previous months. upcoming_events = Event.objects.filter(start_time__gt=timezone.now())\ .filter(start_time__lt=(timezone.now() + relativedelta(months=2)))\ .order_by('start_time') org_upcoming_events = [] for event_date, events in itertools.groupby(upcoming_events, key=day_grouper): events = sorted(events, key=attrgetter('start_time')) org_upcoming_events.append([date(*event_date), events]) context['upcoming_events'] = org_upcoming_events context['user_subscribed'] = False if self.request.user.is_authenticated(): user = self.request.user context['user'] = user if settings.USING_NOTIFICATIONS: if (len(user.eventssubscriptions.all()) > 0): context['user_subscribed'] = True return context
def get(self, request): context = { "states": craw_covid()["county"], } context["states"].sort() print(context) data = None state = request.GET.get('state') print(state) if state and state != "All States": data = CountyCase.objects.filter(county__stateName=state) if ( state != "All States") else CountyCase.objects.all() fromDate = request.GET.get('from') print(fromDate) if fromDate: data = data.filter(date__gte=fromDate) toDate = request.GET.get('to') print(toDate) if toDate: data = data.filter(date__lte=toDate) if data: data = data.order_by('county__stateName', '-date') mode = request.GET.get('mode') print(mode) if fromDate and mode == "Increment": print( data.values('county__countyName', "cases", "date").order_by('county__countyName')) data = data.values('county__countyName') \ .annotate(cases_from=Min('cases'), cases_to=Max('cases'), deaths_from=Min('deaths'), deaths_to=Max('deaths')) print(data) data_formatted = [{ 'county__countyName': row['county__countyName'], 'cases': row['cases_to'] - row['cases_from'], 'deaths': row['deaths_to'] - row['deaths_from'], } for row in data] data = sorted(data_formatted, key=lambda row: -row['cases']) elif mode in ["SUM", "Increment"]: data = data.values('county__countyName') \ .annotate(cases=Max('cases'), deaths=Max('deaths')) \ .order_by('-cases') elif mode in ["plot", "plot top-5"]: data = data.values('county__countyName', 'cases', 'deaths', 'date')\ .order_by('county__countyName', 'date') print(data) traces = [] current_county = None dates = [] cases = [] deaths = [] for row in data: if not current_county: current_county = row["county__countyName"] elif current_county != row["county__countyName"]: #save traces.append((go.Scatter( x=dates, y=cases, name=current_county, ), cases[-1])) current_county = None dates = [] cases = [] deaths = [] current_county = row["county__countyName"] dates.append(row["date"]) cases.append(row["cases"]) traces.append((go.Scatter( x=dates, y=cases, name=current_county, ), cases[-1])) if mode == "plot top-5": print(traces) traces = sorted(traces, key=lambda trace: -trace[1]) print(traces) traces = traces[:5] traces = [trace[0] for trace in traces] print(traces) context["plot"] = plot(go.Data(traces), output_type='div') data = None else: date = None context["data"] = data return render(request, 'finalProject507/data.html', context)
def _execute_seen_stats_query(self, item_list, start=None, end=None, conditions=None, environment_ids=None): project_ids = list(set([item.project_id for item in item_list])) group_ids = [item.id for item in item_list] aggregations = [ ["count()", "", "times_seen"], ["min", "timestamp", "first_seen"], ["max", "timestamp", "last_seen"], ["uniq", "tags[sentry:user]", "count"], ] filters = {"project_id": project_ids, "group_id": group_ids} if self.environment_ids: filters["environment"] = self.environment_ids result = snuba.aliased_query( dataset=snuba.Dataset.Events, start=start, end=end, groupby=["group_id"], conditions=conditions, filter_keys=filters, aggregations=aggregations, referrer= "serializers.GroupSerializerSnuba._execute_seen_stats_query", ) seen_data = { issue["group_id"]: fix_tag_value_data( dict( filter(lambda key: key[0] != "group_id", six.iteritems(issue)))) for issue in result["data"] } user_counts = { item_id: value["count"] for item_id, value in seen_data.items() } last_seen = { item_id: value["last_seen"] for item_id, value in seen_data.items() } if start or end or conditions: first_seen = { item_id: value["first_seen"] for item_id, value in seen_data.items() } times_seen = { item_id: value["times_seen"] for item_id, value in seen_data.items() } else: if environment_ids: first_seen = { ge["group_id"]: ge["first_seen__min"] for ge in GroupEnvironment.objects.filter( group_id__in=[item.id for item in item_list], environment_id__in=environment_ids, ).values("group_id").annotate(Min("first_seen")) } else: first_seen = {item.id: item.first_seen for item in item_list} times_seen = {item.id: item.times_seen for item in item_list} attrs = {} for item in item_list: attrs[item] = { "times_seen": times_seen.get(item.id, 0), "first_seen": first_seen.get(item.id), "last_seen": last_seen.get(item.id), "user_count": user_counts.get(item.id, 0), } return attrs
def list(self, request): nodes = [] # Loop through the last active nodes for last_active in LastActiveNodes.objects.iterator(): # Get the current node node = Node.objects.filter( Q(id=last_active.node.id), ~Q(sensors=None) ).get() # The last acive date last_data_received_at = last_active.last_data_received_at # last_data_received_at stats = [] moved_to = None # Get data stats from 5mins before last_data_received_at if last_data_received_at: last_5_mins = last_data_received_at - datetime.timedelta(minutes=5) stats = ( SensorDataValue.objects.filter( Q(sensordata__sensor__node=last_active.node.id), Q(sensordata__location=last_active.location.id), Q(sensordata__timestamp__gte=last_5_mins), Q(sensordata__timestamp__lte=last_data_received_at), # Ignore timestamp values ~Q(value_type="timestamp"), # Match only valid float text Q(value__regex=r"^\-?\d+(\.?\d+)?$"), ) .order_by() .values("value_type") .annotate( sensor_id=F("sensordata__sensor__id"), start_datetime=Min("sensordata__timestamp"), end_datetime=Max("sensordata__timestamp"), average=Avg(Cast("value", FloatField())), minimum=Min(Cast("value", FloatField())), maximum=Max(Cast("value", FloatField())), ) ) # If the last_active node location is not same as current node location # then the node has moved locations since it was last active if last_active.location.id != node.location.id: moved_to = { "name": node.location.location, "longitude": node.location.longitude, "latitude": node.location.latitude, "city": { "name": node.location.city, "slug": slugify(node.location.city), }, } nodes.append( { "node_moved": moved_to is not None, "moved_to": moved_to, "node": {"uid": last_active.node.uid, "id": last_active.node.id, "owner": last_active.node.owner.id}, "location": { "name": last_active.location.location, "longitude": last_active.location.longitude, "latitude": last_active.location.latitude, "city": { "name": last_active.location.city, "slug": slugify(last_active.location.city), }, }, "last_data_received_at": last_data_received_at, "stats": stats, } ) return Response(nodes)
def complete(request): earliest_db_str = DailyData.objects.all().aggregate( Min("date")).get('date__min').strftime('%d/%m/%Y') latest_db_str = DailyData.objects.all().aggregate( Max("date")).get('date__max').strftime('%d/%m/%Y') context = {'start_db': earliest_db_str, 'end_db': latest_db_str} form_regressors = ClimaticIndex.objects.all().values('id', 'name') context['form_regressors'] = form_regressors if request.method == "POST" and request.POST.get('startDate') is not None: start_date_str = request.POST.get('startDate') end_date_str = request.POST.get('endDate') start_date = datetime.strptime(start_date_str, "%d/%m/%Y").date() end_date = datetime.strptime(end_date_str, "%d/%m/%Y").date() # Creation of lists of vintages vintages = get_vintages_list(start_date, end_date) if vintages is None: context[ 'err_msg'] = "Dati sulle annate mancanti tra il " + start_date_str + " e il " + end_date_str return render(request, "MontalcinoForecastsApp/complete.html", context) # Data array creation regr_combo = get_regr_combo(request.POST) x, regr_list, failed_regr_list = get_data(start_date, end_date, regr_combo) # Error if unable to get all data (all nan or all nan except one) if len(failed_regr_list) > 0: err_msg = "Dati mancanti o non sufficienti tra il " + start_date_str + " e il " + end_date_str + \ " per poter calcolare i seguenti indici: " for regr in failed_regr_list: err_msg += regr + ", " context['err_msg'] = err_msg[:-2] + "." return render(request, "MontalcinoForecastsApp/complete.html", context) # Error if no regressor was selected if len(regr_list) is 0: context['err_msg'] = "Nessun indice selezionato" return render(request, "MontalcinoForecastsApp/complete.html", context) # Error if more regressors than data if len(vintages) <= len(regr_list): err_msg = "Pochi anni e troppi regressori, impossibile usare la regressione lineare." context['err_msg'] = err_msg return render(request, "MontalcinoForecastsApp/complete.html", context) # Linear regression x2 = sm.add_constant(x, has_constant='add') model = sm.OLS(vintages, x2) results = model.fit() vin_pred = results.predict(x2) # Chart and cards data creation chart = create_quality_chart(vintages, vin_pred) icons = get_icons(regr_combo) table_data = list( zip(["Intercetta"] + regr_list, ["fas fa-chart-line"] + icons, results.params, results.pvalues)) # Update context context['start_train_date'] = start_date_str context['end_train_date'] = end_date_str context['chart_out'] = chart.render() context['regr_count'] = len(regr_list) context['r2'] = results.rsquared context['adj_r2'] = results.rsquared_adj context['table_data'] = table_data context['anchor'] = "results-section" context['regr_combo'] = regr_combo return render(request, "MontalcinoForecastsApp/complete.html", context) else: return render(request, "MontalcinoForecastsApp/complete.html", context)
def getuseraction(request,userid): e = Action.objects.filter(userid=userid) z = e.values("token").annotate(log=Max("time"),out=Min("time")) return get_response(200,json.dumps(list(z),cls=DjangoJSONEncoder),{})
def measure(request): ''' Gets data specific to a channel/platform/measure combination ''' channel_name = request.GET.get('channel') platform_name = request.GET.get('platform') measure_name = request.GET.get('measure') interval = request.GET.get('interval') start = request.GET.get('start') relative = request.GET.get('relative') versions = request.GET.getlist('version') if not all([channel_name, platform_name, measure_name, interval]): return HttpResponseBadRequest("All of channel, platform, measure, interval required") if not all([val is None or val.isdigit() for val in (start, interval)]): return HttpResponseBadRequest( "Interval / start time must be specified in seconds (as an integer) %s" % interval) builds = Build.objects.filter(channel__name=channel_name, platform__name=platform_name) if versions: builds = builds.filter(version__in=versions) try: measure = Measure.objects.get(name=measure_name, platform__name=platform_name) except Measure.DoesNotExist: return HttpResponseNotFound("Measure not available") datums = Datum.objects.filter(build__in=builds, measure=measure) ret = {} if relative is None or (relative.isdigit() and not int(relative)): # default is to get latest data for all series datums = _filter_datums_to_time_interval(datums, start, interval) for (build_id, version, timestamp, value, usage_hours) in datums.values_list( 'build__build_id', 'build__version', 'timestamp', 'value', 'usage_hours').order_by('timestamp'): if not ret.get(build_id): ret[build_id] = { 'data': [], 'version': version } ret[build_id]['data'].append((timestamp, value, usage_hours)) else: if not versions: # if the user does not specify a list of versions, generate our # own based on the latest version with data latest_build_id = datums.filter( timestamp__gt=(datetime.datetime.now() - datetime.timedelta(days=1)) ).aggregate( Max('build__build_id'))['build__build_id__max'] if int(interval) == 0: # if interval is 0 for relative, just use the interval of the latest # released version timestamps_for_latest = datums.filter( build__build_id=latest_build_id).aggregate( Min('timestamp'), Max('timestamp')) interval = (timestamps_for_latest['timestamp__max'] - timestamps_for_latest['timestamp__min']).total_seconds() # get data for current + up to three previous versions (handling each # build id for each version, if there are multiple) versions = _sorted_version_list( [str(d[0]) for d in datums.values_list('build__version').distinct()] )[:4] version_timestamps = { (d[0], d[1]): d[2] for d in datums.filter( build__version__in=versions).values_list( 'build__version', 'build__build_id').distinct().annotate( Min('timestamp')) } # for each version/buildid combo, grab their data relative to the # latest version for (version_tuple, base_timestamp) in version_timestamps.items(): (version, build_id) = version_tuple ret[build_id] = { 'version': version, 'data': [] } if start: start_timestamp = base_timestamp + datetime.timedelta(seconds=int(start)) else: start_timestamp = base_timestamp ret[build_id]['data'] = [ [int((timestamp - base_timestamp).total_seconds()), value, usage_hours] for (timestamp, value, usage_hours) in datums.filter( build__version=version, build__build_id=build_id, timestamp__range=(start_timestamp, start_timestamp + datetime.timedelta(seconds=int(interval))) ).order_by('timestamp').values_list('timestamp', 'value', 'usage_hours')] return JsonResponse(data={'measure_data': ret})
def search(request): template = 'recommendation.html' query = request.GET.get('q') tep = "%%%s%%" % query filter_title = Director.objects.raw( "SELECT m.title AS title, d.name AS name, a.name AS star \ FROM pages_director AS d LEFT JOIN pages_movie AS m ON d.name = m.director_id \ LEFT JOIN pages_actor AS a ON a.name = m.actor_id \ WHERE m.title LIKE %s", [tep]) filter_data = Director.objects.raw( "SELECT m.title AS title, m.rating AS rating, m.votes AS votes, m.metascore AS metascore, \ m.gross_earning_in_mil AS gross, d.name AS name, d.award_win AS d_win, d.award_nom AS d_nom, \ a.name AS star, a.award_win AS a_win, a.award_nom AS a_nom \ FROM pages_director AS d LEFT JOIN pages_movie AS m ON d.name = m.director_id \ LEFT JOIN pages_actor AS a ON a.name = m.actor_id \ WHERE m.title LIKE %s", [tep]) limit_tuple = filter_data[:1] for movie in limit_tuple: title = movie.title rating = movie.rating votes = movie.votes metascore = movie.metascore gross = movie.gross d_award = movie.d_win + movie.d_nom a_award = movie.a_win + movie.a_nom d_win_max = Director.objects.all().aggregate(r1=Max('award_win')) d_win_min = Director.objects.all().aggregate(r2=Min('award_win')) d_nom_max = Director.objects.all().aggregate(r3=Max('award_nom')) d_nom_min = Director.objects.all().aggregate(r4=Min('award_nom')) d_range = d_win_max.get('r1') + d_nom_max.get('r3') - d_win_min.get( 'r2') - d_nom_min.get('r4') new_d_award = (d_award - (d_win_min.get('r2') - d_nom_min.get('r4'))) / d_range * 100 a_win_max = Actor.objects.all().aggregate(r1=Max('award_win')) a_win_min = Actor.objects.all().aggregate(r2=Min('award_win')) a_nom_max = Actor.objects.all().aggregate(r3=Max('award_nom')) a_nom_min = Actor.objects.all().aggregate(r4=Min('award_nom')) a_range = a_win_max.get('r1') + a_nom_max.get('r3') - a_win_min.get( 'r2') - a_nom_min.get('r4') new_a_award = (a_award - (a_win_min.get('r2') - a_nom_min.get('r4'))) / a_range * 100 rating_max = Movie.objects.all().aggregate(rm1=Max('rating')) rating_min = Movie.objects.all().aggregate(rm2=Min('rating')) rating_range = rating_max.get('rm1') - rating_min.get('rm2') new_rating = (rating - rating_min.get('rm2')) / rating_range * 100 votes_max = Movie.objects.all().aggregate(rm1=Max('votes')) votes_min = Movie.objects.all().aggregate(rm2=Min('votes')) votes_range = votes_max.get('rm1') - votes_min.get('rm2') new_votes = (votes - votes_min.get('rm2')) / votes_range * 100 metascore_max = Movie.objects.all().aggregate(rm1=Max('metascore')) metascore_min = Movie.objects.all().aggregate(rm2=Min('metascore')) metascore_range = metascore_max.get('rm1') - metascore_min.get('rm2') new_metascore = (metascore - metascore_min.get('rm2')) / metascore_range * 100 gross_max = Movie.objects.all().aggregate(rm1=Max('gross_earning_in_mil')) gross_min = Movie.objects.all().aggregate(rm2=Min('gross_earning_in_mil')) gross_range = gross_max.get('rm1') - gross_min.get('rm2') new_gross = (gross - gross_min.get('rm2')) / gross_range * 100 context = { 'filter_title': filter_title, 'limit_tuple': limit_tuple, 'new_rating': new_rating, 'new_votes': new_votes, 'new_d_award': new_d_award, 'new_a_award': new_a_award, 'ew_metascoren': new_metascore, 'new_gross': new_gross } return render(request, template, context)
# How to do aggregation with filter in django from django.db.models import Min models.ServerName.objects.filter(server_id=ServerName.id).aggregate( Min('time'))
def get_queryset(self, *args, **kwargs): qs = super(TriggerCRUDL.List, self).get_queryset(*args, **kwargs) qs = qs.filter(is_active=True, is_archived=False).annotate( earliest_group=Min('groups__name')).order_by( 'keyword', 'earliest_group') return qs
def action_report(request, test_id, action_id): ''' Generate HTML page with detail data about some action which were execute during the test ''' action_aggregate_data = list( TestActionAggregateData.objects.annotate( test_name=F('test__display_name')).filter( action_id=action_id, test_id__lte=test_id).values( 'test_name', 'data').order_by('-test__start_time'))[:5] action_data = [] for e in action_aggregate_data: data = e['data'] mean = data['mean'] min = data['min'] max = data['max'] q3 = data['75%'] q2 = data['50%'] std = data['std'] if '25%' in data: q1 = data['25%'] else: # WTF lol q1 = q2 - (q3 - q2) IQR = q3 - q1 LW = q1 - 1.5 * IQR if LW < 0: LW = 0.1 UW = q3 + 1.5 * IQR test_name = e['test_name'] action_data.append({ "q1": q1, "q2": q2, "q3": q3, "IQR": IQR, "LW": LW, "UW": UW, "mean": mean, "min": min, "max": max, "std": std, "test_name": test_name }) test_start_time = TestActionData.objects. \ filter(test_id=test_id, data_resolution_id=1). \ aggregate(min_timestamp=Min( RawSQL("((data->>%s)::timestamp)", ('timestamp',))))['min_timestamp'] test_errors = TestError.objects.annotate(text=F('error__text'), code=F('error__code')).filter( test_id=test_id, action_id=action_id).values( 'text', 'code', 'count') return render( request, 'action_report.html', { 'test_id': test_id, 'action': Action.objects.get(id=action_id), 'action_data': action_data, 'test_start_time': test_start_time, 'test_errors': test_errors, })