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
.
form_class =
<class 'vkk.workhours.accounting.departments.forms.EvaluationForm'>
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
- 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