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
.
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 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
- 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