Введение
В процессе написания небольшой CRM возникла задача вывода отчёта о текущих трудозатратах на проект. Чтобы было не скучно, CRM может содержать проекты, которые включают в себя контракты, каждый контракт делится на фазы, которые наполнены задачами, и на задачи списываются трудозатраты. Лучше покажу графически::
Project
-> Contract
-> Phase
-> Step
-> Task
-> Effort
Тут надо пояснить, что Step -- это словарь с задачами, а Task -- это реально заведённые задачи на контракт.
Задача немного упрощалась тем, что требовалось показать отчёт по конкретному проекту, и одновременно усложнялась требованием выводить промежуточную сумму для каждой фазы, контракта и проекта в целом.
Сырой SQL
Учитывая эти требования, я даже не стал пытаться изобразить подобный отчёт на ORM и сразу рванул в сторону RAW SQL.
Для начала я решил, что раз надо выводить информацию о трудозатратах, то с них и начнём:
select c.title as contract, p.title as phase, s.title as step,
sum(e.duration) as duration
from app_effort e
left join app_task t on t.id=e.task_id
left join app_contract c on c.id=t.contract_id
left join app_step s on s.id=t.step_id
left join app_phase p on p.id=s.phase_id
where c.project_id=N
group by c.title, p.title, s.title;
где N - идентификатор проекта.
Результат запроса:
+------------------+------------+------------------------+----------+
| contract | phase | step | duration |
+------------------+------------+------------------------+----------+
| Основной | CD | Архитектура | 4.5 |
| Основной | CD | Вентиляция | 4.5 |
| Основной | Conceptual | Планировка | 0.75 |
+------------------+------------+------------------------+----------+
Как видно, задача группировки решена. Осталось придумать как
подсчитать сумму для фазы CD и всего контракта. Хотелось
избежать дополнительных запросов к базе данных.
К счастью, используемый мной MySQL 5.5 обладает крайне полезным
модификатором
для оператора GROUP BY - WITH ROLLUP. Данный модификатор как
раз решает мою задачу. Применим его:
select c.title as contract, p.title as phase, s.title as step,
sum(e.duration) as duration
from app_effort e
left join app_task t on t.id=e.task_id
left join app_contract c on c.id=t.contract_id
left join app_step s on s.id=t.step_id
left join app_phase p on p.id=s.phase_id
where c.project_id=N
group by c.title, p.title, s.title with rollup;
Как видите, запрос практически не изменился.
Результат запроса:
+------------------+------------+------------------------+----------+
| contract | phase | step | duration |
+------------------+------------+------------------------+----------+
| Основной | CD | Архитектура | 4.5 |
| Основной | CD | Вентиляция | 4.5 |
| Основной | CD | NULL | 9 |
| Основной | Conceptual | Планировка | 0.75 |
| Основной | Conceptual | NULL | 0.75 |
| Основной | NULL | NULL | 9.75 |
| NULL | NULL | NULL | 9.75 |
+------------------+------------+------------------------+----------+
Теперь значение NULL будет показывать, что речь идёт о промежуточной сумме.
Реализация в представлении
Определяем переменную с шаблоном запроса:
SQL_EFFORT_SUM = """
select c.title as contract, p.title as phase, s.title as step,
sum(e.duration) as duration
from app_effort e
left join app_task t on t.id=e.task_id
left join app_contract c on c.id=t.contract_id
left join app_step s on s.id=t.step_id
left join app_phase p on p.id=s.phase_id
where c.project_id=%s
group by c.title, p.title, s.title with rollup;
"""
Обратите внимание, что мы вместо N используем стандартную
подстановку для строки.
Создаём функцию, которая будет возвращать выборку в виде словаря "название поля - значение поля":
def dictfetchall(cursor):
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
Дальше реализуем простое представление:
from django.contrib.auth.decorators import login_required
from django.db import connection
from django.shortcuts import get_object_or_404
from django.utils.translation import ugettext_lazy as _
from django.views.generic.simple import direct_to_template
@login_required
def list_view(request, pk):
project = get_object_or_404(Project, pk=pk)
cursor = connection.cursor()
cursor.execute(SQL_EFFORT_SUM, [pk])
qs_dict = dictfetchall(cursor)
context = {
'title': _('Effort List'),
'project': project,
'object_list': qs_dict,
}
return direct_to_template(
request,
'custom_admin/project/effort_list.html',
context)
Здесь мы просто делаем выборку и заполняем контекст, который передаётся в шаблон. Основная работа будет сделана в шаблоне.
Шаблон отчёта
Отчёт отображается в рамках интерфейса администратора, поэтому я указываю базовый шаблон:
{% extends "admin/base_site.html" %}
Приведу сразу весь шаблон и постепенно опишу его части:
{% block content %}{% trans "total" as total_txt %}
<div id="content" class="flex">
<div id="content-main">
{% regroup object_list by contract as contracts %}
<table class="table table-striped">
{% for contract in contracts %}
{% if contract.grouper %}
<tr>
<th>{% trans "Contract" %}</th>
<th>{% trans "Phase" %}</th>
<th>{% trans "Task" %}</th>
<th>{% trans "Efforts" %}</th>
</tr>
{% endif %}
{% regroup contract.list by phase as phases %}
{% for phase in phases %}
{% for task in phase.list %}
<tr>
<td>
{% if contract.grouper %}
{% if forloop.parentloop.first and forloop.first %}
{{ contract.grouper }}
{% endif %}
{% else %}
<b>{{ total_txt|capfirst }}</b>
{% endif %}
</td>
<td>
{% if phase.grouper %}
{% if forloop.first %}{{ phase.grouper }}{% endif %}
{% else %}
{% if contract.grouper %}
<b>{{ total_txt|capfirst }}</b>
{% endif %}
{% endif %}
</td>
<td>
{% if task.step %}
{{ task.step }}
{% else %}
{% if phase.grouper %}
<b>{{ total_txt|capfirst }}</b>
{% endif %}
{% endif %}
</td>
<td>{{ task.duration|floatformat:2 }}</td>
</tr>
{% endfor %}
{% endfor %}
{% endfor %}
</table>
</div> <!-- content-main -->
</div> <!-- content -->
{% endblock %}
Самой интересной частью данного шаблона является тег regroup, с помощью которого организовывается группировка предварительно отсортированных данных.
Сначала производим группировку по имени контракта. В результате мы получаем список только тех записей, которые относятся к текущему контракту.
Заголовок таблицы показывается только для контракта. Отображаем первую строку очередного контракта, значит надо показать заголовок таблицы.
Далее производим группировку по названию фазы, внутри фазы получаем список задач, для которых у нас есть зарегистрированные трудозатраты.
Обратите внимание на то, что вместо NULL, который мы видели в MySQL, в шаблон приходит объект None, чем мы и пользуемся для проверки уровня группировки. В зависимости от текущего уровня группировки производится отображение слова total/всего.
Кстати, посмотрите где определяется один раз перевод слова "total" и как этот перевод потом применяется в нескольких местах шаблона.
Результат
Вот так это выглядит на сайте:
По-моему, ради простоты реализации в данном случае можно пожертвовать переносимостью между базами данных.