### ===================================================================================================================
### FUNCTION: Create Uren Monitor report Excel
### ===================================================================================================================
# Copyright ©VIIA 2024
### ===================================================================================================================
### 1. Import modules
### ===================================================================================================================
# General imports
from __future__ import annotations
from typing import TYPE_CHECKING, Optional, List, Dict
from datetime import datetime
from copy import deepcopy
from pathlib import Path
import pytz
# References for functions and classes in the rhdhv_fem package
from rhdhv_fem.fem_tools import fem_create_folder
# References for functions and classes in the viiaPackage
if TYPE_CHECKING:
from viiapackage.viiaStatus import ViiaProject
from viiapackage.database import myviia_get_object, myviia_get_general_data, myviia_get_all_opdrachten, \
myviia_get_all_object_opdrachten, myviia_get_fin_uren, myviia_get_opdracht_kengetallen, \
myviia_get_object_voortgang, myviia_get_object_opdracht_object_deels, myviia_get_fin_voortgang, \
myviia_get_all_deliverable_status, myviia_get_object_deel_projectleider, \
myviia_get_object_deel_hoofdconstructeur
# Import module openpyxl
# This module is used for creating Excel-sheet for Uren Monitor Report deliverable
from openpyxl import load_workbook
from openpyxl.utils.cell import get_column_letter
from openpyxl.formatting import Rule
from openpyxl.styles import Font
from openpyxl.styles.differential import DifferentialStyle
### ===================================================================================================================
### 2. Function to create Uren Monitor Report deliverable
### ===================================================================================================================
[docs]def viia_create_uren_monitor_report(
project: ViiaProject, werkpakket_lst: List[str], template_locations: Dict[str, str],
output_folder: Optional[Path] = None) -> List[Path]:
"""
This function creates an hours monitor report for a list of 'werkpakket'. Data from all objects related to each
specific werkpakket are collected in a separate worksheet. Subsequently, a table, collected from the
(specified, optional) template location, is filled with the object data and saved in the (specified, optional)
output folder.
Input:
- project (obj): VIIA project object containing collections of fem objects and project variables.
- werkpakket_lst (list of str): List of names of the werkpakket to create the Excel for.
- project (obj): VIIA project object containing collections of fem objects and project variables.
- template_locations (dict): Dictionary which stores the path of all requested template files.
- output_folder (Path): Optional input for location where to create the report. Default value is None,
indicating the default location is used. In normal production objects do not change this!
Output:
- Filled template with combined information from project and MYVIIA. Values that couldn't be retrieved are
left blank.
- Returns list of created files.
"""
# Create or check the location where the report is generated
if output_folder is None:
# Create sub-folder
fem_create_folder('Uren Monitor Report', ref_folder=project.workfolder_location)
report_location = project.workfolder_location / 'Uren Monitor Report'
else:
if isinstance(output_folder, str):
output_folder = Path(output_folder)
report_location = output_folder
# Check if output-folder is created
if not report_location.exists():
raise FileNotFoundError("ERROR: The specified output-folder does not exist, please check input.")
# Collect the data from myviia
all_data = collect_data_from_myviia(project=project, werkpakket_lst=werkpakket_lst)
# Map excel column name and data key
column_mapping = {
'object_name': 'Object',
'objectdeel': 'Objectdeel',
'boekingscode': 'Boekingscode',
'object_scope': 'Object scope (binnen deze opdracht)',
'geo_nodig': 'Geo nodig',
'aansturing_begrote_uren': 'Aansturing - begrote uren',
'aansturing_geboekte_uren': 'Aansturing - geboekte uren',
'aansturing_resultaat': 'Aansturing - resultaat',
'opname_begrote_uren': 'Opname - begrote uren',
'opname_voortgang': 'Opname - voortgang',
'opname_geboekte_uren': 'Opname - geboekte uren',
'opname_prognose_uren': 'Opname - prognose uren',
'opname_resultaat': 'Opname - resultaat',
'tva_begrote_uren': 'TVA - begrote uren',
'tva_voortgang': 'TVA - voortgang',
'tva_storypoints_left': 'TVA - Storypoints left',
'tva_geboekte_uren': 'TVA - geboekte uren',
'tva_prognose_uren': 'TVA - prognose uren',
'tva_resultaat': 'TVA - resultaat',
'kosten_begrote_uren': 'Kosten - begrote uren',
'kosten_voortgang': 'Kosten - voortgang',
'kosten_geboekte_uren': 'Kosten - geboekte uren',
'kosten_prognose_uren': 'Kosten - prognose uren',
'kosten_resultaat': 'Kosten - resultaat',
'evva_begrote_uren': 'EVVA - begrote uren',
'evva_voortgang': 'EVVA - voortgang',
'evva_geboekte_uren': 'EVVA - geboekte uren',
'evva_prognose_uren': 'EVVA - prognose uren',
'evva_resultaat': 'EVVA - resultaat',
'project_totaal': 'Project resultaat totaal',
'som_meerwerk': 'Som meerwerk - euro',
'projectleider': 'Projectleider',
'rekenmethodiek': 'Rekenmethodiek',
'hoofdconstructeur': 'Hoofdconstructeur'}
# Create report(s)
generated_reports = []
for report_name, template_location in template_locations.items():
try:
# Generate the report
cet_time = datetime.now(pytz.timezone('CET'))
time_reference = cet_time.strftime('%Y%m%d%H%M%S')
output_document_name = f"VIIA-Overzicht_Voortgang_{time_reference}.xlsx"
target_file = report_location / output_document_name
# Load excel template
wb = load_workbook(project.viia_settings.project_specific_package_location / template_location)
temp_ws = wb.active
temp_ws.title = werkpakket_lst[0].split('_')[0]
temp_table = temp_ws.tables['Finance']
# Set worksheet (and table) for each werkpakket
for i, werkpakket in enumerate(werkpakket_lst):
if i == 0:
all_data[werkpakket]['worksheet'] = temp_ws
all_data[werkpakket]['table'] = temp_table
else:
target_ws = wb.copy_worksheet(temp_ws)
target_ws.title = werkpakket_lst[i].split('_')[0]
target_ws_table = deepcopy(temp_table)
target_ws_table.name = f'Finance{i}'
target_ws_table.displayName = f'Finance{i}'
target_ws.add_table(target_ws_table)
all_data[werkpakket]['worksheet'] = target_ws
all_data[werkpakket]['table'] = target_ws_table
# Write data for each werkpakket to the worksheet
for werkpakket, data in all_data.items():
ws = data['worksheet']
table = data['table']
myviia_data = data['myviia_data']
# Fill in header
ws['C2'] = werkpakket
ws['C3'] = str(datetime.now().isocalendar()[0]) + ' week ' + str(datetime.now().isocalendar()[1])
ws['C4'] = datetime.now()
# Set up table size
top_left_cell = 'A5'
bottom_right_cell = 'AH' + str(5 + len(myviia_data) + 1)
table.ref = top_left_cell + ':' + bottom_right_cell
ws.move_range('A7:A7', rows=len(myviia_data) - 1)
# Loop over myviia_data entries and fill in template
row_pointer = 5
for myviia_data_entry in myviia_data:
row_pointer += 1
for k, v in myviia_data_entry.items():
if column_mapping[k] in table.column_names:
ws[get_column_letter(table.column_names.index(column_mapping[k]) + 1) +
str(row_pointer)] = v
# Apply conditional formatting to the correct columns (columns containing 'resultaat')
red_font = DifferentialStyle(font=Font(color='CC0000'))
orange_font = DifferentialStyle(font=Font(color='FF9900'))
green_font = DifferentialStyle(font=Font(color='006600'))
color_rule_red = Rule(type='cellIs', operator='lessThan', formula=['0'], dxf=red_font)
color_rule_orange_1 = Rule(type='cellIs', operator='between', formula=['0', '10'], dxf=orange_font)
color_rule_orange_2 = Rule(type='cellIs', operator='equal', formula=['0', '10'], dxf=orange_font)
color_rule_green = Rule(type='cellIs', operator='greaterThan', formula=['10'], dxf=green_font)
for column_name in table.column_names:
if 'resultaat' in column_name:
rule_column = get_column_letter(table.column_names.index(column_name) + 1)
rule_range = rule_column + '6:' + rule_column + str(5 + len(myviia_data) + 1)
ws.conditional_formatting.add(rule_range, color_rule_red)
ws.conditional_formatting.add(rule_range, color_rule_orange_1)
ws.conditional_formatting.add(rule_range, color_rule_orange_2)
ws.conditional_formatting.add(rule_range, color_rule_green)
# Save the target Excel file
wb.save(target_file)
project.write_log(
f"Successfully created {report_name.upper()} deliverable {output_document_name}. Generated report can "
f"be found in {target_file.as_posix()}.")
generated_reports.append(target_file)
except Exception as e:
project.write_log(f"ERROR: Creation of {report_name.upper()} deliverable failed due to exception: {e}")
# Return list of all created reports
return generated_reports
### ===================================================================================================================
### 3. Function to collect data
### ===================================================================================================================
[docs]def collect_data_from_myviia(project: ViiaProject, werkpakket_lst: [str]) -> dict:
""" Function to collect relevant data from MYVIIA for uren monitor report."""
# Initialise the output
all_data = {}
# Geo mapping
geo_mapping = {
None: 'Onbekend',
True: 'Benodigd',
False: 'Niet benodigd'}
# Get general data
general_data = myviia_get_general_data(token=project.token)
if not general_data:
raise ValueError("ERROR: Required data could not be retrieved from MYVIIA for uren monitor tool.")
# Get all kengetal data
opname_kengetal, beoordeling_kengetal = _calculate_kengetal(project=project, general_data=general_data)
# Get all fin_uren_monitor data
fin_uren_lst = myviia_get_fin_uren(token=project.token)
# Get all fin_voortgang data
fin_voortgang_lst = myviia_get_fin_voortgang(token=project.token)
# Get all deliverable_status data
deliverable_status_lst = myviia_get_all_deliverable_status(token=project.token)
# Get all opdrachten
opdrachten = myviia_get_all_opdrachten(token=project.token)
# Get all object_opdrachten
object_opdrachten = myviia_get_all_object_opdrachten(token=project.token)
# Get all object_opdracht_object_deels
object_opdracht_object_deels = myviia_get_object_opdracht_object_deels(token=project.token)
# Get project leaders for all object parts
object_deel_projectleider = myviia_get_object_deel_projectleider(token=project.token)
# Get lead engineers for all object parts
object_deel_hoofdconstructeur = myviia_get_object_deel_hoofdconstructeur(token=project.token)
# Get myviia data for each werkpakket
for werkpakket in werkpakket_lst:
all_data[werkpakket] = {}
all_data[werkpakket]['myviia_data'] = _collect_data_from_myviia_per_werkpakket(
project=project,
werkpakket=werkpakket,
geo_mapping=geo_mapping,
general_data=general_data,
opname_kengetal=opname_kengetal,
beoordeling_kengetal=beoordeling_kengetal,
fin_uren_lst=fin_uren_lst,
fin_voortgang_lst=fin_voortgang_lst,
deliverable_status_lst=deliverable_status_lst,
opdrachten=opdrachten,
object_opdrachten=object_opdrachten,
object_opdracht_object_deels=object_opdracht_object_deels,
object_deel_projectleider=object_deel_projectleider,
object_deel_hoofdconstructeur=object_deel_hoofdconstructeur)
return all_data
def _collect_data_from_myviia_per_werkpakket(
project: ViiaProject, werkpakket: str, geo_mapping: dict, general_data: dict, opname_kengetal: dict,
beoordeling_kengetal: dict, fin_uren_lst: list, fin_voortgang_lst: list, deliverable_status_lst: list,
opdrachten: list, object_opdrachten: list, object_opdracht_object_deels: list,
object_deel_projectleider: list, object_deel_hoofdconstructeur: list) -> List[dict]:
# Initialise output data
werkpakket_data = []
# Get opdracht for the specific werkpakket
opdracht = [opdracht for opdracht in opdrachten if opdracht['werkpakket'] == werkpakket]
if not opdracht:
raise ValueError(f"ERROR: Cannot find opdracht with the given werkpakket {werkpakket}.")
# Get all object_opdrachten in the opdracht
selected_object_opdrachten = [
object_opdracht for object_opdracht in object_opdrachten
if object_opdracht['opdracht_id'] == opdracht[0]['id']]
# Collect data for selected objects
for object_opdracht in selected_object_opdrachten:
# Get scope for the object_opdracht
object_opdracht_scope = None
for scope in general_data['object_scope']:
if scope['id'] == object_opdracht['object_scope_id']:
object_opdracht_scope = scope['scope_opdracht']
break
# Get fin_uren for the object_opdracht
object_fin_uren_lst = [
fin_uren_record for fin_uren_record in fin_uren_lst
if fin_uren_record['boekingscode'] == object_opdracht['boekingscode']]
# Get viia_object from myviia
myviia_object = myviia_get_object(object_id=object_opdracht['viia_object_id'], token=project.token)
# Get object_opdracht_scopewijziging
som_meerwerk = None
object_opdracht_scopewijzigingen = myviia_object['object_opdracht_scopewijzigingen']
if object_opdracht_scopewijzigingen:
try:
som_meerwerk = sum(
[float(scopewijziging['scopewijziging']['financieel_impact'])
for scopewijziging in object_opdracht_scopewijzigingen])
except (ValueError, TypeError) as e:
som_meerwerk = 'Zie MYVIIA'
# Get all object_deel_id in this object_opdracht
object_opdracht_object_deel_ids = [
object_opdracht_object_deel['object_deel_id'] for object_opdracht_object_deel in
object_opdracht_object_deels if object_opdracht_object_deel['object_opdracht_id'] == object_opdracht['id']]
# Get voortgang for all object parts
voortgang_records = myviia_get_object_voortgang(
object_id=object_opdracht['viia_object_id'], token=project.token)
# Get object parts
object_parts = myviia_object['objectdelen']
object_data = []
for object_part in object_parts:
if object_part['id'] in object_opdracht_object_deel_ids:
# Collect rekenmethodiek
rekenmethodiek = '-'
if object_part['rekenmethodiek_id']:
rekenmethodiek = object_part['rekenmethodiek_id']['subtype']
# Collect projectleader name
projectleider = [
resource['projectleider'] for resource in object_deel_projectleider
if resource['object_deel_id'] == object_part['id']]
if projectleider:
projectleider_naam = projectleider[0]
else:
projectleider_naam = 'Niet ingepland'
# Collect name lead engineer
hoofdconstructeur = [
resource['hoofdconstructeur'] for resource in object_deel_hoofdconstructeur
if resource['object_deel_id'] == object_part['id']]
if hoofdconstructeur:
hoofdconstructeur_naam = hoofdconstructeur[0]
else:
hoofdconstructeur_naam = '-'
if rekenmethodiek != '-':
hoofdconstructeur_naam = 'Niet ingepland'
# Collect data for the object part
new_row = dict(
object_name=myviia_object['objectnummer_viia'],
objectdeel=object_part['naam'],
boekingscode=object_opdracht['boekingscode'],
object_scope=object_opdracht_scope,
geo_nodig=geo_mapping[object_part['geotechnische_analyse']],
rekenmethodiek=rekenmethodiek,
projectleider=projectleider_naam,
hoofdconstructeur=hoofdconstructeur_naam)
if som_meerwerk and object_part['naam'] == 'Gehele object':
new_row.update(som_meerwerk=som_meerwerk)
# Add object geboekte uren
if 'gehele object' in object_part['naam'].lower():
new_row.update(
aansturing_geboekte_uren=0,
opname_geboekte_uren=0,
tva_geboekte_uren=0,
kosten_geboekte_uren=0,
evva_geboekte_uren=0)
if object_fin_uren_lst:
object_fin_uren = object_fin_uren_lst[0]
new_row.update(
aansturing_geboekte_uren=object_fin_uren['aansturing'],
opname_geboekte_uren=object_fin_uren['opname'],
tva_geboekte_uren=object_fin_uren['bsc_tva'],
kosten_geboekte_uren=object_fin_uren['kostenraming'],
evva_geboekte_uren=object_fin_uren['eindredactie'])
# Add object part begrote uren (based on the selected opdracht)
new_row.update(
aansturing_begrote_uren=0,
tva_begrote_uren=0,
kosten_begrote_uren=0,
evva_begrote_uren=0)
size = myviia_object['object_grootte_id']['id']
specificatie = opdracht[0]['specificatie']
uren_factor_engineering = 1.4
if specificatie == 1:
uren_factor_engineering = 1.0
if 'opname' in object_opdracht_scope.lower():
# Get budget hours (aansturing) from kengetal
if object_part['opnamemethodiek_id']:
opnamemethodiek_id = object_part['opnamemethodiek_id']['id']
new_row.update(
aansturing_begrote_uren=new_row['aansturing_begrote_uren'] +
opname_kengetal[specificatie][opnamemethodiek_id][size]['Aansturing'])
else:
# Give warning (except when multiple object parts and the current one is not 'gehele object')
if len(object_parts) > 1 and object_part['naam'].lower() != 'gehele object':
pass
else:
project.write_log(
f"WARNING: Opname is in scope, for {new_row['object_name']}, {new_row['objectdeel']}, "
f"but no opname-methodiek information available. Cannot retrieve begrote uren "
f"(for aansturing).")
if 'beoordeling' in object_opdracht_scope.lower():
# Get budget hours (aansturing, tva, kosten, evva) from kengetal
if object_part['rekenmethodiek_id']:
rekenmethodiek_id = object_part['rekenmethodiek_id']['id']
if 'bestaand' in myviia_object['object_scope_id']['scope_opdracht'].lower():
kosten_begroot = 0
else:
kosten_begroot = beoordeling_kengetal[specificatie][rekenmethodiek_id][size]['Kostenraming']
new_row.update(
aansturing_begrote_uren=new_row['aansturing_begrote_uren'] +
beoordeling_kengetal[specificatie][rekenmethodiek_id][size]['Aansturing'],
tva_begrote_uren=beoordeling_kengetal[specificatie][rekenmethodiek_id][size]['BSC/TVA'],
kosten_begrote_uren=kosten_begroot,
evva_begrote_uren=beoordeling_kengetal[specificatie][rekenmethodiek_id][size]
['Eindredactie'])
else:
# Give warning (except when multiple object parts and the current one is 'gehele object')
if not (len(object_parts) > 1 and object_part['naam'].lower() == 'gehele object'):
project.write_log(
f"WARNING: Beoordeling is in scope, for {new_row['object_name']}, "
f"{new_row['objectdeel']}, but no rekenmethodiek information available. Cannot "
f"retrieve begrote uren (for aansturing, TVA, kosten, EVVA).")
# Collect object part voortgang
new_row.update(
tva_voortgang=0,
kosten_voortgang=0,
evva_voortgang=0,
tva_storypoints_left=0)
object_deel_voortgang = [
object_deel_voortgang for object_deel_voortgang in voortgang_records
if object_deel_voortgang['id'] == object_part['id']]
voortgang_record = None
if object_deel_voortgang:
voortgang_record = object_deel_voortgang[0]
if 'opname' in object_opdracht_scope.lower() and object_part['naam'] == 'Gehele object':
new_row.update(
opname_begrote_uren=0,
opname_voortgang=0)
# Get voortgang and budget hours for opname
if voortgang_record:
for voortgang in voortgang_record['voortgang']:
if voortgang['deliverable'] == 'Opnamerapport':
if voortgang['voortgang']:
new_row.update(
opname_voortgang=voortgang['voortgang'] / 100)
# Get budget hours (opname) from fin_voortgang for the deliverable
# Note assume that only find one matching deliverable_status and fin_voortgang
deliverable_status = [
deliverable_status for deliverable_status in deliverable_status_lst
if deliverable_status['deliverable_id']['deliverable'] == 'Opnamerapport'
and deliverable_status['object_deel_id']['id'] == object_part['id']]
fin_voortgang = [
fin_voortgang for fin_voortgang in fin_voortgang_lst
if fin_voortgang['deliverable_status_id'] == deliverable_status[0]['id']]
new_row.update(
opname_begrote_uren=round(fin_voortgang[0]['budget'] / 114, 1))
new_row.update(
opname_prognose_uren=new_row['opname_begrote_uren'] * (1 - new_row['opname_voortgang']))
if 'beoordeling' in object_opdracht_scope.lower():
# Get voortgang for tva, kosten and evva
if voortgang_record:
for voortgang in voortgang_record['voortgang']:
if voortgang['deliverable'] == 'TVA':
if voortgang['voortgang']:
new_row.update(tva_voortgang=voortgang['voortgang'] / 100)
if voortgang['storypoints_left']:
new_row.update(tva_storypoints_left=voortgang['storypoints_left'])
elif voortgang['deliverable'] == 'Kostenraming':
if voortgang['voortgang']:
new_row.update(kosten_voortgang=voortgang['voortgang'] / 100)
elif voortgang['deliverable'] == 'EVVA/BVVA':
if voortgang['voortgang']:
new_row.update(evva_voortgang=voortgang['voortgang'] / 100)
# Calculate object part prognose uren
new_row.update(
tva_prognose_uren=new_row['tva_storypoints_left'] / uren_factor_engineering * 8,
kosten_prognose_uren=new_row['kosten_begrote_uren'] * (1 - new_row['kosten_voortgang']),
evva_prognose_uren=new_row['evva_begrote_uren'] * (1 - new_row['evva_voortgang']))
# Add object part data row to object data list
object_data.append(new_row)
# Calculate results at object level
aansturing_resultaat = 0
opname_resultaat = 0
tva_resultaat = 0
kosten_resultaat = 0
evva_resultaat = 0
for data in object_data:
# Booked hours only exist in the data dictionary for 'gehele object'
if data['objectdeel'].lower() == 'gehele object':
aansturing_resultaat += \
data['aansturing_begrote_uren'] - data['aansturing_geboekte_uren']
if 'opname_begrote_uren' in data:
opname_resultaat += data['opname_begrote_uren']
opname_resultaat -= data['opname_geboekte_uren']
if 'opname_prognose_uren' in data:
opname_resultaat -= data['opname_prognose_uren']
if 'beoordeling' in object_opdracht_scope.lower():
tva_resultaat += \
data['tva_begrote_uren'] - data['tva_geboekte_uren'] - data['tva_prognose_uren']
kosten_resultaat += \
data['kosten_begrote_uren'] - data['kosten_geboekte_uren'] - data['kosten_prognose_uren']
evva_resultaat += \
data['evva_begrote_uren'] - data['evva_geboekte_uren'] - data['evva_prognose_uren']
else:
aansturing_resultaat += data['aansturing_begrote_uren']
if 'beoordeling' in object_opdracht_scope.lower():
tva_resultaat += data['tva_begrote_uren'] - data['tva_prognose_uren']
kosten_resultaat += data['kosten_begrote_uren'] - data['kosten_prognose_uren']
evva_resultaat += data['evva_begrote_uren'] - data['evva_prognose_uren']
for data in object_data:
if data['objectdeel'].lower() == 'gehele object':
data.update(
aansturing_resultaat=aansturing_resultaat,
opname_resultaat=opname_resultaat,
tva_resultaat=tva_resultaat,
kosten_resultaat=kosten_resultaat,
evva_resultaat=evva_resultaat,
project_totaal=aansturing_resultaat + opname_resultaat + tva_resultaat + kosten_resultaat +
evva_resultaat)
# Overwrite relevant cells when not in scope
for data in object_data:
if 'opname' not in object_opdracht_scope.lower():
for key in data.keys():
if 'opname' in key.lower():
if data[key] != 0:
if 'result' not in key:
project.write_log(
f"WARNING: Opname is not in scope for {data['object_name']}, {data['objectdeel']}, "
f"but relevant data '{key}' received from MYVIIA.")
else:
data[key] = '-'
if 'beoordeling' not in object_opdracht_scope.lower():
for key in data.keys():
if any(name in key.lower() for name in ['tva', 'kosten', 'evva']):
if data[key] != 0:
if 'result' not in key:
project.write_log(
f"WARNING: Beoordeling is not in scope for {data['object_name']}, "
f"{data['objectdeel']}, but relevant data '{key}' received from MYVIIA.")
else:
data[key] = '-'
if data['objectdeel'] == 'Gehele object' and len(myviia_object['objectdelen']) > 1:
del data['kosten_begrote_uren']
del data['evva_begrote_uren']
del data['kosten_voortgang']
del data['evva_voortgang']
del data['kosten_prognose_uren']
del data['evva_prognose_uren']
# Add to all data list
werkpakket_data.extend(object_data)
return werkpakket_data
def _calculate_kengetal(project: ViiaProject, general_data: dict) -> tuple:
""" Function to calculate total budget hours per specificatie, method, size and cost items."""
# Get general information
if not general_data or 'object_opnamemethodiek' not in general_data or \
'opdracht_rekenmethodiek' not in general_data:
raise ValueError("ERROR: The general data from MYVIIA was not correctly retrieved, please check.")
opnamemethodiek_types = [opnamemethodiek['id'] for opnamemethodiek in general_data['object_opnamemethodiek']]
rekenmethodiek_types = [rekenmethodiek['id'] for rekenmethodiek in general_data['opdracht_rekenmethodiek']]
# Get all opdracht_kengetal
opdracht_kengetallen = myviia_get_opdracht_kengetallen(token=project.token)
if not opdracht_kengetallen:
raise ValueError("ERROR: The kengetallen data from MYVIIA was not correctly retrieved, please check.")
specificatie_types = list(set([opdracht_kengetal['specificatie'] for opdracht_kengetal in opdracht_kengetallen]))
size_types = [1, 2, 3, 4, 5]
kostendrager_types = list(set([opdracht_kengetal['kostendrager'] for opdracht_kengetal in opdracht_kengetallen]))
# Calculate opname scope kengetallen
opname_kengetal = {}
for specificatie in specificatie_types:
opname_kengetal[specificatie] = {}
for opnamemethodiek in opnamemethodiek_types:
opname_kengetal[specificatie][opnamemethodiek] = {}
for size in size_types:
opname_kengetal[specificatie][opnamemethodiek][size] = {}
for kostendrager in kostendrager_types:
opname_kengetal[specificatie][opnamemethodiek][size][kostendrager] = 0
for opdracht_kengetal in opdracht_kengetallen:
if opdracht_kengetal['specificatie'] == specificatie and \
opdracht_kengetal['opnamemethodiek_id'] == opnamemethodiek and \
opdracht_kengetal['object_grootte_id'] == size and \
opdracht_kengetal['kostendrager'] == kostendrager:
opname_kengetal[specificatie][opnamemethodiek][size][kostendrager] += \
opdracht_kengetal['kengetal']
# Calculate beoordeling scope kengetallen
beoordeling_kengetal = {}
for specificatie in specificatie_types:
beoordeling_kengetal[specificatie] = {}
for rekenmethodiek_type in rekenmethodiek_types:
beoordeling_kengetal[specificatie][rekenmethodiek_type] = {}
for size in size_types:
beoordeling_kengetal[specificatie][rekenmethodiek_type][size] = {}
for kostendrager in kostendrager_types:
beoordeling_kengetal[specificatie][rekenmethodiek_type][size][kostendrager] = 0
for opdracht_kengetal in opdracht_kengetallen:
if opdracht_kengetal['specificatie'] == specificatie and \
opdracht_kengetal['rekenmethodiek_id'] == rekenmethodiek_type and \
opdracht_kengetal['object_grootte_id'] == size and \
opdracht_kengetal['kostendrager'] == kostendrager:
beoordeling_kengetal[specificatie][rekenmethodiek_type][size][kostendrager] += \
opdracht_kengetal['kengetal']
return opname_kengetal, beoordeling_kengetal
### ===================================================================================================================
### 4. End of script
### ===================================================================================================================