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