### ===================================================================================================================
### Create VIIA scopechanges ROK report, form for the client
### ===================================================================================================================
# Copyright ©VIIA 2025
### ===================================================================================================================
### 1. Import modules
### ===================================================================================================================
# General imports
from __future__ import annotations
from pathlib import Path
from datetime import datetime
from typing import TYPE_CHECKING, Optional, Dict, Union, List
from dataclasses import dataclass
from copy import copy
# References for functions and classes in the haskoning_structural package
from haskoning_structural.fem_tools import fem_create_folder
from haskoning_structural.fem_math import fem_compare_values
# References for functions and classes in the viiaPackage
if TYPE_CHECKING:
from viiapackage.viiaStatus import ViiaProject
from viiapackage.database import myviia_get_scopewijziging
# Import module openpyxl
# This module is used for creating Excel-sheet for the project-leader
from openpyxl import load_workbook
from openpyxl.styles import Font
### ===================================================================================================================
### 2. Function to create the scope change ROK report
### ===================================================================================================================
[docs]def viia_create_scope_change_rok_report(
project: ViiaProject, scope_change_id: int, template_locations: Dict[str, str],
output_folder: Optional[Path] = None, project_leader: str = None) -> List[Path]:
"""
This function creates a report for proposing scope changes for the NCG in the ROK framework agreement. Data from
MYVIIA related to the scope change is collected in a 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.
- scope_change_id (int): ID of the selected object scope-change in MYVIIA database.
- 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!
- project_leader (str): Name of the project leader to be filled in the report. Default value is None, in which
case the name is not added in the Excel file.
Output:
- Filled scope change report template with information from 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('reports', ref_folder=project.workfolder_location)
report_location = project.workfolder_location / 'reports'
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 scope change data from MYVIIA
data = myviia_get_scopewijziging(scope_change_id=scope_change_id, token=project.token)
# General info
current_time = datetime.now()
general_data = {
# Opdrachtnemer
'B3': 'VIIA',
# Opdrachtnummer
'B4': data['opdracht']['opdrachtnummer_og'],
# Datum indiening
'B5': current_time.strftime('%d-%m-%Y'),
# Totaalbedrag(excl.btw):
'B6': data['financieel_impact'],
# Totaalbedrag(incl.btw):
'B7': data['financieel_impact'] * 1.21
}
@dataclass
class AdditionalWorkRow:
opdrachtnemer: str = None
adres_id: str = None
ad_nr: str = None
adres: str = None
benodigde_actie: str = None
bedrag_excl: float = None
bedrag_incl: float = None
reden_meerwerk: str = None
@property
def data_services(self):
return {
'A': self.opdrachtnemer,
'B': self.adres_id,
'C': self.ad_nr,
'D': self.adres,
'E': self.benodigde_actie,
'F': self.bedrag_excl,
'G': self.bedrag_incl,
'H': self.reden_meerwerk}
services = []
total_excl = 0.0
for osa in data['opdracht_scopewijziging_adressen']:
for onderzoek in osa['onderzoeken']:
total_excl += float(onderzoek['onderzoek']['tarief'])
services.append(
AdditionalWorkRow(
opdrachtnemer='VIIA',
adres_id=osa['object_adres']['adres_id_ncg'],
ad_nr=project.project_information['projectnummer_og'],
adres=f"{osa['object_adres']['straatnaam']} {osa['object_adres']['huisnummer']}, "
f"{osa['object_adres']['plaatsnaam']}",
benodigde_actie=onderzoek['onderzoek']['aanvullend_onderzoek'],
bedrag_excl=float(f"{float(onderzoek['onderzoek']['tarief']):.2f}"),
bedrag_incl=float(f"{float(onderzoek['onderzoek']['tarief']) * 1.21:.2f}"),
reden_meerwerk=onderzoek['motivatie']))
for dienst in osa['diensten']:
total_excl += float(dienst['dienst']['tarief']) * float(dienst['uren'])
services.append(
AdditionalWorkRow(
opdrachtnemer='VIIA',
adres_id=osa['object_adres']['adres_id_ncg'],
ad_nr=project.project_information['projectnummer_og'],
adres=f"{osa['object_adres']['straatnaam']} {osa['object_adres']['huisnummer']}, "
f"{osa['object_adres']['plaatsnaam']}",
benodigde_actie=dienst['dienst']['functionaris'],
bedrag_excl=float(f"{float(dienst['dienst']['tarief']) * float(dienst['uren']):.2f}"),
bedrag_incl=float(f"{float(dienst['dienst']['tarief']) * float(dienst['uren']) * 1.21:.2f}"),
reden_meerwerk=dienst['motivatie']))
warning = None
if not fem_compare_values(value1=total_excl, value2=data['financieel_impact'], precision=2):
warning = \
f"Het totaalbedrag op basis van de diensten ({total_excl:.2f} excl. BTW) komt niet overeen met het " \
f"totaal in de scopewijziging."
# Create report
generated_reports = []
for report_name, template_location in template_locations.items():
try:
output_document_name = \
f"{project.project_information['objectnummer_viia']}-SW{data['id']}-Indienverzoek Aanvullend werk " \
f"bij ontwerpnotitie-{current_time.strftime('%Y%m%d%H%M%S')}.xlsx"
target_file = report_location / output_document_name
# Load Excel template
wb = load_workbook(project.viia_settings.project_specific_package_location / template_location)
ws = wb.active
# Fill in header
for k, v in general_data.items():
ws[k] = v
ws['B6'].number_format = '€ #,##0.00'
ws['B7'].number_format = '€ #,##0.00'
# Add warning
if warning:
ws['D6'].value = warning
ws['D6'].font = Font(color="FF0000")
# Check if the table needs to be expanded (more than 8 rows are required)
copies = 0
if len(services) > 8:
# Insert empty rows to make space
copies = len(services) - 8
ws.insert_rows(20, amount=copies)
# Update the table range
table = ws.tables['Tabel1']
table.ref = f'A12:H{21+copies}'
# Copy each cell from the source row
for i in range(copies):
target_row = 20 + i
source_row = 18 if target_row % 2 == 0 else 19
for col in range(1, ws.max_column + 1):
source_cell = ws.cell(row=source_row, column=col)
target_cell = ws.cell(row=target_row, column=col)
target_cell.value = source_cell.value
# Copy style
if source_cell.has_style:
target_cell.font = copy(source_cell.font)
target_cell.border = copy(source_cell.border)
target_cell.fill = copy(source_cell.fill)
target_cell.number_format = source_cell.number_format
target_cell.protection = copy(source_cell.protection)
target_cell.alignment = copy(source_cell.alignment)
# Unmerge the original range B26:B30
ws.unmerge_cells('B26:B30')
# Recreate the merged range shifted down
new_start = 26 + copies
new_end = new_start + 4 # because original range was 5 rows tall
ws.merge_cells(start_row=new_start, start_column=2, end_row=new_end, end_column=2)
# Write data for selected contract to the worksheet
row_pointer = 13
for i, service_data in enumerate(services):
for j, service in service_data.data_services.items():
ws[f'{j}{row_pointer+i}'].value = service
# Set projectleader
ws[f'B{23+copies}'] = project_leader
ws[f'B{24+copies}'] = current_time.strftime('%d-%m-%Y')
# Unhide all rows in Excel
for r in [2, 8, 11]:
ws.row_dimensions[r].hidden = False
# 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. End of script
### ===================================================================================================================