def json_availability_feed(request,instructor_id=None):
if not instructor_id:
return JsonResponse({})
startDate = request.GET.get('start','')
endDate = request.GET.get('end','')
timeZone = request.GET.get('timezone',getattr(settings,'TIME_ZONE','UTC'))
hideUnavailable = request.GET.get('hideUnavailable', False)
time_filter_dict_events = {}
if startDate:
time_filter_dict_events['startTime__gte'] = ensure_timezone(datetime.strptime(startDate,'%Y-%m-%d'))
if endDate:
time_filter_dict_events['startTime__lte'] = ensure_timezone(datetime.strptime(endDate,'%Y-%m-%d')) + timedelta(days=1)
this_instructor = Instructor.objects.get(id=instructor_id)
availability = InstructorAvailabilitySlot.objects.filter(
instructor=this_instructor,
).filter(**time_filter_dict_events)
if (
((
hasattr(request.user,'staffmember') and request.user.staffmember == this_instructor and
request.user.has_perm('private_lessons.edit_own_availability')
) or
request.user.has_perm('private_lessons.edit_others_availability')
) and not hideUnavailable
):
eventlist = [AvailabilityFeedItem(x,timeZone=timeZone).__dict__ for x in availability]
else:
eventlist = [AvailabilityFeedItem(x,timeZone=timeZone).__dict__ for x in availability if x.isAvailable]
return JsonResponse(eventlist,safe=False)
def json_availability_feed(request,instructor_id=None):
if not instructor_id:
return JsonResponse({})
startDate = request.GET.get('start','')
endDate = request.GET.get('end','')
timeZone = request.GET.get('timezone',getattr(settings,'TIME_ZONE','UTC'))
hideUnavailable = request.GET.get('hideUnavailable', False)
time_filter_dict_events = {}
if startDate:
time_filter_dict_events['startTime__gte'] = ensure_timezone(datetime.strptime(startDate,'%Y-%m-%d'))
if endDate:
time_filter_dict_events['startTime__lte'] = ensure_timezone(datetime.strptime(endDate,'%Y-%m-%d')) + timedelta(days=1)
this_instructor = Instructor.objects.get(id=instructor_id)
availability = InstructorAvailabilitySlot.objects.filter(
instructor=this_instructor,
).filter(**time_filter_dict_events)
if (
((
hasattr(request.user,'staffmember') and getattr(request.user.staffmember,'instructor') == this_instructor and
request.user.has_perm('private_lessons.edit_own_availability')
) or
request.user.has_perm('private_lessons.edit_others_availability')
) and not hideUnavailable
):
eventlist = [AvailabilityFeedItem(x,timeZone=timeZone).__dict__ for x in availability]
else:
eventlist = [AvailabilityFeedItem(x,timeZone=timeZone).__dict__ for x in availability if x.isAvailable]
return JsonResponse(eventlist,safe=False)
def addPrivateEvent(request):
if request.POST:
form = AddPrivateEventForm(request.POST, user=request.user)
formset = EventOccurrenceFormSet(request.POST)
if form.is_valid() and formset.is_valid():
obj = form.save()
formset.instance = obj
formset.save()
return HttpResponseRedirect(reverse('privateCalendar'))
# Otherwise, return the initial form for this instructor
# GET parameters can be passed to the form, but the form will not be validated with them.
else:
form = AddPrivateEventForm(user=request.user)
formset = EventOccurrenceFormSet()
for key in request.GET:
try:
form.fields[key].initial = request.GET.get(key)
except KeyError:
pass
try:
# Only the startTime should be passable to the formset
if key == 'startTime':
formset[0].fields['startTime'].initial = ensure_timezone(datetime.strptime(request.GET.get(key) or '','%Y-%m-%d'))
formset[0].fields['endTime'].initial = ensure_timezone(datetime.strptime(request.GET.get(key) or '','%Y-%m-%d'))
formset[0].fields['allDay'].initial = True
except ValueError:
pass
return render(request,'private_events/add_private_event_form.html',{
'form': form,
'formset': formset,
'formset_helper': OccurrenceFormSetHelper()},)
def addPrivateEvent(request):
if request.POST:
form = AddPrivateEventForm(request.POST, user=request.user)
formset = EventOccurrenceFormSet(request.POST)
if form.is_valid() and formset.is_valid():
obj = form.save()
formset.instance = obj
formset.save()
return HttpResponseRedirect('/admin')
# Otherwise, return the initial form for this instructor
# GET parameters can be passed to the form, but the form will not be validated with them.
else:
form = AddPrivateEventForm(user=request.user)
formset = EventOccurrenceFormSet()
for key in request.GET:
try:
form.fields[key].initial = request.GET.get(key)
except KeyError:
pass
try:
# Only the startTime should be passable to the formset
if key == 'startTime':
formset[0].fields['startTime'].initial = ensure_timezone(datetime.strptime(request.GET.get(key) or '','%Y-%m-%d'))
formset[0].fields['endTime'].initial = ensure_timezone(datetime.strptime(request.GET.get(key) or '','%Y-%m-%d'))
formset[0].fields['allDay'].initial = True
except ValueError:
pass
return render(request,'private_events/add_private_event_form.html',{
'form': form,
'formset': formset,
'formset_helper': OccurrenceFormSetHelper()},)
def clean(self):
startTime = self.cleaned_data['startTime']
endTime = self.cleaned_data['endTime']
allDay = self.cleaned_data.pop('allDay', None) or False
if allDay:
self.cleaned_data['startTime'] = ensure_timezone(datetime(startTime.year,startTime.month,startTime.day))
self.cleaned_data['endTime'] = ensure_timezone(datetime(endTime.year,endTime.month,endTime.day)) + timedelta(days=1)
if self.cleaned_data['endTime'] < self.cleaned_data['startTime']:
raise ValidationError(_('End time cannot be before start time.'),code='invalid')
def clean(self):
startTime = self.cleaned_data['startTime']
endTime = self.cleaned_data['endTime']
allDay = self.cleaned_data.pop('allDay', None) or False
if allDay:
self.cleaned_data['startTime'] = ensure_timezone(datetime(startTime.year,startTime.month,startTime.day))
self.cleaned_data['endTime'] = ensure_timezone(datetime(endTime.year,endTime.month,endTime.day)) + timedelta(days=1)
if self.cleaned_data['endTime'] < self.cleaned_data['startTime']:
raise ValidationError(_('End time cannot be before start time.'),code='invalid')
def json_lesson_feed(request,
location_id=None,
room_id=None,
show_others=False):
'''
This function displays a JSON feed of all lessons scheduled, optionally
filtered by location. If show_others is specified, it requires that the
user has permission to see all other instructor's lessons as well.
'''
if not request.user or not request.user.is_staff:
raise Http404()
# Don't allow individuals to see others' lessons without permission
if not request.user.has_perm('private_lessons.view_others_lessons'):
show_others = False
this_instructor = getattr(request.user, 'staffmember', None)
startDate = request.GET.get('start', '')
endDate = request.GET.get('end', '')
timeZone = request.GET.get('timezone', getattr(settings, 'TIME_ZONE',
'UTC'))
filters = Q(instructoravailabilityslot__status=InstructorAvailabilitySlot.
SlotStatus.booked)
if not show_others:
filters = filters & Q(eventstaffmember__staffMember=this_instructor)
if startDate:
filters = filters & Q(startTime__gte=ensure_timezone(
datetime.strptime(startDate, '%Y-%m-%d')))
if endDate:
filters = filters & Q(endTime__lte=ensure_timezone(
datetime.strptime(endDate, '%Y-%m-%d')) + timedelta(days=1))
if location_id:
filters = filters & Q(location__id=location_id)
if room_id:
filters = filters & Q(room_id=room_id)
lessons = PrivateLessonEvent.objects.filter(filters).distinct()
eventlist = [
PrivateLessonFeedItem(x, timeZone=timeZone).__dict__ for x in lessons
]
return JsonResponse(eventlist, safe=False)
def get_context_data(self, **kwargs):
from .stats import getGeneralStats
request = self.request
context_data = kwargs
(totalStudents, numSeries, totalSeriesRegs,
totalTime) = getGeneralStats(request)
bestCustomersLastTwelveMonths = Customer.objects.values(
'user__first_name', 'user__last_name').filter(
**{
'eventregistration__registration__dateTime__gte':
ensure_timezone(
datetime(timezone.now().year - 1,
timezone.now().month,
timezone.now().day)),
'eventregistration__dropIn':
False,
'eventregistration__cancelled':
False
}).annotate(Count('eventregistration')).order_by(
'-eventregistration__count')[:20]
bestCustomersAllTime = Customer.objects.values(
'user__first_name', 'user__last_name').filter(
**{
'eventregistration__dropIn': False,
'eventregistration__cancelled': False
}).annotate(Count('eventregistration')).order_by(
'-eventregistration__count')[:20]
mostActiveTeachersThisYear = SeriesTeacher.objects.filter(
event__year=timezone.now().year).exclude(
staffMember__instructor__status=Instructor.InstructorStatus.
guest).values_list(
'staffMember__firstName',
'staffMember__lastName').annotate(
Count('staffMember')).order_by('-staffMember__count')
context_data.update({
'totalStudents':
totalStudents,
'numSeries':
numSeries,
'totalSeriesRegs':
totalSeriesRegs,
'totalTime':
totalTime,
'bestCustomersAllTime':
bestCustomersAllTime,
'bestCustomersLastTwelveMonths':
bestCustomersLastTwelveMonths,
'mostActiveTeachersThisYear':
mostActiveTeachersThisYear,
})
return context_data
def json_event_feed(request,location_id=None,room_id=None):
'''
The Jquery fullcalendar app requires a JSON news feed, so this function
creates the feed from upcoming PrivateEvent objects
'''
if not getConstant('calendar__privateCalendarFeedEnabled') or not request.user.is_staff:
return JsonResponse({})
this_user = request.user
startDate = request.GET.get('start','')
endDate = request.GET.get('end','')
timeZone = request.GET.get('timezone',getattr(settings,'TIME_ZONE','UTC'))
time_filter_dict_events = {}
if startDate:
time_filter_dict_events['startTime__gte'] = ensure_timezone(datetime.strptime(startDate,'%Y-%m-%d'))
if endDate:
time_filter_dict_events['endTime__lte'] = ensure_timezone(datetime.strptime(endDate,'%Y-%m-%d')) + timedelta(days=1)
instructor_groups = list(this_user.groups.all().values_list('id',flat=True))
filters = Q(event__privateevent__isnull=False) & (
Q(event__privateevent__displayToGroup__in=instructor_groups) |
Q(event__privateevent__displayToUsers=this_user) |
(Q(event__privateevent__displayToGroup__isnull=True) & Q(event__privateevent__displayToUsers__isnull=True))
)
if location_id:
filters = filters & Q(event__location__id=location_id)
if room_id:
filters = filters & Q(event__room_id=room_id)
occurrences = EventOccurrence.objects.filter(filters).filter(**time_filter_dict_events).order_by('-startTime')
eventlist = [EventFeedItem(x,timeZone=timeZone).__dict__ for x in occurrences]
return JsonResponse(eventlist,safe=False)
def json_event_feed(request, instructorFeedKey):
if not instructorFeedKey or not getConstant(
'calendar__privateCalendarFeedEnabled'):
return JsonResponse({})
startDate = request.GET.get('start', '')
endDate = request.GET.get('end', '')
timeZone = request.GET.get('timezone', getattr(settings, 'TIME_ZONE',
'UTC'))
time_filter_dict_events = {}
if startDate:
time_filter_dict_events['startTime__gte'] = ensure_timezone(
datetime.strptime(startDate, '%Y-%m-%d'))
if endDate:
time_filter_dict_events['endTime__lte'] = ensure_timezone(
datetime.strptime(endDate, '%Y-%m-%d')) + timedelta(days=1)
this_user = StaffMember.objects.get(feedKey=instructorFeedKey).userAccount
instructor_groups = list(this_user.groups.all().values_list('id',
flat=True))
occurrences = EventOccurrence.objects.filter(
event__privateevent__isnull=False).filter(
**time_filter_dict_events).filter(
Q(event__privateevent__displayToGroup__in=instructor_groups)
| Q(event__privateevent__displayToUsers=this_user)
| (Q(event__privateevent__displayToGroup__isnull=True)
& Q(event__privateevent__displayToUsers__isnull=True))
).order_by('-startTime')
eventlist = [
EventFeedItem(x, timeZone=timeZone).__dict__ for x in occurrences
]
return JsonResponse(eventlist, safe=False)
def json_lesson_feed(request,location_id=None,room_id=None,show_others=False):
'''
This function displays a JSON feed of all lessons scheduled, optionally
filtered by location. If show_others is specified, it requires that the
user has permission to see all other instructor's lessons as well.
'''
if not request.user or not request.user.is_staff:
raise Http404()
# Don't allow individuals to see others' lessons without permission
if not request.user.has_perm('private_lessons.view_others_lessons'):
show_others = False
this_instructor = getattr(request.user,'staffmember',None)
startDate = request.GET.get('start','')
endDate = request.GET.get('end','')
timeZone = request.GET.get('timezone',getattr(settings,'TIME_ZONE','UTC'))
filters = Q(instructoravailabilityslot__status=InstructorAvailabilitySlot.SlotStatus.booked)
if not show_others:
filters = filters & Q(eventstaffmember__staffMember=this_instructor)
if startDate:
filters = filters & Q(startTime__gte=ensure_timezone(datetime.strptime(startDate,'%Y-%m-%d')))
if endDate:
filters = filters & Q(endTime__lte=ensure_timezone(datetime.strptime(endDate,'%Y-%m-%d')) + timedelta(days=1))
if location_id:
filters = filters & Q(location__id=location_id)
if room_id:
filters = filters & Q(room_id=room_id)
lessons = PrivateLessonEvent.objects.filter(filters).distinct()
eventlist = [PrivateLessonFeedItem(x,timeZone=timeZone).__dict__ for x in lessons]
return JsonResponse(eventlist,safe=False)
def get_context_data(self, **kwargs):
from .stats import getGeneralStats
request = self.request
context_data = kwargs
(totalStudents,numSeries,totalSeriesRegs,totalTime) = getGeneralStats(request)
bestCustomersLastTwelveMonths = Customer.objects.values(
'first_name','last_name'
).filter(**{
'eventregistration__registration__dateTime__gte': ensure_timezone(datetime(timezone.now().year - 1,timezone.now().month,timezone.now().day)),
'eventregistration__dropIn':False,'eventregistration__cancelled':False
}).annotate(Count('eventregistration')).order_by('-eventregistration__count')[:20]
bestCustomersAllTime = Customer.objects.values(
'first_name','last_name'
).filter(**{
'eventregistration__dropIn':False,
'eventregistration__cancelled':False
}).annotate(Count('eventregistration')).order_by('-eventregistration__count')[:20]
mostActiveTeachersThisYear = SeriesTeacher.objects.filter(
event__year=timezone.now().year
).exclude(
staffMember__instructor__status=Instructor.InstructorStatus.guest
).values_list(
'staffMember__firstName','staffMember__lastName'
).annotate(Count('staffMember')).order_by('-staffMember__count')
context_data.update({
'totalStudents':totalStudents,
'numSeries':numSeries,
'totalSeriesRegs':totalSeriesRegs,
'totalTime':totalTime,
'bestCustomersAllTime': bestCustomersAllTime,
'bestCustomersLastTwelveMonths': bestCustomersLastTwelveMonths,
'mostActiveTeachersThisYear': mostActiveTeachersThisYear,
})
return context_data
def prepareStatementByMonth(**kwargs):
basis = kwargs.get('basis')
if basis not in EXPENSE_BASES.keys():
basis = 'accrualDate'
rev_basis = basis
if rev_basis in ['paymentDate', 'approvalDate']:
rev_basis = 'receivedDate'
start_date = kwargs.get('start_date')
end_date = kwargs.get('end_date')
year = kwargs.get('year')
# In order to provide major calculations, it's easiest to just get all the Expense and Revenue line items at once
# To avoid too many unnecessary calls, be sure to filter off these querysets rather than pulling them again.
expenseitems = ExpenseItem.objects.select_related('event').annotate(
year=ExtractYear(basis, 'year'), month=ExtractMonth(basis))
revenueitems = RevenueItem.objects.select_related('event').annotate(
year=ExtractYear(rev_basis, 'year'), month=ExtractMonth(rev_basis))
timeFilters = {}
rev_timeFilters = {}
if start_date:
timeFilters['%s__gte' % basis] = start_date
rev_timeFilters['%s__gte' % rev_basis] = start_date
if end_date:
timeFilters['%s__lt' % basis] = end_date
rev_timeFilters['%s__lt' % rev_basis] = end_date
if year and not (start_date or end_date):
start_limit = ensure_timezone(datetime(year, 1, 1))
end_limit = ensure_timezone(datetime(year + 1, 1, 1))
timeFilters['%s__gte' % basis] = start_limit
rev_timeFilters['%s__gte' % rev_basis] = start_limit
timeFilters['%s__lt' % basis] = end_limit
rev_timeFilters['%s__lt' % rev_basis] = end_limit
if timeFilters:
expenseitems = expenseitems.filter(**timeFilters)
revenueitems = revenueitems.filter(**rev_timeFilters)
# Get the set of possible month tuples. If we are not given a date window, default to the last 12 months.
allMonths = [(x.year, x.month)
for x in revenueitems.dates(rev_basis, 'month')]
allMonths.sort(reverse=True)
paginator = Paginator(allMonths, kwargs.get('paginate_by', 50))
try:
paged_months = paginator.page(kwargs.get('page', 1))
except PageNotAnInteger:
if kwargs.get('page') == 'last':
paged_months = paginator.page(paginator.num_pages)
else:
paged_months = paginator.page(1)
except EmptyPage:
paged_months = paginator.page(paginator.num_pages)
# Get everything by month in one query each, then pull from this.
totalExpensesByMonth = expenseitems.values_list('year', 'month').annotate(
Sum('total'), Sum('adjustments'),
Sum('fees')).order_by('-year', '-month')
instructionExpensesByMonth = expenseitems.filter(category__in=[
getConstant('financial__classInstructionExpenseCat'),
getConstant('financial__assistantClassInstructionExpenseCat')
]).values_list('year',
'month').annotate(Sum('total'), Sum('adjustments'),
Sum('fees')).order_by('-year', '-month')
venueExpensesByMonth = expenseitems.filter(
category=getConstant('financial__venueRentalExpenseCat')).values_list(
'year', 'month').annotate(Sum('total'), Sum('adjustments'),
Sum('fees')).order_by('-year', '-month')
totalRevenuesByMonth = revenueitems.values_list('year', 'month').annotate(
Sum('total'), Sum('adjustments'),
Sum('fees')).order_by('-year', '-month')
# This includes only registrations in which a series was registered for (and was not cancelled)
registrationsByMonth = Registration.objects.filter(
eventregistration__cancelled=False).annotate(
year=ExtractYear('dateTime'),
month=ExtractMonth('dateTime')).values_list(
'year', 'month').annotate(count=Count('id')).order_by(
'-year', '-month')
# This little helper function avoids 0-item list issues and encapsulates the
# needed list iterator for readability
def valueOf(resultset, month, values=1):
this = [
x[2:(2 + values)] for x in resultset
if x[0] == month[0] and x[1] == month[1]
]
if this and values != 1:
return this[0]
elif this:
return this[0][0]
elif values != 1:
return [0] * values
else:
return 0
monthlyStatement = []
for this_month in paged_months:
thisMonthlyStatement = {}
thisMonthlyStatement['month'] = this_month
thisMonthlyStatement['month_name'] = month_name[
this_month[1]] + ' ' + str(this_month[0])
thisMonthlyStatement['month_date'] = datetime(this_month[0],
this_month[1], 1)
revenueList = valueOf(totalRevenuesByMonth, this_month, values=3)
thisMonthlyStatement[
'revenues'] = revenueList[0] + revenueList[1] - revenueList[2]
totalExpenseList = valueOf(totalExpensesByMonth, this_month, values=3)
instructionExpenseList = valueOf(instructionExpensesByMonth,
this_month,
values=3)
venueExpenseList = valueOf(venueExpensesByMonth, this_month, values=3)
thisMonthlyStatement['expenses'] = {
'total': (totalExpenseList[0] or 0) + (totalExpenseList[1] or 0) -
(totalExpenseList[2] or 0),
'instruction': (instructionExpenseList[0] or 0) +
(instructionExpenseList[1] or 0) -
(instructionExpenseList[2] or 0),
'venue': (venueExpenseList[0] or 0) + (venueExpenseList[1] or 0) -
(venueExpenseList[2] or 0),
}
thisMonthlyStatement['expenses']['other'] = thisMonthlyStatement[
'expenses']['total'] - thisMonthlyStatement['expenses'][
'instruction'] - thisMonthlyStatement['expenses']['venue']
thisMonthlyStatement['registrations'] = valueOf(
registrationsByMonth, this_month)
thisMonthlyStatement['net_profit'] = thisMonthlyStatement[
'revenues'] - thisMonthlyStatement['expenses']['total']
monthlyStatement.append(thisMonthlyStatement)
monthlyStatement.sort(key=lambda x: x['month'], reverse=True)
# Return not just the statement, but also the paginator in the style of ListView's paginate_queryset()
return (paginator, paged_months, monthlyStatement,
paged_months.has_other_pages())
def createGenericExpenseItems(request=None, datetimeTuple=None, rule=None):
'''
Generic repeated expenses are created by just entering an
expense at each exact point specified by the rule, without
regard for whether events are scheduled in the specified
window,
'''
# These are used repeatedly, so they are put at the top
submissionUser = getattr(request, 'user', None)
# Return the number of new expense items created
generate_count = 0
# First, construct the set of rules that need to be checked for affiliated events
rule_filters = Q(disabled=False) & Q(rentalRate__gt=0) & \
Q(genericrepeatedexpense__isnull=False)
if rule:
rule_filters = rule_filters & Q(id=rule.id)
rulesToCheck = RepeatedExpenseRule.objects.filter(rule_filters).distinct()
# These are the filters place on Events that overlap the window in which expenses are being generated.
if datetimeTuple and len(datetimeTuple) == 2:
timelist = list(datetimeTuple)
timelist.sort()
else:
timelist = None
# Now, we loop through the set of rules that need to be applied, check for an
# existing expense item at each point specified by the rule, and create a new
# expense if one does not exist.
for rule in rulesToCheck:
limits = timelist or [
ensure_timezone(datetime.min),
ensure_timezone(datetime.max)
]
if rule.advanceDays:
limits[1] = min(limits[1],
timezone.now() + timedelta(days=rule.advanceDays))
if rule.priorDays:
limits[0] = max(limits[0],
timezone.now() - timedelta(days=rule.priorDays))
if rule.startDate:
limits[0] = max(
limits[0],
timezone.now().replace(
year=rule.startDate.year,
month=rule.startDate.month,
day=rule.startDate.day,
hour=0,
minute=0,
second=0,
microsecond=0,
))
if rule.endDate:
limits[1] = min(
limits[1],
timezone.now().replace(
year=rule.endDate.year,
month=rule.endDate.month,
day=rule.endDate.day,
hour=0,
minute=0,
second=0,
microsecond=0,
))
# Find the first start time greater than the lower bound time.
if rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.hourly:
this_time = limits[0].replace(minute=0, second=0, microsecond=0)
if this_time < limits[0]:
this_time += timedelta(hours=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.daily:
this_time = limits[0].replace(hour=rule.dayStarts,
minute=0,
second=0,
microsecond=0)
if this_time < limits[0]:
this_time += timedelta(days=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.weekly:
offset = limits[0].weekday() - rule.weekStarts
this_time = limits[0].replace(day=limits[0].day - offset,
hour=rule.dayStarts,
minute=0,
second=0,
microsecond=0)
if this_time < limits[0]:
this_time += timedelta(days=7)
else:
this_time = limits[0].replace(day=rule.monthStarts,
hour=rule.dayStarts,
minute=0,
second=0,
microsecond=0)
if this_time < limits[0]:
this_time += relativedelta(months=1)
while this_time <= limits[1]:
defaults_dict = {
'category': rule.category,
'description': rule.name,
'submissionUser': submissionUser,
'total': rule.rentalRate,
'accrualDate': this_time,
'payTo': rule.payTo,
}
item = ExpenseItem.objects.get_or_create(expenseRule=rule,
periodStart=this_time,
periodEnd=this_time,
defaults=defaults_dict)[0]
if created:
generate_count += 1
if rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.hourly:
this_time += timedelta(hours=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.daily:
this_time += timedelta(days=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.weekly:
this_time += timedelta(days=7)
else:
this_time += relativedelta(months=1)
rulesToCheck.update(lastRun=timezone.now())
return generate_count
def get_context_data(self,**kwargs):
instructor = self.object
context = {}
query_filter = Q()
# These will be passed to the template
year = self.kwargs.get('year')
eligible_years = list(set([x.year for x in ExpenseItem.objects.values_list('accrualDate',flat=True).distinct()]))
eligible_years.sort(reverse=True)
if not year or year == 'all':
int_year = None
year = 'all'
else:
try:
int_year = int(year)
# Check for year in kwargs and ensure that it is eligible
if int_year not in eligible_years:
raise Http404(_("Invalid year."))
query_filter = query_filter & (Q(accrualDate__year=int_year) | Q(paymentDate__year=int_year) | Q(submissionDate__year=int_year))
except (ValueError, TypeError):
raise Http404(_("Invalid year."))
# No point in continuing if we can't actually match this instructor to their payments.
if not hasattr(instructor,'userAccount'):
return super(DetailView, self).get_context_data(instructor=instructor)
all_payments = instructor.userAccount.payToUser.filter(query_filter).order_by('-submissionDate')
paid_items = all_payments.filter(**{'paid':True,'reimbursement':False}).order_by('-paymentDate')
unpaid_items = all_payments.filter(**{'paid':False}).order_by('-submissionDate')
reimbursement_items = all_payments.filter(**{'paid':True,'reimbursement':True}).order_by('-paymentDate')
if int_year:
time_lb = ensure_timezone(datetime(int_year,1,1,0,0))
time_ub = ensure_timezone(datetime(int_year + 1,1,1,0,0))
else:
time_lb = ensure_timezone(datetime(timezone.now().year,1,1,0,0))
time_ub = ensure_timezone(datetime(timezone.now().year + 1,1,1,0,0))
paid_this_year = paid_items.filter(paymentDate__gte=time_lb,paymentDate__lt=time_ub).order_by('-paymentDate')
accrued_paid_this_year = paid_items.filter(accrualDate__gte=time_lb,accrualDate__lt=time_ub).order_by('-paymentDate')
reimbursements_this_year = all_payments.filter(paymentDate__gte=time_lb,paymentDate__lt=time_ub,paid=True,reimbursement=True)
context.update({
'instructor': instructor,
'current_year': year,
'eligible_years': eligible_years,
'all_payments': all_payments,
'paid_items': paid_items,
'unpaid_items': unpaid_items,
'reimbursement_items': reimbursement_items,
'paid_this_year': paid_this_year,
'accrued_paid_this_year': accrued_paid_this_year,
'reimbursements_this_year': reimbursements_this_year,
'total_paid_alltime': sum(filter(None,[x.total for x in paid_items])),
'total_awaiting_payment': sum(filter(None,[x.total for x in unpaid_items])),
'total_paid_this_year': sum(filter(None,[x.total for x in paid_this_year])),
'total_reimbursements': sum(filter(None,[x.total for x in reimbursements_this_year])),
})
# Note: This get the detailview's context, not all the mixins. Supering itself led to an infinite loop.
return super(DetailView, self).get_context_data(**context)
def get_context_data(self, **kwargs):
instructor = self.object
context = {}
query_filter = Q()
# These will be passed to the template
year = self.kwargs.get('year')
eligible_years = list(
set([
x.year
for x in ExpenseItem.objects.values_list('accrualDate',
flat=True).distinct()
]))
eligible_years.sort(reverse=True)
if not year or year == 'all':
int_year = None
year = 'all'
else:
try:
int_year = int(year)
# Check for year in kwargs and ensure that it is eligible
if int_year not in eligible_years:
raise Http404(_("Invalid year."))
query_filter = query_filter & (
Q(accrualDate__year=int_year)
| Q(paymentDate__year=int_year)
| Q(submissionDate__year=int_year))
except (ValueError, TypeError):
raise Http404(_("Invalid year."))
# No point in continuing if we can't actually match this instructor to their payments.
if not hasattr(instructor, 'userAccount'):
return super(DetailView,
self).get_context_data(instructor=instructor)
all_payments = instructor.userAccount.payToUser.filter(
query_filter).order_by('-submissionDate')
paid_items = all_payments.filter(**{
'paid': True,
'reimbursement': False
}).order_by('-paymentDate')
unpaid_items = all_payments.filter(**{
'paid': False
}).order_by('-submissionDate')
reimbursement_items = all_payments.filter(**{
'paid': True,
'reimbursement': True
}).order_by('-paymentDate')
if int_year:
time_lb = ensure_timezone(datetime(int_year, 1, 1, 0, 0))
time_ub = ensure_timezone(datetime(int_year + 1, 1, 1, 0, 0))
else:
time_lb = ensure_timezone(datetime(timezone.now().year, 1, 1, 0,
0))
time_ub = ensure_timezone(
datetime(timezone.now().year + 1, 1, 1, 0, 0))
paid_this_year = paid_items.filter(
paymentDate__gte=time_lb,
paymentDate__lt=time_ub).order_by('-paymentDate')
accrued_paid_this_year = paid_items.filter(
accrualDate__gte=time_lb,
accrualDate__lt=time_ub).order_by('-paymentDate')
reimbursements_this_year = all_payments.filter(
paymentDate__gte=time_lb,
paymentDate__lt=time_ub,
paid=True,
reimbursement=True)
context.update({
'instructor':
instructor,
'current_year':
year,
'eligible_years':
eligible_years,
'all_payments':
all_payments,
'paid_items':
paid_items,
'unpaid_items':
unpaid_items,
'reimbursement_items':
reimbursement_items,
'paid_this_year':
paid_this_year,
'accrued_paid_this_year':
accrued_paid_this_year,
'reimbursements_this_year':
reimbursements_this_year,
'total_paid_alltime':
sum(filter(None, [x.total for x in paid_items])),
'total_awaiting_payment':
sum(filter(None, [x.total for x in unpaid_items])),
'total_paid_this_year':
sum(filter(None, [x.total for x in paid_this_year])),
'total_reimbursements':
sum(filter(None, [x.total for x in reimbursements_this_year])),
})
# Note: This get the detailview's context, not all the mixins. Supering itself led to an infinite loop.
return super(DetailView, self).get_context_data(**context)
def get_context_data(self, **kwargs):
context = kwargs.copy()
timeFilters = {}
# Determine the period over which the statement should be produced.
year = kwargs.get('year')
month = kwargs.get('month')
startDate = kwargs.get('startDate')
endDate = kwargs.get('endDate')
basis = kwargs.get('basis')
context.update({
'basis': basis,
'basis_name': EXPENSE_BASES[basis],
'rangeTitle': '',
})
if startDate:
timeFilters['%s__gte' % basis] = startDate
context['rangeType'] = 'Date Range'
context['rangeTitle'] += _('From %s' %
startDate.strftime('%b. %d, %Y'))
if endDate:
timeFilters['%s__lt' % basis] = endDate
context['rangeType'] = 'Date Range'
context['rangeTitle'] += _('To %s' %
endDate.strftime('%b. %d, %Y'))
if not startDate and not endDate:
if month and year:
end_month = ((month) % 12) + 1
end_year = year
if end_month == 1:
end_year = year + 1
timeFilters['%s__gte' % basis] = ensure_timezone(
datetime(year, month, 1))
timeFilters['%s__lt' % basis] = ensure_timezone(
datetime(end_year, end_month, 1))
context['rangeType'] = 'Month'
context['rangeTitle'] = '%s %s' % (month_name[month], year)
elif year:
timeFilters['%s__gte' % basis] = ensure_timezone(
datetime(year, 1, 1))
timeFilters['%s__lt' % basis] = ensure_timezone(
datetime(year + 1, 1, 1))
context['rangeType'] = 'Year'
context['rangeTitle'] = '%s' % year
else:
# Assume year to date if nothing otherwise specified
timeFilters['%s__gte' % basis] = ensure_timezone(
datetime(timezone.now().year, 1, 1))
timeFilters['%s__lt' % basis] = ensure_timezone(
datetime(timezone.now().year + 1, 1, 1))
context['rangeType'] = 'YTD'
context['rangeTitle'] = _('Calendar Year To Date')
context['startDate'] = timeFilters['%s__gte' % basis]
context['endDate'] = timeFilters['%s__lt' % basis]
# Revenues are booked on receipt basis, not payment/approval basis
rev_timeFilters = timeFilters.copy()
rev_basis = basis
if basis in ['paymentDate', 'approvalDate']:
rev_basis = 'receivedDate'
rev_timeFilters['receivedDate__gte'] = rev_timeFilters['%s__gte' %
basis]
rev_timeFilters['receivedDate__lt'] = rev_timeFilters['%s__lt' %
basis]
del rev_timeFilters['%s__gte' % basis]
del rev_timeFilters['%s__lt' % basis]
expenseItems = ExpenseItem.objects.filter(**timeFilters).annotate(
net=F('total') + F('adjustments') + F('fees'),
basisDate=Min(basis)).order_by(basis)
revenueItems = RevenueItem.objects.filter(**rev_timeFilters).annotate(
net=F('total') + F('adjustments') - F('fees'),
basisDate=Min(rev_basis)).order_by(rev_basis)
context['expenseItems'] = expenseItems
context['revenueItems'] = revenueItems
# Registration revenues, instruction and venue expenses
# are broken out separately.
context.update({
'instructionExpenseItems':
expenseItems.filter(category__in=[
getConstant('financial__classInstructionExpenseCat'),
getConstant('financial__assistantClassInstructionExpenseCat')
]).order_by('payToUser__last_name', 'payToUser__first_name'),
'venueExpenseItems':
expenseItems.filter(category=getConstant(
'financial__venueRentalExpenseCat')).order_by('payToLocation'),
'otherExpenseItems':
expenseItems.exclude(category__in=[
getConstant('financial__classInstructionExpenseCat'),
getConstant('financial__assistantClassInstructionExpenseCat'),
getConstant('financial__venueRentalExpenseCat')
]).order_by('category'),
'expenseCategoryTotals':
ExpenseCategory.objects.filter(
expenseitem__in=expenseItems).annotate(
category_total=Sum('expenseitem__total'),
category_adjustments=Sum('expenseitem__adjustments'),
category_fees=Sum('expenseitem__fees')).annotate(
category_net=F('category_total') +
F('category_adjustments') + F('category_fees')),
})
context.update({
'instructionExpenseInstructorTotals':
User.objects.filter(
payToUser__in=context['instructionExpenseItems']).annotate(
instructor_total=Sum('payToUser__total'),
instructor_adjustments=Sum('payToUser__adjustments'),
instructor_fees=Sum('payToUser__fees')).annotate(
instructor_net=F('instructor_total') +
F('instructor_adjustments') + F('instructor_fees')),
'instructionExpenseOtherTotal':
context['instructionExpenseItems'].filter(
payToUser__isnull=True).annotate(
net=F('total') + F('adjustments') + F('fees')).aggregate(
instructor_total=Sum('total'),
instructor_adjustments=Sum('adjustments'),
instructor_fees=Sum('fees'),
instructor_net=Sum('net')),
'venueExpenseVenueTotals':
Location.objects.filter(
expenseitem__in=context['venueExpenseItems']).annotate(
location_total=Sum('expenseitem__total'),
location_adjustments=Sum('expenseitem__adjustments'),
location_fees=Sum('expenseitem__fees')).annotate(
location_net=F('location_total') +
F('location_adjustments') + F('location_fees')),
'venueExpenseOtherTotal':
context['venueExpenseItems'].filter(
payToLocation__isnull=True).annotate(
location_net=F('total') + F('adjustments') +
F('fees')).aggregate(
location_total=Sum('total'),
location_adjustments=Sum('adjustments'),
location_fees=Sum('fees'),
location_net=Sum('net')),
'totalInstructionExpenses':
sum([
x.category_net or 0
for x in context['expenseCategoryTotals'].filter(id__in=[
getConstant('financial__classInstructionExpenseCat').id,
getConstant(
'financial__assistantClassInstructionExpenseCat').id
])
]),
'totalVenueExpenses':
sum([
x.category_net or 0
for x in context['expenseCategoryTotals'].filter(
id=getConstant('financial__venueRentalExpenseCat').id)
]),
'totalOtherExpenses':
sum([
x.category_net or 0
for x in context['expenseCategoryTotals'].exclude(id__in=[
getConstant('financial__classInstructionExpenseCat').id,
getConstant(
'financial__assistantClassInstructionExpenseCat').id,
getConstant('financial__venueRentalExpenseCat').id
])
]),
'totalExpenses':
sum([
x.category_net or 0 for x in context['expenseCategoryTotals']
]),
})
context.update({
'registrationRevenueItems':
revenueItems.filter(
category=getConstant('financial__registrationsRevenueCat')).
order_by('-event__startTime'),
'otherRevenueItems':
revenueItems.exclude(category=getConstant(
'financial__registrationsRevenueCat')).order_by('category'),
'revenueCategoryTotals':
RevenueCategory.objects.filter(
revenueitem__in=revenueItems).annotate(
category_total=Sum('revenueitem__total'),
category_adjustments=Sum('revenueitem__adjustments'),
category_fees=Sum('revenueitem__fees')).annotate(
category_net=F('category_total') +
F('category_adjustments') - F('category_fees')),
})
context.update({
'registrationRevenueEventTotals':
Event.objects.filter(
eventregistration__invoiceitem__revenueitem__in=context[
'registrationRevenueItems']).
annotate(
event_total=Sum(
'eventregistration__invoiceitem__revenueitem__total'),
event_adjustments=Sum(
'eventregistration__invoiceitem__revenueitem__adjustments'
),
event_fees=Sum(
'eventregistration__invoiceitem__revenueitem__fees')).
annotate(event_net=F('event_total') + F('event_adjustments') -
F('event_fees')),
'registrationRevenueOtherTotal':
context['registrationRevenueItems'].filter(
invoiceItem__finalEventRegistration__isnull=True).annotate(
event_net=F('total') + F('adjustments') -
F('fees')).aggregate(event_total=Sum('total'),
event_adjustments=Sum('adjustments'),
event_fees=Sum('fees'),
event_net=Sum('net')),
'totalRegistrationRevenues':
sum([
x.category_net or 0
for x in context['revenueCategoryTotals'].filter(
id=getConstant('financial__registrationsRevenueCat').id)
]),
'totalOtherRevenues':
sum([
x.category_net or 0
for x in context['revenueCategoryTotals'].exclude(
id=getConstant('financial__registrationsRevenueCat').id)
]),
'totalRevenues':
sum([
x.category_net or 0 for x in context['revenueCategoryTotals']
]),
})
context.update({
'netProfit':
context['totalRevenues'] - context['totalExpenses'],
})
return super(self.__class__, self).get_context_data(**context)
def prepareStatementByPeriod(**kwargs):
basis = kwargs.get('basis')
if basis not in EXPENSE_BASES.keys():
basis = 'accrualDate'
rev_basis = basis
if rev_basis in ['paymentDate', 'approvalDate']:
rev_basis = 'receivedDate'
# Used to filter the set of observations shown
start_date = kwargs.get('start_date')
end_date = kwargs.get('end_date')
year = kwargs.get('year')
# Needed to ensure that everything is in local time.
localTimeZone = None
if getattr(settings, 'TIME_ZONE', None):
localTimeZone = pytz.timezone(getattr(settings, 'TIME_ZONE'))
# Currently supported period types include month and date. The variables
# values, annotations, and order_by are used repeatedly for constructing
# queries.
period_type = kwargs.get('type', 'month')
values = ('basisDate',)
order_by = ('-basisDate',)
def date_for_month(*args,**kwargs):
return TruncDate(TruncMonth(*args,**kwargs))
# NOTE: Django 2.1 introduces "TruncWeek", which should be added to the
# options once the project requires Django 2.1.
if period_type == 'month':
DateFunc = date_for_month
elif period_type == 'date':
DateFunc = TruncDate
else:
raise ValueError(_('Invalid period type passed as kwarg.'))
time_annotations = {'basisDate': DateFunc(basis, tzinfo=localTimeZone)}
rev_time_annotations = {'basisDate': DateFunc(rev_basis, tzinfo=localTimeZone)}
reg_time_annotations = {
'basisDate': DateFunc(
'eventregistration__event__startTime', tzinfo=localTimeZone
)
}
timeFilters = {'%s__isnull' % basis: False}
rev_timeFilters = {'%s__isnull' % rev_basis: False}
if start_date:
timeFilters['%s__gte' % basis] = start_date
rev_timeFilters['%s__gte' % rev_basis] = start_date
if end_date:
timeFilters['%s__lt' % basis] = end_date
rev_timeFilters['%s__lt' % rev_basis] = end_date
if year and not (start_date or end_date):
start_limit = ensure_timezone(datetime(year, 1, 1))
end_limit = ensure_timezone(datetime(year + 1, 1, 1))
timeFilters['%s__gte' % basis] = start_limit
rev_timeFilters['%s__gte' % rev_basis] = start_limit
timeFilters['%s__lt' % basis] = end_limit
rev_timeFilters['%s__lt' % rev_basis] = end_limit
# In order to provide major calculations, it's easiest to just get all the
# Expense and Revenue line items at once To avoid too many unnecessary
# calls, be sure to filter off these querysets rather than pulling them
# again.
expenseitems = ExpenseItem.objects.select_related('event').annotate(
**time_annotations
).filter(**timeFilters)
revenueitems = RevenueItem.objects.select_related('event').annotate(
**rev_time_annotations
).filter(**rev_timeFilters)
# Get the set of possible dates or months.
all_periods_set = set()
for qs in [expenseitems, revenueitems]:
all_periods_set.update(list(
qs.order_by().values('basisDate').annotate(n=Count('pk')).values_list('basisDate')
))
all_periods = [x[0] for x in all_periods_set]
all_periods.sort(reverse=True)
paginator = Paginator(all_periods, kwargs.get('paginate_by', 50))
try:
paged_periods = paginator.page(kwargs.get('page', 1))
except PageNotAnInteger:
if kwargs.get('page') == 'last':
paged_periods = paginator.page(paginator.num_pages)
else:
paged_periods = paginator.page(1)
except EmptyPage:
paged_periods = paginator.page(paginator.num_pages)
# Define common annotations used repeatedly in queries.
sum_annotations = (Sum('total'), Sum('adjustments'), Sum('fees'))
# Get everything by month in one query each, then pull from this.
totalExpensesByPeriod = expenseitems.values(*values).annotate(
*sum_annotations
).order_by(*order_by)
instructionExpensesByPeriod = expenseitems.filter(
category__in=[
getConstant('financial__classInstructionExpenseCat'),
getConstant('financial__assistantClassInstructionExpenseCat')
]
).values(*values).annotate(*sum_annotations).order_by(*order_by)
venueExpensesByPeriod = expenseitems.filter(
category=getConstant('financial__venueRentalExpenseCat')
).values(*values).annotate(*sum_annotations).order_by(*order_by)
totalRevenuesByPeriod = revenueitems.values(*values).annotate(
*sum_annotations
).order_by(*order_by)
# This includes only registrations in which a series was registered for (and was not cancelled)
registrationsByPeriod = Registration.objects.filter(
eventregistration__cancelled=False
).annotate(
**reg_time_annotations
).values(*values).annotate(count=Count('id')).order_by(*order_by)
periodStatement = []
for this_period in paged_periods:
thisPeriodStatement = {}
thisPeriodStatement['period'] = this_period
this_period_date = datetime.combine(this_period, datetime.min.time())
if period_type == 'month':
thisPeriodStatement.update({
'period_date': this_period_date,
'period_name': this_period_date.strftime('%B %Y'),
})
elif period_type == 'date':
thisPeriodStatement.update({
'period_date': this_period_date,
'period_name': this_period.strftime('%b. %-d, %Y'),
})
def get_net(this_dict):
'''
Convenience function to calculate net value incorporating adjustments and fees.
'''
if not isinstance(this_dict,dict):
this_dict = {}
return this_dict.get('total__sum',0) + \
this_dict.get('adjustments__sum',0) - \
this_dict.get('fees__sum',0)
thisPeriodStatement['revenues'] = get_net(
totalRevenuesByPeriod.filter(basisDate=this_period).first()
)
thisPeriodStatement['expenses'] = {
'total': get_net(totalExpensesByPeriod.filter(basisDate=this_period).first()),
'instruction': get_net(
instructionExpensesByPeriod.filter(basisDate=this_period).first()
),
'venue': get_net(
venueExpensesByPeriod.filter(basisDate=this_period).first()
),
}
thisPeriodStatement['expenses']['other'] = (
thisPeriodStatement['expenses']['total'] -
thisPeriodStatement['expenses']['instruction'] -
thisPeriodStatement['expenses']['venue']
)
thisPeriodStatement['registrations'] = (
registrationsByPeriod.filter(basisDate=this_period).first() or {}
).get('count',0)
thisPeriodStatement['net_profit'] = (
thisPeriodStatement['revenues'] - thisPeriodStatement['expenses']['total']
)
periodStatement.append(thisPeriodStatement)
periodStatement.sort(key=lambda x: x['period_date'], reverse=True)
# Return not just the statement, but also the paginator in the style of
# ListView's paginate_queryset()
return (paginator, paged_periods, periodStatement, paged_periods.has_other_pages())
def createGenericExpenseItems(request=None,datetimeTuple=None,rule=None):
'''
Generic repeated expenses are created by just entering an
expense at each exact point specified by the rule, without
regard for whether events are scheduled in the specified
window,
'''
# These are used repeatedly, so they are put at the top
submissionUser = getattr(request,'user',None)
# Return the number of new expense items created
generate_count = 0
# First, construct the set of rules that need to be checked for affiliated events
rule_filters = Q(disabled=False) & Q(rentalRate__gt=0) & \
Q(genericrepeatedexpense__isnull=False)
if rule:
rule_filters = rule_filters & Q(id=rule.id)
rulesToCheck = RepeatedExpenseRule.objects.filter(rule_filters).distinct()
# These are the filters place on Events that overlap the window in which expenses are being generated.
if datetimeTuple and len(datetimeTuple) == 2:
timelist = list(datetimeTuple)
timelist.sort()
else:
timelist = None
# Now, we loop through the set of rules that need to be applied, check for an
# existing expense item at each point specified by the rule, and create a new
# expense if one does not exist.
for rule in rulesToCheck:
limits = timelist or [ensure_timezone(datetime.min), ensure_timezone(datetime.max)]
if rule.advanceDays:
limits[1] = min(limits[1],timezone.now() + timedelta(days=rule.advanceDays))
if rule.priorDays:
limits[0] = max(limits[0],timezone.now() - timedelta(days=rule.priorDays))
if rule.startDate:
limits[0] = max(
limits[0],
timezone.now().replace(
year=rule.startDate.year,month=rule.startDate.month,day=rule.startDate.day,
hour=0,minute=0,second=0,microsecond=0,
)
)
if rule.endDate:
limits[1] = min(
limits[1],
timezone.now().replace(
year=rule.endDate.year,month=rule.endDate.month,day=rule.endDate.day,
hour=0,minute=0,second=0,microsecond=0,
)
)
# Find the first start time greater than the lower bound time.
if rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.hourly:
this_time = limits[0].replace(minute=0,second=0,microsecond=0)
if this_time < limits[0]:
this_time += timedelta(hours=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.daily:
this_time = limits[0].replace(hour=rule.dayStarts,minute=0,second=0,microsecond=0)
if this_time < limits[0]:
this_time += timedelta(days=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.weekly:
offset = limits[0].weekday() - rule.weekStarts
this_time = limits[0].replace(day=limits[0].day - offset, hour=rule.dayStarts,minute=0,second=0,microsecond=0)
if this_time < limits[0]:
this_time += timedelta(days=7)
else:
this_time = limits[0].replace(day=rule.monthStarts,hour=rule.dayStarts,minute=0,second=0,microsecond=0)
if this_time < limits[0]:
this_time += relativedelta(months=1)
while this_time <= limits[1]:
defaults_dict = {
'category': rule.category,
'description': rule.name,
'submissionUser': submissionUser,
'total': rule.rentalRate,
'accrualDate': this_time,
'payToUser': rule.payToUser,
'payToLocation': rule.payToLocation,
'payToName': rule.payToName,
}
item, created = ExpenseItem.objects.get_or_create(
expenseRule=rule,
periodStart=this_time,
periodEnd=this_time,
defaults=defaults_dict
)
if created:
generate_count += 1
if rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.hourly:
this_time += timedelta(hours=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.daily:
this_time += timedelta(days=1)
elif rule.applyRateRule == RepeatedExpenseRule.RateRuleChoices.weekly:
this_time += timedelta(days=7)
else:
this_time += relativedelta(months=1)
rulesToCheck.update(lastRun=timezone.now())
return generate_count
def prepareStatementByMonth(**kwargs):
basis = kwargs.get('basis')
if basis not in EXPENSE_BASES.keys():
basis = 'accrualDate'
rev_basis = basis
if rev_basis in ['paymentDate','approvalDate']:
rev_basis = 'receivedDate'
start_date = kwargs.get('start_date')
end_date = kwargs.get('end_date')
year = kwargs.get('year')
# In order to provide major calculations, it's easiest to just get all the Expense and Revenue line items at once
# To avoid too many unnecessary calls, be sure to filter off these querysets rather than pulling them again.
expenseitems = ExpenseItem.objects.select_related('event').annotate(year=ExtractYear(basis,'year'),month=ExtractMonth(basis))
revenueitems = RevenueItem.objects.select_related('event').annotate(year=ExtractYear(rev_basis,'year'),month=ExtractMonth(rev_basis))
timeFilters = {}
rev_timeFilters = {}
if start_date:
timeFilters['%s__gte' % basis] = start_date
rev_timeFilters['%s__gte' % rev_basis] = start_date
if end_date:
timeFilters['%s__lt' % basis] = end_date
rev_timeFilters['%s__lt' % rev_basis] = end_date
if year and not (start_date or end_date):
start_limit = ensure_timezone(datetime(year,1,1))
end_limit = ensure_timezone(datetime(year + 1,1,1))
timeFilters['%s__gte' % basis] = start_limit
rev_timeFilters['%s__gte' % rev_basis] = start_limit
timeFilters['%s__lt' % basis] = end_limit
rev_timeFilters['%s__lt' % rev_basis] = end_limit
if timeFilters:
expenseitems = expenseitems.filter(**timeFilters)
revenueitems = revenueitems.filter(**rev_timeFilters)
# Get the set of possible month tuples. If we are not given a date window, default to the last 12 months.
allMonths = [(x.year,x.month) for x in revenueitems.dates(rev_basis,'month')]
allMonths.sort(reverse=True)
paginator = Paginator(allMonths,kwargs.get('paginate_by',50))
try:
paged_months = paginator.page(kwargs.get('page',1))
except PageNotAnInteger:
if kwargs.get('page') == 'last':
paged_months = paginator.page(paginator.num_pages)
else:
paged_months = paginator.page(1)
except EmptyPage:
paged_months = paginator.page(paginator.num_pages)
# Get everything by month in one query each, then pull from this.
totalExpensesByMonth = expenseitems.values_list('year','month').annotate(Sum('total'),Sum('adjustments'),Sum('fees')).order_by('-year','-month')
instructionExpensesByMonth = expenseitems.filter(category__in=[getConstant('financial__classInstructionExpenseCat'),getConstant('financial__assistantClassInstructionExpenseCat')]).values_list('year','month').annotate(Sum('total'),Sum('adjustments'),Sum('fees')).order_by('-year','-month')
venueExpensesByMonth = expenseitems.filter(category=getConstant('financial__venueRentalExpenseCat')).values_list('year','month').annotate(Sum('total'),Sum('adjustments'),Sum('fees')).order_by('-year','-month')
totalRevenuesByMonth = revenueitems.values_list('year','month').annotate(Sum('total'),Sum('adjustments'),Sum('fees')).order_by('-year','-month')
# This includes only registrations in which a series was registered for (and was not cancelled)
registrationsByMonth = Registration.objects.filter(eventregistration__cancelled=False).annotate(year=ExtractYear('dateTime'),month=ExtractMonth('dateTime')).values_list('year','month').annotate(count=Count('id')).order_by('-year','-month')
# This little helper function avoids 0-item list issues and encapsulates the
# needed list iterator for readability
def valueOf(resultset,month,values=1):
this = [x[2:(2 + values)] for x in resultset if x[0] == month[0] and x[1] == month[1]]
if this and values != 1:
return this[0]
elif this:
return this[0][0]
elif values != 1:
return [0] * values
else:
return 0
monthlyStatement = []
for this_month in paged_months:
thisMonthlyStatement = {}
thisMonthlyStatement['month'] = this_month
thisMonthlyStatement['month_name'] = month_name[this_month[1]] + ' ' + str(this_month[0])
thisMonthlyStatement['month_date'] = datetime(this_month[0],this_month[1],1)
revenueList = valueOf(totalRevenuesByMonth,this_month,values=3)
thisMonthlyStatement['revenues'] = revenueList[0] + revenueList[1] - revenueList[2]
totalExpenseList = valueOf(totalExpensesByMonth,this_month,values=3)
instructionExpenseList = valueOf(instructionExpensesByMonth,this_month,values=3)
venueExpenseList = valueOf(venueExpensesByMonth,this_month,values=3)
thisMonthlyStatement['expenses'] = {
'total': (totalExpenseList[0] or 0) + (totalExpenseList[1] or 0) - (totalExpenseList[2] or 0),
'instruction': (instructionExpenseList[0] or 0) + (instructionExpenseList[1] or 0) - (instructionExpenseList[2] or 0),
'venue': (venueExpenseList[0] or 0) + (venueExpenseList[1] or 0) - (venueExpenseList[2] or 0),
}
thisMonthlyStatement['expenses']['other'] = thisMonthlyStatement['expenses']['total'] - thisMonthlyStatement['expenses']['instruction'] - thisMonthlyStatement['expenses']['venue']
thisMonthlyStatement['registrations'] = valueOf(registrationsByMonth,this_month)
thisMonthlyStatement['net_profit'] = thisMonthlyStatement['revenues'] - thisMonthlyStatement['expenses']['total']
monthlyStatement.append(thisMonthlyStatement)
monthlyStatement.sort(key=lambda x: x['month'], reverse=True)
# Return not just the statement, but also the paginator in the style of ListView's paginate_queryset()
return (paginator, paged_months, monthlyStatement, paged_months.has_other_pages())
def get_context_data(self,**kwargs):
context = kwargs.copy()
timeFilters = {}
# Determine the period over which the statement should be produced.
year = kwargs.get('year')
month = kwargs.get('month')
startDate = kwargs.get('startDate')
endDate = kwargs.get('endDate')
basis = kwargs.get('basis')
context.update({
'basis': basis,
'basis_name': EXPENSE_BASES[basis],
'rangeTitle': '',
})
if startDate:
timeFilters['%s__gte' % basis] = startDate
context['rangeType'] = 'Date Range'
context['rangeTitle'] += _('From %s' % startDate.strftime('%b. %d, %Y'))
if endDate:
timeFilters['%s__lt' % basis] = endDate
context['rangeType'] = 'Date Range'
context['rangeTitle'] += _('To %s' % endDate.strftime('%b. %d, %Y'))
if not startDate and not endDate:
if month and year:
end_month = ((month) % 12) + 1
end_year = year
if end_month == 1:
end_year = year + 1
timeFilters['%s__gte' % basis] = ensure_timezone(datetime(year,month,1))
timeFilters['%s__lt' % basis] = ensure_timezone(datetime(end_year,end_month,1))
context['rangeType'] = 'Month'
context['rangeTitle'] = '%s %s' % (month_name[month], year)
elif year:
timeFilters['%s__gte' % basis] = ensure_timezone(datetime(year,1,1))
timeFilters['%s__lt' % basis] = ensure_timezone(datetime(year + 1,1,1))
context['rangeType'] = 'Year'
context['rangeTitle'] = '%s' % year
else:
# Assume year to date if nothing otherwise specified
timeFilters['%s__gte' % basis] = ensure_timezone(datetime(timezone.now().year,1,1))
timeFilters['%s__lt' % basis] = ensure_timezone(datetime(timezone.now().year + 1,1,1))
context['rangeType'] = 'YTD'
context['rangeTitle'] = _('Calendar Year To Date')
context['startDate'] = timeFilters['%s__gte' % basis]
context['endDate'] = timeFilters['%s__lt' % basis]
# Revenues are booked on receipt basis, not payment/approval basis
rev_timeFilters = timeFilters.copy()
rev_basis = basis
if basis in ['paymentDate', 'approvalDate']:
rev_basis = 'receivedDate'
rev_timeFilters['receivedDate__gte'] = rev_timeFilters['%s__gte' % basis]
rev_timeFilters['receivedDate__lt'] = rev_timeFilters['%s__lt' % basis]
del rev_timeFilters['%s__gte' % basis]
del rev_timeFilters['%s__lt' % basis]
expenseItems = ExpenseItem.objects.filter(**timeFilters).annotate(net=F('total') + F('adjustments') + F('fees'),basisDate=Min(basis)).order_by(basis)
revenueItems = RevenueItem.objects.filter(**rev_timeFilters).annotate(net=F('total') + F('adjustments') - F('fees'),basisDate=Min(rev_basis)).order_by(rev_basis)
context['expenseItems'] = expenseItems
context['revenueItems'] = revenueItems
# Registration revenues, instruction and venue expenses
# are broken out separately.
context.update({
'instructionExpenseItems': expenseItems.filter(category__in=[getConstant('financial__classInstructionExpenseCat'),getConstant('financial__assistantClassInstructionExpenseCat')]).order_by('payToUser__last_name','payToUser__first_name'),
'venueExpenseItems': expenseItems.filter(category=getConstant('financial__venueRentalExpenseCat')).order_by('payToLocation'),
'otherExpenseItems': expenseItems.exclude(category__in=[getConstant('financial__classInstructionExpenseCat'),getConstant('financial__assistantClassInstructionExpenseCat'),getConstant('financial__venueRentalExpenseCat')]).order_by('category'),
'expenseCategoryTotals': ExpenseCategory.objects.filter(expenseitem__in=expenseItems).annotate(category_total=Sum('expenseitem__total'),category_adjustments=Sum('expenseitem__adjustments'),category_fees=Sum('expenseitem__fees')).annotate(category_net=F('category_total') + F('category_adjustments') + F('category_fees')),
})
context.update({
'instructionExpenseInstructorTotals': User.objects.filter(payToUser__in=context['instructionExpenseItems']).annotate(instructor_total=Sum('payToUser__total'),instructor_adjustments=Sum('payToUser__adjustments'),instructor_fees=Sum('payToUser__fees')).annotate(instructor_net=F('instructor_total') + F('instructor_adjustments') + F('instructor_fees')),
'instructionExpenseOtherTotal': context['instructionExpenseItems'].filter(payToUser__isnull=True).annotate(net=F('total') + F('adjustments') + F('fees')).aggregate(instructor_total=Sum('total'),instructor_adjustments=Sum('adjustments'),instructor_fees=Sum('fees'),instructor_net=Sum('net')),
'venueExpenseVenueTotals': Location.objects.filter(expenseitem__in=context['venueExpenseItems']).annotate(location_total=Sum('expenseitem__total'),location_adjustments=Sum('expenseitem__adjustments'),location_fees=Sum('expenseitem__fees')).annotate(location_net=F('location_total') + F('location_adjustments') + F('location_fees')),
'venueExpenseOtherTotal': context['venueExpenseItems'].filter(payToLocation__isnull=True).annotate(location_net=F('total') + F('adjustments') + F('fees')).aggregate(location_total=Sum('total'),location_adjustments=Sum('adjustments'),location_fees=Sum('fees'),location_net=Sum('net')),
'totalInstructionExpenses': sum([x.category_net or 0 for x in context['expenseCategoryTotals'].filter(id__in=[getConstant('financial__classInstructionExpenseCat').id,getConstant('financial__assistantClassInstructionExpenseCat').id])]),
'totalVenueExpenses': sum([x.category_net or 0 for x in context['expenseCategoryTotals'].filter(id=getConstant('financial__venueRentalExpenseCat').id)]),
'totalOtherExpenses': sum([x.category_net or 0 for x in context['expenseCategoryTotals'].exclude(id__in=[getConstant('financial__classInstructionExpenseCat').id,getConstant('financial__assistantClassInstructionExpenseCat').id,getConstant('financial__venueRentalExpenseCat').id])]),
'totalExpenses': sum([x.category_net or 0 for x in context['expenseCategoryTotals']]),
})
context.update({
'registrationRevenueItems': revenueItems.filter(category=getConstant('financial__registrationsRevenueCat')).order_by('-event__startTime'),
'otherRevenueItems': revenueItems.exclude(category=getConstant('financial__registrationsRevenueCat')).order_by('category'),
'revenueCategoryTotals': RevenueCategory.objects.filter(revenueitem__in=revenueItems).annotate(category_total=Sum('revenueitem__total'),category_adjustments=Sum('revenueitem__adjustments'),category_fees=Sum('revenueitem__fees')).annotate(category_net=F('category_total') + F('category_adjustments') - F('category_fees')),
})
context.update({
'registrationRevenueEventTotals': Event.objects.filter(eventregistration__invoiceitem__revenueitem__in=context['registrationRevenueItems']).annotate(event_total=Sum('eventregistration__invoiceitem__revenueitem__total'),event_adjustments=Sum('eventregistration__invoiceitem__revenueitem__adjustments'),event_fees=Sum('eventregistration__invoiceitem__revenueitem__fees')).annotate(event_net=F('event_total') + F('event_adjustments') - F('event_fees')),
'registrationRevenueOtherTotal': context['registrationRevenueItems'].filter(invoiceItem__finalEventRegistration__isnull=True).annotate(event_net=F('total') + F('adjustments') - F('fees')).aggregate(event_total=Sum('total'),event_adjustments=Sum('adjustments'),event_fees=Sum('fees'),event_net=Sum('net')),
'totalRegistrationRevenues': sum([x.category_net or 0 for x in context['revenueCategoryTotals'].filter(id=getConstant('financial__registrationsRevenueCat').id)]),
'totalOtherRevenues': sum([x.category_net or 0 for x in context['revenueCategoryTotals'].exclude(id=getConstant('financial__registrationsRevenueCat').id)]),
'totalRevenues': sum([x.category_net or 0 for x in context['revenueCategoryTotals']]),
})
context.update({
'netProfit': context['totalRevenues'] - context['totalExpenses'],
})
return super(self.__class__,self).get_context_data(**context)