vkk.workhours.accounting.departments.views

This submodule contains class based views.

  1"""
  2This submodule contains class based views.
  3"""
  4
  5from django.template import loader
  6from django.db import connection
  7from django.http import HttpResponse
  8from django.views.generic import FormView
  9from vkk.workhours.accounting.mixins import AccountantRequiredMixin
 10from .forms import EvaluationForm
 11
 12
 13class EvaluationView(AccountantRequiredMixin, FormView):
 14    """
 15    A class based `View` providing utilities for generating an evaluation of
 16    the given `Department` over some `Periods`.
 17    """
 18    template_name = 'vkk/workhours/accounting/department/evaluation.html'
 19    form_class = EvaluationForm
 20
 21    def get_queryset(self, periods):
 22        """
 23        Returns the result of an SQL query calculating the evaluation.
 24        """
 25        with connection.cursor() as cursor:
 26            cursor.execute("WITH projects AS ( \
 27                    SELECT \
 28                        vkk_project.id, \
 29                        vkk_project.start, \
 30                        vkk_project.end, \
 31                        vkk_project.invoice_number \
 32                    FROM vkk_project \
 33                    WHERE vkk_project.department_id = %s \
 34                ), \
 35                assignments AS ( \
 36                    SELECT \
 37                        projects.id AS project_id, \
 38                        projects.start AS project_start, \
 39                        projects.end AS project_end, \
 40                        vkk_projectassignment.id AS assignment_id, \
 41                        salary_level_id \
 42                    FROM projects JOIN vkk_projectassignment \
 43                        ON projects.id = vkk_projectassignment.project_id \
 44                ), \
 45                workhours AS ( \
 46                    SELECT \
 47                        project_id, \
 48                        project_start, \
 49                        project_end, \
 50                        assignment_id, \
 51                        salary_level_id, \
 52                        period_id, \
 53                        hours \
 54                    FROM assignments JOIN vkk_workhours \
 55                        ON assignments.assignment_id = vkk_workhours.project_assignment_id \
 56                    UNION \
 57                    SELECT \
 58                        project_id, \
 59                        project_start, \
 60                        project_end, \
 61                        assignment_id, \
 62                        salary_level_id, \
 63                        period_id, \
 64                        ammount AS hours \
 65                    FROM assignments JOIN vkk_workhourscorrection \
 66                        ON assignments.assignment_id = vkk_workhourscorrection.project_assignment_id \
 67                ), \
 68                periods AS ( \
 69                    SELECT \
 70                        vkk_period.id, \
 71                        vkk_period.start, \
 72                        vkk_period.end \
 73                    FROM vkk_period \
 74                    WHERE id IN %s \
 75                ), \
 76                workhours_bounded AS ( \
 77                    SELECT \
 78                        project_id, \
 79                        project_start, \
 80                        project_end, \
 81                        assignment_id, \
 82                        salary_level_id, \
 83                        period_id, \
 84                        periods.start AS period_start, \
 85                        periods.end AS period_end, \
 86                        hours \
 87                    FROM workhours JOIN periods \
 88                    ON workhours.period_id = periods.id \
 89                    WHERE  \
 90                        periods.end > workhours.project_start \
 91                        AND periods.start <= workhours.project_end \
 92                ), \
 93                workhours_summed AS ( \
 94                    SELECT \
 95                        project_id, \
 96                        salary_level_id, \
 97                        period_id, \
 98                        SUM(hours) AS hours \
 99                    FROM workhours_bounded \
100                    GROUP BY  \
101                        project_id, \
102                        salary_level_id, \
103                        period_id \
104                ), \
105                salary_dates AS ( \
106                    SELECT \
107                        vkk_salaryleveldate.id AS salary_date_id, \
108                        p.period_id \
109                    FROM vkk_salaryleveldate JOIN ( \
110                        SELECT \
111                            MAX(vkk_salaryleveldate.date) AS salary_date, \
112                            periods.id AS period_id \
113                        FROM vkk_salaryleveldate, periods \
114                        WHERE vkk_salaryleveldate.date <= periods.start \
115                        GROUP BY periods.id \
116                    ) AS p \
117                    ON vkk_salaryleveldate.date = p.salary_date \
118                ), \
119                department_dates AS ( \
120                    SELECT \
121                        vkk_departmentdate.id AS departmentdate_id, \
122                        p.id AS period_id \
123                    FROM vkk_departmentdate JOIN ( \
124                        SELECT \
125                            MAX(vkk_departmentdate.date) AS department_date, \
126                            periods.id AS id \
127                        FROM vkk_departmentdate, periods \
128                        WHERE vkk_departmentdate.date <= periods.start \
129                        GROUP BY periods.id \
130                    ) AS p \
131                    ON vkk_departmentdate.date = p.department_date \
132                ), \
133                project_funded_staff_date AS ( \
134                    SELECT \
135                        vkk_projectfundedstaffdate.id, \
136                        p.project_id, \
137                        p.period_id \
138                    FROM vkk_projectfundedstaffdate, ( \
139                            SELECT \
140                                MAX(vkk_projectfundedstaffdate.date) AS staff_date, \
141                                project_id, \
142                                periods.id AS period_id \
143                            FROM vkk_projectfundedstaffdate, periods \
144                            WHERE vkk_projectfundedstaffdate.date <= periods.start \
145                            GROUP BY project_id, periods.id \
146                        ) AS p \
147                    WHERE \
148                        vkk_projectfundedstaffdate.date = p.staff_date \
149                        AND vkk_projectfundedstaffdate.project_id = p.project_id \
150                ), \
151                general_costs AS ( \
152                    SELECT \
153                        vkk_generalcosts.costs, \
154                        p.period_id \
155                    FROM vkk_generalcosts JOIN ( \
156                        SELECT \
157                            MAX(vkk_generalcosts.start) AS general_costs_date, \
158                            periods.id AS period_id \
159                        FROM vkk_generalcosts, periods \
160                        WHERE vkk_generalcosts.start <= periods.start \
161                        GROUP BY periods.id \
162                    ) AS p \
163                    ON vkk_generalcosts.start = p.general_costs_date \
164                ), \
165                salary_level_costs AS ( \
166                    SELECT \
167                        vkk_salarylevelcosts.salary_level_id, \
168                        vkk_salarylevelcosts.brutto_per_hour, \
169                        salary_dates.period_id \
170                    FROM vkk_salarylevelcosts JOIN salary_dates \
171                    ON vkk_salarylevelcosts.start_id = salary_dates.salary_date_id \
172                ), \
173                department_costs AS ( \
174                    SELECT \
175                        vkk_departmentcosts.equivalents_per_hour, \
176                        department_dates.period_id \
177                    FROM vkk_departmentcosts JOIN department_dates \
178                    ON vkk_departmentcosts.start_id = department_dates.departmentdate_id \
179                    WHERE department_id = %s \
180                ), \
181                project_funded_staff AS ( \
182                    SELECT \
183                        project_id, \
184                        period_id, \
185                        vkk_projectfundedstaff.salary_level_id, \
186                        vkk_projectfundedstaff.hours \
187                    FROM project_funded_staff_date JOIN vkk_projectfundedstaff \
188                    ON project_funded_staff_date.id = vkk_projectfundedstaff.start_id \
189                ), \
190                workhours_together AS ( \
191                    SELECT \
192                        project_id, \
193                        salary_level_id, \
194                        period_id, \
195                        hours \
196                    FROM workhours_summed \
197                \
198                    UNION \
199                \
200                    SELECT \
201                        project_id, \
202                        period_id, \
203                        salary_level_id, \
204                        hours \
205                    FROM project_funded_staff \
206                ), \
207                salary_costs_by_project AS ( \
208                    SELECT \
209                        workhours_summed.project_id, \
210                        SUM(workhours_summed.hours * salary_level_costs.brutto_per_hour) AS salary_costs \
211                    FROM workhours_summed, salary_level_costs \
212                    WHERE workhours_summed.salary_level_id = salary_level_costs.salary_level_id \
213                    AND workhours_summed.period_id = salary_level_costs.period_id \
214                    GROUP BY workhours_summed.project_id \
215                ), \
216                workhours_together_summed AS ( \
217                    SELECT \
218                        project_id, \
219                        period_id, \
220                        SUM(hours) AS hours \
221                    FROM workhours_together \
222                    GROUP BY project_id, period_id \
223                ), \
224                general_costs_by_project AS ( \
225                    SELECT \
226                        project_id, \
227                        SUM(hours * costs) AS general_costs \
228                    FROM workhours_together_summed JOIN general_costs \
229                    ON workhours_together_summed.period_id = general_costs.period_id \
230                    GROUP BY project_id \
231                ), \
232                department_costs_by_project AS ( \
233                    SELECT \
234                        project_id, \
235                        SUM(hours * equivalents_per_hour) AS department_costs \
236                    FROM workhours_together_summed JOIN department_costs \
237                    ON workhours_together_summed.period_id = department_costs.period_id \
238                    GROUP BY project_id \
239                ) \
240                SELECT \
241                    projects.invoice_number AS projekt, \
242                    salary_costs_by_project.salary_costs AS pgk, \
243                    department_costs_by_project.department_costs AS sgk, \
244                    salary_costs_by_project.salary_costs + department_costs_by_project.department_costs AS sum_pgk_sgk, \
245                    general_costs_by_project.general_costs AS vgk, \
246                    salary_costs_by_project.salary_costs + general_costs_by_project.general_costs + department_costs_by_project.department_costs AS sum_all \
247                FROM  \
248                    projects,  \
249                    salary_costs_by_project, \
250                    general_costs_by_project, \
251                    department_costs_by_project \
252                WHERE  \
253                    projects.id = salary_costs_by_project.project_id \
254                    AND salary_costs_by_project.project_id = general_costs_by_project.project_id \
255                    AND general_costs_by_project.project_id = department_costs_by_project.project_id \
256                ORDER BY projects.invoice_number;", 
257                [self.kwargs['pk'], periods, self.kwargs['pk']],
258            )
259            queryset = cursor.fetchall()
260        return queryset
261    
262
263    def form_valid(self, form):
264        """
265        Returns the result of the evaluation in shape of a `.csv` file.
266        """
267        periods = tuple(n[0] for n in form.cleaned_data['periods'].values_list('id'))
268        context = self.get_context_data()
269        context["projects"] = self.get_queryset(periods)
270        print(context["projects"][0])
271        periods_string = "".join(str(p) for p in periods)
272        department_string = self.kwargs['pk']
273        response = HttpResponse(
274            content_type='text/csv',
275            headers={
276                'Content-Disposition': f'attachment; filename="{department_string}-{periods_string}.csv"'},
277        )
278        response.write(
279            loader
280                .get_template('vkk/workhours/accounting/department/evaluation.csv')
281                .render(
282                    context=context,
283                    request=self.request
284                )
285        )
286        return response
class EvaluationView(vkk.workhours.accounting.mixins.AccountantRequiredMixin, django.views.generic.edit.FormView):
 14class EvaluationView(AccountantRequiredMixin, FormView):
 15    """
 16    A class based `View` providing utilities for generating an evaluation of
 17    the given `Department` over some `Periods`.
 18    """
 19    template_name = 'vkk/workhours/accounting/department/evaluation.html'
 20    form_class = EvaluationForm
 21
 22    def get_queryset(self, periods):
 23        """
 24        Returns the result of an SQL query calculating the evaluation.
 25        """
 26        with connection.cursor() as cursor:
 27            cursor.execute("WITH projects AS ( \
 28                    SELECT \
 29                        vkk_project.id, \
 30                        vkk_project.start, \
 31                        vkk_project.end, \
 32                        vkk_project.invoice_number \
 33                    FROM vkk_project \
 34                    WHERE vkk_project.department_id = %s \
 35                ), \
 36                assignments AS ( \
 37                    SELECT \
 38                        projects.id AS project_id, \
 39                        projects.start AS project_start, \
 40                        projects.end AS project_end, \
 41                        vkk_projectassignment.id AS assignment_id, \
 42                        salary_level_id \
 43                    FROM projects JOIN vkk_projectassignment \
 44                        ON projects.id = vkk_projectassignment.project_id \
 45                ), \
 46                workhours AS ( \
 47                    SELECT \
 48                        project_id, \
 49                        project_start, \
 50                        project_end, \
 51                        assignment_id, \
 52                        salary_level_id, \
 53                        period_id, \
 54                        hours \
 55                    FROM assignments JOIN vkk_workhours \
 56                        ON assignments.assignment_id = vkk_workhours.project_assignment_id \
 57                    UNION \
 58                    SELECT \
 59                        project_id, \
 60                        project_start, \
 61                        project_end, \
 62                        assignment_id, \
 63                        salary_level_id, \
 64                        period_id, \
 65                        ammount AS hours \
 66                    FROM assignments JOIN vkk_workhourscorrection \
 67                        ON assignments.assignment_id = vkk_workhourscorrection.project_assignment_id \
 68                ), \
 69                periods AS ( \
 70                    SELECT \
 71                        vkk_period.id, \
 72                        vkk_period.start, \
 73                        vkk_period.end \
 74                    FROM vkk_period \
 75                    WHERE id IN %s \
 76                ), \
 77                workhours_bounded AS ( \
 78                    SELECT \
 79                        project_id, \
 80                        project_start, \
 81                        project_end, \
 82                        assignment_id, \
 83                        salary_level_id, \
 84                        period_id, \
 85                        periods.start AS period_start, \
 86                        periods.end AS period_end, \
 87                        hours \
 88                    FROM workhours JOIN periods \
 89                    ON workhours.period_id = periods.id \
 90                    WHERE  \
 91                        periods.end > workhours.project_start \
 92                        AND periods.start <= workhours.project_end \
 93                ), \
 94                workhours_summed AS ( \
 95                    SELECT \
 96                        project_id, \
 97                        salary_level_id, \
 98                        period_id, \
 99                        SUM(hours) AS hours \
100                    FROM workhours_bounded \
101                    GROUP BY  \
102                        project_id, \
103                        salary_level_id, \
104                        period_id \
105                ), \
106                salary_dates AS ( \
107                    SELECT \
108                        vkk_salaryleveldate.id AS salary_date_id, \
109                        p.period_id \
110                    FROM vkk_salaryleveldate JOIN ( \
111                        SELECT \
112                            MAX(vkk_salaryleveldate.date) AS salary_date, \
113                            periods.id AS period_id \
114                        FROM vkk_salaryleveldate, periods \
115                        WHERE vkk_salaryleveldate.date <= periods.start \
116                        GROUP BY periods.id \
117                    ) AS p \
118                    ON vkk_salaryleveldate.date = p.salary_date \
119                ), \
120                department_dates AS ( \
121                    SELECT \
122                        vkk_departmentdate.id AS departmentdate_id, \
123                        p.id AS period_id \
124                    FROM vkk_departmentdate JOIN ( \
125                        SELECT \
126                            MAX(vkk_departmentdate.date) AS department_date, \
127                            periods.id AS id \
128                        FROM vkk_departmentdate, periods \
129                        WHERE vkk_departmentdate.date <= periods.start \
130                        GROUP BY periods.id \
131                    ) AS p \
132                    ON vkk_departmentdate.date = p.department_date \
133                ), \
134                project_funded_staff_date AS ( \
135                    SELECT \
136                        vkk_projectfundedstaffdate.id, \
137                        p.project_id, \
138                        p.period_id \
139                    FROM vkk_projectfundedstaffdate, ( \
140                            SELECT \
141                                MAX(vkk_projectfundedstaffdate.date) AS staff_date, \
142                                project_id, \
143                                periods.id AS period_id \
144                            FROM vkk_projectfundedstaffdate, periods \
145                            WHERE vkk_projectfundedstaffdate.date <= periods.start \
146                            GROUP BY project_id, periods.id \
147                        ) AS p \
148                    WHERE \
149                        vkk_projectfundedstaffdate.date = p.staff_date \
150                        AND vkk_projectfundedstaffdate.project_id = p.project_id \
151                ), \
152                general_costs AS ( \
153                    SELECT \
154                        vkk_generalcosts.costs, \
155                        p.period_id \
156                    FROM vkk_generalcosts JOIN ( \
157                        SELECT \
158                            MAX(vkk_generalcosts.start) AS general_costs_date, \
159                            periods.id AS period_id \
160                        FROM vkk_generalcosts, periods \
161                        WHERE vkk_generalcosts.start <= periods.start \
162                        GROUP BY periods.id \
163                    ) AS p \
164                    ON vkk_generalcosts.start = p.general_costs_date \
165                ), \
166                salary_level_costs AS ( \
167                    SELECT \
168                        vkk_salarylevelcosts.salary_level_id, \
169                        vkk_salarylevelcosts.brutto_per_hour, \
170                        salary_dates.period_id \
171                    FROM vkk_salarylevelcosts JOIN salary_dates \
172                    ON vkk_salarylevelcosts.start_id = salary_dates.salary_date_id \
173                ), \
174                department_costs AS ( \
175                    SELECT \
176                        vkk_departmentcosts.equivalents_per_hour, \
177                        department_dates.period_id \
178                    FROM vkk_departmentcosts JOIN department_dates \
179                    ON vkk_departmentcosts.start_id = department_dates.departmentdate_id \
180                    WHERE department_id = %s \
181                ), \
182                project_funded_staff AS ( \
183                    SELECT \
184                        project_id, \
185                        period_id, \
186                        vkk_projectfundedstaff.salary_level_id, \
187                        vkk_projectfundedstaff.hours \
188                    FROM project_funded_staff_date JOIN vkk_projectfundedstaff \
189                    ON project_funded_staff_date.id = vkk_projectfundedstaff.start_id \
190                ), \
191                workhours_together AS ( \
192                    SELECT \
193                        project_id, \
194                        salary_level_id, \
195                        period_id, \
196                        hours \
197                    FROM workhours_summed \
198                \
199                    UNION \
200                \
201                    SELECT \
202                        project_id, \
203                        period_id, \
204                        salary_level_id, \
205                        hours \
206                    FROM project_funded_staff \
207                ), \
208                salary_costs_by_project AS ( \
209                    SELECT \
210                        workhours_summed.project_id, \
211                        SUM(workhours_summed.hours * salary_level_costs.brutto_per_hour) AS salary_costs \
212                    FROM workhours_summed, salary_level_costs \
213                    WHERE workhours_summed.salary_level_id = salary_level_costs.salary_level_id \
214                    AND workhours_summed.period_id = salary_level_costs.period_id \
215                    GROUP BY workhours_summed.project_id \
216                ), \
217                workhours_together_summed AS ( \
218                    SELECT \
219                        project_id, \
220                        period_id, \
221                        SUM(hours) AS hours \
222                    FROM workhours_together \
223                    GROUP BY project_id, period_id \
224                ), \
225                general_costs_by_project AS ( \
226                    SELECT \
227                        project_id, \
228                        SUM(hours * costs) AS general_costs \
229                    FROM workhours_together_summed JOIN general_costs \
230                    ON workhours_together_summed.period_id = general_costs.period_id \
231                    GROUP BY project_id \
232                ), \
233                department_costs_by_project AS ( \
234                    SELECT \
235                        project_id, \
236                        SUM(hours * equivalents_per_hour) AS department_costs \
237                    FROM workhours_together_summed JOIN department_costs \
238                    ON workhours_together_summed.period_id = department_costs.period_id \
239                    GROUP BY project_id \
240                ) \
241                SELECT \
242                    projects.invoice_number AS projekt, \
243                    salary_costs_by_project.salary_costs AS pgk, \
244                    department_costs_by_project.department_costs AS sgk, \
245                    salary_costs_by_project.salary_costs + department_costs_by_project.department_costs AS sum_pgk_sgk, \
246                    general_costs_by_project.general_costs AS vgk, \
247                    salary_costs_by_project.salary_costs + general_costs_by_project.general_costs + department_costs_by_project.department_costs AS sum_all \
248                FROM  \
249                    projects,  \
250                    salary_costs_by_project, \
251                    general_costs_by_project, \
252                    department_costs_by_project \
253                WHERE  \
254                    projects.id = salary_costs_by_project.project_id \
255                    AND salary_costs_by_project.project_id = general_costs_by_project.project_id \
256                    AND general_costs_by_project.project_id = department_costs_by_project.project_id \
257                ORDER BY projects.invoice_number;", 
258                [self.kwargs['pk'], periods, self.kwargs['pk']],
259            )
260            queryset = cursor.fetchall()
261        return queryset
262    
263
264    def form_valid(self, form):
265        """
266        Returns the result of the evaluation in shape of a `.csv` file.
267        """
268        periods = tuple(n[0] for n in form.cleaned_data['periods'].values_list('id'))
269        context = self.get_context_data()
270        context["projects"] = self.get_queryset(periods)
271        print(context["projects"][0])
272        periods_string = "".join(str(p) for p in periods)
273        department_string = self.kwargs['pk']
274        response = HttpResponse(
275            content_type='text/csv',
276            headers={
277                'Content-Disposition': f'attachment; filename="{department_string}-{periods_string}.csv"'},
278        )
279        response.write(
280            loader
281                .get_template('vkk/workhours/accounting/department/evaluation.csv')
282                .render(
283                    context=context,
284                    request=self.request
285                )
286        )
287        return response

A class based View providing utilities for generating an evaluation of the given Department over some Periods.

template_name = 'vkk/workhours/accounting/department/evaluation.html'
def get_queryset(self, periods):
 22    def get_queryset(self, periods):
 23        """
 24        Returns the result of an SQL query calculating the evaluation.
 25        """
 26        with connection.cursor() as cursor:
 27            cursor.execute("WITH projects AS ( \
 28                    SELECT \
 29                        vkk_project.id, \
 30                        vkk_project.start, \
 31                        vkk_project.end, \
 32                        vkk_project.invoice_number \
 33                    FROM vkk_project \
 34                    WHERE vkk_project.department_id = %s \
 35                ), \
 36                assignments AS ( \
 37                    SELECT \
 38                        projects.id AS project_id, \
 39                        projects.start AS project_start, \
 40                        projects.end AS project_end, \
 41                        vkk_projectassignment.id AS assignment_id, \
 42                        salary_level_id \
 43                    FROM projects JOIN vkk_projectassignment \
 44                        ON projects.id = vkk_projectassignment.project_id \
 45                ), \
 46                workhours AS ( \
 47                    SELECT \
 48                        project_id, \
 49                        project_start, \
 50                        project_end, \
 51                        assignment_id, \
 52                        salary_level_id, \
 53                        period_id, \
 54                        hours \
 55                    FROM assignments JOIN vkk_workhours \
 56                        ON assignments.assignment_id = vkk_workhours.project_assignment_id \
 57                    UNION \
 58                    SELECT \
 59                        project_id, \
 60                        project_start, \
 61                        project_end, \
 62                        assignment_id, \
 63                        salary_level_id, \
 64                        period_id, \
 65                        ammount AS hours \
 66                    FROM assignments JOIN vkk_workhourscorrection \
 67                        ON assignments.assignment_id = vkk_workhourscorrection.project_assignment_id \
 68                ), \
 69                periods AS ( \
 70                    SELECT \
 71                        vkk_period.id, \
 72                        vkk_period.start, \
 73                        vkk_period.end \
 74                    FROM vkk_period \
 75                    WHERE id IN %s \
 76                ), \
 77                workhours_bounded AS ( \
 78                    SELECT \
 79                        project_id, \
 80                        project_start, \
 81                        project_end, \
 82                        assignment_id, \
 83                        salary_level_id, \
 84                        period_id, \
 85                        periods.start AS period_start, \
 86                        periods.end AS period_end, \
 87                        hours \
 88                    FROM workhours JOIN periods \
 89                    ON workhours.period_id = periods.id \
 90                    WHERE  \
 91                        periods.end > workhours.project_start \
 92                        AND periods.start <= workhours.project_end \
 93                ), \
 94                workhours_summed AS ( \
 95                    SELECT \
 96                        project_id, \
 97                        salary_level_id, \
 98                        period_id, \
 99                        SUM(hours) AS hours \
100                    FROM workhours_bounded \
101                    GROUP BY  \
102                        project_id, \
103                        salary_level_id, \
104                        period_id \
105                ), \
106                salary_dates AS ( \
107                    SELECT \
108                        vkk_salaryleveldate.id AS salary_date_id, \
109                        p.period_id \
110                    FROM vkk_salaryleveldate JOIN ( \
111                        SELECT \
112                            MAX(vkk_salaryleveldate.date) AS salary_date, \
113                            periods.id AS period_id \
114                        FROM vkk_salaryleveldate, periods \
115                        WHERE vkk_salaryleveldate.date <= periods.start \
116                        GROUP BY periods.id \
117                    ) AS p \
118                    ON vkk_salaryleveldate.date = p.salary_date \
119                ), \
120                department_dates AS ( \
121                    SELECT \
122                        vkk_departmentdate.id AS departmentdate_id, \
123                        p.id AS period_id \
124                    FROM vkk_departmentdate JOIN ( \
125                        SELECT \
126                            MAX(vkk_departmentdate.date) AS department_date, \
127                            periods.id AS id \
128                        FROM vkk_departmentdate, periods \
129                        WHERE vkk_departmentdate.date <= periods.start \
130                        GROUP BY periods.id \
131                    ) AS p \
132                    ON vkk_departmentdate.date = p.department_date \
133                ), \
134                project_funded_staff_date AS ( \
135                    SELECT \
136                        vkk_projectfundedstaffdate.id, \
137                        p.project_id, \
138                        p.period_id \
139                    FROM vkk_projectfundedstaffdate, ( \
140                            SELECT \
141                                MAX(vkk_projectfundedstaffdate.date) AS staff_date, \
142                                project_id, \
143                                periods.id AS period_id \
144                            FROM vkk_projectfundedstaffdate, periods \
145                            WHERE vkk_projectfundedstaffdate.date <= periods.start \
146                            GROUP BY project_id, periods.id \
147                        ) AS p \
148                    WHERE \
149                        vkk_projectfundedstaffdate.date = p.staff_date \
150                        AND vkk_projectfundedstaffdate.project_id = p.project_id \
151                ), \
152                general_costs AS ( \
153                    SELECT \
154                        vkk_generalcosts.costs, \
155                        p.period_id \
156                    FROM vkk_generalcosts JOIN ( \
157                        SELECT \
158                            MAX(vkk_generalcosts.start) AS general_costs_date, \
159                            periods.id AS period_id \
160                        FROM vkk_generalcosts, periods \
161                        WHERE vkk_generalcosts.start <= periods.start \
162                        GROUP BY periods.id \
163                    ) AS p \
164                    ON vkk_generalcosts.start = p.general_costs_date \
165                ), \
166                salary_level_costs AS ( \
167                    SELECT \
168                        vkk_salarylevelcosts.salary_level_id, \
169                        vkk_salarylevelcosts.brutto_per_hour, \
170                        salary_dates.period_id \
171                    FROM vkk_salarylevelcosts JOIN salary_dates \
172                    ON vkk_salarylevelcosts.start_id = salary_dates.salary_date_id \
173                ), \
174                department_costs AS ( \
175                    SELECT \
176                        vkk_departmentcosts.equivalents_per_hour, \
177                        department_dates.period_id \
178                    FROM vkk_departmentcosts JOIN department_dates \
179                    ON vkk_departmentcosts.start_id = department_dates.departmentdate_id \
180                    WHERE department_id = %s \
181                ), \
182                project_funded_staff AS ( \
183                    SELECT \
184                        project_id, \
185                        period_id, \
186                        vkk_projectfundedstaff.salary_level_id, \
187                        vkk_projectfundedstaff.hours \
188                    FROM project_funded_staff_date JOIN vkk_projectfundedstaff \
189                    ON project_funded_staff_date.id = vkk_projectfundedstaff.start_id \
190                ), \
191                workhours_together AS ( \
192                    SELECT \
193                        project_id, \
194                        salary_level_id, \
195                        period_id, \
196                        hours \
197                    FROM workhours_summed \
198                \
199                    UNION \
200                \
201                    SELECT \
202                        project_id, \
203                        period_id, \
204                        salary_level_id, \
205                        hours \
206                    FROM project_funded_staff \
207                ), \
208                salary_costs_by_project AS ( \
209                    SELECT \
210                        workhours_summed.project_id, \
211                        SUM(workhours_summed.hours * salary_level_costs.brutto_per_hour) AS salary_costs \
212                    FROM workhours_summed, salary_level_costs \
213                    WHERE workhours_summed.salary_level_id = salary_level_costs.salary_level_id \
214                    AND workhours_summed.period_id = salary_level_costs.period_id \
215                    GROUP BY workhours_summed.project_id \
216                ), \
217                workhours_together_summed AS ( \
218                    SELECT \
219                        project_id, \
220                        period_id, \
221                        SUM(hours) AS hours \
222                    FROM workhours_together \
223                    GROUP BY project_id, period_id \
224                ), \
225                general_costs_by_project AS ( \
226                    SELECT \
227                        project_id, \
228                        SUM(hours * costs) AS general_costs \
229                    FROM workhours_together_summed JOIN general_costs \
230                    ON workhours_together_summed.period_id = general_costs.period_id \
231                    GROUP BY project_id \
232                ), \
233                department_costs_by_project AS ( \
234                    SELECT \
235                        project_id, \
236                        SUM(hours * equivalents_per_hour) AS department_costs \
237                    FROM workhours_together_summed JOIN department_costs \
238                    ON workhours_together_summed.period_id = department_costs.period_id \
239                    GROUP BY project_id \
240                ) \
241                SELECT \
242                    projects.invoice_number AS projekt, \
243                    salary_costs_by_project.salary_costs AS pgk, \
244                    department_costs_by_project.department_costs AS sgk, \
245                    salary_costs_by_project.salary_costs + department_costs_by_project.department_costs AS sum_pgk_sgk, \
246                    general_costs_by_project.general_costs AS vgk, \
247                    salary_costs_by_project.salary_costs + general_costs_by_project.general_costs + department_costs_by_project.department_costs AS sum_all \
248                FROM  \
249                    projects,  \
250                    salary_costs_by_project, \
251                    general_costs_by_project, \
252                    department_costs_by_project \
253                WHERE  \
254                    projects.id = salary_costs_by_project.project_id \
255                    AND salary_costs_by_project.project_id = general_costs_by_project.project_id \
256                    AND general_costs_by_project.project_id = department_costs_by_project.project_id \
257                ORDER BY projects.invoice_number;", 
258                [self.kwargs['pk'], periods, self.kwargs['pk']],
259            )
260            queryset = cursor.fetchall()
261        return queryset

Returns the result of an SQL query calculating the evaluation.

def form_valid(self, form):
264    def form_valid(self, form):
265        """
266        Returns the result of the evaluation in shape of a `.csv` file.
267        """
268        periods = tuple(n[0] for n in form.cleaned_data['periods'].values_list('id'))
269        context = self.get_context_data()
270        context["projects"] = self.get_queryset(periods)
271        print(context["projects"][0])
272        periods_string = "".join(str(p) for p in periods)
273        department_string = self.kwargs['pk']
274        response = HttpResponse(
275            content_type='text/csv',
276            headers={
277                'Content-Disposition': f'attachment; filename="{department_string}-{periods_string}.csv"'},
278        )
279        response.write(
280            loader
281                .get_template('vkk/workhours/accounting/department/evaluation.csv')
282                .render(
283                    context=context,
284                    request=self.request
285                )
286        )
287        return response

Returns the result of the evaluation in shape of a .csv file.

Inherited Members
django.views.generic.base.View
View
http_method_names
view_is_async
as_view
setup
http_method_not_allowed
options
vkk.workhours.accounting.mixins.AccountantRequiredMixin
dispatch
django.contrib.auth.mixins.AccessMixin
login_url
permission_denied_message
raise_exception
redirect_field_name
get_login_url
get_permission_denied_message
get_redirect_field_name
handle_no_permission
django.views.generic.base.TemplateResponseMixin
template_engine
response_class
content_type
render_to_response
get_template_names
django.views.generic.edit.FormMixin
initial
success_url
prefix
get_initial
get_prefix
get_form_class
get_form
get_form_kwargs
get_success_url
form_invalid
get_context_data
django.views.generic.base.ContextMixin
extra_context
django.views.generic.edit.ProcessFormView
get
post
put