def monthly_booking_stats(request): results = Booking.objects.annotate( month=ExtractMonth('created'), year=ExtractYear('created')).values( 'month', 'year').annotate(count=Count('id')).order_by( 'year', 'month').values('month', 'year', 'count') return Response(results, status=200)
def get(self, request, *args, **kwargs): card = self.request.GET.get("card") filter_by = self.request.GET.get("filter_by") if request.user.is_authenticated: if request.org: self.access_level = "local" elif request.user.is_superuser or request.user.groups.filter( name="Global Viewers"): self.access_level = "global" response = dict() response["time_ago"] = Dashboard.get_text_time_ago(filter_by) if card == "sdg_tracked": questions = PollQuestion.objects.filter(is_active=True, poll__is_active=True) if self.access_level == "local": questions = questions.filter(poll__org=self.request.org) if filter_by is None: filter_by = "year" sdg_tracked_questions = questions if filter_by in ["week", "month", "year"]: sdg_tracked_questions = Dashboard.questions_filter( questions, created_on=filter_by) response["filter_by"] = filter_by response[ "sdgs_bubble_data"] = Dashboard.get_sdgs_tracked_bubble_chart_data( sdg_tracked_questions) if card == "partial_results": survey_result_sdg = self.request.GET.get("sdg") questions = PollQuestion.objects.filter(is_active=True, poll__is_active=True) if self.access_level == "local": questions = questions.filter(poll__org=self.request.org) survey_result_sdg_questions = questions.filter( poll__poll_end_date=datetime.datetime.utcnow().replace( tzinfo=utc)) if survey_result_sdg is None: survey_result_sdg_questions = questions else: survey_result_sdg = int(survey_result_sdg) survey_result_sdg_questions = questions.filter( sdgs__contains=[survey_result_sdg]) response["survey_result_sdg"] = settings.SDG_LIST[ survey_result_sdg - 1] survey_result_sdg_questions = [ q for q in survey_result_sdg_questions if q.get_responded() > 0 ] survey_result_sdg_questions = list(survey_result_sdg_questions) random.shuffle(survey_result_sdg_questions) questions = [] for question in survey_result_sdg_questions[:20]: word_cloud = [] statistics = {} if question.is_open_ended(): for category in question.get_results()[0].get( "categories"): count = category.get("count") word_cloud.append({ "text": category.get("label").upper(), "size": count if count > 10 else 20 + count }) else: labels = [] series = [] counts = [] results = question.get_results()[0] categories = results.get("categories") for category in categories: labels.append(category.get("label")) series.append("{0:.0f}".format( category.get("count") / results.get("set") * 100)) counts.append(category.get("count")) statistics["labels"] = labels statistics["series"] = series statistics["counts"] = counts questions.append({ "id": question.pk, "title": question.title, "url": reverse("results.poll_read", args=[question.poll.pk]), "is_open_ended": question.is_open_ended(), "word_cloud": word_cloud, "statistics": statistics, }) response["questions_count"] = len(questions) response["questions"] = questions if card == "message_metrics": channel_type = self.request.GET.get("type") channels = ChannelStats.objects.all().order_by("channel_type") if self.access_level == "local": channels = channels.filter(org=self.request.org) channels_info = {} channels_data = {} for channel in channels: channels_info[channel.channel_type] = { "type": channel.channel_type, "name": Dashboard.channel_info(channel.channel_type, "name"), "icon": Dashboard.channel_info(channel.channel_type, "icon"), } response["channels_info"] = channels_info for channel in channels: total = ChannelDailyStats.objects.filter( channel=channel, **Dashboard.filter_by_date( "date", filter_by)).aggregate(total=Sum("count"))["total"] global_total = ChannelDailyStats.objects.filter( channel__channel_type=channel.channel_type, **Dashboard.filter_by_date( "date", filter_by)).aggregate(total=Sum("count"))["total"] if channel.channel_type not in channels_data: channels_data[channel.channel_type] = { "type": channel.channel_type, "name": Dashboard.channel_info(channel.channel_type, "name"), "icon": Dashboard.channel_info(channel.channel_type, "icon"), "total": 0, "global": global_total if global_total is not None else 0, } channels_data[channel.channel_type][ "total"] += total if total is not None else 0 if channel_type: channels = channels.filter(channel_type=channel_type) channels_stats = ChannelDailyStats.objects.filter( channel__in=channels, msg_direction__in=["I", "O", "E"], msg_type__in=["M", "I", "E"], **Dashboard.filter_by_date("date", filter_by), ).order_by("date") if self.access_level == "local": channels_stats = channels_stats.filter( channel__org=self.request.org) labels = [] series = {"O": {}, "I": {}, "E": {}} key = "" for stats in channels_stats: if filter_by == "year" or filter_by == "": key = stats.date.strftime("%B/%Y") else: key = stats.date.strftime("%d/%m") if key not in series["O"]: series["O"][key] = 0 if key not in series["I"]: series["I"][key] = 0 if key not in series["E"]: series["E"][key] = 0 labels.append(key) series[stats.msg_direction][key] += stats.count response["channels_stats"] = { "labels": list(dict.fromkeys(labels)), "series": series } response["channels_data"] = channels_data response["channel_type"] = channel_type response["filter_by"] = filter_by if card == "channel_most_used": most_used = ChannelDailyStats.objects.filter( msg_direction__in=["I", "O"], msg_type__in=["M", "I"], **Dashboard.filter_by_date("date", filter_by)) if self.access_level == "local": most_used = most_used.filter(channel__org=self.request.org) most_used = ( most_used.filter().values("channel__channel_type").annotate( total=Sum("count")).order_by("-total")[:3]) most_used_global = (ChannelDailyStats.objects.filter( msg_direction__in=["I", "O"], msg_type__in=["M", "I"], **Dashboard.filter_by_date("date", filter_by), ).values("channel__channel_type").annotate( total=Sum("count")).order_by("-total")[:3]) channels_most_used = [] channels_most_used_global = [] for channel in most_used: channels_most_used.append({ "name": Dashboard.channel_info( channel.get("channel__channel_type"), "name"), "total": channel.get("total", 0), }) for channel in most_used_global: channels_most_used_global.append({ "name": Dashboard.channel_info( channel.get("channel__channel_type"), "name"), "total": channel.get("total", 0), }) response["channels_most_used"] = channels_most_used response["channels_most_used_global"] = channels_most_used_global if card == "rapidpro_contacts": contacts_over_time = (Contact.objects.filter( registered_on__gte=datetime.datetime.utcnow().replace( tzinfo=utc) - datetime.timedelta(days=180)).annotate( month=ExtractMonth("registered_on"), year=ExtractYear("registered_on")).order_by( "year", "month").values( "month", "year").annotate(total=Count("*")).values( "month", "year", "total", "org")) labels = [] series = {"local": {}, "global": {}} key = "" for contact in contacts_over_time: key = calendar.month_name[contact.get("month")] labels.append(key) if key not in series["local"]: series["local"][key] = 0 if key not in series["global"]: series["global"][key] = 0 scope = "global" if self.request.org and contact.get( "org") == self.request.org.id: scope = "local" if series["global"].get(key): series["global"][key] += contact.get("total") else: series[scope][key] = contact.get("total") response["contacts_over_time"] = { "labels": list(dict.fromkeys(labels)), "series": series } response["global_total_contacts"] = { "local": Contact.objects.filter(org=self.request.org).count(), "global": Contact.objects.all().count(), } return JsonResponse(response)
def post(self, request): """Return all budget function/subfunction titles matching the provided search text""" json_request = request.data group = json_request.get('group', None) filters = json_request.get('filters', None) subawards = json_request.get('subawards', False) if group is None: raise InvalidParameterException('Missing one or more required request parameters: group') if filters is None: raise InvalidParameterException('Missing one or more required request parameters: filters') potential_groups = ['quarter', 'fiscal_year', 'month', 'fy', 'q', 'm'] if group not in potential_groups: raise InvalidParameterException('group does not have a valid value') if type(subawards) is not bool: raise InvalidParameterException('subawards does not have a valid value') # define what values are needed in the sql query # we do not use matviews for Subaward filtering, just the Subaward download filters queryset = subaward_filter(filters) if subawards else spending_over_time(filters) \ .values('action_date', 'federal_action_obligation', 'original_loan_subsidy_cost') # build response response = {'group': group, 'results': []} nested_order = '' # list of time_period objects ie {"fy": "2017", "quarter": "3"} : 1000 group_results = OrderedDict() # for Subawards we extract data from action_date, for Awards we use sum_transaction_amount if subawards: data_set = queryset.values('award_type'). \ annotate(month=ExtractMonth('action_date'), year=ExtractYear('action_date'), transaction_amount=Sum('amount')). \ values('month', 'year', 'transaction_amount') else: data_set = queryset.values('fiscal_year') if not (group == 'fy' or group == 'fiscal_year'): # quarterly also takes months and aggregates the data data_set = queryset.annotate(month=ExtractMonth('action_date')).values('fiscal_year', 'month') filter_types = filters['award_type_codes'] if 'award_type_codes' in filters else award_type_mapping data_set = sum_transaction_amount(data_set, filter_types=filter_types) for record in data_set: # create fiscal year data based on the action_date for Subawards if subawards: record['fiscal_year'] = generate_fiscal_year(date(record['year'], record['month'], 1)) # generate unique key by fiscal date, depending on group key = {'fiscal_year': str(record['fiscal_year'])} if group == 'm' or group == 'month': # generate the fiscal month key['month'] = generate_fiscal_month(date(year=2017, day=1, month=record['month'])) nested_order = 'month' elif group == 'q' or group == 'quarter': # generate the fiscal quarter key['quarter'] = FiscalDate(2017, record['month'], 1).quarter nested_order = 'quarter' key = str(key) # if key exists, aggregate if group_results.get(key) is None: group_results[key] = record['transaction_amount'] else: group_results[key] = group_results.get(key) + record['transaction_amount'] # convert result into expected format, sort by key to meet front-end specs results = [] # Expected results structure # [{ # 'time_period': {'fy': '2017', 'quarter': '3'}, # 'aggregated_amount': '200000000' # }] sorted_group_results = sorted( group_results.items(), key=lambda k: ( ast.literal_eval(k[0])['fiscal_year'], int(ast.literal_eval(k[0])[nested_order])) if nested_order else (ast.literal_eval(k[0])['fiscal_year'])) for key, value in sorted_group_results: key_dict = ast.literal_eval(key) result = {'time_period': key_dict, 'aggregated_amount': float(value) if value else float(0)} results.append(result) response['results'] = results return Response(response)
def AnalyticsBoard(request): filename =request.session.get('saved_file') clientname =request.session.get('clientname') engangement =request.session.get('engangement') context={} context['logo']=request.session.get("logo") context['filename']=filename context['clientname']=clientname context['username']=request.session.get('username') context['customer']= request.session.get('schema_name') context['engangement']=engangement try: context['Currency']=request.session['Currency'] except: pass with schema_context(request.session.get('schema_name')): FilterClientEng=FinalTable.Filter(clientname,engangement) # =========================================================================== # charts context['dataset']=FilterClientEng\ .values("StatusPostedUnposted")\ .annotate(survived_count=Count('StatusPostedUnposted'),not_survived_count=Count('JournalType'))\ # ==================================================================== #''' Missing Values ''' obj=FilterClientEng.order_by('SrNo') context['transaction']=obj.count() # print('========'+str(engangement)) qs=read_frame(obj) df=pd.DataFrame(qs).drop(['client','engangement','user_id','Upload_Date','id'], axis=1) # ============================================================================= df['SrNo']=df['SrNo'].astype(float) dcf=pd.DataFrame(missing_values(df['SrNo'])) dcf=dcf.count() context['missing']=dcf.to_csv(index=False) # ============================================================================== # ''' Top users number of entries / value ''' JE=FilterClientEng\ .values('CreatedBy')\ .annotate(JE=Count('CreatedBy'))\ .annotate(Credit=Cast('CreditAmount', FloatField()),Debit=Cast('DebitAmount', FloatField()))\ .annotate(Credit=Sum('Credit'),Debit=Sum('Debit')) context['JEuser']=JE # ================================================================================== # ''' Bottom users number of entries / value ''' JE=FilterClientEng\ .values('CreatedBy')\ .annotate(JE=Count('CreatedBy'))\ .annotate(Credit=Cast('CreditAmount', FloatField()),Debit=Cast('DebitAmount', FloatField()))\ .annotate(Credit=Sum('Credit'),Debit=Sum('Debit'))\ .order_by('-JE') context['JEuser']=JE # ================================================================================== # ''' Posted / Un posted - Authorised / Un Authorised JE ''' posted_unposted=FilterClientEng\ .values('StatusPostedUnposted')\ .annotate(Credit=Cast('CreditAmount', FloatField()),Debit=Cast('DebitAmount', FloatField()))\ .annotate(posted_unposted=Count('StatusPostedUnposted'),Credit=Sum('Credit'),Debit=Sum('Debit')) context['pos_unpos']=posted_unposted # ======================================================================================= # '''Manual JE / System generated JE in terms of number and value ''' Je_auto_manual=FilterClientEng\ .values('TransactionType')\ .annotate(Generated=Count('TransactionType')) context['TransactionType']=Je_auto_manual # ========================================================================== #'''created and authorized by same user''' data1=FilterClientEng\ .filter(AuthorisedBy__iregex=F('CreatedBy')) context['cre_equ_auth_count']=data1.count() # ===================================================================================== # JV passby Month Jv_month=FilterClientEng\ .annotate(Mahina=ExtractMonth("JournalDate"))\ .values('Mahina')\ .annotate(month=Count("Mahina"))\ .annotate(Credit=Cast('CreditAmount', FloatField()),Debit=Cast('DebitAmount', FloatField()))\ .annotate(Credit=Sum('Credit'),Debit=Sum('Debit'))\ .order_by('Mahina') context['Jvmonth']=Jv_month # ================================================= #JV's with related parties # JvParties=FinalTable.objects\ # .values('MainAccountName')\ # .annotate(c=Count('MainAccountName'))\ # .filter(client=clientname,engangement=engangement) # context['JvP']=JvParties # ======================================================================================= #JV's On Weekend Jvholidays=FilterClientEng\ .annotate(Week=ExtractWeekDay("JournalDate"))\ .values('Week')\ .annotate(week=Count("Week"))\ .filter(JournalDate__week_day__in=[1,7])\ .annotate(Credit=Cast('CreditAmount', FloatField()),Debit=Cast('DebitAmount', FloatField()))\ .annotate(Credit=Sum('Credit'),Debit=Sum('Debit'))\ .order_by("Week") context['JVweekend']=Jvholidays #================================================================================================= #JV's with Little Description # JvLittleDesc=FilterClientEng\ # .values('ShortText','CreatedBy')\ # .annotate(len=Length('ShortText'))\ # .filter(len__lte=25) # context['JvLittleDesc']=JvLittleDesc #========================================================================================\ # Debit-Credit Amount JVCreDebAmount=FilterClientEng\ .values('CreditAmount','DebitAmount' )\ .annotate(Credit=Cast('CreditAmount', FloatField()),Debit=Cast('DebitAmount', FloatField()))\ .aggregate(Sum('Credit'),Sum('Debit')) context['DebCre']=JVCreDebAmount # #==========================================------------------------------- return render(request,'analytics.html',context)
def get_actors_movie_count_released_in_their_birth_month(): return MovieCast.objects.annotate( release_month=ExtractMonth('movie__release_date'), birth_month=ExtractMonth('cast__birth_date')).filter( release_month=F('birth_month')).values("cast__name").annotate( movie_count=Count("movie"))
def song(request, song_id): song_name, avg_gap, show_list = Show.manager.song_appearances(song_id) # years heat calc # grab total shows per year for heat calc shows_yr = Show.manager.shows_per_year() # get times song way played per year for heat plays_yr = Show.objects.filter( showsong__song_id=song_id).annotate( year=ExtractYear('date__year')).values('year') \ .annotate(count=Count('id', distinct=Show)).values('year', 'count') # convert to tuples for calc script shows_yr = dict([tuple(d.values()) for d in shows_yr]) plays_yr = dict([tuple(d.values()) for d in plays_yr]) song_year_heat = [] # builds list of tuples for year heat map for year in shows_yr.keys(): song_year_heat.append( (year, round((plays_yr.get(year, 0) / shows_yr[year]) * 100))) # months heat calc shows_month = Show.manager.shows_per_month() plays_month = Show.objects.filter( showsong__song_id=song_id).annotate( month=ExtractMonth('date__month')).values('month') \ .annotate(count=Count('id', distinct=Show)).values('month', 'count') shows_month = dict([tuple(d.values()) for d in shows_month]) plays_month = dict([tuple(d.values()) for d in plays_month]) song_month_heat = [] # builds list of tuples for year heat map for month in shows_month.keys(): song_month_heat.append( (month, round((plays_month.get(month, 0) / shows_month[month]) * 100))) # days heat calc shows_weekday = Show.manager.shows_per_weekday() plays_weekday = Show.objects.filter( showsong__song_id=song_id).annotate( weekday=ExtractWeekDay('date__week_day')).values('weekday') \ .annotate(count=Count('id', distinct=Show)).values('weekday', 'count') shows_weekday = dict([tuple(d.values()) for d in shows_weekday]) plays_weekday = dict([tuple(d.values()) for d in plays_weekday]) song_weekday_heat = [] # builds list of tuples for year heat map for weekday in shows_weekday.keys(): song_weekday_heat.append( (weekday, round((plays_weekday.get(weekday, 0) / shows_weekday[weekday]) * 100))) # geo heat calc shows_state = Show.manager.shows_per_state() print(shows_state) plays_state = Show.objects.filter( showsong__song_id=song_id).select_related('venue').values('venue__state') \ .annotate(count=Count('id', distinct=Show)).values('venue__state', 'count') shows_state = dict([tuple(d.values()) for d in shows_state]) plays_state = dict([tuple(d.values()) for d in plays_state]) song_state_heat = [] # builds list of tuples for year heat map for state in shows_state.keys(): try: song_state_heat.append( (state, round( (plays_state.get(state, 0) / shows_state[state]) * 100))) except ZeroDivisionError: pass context = { 'song_name': song_name, 'show_list': show_list, 'show_count': len(show_list), 'avg_gap': avg_gap, 'song_year_heat': sorted(song_year_heat), 'song_month_heat': sorted(song_month_heat), 'song_weekday_heat': sorted(song_weekday_heat), 'song_state_heat': sorted(song_state_heat), } return render(request, 'songs/song.html', context)
def _compute_monthly_totals(self, model_klass, provider, documents): klass_name_plural = model_klass.__name__ + 's' totals = {} totals[klass_name_plural] = OrderedDict() documents = documents.filter(provider=provider) documents_months_years = documents.order_by().annotate( month=ExtractMonth('issue_date'), year=ExtractYear('issue_date'), ).filter( provider=provider ).values_list( 'month', 'year' ).distinct() totals[klass_name_plural]['entries'] = OrderedDict() documents_years_months = sorted((year, month) for month, year in documents_months_years if month and year) documents_currencies = set( documents.filter(provider=provider).values_list("currency", flat=True).distinct() ) unpaid_documents = documents.filter(state=BillingDocumentBase.STATES.DRAFT) draft_totals = totals[klass_name_plural]['draft'] = defaultdict(Decimal) for doc in unpaid_documents: draft_totals[doc.currency] += doc.total totals[klass_name_plural]['currencies'] = documents_currencies for year_month in documents_years_months: if year_month is None: continue display_date = date(day=1, month=year_month[1], year=year_month[0]).strftime('%B %Y') totals[klass_name_plural]['entries'][display_date] = defaultdict(Decimal) documents_from_month = documents.filter( state__in=[BillingDocumentBase.STATES.ISSUED, BillingDocumentBase.STATES.PAID], issue_date__month=year_month[1], issue_date__year=year_month[0] ) totals_per_date = totals[klass_name_plural]['entries'][display_date] for doc in documents_from_month: totals_per_date['total_' + doc.currency] += doc.total if doc.state == BillingDocumentBase.STATES.ISSUED: totals_per_date['unpaid_' + doc.currency] += doc.total if doc.state == BillingDocumentBase.STATES.PAID: totals_per_date['paid_' + doc.currency] += doc.total totals[klass_name_plural]['entries'][display_date] = { total_key: str(total_value) for total_key, total_value in totals_per_date.items() } return totals
def get(self, request, *args, **kwargs): DATE_FORMAT = "%Y-%m-%d" user_events = request.user.winery.events.all() user_events_reservations = Reservation.objects.filter( event_occurrence__event__in=user_events) user_events_ratings = Rate.objects.filter(event__in=user_events) from_date = request.query_params.get('from_date') to_date = request.query_params.get('to_date') try: if from_date: from_date = datetime.strptime(from_date, DATE_FORMAT) user_events_reservations = user_events_reservations.filter( event_occurrence__start__gte=from_date) user_events_ratings = user_events_ratings.filter( created__gte=from_date) if to_date: to_date = datetime.strptime(to_date, DATE_FORMAT) user_events_reservations = user_events_reservations.filter( event_occurrence__end__lte=to_date) user_events_ratings = user_events_ratings.filter( created__lte=to_date) except (ValueError, TypeError): return Response({"errors": "Dates format must be 'YYYY-MM-DD'"}, status=status.HTTP_400_BAD_REQUEST) today = date.today() age_18_birth_year = (today - relativedelta(years=18)).year age_35_birth_year = (today - relativedelta(years=35)).year age_50_birth_year = (today - relativedelta(years=50)).year response = { "reservations_by_event": (user_events_reservations.values("event_occurrence__event__name"). annotate(name=F("event_occurrence__event__name")).annotate( count=Count("id")).values("name", "count").order_by("count")[:10]), "reservations_by_month": (user_events_reservations.annotate(month=ExtractMonth( "event_occurrence__start")).values("month").annotate( count=Count("id")).values("month", "count").order_by("month")), "attendees_languages": (user_events_reservations.values("user__language__name").annotate( language=F("user__language__name")).annotate( count=Count("id")).values("language", "count")), "attendees_countries": (user_events_reservations.values("user__country__name").annotate( country=F("user__country__name")).annotate( count=Count("id")).values("country", "count")), "attendees_age_groups": (user_events_reservations.annotate(young=Case( When(user__birth_date__year__range=(age_35_birth_year, age_18_birth_year), then=1), default=0, output_field=IntegerField())).annotate( midage=Case(When(user__birth_date__year__range=( age_50_birth_year, age_35_birth_year - 1), then=1), default=0, output_field=IntegerField())). annotate(old=Case(When( user__birth_date__year__lt=age_50_birth_year, then=1), default=0, output_field=IntegerField())).aggregate( young_sum=Sum('young'), midage_sum=Sum('midage'), old_sum=Sum('old'))), "events_by_rating": (user_events_ratings.values("event__name").annotate( avg_rating=Coalesce(Avg("rate"), 0)).annotate( name=F("event__name")).values( "name", "avg_rating").order_by("-avg_rating")[:10]), "reservations_by_earnings": (user_events_reservations.values( "event_occurrence__event__name").annotate( earnings=Sum("paid_amount")).annotate( name=F("event_occurrence__event__name")).values( "name", "earnings").order_by("-earnings")[:10]) } # Awful hack to return months with count = 0 # To achieve this with SQL, a Month table is needed :( zero_count_months = [{"month": i, "count": 0} for i in range(1, 13)] reservations = response["reservations_by_month"] for elem in reservations: zero_count_months[elem['month'] - 1].update(elem) response['reservations_by_month'] = zero_count_months response['attendees_age_groups'] = [{ "group": k.split("_")[0], "count": v } for k, v in response['attendees_age_groups'].items()] return Response(response)
def monthly_transactions(self): stats = Transaction.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( c=Count('id')).values('month', 'c') return stats
def sum_effective(**kwargs): return sum_when(**kwargs, due_date__month=ExtractMonth('date'), due_date__year=ExtractYear('date'))
def admin_dashboard(request): from datetime import date, timedelta from django.db.models import Q from info.models import AbViews today = datetime.datetime.now() user_id = request.user.id # remove it last_year = today.year - 1 allUser = AbUsers.objects.filter(created_at__year=today.year) eligableDate = (date.today() - timedelta(days=31)).isoformat() eligableDateYearly = (date.today() - timedelta(days=365)).isoformat() personalPlanMonthly = AbUsers.objects.filter( order_id=101, trans_date__gt=eligableDate).count() personalPlanYearly = AbUsers.objects.filter( order_id=102, trans_date__gt=eligableDateYearly).count() corporatePlanMonthly = AbUsers.objects.filter( order_id=201, trans_date__gt=eligableDate).count() corporatePlanYearly = AbUsers.objects.filter( order_id=201, trans_date__gt=eligableDateYearly).count() freePlan = allUser.count( ) - personalPlanMonthly - personalPlanYearly - corporatePlanMonthly - corporatePlanYearly recentAirbookers = UsersSerializer(AbUsers.objects.order_by('-id')[:10], many=True).data # thisMonth = datetime.date(today.year + int(today.month/12),today.month%12+1, 1)-datetime.timedelta(days=1) # lastTwelveMonthDate = (thisMonth - timedelta(days=365)).isoformat() # # from django.db.models import Count # from django.db.models.functions import TruncMonth , ExtractMonth # from django.conf import settings # settings.USE_TZ = False # lineChartData = AbUsers.objects.annotate(month=ExtractMonth('created_at')).values('month').annotate(users=Count('id')).filter(created_at__range=(lastTwelveMonthDate, thisMonth)) thisMonth = datetime.date(today.year, 1 % 12, 1) lastTwelveMonthDate = (thisMonth + timedelta(days=365)).isoformat() from django.db.models import Count from django.db.models.functions import TruncMonth, ExtractMonth settings.USE_TZ = False userYearlyData = AbUsers.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) companyYearlyData = AbCompanies.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) contactsYearlyData = AbContacts.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) aircraftsYearlyData = AbAircrafts.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) enginesYearlyData = AbEngines.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) apusYearlyData = AbApus.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) wantedYearlyData = AbWanteds.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) partsYearlyData = AbParts.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) airportsYearlyData = AbAirports.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) eventsYearlyData = AbEvents.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) newsYearlyData = AbNews.objects.annotate( month=ExtractMonth('created_at')).values('month').annotate( count=Count('id')).order_by('month').filter( created_at__range=(thisMonth, lastTwelveMonthDate)) activeUser = AbUsers.objects.filter(is_active=1).count() inActiveUser = AbUsers.objects.filter(is_active=0).count() activeCompany = AbCompanies.objects.filter(is_active=1).count() inActiveCompany = AbCompanies.objects.filter(is_active=0).count() activeContact = AbContacts.objects.filter(is_published=1).count() inActiveContact = AbContacts.objects.filter(is_published=0).count() activeAircraft = AbAircrafts.objects.filter(isactivestatus='Approved', is_active_by_user=1).count() inActiveAircraft = AbAircrafts.objects.filter( ~Q(isactivestatus='Approved') | Q(is_active_by_user=0)).count() activeEngine = AbEngines.objects.filter(isactivestatus='Approved', is_active_by_user=1).count() inActiveEngine = AbEngines.objects.filter( ~Q(isactivestatus='Approved') | Q(is_active_by_user=0)).count() activeApu = AbApus.objects.filter(isactivestatus='Approved', is_active_by_user=1).count() inActiveApu = AbApus.objects.filter(~Q(isactivestatus='Approved') | Q(is_active_by_user=0)).count() activeWanted = AbWanteds.objects.filter(is_active=1).count() inActiveWanted = AbWanteds.objects.filter(is_active=0).count() activePart = AbParts.objects.filter(is_active=1).count() inActivePart = AbParts.objects.filter(is_active=0).count() activeAirport = AbAirports.objects.filter(is_active=1).count() inActiveAirport = AbAirports.objects.filter(is_active=0).count() activeNews = AbNews.objects.filter(is_active=1).count() inActiveNews = AbNews.objects.filter(is_active=0).count() activeEvent = AbEvents.objects.filter(is_active=1, end_date__range=[ datetime.date( today.year, today.month, today.day), "2080-01-31" ]).count() inActiveEvent = AbEvents.objects.filter(is_active=0, end_date__range=[ datetime.date( today.year, today.month, today.day), "2080-01-31" ]).count() content = { 'pieChartData': { 'freePlan': freePlan, 'personalPlan': personalPlanMonthly + personalPlanYearly, 'corporatePlan': corporatePlanMonthly + corporatePlanYearly }, 'recentAirbookers': recentAirbookers, 'userYearlyData': userYearlyData, 'activeUser': activeUser, 'inActiveUser': inActiveUser, 'companyYearlyData': companyYearlyData, 'activeCompany': activeCompany, 'inActiveCompany': inActiveCompany, 'contactsYearlyData': contactsYearlyData, 'activeContact': activeContact, 'inActiveContact': inActiveContact, 'aircraftsYearlyData': aircraftsYearlyData, 'activeAircraft': activeAircraft, 'inActiveAircraft': inActiveAircraft, 'enginesYearlyData': enginesYearlyData, 'activeEngine': activeEngine, 'inActiveEngine': inActiveEngine, 'apusYearlyData': apusYearlyData, 'activeApu': activeApu, 'inActiveApu': inActiveApu, 'wantedYearlyData': wantedYearlyData, 'activeWanted': activeWanted, 'inActiveWanted': inActiveWanted, 'partsYearlyData': partsYearlyData, 'activePart': activePart, 'inActivePart': inActivePart, 'activeTotalAsset': activeAircraft + activeEngine + activeApu, 'inActiveTotalAsset': inActiveAircraft + inActiveEngine + inActiveApu, 'airportsYearlyData': airportsYearlyData, 'activeAirport': activeAirport, 'inActiveAirport': inActiveAirport, 'newsYearlyData': newsYearlyData, 'activeNews': activeNews, 'inActiveNews': inActiveNews, 'eventsYearlyData': eventsYearlyData, 'activeEvent': activeEvent, 'inActiveEvent': inActiveEvent, } return Response(content)
def sum_real(**kwargs): return sum_when(**kwargs, payment_date__isnull=False, payment_date__month=ExtractMonth('date'), payment_date__year=ExtractYear('date'))
def analytics_daily_data(request, year=None, month=None, day=None): year = year or timezone.now().year month = month or timezone.now().month day = day or timezone.now().day datefield = 'created_at' user_datefield = 'date_joined' yearfield = 'year' monthfield = 'month' dayfield = 'day' data = [] payment_set = Payment.objects.filter(created_at__year=year, created_at__month=month, created_at__day=day).annotate(year=ExtractYear(datefield), month=ExtractMonth(datefield), day=ExtractDay(datefield)) transfer_set = Transfer.filter(created_at__year=year, created_at__month=month, created_at__day=day).objects.annotate(year=ExtractYear(datefield), month=ExtractMonth(datefield), day=ExtractDay(datefield)) payment_request_set = PaymentRequest.objects.filter(created_at__year=year, created_at__month=month, created_at__day=day).annotate(year=ExtractYear(datefield), month=ExtractMonth(datefield), day=ExtractDay(datefield)) service_set = Service.objects.filter(created_at__year=year, created_at__month=month, created_at__day=day).annotate(year=ExtractYear(datefield), month=ExtractMonth(datefield), day=ExtractDay(datefield)) user_set = User.objects.filter(date_joined__year=year, date_joined__month=month, date_joined__day=day).annotate(year=ExtractYear(user_datefield), month=ExtractMonth(user_datefield), day=ExtractDay(user_datefield)) data.append({'label':_('Payments'), 'datasets': list(payment_set.values(yearfield, monthfield).annotate(count=Count(monthfield)))}) data.append({'label':_('Transfers'), 'datasets': list(transfer_set.values(yearfield, monthfield).annotate(count=Count(monthfield)))}) data.append({'label':_('Payments Request'), 'datasets': list(payment_request_set.values(yearfield, monthfield).annotate(count=Count(monthfield)))}) data.append({'label':_('Users'), 'datasets': list(user_set.values(yearfield, monthfield).annotate(count=Count(monthfield)))}) return Response(data, status=status.HTTP_200_OK)
def number_movies_in_birth_month(): sub = Movie.objects.filter(release_date__month=OuterRef('month'), actors=OuterRef('pk')).values('id') return Actor.objects.annotate(month=ExtractMonth('birth_date')).annotate( count=Count(Subquery(sub))).filter(~Q(count=0)).values( 'name', 'count')
def get_context_data(self, **kwargs): lista = [] contador = 0 context = super(ConstanciaUtil, self).get_context_data(**kwargs) tipos_conta = inv_m.DispositivoTipo.objects.filter(conta=True) for tipo in tipos_conta: detalles_mes = inv_m.EntradaDetalle.objects.filter(entrada=self.object.id, tipo_dispositivo=tipo).exclude(fecha_dispositivo__isnull=True).annotate(month=ExtractMonth('fecha_dispositivo')).values('month').annotate(util=Sum('util')).annotate(total=Sum('total')).values('month','total','util') for mes in detalles_mes: responsables = [] detalles_entrada = inv_m.EntradaDetalle.objects.filter(entrada=self.object.id, tipo_dispositivo=tipo, fecha_dispositivo__month=mes['month']) contador += 1 for datos in detalles_entrada: if datos.creado_por.get_full_name() not in responsables: responsables.append(datos.creado_por.get_full_name()) index = contador % 2 diccionario = { 'tipo_dispositivo': tipo.tipo, 'cantidad': mes['total'], 'util': mes['util'], 'mes': calendar.month_name[mes['month']], 'index': index, 'creado_por': ', '.join(str(x) for x in responsables), } lista.append(diccionario) context['dispositivo_tipo'] = lista return context
def list(self, request): start_month = int(request.query_params['start_month']) start_year = int(request.query_params['start_year']) end_month = int(request.query_params['end_month']) end_year = int(request.query_params['end_year']) stop1_id = int(request.query_params.get('stop1', 0)) stop2_id = int(request.query_params.get('stop2', 0)) week_days_str = request.query_params.get('days') hours_str = request.query_params.get('hours') stop1 = None stop2 = None if stop1_id: stop1 = models.Stop.objects.get(pk=stop1_id) if stop2_id: stop2 = models.Stop.objects.get(pk=stop2_id) if week_days_str: week_days = [int(d) for d in week_days_str.split(",")] if min(week_days) < 0 or max(week_days) > 6: raise exceptions.ValidationError("Days expected to be in the range [0-6]") else: week_days = None if hours_str: hours = [int(hour) for hour in hours_str.split(',')] if min(hours) < 0 or max(hours) > 23: raise exceptions.ValidationError("Hours expected to be in the range [0-23]") else: hours = None start_date = datetime.date(start_year, start_month, 1) _, num_days = calendar.monthrange(end_year, end_month) end_date = datetime.date(end_year,end_month, num_days) trips_qs = models.Trip.objects.filter( date__gte=start_date, date__lte=end_date, valid=True) if week_days: trips_qs = trips_qs.filter(x_week_day_local__in=week_days) if hours: trips_qs = trips_qs.filter(x_hour_local__in=hours) if stop1 or stop2: if stop1 and stop2: stops = [stop1, stop2] else: stops = [stop1] assert not stop2 routes = logic.get_routes_with_stops(stops) trips_qs = trips_qs.filter(route__in=routes) delays_by_month = list( trips_qs.annotate( y=ExtractYear('date'), m=ExtractMonth('date') ).values( 'y', 'm' ).annotate( count=Count('id') ).annotate( count_late_max=Sum(Case(When(x_max_delay_arrival__gte=300, then=Value(1)), default=Value(0), output_field=IntegerField())) ).annotate( count_late_last=Sum(Case(When(x_last_delay_arrival__gte=300, then=Value(1)), default=Value(0), output_field=IntegerField())) ) ) delays_by_month.sort(key=lambda x: (x['y'], x['m'])) return Response(data=delays_by_month)
def my_stats(request): shows = UserProfile.objects.get(user=request.user).shows.all() #users top ten songs top_ten_songs = shows.values('showsong__song__name').annotate( count=Count('pk', distinct=True)).order_by('-count')[:10] # returns how many distinct songs were played within the queryset song_count = shows.aggregate( song_count=Count('showsong__song_id', distinct=True)) # originals covers played count non unique originals_played_count = ShowSong.objects.filter(song__artist__name='STS9', show__in=shows).count() covers_played_count = ShowSong.objects.filter(show__in=shows).exclude( song__artist__name='STS9').count() # number of shows per weekday within queryset weekdays_distribution = \ shows.annotate( weekday=ExtractWeekDay('date__week_day')) \ .values('weekday').annotate(count=Count('id')).values('weekday', 'count') # number of shows per month within queryset months_distribution = \ shows.annotate(month=ExtractMonth('date__month')) \ .values('month').annotate(count=Count('id')).values('month', 'count') # number of shows per year within queryset shows_per_year = \ shows.annotate(year=ExtractYear('date__year')).values( 'year').annotate(count=Count('id')).values('year', 'count') shows_yr = Show.manager.shows_per_year() shows_yr = dict([tuple(d.values()) for d in shows_yr]) shows_per_year = dict([tuple(d.values()) for d in shows_per_year]) years_distribution = [] # builds list of tuples for year heat map for year in shows_yr.keys(): years_distribution.append((year, shows_per_year.get(year, 0))) geo_distribution = \ shows.values('venue__state').annotate(count=Count('id')).values( 'venue__state', 'count') context = { 'shows': shows.order_by('-date'), 'top_ten_songs': top_ten_songs, 'song_count': song_count, 'originals_played_count': originals_played_count, 'others_played_count': covers_played_count, 'weekdays_distribution': weekdays_distribution, 'months_distribution': months_distribution, 'years_distribution': sorted(years_distribution), 'geo_distribution': geo_distribution, 'stats_name': request.user.username + "'s Statistics" } return render(request, 'stats/index.html', context)
def sales_per_type(type_name, field_lookup, params): ids = params.get("id") year = params.get("year") month = params.get("month") group_by = params.get("group_by") date_end = params.get("date_end") date_start = params.get("date_start") # Format id param into list ids_list = ids.split(',') if ids else [] # Format group_by param into list group_by_params = group_by.split(',') if group_by else [type_name] # Validate group_by params if group_by_params and \ any(param not in [type_name, "customer", "day", "month", "year"] for param in group_by_params): raise ParseError( "Can only group by {0}, customer, day, month or year".format( type_name)) if "customer" in group_by_params and not ids_list: raise ParseError("Provide ID to group by customer") if "day" in group_by_params and "month" not in group_by_params and not month: raise ParseError( "Must group by both day and month when filtering by year or custom dates" ) if "month" in group_by_params and year and month: raise ParseError("Can not group by month when filtering by month") # Initial queryset queryset = database.models.InvoiceProduct.objects.totals().select_related( 'product', 'invoice') # Handle ids filter if ids_list: lookup = field_lookup + '__in' queryset = queryset.filter(**{lookup: ids_list}) # Handle date range filters if month: if not year: raise ParseError("Provide year for month {0}".format(month)) queryset = queryset.filter(invoice__date_of_sale__month=month, invoice__date_of_sale__year=year) elif year: queryset = queryset.filter(invoice__date_of_sale__year=year) elif date_start and date_end: queryset = queryset.filter(invoice__date_of_sale__range=(date_start, date_end)) else: raise ParseError("Must provide a month, year or custom date range") # Check for case where field already exists in the queryset i.e. product field if type_name != field_lookup: queryset = queryset.annotate(**{type_name: F(field_lookup)}) # Do the query queryset = queryset.annotate(customer=F('invoice__customer'), month=ExtractMonth('invoice__date_of_sale'), year=ExtractYear('invoice__date_of_sale'), day=ExtractDay('invoice__date_of_sale'))\ .annotate(product_sales=ExpressionWrapper( (F('quantity') - F('returned_quantity')) * F('sell_price'), output_field=models.DecimalField(max_digits=15, decimal_places=3)))\ .annotate(product_profit=ExpressionWrapper( (F('quantity') - F('returned_quantity')) * (F('sell_price') - F('cost_price')), output_field=models.DecimalField(max_digits=15, decimal_places=3)))\ .annotate(_sales=Sum("product_sales"))\ .annotate(_profit=Sum("product_profit"))\ .values(*group_by_params)\ .annotate(sales=F('_sales'), profit=F('_profit'), units=Sum(F('quantity') - F('returned_quantity')))\ .order_by(*group_by_params) return queryset
def stats_view(request): # set song var from URL song_name = request.GET.get('song') # build list of kwargs from url to get queryset stat_filters = { 'date__year__in': request.GET.getlist('year'), 'date__month__in': request.GET.getlist('month'), 'date__day__in': request.GET.getlist('day'), 'date__week_day__in': request.GET.getlist('weekday'), 'venue_id__in': request.GET.getlist('venue'), 'venue__city__iexact__in': request.GET.getlist('city'), 'venue__state__iexact__in': request.GET.getlist('state'), 'venue__country__iexact__in': request.GET.getlist('country'), } # only pass kwargs with values stat_filters = {k: v for k, v in stat_filters.items() if v} # only search for songs if there is a song input from url if song_name: # outeref for subquery to pass to next script to see if # song exists in show showsongs = ShowSong.objects.filter(show=OuterRef('pk'), song__name__iexact=song_name) # build queryset based on parameters shows = Show.objects.annotate( song_exists=Exists(showsongs)).filter(**stat_filters, song_exists=True) else: # if no song input from url just build queryset on everything else shows = Show.objects.filter(**stat_filters) # returns how many distinct songs were played within the queryset song_count = shows.aggregate( song_count=Count('showsong__song_id', distinct=True)) # originals covers played count non unique originals_played_count = ShowSong.objects.filter(song__artist__name='STS9', show__in=shows).count() covers_played_count = ShowSong.objects.filter(show__in=shows).exclude( song__artist__name='STS9').count() # number of shows per weekday within queryset weekdays_distribution = \ shows.annotate( weekday=ExtractWeekDay('date__week_day')) \ .values('weekday').annotate(count=Count('id')).values('weekday', 'count') # number of shows per month within queryset months_distribution = \ shows.annotate(month=ExtractMonth('date__month')) \ .values('month').annotate(count=Count('id')).values('month', 'count') # number of shows per year within queryset shows_per_year = \ shows.annotate(year=ExtractYear('date__year')).values( 'year').annotate(count=Count('id')).values('year', 'count') shows_yr = Show.manager.shows_per_year() shows_yr = dict([tuple(d.values()) for d in shows_yr]) shows_per_year = dict([tuple(d.values()) for d in shows_per_year]) years_distribution = [] # builds list of tuples for year heat map for year in shows_yr.keys(): years_distribution.append((year, shows_per_year.get(year, 0))) geo_distribution = \ shows.values('venue__state').annotate(count=Count('id')).values( 'venue__state', 'count') top_ten_songs = shows.values('showsong__song__name').annotate( count=Count('pk', distinct=True)).order_by('-count')[:10] context = { 'get_request_stats': request.GET.items(), 'shows': shows.order_by('-date'), 'top_ten_songs': top_ten_songs, 'song_count': song_count, 'originals_played_count': originals_played_count, 'others_played_count': covers_played_count, 'weekdays_distribution': weekdays_distribution, 'months_distribution': months_distribution, 'years_distribution': sorted(years_distribution), 'geo_distribution': geo_distribution, 'stats_name': 'Statistics' } return render(request, 'stats/index.html', context)
def get_queryset(self): return database.models.InvoiceProduct.objects.annotate(month=ExtractMonth('invoice__date_of_sale'))\ .values('product', 'month')\ .annotate(quantity=Sum(F('quantity') - F('returned_quantity')))
def get_date(): # dates = Post.my_objects.annotate(month=ExtractMonth("created_time"), year=ExtractYear("created_time")).annotate(total=Count("*")).filter(total__gt=0).values("month", "year", "total").order_by("-year", "-month") dates = Post.my_objects.annotate(month=ExtractMonth('created_time'), year=ExtractYear('created_time'), ).order_by('-month').values('month', 'year').annotate(total=Count('*')).values('month', 'year', 'total').order_by('-year','-month') print(dates.query) return dates
def score_graphs(request): context = {'user': request.user} if request.method == 'POST': form = ScoreDataForm(request.POST) if form.is_valid(): pair = form.cleaned_data['child_parent_pair'] pair = pair.split(':') user_is_admin = False if request.user.profile.is_admin: children = list(Child.objects.all().order_by( 'parent__user__username', 'name')) user_is_admin = True elif request.user.profile.type == 'teacher': children = list( Child.objects.filter( teacher=request.user.profile).order_by( 'parent__user__username', 'name')) else: children = list( Child.objects.filter( parent=request.user.profile).order_by('name')) if pair[0] == 'All' and user_is_admin: context['scoreData'] = list( Score.objects.values(d=ExtractDay('date'), m=ExtractMonth('date'), y=ExtractYear('date')).annotate( Sum('amount'))) context['child_name'] = 'All' else: parent_user_of_chosen_child = User.objects.get( username=pair[1]) chosen_child = Child.objects.get( name=pair[0], parent=parent_user_of_chosen_child.profile) context['child_name'] = chosen_child.name if user_is_admin or request.user == parent_user_of_chosen_child or request.user.profile == chosen_child.teacher: context['scoreData'] = list( Score.objects.filter(child=chosen_child).values( d=ExtractDay('date'), m=ExtractMonth('date'), y=ExtractYear('date')).annotate(Sum('amount'))) else: return render(request, 'Preschool_Play/error.html', {'message': 'Unauthorized user'}) form = ScoreDataForm() pairs_choices = [(f'{c.name}:{c.parent.user.username}', f'P: {c.parent.user.username}. C: {c.name}.') for c in children] if user_is_admin: pairs_choices.insert(0, ('All:All', 'All')) form.fields['child_parent_pair'] = forms.CharField( widget=forms.Select(choices=pairs_choices)) form.fields['child_parent_pair'].initial = pairs_choices[0][1] context['form'] = form return render(request, 'Preschool_Play/score-graphs.html', context) else: user_is_admin = False if request.user.profile.is_admin: children = list(Child.objects.all().order_by( 'parent__user__username', 'name')) user_is_admin = True elif request.user.profile.type == 'teacher': children = list( Child.objects.filter(teacher=request.user.profile).order_by( 'parent__user__username', 'name')) else: children = list( Child.objects.filter( parent=request.user.profile).order_by('name')) if user_is_admin: context['scoreData'] = list( Score.objects.values(d=ExtractDay('date'), m=ExtractMonth('date'), y=ExtractYear('date')).annotate( Sum('amount'))) else: if children.__len__() < 1: return render(request, 'Preschool_Play/error.html', {'message': 'Unauthorized user'}) context['scoreData'] = list( Score.objects.filter(child=children[0]).values( d=ExtractDay('date'), m=ExtractMonth('date'), y=ExtractYear('date')).annotate(Sum('amount'))) form = ScoreDataForm() pairs_choices = [(f'{c.name}:{c.parent.user.username}', f'P: {c.parent.user.username}. C: {c.name}.') for c in children] if user_is_admin: pairs_choices.insert(0, ('All:All', 'All')) context['child_name'] = 'All' else: context['child_name'] = children[0].name form.fields['child_parent_pair'] = forms.CharField(widget=forms.Select( choices=pairs_choices)) form.fields['child_parent_pair'].initial = pairs_choices[0][0] context['form'] = form return render(request, 'Preschool_Play/score-graphs.html', context) return render(request, 'Preschool_Play/error.html', {'message': 'Unauthorized user'})
import datetime from dateutil.relativedelta import relativedelta from django.http import JsonResponse from django.db.models import Count from django.db.models.functions import (ExtractMonth, ExtractYear) from app.models import Trade # 计算时间 time_ago = datetime.datetime.now() - relativedelta(years=1) # 获取近一年数据 one_year_data = Data.objects.filter(create_time__gte=time_ago) # 分组统计每个月的数据 count_res = one_year_data\ .annotate(year=ExtractYear('create_time'),month=ExtractMonth('create_time'))\ .values('year', 'month').order_by('year', 'month').annotate(count=Count('id')) print(count_res) url = "https://www.jb51.net/article/165680.htm" def trade_list(request, year, month): trades = Trade.objects.filter(createTime__year=year, createTime__month=month) month_res = trades.annotate(day=ExtractDay('createTime')).values('day').order_by('day')\ .annotate(count=Count('id')) data = list(month_res) return JsonResponse({'month_res': data})
def redemption_report(request): today = date.today() # Query to show the aggregate usage of points on a monthly basis # Query set for aggregate sent points on monthly basis by user points_given_agg = pd.DataFrame(PointTransactions.objects.all().values( 'sender', month=ExtractMonth('transaction_date')).annotate(Sum('sent_amount')), columns=[ 'month', 'sender', 'sent_amount__sum' ]) points_given_agg.columns = ['month', 'user', 'Points Given'] # Query set for aggregate received points on monthly basis by user points_received_agg = pd.DataFrame(PointTransactions.objects.all().values( 'recipient', month=ExtractMonth('transaction_date')).annotate(Sum('sent_amount')), columns=[ 'month', 'recipient', 'sent_amount__sum' ]) points_received_agg.columns = ['month', 'user', 'Points Received'] # Merging first two queries second_df = pd.merge(points_given_agg, points_received_agg, how='outer', left_on=['month', 'user'], right_on=['month', 'user']) # Query set for aggregate redeemed points on motnhly basis by user points_redeemed_agg = pd.DataFrame( RedeemTransactions.objects.all().values( 'user', month=ExtractMonth('transaction_date')).annotate( Sum('points_redeemed')), columns=['month', 'user', 'points_redeemed__sum']) points_received_agg.columns = ['month', 'user', 'Points Redeemed'] # Merging last two queries final_df = pd.merge(second_df, points_redeemed_agg, how='outer', left_on=['month', 'user'], right_on=['month', 'user']).sort_values( by=['month', 'Points Received'], ascending=False) aggregate_data = [tuple(x) for x in final_df.values.tolist()] # Query to show who isn’t giving out all of their points for the current recent month only leftover_users = Users.objects.filter(~Q(points_left=0)).values_list( 'user_id', 'username', 'points_left') # Query to show all redemptions, by month by user, for the previous two months redemptions = RedeemTransactions.objects.filter( transaction_date__month__gte=(today.month - 2)).values( 'user_id', month=Extract('transaction_date', 'month')).annotate( Sum('points_redeemed')).order_by('-month') return render( request, 'points/redemption_report.html', { 'aggregate_data': aggregate_data, 'leftover_users': leftover_users, 'data': redemptions, 'admin': request.session['admin'] })
def get_entrustability_chart(request, year): feedback = Feedback.objects.filter(date__year=year) grouped_feedback = feedback.annotate(entrustability_score=F('entrustability')).annotate(month=ExtractMonth('date')) \ .values('month').annotate(average=Avg('entrustability')).values('month', 'average').order_by('month') feedback_dict = get_year_dict() for group in grouped_feedback: feedback_dict[months[group['month'] - 1]] = round(group['average'], 2) return JsonResponse({ 'title': f'Entrustability in {year}', 'data': { 'labels': list(feedback_dict.keys()), 'datasets': [{ 'label': 'Average Entrustability Score', 'borderColor': '#007bff', 'data': list(feedback_dict.values()), }] }, })
def evolution_chart(request): if (request.user.is_anonymous): user = User.objects.get(pk=1) else: user = request.user # return redirect('/login') assets = list( Asset.objects.filter(user=user).values('code', 'invest_type')) first_date = list( AssetPurchase.objects.filter( asset__user=user).order_by('date').values('date'))[0]["date"] timestamp_beginning = time.mktime( (first_date - timedelta(days=31)).timetuple()) data = EvolutionViews.price_cache(start=int(timestamp_beginning), end=int(time.time()), key="USDBRL=X") usd_prices = [{ "date": datetime.date(datetime.strptime(x[0], "%Y-%m-%d")), "value": x[4] } for x in data[1:]] last_usd_price = "null" index = -1 while (last_usd_price == "null" and index * -1 < len(usd_prices)): last_usd_price = usd_prices[index]["value"] index -= 1 savings = Saving.objects.filter(user=user).values( year=ExtractYear('date'), month=ExtractMonth('date')).annotate( accumulated_amount=Window(expression=Sum('final_amount'), order_by=[ ExtractYear('date').asc(), ExtractMonth('date').asc() ]), accumulated_spend=Window(expression=Sum('amount'), order_by=[ ExtractYear('date').asc(), ExtractMonth('date').asc() ]), ).order_by('year', 'month').distinct('year', 'month').values( 'date', 'year', 'month', 'accumulated_amount', 'accumulated_spend', 'final_amount', 'amount') for asset in assets: print("Getting purchases for %s" % (asset["code"])) asset_purchases = AssetPurchase.objects.filter( asset__code=asset["code"], asset__user=user).values( year=ExtractYear('date'), month=ExtractMonth('date')).annotate( accumulated_amount=Window( expression=Sum('amount'), order_by=[ ExtractYear('date').asc(), ExtractMonth('date').asc() ]), accumulated_spend=Window( expression=Sum( (F('value') * F('amount') + F('taxes_value')) * F('transfer__value') / F('transfer__final_value')), order_by=[ ExtractYear('date').asc(), ExtractMonth('date').asc() ]), spend=Window( partition_by=[ ExtractYear('date'), ExtractMonth('date') ], expression=Sum( (F('value') * F('amount') + F('taxes_value')) * F('transfer__value') / F('transfer__final_value')), order_by=[ ExtractYear('date').asc(), ExtractMonth('date').asc() ]), currency=F( 'asset__stock_exchange__currency__code')).order_by( 'year', 'month').distinct('year', 'month').values( 'date', 'year', 'month', 'accumulated_amount', 'accumulated_spend', 'currency', 'amount', 'spend') if (len(asset_purchases) == 0): asset.prices = [] continue month_prices = [] if asset["invest_type"] == 'S': data = EvolutionViews.price_cache( start=int(timestamp_beginning), end=int(time.time()), key=asset["code"]) month_prices = [{ "date": datetime.date(datetime.strptime(x[0], "%Y-%m-%d")), "value": x[4] } for x in data[1:]] else: response = ServiceViews.get_fund_price(request, asset["code"]) price = json.loads(response.content)['price'] beginning_date = datetime.fromtimestamp(timestamp_beginning) start = beginning_date.month end = datetime.now().month month_prices = [{ "date": (beginning_date + relativedelta(months=x)).date(), "value": price } for x in range(end - start + 1)] last_price = "null" index = -1 while (last_price == "null" and index * -1 < len(month_prices)): last_price = month_prices[index]["value"] index -= 1 index_month = 0 for month_price in month_prices: try: month_price["value"] = float(month_price["value"]) except: month_price["value"] = 0.0 if (index_month >= len(asset_purchases) or asset_purchases[index_month]["date"] > month_price["date"]): if (index_month == 0): month_price["total_value"] = 0 month_price["invested_value"] = 0 month_price["value"] = 0 month_price["invested"] = 0 currency = "" else: month_price["total_value"] = asset_purchases[ index_month - 1]["accumulated_amount"] * float( month_price["value"]) month_price["invested_value"] = asset_purchases[ index_month - 1]["accumulated_spend"] month_price["value"] = asset_purchases[ index_month - 1]["amount"] * float(last_price) month_price["invested"] = asset_purchases[index_month - 1]["spend"] currency = asset_purchases[index_month - 1]["currency"] else: month_price["total_value"] = asset_purchases[index_month][ "accumulated_amount"] * month_price["value"] month_price["invested_value"] = asset_purchases[ index_month]["accumulated_spend"] month_price["value"] = asset_purchases[index_month][ "amount"] * float(last_price) month_price["invested"] = asset_purchases[index_month][ "spend"] currency = asset_purchases[index_month]["currency"] index_month += 1 if (currency == "USD"): usd_value = float(usd_prices[index_month]["value"]) month_price["total_value"] *= usd_value month_price["invested"] *= usd_value month_price["value"] *= float(last_usd_price) asset["prices"] = month_prices months = [] for i in range(len(assets[0]["prices"])): date = assets[0]["prices"][i]["date"] month = { "date": date, "total_value": 0, "invested_value": 0, "invested": 0, "value": 0, "components": [] } for j in range(len(assets)): if (i >= len(assets[j]["prices"])): break month["total_value"] += assets[j]["prices"][i]["total_value"] month["invested_value"] += assets[j]["prices"][i][ "invested_value"] month["invested"] += assets[j]["prices"][i]["invested"] month["value"] += assets[j]["prices"][i]["value"] month["components"] += [{ "code": assets[j]["code"], "total_value": assets[j]["prices"][i]["total_value"], "invested_value": assets[j]["prices"][i]["invested_value"], "invested": assets[j]["prices"][i]["invested"], "value": assets[j]["prices"][i]["value"], }] months.append(month) index_saving = 0 for month in months: if (index_saving >= len(savings) or savings[index_saving]["date"] > month["date"]): if (index_saving > 0): month["total_value"] += savings[index_saving - 1]["accumulated_amount"] month["invested_value"] += savings[index_saving - 1]["accumulated_spend"] month["invested"] += savings[index_saving - 1]["amount"] month["value"] += savings[index_saving - 1]["final_amount"] month["components"] += [{ "code": "Saving", "total_value": savings[index_saving - 1]["accumulated_amount"], "invested_value": savings[index_saving - 1]["accumulated_spend"], "invested": savings[index_saving - 1]["amount"], "value": savings[index_saving - 1]["final_amount"], }] else: month["total_value"] += savings[index_saving][ "accumulated_amount"] month["invested_value"] += savings[index_saving][ "accumulated_spend"] month["invested"] += savings[index_saving]["amount"] month["value"] += savings[index_saving]["final_amount"] month["components"] += [{ "code": "Saving", "total_value": savings[index_saving]["accumulated_amount"], "invested_value": savings[index_saving]["accumulated_spend"], "invested": savings[index_saving]["amount"], "value": savings[index_saving]["final_amount"], }] index_saving += 1 date = month["date"] month["date"] = str(date.year) + "-" + str(date.month) + "-" + str( date.day) print(months) return render(request, 'Charts/evolution.html', {'assets': months})
def annual_working_time(year, *, users): absences = defaultdict( lambda: { "absence_vacation": [], "absence_sickness": [], "absence_paid": [], "absence_correction": [], }) months = Months(year=year, users=users) vacation_days_credit = defaultdict(lambda: Z1) dpm = days_per_month(year) overrides = { override.user_id: override for override in VacationDaysOverride.objects.filter(year=year, user__in=users) } for employment in Employment.objects.filter( user__in=months.users_with_wtm).order_by("-date_from"): percentage_factor = Decimal(employment.percentage) / 100 available_vacation_days_per_month = ( Decimal(employment.vacation_weeks) * 5 / 12 * percentage_factor) month_data = months[employment.user_id] for month, days in monthly_days(employment.date_from, employment.date_until): if month.year < year: continue elif month.year > year: break partial_month_factor = Decimal(days) / dpm[month.month - 1] month_data["target"][month.month - 1] += ( month_data["year"].months[month.month - 1] * percentage_factor * partial_month_factor * month_data["year"].working_time_per_day) month_data["percentage"][month.month - 1] += (100 * percentage_factor * partial_month_factor) month_data["available_vacation_days"][month.month - 1] += ( available_vacation_days_per_month * partial_month_factor) month_data["employments"].add(employment) for row in (LoggedHours.objects.order_by().filter( rendered_by__in=months.users_with_wtm, rendered_on__year=year).values("rendered_by").annotate( month=ExtractMonth("rendered_on")).values( "rendered_by", "month").annotate(Sum("hours"))): month_data = months[row["rendered_by"]] month_data["hours"][row["month"] - 1] += row["hours__sum"] remaining = defaultdict( lambda: Z1, { user: sum(month_data["available_vacation_days"]) for user, month_data in months.items() }, ) for user_id, override in overrides.items(): if override.type == override.Type.ABSOLUTE: remaining[user_id] = override.days elif override.type == override.Type.RELATIVE: remaining[user_id] += override.days else: # pragma: no cover raise Exception(f"Unknown override type {override.type}") available_vacation_days = defaultdict(lambda: Z1, remaining) for absence in Absence.objects.filter( user__in=months.users_with_wtm, starts_on__year=year, is_working_time=True).order_by("starts_on"): month_data = months[absence.user_id] key = "absence_%s" % absence.reason absences[absence.user_id][key].append(absence) starts_on = absence.starts_on ends_on = absence.ends_on or starts_on if starts_on.month == ends_on.month: days = [(absence.starts_on.month, absence.days)] else: total = Decimal((ends_on - starts_on).days + 1) one_day = dt.timedelta(days=1) calendar_days_per_month = [ ( m, Decimal(( # end of absence or last day of this month min(ends_on, next_valid_day(ends_on.year, m, 99) - one_day) # start of absence or first day of this month - max(starts_on, dt.date(starts_on.year, m, 1))).days # Always off by one + 1), ) for m in range(starts_on.month, ends_on.month + 1) ] days = [(m, absence.days * d / total) for m, d in calendar_days_per_month] for month, d in days: month_data[key][month - 1] += d if absence.is_vacation: for m, d in days: if d > remaining[absence.user_id]: month_data["vacation_days_correction"][m - 1] += ( remaining[absence.user_id] - d) remaining[absence.user_id] = max( 0, remaining[absence.user_id] - d) for user_id, vacation_days in remaining.items(): if vacation_days > 0: vacation_days_credit[user_id] = vacation_days def absences_time(data): return [ sum( ( data["absence_vacation"][i], data["absence_sickness"][i], data["absence_paid"][i], data["absence_correction"][i], data["vacation_days_correction"][i], ), Z1, ) * data["year"].working_time_per_day for i in range(12) ] def working_time(data): at = absences_time(data) return [sum((data["hours"][i], at[i]), Z1) for i in range(12)] def monthly_sums(data): sums = [None] * 12 wt = working_time(data) for i in range(12): sums[i] = wt[i] - data["target"][i] return sums statistics = [] for user in months.users_with_wtm: month_data = months[user.id] sums = monthly_sums(month_data) at = absences_time(month_data) wt = working_time(month_data) balance = (sum(sums) + month_data["year"].working_time_per_day * vacation_days_credit[user.id]) statistics.append({ "user": user, "months": month_data, "absences": absences[user.id], "employments": month_data["employments"], "working_time": wt, "absences_time": at, "monthly_sums": sums, "running_sums": [sum(sums[:i], Z1) for i in range(1, 13)], "totals": { "target_days": sum(month_data["target_days"]), "percentage": sum(month_data["percentage"]) / 12, "available_vacation_days": available_vacation_days[user.id], "calculated_vacation_days": sum(month_data["available_vacation_days"]), "vacation_days_override": overrides.get(user.id), "absence_vacation": sum(month_data["absence_vacation"]), "vacation_days_correction": sum(month_data["vacation_days_correction"]), "vacation_days_credit": vacation_days_credit[user.id].quantize(Z2, rounding=ROUND_UP), "balance": balance, "balance_days": (balance / month_data["year"].working_time_per_day).quantize( Z2, rounding=ROUND_UP), "absence_sickness": sum(month_data["absence_sickness"]), "absence_paid": sum(month_data["absence_paid"]), "absence_correction": sum(month_data["absence_correction"]), "target": sum(month_data["target"]), "hours": sum(month_data["hours"]), "absences_time": sum(at), "working_time": sum(wt), "running_sum": sum(sums).quantize(Z1), }, }) overall = { key: sum((s["totals"][key] for s in statistics), Z1) for key in [ "percentage", "available_vacation_days", "absence_vacation", "vacation_days_correction", "vacation_days_credit", "absence_sickness", "absence_paid", "absence_correction", "hours", "running_sum", "balance", ] } overall["absence_vacation"] -= overall["vacation_days_correction"] return {"months": months, "overall": overall, "statistics": statistics}
def create_relative_index(prod_id, prod_value, year, relative_type, location_id, audit_user_id, rel_price_type, period=None, month_start=None, month_end=None): distr = RelativeDistribution.objects \ .filter(product_id=prod_id) \ .filter(period=period) \ .filter(type=relative_type) \ .filter(care_type=rel_price_type) \ .filter(validity_to__isnull=False) \ .first() distr_perc = distr.percent if distr and distr.percent else 1 claim_value = 0 for claim_detail in [ClaimService, ClaimItem]: qs_val = claim_detail.objects \ .filter(status=ClaimDetail.STATUS_PASSED) \ .filter(claim__validity_to__isnull=True) \ .filter(validity_to__isnull=True) \ .filter(claim__status__in=[Claim.STATUS_PROCESSED, Claim.STATUS_VALUATED]) \ .annotate(nn_process_stamp_month=Coalesce(ExtractMonth("claim__process_stamp"), Value(-1))) \ .annotate(nn_process_stamp_year=Coalesce(ExtractYear("claim__process_stamp"), Value(-1))) \ .filter(nn_process_stamp_year=year) \ .filter(product_id=prod_id) if period: qs_val = qs_val.filter(nn_process_stamp_month=period) elif month_start and month_end: qs_val = qs_val.filter( nn_process_stamp_month__gte=month_start).filter( nn_process_stamp_month__lte=month_end) # else not needed as the year simply relies on the above year filter if rel_price_type == RelativeIndex.CARE_TYPE_IN_PATIENT: qs_val = qs_val.filter( claim__health_facility__level=HealthFacility.LEVEL_HOSPITAL) elif rel_price_type == RelativeIndex.CARE_TYPE_OUT_PATIENT: qs_val = qs_val.exclude( claim__health_facility__level=HealthFacility.LEVEL_HOSPITAL) # else both, no filter needed price_valuated = qs_val.values("price_valuated").aggregate( sum=Sum(Coalesce("price_valuated", 0)))["sum"] claim_value += price_valuated if price_valuated else 0 if claim_value == 0: rel_index = 1 else: rel_index = (prod_value * distr_perc) / claim_value from core.utils import TimeUtils return RelativeIndex.objects.create( product_id=prod_id, type=relative_type, care_type=RelativeIndex.CARE_TYPE_IN_PATIENT, year=year, period=period, calc_date=TimeUtils.now(), rel_index=rel_index, audit_user_id=audit_user_id, location_id=location_id, )
def post(self, request): """Return all budget function/subfunction titles matching the provided search text""" valid_groups = ['quarter', 'fiscal_year', 'month', 'fy', 'q', 'm'] models = [ {'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}, {'name': 'group', 'key': 'group', 'type': 'enum', 'enum_values': valid_groups, 'optional': False} ] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) json_request = TinyShield(models).block(request.data) group = json_request['group'] subawards = json_request['subawards'] filters = json_request.get("filters", None) if filters is None: raise InvalidParameterException('Missing request parameters: filters') # define what values are needed in the sql query # we do not use matviews for Subaward filtering, just the Subaward download filters if subawards: queryset = subaward_filter(filters) else: queryset = spending_over_time(filters).values('action_date', 'generated_pragmatic_obligation') # build response response = {'group': group, 'results': []} nested_order = '' # list of time_period objects ie {"fy": "2017", "quarter": "3"} : 1000 group_results = OrderedDict() # for Subawards we extract data from action_date if subawards: data_set = queryset \ .values('award_type') \ .annotate(month=ExtractMonth('action_date'), transaction_amount=Sum('amount')) \ .values('month', 'fiscal_year', 'transaction_amount') else: # for Awards we Sum generated_pragmatic_obligation for transaction_amount queryset = queryset.values('fiscal_year') if group in ('fy', 'fiscal_year'): data_set = queryset \ .annotate(transaction_amount=Sum('generated_pragmatic_obligation')) \ .values('fiscal_year', 'transaction_amount') else: # quarterly also takes months and aggregates the data data_set = queryset \ .annotate( month=ExtractMonth('action_date'), transaction_amount=Sum('generated_pragmatic_obligation')) \ .values('fiscal_year', 'month', 'transaction_amount') for record in data_set: # generate unique key by fiscal date, depending on group key = {'fiscal_year': str(record['fiscal_year'])} if group in ('m', 'month'): # generate the fiscal month key['month'] = generate_fiscal_month(date(year=2017, day=1, month=record['month'])) nested_order = 'month' elif group in ('q', 'quarter'): # generate the fiscal quarter key['quarter'] = FiscalDate(2017, record['month'], 1).quarter nested_order = 'quarter' key = str(key) # if key exists, aggregate if group_results.get(key) is None: group_results[key] = record['transaction_amount'] else: group_results[key] = group_results.get(key) + record['transaction_amount'] # convert result into expected format, sort by key to meet front-end specs results = [] # Expected results structure # [{ # 'time_period': {'fy': '2017', 'quarter': '3'}, # 'aggregated_amount': '200000000' # }] sorted_group_results = sorted( group_results.items(), key=lambda k: ( ast.literal_eval(k[0])['fiscal_year'], int(ast.literal_eval(k[0])[nested_order])) if nested_order else (ast.literal_eval(k[0])['fiscal_year'])) for key, value in sorted_group_results: key_dict = ast.literal_eval(key) result = {'time_period': key_dict, 'aggregated_amount': float(value) if value else float(0)} results.append(result) response['results'] = results return Response(response)
def generate_summary(request): if request.user.is_authenticated: date_range = request.POST.get('dateRange') dates = date_range.split('-') start = dates[0].split('/') end = dates[1].split('/') startday = int(start[1]) startmos = int(start[0]) startyr = int(start[2]) endday = int(end[1]) endmos = int(end[0]) endyr = int(end[2]) sdate = date(startyr, startmos, startday) # start date edate = date(endyr, endmos, endday) # end date all_incidents = IncidentReport.objects.filter( incident_date_time__gte=datetime.date(startyr, startmos, startday), incident_date_time__lte=datetime.date(endyr, endmos, endday)) eq_incidents = all_incidents.filter(incident_type_id=1) fr_incidents = all_incidents.filter(incident_type_id=2) fl_incidents = all_incidents.filter(incident_type_id=3) all_query = all_incidents.annotate( month=ExtractMonth('incident_date_time'), year=ExtractYear('incident_date_time')).values( 'month', 'year').annotate(count=Count('month')) eq_query = eq_incidents.annotate( month=ExtractMonth('incident_date_time'), year=ExtractYear('incident_date_time')).values( 'month', 'year').annotate(count=Count('month')) fr_query = fr_incidents.annotate( month=ExtractMonth('incident_date_time'), year=ExtractYear('incident_date_time')).values( 'month', 'year').annotate(count=Count('month')) fl_query = fl_incidents.annotate( month=ExtractMonth('incident_date_time'), year=ExtractYear('incident_date_time')).values( 'month', 'year').annotate(count=Count('month')) #eqlvl_query = eq_incidents.order_by('incident_level').annotate(count = Count('incident_level')) eqlvl_query = eq_incidents.values('incident_level').annotate( count=Count('incident_level')).order_by('count') frlvl_query = fr_incidents.values('incident_level').annotate( count=Count('incident_level')).order_by('count') fllvl_query = fl_incidents.values('incident_level').annotate( count=Count('incident_level')).order_by('count') print(eqlvl_query) eq_months = [] fr_months = [] fl_months = [] eq_count = [] fr_count = [] fl_count = [] months = [] eq_total = [] fr_total = [] fl_total = [] eq_lvls = [] fr_lvls = [] fl_lvls = [] eq_cntlvl = [] fr_cntlvl = [] fl_cntlvl = [] for eqlvl in eqlvl_query: eq_lvls.append(eqlvl['incident_level']) eq_cntlvl.append(eqlvl['count']) for frlvl in frlvl_query: fr_lvls.append(frlvl['incident_level']) fr_cntlvl.append(frlvl['count']) for fllvl in fllvl_query: fl_lvls.append(fllvl['incident_level']) fl_cntlvl.append(fllvl['count']) for all in all_query: months.append(calendar.month_abbr[all['month']] + " " + str(all['year'])) for eq in eq_query: month_key = calendar.month_abbr[eq['month']] + " " + str( eq['year']) eq_months.append(month_key) eq_count.append(eq["count"]) for fl in fl_query: month_key = calendar.month_abbr[fl['month']] + " " + str( fl['year']) fl_months.append(month_key) fl_count.append(fl["count"]) for fr in fr_query: month_key = calendar.month_abbr[fr['month']] + " " + str( fr['year']) fr_months.append(month_key) fr_count.append(fr["count"]) intctr = 0 for mos in months: if mos in eq_months: eq_total.append(eq_count[intctr]) intctr += 1 else: eq_total.append(0) intctr = 0 for mos in months: if mos in fl_months: fl_total.append(fl_count[intctr]) intctr += 1 else: fl_total.append(0) intctr = 0 for mos in months: if mos in fr_months: fr_total.append(fr_count[intctr]) intctr += 1 else: fr_total.append(0) context = { 'months': months, 'eq_lvls': eq_lvls, 'eq_cntlvl': eq_cntlvl, 'eq_months': eq_months, 'eq_total': eq_total, 'fr_lvls': fr_lvls, 'fr_cntlvl': fr_cntlvl, 'fr_months': fr_months, 'fr_total': fr_total, 'fl_lvls': fl_lvls, 'fl_cntlvl': fl_cntlvl, 'fl_months': fl_months, 'fl_total': fl_total } context["inputDateRange"] = date_range context['all_devices'] = Device.objects.all().filter( device_isDeleted=False) return render(request, "AEIRBS-Summary.html", context=context) else: return render(request, 'AEIRBS-Login.html')