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

Returns the result of an SQL query calculating the evaluation.

def form_valid(self, form):
255    def form_valid(self, form):
256        """
257        Returns the result of the evaluation in shape of a `.csv` file.
258        """
259        periods = tuple(n[0] for n in form.cleaned_data['periods'].values_list('id'))
260        context = self.get_context_data()
261        context["projects"] = self.get_queryset(periods)
262        response = HttpResponse(
263            content_type='text/csv',
264            headers={
265                'Content-Disposition': 'attachment; filename="12345.csv"'},
266        )
267        response.write(
268            loader
269                .get_template('vkk/workhours/accounting/department/evaluation.csv')
270                .render(
271                    context=context,
272                    request=self.request
273                )
274        )
275        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