def annotate_members_with_assignemnt_count(members):
now = timezone.now()
return members.annotate(assignment_count=Sum(
Case(When(assignment__job__time__gte=temporal.start_of_business_year(), assignment__job__time__lt=now, then='assignment__amount')))).annotate(
core_assignment_count=Sum(Case(
When(assignment__job__time__gte=temporal.start_of_business_year(), assignment__job__time__lt=now, assignment__core_cache=True,
then='assignment__amount'))))
def bill_subscription(subscription):
now = timezone.now()
start = start_of_business_year()
end = start_of_next_business_year()
price = scale_subscription_price(subscription, start_of_business_year(),
end_of_business_year() - timedelta(1))
refnumber = generate_ref_number('subscription', subscription.id,
subscription.primary_member.id, start)
bill = Bill.objects.create(billable=subscription,
amount=price,
ref_number=refnumber,
bill_date=now)
send_bill_sub(bill, subscription, start, end, subscription.primary_member)
def stats(request, trunc='week'):
start_date = date_from_get(request, 'start_date', start_of_business_year())
end_date = date_from_get(request, 'end_date', end_of_business_year())
temporal_data = TemporalData(trunc, start_date, end_date)
data = temporal_data.data_to_dict(assignments_by)
done_jobs = list(data.values())
available_slots = list(
temporal_data.data_to_dict(slots_by, 'available').values())
renderdict = {
'trunc_name':
temporal_data.trunc_adjective().capitalize() + 'e',
'start_date':
start_date,
'end_date':
end_date,
'labels': [temporal_data.date_to_label(date) for date in data.keys()],
'done_jobs':
done_jobs,
'available_slots':
available_slots,
'mobilization':
[i / j if j > 0 else 0 for i, j in zip(done_jobs, available_slots)],
'query':
urllib.parse.urlencode(request.GET),
'date_form':
DateRangeForm(initial={
'start_date': start_date,
'end_date': end_date
})
}
return render(request, 'mag/stats.html', renderdict)
def annotate_members_with_assignemnt_count(members):
now = timezone.now()
start = gdtz().localize(datetime.combine(start_of_business_year(), time.min))
return members.annotate(assignment_count=Sum(
Case(When(assignment__job__time__gte=start, assignment__job__time__lt=now, then='assignment__amount')))).annotate(
core_assignment_count=Sum(Case(
When(assignment__job__time__gte=start, assignment__job__time__lt=now, assignment__core_cache=True,
then='assignment__amount'))))
def required_core_assignments(self):
result = 0
for part in self.active_parts.all():
result += part.type.required_core_assignments
if self.activation_date is not None and self.activation_date > start_of_business_year(
):
result = round(
result *
calculate_remaining_days_percentage(self.activation_date))
return result
def bookings_export(request):
default_range = {
'fromdate': start_of_business_year(),
'tilldate': start_of_next_business_year() - timedelta(1)
}
renderdict = get_menu_dict(request)
# daterange for bookings export
if 'fromdate' in request.GET and 'tilldate' in request.GET:
# request with query parameter
daterange_form = DateRangeForm(request.GET)
else:
daterange_form = DateRangeForm(default_range)
if daterange_form.is_valid():
fromdate = daterange_form.cleaned_data['fromdate']
tilldate = daterange_form.cleaned_data['tilldate']
bill_bookings = get_bill_bookings(fromdate, tilldate)
payment_bookings = get_payment_bookings(fromdate, tilldate)
else:
bill_bookings = []
payment_bookings = []
# export button pressed and date fields OK -> do excel export
if ('export' in request.GET) and daterange_form.is_valid():
# sort bookings on date and docnumber
bookings = sorted(bill_bookings + payment_bookings,
key=lambda bk: (bk.date, bk.docnumber))
return export_bookings(bookings, "bookings")
# otherwise return page
renderdict.update({
'daterange_form': daterange_form,
'bill_bookings_count': len(bill_bookings),
'payment_bookings_count': len(payment_bookings),
})
return render(request, 'jb/bookings_export.html', renderdict)
def assignments_for_member_current_business_year(member):
start = gdtz().localize(
datetime.combine(start_of_business_year(), time.min))
return juntagrico.entity.jobs.Assignment.objects.filter(member=member).\
filter(job__time__gte=start, job__time__lt=timezone.now())
def stats(request):
activity_area = ActivityArea.objects.filter(
pk=request.GET.get('activity_area', None)).first()
start_date = date_from_get(request, 'start_date', start_of_business_year())
end_date = date_from_get(request, 'end_date', end_of_business_year())
filename = '{}_{}_{}stats.xlsx'.format(
start_date.strftime('%Y-%m-%d'), end_date.strftime('%Y-%m-%d'),
str(activity_area.pk) + '_' if activity_area else '')
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=' + filename
wb = Workbook()
# Sheet 5: master sheet
ws5 = wb.active
ws5.title = "master"
# header
ws5.cell(1, 1, u"{}".format(_('Mitgliedernummer')))
ws5.column_dimensions['B'].width = 17
ws5.cell(1, 2, u"{}".format('Vorname'))
ws5.column_dimensions['C'].width = 17
ws5.cell(1, 3, u"{}".format(_('Name')))
ws5.column_dimensions['D'].width = 17
ws5.cell(1, 4, u"{}".format(_('Strasse')))
ws5.column_dimensions['E'].width = 17
ws5.cell(1, 5, u"{}".format('PLZ'))
ws5.column_dimensions['F'].width = 17
ws5.cell(1, 6, u"{}".format(_('Mail')))
ws5.column_dimensions['G'].width = 40
ws5.cell(1, 7, u"{}".format('Telefon'))
ws5.column_dimensions['H'].width = 17
ws5.cell(1, 8, u"{}".format(_('Anmeldedatum')))
ws5.column_dimensions['M'].width = 40
ws5.cell(1, 9, u"{}".format(_('Depot')))
ws5.column_dimensions['I'].width = 17
ws5.cell(1, 10, u"{}".format(_('Arbeitseinsätze')))
ws5.column_dimensions['J'].width = 17
ws5.cell(1, 11, u"{}".format(_('Arbeitseinsätze Kernbereich')))
ws5.column_dimensions['K'].width = 40
ws5.cell(1, 12, u"{}".format('Mit-Abo-Mails'))
ws5.column_dimensions['L'].width = 80
ws5.cell(1, 13, u"{}".format(_('Anzahl Anteilscheine')))
ws5.column_dimensions['N'].width = 40
ws5.cell(1, 14, u"{}".format('Nr.Anteilschein(e)'))
ws5.column_dimensions['A'].width = 17
ws5.cell(1, 15, u"{}".format('Datum Einzahlung Anteilschein(e)'))
ws5.column_dimensions['P'].width = 40
ws5.cell(1, 16, u"{}".format(('Anteilschein(e) ausgestellt')))
ws5.column_dimensions['Q'].width = 40
ws5.cell(1, 17, u"{}".format(('Anzahl Ernteanteile')))
ws5.column_dimensions['O'].width = 40
#ws5.cell(1, 18, u"{}".format(('Datum Einzahlung Ernteanteil(e)')))
ws5.cell(1, 18, u"{}".format(('Grösse Ernteanteil(e)')))
ws5.column_dimensions['R'].width = 40
# data
members = members_with_assignments_no_filter(start_date, end_date)
#members = MemberDao.all_members().filter(inactive=False)
#users = User.objects.all()
#print(users)
for curr_row, member in enumerate(members, 2):
curr_user = member.user
ws5.cell(curr_row, 1, member.id)
ws5.cell(curr_row, 2, member.first_name)
ws5.cell(curr_row, 3, member.last_name)
ws5.cell(curr_row, 4, member.addr_street)
ws5.cell(curr_row, 5, member.addr_zipcode)
ws5.cell(curr_row, 6, member.email)
ws5.cell(curr_row, 7, member.phone)
ws5.cell(curr_row, 8, curr_user.date_joined.date())
sub = member.subscription
depot = sub.depot.name if sub is not None else '-'
ws5.cell(curr_row, 9, depot)
assignments = member.assignments if member.assignments is not None else '-'
ws5.cell(curr_row, 10, assignments)
ws5.cell(curr_row, 11, 'N.A.')
email_string = '-'
if sub is not None:
co_members = sub.other_recipients()
email_list = [m.email for m in co_members]
if email_list:
email_string = ', '.join(email_list)
ws5.cell(curr_row, 12, email_string)
curr_shares = member.active_shares
num_shares = len(curr_shares)
ws5.cell(curr_row, 13, num_shares)
share_id_string = '-'
share_ids = [str(s.id) for s in curr_shares]
if share_ids:
share_id_string = ', '.join(share_ids)
ws5.cell(curr_row, 14, share_id_string)
share_paid_date_string = '-'
share_paid_dates = [str(s.paid_date) for s in curr_shares]
if share_paid_dates:
share_paid_date_string = ', '.join(share_paid_dates)
ws5.cell(curr_row, 15, share_paid_date_string)
share_issue_date_string = '-'
share_issue_dates = [str(s.issue_date) for s in curr_shares]
if share_issue_dates:
share_issue_date_string = ', '.join(share_issue_dates)
ws5.cell(curr_row, 16, share_issue_date_string)
amount_subs_string = '-'
size_subs_string = '-'
if sub is not None:
amount_subs_string = str(sub.subscription_amount(0))
size_subs_string = str(sub.size_name)
ws5.cell(curr_row, 17, amount_subs_string)
ws5.cell(curr_row, 18, size_subs_string)
# Sheet 1: assignments by subscription
ws1 = wb.create_sheet(title="assignments by subscription")
# header
ws1.cell(1, 1, u"{}".format(Config.vocabulary('member_pl')))
ws1.column_dimensions['A'].width = 40
ws1.cell(1, 2, u"{}".format(_('Arbeitseinsätze')))
ws1.column_dimensions['B'].width = 17
ws1.cell(
1, 3,
u"{}".format(_('{}-Grösse').format(Config.vocabulary('subscription'))))
ws1.column_dimensions['C'].width = 17
# data
for row, subscription in enumerate(
assignments_by_subscription(start_date, end_date, activity_area),
2):
ws1.cell(
row, 1, ", ".join([
member.get_name()
for member in subscription['subscription'].members.all()
]))
ws1.cell(row, 2, subscription['assignments'])
ws1.cell(row, 3, subscription['subscription'].totalsize)
# Sheet 2: assignments per day
ws2 = wb.create_sheet(title="assignments per day")
# header
ws2.cell(1, 1, u"{}".format(_('Datum')))
ws2.column_dimensions['A'].width = 20
ws2.cell(1, 2, u"{}".format(_('Arbeitseinsätze geleistet')))
ws2.column_dimensions['B'].width = 17
# data
for row, assignment in enumerate(
assignments_by_day(start_date, end_date, activity_area), 2):
ws2.cell(row, 1, assignment['day'])
ws2.cell(row, 2, assignment['count'])
# Sheet 3: slots by day
ws3 = wb.create_sheet(title="slots per day")
# header
ws3.cell(1, 1, u"{}".format(_('Datum')))
ws3.column_dimensions['A'].width = 20
ws3.cell(1, 2, u"{}".format(_('Arbeitseinsätze ausgeschrieben')))
ws3.column_dimensions['B'].width = 17
# data
for row, assignment in enumerate(
slots_by_day(start_date, end_date, activity_area), 2):
ws3.cell(row, 1, assignment['day'])
ws3.cell(row, 2, assignment['available'])
# Sheet 4: assignments per member
ws4 = wb.create_sheet(title="assignments per member")
# header
ws4.cell(1, 1, u"{}".format(Config.vocabulary('member')))
ws4.column_dimensions['A'].width = 40
ws4.cell(1, 2, u"{}".format(_('Arbeitseinsätze')))
ws4.column_dimensions['B'].width = 17
# data
members = members_with_assignments(start_date, end_date, activity_area)
for row, member in enumerate(members, 2):
ws4.cell(row, 1, u"{}".format(member))
ws4.cell(row, 2, member.assignments)
wb.save(response)
return response
def test_start_of_business_year(self):
today = date.today()
expected_start = date(today.year, 1, 1)
self.assertEqual(expected_start, start_of_business_year())
def stats_export(request):
activity_area = ActivityArea.objects.filter(
pk=request.GET.get('activity_area', None)).first()
start_date = date_from_get(request, 'start_date', start_of_business_year())
end_date = date_from_get(request, 'end_date', end_of_business_year())
filename = '{}_{}_{}stats.xlsx'.format(
start_date.strftime('%Y-%m-%d'), end_date.strftime('%Y-%m-%d'),
str(activity_area.pk) + '_' if activity_area else '')
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=' + filename
wb = Workbook()
# Sheet 1: assignments by subscription
ws1 = wb.active
ws1.title = "assignments by subscription"
# header
ws1.cell(1, 1, u"{}".format(Config.vocabulary('member_pl')))
ws1.column_dimensions['A'].width = 40
ws1.cell(1, 2, u"{}".format(_('Arbeitseinsätze')))
ws1.column_dimensions['B'].width = 17
ws1.cell(
1, 3,
u"{}".format(_('{}-Grösse').format(Config.vocabulary('subscription'))))
ws1.column_dimensions['C'].width = 17
# data
for row, subscription in enumerate(
assignments_by_subscription(start_date, end_date, activity_area),
2):
ws1.cell(
row, 1, ", ".join([
member.get_name()
for member in subscription['subscription'].recipients
]))
ws1.cell(row, 2, subscription['assignments'])
ws1.cell(row, 3, subscription['subscription'].totalsize)
# Sheet 2: assignments per day
ws2 = wb.create_sheet(title="assignments per day")
# header
ws2.cell(1, 1, u"{}".format(_('Datum')))
ws2.column_dimensions['A'].width = 20
ws2.cell(1, 2, u"{}".format(_('Arbeitseinsätze geleistet')))
ws2.column_dimensions['B'].width = 17
# data
for row, assignment in enumerate(
assignments_by('day', start_date, end_date, activity_area), 2):
ws2.cell(row, 1, make_naive(assignment['day']))
ws2.cell(row, 2, assignment['count'])
# Sheet 3: slots by day
ws3 = wb.create_sheet(title="slots per day")
# header
ws3.cell(1, 1, u"{}".format(_('Datum')))
ws3.column_dimensions['A'].width = 20
ws3.cell(1, 2, u"{}".format(_('Arbeitseinsätze ausgeschrieben')))
ws3.column_dimensions['B'].width = 17
# data
for row, assignment in enumerate(
slots_by('day', start_date, end_date, activity_area), 2):
ws3.cell(row, 1, make_naive(assignment['day']))
ws3.cell(row, 2, assignment['available'])
# Sheet 4: assignments per member
ws4 = wb.create_sheet(title="assignments per member")
# header
ws4.cell(1, 1, u"{}".format(Config.vocabulary('member')))
ws4.column_dimensions['A'].width = 40
ws4.cell(1, 2, u"{}".format(_('Arbeitseinsätze')))
ws4.column_dimensions['B'].width = 17
# data
members = members_with_assignments(start_date, end_date, activity_area)
for row, member in enumerate(members, 2):
ws4.cell(row, 1, u"{}".format(member))
ws4.cell(row, 2, member.assignments)
wb.save(response)
return response