def get_context_data(self, **kwargs):
context = super(IncomeExpenseReport, self).get_context_data(**kwargs)
queryset = Split.objects.past().order_by()
incomes = queryset.income().annotate(
m=TruncMonth('date')).values('m').annotate(
total=models.Sum('amount'))
expenses = queryset.expense().annotate(
m=TruncMonth('date')).values('m').annotate(
total=models.Sum('amount'))
incomes = sorted([{
'm': x['m'],
'total': x['total']
} for x in incomes],
key=lambda i: i['m'])
expenses = sorted([{
'm': x['m'],
'total': x['total']
} for x in expenses],
key=lambda i: i['m'])
result = []
for i, e in zip(incomes, expenses):
result.append({
'month': i['m'],
'income': i['total'],
'expense': e['total'],
'total': i['total'] + e['total']
})
context['result'] = result
return context
def _get_trunc_func(
self, subject: str, period: str
) -> Tuple[Union[TruncHour, TruncDay, TruncWeek, TruncMonth], str]:
if period == "Hour":
fields = """
FLOOR(DATE_PART('day', first_date - %s) * 24 + DATE_PART('hour', first_date - %s)) AS first_date,
FLOOR(DATE_PART('day', event_date - first_date) * 24 + DATE_PART('hour', event_date - first_date)) AS date,
"""
return TruncHour(subject), fields
elif period == "Day":
fields = """
FLOOR(DATE_PART('day', first_date - %s)) AS first_date,
FLOOR(DATE_PART('day', event_date - first_date)) AS date,
"""
return TruncDay(subject), fields
elif period == "Week":
fields = """
FLOOR(DATE_PART('day', first_date - %s) / 7) AS first_date,
FLOOR(DATE_PART('day', event_date - first_date) / 7) AS date,
"""
return TruncWeek(subject), fields
elif period == "Month":
fields = """
FLOOR((DATE_PART('year', first_date) - DATE_PART('year', %s)) * 12 + DATE_PART('month', first_date) - DATE_PART('month', %s)) AS first_date,
FLOOR((DATE_PART('year', event_date) - DATE_PART('year', first_date)) * 12 + DATE_PART('month', event_date) - DATE_PART('month', first_date)) AS date,
"""
return TruncMonth(subject), fields
else:
raise ValidationError(f"Period {period} is unsupported.")
def _leads_period_unit_expr(period):
"""
:param period: - 'hour', 'day', 'week', 'moth', 'year'
:param date_to:
:return:
>>> _leads_period_unit_expr('hour')
TruncHour(F(created))
>>> _leads_period_unit_expr('day')
TruncDay(F(created))
>>> _leads_period_unit_expr('week')
Trunc(F(created))
>>> _leads_period_unit_expr('month')
TruncMonth(F(created))
>>> _leads_period_unit_expr('year')
TruncYear(F(created))
"""
if period == 'hour':
return TruncHour('created')
elif period == 'day':
return TruncDay('created')
elif period == 'week':
return Trunc('created', 'week')
elif period == 'month':
return TruncMonth('created')
else:
return TruncYear('created')
def ventas_por_mes(self, request):
db = request.GET.get('db', 'default')
list(
Compra.objects.using(db).annotate(
month=TruncMonth('fecha')).values('month').annotate(
total=Count('id')).values('month', 'total'))
return Response({})
def _determineTrunc(
subject: str, period: str
) -> Union[TruncHour, TruncDay, TruncWeek, TruncMonth]:
if period == "Hour":
return TruncHour(subject)
elif period == "Day":
return TruncDay(subject)
elif period == "Week":
return TruncWeek(subject)
elif period == "Month":
return TruncMonth(subject)
else:
raise ValueError(f"Period {period} is unsupported.")
def trunc_func(
self, field_name: str
) -> Union[TruncHour, TruncDay, TruncWeek, TruncMonth]:
if self.interval == "hour":
return TruncHour(field_name)
elif self.interval == "day":
return TruncDay(field_name)
elif self.interval == "week":
return TruncWeek(field_name)
elif self.interval == "month":
return TruncMonth(field_name)
else:
raise ValidationError(f"{self.interval} not supported")
def project_analyze(self, user, filters, limits):
if len(filters) < 1:
return None
mapping = {
'worker': 'worker_id',
'project': 'projects__id',
'process': 'process_id',
'subProcess': 'sub_process_id',
'funcRole': 'func_role_id',
'place': 'place_id',
'projectState': 'projectstate__state_id',
'date': '' # Обрабатывается отдельно
}
value_fields = []
user_projects_ids = [
d['id'] for d in Project.objects.filter(gip=user).values('id')
]
filtered = self.filter(deleted=False, checked_by__isnull=False)
filtered = limits.get_range(filtered, 'report_date')
if not user.has_perm(
'projects.global_analysis') and not user_projects_ids:
raise SngyException(
'Отсутствуют проекты, в которых вы являетесь ГИПом!')
if not user.has_perm('projects.global_analysis'):
project_filter = [f for f in filters if f['name'] == 'project']
if project_filter:
project_filter = project_filter[0]
project_filter.filter = [
id for id in project_filter.filter
if id in user_projects_ids
]
if not project_filter.filter:
project_filter.filter = user_projects_ids
else:
filtered = filtered.filter(projects__id__in=user_projects_ids)
for f in filters:
if len(f.filter) > 0:
field_name = mapping[f.name] + '__in'
filtered = filtered.filter(models.Q(**{field_name: f.filter}))
if mapping[f.name]:
value_fields.append(mapping[f.name])
# Для вычисления стоимости часов нам все равно необходимо сгруппировать сотрудников
worker_mapping = mapping['worker']
if worker_mapping not in value_fields:
value_fields.append(worker_mapping)
sub_query = self.filter(id=OuterRef('id')).annotate(
count=models.Count('projects__number')).values('count')
hour_sum = models.Sum(
Extract('time_spent', 'EPOCH') / Subquery(sub_query),
output_field=models.FloatField()) / 3600
values = filtered.values(*value_fields)
result = values.annotate(
hours=hour_sum,
mnth=TruncMonth('report_date')).order_by(*value_fields)
root_class = get_class_hierarchy([(f.name, mapping[f.name])
for f in filters])
root_class.append(result)
root_class.get_totals()
return root_class.storage
